What’s all this CRUD about?
This is the stuff at the heart of all databases and SQL. A database essentially carries out these four operations over and over again for the duration of its existence.
In this podcast (which you can play above), I talk about these four database operations in detail and the actual syntax that is used in a flavour of SQL known as MySQL.
You can think of SQL as kind of a specification that needs implementation. In Java terms you can think of SQL like an interface and MySQL like the class that implements it.
I chose to go into depth with MySQL as it’s a free implementation of SQL, it is popular and widely adopted with plenty of HOW-TO articles around the net.
If you wish to learn how to install the MySQL RDBMS and a GUI for manipulation your database, then please check out this video:
Let’s talk syntax!
The first step in our CRUD operations is the Create operation. This is used to create data in the database. This can be accomplished using the
Let’s assume you would like to
insert a new
User into your database. How would you accomplish this?
I like to approach these questions with the mindset of “What would I need to know if I was the database management system?” I would need to know where to insert the data, and specifically what data to insert.
Let’s see what that looks like in the MySQL syntax:
insert into users (username, password) values ('tpage', 'password123');
In the SQL code above, we have satisfied the questions of where and what. The where is satisfied by specifying the name of the table in which the data is to be inserted, and also the columns we wish to populate. The what is satisfied by the actual data we are providing in the single quotes. For more info on this, be sure to actually listen to this podcast by clicking the play button at the top of this post.
This is by far the most commonly used operation in any database. The ability to retrieve/read data in a database is crucial to a properly functioning application. In order to read information from a database using MySQL you’ll use the
select keyword. I’m not sure why they didn’t choose to go with a keyword like “read”, but hey, what can you do!
Just like in the “Create” section, when “Reading” information from a database, you’ll need to think of what the database management system would need to use to get you to the information that you want. In this case, it’s mostly just a matter of “where” is the data that you want. Let’s assume you want to retrieve the username and password for a particular user to validate that they are indeed a valid user. What would you need to specify? Well, you need to tell the database where this information exists in terms of the table and the columns. Here’s an example:
select username, password from users;
Now this code will give you the
password of ALL the records in the
users table. This isn’t exactly what we wanted, we specifically want to verify if ONE particular user exists in the database and what that particular user’s
password is. So how do we accomplish this with SQL? It’s all about the “where”!
select username, password from users where username = 'tpage';
Don’t forget that you can also keep ‘chaining’ the where question in MySQL using the
and keyword like so:
select username, password from users where username = 'tpage' and password = 'password123';
This will only return a match if you have a user that exists in the
users table with the
username = 'tpage' which has a
password = 'password123'.
Note: You can see that I broke up the above SQL statement with carriage returns so that it becomes a bit more readable. I separate each ‘section’ of the syntax with a line break.
When you want to change the data for any existing row in a database, you need to invoke an Update using the
Let’s say that you wish to change a given user’s email address… how would you do it?
update users set email = 'firstname.lastname@example.org' where email = 'email@example.com';
This is kind of tricky, but it functions just like a Java statement. The database management system will essentially read this statement in chunks.
- It will figure out what table will be changing (this is found in the first line of the script above)
- It will figure out which row(s) that need to be updated
- It will change the data to the value you’ve specified
In other words, the database management system (MySQL) is able to find the row of data that needs to be updated with the last line (
where email = 'firstname.lastname@example.org'), it will then take the row(s) that it finds and execute the update that you’ve specified to change the row(s) email to be ‘email@example.com’.
Note: It’s very important that you know the results of the
update statement before it’s run, as you don’t want to accidentally update MORE rows than you intended. For this, I would recommend copy/pasting your
update statement and changing it to reflect a
select statement like so:
--update users --set email = 'firstname.lastname@example.org' --where email = 'email@example.com'; select * from users where email = 'firstname.lastname@example.org';
This way you can run the
select statement and ensure that it returns the expected rows. Once you’re satisfied with the rows that are returned, you can run the
update statement and it will then update those rows that you saw in the
The final main operation that MySQL can carry out is a
delete. This is used to complete destroy a row (or multiple rows) of data. This is very similar to writing a
select statement, in that you are narrowing in on the data that you wish to delete. Let’s say we want to delete the row of data that pertains to the user with the email address ‘email@example.com’. It would look something like this:
delete from users where email = 'firstname.lastname@example.org';
Easy peasy. The only thing that trips me up sometimes with the
delete statement, is that sometimes I put an asterisk next to the
delete keyword. It feels natural to want to say “delete ALL from users” by typing
delete * from users, but this will in fact result in a syntax error.
I hope I have helped to shed some light on the mysteries of SQL!