Creating and Using a Database
=============================
Now that you know how to enter commands, it's time to access a database.
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
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. *Note `GRANT': GRANT.
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.
should
probably ask your MySQL administrator for permission to use a database
of your own. Suppose 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.
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;
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 a Table
----------------
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.
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'll soon need to send out birthday greetings, 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 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' datatype 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>
| 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.
Loading Data into a Table
-------------------------
After creating your table, you need to populate it. The `LOAD DATA' and
`INSERT' statements are useful for this.
Suppose 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
WhistlerGwen bird 1997-12-09
Slim Benny snake m 1996-04-29
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 "pet.txt" INTO TABLE pet;
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 *Note `LOAD
DATA LOCAL': 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 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);
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.
Retrieving Information from a Table
-----------------------------------
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;
clause is
optional. If it's present, `conditions_to_satisfy' specifies
conditions that rows must satisfy to qualify for retrieval.
Selecting All Data
..................
The simplest form of `SELECT' retrieves everything from a table:
mysql>
| 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 instance, 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:
* 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.
Selecting Particular Rows
.........................
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>
| 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>
| 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, though `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 |
+-------+--------+---------+------+------------+-------+
Selecting Particular Columns
............................
`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> Benny |
| Diane |
+--------+
However, notice that the query simply retrieves the `owner' field from
each record, and some of them appear more than once. To minimise 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 |
+--------+---------+------------+
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.
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> |
| 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.
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.
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),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) 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) 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) AS 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. *Note Working with
`NULL': Working with NULL.
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 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, of
course. 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').
Working with `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.
Pattern Matching
................
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>
| 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 fives instances of
the `_' pattern character:
mysql>
| 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 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 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>
| 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 |
+----------+-------+---------+------+------------+------------+
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 |
+-------+--------+---------+------+------------+-------+
Counting Rows
.............
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.
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 characterising 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 |
+---------+------+----------+
Using More Than one Table
.........................
table.
What should this table look like? It needs:
* To contain the pet name so you know which animal each event
pertains to.
* A date so you know when the event occurred.
* A field to describe the event.
* An event type field, if you want to be able to categorise 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:
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 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) 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:
* The `FROM' clause lists 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.
* 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, 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.
[Назад] [Содержание] [Вперед]
| Главная |