Cover | Table of Contents | Colophon

Table of Contents

Chapter 1: Introduction to MySQL

Content preview·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 Public License (GPL); this caused its popularity to soar. The company that owns and develops MySQL is MySQL AB (the AB stands for aktiebolag, or stock company). Currently, MySQL AB estimates that there are more than 4 million installations of MySQL worldwide, and reports an average of 35,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 databases 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 http://www.mysql.com/it-resources/benchmarks for more details). For a database long dismissed by many people, 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 commands and the database's backend storage, is the critical software in any database management system. MySQL offers several storage engines—previously called table types—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. Whenever you visit MySQL AB's site to download MySQL, you will see a stable release that has been thoroughly tested. You will also see a distribution that has undergone testing, but contains components that have not been tested as thoroughly as the standard version. This version contains everything in the latest standard version plus new features that eventually will be rolled into the standard version. Each new release comes with speed and stability improvements, 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 Value of MySQL

Content preview·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 http://www.mysql.com/it-resources/benchmarks for more details). For a database long dismissed by many people, 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 commands and the database's backend storage, is the critical software in any database management system. MySQL offers several storage engines—previously called table types—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. Whenever you visit MySQL AB's site to download MySQL, you will see a stable release that has been thoroughly tested. You will also see a distribution that has undergone testing, but contains components that have not been tested as thoroughly as the standard version. This version contains everything in the latest standard version plus new features that eventually will be rolled into the standard version. Each new release comes with speed and stability improvements, 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 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 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 use is done by programs using a variety of languages. The interfaces for Perl, PHP, and C 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 different Unix socket files, and to make it easier to serve different sets of databases. For MS Windows NT and 2000 servers, there's mysqld-nt. It supports the named pipes that some Windows systems use instead of socket files.

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 http://www.perl.org to download and install a copy of Perl on non-Windows systems, and http://www.activestate.com/Products/ActivePerl to download and install a copy of ActivePerl on Windows systems.

MySQL also comes with a few utilities for importing and exporting data from and to MySQL databases. mysqldump is the most popular for exporting data and table structures to a plain-text file known as 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!

Licensing

Content preview·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 http://www.fsf.org/licenses 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 developed, 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 purchase a license, see http://www.mysql.com/company/legal/licensing.

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 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 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 http://lists.mysql.com. 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. Some subscribers, incidentally, like to use a special email address and name representing their online persona. It allows you anonymity and may make sorting emails easier. Others, however, prefer to use their real name 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. For example, if you have a problem with the Perl DBI module in relation to MySQL, you would send a message to

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 reprint rights for this chapter

Besides the mailing list archives mentioned in the last section, MySQL AB provides extensive online documentation of the MySQL server and all of the other software it distributes. You can find documentation at http://dev.mysql.com/doc. You can read the material online or download it in a couple of formats (e.g., HTML or PDF). It is also available in hardcopy format: MySQL Language Reference and MySQL Administrator's 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 (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 (2003). For common practical problem solving, there's MySQL Cookbook (2002) 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 application programming interfaces (APIs). For PHP development with MySQL, there's Web Database Applications with PHP and MySQL (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 Programming with JDBC and Java (2002).

In addition to 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 (http://www.onlamp.com/onlamp/general/mysql.csp). Incidentally, I've contributed a few articles to that publication on MySQL and related topics. I've also written many articles on MySQL for my column on Unix Review.com (http://www.unixreview.com/mysql). MySQL AB also provides some in-depth articles on MySQL. You can find them at

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 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 MySQL from MySQL AB's site (http://dev.mysql.com/downloads) or from one of its mirror sites (http://dev.mysql.com/downloads/mirrors.html).

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, Section 2.1; the section that applies to the distribution that you choose; and the Section 2.8 at the end of the chapter.

Before beginning to download an installation package, you must decide what version of MySQL to install. The best choice is usually the latest stable version recommended by MySQL AB on its site. It's not recommended that you install a newer version unless you need some new feature that is contained only in a newer 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 special configuration must be performed during 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.

For some distributions, you can download a Standard version, a Max version, or a Debug version. The Standard version is recommended for most users and developers, as it has been thoroughly tested.

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 reprint rights for this chapter

Before beginning to download an installation package, you must decide what version of MySQL to install. The best choice is usually the latest stable version recommended by MySQL AB on its site. It's not recommended that you install a newer version unless you need some new feature that is contained only in a newer 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 special configuration must be performed during 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.

For some distributions, you can download a Standard version, a Max version, or a Debug version. The Standard version is recommended for most users and developers, as it has been thoroughly tested.

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 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 Foundation's site (http://www.gnu.org/).

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 directory where MySQL is installed from the default, 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 latin1, use --with-charset. Here is an example of how you might configure MySQL with these particular options before building the binary files:

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 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 placed 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 Foundation's site (http://www.gnu.org).

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 process:

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.

At this point, MySQL is basically installed. Now you must generate the initial privileges or grant tables, and change the file ownership of the MySQL programs and datafiles. To do these tasks, enter the following from the command line:

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 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 RedHat Package Manager), it is recommended that you use a package instead of a source distribution. The differences between RPM versions are based not on the Linux distribution (e.g., SuSE or Mandrake), but 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 files. Their 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 the 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 the 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 postinstallation 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 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 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 graphical user interface (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, use the Finder utility to locate the disk image file (the .dmg file) that you downloaded, and mount it by double-clicking it. This reveals the disk image file's contents. Look for the PKG icon, and double-click it to 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.

Once you've finished installing MySQL, start the service by entering the following from the command line:

sudo /usr/local/mysql/bin/mysqld_safe
[Ctrl-z]
bg

On the second line, hold down the Ctrl key and then press the Z key. Finally enter bg to send the daemon's process to the background.

To have MySQL started at boot time, add a StartupItem. Within the disk image file that you downloaded, you should see an icon labeled MySQLStartupItem.pkg. Just double-click it, and it will create a StartupItem

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 reprint rights for this chapter

If your server is using Novell NetWare 6.0 or above, 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 (http://support.novell.com). You can find the latest version of LibC at http://developer.novell.com/ndk/libc.htm. Another requirement for installing MySQL is that the MySQL server and data be installed on an 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 NLM's 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 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 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 (http://www.winzip.com) 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.

Once you've started the installer, a dialog box appears that offers you three general choices. 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!

Postinstallation

Content preview·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. MySQL is 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 Chapter 10.

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 if the mysqld daemon is running, you should get a prompt like this:

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 reprint rights for this chapter

While the bulk of MySQL in a Nutshell contains reference information, which you can read in small segments as needed, this chapter presents a basic tutorial on MySQL. It explains how to log in to the MySQL server, create databases, and enter and manipulate data within them. This tutorial does not cover everything about MySQL. 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 for interacting with the MySQL server and, thereby, developing or working with a database. The most basic interface that you can use, though, is the mysql client. With it, you may interact with the server from either the command line or what is sometimes called the monitor.

If MySQL was installed properly on your server, mysql should be available to run. If not see Chapter 2. If you used the default installation method, the mysql program resides at /usr/local/mysql/bin/mysql. On Unix systems, be sure that mysql is in your path by typing:

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

Assuming that everything is working, you will also need a MySQL username and an accompanying 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. See Chapter 2 to learn how to set passwords, and to create new users and grant them privileges.

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

mysql -h host -u user -p

If you're logging in locally—that is to say, from the server itself—either physically or through a remote login method, such as Telnet or the 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 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!

The mysql Client

Content preview·Buy reprint rights for this chapter

There are various methods for interacting with the MySQL server and, thereby, developing or working with a database. The most basic interface that you can use, though, is the mysql client. With it, you may interact with the server from either the command line or what is sometimes called the monitor.

If MySQL was installed properly on your server, mysql should be available to run. If not see Chapter 2. If you used the default installation method, the mysql program resides at /usr/local/mysql/bin/mysql. On Unix systems, be sure that mysql is in your path by typing:

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

Assuming that everything is working, you will also need a MySQL username and an accompanying 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. See Chapter 2 to learn how to set passwords, and to create new users and grant them privileges.

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

mysql -h host -u user -p

If you're logging in locally—that is to say, from the server itself—either physically or through a remote login method, such as Telnet or the 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 username. The -p option instructs mysql to prompt you for a password. You can also add the password to the end of the -p option (e.g., enter -prover where rover is the password); there's no space between -p and the password. However, entering the password on the command line is not a good security practice, because it displays the password on the screen and it transmits the password as clear text through the network, as well as making it visible whenever somebody gets a list of processes running on the machine.

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 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 regards to reserved words for SQL statements and clauses. Database and table names are case-sensitive on operating systems that are case-sensitive, such as Unix 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 this SQL statement 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, we can switch the default database for the session to the new database like so:

USE bookstore;

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

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

This SQL statement creates the table books with three columns. The first column is a simple identification number for each record. It has an integer type. Incidentally, fields are referred to as columns and records as rows in MySQL. The data-type for the second and third columns consists of character fields of variable widths—up to 50 characters each. Notice that the list of columns is contained within parentheses.

To see the results of the table we just created, enter 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!

Show Me

Content preview·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 like so:

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

The result of the SHOW DATABASES statement lists not only the database that we've created, but also two others. One is the mysql database, which contains data on user privileges. This was covered in Chapter 2. The third one listed 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 SHOWTABLES 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:

USE bookstore;
SHOW TABLES FROM mysql;

This displays a list of tables from the mysql database while still anchoring the client in 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 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 statement. With INSERT, we can add one or more records at a time. Before adding information on 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('Vernon','Olympia','USA');

After adding an entry for the author, we can insert an entry for the book:

INSERT INTO books
(title, author_id, isbn, genre, pub_year)
VALUES('Eden', LAST_INSERT_ID( ),'0802117287','novel','2003');

With the first SQL statement, we've added a record or row for Olympia Vernon, an author I love who wrote the book Eden. The standard INSERT syntax is to name 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. In the second SQL statement, we've 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. We are getting the author_id number for the row just inserted in the previous statement by using the LAST_INSERT_ID() function.

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 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 and rows, we name the columns we want and add a WHERE clause to the end of our SELECT statement:

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

This SQL statement displays just the record 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 like this:

SELECT books.rec_id, title, pub_year, 
       CONCAT(author_first, ' ', author_last) AS author
FROM books, authors
WHERE author_last = 'Vernon'
   AND author_id = authors.rec_id;

Both tables have columns called rec_id, so we need to specify the table to which we're referring whenever we refer to rec_id and are joining both tables. We do this by inserting the name of the table followed by a dot as a separator and then the column name. You can see an example of this in the first line, where we're selecting the record identification number for each book. Notice also in the second line 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, and then the author's last name onto the end of that. 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 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 books.rec_id, title, publisher
FROM books, authors, publishers
WHERE author_last = 'Shakespeare'
   AND genre = 'play'
   AND author_id = authors.rec_id
   AND publisher_id = publishers.rec_id
ORDER BY title, pub_year;

The ORDER BY clause comes at the end, after the WHERE clause. First, we're ordering the data results 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.

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 like so:

SELECT books.rec_id, title
FROM books, authors, publishers
WHERE author_last = 'Shakespeare'
   AND genre = 'play'
   AND author_id = authors.rec_id
   AND publisher_id = publishers.rec_id
ORDER BY title, pub_year
LIMIT 20;

This addition will limit 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 would replace the LIMIT clause in the SQL statement with this one:

...
LIMIT 20, 10;

As you can see, in a two-argument clause, the first argument specifies the number of rows to skip or the point to begin (i.e., 20) and the second argument states the number of rows to display (i.e., 10).

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 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 Tolstoy. We could enter a SELECT statement containing a COUNT() function like this:

SELECT COUNT(*)
FROM books, authors
WHERE author_last = 'Tolstoy'
   AND author_id = authors.rec_id;
   
+----------+
| 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, books, authors
WHERE  title = 'Armadillo'
   AND author_last = 'Boyd'
   AND book_id = books.rec_id
   AND author_id = authors.rec_id;
   
+-----------------+
| Armadillo Sales |
+-----------------+
|          250.25 |
+-----------------+

Here we are joining three tables together to retrieve the desired information. MySQL is selecting 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. Most column names appear in only one table, so MySQL knows what we mean even if we don't specify the table each column is in; for a couple of columns we need to use the table.column format.

For columns that contain date or time information, we can format how the data is displayed 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 rec_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 rec_id = '1250';
   
+---------------+
| Purchase Date |
+---------------+
| 2004-03-01    |
+---------------+

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 reprint rights for this chapter

You can change data in a database using a few different methods. The most basic and perhaps the most common method is to use the UPDATE statement. With an UPDATE 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 Olympia Vernon's book Logic has a copyright year of 2003. That's not correct; it should read 2004. To change or update that bit of information, enter the following SQL statement:

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

First, name the table that's being updated. Next, issue the SET keyword with the column to change and its corresponding new value. If we want to change the values of more than one column, we would provide a comma-separated list of each column along with the equals-sign operator and the new respective values. SET is declared only once, by the way.

This statement has a WHERE clause in which we're limiting the rows that will change 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 errors 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, we'll end up with a duplicate row. To prevent this, we can use the REPLACE statement, which inserts new rows and replaces existing rows with new data. From MySQL's perspective, duplicates occur only when unique columns would contain the same value. Because the rec_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 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 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 just don't want that kind of business), we could issue the following statement:

DELETE FROM books
WHERE author_id =
   (SELECT authors.rec_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 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 statement would be to utilize user-defined variables. Here is the same example using variables:

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

In the first stanza, 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 authors table, still making use of the variable. A user-defined variable will last until it's reset or until the MySQL session is closed.

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 reprint rights for this chapter

Once our database is loaded with large amounts of data, it can be cumbersome to locate data by simply scrolling through the results of SELECT statements. Also, sometimes we don't have the exact or complete text for a column in which we're looking. 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 author or the title, but he does remember that the words traveler and winter are in the title. We could enter this statement to search the database based on this minimal information:

SELECT books.rec_id, title,
       CONCAT(author_first, ' ', author_last) AS author
FROM books, authors
WHERE title LIKE '%traveler%'
   AND title LIKE '%winter%'
   AND author_id = authors.rec_id;
   
+--------+-----------------------------------+---------------+
| rec_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 starts with zero or more characters before the pattern of traveler is found, and then zero or more characters may follow. Put another way, the word traveler must be contained somewhere in the column's data to have a pattern match. Also, winter must be found in the column. Incidentally, the LIKE keyword is an operator. For more information on operators, see Appendix B.

If another customer asks us to search the database for a book with either the word Ford or Chevrolet in the title, we could use the OR operator within an expression like so:

SELECT books.rec_id, title,
       CONCAT(author_first, ' ', author_last) AS author
FROM books, authors
WHERE title LIKE '%Ford%' AND author_id = authors.rec_id
OR title LIKE '%Chevrolet%' AND author_id = authors.rec_id;

You can find more examples and possibilities for searching data in Chapter 4.

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

Importing Data in Bulk

Content preview·Buy reprint rights for this chapter

While the INSERT and REPLACE statements are useful, they can be time-consuming when you're entering a large number of rows, because they're somewhat manual methods of entering data. Often, when setting up a new database, you will need to migrate data from an old database to MySQL. In the case of our bookstore, let's suppose that a vendor has sent us a disk with a list of all of their books in a simple text file. Each record for each book is on a separate line and each field of each record is separated by a vertical bar. Here's how the fictitious vendor's data text file looks:

ISBN|TITLE|AUTHOR LAST|AUTHOR FIRST|COPYRIGHT DATE|
067973452X|Notes from Underground|Dostoevsky|Fyodor|August 1994|
...

Obviously, an actual vendor file would contain more fields and records than are shown here, but this is enough for our example. The first line contains descriptions of the fields in the records that follow. We don't need to extract the first line; it's just instructions for us. So, we'll tell MySQL to ignore it when we enter our SQL statement. As for the data, we must consider a few problems: the fields are not in the order that they are found in our tables. We'll have to tell MySQL the order in which the data will be coming so that it can make adjustments. The other problem is that this text table contains data for our books table and our authors table. This is going to be a bit tricky, but we can deal with it. What we'll do is extract the author information only in one SQL statement, then we'll run a separate SQL statement to import the book information. To start, we will copy the vendor's file called books.txt to the /tmp directory, and then we will run a LOAD DATA INFILE statement from the mysql client:

LOAD DATA INFILE '/tmp/books.txt' REPLACE INTO TABLE authors
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'
TEXT_FIELDS(col1, col2, col3, col4, col5)
SET author_last = col3, author_first = col4
IGNORE col1, col2, col5, 1 LINES;

First, I should point out that the TEXT_FIELDS and the IGNORE clause for columns are not available before Version 4.1 of MySQL. The IGNORE n LINES

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

Command-Line Interface

Content preview·Buy reprint rights for this chapter

It's not necessary to open a MySQL monitor to enter SQL statements into the MySQL server. In fact, sometimes you may have only a quick query to make in MySQL, and you'd rather just do it from the shell or command line. For instance, suppose we have a table called vendors in our database, and we want to get a quick list of vendors in Louisiana and their telephone numbers. We could enter the following from the shell in Linux (or an equivalent operating system) to get this list:

mysql --user='tina' --password='muller' \
-e "SELECT vendor, telephone FROM vendors \
    WHERE state='LA'" bookstore

The mysql command or interface is called on, although we're not entering monitor mode. Next, we provide the username tina and the password muller. This line ends with a backslash to let the Unix shell know that there are more parameters to come. Otherwise, we would need to put all of this on one line. On the second line we use the -e switch to indicate that what follows it in quotes is to be executed by the mysql client. Notice that what's in double quotes is the same SQL statement in the same syntax as we would enter it in monitor mode. Finally, we provide the name of the database