The SELECT Statement and Basic Querying Techniques | 145

Combining conditions with AND, OR, NOT, and XOR
So far, we’ve used the
WHERE
clause to test one condition, returning all rows that meet it. You can combine two or more conditions using the Boolean operators AND, OR,
NOT
,
and
XOR
Let’s start with an example. Suppose you want to find all albums with a title that begins with a character greater than
C
but less than M. This is straightforward with the
AND
operator:
mysql> SELECT album_name FROM album WHERE
-> album_name > "C" AND album_name < "M";
+-----------------------+
| album_name |
+-----------------------+
| Let Love In |
| Live Around The World |
| In A Silent Way |
| Exile On Main Street |
| Light Years |
+-----------------------+
5 rows inset sec)
The
AND
operation in the
WHERE
clause restricts the results to those rows that meet both conditions.
The
OR
operator is used to find rows that meet at least one of several conditions. To illustrate, imagine you want a list of all albums that have a title beginning with LS, or
P
. You can do this with two
OR
and three
LIKE
clauses:
mysql> SELECT album_name FROM album WHERE
-> album_name LIKE "L" OR
-> album_name LIKES" OR
-> album_name LIKE "P%";
+--------------------------+
| album_name |
+--------------------------+
| Let Love In |
| Substance (Disc 2) |
| Live Around The World |
| Power, Corruption & Lies |
| Substance 1987 (Disc 1) |
| Second Coming |
| Light Years |
+--------------------------+
7 rows inset sec)
The
OR
operations in the
WHERE
clause restrict the answers to those that meet any of the three conditions. As an aside, it’s particularly obvious in this example that the results are reported without sorting in this case, they’re reported in the order they were added to the database. We’ll return to sorting output later in ORDER BY Clauses.”

Download 4.24 Mb.

Share with your friends: