Intro to SQL and Databases

by Trevor Page on November 28, 2013

Java-Podcast

What’s all this CRUD about?

  • Create
  • Read
  • Update
  • Delete

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.

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:

Create

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 insert keyword.

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.

Read

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 username and 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 username and 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.

Update

When you want to change the data for any existing row in a database, you need to invoke an Update using the update keyword.

Let’s say that you wish to change a given user’s email address… how would you do it?

update users 
set email = 'trevor@javavideotutorials.net' 
where email = 'info@howtoprogramwithjava.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.

  1. It will figure out what table will be changing (this is found in the first line of the script above)
  2. It will figure out which row(s) that need to be updated
  3. 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 = 'info@howtoprogramwithjava.com'), 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 ‘trevor@javavideotutorials.net’.

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 = 'trevor@javavideotutorials.net' 
--where email = 'info@howtoprogramwithjava.com';

select * from users 
where email = 'info@howtoprogramwithjava.com';

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 select statement.

Make sense?

Delete

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 ‘info@howtoprogramwithjava.com’. It would look something like this:

delete from users
where email = 'info@howtoprogramwithjava.com';

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.

Podcast Survey

I hope I have helped to shed some light on the mysteries of SQL!

Please help to support these podcasts by filling out a quick survey. This will help keep this information FREE for you to consume and it will help me to keep creating these valuable tutorials at a predictable pace!

Keep Podcast Free

{ 1 comment… read it below or add one }

Leave a Comment

Powered by sweet Captcha

{ 1 trackback }

Previous post:

Next post: