MySQL--多表查询和事务

一对多 (多对一)

案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键

多对多

案例：学生与课程的关系
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

案例：用户与用户详情的关系
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的（unique）

多表查询概述

概述：指从多张表中查询数据
笛卡尔积：笛卡尔乘积是指在数学中，两个集合 A 集合和 B 集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)

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

多表查询分类

连接查询
- 内连接：相当于查询 A、B 交集部分数据
- 外连接：
  - 左外连接：查询左表所有数据，以及两张表交集部分数据
  - 右外连接：查询右表所有数据，以及两张表交集部分数据
- 自连接：当前表与自身的连接查询，自连接必须使用表别名
子查询

内连接

隐式内连接：

select 字段列表 from 表1 , 表2 where 条件 ... ;

显式内连接：

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

内连接查询的是两张表交集的部分

外连接

左外连接：

select 字段列表 from 表1 left [outer] join 表2 on 条件 ... ;

相当于查询表 1 (左表) 的所有数据包含表 1 和表 2 交集部分的数据

右外连接：

select 字段列表 from 表1 right [outer] join 表2 on 条件 ... ;

相当于查询表 2 (右表) 的所有数据包含表 1 和表 2 交集部分的数据

自连接

select 字段列表 from 表a 别名a join 表a 别名b on 条件 ... ;

自连接查询，可以是内连接查询，也可以是外连接查询。取别名！

联合查询

对于 union 查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from 表a ...
union [all]
select 字段列表 from 表b ...;

注意：

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

子查询

概念：sql 语句中嵌套 select 语句，称为嵌套查询，又称子查询。

select * from t1 where column1 = (select column1 from t2);

子查询外部的语句可以是 insert /update/delete /select 的任何一个。

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

标量子查询（子查询结果为单个值）
列子查询 (子查询结果为一列)
行子查询 (子查询结果为一行)
表子查询 (子查询结果为多行多列)

根据子查询位置，分为：

where 之后
from 之后
select 之后

标量子查询

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

常用的操作符：= <>> >= < <=

-- 查询在“方东白”入职之后的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白');

列子查询

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

常用的操作符：in 、not in 、 any 、 some 、 all

操作符描述
in 在指定的集合范围之内，多选一
not in 不在指定的集合范围之内
any 子查询返回列表中，有任意一个满足即可
some 与 any 等同，使用 some 的地方都可以使用 any
all 子查询返回列表的所有值都必须满足

操作符	描述
in	在指定的集合范围之内，多选一
not in	不在指定的集合范围之内
any	子查询返回列表中，有任意一个满足即可
some	与 any 等同，使用 some 的地方都可以使用 any
all	子查询返回列表的所有值都必须满足

行子查询

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

常用的操作符：= 、<> 、in 、not in

-- 查询与“张无忌”的薪资和直属领导都相同的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');

表子查询

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

常用的操作符：in

-- 查询与“张无忌”和“宋远桥”的职位和薪资都相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name = '张无忌' or name = '宋远桥');

-- 查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
select e.*,d.* from (select * from emp where entrydate >'2006-01-01') e left jion dept d on e.dept_id =d.id;
-- 用到左外连接

事务

事务简介

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

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务

事务操作

方式一：

查看 / 设置事务提交方式

select @@autocommit;
set @@autocommit = 0;

提交事务

commit;

回滚事务

rollback;

方式二：

开启事务

start transaction 或 begin ;

提交事务

commit ;

回滚事务

rollback ;

事务四大特性

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据。
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。

事务隔离级别

隔离级别	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read (默认)	×	×	√
serializable	×	×	×

-- 查看事务隔离级别

select @@transaction_isolation;

-- 设置事务隔离级别

set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}

注意：事务隔离级别越高，数据越安全，但是性能越低。