MySQL入门手册

时间：2022-03-14 01:22

本文内容摘自MySQL5.6官方文档，主要选取了在实践过程中所用到的部分文字解释，力求只摘录重点，快速学会使用MySQL，本文所贴代码地方就是我亲自练习过的代码，凡本文没有练习过的代码都没有贴在此处，如果读者想自己尝试，可以查看官方文档，文中给出了原官方文档的对应链接以供查阅。

本文地址：

This chapter provides a tutorial introduction to MySQL by showing how to use the client program to create and use a simple database.

This chapter describes the entire process of setting up and using a database.

　　3.1.

　　3.2.

　　3.3.

　　3.4.

To connect to the server, you will usually need to provide a MySQL user name when you invoke 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: ********

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

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql>

Most examples in the following sections assume that you are connected to the server. They indicate this by the mysql> prompt.

This section describes the basic principles of entering commands, using several queries you can try out to familiarize yourself with how works.

mysql> SELECT VERSION(), CURRENT_DATE;
+------------+--------------+
| VERSION()  | CURRENT_DATE |
+------------+--------------+
| 5.6.21-log | 2014-10-14   |
+------------+--------------+
1 row in set (0.05 sec)

mysql>

Here is another query. It demonstrates that you can use as a simple calculator:

mysql> SELECT SIN(PI()/4), (3+2)*5;
+--------------------+---------+
| SIN(PI()/4)        | (3+2)*5 |
+--------------------+---------+
| 0.7071067811865475 |      25 |
+--------------------+---------+

The queries shown thus far have been relatively short, single-line statements. You can even enter multiple statements on a single line. Just end each one with a semicolon:

mysql> SELECT VERSION(); SELECT NOW();
+------------+
| VERSION()  |
+------------+
| 5.6.21-log |
+------------+
1 row in set (0.30 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2014-10-16 14:27:13 |
+---------------------+
1 row in set (0.19 sec)

mysql>

A command need not be given all on a single line, so lengthy commands that require several lines are not a problem. determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line. (In other words, accepts free-format input: it collects input lines but does not execute them until it sees the semicolon.)

Here is a simple multiple-line statement:

mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;
+----------------+--------------+
| USER()         | CURRENT_DATE |
+----------------+--------------+
| root@localhost | 2014-10-16   |
+----------------+--------------+

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          ;;小写的才对
ERROR: 
Usage: \C charset_name | charset charset_name
    -> \c
mysql>

The following table shows each of the prompts you may see and summarizes what they mean about the state that 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 `/*`.

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;
    ‘>

Instead of wondering why this query takes so long, notice the clue provided by the ‘> prompt. It tells you that 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 quotation 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 interprets it as part of the string that it is collecting. Instead, enter the closing quote character (so knows you‘ve finished the string), then type \c:

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

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 perform the following operations:

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 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

我的pet.txt文件内容：

Fluffy    Haroid    cat    f    1993-02-04    \N
Claws    Gwen    cat    m    1994-03-17    \N
Buffy    Haroid    cat    f    1989-05-13    \N
Fang    Benny    dog    m    1990-08-27    \N
Browser    Diane    dog    m    1979-08-31    1995-07-29
Chirpy    Gwen    bird    f    1998-09-11    \N
Whistler    Gwen    bird    \N    1997-12-09    \N
Slim    Benny    snake    m    1996-04-29    \N

To load the text file pet.txt into the pet table, use this statement:(这里给出完整的操作过程)

$ mysql -u root -p
mysql> SHOW DATABASES;
mysql> SELECT DATABASE();
mysql> USE menagerie
mysql> SELECT DATABASE();
mysql> SHOW TABLES;
mysql> DESCRIBE pet;
mysql> LOAD DATA LOCAL INFILE ‘/home/hostName/test/pet.txt‘ INTO TABLE pet;           ##hostName主机用户名
Query OK, 8 rows affected (0.23 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql>

You can specify the column value separator and end of line marker explicitly in the statement if you wish, but the defaults are tab and linefeed. These are sufficient for the statement to read the file pet.txt properly.

When you want to add new records one at a time, the statement is useful. In its simplest form, you supply values for each column, in the order in which the columns were listed in the statement. Suppose that Diane gets a new hamster named “Puffball.” You could add a new record using an statement like this:

mysql> INSERT INTO pet
    -> VALUES (‘Puffball‘, ‘Diane‘, ‘hamster‘, ‘f‘, ‘1999-03-30‘, NULL);
Query OK, 1 row affected (0.09 sec)

String and date values are specified as quoted strings here. Also, with , you can insert NULL directly to represent a missing value. You do not use \N like you do with .

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 statements rather than a single statement.

The 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;

1.选择所有数据：

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Haroid | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Haroid | cat     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Browser  | 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       |
+----------+--------+---------+------+------------+------------+

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:（这里只使用UPDATE方法，另一方法是用DELETE与LOAD DATA，见）

mysql> UPDATE pet SET birth = ‘1989-08-31‘ WHERE name = ‘Browser‘;

2. 选择特定行

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 = ‘browSer‘;
+---------+-------+---------+------+------------+------------+
| name    | owner | species | sex  | birth      | death      |
+---------+-------+---------+------+------------+------------+
| Browser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+---------+-------+---------+------+------------+------------+

String comparisons normally are case-insensitive不分大小写, so you can specify the name as ‘bowser‘, ‘BOWSER‘, and so forth. The query result is the same.

As shown in the preceding section, it is easy to retrieve an entire table. Just omit the WHERE clause from the 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.

mysql> SELECT * FROM pet WHERE name = ‘Bowser‘;
mysql> SELECT * FROM pet WHERE birth >= ‘1998-1-1‘;
mysql> SELECT * FROM pet WHERE species = ‘dog‘ AND sex = ‘f‘;
mysql> SELECT * FROM pet WHERE species = ‘snake‘ OR species = ‘bird‘;
mysql> SELECT * FROM pet WHERE (species = ‘cat‘ AND sex = ‘m‘)
    -> OR (species = ‘dog‘ AND sex = ‘f‘);

3. 选择特定列

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:

mysql> SELECT name, birth FROM pet;
mysql> SELECT owner FROM pet;
mysql> SELECT DISTINCT owner FROM pet;
mysql> SELECT name, species, birth FROM pet
    -> WHERE species = ‘dog‘ OR species = ‘cat‘;

4. 排列rows：

You may have noticed in the preceding examples that the result rows are displayed in no particular order. It is 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;

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 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:

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

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;

5. 日期计算：

To determine how many years old each of your pets is, use the function.

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2014-10-17 |   21 |
| Claws    | 1994-03-17 | 2014-10-17 |   20 |
| Buffy    | 1989-05-13 | 2014-10-17 |   25 |
| Fang     | 1990-08-27 | 2014-10-17 |   24 |
| Browser  | 1989-08-31 | 2014-10-17 |   25 |
| Chirpy   | 1998-09-11 | 2014-10-17 |   16 |
| Whistler | 1997-12-09 | 2014-10-17 |   16 |
| Slim     | 1996-04-29 | 2014-10-17 |   18 |
| Puffball | 1999-03-30 | 2014-10-17 |   15 |
+----------+------------+------------+------+

按name排列一下：

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age
    -> FROM pet ORDER BY name;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Browser  | 1989-08-31 | 2014-10-17 |   25 |
| Buffy    | 1989-05-13 | 2014-10-17 |   25 |
| Chirpy   | 1998-09-11 | 2014-10-17 |   16 |
| Claws    | 1994-03-17 | 2014-10-17 |   20 |
| Fang     | 1990-08-27 | 2014-10-17 |   24 |
| Fluffy   | 1993-02-04 | 2014-10-17 |   21 |
| Puffball | 1999-03-30 | 2014-10-17 |   15 |
| Slim     | 1996-04-29 | 2014-10-17 |   18 |
| Whistler | 1997-12-09 | 2014-10-17 |   16 |
+----------+------------+------------+------+

To sort the output by age rather than name, just use a different ORDER BY clause:

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

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, 
    -> TIMESTAMPDIFF(YEAR, birth, death) AS age
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;
+---------+------------+------------+------+
| name    | birth      | death      | age  |
+---------+------------+------------+------+
| Browser | 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 .

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 , , and . is the appropriate function here. To see how it works, run a simple query that displays the value of both birth and :

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

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;

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. enables you to add a time interval to a given date. If you add a month to the value of , then extract the month part with , 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;

returns a number between 1 and 12. And returns a number between 0 and 11. So the addition has to be after the , otherwise we would go from November (11) to January (1).

6. 处理NULL值

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, use the and operators, as shown here:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;

You cannot use arithmetic comparison operators such as , , or to test for NULL. 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 |
+----------+-----------+----------+----------+

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.

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 |
+-----------+---------------+------------+----------------+

Thus it is entirely possible to insert a zero or empty string into a NOT NULL column, as these are in fact NOT NULL. See .

7. 模式匹配

To find names beginning with “b”:

mysql> SELECT * FROM pet WHERE name LIKE ‘b%‘;

To find names ending with “fy”:

mysql> SELECT * FROM pet WHERE name LIKE ‘%fy‘;

To find names containing a “w”:

mysql> SELECT * FROM pet WHERE name LIKE ‘%w%‘;

To find names containing exactly five characters, use five instances of the “_” pattern character:

mysql> SELECT * FROM pet WHERE name LIKE ‘_____‘;

To demonstrate how extended regular expressions work, the queries shown previously are rewritten here to use .

To find names beginning with “b”, use “^” to match the beginning of the name:

mysql> SELECT * FROM pet WHERE name REGEXP ‘^b‘;

If you really want to force a comparison to be case sensitive, use the 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$‘;

To find names containing a “w”, use this query:

mysql> SELECT * FROM pet WHERE name REGEXP ‘w‘;

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 ‘^.....$‘;

You could also write the previous query using the {n} (“repeat-n-times”) operator:

mysql> SELECT * FROM pet WHERE name REGEXP ‘^.{5}$‘;

, provides more information about the syntax for regular expressions.

8. 列的统计

Databases are often used to answer the question, “How often does a certain type of data occur in a table?”

counts the number of rows, so the query to count your animals looks like this:

mysql> SELECT COUNT(*) FROM pet;

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

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

Number of animals per combination of species and sex:

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

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = ‘dog‘ OR species = ‘cat‘
    -> GROUP BY species, sex;

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE sex IS NOT NULL
    -> GROUP BY species, sex;

If you name columns to select in addition to the value, a GROUP BY clause should be present that names those same columns. Otherwise, the following occurs:

我的event.txt(注意，Fang那行的 remark \N之后多了一个空格，造成LOAD DATA将其识别为N空格，而不是NULL):

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    \N
Fang    1991-10-12    kennel    \N 
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 ‘/home/hostName/test/event.txt‘ INTO TABLE event;

mysql> SELECT * FROM event;
+----------+------------+----------+-----------------------------+
| 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   | NULL                        |
| Fang     | 1991-10-12 | kennel   | N                           |
| 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              |
+----------+------------+----------+-----------------------------+

mysql> UPDATE event SET remark = NULL WHERE name = ‘Fang‘ AND type = ‘kennel‘;

mysql> SELECT * FROM event;
+----------+------------+----------+-----------------------------+
| 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   | NULL                        |
| Fang     | 1991-10-12 | kennel   | NULL                        |
| 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              |
+----------+------------+----------+-----------------------------+

Suppose that you want to find out the ages at which each pet had its litters.

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         |
+--------+------+-----------------------------+

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‘;

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)?

You have previously seen , which lists the databases managed by the server. To find out which database is currently selected, use the function:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+

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 statement 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    |       |
+---------+-------------+------+-----+---------+-------+

In the previous sections, you used interactively to enter queries and view the results. You can also run in batch mode. To do this, put the commands you want to run in a file, then tell to read its input from the file:

shell> mysql -h host -u user -p < batch-file
Enter password: ********

我的batch-file：select.batchmode

SHOW DATABASES;
SELECT DATABASE();
USE menagerie;
SHOW TABLES;
SELECT DISTINCT species FROM pet;

$ mysql -u root -p < /home/hostName/test/select.batchmode 
Enter password: 
Database
information_schema
menagerie
mysql
performance_schema
test
DATABASE()
NULL
Tables_in_menagerie
event
pet
species
cat
dog
bird
snake
hamster

ovonel@ovonel-usa:~$ mysql -u root -p -t < /home/hostName/test/select.batchmode    ###-t互动输出格式
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
.......

$ mysql -u root -p -t -vvv < /home/ovonel/test/select.batchmode      ###-vvv显示执行的命令
Enter password: 
--------------
SHOW DATABASES
--------------

+--------------------+
| Database           |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

.........

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 and select a database:

首先创建一个market

$ mysql -u root -p
mysql> CREATE DATABASE market;
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| market             |
| menagerie          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
mysql> USE market;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)
mysql> quit

开始操作：

$ mysql -u root -p market    ###使用market数据库
Enter password: 

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| market     |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE shop (
    ->     article INT(4) UNSIGNED ZEROFILL DEFAULT ‘0000‘ NOT NULL,
    ->     dealer  CHAR(20)                 DEFAULT ‘‘     NOT NULL,
    ->     price   DOUBLE(16,2)             DEFAULT ‘0.00‘ NOT NULL,
    ->     PRIMARY KEY(article, dealer));
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO shop VALUES
    ->     (1,‘A‘,3.45),(1,‘B‘,3.99),(2,‘A‘,10.99),(3,‘B‘,1.45),
    ->     (3,‘C‘,1.69),(3,‘D‘,1.25),(4,‘D‘,19.95);
Query OK, 7 rows affected (0.10 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
7 rows in set (0.01 sec)

“What is the highest item number?”

mysql> SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
|       4 |
+---------+

Task: Find the number, dealer, and price of the most expensive article.

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+

其他两种方法LEFT JOIN, LIMIT：

mysql> SELECT s1.article, s1.dealer, s1.price
    -> FROM shop s1
    -> LEFT JOIN shop s2 ON s1.price < s2.price
    -> WHERE s2.article IS NULL;

mysql> SELECT article, dealer, price
    -> FROM shop
    -> ORDER BY price DESC
    -> LIMIT 1;

Task: Find the highest price per article.

mysql> 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 |
+---------+-------+

Task: For each article, find the dealer or dealers with the most expensive price.

This problem can be solved with a subquery like this one:

mysql> SELECT article, dealer, price
    -> FROM   shop s1
    -> WHERE  price=(SELECT MAX(s2.price)
    ->               FROM shop s2
    ->               WHERE s1.article = s2.article);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

The preceding example uses a correlated subquery, which can be inefficient (see ). Other possibilities for solving the problem are to use an uncorrelated subquery in the FROM clause or a LEFT JOIN.

mysql> SELECT s1.article, dealer, s1.price
    -> FROM shop s1
    -> JOIN (
    ->   SELECT article, MAX(price) AS price
    ->   FROM shop
    ->   GROUP BY article) AS s2
    ->   ON s1.article = s2.article AND s1.price = s2.price;

mysql> SELECT s1.article, s1.dealer, s1.price
    -> FROM shop s1
    -> LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
    -> WHERE s2.article IS NULL;

The LEFT JOIN works on the basis that when s1.price is at its maximum value, there is no s2.price with a greater value and the s2 rows values will be NULL. See .

You can employ MySQL user variables to remember results without having to store them in temporary variables in the client. (See .)

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;
+------------------------+------------------------+
| @min_price:=MIN(price) | @max_price:=MAX(price) |
+------------------------+------------------------+
|                   1.25 |                  19.95 |
+------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

It is also possible to store the name of a database object such as a table or a column in a user variable and then to use this variable in an SQL statement; however, this requires the use of a prepared statement. See , for more information.

In MySQL, InnoDB tables support checking of foreign key constraints. See , and .

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.

You can use a column so created as a join column, as shown here:

$ mysql -u root -p
Enter password: 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| market             |
| menagerie          |
| mysql              |
| performance_schema |
| test               |
+--------------------+

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> USE market;
mysql> SHOW TABLES;

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);
mysql> DESCRIBE person;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(60)             | NO   |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+

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)
);
mysql> DESCRIBE shirt;
+-------+---------------------------------------------+------+-----+---------+----------------+
| Field | Type                                        | Null | Key | Default | Extra          |
+-------+---------------------------------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned                        | NO   | PRI | NULL    | auto_increment |
| style | enum(‘t-shirt‘,‘polo‘,‘dress‘)              | NO   |     | NULL    |                |
| color | enum(‘red‘,‘blue‘,‘orange‘,‘white‘,‘black‘) | NO   |     | NULL    |                |
| owner | smallint(5) unsigned                        | NO   |     | NULL    |                |
+-------+---------------------------------------------+------+-----+---------+----------------+

INSERT INTO person VALUES (NULL, ‘Antonio Paz‘);
mysql> SELECT * FROM person;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | Antonio Paz |
+----+-------------+

mysql> SELECT @last := LAST_INSERT_ID();
+---------------------------+
| @last := LAST_INSERT_ID() |
+---------------------------+
|                         1 |
+---------------------------+

INSERT INTO shirt VALUES
(NULL, ‘polo‘, ‘blue‘, @last),
(NULL, ‘dress‘, ‘white‘, @last),
(NULL, ‘t-shirt‘, ‘blue‘, @last);

mysql> SELECT * FROM shirt;
+----+---------+-------+-------+
| id | style   | color | owner |
+----+---------+-------+-------+
|  1 | polo    | blue  |     1 |
|  2 | dress   | white |     1 |
|  3 | t-shirt | blue  |     1 |
+----+---------+-------+-------+

INSERT INTO person VALUES (NULL, ‘Lilliana Angelovska‘);
mysql> SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

mysql> SELECT @last := LAST_INSERT_ID();
+---------------------------+
| @last := LAST_INSERT_ID() |
+---------------------------+
|                         2 |
+---------------------------+

INSERT INTO shirt VALUES
(NULL, ‘dress‘, ‘orange‘, @last),
(NULL, ‘polo‘, ‘red‘, @last),
(NULL, ‘dress‘, ‘blue‘, @last),
(NULL, ‘t-shirt‘, ‘white‘, @last);

mysql> 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 |
+----+---------+--------+-------+

mysql> 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 or :

mysql> 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=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

（完）

3.7 Using MySQL with Apache

There are programs that let you authenticate your users from a MySQL database and also let you write your log files into a MySQL table.

You can change the Apache logging format to be easily readable by MySQL by putting the following into the Apache configuration file:

LogFormat         "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\",          \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

To load a log file in that format into MySQL, you can use a statement something like this:

LOAD DATA INFILE ‘/local/access_log‘ INTO TABLE tbl_name
FIELDS TERMINATED BY ‘,‘ OPTIONALLY ENCLOSED BY ‘"‘ ESCAPED BY ‘\\‘

The named table should be created to have columns that correspond to those that the LogFormat line writes to the log file.

电脑软件

今日推荐

惠家教完整版

版本：1.0.8

大小：3.24MB

日期：2024-10-06
金箍棒英语官方版

版本：10.0

大小：8.44MB

日期：2024-10-06
趣味校园在线版

版本：1.0.0

大小：98.09MB

日期：2024-10-06
学习猫官方版

版本：2.1.1

大小：12.43MB

日期：2024-10-06
暑假作业大师官方版

版本：1.0.0

大小：33.86MB

日期：2024-10-06
皮卡生活官方版

版本：v1.0.2

大小：44.6M

日期：2024-10-06

MySQL入门手册

3.7 Using MySQL with Apache

相关推荐

电脑软件

热门排行

今日推荐

热门手游