443-MySQL(连接查询)

连接查询

表多了，我们有时候要在两个表甚至多个表之间进行查询，一次select涉及到多张表格。
如果你是一张一张表select，一个一个取数据的话，是不好的。
mysql client 发送一条SQL语句到mysql server，mysql server要做很多校验操作。你发2条不同的SQL，首先，在mysql server处理的流程就要走2遍，而且，1次通信就代表1次完整的TCP的3次握手，整个的处理流程返回结果，和TCP的4次挥手，效率就低了，所以有联合查询。

连接查询分为：内连接查询和外连接查询
外连接查询分为：left左连接查询和right右连接查询

我们把2个圆圈看做2张表，我们在做内连接查询的时候，相当于是查两个表的交集。

如果是左连接的话，相当于查询蓝色部分（表1特有的数据）。

如果是右连接的话，相当于查询绿色部分（表2特有的数据）。

场景1

学生-课程-考试结果

我们书写建表语句

然后进行建表

然后添加一些数据

学生id，课程id，时间，考试成绩。


1、我们查询zhangsan同学某门课的成绩


但是显示80这个分数而已，信息太少了。
我们不仅仅想知道分数，还想知道学生的详细信息和课程信息。
涉及到多个表的查询

我们在student里面查zhangsan的数据，然后在exam里面也要查zhangsan的数据，在course查考试成绩的课程名称。

内连接查询

SELECT a.属性名1,a.属性名2,...,b,属性名1,b.属性名2... 
FROM table_name1 a inner join table_name2
b on a.id = b.id where a.属性名 满足某些条件;

解释：

大表的字段建索引最有用的


3表连接查询





有关联字段的表才可以进行连接
在进行多张表连接的话，主表写在前面，然后拿主表连接其他的表。

内连接和limit 的联系

我们打开之前大数据创建的表

如果只选择1个id，这个效率差别就很明显了！

这2个操作告诉我们：在做大量偏移的时候，我们select的多与少也是影响偏移的效率。
我们一般通过where加索引的方式把M性能的消耗去掉。
但有的时候我们没有办法加。只能limitM N
那怎么优化？

如果说，我就是想选择这么多列的数据，但是要它的时间消耗和下面的一样。

怎么解决？

这个方法很好，利用索引去做。但是我们有时候不知道这个id的值啊！
我们只知道要偏移多少而已，只知道要展现那么多列的数据。

创建临时表，只显示10行，但是id是有索引的，都是常量时间！

小表整张表搜索。然后拿小表的id去大表索引搜索。

内连接的SQL执行过程是什么？

首先，它会在内连接的SQL语句中，区分大表和小表（以表里的行数），小表永远都是整张表扫描，然后拿小表连接的字段去大表里搜索，搜索完之后的结果就是内连接的结果。
如果给内连接的SQL语句增加where条件，当然是where条件先执行，因为能不能用到索引是看where后面的字段，和select的字段没有关系，where执行完，这个大表和小表的身份可能对调。然后再去小表整张表扫描，然后拿小表连接的字段去大表里搜索，搜索完之后的结果就是内连接的结果。

外连接查询

左连接查询

SELECT a.属性名列表, b.属性名列表 FROM table_name1 a LEFT [OUTER] 
JOIN table_name2 b on
a.id = b.id;

//把left这边的表所有的数据显示出来，在右表中不存在相应数据，则显示NULL 
select a.* from User a left outer join Orderlist b on a.uid=b.uid 
where a.orderid is null;

右连接查询

SELECT a.属性名列表, b.属性名列表 FROM table_name1 a RIGHT [OUTER] 
JOIN table_name2 b on
a.id = b.id;

//把right这边的表所有的数据显示出来，在左表中不存在相应数据，则显示NULL 
select a.* from User a right outer join Orderlist b on a.uid=b.uid 
where b.orderid is null;

weiwie没有参加考试


a表是小表，b表是大表。因为a是小表，整表扫描，所以实际上没有用到索引。然后在b表是用主键索引搜索

我们现在把b表变成小表：
先执行b表的整张表扫描，没有用到索引，因为是cid，涉及到多列的索引，多列索引必须是先用到第1列，才可以用到这个联合的索引。

下面两种写法结果一样，执行计划也是一样的

相当于是优化翻译为where了，因为用where的好处是where后面的过滤条件可以通过索引进行过滤，效率是相当快的。

外连接示例

左连接

把left这边的表所有的数据显示出来，在右表中不存在相应数据，则显示NULL
因为7这个同学没有创建考试，在b表中没有数据，所以就显示空。

同理 右连接
把right这边的表所有的数据显示出来，在左表中不存在相应数据，则显示NULL

外连接性能分析

右连接是对右表做1个整张表的搜索，然后去左表通过uid去找，找见的显示出来，找不见的显示null
左连接是对左表做1个整张表的搜索，然后去右表通过uid去找，找见的显示出来，找不见的显示null

我们现在想看看哪些人没有参加过考试
（内连接只能查看哪些人参加过考试，因为是取两张表的共同部分。）

方法1：

in是可以用到索引的。
not in基本上是用不到索引，理所当然，因为不在这个列表里，肯定要搜索完才知道不在列表里面。
但是not in能不能用到索引和select的列有关系。
子查询先查询。uid会重复，我们去重。


中间表不一定产生，用explain去查看，而且MySQL会做优化的。

方法2：采用左连接

外连接和内连接的区别

我现在想找没有参加某一门课的同学的信息

我们先看看参加某一门课程的同学

没有参加的就是1,4，7

如果我们这么写

为什么上面的和内连接的结果一样？

这样看起来好像就是左连接了

这才是正确的。
在外连接，条件加到on和where后面是有区别的。

我们来查看查看

这样的话外连接和内连接的效果一模一样，先是用where后面的b.uid=3，把b表过滤了一下，变成了小表了，然后拿b.uid去大表搜索。
如果我们在外连接把这个限制条件加到where，处理的结果和内连接一样。

所以在外连接的时候，要把过滤条件放在连接的on后面！
因为左连接肯定是先搜索a表，左右连接是不区分大小表的，左连接的话肯定是左表要整表搜索，右连接的话肯定是右表要整表搜索。


这是外连接的执行过程
把限制条件写在连接的on后面，where后面只写null值判断就可以了