D17--MYSQL又相遇（多表查询与事物）重点

一、三范式

1.1什么是范式

范式是指： 设计 数据库表的规则。规范的数据库就需要满足一些规则来优化数据的设计和存储。

1.2范式的基本分类

• 第一范式（1NF）
• 第二范式（2NF）
• 第三范式（3NF）
• 巴斯-科德范式（BCNF）
• 第四范式（4NF）
• 第五范式（5NF，又称完美范式）
  一般来说，数据库满足第三范式就行了。

1.3第一范式（1NF）

定义： 数据库表的每一列都是不可分割的院子数据项，而不能是集合、数组、记录等非原子数据项。简而言之，第一范式的每一列不可再拆分。

1.4第二范式（2NF）

1. 一张表只描述了一件事
2. 表中的每一个字段都依赖于主键

1.5第三范式（3NF）

从表的外键必须使用主表的主键

• 第三范式是第二范式的优化
• 第二范式是第一范式的优化

二、多表查询（重点）

2.1多表查询的分类：

• 内连接：
  1. 隐式内连接
  2. 显示内连接

• 外链接：
  1. 左外连接
  2. 右外连接

2.2 笛卡尔积

**定义：**表A的每一条数据都和表B的每一条数据进行一次匹配

SELECT *
FROM 表A，表B;

2.3 内连接

1. 隐式内连接

格式：

SELECT *
FROM 表A，表B
WHERE 条件；

• 例 :使用隐式内连接查询所有员工以及其对应部门的信息

SELECT *
FROM emp AS e,dept AS d
WHERE e.dept_id=d.id

2. 显式内连接

格式：

SELECT *
FROM 表A INNER JOIN 表B
ON 条件;
ps:INNER 可以省略

总结内连接的查询步骤：

1. 确定查询哪些表
2. 确定表连接条件
3. 确定查询字段

2.4外连接

1.左外连接

格式：

SELECT *
FROM 表A LEFT OUTER JOIN 表B
ON 条件
ps:查询左表中所有的数据，以及右表中关联着的数据

• 例：使用左外连接查询员工以及其对应部门的信息

SELECT *
FROM emp e LEFT OUTER  JOIN dept d
ON e.dept_id=d.id;

OUTER 可以省略

PS: 用左边表的记录去匹配右边表的记录，如果符合条件则显示，；否则，显示NULL。可以理解为：在内连接的基础上，保证左表的数据全部显示。

2.右外连接

格式：

SELECT *
FROM 表A RIGHT OUTER JOIN表B
ON 条件;

• 例

SELECT *
FROM emp e RIGHT OUTER JOIN dept d
ON e.dept_id=d.id;

PS:
用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示为NULL。可以理解为：在内连接的基础上保证右边的数据会全部显示，

2.5 子查询

定义：

1.一个查询语句可以作为另一个查询语句的一部分。
2.如果一个查询语句的结构只有一列，那么可以作为另一个查询语句的条件。
3.如果一个查询语句的结果有多列，那么可以作为另一个查询语句的表。
4.子查询语句一定要使用小括号包裹起来！

员工信息表 emp

部门表 dept

1.子查询的结果是一个值

• 例：查询工资最高的员工是谁？
  分析：
  1. 先查出最高工资
  2. 根据最高工资，查出员工信息

  -- 查出最高工资
  SELECT MAX(salary)
  FROM emp;
  -- 根据最高工资，查员工信息
  SELECT `name`
  FREOM emp
  WHERE salary =（SELECT MAX(salary)  FROM emp ）;
  

• 例 ：查询工资小于平均工资的员工有哪些？
  分析：

1. 先计算出平均工资
2. 在查出那些小于平均工资的员工

--直接写
SELECT `name`
FROM emp
WHERE salary<(SELECT  AVG(salary) FROM emp );

2.子查询是单列多行

技巧： 子查询如果是单列多行，要使用in运算符去运算

• 例：查询工资大于5000的员工的部门名字
  分析：

1. 先查出工资大于5000的员工信.
2. 在根据查出的员工信息查出部门信息

-- 查工资大于5000的员工信息,两张表通过部门id产生联系
SELECT  dept_id
FROM emp 
WHERE salary>5000;

-- 查部门信息
SELECT `name`
FROM dept
WHERE id  IN(
			SELECT dept_id 
			FROM emp  
			WHERE salary >5000
			);

• 例：查询开发部与财务部所有的员工信息
  分析：

1. 先查询开发部与财务部的id（两张表通过部门id产生联系）
2. 在通过部门id查询所有员工信息

-- 直接写
SELECT *
FROM emp
WHERE dept_id IN(
				SELECT id 
				FROM dept
				WHERE `name` IN  (`开发部`,`财务部`)
				 );

3.子查询的结果是多行多列

技巧： 如果子查询的结果是多行多列，那么可以当做另一个查询语句的表。

• 例：查询出2011年以后入职的员工信息，包括部门名称
  分析：
• 先查出2011年以后入职的员工信息。
• 在通过两张表的部门id产生联系，查询部门信息，然后，显示员工信息（包括部门名称）

-- 查询2011年后的入职信息
SELECT *
FROM emp
WHERE join_date>'2011-01-01'；

-- 结果
SELECT e.*,d.'name'
FROM dept d,(SELECT * FROM emp WHERE join_date>'2011-01-01') e
WHERE e.dept_id=d.id;

3.物理外键和逻辑外键

1. ** 物理外键：** 我们手动添加的foreign key 外键约束
   优点： 可以保证数据的完整性。
   缺点： 限制性过于强，使用起来非常不灵活。
2. 逻辑外键： 不需要使用foreign key手动添加外键约束，只需要让表与表之间在逻辑上产生关联关系即可。
   优点： 使用起来灵活
   缺点： 不能再数据库端保证完整性。

3.事务安全

3.1 事务概念

概念： 事务是逻辑上的一组操作， 这组操作要么都成功，要么都失败。

• 开启事务： 事务一旦开启，后面的操作全部都是一组操作了，那么全部成功，要么全部失败。
• 提交事务： 让事务内的所有的操作全部都生效。
• 回滚事务： 让事务内的所有操作全部失效。
  注意： 事务一旦开启后，只要不提交，那么就不会生效。事务开启之后回滚或者提交，那么该事务就结束了。mysql默认事务自动提交。

3.2事务原理

3.3事务的四大特性

3.3.1事务的四大特性

• 原子性： 事务的一组操作是不可再分的。这组操作要么都成功，要么都失败。
• 一致性： 事务操作前后数据是一致的。
• 隔离性： 多个事务之间应该互不干扰。
• 持久性： 事务一旦提交数据就真正的生效了。

3.3.3 不考虑隔离性，有三种并发访问问题

1. 脏读： 一个事务读取到了另一个事务没有提交的数据。
2. 不可重复读： 一个事务两次读取到的数据不一致。这是update引起的。
3. 幻读（虚读）： 一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，
   这是insert或delete时引发的问题

3.3.4 四个隔离级别

1. 读未提交： 会产生上面所有的并发访问问题（脏读，不可重复读，幻读）
2. 读已提交： 解决脏读，会产生不可重复读，幻读。
3. 可重复读： 解决脏读，不可重复读。会产生 幻读。
4. 串行化： 会解决所有问题，但同时只能执行一个事务（相当于事务的单线程）。

3.4 回滚点

定义： 可以供后续失败操作返回到该位置，而不是返回所有操作。
添加回滚点： savepoint 回滚点名称;
回滚到回滚点： rollback to 回滚点名称;