SQL
SQL - short for Structured Query Language - is used for storing information in databases, and getting it back out again. It easily searches, sorts, filters, dices, chops, and analyzes information, making it easier to work with. (It also makes a mean carrot cake.)
If you're setting up a web site of low or moderate complexity, you don't need to know that much about SQL - the scripts you install take care of the aggravating work of talking to the database themselves. However, if you're the kind of person that likes to break a sweat and get your hands dirty, you might be interested in learning more about SQL.
Contents |
Getting a Database
Getting a database at DreamHost to practice on is easy - just visit the Goodies section of the Control Panel.
Getting Connected
You can practice SQL commands in a few places. The easiest is probably to connect using the mysql command from your shell account.
Tables, Rows, and Columns
If you've ever used a spreadsheet, you already know how a database stores information (conceptually, at least - you really don't want to know how the data is actually stored!). Tables are used to store information that's directly related. You can have pretty much as many tables in a database as you want.
Just like the tables from your favorite high school textbook or encyclopedia, tables in a database have rows and columns.
Here's a simple table you might typically find in a database:
| ID | LastName | FirstName | Rank | SpecialTraits |
|---|---|---|---|---|
| 1 | NULL | Spock | Captain | Turns green when embarrased |
| 3 | Chekov | Pavel | Commander | Steers wessels with ease |
| 2 | Scott | Montgomery | Captain | Possesses a unique understanding of 20th century computer technology |
| 4 | Kirk | James | Admiral | Talks... with frequent... dramatic... pauses |
The rows run horizontally. In this example, each row corresponds to a single, distinct Star Trek character. I'll leave the rest of the rows in this database to your imagination. If you're more of a TNG fan, for example, you might add an additional row for Picard, Riker, or maybe even Data.
The columns run vertically. Each row has columns which label and describe the information in a row. Don't worry if this doesn't make sense right now - there will be plenty of hands-on practice later.
Words of Warning
WARNING: If you're following along on your own database, be careful. Many of the commands I'll be introducing have the ability to really wreck your data. Please practice on a brand new database that doesn't contain anything of value (especially since the Happy DreamHost Database Creation Team lets you have as many as you need).
WARNING: Stop reading until you read the preceding paragraph.
WARNING: Okay, I mean it this time.
By the way, if you somehow missed or ignored all of that, you should know that there is generally no undo in SQL. So make sure you consider the effect of any query carefully before you run it. It might be a good idea to try any complex commands on a test database first.
Also, you should know that SQL varies slightly from database to database. If you're following along on MySQL, which DreamHost provides, you should have no problem. Otherwise, you might need to adjust the examples provided.
Creating the Table
Now that you've created a new database so you can practice safely, it's time to create the sample table I just showed you. How you do this depends on the database engine you're using; with MySQL, you might type a command something like this:
CREATE TABLE Crewmembers (ID TINYINT(3) UNSIGNED AUTO_INCREMENT PRIMARY KEY, LastName VARCHAR(50), FirstName VARCHAR(50), Rank VARCHAR(50), SpecialTraits TEXT);
If everything's okay, MySQL should hum a microsecond or two before helpfully replying with Query OK, 0 rows affected.
Basically, all that command does is create a new table with 5 columns. We describe the data each column is supposed to hold, and for the ID column, we ask MySQL to automatically number new rows and make a primary key. Primary keys are cool because they guarantee that even if all the other data is the same, we can still distinguish rows from each other. You'll see more about why this is important as we continue.
Now, let's fill up your new, empty table with the sample data. If you don't understand the following commands, don't worry - we have a little further to go first.
INSERT INTO Crewmembers (ID, LastName, FirstName, Rank, SpecialTraits) VALUES (1, NULL, "Spock", "Captain", "Turns green when embarrased"); INSERT INTO Crewmembers (ID, LastName, FirstName, Rank, SpecialTraits) VALUES (3, "Chekov", "Pavel", "Commander", "Steers wessels with ease"); INSERT INTO Crewmembers (ID, LastName, FirstName, Rank, SpecialTraits) VALUES (2, "Scott", "Montgomery", "Captain", "Possesses a unique understanding of 20th century computer technology"); INSERT INTO Crewmembers (ID, LastName, FirstName, Rank, SpecialTraits) VALUES (4, "Kirk", "James", "Admiral", "Talks... with frequent... dramatic... pauses");
Retreiving Information with SELECT
Basics
How's your database so far? We've done quite a bit - you added a new table and four new rows. If you want to see how your creation looks, you can use the SELECT command. It looks like this:
SELECT * FROM Crewmembers;
That command basically says "Show me all the columns of all the rows from the table called 'Crewmembers'." Pretty simple, huh? Surprisingly enough, MySQL shows you the sample table from the previous section. Here it is again:
| ID | LastName | FirstName | Rank | SpecialTraits |
|---|---|---|---|---|
| 1 | NULL | Spock | Captain | Turns green when embarrased |
| 3 | Chekov | Pavel | Commander | Steers wessels with ease |
| 2 | Scott | Montgomery | Captain | Possesses a unique understanding of 20th century computer technology |
| 4 | Kirk | James | Admiral | Talks... with frequent... dramatic... pauses |
Specifying Columns
The new table is well and good, but you may not be quite happy with it. "For now," you might be thinking, "all I want to know is the names of the fearless crew of the Enterprise. I could care less about their rank or odd hobbies. For that matter, I really don't want to see this primary key thingee the mean author is making me put in."
If you were to write a script to get only the names and filter out all the other information, it would have to be fairly complicated - not to mention a pain in the butt to write and a waste of DreamHost's network bandwidth. (And we certainly don't want to waste that - their prices are low for a reason, after all.)
Luckily, SQL is a pro at this sort of thing. Here's how you would do it:
SELECT LastName, FirstName FROM Crewmembers;
...and here's what MySQL would tell you:
| LastName | FirstName |
|---|---|
| NULL | Spock |
| Chekov | Pavel |
| Scott | Montgomery |
| Kirk | James |
Wow! That's a lot easier to read. Don't worry about the missing information, by the way - all we've done is hide it. If you do the full SELECT command, it'll all be back, just as you left it.
Note that you can put the columns in any order you choose. Just because LastName comes before FirstName in the table doesn't mean it always have to be this way. You could just as easily type:
SELECT FirstName, LastName FROM Crewmembers;
...and get:
| FirstName | LastName |
|---|---|
| Spock | NULL |
| Pavel | Chekov |
| Montgomery | Scott |
| James | Kirk |
Limiting
It might seem nice to get all the rows in your table back, but often you really don't want to do that. For example, say you give out lottery checks for a living. You probably have millions of rows to deal with - thousands from me alone! If you were to type something like SELECT * FROM entries;, the entries would scroll by so fast you couldn't possibly read them all.
SQL provides a solution. Instead of showing you all the rows in the result, you can ask the database to show you only a few. You do this by adding the LIMIT clause to your SELECT:
SELECT FirstName, LastName FROM Crewmembers LIMIT 2;
| FirstName | LastName |
|---|---|
| Spock | NULL |
| Pavel | Chekov |
As you can see, you asked for two rows, and got two back.
MySQL even lets you pick where in the results you want to start from. For example, if Chekov isn't who you're looking for and you want to skip to the next page, you could type:
SELECT FirstName, LastName FROM Crewmembers LIMIT 2,2;
The first 2 is the number of the first row you want. (I know, I know, you actually want the third row. Sometimes silly programmers count from 0 rather than 1, and it's something the rest of us have to learn to deal with.) The second 2 means we want to get up to two rows.
| FirstName | LastName |
|---|---|
| Montgomery | Scott |
| James | Kirk |
If we asked for 3 rows, 4, or a million, it wouldn't matter - MySQL will just give you as many as it has, then stop.
Sorting
If you just type SELECT commands, as we have been, the rows come back in an arbitrary order - usually the order we INSERTed them into the database, beginning with the oldest. Sometimes it's nice to get the rows in a different order instead. SQL makes it easy to tell the database what you want.
Say, for example, that I want to see all the crew in my table in order of rank. While I can't ask for Admirals to be first, followed by Captains, Commanders, and so forth, I can at least put them in alphabetical order. Here's how:
SELECT Rank, FirstName, LastName FROM Crewmembers ORDER BY Rank;
| Rank | FirstName | LastName |
|---|---|---|
| Admiral | James | Kirk |
| Captain | Spock | NULL |
| Captain | Montgomery | Scott |
| Commander | Pavel | Chekov |
What if I wanted to give poor Pavel an instant promotion? No problem. I just tell the database to sort in descending order:
SELECT Rank, FirstName, LastName FROM Crewmembers ORDER BY Rank DESC;
| Rank | FirstName | LastName |
|---|---|---|
| Commander | Pavel | Chekov |
| Captain | Spock | NULL |
| Captain | Montgomery | Scott |
| Admiral | James | Kirk |
You can also sort without showing the column you're sorting on. If I typed SELECT FirstName, LastName FROM Crewmembers ORDER BY Rank;, it would work just as you might expect.
If you're adventurous, you can even sort by multiple columns at the same time. For example, if I wanted to make sure Spock always followed Scotty, I could do this (give this query a moment to sink in):
SELECT FirstName, LastName FROM Crewmembers ORDER BY Rank ASC, FirstName ASC LIMIT 3;
| FirstName | LastName |
|---|---|
| James | Kirk |
| Montgomery | Scott |
| Spock | NULL |
If you're wondering why Kirk is there, rather than Chekov, the reason is that the sort always happens before the limit. Since Kirk's rank of Admiral sorted before Chekov's rank of Commander, he got pushed to the fourth row, and wasn't lucky enough to appear in the results.
Searching
One of the most obvious uses of a database is to search for things. In SQL, most searches happen in a WHERE clause. You can think of WHERE as a way of saying, "but only if." Take this query, for example:
SELECT * FROM Crewmembers WHERE LastName = "Kirk";
What you're really saying is, "Show me all columns of all rows in the table Crewmembers, but only if the last name is Kirk."
Sure enough, MySQL happily responds with:
| ID | LastName | FirstName | Rank | SpecialTraits |
|---|---|---|---|---|
| 4 | Kirk | James | Admiral | Talks... with frequent... dramatic... pauses |
All the other combinations of the modifiers above work. Try this query:
SELECT LastName, FirstName FROM Crewmembers WHERE FirstName <> "Pavel" ORDER BY ID DESC LIMIT 2;
The only thing that should seem new to you is the <>. That means "not equal to." Some of the other comparison operators you'll see are <, >, <=, and =>.
No answer key for you this time. Try to look at the query, and the data, and figure out what the result will be. Then try it in MySQL and see if you're getting the hang of it.
Other Searches
In addition to the simple comparison operators, you can use many other operators to describe your searches. If you've worked with a search engine, you might recognize the booleans - AND and OR. They help modify a search and allow you to make more than one comparison at a time.
Say we're planning a birthday party for Scotty. We want to invite only Jim and Spock. (Poor Chekov's promotion didn't last long). Here's one way to accomplish that:
SELECT FirstName FROM Crewmembers WHERE FirstName = "James" OR FirstName = "Spock";
| FirstName |
|---|
| Spock |
| James |
Similarly, you can use AND if you have a combination of comparisons you need to make that should all be true.
SQL supports wildcards using the LIKE operator. A wildcard lets you match a pattern of characters instead of exact text. You use a percent sign to signify the letters you want to fill in, like this:
SELECT FirstName FROM Crewmembers WHERE FirstName LIKE "%a%";
This will return "Pavel" and "James", because only they have an A in their first names. If you want to leave a space for exactly one unknown character, use the underscore. For example:
SELECT FirstName FROM Crewmembers WHERE FirstName LIKE "Pa_el";
...gives you Pavel.
Adding Data with INSERT
A database wouldn't be much fun if it didn't let you easily add data to it. SQL lets you add new rows to a database with INSERT. You've already seen a few INSERT statements earlier in this tutorial. Let's look at one again:
INSERT INTO Crewmembers (LastName, FirstName, Rank, SpecialTraits)
VALUES ("Kirk", "James", "Admiral", "Talks... with frequent... dramatic... pauses");
This query tells the database, "Create a new row in the table Crewmembers. The last name should be "Kirk", the first name should be "James", the rank should be "Admiral", and the special traits are "Talks... with frequent... dramatic... pauses". MySQL will add the row and then (hopefully) tell you Query OK, 1 row affected (0.00 sec).
While there's several ways to format an INSERT, I prefer this method because it lets you add data without having to deal with pesky details like remembering what order you created the columns in and what happens if you leave a column back.
Most database packages will automatically assign a default value to columns that aren't specified. For example, because we defined the ID column of Crewmembers to be AUTO_INCREMENT when we created the table, it'll automatically get the next higher number in the table.
Why Primary Keys are Important
Oh, no!! What have we done?!?!
I just realized that when we executed that last query, we added a second, duplicate Admiral Kirk to the table. (Okay, I had this planned all along, but it sure got your heart pumping, huh?) See for yourself:
SELECT ID, FirstName, LastName FROM Crewmembers WHERE LastName = "Kirk";
You can verify that the rest of the row is identical, if you're so inclined.
So, what do we do? Luckily, the primary key, which I've been ignoring until now, ensures that every row has a unique identifier that can be quickly used to weed out duplicates. You may be thinking to yourself, "Sure, but what happens if I accidentally re-use an ID?" It's no problem, because the database will say, "Hey, stupid! That number's taken already!"
INSERT INTO Crewmembers (ID, LastName, FirstName, Rank, SpecialTraits) VALUES (4, "Kirk", "James", "Admiral", "Talks... with frequent... dramatic... pauses");
When I run that query, I get ERROR 1062 (23000): Duplicate entry '4' for key 1, because 4 is already assigned to one of the Kirks. Your wording will vary slightly, but it probably won't call you names.
Modifying Rows with UPDATE
If you need to fix a row because of a typo, outdated information, or a scatterbrained tutorial, you can use the UPDATE statement. The most basic form of UPDATE looks like the following query. However, don't type it. I'll explain why in a moment.
UPDATE Crewmembers SET FirstName = "Jean-Luc";
As I said, don't run this query. The reason you don't want to do this is that it will make the change to every row, which is almost always not want you want. If I were to run that query and then do a subsequent SELECT, I'd get something like this:
| ID | LastName | FirstName | Rank | SpecialTraits |
|---|---|---|---|---|
| 1 | NULL | Jean-Luc | Captain | Turns green when embarrased |
| 3 | Chekov | Jean-Luc | Commander | Steers wessels with ease |
| 2 | Scott | Jean-Luc | Captain | Possesses a unique understanding of 20th century computer technology |
| 4 | Kirk | Jean-Luc | Admiral | Talks... with frequent... dramatic... pauses |
| 5 | Kirk | Jean-Luc | Admiral | Talks... with frequent... dramatic... pauses |
That's not a very good result. So, if we can't do that, how do we change just the rows we want? Actually, all we need to do is use the same WHERE syntax we learned for SELECTs. Try this query out:
UPDATE Crewmembers SET SpecialTraits = "Hates Klingons" WHERE FirstName = "James" AND LastName = "Kirk";
MySQL will respond, telling you that two rows were affected. Let's take a look at how the table looks now. Write the necessary SELECT to get the following table:
| ID | FirstName | LastName | SpecialTraits |
|---|---|---|---|
| 1 | Spock | NULL | Turns green when embarrased |
| 3 | Pavel | Chekov | Steers wessels with ease |
| 4 | James | Kirk | Hates Klingons |
| 5 | James | Kirk | Hates Klingons |
| 2 | Montgomery | Scott | Possesses a unique understanding of 20th century computer technology |
As you can see, you can change multiple rows at the same time without too much trouble. However, if you're only making one change at a time, the safest way to decide what row to update is to use the primary key, like this:
UPDATE Crewmembers SET FirstName = "Jim" WHERE ID = 4;
