mySQL多表查询与事务
1. 什么是范式
1.1 什么是范式
-
范式：设置一个科学的、规范的数据库，需要满足的一些规则
1.2 有哪些范式
-
共有：6大范式
-
第1范式：1NF 满足最基本的要求
-
第２范式：２NF 在１NF基础上，满足更多要求
-
第３范式：３NF 在２NF基础上，满足更多要求
-
巴斯－科德范式：BCNF 在３NF基础上，满足更多要求
-
第４范式：４NF 在BCNF基础上，满足更多要求
-
第５范式：５NF 在４NF基础上，满足更多要求
-
2. 常用范式
2.1 第一范式1NF
-
所有列不可拆分
2.2 第二范式2NF
-
所有列不可拆分
-
每张表所有列完全依赖于主键
2.3 第三范式3NF
-
所有列不可拆分
-
每张表所有列完全依赖于主键
-
每张表里的字段，如果要引用其它表数据，要引用其它表的主键
二、==多表查询==
多表查询的技巧：根据一些条件，把多张表关联合成一张表，从而把查询变成单表查询
-- 1.1 查询每个员工的信息，和所有的部门名称
-- 步骤1：确定数据在哪些表里 dept, emp
-- 步骤2：使用关联条件，关联这些表，合成一张表 dept.id = emp.dept_id
-- 步骤3：从关联后的表里，挑、运算、分组、统计 想要的数据
SELECT emp.*, dept.name FROM dept, emp WHERE dept.id = emp.dept_id
1. 迪卡尔积
-
迪卡尔积：多表查询时，没有关系条件，导致了 表的数据毫无意义的排列组合，这个结果叫迪卡尔积
-
迪卡尔积里有大量的脏数据，是一定要避免
-
怎样避免迪卡尔积：表关联时，一定要有关联的条件
2. 查询方式
2.1 内连接查询
-
内连接查询的是：表之间必定关联的数据
-
隐式内连接：
select 字段 from 表1, 表2, ... where 表关联条件 and 过滤条件 -
显式内连接：
select 字段 from 表1 inner join 表2 on 表关联条件 where 过滤条件
-- 2.1 查询唐僧的 员工id、姓名、工资、性别、部门名称： dept, emp； 关联条件：dept.id = emp.dept_id
-- 2.1.1 隐式内连接查询
SELECT emp.id, emp.name, emp.salary, emp.gender, dept.name deptname FROM dept, emp WHERE dept.id = emp.dept_id AND emp.name = '唐僧';
SELECT e.id, e.name, e.salary, e.gender, d.name deptname FROM dept d, emp e WHERE d.id = e.dept_id AND e.name = '唐僧';
-- 2.1.2 显式内连接查询
SELECT emp.id, emp.name, emp.salary, emp.gender, dept.name deptname FROM dept INNER JOIN emp ON dept.id = emp.dept_id WHERE emp.name = '唐僧';
2.2 外连接查询
-
外连接查询的效果是：查询一张表的全部数据，以及另外一张表的关联数据
-
左外连接：
-
查询左表的全部数据，及右表的关联数据
-
select 字段 from 表1 left join 表2 on 表关联条件 where 过滤条件
-
-
右外连接：
-
查询右表的全部数据，及左表的关联数据
-
select 字段 from 表1 right join 表2 on 表关联条件 where 过滤条件
-
-- 3. 外连接查询：查询一张表的全部数据，以及另外一张表的关联数据
-- 3.1 查询所有的员工，以其部门信息
-- 3.1.1 使用左外连接查询
SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;
-- 3.1.2 使用右外连接查询
SELECT * FROM dept d RIGHT JOIN emp e ON e.dept_id = d.id;
-- 3.2 查询所有的部门，及这个部门里的员工信息
-- 3.2.1 使用左外连接查询
SELECT * FROM dept d LEFT JOIN emp e ON d.id = e.dept_id;
-- 3.2.2 使用右外连接查询
SELECT * FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;
2.3 子查询
-
子查询结果是一个值（一行一列）
-
子查询结果是一个集合（多行一列）
-
子查询结果是一张虚拟表（多行多列）
-- 3. 子查询：一种查询技巧，select嵌套
-- 3.1 查询最大工资的那个员工信息
-- 3.1.1 分步骤查询方式：
SELECT MAX(salary) FROM emp;
SELECT * FROM emp WHERE salary = 9000;
-- 3.1.2 把多条语句合并成一条
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
-- 3.2 查询工资大于5000的员工 的部门名称
-- 3.2.1 使用外连接方式查询
SELECT d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.salary > 5000;
-- 3.2.2 使用子查询方式
SELECT dept_id FROM emp WHERE salary > 5000;
SELECT NAME FROM dept WHERE id IN (1, 2);
SELECT NAME FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary > 5000);
-- 3.3 查询工资大于5000的员工信息和部门名称
-- 3.3.1 使用外连接方式查询
SELECT e.*, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.salary > 5000;
-- 3.3.2 使用子查询方式
-- 步骤1：先查询出来工资大于5000的员工信息：多行多列的结果，相当于一张虚拟表
SELECT * FROM emp WHERE salary > 5000;
-- 步骤2：拿部门表，和工资大于5000的员工信息虚拟表，进行内连接查询
SELECT * FROM dept d, (SELECT * FROM emp WHERE salary > 5000) t WHERE d.id = t.dept_id;
三、事务
1. 事务简介
-
事务：组成一个事务的多个操作单元，要么全部成功，要么全部失败。
-
作用：保证组成事务的多个操作，要么全部成功，要么全部失败。
-
经典使用场景：jack给rose转账1000
-
开启事务
-
jack的帐户扣钱1000：
update -
rose的帐户加钱1000：
update -
关闭事务：
-
提交事务：事务里所有的数据变更会生效
-
回滚事务：事务里所有的数据变更会撤消
-
-
-
什么时候使用事务：
-
要执行数据变更（无关查询）
-
多条SQL的数据变更
-
2. 事务管理
2.1 手动的事务管理
-- 1. 手动事务管理：jack给rose转账100
-- 1.1 开启事务
START TRANSACTION;
-- 1.2 jack扣钱100--- 开启事务后，执行数据变更，是在缓存里，没有真正生效
UPDATE account SET balance = balance - 100 WHERE NAME = 'jack';
-- 1.3 rose加钱100
UPDATE account SET balance = balance + 100 WHERE NAME = 'rose';
-- 1.4 关闭事务：提交事务，事务里所有的数据变更会真正的生效
-- commit;
-- 1.4 关闭事务：回滚事务，事务里所有的数据变更会撤消，不会生效
ROLLBACK;
2.2 自动的事务管理
-
MySql的事务，默认情况下，自动提交是开启状态的
-- 2. 自动事务提交
-- 2.1 MySql的事务自动提交的开关操作
-- 2.1.1 关闭自动提交：仅仅是本次连接有效
SET autocommit = 0;
-- 2.1.2 查询自动提交的开关
SELECT @@autocommit;
-- 2.1.3 开启自动提交
SET autocommit = 1;
-- 2.2 自动提交的事务管理
-- 2.2.1 关闭自动提交
SET autocommit = 0;
-- 2.2.2 执行jack扣钱100 -- 数据变更缓存起来了
UPDATE account SET balance = balance - 100 WHERE NAME = 'jack';
-- 2.2.3 执行rose加钱100
UPDATE account SET balance = balance + 100 WHERE NAME = 'rose';
-- 2.2.4 关闭事务：提交事务
-- commit;
-- 2.2.4 关闭事务：回滚事务
ROLLBACK;
2.3 事务的回滚点
-
设置回滚点：
savepoint 回滚点名称 -
回滚到回滚点：
rollback to 回滚点名称
-- 3.事务的回滚点
-- 3.1 开启事务
START TRANSACTION;
-- 3.2 jack扣钱100, 余额成：900
UPDATE account SET balance = balance - 100 WHERE NAME = 'jack';
-- 3.3 设置一个回滚点，名称为point1
SAVEPOINT point1;
-- 3.4 jack扣钱100，余额成：800
UPDATE account SET balance = balance - 100 WHERE NAME = 'jack';
-- 3.5 回滚到回滚点point1
ROLLBACK TO point1;
-- 3.5 结束事务
COMMIT;
3. 事务的特性和隔离（概念性的）
3.1 ==事务的ACID四大特性(面试题)==
-
A：Atomicity，原子性。事务不可分割，即：事务不可能存在成功一半的情况
-
C：Consistency，一致性。事务提交前后，数据是一致的。
-
I：Isolation，隔离性。多事务并发时，理论是事务之间是完全隔离，互不干扰的
-
D：Durability，持久性。事务一旦提交，就会永久保存到磁盘文件上，除非再次更改。
3.2 事务并发的问题
-
脏读：一个事务读取到了另外一个事务未提交的数据。最严重的
-
不可重复读：一个事务里多次读取的数据不一致。受到了其它事务的update干扰
-
虚读/幻读：一个事务里多次读取的数据不一致。受到了其它事务的insert/delete干扰
3.3 事务的隔离级别
| 隔离级别 | 脏读问题 | 不可重复读问题 | 虚读/幻读问题 |
|---|---|---|---|
read uncommitted |
★ | ★ | ★ |
read committed |
☆ | ★ | ★ |
repeatable read |
☆ | ☆ | ★ |
serializable |
☆ | ☆ | ☆ |
3.4 事务并发问题的解决（演示）
-- 1. 查询隔离级别
SELECT @@tx_isolation;
-- 2. 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
3.4.1 read uncommitted
-
准备两个连接：A（效果演示事务）和B（干扰事务）
-
设置A的隔离级别为：
read uncommitted -
A和B同时开启事务
-
A查询jack的余额
-
B更新jack的余额：减100；但是不提交
-
A再次查询jack的余额
-
如果数据变了，演示了脏读的效果
-
3.4.2 read committed
-
准备两个连接：A（效果演示事务）和B（干扰事务）
-
设置A的隔离级别为：
read committed -
A和B同时开启事务
-
A查询jack的余额
-
B更新jack的余额：减100；但是不提交
-
A再次查询jack的余额
-
如果数据没有变，说明脏读问题没有了
-
-
B提交事务
-
A再次查询jack的余额
-
如果数据变了，说明存在不可重复读问题
-
3.4.3 repeatable read
-
准备两个连接：A（效果演示事务）和B（干扰事务）
-
设置A的隔离级别为：
repeatable read -
A和B同时开启事务
-
A查询jack的余额
-
B更新jack的余额：减100；但是不提交
-
A再次查询jack的余额
-
如果数据没有变，说明脏读问题不存在
-
-
B提交事务
-
A再次查询jack的余额
-
如果数据不变，说明不可重复读问题解决了
-
3.4.4 serializable
-
准备两个连接：A（效果演示事务）和B（干扰事务）
-
设置A的隔离级别为
serializable -
A和B同时开启事务
-
A执行一次查询；不结束事务
-
B执行一次修改
-
等待A事务结束，只有A结束了，B才会执行
-
