MYSQL多表查询和事物详细解释

MySql多表查询和事务

一、范式

1. 什么是范式

1.1 什么是范式

• 范式：要想设计一个科学的、规范的数据库，所需要遵循的规则和规范

1.2 有哪些范式

• 共有6大范式：层层递进

  • 1NF：最基本的要求

  • 2NF：1NF+更多要求

  • 3NF：2NF+更多要求

  • BCNF：3NF+更多要求

  • 4NF：BCNF+更多要求

  • 5NF：4NF+更多要求， 完美范式

2. 常见范式

2.1 第1范式

• 要求所有列不可拆分

2.2 第2范式

• 表里所有列完全依赖于主键

2.3 第3范式

• 表里字段如果要引入其它表数据，要把字段设置为外键，引用其它表的主键

二、==多表查询==

1. 迪卡尔积

• 迪卡尔积：多表之间毫无意义的排列组合，组合的结果是迪卡尔积。

• 如何避免迪卡尔积：多表关联时，一定要有关联条件

• 多表关联查询的技巧：

  • 确定要查询的数据在哪些表里

  • 把多表合并成一张表，注意：一定要有表的关联条件

  • 从合并后的结果里，筛选、统计、计算 需要的结果

2. 多表查询方式

2.1 内连接

• 内连接的查询效果：查询两张表之间必定有关联的数据

• 内连接的查询语法：

  • 显式内连接：select 字段 from 表1 inner join 表2 on 关联条件 where 条件

  • 隐式内连接：select 字段 from 表1, 表2 where 关联条件 and 条件

-- 1. 查询所有的员工和部门信息
-- 1.1 确定要查询的数据分别在：dept表里、emp表里
-- 1.2 两张表之间的关系：dept.id = emp.dept_id
SELECT * FROM dept, emp WHERE dept.id = emp.dept_id;
​
-- 1.3 使用隐式内连接：查询员工的姓名和所属部门的名称
SELECT emp.name, dept.name FROM dept, emp WHERE dept.id = emp.dept_id;
​
-- 1.4 使用显式内连接：查询员工的姓名和所属部门的名称
-- 1.4.1 数据在哪些表里：emp、dept
-- 1.4.2 这些表之间的关联条件：emp.dept_id = dept.id
SELECT emp.name, dept.name FROM emp INNER JOIN dept ON dept.id = emp.dept_id ;
​
-- 1.5 查询工资大于5000的 员工的姓名和所属部门名称
SELECT emp.name, dept.name FROM emp INNER JOIN dept ON dept.id = emp.dept_id WHERE emp.salary > 5000;

2.2 外连接

• 外连接查询的效果：查询一张表的全部数据，以及另外一张表的关联数据

• 左外连接：查左表的全部数据，以及右表的关联数据

  • select 字段 from 表1 left join 表2 on 表关联条件 where 条件

• 右外连接：查右表的全部数据，以及左表的关联数据

  • select 字段 from 表1 right join 表2 on 表关联条件 where 条件

-- 2. 外连接查询：
-- 2.1 查询所有的员工信息，及关联的部门信息
-- 数据在哪些表里：emp, dept
-- 这些表的关联条件：emp.dept_id = dept.id
-- 2.1.1 左外连接
SELECT * FROM emp LEFT JOIN dept ON emp.dept_id = dept.id;
-- 2.1.2 右外连接
SELECT * FROM dept RIGHT JOIN emp ON emp.dept_id = dept.id;
​
-- 2.2 查询所有的部门信息，及关联的员工信息
-- 2.2.1 左外连接
SELECT * FROM dept LEFT JOIN emp ON dept.id = emp.dept_id;
-- 2.2.2 右外连接
SELECT * FROM emp RIGHT JOIN dept ON dept.id = emp.dept_id;

2.3 子查询

• 是一种查询的技巧，没有固定语法。查询嵌套

• 子查询结果是一行一列：一个值

• 子查询结果是多行一列：一个集合

• 子查询结果是多行多列：一张虚拟表

-- 3. 子查询：一种查询技巧，没有固定语法
-- 3.1 查询工资最高的那个员工信息
-- 3.1.1 查询出来最高工资是多少
SELECT MAX(salary) FROM emp;
-- 3.1.2 查询工资等于 最高工资的员工
SELECT * FROM emp WHERE salary = 9000;
​
-- 合并成一条SQL
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
​
-- 3.2 查询工资比平均工资高的员工信息
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);
​
-- 3.3 查询工资大于5000的员工 所属的部门名称
-- 3.3.1 查询工资大于5000的员工的dept_id
SELECT dept_id FROM emp WHERE salary > 5000;
-- 3.3.2 查询这些dept_id对应的部门名称
SELECT * FROM dept WHERE id IN (1, 2);
​
SELECT * FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary > 5000);
​
-- 3.4 查询工资大于5000的 员工信息和部门信息
-- 3.4.1 使用外连接查询 
SELECT * FROM emp LEFT JOIN dept ON emp.dept_id = dept.id WHERE emp.salary > 5000;
-- 3.4.2 使用子查询
SELECT * FROM emp WHERE salary > 5000;
SELECT * FROM (SELECT * FROM emp WHERE salary > 5000) t LEFT JOIN dept d ON d.id = t.dept_id;

三、事务

1. 什么是事务

• 什么是事务：数据库的概念，指 事务组成的一级操作单元，要么全部成功，要么全部失败。

• 事务的作用：保证多个操作要么全部成功，要么全部失败。

• 什么时候使用事务：多条DML语句执行时，要求都成功或都失败。事务无关查询操作

• 事务的经典使用场景：张三要给李四转账100

  • 开启事务

  • 执行SQL：张三扣钱100

  • 执行SQL：李四加钱100

  • 关闭事务：

    • 提交事务：SQL语句生效

    • 回滚事务：SQL语句撤消

2. 事务管理

• 数据准备

-- 事务管理
-- 1. 准备数据
CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    money DOUBLE
);
INSERT INTO account (id,NAME,money) VALUES (NULL, 'tom', 1000);
INSERT INTO account (id,NAME,money) VALUES (NULL, 'jerry', 1000);

2.1 手动提交的事务管理

-- 手动提交的事务管理
-- 1. 开启事务
START TRANSACTION;
​
-- 2. tom扣钱100  数据变更没有真正生效，是缓存起来了
UPDATE account SET money = money - 100 WHERE NAME = 'tom';
​
-- 3. jerry加钱100
UPDATE account SET money = money + 100 WHERE NAME = 'jerry';
​
-- 4. 关闭事务：提交事务
-- commit;
-- 4. 关闭事务：回滚事务
ROLLBACK;

2.2 自动提交的事务 管理

• 自动提交的开关设置和查询

-- 自动提交事务  开关进行管理
-- 1. 操作自动提交的开关
-- 1.1 查看自动提交的开关状态
SELECT @@autocommit;
-- 1.2 关闭自动提交的开关
SET autocommit = 0;
-- 1.3 开启自动提交的开关
SET autocommit = 1;

• 通过自动提交开关，进行事务管理

-- 2. 通过 自动提交的开关，进行事务管理
-- 2.1 关闭自动提交
SET autocommit = 0;
​
-- 2.2 执行tom扣钱100
UPDATE account SET money = money - 100 WHERE NAME = 'tom';
​
-- 2.3 执行jerry加钱100
UPDATE account SET money = money + 100 WHERE NAME = 'jerry';
​
-- 2.4 关闭事务：提交事务
COMMIT;
-- 2.4 关闭事务：回滚事务
ROLLBACK;
​
-- 2.5 开启自动提交
SET autocommit = 1;

3. 回滚点（了解）

• 回滚点：事务里可以回滚到指定的回滚点，而不必回滚事务里所有的操作

-- 回滚点
-- 1. 开启事务
START TRANSACTION;
-- 2. tom扣钱100，余额：900
UPDATE account SET money = money - 100 WHERE NAME ='tom';
-- 3. tom扣钱100，余额：800
UPDATE account SET money = money - 100 WHERE NAME ='tom';
-- 4. 设置一个回滚点，名称：point1
SAVEPOINT point1;
-- 5. tom扣钱100，余额：700
UPDATE account SET money = money - 100 WHERE NAME ='tom';
-- 6. 回滚到point1
ROLLBACK TO point1;
-- 7. 关闭事务：提交事务   tom余额：800
COMMIT;

4. 事物特性和隔离级别(概念性)

4.1 事务的四大特性ACID（面试题）

• A：Atomicity，原子性。事务不可分割，即：事务里所有操作要成功，都成功；要失败，都失败。不可能成功一半

• C： Consistency，一致性。事务提交前后，数据是完整一致的

• I：Isolation，隔离性。多事务并发时，理论上事务应该是完全隔离，相互独立、互不影响的

• D：Durability，持久性。事务一旦提交，数据变更就永久保存到磁盘文件上了。

4.2 事务的隔离级别和并发问题

4.2.1 事务并发的问题

• 脏读：一个事务里，读取到了另外一个事务未提交的数据

• 不可重复读：一个事务里，多次读取的数据不一致；受到了其它事务的update干扰

• 虚读/幻读：一个事务里，多次读取的数据数量不一致；受到了其它事务的insert、delete干扰

4.2.2 事务的隔离级别

隔离级别	脏读	不可重复读	虚读
read uncommitted	★	★	★
read committed	☆	★	★
repeatable read	☆	☆	★
serializable	☆	☆	☆

4.3 事务的隔离级别效果的演示

• 隔离级别的操作

-- 1. 查看隔离级别
SELECT @@tx_isolation;
-- 2. 修改隔离级别：当前本次连接（会话）的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

4.3.1 演示read uncommitted

1. 开启两个连接：A（效果演示的事务）和B（干扰事务）

2. 修改A的隔离级别：read uncommitted

3. A和B都开启事务

4. A查询tom的余额--确认原始数据

5. B修改tom的余额，但是不提交事务

6. A再查询tom的余额

   • 如果查询结果变了，说明存在脏读问题

4.3.2 演示read committed

1. 开启两个连接：A（效果演示的事务）和B（干扰事务）

2. 修改A的隔离级别：read committed

3. A和B都开启事务

4. A查询tom的余额--确认原始数据

5. B修改tom余额，但是不提交事务

6. A再查询tom的余额

   • 如果数据没有变，就说明脏读问题没有了

7. B提交事务（A不提交）

8. A再次查询tom的余额

   • 如果数据变了，说明有不可重复读问题

4.3.3 演示repeatable read

1. 开启两个连接：A（效果演示的事务）和B（干扰事务）

2. 修改A的隔离级别：repeatable read

3. A和B都开启事务

4. A查询tom的余额--确认原始数据

5. B修改tom余额，但是不提交事务

6. A再查询tom的余额

   • 如果数据没有变，就说明脏读问题没有了

7. B提交事务（A不提交）

8. A再次查询tom的余额

   • 如果数据没有变，就说明不可重复读问题没有了

4.3.4 演示serializable

1. 开启两个连接：A（效果演示的事务）和B（干扰事务）

2. 修改A的隔离级别：serializable

3. A和B都开启事务

4. A执行了查询操作，事务不关闭

5. B执行操作

   • 等待A事务结束后，B再执行

 

内容回顾

1. 了解数据库的范式：设计一个科学的、规范的数据库，需要遵循的规则和规范

   • 1NF：要求列不可分割

   • 2NF：所有列要完全依赖于主键

   • 3NF：如果有列要引用其它表，要把字段设置为外键，引用其它表的主键字段

2. ==多表查询==

   1. 多表查询技巧：确定表；确定表之间的关联条件；从关联条件得到结果里筛选、统计、计算想要的结果

   2. 内连接查询：查询必定有关联的数据

   3. 外连接查询：查询一张表的全部数据，及另外一张表的关联数据

   4. 子查询：查询技巧，没有固定语法

3. 事务

   1. 能够说出事务的作用：保证事务里多个操作，要么全部成功，要么全部失败

   2. 理解事务操作的步骤：

      • 开启事务

      • 执行多个DML操作

      • 关闭事务：

        • 提交事务：

        • 回滚事务：

   3. 事务的四大特性ACID：

      1. 原子性

      2. 一致性

      3. 隔离性

      4. 持久性

   4. 事务并发存在的问题：

      1. 脏读

      2. 不可重复读

      3. 虚读/幻读

   5. 隔离级别解决事务并发问题

      1. read uncommitted：解决了：无；存在：脏读、不可重复读、幻读

      2. read committed：解决了：脏读 ； 存在：不可重复读、幻读

      3. repeatable read：解决了：脏读 、不可重复读； 存在：幻读

      4. serializable：解决了：脏读、不可重复读、幻读； 存在：无