Short SQL Tutorial

I was going to write a simple tutorial about SQL for people who know HTML and some programming. I found this other excellent tutorial, however, and scrapped my own.

http://www.hotwired.com/webmonkey/databases/tutorials/tutorial1.html

The article describes how to set up a server and database and create a real db driven website.

There are also a number of tutorials about SQL, which I'm sure I'll get linked in here eventually.

In the meantime, here's a very short description of some common SQL queries that might prove useful to bright HTML coders.

About Databases

The simplest database is a table, with columns and rows. In SQL lingo, we call these things columns and rows. (In other systems, they're called fields and records, but we're in SQL-land, so let's not confuse the issue by using alien terminology).

Here's an example of a table. In SQL lingo, a database is a collection of one or more tables.

TABLE: cream 

Generally, in SQL-land, you work with only one database at a time. You start using one database, and limit your accesses to the tables in that single database. You'll see why in the exaple SQL statements below.

But, before you can start typing statements, you need to get into MySQL and tell it what database to use. Here's a short transcript of a session. You can't use it verbatim, but you can study it:

system% mysql
mysql> use icecream;
Now using icecream
mysql>

Sample SQL statements:
SELECT email FROM cream;
SELECT name FROM cream WHERE ice_cream = 'marble fudge';
SELECT * FROM cream WHERE ice_cream = 'vanilla' OR ice_cream = 'cherry';
SELECT * FROM cream WHERE email LIKE '%cyberjava.com';
SELECT name FROM cream WHERE name LIKE '% K%';

Each SQL statement returns some rows. More specifically, each query returns a table composed of the results. Here's each statement, followed by the rows they return. 

SELECT email FROM cream;

johnk@cyberjava.com
fredf@hannabarbera.com
vamp@cyberjava.com
ralph@honeymooners.org

The statement returned a table composed of all the email addresses in cream. It returned only the emails because we wrote 'SELECT email'. 
SELECT name,email FROM cream WHERE ice_cream = 'marble fudge';

Fred Flintstone fredf@hannabarbera.com
Ralph Kramden ralph@honeymooners.org

The statement returned a table composed of rows where the column ice_cream contained 'marble fudge'. You use the 'WHERE' statement to specify matching criteria. It returned both the names and emails because we specified 'name,email'.

SELECT * FROM cream WHERE ice_cream = 'vanilla' OR ice_cream = 'cherry';

John Kawakami johnk@cyberjava.com vanilla
Theda Bara vamp@cyberjava.com cherry

This statement uses 'SELECT *' to return all the columns. It also uses the 'OR' keyword to match the two different flavors of ice cream. (There's also an AND statement as well as a NOT.) 
SELECT * FROM cream WHERE email LIKE '%cyberjava.com';

John Kawakami johnk@cyberjava.com vanilla
Theda Bara vamp@cyberjava.com cherry

This coincidentally returned the same table as the last statement. This time, though, we used the 'WHERE email LIKE' statement. The LIKE statement lets you specify partial matches. '%cyberjava.com' matches everything that ends in 'cyberjava.com'. The % works like the '*' in DOS. 
SELECT name FROM cream WHERE name LIKE '%Ã?K%';

John Kawakami
Ralph Kramden

This one's similar to the last, except is picks out the pattern '% K%', which, in this case, matches to everyone whose last name begins with a capital K. (Yes, you can use two %s.)



That's all for now. SQL is really a lot more powerful than this. You can use it to change data, as well as summarize it. With more complicated statements, you can write statements that selectively update or create records based on arbitrary criteria. You don't need to write programs to make these things happen.