MySQL多表查询

一、多表设计

• 外键
  • 外键就是关联两张表结构的一个约束，俗称外键约束，一个表的外键是另一张表的主键

创建外键

1.1 一对一

所谓的一对一关系，是指两个表之间对应的关系，比如一个人，对应一张身份证；反过来一张身份证也只能对应一个人

• 在一对一关系中，我们要让两张表产生联系，就要在任意一张表中添加外键以关联表结构

1.2 一对多

一对多的关系在我们生活中十分的常见，其本质就是两个表中，一个表中的字段对应另一张表中的多个字段，如我们平常班级里的一个班主任管理班级中的多个学生，但一个学生只服从一个班主任的管理，这就是一对多的关系

• 在一对多的关系中，我们要让两张表产生关系，就要在多的字段那张表添加外键约束，比如在学生和班主任这张表中，一个班主任对应多个学生，我们就可以在学生表中添加外键关联班主任表中的主键

1.3 多对多

多对多关系就是表1中的一条数据对应着多条表2中的数据，反过来，表2中的一条数据也对应着表1中的多条数据，这样的关系我们就称为多对多关系，例如，在一个班级中，一个学生可能会上很多个任课老师的课，如英语老师、数学老师、语文老师等等，那么反过来，一个任课老师，如语文老师，也会给班级中的学生1、学生2、学生3等学生上课

• 在多对多的关系中，我们如果想要让两张表产生关联，光靠在其中一张表中添加外键关联是不够的，我们需要建立一张中间表，在中间表中添加两个外键约束，分别关联两张表中的主键，这样我们就可以通过中间表来查询两张表所对应的关系了

二、多表查询

下面是表连接所用到的表结构

2.1 内连接

• 笛卡尔积
• 笛卡尔积就是两种表进行连接的时候，两张表的所有数据相互相乘，导致会出现许多多余的数据

• 内连接就是通过把两张表进行连接，并通过条件去除表中的多余数据

类型	格式
隐式内连接	select * from 表1,表2 where xxx = xxx
显式内连接	select * from 表1 [inner] join 表2 on xxx = xxx

# 隐式内连接
select *
from student,
     main_teacher
where student.main_teacher_id = main_teacher.id;

# 显式内连接
select *
from student
         inner join main_teacher on student.main_teacher_id = main_teacher.id;

2.2 外连接

外连接同样会去除笛卡尔积多余的数据，但在外连接中，有主表和从表，并且主表中的数据一定会查询出来，因为在内连接中，如果查询的条件字段有为null，那么这条数据就不会被查询出来

• 格式

类型	格式
左外连接	select * from 主表 left join 从表 on xxx = xxx
右外连接	select * from 从表 right join 主表 on xxx = xxx

# 左外连接
select *
from student
         left join main_teacher on student.main_teacher_id = main_teacher.id;

# 右外连接
select *
from main_teacher
         right join student on main_teacher.id = student.main_teacher_id;

2.3 子查询

下面是子查询所用到的表结构

• 标量子查询

标量子查询就是子查询返回的结果是单个值

类似我现在要找一个名字为刘备的班主任管理的学生信息，我们会分两步来做

1. 在班主任表中先找出名字为刘备的班主任id
2. 在学生表中查询班主任id为先前找到id的信息

# 1、在班主任表中先找出名字为刘备的班主任id
select id from main_teacher where name = '刘备';

# 2、在学生表中查询班主任id为先前找到id的信息
select * from student where main_teacher_id = 1;

然而现在标量子查询我们可以把查询到的结果当做一个值来使用，这样就实现了SQL语句的嵌套，不用把SQL语句写成两句，这样的查询语句简单而优雅

# 标量子查询
select *
from student
where main_teacher_id in (select id from main_teacher where name = '刘备');

• 列子查询
• 字段 in (值1,值2....) ==> (字段 = 值1 or 字段 = 值2 ....)

列子查询返回的是一列数据

类似先要我们现在要找班主任名字为孙权和曹操所管理的学生信息，这时候我们子查询返回的就是一行数据

1. 在班主任表中先找出名字为孙权和曹操的班主任id列表
2. 在学生表中查询班主任id为先前找到id列表的信息

#  1. 在班主任表中先找出名字为孙权和曹操的班主任id列表
select id from main_teacher where name = '孙权' or name = '曹操';

#  2. 在学生表中查询班主任id为先前找到id列表的信息
select * from student where main_teacher_id = 2 or main_teacher_id = 3;

使用行子查询，我们可以把查询到的行数据当成普通数据来使用

# 行子查询
select *
from student
where main_teacher_id 
in (select id from main_teacher where name = '孙权' or name = '曹操');

• 行子查询

行子查询就是查询到的结果为一行，类似如下

类似我们现在要找学生表中归属于诸葛亮管理的学生信息【此查询是为了演示行子查询而编写】

1. 在学生表找到id为4的班主任所管理的信息

select *
from student
where (name, main_teacher_id) =
(select name, main_teacher_id from student where main_teacher_id = 4);

• 表子查询

子查询返回的结果是多行多列，我们可以直接当做一个表来使用

类似现在要找名字为曹操和孙权两位班主任管理的学生信息及班主任信息

1. 先在班主任表中找到名字为曹操和孙权两位班主任的信息
2. 通过学生表和刚才找到的表信息进行连接查询信息

select *
from student,
     (select * from main_teacher where name in ('曹操', '孙权')) temp
where student.main_teacher_id = temp.id;

三、事务

事务是一组操作的集合，它是一个不可分割的操作，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

• 案例
• 假设学生表和班主任表中还没有外键约束，我们通过物理外键的形式关联两张表，当班主任表中有一位班主任被删除了，那么这位班主任所管理的学生也要被同时删除，这就是物理外键的约束

没有开启事务的时候，每一条SQL语句都是一个单独的事务，所以我们在上面同时执行的SQL语句，就算在中间发生了异常，但在第一句的删除班主任的SQL语句作为一个单独的事务，执行完SQL语句后就直接提交，数据库中的数据永久发生改变，等执行第二句异常的SQL语句后，第三句SQL语句无法执行，这就造成了无法同时删除班主任表和学生表中的数据

开启事务我们就可以实现SQL语句的同时成功或者同时失败

• 事务的流程

1. 使用start transaction / begin 开启事务
2. 操作结束后提交事务，在事务没有提交之前不会sql语句的操作不会影响数据的数据变动
3. 如果操作中有一个不成功的操作，我们可以用rollback来恢复数据

以上需求SQL语句可以优化为

# 开启事务
start transaction ;

# 删除班主任表中的诸葛亮
delete
from main_teacher
where id = 4;

delete from xxx where id = 1;

# 同时删除诸葛亮管理的学生孟获
delete
from student
where main_teacher_id = 4;

# 根据情况提交事务
commit ;

# 根据情况数据回滚
rollback ;

• 事务的四大特性

特性	描述
原子性	事务是不可分割的最小单元，要么全部成功，要么全部失败
一致性	事务完成时，必须是所有的数据都保持一致状态
隔离性	数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性	事务一但提交或者回滚，它对数据库中的数据的改变就是永久性的

四、索引

索引就是帮助数据库高效获取数据的数据结构，下面是查询一条数据库中有三百万条数据的数据，如果不用索引来查花费的时间非常多，而通过主键id索引查询速度优化了很多

• 优点

1. 提高数据查询的效率，降低数据库IO成本
2. 通过索引对数据进行排序，降低数据排序的成本，降低CPU消耗

• 缺点

1. 索引会占用存储空间
2. 索引大大提高了查询效率，但同时也降低了增删改的效率

• 格式

类型	格式
创建索引	create [unique] index 索引名 on 表名 (字段名)
查看索引	show index from 表名
删除表名	drop index 索引名 on 表名

• 注意事项

1. 创建表并定义主键的时候，数据库会把这个主键变成索引，而且这个索引是所有索引中速度最快的
2. 创建表并给字段添加unique约束时，数据库会把这个字段也变成索引
3. MySQL数据库中的默认的索引结构是B+tree【了解】