The MySQL Database Server

The MySQL Database Server

Databases are everywhere these days. It's all part of the increasing role of information in society. There is more information, and more need to organize it. Even your cell phone has a database.

MySQL is a relational database management system ( RDBMS ) that understands Structured Query Language ( SQL ) (pronounced either by saying the letters individually or the word "sequel"). The ideas of tracking relationships within a database and of being able to query it for information have been around for 20 years and are fundamental precepts to the way almost all corporate databases run today.

MySQL is a powerful, stable, open -source RDBMS that you can use without cost, but you can also purchase commercial support for it from the developers at MySQL (www.mysql.com).

If a database engine (that's another name for an RDBMS) understands SQL, then other applications can talk to it in a standardized fashion. Software written to communicate with one SQL database can usually communicate with any other with only minimal changes.

An RDBMS can contain many databases, such as one for "purchases" and one for "sales." Each database in turn may have many tables, identifying "customers," "invoices," and "invoice line items." A table is a collection of data entries that all have exactly the same list of headings, called columns or fields . Each table can have many columns, such as "ID number," "name," "address," and "phone."

A database is essentially a tool for organizing information. Relational databases are databases that can contain several tables of information, which can be linked to each other. A database of purchase histories might contain one table that lists customers (the "customer table"), another that lists invoices (the "invoice table"), and a third that lists invoice line items (the "invoice line-items table"). An entry in the invoice table might have only the invoice number, customer number, and invoice date. Thus, an entry in the invoice table is not actually a complete invoice; rather, it holds only the information that is unique to one invoice. This avoids the necessity of keeping a copy of the customer address for every invoice. The customer's ID number is in the invoice table and is used to look up the customer address for each invoice in the customer table. This connection between the tables is called a relationship .

To produce a complete invoice, the system would need to pull a record from the invoice table and then pull information from the two related tables (the customer table and the invoice line-items table). This is possible because each record in the invoice table has a customer ID number (which is used to find a customer) and an invoice ID. Each record in the invoice line-items table has an invoice ID, so by finding all the records in the invoice line-items table with a particular invoice ID, the database can find all the line items for one invoice. Figure 14.17 is a diagram showing the relationships between the tables in a sample database.

Figure 14.17. Diagram showing the relationships between tables in a relational database.

Learning how to use a database, totally apart from setting one up, is quite a project. But you have to have a database in order to start learning, and installing an industrial-strength database on Mac OS X is actually very easy.

Installing MySQL

You can install MySQL from source code using Fink or the other methods described in Chapter 13, "Installing Software from Source Code." With Fink, you can easily remove the entire installation. However, an even easier way to install MySQL is to download the MySQL installer package from the MySQL Web site.

To install MySQL using precompiled binaries:

1.	Download the installer-package disk image from MySQL. The disk image is available from http://dev.mysql.com/downloads/mysql/. Look for the "Mac OS X downloads" and pick the appropriate version. Figure 14.18 shows a partial screen shot of the MySQL download page. Figure 14.18. Partial screen shot of the MySQL download page showing various alternatives for Mac OS X.
2.	Double-click the installer package and follow the instructions to install the MySQL database server and many utility programs. The main installation location is /usr/local/mysql- VERSION For example: /usr/local/ mysql-standard-4.1.13- apple-darwin6.8-powerpc A symbolic link, /usr/local/mysql , is created pointing to the new location. All of the installed files will be in /usr/local/mysql . Initially there are two databases: mysql and test . The mysql database is where MySQL stores information about itselfusers, passwords, and so on.
3.	Double-click the MySQLStartupItem installer package and follow the instructions. This installs a StartupItem ( /Library/StartupItems/MySQLCOM ) that provides the MySQL service. An entry is also created in /etc/hostconfig .
4.	Check the firewall. MySQL listens on port 3306. If you are running firewall software, be sure to configure it to allow access to this port if you want other machines to be able to connect to your MySQL server.
5.	Set the MySQL root password. The MySQL database gets installed with no password protection. You should set a password right away. Immediately proceed to the next task, "To set the MySQL root password."

Tips

• Besides man pages for all the MySQL utility programs, the MySQL user 's manual includes a tutorial, performance-tuning tips, and detailed reference material on all aspects of MySQL. The manual is available online at http://dev.mysql.com/doc/ and may be browsed and searched online, as well as downloaded and stored on your local disk in HTML and PDF formats. For books about MySQL, see http://dev.mysql.com/books/.

• The installer puts over 50 programs in /usr/local/mysql/bin/ and the commonly used ones have built-in help available if run with the -help optionfor example:

  mysqldump -help

  See Table 14.2 for a list of the commonly used MySQL commands.

Table 14.2. Commonly Used MySQL Commands

(All installed in /usr/local/mysql/bin .)
C OMMAND	D ESCRIPTION
mysql	Command-line client program for issuing SQL commands and seeing the results.
mysqladmin	For changing the root password, shutting down the database server, reloading access controls, and so on.
mysqldump	Dumping the contents of an entire database or one or more tables as an SQL text file. Very useful for making backups . Output can be piped to the mysql utility to create a copy of a database.
mysqlimport	Reads delimited text files into a database.

GUI Tools for MySQL Administration

There are a number of graphical tools for administering MySQL. Webmin (www.webmin.com) has a MySQL module that works nicely . Another Web-based tool, phpMyAdmin, is available in 50 languages (www. phpmyadmin .net).

The folks at MySQL AB (the makers of MySQL) have released Mac OS X versions of their MySQL Administrator (http://dev.mysql.com/downloads/administrator) and MySQL Query Browser (http://dev.mysql.com/downloads/query-browser). These are proper Mac applications that allow you to administer MySQL. They are very fast and offer nice built-in tools for creating and saving SQL queries, comparing results from two queries, and so on. As of this writing, these tools are available for Mac OS X 10.3, but not yet for 10.4.

Configuring and starting MySQL

Configuring MySQL involves several steps: creating a root password for MySQL, starting the mysqld program itself, learning how to use the mysql command-line tool, creating one or more MySQL users, creating a MySQL database, setting the access controls for the new database, and creating tables in the new database. Once all this is done, you can insert and retrieve data from the tables in the new database. Though this is more detail than we've gone into for most tasks in this book, we feel that it's appropriate because database mastery is a key asset for knowledgeable Unix users. See also the sidebar "GUI Tools for MySQL Administration."

MySQL has its own list of users, which are totally separate from the user accounts on your system. When you install MySQL, it has one user already installedroot. This can be a bit confusingthe MySQL root account is totally different from the Unix root account. It canand shouldhave a different password and is used only for managing MySQL.

The MySQL root account starts off with no password, so the first thing you should do is set a password for the MySQL root account.

For the following tasks, we are assuming you have added the directory /usr/local/mysql/bin to your PATH . If you haven't done that, do it now (see Chapter 7, "Configuring Your Environment with Unix," for instructions).

To set the MySQL root password:

1.	Become root: sudo -s Enter your Unix password if prompted. You need to be the Unix root user to start MySQL.
2.	Start the MySQL database server. If you installed the StartupItem package in the previous task, then you can start the server with SystemStarter start MySQL See Figure 14.19 . Figure 14.19. Starting the mysqld daemon using the StartupItem script. g4-cube:~ root# SystemStarter start MySQL Starting MySQL database server g4-cube:~ root# That command runs the StartupItem script /Library/StartupItems/MySQLCOM , which in turn actually runs another script, which you can run directly if you need to: /usr/local/mysql/support-files/mysql. server start That script runs the database server daemon: mysqld . The mysqld daemon must be running in order to set a password for the MySQL root account.
3.	mysqladmin -u root password ' mypassword ' That sets the MySQL root password to mypassword. Note that the new password is enclosed in single quotes. For example, to set the MySQL root password to Gr56%kQ, the command is mysqladmin -u root password 'Gr56%kQ From now on, when you use the mysqladmin command, you need to add the -p option, which will tell mysqladmin to ask you for a password, and then enter the password you just created. For example, mysqladmin -u root -p status will prompt for a password, and when you enter it, you will get status information about the MySQL database engine.
4.	Clear the password out of your shell's command history. Because your command history (see Chapter 2) is written to disk, when your shell exits, you want to ensure that the password you just typed doesn't get written where someone might be able to see it. If you are using the default shell ( bash ): unset HISTFILE If you are using the tcsh shell: history -c
5.	exit This exits the root shell you started in step 1, and you should be back to your regular shell prompt.

To start MySQL from the command line:

• sudo SystemStarter start MySQL
  
  or, if you have not installed the StartupItem, then
  
  

   sudo /usr/local/mysql/support-files/  mysql.server start 

  
  

To shut down MySQL from the command line:

• sudo SystemStarter stop MySQL
  
  or, if you have not installed the StartupItem, then
  
  

   sudo /usr/local/mysql/support-files/  mysql.server stop 

  
  You can also use
  
  mysqladmin -u root -p shutdown
  
  and then enter the MySQL root password when prompted. Notice that this method uses the MySQL root password, not your password.
  
  

MySQL has dozens of available options. You put options into the MySQL options file, /etc/my.cnf . (The options file is also called the configuration file .) If that file does not exist, then reasonable default values are used.

Four sample configuration files are provided in /usr/local/mysql/support-files/ , each corresponding to how heavily used your MySQL server will be: my-huge.cnf , my-large.cnf , my-medium.cnf , and my-small.cnf .

The files contain some documentation about the various options, and the MySQL manual discusses options files in section 4.3.2 (see http://dev.mysql.com/doc/mysql).

To install a MySQL options/configuration file:

1.	Choose one of the sample options files from /usr/local/mysql/share/mysql .
2.	Make a backup copy of /etc/my.cnf . For example: cp /etc/my.cnf /etc/my.cnf.20051221
3.	Copy the file to /etc/my.cnf . For example: sudo cp /usr/local/mysql/share/mysql/ my-medium.cnf /etc/my.cnf
4.	Edit the file to adjust the options to your liking. You need to be root in order to edit the file.
5.	Start or restart MySQL. (See tasks above.)

Connecting to MySQL

The main way to manage MySQL is to connect to the mysqld daemon using the mysql command-line utility. (See also the sidebar "GUI Tools for MySQL Administration.") Most of the commands you use inside the mysql utility are SQL commands, called statements . The MySQL manual has extensive coverage of SQL in section 13, "SQL Statement Syntax," as well as a complete tutorial in section 3, "MySQL Tutorial," which we strongly recommend that you read and perform.

In the following tasks, we use two databases that are created when you install MySQL. One is called "mysql"it's the "master database" containing information about MySQL itself, as well as all the databases, users, and access-control information. The other is called "test"; it's an empty database used foryou guessed ittesting.

When you connect to MySQL, you must do so as a MySQL user. Until you create more MySQL users, the only one that exists is the MySQL root user, so we use that user in this task. You do not need to be root to perform the following task. You are logging in to the MySQL server as the "MySQL" root user, which is not the same as the Unix root user (it just has the same name in order to confuse you).

To use the mysql utility to connect to the mysqld daemon:

1.	mysql -u root -p
2.	Enter the MySQL root password. You are now connected to mysqld . Figure 14.20 shows the command line for connecting, and then the output and prompt from the mysql utility. Figure 14.20. Connecting to mysqld using the mysql command-line utility. localhost:~ vanilla% mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.13-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> The mysql utility is an interactive program, like the ftp command. mysql provides its own prompt, where you type commands to it. You can think of it as a shell program just for MySQL.
3.	show databases; This shows a list of all the databases that mysqld is managing ( Figure 14.21 ). Figure 14.21. Using the show databases command in the mysql utility to display a list of all MySQL databases. mysql> show databases; +-----+ Database +-----+ mysql test +-----+ 2 rows in set (0.01 sec) mysql> You can enter commands in the mysql utility (called SQL statements ) on multiple lines. That is, you can press in the middle of an SQL statement because the SQL statement doesn't actually end (and get executed) until you enter one of the following and press : ; \g \G The first two have the same meaning. The third one, \G , does a nice job of formatting output that would be too wide for your screen when using either of the first two forms.
4.	use mysql; This tells the mysql utility that you want to communicate with the MySQL database ( Figure 14.22 ). Figure 14.22. Telling the mysql utility which database you want to communicate with. mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql>
5.	show tables; This command lists all the tables in the currently selected databasein this case, the MySQL database ( Figure 14.23 ). Figure 14.23. Using the show tables command to see all the tables in the currently selected database. mysql> show tables; +---------------------------+ Tables_in_mysql +---------------------------+ columns_priv db func help_category help_keyword help_relation help_topic host tables_priv time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type user +---------------------------+ 15 rows in set (0.01 sec) mysql>
6.	DESCRIBE user; This command (which need not be capitalized) describes all the fields (or columns) in the user table. Figure 14.24 shows the output. The output shows the column (Field) name, the data type for the column, whether the column can contain NULL (empty) values, whether the column is indexed (in which case the Key column has a value in it), and finally the column's default value. In this example 21 fields have a default value of N , 7 have no default, and 3 have a default value of . Figure 14.24. Using the DESCRIBE command to see the descriptions of all the fields in the user table. mysql> DESCRIBE user; +-----------------------+-----------------------------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-----------------------+-----------------------------------+------+-----+---------+-------+ Host varchar(60) PRI User varchar(16) PRI Password varchar(41) Select_priv enum('N','Y') N Insert_priv enum('N','Y') N Update_priv enum('N','Y') N Delete_priv enum('N','Y') N Create_priv enum('N','Y') N Drop_priv enum('N','Y') N Reload_priv enum('N','Y') N Shutdown_priv enum('N','Y') N Process_priv enum('N','Y') N File_priv enum('N','Y') N Grant_priv enum('N','Y') N References_priv enum('N','Y') N Index_priv enum('N','Y') N Alter_priv enum('N','Y') N Show_db_priv enum('N','Y') N Super_priv enum('N','Y') N Create_tmp_table_priv enum('N','Y') N Lock_tables_priv enum('N','Y') N Execute_priv enum('N','Y') N Repl_slave_priv enum('N','Y') N Repl_client_priv enum('N','Y') N ssl_type enum('','ANY','X509','SPECIFIED') ssl_cipher blob x509_issuer blob x509_subject blob max_questions int(11) unsigned 0 max_updates int(11) unsigned 0 max_connections int(11) unsigned 0 +-----------------------+-----------------------------------+------+-----+---------+-------+ 31 rows in set (0.03 sec) mysql>
7.	quit This exits the mysql utility, taking you back to your shell prompt. Notice that you did not need a semicolon at the end of that command.

Tips

• Commands, table names, and field (column) names are not case sensitive, so describe and DESCRIBE mean the same thing. We show SQL commands in uppercase for clarity only.

• You can specify which database to connect to on the command line when you run the mysql utilityfor example,

  mysql -u root -p test

  starts the mysql program and connects to the test database right away.

• The mysql tool has a command history much like your Unix shell's. You can use the up arrow and down arrow keys to scroll through command lines you have entered.

Creating a new database

When you create a new database in MySQL, it has no access controls whatsoever, unless you have put them in place before creating the database. So the process we show you for creating a new database in MySQL involves first setting up the access controls and then creating the empty database. Please note that section 5 of the MySQL user's manual goes into great detail about MySQL access controls. Section 5.6 in particular covers the MySQL access-control system.

To set up the access controls, connect to the MySQL master database, mysql. That is the database where MySQL keeps information about itself and any databases you create. Once again, we are assuming you have added /usr/local/mysql/bin to your PATH .

At the end of this chapter, we show you a simple Perl script that connects to MySQL. The script assumes you have created the sample database and user described below.

To create a new database in MySQL:

1.	mysqladmin -u root -p create dbname where dbname is the name of the new database. For example, to create a database called "sample": mysqladmin -u root -p create sample
2.	Enter the MySQL root password. The database now exists but has no tables or access control. See the next two tasks to set up access control and create tables in the database.

Tip

• You may also create a new database from within the mysql shell program, if you connect as the MySQL root user. Use the following SQL statement:

  create database dbname ;

Access control in MySQL can be quite complex. The simple version we show you here creates a single MySQL user with its own password. The user is allowed to connect to one database and will have a great deal of access to the database, but not total access. The user is able to retrieve information from the database ("SELECT" data) and to add, delete, and change records in the database. He or she is able to connect to the database only from the same machine that MySQL is running on (localhost) and can create and removebut not alterthe structure of tables in the database.

The graphical interfaces for MySQL provided by the Webmin, phpMySQL, and MySQL Administrator applications simplifies much of MySQL administration. See the sidebar "GUI Tools for MySQL Administration," earlier in this chapter.

Properly setting up a new database in MySQL involves first creating its access controls. This ensures that a new database never exists even for a moment without access control.

To configure access control for a database:

1.	If mysqld is not already running, then start it.
2.	mysql -u root -p mysql You are connecting to the "master" database, mysql, as user root.
3.	Enter the MySQL root password.
4.	Create a new MySQL user. Commands in the mysql tool can be many lines long. The following command inserts a new row into the user table, creating a MySQL user named "daffy": INSERT INTO user SET Host='localhost', User='daffy' To execute the command, enter a semicolon, or \g or \G , and press , as shown in Figure 14.25 : ; Figure 14.25. Creating a new MySQL user named "daffy." mysql> INSERT INTO user SET -> Host='localhost', User='daffy' -> ; Query OK, 1 row affected (0.03 sec) mysql> The above command is an SQL statement. In this case, you are inserting data into a table called "user" in the mysql database. The new user is added, and is set to be allowed to connect only from this machine (that's the localhost setting) and to have no permissions to do anything yet (the default).
5.	Look at the record ("row") for the new user: SELECT * FROM user WHERE User='daffy' \G The * means that we are asking for all the fields in the record. We use the \G end-of-command code here because otherwise the output would not fit nicely on the screen ( Figure 14.26 ). Figure 14.26. Using an SQL SELECT statement to view a record from the database. mysql> SELECT * FROM user WHERE -> User='daffy' -> \G *************************** 1. row ************ Host: localhost User: daffy Password: Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 1 row in set (0.06 sec) mysql> Notice how all of the fields except the Password field have something in them. When you created this record in step 4, you set the values for the User and Host fields, and the default values shown in Figure 14.26 were filled in for all the fields you did not set. One field, the Password field, has no default, so it is empty.
6.	Set the user's password. The following SQL statement updates the user table, setting the contents of the field Password to an encrypted version of "qu@cker"you should use a different password, of course, but just remember what you use! UPDATE user SET Password=password('qu@cker) WHERE User='daffy; The value put into the Password field is the result of using the MySQL password() function to encrypt the supplied password ( Figure 14.27 ). Figure 14.27. Using an SQL UPDATE statement to set the user's password. mysql> UPDATE user SET Password=password(`qu@cker') -> WHERE User='daffy'; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>
7.	Check that the update worked: SELECT User,Password FROM user WHERE User='daffy'; In this case, you are asking for only two fields from the record: the User field and the Password field ( Figure 14.28 ). Figure 14.28. Using a SELECT statement to retrieve only two fields from a record. mysql> SELECT User,Password FROM user -> WHERE User='daffy'; +-------+-------------------------------------------+ User Password +-------+-------------------------------------------+ daffy *59E155CA427C9377E9B924967BD8AD16798FC0F5 +-------+-------------------------------------------+ 1 row in set (0.00 sec) mysql>
8.	DESCRIBE db; This shows all the column (field) descriptions in the db table ( Figure 14.29 ). Figure 14.29. Output from DESCRIBE db showing the column descriptions for the db table. mysql> DESCRIBE db; +-----------------------+---------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-----------------------+---------------+------+-----+---------+-------+ Host char(60) PRI Db char(64) PRI User char(16) PRI Select_priv enum('N','Y') N Insert_priv enum('N','Y') N Update_priv enum('N','Y') N Delete_priv enum('N','Y') N Create_priv enum('N','Y') N Drop_priv enum('N','Y') N Grant_priv enum('N','Y') N References_priv enum('N','Y') N Index_priv enum('N','Y') N Alter_priv enum('N','Y') N Create_tmp_table_priv enum('N','Y') N Lock_tables_priv enum('N','Y') N +-----------------------+---------------+------+-----+---------+-------+ 15 rows in set (0.00 sec) mysql>
9.	Create an entry for the new database. Here's how to create an entry for a new database called "sample." The database doesn't exist yetyou are simply preparing access controls for it first. INSERT INTO db VALUES ('localhost','sample','daffy', 'Y','Y','Y','Y','Y','Y','N','N','N',' N','N','N'); See Figure 14.30 . Figure 14.30. Inserting a new record into the db table. mysql> INSERT INTO db VALUES (`localhost','sample','daffy' , 'Y','Y','Y','Y','Y','Y','N','N','N','N','N','N'); Query OK, 1 row affected (0.01 sec) mysql> Here you have given the user daffy permission to connect to the sample database from the local machine (not over the Internet) and have granted the user select, insert, update, and delete privileges. The six "Y" values correspond to the fourth, fifth, sixth , seventh, eight, and ninth fields listed in Figure 14.29. Using this form of the INSERT statement, you must supply exactly the right number of values, one for each column. Otherwise, you will get an error message like this: ERROR 1136: Column count doesn't match value count at row 1
10.	Check that the new record looks OK: SELECT * FROM db WHERE User='daffy'\G as shown in Figure 14.31 . Figure 14.31. Selecting a record from the db table. mysql> SELECT * FROM db WHERE User='daffy'\G *************************** 1. row *************************** Host: localhost Db: sample User: daffy Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N 1 row in set (0.00 sec) mysql>
11.	flush privileges; This tells MySQL to reload its access controls. MySQL now knows about the access controls you just created. You could also do this from a Unix shell prompt with mysqladmin -u root -p reload
12.	create database sample; This creates a new, empty database. You can also do this from a Unix shell prompt with mysqladmin -u root -p create sample
13.	Quit the mysql tool: quit (or exit ) You're back at your Unix shell prompt.
14.	Connect to the new database as user daffy: mysql -u daffy -p sample
15.	Enter daffy's password. You are now connected to the sample database as the MySQL user daffy ( Figure 14.32 ). Figure 14.32. Connecting to the sample database as the MySQL user daffy. localhost:~ vanilla$ mysql -u daffy -p sample Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 4.1.13-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
16.	Quit the mysql utility: quit

To add a table to a database:

1.	Connect to the database. To connect to the sample database as the user daffy: mysql -u daffy -p sample
2.	Enter the MySQL user's password (Figure 14.32).
3.	Create the table. The following SQL statement creates a table called "table_one" with three fields. The "name" field will hold up to 20 characters , the "address" field up to 30, and the "phone" field up to 20; and the "id" field will hold an integer (a number) that will be automatically filled in each time a record is added. The PRIMARY KEY instruction tells MySQL to use the id field as a sort of master index for the table. Searching the table based on that field will go faster than a search based on other fields. CREATE TABLE table_one ( name char(20), address char(30), phone char(20), id int NOT NULL auto_increment, PRIMARY KEY(id) ); Figure 14.33 shows the result, and also the result of the next step. Section 13.1.5 of the MySQL user's manual covers the CREATE TABLE syntax in detail (see http://dev.mysql.com/doc/mysql/en/sql-syntax.html). Figure 14.33. Creating a new table with a CREATE statement. mysql> CREATE TABLE table_one ( -> name char(20) , -> address char(30) , -> phone char(20) , -> id int NOT NULL auto_increment , -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.01 sec) mysql> show tables; + - - - - - - - - - + Tables_in_sample + - - - - - - - - - + table_one + - - - - - - - - - + 1 row in set (0.00 sec) mysql>
4.	show tables; This shows that the new table has been created. If you want to see how mysql describes your table, use DESCRIBE table_one;
5.	Quit the mysql utility: quit

Tip

• You can see the SQL statement used to create any table with the special SQL statement

  show create table tablename \G

  This can be a useful way to learn how tables are constructed .

For the following tasks, we assume that you are connected to the sample database as the user daffy, as described above.

About the SQL WHERE clause

Many of the SQL statements we show you here use the SQL WHERE clause to limit which records are affected by the SQL statement. The options for using a WHERE clause are varied, but usually you specify a column and the value it must contain for the SQL statement to "catch" a record. Section 13.2.7 of the MySQL user's manual has some good examples of using a WHERE clause in an SQL statement.

To add a new record (row) to a table:

• Use an INSERT statement.
  
  There are three forms of the INSERT statement. Section 13.2.4 of the MySQL user's manual covers INSERT syntax in detail. Here is one way to insert a new row into a table:
  
  

   INSERT INTO table_one SET name = 'Margaret', address='Constantinople'; 

  
  In this case, even though you did not specify a value for the id column, it will be filled in automatically, because it is an auto_increment field. The phone field remains empty.
  
  

To select data from a table:

• Use a SELECT statement.
  
  The SELECT statement is covered in section 13.2.7 of the MySQL user's manual.
  
  We are covering this after the INSERT statement so that you will have some data to select! But SELECT statements are actually the most common in using databases.
  
  The following SQL statement selects the name, address, and id fields for all records from table_one :
  
  

   SELECT name,address,id FROM table_one; 

  
  Figure 14.34 shows the result (we cheated and added two more records when you weren't looking). We can see that there are two Margaretsone in Constantinople with id 1, and one in Pompeii, with id 3.
  
  

  Figure 14.34. Using a SELECT statement to select only the name, address, and id columns.

   mysql>  SELECT name,address,id FROM table_one;  +----------+----------------+----+  name      address         id  +----------+----------------+----+  Margaret  Constantinople   1   Larry     Ohio             2   Margaret  Pompeii          3  +----------+----------------+----+ 3 rows in set (0.00 sec) mysql> 

  
  If you do not include a WHERE clause in the SELECT statement, it fetches every record in the table.
  
  To do the same SELECT statement, but to find only records where the name is Margaret, you would use
  
  

   SELECT name,address,id FROM table_one WHERE name='Margaret'; 

  
  

To change the values in an existing record:

• Use an UPDATE statement.
  
  Section 13.2.10 of the MySQL user's manual covers UPDATE in detail. Here is an example of changing Margaret's address to "Istanbul":
  
  

   UPDATE table_one SET address='Istanbul' WHERE name='Margaret'; 

  
  That is OK, unless there is more than one Margaret in the table, in which case they would all get moved to Istanbulmaybe not what they wanted.
  
  In our sample table, the id field was designated as a PRIMARY KEY , which means that no two rows will ever have the same value in that column. So if you know that the record you want to update has an id of 1 , you would use
  
  

   UPDATE table_one SET address='Istanbul' WHERE id=1; 

  
  Because the id field is an integer field, you do not enclose its value in single quotes.
  
  How would you know that the record had an id of 1 ? You would have had to SELECT that data from the table first. See the next task.
  
  

To delete a row from a table:

• Use a DELETE statement.
  
  Unless you want to delete every row in a table, always include a WHERE clause in your DELETE statements. DELETE is covered in section 13.2.1 of the MySQL user's manual.
  
  To delete the Margaret who lives in Pompeii:
  
  

   DELETE FROM table_one WHERE id=3; 

  
  as shown in Figure 14.35 .
  
  

  Figure 14.35. Using a DELETE statement to delete a record.

   mysql>  DELETE FROM table_one  ->  WHERE id=3;  Query OK, 1 row affected (0.00 sec) mysql> 

  
  

Creating a script that uses SQL

Now that you have that lovely new database running, we are going to show you a very simple Perl script that uses MySQL.

The script uses the popular Perl DBI ( DataBase Independent ) module, which allows Perl scripts to be written to work with a variety of SQL databases. You add a second Perl module for the particular database engine you are using. So our script will also use the DBD::mysql module.

If this sort of thing interests you (and who would not be fascinated by such an enthralling subject?), then we suggest you have a look at the book Programming the Perl DBI , coauthored by Alligator Descartes and the creator of the Perl DBI module, Tim Bunce (O'Reilly; www.oreilly.com). Installing Perl modules is covered in Chapter 13.

To install the Perl DBI and DBD::mysql modules:

1.

Install the MySQL database server.

2.

Make sure the MySQL server is running. Now you can use the instructions in Chapter 13 to install the DBI and DBD::mysql modules. The short version for the DBI module is sudo cpan -i DBI (You may need to do export FTP_PASSIVE=1 first.) For the DBD::mysql module, you need to use the CPAN shell and add a little extra command so that the install script will know to use the MySQL user "test" when testing the installation. The short version would be sudo cpan Then, at the cpan> prompt: o conf makepl_arg -testuser=test install DBD::mysql The DBD::mysql install process performs several hundred tests. Sometimes a few of the tests will fail but the module is still usable. You can force CPAN to install the module in spite of the test failures by using this command at the cpan> prompt: force install DBD::mysql (We had to do this for DBD::mysql back in version 2.1020, which failed two tests on Mac OS X 10.2. DBD::mysql 3.0002 installed cleanly on Mac OS X 10.4.2.)

Once you have installed the Perl modules, you can create a simple script that will talk to the MySQL database engine. The script in the following task assumes you have created the "sample" database described earlier.

To create a Perl script that uses MySQL:

1.	Copy the code from Figure 14.36 (on the facing page) into a file called database.pl. Figure 14.36. Code listing of a Perl script that searches a database for a name supplied by the user. #!/usr/bin/perl # Simple script that uses MySQL # ######################################################################### # Tell Perl we want to use the DBI module use DBI; # Ask the user for a name to search the database for. print "Enter a name:"; $name = <STDIN>; chop $name; # strip the newline character # Items needed to connect to MySQL $database = `sample'; $user = `daffy'; $password = `d**kb0y'; $hostname = `localhost'; # dsn is the "Distiguished Service name". It includes the name of the # DBD module we are using (mysql) $dsn = "DBI:mysql:database=$database;host=$hostname"; # dbh is the "Database handle" $dbh = DBI->connect($dsn, $user, $password); # Here's SQL statement we'll use $sql = "SELECT id,name,address,phone FROM table_one WHERE name=?"; # These lines check the SQl statement for errors $sth = $dbh->prepare($sql); $sth->execute($name); # Here we actually fetch the data $found_rows = $sth->fetchall_arrayref( {} ); $sth->finish; # tell the database we are done with this request # If we didn't find anything, give a message and quit unless ( @{$found_rows} ) { print "Didn't find anyone with the name $name\n"; exit; } # If we get this far we found at least one row. # Print our all the found rows. foreach $row ( @{$found_rows} ) { print "ID: $row->{id}\n"; print "Name: $row->{name}\n"; print "Address: $row->{address}\n"; print "Phone: $row->{phone}\n"; print "\n"; # extra blank line } Change the line $password = `d**kb0y'; to have the password for user daffy .
2.	Make the file executable, but not readable by others: chmod 700 database.pl The file contains a MySQL user name and password, so you make it executable for yourself but not readable by others; that way, they can't find the password. If the MySQL user whose password is in the script has only SELECT privileges on the database, then it would be safe to let others see the password (assuming that there is no private information in the database).
3.	Test the script: ./database.pl You are prompted for a name. Type one that you know is in the database, and press . Figure 14.37 shows an example of what happens when the script doesn't find anyone, and when it does. Figure 14.37. Using the Perl script to search the database. localhost:~/bin vanilla$ ./database.pl Enter a name: Johnny Didn't find anyone with the name Johnny localhost:~/bin vanilla$ ./database.pl Enter a name: Margaret ID: 1 Name: Margaret Address: Constantinople Phone: ID: 4 Name: Margaret Address: Ithaca Phone: [phone] hostname:~/bin vanilla$
4.	Congratulations! You are now a Unix database-application programmer.

Learning More About SQL

Obviously, there is a great deal more to SQL than we show you here. Section 13 of the MySQL user's manual is a good reference. An online tutorial for beginners is available at A Gentle Introduction to SQL (www.sqlzoo.net). Two books about SQL are SQL: Visual QuickStart Guide , Second Edition, by Chris Fehily (Peachpit Press; www.peachpit.com), and SQL in a Nutshell , Second Edition, by Kevin Kline (O'Reilly; www.oreilly.com).

Another very popular open-source SQL database is PostgreSQL (pronounced post-gres-KYOO-well) (www.postgresql.org).