An introduction to MySQL

MySQL is a powerful, standardized, time-tested database system. It can be daunting to use if you're not familiar with some basic concepts and paradigms.

To learn how to begin using MySQL, follow the steps in this tutorial.

Installing MySQL

There are many different ways to install MySQL. If you are on a system that uses a package manager, like APT, RPM, or dpkg, you can install MySQL from a system-specific package. Or, you can install generic binaries, which are not optimized for your system but should work fine. Or you can compile the source yourself.

At a minimum, you need to install the MySQL client and the MySQL server. These can run on the same system or different systems.

Installing MySQL using APT

If you are running Ubuntu, Debian, or another system that uses APT package management, the packages you want to install are called mysql-client and mysql-server.

We use apt-get to install the packages, and when installing, we prefix our commands with sudo to run them with root privileges.

To install the client, we can use the command:

sudo apt-get install mysql-client

…and on the system which functions as the MySQL server, we can install the server software with:

sudo apt-get install mysql-server

…or, if both are running on the same system, we can install them both with one command:

sudo apt-get install mysql-client mysql-server

To install a specific version of MySQL, specify the version number in the package name. For instance,

sudo apt-get install mysql-client-5.5

…installs the client for MySQL version 5.5. You can check which versions are available using the apt-cache tool to search for a partial package name, for instance:

apt-cache search mysql-client

…lists all packages with mysql-client in the name.

Once apt-get installs the packages and their dependencies, an initial database is created, and a configuration file is created at /etc/mysql/my.cnf. An init script for starting and stopping the MySQL service is installed at /etc/init.d/mysql. The server is started automatically after installation.

You can manually start and stop the server using the service command. For example:

sudo service mysql start

…attempts to start the MySQL server, and:

sudo service mysql stop

…attempts to stop it.

Installing MySQL with RPM

If you are running Red Hat, Fedora, CentOS, or another system that uses RPM (Red Hat Package Manager) package management, there are three main packages to install: mysql for the client tools, mysql-server for the server and associated tools, and mysql-libs for the MySQL libraries, which are required if you are going to be connecting to the MySQL server using Perl, Python, or other languages.

To install the RPM packages, use the yum command. For instance,

sudo yum install mysql mysql-server mysql-libs

…installs all three packages on a single system. A sample configuration file is installed at the location /etc/my.cnf, and an init script to start and stop the server is installed to /etc/init.d/mysqld. To start the service (called mysqld, for MySQL daemon), use the service command. For instance:

service mysqld start

…starts the MySQL server daemon, and:

service mysqld stop

…stops it.

Installing MySQL from a Debian package

To install the MySQL binaries using a Debian package, you can download a .deb file from this URL (uniform resource locator). To install the package, use the dpkg utility.

For instance, to install a package named mysql-apt-config_0.3.2-1debian7_all.deb, we would use this command:

sudo dpkg -i mysql-apt-config_0.3.2-1debian7_all.deb

You may also need to install the libaio library if it's not already installed on your system. This provides necessary software for enabling asynchronous Input/Output. The easiest way to install this library is with apt-get, for instance:

sudo apt-get install libaio1

The other details of this type of installation are identical to installing MySQL using APT (init script name and location, etc).

Installing generic binaries from a tar archive

Installing MySQL generic binaries from a tarball is a bit more complicated, but if you don't use a package manager and you don't want to compile from source, here's how to go about it.

First, download the tarball from the MySQL official website. Select the appropriate software for your OS (operating system) and CPU (centeral processing unit), and download the tar.gz file. Make sure you have the appropriate version of C libraries (glibc) installed; for instance, MySQL version 5.6.21 requires glibc 2.5. You can determine what version of glibc your OS uses with the following command:

ldd --version

This gives you output resembling the following:

ldd (Debian GLIBC 2.19-13) 2.19

Here, we have glibc version 2.19 installed, so any version of MySQL we download should also use that version. The version of glibc is listed with the MySQL download.

If you can't find a version of MySQL that uses your current version of glibc, we recommend you find another way to install MySQL, such the package manager methods listed above. Manually upgrading glibc is very risky because other programs depend on it.

If you find a version that matches your glibc version, however, here are the steps for configuring your system and installing MySQL from the tarball.

First, you need to create the mysql group if it does not already exist. You can find out if it exists:

cut -d: -f1 /etc/group | grep mysql

If the mysql group already exists, the above command prints:

mysql

If the command does not give this result, you need to create the mysql group:

sudo groupadd mysql

Now that the mysql group is added, you need to create a user named mysql. First, check to see if it already exists:

cut -d: -f1 /etc/passwd | grep mysql

If this command does not return:

mysql

…then you need to create the mysql user, and add it to the mysql group:

sudo useradd -r -g mysql mysql

Next we unpack the MySQL tar archive. Here, we assume the file is named mysql-5.1.73-linux-x86_64-glibc23.tar.gz, and that it is located in the directory /home/yourusername/downloads.

First, choose where to install MySQL. Here, we choose the directory /usr/local, a standard location. In our installation, making changes to this directory requires root privileges, so we execute many of the following commands using sudo.

First, we'll cd to /usr/local:

cd /usr/local

Now, we unpack the tar archive into this directory, which creates an installation directory containing all the tarball's contents:

tar zxvf /home/yourusername/downloads/mysql-5.1.73-linux-x86_64-glibc23.tar.gz

Next we create a symbolic link to this directory called mysql, using the ln command:

ln -s mysql-5.1.73-linux-x86_64-glibc23 mysql

Now we edit the configuration file, create the core databases, start the MySQL server, and set up passwords for the essential MySQL users.

First, we cd into our base MySQL directory:

cd /usr/local/mysql

This directory contains several important subdirectories. The key directories for our purposes are bin, which contains the client and server programs, and scripts, which contains the mysql_install_db program, which we use to initialize the database containing the grant tables which store the server access permissions.

We should add /usr/local/mysql to our PATH environment variable. If you are using bash (the default command shell on most versions of Linux), you ant to add the line PATH=$PATH:/usr/local/mysql/bin to the .profile file in your home directory, which bash runs every time you log in:

echo 'PATH=$PATH:/usr/local/mysql/bin' >> ~/.profile

Or, if you want to add this to the PATH environment variable for all users on your system, you can use this command:

sudo echo 'PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile

Now let's make sure the mysql user and group are the owners of everything we installed:

sudo chown -R mysql /usr/local/mysql/.

sudo chgrp -R mysql /usr/local/mysql/.

Now we run the mysql_install_db program to set up the initial MySQL grant tables which define how users can access the MySQL databases. This process is performed automatically in other installation procedures, such as using a package manager. Here we do it manually.

sudo /usr/local/mysql/scripts/mysql_install_db --user=mysql

sudo scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql \
                              --datadir=/usr/local/mysql/data

Here, the backslash ("\") indicates that the following line is part of the same command. You can omit this when running the command if you want.

When mysql_install_db runs successfully, it creates the server's data directory with mysql as the owner. Under the data directory, it creates directories for the database which holds the grant tables and the test database you can use to test MySQL. It also creates privilege table entries for the root and anonymous MySQL user accounts. These accounts have no password initially (more on that in a moment). These default privileges permit the MySQL root account to do anything, and permit any MySQL user to create or use databases with a name of test or a name starting with test_.

Now we start the MySQL server:

sudo bin/mysqld_safe --user=mysql &

At this point, anyone can access MySQL as the MySQL root user, because it has no password associated with it. This is very insecure, so we should set a password for the MySQL root user. To do this, we launch the mysql client and issue the appropriate commands. Start the mysql client as the MySQL root user:

mysql -u root

After a brief hello message, you are presented with the mysql> prompt. At this prompt, enter the following commands, replacing newrootpwd with the password you'd like to set for MySQL root, and thishostname with the hostname of your system:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newrootpwd');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newrootpwd');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('newrootpwd');
mysql> SET PASSWORD FOR 'root'@'thishostname' = PASSWORD('newrootpwd');

You can now exit the mysql client by issuing the exit command:

mysql> exit

Bye

To check that the MySQL server is running, we can use the mysqladmin program using this command, for example:

/usr/local/mysql/bin/mysqladmin version -u root -p

Here, we are specifying that the command should run as the MySQL root user (-u root) and that it should prompt us for that user's password (-p). If you omit -p, MySQL tries to authenticate root without using a password, and fails. Enter the MySQL root password (which you set in the commands above), and you receive output resembling the following:

mysqladmin  Ver 8.42 Distrib 5.1.73, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version        5.1.73-1
Protocol version      10
Connection            Localhost via UNIX socket
UNIX socket           /var/run/mysqld/mysqld.sock
Uptime:               2 min 52 sec
Threads: 1  Questions: 120  Slow queries: 0  Opens: 171  Flush tables: 1  
Open tables: 41  Queries per second avg: 0.001

Also, verify you can shut down the server, using this command:

sudo /usr/local/bin/mysqladmin -u root -p shutdown

You are prompted for the MySQL root user password.

To start the server again, we can use the same command as above:

sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &

Compiling MySQL from source

Building MySQL from source lets you fully customize and optimize your MySQL operation. This process is significantly more complicated (and time-consuming), and is usually not required; MySQL binary distributions come in many varieties to cover the vast majority of system hardware and operating system software. However, if you want to build from source, these are the steps to follow.

The recommended compilation tool for building MySQL is cmake. If you don't already have cmake installed, download and install it. You can use a package manager to install it, for example, with apt-get:

sudo apt-get install cmake

For other ways to download and install cmake, you can reference the instructions at the CMake official website.

There are two main source trees to choose from when building MySQL: the standard distribution, and the development tree. The development tree is the bleeding-edge, most-recent (and least tested!) source code, and we do not cover that installation process here. If you're installing the development source yourself, do more research than can be covered in this page. Here, we cover building from the standard distribution.

First, create a mysql user and group, as covered in the previous section:

sudo groupadd mysql

sudo useradd -r -g mysql mysql

Next, download a .tar.gz source archive from this URL suitable for your hardware and operating system, and make sure it uses your installed version of the glibc C libraries, as covered in the previous section. Extract the archive wherever you want:

tar zxvf mysql-VERSION.tar.gz

Change into the top-level directory of the distribution:

cd mysql-VERSION.tar.gz

Next, configure the source directory with cmake. To use the default configuration, run:

cmake .

Or, if you want to use a custom configuration, you can specify various options when running cmake. For instance,

• to configure the source with the same build options used to produce binary distributions for official MySQL releases, run:

  cmake . -DBUILD_CONFIG=mysql_release

• …or, to configure the distribution for installation to a particular custom location:

  cmake . -DCMAKE_INSTALL_PREFIX=custom-location-directory-name

• …or, to configure the distribution for full debugging support:

  cmake . -DWITH_DEBUG=1

Next, once the source is configured, build it with make:

make

Then install the distribution with make install:

sudo make install

From here, the remainder of the steps are identical to the post-installation steps covered in the section above: setting up the configuration file, creating core databases, starting the server, and setting the root password.

Connecting to and disconnecting from the server

Once MySQL is installed, the next basic step is connecting to (and disconnecting from) the server. This requires a username; if no other user is configured, you can log in as root.

The general form of the login command is:

mysql -h hostname -u username -p

The -p means "log in with a password." You can enter the password in plaintext on the command line if you specify it after the -p (for instance, -pmypassword, with no space between them), but this is very insecure since then your password is visible in your command history and in the process name itself. It's better to use -p and let mysql prompt you to type in the password at a prompt.

If the server is running on the system you're currently logged in to (localhost), you can omit the -h hostname option and connect with this simpler command:

mysql -u username -p

So, to log in to the server on localhost as root, the command would be:

mysql -u root -p

If this works, you are given some introductory information and be presented with the mysql> prompt:

Server version: 5.5.39-1 (Debian)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

If instead, you receive a message like this:

ERROR 2002 (HY000): Can't connect to local MySQL server through 
socket '/var/run/mysqld/mysqld.sock' (2)

…this indicates that the MySQL service is not running. As in the examples above, you can start the service using the service command:

sudo service mysql start

…or, if your installation named the service mysqld (see the installation instructions):

sudo service mysqld start

Alternatively, you can invoke the mysqld_safe command:

sudo mysqld_safe --user=mysql &

Once the server is running, try to log in again, and it should work.

Once you are at the mysql> prompt, you can disconnect and exit the mysql client using the command QUIT:

mysql> QUIT

…and mysql says:

Bye

…and return you to your shell's command prompt.

Entering queries

Once you are connected to the server and at the mysql> prompt, you can start to perform basic queries. Here, "querying" means to ask MySQL for information.

Here's an example of a simple query which asks MySQL for its version number, and the current date:

mysql> SELECT VERSION(), CURRENT_DATE;

Entering this query returns a result resembling the following:

+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 5.5.39-1  | 2014-11-23   |
+-----------+--------------+
1 row in set (0.00 sec)

…and then return you to another mysql> prompt.

There are a few important things to notice about the above example:

• A MySQL command is usually followed by a semicolon. There are a few exceptions, however, such as the QUIT and USE commands.
• When you issue a query, MySQL responds and then returns you to another mysql> prompt.
• MySQL displays output in tabular form (in a table, with rows and columns). The first row contains labels for the contents of each column. Each row below the first row contains the results of the query. Normally, the labels are the names of the columns you requested from database tables. If, as in this example, your query requested the value of expressions, each label is the name of an expression in your query, and the values in the rows below represent the evaluation of that expression.
• MySQL tells you how many rows were returned and how long it took the server to return the result of the query. This gives you an idea about the server's response time, a crucial piece of information when evaluating the server's performance. Response time can be affected by factors such as server load and network latency.

When you're making a query, each keyword can be in uppercase, lowercase, or any mixture of the two. For instance, the following queries are all equivalent:

mysql> SELECT VERSION(), CURRENT_DATE;

mysql> select version(), current_date;

mysql> SeLeCt vErSiOn(), current_DATE;

Here is another query which asks for the value of two expressions. This time, we're doing some simple math:

SELECT SIN(PI()/4), (4+1)*5;

+------------------+---------+
| SIN(PI()/4)      | (4+1)*5 |
+------------------+---------+
| 0.70710678118655 |      25 |
+------------------+---------+
1 row in set (0.02 sec)

This example hopefully illustrates the "L" in "MySQL": at its core, MySQL is a language, like any scripting or programming language.

You can combine more than one statement in a single line, by ending each one with a semicolon. For example:

mysql> SELECT VERSION(); SELECT NOW();
+-----------+
| VERSION() |
+-----------+
| 5.5.39-1  |
+-----------+
1 row in set (0.00 sec)
+---------------------+
| NOW()               |
+---------------------+
| 2014-11-24 00:04:49 |
+---------------------+
1 row in set (0.00 sec)

Notice how this output differs from our previous examples, where two evaluations were part of a single query statement. There, the output was given in one table with two tables; here, the output is given in two separate tables of one column each.

It's also possible to give a single command on more than one line. MySQL determines where your statement ends by the semicolon, not by the end of the line.

Here's an example of a multi-line statement:

mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;

+----------------+--------------+
| USER()         | CURRENT_DATE |
+----------------+--------------+
| root@localhost | 2014-11-24   |
+----------------+--------------+
1 row in set (0.03 sec)

In this example, we typed the following: SELECT (enter) USER() (enter) , (enter) CURRENT_DATE; (enter). Notice how the first three times we pressed enter, MySQL changed the prompt to -> and allowed us to keep typing. This is how it indicates that it has not yet seen a complete statement and is waiting for the rest.

If you're in the midst of typing a statement and you want to abort it, you can end it with \c and press Enter. MySQL ignores everything you've typed so far, and give you a fresh prompt to start over. For example:

mysql> SELECT
    -> USER()
    -> \c
mysql>

Think of \c as the opposite of a semicolon: it indicates the end of a command, but tells MySQL to ignore the command rather than execute it.

We've seen two types of MySQL prompts so far: mysql>, indicating that MySQL is ready for a new statement, and ->, indicating that MySQL is ready for you to continue the statement from the previous line. Here is a handy list of the prompts available while using MySQL, and a description of what they indicate:

Multi-line statements happen if you press Enter and forget to use the terminating semicolon. If you are entering a command, and forget the semicolon:

mysql> SELECT()
    ->

…you can enter a semicolon at the -> prompt to complete the command:

mysql> SELECT USER()
    -> ;

+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

The '> and "> prompts occur during string collection. They indicate you've started a string with a single or double quote on a previous line, and haven't terminated the string with a corresponding quote. For instance, the following command was typed incorrectly:

mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '>

In the command above, the string Smith should be enclosed in single quotes, but we forgot to type in the second quote. Therefore when we pressed enter, MySQL responded with '> telling us that it's waiting for us to close the string. We need to cancel this command — how do we do that?

We can't type \c at the '> prompt, because MySQL thinks it's part of the string we started with our first single-quote. So, to cancel the command, we should type in '\c like this:

mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '> '\c
mysql>

This terminates the string and instructs MySQL to cancel the command. We can see by the fresh mysql> prompt that it's ready for a new command.

The '>, ">, and `> prompts are important to understand because they let you know that until you terminate the string or identifier you began with ',", or `, MySQL does not understand your commands — even the QUIT command.

Creating and using a database

With the knowledge of how to enter commands, let's go over how a database is created and accessed.

A database is, in essence, a table of information. For instance, let's say you have a lot of pets: cats, dogs, hamsters, birds, sugar gliders, ferrets, etc. You want to store a bunch of information about them in a logical way. You can store this information in a database, and then query the database for that information later. The database server looks up the information based on the criteria you provide, and give you the answer to the question you're asking.

To create our database, we need to know how to:

• create a database
• create a table in that database
• load data into the table
• retrieve data from the table in various ways
• use multiple tables

First, let's see what databases already exist on our server. To do this, we use the SHOW command.

mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.09 sec)

information_schema and performance_schema are built-in databases that MySQL uses to monitor itself. mysql is a built-in database that describes user access privileges. These are databases like any that we create ourselves, and when we create databases, they are listed right with these.

Let's create a database of our own, and call it pets. We use this to store information about our fictional menagerie of domesticated animals.

mysql> CREATE DATABASE pets;

Query OK, 1 row affected (0.00 sec)

Database names are case-sensitive, unlike MySQL commands. So once we've named our database pets, we always refer to it with lowercase letters; "Pets", "PETS", etc., does not work.

We've created our database pets, but we can't use it yet; it hasn't been selected for use. We must tell MySQL that it's the database we want to use in our subsequent commands:

mysql> USE pets

Database changed

Notice that there was no semicolon at the end of our USE statement; USE, like QUIT, is one of a handful of special MySQL commands that is not terminated with a semicolon.

Your database only needs to be created once, but it needs to be selected for use with a USE statement each session before it can be accessed. Alternatively, you can select the database on the command line when you launch the MySQL client; specify the database name after any connection options. For instance:

mysql -h hostname -u username -p pets

This is equivalent to launching mysql and entering USE pets at the mysql> prompt.

At any time when you're running mysql, you can determine which database you're using with this command:

SELECT DATABASE();

Which returns, for example:

+------------+
| DATABASE() |
+------------+
| pets       |
+------------+
1 row in set (0.00 sec)

Creating a table

At this point, our database is empty (it contains no tables). We can check this with the SHOW TABLES; command:

mysql> SHOW TABLES;

Empty set (0.00 sec)

Before we start populating our database with information, we need to decide what structure should take: what tables it should have, and what columns should appear in each table.

At a minimum, we should probably include the name of each pet. Species is another good candidate, and gender. We could also include the color of the pet, an immediately noticeable identifying quality.

What about the age of each pet? This information is less good, because it changes often and it would require us to update the information whenever it changes. However, we could store each pet's birthday. MySQL provides functions for performing date arithmetic, so it would not be difficult to calculate the date of any of our pets on the fly. Storing birthday rather than age has other advantages, too:

• You can use the database for tasks such as generating reminders for upcoming pet birthdays.
• You can calculate age related to dates other than the current date. For example, if one of our pets passes away, we can record the date it died and use their birthday to calculate their age when they passed.

That's a good set of information to start with: name, species, gender, color, birth date, and death date.

Let's create our table. We can call it pet. We accomplish this with the command CREATE TABLE, specifying the layout of the table in the command:

mysql> CREATE TABLE pet (name VARCHAR(20), species VARCHAR(20), 
    -> gender CHAR(1), color VARCHAR(20), birth DATE, death DATE);

Query OK, 0 rows affected (0.47 sec)

VARCHAR(20) means that those variables are a string of characters of variable length, up to 20 characters. CHAR(1) means that our gender is represented by a single character (M or F, probably). DATE is a special variable type that holds a date. If we want to change any of these types later, we can use MySQL's ALTER TABLE command.

We can verify that our table was created with the SHOW TABLES; command:

mysql> SHOW TABLES;

+----------------+
| Tables_in_pets |
+----------------+
| pet            |
+----------------+
1 row in set (0.00 sec)

To verify the table was created the way we specified, we can use the DESCRIBE command:

mysql> DESCRIBE pet;

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| gender  | char(1)     | YES  |     | NULL    |       |
| color   | varchar(20) | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.16 sec)

Loading data into a table

Now we need to populate our table with data. The LOAD DATA and INSERT statements get this done.

Let's say this is the information we want to plug into our pet table:

Note that the dates are formatted as YYYY-MM-DD; this is how MySQL expects them, so make sure to format your dates this way.

The easiest way to quickly populate this information into our table is to create a text file containing all the information and import it. Let's name the text file pets.txt, and edit it to contain all this information with each row of data on its own line with the fields delimited by a tab character. Our text file looks like this:

Steven    cat             M    butterscotch    2010-02-05    \N
Fluffy    dog             F    golden          2007-06-02    \N
Grendel   sugar glider    M    gray            2012-04-08    \N
Blue      ferret          M    brown           2010-12-25    \N
Alfred    hamster         M    beige           2013-09-04    \N
Spike     parrot          F    blue            2011-11-12    \N
Sandy     goldfish        F    orange          2014-03-22    2014-06-15
Polenta   python          F    green           2012-10-01    \N

Here, the information is separated not by spaces, but by a single tab character. So realize that if you recreate this example and view it, it may look slightly different on your screen, as tabs can be displayed in different ways. It's important you use tabs, not spaces, between the information fields; if you use spaces, they are interpreted literally by MySQL and the data is not imported correctly.

Note the \N entries; those represent NULL, and tell MySQL that there is no entry (yet) for that information. Note also that tab is merely the default delimiter for imported information; if necessary, you can specify a different delimiter character when using LOAD DATA.

Save and close the file; now, let's import it into the table using LOAD DATA:

mysql> LOAD DATA LOCAL INFILE 'pets.txt' INTO TABLE pet;

Query OK, 8 rows affected (0.18 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

This is merely the most convenient way to load data into your table. If instead you prefer to load the data manually row by row, you can use the IMPORT command, like this:

mysql> INSERT INTO pet
    -> VALUES ('Steven','cat','M','butterscotch','2010-02-05',NULL);

Note that when you add data with INSERT, strings (even single-character strings) are quoted, and NULL is inserted directly, rather than with the string \N.

We can verify that our data was added to the table by retrieving all data with the command:

mysql> SELECT * FROM pet;

+---------+--------------+--------+--------------+------------+------------+
| name    | species      | gender | color        | birth      | death      |
+---------+--------------+--------+--------------+------------+------------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL       |
| Fluffy  | dog          | F      | golden       | 2007-06-02 | NULL       |
| Grendel | sugar glider | M      | gray         | 2012-04-08 | NULL       |
| Blue    | ferret       | M      | brown        | 2010-12-25 | NULL       |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL       |
| Spike   | parrot       | F      | blue         | 2011-11-12 | NULL       |
| Sandy   | goldfish     | F      | orange       | 2014-03-22 | 2014-06-15 |
| Polenta | python       | F      | green        | 2012-10-01 | NULL       |
+---------+--------------+--------+--------------+------------+------------+
8 rows in set (0.04 sec)

Retrieving data from a table is covered in greater detail in the next section.

Retrieving information from a table

The SELECT statement is used to pull information from a database table. It takes this general form:

SELECT what-to-select FROM which-table WHERE conditions-to-satisfy

• what-to-select specifies the columns of information you want to pull, including a list of column names or an asterisk ("*") meaning "all columns."
• which-table names the table of where you want to pull data.
• The WHERE clause is optional. If you include it, conditions-to-satisfy represents one or more conditions the information must satisfy to qualify for retrieval.

Selecting all data

The simplest way to select data is with SELECT *, as we did above. Let's look at it again:

mysql> SELECT * FROM pet;

+---------+--------------+--------+--------------+------------+------------+
| name    | species      | gender | color        | birth      | death      |
+---------+--------------+--------+--------------+------------+------------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL       |
| Fluffy  | dog          | F      | golden       | 2007-06-02 | NULL       |
| Grendel | sugar glider | M      | gray         | 2012-04-08 | NULL       |
| Blue    | ferret       | M      | brown        | 2010-12-25 | NULL       |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL       |
| Spike   | parrot       | F      | blue         | 2011-11-12 | NULL       |
| Sandy   | goldfish     | F      | orange       | 2014-03-22 | 2014-06-15 |
| Polenta | python       | F      | green        | 2012-10-01 | NULL       |
+---------+--------------+--------+--------------+------------+------------+
8 rows in set (0.04 sec)

This is a useful way to look at all the data in your table if (for instance) you want to verify that it has imported correctly. Let's say we imported our data and we realized that Grendel was born in May, not April, so we need to change Grendel's birth date. Here are the two simplest ways to fix this:

• Edit pets.txt, fix the error, and save and close the file. Back in mysql, empty the table with DELETE:
  
  

  mysql> DELETE FROM pet;

  Query OK, 8 rows affected (0.04 sec)

  Optional: verify that the table is now empty:
  
  

  mysql> SELECT * FROM pet;

  Empty set (0.00 sec)

  …and import the revised data from pet.txt with LOAD DATA:
  
  

  mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;

• Or, you can fix the erroneous entry with an UPDATE statement:
  
  

  mysql> UPDATE pet SET birth = '2012-05-08'  -> WHERE name = 'Grendel';

  Query OK, 0 rows affected (0.07 sec)Rows matched: 1  Changed: 0  Warnings: 0

Now let's verify that Grendel's birthday was updated:

mysql> SELECT * FROM pet;

+---------+--------------+--------+--------------+------------+------------+
| name    | species      | gender | color        | birth      | death      |
+---------+--------------+--------+--------------+------------+------------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL       |
| Fluffy  | dog          | F      | golden       | 2007-06-02 | NULL       |
| Grendel | sugar glider | M      | gray         | 2012-05-08 | NULL       |
| Blue    | ferret       | M      | brown        | 2010-12-25 | NULL       |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL       |
| Spike   | parrot       | F      | blue         | 2011-11-12 | NULL       |
| Sandy   | goldfish     | F      | orange       | 2014-03-22 | 2014-06-15 |
| Polenta | python       | F      | green        | 2012-10-01 | NULL       |
+---------+--------------+--------+--------------+------------+------------+
8 rows in set (0.07 sec)

Selecting particular rows

Usually you only want to view a particular entry or entries. For instance, it is easier to verify the change to Grendel's birthday if we view Grendel's row in the table:

mysql> SELECT * FROM pet WHERE name='Grendel';

+---------+--------------+--------+-------+------------+-------+
| name    | species      | gender | color | birth      | death |
+---------+--------------+--------+-------+------------+-------+
| Grendel | sugar glider | M      | gray  | 2012-05-08 | NULL  |
+---------+--------------+--------+-------+------------+-------+
1 row in set (0.03 sec)

Here, we've selected every item in the row whose name matches the string 'Grendel'. This is an example of string comparison: MySQL has determined which items match our query by comparing our string with the strings in the name column. String comparisons are case-insensitive by default, so the following searches:

mysql> SELECT * FROM pet WHERE name='grendel';

mysql> SELECT * FROM pet WHERE name='GRENDEL';

mysql> SELECT * FROM pet WHERE name='gREnDeL';

…all return the same result:

+---------+--------------+--------+-------+------------+-------+
| name    | species      | gender | color | birth      | death |
+---------+--------------+--------+-------+------------+-------+
| Grendel | sugar glider | M      | gray  | 2012-05-08 | NULL  |
+---------+--------------+--------+-------+------------+-------+
1 row in set (0.00 sec)

We can specify any of our columns as a condition:

mysql> SELECT * FROM pet WHERE gender = 'F';

+---------+----------+--------+--------+------------+------------+
| name    | species  | gender | color  | birth      | death      |
+---------+----------+--------+--------+------------+------------+
| Fluffy  | dog      | F      | golden | 2007-06-02 | NULL       |
| Spike   | parrot   | F      | blue   | 2011-11-12 | NULL       |
| Sandy   | goldfish | F      | orange | 2014-03-22 | 2014-06-15 |
| Polenta | python   | F      | green  | 2012-10-01 | NULL       |
+---------+----------+--------+--------+------------+------------+
4 rows in set (0.00 sec)

…we can also combine conditions, for instance with the boolean AND operator:

mysql> SELECT * FROM pet WHERE gender = 'F' AND species = 'goldfish';

+-------+----------+--------+--------+------------+------------+
| name  | species  | gender | color  | birth      | death      |
+-------+----------+--------+--------+------------+------------+
| Sandy | goldfish | F      | orange | 2014-03-22 | 2014-06-15 |
+-------+----------+--------+--------+------------+------------+
1 row in set (0.01 sec)

…or the OR operator:

mysql> SELECT * FROM pet WHERE name = 'Sandy' OR gender = 'M';

+---------+--------------+--------+--------------+------------+------------+
| name    | species      | gender | color        | birth      | death      |
+---------+--------------+--------+--------------+------------+------------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL       |
| Grendel | sugar glider | M      | gray         | 2012-05-08 | NULL       |
| Blue    | ferret       | M      | brown        | 2010-12-25 | NULL       |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL       |
| Sandy   | goldfish     | F      | orange       | 2014-03-22 | 2014-06-15 |
+---------+--------------+--------+--------------+------------+------------+
5 rows in set (0.01 sec)

AND and OR can also be mixed. It's a good idea to use parentheses to make clear the order of operations, for instance:

mysql> SELECT * FROM pet WHERE (gender = 'F' AND species = 'dog') 
    -> OR (gender = 'M' AND color = 'gray');

+---------+--------------+--------+--------+------------+-------+
| name    | species      | gender | color  | birth      | death |
+---------+--------------+--------+--------+------------+-------+
| Fluffy  | dog          | F      | golden | 2007-06-02 | NULL  |
| Grendel | sugar glider | M      | gray   | 2012-05-08 | NULL  |
+---------+--------------+--------+--------+------------+-------+
2 rows in set (0.00 sec)

Selecting particular columns

Until now we've been using SELECT * to select "all columns" from our tables. We can also select specific columns by specifying them as a comma-separated list after SELECT:

mysql> SELECT name,color FROM pet;

+---------+--------------+
| name    | color        |
+---------+--------------+
| Steven  | butterscotch |
| Fluffy  | golden       |
| Grendel | gray         |
| Blue    | brown        |
| Alfred  | beige        |
| Spike   | blue         |
| Sandy   | orange       |
| Polenta | green        |
+---------+--------------+
8 rows in set (0.14 sec)

It's okay to use a space after the comma. For example:

mysql> SELECT species, birth FROM pet;

+--------------+------------+
| species      | birth      |
+--------------+------------+
| cat          | 2010-02-05 |
| dog          | 2007-06-02 |
| sugar glider | 2012-05-08 |
| ferret       | 2010-12-25 |
| hamster      | 2013-09-04 |
| parrot       | 2011-11-12 |
| goldfish     | 2014-03-22 |
| python       | 2012-10-01 |
+--------------+------------+
8 rows in set (0.00 sec)

Selecting unique data with DISTINCT

Sometimes you want to suppress repeated instances of a result to your query. For instance, let's select the gender data of our pets:

SELECT gender FROM pet;

+--------+
| gender |
+--------+
| M      |
| F      |
| M      |
| M      |
| M      |
| F      |
| F      |
| F      |
+--------+
8 rows in set (0.00 sec)

This might be what we want, but we can also use the DISTINCT keyword to suppress any result that is not unique. In other words, regardless of how many times they appear in the table, values appear only once each in our query response:

mysql> SELECT DISTINCT gender FROM pet;

+--------+
| gender |
+--------+
| M      |
| F      |
+--------+
2 rows in set (0.01 sec)

In our list, each animal is a different species. But if we had many dogs and cats, for instance, we could quickly generate a list of unique species types using the DISTINCT keyword to filter out repeated results.

It should be clear that we can combine SELECT and WHERE clauses to precisely choose which rows and columns we want to select:

mysql> SELECT species, birth FROM pet WHERE gender = 'M';

+--------------+------------+
| species      | birth      |
+--------------+------------+
| cat          | 2010-02-05 |
| sugar glider | 2012-05-08 |
| ferret       | 2010-12-25 |
| hamster      | 2013-09-04 |
+--------------+------------+
4 rows in set (0.00 sec)

Sorting rows

In all our examples so far, results were listed in no special order. It's usually more useful to have information sorted in some way. To do this, use the ORDER BY clause in your command.

Let's list our pets in order of birthday:

mysql> SELECT name, birth FROM pet ORDER BY birth;

+---------+------------+
| name    | birth      |
+---------+------------+
| Fluffy  | 2007-06-02 |
| Steven  | 2010-02-05 |
| Blue    | 2010-12-25 |
| Spike   | 2011-11-12 |
| Grendel | 2012-05-08 |
| Polenta | 2012-10-01 |
| Alfred  | 2013-09-04 |
| Sandy   | 2014-03-22 |
+---------+------------+
8 rows in set (0.11 sec)

The default sort order is ascending, with smaller values listed first.

We can reverse the sort order with the DESC keyword, which sorts items in descending order:

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

+---------+------------+
| name    | birth      |
+---------+------------+
| Sandy   | 2014-03-22 |
| Alfred  | 2013-09-04 |
| Polenta | 2012-10-01 |
| Grendel | 2012-05-08 |
| Spike   | 2011-11-12 |
| Blue    | 2010-12-25 |
| Steven  | 2010-02-05 |
| Fluffy  | 2007-06-02 |
+---------+------------+
8 rows in set (0.00 sec)

You can sort by more than one column, and you can sort columns in different directions. To sort our list of pets by gender and then by name:

mysql> SELECT name, gender, birth FROM pet ORDER BY gender, birth;

+---------+--------+------------+
| name    | gender | birth      |
+---------+--------+------------+
| Fluffy  | F      | 2007-06-02 |
| Spike   | F      | 2011-11-12 |
| Polenta | F      | 2012-10-01 |
| Sandy   | F      | 2014-03-22 |
| Steven  | M      | 2010-02-05 |
| Blue    | M      | 2010-12-25 |
| Grendel | M      | 2012-05-08 |
| Alfred  | M      | 2013-09-04 |
+---------+--------+------------+
8 rows in set (0.02 sec)

Here, F comes before M in the alphabet, so our female pets are listed first. After that, results are sorted by birthday, with earlier birthdays first.

Let's flip the order of the genders by specifying DESC after the column name gender in our ORDER BY clause:

mysql> SELECT name, gender, birth FROM pet ORDER BY gender DESC, birth;

+---------+--------+------------+
| name    | gender | birth      |
+---------+--------+------------+
| Steven  | M      | 2010-02-05 |
| Blue    | M      | 2010-12-25 |
| Grendel | M      | 2012-05-08 |
| Alfred  | M      | 2013-09-04 |
| Fluffy  | F      | 2007-06-02 |
| Spike   | F      | 2011-11-12 |
| Polenta | F      | 2012-10-01 |
| Sandy   | F      | 2014-03-22 |
+---------+--------+------------+
8 rows in set (0.00 sec)

Notice how the DESC keyword only affects the gender column. Let's add it after birth to sort birthdays in descending order as well:

mysql> SELECT name, gender, birth FROM pet 
    -> ORDER BY gender DESC, birth DESC;

+---------+--------+------------+
| name    | gender | birth      |
+---------+--------+------------+
| Alfred  | M      | 2013-09-04 |
| Grendel | M      | 2012-05-08 |
| Blue    | M      | 2010-12-25 |
| Steven  | M      | 2010-02-05 |
| Sandy   | F      | 2014-03-22 |
| Polenta | F      | 2012-10-01 |
| Spike   | F      | 2011-11-12 |
| Fluffy  | F      | 2007-06-02 |
+---------+--------+------------+
8 rows in set (0.01 sec)

Calculating dates

In our table, we have stored the birthday of each of our pets. We can use this information to calculate how many years ago they were born. The functions we use to do this calculation are TIMESTAMPDIFF() and CURDATE():

mysql> SELECT name, birth, CURDATE(), 
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;

+---------+------------+------------+------+
| name    | birth      | CURDATE()  | age  |
+---------+------------+------------+------+
| Steven  | 2010-02-05 | 2014-11-26 |    4 |
| Fluffy  | 2007-06-02 | 2014-11-26 |    7 |
| Grendel | 2012-05-08 | 2014-11-26 |    2 |
| Blue    | 2010-12-25 | 2014-11-26 |    3 |
| Alfred  | 2013-09-04 | 2014-11-26 |    1 |
| Spike   | 2011-11-12 | 2014-11-26 |    3 |
| Sandy   | 2014-03-22 | 2014-11-26 |    0 |
| Polenta | 2012-10-01 | 2014-11-26 |    2 |
+---------+------------+------------+------+
8 rows in set (0.01 sec)

Here, AS age tells MySQL to use the alias age to represent the result of TIMESTAMPDIFF(YEAR,birth,CURDATE()). We can sort by this alias:

mysql> SELECT name, birth, CURDATE(), 
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet ORDER BY age;

+---------+------------+------------+------+
| name    | birth      | CURDATE()  | age  |
+---------+------------+------------+------+
| Sandy   | 2014-03-22 | 2014-11-26 |    0 |
| Alfred  | 2013-09-04 | 2014-11-26 |    1 |
| Grendel | 2012-05-08 | 2014-11-26 |    2 |
| Polenta | 2012-10-01 | 2014-11-26 |    2 |
| Blue    | 2010-12-25 | 2014-11-26 |    3 |
| Spike   | 2011-11-12 | 2014-11-26 |    3 |
| Steven  | 2010-02-05 | 2014-11-26 |    4 |
| Fluffy  | 2007-06-02 | 2014-11-26 |    7 |
+---------+------------+------------+------+
8 rows in set (0.02 sec)

But — isn't Sandy the goldfish actually dead? Here is a query which only calculates the age of a pet if it has died:

mysql> SELECT name, birth, death, 
    -> TIMESTAMPDIFF(YEAR,birth,death) AS age 
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;

+-------+------------+------------+------+
| name  | birth      | death      | age  |
+-------+------------+------------+------+
| Sandy | 2014-03-22 | 2014-06-15 |    0 |
+-------+------------+------------+------+
1 row in set (0.01 sec)

…then again, for Sandy it might be more appropriate to list her age in days, since she didn't even make it to her first birthday:

mysql> SELECT name, birth, death, 
    -> TIMESTAMPDIFF(DAY,birth,death) AS age_in_days 
    -> FROM pet WHERE death IS NOT NULL ORDER BY age_in_days;

+-------+------------+------------+-------------+
| name  | birth      | death      | age_in_days |
+-------+------------+------------+-------------+
| Sandy | 2014-03-22 | 2014-06-15 |          85 |
+-------+------------+------------+-------------+
1 row in set (0.01 sec)

Now, let's say we want to calculate which of our pets has a birthday next month. To figure this out, year and day of birth are irrelevant; all we want is the month component of the birth date. MySQL provides functions for extracting parts of dates, such as YEAR(), MONTH(), and DAYOFMONTH(). Let's compare birth and MONTH(birth):

mysql> SELECT birth, MONTH(birth) FROM pet;

+------------+--------------+
| birth      | MONTH(birth) |
+------------+--------------+
| 2010-02-05 |            2 |
| 2007-06-02 |            6 |
| 2012-05-08 |            5 |
| 2010-12-25 |           12 |
| 2013-09-04 |            9 |
| 2011-11-12 |           11 |
| 2014-03-22 |            3 |
| 2012-10-01 |           10 |
+------------+--------------+
8 rows in set (0.05 sec)

Okay. So, we want to query the database to find out which pets have birthdays next month. We have a representation of the month as a number, and we can always add one to it — but if the month wraps from December to January, we need the number to wrap from 12 to 1.

MySQL provides a function, DATE_ADD() which lets you add an interval to a given date. We can use it to add one month to CURDATE(), then extract the month part with MONTH(). Here's what the query looks like, and its result:

mysql> SELECT name, species, CURDATE(), birth FROM pet WHERE 
    -> MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

+------+---------+------------+------------+
| name | species | CURDATE()  | birth      |
+------+---------+------------+------------+
| Blue | ferret  | 2014-11-26 | 2010-12-25 |
+------+---------+------------+------------+
1 row in set (0.00 sec)

As you can see, it's currently November, and Blue the ferret is our only pet with a birthday next month.

Pattern matching

MySQL can do pattern matching using SQL-like patterns, or using extended regular expressions similar to the expressions used by vi, grep, and sed. Either works, but they have slightly different rules.

SQL-style pattern matching

Using SQL-style pattern matching, you can use an underscore ("_") to match any single character, and a percent sign ("%") to match an arbitrary number of characters, including zero characters. SQL patterns are case-insensitive by default. The comparison operators for this style of pattern are LIKE and NOT LIKE.

For example, this pattern matches any name which begins with the letter s:

mysql> SELECT * FROM pet WHERE name LIKE 's%';

+--------+----------+--------+--------------+------------+------------+
| name   | species  | gender | color        | birth      | death      |
+--------+----------+--------+--------------+------------+------------+
| Steven | cat      | M      | butterscotch | 2010-02-05 | NULL       |
| Spike  | parrot   | F      | blue         | 2011-11-12 | NULL       |
| Sandy  | goldfish | F      | orange       | 2014-03-22 | 2014-06-15 |
+--------+----------+--------+--------------+------------+------------+
3 rows in set (0.00 sec)

…and this pattern matches any color that begins with a b or a g:

mysql> SELECT * FROM pet WHERE (color LIKE 'b%') 
    -> OR (color LIKE 'g%');

+---------+--------------+--------+--------------+------------+-------+
| name    | species      | gender | color        | birth      | death |
+---------+--------------+--------+--------------+------------+-------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL  |
| Fluffy  | dog          | F      | golden       | 2007-06-02 | NULL  |
| Grendel | sugar glider | M      | gray         | 2012-05-08 | NULL  |
| Blue    | ferret       | M      | brown        | 2010-12-25 | NULL  |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL  |
| Spike   | parrot       | F      | blue         | 2011-11-12 | NULL  |
| Polenta | python       | F      | green        | 2012-10-01 | NULL  |
+---------+--------------+--------+--------------+------------+-------+
7 rows in set (0.00 sec)

…and this pattern matches any name with exactly six characters (we accomplish this using exactly six underscores in our pattern):

mysql> SELECT * FROM pet WHERE name LIKE '______';

+--------+---------+--------+--------------+------------+-------+
| name   | species | gender | color        | birth      | death |
+--------+---------+--------+--------------+------------+-------+
| Steven | cat     | M      | butterscotch | 2010-02-05 | NULL  |
| Fluffy | dog     | F      | golden       | 2007-06-02 | NULL  |
| Alfred | hamster | M      | beige        | 2013-09-04 | NULL  |
+--------+---------+--------+--------------+------------+-------+
3 rows in set (0.00 sec)

Extended regular expression pattern matching

MySQL also recognizes extended regular expressions. When comparing against a regular expression, use the comparison operators REGEXP and NOT REGEXP (or RLIKE and NOT RLIKE, which are synonyms).

In brief, here are some major syntactical characteristics of extended regular expressions:

• A dot "." matches any single character.
• A character class "[…]" matches any character inside the brackets. For example, "[abc]" matches "a", "b", or "c". To name a range of characters, use a dash. "[a-z]" matches any letter, whereas "[0-9]" matches any digit.
• "*" matches zero or more instances of the thing preceding it. For example, "x*" matches any number of "x" characters, "[0-9]*" matches any number of digits, and ".*" matches any number of anything.
• A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested. This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.
• To anchor a pattern so that it must match the beginning or end of the value being tested, use "^" at the beginning or "$" at the end of the pattern.

Note that when MySQL attempts to match one of its strings to an extended regular expression, by default, it performs a case-insensitive match.

Here are the same pattern matches we performed above, but using extended regular expressions:

mysql> SELECT * FROM pet WHERE name REGEXP '^s';

+--------+----------+--------+--------------+------------+------------+
| name   | species  | gender | color        | birth      | death      |
+--------+----------+--------+--------------+------------+------------+
| Steven | cat      | M      | butterscotch | 2010-02-05 | NULL       |
| Spike  | parrot   | F      | blue         | 2011-11-12 | NULL       |
| Sandy  | goldfish | F      | orange       | 2014-03-22 | 2014-06-15 |
+--------+----------+--------+--------------+------------+------------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM pet WHERE (color REGEXP '^b') 
    -> OR (color REGEXP '^g');

+---------+--------------+--------+--------------+------------+-------+
| name    | species      | gender | color        | birth      | death |
+---------+--------------+--------+--------------+------------+-------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL  |
| Fluffy  | dog          | F      | golden       | 2007-06-02 | NULL  |
| Grendel | sugar glider | M      | gray         | 2012-05-08 | NULL  |
| Blue    | ferret       | M      | brown        | 2010-12-25 | NULL  |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL  |
| Spike   | parrot       | F      | blue         | 2011-11-12 | NULL  |
| Polenta | python       | F      | green        | 2012-10-01 | NULL  |
+---------+--------------+--------+--------------+------------+-------+

mysql> SELECT * FROM pet WHERE name REGEXP '……';

+---------+--------------+--------+--------------+------------+-------+
| name    | species      | gender | color        | birth      | death |
+---------+--------------+--------+--------------+------------+-------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL  |
| Fluffy  | dog          | F      | golden       | 2007-06-02 | NULL  |
| Grendel | sugar glider | M      | gray         | 2012-05-08 | NULL  |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL  |
| Polenta | python       | F      | green        | 2012-10-01 | NULL  |
+---------+--------------+--------+--------------+------------+-------+
5 rows in set (0.03 sec)

Counting rows

Databases are used to retrieve specific data ("What are the names of my dogs? What color are they?"), but it is often useful to provide a count of data ("How many pets do I have? How many of them are female? How many were born in 2012?").

Assuming that we've listed all our pets in our pet table, counting "How many pets do I have?" is the same as asking "How many rows are in my pet table?". To find the answer to this, we can use the COUNT() function in our query, using the form COUNT(*) to count every single row.

mysql> SELECT COUNT(*) FROM pet;

+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.15 sec)

If we want to break down this count even further, we can use the GROUP BY clause, specifying how we want to group these results:

mysql> SELECT gender, COUNT(*) FROM pet GROUP BY gender;

+--------+----------+
| gender | COUNT(*) |
+--------+----------+
| F      |        4 |
| M      |        4 |
+--------+----------+
2 rows in set (0.00 sec)

You can GROUP BY more than one column to break down the count by more than one criterion. Here we get a count which groups our pets according to gender and year of birth:

mysql> SELECT gender, YEAR(birth), COUNT(*) FROM pet 
    -> GROUP BY gender, YEAR(birth);

+--------+-------------+----------+
| gender | YEAR(birth) | COUNT(*) |
+--------+-------------+----------+
| F      |        2007 |        1 |
| F      |        2011 |        1 |
| F      |        2012 |        1 |
| F      |        2014 |        1 |
| M      |        2010 |        2 |
| M      |        2012 |        1 |
| M      |        2013 |        1 |
+--------+-------------+----------+
7 rows in set (0.39 sec)

As you can see, the only overlap is male pets born in 2010, of which we have two.

Using more than one table

Now let's make another table to use alongside our pet table. Let's create this one to store information about important events in the life of our pets, and let's call the table event.

We want to use the following columns: name (which pet was involved in the event?), date (when did the event happen?), type (what type of event was it?), and details (a detailed description of the event).

mysql> CREATE TABLE event (name VARCHAR(20), date DATE, 
    -> type VARCHAR(20), details VARCHAR(255));

Query OK, 0 rows affected (5.28 sec)

The easiest way to populate our table with data is to create another text file where each line represents a row, and data is tab-delimited. We call our file events.txt:

Steven     2010-06-06    vet         Neutered
Fluffy     2011-08-08    kennel      At kennel while we took a vacation
Spike      2012-02-15    vet         Routine checkup, prescribed prozac
Fluffy     2008-06-02    birthday    Ribeye steak, new chewtoy
Polenta    2013-11-06    other       escaped
Polenta    2013-11-08    other       found
Grendel    2013-01-06    vet         Routine checkup
Steven     2010-09-04    vet         Gave birth to six kittens, two survived
Grendel    2013-04-08    birthday    Tangerine slices and a ginger cookie

Save and close the file. Now we import it into our table using LOAD DATA:

mysql> LOAD DATA LOCAL INFILE 'events.txt' INTO TABLE event;

Query OK, 9 rows affected (0.37 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

Now let's verify that our data has imported using SELECT *:

mysql> SELECT * FROM event;

+---------+------------+----------+-----------------------------------------+
| name    | date       | type     | details                                 |
+---------+------------+----------+-----------------------------------------+
| Steven  | 2010-06-06 | vet      | Neutered                                |
| Fluffy  | 2011-08-08 | kennel   | At kennel while we took a vacation      |
| Spike   | 2012-02-15 | vet      | Routine checkup, prescribed prozac      |
| Fluffy  | 2008-06-02 | birthday | Ribeye steak, new chewtoy               |
| Polenta | 2013-11-06 | other    | escaped                                 |
| Polenta | 2013-11-08 | other    | found                                   |
| Grendel | 2013-01-06 | vet      | Routine checkup                         |
| Steven  | 2010-09-04 | vet      | Gave birth to six kittens, two survived |
| Grendel | 2013-04-08 | birthday | Tangerine slices and a ginger cookie    |
+---------+------------+----------+-----------------------------------------+
9 rows in set (0.01 sec)

Now we're going to perform a query which requires both of our tables, pet and event. For instance, let's query all the events for each pet, and the age of each pet when it happened. This requires the birth column from pet, and the information in event. We use the clause INNER JOIN to unite the results:

mysql> SELECT pet.name, 
    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) 
    -> AS age, details FROM pet INNER JOIN event 
    -> ON pet.name = event.name 
    -> WHERE event.name = 'Polenta';

+---------+------+---------+
| name    | age  | details |
+---------+------+---------+
| Polenta |    1 | escaped |
| Polenta |    1 | found   |
+---------+------+---------+
2 rows in set (0.02 sec)

This is the most complicated query we've seen so far. Here are some important things to note about it:

• The FROM clause joins two tables using INNER JOIN because we need to use two tables to complete the query.
• When joining the results from more than one table, we need to specify how records in one table can be matched to records in another. This is easy here because both tables have a name column. We use an ON clause to specify the name columns in both tables, referring to the column with the form "tablename.columnname."
• The actual joining is done by INNER JOIN. This permits rows from either table to appear in the result if and only if both tables meet the conditions specified with the ON clause. In this example, the ON clause specifies that the name column in the pet table must match the name column in the event table. If a name appears in one table but not the other, the row does not appear in the result because the condition in the ON clause fails.
• Because the name column appears in both tables, we must be specific about the table we mean when referring to the column. This is why we prepend the table name before the column name with a period.

Note that we don't need two tables to perform a JOIN. Sometimes it's useful to JOIN a table to itself, if we want to compare records in a table to other records in that same table. For example, let's say we want to list every combination of male and female pet in our menagerie. We can do this by JOINing records in the pet table:

mysql> SELECT p1.name, p1.gender, p2.name, p2.gender 
    -> FROM pet AS p1 INNER JOIN pet AS p2 
    -> ON p1.gender = 'F' AND p2.gender = 'M';

+---------+--------+---------+--------+
| name    | gender | name    | gender |
+---------+--------+---------+--------+
| Fluffy  | F      | Steven  | M      |
| Spike   | F      | Steven  | M      |
| Sandy   | F      | Steven  | M      |
| Polenta | F      | Steven  | M      |
| Fluffy  | F      | Grendel | M      |
| Spike   | F      | Grendel | M      |
| Sandy   | F      | Grendel | M      |
| Polenta | F      | Grendel | M      |
| Fluffy  | F      | Blue    | M      |
| Spike   | F      | Blue    | M      |
| Sandy   | F      | Blue    | M      |
| Polenta | F      | Blue    | M      |
| Fluffy  | F      | Alfred  | M      |
| Spike   | F      | Alfred  | M      |
| Sandy   | F      | Alfred  | M      |
| Polenta | F      | Alfred  | M      |
+---------+--------+---------+--------+
16 rows in set (0.00 sec)

Getting information about databases and tables

To find out which database is currently selected, use the DATABASE() function with the SELECT command:

mysql> SELECT DATABASE();

+------------+
| DATABASE() |
+------------+
| pets       |
+------------+
1 row in set (0.01 sec)

If there is no database currently selected for use, the result is NULL.

To find out what tables the currently-selected database contains, use the SHOW TABLES; command:

mysql> SHOW TABLES;

+----------------+
| Tables_in_pets |
+----------------+
| event          |
| pet            |
+----------------+
2 rows in set (0.00 sec)

The name of the column in this query result is always Tables_in_tablename.

To find out how a table is structured, use the DESCRIBE command:

mysql> DESCRIBE event;

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(20)  | YES  |     | NULL    |       |
| date    | date         | YES  |     | NULL    |       |
| type    | varchar(20)  | YES  |     | NULL    |       |
| details | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.19 sec)

Field indicates the name of each column, Type indicates the variable type held by entries in that column, Null indicates whether the column can hold NULL values, Key indicates whether the column is indexed, and Default indicates the column's default value. Extra displays special information about columns: If a column was created with the AUTO_INCREMENT option, the value is auto_increment rather than empty.

The DESCRIBE command can also be abbreviated as DESC.

Finding the maximum value of a column

To select the maximum value of a column, use the MAX() function:

mysql> SELECT MAX(birth) FROM pet;

+------------+
| MAX(birth) |
+------------+
| 2014-03-22 |
+------------+
1 row in set (0.02 sec)

Let's look at how many days old our pets are:

mysql> SELECT name, TIMESTAMPDIFF(DAY,birth,CURDATE()) 
    -> AS days_old FROM pet;

+---------+----------+
| name    | days_old |
+---------+----------+
| Steven  |     1757 |
| Fluffy  |     2736 |
| Grendel |      934 |
| Blue    |     1434 |
| Alfred  |      450 |
| Spike   |     1112 |
| Sandy   |      251 |
| Polenta |      788 |
+---------+----------+
8 rows in set (0.00 sec)

Now let's find who's the oldest. We can do this by SELECTing WHERE the result is the same as the result of a SELECT MAX query:

mysql> SELECT name, TIMESTAMPDIFF(day,birth,CURDATE()) AS days_old 
    -> FROM pet WHERE 
    -> TIMESTAMPDIFF(day,birth,CURDATE()) = 
    -> (SELECT MAX(TIMESTAMPDIFF(day,birth,CURDATE())) 
    -> FROM pet);

+--------+----------+
| name   | days_old |
+--------+----------+
| Fluffy |     2736 |
+--------+----------+
1 row in set (0.00 sec)

User-defined variables

You can store the result of certain queries into variables and use them later in the same session. For example:

mysql> SELECT @max_days_old:=MAX(TIMESTAMPDIFF(DAY,birth,CURDATE())) 
    -> FROM pet;

+--------------------------------------------------------+
| @max_days_old:=MAX(TIMESTAMPDIFF(DAY,birth,CURDATE())) |
+--------------------------------------------------------+
|                                                   2736 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT name FROM pet WHERE 
    -> TIMESTAMPDIFF(DAY,birth,CURDATE()) = @max_days_old;

+--------+
| name   |
+--------+
| Fluffy |
+--------+
1 row in set (0.03 sec)

Using AUTO_INCREMENT

The AUTO_INCREMENT attribute can automatically increment a column value every time a new row is added. For example:

mysql> CREATE TABLE animals (
    -> id MEDIUMINT NOT NULL AUTO_INCREMENT,
    -> name CHAR(30) NOT NULL,
    -> PRIMARY KEY (id));

Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO animals (name) VALUES
    -> ('dog'),('cat'),('penguin'),
    -> ('sugar glider'),('whale'),('ostrich');

Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM animals;

+----+--------------+
| id | name         |
+----+--------------+
|  1 | dog          |
|  2 | cat          |
|  3 | penguin      |
|  4 | sugar glider |
|  5 | whale        |
|  6 | ostrich      |
+----+--------------+
6 rows in set (0.00 sec)

Related commands

mysql — An open-source relational database management system.
myisamchk — Check, repair, optimize, or fetch information about a MySQL database.
mysqldump — A tool for backing up or transferring MySQL databases.