SELECT Queries and Perl (
Page 1 of 4 )
In this third part of a four-part series on Perl and DBI, you will learn about using SELECT queries to get information from databases. This article is excerpted from chapter 15 of the book Beginning Perl (Apress; ISBN: 159059391X).
The WHERE Clause
So far, all of our
SELECT
queries have shown every row in the table. Oftentimes we want only specific rows. The
WHERE
clause tells the
SELECT
query to show only the rows that match our criteria. For instance, we may want to see all the information in the
musicians
table for the musician with
player_id
having 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 with
player_id
1.
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 a
SELECT
query 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 the
WHERE
clause in the
SELECT
. See the docs for all the details.
We could go on forever with describing all the different uses of the
WHERE
clause, but we should instead mention how to sort the output.
