MySQL联合查询

一、内连接查询

先创建表student、course、exam

CREATE TABLE student(
	uid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	age TINYINT UNSIGNED NOT NULL,
	sex enum("man", "woman") NOT NULL
);

CREATE TABLE course(
	cid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	credit TINYINT UNSIGNED NOT NULL
);

CREATE TABLE exam(
	uid INT UNSIGNED NOT NULL,
	cid INT UNSIGNED NOT NULL,
	time DATE NOT NULL,
	score FLOAT NOT NULL,
	PRIMARY KEY(uid, cid)
);

insert into student(name, age, sex) values('zhangsan',18,'man'),('gaoyang',20,'woman'),('chenwei',22,'man'),('linfeng',21,'woman'),('liuxiang',19,'man');
insert into course(cname, credit) values('C++基础课程', 5),('C++高级课程', 10),('C++项目开发', 8),('C++算法课程', 12);
insert into exam(uid,cid,time,score) values(1,1,20210409,99),(1,2,20210410,80),(2,2,20210410,90),(2,3,20210412,99),(3,1,20210409,56),(3,2,20210410,93),(3,3,20210412,89),(3,4,20210411,100),(4,4,20210411,99),(5,2,20210410,59),(5,3,20210412,94),(5,4,20210411,95);

我们现在想查询1号同学的个人信息以及他的2号课程的成绩

1. 先查询在学生表中查询zahngsan的详细信息：

select uid, name, age, sex from student where uid=1;

2. 然后再考试表中查询zhangsan同学的考试信息：

select time, score from exam where uid=1 and cid=2;

3. 内连接合并两次查询的结果

由于是查询学生表和考试表公共的部分，索引使用inner join

select stu.uid, stu.name, stu.age, stu.sex, ex.time, ex.score from student as stu 
inner join exam as ex 
on stu.uid=ex.uid 
where ex.uid=1 and ex.cid=2;

4. 上述查询还看不见课程名，我们再次使用内连接，连接3张表，查询带有课程名的信息

select stu.uid, stu.name, stu.age, stu.sex, ex.time, ex.score, co.cname from exam as ex -- 第一个放用于连接的中间表，即和另外两张表有公共部分的表
inner join student as stu on ex.uid=stu.uid -- 连接考试表和学生表
inner join course as co on ex.cid=co.cid    -- 连接考试表和课程表
where ex.uid=1 and ex.cid=2;                -- 过滤条件

5. 查询每门课高于90分的有多少个人

select course.cid, course.cname, count(*) cnt
from course
join exam on exam.cid=course.cid  -- 连接
where exam.score > 90.0           -- 条件过滤
group by exam.cid                 -- 分组
order by cnt desc;                -- 排序

6. 查询cid=2这门课程考最高分的学生信息和课程信息

select co.cname, co.credit, stu.name, ex.score
from exam ex
inner join course co on ex.cid=co.cid
inner join student stu on ex.uid=stu.uid
where ex.cid=2
order by ex.score desc
limit 1;

7. 查询cid=2这门课程的平均成绩

select avg(score) from exam where cid=2;

8. 每门课程的详细信息以及平均成绩

select co.cname, co.credit, avg(score) from course co
inner join exam ex
on co.cid=ex.cid
group by ex.cid;

在MySQL库表操作以及简单查询语句中有提到，可以使用select属性的数量也会影响查询的速度，也可以使用条件过滤where <带有索引的属性>加快查询，现在我们介绍使用内连接优化查询

能不能在查询多个属性的情况下，还能花费较少的时间呢？

内连接优化查询

优化原理：由于生成小表（临时表）的时候使用了带有索引的属性id，故生成小表很快，接着用小表的数据在大表t_user里面匹配id，也使用了索引，故能加快查询

select a.id, a.email, a.password from
t_user inner join (select id from t_user limit 1500000, 10) b
on a.id=b.id

二、详细解释内连接的查询过程

数据库引擎如何按照 on a.uid=b.uid 进行表合并的？

先根据数据量确定大表和小表，小表永远是整表扫描，拿着小表的uid去大表搜索uid。很明显，由于小表永远是整表扫描，无需使用索引，我们一般给大表建索引加快搜索。

对于inner join而言，假设一开始A表是大表，B表是小表，数据库引擎拿着B表的所有数据去A表做匹配的时候，发现SQL语句还有where，这时候就需要进行数据过滤，过滤出满足条件的数据。此时可能由于A表满足条件的数据比B表满足条件的数据还少，这是A表满足条件的数据形成的表成了小表，B表满足条件的数据形成的表成了大表。总结下来就是先用where进行数据过滤，在用小表的数据去大表匹配满足on条件的数据

对于inner join，where的子条件放在on后面，效果和效率是一样的。因为MySQL引擎会把on后面的条件优化为where，where是可以使用索引的，效率高。

三、左、右连接

外连接不区分大小表，只有inner join区分大小表

// left join把左表的所有数据显示出来，若右表不存在，则显示为NULL
select * from student left join exam on student.uid=exam.uid;

// right join把右表的所有数据显示出来，若左表不存在，则显示为NULL
select * from student right join exam on student.uid=exam.uid;

内连接区分大小表，外连接不区分大小表。对于左右连接而言，都有一个表需要整表搜索

应用场景1：查看没有参加考试的同学

-- select distinct uid from exam 会产生一张中间表供外面的SQL查询
-- not in对索引的命中并不高，一般情况下都是整表扫描
select * from student where uid not in (select distinct uid from exam);

-- 使用外连接，只显示课程号为null的学生信息
select a.* from student a left join exam b on a.uid=b.uid where b.cid is null;

在一些没有出现的场景的时候经常使用外连接，比如没有出现在任何订单的商品等

应用场景2：查看没有参加3号课程考试的同学

-- 查看参加了3号课程的人，用where过滤后，再决定大小表关系，大表整表扫描
select a.* from student a inner join exam b on a.uid=b.uid where b.cid=3;

-- 看起来是left join，其实变成了inner join，查询过程和inner join一样
select a.* from student a left join exam b on a.uid=b.uid where b.cid=3;

on后面写连接条件，where后面写过滤条件

-- 查看参加了考试同学的信息，未参加3号课程考试的则用NULL填充
select a.*, b.* from student a left join exam b on a.uid=b.uid and b.cid=3;

-- 查看没有参加3号课程考试的同学
select a.*, b.* from student a left join exam b on a.uid=b.uid and b.cid=3 where b.cid is null;

左连接：数据量和左表相同，无法连接的部分用null填充

select * from Customers left join Orders on Customers.Id = Orders.CustomerId;

id	name	id	customerId
1	“Joe”	2	1
2	“Henry”	null	null
3	“Sam”	1	3
4	“Max”	null	null

内连接

select * from Customers join Orders on Customers.Id = Orders.CustomerId;

id	name	id	customerId
1	“Joe”	2	1
3	“Sam”	1	3

右连接：数据量和右表相同，无法连接的部分用null填充

select * from Customers right join Orders on Customers.Id = Orders.CustomerId;

id	name	id	customerId
3	“Sam”	1	3
1	“Joe”	2	1