By Russell Dyer
Price: $34.99 USD
£21.99 GBP

Cover | Table of Contents | Colophon

Table of Contents

Chapter 1: Introduction to MySQL

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

MySQL is an open source, multithreaded, relational database management system created by Michael “Monty” Widenius in 1995. In 2000, MySQL was released under a dual-license model that permitted the public to use it for free under the GNU General Public License (GPL); this caused its popularity to soar. The company that owns and develops MySQL is MySQL AB (the AB stands for aktiebolag, the Swedish term for stock company), which is now a subsidiary of Sun Microsystems. Currently, MySQL AB estimates that there are more than 6 million installations of MySQL worldwide, and reports an average of 50,000 downloads a day of MySQL installation software from its site and from mirror sites. The success of MySQL as a leading database is due not only to its price—after all, other cost-free and open source are available—but also its reliability, performance, and features.

Many features contribute to MySQL’s standing as a superb database system. Its speed is one of its most prominent features. In a comparison by eWEEK of several databases—including MySQL, Oracle, MS SQL, IBM DB2, and Sybase ASE—MySQL and Oracle tied for best performance and for greatest scalability (see for more details). MySQL is remarkably scalable, and is able to handle tens of thousands of tables and billions of rows of data. Plus, it manages small amounts of data quickly and smoothly.

The storage engine, which manages queries and interfaces between a user’s SQL statements and the database’s backend storage, is the critical software in any database management system. MySQL offers several storage engines with different advantages. Some are transaction-safe storage engines that allow for rollback of data. Additionally, MySQL has a tremendous number of built-in functions that are detailed in several chapters of this book. MySQL is also very well known for rapid and stable improvements. Each new release comes with speed and stability upgrades, as well as new features.

The MySQL package comes with several programs. Foremost is the MySQL server, represented by the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

The Value of MySQL

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Many features contribute to MySQL’s standing as a superb database system. Its speed is one of its most prominent features. In a comparison by eWEEK of several databases—including MySQL, Oracle, MS SQL, IBM DB2, and Sybase ASE—MySQL and Oracle tied for best performance and for greatest scalability (see for more details). MySQL is remarkably scalable, and is able to handle tens of thousands of tables and billions of rows of data. Plus, it manages small amounts of data quickly and smoothly.

The storage engine, which manages queries and interfaces between a user’s SQL statements and the database’s backend storage, is the critical software in any database management system. MySQL offers several storage engines with different advantages. Some are transaction-safe storage engines that allow for rollback of data. Additionally, MySQL has a tremendous number of built-in functions that are detailed in several chapters of this book. MySQL is also very well known for rapid and stable improvements. Each new release comes with speed and stability upgrades, as well as new features.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

The MySQL Package

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The MySQL package comes with several programs. Foremost is the MySQL server, represented by the mysqld daemon. The daemon listens for requests on a particular network port (3306 by default) by which clients submit queries. The standard MySQL client program is simply called mysql. With this text-based interface, a user can log in and execute SQL queries. This client can also accept queries from text files containing queries, and thereby execute them on behalf of the user or other software. However, most MySQL interaction is done by programs using a variety of languages. The interfaces for C, Perl, and PHP are discussed in this book.

A few wrapper scripts for mysqld come with MySQL. The mysqld_safe script is the most common way to start mysqld, because the script can restart the daemon if it crashes. This helps ensure minimal downtime for database services. The script mysqld_multi is used to start multiple sessions of mysqld_safe, and thereby multiple mysqld instances, for handling requests from different ports, and to make it easier to serve different sets of databases or to test different versions of MySQL.

MySQL also comes with a variety of utilities for managing a MySQL server. mysqlaccess is used for creating user accounts and setting their privileges. mysqladmin can be used to manage the MySQL server itself from the command line. This interaction includes checking a server’s status and usage, and shutting down a server. mysqlshow may be used to examine a server’s status, as well as information about databases and tables. Some of these utilities require Perl, or ActivePerl for Windows, to be installed on the server. See to download and install a copy of Perl on non-Windows systems, and see to download and install a copy of ActivePerl on Windows .

MySQL also comes with a few utilities for importing and exporting data to and from MySQL databases. mysqldump is the most popular for exporting data and table structures to a plain-text file known as a dump file. This can be used for backing up data or for manually moving it between servers. The

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Licensing

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Although MySQL can be used for free and is open source, MySQL AB holds the copyrights to the source code. The company offers a dual-licensing program for its software: one allows cost-free use through the GPL under certain common circumstances, and the other is a commercial license bearing a fee. They’re both the same software, but each has a different license and different privileges. See for more details on the GPL.

MySQL AB allows you to use the software under the GPL if you use it without redistributing it, or if you redistribute it only with software licensed under the GPL. You can even use the GPL if you redistribute MySQL with software that you , as long as you distribute your software under the GPL as well.

However, if you have developed an application that requires MySQL for its functionality and you want to sell your software with MySQL under a nonfree license, you must purchase a commercial license from MySQL AB. There are other scenarios in which a commercial license may be required. For details on when you must a license, see .

Besides holding the software copyrights, MySQL AB also holds the MySQL trademark. As a result, you cannot distribute software that includes MySQL in the name.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Mailing Lists

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You can receive some assistance with problems that you may have with MySQL from the MySQL community at no charge through several listserv email systems hosted by MySQL AB. There is a main mailing list for MySQL (mysql) and several specialized mailing lists where anyone can post a message for help on a particular topic. One list covers questions about database performance (benchmarks). Another is for questions on the Windows versions of MySQL (win32). There are also lists for problems concerning the Java Database Connectivity™ (JDBC) drivers (java) and for the Perl DBI module (perl).

For a complete listing or to subscribe to one or more of these mailing lists, go to . On this mailing list page, you will find links for subscribing to each list. When you click a subscription link, you will see a very simple form on which to enter your email address. Incidentally, some subscribers like to use special email addresses and names representing their online personas. This allows anonymity and may make sorting emails easier. Others prefer to use their real names and contact information. After you enter your email address, you will receive an automated message to confirm your address. That email will have a link to the MySQL site with some parameters identifying your address. Click the link, and it will open your web browser and confirm your subscription.

The page from which you can subscribe to a list also has links for unsubscribing from lists, as well as links to archives of previous listserv messages for each list. You can search these archives for messages from others who are describing the same problem that you are trying to resolve. It’s always a good idea to search archives before posting anything of your own, to find out whether your topic has been discussed before. If you can’t find a solution in the documentation available to you or in the archives, you can post a message to a particular mailing list by sending an email to that list on lists.mysql.com

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Books and Other Publications

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Besides the mailing list archives mentioned in the previous section, MySQL AB provides extensive online documentation of the MySQL server and all of the other software it distributes. You can find the documentation at . The documentation is now organized by version of MySQL. You can read the material online or download it in a few different formats (e.g., HTML or PDF). It is also available in hardcopy format: MySQL Language Reference and MySQL Guide, both from MySQL Press.

In addition to this book, O’Reilly Media publishes a few other books on MySQL worth buying and reading. O’Reilly’s mainline MySQL book is Managing & Using MySQL (2nd ed., 2002) by George Reese, Randy Jay Yarger, and Tim King (with Hugh E. Williams). George Reese has compiled a smaller version called MySQL Pocket Reference (2nd ed., 2007). For common practical problem solving, there’s MySQL Cookbook (2nd ed., 2006) by Paul DuBois. For advice on optimizing MySQL and performing administrative tasks, such as backing up databases, O’Reilly has published High Performance MySQL (2004) by Jeremy D. Zawodny and Derek J. Balling.

O’Reilly also publishes several books with regard to the MySQL APIs. For PHP development with MySQL, there’s Web Database Applications with PHP and MySQL (2nd ed., 2004) by Hugh E. Williams and David Lane. For interfacing with Perl to MySQL and other database systems, there’s Programming the Perl DBI (2000) by Alligator Descartes and Tim Bunce. To interface to MySQL with Java, you can use the JDBC and JConnector drivers and George Reese’s book, Database with JDBC and Java (2nd ed., 2000).

In addition to the published books on MySQL, a few web sites offer brief tutorials on using MySQL topics. The O’Reilly Network often publishes articles on MySQL and the APIs for Perl, PHP, and Python in its online publication ONLamp.com (). Incidentally, I’ve contributed a few articles to that site and to several other publications on MySQL and related topics. MySQL AB also provides some in-depth articles on MySQL. You can find them in the DevZone section of its web site, . Many of these articles deal with new products and features, making them ideal if you want to learn about using the latest releases available even while they’re still in the testing stages. Developer Shed () is an additional educational resource. All of these online publications are . If you are a MySQL Enterprise customer, though, you can get information about MySQL from its private Knowledge Base, of which I am currently the editor.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 2: Installing MySQL

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The MySQL database server and client software work on several different operating systems, notably Linux, FreeBSD, and a wide range of Unix systems: Sun Solaris, IBM AIX, HP-UX, and so on. MySQL AB has also developed a Mac OS X version, a Novell NetWare version, and several MS Windows versions. You can obtain a copy of the community version of MySQL from MySQL AB’s site ().

This chapter briefly explains the process of installing MySQL on Unix, Linux, Mac OS X, NetWare, and Windows operating systems. For some operating systems, there are additional sections for different distribution formats. For any one platform, you can install MySQL by reading just three sections of this chapter: the next section on ”; the section that applies to the distribution that you choose; and the section on ” at the end of the chapter.

Before beginning to download an installation package, you must decide which version of MySQL to install. The best choice is usually the latest stable version recommended by MySQL AB on its site. This is the GA (Generally Available) release. It’s not recommended that you install a newer version unless you need some new feature that is contained only in one of the newer versions, such as the beta version or the RC (Release Candidate) version. It’s also not recommended that you install an older version unless you have an existing database or an API application that won’t function with the current version.

When installing MySQL, you also have the option of using either a source distribution or a binary distribution. It’s easier, and recommended, for you to install a binary distribution. However, you may want to use a source distribution if you have special configuration requirements that must be set during the installation or at compile time. You may also have to use a source distribution if a binary distribution isn’t available for your operating system.

The steps for installing MySQL on all Unix types of operating systems are basically the same. This includes Linux, Sun Solaris, FreeBSD, IBM AIX, HP-UX, etc. It’s recommended that you install MySQL with a binary distribution, but as explained in the previous section, sometimes you may want to use a source distribution. To install a source distribution, you will need copies of GNU

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Choosing a Distribution

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Before beginning to download an installation package, you must decide which version of MySQL to install. The best choice is usually the latest stable version recommended by MySQL AB on its site. This is the GA (Generally Available) release. It’s not recommended that you install a newer version unless you need some new feature that is contained only in one of the newer versions, such as the beta version or the RC (Release Candidate) version. It’s also not recommended that you install an older version unless you have an existing database or an API application that won’t function with the current version.

When installing MySQL, you also have the option of using either a source distribution or a binary distribution. It’s easier, and recommended, for you to install a binary distribution. However, you may want to use a source distribution if you have special configuration requirements that must be set during the installation or at compile time. You may also have to use a source distribution if a binary distribution isn’t available for your operating system.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Unix Source Distributions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The steps for installing MySQL on all Unix types of operating systems are basically the same. This includes Linux, Sun Solaris, FreeBSD, IBM AIX, HP-UX, etc. It’s recommended that you install MySQL with a binary distribution, but as explained in the previous section, sometimes you may want to use a source distribution. To install a source distribution, you will need copies of GNU gunzip, GNU tar, GNU gcc (at least version 2.95.2), and GNU make. These tools are usually included in all Linux systems and in most Unix systems. If your system doesn’t have them, you can download them from the GNU Project’s site ().

Once you’ve chosen and downloaded the source distribution files for MySQL, enter the following commands as root from the directory where you want the source files stored:

groupadd mysql
useradd -g mysql mysql
tar xvfz /tmp/mysql-version.tar.gz
cd mysql-version

The first command creates the user group mysql. The second creates the system user mysql and adds it to the group mysql at the same time. The next command uses the tar utility (along with gunzip via the z option) to unzip and unpack the source distribution file you downloaded. You should replace the word version with the version number—that is to say, you should use the actual path and filename of the installation file that you downloaded for the second argument of the tar command. The last command changes to the directory created by tar in the previous line. That directory contains the files needed to configure MySQL.

This brings you to the next step, which is to configure the source files to prepare them for building the binary programs. This is where you can add any special build requirements you may have. For instance, if you want to change the default directory from where MySQL is installed, use the --prefix option with a value set to equal the desired directory. To set the Unix socket file’s path, you can use --with-unix-socket-path. If you would like to use a different character set from the default of

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Unix Binary Distributions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Installing MySQL with a binary distribution is easier than using a source distribution and is the recommended choice if a binary distribution is available for your platform. The files are packaged together into an archive file and then compressed before being put on the Internet for downloading. Therefore, you will need a copy of GNU tar and GNU gunzip to be able to unpack the installation files. These tools are usually included on all Linux systems and most Unix systems. If your system doesn’t have them, though, you can download them from the GNU Project’s site ().

Once you’ve chosen and downloaded the installation package, enter something like the following from the command line as root to begin the MySQL installation :

groupadd mysql
useradd -g mysql mysql
cd /usr/local
tar xvfz /tmp/mysql-version.tar.gz

The first command creates the user group mysql. The second creates the user mysql and adds it to the group mysql at the same time. The next command changes to the directory where the MySQL files are about to be extracted. In the last command, you use the tar utility (along with gunzip via the z option) to unzip and unpack the source distribution file that you downloaded. The word version in the name of the installation file is replaced with the version number—that is to say, use the actual path and name of the installation file that you downloaded as the second argument of the tar command. For Sun Solaris systems, you should use gtar instead of tar.

After running the previous commands, you need to create a symbolic link to the directory created by tar in /usr/local:

ln -s /usr/local/mysql-version /usr/local/mysql

This creates /usr/local/mysql as a link to /usr/local/mysql-version, where mysql-version is the actual name of the subdirectory that tar created in /usr/local. The link is necessary because MySQL is expecting the software to be located in /usr/local/mysql and the data to be in /usr/local/mysql/data by default. It should be noted that for some versions of MySQL, a different directory is expected and used. So consult MySQL’s online documentation to be sure.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Linux RPM Distributions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If your server is running on a version of Linux that installs software through the RPM package format (where RPM originally stood for Red Hat Package Manager), it is recommended that you use a package instead of a source distribution. Currently, RPMs are provided based on only a couple of different Linux distributions: various versions of Red Hat Enterprise Linux and SuSE Linux Enterprise. For all other distributions of Linux, MySQL RPMs are based on the Linux kernel or the type of libraries installed on the server. For each version of MySQL, there are a few RPM files that you can download. The primary two contain the server and client naming scheme is MySQL-server-version.rpm and MySQL-client-version.rpm, where version is the actual version number. In addition to these main packages, you may also want to install some of the other RPM files that are part of a distribution. There’s an RPM for client-shared libraries (MySQL-shared-version.rpm), another for libraries and C API include files for certain clients (MySQL-devel-version.rpm), and another for benchmarking and other MySQL performance tests (MySQL-bench-version.rpm).

To install RPM files after downloading them to your server, enter something like the following from the command line in the directory where they’re located:

rpm -ivh MySQL-server-version.rpm \
         MySQL-client-version.rpm

If an earlier version of MySQL is already installed on the server, you will receive an error message stating this problem, and the installation will be canceled. If you want to upgrade an existing installation, you can replace the i option in the example with an uppercase U.

When the RPM files are installed, the mysqld daemon will be started or restarted automatically. Once MySQL is installed and running, you need to make some adjustments that are explained in the last section of this chapter (”).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Macintosh OS X Distributions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

On recent versions of Mac OS X, MySQL is usually installed already. However, in case it is not installed on your system or you want to upgrade your copy of MySQL by installing the latest release, directions are included here.

As of version 10.2 of Mac OS X and version 4.0.11 of MySQL, binary package (PKG) files are available for installing MySQL. If your server is using an older version of Mac OS X, you need to install MySQL using a Unix source or binary distribution, following the directions described earlier in this chapter for those particular packages. If your server is not running a graphical user interface (GUI) or a desktop manager, you can instead install MySQL on a Macintosh system with a TAR package. This can be downloaded from the download page on MySQL’s web site. Explanation of that method of installation is included here.

If an older version of MySQL is already installed on your server, you will need to shut down the MySQL service before installing and running the newer version. You can do this with the MySQL Manager Application, which is a GUI application. It’s typically installed on recent versions of Mac OS X by default. If your server doesn’t have the MySQL Manager Application, enter the following from the command line to shut down the MySQL service:

mysqladmin -u root shutdown

Incidentally, if MySQL isn’t already installed on your system, you may need to create the system user, mysql, before installing MySQL.

To install the MySQL package file, from the Finder desktop manager, double-click on the disk image file (the .dmg file) that you downloaded. This will reveal the disk image file’s contents. Look for the PKG files; there will be at least two. Double-click on the one named MySQL followed by the version numbers. This will begin the installation program. The installer will take you through the installation steps from there. The default settings are recommended for most users and developers. You will need an administrator username and password. To have MySQL started at boot time, add a

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Novell NetWare Distributions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If your server is using Novell NetWare 6.0 or later, and the required Novell support packs have been installed, you can install MySQL on it. For version 6.0 of NetWare, you need to have Support Pack 4 installed and updated along with the current version of LibC. For version 6.5 of NetWare, Support Pack 2 needs to be installed and updated along with the current version of LibC. You can obtain support packs from Novell’s site (). You can find the latest version of LibC at . Another requirement for installing MySQL is that the MySQL server and data be installed on a Novell Storage Services (NSS) volume.

If an older version of MySQL is already installed and running on your server, you need to shut down the MySQL service before installing and running the newer version. You can do this from the server console like so:

mysqladmin -u root shutdown

Next, you need to log on to the server from a client that has access to the location (SYS:MYSQL) where MySQL is to be installed. Unpack the compressed binary package to that location. When the ZIP file has finished unpacking, you can establish a search path for the directory that holds the MySQL NetWare Loadable Modules (NLMs) by entering the following from the server console:

SEARCH ADD SYS:MYSQL\BIN

At this point, MySQL is basically installed. Now you need to generate the initial privileges or grant tables. You can do this by entering the following from the server console:

.\scripts\mysql_install_db

The mysql_install_db utility is a script provided with the distribution to generate the initial privileges or grant tables (i.e., the mysql database). Once this is done, MySQL is ready to be started. To do this, just enter the following from the server console:

mysqld_safe

To have MySQL started at boot time, you must add the following lines to the server’s autoexec.ncf file:

SEARCH ADD SYS:MYSQL\BIN
MYSQLD_SAFE --autoclose --skip-external-locking

The first line establishes the search path for MySQL. The second line starts the mysqld_safe daemon at startup. The first option in this command instructs the server to close MySQL automatically when the server shuts down. The second option instructs the server not to allow external table locking. (External locks can cause problems with NetWare version 6.0.) Both of these options are recommended.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Windows Distributions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Installing MySQL on a server using Windows is fairly easy. If MySQL is already installed and running on your server and you want to install a newer version, you will need to shut down the existing one first. For server versions of MS Windows (e.g., Windows NT), MySQL is installed as a service. If it’s installed as a service on your server, you can enter the following from a DOS command window to shut down the service and remove it:

mysqld -remove

If MySQL is running, but not as a service, you can enter the following from a DOS command window to shut it down:

msyqladmin -u root shutdown

MySQL AB’s site (http://dev.mysql.com/downloads/) contains three installation packages: a Windows Essential package, a standard Windows package, and a standard Windows package without the installer. The Windows Essential package is the recommended format. It contains only the essential files for running MySQL. This includes the usual command-line utilities and the header files for the C API. The standard Windows package contains the essential files, as well as documentation, the MySQL Administrator, the embedded server, the benchmark suite, and a few other useful scripts. The standard Windows package without the installer contains the same binary files and other related files for MySQL, but not an installer. You’ll need to extract and copy the files into the c:\mysql directory. Then, you must create a my.ini file in the c:\windows directory. Several examples showing different server usage come with the distribution package.

The Windows Essential package is a file called MySQL-version.msi. From the Windows desktop, just double-click this file’s icon and the Windows Installer program will start.

The standard Windows installation package is a compressed file from which you have to extract the installation files. To do this, you need a utility such as WinZip () to uncompress the files. One of the files is named setup.exe. Double-click it to start the installer for this package. From this point, the installation process is pretty much the same for the packages that use the installer.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Postinstallation

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

After you’ve finished installing MySQL on your server, you should perform a few tasks before allowing others to begin using the service. You may want to configure the server differently by making changes to the configuration file. At a minimum, you should change the password for the root user and add some nonadministrative users. Some versions of MySQL are initially set up with anonymous users. You should delete them. This section will briefly explain these tasks.

Although the MySQL developers have set the server daemon to the recommended configuration, you may want to set the daemon differently. For instance, you may want to turn on error logging. To do this, you will need to edit the main configuration file for MySQL. On Unix systems, this file is /etc/my.cnf. On Windows systems, the main configuration file is usually either c:\windows\my.ini or c:\my.cnf. The configuration file is a simple text file that you can edit with a plain-text editor, not a word processor. The configuration file is organized into sections or groups under a heading name contained in square brackets. For instance, settings for the server daemon mysqld are listed under the group heading [mysqld]. Under this heading, you could add something like log = /var/log/mysql to enable logging and to set the directory for the log files to the one given. You can list many options in the file for a particular group. For a complete listing and explanation of these options, see .

You can change the password for the root user in MySQL in a few ways. One simple way is to log in to MySQL through the mysql client by entering the following from the command line:

mysql -u root -p

On a Windows system, you may have to add the path c:\mysql\bin\ to the beginning of this line, if you haven’t added it to your command path. After successfully entering the command, you will be prompted for the root user’s password. This is not the operating system’s root user, but the root user for MySQL. Initially there is no password, so press Enter to leave it blank. If everything was installed properly and the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 3: MySQL Basics

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Although the bulk of this new edition of MySQL in a Nutshell contains reference information, which you can read in small segments as needed, this chapter presents a basic MySQL tutorial. It explains how to log in to the MySQL server through the mysql client, create a database, create tables within a database, and enter and data in tables.

This tutorial does not cover MySQL in depth. Instead, it’s more of a sampler; it’s meant to show you what’s possible and to get you thinking about how to approach tasks in MySQL.

There are various methods of interacting with the MySQL server to develop or work with a MySQL database. The most basic interface that you can use is the mysql client. With it, you can interact with the server from either the command line or within an interface environment.

If MySQL was installed properly on your server, mysql should be available for use. If not, see . On Unix-based systems, you can type whereis mysql. Windows, Macintosh, and other GUI-type systems have a program location utility for finding a program. If you used the default installation method, the mysql program probably resides at /usr/local/mysql/bin/mysql. On Unix systems, if /usr/local/mysql/bin/ is in your default path (the PATH environment variable), you can specify mysql without the full pathname. If the directory is not in your path, you can add it by entering:

PATH=$PATH:/usr/local/mysql/bin
export PATH

Assuming that everything is working, you will need a MySQL username and password. If you’re not the administrator, you must obtain these from her. If MySQL was just installed and the root password is not set yet, its password is blank. To learn how to set the root password and to create new users and grant them privileges, see for starting points and for more advanced details.

From a shell prompt, log in to MySQL like this:

mysql -h host -u user -p

If you’re logging in locally—that is, from the server itself—either physically or through a remote login method, such as SSH (secure shell), you can omit the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

The mysql Client

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

There are various methods of interacting with the MySQL server to develop or work with a MySQL database. The most basic interface that you can use is the mysql client. With it, you can interact with the server from either the command line or within an interface environment.

If MySQL was installed properly on your server, mysql should be available for use. If not, see . On Unix-based systems, you can type whereis mysql. Windows, Macintosh, and other GUI-type systems have a program location utility for finding a program. If you used the default installation method, the mysql program probably resides at /usr/local/mysql/bin/mysql. On Unix systems, if /usr/local/mysql/bin/ is in your default path (the PATH environment variable), you can specify mysql without the full pathname. If the directory is not in your path, you can add it by entering:

PATH=$PATH:/usr/local/mysql/bin
export PATH

Assuming that everything is working, you will need a MySQL username and password. If you’re not the administrator, you must obtain these from her. If MySQL was just installed and the root password is not set yet, its password is blank. To learn how to set the root password and to create new users and grant them privileges, see for starting points and for more advanced details.

From a shell prompt, log in to MySQL like this:

mysql -h host -u user -p

If you’re logging in locally—that is, from the server itself—either physically or through a remote login method, such as SSH (secure shell), you can omit the -h host argument. This is because the default host is localhost, which refers to the system you are on. In other circumstances, where your commands actually have to travel over a network to reach the server, replace the argument host with either a hostname that is translatable to an IP address or the actual IP address of the MySQL server. You should replace the argument user with your MySQL username. This is not necessarily the same as your filesystem username.

The -p option instructs

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Creating a Database and Tables

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Assuming that you have all of the privileges necessary to create and modify databases on your server, let’s look at how to create a database and then tables within a database. For the examples in this chapter, we will build a database for a fictitious bookstore:

CREATE DATABASE bookstore;

In this brief SQL statement, we have created a database called bookstore. You may have noticed that the commands or reserved words are printed here in uppercase letters. This isn’t necessary; MySQL is case-insensitive with regard to reserved words for SQL statements and clauses. Database and table names are case-sensitive on operating systems that are case-sensitive, such as Linux systems, but not on systems that are case-insensitive, such as Windows. As a general convention, though, reserved words in SQL documentation are presented in uppercase letters and database names, table names, and column names in lowercase letters. You may have also noticed that the SQL statement shown ends with a semicolon. An SQL statement may be entered over more than one line, and it’s not until the semicolon is entered that the client sends the statement to the server to read and process it. To cancel an SQL statement once it’s started, enter \c instead of a semicolon.

With our database created, albeit an empty one, we can switch the default database for the session to the new database like this:

USE bookstore

This saves us from having to specify the database name in every SQL statement. MySQL by default will assume the current database, the one we last told it to use. No semicolon is given with the USE statement because it’s a client-based SQL .

Next, we will create our first table, in which we will later add data. We’ll start by creating a table that we’ll use to enter basic information about books, because that’s at the core of a bookstore’s business:

CREATE TABLE books (
book_id INT, 
title VARCHAR(50),
author VARCHAR(50));

This SQL statement creates the table books with three columns. Note that the entire list of columns is contained within parentheses.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Show Me

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Let’s take a moment to admire our work and see what we’ve done so far. To get a list of databases, use the SHOW DATABASES statement:

SHOW DATABASES;
   
+-----------+
| Database  |
+-----------+
| bookstore |
| mysql     |
| test      |
+-----------+

The result of the SHOW DATABASES statement lists not only the database we’ve created, but also two others. One is the mysql database, which contains data about user privileges and was covered in . The third database is the test database, which is set up by default when MySQL is installed. It’s there as a convenience for you to be able to add tables or run SQL statements for testing.

To see a list of tables in the bookstore database, once we select the bookstore database with the USE statement shown earlier, we would enter the following statement:

SHOW TABLES;
   
+---------------------+
| Tables_in_bookstore |
+---------------------+
| authors             |
| books               |
+---------------------+

The result of the SHOW TABLES statement provides a list containing our two tables, just as we expected. If you want to see a list of tables from another database while still using the bookstore database, add a FROM clause to the previous statement:

SHOW TABLES FROM mysql;

This displays a list of tables from the mysql database, even though the default database for the client session is the bookstore database.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Inserting Data

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Now that we’ve set up our first two tables, let’s look at how we can add data to them. We’ll start with the simplest method: the INSERT . With the INSERT we can add one or more records. Before adding information about a book to our books table, because it refers to a field in our authors table, we need to add the author’s information to the latter. We’ll do this by entering these SQL statements through the mysql client:

INSERT INTO authors
(author_last, author_first, country)
VALUES('Greene','Graham','United Kingdom');

SELECT LAST_INSERT_ID( );

+-------------------+
| LAST_INSERT_ID( ) |
+-------------------+
|                 1 |
+-------------------+

INSERT INTO books
(title, author_id, isbn, genre, pub_year)
VALUES('The End of the Affair', 1,'0099478447','novel','1951');

Our first SQL statement added a record, or row, for Graham Greene, an author who wrote the book The End of the Affair. The standard INSERT syntax names the columns for which the values are to be inserted, as we’re doing here. If you’re going to enter values for all of the columns, you don’t need to name the columns, but you must list the data in the same order in which the columns are listed in the table.

In the second SQL statement, we retrieved the identification number assigned to the row we just entered for the author by using the LAST_INSERT_ID⁠(⁠ ⁠ ⁠) function. We could just as easily have entered SELECT author_id FROM authors;.

In the third SQL statement, we added data for a Graham Greene book. In that statement, we listed the columns in an order that’s different from their order in the table. That’s acceptable to MySQL; we just have to be sure that our values are in the same order.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Selecting Data

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Now that we have one row of data in each of our two tables, let’s run some queries. We’ll use the SELECT statement to select the data that we want. To get all of the columns and rows from the books table, enter the following:

SELECT * FROM books;

The asterisk, which acts as a wildcard, selects all columns. We did not specify any criteria by which specific rows are selected, so all rows are displayed from the books table. To select specific columns, we name the columns we want. To select specific rows, we add a WHERE clause to the end of the SELECT statement:

SELECT book_id, title, description 
FROM books
WHERE genre = 'novel';

This SQL statement displays just the book’s identification number, the book’s title, and the description of the book from the books table for all books where the genre column has a value of novel. The results will be more meaningful, of course, when we have data on more books in the database. So, let’s assume that we’ve entered data for a few dozen more books, and proceed.

If we want to get a list of novels from the database along with the author’s full name, we need to join the books table to the authors table. We can join the two tables with a JOIN clause like this:

SELECT book_id, title, pub_year, 
CONCAT(author_first, ' ', author_last) AS author
FROM books
JOIN authors USING(author_id)
WHERE author_last = 'Greene';

In the FROM clause, we join the books table to the authors table using the author_id columns in both tables. If the columns had different labels, we would have to use a different clause or method in the JOIN clause to join the tables (e.g., ON (author_id = writer_id)). Notice in the second line of this SQL statement that we’ve employed a string function, CONCAT⁠(⁠ ⁠ ⁠). With this function you can take bits of data and merge them together with text to form more desirable-looking output. In this case, we’re taking the author’s first name and pasting a space (in quotes) onto the end of it, followed by the author’s last name. The results will appear in the output display as one column, which we’ve given a column heading of

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Ordering, Limiting, and Grouping

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

For times when we retrieve a long list of data, it can be tidier to sort the data output in a specific order. To do this, we can use the ORDER BY clause. Suppose that we want a list of plays written by William Shakespeare from our database. We could enter the following SQL statement to retrieve such a list and to sort the data by the play title:

SELECT book_id, title, publisher
FROM books
JOIN authors USING(author_id)
JOIN publishers USING(publisher_id)
WHERE author_last = 'Shakespeare'
AND genre = 'play'
ORDER BY title, pub_year;

The ORDER BY clause comes at the end, after the WHERE clause. Here the ORDER BY clause orders the data results first by the title column and then, within title, by the pub_year column, or the year that the particular printing of the play was published. By default, data is sorted in ascending alphanumeric order. If we want to order the results in descending order for the titles, we can just add a DESC flag immediately after the title column in the ORDER BY clause and before the comma that precedes pub_year:

...
ORDER BY title DESC, pub_year;

A large bookstore will have many editions of Shakespeare’s plays, possibly a few different printings for each play. If we want to limit the number of records displayed, we could add a LIMIT clause to the end of the previous SQL statement:

SELECT book_id, title, publisher
FROM books
JOIN authors USING(author_id)
JOIN publishers USING(publisher_id)
WHERE author_last = 'Shakespeare'
AND genre = 'play'
ORDER BY title DESC, pub_year
LIMIT 20;

This addition limits the number of rows displayed to the first 20. The count starts from the first row of the result set after the data has been ordered according to the ORDER BY clause. If we want to retrieve the next 10, we would adjust the LIMIT clause to specify the number of rows to skip, along with the number of records to retrieve. So if we want to skip the first 20 rows and list the next 10 rows from our sort, we replace the LIMIT clause in the SQL statement with this one:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Analyzing and Manipulating Data

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

With MySQL you can not only retrieve raw data, but also analyze and format the data retrieved. For instance, suppose we want to know how many titles we stock by Leo Tolstoy. We could enter a SELECT statement containing a COUNT⁠(⁠ ⁠ ⁠) function like this:

SELECT COUNT(*)
FROM books
JOIN authors USING(author_id)
WHERE author_last = 'Tolstoy';
   
+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+

As another example, suppose that after setting up our database and putting it to use we have another table called orders that contains information on customer orders. We can query that table to find the total sales of a particular book. For instance, to find the total revenues generated from, say, William Boyd’s book Armadillo, we would enter the following SQL statement in the mysql client:

SELECT SUM(sale_amount) AS 'Armadillo Sales'
FROM orders
JOIN books USING(book_id)
JOIN authors USING(author_id)
WHERE title = 'Armadillo'
AND author_last = 'Boyd';
   
+-----------------+
| Armadillo Sales |
+-----------------+
|          250.25 |
+-----------------+

Here we are joining three tables together to retrieve the desired information. MySQL selects the value of the sale_amount column from each row in the orders table that matches the criteria of the WHERE clause. Then it adds those numbers and displays the sum with the column heading given.

For columns that contain date or time information, we can decide on the format for displaying the data using a variety of functions. For instance, suppose that we want to extract from the orders table the date that a customer made a particular purchase, based on his receipt number (e.g., 1250), which in turn is the record identification number, or sale_id. We could simply enter the following statement and get the default format as shown in the last line of results:

SELECT purchase_date AS 'Purchase Date' 
FROM orders
WHERE sale_id = '1250';

+---------------+
| Purchase Date |
+---------------+
| 2004-03-01    |
+---------------+

This format (year-month-day) is understandable. However, if we want the month displayed in English rather than numerically, we have to use some date functions:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Changing Data

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You can change data in a table using a few different methods. The most basic and perhaps the most common method is to use the UPDATE statement. With this statement, you can change data for all rows or for specific records based on a WHERE clause.

Looking back on the results displayed from an earlier query, we can see that Graham Greene’s book Brighton Rock has a copyright year of 1937. That’s not correct; it should be 1938. To change or update that bit of information, we would enter the following SQL statement:

UPDATE books
SET pub_year = '1938'
WHERE book_id = '2';
   
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

First, we state the name of the table that’s being updated. Next, we include the SET keyword with the column to change and its corresponding new value. If we wanted to change the values of more than one column, we would provide a comma- list of each column along with the equals sign operator and the new respective values. SET is given only once.

The preceding SQL statement has a WHERE clause limiting the rows that will be updated by specifying a condition the row must meet. In this case, our condition is for a specific value of a unique column, so only one row will be changed. The results of the query show that one row was affected, one row was matched, one row was changed, and there were no problems to generate warnings.

Sometimes inserting data into a table will cause a duplicate row to be created because a row for the data already exists. For instance, suppose that we want to run an SQL statement that inserts data on a few books into the books table and one of the books is already in the table. If we use INSERT, a duplicate row will generally be rejected. To prevent this, we can use the REPLACE statement, which inserts rows that are new and replaces existing rows with new data.

From MySQL’s perspective, duplicates occur only when columns defined as unique contain the same value. Because the book_id column is assigned automatically, it’s unlikely that we would duplicate it, because we wouldn’t tend to assign its value when adding records. What’s unique about each book in the book business is its ISBN number, which is the bar code number on the back of the book. To ensure that we do not have rows with the same ISBN number, we’ll alter our

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Deleting Data

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To delete specific rows of data, you can use the DELETE statement. For example, if we want to delete all rows of data from our books table for the author J. K. Rowling, because we’ve decided not to carry Harry Potter books, we could issue the following statement:

DELETE FROM books
WHERE author_id =
   (SELECT authors.author_id FROM authors
    WHERE author_last = 'Rowling' 
    AND author_first = 'J. K.');
   
DELETE FROM authors
WHERE author_last = 'Rowling' 
AND author_first = 'J. K.';

Here, we’re deleting only rows from the books table where the author identification number is whatever is selected from the authors table based on the specified author’s last name and first name. That is to say, the author_id must be whatever value is returned by the SELECT statement, the subquery contained in the parentheses. This statement involves a subquery, so it requires version 4.1 or later of MySQL. To delete these same rows with an earlier version of MySQL, you would need to run the SELECT statement shown in parentheses here separately (not as a subquery), make note of the author’s identification number, and then run the first DELETE statement, manually entering the identification number at the end instead of the parenthetical SELECT statement shown.

An alternative to the previous SQL statements would be to utilize user-defined . Here is the same example using variables:

SET @potter =
   (SELECT author_id FROM authors
    WHERE author_last = 'Rowling'
    AND author_first = 'J. K.');
   
DELETE FROM books
WHERE author_id = @potter;
   
DELETE FROM authors
WHERE author_id = @potter;

In the first part, we use the SET statement to establish a variable called @potter that will contain the results of the SELECT statement that follows in parentheses, another subquery. Incidentally, although this subquery is not available before version 4.1, user-defined variables are. The second SQL statement deletes the rows from books where the author identification number matches the value of the temporary variable. Next, we delete the data from the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Searching Data

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Once our database is loaded with large amounts of data, it can be cumbersome to locate data simply by scrolling through the results of SELECT statements. Also, sometimes we don’t have the exact or complete text for a column we’re examining. For these situations, we can use the LIKE operator. Suppose that our books table now has thousands of entries. Suppose further that a customer says he’s looking for a specific book. He can’t remember the name of the author or the title of the book, but he does remember that the words traveler and winter are in the title. We could enter this SQL statement to search the database based on this minimal information:

SELECT book_id, title,
CONCAT(author_first, ' ', author_last) AS author
FROM books
JOIN authors USING(author_id)
WHERE title LIKE '%traveler%'
AND title LIKE '%winter%';
   
+---------+-----------------------------------+---------------+
| book_id | title                             | author        |
+---------+-----------------------------------+---------------+
|    1400 | If on a winter's night a traveler | Italo Calvino |
+---------+-----------------------------------+---------------+

With the LIKE operator, we use the percent sign wildcard twice to indicate that we’re searching for all rows in which the title column’s data contains the string traveler with zero or more characters before it (the preceding percent sign), and zero or more characters after it (the terminating percent sign). Put another way, the word traveler must be contained somewhere in the column’s data to have a pattern match. The next par