MySQL高级篇 | 蒲公英

目标:

掌握约束的使用

掌握表关系及建表原则

# 约束

在开始学习约束前，我们先看下这个表格：

上面表中可以看到表中数据存在一些问题：

id 列一般是用于标识数据的唯一性，而上述表中的 id 为1的有三条数据，并且 马花疼 没有 id 进行标识；
柳白的 age 年龄列的数据是 3000，这显然不太符合现实规律；
马运的 math 数学成绩是 -5，数学交白卷也就是零分；
柳青的 english英语成绩为 null，即使缺考也就是零分。

针对上述数据问题，如果通过Excel编辑数据，我们可以设置数据有效性检查：

那么数据库层面是不是可以在添加数据的时候进行限制？有，就是我们接下来要学习的约束。

# 概念

约束是作用于表中列上的规则，用于限制加入表的数据。例如：可以给 id 加约束，让其值不能重复，不能为 null 值
约束的存在保证了数据库中数据的正确性、有效性和完整性。约束可以在添加数据的时候就限制不正确的数据添加成功。

# 分类

如图，MySQL约束大致分为非空约束、唯一约束、主键约束、默认约束、外键约束、检查约束。

下面我们就来逐个介绍一下它们：

非空约束：关键字是 NOT NULL，用来保证列中所有的数据不能有 null 值。
唯一约束：关键字是 UNIQUE，保证列中所有数据各不相同。
主键约束：关键字是 PRIMARY KEY，主键是一行数据的唯一标识，要求非空且唯一。一般我们都会给每张表添加一个主键列用来唯一标识数据。
默认约束：关键字是 DEFAULT，保存数据时，如果未指定值则采用默认值。
外键约束：关键字是 FOREIGN KEY，外键用来让两个表的数据之间建立联系，保证数据的一致性和完整性。外键约束当前不太好理解，后面我们讲解表关系时会重点介绍。
检查约束：因MySQL 5.7.24不支持，这里就不在详细介绍了。

不过我们要针对 检查约束 做下额外说明：

检查约束说明

检查约束主要用来保证列中的数据满足一定的条件。比如：给 age 添加一个范围 0 - 150，那我们在添加数据时最小年龄可以设置为0，最大年龄可以设置为150，但是超过这个范围就无法添加成功。

但是，MySQL 5.7.24 版并不支持检查约束，或者说检查约束是受限制的。

在 8.0.16 版本之前，MySQL使用此约束的受限版本。在之前的版本中，可以创建检查约束，但它不起作用，也就是 create table 语句可以包含 check 约束，但它们会被MySQL解析和忽略。

那这样我们是不是就没办法保证年龄在指定范围之内了呢？还是有其他方法的，虽然在数据库层面没办法解决，但我们可以在Java代码中进行限制，一样可以实现检查约束的效果。

# 非空约束

概念：非空约束用于保证列中所有数据不能有 null 值

语法：

添加约束

-- 创建表时添加非空约束
create table 表名 (
  列名 数据类型 not null,
  ...
);

1
2
3
4
5

-- 建完表后添加非空约束
alter table 表名 modify 字段名 数据类型 not null;

1
2

删除约束

alter table 表名 modify 字段名 数据类型;

# 唯一约束

概念：唯一约束用于保证列中所有数据各不相同，不会出现重复的数据。

语法：

添加约束

-- 创建表时添加唯一约束
create table 表名 (
  列名 数据类型 unique,
  ...
);
create table 表名 (
  列名 数据类型,
  ...
  [constraint] [约束名称] unique(列名)
)

1
2
3
4
5
6
7
8
9
10

-- 建完表后添加唯一约束
alter table 表名 modify 字段名 数据类型 unique;

1
2

删除约束

alter table 表名 drop index 约束名称;

# 主键约束

概念：主键是一行数据的唯一标识，要求非空且唯一，一张表只能有一个主键。

语法：

添加约束

-- 创建表时添加主键约束
create table 表名 (
  列名 数据类型 primary key [auto_increment], -- auto_increment: 当不指定值时自动增长
  ...
);
create table 表名 (
  列名 数据类型,
  [constraint] [约束名称] primary key(列名)
);

1
2
3
4
5
6
7
8
9

-- 建完表后添加主键约束
alter table 表名 add primary key(列名);

1
2

删除约束

alter table 表名 drop primary key;

# 默认约束

概念：保存数据时，未指定值时采用默认值

语法：

添加约束

-- 创建表时添加默认约束
create table 表名 (
  列名 数据类型 default 默认值,
  ...
);

1
2
3
4
5

-- 建完表后添加默认约束
alter table 表名 modify column 列名 数据类型 default 默认值;

1
2

删除约束

alter table 表名 modify column 列名 数据类型 default null;

# 外键约束

外键约束：外键顾名思义，用来让一个表和其他表的数据之间建立联系。如下图：

员工表中的 dep_id 字段与部门表的 id 字段关联，通过这种关联关系，我们就知道张三属于部门 研发部 ，而 研发部 下有3名员工。加入我们删除 研发部 那在研发部下的员工就会出现数据不完整的问题，因为 研发部 删除后已经没办法确定 张三、李四、王五 它们归属于哪个部门。为了建立这种关系，及数据的完整性我们就要引入 外键约束 进行限制。这样可以确保在部门下有员工数据时，无法删除部门数据。

语法格式：

添加外键约束

-- 创建表时添加外键约束
create table 表名 (
  列名 数据类型,
  ...
  [constrant] [外键名称] foreign key(外键列名) references 主表(主表列名)
);

1
2
3
4
5
6

-- 建完表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key(外键字段) references 主表名称(主表列名称);

1
2

删除外键约束

alter table 表名 drop foreign key 外键名称;

# 约束练习

根据需求，为表添加合适的约束：

-- 员工表
create table emp (
  id int, -- 员工id，主键且自增长
  ename varchar(50), -- 员工姓名，非空且唯一
  joindate date, -- 入职日期，非空
  salary double(7,2), -- 工资，非空
  bonus double(7,2), -- 奖金，如果没有设置默认为0
  dep_id int -- 部门外键，非空
);

-- 部门表
create table dept (
  id int, -- 部门id，主键且自增长
  dep_name varchar(20), -- 部门名称，非空
  addr varchar(20) -- 部门地址
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

依据上面给出的需求，按照要求创建两张表，并未每一列添加对应的约束。建表语句如下：

-- 删除表
drop table if exists emp;
drop table if exists dept;

-- 部门表
create table dept (
  id int not null,
  dep_name varchar(20) not null,
  addr varchar(20),
  primary key(id)
);

-- 员工表
create table emp (
  id int not null, -- 员工id，主键且自增长
  ename varchar(50) not null unique, -- 员工姓名，非空且唯一
  joindate date not null, -- 入职日期，非空
  salary double(7,2) not null, -- 工资，非空
  bonus double(7,2) default 0, -- 奖金，没有设置默认为0
  dep_id int not null,
  primary key(id),
  constraint fk_emp_dept foreign key(dep_id) references dept(id)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

下面我们来针对添加的约束一一验证一下，先添加几条没有问题的数据。

-- 添加部门数据
insert into dept(dep_name, addr) values('研发部', '北京'),('销售部', '上海');
insert into emp(id, ename, joindate, salary, bonus, dep_id) values(1, '张三', '1999-11-11', 8800, 5000, 1);

1
2
3

验证主键约束，非空且唯一

insert into emp(id, ename, joindate, salary, bonus, dep_id)
values(null, '张三', '1999-11-11', 8800, 5000, 1);

1
2

执行结果如下：

从上面的结果可以看到，字段 id 不能为 null。再重新添加一条数据，如下：

insert into emp(id, ename, joindate, salary, bonus, dept_id) values(1, '张三', '1999-11-11', 8800, 5000, 1);

执行结果如下：

从上面结果可以看到，1 这个值重复了。因此主键约束是用来限制数据非空且唯一的。下面再添加一条符合要求的数据：

insert into emp(id, ename, joindate, salary, bonus, dep_id) values(1, '李四', '1999-11-11', 8800, 5000, 2);

执行结果如下：

验证非空约束

insert into emp(id, ename, joindate, salary, bonus, dep_id) values(3, null, '1999-11-11', 8800, 5000, 1);

执行结果如下：

从上面结果可以看到，ename字段的非空约束生效了。

验证唯一约束

insert into emp(id, ename, joindate, salary, bonus, dep_id) values(3, '李四', '1999-11-11', 8800, 5000, 1);

执行结果如下：

从上面的结果可以看到，ename 字段的唯一约束生效了。

验证默认约束：

insert into emp(id, ename, joindate, salary, dep_id) values(4, '王五', '1999-11-11', 88000, 1);

执行完上面语句后查询表中数据，可以看到王五这条数据的bonus列就有了值0。

注意

默认约束只有在不给值时才会采用默认值。如果设置了null，那值就是null值。如：

insert into emp(id, ename, joindate, salary, bonus, dep_id) values(5, '赵六', '1999-11-11', 8800, null, 2);

执行完上面语句后查看下结果：

验证自动增长：auto_increment 修饰的列是数字类型，并且唯一

重新创建 emp 表，并给 id 列添加自动增长

-- 删除表
drop table if exists emp;

-- 创建表
create table emp (
  id int not null auto_increment,
  ename varchar(50) not null unique,
  joindate date not null,
  salary double(7,2) not null,
  bonus double(7,2) default 0,
  dep_id int not null,
  primary key(id),
  constraint fk_emp_dept foreign key(dep_id) references dept(id)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14

接下来给 emp 添加数据，不给 id 列添加值以及给 id 列添加 null 值来验证 id 列的值会不会自动增长：

insert into emp(ename, joindate, salary, bonus, dep_id) values('赵六', '1999-11-11', 8800, null, 1);
insert into emp(id, ename, joindate, salary, bonus, dep_id) values(null, '赵六2', '1999-11-11', 8800, null, 1);
insert into emp(id, ename, joindate, salary, bonus, dep_id) values(null, '赵六3', '1999-11-11', 8800, null, 1);

1
2
3

执行结果：

验证外键约束

尝试删除 dept 表 id 为 1 的数据，会发现：

可以看到，无法删除 dept 表中 id = 1 的数据。

删除外键后我们在尝试一下看看：

-- 删除外键
alter table emp drop foreign key fk_emp_dept;
-- 删除数据
delete from dept where id = 1;

1
2
3
4

验证完成，我们重新添加一下外键：

alter table emp add constraint fk_emp_dept foreign key(dep_id) references dept(id);

特别注意

由于 dept 表中 id = 1 的数据已经被删除，而 emp 表中依旧有 dep_id = 1 的员工数据，此时重新添加外键会失败：

此时，需要删除 emp 表中 dep_id = 1 的员工数据，或者恢复 dept 表中 id = 1 的数据以后在添加外键

# 数据库设计

在企业开发工作中，设计工作不可或缺，比如：原型设计、UI设计、数据库设计、接口设计等等，合理的设计可以使应用系统易用、性能良好、易于维护扩展。因此，设计工作往往由经验丰富的业务专家、架构师来承担。下面我们来看下企业中软件的研发步骤：

# 数据库设计简介

上面我们介绍了在软件开发过程中设计工作的重要性，下面我们来重点讲解一下数据库设计相关的内容。

数据库设计概念：

数据库设计就是根据业务系统的具体需求，结合我们选用的DBMS，为这个业务系统构造出最优的数据存储模型。
构造数据存储模型就需要我们确定下面几个内容：
- 有哪些表？
- 每张表有哪些字段？
- 表与表之间有什么关系？

简单来说，数据库设计就是依据需求，确定数据库中表结构、表关系的过程。

数据库设计步骤：

数据库设计大体上分为三步：需求分析、逻辑分析、物理设计，然后经历这么一个过程后，我们就可以获得一个表结构、表关系确定的数据库，依据这个数据库就可以编码满足业务需求。随着系统的使用可能还会有新需求提出，我们沿用上面的三个步骤进行迭代设计、开发即可。

需求分析：就是针对需求规格说明书或者需求原型进行分析，确定：
- 数据是什么？
- 数据具有哪些属性？
- 数据与属性的特点是什么？
逻辑分析：需求分析完成后，我们就可以针对需求分析结果进行建模，产出E-R图，在这个阶段可以不用考虑所选用的数据库管理系统，如下图就是ER(Entity/Relation)图
物理设计：就是根据所选用的数据库管理系统将逻辑设计转换为物理设计，进行建库、建表等操作

数据库设计的规则 -- 三大范式：

为了建立冗余较小、结构合理的数据库，数据库设计时必须遵循一定的规则。这种规则就叫做范式。范式就是符合某一种设计要求的总结，要想设计一个结构合理的关系型数据库，必须满足一定的范式。

在实际开发中最常见的范式有三个：

第一范式 -- 确保每列保持原子性：

第一范式是最基本的范式。如果数据库表中的所有字段都是不可分解的原子值，就说明该数据库满足第一范式。

第一范式的合理遵循需要根据系统给的实际需求来确定。比如：某些系统中需要用到地址这个属性，本来直接将地址属性设计为一个表的字段就行，但如果系统经常访问地址属性中的城市部分，那么一定要把地址这个属性重新拆分为省份、城市、详细地址 等多个部分来进行存储，这样对地址中某一个部分进行操作的时候会非常方便，这样设计才算满足第一范式。如下图：

第二范式 -- 确保表中的每列都和主键相关：

第二范式在第一范式的基础上更进一层，第二范式需要确保表中每一列都和主键相关，而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如图：

这里就产生了一个问题：这个表中是以订单编号和商品编号作为联合主键，这样在该表中商品名称、单位、商品价格等信息不与该表的主键完全相关，而仅仅只与商品的编号相关，在这里就违反了第二范式。

如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单信息也分离到另一个表中，然后通过订单项目表来维持商品信息表和订单信息表的关系就可以了，这样的设计就比较完美了，如图：

上面的设计在很大程度上减小了数据的冗余，如果要获取订单的商品信息，可以通过订单信息表、订单项目表、商品信息表之间的关系进行查询获取。

第三范式 -- 确保每列都和主键直接相关，而不是间接相关：

比如，在设计订单信息表时，可以将客户编号作为一个外键和订单表建立关系，而不是在订单表中添加关于客户的(比如姓名、所属公司)字段，如下图的设计就是满足第三范式的设计：

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入同一个客户信息的内容，减少数据冗余。

上面我们介绍了数据库设计的概念、步骤、规则，可以看到在设计过程中我们不仅要确定有哪些数据和属性，还要确定数据与数据之间的关系。下面我们就来看一下数据库中表之间存在哪些关系？

# 一对一表关系

概念：

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放在另一张表中，可用于提升查询性能。如：用户和 用户详情 表。

上图左边是用户的详细信息，而我们经常展示的是上图右边红框所示内容，所以我们一般会将用户详细信息拆分成两张表。

实现方式：

在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)。

案例：

以 用户表 举例：

而在真正使用过程中 id、photo、nickname、age、gender 字段比较常用，我们就可以将这张表拆分成两张表。

建表语句如下：

create table tb_user_desc (
  id int not null auto_increment,
  city varchar(20),
  edu varchar(20),
  income int,
  status char(2),
  des varchar(100),
  constraint pk_tb_user_desc primary key(id)
);

create table tb_user (
  id int not null auto_increment,
  photo varchar(100),
  nickname varchar(50),
  age int,
  gender char(1),
  desc_id int unique,
  constraint pk_tb_user primary key(id),
  constraint fk_user_desc foreign key(desc_id) references tb_user_desc(id)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

其表结构模型图：

# 一对多表关系

概念：

一对多表关系在现实生活中还是比较常见的，比如：部门和员工 , 班级和学生。

实现方式：

在多的一方建立外键，指向一的一方的主键。

案例：

依旧以 员工表 和 部门表 为例：

经过分析，员工表 属于多的一方，部门表 属于一的一方，此时我们会在员工表中添加一列 dep_id ，指向 部门表 的主键 id 。

建表语句如下：

-- 删除表
drop table if exists tb_emp;
drop table if exists tb_dept;

-- 部门表
create table tb_dept (
  id int not null auto_increment,
  dep_name varchar(20),
  addr varchar(20),
  -- 添加主键
  constraint pk_tb_dept primary key(id)
);

-- 员工表
create table tb_emp (
  id int not null auto_increment,
  name varchar(20),
  age int,
  dep_id int,
  -- 添加主键
  constraint pk_tb_emp primary key(id),
  -- 添加外键
  constraint fk_emp_dept foreign key(dep_id) references tb_dept(id)
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

其表结构模型图如下：

# 多对多表关系

概念：

多对多表关系实际上可以拆分为两个一对多的表关系，类似于咱们上面介绍数据库设计第二范式时举的例子，订单信息表 、订单项目表 、 商品信息表 ，一个商品对应多个订单，一个订单包含多个商品。

实现方式：

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

案例：

以 订单表 和 商品表 举例：

经分析，订单表 和 商品表 都属于多的一方，此时需要创建一个 中间表 ，在 中间表 中添加 订单表 的外键和 商品表 的外键分别指向两张表的主键：

建表语句如下：

-- 删除表
drop table if exists tb_order_goods;
drop table if exists tb_order;
drop table if exists tb_goods;

-- 订单表
create table tb_order (
  id int not null auto_increment,
  payment double(10, 2),
  payment_type tinyint,
  status tinyint,
  constraint pk_tb_order primary key(id)
);

-- 商品表
create table tb_goods (
  id int not null auto_increment,
  title varchar(100),
  price double(10, 2),
  constraint pk_tb_goods primary key(id)
);

-- 订单商品中间表
create table tb_order_goods (
  id int not null auto_increment,
  order_id int,
  goods_id int,
  count int,
  constraint pk_tb_order_goods primary key(id)
);

-- 建完表后，添加外键
alter table tb_order_goods add constraint fk_order_id foreign key(order_id) references tb_order(id);
alter table tb_order_goods add constraint fk_goods_id foreign key(goods_id) references tb_goods(id);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

其表结构模型图如下：

# 数据库设计案例

假设现在给你了一个原型图，需要你根据原型图设计表及表和表之间的关系：

经分析，里面大概分为 专辑表 、 曲目表 、 短评表 、 用户表 4张表。

一个专辑可以有多个曲目，一个曲目只能属于某一张专辑，所以 专辑表 和 曲目表 的关系是一对多。

一个专辑可以被多个用户进行评论，一个用户可以对多个专辑进行评论，所以 专辑表 和 用户表 的关系是多对多。

一个用户可以发多个短评，一个短评只能是某一个人发的，所以 用户表 和 短评表 的关系是一对多。

目标：

重点掌握多表查询操作

掌握事务操作

# 多表查询

上面我们讲解数据库设计时已经发现，业务系统开发过程中不可能从一张表中就能获取到所有我们想用的数据，比如我们在介绍一对多表关系时，如果我们想要获取 部门名称 、部门地址 以及部门中的 员工姓名 、员工年龄 该怎么操作？大家可以看到，这些信息必须得从两张表里面获取，接下来我们就来学习下如何从多张表中查询所需要的数据。

先准备下环境，创建两张表 部门表 、 员工表 并初始化一些数据到这两张表中：

-- 删除表
drop table if exists emp;
drop table if exists dept;

-- 创建部门表
create table dept (
  did int not null auto_increment,
  dname varchar(20),
  constraint pk_dept primary key(did)
);

-- 创建员工表
create table emp (
  id int not null auto_increment,
  name varchar(10),
  gender char(1), -- 性别
  salary double(10,2), -- 工资
  join_date date, -- 入职日期
  dep_id int,
  constraint pk_emp primary key(id),
  -- 外键，关联部门表
  constraint fk_emp_dep foreign key(dep_id) references dept(did)
);

-- 添加部门数据
INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',null);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

数据初始化完毕后，咱们先按照自己的理解来写一个多表查询的语句，看查询结果是否正常，多表查询是不是就在 from 关键字后面写多个表名就可以了？咱们来试试看：

select * from emp, dept; -- 从emp何dept表中查询所有的字段数据

结果如下：

从图中可以看到，查询出的结果特别奇怪，有一些无效的数据，如 孙悟空 这个员工属于 1 好部门，但也同时关联到了 2、3、4 号部门，这种奇怪的结果在数学领域叫 笛卡尔积 ，这里我们就不做具体的讲解了，简单了解一下它的计算方式即可：

再来分析下上面的SQL语句，我们知道 emp 、 dept 这两张表是有关系的，但在SQL语句中并没有使用它们的关系，那我们来尝试通过限制 员工表 中的 dep_id 字段的值和 部门表 中 did 字段的值相等再来看一下结果：

select * from emp, dept where emp.dep_id = dept.did;

执行结果如下：

这次的执行结果就比较正常了，其实上面的语句就是连接查询，那么多表查询都有哪些分类呢？

# 内连接查询

语法：

-- 隐式内连接
select 字段列表 from 表1, 表2 ... where 条件列表;

-- 显示内连接
select 字段列表 from 表1 [ inner ] join 表2 on 条件;

1
2
3
4
5

作用：查询A表、B表交集的数据

案例:

隐式内连接

select * from emp, dept where emp.dep_id = dept.did;

执行上述语句结果如下：

查询 emp 的 name、gender、dept 表的 dname

select emp.name, emp.gender, dept.dname from emp, dept where emp.dep_id = dept.did;

结果如下：

上面语句中使用表名指定字段有点麻烦，这个地方也支持通过 as 给表指定别名，如：

select t1.name, t1.gender, t2.dname from emp as t1, dept t2 where t1.dep_id = t2.did;

显式内连接

select * from emp inner join dept on emp.dep_id = dept.did;

-- 上面的inner可以省略
select * from emp join dept on emp.dep_id = dept.did;

1
2
3
4

执行结果如下：

# 外连接查询

语法：

-- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;

-- 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;

1
2
3
4
5

作用：

左外连接：相当于查询表A所有数据和表B交集部分数据

右外连接：相当于查询表B所有数据和表A交集部分数据

案例：
1. 查询 emp 表所有数据和对应的部门信息 -- 使用左外连接
```
select * from emp left join dept on emp.dep_id = dept.did;
```
1
结果如下：

查询到了左表 emp 中所有的数据及两张表能关联到的数据。
1. 查询 dept 表所有数据和对应的员工信息 -- 右外连接
```
select * from emp right join dept on emp.dep_id = dept.did;
```
1
结果如下：

查询到了右表 dept 中所有的数据及两张表能关联的数据。

提示

左外连接、右外连接两者之间是可以通过调整位置来进行转换的，比如：查询 dept 表所有数据和对应的员工信息 -- 使用右外连接，我们也可以通过左外连接来实现：

select * from dept left join emp on emp.dep_id = dept.did;

后面我们经常使用左外连接来进行表关联查询。

# 子查询

概念：

子查询就是在查询中嵌套查询，举个例子：查询员工表中工资高于猪八戒的员工信息。

我们首先来分析一下这个需求：

上面的需求完全可以通过一条语句来查询：

select * from emp where salary > (select salary from emp where name = '猪八戒');

这就是查询中嵌套查询。

作用：根据子查询结果不同，作用也不同
1. 子查询结果是单行单列，子查询作为条件，可以使用 =、!=、>、< 等进行条件判断
2. 子查询结果是多行单列，子查询作为条件，可以使用 in 等关键字进行条件判断
3. 子查询结果是多行多列，子查询可作为虚拟表

案例：

查询 财务部 和 市场部 所有的员工信息

select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');

查询 入职日期 是 2011-11-11 之后的员工信息和部门信息

select * from (select * from emp where join_date > '2011-11-11') t1, dept where t1.dep_id = dept.did;

# 多表查询练习

创建练习表、初始化数据

-- 删除表
drop table if exists emp;
drop table if exists dept;
drop table if exists job;
drop table if exists salarygrade;

-- 部门表
create table dept (
  did int not null auto_increment, -- 部门id
  dname varchar(50), -- 部门名称
  loc varchar(50), -- 部门所在地
  constraint pk_dept primary key(did)
);

-- 职务表
create table job (
  id int not null auto_increment, -- 职务id
  jname varchar(20), -- 职务名称
  description varchar(50), -- 职务描述
  constraint pk_job primary key(id)
);

-- 员工表
create table emp (
  id int not null auto_increment, -- 员工id
  ename varchar(50), -- 员工姓名
  job_id int, -- 职务id
  mgr int, -- 上级领导
  joindate date, -- 入职日期
  salary decimal(7,2), -- 工资
  bonus decimal(7,2), -- 奖金
  dept_id int, -- 所在部门id
  constraint pk_emp primary key(id),
  constraint fk_job foreign key(job_id) references job(id),
  constraint fk_dept foreign key(dept_id) references dept(did)
);

-- 工资等级表
create table salarygrade (
  grade int not null,
  losalary int, -- 最低工资
  hisalary int, -- 最高工资
  constraint pk_salarygrade primary key(grade)
);

-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');


-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);


-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

查询所有员工信息，查询内容：员工编号、员工姓名、工资、职务名称、职务描述

/*
  分析：
    1. emp: 员工编号、员工姓名、工资
    2. job: 职务名称、职务描述
    3. emp.job_id = job.id
*/
-- 隐式内连接
select 
  emp.id,
  emp.ename,
  emp.salary,
  job.jname,
  job.description
from 
  emp, job
where
  emp.job_id = job.id;

-- 显式内连接
select 
  emp.id,
  emp.ename,
  emp.salary,
  job.jname,
  job.description
from 
  emp join job
on 
  emp.job_id = job.id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

查询 员工编号、员工姓名、工资、职务名称、职务描述、部门名称、部门位置

/*
  分析：
    1. 员工编号，员工姓名，工资 信息在emp 员工表中
    2. 职务名称，职务描述 信息在 job 职务表中
    3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
    4. 部门名称，部门位置 来自于 部门表 dept
    5. dept 和 emp 一对多关系 dept.id = emp.dept_id
*/

-- 方式一 ：隐式内连接
select
  emp.id,
  emp.ename,
  emp.salary,
  job.jname,
  job.description,
  dept.dname,
  dept.loc
from
  emp,
  job,
  dept
where
  emp.job_id = job.id
  and dept.id = emp.dept_id;

-- 方式二 ：显式内连接
select
  emp.id,
  emp.ename,
  emp.salary,
  job.jname,
  job.description,
  dept.dname,
  dept.loc
from
  emp
inner join job on emp.job_id = job.id
inner join dept on dept.id = emp.dept_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

查询 员工姓名、工资、工资等级

/*
  分析：
    1. 员工姓名，工资 信息在emp 员工表中
    2. 工资等级 信息在 salarygrade 工资等级表中
    3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
select
  emp.ename,
  emp.salary,
  t2.*
from
  emp,
  salarygrade t2
where
  emp.salary >= t2.losalary
and emp.salary <= t2.hisalary;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

查询 员工姓名、工资、职务名称、职务描述、部门名称、部门位置、工资等级

/*
  分析：
    1. 员工编号，员工姓名，工资 信息在emp 员工表中
    2. 职务名称，职务描述 信息在 job 职务表中
    3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
    4. 部门名称，部门位置 来自于 部门表 dept
    5. dept 和 emp 一对多关系 dept.id = emp.dept_id
    6. 工资等级 信息在 salarygrade 工资等级表中
    7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
select
  emp.id,
  emp.ename,
  emp.salary,
  job.jname,
  job.description,
  dept.dname,
  dept.loc,
  t2.grade
from
  emp
inner join job on emp.job_id = job.id
inner join dept on dept.id = emp.dept_id
inner join salarygrade t2 on emp.salary between  t2.losalary and t2.hisalary;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

查询 部门编号、部门名称、部门位置、部门人数

/*
  分析：
    1. 部门编号、部门名称、部门位置 来自于部门 dept 表
    2. 部门人数: 在emp表中 按照dept_id 进行分组，然后count(*)统计数量
    3. 使用子查询，让部门表和分组后的表进行内连接
*/
-- 根据部门id分组查询每一个部门id和员工数
select dept_id, count(*) from emp group by dept_id;

select
  dept.id,
  dept.dname,
  dept.loc,
  t1.count
from
  dept,
  (
    select
      dept_id,
      count(*) count
    from
      emp
    group by
      dept_id
  ) t1
where
  dept.id = t1.dept_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 事务

# 事务概述

数据库的事务(Transaction)是一种机制、一个操作序列，包含了一组数据库操作命令。事务把所有的命令作为一个整体向系统提交或撤销，即这一组数据库命令要么同时成功，要么同时失败。事务是一个不可分割的工作逻辑单元。

这些概念不好理解，接下来举例说明，如下图：

张三和李四账户中各有1000块钱，现李四要转500块给张三，具体的操作为：

第一步：查询李四账户余额
第二步：从李四账户 -500
第三步：给张三账户 +500

现在假设在转账过程中第二步完成后出现了异常，导致第三步没有正常执行，就会造成李四账户少了500，但张三账户并没有加500；这样的系统肯定是有问题的，导致了客户的财产损失，如何解决呢？事务就是用来针对这类问题的。

从上图可以看到在转账前开启事务，如果有异常就回滚事务，三步正常执行成功就提交事务，这样就可以完美解决问题了。

# 事务语法

开启事务：

start transaction;
或者
begin;

1
2
3

提交事务

commit;

回滚事务

rollback;

# 代码验证

准备环境，创建表、初始化数据

-- 删除表
drop table if exists account;

-- 创建账户表
create table account (
  id int not null auto_increment,
  name varchar(10),
  money double(10,2),
  constraint pk_account primary key(id)
);

-- 添加数据
insert into account(name, money) values('张三', 1000), ('李四', 1000);

1
2
3
4
5
6
7
8
9
10
11
12
13

不加事务演示问题

-- 转账操作
-- 1. 查询李四账户金额是否大于500

-- 2. 李四账户 -500
update account set money = money - 500 where name = '李四';

出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行

-- 3. 张三账户 +500
update account set money = money + 500 where name = '张三';

1
2
3
4
5
6
7
8
9
10

执行结果：

添加事务

-- 开启事务
begin;
-- 转账操作
-- 1. 查询李四账户金额是否大于500

-- 2. 李四账户 -500
update account set money = money - 500 where name = '李四';

出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行

-- 3. 张三账户 +500
update account set money = money + 500 where name = '张三';

-- 提交事务
commit;

-- 回滚事务
rollback;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

上面sql语句执行成功则提交事务，出现问题回滚事务。后面我们在实际工作中肯定不是这样操作的，而是在程序中进行操作，利用Java的异常捕获机制，无异常提交事务，出现异常回滚事务。

# 事务的特征

原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
隔离性（Isolation） :多个事务之间，操作的可见性
持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

注意

mysql中事务是自动提交的。也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。可以通过下面语句查询默认提交方式：

SELECT @@autocommit;

查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式

set @@autocommit = 0;