MySQL联合查询

MySQL联合查询

文章目录

​​一、内连接查询​​​​二、详细解释内连接的查询过程​​​​三、左、右连接​​

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

一、内连接查询

先创建表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));

我们现在想查询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-ame 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-ame, count(*) cntfrom coursejoin exam on exam.cid=course.cid -- 连接where exam.score > 90.0 -- 条件过滤group by exam.cid -- 分组order by cnt desc; -- 排序

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

select co-ame, co.credit, stu.name, ex.scorefrom exam exinner join course co on ex.cid=co.cidinner join student stu on ex.uid=stu.uidwhere ex.cid=2order by ex.score desclimit 1;

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

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

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

select co-ame, co.credit, avg(score) from course coinner join exam exon co.cid=ex.cidgroup by ex.cid;

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

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

内连接优化查询

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

select a.id, a.email, a.password fromt_user inner join (select id from t_user limit 1500000, 10) bon 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是可以使用索引的，效率高。

三、左、右连接

// left join把左表的所有数据显示出来，若右表不存在，则显示为NULLselect * from student left join exam on student.uid=exam.uid;// right join把右表的所有数据显示出来，若左表不存在，则显示为NULLselect * 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号课程考试的同学

select a.* from student a inner join exam b on a.uid=b.uid where b.cid=3;-- 看起来是left join，其实变成了inner joinselect a.* from student a left join exam b on a.uid=b.uid where b.cid=3;

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

select a.* from student a left join exam b on a.uid=b.uid and b.cid=3 where b.cid is null;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。