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

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.

{mospagebreak title=The ORDER BY Clause}

The last bit of SQL that we will look at will be how to order the output. The ORDER BY clause allows us to specify on which field the output should be sorted. Let’s say we want to show all the musician information, but the output is to be sorted by name.

mysql> SELECT * FROM musicians ORDER BY name; 

 

 player_id name

phone

 

 

 

2 Geddy Lee

 555-2323 

 

5  Lenny Kravitz

 555-5656 

 

3  Marshall Mathers III 555-3434

 

6  Mike Diamond

 555-6767 

 

1  Roger Waters

555-1212 

 

4  Thom Yorke

 555-4545 

 

6 rows in set (0.00 sec)

How about all the instruments and their difficulty from easiest to hardest.

mysql> SELECT instrument, difficulty FROM instruments ORDER BY difficulty;
+————+————+
| instrument | difficulty |
+————+————+
| drums      |          0 |
| conductor  |          0 |
| keyboards  |          1 |
| bass       |          3 |
| timpani    |          4 |
| guitar     |          4 |
| trumpet    |          5 |
| piccolo    |          5 |
| vocals     |          5 |
| bugle      |          6 |
| violin     |          7 |
| harp       |          8 |
| bagpipes   |          9 |
| oboe       |          9 |
+————+————+
14 rows in set (0.00 sec)

Let’s list all the percussion instruments sorted on the name:

mysql> SELECT instrument FROM instruments
   
->  WHERE type = "percussion"
   
->  
ORDER BY instrument;
+————+
| instrument |
+————+
| drums      |
| timpani    |
+————+
3 rows in set (0.00 sec)

You may be wondering, “Can I reverse that order?” Yup, using the qualifier DESC .

mysql> SELECT instrument FROM instruments
   
->   WHERE type = "percussion"
  
->   
ORDER BY instrument DESC;

+————+
| instrument |
+————+
| timpani    |
| drums      |
+————+
3 rows in set (0.00 sec)

{mospagebreak title=More Complicated SELECTs}

Sometimes we want to select information from our database to satisfy criteria that are a bit more complicated than what we have seen so far. For instance, if we use our database for what it was really created for, finding out what instrument a particular musician plays, it is going to be more complex.

As an example, if we wanted to find out what instruments Lenny Kravitz plays, we would have to first find out what his player_id is by querying the musicians table; then using that player_id , select the inst_id s out of what_they_play ; then for each of those inst_id s, we can then get the instrument name out of the instruments table.

First, get Lenny Kravitz’s player_id :

mysql> SELECT player_id FROM musicians WHERE name = "Lenny Kravitz";
+———–+
| player_id |
+———–+
|         5 |
+———–+
1 row in set (0.00 sec)

Now, using his player_id of 5, grab the inst_id s out of what_they_play :

mysql> SELECT inst_id FROM what_they_play WHERE player_id = 5;
+———+
| inst_id |
+———+
|      11 |
|      14 |
+———+
2 rows in set (0.00 sec)

And the last step is, for each of the inst_id s, 11 and 14, query the instruments table for the instrument :

mysql> SELECT instrument FROM instruments WHERE inst_id = 11;
+————+
| instrument |
+————+
| guitar     |
+————+
1 row in set (0.02 sec)

mysql> SELECT instrument FROM instruments WHERE inst_id = 14;
+———–
+
| instrument|
+———–+
| vocals    |
+———–+
1 row in set (0.00 sec)

Whew, that seems like a lot of work just to find the instruments that Lenny Kravitz plays, especially since this database was created to do just that kind of query. There must be a better way, right? Yes, there is another way.6 These four queries can be done in one query using a table join.

Table Joins

MySQL is a relational database, so we must be able to query information out of our database using information from more than one table. By joining the tables, we can use multiple tables in a single SELECT.

Again, the best way to talk about table joins is with an example. In the complex SELECT in the previous section, we discovered the instruments that Lenny Kravitz plays. We could have stated that request like this: “Give me all the instrument names in the instruments table that match the inst_id s in the what_they_play table for the player_id in the musicians table asso ciated with the musician with the name Lenny Kravitz.”

The field instrument in the instruments table can be indicated in SQL with both the table name and the field name as instruments.instrument . Likewise, inst_id in what_they_play is what_they_play.inst_id . These fully qualified names allow us to use player_id in both the musicians table and the what_they_play table, and SQL can keep them separate because we will call them musicians.player_id and what_they_play.player_id .

Given that bit of good news, let’s translate the query we stated in English two paragraphs earlier into SQL:

mysql> SELECT instrument FROM instruments,what_they_play,musicians
   ->   WHERE instruments.inst_id = what_they_play.inst_id AND
   ->        what_they_play.player_id = musicians.player_id AND 
   
->        musicians.name = "Lenny Kravitz";

+————+
| instrument |
+————+
| guitar     |
| vocals     |
+————+
2 rows in set (0.00 sec)

The big difference in this query is that we have listed more than one table in the FROM part: instruments , what_they_play , and musicians . Then, our WHERE clause has several conditions all AND ed together. Since we are using more than one table and are comparing values in one table with the values in another, we are joining the data together. Hence the term table join.

Let’s do one more table join. Here is a query that will show all the musicians that play percussion instruments:

mysql> SELECT what_they_play.player_id FROM what_they_play,instruments 
  
->     WHERE what_they_play.inst_id = instruments.inst_id AND
   ->           instruments.type = "percussion";
+———–+
| player_id |
+———–+
|         6 |
+———–+
2 rows in set (0.00 sec)

As you can see, the SELECT combinations are endless! SQL is quite flexible—we can pull out exactly the information we need in exactly the order we want from the particular tables in which we are interested. We have only explored a few SQL commands—there are so many more to learn. Check out the documentation and Kofler’s book for more information.

Using the MySQL command line interface is enjoyable, but it is much more fun to query our database using Perl and DBI .

{mospagebreak title=Introduction to DBI}

DBI is the Database Independent module and was written by Tim Bunce. It is a collection of APIs that allow a programmer to connect to and access a database. As the name implies, the module allows us to write programs to access a database independent of the type of database. We can write a program to query an Oracle database, or a Sybase database, or MySQL, or Postgres, or ODBC, and the list goes on and on. All we need on our computers is the DBI module and the appropriate Database Driver (DBD).


Note  Each of the mentioned databases has its own dialect of SQL. Most implement the basic commands such as INSERT and SELECT in a similar way, but when it comes to the details of specific commands, they are sometimes implemented slightly differently from database to database. Keep this in mind if you are creating a Perl script that you want to port from one type of database to another—use the common form of each command even if a database has a nifty feature that you can use that is not supported elsewhere.


We are using MySQL server in this chapter, so we need to install DBI and the MySQL DBD modules.

Installing DBI and the DBD::mysql

The first step to using DBI is to install the appropriate modules. The first module we need is DBI. As this is being written, the latest version of DBI on CPAN is 1.42, but as usual this version may not be the same by the time you read this.

Follow the instructions in Chapter 13 on installing modules to install DBI . When successful, it is time to install the MySQL driver. The name of this module is DBD::mysql .

Connecting to the MySQL Database

Our first Perl program will simply connect to the MySQL database. If it works correctly, we know that DBI and DBD::mysql were installed correctly, and the real fun can then begin. Let’s look at an example ( connect.pl ):

#!/usr/bin/perl -w
# connect.pl

use strict;
use DBI;

my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", "CrimsonKing");

die "connect failed: " . DBI->errstr() unless $dbh;

print "connect successful!n";

$dbh->disconnect();

After the shebang, comment, and use strict; , we use the DBI module. Then we see a call to the DBI->connect() method. When we talked about object-oriented programming in Chapter 12, we mentioned that most modules use the method new() as their constructor. DBI , however, uses connect() . This is fine—any method name that we choose can be the constructor, and since to construct a DBI object we must connect to a database, connect() seems a logical choice.

There are three arguments to connect() : the DSN, also known as the data source name, the username of the user, and their password. In this invocation, the data source name is

"DBI:mysql:musicians_db"

All data sources will start with DBI , followed by a colon, the term mysql since we are using the MySQL server and DBD::mysql , a colon, and the database to which we are connecting, here musicians_db .


Note  Let’s say that one day we want to port our database from MySQL to some other database server such as Oracle. In this script, all we need to do is change the text mysql in the data source to oracle . Provided that DBD::oracle is installed on our machine and we don’t use any MySQL-specific queries, the script will work perfectly. Talk about portable!


The return value of DBI->connect() is an object that we can use to do things with the data base. We call this the database handler so we name it $dbh . After the call to DBI->connect() , we check the value of $dbh .

die "connect failed: " . DBI->errstr() unless $dbh;

This makes sure $dbh has a true value. If DBI->connect() fails, it returns a false value to $dbh , so we die() , complaining that something went wrong with the database connection. The function DBI->errstr() will report the error of whatever just went wrong, so as a help to the user we will include this information in the string that die() prints.

If all is well, we print a cheerful message and disconnect from the database.

print "connect successful!n";

$dbh->disconnect();

We use the disconnect() method to disconnect from the database. This is not really necessary since Perl will disconnect us when the script terminates, but it is still a polite thing to do.

Here is what happens if we execute this program:

$ perl connect.pl
connect successful!
$

Now that we can connect, it is time to execute an SQL query.

Please check back next week for the conclusion to this article.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye