敞开生长之旅！这是我参与「日新计划 12 月更文挑战」的第19天，点击查看活动详情

一、多表查询

1.1 什么是多表查询

如下表，咱们想查询孙悟空的姓名和他地点的部分的姓名，则需求运用多表查询，即咱们需求查询多张表才干得到咱们想要的成果。

# 创立部分表
create table dept( 
id int primary key auto_increment,
name varchar(20) )

insert	into dept (name) values ('开发部'),('市场部'),('财务部');

insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男 ',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男 ',3600,'2010-12-02',2); 
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008- 08-08',2); 
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女 ',5000,'2015-10-07',3); 
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女 ',4500,'2011-03-14',1);

1.1.1 多表查询的分类：

1.2 笛卡尔积现象

1.2.1 什么是笛卡尔积

笛卡尔乘积是指在数学中，两个调集X和Y的笛卡尔积（Cartesian product），又称直积，表明为XY，第一个对象是X的成员而第二个对象是Y的一切可能有序对的其中一个成员

需求：
查询一切的职工和一切的部分 
```sql
select * from emp,dept;

成果分析：

1.2.2 怎么清除笛卡尔积现象的影响

咱们发现不是一切的数据组合都是有用的，只要职工表.dept_id = 部分表.id 的数据才是有用的。所以需求通过条件过滤掉没用的数据。 — 设置过滤条件 Column ‘id’ in where clause is ambiguous

select * from emp,dept where id=5;
select * from emp,dept where emp.`dept_id` = dept.`id`;

— 查询职工和部分的姓名

select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

1.3 内衔接

用左面表的记载去匹配右边表的记载，假如契合条件的则显现。如：从表.外键=主表.主键

1.3.1 隐式内衔接

隐式内衔接： 看不到 JOIN 关键字，条件运用 WHERE 指定 SELECT 字段名 FROM 左表, 右表 WHERE 条件

select * from emp,dept where emp.`dept_id` = dept.`id`;

1.3.2 显式内衔接

显现内衔接： 运用 INNER JOIN … ON 句子, 能够省掉 INNER

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

查询唐僧的信息，显现职工 id，姓名，性别，薪酬和地点的部分称号，咱们发现需求联合 2 张表一起才干查询出需求的数据，运用内衔接

select
    e.id, --编号
    e.name,--姓名
    e.gender, --性别
    e.salary, --薪酬
    d.name,--部分姓名 
    from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧';

1.4 外衔接

外衔接能够分为左外衔接和右外衔接。

1.4.1 左外衔接

左外衔接：运用 LEFT OUTER JOIN … ON，OUTER 能够省掉

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件 用左面表的记载去匹配右边表的记载，假如契合条件的则显现；否则，显现 NULL

能够理解为：在内衔接的基础上确保左表的数据悉数显现(左表是部分，右表职工) — 在部分表中添加一个销售部

insert into dept (name) values ('销售部'); select * from dept;

— 运用内衔接查询

select * from dept d inner join emp e on d.`id` = e.`dept_id`;

— 运用左外衔接查询

select * from dept d left join emp e on d.`id` = e.`dept_id`;

1.5 右外衔接

右外衔接：运用 RIGHT OUTER JOIN … ON，OUTER 能够省掉

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件用右边表的记载去匹配左面表的记载，假如契合条件的则显现；否则，显现 NULL

能够理解为：在内衔接的基础上确保右表的数据悉数显现 — 在职工表中添加一个职工 — 在职工表中添加一个职工

insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);

select * from emp;

— 运用内衔接查询

select * from dept inner join emp on dept.`id` = emp.`dept_id`;

— 运用右外衔接查询

select * from dept right join emp on dept.`id` = emp.`dept_id`;

二、子查询

2.1 什么是子查询

子查询 指的是嵌套查询，查询中套查询。一个查询的成果做为另一个查询的条件，有查询的嵌套，内部的查询称为子查询，子查询要运用括号。

-- 需求：查询开发部中有哪些职工 
select * from emp; 
-- 通过两条句子查询 
select id from dept where name='开发部' ; 
select * from emp where dept_id = 1; 
-- 运用子查询 
select * from emp where dept_id = (select id from dept where name='市场部');

2.2 子查询成果的三种状况：

子查询的成果是单行单列
子查询的成果是多行单列
子查询的成果是多行多列

2.3 子查询的成果是一个值的时分

子查询成果只要是单行单列，肯定在 WHERE 后边作为条件，父查询运用：比较运算符，如：> 、<、<>、= 等

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

2.3.1 案例：查询薪酬最高的职工是谁？

-- 1) 查询最高薪酬是多少 
select max(salary) from emp; 
-- 2) 依据最高薪酬到职工表查询到对应的职工信息 
select * from emp where salary = (select max(salary) from emp);

2.3.2 查询薪酬小于平均薪酬的职工有哪些？

-- 1) 查询平均薪酬是多少 select avg(salary) from emp; 
-- 2) 到职工表查询小于平均的职工信息 select * from emp where salary < (select avg(salary) from emp);

2.4 子查询成果是多行单列的时分

子查询成果是单例多行，成果集类似于一个数组，父查询运用 IN 运算符

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

2.4.1 查询薪酬大于 5000 的职工，来自于哪些部分的姓名

-- 先查询大于 5000 的职工地点的部分 id 
select dept_id from emp where salary > 5000;
-- 再查询在这些部分 id 中部分的姓名 Subquery returns more than 1 row 
select name from dept where id = (select dept_id from emp where salary > 5000);
select name from dept where id in (select dept_id from emp where salary > 5000);

2.4.2 查询开发部与财务部一切的职工信息

-- 先查询开发部与财务部的 id 
select id from dept where name in('开发部','财务部'); 
-- 再查询在这些部分 id 中有哪些职工 
select * from emp where dept_id in (select id from dept where name in('开发部','财务 部'));

2.5 子查询的成果是多行多列

子查询成果只要是多列，肯定在 FROM 后边作为表

SELECT 查询字段 FROM （子查询） 表别号 WHERE 条件;

子查询作为表需求取别号，否则这张表没有称号则无法拜访表中的字段

2.5.1 查询出 2011 年今后入职的职工信息，包括部分称号

-- 查询出 2011 年今后入职的职工信息，包括部分称号 
-- 在职工表中查询 2011-1-1 今后入职的职工 
select * from emp where join_date >='2011-1-1'; 
-- 查询一切的部分信息，与上面的虚拟表中的信息组合，找出一切部分 id 等于的 dept_id 
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;
-- 也能够运用表衔接： 
select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1'; select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';

三、业务

3.1 业务的运用场景说明

业务指的是在一个业务操作存在多个操作数据库的操作，多个数据库操作一起成功一起失利，失利后数据一起回滚。例如：转账，往往是要屡次拜访数据库才干完结的。转账是一个用户扣钱，另一个用户加钱。假如其中有一条 SQL 句子出现异常，这条SQL就可能履行失利。业务履行是一个整体，一切的 SQL 句子都必须履行成功。假如其中有 1 条 SQL 句子出现异常，则一切的 SQL 句子都要回滚，整个业务履行失利。

转账的操作
-- 创立数据表 
CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), balance DOUBLE ); --- 添加数据 
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000); 
--模仿张三给李四转 500 元钱，一个转账的业务操作最少要履行下面的 2 条句子： 张三账号-500 李四账号+500 
-- 张三账号-500 
update account set balance = balance - 500 where name='张三'; 
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

假设当张三账号上-500 元,服务器溃散了。李四的账号并没有+500 元，数据就出现问题了。咱们需求确保其中一条 SQL 句子出现问题，整个转账就算失利。只要两条 SQL 都成功了转账才算成功。这个时分就需求用到业务。

3.2 业务提交

MYSQL 中能够有两种方式进行业务的操作：

手动提交业务
主动提交业务

3.2.1 手动提交业务

# 敞开业务
start transaction;
# 提交业务 
commit;
# 回滚业务 
rollback;

3.2.2 手动提交业务运用过程：

履行成功的状况：
- 敞开业务
- 履行多条 SQL 句子
- 成功提交业务
履行失利的状况：
- 敞开业务
- 履行多条 SQL 句子
- 业务的回滚

3.3 主动提交业务

MySQL 默认每一条 DML(增修改)句子都是一个单独的业务，每条句子都会主动敞开一个业务，句子履行结束主动提交业务，MySQL 默认开端主动提交业务

3.4 业务原理

业务敞开之后, 一切的操作都会暂时保存到业务日志中, 业务日志只要在得到 commit 指令才会同步到数据表中，其他任何状况都会清空业务日志(rollback，断开衔接)

3.4.1 原理图：

3.4.2 业务的步骤：

客户端衔接数据库服务器，创立衔接时创立此用户暂时日志文件
敞开业务今后，一切的操作都会先写入到暂时日志文件中
一切的查询操作从表中查询，但会通过日志文件加工后才回来
假如业务提交则将日志文件中的数据写到表中，否则清空日志文件。

3.5 回滚点

3.5.1 什么是回滚点

在某些成功的操作完结之后，后续的操作有可能成功有可能失利，但是不论成功还是失利，前面操作都现已成功，能够在当时成功的方位设置一个回滚点。能够供后续失利操作回来到该方位，而不是回来一切操作，这个点称之为回滚点。

3.5.2 回滚点的操作句子

3.6 业务的阻隔等级

3.6.1 业务的四大特性 ACID 业务特性意义

3.6.2 业务的阻隔等级

业务在操作时的抱负状况：一切的业务之间坚持阻隔，互不影响。因为并发操作，多个用户一起拜访同一个数据。可能引发并发拜访的问题：

3.6.3 MySQL 数据库有四种阻隔等级

上面的等级最低，下面的等级最高。“是”表明会出现这种问题，“否”表明不会出现这种问题。

阻隔等级越高，性能越差，安全性越高。

3.6.4 MySQL 业务阻隔等级相关的指令

查询大局业务阻隔等级

select @@tx_isolation;

设置业务阻隔等级，需求退出 MySQL 再从头登录才干看到阻隔等级的变化设置阻隔等级

set global transaction isolation level 等级字符串;

跋文

喜爱我的文章的朋友点点喜爱、保藏，也欢迎朋友们评论区留下你的意见和主张，恕毅在此感谢！

Mysql数据库基础篇 – 多表查询与事务的操作