一、内连接查询
先创建表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号课程的成绩
- 先查询在学生表中查询zahngsan的详细信息：
select uid, name, age, sex from student where uid=1;
- 然后再考试表中查询zhangsan同学的考试信息：
select time, score from exam where uid=1 and cid=2;
- 内连接合并两次查询的结果
由于是查询学生表和考试表公共的部分，索引使用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;
- 上述查询还看不见课程名，我们再次使用内连接，连接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; -- 过滤条件
- 查询每门课高于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; -- 排序
- 查询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;
- 查询cid=2这门课程的平均成绩
select avg(score) from exam where cid=2;
- 每门课程的详细信息以及平均成绩
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 |
