This chapter provides a tutorial introduction to MySQL by showing
how to use the mysql client program to create and use a simple
database. mysql (sometimes referred to as the ``terminal monitor'' or
just ``monitor'') is an interactive program that allows you to connect to a
MySQL server, run queries, and view the results. mysql may
also be used in batch mode: you place your queries in a file beforehand, then
tell mysql to execute the contents of the file. Both ways of using
mysql are covered here.
To see a list of options provided by mysql, invoke it with
the --help option:
shell> mysql --help
This chapter assumes that mysql 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 you are the
administrator, you will need to consult other sections of this manual.)
This chapter describes the entire process of setting up and using a database. If you are interested only in accessing an already-existing database, you may want to skip over the sections that describe how to create the database and the tables it contains.
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.
To connect to the server, you'll usually need to provide a MySQL
username when you invoke mysql and, most likely, a password. If the
server runs on a machine other than the one where you log in, you'll also
need to specify a hostname. Contact your administrator to find out what
connection parameters you should use to connect (that is, what host, username,
and password to use). Once you know the proper parameters, you should be
able to connect like this:
shell> mysql -h host -u user -p Enter password: ********
host and user represent the hostname where your MySQL server is
running and the username of your MySQL account. Substitute appropriate values
for your setup.
The ******** represents your password; enter it when mysql
displays the Enter password: prompt.
If that works, you should see some introductory information followed by a
mysql> prompt:
shell> mysql -h host -u user -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25338 to server version: 4.0.14-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
The prompt tells you that mysql is ready for you to enter commands.
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
mysql without any options:
shell> mysql
After you have connected successfully, you can disconnect any time by typing
QUIT (or \q) at the mysql> prompt:
mysql> QUIT Bye
On Unix, you can also disconnect by pressing Control-D.
Most examples in the following sections assume that you are connected to the
server. They indicate this by the mysql> prompt.
Make sure that you are connected to the server, as discussed in the previous
section. Doing so will 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 mysql works.
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 mysql>
prompt and press Enter:
mysql> SELECT VERSION(), CURRENT_DATE; +--------------+--------------+ | VERSION() | CURRENT_DATE | +--------------+--------------+ | 3.22.20a-log | 1999-03-19 | +--------------+--------------+ 1 row in set (0.01 sec) mysql>
This query illustrates several things about mysql:
QUIT,
mentioned earlier, is one of them. We'll get to others later.)
mysql sends it to the server for execution
and displays the results, then prints another mysql> prompt to indicate
that it is ready for another command.
mysql 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), mysql labels the column
using the expression itself.
mysql 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 ``rows in set'' line is not shown in
the remaining examples in this chapter.)
Keywords may be entered in any lettercase. The following queries are equivalent:
mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE;
Here's another query. It demonstrates that you can use mysql as a
simple calculator:
mysql> SELECT SIN(PI()/4), (4+1)*5; +-------------+---------+ | SIN(PI()/4) | (4+1)*5 | +-------------+---------+ | 0.707107 | 25 | +-------------+---------+
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:
mysql> SELECT VERSION(); SELECT NOW(); +--------------+ | VERSION() | +--------------+ | 3.22.20a-log | +--------------+ +---------------------+ | NOW() | +---------------------+ | 1999-03-19 00:15:33 | +---------------------+
A command need not be given all on a single line, so lengthy commands that
require several lines are not a problem. mysql determines where your
statement ends by looking for the terminating semicolon, not by looking for
the end of the input line. (In other words, mysql
accepts free-format input: it collects input lines but does not execute them
until it sees the semicolon.)
Here's a simple multiple-line statement:
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
+--------------------+--------------+
| USER() | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18 |
+--------------------+--------------+
In this example, notice how the prompt changes from mysql> to
-> after you enter the first line of a multiple-line query. This is
how mysql indicates that it hasn't 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 will always be aware of
what mysql is waiting for.
If you decide you don't want to execute a command that you are in the
process of entering, cancel it by typing \c:
mysql> SELECT
-> USER()
-> \c
mysql>
Here, too, notice the prompt. It switches back to mysql> after you
type \c, providing feedback to indicate that mysql is ready
for a new command.
The following table shows each of the prompts you may see and summarizes what
they mean about the state that mysql is in:
| Prompt | Meaning |
mysql> | Ready for new command. |
-> | Waiting for next line of multiple-line command. |
'> | Waiting for next line, collecting a string that begins with a single quote (`''). |
"> | Waiting for next line, collecting a string that begins with a double quote (`"'). |
`> | Waiting for next line, collecting an identifier that begins with a backtick (``'). |
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, mysql waits for more input:
mysql> SELECT USER()
->
If this happens to you (you think you've entered a statement but the only
response is a -> prompt), most likely mysql is waiting for the
semicolon. If you don't notice what the prompt is telling you, you might sit
there for a while before realising what you need to do. Enter a semicolon to
complete the statement, and mysql will execute it:
mysql> SELECT USER()
-> ;
+--------------------+
| USER() |
+--------------------+
| joesmith@localhost |
+--------------------+
The '> and "> prompts occur during string collection.
In MySQL, you can write strings surrounded by either `''
or `"' characters (for example, 'hello' or "goodbye"),
and mysql lets you enter strings that span multiple lines. When you
see a '> or "> prompt, it means that you've entered a line
containing a string that begins with a `'' or `"' quote character,
but have not yet entered the matching quote that terminates the string.
That's fine if you really are entering a multiple-line string, but how likely
is that? Not very. More often, the '> and "> prompts indicate
that you've inadvertantly left out a quote character. For example:
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
'>
If you enter this SELECT statement, then press Enter and wait for the
result, nothing will happen. Instead of wondering why this
query takes so long, notice the clue provided by the '> prompt. It
tells you that mysql expects to see the rest of an unterminated
string. (Do you see the error in the statement? The string 'Smith is
missing the second quote.)
At this point, what do you do? The simplest thing is to cancel the command.
However, you cannot just type \c in this case, because mysql
interprets it as part of the string that it is collecting! Instead, enter
the closing quote character (so mysql knows you've finished the
string), then type \c:
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
'> '\c
mysql>
The prompt changes back to mysql>, indicating that mysql
is ready for a new command.
The `> prompt is similar to th '> and "> prompts,
but indicates that you have begun but not completed a backtick-quoted
identifier.
It's important to know what the '>, ">, and `> prompts signify,
because if you mistakenly enter an unterminated string, any further lines you
type will appear to be ignored by mysql---including a line
containing QUIT! This can be quite confusing, especially if you
don't know that you need to supply the terminating quote before you can
cancel the current command.
Now that you know how to enter commands, it's time to access a database.
Suppose that you have several pets in your home (your menagerie) and you'd 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:
The menagerie database will be 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's available in either compressed tar format
(http://www.mysql.com/Downloads/Contrib/Examples/menagerie.tar.gz)
or Zip format
(http://www.mysql.com/Downloads/Contrib/Examples/menagerie.zip).
Use the SHOW statement to find out what databases currently exist
on the server:
mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | | tmp | +----------+
The list of databases is probably different on your machine, but the
mysql and test databases are likely to be among them. The
mysql database is required because it describes user access
privileges. The test database is often provided as a workspace for
users to try things out.
Note that you may not see all databases if you don't have the
SHOW DATABASES privilege. See section 13.5.1.2 GRANT and REVOKE Syntax.
If the test database exists, try to access it:
mysql> USE test Database changed
Note that USE, like QUIT, does not require a semicolon. (You
can terminate such statements with a semicolon if you like; it does no harm.)
The USE statement is special in another way, too: it must be given on
a single line.
You can use the test 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 menagerie. The
administrator needs to execute a command like this:
mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
where your_mysql_name is the MySQL username assigned to
you and your_client_host is the host from which you connect to the
server.
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:
mysql> CREATE DATABASE menagerie;
Under Unix, database names are case sensitive (unlike SQL keywords), so you
must always refer to your database as menagerie, not as
Menagerie, MENAGERIE, 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.)
Creating a database does not select it for use; you must do that explicitly.
To make menagerie the current database, use this command:
mysql> USE menagerie Database changed
Your database needs to be created only once, but you must select it for use
each time you begin a mysql session. You can do this by issuing a
USE statement as shown in the example. Alternatively, you can select the
database on the command line when you invoke mysql. Just specify its
name after any connection parameters that you might need to provide. For
example:
shell> mysql -h host -u user -p menagerie Enter password: ********
Note that menagerie is not your password on the command just shown.
If you want to supply your password on the command line after the -p
option, you must do so with no intervening space (for example, as
-pmypassword, not as -p mypassword). 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.
Creating the database is the easy part, but at this point it's empty, as
SHOW TABLES will tell you:
mysql> SHOW TABLES; Empty set (0.00 sec)
The harder part is deciding what the structure of your database should be: what tables you will need and what columns will be in each of them.
You'll want a table that contains a record for each of your pets. This can
be called the pet 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.
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:
You can probably think of other types of information that would be useful in
the pet table, but the ones identified so far are sufficient for now:
name, owner, species, sex, birth, and death.
Use a CREATE TABLE statement to specify the layout of your table:
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
VARCHAR is a good choice for the name, owner, and
species columns because the column values will vary in length. The
lengths of those columns need not all be the same, and need not be
20. You can pick any length from 1 to 255, whatever
seems most reasonable to you. (If you make a poor choice and it turns
out later that you need a longer field, MySQL provides an
ALTER TABLE statement.)
Several types of values can be chosen to represent sex in animal records,
such as 'm'
and 'f', or perhaps 'male' and 'female'. It's simplest
to use the single characters 'm' and 'f'.
The use of the DATE data type for the birth and death
columns is a fairly obvious choice.
Now that you have created a table, SHOW TABLES should produce some
output:
mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | pet | +---------------------+
To verify that your table was created the way you expected, use
a DESCRIBE statement:
mysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | 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 | | +---------+-------------+------+-----+---------+-------+
You can use DESCRIBE any time, for example, if you forget the names of
the columns in your table or what types they have.
After creating your table, you need to populate it. The LOAD DATA and
INSERT statements are useful for this.
Suppose that your pet records can be described as shown here.
(Observe that MySQL expects dates in 'YYYY-MM-DD' format;
this may be different from what you are used to.)
| name | owner | species | sex | birth | death |
| Fluffy | Harold | cat | f | 1993-02-04 | |
| Claws | Gwen | cat | m | 1994-03-17 | |
| Buffy | Harold | dog | f | 1989-05-13 | |
| Fang | Benny | dog | m | 1990-08-27 | |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | |
| Whistler | Gwen | bird | 1997-12-09 | ||
| Slim | Benny | snake | m | 1996-04-29 |
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.
You could create a text file `pet.txt' containing one record per line,
with values separated by tabs, and given in the order in which the columns
were listed in the CREATE TABLE statement. For missing values (such
as unknown sexes or death dates for animals that are still living), you can
use NULL values. To represent these in your text file, use
\N (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):
| name | owner | species | sex | birth | death |
Whistler | Gwen | bird | \N | 1997-12-09 | \N
|
To load the text file `pet.txt' into the pet table, use this
command:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
Note that if you created the file on Windows with an editor that uses
\r\n as a line terminator, you should use:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
You can specify the column value separator and end of line marker explicitly
in the LOAD DATA statement if you wish, but the defaults are tab and
linefeed. These are sufficient for the statement to read the file
`pet.txt' properly.
If the statement fails, it is likely that your MySQL installation does not
have local file capability enabled by default. See
section 5.4.4 Security Issues with LOAD DATA LOCAL for information on how to
change this.
When you want to add new records one at a time, the INSERT statement
is useful. In its simplest form, you supply values for each column, in the
order in which the columns were listed in the CREATE TABLE statement.
Suppose that Diane gets a new hamster named Puffball. You could add a new record
using an INSERT statement like this:
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Note that string and date values are specified as quoted strings here. Also,
with INSERT, you can insert NULL directly to represent a
missing value. You do not use \N like you do with LOAD DATA.
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 INSERT statements rather
than a single LOAD DATA statement.
The SELECT statement is used to pull information from a table.
The general form of the statement is:
SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;
what_to_select indicates what you want to see. This can be a list of
columns, or * to indicate ``all columns.'' which_table
indicates the table from which you want to retrieve data. The WHERE
clause is optional. If it's present, conditions_to_satisfy specifies
conditions that rows must satisfy to qualify for retrieval.
The simplest form of SELECT retrieves everything from a table:
mysql> SELECT * FROM pet; +----------+--------+---------+------+------------+------------+ | 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 | +----------+--------+---------+------+------------+------------+
This form of SELECT is useful if you want to review your entire table,
for example, after you've just loaded it with your initial dataset. 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.
There are least a couple of ways to fix this:
DELETE and LOAD DATA:
mysql> DELETE FROM pet; mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;However, if you do this, you must also re-enter the record for Puffball.
UPDATE statement:
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';The
UPDATE changes only the record in question and does not require you
to reload the table.
As shown in the preceding section, it is easy to retrieve an entire table.
Just omit the WHERE clause from the SELECT 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.
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:
mysql> SELECT * FROM pet WHERE name = 'Bowser'; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+
The output confirms that the year is correctly recorded now as 1989, not 1979.
String comparisons normally are case-insensitive, so you can specify the
name as 'bowser', 'BOWSER', etc. The query result will be
the same.
You can specify conditions on any column, not just name. For example,
if you want to know which animals were born after 1998, test the birth
column:
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1'; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+
You can combine conditions, for example, to locate female dogs:
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f'; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+
The preceding query uses the AND logical operator. There is also an
OR operator:
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'; +----------+-------+---------+------+------------+-------+ | 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 | +----------+-------+---------+------+------------+-------+
AND and OR may be intermixed, although AND has higher
precedence than OR. If you use both operators, it's a good idea
to use parentheses to indicate explicitly how conditions should be grouped:
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
-> OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
If you don't want to see entire rows from your table, just name the columns
in which you're interested, separated by commas. For example, if you want to
know when your animals were born, select the name and birth
columns:
mysql> SELECT name, birth FROM pet; +----------+------------+ | 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 | +----------+------------+
To find out who owns pets, use this query:
mysql> SELECT owner FROM pet; +--------+ | owner | +--------+ | Harold | | Gwen | | Harold | | Benny | | Diane | | Gwen | | Gwen | | Benny | | Diane | +--------+
However, notice that the query simply retrieves the owner field 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
DISTINCT:
mysql> SELECT DISTINCT owner FROM pet; +--------+ | owner | +--------+ | Benny | | Diane | | Gwen | | Harold | +--------+
You can use a WHERE clause to combine row selection with column
selection. For example, to get birth dates for dogs and cats only,
use this query:
mysql> SELECT name, species, birth FROM pet
-> WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| 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 |
+--------+---------+------------+
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 ORDER BY clause.
Here are animal birthdays, sorted by date:
mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | 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 | +----------+------------+
On character type columns, sorting--like all other comparison
operations--is normally performed in a case-insensitive fashion.
This means that the order will be undefined for columns that are identical
except for their case. You can force a case-sensitive sort for a column
by using the BINARY cast: ORDER BY BINARY col_name.
The default sort order is ascending, with smallest values first.
To sort in reverse (descending) order, add the DESC keyword to the
name of the column you are sorting by:
mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | 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 | +----------+------------+
You can sort on multiple columns, and you can sort 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:
mysql> SELECT name, species, birth FROM pet
-> ORDER BY species, birth DESC;
+----------+---------+------------+
| 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 |
+----------+---------+------------+
Note that the DESC keyword applies only to the column name immediately
preceding it (birth); it does not affect the species column sort
order.
MySQL provides several functions that you can use to perform calculations on dates, for example, to calculate ages or extract parts of dates.
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.
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age
-> FROM pet;
+----------+------------+------------+------+
| 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 |
+----------+------------+------------+------+
Here, YEAR() pulls out the year part of a date and RIGHT()
pulls off the rightmost five characters that represent the MM-DD
(calendar year) part of the date. The part of the expression that
compares the MM-DD values evaluates to 1 or 0, which adjusts the
year difference down a year if CURDATE() occurs earlier in
the year than birth. The full expression is somewhat ungainly,
so an alias (age) is used to make the output column label more
meaningful.
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 ORDER
BY name clause to sort the output by name:
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age
-> FROM pet ORDER BY name;
+----------+------------+------------+------+
| 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 |
+----------+------------+------------+------+
To sort the output by age rather than name, just use a
different ORDER BY clause:
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age
-> FROM pet ORDER BY age;
+----------+------------+------------+------+
| 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 |
+----------+------------+------------+------+
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
death value is NULL. Then, for those with non-NULL
values, compute the difference between the death and birth
values:
mysql> SELECT name, birth, death,
-> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
-> AS age
-> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+------+
The query uses death IS NOT NULL rather than death <> NULL
because NULL is a special value that cannot be compared using the usual
comparison operators. This is discussed later.
See section 3.3.4.6 Working with NULL Values.
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 birth column. MySQL provides several
date-part extraction functions, such as YEAR(), MONTH(), and
DAYOFMONTH(). MONTH() is the appropriate function here. To
see how it works, run a simple query that displays the value of both
birth and MONTH(birth):
mysql> SELECT name, birth, MONTH(birth) FROM pet; +----------+------------+--------------+ | 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 | +----------+------------+--------------+
Finding animals with birthdays in the upcoming month is easy, too. Suppose
that the current month is April. Then the month value is 4 and you look
for animals born in May (month 5) like this:
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5; +-------+------------+ | name | birth | +-------+------------+ | Buffy | 1989-05-13 | +-------+------------+
There is a small complication if the current month is December.
You don't just add one to the month number (12) and look for animals
born in month 13, because there is no such month. Instead, you look for
animals born in January (month 1).
You can even write the query so that it works no matter what the current
month is. That way you don't have to use a particular month number
in the query. DATE_ADD() allows you to add a time interval to a
given date. If you add a month to the value of CURDATE(), then extract
the month part with MONTH(), the result produces the month in which to
look for birthdays:
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
A different way to accomplish the same task is to add 1 to get the
next month after the current one (after using the modulo function (MOD)
to wrap around the month value to 0 if it is currently
12):
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
Note that MONTH returns a number between 1 and 12. And
MOD(something,12) returns a number between 0 and 11. So the
addition has to be after the MOD(), otherwise we would go from
November (11) to January (1).
NULL Values
The NULL value can be surprising until you get used to it.
Conceptually, NULL means missing value or unknown value and it
is treated somewhat differently than other values. To test for NULL,
you cannot use the arithmetic comparison operators such as =, <,
or <>. To demonstrate this for yourself, try the following query:
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+
Clearly you get no meaningful results from these comparisons. Use
the IS NULL and IS NOT NULL operators instead:
mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+---------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+
Note that in MySQL, 0 or NULL means false and anything else means
true. The default truth value from a boolean operation is 1.
This special treatment of NULL is why, in the previous section, it
was necessary to determine which animals are no longer alive using
death IS NOT NULL instead of death <> NULL.
Two NULL values are regarded as equal in a GROUP BY.
When doing an ORDER BY, NULL values are presented first if you
do ORDER BY ... ASC and last if you do ORDER BY ... DESC.
Note that MySQL 4.0.2 to 4.0.10 incorrectly always sorts NULL values
first regardless of the sort direction.
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 vi, grep, and sed.
SQL pattern matching allows you to use `_' to match any single
character and `%' 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 =
or <> when you use SQL patterns; use the LIKE or NOT
LIKE comparison operators instead.
To find names beginning with `b':
mysql> SELECT * FROM pet WHERE name LIKE 'b%'; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+
To find names ending with `fy':
mysql> SELECT * FROM pet WHERE name LIKE '%fy'; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+
To find names containing a `w':
mysql> SELECT * FROM pet WHERE name LIKE '%w%'; +----------+-------+---------+------+------------+------------+ | 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 | +----------+-------+---------+------+------------+------------+
To find names containing exactly five characters, use five instances of the `_' pattern character:
mysql> SELECT * FROM pet WHERE name LIKE '_____'; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+
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 REGEXP and NOT REGEXP operators (or RLIKE and
NOT RLIKE, which are synonyms).
Some characteristics of extended regular expressions are:
REGEXP pattern match succeed if
the pattern matches anywhere in the value being tested.
(This differs from a LIKE pattern match, which succeeds only if the
pattern matches the entire value.)
To demonstrate how extended regular expressions work, the LIKE queries
shown previously are rewritten here to use REGEXP.
To find names beginning with `b', use `^' to match the beginning of the name:
mysql> SELECT * FROM pet WHERE name REGEXP '^b'; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+
Prior to MySQL Version 3.23.4, REGEXP is case sensitive,
and the previous query will return no rows. In this case, to match either
lowercase or uppercase `b', use this query instead:
mysql> SELECT * FROM pet WHERE name REGEXP '^[bB]';
From MySQL 3.23.4 on, if you really want to force a REGEXP comparison to
be case sensitive, use the BINARY keyword to make one of the
strings a binary string. This query will match only lowercase `b'
at the beginning of a name:
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';
To find names ending with `fy', use `$' to match the end of the name:
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$'; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+
To find names containing a `w', use this query:
mysql> SELECT * FROM pet WHERE name REGEXP 'w'; +----------+-------+---------+------+------------+------------+ | 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 | +----------+-------+---------+------+------------+------------+
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.
To find names containing exactly five characters, use `^' and `$' to match the beginning and end of the name, and five instances of `.' in between:
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$'; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+
You could also write the previous query using the `{n}'
``repeat-n-times'' operator:
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
Databases are often used to answer the question, ``How often does a certain type of data occur in a table?'' 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.
Counting the total number of animals you have is the same question as ``How
many rows are in the pet table?'' because there is one record per pet.
COUNT(*) counts the number of rows, so
the query to count your animals looks like this:
mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+
Earlier, you retrieved the names of the people who owned pets. You can
use COUNT() if you want to find out how many pets each owner has:
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; +--------+----------+ | owner | COUNT(*) | +--------+----------+ | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +--------+----------+
Note the use of GROUP BY to group together all records for each
owner. Without it, all you get is an error message:
mysql> SELECT owner, COUNT(*) FROM pet; ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause
COUNT() and GROUP BY are useful for characterizing your
data in various ways. The following examples show different ways to
perform animal census operations.
Number of animals per species:
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species; +---------+----------+ | species | COUNT(*) | +---------+----------+ | bird | 2 | | cat | 2 | | dog | 3 | | hamster | 1 | | snake | 1 | +---------+----------+
Number of animals per sex:
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex; +------+----------+ | sex | COUNT(*) | +------+----------+ | NULL | 1 | | f | 4 | | m | 4 | +------+----------+
(In this output, NULL indicates that the sex is unknown.)
Number of animals per combination of species and sex:
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; +---------+------+----------+ | 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 | +---------+------+----------+
You need not retrieve an entire table when you use COUNT(). For
example, the previous query, when performed just on dogs and cats, looks like
this:
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE species = 'dog' OR species = 'cat'
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
+---------+------+----------+
Or, if you wanted the number of animals per sex only for known-sex animals:
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE sex IS NOT NULL
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
The pet 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:
Given these considerations, the CREATE TABLE statement for the
event table might look like this:
mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
-> type VARCHAR(15), remark VARCHAR(255));
As with the pet table, it's easiest to load the initial records
by creating a tab-delimited text file containing the information:
| name | date | type | remark |
| Fluffy | 1995-05-15 | litter | 4 kittens, 3 female, 1 male |
| Buffy | 1993-06-23 | litter | 5 puppies, 2 female, 3 male |
| Buffy | 1994-06-19 | litter | 3 puppies, 3 female |
| Chirpy | 1999-03-21 | vet | needed beak straightened |
| Slim | 1997-08-03 | vet | broken rib |
| Bowser | 1991-10-12 | kennel | |
| Fang | 1991-10-12 | kennel | |
| Fang | 1998-08-28 | birthday | Gave him a new chew toy |
| Claws | 1998-03-17 | birthday | Gave him a new flea collar |
| Whistler | 1998-12-09 | birthday | First birthday |
Load the records like this:
mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
Based on what you've learned from the queries you've run on the pet
table, you should be able to perform retrievals on the records in the
event table; the principles are the same. But when is the
event table by itself insufficient to answer questions you might ask?
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
event table, but to calculate her age on that date you need her
birth date, which is stored in the pet table.
This means the query requires both tables:
mysql> SELECT pet.name,
-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
-> remark
-> FROM pet, event
-> WHERE pet.name = event.name AND type = 'litter';
+--------+------+-----------------------------+
| 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 |
+--------+------+-----------------------------+
There are several things to note about this query:
FROM clause lists two tables because the query needs to pull
information from both of them.
name column. The query uses
WHERE clause to match up records in the two tables based on the
name values.
name 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.
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 pet table with itself to produce
candidate pairs of males and females of like species:
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1, pet AS p2
-> WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
+--------+------+--------+------+---------+
| name | sex | name | sex | species |
+--------+------+--------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
+--------+------+--------+------+---------+
In this query, we specify aliases for the table name in order to refer to the columns and keep straight which instance of the table each column reference is associated with.
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.
You have already seen SHOW DATABASES, which lists the databases
managed by the server. To find out which database is currently selected,
use the DATABASE() function:
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | menagerie | +------------+
If you haven't selected any database yet, the result is NULL
(or the empty string before MySQL 4.1.1).
To find out what tables the current database contains (for example, when you're not sure about the name of a table), use this command:
mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | event | | pet | +---------------------+
If you want to find out about the structure of a table, the DESCRIBE
command is useful; it displays information about each of a table's columns:
mysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | 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 | | +---------+-------------+------+-----+---------+-------+
Field indicates the column name, Type is the data type for
the column, NULL indicates whether the column can contain
NULL values, Key indicates whether the column is
indexed, and Default specifies the column's default value.
If you have indexes on a table,
SHOW INDEX FROM tbl_name produces information about them.
mysql in Batch Mode
In the previous sections, you used mysql interactively to enter
queries and view the results. You can also run mysql in batch
mode. To do this, put the commands you want to run in a file, then
tell mysql to read its input from the file:
shell> mysql < batch-file
If you are running mysql under Windows and have some special
characters in the file that cause problems, you can do this:
C:\> mysql -e "source batch-file"
If you need to specify connection parameters on the command line, the command might look like this:
shell> mysql -h host -u user -p < batch-file Enter password: ********
When you use mysql this way, you are creating a script file, then
executing the script.
If you want the script to continue even if some of the statements in it
produce errors, you should
use the --force command-line option.
Why use a script? Here are a few reasons:
mysql to execute it again.
shell> mysql < batch-file | more
shell> mysql < batch-file > mysql.out
cron job. In this case, you must use batch mode.
The default output format is different (more concise) when you run
mysql in batch mode than when you use it interactively. For
example, the output of SELECT DISTINCT species FROM pet looks like
this when mysql is run interactively:
+---------+ | species | +---------+ | bird | | cat | | dog | | hamster | | snake | +---------+
In batch mode, the output looks like this instead:
species bird cat dog hamster snake
If you want to get the interactive output format in batch mode, use
mysql -t. To echo to the output the commands that are executed, use
mysql -vvv.
You can also use scripts from the mysql prompt by
using the source or \. command:
mysql> source filename; mysql> \. filename
Here are examples of how to solve some common problems with MySQL.
Some of the examples use the table shop 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 (article,
dealer) is a primary key for the records.
Start the command-line tool mysql and select a database:
shell> mysql your-database-name
(In most MySQL installations, you can use the database name test).
You can create and populate the example table with these statements:
mysql> 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));
mysql> 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);
After issuing the statements, the table should have the following contents:
mysql> 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 | +---------+--------+-------+
``What's the highest item number?''
SELECT MAX(article) AS article FROM shop; +---------+ | article | +---------+ | 4 | +---------+
``Find number, dealer, and price of the most expensive article.''
In standard SQL (and as of MySQL 4.1), this is easily done with a subquery:
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);
In MySQL versions prior to 4.1, just do it in two steps:
SELECT statement.
mysql> SELECT MAX(price) FROM shop; +------------+ | MAX(price) | +------------+ | 19.95 | +------------+
mysql> SELECT article, dealer, price
-> FROM shop
-> WHERE price=19.95;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0004 | D | 19.95 |
+---------+--------+-------+
Another solution is to sort all rows descending by price and only
get the first row using the MySQL-specific LIMIT clause:
SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
Note: If there were several most expensive articles, each with a
price of 19.95, the LIMIT solution would show only one of them!
``What's the highest price per article?''
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 | +---------+-------+
``For each article, find the dealer or dealers with the most expensive price.''
In standard SQL (and as of MySQL 4.1), the problem can be solved with a subquery like this:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
In MySQL versions prior to 4.1, it's best do it in several steps:
This can easily be done with a temporary table and a join:
CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);
LOCK TABLES shop READ;
INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;
If you don't use a TEMPORARY table, you must also lock the tmp
table.
``Can it be done with a single query?''
Yes, but only by using a quite inefficient trick called the ``MAX-CONCAT trick'':
SELECT article,
SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM shop
GROUP BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+
The last example can be made a bit more efficient by doing the splitting of the concatenated column in the client.
You can use MySQL user variables to remember results without having to store them in temporary variables in the client. See section 9.3 User Variables.
For example, to find the articles with the highest and lowest price you can do this:
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+
In MySQL 3.23.44 and up, InnoDB tables support checking of
foreign key constraints. See section 15 The InnoDB Storage Engine.
See also section 1.5.5.5 Foreign Keys.
You don't actually need foreign keys to join two tables.
For table types other than InnoDB,
the only things MySQL currently doesn't do are 1)
CHECK to make sure that the keys you use
really exist in the table or tables you're referencing and 2)
automatically delete rows from a table with a foreign key
definition. Using your keys to join tables will work just fine:
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, shirt s
WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id
AND s.color <> 'white';
+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+
An OR using a single key is well optimized, as is the handling
of AND.
The one tricky case is that of searching on two different keys
combined with OR:
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' OR field2_index = '1'
This case is optimized from MySQL 5.0.0. See section 7.2.6 Index Merge Optimization.
In MySQL 4.0 and up, you can also solve the problem efficiently by
using a UNION that combines the output of two separate
SELECT statements.
See section 13.1.7.2 UNION Syntax.
Each SELECT searches only one key and can be optimized:
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';
Prior to MySQL 4.0, you can achieve the same effect by using a
TEMPORARY table and separate SELECT statements.
This type of optimization is also very good if
you are using very complicated queries where the SQL server does the
optimizations in the wrong order.
CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index
FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index
FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;
This method of solving the problem is in effect a UNION of two queries.
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.
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);
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:
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;
Which returns:
+------+-------+------+ | year | month | days | +------+-------+------+ | 2000 | 01 | 3 | | 2000 | 02 | 2 | +------+-------+------+
The query calculates how many different days appear in the table for each year/month combination, with automatic removal of duplicate entries.
AUTO_INCREMENT
The AUTO_INCREMENT attribute can be used to generate a unique
identity for new rows:
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;
Which returns:
+----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+
You can retrieve the most recent AUTO_INCREMENT value with the
LAST_INSERT_ID() SQL function or the mysql_insert_id() C API
function. These functions are connection-specific, so their return value
is not affected by another connection also doing inserts.
Note: For a multiple-row insert,
LAST_INSERT_ID()/mysql_insert_id() will actually return the
AUTO_INCREMENT key from the first of the inserted rows.
This allows multiple-row inserts to be reproduced correctly on other servers
in a replication setup.
For MyISAM and BDB tables you can specify AUTO_INCREMENT
on a secondary column in a multiple-column index. In this case, the generated
value for the AUTO_INCREMENT column is calculated as
MAX(auto_increment_column)+1 WHERE prefix=given-prefix. This is
useful when you want to put data into ordered groups.
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;
Which returns:
+--------+----+---------+ | grp | id | name | +--------+----+---------+ | fish | 1 | lax | | mammal | 1 | dog | | mammal | 2 | cat | | mammal | 3 | whale | | bird | 1 | penguin | | bird | 2 | ostrich | +--------+----+---------+
Note that in this case (when the AUTO_INCREMENT column is part of a
multiple-column index), AUTO_INCREMENT values will be reused if you
delete the row with the biggest AUTO_INCREMENT value in any group.
This happens even for MyISAM tables, for which AUTO_INCREMENT
values normally are not reused.)
At Analytikerna and Lentus, we have been doing the systems and field work for a big research project. This project is 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.
The project involves a screening part where all twins in Sweden older than 65 years are interviewed by telephone. Twins who meet certain criteria are passed on to the next stage. In this latter stage, twins who want to participate are visited by a doctor/nurse team. Some of the examinations include physical and neuropsychological examination, laboratory testing, neuroimaging, psychological status assessment, and family history collection. In addition, data are collected on medical and environmental risk factors.
More information about Twin studies can be found at: http://www.mep.ki.se/twinreg/index_en.html
The latter part of the project is administered with a Web interface written using Perl and MySQL.
Each night all data from the interviews is moved into a MySQL database.
The following query is used to determine who goes into the second part of the project:
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 sceening 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;
Some explanations:
CONCAT(p1.id, p1.tvab) + 0 AS tvid
id and tvab in
numerical order. Adding 0 to the result causes MySQL to
treat the result as a number.
id
tvab
1 or 2.
ptvab
tvab. When tvab is 1 this is
2, and vice versa. It exists to save typing and to make it easier for
MySQL to optimize the query.
This query demonstrates, among other things, how to do lookups on a table
from the same table with a join (p1 and p2). 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.
All of the above exist in all tables with twin-related information. We
have a key on both id,tvab (all tables), and id,ptvab
(person_data) to make queries faster.
On our production machine (A 200MHz UltraSPARC), this query returns about 150-200 rows and takes less than one second.
The current number of records in the tables used in the query:
| Table | Rows |
person_data | 71074 |
lentus | 5291 |
twin_project | 5286 |
twin_data | 2012 |
informant_data | 663 |
harmony | 381 |
postal_groups | 100 |
Each interview ends with a status code called event. The query
shown here is used to display a table over 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.
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 sceening 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;
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.
You can change the Apache logging format to be easily readable by MySQL by putting the following into the Apache configuration file:
LogFormat \
"\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \
\"%U\",\"%{Referer}i\",\"%{User-Agent}i\""
To load a log file in that format into MySQL, you can use a statement something like this:
LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
The named table should be created to have columns that correspond to those
that the LogFormat line writes to the log file.
Go to the first, previous, next, last section, table of contents.