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

拼搏现实的明天。 2022-02-21 10:49 201阅读 0赞

# 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 事务的隔离级别

<table> 
 <thead> 
  <tr> 
   <th>隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>虚读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>read uncommitted</code></td> 
   <td>★</td> 
   <td>★</td> 
   <td>★</td> 
  </tr> 
  <tr> 
   <td><code>read committed</code></td> 
   <td>☆</td> 
   <td>★</td> 
   <td>★</td> 
  </tr> 
  <tr> 
   <td><code>repeatable read</code></td> 
   <td>☆</td> 
   <td>☆</td> 
   <td>★</td> 
  </tr> 
  <tr> 
   <td><code>serializable</code></td> 
   <td>☆</td> 
   <td>☆</td> 
   <td>☆</td> 
  </tr> 
 </tbody> 
</table>

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`：解决了：脏读、不可重复读、幻读； 存在：无