Databases Reference
In-Depth Information
Now let's try retrieving values in a range. This is simplest for numeric ranges, so let's
start by finding the names of all artists with an
artist_id
less than 5. To do this, type:
mysql>
SELECT artist_name FROM artist WHERE artist_id < 5;
+---------------------------+
| artist_name |
+---------------------------+
| New Order |
| Nick Cave & The Bad Seeds |
| Miles Davis |
| The Rolling Stones |
+---------------------------+
4 rows in set (0.06 sec)
For numbers, the frequently used operators are equals (
=
), greater than (
>
), less than
(
<
), less than or equal (
<=
), greater than or equal (
>=
), and not equal (
<>
or
!=
).
Consider one more example. If you want to find all albums that don't have an
album_id
of 2, you'd type:
mysql>
SELECT album_name FROM album WHERE album_id <> 2;
+------------------------------------------+
| album_name |
+------------------------------------------+
| Let Love In |
| Retro - John McCready FAN |
| Retro - Miranda Sawyer POP |
| Retro - New Order / Bobby Gillespie LIVE |
| Live Around The World |
| Power, Corruption & Lies |
| Exile On Main Street |
| Substance 1987 (Disc 1) |
| Second Coming |
| Light Years |
| Brotherhood |
+------------------------------------------+
11 rows in set (0.01 sec)
This shows us the first, third, and all subsequent albums for all artists. Note that you
can use either
<>
or
!=
for not-equal.
You can use the same operators for strings. For example, if you want to list all artists
whose name appears earlier alphabetically than (is less than)
'M'
, use:
mysql>
SELECT artist_name FROM artist WHERE artist_name < 'M';
+---------------+
| artist_name |
+---------------+
| Kylie Minogue |
+---------------+
1 row in set (0.00 sec)
Since
Kylie Minogue
begins with a letter alphabetically less than
'M'
, she's reported as
an answer; the names of our six other artists all come later in the alphabet. Note that
by default MySQL doesn't care about case; we'll discuss this in more detail later in
