一, 范式
1. 什么是范式
1.1 什么是范式
范式: 设置一个科学的, 规范的数据库, 需要满足的一些规则
1.2 有哪些范式
共有: 6 大范式
第 1 范式: 1NF 满足最基本的要求
第2范式:2NF 在1NF 基础上, 满足更多要求
第3范式:3NF 在2NF 基础上, 满足更多要求
巴斯 - 科德范式: BCNF 在3NF 基础上, 满足更多要求
第4范式:4NF 在 BCNF 基础上, 满足更多要求
第5范式:5NF 在4NF 基础上, 满足更多要求
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 才会执行
来源: http://www.bubuko.com/infodetail-3054439.html