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:
 ~% 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:
 ~/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.
|Using Salt Stack and Vagrant for Drupal Development||May 20, 2013|
|Making Linux and Android Get Along (It's Not as Hard as It Sounds)||May 16, 2013|
|Drupal Is a Framework: Why Everyone Needs to Understand This||May 15, 2013|
|Home, My Backup Data Center||May 13, 2013|
|Non-Linux FOSS: Seashore||May 10, 2013|
|Trying to Tame the Tablet||May 08, 2013|
- Making Linux and Android Get Along (It's Not as Hard as It Sounds)
- Using Salt Stack and Vagrant for Drupal Development
- New Products
- Validate an E-Mail Address with PHP, the Right Way
- Drupal Is a Framework: Why Everyone Needs to Understand This
- A Topic for Discussion - Open Source Feature-Richness?
- Home, My Backup Data Center
- New Products
- RSS Feeds
- Readers' Choice Awards
- Automatically updating Guest Additions
45 min 44 sec ago
- I like your topic on android
1 hour 32 min ago
- Reply to comment | Linux Journal
1 hour 53 min ago
- This is the easiest tutorial
8 hours 7 min ago
- Ahh, the Koolaid.
13 hours 46 min ago
- git-annex assistant
19 hours 46 min ago
- direct cable connection
20 hours 8 min ago
- Agreed on AirDroid. With my
20 hours 18 min ago
- I just learned this
20 hours 22 min ago
20 hours 53 min ago
Enter to Win an Adafruit Prototyping Pi Plate Kit for Raspberry Pi
It's Raspberry Pi month at Linux Journal. Each week in May, Adafruit will be giving away a Pi-related prize to a lucky, randomly drawn LJ reader. Winners will be announced weekly.
Fill out the fields below to enter to win this week's prize-- a Prototyping Pi Plate Kit for Raspberry Pi.
Congratulations to our winners so far:
- 5-8-13, Pi Starter Pack: Jack Davis
- 5-15-13, Pi Model B 512MB RAM: Patrick Dunn
- Next winner announced on 5-21-13!
Free Webinar: Linux Backup and Recovery
Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.
In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.