<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 3. Tutorial</title><link rel="stylesheet" href="mysql-html.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="prev" href="installing.html" title="Chapter 2. Installing and Upgrading MySQL"><link rel="next" href="using-mysql-programs.html" title="Chapter 4. Using MySQL Programs"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 3. Tutorial</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="installing.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="using-mysql-programs.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="tutorial"></a>Chapter 3. Tutorial</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="tutorial.html#connecting-disconnecting">3.1. Connecting to and Disconnecting from the Server</a></span></dt><dt><span class="section"><a href="tutorial.html#entering-queries">3.2. Entering Queries</a></span></dt><dt><span class="section"><a href="tutorial.html#database-use">3.3. Creating and Using a Database</a></span></dt><dd><dl><dt><span class="section"><a href="tutorial.html#creating-database">3.3.1. Creating and Selecting a Database</a></span></dt><dt><span class="section"><a href="tutorial.html#creating-tables">3.3.2. Creating a Table</a></span></dt><dt><span class="section"><a href="tutorial.html#loading-tables">3.3.3. Loading Data into a Table</a></span></dt><dt><span class="section"><a href="tutorial.html#retrieving-data">3.3.4. Retrieving Information from a Table</a></span></dt></dl></dd><dt><span class="section"><a href="tutorial.html#getting-information">3.4. Getting Information About Databases and Tables</a></span></dt><dt><span class="section"><a href="tutorial.html#batch-mode">3.5. Using <span><strong class="command">mysql</strong></span> in Batch Mode</a></span></dt><dt><span class="section"><a href="tutorial.html#examples">3.6. Examples of Common Queries</a></span></dt><dd><dl><dt><span class="section"><a href="tutorial.html#example-maximum-column">3.6.1. The Maximum Value for a Column</a></span></dt><dt><span class="section"><a href="tutorial.html#example-maximum-row">3.6.2. The Row Holding the Maximum of a Certain Column</a></span></dt><dt><span class="section"><a href="tutorial.html#example-maximum-column-group">3.6.3. Maximum of Column per Group</a></span></dt><dt><span class="section"><a href="tutorial.html#example-maximum-column-group-row">3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field</a></span></dt><dt><span class="section"><a href="tutorial.html#example-user-variables">3.6.5. Using User-Defined Variables</a></span></dt><dt><span class="section"><a href="tutorial.html#example-foreign-keys">3.6.6. Using Foreign Keys</a></span></dt><dt><span class="section"><a href="tutorial.html#searching-on-two-keys">3.6.7. Searching on Two Keys</a></span></dt><dt><span class="section"><a href="tutorial.html#calculating-days">3.6.8. Calculating Visits Per Day</a></span></dt><dt><span class="section"><a href="tutorial.html#example-auto-increment">3.6.9. Using <code class="literal">AUTO_INCREMENT</code></a></span></dt></dl></dd><dt><span class="section"><a href="tutorial.html#twin">3.7. Queries from the Twin Project</a></span></dt><dd><dl><dt><span class="section"><a href="tutorial.html#twin-pool">3.7.1. Find All Non-distributed Twins</a></span></dt><dt><span class="section"><a href="tutorial.html#twin-event">3.7.2. Show a Table of Twin Pair Status</a></span></dt></dl></dd><dt><span class="section"><a href="tutorial.html#apache">3.8. Using MySQL with Apache</a></span></dt></dl></div><a class="indexterm" name="id2539592"></a><a class="indexterm" name="id2539601"></a><a class="indexterm" name="id2539614"></a><a class="indexterm" name="id2539626"></a><p>
This chapter provides a tutorial introduction to MySQL by showing
how to use the <span><strong class="command">mysql</strong></span> client program to create and
use a simple database. <span><strong class="command">mysql</strong></span> (sometimes referred
to as the “<span class="quote">terminal monitor</span>” or just
“<span class="quote">monitor</span>”) is an interactive program that allows you to
connect to a MySQL server, run queries, and view the results.
<span><strong class="command">mysql</strong></span> may also be used in batch mode: you place
your queries in a file beforehand, then tell
<span><strong class="command">mysql</strong></span> to execute the contents of the file. Both
ways of using <span><strong class="command">mysql</strong></span> are covered here.
</p><p>
To see a list of options provided by <span><strong class="command">mysql</strong></span>,
invoke it with the <code class="option">--help</code> option:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql --help</code></strong>
</pre><p>
This chapter assumes that <span><strong class="command">mysql</strong></span> is installed on
your machine and that a MySQL server is available to which you can
connect. If this is not true, contact your MySQL administrator. (If
<span class="emphasis"><em>you</em></span> are the administrator, you need to consult
the relevant portions of this manual, such as
<a href="server-administration.html" title="Chapter 5. MySQL Server Administration">Chapter 5, <i>MySQL Server Administration</i></a>.)
</p><p>
This chapter describes the entire process of setting up and using a
database. If you are interested only in accessing an existing
database, you may want to skip over the sections that describe how
to create the database and the tables it contains.
</p><p>
Because this chapter is tutorial in nature, many details are
necessarily omitted. Consult the relevant sections of the manual for
more information on the topics covered here.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="connecting-disconnecting"></a>3.1. Connecting to and Disconnecting from the Server</h2></div></div></div><a class="indexterm" name="id2539758"></a><a class="indexterm" name="id2539771"></a><a class="indexterm" name="id2539783"></a><a class="indexterm" name="id2539796"></a><p>
To connect to the server, you will usually need to provide a MySQL
user name when you invoke <span><strong class="command">mysql</strong></span> and, most
likely, a password. If the server runs on a machine other than the
one where you log in, you will also need to specify a host name.
Contact your administrator to find out what connection parameters
you should use to connect (that is, what host, user name, and
password to use). Once you know the proper parameters, you should
be able to connect like this:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql -h <em class="replaceable"><code>host</code></em> -u <em class="replaceable"><code>user</code></em> -p</code></strong>
Enter password: <strong class="userinput"><code>********</code></strong>
</pre><p>
<code class="literal">host</code> and <code class="literal">user</code> represent the
host name where your MySQL server is running and the user name of
your MySQL account. Substitute appropriate values for your setup.
The <code class="literal">********</code> represents your password; enter it
when <span><strong class="command">mysql</strong></span> displays the <code class="literal">Enter
password:</code> prompt.
</p><p>
If that works, you should see some introductory information
followed by a <code class="literal">mysql></code> prompt:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql -h <em class="replaceable"><code>host</code></em> -u <em class="replaceable"><code>user</code></em> -p</code></strong>
Enter password: <strong class="userinput"><code>********</code></strong>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 5.0.54-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
</pre><p>
The <code class="literal">mysql></code> prompt tells you that
<span><strong class="command">mysql</strong></span> is ready for you to enter commands.
</p><p>
If you are logging in on the same machine that MySQL is running
on, you can omit the host, and simply use the following:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql -u <em class="replaceable"><code>user</code></em> -p</code></strong>
</pre><p>
If, when you attempt to log in, you get an error message such as
<span class="errortext">ERROR 2002 (HY000): Can't connect to local MySQL server
through socket '/tmp/mysql.sock' (2)</span>, it means that
that MySQL server daemon (Unix) or service (Windows) is not
running. Consult the administrator or see the section of
<a href="installing.html" title="Chapter 2. Installing and Upgrading MySQL">Chapter 2, <i>Installing and Upgrading MySQL</i></a> that is appropriate to your operating
system.
</p><p>
For help with other problems often encountered when trying to log
in, see <a href="error-handling.html#common-errors" title="B.1.2. Common Errors When Using MySQL Programs">Section B.1.2, “Common Errors When Using MySQL Programs”</a>.
</p><p>
Some MySQL installations allow users to connect as the anonymous
(unnamed) user to the server running on the local host. If this is
the case on your machine, you should be able to connect to that
server by invoking <span><strong class="command">mysql</strong></span> without any options:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql</code></strong>
</pre><p>
After you have connected successfully, you can disconnect any time
by typing <code class="literal">QUIT</code> (or <code class="literal">\q</code>) at
the <code class="literal">mysql></code> prompt:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>QUIT</code></strong>
Bye
</pre><p>
On Unix, you can also disconnect by pressing Control-D.
</p><p>
Most examples in the following sections assume that you are
connected to the server. They indicate this by the
<code class="literal">mysql></code> prompt.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="entering-queries"></a>3.2. Entering Queries</h2></div></div></div><a class="indexterm" name="id2540081"></a><a class="indexterm" name="id2540094"></a><a class="indexterm" name="id2540106"></a><p>
Make sure that you are connected to the server, as discussed in
the previous section. Doing so does not in itself select any
database to work with, but that's okay. At this point, it's more
important to find out a little about how to issue queries than to
jump right in creating tables, loading data into them, and
retrieving data from them. This section describes the basic
principles of entering commands, using several queries you can try
out to familiarize yourself with how <span><strong class="command">mysql</strong></span>
works.
</p><p>
Here's a simple command that asks the server to tell you its
version number and the current date. Type it in as shown here
following the <code class="literal">mysql></code> prompt and press Enter:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT VERSION(), CURRENT_DATE;</code></strong>
+----------------+--------------+
| VERSION() | CURRENT_DATE |
+----------------+--------------+
| 5.0.7-beta-Max | 2005-07-11 |
+----------------+--------------+
1 row in set (0.01 sec)
mysql>
</pre><p>
This query illustrates several things about
<span><strong class="command">mysql</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
A command normally consists of an SQL statement followed by a
semicolon. (There are some exceptions where a semicolon may be
omitted. <code class="literal">QUIT</code>, mentioned earlier, is one of
them. We'll get to others later.)
</p></li><li><p>
When you issue a command, <span><strong class="command">mysql</strong></span> sends it to
the server for execution and displays the results, then prints
another <code class="literal">mysql></code> prompt to indicate that
it is ready for another command.
</p></li><li><p>
<span><strong class="command">mysql</strong></span> displays query output in tabular form
(rows and columns). The first row contains labels for the
columns. The rows following are the query results. Normally,
column labels are the names of the columns you fetch from
database tables. If you're retrieving the value of an
expression rather than a table column (as in the example just
shown), <span><strong class="command">mysql</strong></span> labels the column using the
expression itself.
</p></li><li><p>
<span><strong class="command">mysql</strong></span> shows how many rows were returned and
how long the query took to execute, which gives you a rough
idea of server performance. These values are imprecise because
they represent wall clock time (not CPU or machine time), and
because they are affected by factors such as server load and
network latency. (For brevity, the “<span class="quote">rows in set</span>”
line is sometimes not shown in the remaining examples in this
chapter.)
</p></li></ul></div><p>
Keywords may be entered in any lettercase. The following queries
are equivalent:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT VERSION(), CURRENT_DATE;</code></strong>
mysql> <strong class="userinput"><code>select version(), current_date;</code></strong>
mysql> <strong class="userinput"><code>SeLeCt vErSiOn(), current_DATE;</code></strong>
</pre><p>
Here's another query. It demonstrates that you can use
<span><strong class="command">mysql</strong></span> as a simple calculator:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT SIN(PI()/4), (4+1)*5;</code></strong>
+------------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+------------------+---------+
| 0.70710678118655 | 25 |
+------------------+---------+
1 row in set (0.02 sec)
</pre><p>
The queries shown thus far have been relatively short, single-line
statements. You can even enter multiple statements on a single
line. Just end each one with a semicolon:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT VERSION(); SELECT NOW();</code></strong>
+----------------+
| VERSION() |
+----------------+
| 5.0.7-beta-Max |
+----------------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2005-07-11 17:59:36 |
+---------------------+
1 row in set (0.00 sec)
</pre><p>
A command need not be given all on a single line, so lengthy
commands that require several lines are not a problem.
<span><strong class="command">mysql</strong></span> determines where your statement ends by
looking for the terminating semicolon, not by looking for the end
of the input line. (In other words, <span><strong class="command">mysql</strong></span>
accepts free-format input: it collects input lines but does not
execute them until it sees the semicolon.)
</p><p>
Here's a simple multiple-line statement:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT</code></strong>
-> <strong class="userinput"><code>USER()</code></strong>
-> <strong class="userinput"><code>,</code></strong>
-> <strong class="userinput"><code>CURRENT_DATE;</code></strong>
+---------------+--------------+
| USER() | CURRENT_DATE |
+---------------+--------------+
| jon@localhost | 2005-07-11 |
+---------------+--------------+
</pre><p>
In this example, notice how the prompt changes from
<code class="literal">mysql></code> to <code class="literal">-></code> after you
enter the first line of a multiple-line query. This is how
<span><strong class="command">mysql</strong></span> indicates that it has not yet seen a
complete statement and is waiting for the rest. The prompt is your
friend, because it provides valuable feedback. If you use that
feedback, you can always be aware of what <span><strong class="command">mysql</strong></span>
is waiting for.
</p><p>
If you decide you do not want to execute a command that you are in
the process of entering, cancel it by typing
<code class="literal">\c</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT</code></strong>
-> <strong class="userinput"><code>USER()</code></strong>
-> <strong class="userinput"><code>\c</code></strong>
mysql>
</pre><p>
Here, too, notice the prompt. It switches back to
<code class="literal">mysql></code> after you type <code class="literal">\c</code>,
providing feedback to indicate that <span><strong class="command">mysql</strong></span> is
ready for a new command.
</p><p>
The following table shows each of the prompts you may see and
summarizes what they mean about the state that
<span><strong class="command">mysql</strong></span> is in:
</p><a class="indexterm" name="id2540513"></a><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Prompt</strong></span></td><td><span class="bold"><strong>Meaning</strong></span></td></tr><tr><td><code class="literal">mysql></code></td><td>Ready for new command.</td></tr><tr><td><code class="literal">-></code></td><td>Waiting for next line of multiple-line command.</td></tr><tr><td><code class="literal">'></code></td><td>Waiting for next line, waiting for completion of a string that began
with a single quote (“<span class="quote"><code class="literal">'</code></span>”).</td></tr><tr><td><code class="literal">"></code></td><td>Waiting for next line, waiting for completion of a string that began
with a double quote (“<span class="quote"><code class="literal">"</code></span>”).</td></tr><tr><td><code class="literal">`></code></td><td>Waiting for next line, waiting for completion of an identifier that
began with a backtick
(“<span class="quote"><code class="literal">`</code></span>”).</td></tr><tr><td><code class="literal">/*></code></td><td>Waiting for next line, waiting for completion of a comment that began
with <code class="literal">/*</code>.</td></tr></tbody></table></div><p>
In the MySQL 5.0 series, the <code class="literal">/*></code> prompt was
implemented in MySQL 5.0.6.
</p><p>
Multiple-line statements commonly occur by accident when you
intend to issue a command on a single line, but forget the
terminating semicolon. In this case, <span><strong class="command">mysql</strong></span>
waits for more input:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT USER()</code></strong>
->
</pre><p>
If this happens to you (you think you've entered a statement but
the only response is a <code class="literal">-></code> prompt), most
likely <span><strong class="command">mysql</strong></span> is waiting for the semicolon. If
you don't notice what the prompt is telling you, you might sit
there for a while before realizing what you need to do. Enter a
semicolon to complete the statement, and <span><strong class="command">mysql</strong></span>
executes it:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT USER()</code></strong>
-> <strong class="userinput"><code>;</code></strong>
+---------------+
| USER() |
+---------------+
| jon@localhost |
+---------------+
</pre><p>
The <code class="literal">'></code> and <code class="literal">"></code> prompts
occur during string collection (another way of saying that MySQL
is waiting for completion of a string). In MySQL, you can write
strings surrounded by either “<span class="quote"><code class="literal">'</code></span>”
or “<span class="quote"><code class="literal">"</code></span>” characters (for example,
<code class="literal">'hello'</code> or <code class="literal">"goodbye"</code>), and
<span><strong class="command">mysql</strong></span> lets you enter strings that span multiple
lines. When you see a <code class="literal">'></code> or
<code class="literal">"></code> prompt, it means that you have entered a
line containing a string that begins with a
“<span class="quote"><code class="literal">'</code></span>” or
“<span class="quote"><code class="literal">"</code></span>” quote character, but have not
yet entered the matching quote that terminates the string. This
often indicates that you have inadvertently left out a quote
character. For example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM my_table WHERE name = 'Smith AND age < 30;</code></strong>
'>
</pre><p>
If you enter this <code class="literal">SELECT</code> statement, then press
<span><strong class="keycap">Enter</strong></span> and wait for the result, nothing happens.
Instead of wondering why this query takes so long, notice the clue
provided by the <code class="literal">'></code> prompt. It tells you that
<span><strong class="command">mysql</strong></span> expects to see the rest of an
unterminated string. (Do you see the error in the statement? The
string <code class="literal">'Smith</code> is missing the second single
quote mark.)
</p><p>
At this point, what do you do? The simplest thing is to cancel the
command. However, you cannot just type <code class="literal">\c</code> in
this case, because <span><strong class="command">mysql</strong></span> interprets it as part
of the string that it is collecting. Instead, enter the closing
quote character (so <span><strong class="command">mysql</strong></span> knows you've finished
the string), then type <code class="literal">\c</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM my_table WHERE name = 'Smith AND age < 30;</code></strong>
'> <strong class="userinput"><code>'\c</code></strong>
mysql>
</pre><p>
The prompt changes back to <code class="literal">mysql></code>,
indicating that <span><strong class="command">mysql</strong></span> is ready for a new
command.
</p><p>
The <code class="literal">`></code> prompt is similar to the
<code class="literal">'></code> and <code class="literal">"></code> prompts, but
indicates that you have begun but not completed a backtick-quoted
identifier.
</p><p>
It is important to know what the <code class="literal">'></code>,
<code class="literal">"></code>, and <code class="literal">`></code> prompts
signify, because if you mistakenly enter an unterminated string,
any further lines you type appear to be ignored by
<span><strong class="command">mysql</strong></span> — including a line containing
<code class="literal">QUIT</code>. This can be quite confusing, especially
if you do not know that you need to supply the terminating quote
before you can cancel the current command.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="database-use"></a>3.3. Creating and Using a Database</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="tutorial.html#creating-database">3.3.1. Creating and Selecting a Database</a></span></dt><dt><span class="section"><a href="tutorial.html#creating-tables">3.3.2. Creating a Table</a></span></dt><dt><span class="section"><a href="tutorial.html#loading-tables">3.3.3. Loading Data into a Table</a></span></dt><dt><span class="section"><a href="tutorial.html#retrieving-data">3.3.4. Retrieving Information from a Table</a></span></dt></dl></div><a class="indexterm" name="id2541034"></a><a class="indexterm" name="id2541046"></a><a class="indexterm" name="id2541059"></a><p>
Once you know how to enter commands, you are ready to access a
database.
</p><p>
Suppose that you have several pets in your home (your menagerie)
and you would like to keep track of various types of information
about them. You can do so by creating tables to hold your data and
loading them with the desired information. Then you can answer
different sorts of questions about your animals by retrieving data
from the tables. This section shows you how to:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Create a database
</p></li><li><p>
Create a table
</p></li><li><p>
Load data into the table
</p></li><li><p>
Retrieve data from the table in various ways
</p></li><li><p>
Use multiple tables
</p></li></ul></div><p>
The menagerie database is simple (deliberately), but it is not
difficult to think of real-world situations in which a similar
type of database might be used. For example, a database like this
could be used by a farmer to keep track of livestock, or by a
veterinarian to keep track of patient records. A menagerie
distribution containing some of the queries and sample data used
in the following sections can be obtained from the MySQL Web site.
It is available in both compressed <span><strong class="command">tar</strong></span> file and
Zip formats at <a href="http://dev.mysql.com/doc/" target="_top">http://dev.mysql.com/doc/</a>.
</p><p>
Use the <code class="literal">SHOW</code> statement to find out what
databases currently exist on the server:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW DATABASES;</code></strong>
+----------+
| Database |
+----------+
| mysql |
| test |
| tmp |
+----------+
</pre><p>
The <code class="literal">mysql</code> database describes user access
privileges. The <code class="literal">test</code> database often is
available as a workspace for users to try things out.
</p><p>
The list of databases displayed by the statement may be different
on your machine; <code class="literal">SHOW DATABASES</code> does not show
databases that you have no privileges for if you do not have the
<code class="literal">SHOW DATABASES</code> privilege. See
<a href="sql-syntax.html#show-databases" title="12.5.4.8. SHOW DATABASES Syntax">Section 12.5.4.8, “<code class="literal">SHOW DATABASES</code> Syntax”</a>.
</p><p>
If the <code class="literal">test</code> database exists, try to access it:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>USE test</code></strong>
Database changed
</pre><p>
Note that <code class="literal">USE</code>, like <code class="literal">QUIT</code>,
does not require a semicolon. (You can terminate such statements
with a semicolon if you like; it does no harm.) The
<code class="literal">USE</code> statement is special in another way, too:
it must be given on a single line.
</p><p>
You can use the <code class="literal">test</code> database (if you have
access to it) for the examples that follow, but anything you
create in that database can be removed by anyone else with access
to it. For this reason, you should probably ask your MySQL
administrator for permission to use a database of your own.
Suppose that you want to call yours <code class="literal">menagerie</code>.
The administrator needs to execute a command like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';</code></strong>
</pre><p>
where <code class="literal">your_mysql_name</code> is the MySQL user name
assigned to you and <code class="literal">your_client_host</code> is the
host from which you connect to the server.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="creating-database"></a>3.3.1. Creating and Selecting a Database</h3></div></div></div><a class="indexterm" name="id2541322"></a><a class="indexterm" name="id2541335"></a><p>
If the administrator creates your database for you when setting
up your permissions, you can begin using it. Otherwise, you need
to create it yourself:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE DATABASE menagerie;</code></strong>
</pre><p>
Under Unix, database names are case sensitive (unlike SQL
keywords), so you must always refer to your database as
<code class="literal">menagerie</code>, not as
<code class="literal">Menagerie</code>, <code class="literal">MENAGERIE</code>, or
some other variant. This is also true for table names. (Under
Windows, this restriction does not apply, although you must
refer to databases and tables using the same lettercase
throughout a given query. However, for a variety of reasons, our
recommended best practice is always to use the same lettercase
that was used when the database was created.)
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
If you get an error such as <span class="errortext">ERROR 1044 (42000):
Access denied for user 'monty'@'localhost' to database
'menagerie'</span> when attempting to create a database,
this means that your user account does not have the necessary
privileges to do so. Discuss this with the administrator or
see <a href="server-administration.html#privilege-system" title="5.7. The MySQL Access Privilege System">Section 5.7, “The MySQL Access Privilege System”</a>.
</p></div><p>
Creating a database does not select it for use; you must do that
explicitly. To make <code class="literal">menagerie</code> the current
database, use this command:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>USE menagerie;</code></strong>
Database changed
</pre><p>
Your database needs to be created only once, but you must select
it for use each time you begin a <span><strong class="command">mysql</strong></span>
session. You can do this by issuing a <code class="literal">USE</code>
statement as shown in the example. Alternatively, you can select
the database on the command line when you invoke
<span><strong class="command">mysql</strong></span>. Just specify its name after any
connection parameters that you might need to provide. For
example:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql -h <em class="replaceable"><code>host</code></em> -u <em class="replaceable"><code>user</code></em> -p menagerie</code></strong>
Enter password: <strong class="userinput"><code>********</code></strong>
</pre><p>
Note that <code class="literal">menagerie</code> in the command just shown
is <span class="bold"><strong>not</strong></span> your password. If you
want to supply your password on the command line after the
<code class="literal">-p</code> option, you must do so with no intervening
space (for example, as <code class="literal">-pmypassword</code>,
<span class="emphasis"><em>not</em></span> as <code class="literal">-p mypassword</code>).
However, putting your password on the command line is not
recommended, because doing so exposes it to snooping by other
users logged in on your machine.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="creating-tables"></a>3.3.2. Creating a Table</h3></div></div></div><a class="indexterm" name="id2541545"></a><a class="indexterm" name="id2541558"></a><p>
Creating the database is the easy part, but at this point it's
empty, as <code class="literal">SHOW TABLES</code> tells you:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW TABLES;</code></strong>
Empty set (0.00 sec)
</pre><p>
The harder part is deciding what the structure of your database
should be: what tables you need and what columns should be in
each of them.
</p><p>
You want a table that contains a record for each of your pets.
This can be called the <code class="literal">pet</code> table, and it
should contain, as a bare minimum, each animal's name. Because
the name by itself is not very interesting, the table should
contain other information. For example, if more than one person
in your family keeps pets, you might want to list each animal's
owner. You might also want to record some basic descriptive
information such as species and sex.
</p><p>
How about age? That might be of interest, but it's not a good
thing to store in a database. Age changes as time passes, which
means you'd have to update your records often. Instead, it's
better to store a fixed value such as date of birth. Then,
whenever you need age, you can calculate it as the difference
between the current date and the birth date. MySQL provides
functions for doing date arithmetic, so this is not difficult.
Storing birth date rather than age has other advantages, too:
</p><div class="itemizedlist"><ul type="disc"><li><p>
You can use the database for tasks such as generating
reminders for upcoming pet birthdays. (If you think this
type of query is somewhat silly, note that it is the same
question you might ask in the context of a business database
to identify clients to whom you need to send out birthday
greetings in the current week or month, for that
computer-assisted personal touch.)
</p></li><li><p>
You can calculate age in relation to dates other than the
current date. For example, if you store death date in the
database, you can easily calculate how old a pet was when it
died.
</p></li></ul></div><p>
You can probably think of other types of information that would
be useful in the <code class="literal">pet</code> table, but the ones
identified so far are sufficient: name, owner, species, sex,
birth, and death.
</p><p>
Use a <code class="literal">CREATE TABLE</code> statement to specify the
layout of your table:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),</code></strong>
-> <strong class="userinput"><code>species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);</code></strong>
</pre><p>
<code class="literal">VARCHAR</code> is a good choice for the
<code class="literal">name</code>, <code class="literal">owner</code>, and
<code class="literal">species</code> columns because the column values
vary in length. The lengths in those column definitions need not
all be the same, and need not be <code class="literal">20</code>. You can
normally pick any length from <code class="literal">1</code> to
<code class="literal">65535</code>, whatever seems most reasonable to you.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Prior to MySQL 5.0.3, the upper limit was 255.) If you make a
poor choice and it turns out later that you need a longer
field, MySQL provides an <code class="literal">ALTER TABLE</code>
statement.
</p></div><p>
Several types of values can be chosen to represent sex in animal
records, such as <code class="literal">'m'</code> and
<code class="literal">'f'</code>, or perhaps <code class="literal">'male'</code> and
<code class="literal">'female'</code>. It is simplest to use the single
characters <code class="literal">'m'</code> and <code class="literal">'f'</code>.
</p><p>
The use of the <code class="literal">DATE</code> data type for the
<code class="literal">birth</code> and <code class="literal">death</code> columns is
a fairly obvious choice.
</p><p>
Once you have created a table, <code class="literal">SHOW TABLES</code>
should produce some output:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW TABLES;</code></strong>
+---------------------+
| Tables in menagerie |
+---------------------+
| pet |
+---------------------+
</pre><p>
To verify that your table was created the way you expected, use
a <code class="literal">DESCRIBE</code> statement:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>DESCRIBE pet;</code></strong>
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
</pre><p>
You can use <code class="literal">DESCRIBE</code> any time, for example,
if you forget the names of the columns in your table or what
types they have.
</p><p>
For more information about MySQL data types, see
<a href="data-types.html" title="Chapter 10. Data Types">Chapter 10, <i>Data Types</i></a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="loading-tables"></a>3.3.3. Loading Data into a Table</h3></div></div></div><a class="indexterm" name="id2541916"></a><a class="indexterm" name="id2541928"></a><a class="indexterm" name="id2541941"></a><p>
After creating your table, you need to populate it. The
<code class="literal">LOAD DATA</code> and <code class="literal">INSERT</code>
statements are useful for this.
</p><p>
Suppose that your pet records can be described as shown here.
(Observe that MySQL expects dates in
<code class="literal">'YYYY-MM-DD'</code> format; this may be different
from what you are used to.)
</p><div class="informaltable"><table border="1"><colgroup><col><col><col><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>name</strong></span></td><td><span class="bold"><strong>owner</strong></span></td><td><span class="bold"><strong>species</strong></span></td><td><span class="bold"><strong>sex</strong></span></td><td><span class="bold"><strong>birth</strong></span></td><td><span class="bold"><strong>death</strong></span></td></tr><tr><td>Fluffy</td><td>Harold</td><td>cat</td><td>f</td><td>1993-02-04</td><td> </td></tr><tr><td>Claws</td><td>Gwen</td><td>cat</td><td>m</td><td>1994-03-17</td><td> </td></tr><tr><td>Buffy</td><td>Harold</td><td>dog</td><td>f</td><td>1989-05-13</td><td> </td></tr><tr><td>Fang</td><td>Benny</td><td>dog</td><td>m</td><td>1990-08-27</td><td> </td></tr><tr><td>Bowser</td><td>Diane</td><td>dog</td><td>m</td><td>1979-08-31</td><td>1995-07-29</td></tr><tr><td>Chirpy</td><td>Gwen</td><td>bird</td><td>f</td><td>1998-09-11</td><td> </td></tr><tr><td>Whistler</td><td>Gwen</td><td>bird</td><td> </td><td>1997-12-09</td><td> </td></tr><tr><td>Slim</td><td>Benny</td><td>snake</td><td>m</td><td>1996-04-29</td><td> </td></tr></tbody></table></div><p>
Because you are beginning with an empty table, an easy way to
populate it is to create a text file containing a row for each
of your animals, then load the contents of the file into the
table with a single statement.
</p><p>
You could create a text file <code class="filename">pet.txt</code>
containing one record per line, with values separated by tabs,
and given in the order in which the columns were listed in the
<code class="literal">CREATE TABLE</code> statement. For missing values
(such as unknown sexes or death dates for animals that are still
living), you can use <code class="literal">NULL</code> values. To
represent these in your text file, use <code class="literal">\N</code>
(backslash, capital-N). For example, the record for Whistler the
bird would look like this (where the whitespace between values
is a single tab character):
</p><pre class="programlisting">Whistler Gwen bird \N 1997-12-09 \N
</pre><p>
To load the text file <code class="filename">pet.txt</code> into the
<code class="literal">pet</code> table, use this command:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;</code></strong>
</pre><p>
Note that if you created the file on Windows with an editor that
uses <code class="literal">\r\n</code> as a line terminator, you should
use:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet</code></strong>
-> <strong class="userinput"><code>LINES TERMINATED BY '\r\n';</code></strong>
</pre><p>
(On an Apple machine running OS X, you would likely want to use
<code class="literal">LINES TERMINATED BY '\r'</code>.)
</p><p>
You can specify the column value separator and end of line
marker explicitly in the <code class="literal">LOAD DATA</code> statement
if you wish, but the defaults are tab and linefeed. These are
sufficient for the statement to read the file
<code class="filename">pet.txt</code> properly.
</p><p>
If the statement fails, it is likely that your MySQL
installation does not have local file capability enabled by
default. See <a href="server-administration.html#load-data-local" title="5.6.4. Security Issues with LOAD DATA LOCAL">Section 5.6.4, “Security Issues with <code class="literal">LOAD DATA LOCAL</code>”</a>, for information
on how to change this.
</p><p>
When you want to add new records one at a time, the
<code class="literal">INSERT</code> statement is useful. In its simplest
form, you supply values for each column, in the order in which
the columns were listed in the <code class="literal">CREATE TABLE</code>
statement. Suppose that Diane gets a new hamster named
“<span class="quote">Puffball.</span>” You could add a new record using an
<code class="literal">INSERT</code> statement like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO pet</code></strong>
-> <strong class="userinput"><code>VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);</code></strong>
</pre><p>
Note that string and date values are specified as quoted strings
here. Also, with <code class="literal">INSERT</code>, you can insert
<code class="literal">NULL</code> directly to represent a missing value.
You do not use <code class="literal">\N</code> like you do with
<code class="literal">LOAD DATA</code>.
</p><p>
From this example, you should be able to see that there would be
a lot more typing involved to load your records initially using
several <code class="literal">INSERT</code> statements rather than a
single <code class="literal">LOAD DATA</code> statement.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="retrieving-data"></a>3.3.4. Retrieving Information from a Table</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="tutorial.html#selecting-all">3.3.4.1. Selecting All Data</a></span></dt><dt><span class="section"><a href="tutorial.html#selecting-rows">3.3.4.2. Selecting Particular Rows</a></span></dt><dt><span class="section"><a href="tutorial.html#selecting-columns">3.3.4.3. Selecting Particular Columns</a></span></dt><dt><span class="section"><a href="tutorial.html#sorting-rows">3.3.4.4. Sorting Rows</a></span></dt><dt><span class="section"><a href="tutorial.html#date-calculations">3.3.4.5. Date Calculations</a></span></dt><dt><span class="section"><a href="tutorial.html#working-with-null">3.3.4.6. Working with <code class="literal">NULL</code> Values</a></span></dt><dt><span class="section"><a href="tutorial.html#pattern-matching">3.3.4.7. Pattern Matching</a></span></dt><dt><span class="section"><a href="tutorial.html#counting-rows">3.3.4.8. Counting Rows</a></span></dt><dt><span class="section"><a href="tutorial.html#multiple-tables">3.3.4.9. Using More Than one Table</a></span></dt></dl></div><a class="indexterm" name="id2542509"></a><a class="indexterm" name="id2542521"></a><a class="indexterm" name="id2542534"></a><a class="indexterm" name="id2542546"></a><p>
The <code class="literal">SELECT</code> statement is used to pull
information from a table. The general form of the statement is:
</p><pre class="programlisting">SELECT <em class="replaceable"><code>what_to_select</code></em>
FROM <em class="replaceable"><code>which_table</code></em>
WHERE <em class="replaceable"><code>conditions_to_satisfy</code></em>;
</pre><p>
<em class="replaceable"><code>what_to_select</code></em> indicates what you
want to see. This can be a list of columns, or
<code class="literal">*</code> to indicate “<span class="quote">all columns.</span>”
<em class="replaceable"><code>which_table</code></em> indicates the table from
which you want to retrieve data. The <code class="literal">WHERE</code>
clause is optional. If it is present,
<em class="replaceable"><code>conditions_to_satisfy</code></em> specifies one
or more conditions that rows must satisfy to qualify for
retrieval.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="selecting-all"></a>3.3.4.1. Selecting All Data</h4></div></div></div><p>
The simplest form of <code class="literal">SELECT</code> retrieves
everything from a table:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet;</code></strong>
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
</pre><p>
This form of <code class="literal">SELECT</code> is useful if you want
to review your entire table, for example, after you've just
loaded it with your initial data set. For example, you may
happen to think that the birth date for Bowser doesn't seem
quite right. Consulting your original pedigree papers, you
find that the correct birth year should be 1989, not 1979.
</p><p>
There are at least two ways to fix this:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Edit the file <code class="filename">pet.txt</code> to correct the
error, then empty the table and reload it using
<code class="literal">DELETE</code> and <code class="literal">LOAD
DATA</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>DELETE FROM pet;</code></strong>
mysql> <strong class="userinput"><code>LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;</code></strong>
</pre><p>
However, if you do this, you must also re-enter the record
for Puffball.
</p></li><li><p>
Fix only the erroneous record with an
<code class="literal">UPDATE</code> statement:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';</code></strong>
</pre><p>
The <code class="literal">UPDATE</code> changes only the record in
question and does not require you to reload the table.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="selecting-rows"></a>3.3.4.2. Selecting Particular Rows</h4></div></div></div><a class="indexterm" name="id2542788"></a><a class="indexterm" name="id2542801"></a><p>
As shown in the preceding section, it is easy to retrieve an
entire table. Just omit the <code class="literal">WHERE</code> clause
from the <code class="literal">SELECT</code> statement. But typically
you don't want to see the entire table, particularly when it
becomes large. Instead, you're usually more interested in
answering a particular question, in which case you specify
some constraints on the information you want. Let's look at
some selection queries in terms of questions about your pets
that they answer.
</p><p>
You can select only particular rows from your table. For
example, if you want to verify the change that you made to
Bowser's birth date, select Bowser's record like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE name = 'Bowser';</code></strong>
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
</pre><p>
The output confirms that the year is correctly recorded as
1989, not 1979.
</p><p>
String comparisons normally are case-insensitive, so you can
specify the name as <code class="literal">'bowser'</code>,
<code class="literal">'BOWSER'</code>, and so forth. The query result is
the same.
</p><p>
You can specify conditions on any column, not just
<code class="literal">name</code>. For example, if you want to know
which animals were born during or after 1998, test the
<code class="literal">birth</code> column:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE birth >= '1998-1-1';</code></strong>
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
</pre><p>
You can combine conditions, for example, to locate female
dogs:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';</code></strong>
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
</pre><p>
The preceding query uses the
<a href="functions.html#operator_and"><code class="literal">AND</code></a> logical operator. There
is also an <a href="functions.html#operator_or"><code class="literal">OR</code></a> operator:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';</code></strong>
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+-------+---------+------+------------+-------+
</pre><p>
<a href="functions.html#operator_and"><code class="literal">AND</code></a> and
<a href="functions.html#operator_or"><code class="literal">OR</code></a> may be intermixed,
although <a href="functions.html#operator_and"><code class="literal">AND</code></a> has higher
precedence than <a href="functions.html#operator_or"><code class="literal">OR</code></a>. If you
use both operators, it is a good idea to use parentheses to
indicate explicitly how conditions should be grouped:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')</code></strong>
-> <strong class="userinput"><code>OR (species = 'dog' AND sex = 'f');</code></strong>
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="selecting-columns"></a>3.3.4.3. Selecting Particular Columns</h4></div></div></div><a class="indexterm" name="id2543052"></a><a class="indexterm" name="id2543064"></a><p>
If you do not want to see entire rows from your table, just
name the columns in which you are interested, separated by
commas. For example, if you want to know when your animals
were born, select the <code class="literal">name</code> and
<code class="literal">birth</code> columns:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, birth FROM pet;</code></strong>
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+
</pre><p>
To find out who owns pets, use this query:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT owner FROM pet;</code></strong>
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+
</pre><a class="indexterm" name="id2543133"></a><p>
Notice that the query simply retrieves the
<code class="literal">owner</code> column from each record, and some of
them appear more than once. To minimize the output, retrieve
each unique output record just once by adding the keyword
<code class="literal">DISTINCT</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT DISTINCT owner FROM pet;</code></strong>
+--------+
| owner |
+--------+
| Benny |
| Diane |
| Gwen |
| Harold |
+--------+
</pre><p>
You can use a <code class="literal">WHERE</code> clause to combine row
selection with column selection. For example, to get birth
dates for dogs and cats only, use this query:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, species, birth FROM pet</code></strong>
-> <strong class="userinput"><code>WHERE species = 'dog' OR species = 'cat';</code></strong>
+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Fluffy | cat | 1993-02-04 |
| Claws | cat | 1994-03-17 |
| Buffy | dog | 1989-05-13 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
+--------+---------+------------+
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="sorting-rows"></a>3.3.4.4. Sorting Rows</h4></div></div></div><a class="indexterm" name="id2543221"></a><a class="indexterm" name="id2543230"></a><a class="indexterm" name="id2543242"></a><a class="indexterm" name="id2543254"></a><a class="indexterm" name="id2543267"></a><p>
You may have noticed in the preceding examples that the result
rows are displayed in no particular order. It's often easier
to examine query output when the rows are sorted in some
meaningful way. To sort a result, use an <code class="literal">ORDER
BY</code> clause.
</p><p>
Here are animal birthdays, sorted by date:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, birth FROM pet ORDER BY birth;</code></strong>
+----------+------------+
| name | birth |
+----------+------------+
| Buffy | 1989-05-13 |
| Bowser | 1989-08-31 |
| Fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Slim | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+
</pre><p>
On character type columns, sorting — like all other
comparison operations — is normally performed in a
case-insensitive fashion. This means that the order is
undefined for columns that are identical except for their
case. You can force a case-sensitive sort for a column by
using <code class="literal">BINARY</code> like so: <code class="literal">ORDER BY
BINARY <em class="replaceable"><code>col_name</code></em></code>.
</p><p>
The default sort order is ascending, with smallest values
first. To sort in reverse (descending) order, add the
<code class="literal">DESC</code> keyword to the name of the column you
are sorting by:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, birth FROM pet ORDER BY birth DESC;</code></strong>
+----------+------------+
| name | birth |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Claws | 1994-03-17 |
| Fluffy | 1993-02-04 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Buffy | 1989-05-13 |
+----------+------------+
</pre><p>
You can sort on multiple columns, and you can sort different
columns in different directions. For example, to sort by type
of animal in ascending order, then by birth date within animal
type in descending order (youngest animals first), use the
following query:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, species, birth FROM pet</code></strong>
-> <strong class="userinput"><code>ORDER BY species, birth DESC;</code></strong>
+----------+---------+------------+
| name | species | birth |
+----------+---------+------------+
| Chirpy | bird | 1998-09-11 |
| Whistler | bird | 1997-12-09 |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
| Buffy | dog | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim | snake | 1996-04-29 |
+----------+---------+------------+
</pre><p>
Note that the <code class="literal">DESC</code> keyword applies only to
the column name immediately preceding it
(<code class="literal">birth</code>); it does not affect the
<code class="literal">species</code> column sort order.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="date-calculations"></a>3.3.4.5. Date Calculations</h4></div></div></div><a class="indexterm" name="id2543445"></a><a class="indexterm" name="id2543454"></a><a class="indexterm" name="id2543466"></a><a class="indexterm" name="id2543479"></a><p>
MySQL provides several functions that you can use to perform
calculations on dates, for example, to calculate ages or
extract parts of dates.
</p><p>
To determine how many years old each of your pets is, compute
the difference in the year part of the current date and the
birth date, then subtract one if the current date occurs
earlier in the calendar year than the birth date. The
following query shows, for each pet, the birth date, the
current date, and the age in years.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, birth, CURDATE(),</code></strong>
-> <strong class="userinput"><code>(YEAR(CURDATE())-YEAR(birth))</code></strong>
-> <strong class="userinput"><code>- (RIGHT(CURDATE(),5)<RIGHT(birth,5))</code></strong>
-> <strong class="userinput"><code>AS age</code></strong>
-> <strong class="userinput"><code>FROM pet;</code></strong>
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
+----------+------------+------------+------+
</pre><p>
Here, <a href="functions.html#function_year"><code class="literal">YEAR()</code></a> pulls out the
year part of a date and
<a href="functions.html#function_right"><code class="literal">RIGHT()</code></a> pulls off the
rightmost five characters that represent the
<code class="literal">MM-DD</code> (calendar year) part of the date. The
part of the expression that compares the
<code class="literal">MM-DD</code> values evaluates to 1 or 0, which
adjusts the year difference down a year if
<a href="functions.html#function_curdate"><code class="literal">CURDATE()</code></a> occurs earlier in
the year than <code class="literal">birth</code>. The full expression is
somewhat ungainly, so an <span class="emphasis"><em>alias</em></span>
(<code class="literal">age</code>) is used to make the output column
label more meaningful.
</p><p>
The query works, but the result could be scanned more easily
if the rows were presented in some order. This can be done by
adding an <code class="literal">ORDER BY name</code> clause to sort the
output by name:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, birth, CURDATE(),</code></strong>
-> <strong class="userinput"><code>(YEAR(CURDATE())-YEAR(birth))</code></strong>
-> <strong class="userinput"><code>- (RIGHT(CURDATE(),5)<RIGHT(birth,5))</code></strong>
-> <strong class="userinput"><code>AS age</code></strong>
-> <strong class="userinput"><code>FROM pet ORDER BY name;</code></strong>
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
+----------+------------+------------+------+
</pre><p>
To sort the output by <code class="literal">age</code> rather than
<code class="literal">name</code>, just use a different <code class="literal">ORDER
BY</code> clause:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, birth, CURDATE(),</code></strong>
-> <strong class="userinput"><code>(YEAR(CURDATE())-YEAR(birth))</code></strong>
-> <strong class="userinput"><code>- (RIGHT(CURDATE(),5)<RIGHT(birth,5))</code></strong>
-> <strong class="userinput"><code>AS age</code></strong>
-> <strong class="userinput"><code>FROM pet ORDER BY age;</code></strong>
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
+----------+------------+------------+------+
</pre><p>
A similar query can be used to determine age at death for
animals that have died. You determine which animals these are
by checking whether the <code class="literal">death</code> value is
<code class="literal">NULL</code>. Then, for those with
non-<code class="literal">NULL</code> values, compute the difference
between the <code class="literal">death</code> and
<code class="literal">birth</code> values:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, birth, death,</code></strong>
-> <strong class="userinput"><code>(YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))</code></strong>
-> <strong class="userinput"><code>AS age</code></strong>
-> <strong class="userinput"><code>FROM pet WHERE death IS NOT NULL ORDER BY age;</code></strong>
+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+------+
</pre><p>
The query uses <code class="literal">death IS NOT NULL</code> rather
than <code class="literal">death <> NULL</code> because
<code class="literal">NULL</code> is a special value that cannot be
compared using the usual comparison operators. This is
discussed later. See <a href="tutorial.html#working-with-null" title="3.3.4.6. Working with NULL Values">Section 3.3.4.6, “Working with <code class="literal">NULL</code> Values”</a>.
</p><p>
What if you want to know which animals have birthdays next
month? For this type of calculation, year and day are
irrelevant; you simply want to extract the month part of the
<code class="literal">birth</code> column. MySQL provides several
functions for extracting parts of dates, such as
<a href="functions.html#function_year"><code class="literal">YEAR()</code></a>,
<a href="functions.html#function_month"><code class="literal">MONTH()</code></a>, and
<a href="functions.html#function_dayofmonth"><code class="literal">DAYOFMONTH()</code></a>.
<a href="functions.html#function_month"><code class="literal">MONTH()</code></a> is the appropriate
function here. To see how it works, run a simple query that
displays the value of both <code class="literal">birth</code> and
<code class="literal">MONTH(birth)</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, birth, MONTH(birth) FROM pet;</code></strong>
+----------+------------+--------------+
| name | birth | MONTH(birth) |
+----------+------------+--------------+
| Fluffy | 1993-02-04 | 2 |
| Claws | 1994-03-17 | 3 |
| Buffy | 1989-05-13 | 5 |
| Fang | 1990-08-27 | 8 |
| Bowser | 1989-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puffball | 1999-03-30 | 3 |
+----------+------------+--------------+
</pre><p>
Finding animals with birthdays in the upcoming month is also
simple. Suppose that the current month is April. Then the
month value is <code class="literal">4</code> and you can look for
animals born in May (month <code class="literal">5</code>) like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, birth FROM pet WHERE MONTH(birth) = 5;</code></strong>
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
</pre><p>
There is a small complication if the current month is
December. You cannot merely add one to the month number
(<code class="literal">12</code>) and look for animals born in month
<code class="literal">13</code>, because there is no such month.
Instead, you look for animals born in January (month
<code class="literal">1</code>).
</p><p>
You can write the query so that it works no matter what the
current month is, so that you do not have to use the number
for a particular month. <code class="literal">DATE_ADD()</code> allows
you to add a time interval to a given date. If you add a month
to the value of <a href="functions.html#function_curdate"><code class="literal">CURDATE()</code></a>,
then extract the month part with
<a href="functions.html#function_month"><code class="literal">MONTH()</code></a>, the result produces
the month in which to look for birthdays:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, birth FROM pet</code></strong>
-> <strong class="userinput"><code>WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));</code></strong>
</pre><p>
A different way to accomplish the same task is to add
<code class="literal">1</code> to get the next month after the current
one after using the modulo function (<code class="literal">MOD</code>)
to wrap the month value to <code class="literal">0</code> if it is
currently <code class="literal">12</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT name, birth FROM pet</code></strong>
-> <strong class="userinput"><code>WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;</code></strong>
</pre><p>
Note that <code class="literal">MONTH</code> returns a number between
<code class="literal">1</code> and <code class="literal">12</code>. And
<code class="literal">MOD(something,12)</code> returns a number between
<code class="literal">0</code> and <code class="literal">11</code>. So the
addition has to be after the
<a href="functions.html#function_mod"><code class="literal">MOD()</code></a>, otherwise we would go
from November (<code class="literal">11</code>) to January
(<code class="literal">1</code>).
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="working-with-null"></a>3.3.4.6. Working with <code class="literal">NULL</code> Values</h4></div></div></div><a class="indexterm" name="id2544162"></a><a class="indexterm" name="id2544171"></a><p>
The <code class="literal">NULL</code> value can be surprising until you
get used to it. Conceptually, <code class="literal">NULL</code> means
“<span class="quote">a missing unknown value</span>” and it is treated
somewhat differently from other values. To test for
<code class="literal">NULL</code>, you cannot use the arithmetic
comparison operators such as <code class="literal">=</code>,
<code class="literal"><</code>, or <code class="literal"><></code>. To
demonstrate this for yourself, try the following query:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;</code></strong>
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
</pre><p>
Clearly you get no meaningful results from these comparisons.
Use the <a href="functions.html#operator_is-null"><code class="literal">IS NULL</code></a> and
<a href="functions.html#operator_is-not-null"><code class="literal">IS NOT NULL</code></a> operators
instead:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT 1 IS NULL, 1 IS NOT NULL;</code></strong>
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
</pre><p>
Note that in MySQL, <code class="literal">0</code> or
<code class="literal">NULL</code> means false and anything else means
true. The default truth value from a boolean operation is
<code class="literal">1</code>.
</p><p>
This special treatment of <code class="literal">NULL</code> is why, in
the previous section, it was necessary to determine which
animals are no longer alive using <code class="literal">death IS NOT
NULL</code> instead of <code class="literal">death <>
NULL</code>.
</p><p>
Two <code class="literal">NULL</code> values are regarded as equal in a
<code class="literal">GROUP BY</code>.
</p><p>
When doing an <code class="literal">ORDER BY</code>,
<code class="literal">NULL</code> values are presented first if you do
<code class="literal">ORDER BY ... ASC</code> and last if you do
<code class="literal">ORDER BY ... DESC</code>.
</p><p>
A common error when working with <code class="literal">NULL</code> is to
assume that it is not possible to insert a zero or an empty
string into a column defined as <code class="literal">NOT NULL</code>,
but this is not the case. These are in fact values, whereas
<code class="literal">NULL</code> means “<span class="quote">not having a
value.</span>” You can test this easily enough by using
<code class="literal">IS </code>[<code class="literal">NOT</code>]<code class="literal">
NULL</code> as shown:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;</code></strong>
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
| 0 | 1 | 0 | 1 |
+-----------+---------------+------------+----------------+
</pre><p>
Thus it is entirely possible to insert a zero or empty string
into a <code class="literal">NOT NULL</code> column, as these are in
fact <code class="literal">NOT NULL</code>. See
<a href="error-handling.html#problems-with-null" title="B.1.5.3. Problems with NULL Values">Section B.1.5.3, “Problems with <code class="literal">NULL</code> Values”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="pattern-matching"></a>3.3.4.7. Pattern Matching</h4></div></div></div><a class="indexterm" name="id2544477"></a><a class="indexterm" name="id2544486"></a><a class="indexterm" name="id2544498"></a><p>
MySQL provides standard SQL pattern matching as well as a form
of pattern matching based on extended regular expressions
similar to those used by Unix utilities such as
<span><strong class="command">vi</strong></span>, <span><strong class="command">grep</strong></span>, and
<span><strong class="command">sed</strong></span>.
</p><p>
SQL pattern matching allows you to use
“<span class="quote"><code class="literal">_</code></span>” to match any single
character and “<span class="quote"><code class="literal">%</code></span>” to match an
arbitrary number of characters (including zero characters). In
MySQL, SQL patterns are case-insensitive by default. Some
examples are shown here. Note that you do not use
<code class="literal">=</code> or <code class="literal"><></code> when you
use SQL patterns; use the <code class="literal">LIKE</code> or
<code class="literal">NOT LIKE</code> comparison operators instead.
</p><p>
To find names beginning with
“<span class="quote"><code class="literal">b</code></span>”:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE name LIKE 'b%';</code></strong>
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
</pre><p>
To find names ending with
“<span class="quote"><code class="literal">fy</code></span>”:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE name LIKE '%fy';</code></strong>
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
</pre><p>
To find names containing a
“<span class="quote"><code class="literal">w</code></span>”:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE name LIKE '%w%';</code></strong>
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
</pre><p>
To find names containing exactly five characters, use five
instances of the “<span class="quote"><code class="literal">_</code></span>” pattern
character:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE name LIKE '_____';</code></strong>
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
</pre><p>
The other type of pattern matching provided by MySQL uses
extended regular expressions. When you test for a match for
this type of pattern, use the <code class="literal">REGEXP</code> and
<code class="literal">NOT REGEXP</code> operators (or
<code class="literal">RLIKE</code> and <code class="literal">NOT RLIKE</code>,
which are synonyms).
</p><p>
Some characteristics of extended regular expressions are:
</p><div class="itemizedlist"><ul type="disc"><li><p>
“<span class="quote"><code class="literal">.</code></span>” matches any single
character.
</p></li><li><p>
A character class “<span class="quote"><code class="literal">[...]</code></span>”
matches any character within the brackets. For example,
“<span class="quote"><code class="literal">[abc]</code></span>” matches
“<span class="quote"><code class="literal">a</code></span>”,
“<span class="quote"><code class="literal">b</code></span>”, or
“<span class="quote"><code class="literal">c</code></span>”. To name a range of
characters, use a dash.
“<span class="quote"><code class="literal">[a-z]</code></span>” matches any
letter, whereas “<span class="quote"><code class="literal">[0-9]</code></span>”
matches any digit.
</p></li><li><p>
“<span class="quote"><code class="literal">*</code></span>” matches zero or more
instances of the thing preceding it. For example,
“<span class="quote"><code class="literal">x*</code></span>” matches any number of
“<span class="quote"><code class="literal">x</code></span>” characters,
“<span class="quote"><code class="literal">[0-9]*</code></span>” matches any
number of digits, and “<span class="quote"><code class="literal">.*</code></span>”
matches any number of anything.
</p></li><li><p>
A <code class="literal">REGEXP</code> pattern match succeeds if the
pattern matches anywhere in the value being tested. (This
differs from a <code class="literal">LIKE</code> pattern match,
which succeeds only if the pattern matches the entire
value.)
</p></li><li><p>
To anchor a pattern so that it must match the beginning or
end of the value being tested, use
“<span class="quote"><code class="literal">^</code></span>” at the beginning or
“<span class="quote"><code class="literal">$</code></span>” at the end of the
pattern.
</p></li></ul></div><p>
To demonstrate how extended regular expressions work, the
<code class="literal">LIKE</code> queries shown previously are rewritten
here to use <code class="literal">REGEXP</code>.
</p><p>
To find names beginning with
“<span class="quote"><code class="literal">b</code></span>”, use
“<span class="quote"><code class="literal">^</code></span>” to match the beginning of
the name:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE name REGEXP '^b';</code></strong>
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
</pre><p>
If you really want to force a <code class="literal">REGEXP</code>
comparison to be case sensitive, use the
<code class="literal">BINARY</code> keyword to make one of the strings a
binary string. This query matches only lowercase
“<span class="quote"><code class="literal">b</code></span>” at the beginning of a
name:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE name REGEXP BINARY '^b';</code></strong>
</pre><p>
To find names ending with
“<span class="quote"><code class="literal">fy</code></span>”, use
“<span class="quote"><code class="literal">$</code></span>” to match the end of the
name:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE name REGEXP 'fy$';</code></strong>
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
</pre><p>
To find names containing a
“<span class="quote"><code class="literal">w</code></span>”, use this query:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE name REGEXP 'w';</code></strong>
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
</pre><p>
Because a regular expression pattern matches if it occurs
anywhere in the value, it is not necessary in the previous
query to put a wildcard on either side of the pattern to get
it to match the entire value like it would be if you used an
SQL pattern.
</p><p>
To find names containing exactly five characters, use
“<span class="quote"><code class="literal">^</code></span>” and
“<span class="quote"><code class="literal">$</code></span>” to match the beginning and
end of the name, and five instances of
“<span class="quote"><code class="literal">.</code></span>” in between:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE name REGEXP '^.....$';</code></strong>
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
</pre><p>
You could also write the previous query using the
<code class="literal">{<em class="replaceable"><code>n</code></em>}</code>
(“<span class="quote">repeat-<em class="replaceable"><code>n</code></em>-times</span>”)
operator:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM pet WHERE name REGEXP '^.{5}$';</code></strong>
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
</pre><p>
<a href="functions.html#regexp" title="11.4.2. Regular Expressions">Section 11.4.2, “Regular Expressions”</a>, provides more information about the
syntax for regular expressions.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="counting-rows"></a>3.3.4.8. Counting Rows</h4></div></div></div><a class="indexterm" name="id2545180"></a><a class="indexterm" name="id2545192"></a><a class="indexterm" name="id2545205"></a><p>
Databases are often used to answer the question, “<span class="quote">How
often does a certain type of data occur in a table?</span>”
For example, you might want to know how many pets you have, or
how many pets each owner has, or you might want to perform
various kinds of census operations on your animals.
</p><p>
Counting the total number of animals you have is the same
question as “<span class="quote">How many rows are in the
<code class="literal">pet</code> table?</span>” because there is one
record per pet. <a href="functions.html#function_count"><code class="literal">COUNT(*)</code></a>
counts the number of rows, so the query to count your animals
looks like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT COUNT(*) FROM pet;</code></strong>
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
</pre><p>
Earlier, you retrieved the names of the people who owned pets.
You can use <a href="functions.html#function_count"><code class="literal">COUNT()</code></a> if you
want to find out how many pets each owner has:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT owner, COUNT(*) FROM pet GROUP BY owner;</code></strong>
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+--------+----------+
</pre><p>
Note the use of <code class="literal">GROUP BY</code> to group all
records for each <code class="literal">owner</code>. Without it, all you
get is an error message:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT owner, COUNT(*) FROM pet;</code></strong>
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause
</pre><p>
<a href="functions.html#function_count"><code class="literal">COUNT()</code></a> and <code class="literal">GROUP
BY</code> are useful for characterizing your data in
various ways. The following examples show different ways to
perform animal census operations.
</p><p>
Number of animals per species:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT species, COUNT(*) FROM pet GROUP BY species;</code></strong>
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird | 2 |
| cat | 2 |
| dog | 3 |
| hamster | 1 |
| snake | 1 |
+---------+----------+
</pre><p>
Number of animals per sex:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT sex, COUNT(*) FROM pet GROUP BY sex;</code></strong>
+------+----------+
| sex | COUNT(*) |
+------+----------+
| NULL | 1 |
| f | 4 |
| m | 4 |
+------+----------+
</pre><p>
(In this output, <code class="literal">NULL</code> indicates that the
sex is unknown.)
</p><p>
Number of animals per combination of species and sex:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;</code></strong>
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | NULL | 1 |
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
</pre><p>
You need not retrieve an entire table when you use
<a href="functions.html#function_count"><code class="literal">COUNT()</code></a>. For example, the
previous query, when performed just on dogs and cats, looks
like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT species, sex, COUNT(*) FROM pet</code></strong>
-> <strong class="userinput"><code>WHERE species = 'dog' OR species = 'cat'</code></strong>
-> <strong class="userinput"><code>GROUP BY species, sex;</code></strong>
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
+---------+------+----------+
</pre><p>
Or, if you wanted the number of animals per sex only for
animals whose sex is known:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT species, sex, COUNT(*) FROM pet</code></strong>
-> <strong class="userinput"><code>WHERE sex IS NOT NULL</code></strong>
-> <strong class="userinput"><code>GROUP BY species, sex;</code></strong>
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="multiple-tables"></a>3.3.4.9. Using More Than one Table</h4></div></div></div><a class="indexterm" name="id2545508"></a><p>
The <code class="literal">pet</code> table keeps track of which pets you
have. If you want to record other information about them, such
as events in their lives like visits to the vet or when
litters are born, you need another table. What should this
table look like? It needs:
</p><div class="itemizedlist"><ul type="disc"><li><p>
To contain the pet name so that you know which animal each
event pertains to.
</p></li><li><p>
A date so that you know when the event occurred.
</p></li><li><p>
A field to describe the event.
</p></li><li><p>
An event type field, if you want to be able to categorize
events.
</p></li></ul></div><p>
Given these considerations, the <code class="literal">CREATE
TABLE</code> statement for the <code class="literal">event</code>
table might look like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE event (name VARCHAR(20), date DATE,</code></strong>
-> <strong class="userinput"><code>type VARCHAR(15), remark VARCHAR(255));</code></strong>
</pre><p>
As with the <code class="literal">pet</code> table, it's easiest to load
the initial records by creating a tab-delimited text file
containing the information:
</p><div class="informaltable"><table border="1"><colgroup><col><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>name</strong></span></td><td><span class="bold"><strong>date</strong></span></td><td><span class="bold"><strong>type</strong></span></td><td><span class="bold"><strong>remark</strong></span></td></tr><tr><td>Fluffy</td><td>1995-05-15</td><td>litter</td><td>4 kittens, 3 female, 1 male</td></tr><tr><td>Buffy</td><td>1993-06-23</td><td>litter</td><td>5 puppies, 2 female, 3 male</td></tr><tr><td>Buffy</td><td>1994-06-19</td><td>litter</td><td>3 puppies, 3 female</td></tr><tr><td>Chirpy</td><td>1999-03-21</td><td>vet</td><td>needed beak straightened</td></tr><tr><td>Slim</td><td>1997-08-03</td><td>vet</td><td>broken rib</td></tr><tr><td>Bowser</td><td>1991-10-12</td><td>kennel</td><td> </td></tr><tr><td>Fang</td><td>1991-10-12</td><td>kennel</td><td> </td></tr><tr><td>Fang</td><td>1998-08-28</td><td>birthday</td><td>Gave him a new chew toy</td></tr><tr><td>Claws</td><td>1998-03-17</td><td>birthday</td><td>Gave him a new flea collar</td></tr><tr><td>Whistler</td><td>1998-12-09</td><td>birthday</td><td>First birthday</td></tr></tbody></table></div><p>
Load the records like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;</code></strong>
</pre><p>
Based on what you have learned from the queries that you have
run on the <code class="literal">pet</code> table, you should be able to
perform retrievals on the records in the
<code class="literal">event</code> table; the principles are the same.
But when is the <code class="literal">event</code> table by itself
insufficient to answer questions you might ask?
</p><p>
Suppose that you want to find out the ages at which each pet
had its litters. We saw earlier how to calculate ages from two
dates. The litter date of the mother is in the
<code class="literal">event</code> table, but to calculate her age on
that date you need her birth date, which is stored in the
<code class="literal">pet</code> table. This means the query requires
both tables:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT pet.name,</code></strong>
-> <strong class="userinput"><code>(YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,</code></strong>
-> <strong class="userinput"><code>remark</code></strong>
-> <strong class="userinput"><code>FROM pet INNER JOIN event</code></strong>
-> <strong class="userinput"><code> ON pet.name = event.name</code></strong>
-> <strong class="userinput"><code>WHERE event.type = 'litter';</code></strong>
+--------+------+-----------------------------+
| name | age | remark |
+--------+------+-----------------------------+
| Fluffy | 2 | 4 kittens, 3 female, 1 male |
| Buffy | 4 | 5 puppies, 2 female, 3 male |
| Buffy | 5 | 3 puppies, 3 female |
+--------+------+-----------------------------+
</pre><p>
There are several things to note about this query:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">FROM</code> clause joins two tables
because the query needs to pull information from both of
them.
</p></li><li><p>
When combining (joining) information from multiple tables,
you need to specify how records in one table can be
matched to records in the other. This is easy because they
both have a <code class="literal">name</code> column. The query uses
<code class="literal">ON</code> clause to match up records in the
two tables based on the <code class="literal">name</code> values.
</p><p>
The query uses an <code class="literal">INNER JOIN</code> to combine
the tables. An <code class="literal">INNER JOIN</code> allows for
rows from either table to appear in the result if and only
if both tables meet the conditions specified in the
<code class="option">ON</code> clause. In this example, the
<code class="literal">ON</code> clause specifies that the
<code class="literal">name</code> column in the
<code class="literal">pet</code> table must match the
<code class="literal">name</code> column in the
<code class="literal">event</code> table. If a name appears in one
table but not the other, the row will not appear in the
result because the condition in the <code class="literal">ON</code>
clause fails.
</p></li><li><p>
Because the <code class="literal">name</code> column occurs in both
tables, you must be specific about which table you mean
when referring to the column. This is done by prepending
the table name to the column name.
</p></li></ul></div><p>
You need not have two different tables to perform a join.
Sometimes it is useful to join a table to itself, if you want
to compare records in a table to other records in that same
table. For example, to find breeding pairs among your pets,
you can join the <code class="literal">pet</code> table with itself to
produce candidate pairs of males and females of like species:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species</code></strong>
-> <strong class="userinput"><code>FROM pet AS p1 INNER JOIN pet AS p2</code></strong>
-> <strong class="userinput"><code> ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';</code></strong>
+--------+------+--------+------+---------+
| name | sex | name | sex | species |
+--------+------+--------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
+--------+------+--------+------+---------+
</pre><p>
In this query, we specify aliases for the table name to refer
to the columns and keep straight which instance of the table
each column reference is associated with.
</p></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="getting-information"></a>3.4. Getting Information About Databases and Tables</h2></div></div></div><a class="indexterm" name="id2546158"></a><a class="indexterm" name="id2546171"></a><a class="indexterm" name="id2546183"></a><p>
What if you forget the name of a database or table, or what the
structure of a given table is (for example, what its columns are
called)? MySQL addresses this problem through several statements
that provide information about the databases and tables it
supports.
</p><p>
You have previously seen <code class="literal">SHOW DATABASES</code>, which
lists the databases managed by the server. To find out which
database is currently selected, use the
<a href="functions.html#function_database"><code class="literal">DATABASE()</code></a> function:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT DATABASE();</code></strong>
+------------+
| DATABASE() |
+------------+
| menagerie |
+------------+
</pre><p>
If you have not yet selected any database, the result is
<code class="literal">NULL</code>.
</p><p>
To find out what tables the default database contains (for
example, when you are not sure about the name of a table), use
this command:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW TABLES;</code></strong>
+---------------------+
| Tables_in_menagerie |
+---------------------+
| event |
| pet |
+---------------------+
</pre><p>
The name of the column in the output produced by this statement is
always
<code class="literal">Tables_in_<em class="replaceable"><code>db_name</code></em></code>,
where <em class="replaceable"><code>db_name</code></em> is the name of the
database. See <a href="sql-syntax.html#show-tables" title="12.5.4.25. SHOW TABLES Syntax">Section 12.5.4.25, “<code class="literal">SHOW TABLES</code> Syntax”</a>, for more information.
</p><p>
If you want to find out about the structure of a table, the
<code class="literal">DESCRIBE</code> command is useful; it displays
information about each of a table's columns:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>DESCRIBE pet;</code></strong>
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
</pre><p>
<code class="literal">Field</code> indicates the column name,
<code class="literal">Type</code> is the data type for the column,
<code class="literal">NULL</code> indicates whether the column can contain
<code class="literal">NULL</code> values, <code class="literal">Key</code> indicates
whether the column is indexed, and <code class="literal">Default</code>
specifies the column's default value. <code class="literal">Extra</code>
displays special information about columns; for example, if a
column was created with the <code class="literal">AUTO_INCREMENT</code>
option, this is shown here.
</p><p>
<code class="literal">DESC</code> is a short form of
<code class="literal">DESCRIBE</code>. See <a href="sql-syntax.html#describe" title="12.3.1. DESCRIBE Syntax">Section 12.3.1, “<code class="literal">DESCRIBE</code> Syntax”</a>, for
more information.
</p><p>
You can obtain the <code class="literal">CREATE TABLE</code> statement
necessary to create an existing table using the <code class="literal">SHOW
CREATE TABLE</code> statement. See
<a href="sql-syntax.html#show-create-table" title="12.5.4.6. SHOW CREATE TABLE Syntax">Section 12.5.4.6, “<code class="literal">SHOW CREATE TABLE</code> Syntax”</a>.
</p><p>
If you have indexes on a table, <code class="literal">SHOW INDEX FROM
<em class="replaceable"><code>tbl_name</code></em></code> produces information
about them. See <a href="sql-syntax.html#show-index" title="12.5.4.13. SHOW INDEX Syntax">Section 12.5.4.13, “<code class="literal">SHOW INDEX</code> Syntax”</a>, for more about this
statement.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="batch-mode"></a>3.5. Using <span><strong class="command">mysql</strong></span> in Batch Mode</h2></div></div></div><a class="indexterm" name="id2546453"></a><a class="indexterm" name="id2546465"></a><a class="indexterm" name="id2546474"></a><a class="indexterm" name="id2546486"></a><a class="indexterm" name="id2546496"></a><a class="indexterm" name="id2546508"></a><p>
In the previous sections, you used <span><strong class="command">mysql</strong></span>
interactively to enter queries and view the results. You can also
run <span><strong class="command">mysql</strong></span> in batch mode. To do this, put the
commands you want to run in a file, then tell
<span><strong class="command">mysql</strong></span> to read its input from the file:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql < <em class="replaceable"><code>batch-file</code></em></code></strong>
</pre><p>
If you are running <span><strong class="command">mysql</strong></span> under Windows and have
some special characters in the file that cause problems, you can
do this:
</p><pre class="programlisting">C:\> <strong class="userinput"><code>mysql -e "source <em class="replaceable"><code>batch-file</code></em>"</code></strong>
</pre><p>
If you need to specify connection parameters on the command line,
the command might look like this:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql -h <em class="replaceable"><code>host</code></em> -u <em class="replaceable"><code>user</code></em> -p < <em class="replaceable"><code>batch-file</code></em></code></strong>
Enter password: <strong class="userinput"><code>********</code></strong>
</pre><p>
When you use <span><strong class="command">mysql</strong></span> this way, you are creating a
script file, then executing the script.
</p><p>
If you want the script to continue even if some of the statements
in it produce errors, you should use the <code class="option">--force</code>
command-line option.
</p><p>
Why use a script? Here are a few reasons:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If you run a query repeatedly (say, every day or every week),
making it a script allows you to avoid retyping it each time
you execute it.
</p></li><li><p>
You can generate new queries from existing ones that are
similar by copying and editing script files.
</p></li><li><p>
Batch mode can also be useful while you're developing a query,
particularly for multiple-line commands or multiple-statement
sequences of commands. If you make a mistake, you don't have
to retype everything. Just edit your script to correct the
error, then tell <span><strong class="command">mysql</strong></span> to execute it again.
</p></li><li><p>
If you have a query that produces a lot of output, you can run
the output through a pager rather than watching it scroll off
the top of your screen:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql < <em class="replaceable"><code>batch-file</code></em> | more</code></strong>
</pre></li><li><p>
You can catch the output in a file for further processing:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql < <em class="replaceable"><code>batch-file</code></em> > mysql.out</code></strong>
</pre></li><li><p>
You can distribute your script to other people so that they
can also run the commands.
</p></li><li><p>
Some situations do not allow for interactive use, for example,
when you run a query from a <span><strong class="command">cron</strong></span> job. In
this case, you must use batch mode.
</p></li></ul></div><p>
The default output format is different (more concise) when you run
<span><strong class="command">mysql</strong></span> in batch mode than when you use it
interactively. For example, the output of <code class="literal">SELECT DISTINCT
species FROM pet</code> looks like this when
<span><strong class="command">mysql</strong></span> is run interactively:
</p><pre class="programlisting">+---------+
| species |
+---------+
| bird |
| cat |
| dog |
| hamster |
| snake |
+---------+
</pre><p>
In batch mode, the output looks like this instead:
</p><pre class="programlisting">species
bird
cat
dog
hamster
snake
</pre><p>
If you want to get the interactive output format in batch mode,
use <code class="literal">mysql -t</code>. To echo to the output the
commands that are executed, use <code class="literal">mysql -vvv</code>.
</p><a class="indexterm" name="id2546815"></a><a class="indexterm" name="id2546824"></a><a class="indexterm" name="id2546833"></a><a class="indexterm" name="id2546843"></a><p>
You can also use scripts from the <span><strong class="command">mysql</strong></span> prompt
by using the <code class="literal">source</code> command or
<code class="literal">\.</code> command:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>source <em class="replaceable"><code>filename</code></em>;</code></strong>
mysql> <strong class="userinput"><code>\. <em class="replaceable"><code>filename</code></em></code></strong>
</pre><p>
See <a href="client-utility-programs.html#batch-commands" title="7.7.4. Executing SQL Statements from a Text File">Section 7.7.4, “Executing SQL Statements from a Text File”</a>, for more information.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="examples"></a>3.6. Examples of Common Queries</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="tutorial.html#example-maximum-column">3.6.1. The Maximum Value for a Column</a></span></dt><dt><span class="section"><a href="tutorial.html#example-maximum-row">3.6.2. The Row Holding the Maximum of a Certain Column</a></span></dt><dt><span class="section"><a href="tutorial.html#example-maximum-column-group">3.6.3. Maximum of Column per Group</a></span></dt><dt><span class="section"><a href="tutorial.html#example-maximum-column-group-row">3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field</a></span></dt><dt><span class="section"><a href="tutorial.html#example-user-variables">3.6.5. Using User-Defined Variables</a></span></dt><dt><span class="section"><a href="tutorial.html#example-foreign-keys">3.6.6. Using Foreign Keys</a></span></dt><dt><span class="section"><a href="tutorial.html#searching-on-two-keys">3.6.7. Searching on Two Keys</a></span></dt><dt><span class="section"><a href="tutorial.html#calculating-days">3.6.8. Calculating Visits Per Day</a></span></dt><dt><span class="section"><a href="tutorial.html#example-auto-increment">3.6.9. Using <code class="literal">AUTO_INCREMENT</code></a></span></dt></dl></div><a class="indexterm" name="id2546917"></a><a class="indexterm" name="id2546929"></a><p>
Here are examples of how to solve some common problems with MySQL.
</p><p>
Some of the examples use the table <code class="literal">shop</code> to hold
the price of each article (item number) for certain traders
(dealers). Supposing that each trader has a single fixed price per
article, then (<code class="literal">article</code>,
<code class="literal">dealer</code>) is a primary key for the records.
</p><p>
Start the command-line tool <span><strong class="command">mysql</strong></span> and select a
database:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql <em class="replaceable"><code>your-database-name</code></em></code></strong>
</pre><p>
(In most MySQL installations, you can use the database named
<code class="literal">test</code>).
</p><p>
You can create and populate the example table with these
statements:
</p><pre class="programlisting">CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
</pre><p>
After issuing the statements, the table should have the following
contents:
</p><pre class="programlisting">SELECT * FROM shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
</pre><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="example-maximum-column"></a>3.6.1. The Maximum Value for a Column</h3></div></div></div><p>
“<span class="quote">What's the highest item number?</span>”
</p><pre class="programlisting">SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
| 4 |
+---------+
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="example-maximum-row"></a>3.6.2. The Row Holding the Maximum of a Certain Column</h3></div></div></div><p>
<span class="emphasis"><em>Task: Find the number, dealer, and price of the most
expensive article.</em></span>
</p><p>
This is easily done with a subquery:
</p><pre class="programlisting">SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
</pre><p>
Another solution is to sort all rows descending by price and get
only the first row using the MySQL-specific
<code class="literal">LIMIT</code> clause:
</p><pre class="programlisting">SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
</pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
If there were several most expensive articles, each with a
price of 19.95, the <code class="literal">LIMIT</code> solution would
show only one of them.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="example-maximum-column-group"></a>3.6.3. Maximum of Column per Group</h3></div></div></div><p>
<span class="emphasis"><em>Task: Find the highest price per article.</em></span>
</p><pre class="programlisting">SELECT article, MAX(price) AS price
FROM shop
GROUP BY article
+---------+-------+
| article | price |
+---------+-------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+-------+
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="example-maximum-column-group-row"></a>3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field</h3></div></div></div><p>
<span class="emphasis"><em>Task: For each article, find the dealer or dealers
with the most expensive price.</em></span>
</p><p>
This problem can be solved with a subquery like this one:
</p><pre class="programlisting">SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+
</pre><p>
The preceding example uses a correlated subquery, which can be
inefficient (see <a href="sql-syntax.html#correlated-subqueries" title="12.2.8.7. Correlated Subqueries">Section 12.2.8.7, “Correlated Subqueries”</a>). Other
possibilities for solving the problem are to use a uncorrelated
subquery in the <code class="literal">FROM</code> clause or a
<code class="literal">LEFT JOIN</code>:
</p><pre class="programlisting">SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;
</pre><p>
The <code class="literal">LEFT JOIN</code> works on the basis that when
<code class="literal">s1.price</code> is at its maximum value, there is no
<code class="literal">s2.price</code> with a greater value and the
<code class="literal">s2</code> rows values will be
<code class="literal">NULL</code>. See <a href="sql-syntax.html#join" title="12.2.7.1. JOIN Syntax">Section 12.2.7.1, “<code class="literal">JOIN</code> Syntax”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="example-user-variables"></a>3.6.5. Using User-Defined Variables</h3></div></div></div><p>
You can employ MySQL user variables to remember results without
having to store them in temporary variables in the client. (See
<a href="language-structure.html#user-variables" title="8.4. User-Defined Variables">Section 8.4, “User-Defined Variables”</a>.)
</p><p>
For example, to find the articles with the highest and lowest
price you can do this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;</code></strong>
mysql> <strong class="userinput"><code>SELECT * FROM shop WHERE price=@min_price OR price=@max_price;</code></strong>
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="example-foreign-keys"></a>3.6.6. Using Foreign Keys</h3></div></div></div><a class="indexterm" name="id2547324"></a><a class="indexterm" name="id2547333"></a><p>
In MySQL, <code class="literal">InnoDB</code> tables support checking of
foreign key constraints. See <a href="storage-engines.html#innodb" title="13.2. The InnoDB Storage Engine">Section 13.2, “The <code class="literal">InnoDB</code> Storage Engine”</a>, and
<a href="introduction.html#ansi-diff-foreign-keys" title="1.8.5.4. Foreign Keys">Section 1.8.5.4, “Foreign Keys”</a>.
</p><p>
A foreign key constraint is not required merely to join two
tables. For storage engines other than
<code class="literal">InnoDB</code>, it is possible when defining a column
to use a <code class="literal">REFERENCES
<em class="replaceable"><code>tbl_name</code></em>(<em class="replaceable"><code>col_name</code></em>)</code>
clause, which has no actual effect, and <span class="emphasis"><em>serves only as
a memo or comment to you that the column which you are currently
defining is intended to refer to a column in another
table</em></span>. It is extremely important to realize when
using this syntax that:
</p><div class="itemizedlist"><ul type="disc"><li><p>
MySQL does not perform any sort of <code class="literal">CHECK</code>
to make sure that <em class="replaceable"><code>col_name</code></em>
actually exists in <em class="replaceable"><code>tbl_name</code></em> (or
even that <em class="replaceable"><code>tbl_name</code></em> itself
exists).
</p></li><li><p>
MySQL does not perform any sort of action on
<em class="replaceable"><code>tbl_name</code></em> such as deleting rows in
response to actions taken on rows in the table which you are
defining; in other words, this syntax induces no <code class="literal">ON
DELETE</code> or <code class="literal">ON UPDATE</code> behavior
whatsoever. (Although you can write an <code class="literal">ON
DELETE</code> or <code class="literal">ON UPDATE</code> clause as
part of the <code class="literal">REFERENCES</code> clause, it is also
ignored.)
</p></li><li><p>
This syntax creates a <span class="emphasis"><em>column</em></span>; it does
<span class="bold"><strong>not</strong></span> create any sort of
index or key.
</p></li><li><p>
This syntax will cause an error if used in trying to define
an <code class="literal">InnoDB</code> table.
</p></li></ul></div><p>
You can use a column so created as a join column, as shown here:
</p><pre class="programlisting">CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+
SELECT s.* FROM person p INNER JOIN shirt s
ON s.owner = p.id
WHERE p.name LIKE 'Lilliana%'
AND s.color <> 'white';
+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+
</pre><p>
When used in this fashion, the <code class="literal">REFERENCES</code>
clause is not displayed in the output of <code class="literal">SHOW CREATE
TABLE</code> or <code class="literal">DESCRIBE</code>:
</p><pre class="programlisting">SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
</pre><p>
The use of <code class="literal">REFERENCES</code> in this way as a
comment or “<span class="quote">reminder</span>” in a column definition works
with both <code class="literal">MyISAM</code> and
<code class="literal">BerkeleyDB</code> tables.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="searching-on-two-keys"></a>3.6.7. Searching on Two Keys</h3></div></div></div><a class="indexterm" name="id2547655"></a><a class="indexterm" name="id2547663"></a><a class="indexterm" name="id2547672"></a><a class="indexterm" name="id2547685"></a><p>
An <a href="functions.html#operator_or"><code class="literal">OR</code></a> using a single key is
well optimized, as is the handling of
<a href="functions.html#operator_and"><code class="literal">AND</code></a>.
</p><p>
The one tricky case is that of searching on two different keys
combined with <a href="functions.html#operator_or"><code class="literal">OR</code></a>:
</p><pre class="programlisting">SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR field2_index = '1'
</pre><p>
This case is optimized from MySQL 5.0.0. See
<a href="optimization.html#index-merge-optimization" title="6.2.6. Index Merge Optimization">Section 6.2.6, “Index Merge Optimization”</a>.
</p><p>
You can also solve the problem efficiently by using a
<code class="literal">UNION</code> that combines the output of two
separate <code class="literal">SELECT</code> statements. See
<a href="sql-syntax.html#union" title="12.2.7.3. UNION Syntax">Section 12.2.7.3, “<code class="literal">UNION</code> Syntax”</a>.
</p><p>
Each <code class="literal">SELECT</code> searches only one key and can be
optimized:
</p><pre class="programlisting">SELECT field1_index, field2_index
FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
FROM test_table WHERE field2_index = '1';
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="calculating-days"></a>3.6.8. Calculating Visits Per Day</h3></div></div></div><a class="indexterm" name="id2547794"></a><a class="indexterm" name="id2547803"></a><a class="indexterm" name="id2547812"></a><a class="indexterm" name="id2547821"></a><p>
The following example shows how you can use the bit group
functions to calculate the number of days per month a user has
visited a Web page.
</p><pre class="programlisting">CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
(2000,2,23),(2000,2,23);
</pre><p>
The example table contains year-month-day values representing
visits by users to the page. To determine how many different
days in each month these visits occur, use this query:
</p><pre class="programlisting">SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;
</pre><p>
Which returns:
</p><pre class="programlisting">+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 | 01 | 3 |
| 2000 | 02 | 2 |
+------+-------+------+
</pre><p>
The query calculates how many different days appear in the table
for each year/month combination, with automatic removal of
duplicate entries.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="example-auto-increment"></a>3.6.9. Using <code class="literal">AUTO_INCREMENT</code></h3></div></div></div><a class="indexterm" name="id2547905"></a><a class="indexterm" name="id2547914"></a><a class="indexterm" name="id2547923"></a><p>
The <code class="literal">AUTO_INCREMENT</code> attribute can be used to
generate a unique identity for new rows:
</p><pre class="programlisting">CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
</pre><p>
Which returns:
</p><pre class="programlisting">+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
</pre><p>
You can retrieve the most recent
<code class="literal">AUTO_INCREMENT</code> value with the
<a href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a> SQL function or
the <a href="apis.html#mysql-insert-id" title="23.2.3.37. mysql_insert_id()"><code class="literal">mysql_insert_id()</code></a> C API
function. These functions are connection-specific, so their
return values are not affected by another connection which is
also performing inserts.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
For a multiple-row insert,
<a href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a> and
<a href="apis.html#mysql-insert-id" title="23.2.3.37. mysql_insert_id()"><code class="literal">mysql_insert_id()</code></a> actually
return the <code class="literal">AUTO_INCREMENT</code> key from the
<span class="emphasis"><em>first</em></span> of the inserted rows. This allows
multiple-row inserts to be reproduced correctly on other
servers in a replication setup.
</p></div><p>
For <code class="literal">MyISAM</code> and <code class="literal">BDB</code> tables
you can specify <code class="literal">AUTO_INCREMENT</code> on a secondary
column in a multiple-column index. In this case, the generated
value for the <code class="literal">AUTO_INCREMENT</code> column is
calculated as
<a href="functions.html#function_max"><code class="literal">MAX(<em class="replaceable"><code>auto_increment_column</code></em>)
+ 1 WHERE
prefix=<em class="replaceable"><code>given-prefix</code></em></code></a>. This
is useful when you want to put data into ordered groups.
</p><pre class="programlisting">CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
</pre><p>
Which returns:
</p><pre class="programlisting">+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
</pre><p>
Note that in this case (when the
<code class="literal">AUTO_INCREMENT</code> column is part of a
multiple-column index), <code class="literal">AUTO_INCREMENT</code> values
are reused if you delete the row with the biggest
<code class="literal">AUTO_INCREMENT</code> value in any group. This
happens even for <code class="literal">MyISAM</code> tables, for which
<code class="literal">AUTO_INCREMENT</code> values normally are not
reused.
</p><p>
If the <code class="literal">AUTO_INCREMENT</code> column is part of
multiple indexes, MySQL will generate sequence values using the
index that begins with the <code class="literal">AUTO_INCREMENT</code>
column, if there is one. For example, if the
<code class="literal">animals</code> table contained indexes
<code class="literal">PRIMARY KEY (grp, id)</code> and <code class="literal">INDEX
(id)</code>, MySQL would ignore the <code class="literal">PRIMARY
KEY</code> for generating sequence values. As a result, the
table would contain a single sequence, not a sequence per
<code class="literal">grp</code> value.
</p><p>
To start with an <code class="literal">AUTO_INCREMENT</code> value other
than 1, you can set that value with <code class="literal">CREATE
TABLE</code> or <code class="literal">ALTER TABLE</code>, like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>ALTER TABLE tbl AUTO_INCREMENT = 100;</code></strong>
</pre><p>
More information about <code class="literal">AUTO_INCREMENT</code> is
available here:
</p><div class="itemizedlist"><ul type="disc"><li><p>
How to assign the <code class="literal">AUTO_INCREMENT</code>
attribute to a column: <a href="sql-syntax.html#create-table" title="12.1.5. CREATE TABLE Syntax">Section 12.1.5, “<code class="literal">CREATE TABLE</code> Syntax”</a>, and
<a href="sql-syntax.html#alter-table" title="12.1.2. ALTER TABLE Syntax">Section 12.1.2, “<code class="literal">ALTER TABLE</code> Syntax”</a>.
</p></li><li><p>
How <code class="literal">AUTO_INCREMENT</code> behaves depending on
the SQL mode: <a href="server-administration.html#server-sql-mode" title="5.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>.
</p></li><li><p>
Find the row that contains the most recent AUTO_INCREMENT
value: <a href="functions.html#comparison-operators" title="11.2.3. Comparison Functions and Operators">Section 11.2.3, “Comparison Functions and Operators”</a>.
</p></li><li><p>
Set the <code class="literal">AUTO_INCREMENT</code> value to be
used: <a href="sql-syntax.html#set-option" title="12.5.3. SET Syntax">Section 12.5.3, “<code class="literal">SET</code> Syntax”</a>.
</p></li><li><p>
<code class="literal">AUTO_INCREMENT</code> and replication:
<a href="replication.html#replication-features" title="15.3.1. Replication Features and Issues">Section 15.3.1, “Replication Features and Issues”</a>.
</p></li><li><p>
Server-system variables related to
<code class="literal">AUTO_INCREMENT</code>
(<code class="literal">auto_increment_increment</code> and
<code class="literal">auto_increment_offset</code>) that can be used
for replication:
<a href="server-administration.html#server-system-variables" title="5.2.3. System Variables">Section 5.2.3, “System Variables”</a>.
</p></li></ul></div><p>
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="twin"></a>3.7. Queries from the Twin Project</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="tutorial.html#twin-pool">3.7.1. Find All Non-distributed Twins</a></span></dt><dt><span class="section"><a href="tutorial.html#twin-event">3.7.2. Show a Table of Twin Pair Status</a></span></dt></dl></div><a class="indexterm" name="id2548401"></a><a class="indexterm" name="id2548414"></a><p>
At the places the early MySQL was developed (Analytikerna and
Lentus), the founders did systems and field work for a big
research project. This project was a collaboration between the
Institute of Environmental Medicine at Karolinska Institutet
Stockholm and the Section on Clinical Research in Aging and
Psychology at the University of Southern California.
</p><p>
The project involved lots of data collection from all twins in
Sweden older than 65 Years (see
<a href="http://www.mep.ki.se/twinreg/index_en.html" target="_top">http://www.mep.ki.se/twinreg/index_en.html</a>).
</p><p>
Large parts of the project were administered with a Web interface
written using Perl and MySQL.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="twin-pool"></a>3.7.1. Find All Non-distributed Twins</h3></div></div></div><p>
The following query was used to determine what twins should be
studied further after a initial screening. The time for this was
around MySQL 3.19 in 1997.
</p><pre class="programlisting">SELECT
CONCAT(p1.id, p1.tvab) + 0 AS tvid,
CONCAT(p1.christian_name, ' ', p1.surname) AS Name,
p1.postal_code AS Code,
p1.city AS City,
pg.abrev AS Area,
IF(td.participation = 'Aborted', 'A', ' ') AS A,
p1.dead AS dead1,
l.event AS event1,
td.suspect AS tsuspect1,
id.suspect AS isuspect1,
td.severe AS tsevere1,
id.severe AS isevere1,
p2.dead AS dead2,
l2.event AS event2,
h2.nurse AS nurse2,
h2.doctor AS doctor2,
td2.suspect AS tsuspect2,
id2.suspect AS isuspect2,
td2.severe AS tsevere2,
id2.severe AS isevere2,
l.finish_date
FROM
twin_project AS tp
/* For Twin 1 */
LEFT JOIN twin_data AS td ON tp.id = td.id
AND tp.tvab = td.tvab
LEFT JOIN informant_data AS id ON tp.id = id.id
AND tp.tvab = id.tvab
LEFT JOIN harmony AS h ON tp.id = h.id
AND tp.tvab = h.tvab
LEFT JOIN lentus AS l ON tp.id = l.id
AND tp.tvab = l.tvab
/* For Twin 2 */
LEFT JOIN twin_data AS td2 ON p2.id = td2.id
AND p2.tvab = td2.tvab
LEFT JOIN informant_data AS id2 ON p2.id = id2.id
AND p2.tvab = id2.tvab
LEFT JOIN harmony AS h2 ON p2.id = h2.id
AND p2.tvab = h2.tvab
LEFT JOIN lentus AS l2 ON p2.id = l2.id
AND p2.tvab = l2.tvab,
person_data AS p1,
person_data AS p2,
postal_groups AS pg
WHERE
/* p1 gets main twin and p2 gets his/her twin. */
/* ptvab is a field inverted from tvab */
p1.id = tp.id AND p1.tvab = tp.tvab AND
p2.id = p1.id AND p2.ptvab = p1.tvab AND
/* Just the screening survey */
tp.survey_no = 5 AND
/* Skip if partner died before 65 but allow emigration (dead=9) */
(p2.dead = 0 OR p2.dead = 9 OR
(p2.dead = 1 AND
(p2.death_date = 0 OR
(((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) / 365)
>= 65))))
AND
(
/* Twin is suspect */
(td.future_contact = 'Yes' AND td.suspect = 2) OR
/* Twin is suspect - Informant is Blessed */
(td.future_contact = 'Yes' AND td.suspect = 1
AND id.suspect = 1) OR
/* No twin - Informant is Blessed */
(ISNULL(td.suspect) AND id.suspect = 1
AND id.future_contact = 'Yes') OR
/* Twin broken off - Informant is Blessed */
(td.participation = 'Aborted'
AND id.suspect = 1 AND id.future_contact = 'Yes') OR
/* Twin broken off - No inform - Have partner */
(td.participation = 'Aborted' AND ISNULL(id.suspect)
AND p2.dead = 0))
AND
l.event = 'Finished'
/* Get at area code */
AND SUBSTRING(p1.postal_code, 1, 2) = pg.code
/* Not already distributed */
AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)
/* Has not refused or been aborted */
AND NOT (h.status = 'Refused' OR h.status = 'Aborted'
OR h.status = 'Died' OR h.status = 'Other')
ORDER BY
tvid;
</pre><p>
Some explanations:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">CONCAT(p1.id, p1.tvab) + 0 AS tvid</code>
</p><p>
We want to sort on the concatenated <code class="literal">id</code>
and <code class="literal">tvab</code> in numerical order. Adding
<code class="literal">0</code> to the result causes MySQL to treat the
result as a number.
</p></li><li><p>
column <code class="literal">id</code>
</p><p>
This identifies a pair of twins. It is an index in all
tables.
</p></li><li><p>
column <code class="literal">tvab</code>
</p><p>
This identifies a twin in a pair. It has a value of
<code class="literal">1</code> or <code class="literal">2</code>.
</p></li><li><p>
column <code class="literal">ptvab</code>
</p><p>
This is an inverse of <code class="literal">tvab</code>. When
<code class="literal">tvab</code> is <code class="literal">1</code> this is
<code class="literal">2</code>, and vice versa. It exists to save
typing and to make it easier for MySQL to optimize the
query.
</p></li></ul></div><p>
This query demonstrates, among other things, how to do lookups
on a table from the same table with a join
(<code class="literal">p1</code> and <code class="literal">p2</code>). In the
example, this is used to check whether a twin's partner died
before the age of 65. If so, the row is not returned.
</p><p>
All of the above exist in all tables with twin-related
information. We have an index on both <code class="literal">id,
tvab</code> (all tables), and <code class="literal">id, ptvab</code>
(<code class="literal">person_data</code>) to make queries faster.
</p><p>
When we did this work, our production machine was a 200MHz
UltraSPARC, and on that old hardware this query returned about
150-200 rows in less than one second. The main table had 70k
Rows.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="twin-event"></a>3.7.2. Show a Table of Twin Pair Status</h3></div></div></div><p>
Each twin has a status code called <code class="literal">event</code>. The
query shown here is used to select all twin pairs combined by
event. This indicates in how many pairs both twins are finished,
in how many pairs one twin is finished and the other refused,
and so on.
</p><pre class="programlisting">SELECT
t1.event,
t2.event,
COUNT(*)
FROM
lentus AS t1,
lentus AS t2,
twin_project AS tp
WHERE
/* We are looking at one pair at a time */
t1.id = tp.id
AND t1.tvab=tp.tvab
AND t1.id = t2.id
/* Just the screening survey */
AND tp.survey_no = 5
/* This makes each pair only appear once */
AND t1.tvab='1' AND t2.tvab='2'
GROUP BY
t1.event, t2.event;
</pre></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="apache"></a>3.8. Using MySQL with Apache</h2></div></div></div><a class="indexterm" name="id2548765"></a><p>
There are programs that let you authenticate your users from a
MySQL database and also let you write your log files into a MySQL
table.
</p><p>
You can change the Apache logging format to be easily readable by
MySQL by putting the following into the Apache configuration file:
</p><pre class="programlisting">LogFormat \
"\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \
\"%U\",\"%{Referer}i\",\"%{User-Agent}i\""
</pre><p>
To load a log file in that format into MySQL, you can use a
statement something like this:
</p><pre class="programlisting">LOAD DATA INFILE '<em class="replaceable"><code>/local/access_log</code></em>' INTO TABLE <em class="replaceable"><code>tbl_name</code></em>
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
</pre><p>
The named table should be created to have columns that correspond
to those that the <code class="literal">LogFormat</code> line writes to the
log file.
</p></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="installing.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="using-mysql-programs.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 2. Installing and Upgrading MySQL </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 4. Using MySQL Programs</td></tr></table></div></body></html>
distrib
>
Mandriva
>
2008.1
>
x86_64
>
media
>
main-testing
>
by-pkgid
>
b1e2421f2416edfc24c5845fbc1c5a2e
>
files
>
116
