7-30 Mysql多表查询，事务

MySQL多表查询,事务

1，多表查询

多表查询就是指从多张表中查询数据

执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept ;

2，笛卡尔积

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。

在SQL语句中，去除无效的笛卡尔积， 我们可以给多表查询加上连接查询的条件即可。

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

3，多表查询 分类

• 连接查询

• • 内连接：相当于查询A、B交集部分数据

  • 外连接：

• • • 左外连接：查询左表所有数据，以及两张表交集部分数据

    • 右外连接：查询右表所有数据，以及两张表交集部分数据

• • 自连接：当前表与自身的连接查询，自连接必须使用表别名

• 子查询

4，内连接

内连接的语法分为两种: 隐式内连接、显式内连接。

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

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

5，外连接

外连接分为两种，分别是：左外连接 和 右外连接。

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

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

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

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

注意事项：左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

6，子查询（嵌套查询）

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

语法：SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

7，子查询分类

根据子查询结果不同，分为：

A. 标量子查询（子查询结果为单个值）

B. 列子查询(子查询结果为一列)

C. 行子查询(子查询结果为一行)

D. 表子查询(子查询结果为多行多列)

根据子查询位置，分为：

A. WHERE之后

B. FROM之后

C. SELECT之后

8，量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询

#案例
A. 查询 "教研部" 的所有员工信息
完成这个需求时，我们可以将需求分解为两步：
①. 查询 "教研部" 部门ID
​
select id from dept where name = '教研部';
​
select * from emp where dept_id = (select id from dept where name = '教研部');

9，列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：IN 、NOT IN

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内

案例:
A. 查询 "教研部" 和 "咨询部" 的所有员工信
分解为以下两步:
​
①. 查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name = '教研部' or name = '咨询部';
​
②. 根据部门ID, 查询员工信息
select * from emp where dept_id in (select id from dept where name = '教研部' or name = '咨询部');

10，行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

案例:
A. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ;
这个需求同样可以拆解为两步进行:
​
①. 查询 "韦一笑" 的入职日期 及 职位
select entrydate , job from emp where name = '韦一笑';
​
②. 查询与 "鹿杖客" 的薪资 及 职位相同的员工信息 ;
select * from emp where (entrydate,job) = (select entrydate , job from emp where name = '韦一笑');

11，表子查询

子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。

常用的操作符：IN

案例:
A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
分解为两步执行:
​
①. 入职日期是 "2006-01-01" 之后的员工信息
select * from emp where entrydate > '2006-01-01';
​
②. 查询这部分员工, 对应的部门信息;
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

12，怎么做多表查询的需求?

1、要查询的数据涉及到哪些表

2、找出关联条件 (外键=主键) 去除无效笛卡尔数据 + where其他额外的筛选条件

3、如果有分组，完成分组操作，有条件补充分组条件

4、找出需求中需要的数据放在select后

5、排序上分页

13，事务

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

14，事务的操作

1). 开启事务
START  TRANSACTION   或  BEGIN ;
2). 提交事务
COMMIT;
3). 回滚事务
ROLLBACK;

15，事务的四大特性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。

• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。