mysql教程
mysql教程
2016年5月14日
0:09
1.查看mysql帮助信息
C:\Users\zhangcunli>mysql --help
mysql Ver 14.14 Distrib 5.7.9, for Win64 (x86_64)
Copyright (c) 2000, 2015, 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.
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)
Copyright (c) 2000, 2015, 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>
方法二：
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)
Copyright (c) 2000, 2015, 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>
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)
Copyright (c) 2000, 2015, 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>
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;
+---------+-------------+------+-----+---------+-------+
| 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 | |
+---------+-------------+------+-----+---------+-------+
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;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+--------+---------+------+------------+------------+
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;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+--------+---------+------+------------+------------+
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';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| 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';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
查询种类是dog并且性别为f的动物的信息
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
查询种类是snake或者种类是bird的动物的信息
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+-------+---------+------+------------+-------+
查询种类是cat并且性别是m或者种类是dog并且性别是f的动物的信息
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 |
+-------+--------+---------+------+------------+-------+
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 |
| 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;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| 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;
+----------+------------+------------+------+
| name | birth | CURDATE() | 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;
+--------+------------+------------+------+
| name | birth | death | 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;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | 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.
在MySQL里，0或者NULL就意味着是false , 有时候也是true .默认值 truth是一个boolean类型，值为1.
如果你用 ORDER BY ... ASC 排序，空值会出现在最前面；或 ORDER BY ... DESC 排序，空值会出现在最后面
mysql> SELECT * FROM pet ORDER BY DEATH ASC;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+----------+--------+---------+------+------------+------------+
mysql> SELECT * FROM pet ORDER BY DEATH DESC;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| 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 |
| 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 |
+----------+--------+---------+------+------------+------------+
当你向表中保存0或者一个空字符串（''）的时候，实际这个列不是空，你可以用 IS [NOT] NULL 测试：
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 |
+-----------+---------------+------------+----------------+
因此，你可以放入0或一个空字符串到(不能为空 )NOT NULL的列。
24.模式匹配
在MySQL中，SQL的模式匹配默认不区分大小写。你可以用 “_”匹配任意单个字符，
用“%”匹配匹配任意数量的字符（包括不存在的字符）。
你不能用 = 或者 <>匹配，应该用 LIKE 或 NOT LIKE 代替。
查询 name字段中以字母 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 |
+--------+--------+---------+------+------------+------------+
查询 name字段中以字母 fy结尾的 动物的信息：
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
查询 name字段中包含字母 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 | | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
查看名字为五个字符的动物的信息，用“_”字符匹配：
mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
MySQL其他类型的匹配，继承正则表达式：
[ . . . ] 匹配任何在方括号内的字符。比如，[abc]会匹配“a”,"b" 或 "c"。
[a-z] 匹配 a到z中间的任何字母。同样，[0-9]匹配任何数字。
* 匹配零个或更多的字符。例如，“x*”匹配任何数量的“x”字符。
[0-9]* 匹配任意数量的数字。“.*”匹配任意数量的任何字符。
正则表达式不像模式匹配，正则表达式是匹配任意位置，模式匹配是匹配整个值。
如果一个模式，你必须匹配开始和结尾，
你可以用 “^”作为匹配的开头，用“$”作为匹配的结尾。
下面将演示如何使用正则表达式进行模式匹配。
找到以“b”开头的名字，用“^”匹配名字的开头：
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
如果你要强制正则表达式区分大小写，你可以用 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$';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| 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';
+----------+-------+---------+------+------------+------------+
| 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 | | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
查找有五个字符组成的名字，用“^”和 “$”匹配开始和结尾，用“.”匹配五个字符：
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 |
+-------+--------+---------+------+------------+-------+
你也可以写上一个查询中使用{n}("重复n次")运算符：
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 |
+-------+--------+---------+------+------------+-------+
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';
+--------+------+--------+------+---------+
| name | sex | name | sex | species |
+--------+------+--------+------+---------+
| 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;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
Field 表示列的名称，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;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> DESC pet2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
用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 |
+--------+----+---------+
