MySQL多表、事务

**

一、多表之间的关系

**

1.分类

1）一对一（实际开发中，一对一关系很少）

例如人和身份证的关系，一个人只有一个身份证；一个身份证也只能对应一个人，两者是一一对应关系。

2）一对多或多对一（实际开发中最常见）

例如员工和部门的关系，一个部门可以有多个员工，但是一个员工只能对应一个部门

3）多对多

例如学生和课程之间的关系，一个学生可以选择很多门课程，一门课程也可以对应多个学生

2.实现关系

1）一对多（多对一）

例如部门和员工。
实现方式：在多的一方建立外键，指向一的一方的主键

2）多对多

例如学生和课程。
实现方式：多对多关系实现需要借助第三章中间表。
中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
注意：联合主键。1-2存在的时候，2-1就可以不用写了

3）一对一（实际开发中很少，了解即可）

例如人和身份证
实现方式：可以在任意一方添加唯一的主键，指向另一方的主键

3.表间关系总结

一对多：主外键的关系

多对多：中间表，两个一对多

一对一：特殊的一对多，从表中的外键设置唯一，从表中的主键又是外键

**

二、多表查询

**
2.1 查询的语法

SELECT
	列名列表
FROM
	表名列表
WHERE...

2.2 查看表中的数据

1）普通查询

-- 查看部门表数据
SELECT * FROM dept;
-- 查看员工表数据
SELECT * FROM emp;

2）多表查询

-- 同时查看emp，dept表中的数据
SELECT * FROM emp,dept;

2.3笛卡尔积

1）多表查询的结果，称为笛卡尔积

2）现有A，B两个集合，两个集合的所有组成情况，就是笛卡尔积。例如A有3条数据，B有5条数据，则多表 查询的结果有3*5=15条数据。

3）多表查询时存在的一个问题是，会存在很多无用的数据，因为是所有组合情况。所以使用多表查询需要消除无用数据

2.4 多表查询的分类

1）内连接查询

a)隐式内连接：使用WHERE条件消除无用的数据

-- 查询所有的员工信息和对应的部门信息
SELECT * FROM emp, dept WHERE emp.`dept_id` = dept.`id`;

-- 查询员工表的名称，性别。部门表的名称
SELECT emp.`name`, emp.`gender`, dept.`name` FROM emp, dept WHERE emp.`dept_id` = dept.`id`;

-- 通过给表名起一个别名来简化代码（实际开发中的书写规范）
-- SQL语句【规范写法】（也是企业实际开发中的写法）：一个关键字一行，一个字段一行，一个表一行，一个条件一行
SELECT
	t1.`name`, -- 员工表的姓名
	t1.`gender`, -- 员工表的性别
	t2.`name` -- 部门表的名称
FROM
	emp t1,
	dept t2
WHERE
	t1.`dept_id` = t2.`id`;

b)显示内连接

-- 中括号[]表示这个是一个【可选】操作。
语法：SELECT 字段列表 FROM 表名 [inner] join 表名2 on 条件;
SELECT
	t1.`name`,
	t1.`gender`,
	t2.`name`
FROM
	emp t1
INNER -- INNER可以省略
JOIN
	dept t2
ON
	t1.`dept_id` = t2.`id`;

内连接其实查询的就是两张表的交集部分，交集部分指的是判断条件：t1.dept_id = t2.id;

2）外连接查询

a)左外连接

语法：SELECT 字段列表 FROM 表1（左表） LEFT [OUTER] JOIN 表2（右表） ON 条件;

案例分析：
    场景：新增一条员工数据，外键为NULL
        INSERT
        INTO
            emp (NAME, gender, salary, join_date, dept_id)
        VALUES
            ("小八", "女", 4000, NULL, NULL);
    需求：查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称

    隐式内连接查询：
        SELECT
        	e.*, d.`name`
        FROM
        	emp e, dept d
        WHERE
        	e.`dept_id` = d.`id`;

        存在的问题：
            因为新增的员工数据部门id是空的，但是没有任何一个部门的是空的，所以
            查询的时候，就被认为是不合法的数据，就不能实现需求。

    使用左外连接解决不合法的问题：
        SELECT
        	e.*, d.`name`
        FROM
        	emp e
        LEFT
        OUTER -- 可以省略
        JOIN
        	dept d
        ON
        	e.`dept_id` = d.`id`;

        这样，即使新添加的员工数据部门为空，也可以被查询显示出来，实现了需求。

逻辑分析：
    左外连接查询的是【左表所有数据以及两张表中的交集部分】。
    交集就是写的条件判断：e.`dept_id` = d.`id`

b)右外连接

语法：SELECT 字段列表 FROM 表1（左表） RIGHT [OUTER] JOIN 表2（右表） ON 条件;

逻辑分析：
	右外连接查询的是【右表所有数据以及两张表中的交集部分】。
交集就是写的条件判断：e.`dept_id` = d.`id`

3)子查询

a)子查询的结果是单行单列的：
子查询可以作为条件，使用运算符（> >= < <= =）去判断。

-- 查询工资最高的人
SELECT
    MAX(salary)
FROM
    emp;

SELECT
    *
FROM
    emp
WHERE
    emp.`salary` = 9000;

-- 子查询
SELECT
    *
FROM
    emp
WHERE
    emp.`salary` = (SELECT MAX(salary) FROM emp);

b)子查询的结果是多行单列的：
子查询可以作为条件，使用运算符（IN）来判断。

-- 查询研发部和销售部的所有员工信息
SELECT
    id
FROM
    dept
WHERE
    NAME = "研发部" OR NAME = "销售部";

SELECT
    *
FROM
    emp
WHERE
    emp.`dept_id` = 1
OR
    emp.`dept_id` = 2;

-- 子查询
SELECT
*
FROM
    emp
WHERE
    emp.`dept_id`
IN
(SELECT id FROM dept WHERE NAME = "研发部" OR NAME = "销售部");

c)子查询的结果是多行多列的：
– 子查询可以作为一张虚拟表。

-- 查询员工入职日期在2021-03-01之后的员工信息和部门信息
--子查询
SELECT
	*
FROM
	dept d,
	(SELECT * FROM emp WHERE emp.`join_date` > "2021-03-01") t -- 多行多列，子查询作为一张虚拟表
WHERE
	d.id = t.dept_id;

-- 普通内连接查询
SELECT
	*
FROM
	emp e, dept d
WHERE
	e.`dept_id` = d.`id` -- 到此步查询出来的是所有员工信息及其对应的部门信息
AND
	e.`join_date` > "2021-03-01"; -- 到此步查询出来的是入职时间在2021-03-01之后的所有员工信息及其部门信息

**

三、事务

**

3.1概念

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

3.2操作

1.开启事务：start transaction;
2.回滚：rollback;
3.提交事务：commit

3.3提交方式

1.自动提交：一条DML（增删改）语句会自动提交一次事务。数据会持久性更新
2.手动提交

3.4事务的四大特征

1.原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
2.持久性：当事务提交或回滚后，数据库会持久化更新数据
3.隔离性：多个事务之间，相互独立
4.一致性：事务操作前后，数据总量保持不变

3.4存在的问题

1.脏读：一个事务，读取到另一个事务未提交的数据
2.虚读（不可重复读）：在同一个事务中，两次读取到的数据不一样
3.幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，但是第一个事务查询不到修改

3.5事务的隔离级别

一般不需要进行修改，使用数据库默认的隔离级别就可以。

概念：
多个事务之间是隔离的，即相互独立的，但是如果多个事务操作同一批数据，则会引起脏读、虚读、幻读的问题，设置不同的隔离级别，可以解决这些问题。

1.read uncommitted:读未提交
    如果设置成读未提交，会产生的问题：脏读、不可重复读（虚读）、幻读
2.read commit:读已提交（Oracle默认）
    会产生的问题：不可重复读（虚读）、幻读
3. repeatable read:可重复读（MySQL默认）
    会产生的问题：幻读
4.serializable:串行化
    可以解决所有的问题
    串行化其实就一个锁表的动作，如果一个事务在操作一张数据表，另外一个事务是不可以操作这张表的，只有当锁打开后，才可以对表进行操作。
    由于将表锁住了，所以执行效率比较低。
    类似于多线程的锁机制。
注意：
    隔离级别从小到大（1->4），安全性越来越高，但是效率越来越低