-> (album_name LIKE "L" OR album_name LIKES) AND

-> album_name LIKE "%g";
+---------------+
| album_name |
+---------------+
| Second Coming |
+---------------+
1 row inset sec)
Both examples with parentheses are much easier to understand. We recommend that you use parentheses whenever there’s a chance the intention could be misinterpreted;
there’s no good reason to rely on MySQL’s implicit evaluation order.
The unary
NOT
operator negates a Boolean statement. Suppose you want a list of all albums except the ones having an album_id of 1 or 3. You’d write the query:
mysql> SELECT * FROM album WHERE NOT (album_id = 1 OR album_id = 3);
+-----------+----------+------------------------------------------+
| artist_id | album_id | album_name |
+-----------+----------+------------------------------------------+
| 1 | 2 | Substance (Disc 2) |
| 1 | 4 | Retro - New Order / Bobby Gillespie LIVE |
| 3 | 2 | In A Silent Way |
| 1 | 5 | Power, Corruption & Lies |
| 1 | 6 | Substance 1987 (Disc 1) |
| 1 | 7 | Brotherhood |
+-----------+----------+------------------------------------------+
6 rows inset sec)
The expression in the parentheses says we want:
(album_id = 1 OR album_id = and the
NOT
operation negates it so we get everything but those that meet the condition in the parentheses. There are several other ways you can write a
WHERE
clause with the same function, and it really doesn’t matter which you choose. For example the following three expressions have the same effect:
WHERE NOT (album_id = 1) AND NOT (album_id = WHERE album_id != 1 AND album_id != WHERE album_id != 1 AND NOT (album_id = Consider another example using
NOT
and parentheses. Suppose you want to get a list of all albums with an album_id greater than 2, but not those numbered 4 or 6:
mysql> SELECT * FROM album WHERE album_id > 2

Download 4.24 Mb.

Share with your friends: