一、完整性约束
在创建表时候，约束条件和数据类型的宽度都是可选参数。
作用：用于保证数据的完整性和一致性。
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,'','公关部',7300.33,400,0), # 公关部 ('alex','male',78,'','teacher',1000000.31,401,1), #以下是教学部
('wupeiqi','male',81,'','teacher',8300,401,1),
('yuanhao','male',73,'','teacher',3500,401,1),
('liwenzhou','male',28,'','teacher',2100,401,1),
('jingliyang','female',18,'','teacher',9000,401,1),
('jinxin','male',18,'','teacher',30000,401,1),
('xiaomage','male',48,'','teacher',10000,401,1), ('歪歪','female',48,'','sale',3000.13,402,2), # 以下是销售部
('丫丫','female',38,'','sale',2000.35,402,2),
('丁丁','female',18,'','sale',1000.37,402,2),
('星星','female',18,'','sale',3000.29,402,2),
('格格','female',28,'','sale',4000.33,402,2), ('张野','male',28,'','operation',10000.13,403,3), # 以下是运营部
('程咬金','male',18,'','operation',20000,403,3),
('程咬银','female',18,'','operation',19000,403,3),
('程咬铜','male',18,'','operation',18000,403,3),
('程咬铁','female',18,'','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;
