Databases Reference
In-Depth Information
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 in set (0.01 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 LIKE "S%" AND album_name NOT LIKE "%)";
+---------------+
| album_name |
+---------------+
| Second Coming |
+---------------+
1 row in set (0.00 sec)
MySQL also supports the exclusive-OR operation through the
XOR
operator. An exclu-
sive OR evaluates as true if only one—but not both—of the expressions is true. To be
precise,
a XOR b
is 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 on to 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-
