HTML, XHTML and CSS All-In-One for Dummies - Andy Harris [256]
If you have a particular table currently active, the SQL dialog box shows you the fields of the current table, which can be handy when you write SQL queries.
Try the SELECT * FROM contact; code in the SQL dialog box, and you see the results shown in Figure 2-6.
Selecting only a few fields
As databases get more complex, you’ll often find that you don’t want everything. Sometimes, you only want to see a few fields at a time. You can replace the * characters with field names. For example, if you want to see only the names and e-mail addresses, use this variation of the SELECT statement:
SELECT name, email FROM contact;
Figure 2-6: The standard SELECT statement returns the entire table.
Only the columns you specify appear, as you can see in Figure 2-7.
Here’s another really nice trick you can do with fields. You can give each column a new virtual field name:
SELECT
name as ‘Person‘,
email as ‘Address‘
FROM contact;
This code also selects only two columns, but this time, it attaches the special labels Person and Address to the columns. You can see this result in Figure 2-8.
The capability to add a virtual name for each column doesn’t seem like a big deal now, but it becomes handy when your database contains multiple tables. For example, you may have a table named pet and another table named owner that both have a name field. The virtual title feature helps keep you (and your users) from being confused.
Figure 2-7: Now, the result is only two columns wide.
Figure 2-8: You can create virtual titles for your columns.
Selecting a subset of records
One of the most important jobs in data work is returning a smaller set of the database that meets some kind of criterion. For example, what if you want to dash off a quick e-mail to Steve Jobs? Use this query:
SELECT *
FROM contact
WHERE
name = ‘Steve Jobs‘;
This query has a few key features:
♦ It selects all fields. This query selects all the fields (for now).
♦ A WHERE clause appears. The WHERE clause allows you to specify a condition.
♦ It has a condition. SQL supports conditions, much like ordinary programming languages. MySQL returns only the records that match this condition.
♦ The condition begins with a field name. SQL conditions usually compare a field to a value (or to another field).
♦ Conditions use single equal signs. You can easily get confused on this detail because SQL uses the single equal sign (=) in conditions, whereas most programming languages use double equals (==) for the same purpose.
♦ All text values must be within single quotes. I’m looking for an exact match on the text string ‘Steve Jobs’.
♦ It assumes that searches are case-sensitive. Different databases have different behavior when it comes to case-sensitivity in SELECT statements, but you’re safest assuming that case matters.
Figure 2-9 shows the result of this query.
SQL is pretty picky about the entire text string. The following query doesn’t return any results in the contact database:
SELECT *
FROM contact
WHERE
name = ‘Steve‘;
Figure 2-9: Here’s a query that returns the result of a search.
The contact table doesn’t have any records with a name field containing Steve (unless you added some records when I wasn’t looking). Steve Jobs is not the same as Steve, so this query returns no results.
Searching with partial information
Of course, sometimes all you have is partial information. Take a look at the following variation to see how it works:
SELECT *
FROM contact
WHERE
company LIKE ‘W%‘;
This query looks at the company field and returns any records with a company field beginning with W. Figure 2-10 shows how it works.
The LIKE clause is pretty straightforward:
♦ The keyword LIKE indicates a partial match is coming. It’s still the SELECT statement, but now