Speaking SQL

MySQL comes with a client program named, oddly enough, mysql, which allows us to enter SQL queries directly to the database server, which is presumably running at all times. We enter the database with:

[1016] ~% mysql test
Welcome to the mysql monitor. Commands end with ; or \g.
Type 'help' for help.
mysql>

Just as file systems store files within subdirectories within directories, relational databases store tables inside of databases inside of the overall structure. Thus, when we enter MySQL, we need to specify the name of the database we would like to use. In example above, we specified the test database, to which all users have access without needing to go through the standard procedure of entering a user name and password. While user names and passwords for relational databases can be the same as those for the user's account on the system, they do not need to be. Indeed, for the sake of system security, you should make them distinct from your regular system passwords.

Generally speaking, it is also a good idea to create one or more databases exclusively for CGI programs, in order to avoid giving programs complete access to all databases on the system. The nature of CGI programming is such that users might be able to read the user name and password from the program's source code, thus giving them access to whatever tables are in a given database. However, in the interest of time and space, I encourage you to read the MySQL documentation, which describes how to set user permissions for various databases on the system In the meantime, we will use the test database, to which all users have access, for our examples

To create our telephone directory table, we type:

mysql> create table phone_book (name char(255),
        telephone char(255));

Whitespace is unimportant in SQL queries. In the above example, I pressed enter between the end of the first line and the go statement on the second line. As you might expect, the go command tells a database client to send the query to the database server, where it is evaluated and executed. Alternatively, we can use a semicolon at the end of our query, which will preclude the need for go.

The server responds to our query by giving us some statistics:

Query OK, 0 rows affected (0.27 sec)

In other words, creating a table took .27 seconds and did not affect any existing rows.

You can quit mysql by typing quit at the mysql> prompt.

The MySQL programmatic interface from Perl works in much the same way as the command-line program, except that it uses Perl 5 objects. The basic idea is straightforward; we create an instance of a MySQL object, and then use that object to get through the process of logging in, sending queries, and interpreting the results.

Listing 1 contains a functional program that can query our phone_book table and return the results. More importantly, though, that program is the skeleton for every program we write using MySQL. While the syntax might be slightly different for Sybase and other databases, the general idea is the same—connect to the database server, choose a database, send a query in SQL and iterate through whatever results are returned.

First, we connect to the database server using Unix sockets, in part because MySQL enables those sockets by default, which makes for an easier explanation in a short column such as this one. You can, of course, also connect to a database server running elsewhere on the network, just as a web browser can connect to a web server across a network.

Once we are connected to the MySQL server, we use the query method to enter our SQL query. Just as connecting to the database returns the database handle $dbh, sending an SQL query returns the statement handle $sth. And just as we need to use $dbh in order to send a statement, we need to use $sth in order to retrieve results. In this particular statement, we have asked to see both of the table's columns, as well as all of the rows in the table. However, we could restrict our query with a where clause, as described earlier, which would return a subset of the table's rows. We could also ask for a subset of the table's columns, such that only the name or the telephone number would be returned.

Results are retrieved by iterating over the rows that were returned from the server. If no rows match our query, the iteration is not performed; if 100 rows match our query, it is performed 100 times. If we are interested in maximizing the efficiency of our programs that handle SQL queries, it is in our interest to construct queries that return only those rows that most interest us, since iterating through a large number of rows can be quite inefficient and time-consuming.

If I run the program in Listing 1 (named sql-test.pl on my system) from the command line, I get:

[1031] ~/Text/LJ% ./sql-test.pl
Iris    04-999-8888
Reuven  04-824-2265
Andy    02-123-4567
Gil     04-999-8888

We can, of course, use the above skeleton program to insert rows, create tables and do more complicated things, such as joining tables together (which is, to a large degree, the magic behind SQL) and order results in ascending or descending order. If we were to keep the area code in a different column from the telephone number itself, we could refine our searches even further, asking for all people within a given area code whose first name is Iris, for example.