SELECT Queries and Perl

The WHERE Clause

So far, all of ourSELECT queries have shown every row in the table. Oftentimes we want only specific rows. TheWHEREclause tells theSELECTquery to show only the rows that match our criteria. For instance, we may want to see all the information in themusicianstable for the musician withplayer_idhaving the value 1.

mysql> SELECT * FROM musicians WHERE player_id = 1;
+-----------+--------------+----------+
| player_id | name         | phone    |
+-----------+--------------+----------+
|         1 | Roger Waters | 555-1212 |
+-----------+--------------+----------+
1 row in set (0.00 sec)

Or, how about selecting only the name of the musician withplayer_id1.

mysql> SELECT name FROM musicians WHERE player_id = 1;
+--------------+
| name         |
+--------------+
| Roger Waters |
+--------------+
1 row in set (0.00 sec)

Here’s how to grab Thom Yorke’s phone number:

mysql> SELECT phone FROM musicians WHERE name = "Thom Yorke";
+----------+
| phone    |
+----------+
| 555-4545 |
+----------+
1 row in set (0.00 sec)

Maybe we are interested in all instruments with difficulties of 8 or higher:

mysql> SELECT instrument FROM instruments WHERE difficulty >= 8;
+------------+
| instrument |
+------------+
| bagpipes   |
| oboe       |
| harp       |
+------------+
3 rows in set (0.00 sec)

Or the easiest instruments:

mysql> SELECT instrument FROM instruments WHERE difficulty <= 2;
+--------------+
| instrument   |
+--------------+
| keyboards    |
| drums        |
| conductor    |
+--------------+
3 rows in set (0.00 sec)

More than one condition can be combined. Here is aSELECTquery that returns all the percussion instruments with a difficulty less than or equal to 3:

mysql> SELECT instrument FROM instruments

   ->   WHERE type = "percussion" AND difficulty <= 3;
+------------+
| instrument |
+------------+
| drums      |
+------------+
2 rows in set (0.00 sec)

Note  There are many different ways to use theWHERE clause in theSELECT. See the docs for all the details.

We could go on forever with describing all the different uses of theWHEREclause, but we should instead mention how to sort the output.