java- MySql多表查询和事务(3)

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