Blake O'Hare .com

Learn SQL in Under 4 Minutes

This tutorial assumes you have a database set up and ready to go, and you know how to write queries against it. This tutorial also assumes you already have a strong handle on programming concepts. This will go fast.

Ready? Go!

SQL (pronounced "sequel") is merely a language used to interact with a database. Alone, it doesn't have much practical purpose but is usually used in conjunction with another language. Such as PHP. You can, however, write raw queries against a database for your own amusement. This is handy for testing or learning purposes.

SQL databases store information in tables. When you add information to the database, you are adding a row to a table somewhere. The tables and their columns are defined by you, the programmer.

For example, if you are creating a website where users can register an account, you may have a table called users. The columns in the user table may include username, email address, password, and last time they logged in. As a programmer, it is your duty to create the columns for this table and create a public interface (like through PHP) for users to add rows to this table. Your PHP code will execute SQL queries behind a snazzy HTML front end.

Part 1 - Creating the tables

The query for creating a table is simple...

CREATE TABLE the name of the table (col1 type, col2 type, col3 type, ...);

So in our example...

CREATE TABLE users (username varchar(20), emailaddress varchar(80), password varchar(16), lastlogin int(11));

For small amounts of text, like the username, you should use varchar as the type followed by the maximum length of the text string.

For numbers, use int(11)

For long text, use text

You may notice that I used an integer for the time of the last login. There are date and time types in SQL but you can also use timestamps (that's what I do most of the time).

Part 2 - Adding data to the tables

INSERT INTO table name (col1, col2, col3, col4, ...) VALUES (value1, value2, value3, value4, ...);

Of course, there must be the same number of columns as values. For example...

INSERT INTO users (username, emailaddress, password, lastlogin) VALUES ("Mr. Xenon", "", "go banana!", 1197351913);

Part 3 - Retreiving data from the tables

SELECT col1, col2, col3, ... FROM tablename WHERE condition1 AND condition2 OR condition3;

This is the SELECT statement. After the SELECT keyword, simply list the columns you wish to retrieve from the database separated by commas. After that comes the FROM keyword. This is where you specify which table you are retrieving data from. After that comes the WHERE keyword, where you can list conditions on which rows you want to filter out. You can join these conditions by the keywords AND and OR and you can also group them with parenthesis.

In our example situation, we may have a page to show all users that have logged in in the past 10 minutes. (assuming that the timestamp is number of seconds and the current time is 1197352299)...

SELECT username FROM users WHERE lastlogin > (1197352299 - 60 * 10);

If this were PHP, the SQL query would be sent as a string which we would dynamically generate using the current time from the time() function instead of that hard-coded number.

Part 4 - Modifying data in the tables

UPDATE tablename SET col = value WHERE condition;

The UPDATE statement simply uses the name of the table, the column you want to change, the value you want to change it to, and a condition to identify the rows you want to change.

Suppose I wanted to change my email address...

UPDATE users SET emailaddress="" WHERE username="Mr. Xenon";

Part 5 - Removing data from the table

DELETE FROM tablename WHERE condition

Simply give the table and the condition to determine which rows to delete.

DELETE FROM users WHERE username="Mr. Xenon"

And now you know enough SQL to pretty much do anything in PHP or another host language!

But seriously, this is the bare-bones, crash course, everyday-low-prices tutorial of how to start coding with SQL. If you only use the information you learned in this tutorial, the SQL you write will be slow and inefficient. However, it is enough information to actually do some useful stuff. I strongly encourage you to read up on a full-length tutorial once you've practiced a few queries.

Happy SQL'in!