MySQL多表

表关系

1.一对多

• 应用场景

  • 班级和学生

  • 部门和员工

• 建表原则

  • 设置（ForeginKey）外键连接

    • 一个表的外键即为另外一张表的主键,以此简历两张表的关系

  • 因此需要再学生表中新增一列，命名为 班级表_id，即班级表的主键，又叫学生表的外键

  • ALTER TABLE student
    ADD column class_id int
    comment '班级';
    

• 外键（一对多的关系下外键加在多的一方）

  • 物理外键

    • alter table emp add constraint 外键名称 foreign key (外键字段) references emp (另一个表的主键);

    • 不允许使用

      • 会影响增删改的效率（因为要检查外键关系）

      • 仅用于单节点数据，不适用于分布式、集群场景

      • 容易引发数据库的死锁问题，消耗性能

  • 逻辑外键（常用）

    • 就是在表中创建一个普通字段,这个字段保存另外一张表的主键值

    • ALTER TABLE stu_course_relation
      ADD column course_id int
      comment '班级id';
      

2.一对一

• 应用场景（外键一般插在基本信息表位置）

  • 丈夫和妻子

  • 人和身份证号

• 建表原则

  • 外键列设置唯一约束

3.多对多

• 应用场景

  • 学生和课程

    • 一个学生可能选多个课程，一个课程可能被多个学生选择

  • 老师和学生和班级

    • 一个老师可能教多个学生，一个老师也可能教多个班级

• 建表原则

  • 多对多其实由二个一对多组成

  • 需要借助于第三张表（中间表），需要有至少二个外键字段分别指向各自的主键，建立关联关系

多表查询

1.内连接

• 查询二张表的交集部分

• 语法

  • 隐式内连接

    • select ... from 左表,右表 where 连接条件;

    • 要取别名符合规范（下面这段不符合规范）

    • select student.*,class.name from class,student where student.class_id = class.id;

    • select s.studentName as '学生姓名',c.name as '学生班级' from class as c ,student as s where c.id = s.class_id;

  • 显示内连接

    • select ... from 左表 inner join 右表 on 连接条件;(前后关系无)

    • select student.*,class.name from class inner join student on student.class_id = class.id;
      

2.外连接

• 左外连接

  • 查询左表全部，再去匹配右表，有就显示，没有显示null

  • 语法

    • select ... from 左表 left [outer] join 右表 on 连接条件;

    • select student.*,class.name from student left join class on student.class_id = class.id;

    • select * from student left join stu_course_relation as scr on student.id = scr.stu_id
                    left join course as c on scr.course_id = c.id;

    • #查询课程被哪些学生选择了（课程名+学生名）
      select course.name,s.studentName from course
          left join stu_course_relation scr on course.id = scr.course_id
          left join student s on s.id = scr.stu_id;

• 右外连接（与左连接逻辑相同）

3.子查询

• 标量子查询

  • SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);

• 列子查询

  • SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);

• 行子查询

• 表子查询

4.多表连接 

 5.规律

• 1。确定几张表

• 2。确定连接条件

  • on关键字

• 3。确定业务条件

  • where关键字

• 4。确定显示字段

事物

1.含义

• 是指的一组sql作为一个工作单元，要么都成功，要么都失败

2.操作 

• begin/start transaction（开启事物）

• 中间语句

• commit（提交）

• rollback（回滚）

3.事物特性（ACID）

• A（Atomic）

  • 原子性

    • 不可分割，不可能只执行其中一部分

• C（Consistency）

  • 一致性

    • 数据总是从一种一致性状态转换到另一种一致性状态

    • 主要指的是事务执行后，数据的完整性，数据业务逻辑等要正确，这样才能保证数据转换始终一致

• I（Isolation）

  • 隔离性

    • 通常来讲，一个事物所做的修改对其他的事务是不可见的

• D（Durability）

  • 持久性

    • 事务一旦提交，事务对数据做的修改永久保存到数据库中

4.数据库隔离级别

• 读未提交(Read Uncommitted)（产生脏读（脏数据））

  • 在事物（一组sql运行）中可以查看到其他事务还未提交的修改

• 读已提交（Read Committed）（产生不可重复读（一个事务里多次对同一行数据进行读取，但是读取到的结果是不一样的）但是解决了脏读）

  • 一个事务可以看到其他事务提交后的修改，但在该事务提交之前，所做的修改对其他事务不可见

• 可重复读（Repeatable Read）（解决了不可重复读，但是无法解决幻读，幻读指A事务读取某个范围内（多行）记录时，其他事务又在该范围内插入了新的数据，A事务再次读取该范围时产生了幻行）

  • MySQL 默认

    • 一个事务中对同一行数据进行多次读，读到的结果是一样的

• 串行化（Serialization）

  • 最高级别，强制所有事务按顺序执行，解决了幻读，但是效率低下

• 查看隔离级别SELECT @@transaction_isolation;

面试题 

1.解释数据库事务四大特征

• A（Atomic）

  • 原子性

    • 不可分割，不可能只执行其中一部分

• C（Consistency）

  • 一致性

    • 数据总是从一种一致性状态转换到另一种一致性状态

    • 主要指的是事务执行后，数据的完整性，数据业务逻辑等要正确，这样才能保证数据转换始终一致

• I（Isolation）

  • 隔离性

    • 通常来讲，一个事物所做的修改对其他的事务是不可见的

• D（Durability）

  • 持久性

    • 事务一旦提交，事务对数据做的修改永久保存到数据库中

2.说出数据库隔离级别以及各自解决了什么问题

 

3.什么是脏读，不可重复读，幻读

 脏读（Dirty Read）是数据库系统中的一种常见问题，它指的是在事务处理过程中，由于某些原因，数据没有被正确地更新或提交，导致其他事务看到的数据不是最新的。

不可重复读（Non-Repeatable Read）是数据库事务隔离级别的问题之一，它发生在多个事务并发执行时，一个事务在读取某些数据后，另一个事务对这些数据进行了修改，然后第一个事务再次读取这些数据时，发现数据发生了变化。 

 幻读（Phantom Read）是数据库事务隔离级别的问题之一，它发生在多个事务并发执行时，一个事务在执行过程中，另一个事务插入或删除了符合当前事务查询条件的行，导致第一个事务在后续的查询中看到了之前未见的行，或者之前看到的行消失了。