一、完整性约束
在创建表时候，约束条件和数据类型的宽度都是可选参数。
作用：用于保证数据的完整性和一致性。
1、not null（不可空）与default
示例一：插入一个空值，如下：
mysql> create table tb1(id int); mysql> desc tb1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> insert into tb1 values(); # 不写值即插入空值 mysql> select * from tb1; +------+ | id | +------+ | NULL | +------+ 1 row in set (0.00 sec)
示例二：设置not null，则插入值时不能为空，如下：
mysql> create table tb2(id int not null); # 设置id字段不为空 mysql> desc tb2; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | # Null列变为NO +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> insert into tb2 values(); # 报错，不能插入空
示例三：设置某字段有默认值后，则无论该字段是否设置not null，都可以插入空，插入空时填入default指定的默认值，如下：
情况一：id字段可以为空，设置默认值
mysql> create table tb3(id int default 1); mysql> desc tb3; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | 1 | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> insert into tb3 values(); mysql> select * from tb3; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec)
情况二：id字段不能为空，设置默认值
mysql> create table tb4(id int not null default 2); mysql> desc tb4; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | 2 | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> insert into tb4 values (); mysql> select * from tb4; +----+ | id | +----+ | 2 | +----+ 1 row in set (0.00 sec)
2、unique（唯一的）
示例一：单列唯一
方式一：unique关键字跟在类型后面
mysql> create table department( -> id int unique, -> name char(10) unique -> ); mysql> insert into department values(1,’IT部’); mysql> insert into department values(1,’销售部’); # 报错，不能插入 mysql> insert into department values(2,’IT部’); # 报错，不能插入
方式二：单独使用unique关键字定义
mysql> create table department( -> id int, -> name char(10)， -> unique(id), -> unique(name) -> ); mysql> insert into department values(1,’IT部’); mysql> insert into department values(1,’销售部’); # 报错，不能插入 mysql> insert into department values(2,’IT部’); # 报错，不能插入
示例二：联合唯一
mysql> create table services( -> id int, -> ip char(15), -> port int, -> unique(id), # 单列唯一 -> unique(ip,port) # 联合唯一 -> ); mysql> desc services; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | ip | char(15) | YES | MUL | NULL | | | port | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) # 联合唯一：只要有一个字段的数据不一样，就可以插入，如下 mysql> insert into services values -> (1,'192,168,11,23',80), -> (2,'192,168,11,23',81), -> (3,'192,168,11,25',80); mysql> select * from services; +------+---------------+------+ | id | ip | port | +------+---------------+------+ | 1 | 192,168,11,23 | 80 | | 2 | 192,168,11,23 | 81 | | 3 | 192,168,11,25 | 80 | +------+---------------+------+
3、primary key（主键）
一个表中可以单列做主键，也可以多列做主键（复合主键）。
对于innodb存储引擎来说，一张表只能有一个主键。
主键的作用等价于not null unique，即字段的值不为空且唯一。
示例：not null + unique的化学反应，相当于设置primary key
mysql> create table tb5( -> id int primary key, -> name char(16) -> ); mysql> desc tb5; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> create table tb6( -> id int not null unique, -> name char(16) -> ); mysql> desc tb6; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
4、auto_increment（自动增长）
作用：约束的字段为自动增长，约束的字段必须同时被primary key约束。
示例一：不指定id，则id自动增长
mysql> create table student( -> id int primary key auto_increment, -> name varchar(20), -> gender enum(‘male’,’female’) default ‘male’ -> ); mysql> insert into student(name) values (‘老白’),(‘小白’); mysql> select * from student; +----+--------+--------+ | id | name | gender | +----+--------+--------+ | 1 | 老白 | male | | 2 | 小白 | male | +----+--------+--------+ 2 rows in set (0.00 sec)
示例二：自己指定id
mysql> insert into student values(4,'asb','female'); mysql> insert into student values(7,'wsb','female'); mysql> select * from student; +----+--------+----------+ | id | name | gender | +----+--------+----------+ | 1 | 老白 | male | | 2 | 小白 | male | | 4 | asb | female | | 7 | wsb | female | +----+--------+----------+ 4 rows in set (0.00 sec) # 再次插入一条不指定id的记录，会在之前的最后一条记录继续增长 mysql> insert into student(name) values ('大白'); mysql> select * from student; +----+--------+----------+ | id | name | gender | +----+--------+----------+ | 1 | 老白 | male | | 2 | 小白 | male | | 4 | asb | female | | 7 | wsb | female | | 8 | 大白 | male | +----+--------+----------+ 5 rows in set (0.00 sec) 示例三：对于自增的字段，在用delete删除后，再插入值，则该字段仍按照删除前的位置继续增长
mysql> delete from student; # 删除student表中所有记录 mysql> select * from student; Empty set (0.00 sec) mysql> insert into student(name) values('ysb'); mysql> select * from student; +----+------+--------+ | id | name | gender | +----+------+--------+ | 9 | ysb | male | +----+------+--------+ 1 row in set (0.00 sec) # truncate清空表 mysql> truncate student; Query OK, 0 rows affected (0.03 sec) mysql> insert into student(name) values('xiaobai'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+---------+--------+ | id | name | gender | +----+---------+--------+ | 1 | xiaobai | male | # truncate清空表后，再插入值则id从1开始 +----+---------+--------+ 1 row in set (0.00 sec) 注意：delete和truncate的区别
delete from t1; # 如果有自增id，删除后新增数据时，仍然是以删除前的最后一项作为起始； truncate table t1; # 数据量大时用，删除速度比上一条快，且再次插入时直接从零开始；
了解：auto_increment_increment（步长，默认1）和auto_increment_offset（偏移量，默认1）
# 查看当前生效的以auto_inc开头的设置 mysql> show variables like 'auto_inc%'; # 会话设置，只在本次连接中有效 set session auto_increment_increment=5; # 设置步长为5 # 全局设置，一直有效，设置后需要重启才能生效 set global auto_increment_increment=5; # 步长为5 set global auto_increment_offset=3; # 偏移量（起始值）为3
PS：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略。
5、foreign key
快速理解foreign key
之前创建表的时候都是在一张表中添加记录，比如下表：
| id | name | age | department | dep_describe |
| 1 | wusir | 18 | IT | IT技术有限部门 |
| 2 | xiaoma | 19 | IT | IT技术有限部门 |
| 3 | egon | 20 | 销售部 | 销售不出去部门 |
| 4 | yuanhao | 30 | 财务部 | 花钱太多部门 |
| 5 | alex | 38 | 销售部 | 销售不出去部门 |
假如公司有上表中这三个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门描述越长，越浪费空间。
解决方法：
我们完全可以定义一个部门表，然后让员工信息表关联该部门表，如何关联？用foreign key。
上表结构改为：
| id | name | age | dep_id |
| 1 | wusir | 18 | 1 |
| 2 | xiaoma | 19 | 1 |
| 3 | egon | 20 | 2 |
| 4 | yuanhao | 30 | 3 |
| 5 | alex | 38 | 2 |
| id | dep_name | dep_describe |
| 1 | IT | IT技术有限部门 |
| 2 | 销售部 | 销售不出去部门 |
| 3 | 财务部 | 花钱太多部门 |
上面两张表中，左边是员工表（emp表），即关联表，也叫从表；右边是部门表（dep表），即被关联表，也叫主表。
代码示例：
# 创建表时，先创建被关联表，再创建关联表 create table dep( id int primary key, dep_name varchar(20) not null, dep_describe varchar(20) not null ); create table emp( id int primary key, name varchar(20) not null, age int not null, dep_id int, constraint fk_dep foreign key(dep_id) references dep(id) ); # 查看emp表的表结构有什么特点 mysql> desc emp; +--------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | age | int(11) | NO | | NULL | | | dep_id | int(11) | YES | MUL | NULL | | # KEY列为MUL +--------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec) # 插入记录时，先往被关联表（主表）中插入记录，再往关联表（从表）中插入记录 insert into dep values (1,’IT’,’IT技术有限部门’), (2,’销售部’,’销售不出去部门’), (3,’财务部’,’花钱太多部门’); insert into emp values (1,’wusir’,18,1), (2,’xiaoma’,19,1), (3,’egon’,20,2), (4,’yuanhao’,30,3), (5,’alex’,38,2);
注意：按以上步骤创建表的话，删除dep表中的部门时，必须把emp表中该部门对应的员工记录删除后才能删除部门，否则报错。
上面的删除表记录的操作比较繁琐，按道理讲，裁掉一个部门，该部门的员工也应该被裁掉，其实，在建表的时候加入同步删除，同步更新的操作，就可以实现这样的效果，如下代码：
创建emp表的操作：
create table emp( id int primary key, name varchar(20) not null, age int not null, dep_id int, constraint fk_dep foreign key(dep_id) references dep(id) on delete cascade # 同步删除 on update cascade # 同步更新 );
接下来的操作就符合我们生活中的情况了，即删除（更改）部门时，该部门的员工也跟着删除（更改）。
二、外键的变种 – 表的三种关系
因为有foreign key的约束，使得两张表形成了三种关系：多对一（或一对多）、多对多、一对一。
1、重点理解如何找出两张表之间的关系
步骤1：先站在左表的角度，分析是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key右表的一个字段（通常是id）；
步骤2：再站在右表的角度，分析是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key左表的一个字段（通常是id）；
结论：
1）多对一（或者一对多）
如果只有步骤1成立，则是左表多对一右表；
如果只有步骤2成立，则是左表一对多右表；
2）多对多
如果步骤1和2同时成立，则证明这两张表是多对多，这时需要定义一个表来专门存放二者的关系；
3）一对一
如果步骤1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然，这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可；
2、多对一（或一对多）关系
关联方式：foreign key
书和出版社：一个出版社可以出版多本书，一本书只能由一个出版社出版，如图：
| id | name | press_id |
| id | name |
| 1 | 九阳神功 | 1 |
| 1 | 北京工业地雷出版社 |
| 2 | 九阴真经 | 2 |
| 2 | 人民音乐不好听出版社 |
| 3 | 九阴白骨爪 | 2 |
| 3 | 中国人民真伟大出版社 |
| 4 | 孤独九剑 | 3 |
|
| |
| 5 | 降龙十八掌 | 2 |
| ||
| 6 | 葵花宝典 | 3 |
| ||
3、多对多关系
关联方式：foreign key + 一张新的表
作者和书籍：一个作者可以写多本书，一本书也可以有多个作者，即多对多的关系，如图：
| id | name |
| id | name |
| 1 | egon |
| 1 | python全栈开发 |
| 2 | alex |
| 2 | linux高级运维 |
| 3 | wusir |
| 3 | 爬虫技术 |
| 4 | yuanhao |
| 4 | web前端 |
| id | author_id | book_id |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 1 |
| 5 | 3 | 4 |
| 6 | 4 | 4 |
4、一对一关系
关联方式：foreign key + unique
用户和博客：一个用户只能注册一个博客，即一对一的关系，如图：
| id | name |
| id | url | user_id |
| 1 | alex |
| 1 | http://www.cnblog/alex | 1 |
| 2 | wusir |
| 2 | http://www.cnblog/wusir | 2 |
| 3 | egon |
| 3 | http://www.cnblog/egon | 3 |
| 4 | xiaoma |
| 4 | http://www.cnblog/xiaoma | 4 |
三、单表查询
单表查询的语法：
SELECT 字段1，字段2… FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数
关键字的执行优先级（重点）：
from（找到表） where（拿着where指定的约束条件，去文件/表中取出一条条记录） group by（将取出的一条条记录进行分组，如果没有group by，则整体作为一组） having（将分组的结果进行having过滤） select（执行select） distinct（去重） order by（将结果按条件排序） limit（限制结果的显示条数）
先创建一个公司员工表，本文都是对此表的查询，代码如下：
# 创建employee表create table employee( id int primary key auto_increment, # 员工id name varchar(20) not null, # 姓名 gender enum('male','female') not null default 'male', # 性别，大部分是男的 age int(3) unsigned not null default 28, # 年龄 hire_date date not null, # 入职日期 post varchar(50), # 岗位 post_comment varchar(100), # 岗位描述 salary double(15,2), # 工资 office int, # 办公室，一个部门一个办公室 depart_id int # 部门编号);# 插入记录，有四个部门：公关部，教学部，销售部，运营部insert into employee(name,gender,age,hire_date,post,salary,office,depart_id) values('egon','male',18,'20170301','公关部',7300.33,400,0), # 公关部('alex','male',78,'20150302','teacher',1000000.31,401,1), #以下是教学部('wupeiqi','male',81,'20130305','teacher',8300,401,1),('yuanhao','male',73,'20140701','teacher',3500,401,1),('liwenzhou','male',28,'20121101','teacher',2100,401,1),('jingliyang','female',18,'20110211','teacher',9000,401,1),('jinxin','male',18,'19000301','teacher',30000,401,1),('xiaomage','male',48,'20101111','teacher',10000,401,1),('歪歪','female',48,'20150311','sale',3000.13,402,2), # 以下是销售部('丫丫','female',38,'20101101','sale',2000.35,402,2),('丁丁','female',18,'20110312','sale',1000.37,402,2),('星星','female',18,'20160513','sale',3000.29,402,2),('格格','female',28,'20170127','sale',4000.33,402,2),('张野','male',28,'20160311','operation',10000.13,403,3), # 以下是运营部('程咬金','male',18,'19970312','operation',20000,403,3),('程咬银','female',18,'20130311','operation',19000,403,3),('程咬铜','male',18,'20150411','operation',18000,403,3),('程咬铁','female',18,'20140512','operation',17000,403,3); 1、WHERE约束
where子句中可以使用：
1）比较运算符：>、<、>=、<=、<>、!=；
2）between 80 and 100：值在80到100之间；
3）in(10,20,30)：值是10或20或30；
4）like '字符pattern'：pattern可以是%或者_（%表示任意多个字符，_表示一个字符）；
5）逻辑运算符（and or not）：有多个条件时可以直接使用逻辑运算符；
示例一：单条件查询
mysql> select id,name from employee where id > 5;
示例二：多条件查询
mysql> select name from employee where post='teacher' and salary > 10000;
示例三：关键字BETWEEN AND
mysql> select name,salary from employee where salary between 10000 and 20000; # 工资在10000到20000之间的员工 mysql> select name,salary from employee where salary not between 10000 and 20000; # 工资不在10000到20000之间
示例四：注意''是空字符，不是null
mysql> select name,post_comment from employee where post_comment=''; # 执行完这条语句再用上条查看，就会有结果了 mysql> update employee set post_comment='' where id = 2;
示例五：逻辑运算符or和关键字IN/NOT IN集合查询
mysql> select name,salary from employee where salary=3000 or salary=3500 or salary=4000 or salary=9000; mysql> select name,salary from employee where salary in(3000,3500,4000,9000); mysql> select name,salary from employee where salary not in(3000,3500,4000,9000);
示例六：like模糊查询
mysql> select * from employee where name like 'jin%'; mysql> select age from employee where name like 'ale_';
2、GROUP BY分组查询
1）首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的；
2）分组指的是：将所有记录按照某个相同字段进行归类，比如对员工职位分组，或按照性别分组等；
3）为什么要分组？因为往往有如下需求：
取每个部门的最高工资；
取每个部门的员工数；
取男人数和女人数；
PS：小窍门：'每'这个字后面的字段，就是我们分组的依据。
4）大前提：可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数；
# 若想要分组，必须设置全局sql_mode模式为ONLY_FULL_GROUP_BY，且设置完一定要重新登录 mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; mysql> select @@global.sql_mode; # 查看全局sql_mode模式
示例一：验证通过group by分组之后，只能查看分组字段
mysql> select * from employee group by post; # 报错 mysql> select post from employee group by post; # 正确
示例二：分组后如果想查看组内信息，需要借助聚合函数（聚合函数聚合的是组的内容，若没有分组，则默认是一组），聚合函数有以下几种：
max()求最大值
min()求最小值
avg()求平均值
sum()求和
count()求总个数
# 查看每个部门有多少个员工 mysql> select post,count(id) from employee group by post; # 查看每个部门的最高工资 mysql> select post,max(salary) from employee group by post; # 查看每个部门的最低工资 mysql> select post,min(salary) from employee group by post; # 查看每个部门的平均工资 mysql> select post,avg(salary) from employee group by post; # 查看每个部门的所有工资 mysql> select post,sum(salary) from employee group by post;
3、HAVING过滤
HAVING与WHERE的区别在于：
1）执行优先级从高到低是：where > group by > having；
2）where发生在分组group by之前，因而where中可以有任意字段，但绝不能有聚合函数；
3）having发生在分组group by之后，因而having中可以使用分组的字段，无法直接取到其他字段，且可以使用聚合函数；
示例一：没有group by分组时，where和having效果一样
mysql> select * from employee where salary > 1000000; mysql> select * from employee having salary > 1000000;
示例二：group_concat()函数（必须使用group by，才能使用此函数）
mysql> select post,group_concat(name) from employee group by post;
4、ORDER BY查询排序
按单列排序：
SELECT * from employee ORDER BY age; # 默认升序
SELECT * from employee ORDER BY age ASC; # 按年纪升序
SELECT * from employee ORDER BY age DESC; # 按年纪降序
按多列排序（比如，先按照age升序排序，如果年纪相同，则按照id降序）：
SELECT * from employee ORDER BY age ASC,id DESC;
示例一：验证多列排序
mysql> select * from employee order by age asc,id desc;
5、LIMIT限制查询的记录数
示例一：默认初始位置为0
mysql> SELECT * from employee ORDER BY salary DESC LIMIT 3; # 相当于LIMIT 0,3
示例二：起始位置为0，步长为5，即先查询出第1条，然后包含这一条在内往后查5条
mysql> SELECT * from employee ORDER BY salary DESC LIMIT 0,5;
示例三：起始位置为5，步长为5，即先查询出第6条，然后包含这一条在内往后查5条
mysql> SELECT * from employee ORDER BY salary DESC LIMIT 5,5;