• Choose a category
• All
• Classic-Fiction

Figure 2-10: This query returns companies that begin with W.

♦ The search text is still within single quotes, just like the ordinary SELECT statement.

♦ The percent sign (%) indicates a wildcard value. A search string of ‘W%’ looks for W followed by any number of characters.

♦ Any text followed by % indicates that you’re searching the beginning of the field. So, if you’re looking for people named Steve, you can write SELECT * FROM contact WHERE name LIKE ‘Steve%’;.

Searching for the ending value of a field

Likewise, you can find fields that end with a particular value. Say that you want to send an e-mail to everyone in your contact book with a .com address. This query does the trick:

SELECT *

FROM contact

WHERE

email LIKE ‘%.com‘;

Figure 2-11 shows the results of this query.

Figure 2-11: You can build a query to check the end of a field.

Searching for any text in a field

One more variant of the LIKE clause allows you to find a phrase anywhere in the field. Say that you remember somebody in your database writes books, and you decide to search for e-mail addresses containing the phrase book:

SELECT *

FROM contact

WHERE

email LIKE ‘%book%‘;

The search phrase has percent signs at the beginning and the end, so if the phrase “book” occurs anywhere in the specified field, you get a match. And what do you know? Figure 2-12 shows this query matches on the record of a humble, yet lovable author!

Searching with regular expressions

If you know how to use regular expressions, you know how great they can be when you need a more involved search. MySQL has a special form of the SELECT keyword that supports regular expressions:

Figure 2-12: This query searched for the phrase “book” anywhere in the e-mail string.

SELECT *

FROM contact

WHERE

company REGEXP ‘^.{3}$‘;

The REGEXP keyword lets you search using powerful regular expressions. (Refer to Book IV, Chapter 6 for more information on regular expressions.) This particular expression checks for a company field with exactly three letters. In this table, it returns only one value, shown in Figure 2-13.

Unfortunately, not all database programs support the REGEXP feature, but MySQL does, and it’s really powerful if you understand the (admittedly arcane) syntax of regular expressions.

Sorting your responses

You can specify the order of your query results with the ORDER BY clause. It works like this:

SELECT *

FROM contact

ORDER BY email;

Figure 2-13: Regular expressions are even more powerful than the standard LIKE clause.

The ORDER BY directive allows you to specify a field to sort by. In this case, I want the records displayed in alphabetical order by e-mail address. Figure 2-14 shows how it looks.

By default, records are sorted in ascending order. Numeric fields are sorted from smallest to largest, and text fields are sorted in standard alphabetic order.

Well, not quite standard alphabetic order . . . SQL isn’t as smart as a librarian, who has special rules about skipping “the” and so on. SQL simply looks at the ASCII values of the characters for sorting purposes.

You can also invert the order:

SELECT *

FROM contact

ORDER BY email DESC;

Inverting the order causes the records to be produced in reverse alphabetic order by e-mail address. DESC stands for descending order. ASC stands for ascending order, but because it’s the default, it isn’t usually specified.

Figure 2-14: Now, the result is sorted by e-mail address.

Editing Records

Of course, the purpose of a database is to manage data. Sometimes, you want to edit data after it’s already in the table. SQL includes handy commands for this task: UPDATE and DELETE. The UPDATE command modifies the value of an existing record, and the DELETE command removes a record altogether.

Updating a record

Say that you decide to modify Bill Gates’s address to reinforce a recent marketing triumph. The following SQL code does the trick:

UPDATE contact

SET email = ‘bill@vistaRocks.com