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.
For these example queries, we'll be using a very simple table called
users, which will just have 3 columns:
user_id- an automatically incrementing integer
name- a string of the user's name, limited to 50 characters
likes_bacon- a boolean value (1 or 0), showing if the user likes bacon
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.
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:
Unique: Only allows one column within the table to have the value. You can have more than one column making up a unique index if you wanted. For example if you were running a user management system and you only wanted a username used once - you would set that as a unique index. Separately you can then set the email address to be a unique index. Also, if you wanted to make it so that a user can only like a post once, you would have two columns making up one unique index in your
likestable, the user identifier and the post identifier. This means that there can not be able more rows within the table that has the same values for both fields.
Index: These are used for increasing performance of the requests on your tables. By indexing on a column that you frequently use in your
WHEREqueries you'll find that your query runs much faster. You might not see as much improvement with this if you're running a small database, but as your database grows in size you will definitely see a huge improvement in the speed of your queries.
Primary Key: These are a bit of an amalgamation of the above two types of key. However, unlike the others, there can only be one of these in a table. I personally tend to create all tables with an
idfield of some description that is a primary key and auto incrementing.
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.
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.
To start with, let's just select everything from the table:
SELECT * FROM `users`
- Here we're using a
*to show that we want to get all of the data out of the table. We are able to specify specific columns that we want returned if we want.
- We use the word
FROMfollowed by the table that we want to select the data from, here we're selecting from the table
- Those crazy symbols around the word
usersare called backticks. They are commonly used in SQL around column and table names, anything in single or double quotes is interpreted as a string value.
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.
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
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
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
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
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.
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.
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;
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:
- The table, this can't be omitted - we need to tell it where to actually put the new information.
- 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.
- The values come last, conveniently after the word
VALUES. The values must be in the same order as the respective column, ie. The
namevalue first, then the
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
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
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.
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
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!?
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.