Home arrow Perl Programming arrow SELECT Queries and Perl

SELECT Queries and Perl

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

TABLE OF CONTENTS:
  1. SELECT Queries and Perl
  2. The ORDER BY Clause
  3. More Complicated SELECTs
  4. Introduction to DBI
By: Apress Publishing
Rating: starstarstarstarstar / 5
March 27, 2008

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Perl Programming Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PERL PROGRAMMING ARTICLES

- Perl Turns 25
- Lists and Arguments in Perl
- Variables and Arguments in Perl
- Understanding Scope and Packages in Perl
- Arguments and Return Values in Perl
- Invoking Perl Subroutines and Functions
- Subroutines and Functions in Perl
- Perl Basics: Writing and Debugging Programs
- Structure and Statements in Perl
- First Steps in Perl
- Completing Regular Expression Basics
- Modifiers, Boundaries, and Regular Expressio...
- Quantifiers and Other Regular Expression Bas...
- Parsing and Regular Expression Basics
- Hash Functions

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: