Creating More Powerful Queries
Creating More Powerful Queries
So far, the tables you've created haven't really been any more powerful than HTML tables, and they're a lot more trouble. The excitement of databases comes when you use the information to solve problems. Ironically, the most important part of database work isn't usually getting the data, but filtering the data in order to solve some sort of problem. You might want to get a listing of all heroes in your database who's last name begins with an "E," or perhaps somebody parked a Yak Dirigible in your parking space and you need to know who the driver is. You may also want your list sorted by special power, or you may only want a list of vehicles. All these (admittedly contrived) examples involve grabbing a subset of the original data. The workhorse of SQL is the SELECT statement. You've seen the simplest form of this command used to get all the data in a table, like this:
SELECT * FROM hero;
Figure 7.19 shows this form of the SELECT statement operating on the hero table.
Figure 7.19: The SELECT query is in the top right section, and the results are shown underneath.
| TRICK? |
SQLyog is a wonderful tool for experimenting with SELECT statements because you can write the actual SQL by hand, and then you can see the results immediately in a very clean format. To use SQLyog in this way, type in SQL code in the SQL editor, then press Shift+F5. If you don't want to use SQLyog, you can do the same experiments directly in MySQL. It will still work, but the results will be formatted as text, and not always as easy to see. |
The SELECT statement is extremely powerful because it can be used to grab a subset of a data set that can return only the requested fields and records. This process of asking questions of the database is commonly called a query.
Limiting Columns
There are many times when you might not want all of the fields (or columns) in a table. For example, you might just want a list of the name and weapon of everyone on your list. You can specify this by using the following SELECT statement (illustrated in Figure 7.20).
Figure 7.20: This Query returns only the names and weapons.
SELECT name, weapon FROM hero;
This may seem like a silly capability for such a simple database as the hero list, but you'll often run into extremely complicated tables with many fields, and you'll need to filter only a few fields. For example, I use a database to track the students I advise. Each student's information contains lots of data, but I might just want a list of names and e-mail addresses. The ability to isolate only the fields I need is one way to get useful information from a database.
The results of a query look a lot like a new table. You can think of a query result as a temporary table.
Limiting Rows with the WHERE Clause
In addition to limiting the columns returned in a query, you are often interested in limiting the number of rows. For example, you might run across an evil villain who can only be defeated by a laser pointer. The query shown in Figure 7.21 illustrates a query that solves exactly this dilemma.
Figure 7.21: If you know how to set up the query, you can get very specific results. In this case, the query selects only heroes with a laser pointer.
SELECT * FROM hero WHERE weapon = 'Laser Pointer';
returns only the rows matching a specific condition.
Adding a Condition with a WHERE Clause
You can add a WHERE statement to a query to specify which row or rows you want to see. This clause allows you to specify a condition. The database manager will check every record in the table. If the condition is true for that record, it will be included in the result set. The conditions in a WHERE clause are similar to those in PHP code, but they are not exactly the same. In SQL, the single equal sign (=) is used for equality. Also, text elements are encased in single quotes. ('). You can also use <, >, and <= or >= and != conditions to limit your search.
| TRICK? |
The usage of comparison operators is pretty easy to understand for numeric data, such as integers and real numbers. It's not quite so obvious how a language will treat text comparisons. SQL has developed some standard rules, but each implementation might be somewhat different. SQL generally works in a case-insensitive way, so "Yak-Bot" would match "yak-bot" or "yAK-bOT." Also, the < and > operators refer to alphabetic order, so SELECT * FROM hero WHERE name < 'D'; will select all the records where the hero's name starts with "A," "B," or "C." |
Using the LIKE Clause for Partial Matches
Often you will not know the exact value of a field you are trying to match. The LIKE clause allows you to specify partial matches. For example, you might wish to know which heroes have some sort of super power. This query
SELECT * FROM hero WHERE power LIKE 'Super%';
will return back each hero whose power begins with the value "Super." The percent sign (%) can be used as a wild card to indicate any number of characters. You can use a variation of the LIKE clause to find information about all heroes with a transportation scheme that starts with the letter "B."
SELECT name, transportation FROM hero WHERE transportation LIKE 'B%';
You can also use the underscore character (_) to specify one character.
| TRICK? |
The simple wildcard character support in SQL is sufficient for many purposes. If you like regular expressions, you can use the REGEXP clause to specify whether a field matches a regular expression. This is a very powerful tool, but it is an extension to the SQL standard. It works fine in MySQL, but it is not supported in all SQL databases. |
Generating Multiple Conditions
You can combine conditions with AND, OR, and NOT keywords for more complex expressions. For example,
SELECT *
FROM hero
WHERE transportation LIKE 'B%'
AND power LIKE '%super%';
selects those heroes whose transportation starts with "B" and who have a power with "super" in its name.
This capability to create compound expressions will be very useful as you build more complex databases with multiple tables.
Using the ORDER BY Clause to Sort Results
One more nifty feature of the SELECT statement is the ability to sort the results by any field. Figures 7.22 and 7.23 illustrate how the ORDER BY clause can determine how tables are sorted.
Figure 7.22: This query shows the entire database sorted by the weapon name.
Figure 7.23: This query sorts by the power in descending (reverse alphabetical) order.
The ORDER BY clause allows you to determine how the data is sorted. You can specify any field you wish as the sorting field. As you can see in Figure 7.23, the DESC clause specifies that data should be sorted in descending order.
Changing the Data with the UPDATE Statement
You can also use SQL to modify the data in a database. The key to this behavior is the UPDATE statement. An example will help it make sense.
UPDATE hero SET power = 'Super Electric Toe' WHERE name = 'Lightning Guardian';
This code upgrades Lightning Guardian's power to the Super Electric Toe (which is presumably a lot better than the ordinary Electric Toe).
Generally, you will want to update only once record at a time. You can use a WHERE clause to select which record in the table will be updated.
