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

Next: The ORDER BY Clause >>
 

More Perl Articles
More By Apress Publishing

Comments On: SELECT Queries and Perl

>>> Be the FIRST to comment on this article!

Buy this book...

This article is excerpted from chapter 15 of the book Beginning Perl (Apress; ISBN: 159059391X). Check it out today at your favorite bookstore. Buy this book now.