The SELECT Statement and Basic Querying Techniques | 143

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 inset 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 inset 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 inset 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

Download 4.24 Mb.

Share with your friends: