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 need
to consult the relevant portions of this manual, such as
Chapter 5, Database Administration.)
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.
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.
3.1. Connecting to and Disconnecting from the Server
To connect to the server, you will usually need to provide a MySQL user
name 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 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:
shell> mysql -h host -u user -p
Enter password: ********
host and user
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 ******** 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.1.21-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
The mysql> prompt tells you that
mysql is ready for you to
enter commands.
If you are logging in on the same machine that MySQL is running on, you
can omit the host, and simply use the following:
shell< mysql -u user -p
If, when you attempt to log in, you get an error message such as
ERROR 2002 (HY000): Can't connect to local MySQL
server through socket '/tmp/mysql.sock' (2), it means that that
MySQL server daemon (Unix) or service (Windows) is not running. Consult
the administrator or see the section of
Chapter 2, Installing and Upgrading MySQL that is appropriate
to your operating system.
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.
3.2. Entering Queries
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
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:
This query illustrates several things about
mysql:
A command normally consists of an SQL statement followed by a
semicolon. (There are some exceptions where a semicolon may be
omitted. QUIT, mentioned earlier, is
one of them. We'll get to others later.)
When you issue a command, 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 sometimes not shown in the remaining examples in
this chapter.)
Keywords may be entered in any lettercase. The following queries are
equivalent:
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:
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.)
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 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
mysql
is waiting for.
If you decide you do not 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, waiting for completion of a string that
began with a single quote (‘'’).
">
Waiting for next line, waiting for completion
of a string that began with a double quote (‘"’).
`>
Waiting for next line, waiting for completion
of an identifier that began with a backtick (‘`’).
/*>
Waiting for next line, waiting for completion
of a comment that began with /*.
The `> prompt was implemented MySQL 4.0.16.
The /*> prompt was implemented in MySQL
4.1.12.
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
realizing what you need to do. Enter a semicolon to complete the
statement, and mysql
executes it:
The '> and ">
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 ‘'’ 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 have 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. This often
indicates that you have inadvertently 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 happens. 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
single quote mark.)
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 the
'> and ">
prompts, but indicates that you have begun but not completed a
backtick-quoted identifier.
It is important to know what the '>,
">, and `>
prompts signify, because if you mistakenly enter an unterminated string,
any further lines you type appear to be ignored by
mysql — including a line
containing
QUIT. 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.
Once you know how to enter commands, you are ready to access a database.
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:
Create a database
Create a table
Load data into the table
Retrieve data from the table in various ways
Use multiple tables
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
tar file and Zip formats at
http://dev.mysql.com/doc/.
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 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 user name
assigned to you and your_client_host is the
host from which you connect to the server.
3.3.1. Creating and Selecting a Database
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.
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.)
Note: If you get an error
such as ERROR 1044 (42000): Access denied for
user 'monty'@'localhost' to database 'menagerie' 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
Section 5.7, “The MySQL Access Privilege System”.
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 in the command just
shown is not your password.
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.
3.3.2. Creating a Table
Creating the database is the easy part, but at this point it's empty, as
SHOW TABLES tells 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 need and what columns should be in each of them.
You 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 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.)
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.
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: 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
vary in length. The lengths in those column definitions 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 is 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.
Once 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):
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';
(On an Apple machine running OS X, you would likely want to use
LINES TERMINATED BY '\r'.)
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.6.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 is present,
conditions_to_satisfy
specifies one or more conditions that rows must satisfy to qualify for
retrieval.
3.3.4.1. Selecting All Data
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 at least two ways to fix this:
Edit the file pet.txt to correct the
error, then empty the table and reload it using
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.
Fix only the erroneous record with an
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.
3.3.4.2. Selecting Particular Rows
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 as 1989, not
1979.
String comparisons normally are case-insensitive, so you can specify
the name as 'bowser',
'BOWSER', and so forth. The query result
is the same.
You can specify conditions on any column, not just
name. For example, if you want to know
which animals were born during or 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 is 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 |
+-------+--------+---------+------+------------+-------+
3.3.4.3. Selecting Particular Columns
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
name and
birth columns:
Notice that the query simply retrieves the
owner 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
DISTINCT:
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 |
+--------+---------+------------+
3.3.4.4. Sorting Rows
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.
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 BINARY like so:
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:
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:
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.
3.3.4.5. Date Calculations
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.
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:
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
functions for extracting parts of dates, 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):
Finding animals with birthdays in the upcoming month is also simple.
Suppose that the current month is April. Then the month value is
4 and you can 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
cannot merely 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 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. 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 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).
3.3.4.6. Working with NULL Values
The NULL value can be surprising until
you get used to it. Conceptually, NULL
means “a missing unknown value” and it is
treated somewhat differently from 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:
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.
A common error when working with NULL is
to assume that it is not possible to insert a zero or an empty
string into a column defined as NOT NULL,
but this is not the case. These are in fact values, whereas
NULL means “not
having a value.” You can test this easily enough by using
IS [NOT]
NULL as shown:
mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
| 0 | 1 | 0 | 1 |
+-----------+---------------+------------+----------------+
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:
‘.’ matches any single character.
A character class ‘[...]’ matches any
character within the brackets. For example, ‘[abc]’
matches ‘a’, ‘b’,
or ‘c’. To name a range of
characters, use a dash. ‘[a-z]’
matches any letter, whereas ‘[0-9]’
matches any digit.
‘*’ matches zero or more instances of
the thing preceding it. For example, ‘x*’
matches any number of ‘x’
characters, ‘[0-9]*’ matches any
number of digits, and ‘.*’ matches
any number of anything.
A REGEXP pattern match succeeds if
the pattern matches anywhere in the value being tested. (This
differs from a LIKE pattern match,
which succeeds only if the pattern matches the entire value.)
To anchor a pattern so that it must match the beginning or end of
the value being tested, use ‘^’ at
the beginning or ‘$’ at the end of
the pattern.
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 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 matches 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:
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 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 animals whose
sex is known:
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 |
+---------+------+----------+
3.3.4.9. Using More Than one Table
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:
To contain the pet name so that you know which animal each event
pertains to.
A date so that you know when the event occurred.
A field to describe the event.
An event type field, if you want to be able to categorize events.
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 have learned from the queries that you have 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 INNER JOIN event
-> ON pet.name = event.name
-> WHERE event.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:
The FROM clause joins two tables
because the query needs to pull information from both of them.
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
name column. The query uses
WHERE clause to match up records in
the two tables based on the name
values.
The query uses an INNER JOIN to
combine the tables. An INNER JOIN
allows for rows from either table to appear in the result if and
only if both tables meet the conditions specified in the
ON clause. In this example, the
ON clause specifies that the
name column in the
pet table must match the
name column in the
event table. If a name appears in one
table but not the other, the row will not appear in the result
because the condition in the ON
clause fails.
Because the 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 INNER JOIN pet AS p2
-> ON 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 to refer to the
columns and keep straight which instance of the table each column
reference is associated with.
3.4. Getting Information About Databases and Tables
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 previously seen SHOW DATABASES,
which lists the databases managed by the server. To find out which
database is currently selected, use the
DATABASE() function:
If you have not yet selected any database, the result is
NULL (or the empty string before MySQL
4.1.1).
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:
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.
3.5. Using 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:
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.
You can generate new queries from existing ones that are similar by
copying and editing script files.
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 mysql to execute
it again.
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:
shell> mysql < batch-file | more
You can catch the output in a file for further processing:
shell> mysql < batch-file > mysql.out
You can distribute your script to other people so that they can also
run the commands.
Some situations do not allow for interactive use, for example, when
you run a query from a 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:
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 named
test).
You can create and populate the example table with these statements:
Using the value 19.95 shown by the previous query to be the maximum
article price, write a query to locate and display the corresponding
record:
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 get only
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.
3.6.3. Maximum of Column per Group
Task: Find 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 |
+---------+-------+
3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field
Task: 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:
Get the list of (article,maxprice) pairs.
For each article, get the corresponding rows that have the stored
maximum price.
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.
3.6.5. Using User-Defined Variables
You can employ MySQL user variables to remember results without having
to store them in temporary variables in the client. (See
Section 9.3, “User-Defined 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 |
+---------+--------+-------+
A foreign key constraint is not required merely to join two tables. For
storage engines other than
InnoDB, it is possible when defining a
column to use a REFERENCES
tbl_name(col_name)
clause, which has no actual effect, and
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.
It is extremely important to realize when using this syntax that:
MySQL does not perform any sort of CHECK
to make sure that col_name
actually exists in tbl_name
(or even that tbl_name
itself exists).
MySQL does not perform any sort of action on
tbl_name 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
ON DELETE or ON UPDATE behavior
whatsoever. (Although you can write an ON
DELETE or ON UPDATE clause as
part of the REFERENCES clause, it is
also ignored.)
This syntax creates a column;
it does
not create any sort of
index or key.
This syntax will cause an error if used in trying to define an
InnoDB table.
You can use a column so created as a join column, as shown here:
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 |
+----+-------+--------+-------+
When used in this fashion, the REFERENCES
clause is not displayed in the output of SHOW
CREATE TABLE or DESCRIBE:
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
The use of REFERENCES in this way as a
comment or “reminder” in a column definition
works with both MyISAM and
BerkeleyDB tables.
3.6.7. Searching on Two Keys
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'
In MySQL 4.0 and up, you can solve the problem efficiently by using a
UNION that combines the output of two separate
SELECT statements. See
Section 13.2.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.