The SELECT Statement and Basic Querying Techniques | 149

high priority (or precedence) means it is applied to whatever immediately follows it,
and not to the whole expression!
You can combine the
NOT
operator with LIKE. Suppose you want all albums that don’t begin with an L. To do this, type:
mysql> SELECT album_name FROM album WHERE album_name NOT LIKE "L%";
+------------------------------------------+
| album_name |
+------------------------------------------+
| Retro - John McCready FAN |
| Substance (Disc 2) |
| Retro - Miranda Sawyer POP |
| Retro - New Order / Bobby Gillespie LIVE |
| In A Silent Way |
| Power, Corruption & Lies |
| Exile On Main Street |
| Substance 1987 (Disc 1) |
| Second Coming |
| Brotherhood |
+------------------------------------------+
10 rows inset sec)
The result is all albums, except those beginning with
L
You can combine NOT LIKE
with
AND
and OR. Suppose you want albums beginning with
S
, but not those ending with a closing parenthesis,
')'
. You can do this with:
mysql> SELECT album_name FROM album WHERE
-> album_name LIKES" AND album_name NOT LIKE "%)";
+---------------+
| album_name |
+---------------+
| Second Coming |
+---------------+
1 row inset sec)
MySQL also supports the exclusive-OR operation through the
XOR
operator. An exclusive OR evaluates as true if only one—but not both—of the expressions is true. To be precise, a XOR bis equivalent to a AND (NOT b) OR (NOT a) AND b. For example,
suppose you want to find artists whose names end in es or start with The but not both. You’d need to type:
mysql> SELECT artist_name FROM artist WHERE
-> artist_name LIKE "The" XOR
-> artist_name LIKE "%es";
Empty set (0.00 sec)
There are no matching entries in the database, since both The Stone Roses and “The
Rolling Stones meet both criteria.
Before we move onto sorting, we’ll discuss syntax alternatives. If you’re familiar with a programming language such as PHP, C, Perl, or Java, you’ll be used to using
!
for
NOT
, for OR, and for AND. MySQL also supports these, and you can use them inter-

Download 4.24 Mb.

Share with your friends: