mysql教程

原创

mb61288bff2bcce 2021-08-27 15:43:35 ©著作权

文章标签 mysql 数据库 oracle 数据 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者mb61288bff2bcce的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.查看mysql帮助信息

C:\Users\zhangcunli>mysql --help

mysql Ver 14.14 Distrib 5.7.9, for Win64 (x86_64)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Usage: mysql [OPTIONS] [database]

-?, --help Display this help and exit.

-I, --help Synonym for -?

--auto-rehash Enable automatic rehashing. One doesn't need to use

'rehash' to get table and field completion, but startup

and reconnecting may take a longer time. Disable with

--disable-auto-rehash.

2.链接mysql数据库（服务）

方法一：

C:\Users\zhangcunli>mysql -h localhost -u cunli -p

Enter password: *****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 12

Server version: 5.7.9-log MySQL Community Server (GPL)

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>

方法二：

C:\Users\zhangcunli>mysql -u cunli -p

Enter password: *****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 13

Server version: 5.7.9-log MySQL Community Server (GPL)

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>

3.退出mysql数据库（服务）

mysql> QUIT

Bye

4.查询mysql当前版本和当前日期

mysql> SELECT VERSION(), CURRENT_DATE;

+-----------+--------------+

| VERSION() | CURRENT_DATE |

+-----------+--------------+

| 5.7.9-log | 2016-02-16 |

+-----------+--------------+

1 row in set (0.00 sec)

mysql>

5.mysql语句大小写效果相同

mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE;

6.可以用mysql进行简单的计算

mysql> SELECT SIN(PI()/4), (4+1)*5;

+--------------------+---------+

| SIN(PI()/4) | (4+1)*5 |

+--------------------+---------+

| 0.7071067811865476 | 25 |

+--------------------+---------+

1 row in set (0.14 sec)

7.可以把多条查询语句放在一行执行，只要每行以分号结尾。

mysql> SELECT VERSION(); SELECT NOW();

+-----------+

| VERSION() |

+-----------+

| 5.7.9-log |

+-----------+

1 row in set (0.00 sec)

+---------------------+

| NOW() |

+---------------------+

| 2016-02-16 15:06:29 |

+---------------------+

1 row in set (0.00 sec)

一个简单的多行声明：

mysql> SELECT

-> USER()

-> ,

-> CURRENT_DATE;

+-----------------+--------------+

| USER() | CURRENT_DATE |

+-----------------+--------------+

| cunli@localhost | 2016-02-16 |

+-----------------+--------------+

1 row in set (0.00 sec)

8.如果你不想执行一条语句，你应该以\c结尾

mysql> SELECT

-> USER()

-> \c

9.各种提示的意思

Prompt	Meaning
`mysql>`	Ready for new query
`->`	Waiting for next line of multiple-line query
`'>`	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 `/*`

Prompt	Meaning
`mysql>`	准备执行一个查询
`->`	等待多行输入中的下一行输入
`'>`	等待下一行输入或等待输入一个结束的单引号 (“`'`”)
`">`	等待下一行输入或等待输入一个结束的双引号 (“`"`”)
`>	等待下一行输入或等待输入这个标识符结尾 (“`”)
`/*>`	等待下一行输入或等待输入一个结束的标识符`/*`

下面这种情况，MySQL正等待输入

mysql> SELECT USER()

输入一个分号，以完成这条声明

mysql> SELECT USER()

-> ;

+-----------------+

| USER() |

+-----------------+

| cunli@localhost |

+-----------------+

1 row in set (0.00 sec)

当你看见 '> 或者 "> 标记的时候，意思是提示你，应该以单引号 ’ 或双引号 “ 结尾，但是你还没有结尾

mysql> SELECT * FROM my_table WHERE name='Smith AND age < 30;

这个时候，你该怎么办呢？最简单的，关闭这个查询。但是，你不能只写\c关闭，因为这时候，mysql会把他拆分成上个字符串的一部分。

你应该以缺少的标记 ‘ + \c 结束（ '\c ），这样mysql就知道你要完成这个字符串。

mysql> SELECT * FROM my_table WHERE name='Smith AND age < 30;

'> '\c

mysql>

'\c 这个标记返回到了 mysql>, 这说明mysql准备好执行一个新的语句了。

创建和使用一个数据库

10.用 SHOW 关键字找出当前mysql服务上已经存在的数据库

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| know_yourself |

| menageria |

| mysql |

| performance_schema |

| sakila |

| sys |

| test |

| websystique |

| world |

+--------------------+

10 rows in set (0.00 sec)

11.进入并使用一个数据库

mysql> USE test

Database changed

mysql>

USE 像 LIKE 一样，不需要分号结尾，但是USE必须在单独一行。

如果你能访问一个数据库，你就能使用这个数据库，但是有时候，你创建的数据库，如果别人也能访问，他就可以删除它。因此，你应该向你的数据库管理员要一个你自己的数据库。比如你的数据库名字叫 menagerie，数据库管理员需要执行以下申请

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

your_mysql_name 是访问你数据库的用户名， your_client_host 是连接你数据库服务的地址

创建和查询一个数据库

12.创建一个数据库

mysql> CREATE DATABASE menagerie;

Query OK, 1 row affected (0.02 sec)

mysql>

13.选择要使用的数据库

mysql> USE menagerie

Database changed

14.直接进入指定的数据库

C:\Users\zhangcunli>mysql -h localhost -u cunli -p menagerie

Enter password: *****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 16

Server version: 5.7.9-log MySQL Community Server (GPL)

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>

15.创建一张表

查询当前数据中的所有表

mysql> show tables;

Empty set (0.00 sec)

创建一张pet表，有字段： name, owner, species, sex, birth, and death.

mysql> CREATE TABLE pet (name VARCHAR(20),owner VARCHAR(20),species VARCHAR(20),sex CHAR(1),birth DATE,death DATE);

Query OK, 0 rows affected (0.39 sec)

再次查看当前数据库中的表

mysql> show tables;

+---------------------+

| Tables_in_menagerie |

+---------------------+

| pet |

+---------------------+

1 row in set (0.00 sec)

可以用 DESCRIBE关键字核实你的表是否以你指定的方式创建成功。

mysql> DESCRIBE pet;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| sex | char(1) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

6 rows in set (0.17 sec)

如果你忘记了你表中的列名或类型，你可以用 DESCRIBE关键字查看

16.你可以用 LOAD DATA and INSERT 关键字向表中添加数据

假设你的pet表信息如下：（注意MySQL认为日期的格式是 'YYYY-MM-DD'，这可能和你常用的日期格式不同）

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

使用LOAD DATA 方式向表中添加数据

你应该创建一个pet.txt文件，保证每行一条数据。没有值的字段用 \N 表示，如下：

Whistler        Gwen    bird    \N      1997-12-09      \N

按如下方式加载pet.txt到pet表中：

如果你是在windows系统上创建pet.txt文件，你应该在每行的结尾加上 \r\n , 就像下面这样：

mysql> LOAD DATA LOCAL INFILE 'D:/100/pets.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';

Query OK, 8 rows affected, 7 warnings (0.25 sec)

Records: 8 Deleted: 0 Skipped: 0 Warnings: 7

mysql> select * from pet;

+----------+--------+---------+------+------------+------------+

+----------+--------+---------+------+------------+------------+

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

+----------+--------+---------+------+------------+------------+

8 rows in set (0.00 sec)

你可以用 INSERT 关键字添加一条新数据，像下面这样：

mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1990-03-30',NULL);

Query OK, 1 row affected (0.19 sec)

从一个表中检索信息

17.SELECT 关键字是从一个表中检索信息，顺序是：

SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;

what_to_select 是你想查询的信息。 可以查询其中的一列或用 * 查询出所有的列。

查询所有数据

18.下面这个例子使用SELECT查询表中的所有信息

mysql> select * from pet;

+----------+--------+---------+------+------------+------------+

+----------+--------+---------+------+------------+------------+

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

+----------+--------+---------+------+------------+------------+

8 rows in set (0.00 sec)

删除表中的所有数据

mysql> DELETE FROM pet;

Query OK, 9 rows affected (0.16 sec)

导入pet.txt到pet表中

mysql> load data local infile 'd:/100/pets.txt' INTO TABLE pet;

Query OK, 8 rows affected, 7 warnings (0.22 sec)

Records: 8 Deleted: 0 Skipped: 0 Warnings: 7

用UPDATE关键字可以修改其中一条错误

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

Query OK, 1 row affected (0.19 sec)

Rows matched: 1 Changed: 1 Warnings: 0

UDPATE 关键字只能更该一条数据

19.查询特定的行

查询名字叫Bowser动物的信息

mysql> SELECT * FROM pet WHERE name = 'Bowser';

+--------+-------+---------+------+------------+------------+

+--------+-------+---------+------+------------+------------+

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

+--------+-------+---------+------+------------+------------+

1 row in set (0.00 sec)

查询生日大于1998-1-1的动物的信息

mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';

+----------+-------+---------+------+------------+-------+

+----------+-------+---------+------+------------+-------+

+----------+-------+---------+------+------------+-------+

查询种类是dog并且性别为f的动物的信息

mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';

+-------+--------+---------+------+------------+-------+

+-------+--------+---------+------+------------+-------+

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

查询种类是snake或者种类是bird的动物的信息

mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';

+----------+-------+---------+------+------------+-------+

+----------+-------+---------+------+------------+-------+

+----------+-------+---------+------+------------+-------+

查询种类是cat并且性别是m或者种类是dog并且性别是f的动物的信息

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')

-> OR (species = 'dog' AND sex = 'f');

+-------+--------+---------+------+------------+-------+

+-------+--------+---------+------+------------+-------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

20.查询特定的列

查询表中所有动物的name和birth

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 |

+----------+------------+

查询表中所有的动物

mysql> SELECT owner FROM pet;

+--------+

| owner |

+--------+

| Harold |

| Gwen |

| Harold |

| Benny |

| Diane |

| Gwen |

| Benny |

| Diane |

+--------+

查询所有名字不相同的动物

mysql> SELECT DISTINCT owner FROM pet;

+--------+

| owner |

+--------+

| Harold |

| Gwen |

| Benny |

| Diane |

+--------+

查询表中所有dog或者cat的名字，种类，生日

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 |

+--------+---------+------------+

21.排序行

查询表中所有动物的名字和生日，按生日排序

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 |

+----------+------------+

查询表中所有动物的名字和生日，按生日逆序排序

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

+----------+------------+

| name | birth |

+----------+------------+

| Puffball | 1999-03-30 |

| Chirpy | 1998-09-11 |

| Whistler | 1997-12-09 |

| Slim | 1996-04-29 |

| Claws | 1994-03-17 |

| Fluffy | 1993-02-04 |

| Fang | 1990-08-27 |

| Bowser | 1989-08-31 |

| Buffy | 1989-05-13 |

+----------+------------+

查询表中所有动物的名字和种类，种类顺序，生日逆序

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 |

+----------+---------+------------+

22.日期的计算

计算所有动物的年龄

mysql> SELECT name, birth, CURDATE(),

-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age

-> FROM pet;

+----------+------------+------------+------+

+----------+------------+------------+------+

| Fluffy | 1993-02-04 | 2016-02-23 | 23 |

| Claws | 1994-03-17 | 2016-02-23 | 21 |

| Buffy | 1989-05-13 | 2016-02-23 | 26 |

| Fang | 1990-08-27 | 2016-02-23 | 25 |

| Bowser | 1989-08-31 | 2016-02-23 | 26 |

| Chirpy | 1998-09-11 | 2016-02-23 | 17 |

| Whistler | 1997-12-09 | 2016-02-23 | 18 |

| Slim | 1996-04-29 | 2016-02-23 | 19 |

| Puffball | 1999-03-30 | 2016-02-23 | 16 |

+----------+------------+------------+------+

把刚才查询出的结果按年龄排序

mysql> SELECT name, birth, CURDATE(),

-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age

-> FROM pet ORDER BY age;

+----------+------------+------------+------+

+----------+------------+------------+------+

| Puffball | 1999-03-30 | 2016-02-23 | 16 |

| Chirpy | 1998-09-11 | 2016-02-23 | 17 |

| Whistler | 1997-12-09 | 2016-02-23 | 18 |

| Slim | 1996-04-29 | 2016-02-23 | 19 |

| Claws | 1994-03-17 | 2016-02-23 | 21 |

| Fluffy | 1993-02-04 | 2016-02-23 | 23 |

| Fang | 1990-08-27 | 2016-02-23 | 25 |

| Buffy | 1989-05-13 | 2016-02-23 | 26 |

| Bowser | 1989-08-31 | 2016-02-23 | 26 |

+----------+------------+------------+------+

计算所有动物的年龄，并且按年龄排序，不包括没有死亡日期的动物

mysql> SELECT name, birth, death,

-> TIMESTAMPDIFF(YEAR,birth,death) AS age

-> FROM pet WHERE death IS NOT NULL ORDER BY age;

+--------+------------+------------+------+

+--------+------------+------------+------+

| Bowser | 1989-08-31 | 1995-07-29 | 5 |

+--------+------------+------------+------+

查看所有动物的生日是哪个月

YEAR(), MONTH(), and DAYOFMONTH()

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 |

+----------+------------+--------------+

查看所有在5月份过生日的动物的名字和生日

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

+-------+------------+

| name | birth |

+-------+------------+

| Buffy | 1989-05-13 |

+-------+------------+

查看下个月过生日的动物

mysql> SELECT name, birth FROM pet

-> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

+----------+------------+

| name | birth |

+----------+------------+

| Claws | 1994-03-17 |

| Puffball | 1999-03-30 |

+----------+------------+

mysql> SELECT name, birth FROM pet

-> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

+----------+------------+

| name | birth |

+----------+------------+

| Claws | 1994-03-17 |

| Puffball | 1999-03-30 |

+----------+------------+

MONTH( ) 返回1到12之间的一个数字；

MOD(something,12 ) 返回0到11之间的一个数字，

所有我们要加数字应该在MOD( )之后。

23.空值的处理

测试查看的值是否为空

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

+-----------+---------------+

| 1 IS NULL | 1 IS NOT NULL |

+-----------+---------------+

| 0 | 1 |

+-----------+---------------+

你不能用算术运算符操作 NULL , 请看下面的例子：

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > 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.

在MySQL里，0或者NULL就意味着是false , 有时候也是true .默认值 truth是一个boolean类型，值为1.

如果你用 ORDER BY ... ASC 排序，空值会出现在最前面；或 ORDER BY ... DESC 排序，空值会出现在最后面

mysql> SELECT * FROM pet ORDER BY DEATH ASC;

+----------+--------+---------+------+------------+------------+

+----------+--------+---------+------+------------+------------+

| 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 | 1989-08-31 | 1995-07-29 |

+----------+--------+---------+------+------------+------------+

mysql> SELECT * FROM pet ORDER BY DEATH DESC;

+----------+--------+---------+------+------------+------------+

+----------+--------+---------+------+------------+------------+

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

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

+----------+--------+---------+------+------------+------------+

当你向表中保存0或者一个空字符串（''）的时候，实际这个列不是空，你可以用 IS [NOT] NULL 测试：

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;

+-----------+---------------+------------+----------------+

+-----------+---------------+------------+----------------+

| 0 | 1 | 0 | 1 |

+-----------+---------------+------------+----------------+

因此，你可以放入0或一个空字符串到(不能为空 )NOT NULL的列。

24.模式匹配

在MySQL中，SQL的模式匹配默认不区分大小写。你可以用 “_”匹配任意单个字符，

用“%”匹配匹配任意数量的字符（包括不存在的字符）。

你不能用 = 或者 <>匹配，应该用 LIKE 或 NOT LIKE 代替。

查询 name字段中以字母 b开头的动物的信息：

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

+--------+--------+---------+------+------------+------------+

+--------+--------+---------+------+------------+------------+

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

查询 name字段中以字母 fy结尾的动物的信息：

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

+--------+--------+---------+------+------------+-------+

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+--------+--------+---------+------+------------+-------+

查询 name字段中包含字母 w 的动物的信息：

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

+----------+-------+---------+------+------------+------------+

+----------+-------+---------+------+------------+------------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

+----------+-------+---------+------+------------+------------+

查看名字为五个字符的动物的信息，用“_”字符匹配：

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

+-------+--------+---------+------+------------+-------+

+-------+--------+---------+------+------------+-------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

MySQL其他类型的匹配，继承正则表达式：

[ . . . ] 匹配任何在方括号内的字符。比如，[abc]会匹配“a”,"b" 或 "c"。

[a-z] 匹配 a到z中间的任何字母。同样，[0-9]匹配任何数字。

* 匹配零个或更多的字符。例如，“x*”匹配任何数量的“x”字符。

[0-9]* 匹配任意数量的数字。“.*”匹配任意数量的任何字符。

正则表达式不像模式匹配，正则表达式是匹配任意位置，模式匹配是匹配整个值。

如果一个模式，你必须匹配开始和结尾，

你可以用 “^”作为匹配的开头，用“$”作为匹配的结尾。

下面将演示如何使用正则表达式进行模式匹配。

找到以“b”开头的名字，用“^”匹配名字的开头：

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

+--------+--------+---------+------+------------+------------+

+--------+--------+---------+------+------------+------------+

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

如果你要强制正则表达式区分大小写，你可以用 BINARY 关键字匹配一个二进制字符串。

此查询只匹配小写字母"b"开头的名称：

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

Empty set (0.00 sec)

查找以 “fy”结尾的名字，用“$”匹配名字的结尾：

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

+--------+--------+---------+------+------------+-------+

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+--------+--------+---------+------+------------+-------+

查找包含一个“w”的名字

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

+----------+-------+---------+------+------------+------------+

+----------+-------+---------+------+------------+------------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

+----------+-------+---------+------+------------+------------+

查找有五个字符组成的名字，用“^”和 “$”匹配开始和结尾，用“.”匹配五个字符：

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';

+-------+--------+---------+------+------------+-------+

+-------+--------+---------+------+------------+-------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

你也可以写上一个查询中使用{n}("重复n次")运算符：

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

+-------+--------+---------+------+------------+-------+

+-------+--------+---------+------+------------+-------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

25. 3.3.4.8 计算行

数据库会经常回答一个问题，“表中的数据多久会发生变化？”、

例如，你可能想知道你有多少只宠物，或者你想知道每个业主有多少只宠物，

或者你想对你的动物进行各类普查。

计算你总共有多少只动物，同样的问题：pet表中总共有多少行？

因为每个动物有一条记录。用 COUNG(*)可以计算行数，

所以你可以这样查询并计算你的动物：

mysql> SELECT COUNT(*) FROM pet;

+----------+

| COUNT(*) |

+----------+

| 9 |

+----------+

你可以用COUNT()查看每个业主有几只宠物：

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

+--------+----------+

| owner | COUNT(*) |

+--------+----------+

| Benny | 2 |

| Diane | 2 |

| Gwen | 3 |

| Harold | 2 |

+--------+----------+

上面的查询语句用了 GROUP BY 为每位业主分组，

COUNT( ) 和 GROUP BY 一起使用，可以很好的描述你每个组里面的数据。

下面的示例是用另一种方式进行宠物普查。

查看每种动物的数量：

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

+---------+----------+

| species | COUNT(*) |

+---------+----------+

| bird | 2 |

| cat | 2 |

| dog | 3 |

| hamster | 1 |

| snake | 1 |

+---------+----------+

查看每种性别的动物的数量：

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

+------+----------+

| sex | COUNT(*) |

+------+----------+

| NULL | 1 |

| f | 4 |

| m | 4 |

+------+----------+

（在此输出中，NULL表明，性别是未知的。）

每种组合的动物数量和性别

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 |

+---------+------+----------+

当你使用COUNT( )时，你不用检索整个表。例如，前面的查询，

在执行时，只对狗和猫，看起来像这样：

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 |

+---------+------+----------+

或者，你想知道每个已知性别的动物的数量：

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 |

+---------+------+----------+

如果你选择了COUNT()列，GROUP BY 子句应存在相同名称的列。否则，将发生一下错误：

如果 ONLY_FULL_GROUP_BY 启用SQL模式，会发生一个错误:

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';

Query OK, 0 rows affected, 1 warning (0.22 sec)

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

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'menagerie.pet.owner'; this is incompatible with sql_mode=only_full_group_by

如果 ONLY_FULL_GROUP_BY 没有启用，查询会作为一个单独的组出来，

但为每个指定的列选定的值是不确定的。MYSQL服务可以自由选择任何行中的值：

mysql> SET sql_mode = '';

Query OK, 0 rows affected (0.00 sec)

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

+--------+----------+

| owner | COUNT(*) |

+--------+----------+

| Harold | 9 |

+--------+----------+

26. 3.3.4.9 使用多个表

pet表会对你所拥有的动物做记录。如果你想记录有关他们的其他信息，

像他们的生活中看病和出生，你需要另外一张表。这个表应该是什么样子？

它需要包含以下信息：

宠物名称：方便你知道那个动物出了什么情况。

日期（繁殖日期）：方便你知道事情发生的日期。

描述：记录所发生的事情。

事件类型：如果你要对发生的事情进行分类。

经过上述分析，创建的event table(事件表)可能看起来像这样：

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,

-> type VARCHAR(15), remark VARCHAR(255));

Query OK, 0 rows affected (1.31 sec)

像pet表一样，通过创建制表符分割的文本文件，是最容易加载初始信息的方式，文本文件包含以下信息。

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

像下面这样加载记录：

mysql> LOAD DATA LOCAL INFILE 'd:/event.txt' INTO TABLE event;

假设您想查看每个宠物繁殖时的年龄。我们已经看到了两个日期，但是怎样从两个日期中计算某只宠物繁殖时的年龄。

母亲的生育日期在event表中，但是要计算他的生育时的年龄，你需要她的生日。她的生日存在pet表中。

这意味着需要查询两个表：

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 |

+--------+------+-----------------------------+

关于这个查询有几件事情需要注意：

From 链接了两个表，因为查询需要从两个表中查询信息。

当结合（加入）多个表的信息时，您需要指定如何将一个表中的记录与其他中的记录相匹配。

因为他们都有一个名称列，这是很容易的。该查询基于名称的值，使用ON子句匹配两个表中的记录。

该查询使用INNER JOIN （内部联接）把两个表相结合。INNER JOIN 内部链接是把符合ON条件的两张表中的信息，

显示在一张新表中。在这个例子中，ON子句指定名称的宠物表中的列必须事件表中的列名称相匹配，

如果一个名称在两个表中不是共有的，这个名称将不显示，因为ON子句中的条件失败。

因为 name 列是在两个表中，你必须明确指出你引用的是哪个表中的哪个列，所有你应该为引用的每个列添加表名。

你不需要有两个不同的表执行一次联结。如果你要比较同一个表中的两条记录，有时候联结自己是更实用的。

例如，在你的宠物中找到繁殖的配偶，你可以用pet表联结他自己，找到雌性和雄性。

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

+--------+------+--------+------+---------+

+--------+------+--------+------+---------+

| Fluffy | f | Claws | m | cat |

| Buffy | f | Fang | m | dog |

| Buffy | f | Bowser | m | dog |

+--------+------+--------+------+---------+

在这个查询中，我们指定表的别名名称引用该表的列和值的实例与每个列引用关联。

27. 3.4 获取有关数据库和表的信息

如果你忘记数据库或表的名字，或者你忘记了表结构（例如，这个列是存放什么的？）

mysql通过多条语句解决了提供数据库和表信息的问题。

你以前见到 SHOW DATABASES 命令, 列出由服务器管理的数据库.

用DATABASE() 命令，你可以找出当前选定的数据库

mysql> SELECT DATABASE();

+------------+

| DATABASE() |

+------------+

| menagerie |

+------------+

如果你还没有选中任何的数据库，这个命令的返回结果是 NULL。

如果你不确定这个数据库中包含哪些表，你可以用这个语句来查看：

mysql> SHOW TABLES;

+---------------------+

| Tables_in_menagerie |

+---------------------+

| event |

| pet |

+---------------------+

你可以用 DESCRIBE查看表的结构，它描述了每个列的信息。

mysql> DESCRIBE pet;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| sex | char(1) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

Field 表示列的名称，Type表示列的数据类型，

NULL值指示该列可以包含NULL值，Key指示该列是索引

Default 指出列的默认值，如果列使用了 AUTO_INCREMENT选项，Extra显示列的额外信息，

这个列的值将会自动增长，而不是空的。

DESC 是 DESCRIBE的缩写。

mysql> CREATE TABLE pet2(name VARCHAR(20));

Query OK, 0 rows affected (0.36 sec)

mysql> DESCRIBE pet2;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

mysql> DESC pet2;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

用SHOW CREATE TABLE 语句，可以显示你创建表的语句

mysql> SHOW CREATE TABLE pet2;

+-------+-----------------------------------------------------------------------

-----------------------+

| Table | Create Table

+-------+-----------------------------------------------------------------------

-----------------------+

| pet2 | CREATE TABLE `pet2` (

`name` varchar(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+-----------------------------------------------------------------------

-----------------------+

28. 3.5 以批处理模式使用mysql

在前面的部分中，您使用mysql交互输入语句并查看结果。

您还可以使用批处理模式运行mysql。

例如，在d:/mysql_bat/新建login.bat文件，内容如下：

mysql -h localhost -u cunli -pcunli < d:/mysql_bat/mysql.sql > d:/mysql_bat/bat_out.out

然后在d:/mysql_bat/新建mysql.sql ,内容如下：

#show databases;

use menagerie;

#------------tables----------------

show tables;

SELECT * FROM pet;

双击login.bat,即可在d:/mysql_bat/看到bat_out.out文件，内容是mysql.sql里面执行的内容

29. 3.6 常见的查询的例子

启动命令行，并选中一个数据库：

你可以使用这些语句创建并填充表：

mysql> use menagerie

Database changed

CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

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.28 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.15 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 |

+---------+--------+-------+

30. 3.6.1 查看列的最大值

该项目的最大编号是多少？

mysql> SELECT MAX(article) AS article FROM shop;

+---------+

| article |

+---------+

| 4 |

+---------+

31. 3.6.2 查看该项目的最大编号的行的信息

mysql> SELECT article, dealer, price

-> FROM shop

-> WHERE price=(SELECT MAX(price) FROM shop);

+---------+--------+-------+

| article | dealer | price |

+---------+--------+-------+

| 0004 | D | 19.95 |

+---------+--------+-------+

你也可以用LIFT JOIN 排序所有的行的价格，然后获取第一行。或者用LIMT关键字：

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;

+---------+--------+-------+

| article | dealer | price |

+---------+--------+-------+

| 0004 | D | 19.95 |

+---------+--------+-------+

mysql> SELECT article, dealer, price

-> FROM shop

-> ORDER BY price DESC

-> LIMIT 1;

+---------+--------+-------+

| article | dealer | price |

+---------+--------+-------+

| 0004 | D | 19.95 |

+---------+--------+-------+

32. 3.6.3 查看每组中最大的列的值

查看每种物品的最高价格

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 |

+---------+-------+

33. 3.6.4 查看每种商品的经销商醉的最高价格

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 |

+---------+--------+-------+

无关联的子查询：

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;

LEFT JOIN:

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;

34. 3.6.5 使用用户定义的变量

你可以使用MySQL用户变量记住结果，而不必将它们存储在客户端中的临时变量

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

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 |

+---------+--------+-------+

35. 3.6.6 使用外键

你可以使用一个列创建一个联接列，如下所示

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

使用这种方式时，REFERENCES 子句不会在 SHOW CREATE TABLE 或者 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

36. 3.6.8 计算每天访问

下面的示例说明如何使用位元组函数来计算每月用户访问Web页面的数量

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);

示例表包含年-月-日值代表用户对页面的访问。确定这些访问发生在每个月多少不同的日子，使用以下查询：

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

查询计算/月，每年组合的表中显示多少不同的日子，自动删除重复的条目。

37. 3.6.9 使用AUTO_INCREMENT

AUTO_INCREMENT属性可用于为新行生成一个唯一标识：

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

没有为AUTO_INCREMENT列指定值，所以使用自动分配的序列号。

您也可以显式指定列生成序列号0。如果该列被声明为NOT NULL，也可以指定NULL的列生成序号。当你向一个AUTO_INCREMENT列中插入任何其他值，则列设置为该值和序列重置，以便下自动生成的值如下顺序从最大的列值。

您可以检索最新的自动生成AUTO_INCREMENT值与LAST_INSERT_ID()SQL函数或mysql_insert_id()C API函数。这些功能是连接特定的，所以它们的返回值不受也是执行插入的另一个连接。

使用了AUTO_INCREMENT列，它足够大，可以容纳的最大序列值，您需要的最小整数数据类型。当达到最大值的数据类型的列，接下来生成序列号的尝试失败。如果可能，使用无符号属性以允许更大的范围。例如，如果您使用TINYINT，最大允许序列号码是127。为TINYINT签名，最大值为255。

开始一个AUTO_INCREMENT 1以外的值，设置该值与CREATE TABLE或ALTER TABLE，像这样：

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

引擎优化

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+