Introduction to SQL

Introduction

Whether you're using a blog, a forum or even a site to help you achieve your goals the chances are that there is some database interaction going on. In a lot of cases this will be SQL, and most probably using MySQL. Structured Query Language is a specialised syntax for queries to be ran in a database, it can be very powerful.

Hopefully a lot of the more complex issues such as joins and data manipulation will be clarified within this article, and will serve as a reliable reference document.

The Base

For these example queries, we'll be using a very simple table called users, which will just have 3 columns:

CREATE DATABASE

Before creating the table, we have to make the database that will hold the tables - this is super simple:

CREATE DATABASE `codular_tutorial`;

Next, we need to work on some table creation queries, and then move on to running some queries against the table.

CREATE TABLE

We can't do anything without actually creating the table that we want first. For this we're going to create 3 columns, of three differing types. Let's start with the basics:

CREATE TABLE `users` ();

This won't actually run at all yet, as all tables are required to have at least one column. Let's start with the user_id column. This is going to be of time INT as it will be an integer - we'll make it 10 in length for fun (who knows there might be a billion rows!). So in the first instance we can adapt that line to be:

CREATE TABLE `users` (
    `user_id` INT(10)
);

With this column, we don't actually want any negative numbers. We can therefore make it an unsigned integer, this will double the maximum number that we can store within this column (on 32 bit machine that is from something like 2.1million to 4.2million).

Also, don't forget that we'd specified the user_id column to be auto incrementing number, so we use the word AUTO_INCREMENT a long with specifing what number to start the auto increment at. Bringing both of these together we can get the query as:

CREATE TABLE `users` (
    `user_id` INT(10) unsigned AUTO_INCREMENT
) AUTO_INCREMENT=1;

Quite simply we've told the SQL that we want to start our increments at 1, we could change that to 30 if we wanted. We'll come to the setting of the primary key shortly

Finally we'll want to add the two extra columns, one is going to be a string (VARCHAR) of 50 characters and the other is going to be a TINYINT - there is actually no boolean type within MySQL unfortunately.

CREATE TABLE `users` (
    `user_id` INT(10) unsigned AUTO_INCREMENT,
    `name` VARCHAR(50),
    `likes_bacon` TINYINT(1) DEFAULT 1
) AUTO_INCREMENT=1;

The observant will notice that there is a DEFAULT keyword thrown in there - this is what we're using to specify the default value for the field, in this case, we're going to assume that everyone likes bacon!

Primary Keys & Indexes

We have to specify the user_id field as the primary key of the table, but with that there is a bit of a subtopic to cover very briefly. Within MySQL there are things called indexes:

To pull it back to the query, we can now specify the user_id field as the primary key giving us this completed query:

CREATE TABLE `users` (
    `user_id` INT(10) unsigned AUTO_INCREMENT,
    `name` VARCHAR(50),
    `likes_bacon` TINYINT(1) DEFAULT 1,
    PRIMARY KEY (`user_id`)
) AUTO_INCREMENT=1;

That will then create your table for the rest of this tutorial.

DROP TABLE

This is a very simple query, but not one to be ran by mistake. As it says on the tin, this will delete the whole table that you have and make it so that it's gone ... forever.

DROP TABLE `users`;

It couldn't get any easier than that. It is very rare that you will actually want to run this at any point within your own web application or anything, but I have put it in incase (somehow) you break your earlier table and want to start again.

SELECT

To start with, let's just select everything from the table:

SELECT * FROM `users`

Certain columns

What if we wanted, instead of selecting all of the columns from the table, to select just the names of all of the users? We can replace the previously used * with a comma separated list of column names:

SELECT `name` FROM `users`;

This will now return a list of all of the users with in the table.

WHERE

Now that we can pull everything out of the table, it's pretty useless. We might want to just pull out the people that like bacon (no idea who wouldn't like bacon though!). To do that, we want to look at using the WHERE keyword, this then takes an expression to check the data against:

SELECT `name` FROM `users` WHERE `likes_bacon` = 1;

Simple as that, we want to get the data from the table where the likes_bacon column has a value of 1.

Multiple conditions

What if we wanted to select from the table people with the name Michael that like bacon? That's simple, we can use as many where conditions as we want. We simple separate them with either the word AND or OR, meaning all of the expressions have to be true, or only one of them respectively:

SELECT `user_id` FROM `users` WHERE `likes_bacon` = 1 AND `name` = 'Michael';

Note: Here we're comparing the name against a string, so we wrap that in single quotes

ORDER BY

Now, let's look at sorting the data, it's fine pulling out a list of people, but what if we want to simply alphabetise the list, we can do that using the ORDER BY keyword. This simply takes a column name after it, with an extra word ASC or DESC for ascending or descending sorting respectively.

SELECT `name` FROM `users` WHERE `likes_bacon` = 1 ORDER BY `name` ASC;

Two things to note here: 1. You don't have to be selecting the column that you want to order by 2. You can order by multiple columns by simply comma separating extras.

LIMIT

If we have a thousand users, the chances are that we don't want to pull them all out at the same time, so we might want to pull out just the first 10, this is where ORDER comes in. It takes two comma separated numbers after it showing the row to start at, and how many to pull out:

SELECT `name` FROM `users` WHERE `likes_bacon` = 1 ORDER BY `name` ASC LIMIT 0, 10;

This is interesting, because this forms the foundations for allowing us to pagination by simplying increasing the first number we can get a second batch, and a third - just like on pages.

With LIMIT there is the option to omit the first number and the comma, removing these will default back to starting from 0. So the above query could be correctly written as:

SELECT `name` FROM `users` WHERE `likes_bacon` = 1 ORDER BY `name` ASC LIMIT 10;

INSERT

This is a really simple statement, probably as easy as it gets. I'll just jump in with an example and then break it down.

INSERT INTO `users` (`name`, `likes_bacon`) VALUES ('Michael', 1);

So we have three sections with in the statement:

  1. The table, this can't be omitted - we need to tell it where to actually put the new information.
  2. The column names that we're providing values for. These don't have to be in the same order as the columns in the table, and you don't have to include every column. For ours, we have an autoincrementing id, so we don't insert a value for it.
  3. The values come last, conveniently after the word VALUES. The values must be in the same order as the respective column, ie. The name value first, then the likes_bacon value.

Multiple Inserts

We can duplicate that line a number of times to insert multiple rows, however we can simplify this by providing a comma separated list of value parenthesese:

INSERT INTO `users` (`name`, `likes_bacon`) VALUES ('Michael', 1), ('Andrew', 0), ('Ben', 0);

This will now insert 3 rows into the table for us.

You will want to be careful when inserting data, ensuring that you escape any characters when inserting them. For example, above we would have to prepend any apostrophe that was being inserted wtih a backslash, so can't would become can\'t.

UPDATE

Unless you're crazy, and foolish. You'll be wanting to probably use some sort of UPDATE statement. This will allow you to run a query that will, as the name suggests, update some of the columns of data that you have. Like the SELECT statement, this can take a WHERE condition, and a LIMIT - however, the LIMIT can only take one number, telling it how many to update.

UPDATE `users` SET `name` = 'Bacon Hater', `likes_bacon` = 0 WHERE `user_id` = 1;

So this query is simply going to update the name and likes_bacon columns for the row that has user_id of 1. As you can see, we can specify multiple columns to update by comma separating them. In this case, we wouldn't need to use a LIMIT 1 as there should only be one row with that ID - as it is a primary key which increments each time we insert a new row.

DELETE

You want to be really careful when deleting, it is irreversible! What a lot of sites will do is have a column on a table that shows whether it is deleted or not instead of actually deleting. One of the advantages of this is that you can undo deletes that were done in error, but make sure you delete completely for example if someone deletes their account from your site.

We can use WHERE and LIMIT just like in the UPDATE query - again remembering that we can only use one number for limiting.

DELETE FROM `users` WHERE `likes_bacon` = 0 LIMIT 10;

As you'd expect this would delete the first 10 users that don't like bacon - as after all why would they hate it!?

Conclusion

This is not a complete tutorial of everything that you will ever need to get your own site up and running - there are lots of other things that I will try to cover in a future article.

I didn't explain or specify an encoding or charset for the table that you were creating - by not doing so, it will default to whatever the database (and probably server) are set to have as their defaults.

There is a large chance that you probably have a server that also runs PHPMyAdmin, this is absolutely brilliant at helping guide you through the stages of creating a database and then the table in a nice simple GUI. You can then inspect the CREATE TABLE SQL by going to export the table that you're in.

Tag: MySQL