MYSQL-联合查询
经过上节课，我们学会了如何设计表以及了解到各种范式，这节课就请大家和小L一起来学习设计表之后如何使用
1.为什么要使用联合查询
在数据设计时由于范式的要求，数据被拆分到多个表中，那么要查询⼀个条数据的完整信息，就 要从多个表中获取数据，如下图所⽰：要获取学⽣的基本信息和班级信息就要从学⽣表和班级表中获 取，这时就需要使⽤联合查询，这⾥的联合指的是多个表的组合。
总之就是我们通过范式，将数据分到了很多不同的表里，但是当我们要查询数据时，往往一张表的数据是不够看的，需要联合多张表来得到完整的数据，连接多个表得到数据的过程就叫联合查询
2.联合查询是如何工作的
参与查询的所有表取笛卡尔积，结果集在临时表中
笛卡尔积：相当于表1的所有数据遍历一遍表二的数据，下面我们假设有一个学生信息表和成绩表
SELECT * FROM student;SELECT * FROM score;SELECT student.*,s.score FROM student, score s WHERE student.`name` = '唐三藏';
由于学生信息有点多，我们先只看唐三藏的
如图所示，唐三藏 将所有成绩都打印了一遍，这就叫做笛卡尔积
3.内查询
使用场景：需要将多个表连接，并提取出多个表内有交集的数据
语法：
1 select 字段 from 表 1 别名 1, 表 2 别名 2 where 连接条件 and 其他条件 ;
2 select 字段 from 表 1 别名 1 [inner] join 表 2 别名 2 on 连接条件 where 其他条件 ;
例：查询唐三藏的成绩
select s.name, sco.score from student s, score sco where s.`name` = '唐三藏' and s.id = sco.student_id;
4.外查询
使用场景：需要多个表连接，但是要记录所有的数据，即使没有交集也要记录下来
外连接分为左外连接、右外连接和全外连接三种类型，MySQL不⽀持全外连接。
• 左外连接：返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对 应字段会显⽰为NULL。
• 右外连接：与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记 录，则结果集中对应字段会显⽰为NULL。
• 全外连接：结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某⼀边表中没有匹 配的记录，则结果集中对应字段会显⽰为NULL。
左外连接和 右外连接的区别是基表的位置，左外查询基表在左边，右外查洵基表在右边
语法：
-- 左外连接，表 1 完全显⽰
select 字段名 from 表名 1 left join 表名 2 on 连接条件；
-- 右外连接，表 2 完全显⽰
select 字段 from 表名 1 right join 表名 2 on 连接条件 ;
例：查询没来考试的学生（成绩为null的）
假设我们用内连接
SELECT * FROM student s INNER JOIN score sco on s.id = sco.student_id;
发现并没有“不想毕业”这位同学
当我们使用左外连接时
select * from student s left join score sco on s.id = sco.student_id;
然后我们用where语句限制score条件即可得到成绩为空的同学
select * from student s left join score sco on s.id = sco.student_id where sco.score is null; 
5自查询
当我们想要对一行的数据进行操作时就会用到自查询的操作，比如说比较一行数据中的各科成绩，找到一行数据中的相似点等等
语法
-- select 字段名 from 表1 代号1，表1 代号2 where 条件1 and 条件2；
示例：显式所有"MySQL"成绩比"JAVA"成绩高的 学生信息 和 班级 以及 成绩信息
SELECT stu.`name` , cl.`name` AS '班级', s1.score AS 'Java成绩', s2.score as 'MYSQL成绩' FROM
student stu,
class cl,
score s1,
score s2,
course c1,
course c2 WHERE
s2.student_id = s1.student_id -- 确保是同一行
and stu.id = s2.student_id -- 为确定打印名字
and stu.class_id = cl.id -- 确定班级
and c1.`name` = 'Java'
and c2.`name` = 'MYSQL'
and s1.course_id = c1.id -- 找到不同课程
and s2.course_id = c2.id
and s2.score > s1.score; -- 比较分数
6.子查询
相当于select里存select
1.单行子查询
select 字段 from 表 where 字段(1个) = （select 相同字段 from 表 where 条件）and 条件
先执行内部的一个字段sql语句在将语句的结果传给外部来给外部当条件，相当于嵌套
示例：查询不想毕业的同学
select * from student s where class_id = (SELECT class_id from student s where s.`name` = '不想毕业') and s.`name` != '不想毕业';
2.多行子查询
先执行内部的多个字段sql语句在将语句传给外部
select 字段 from 表 where 字段(1个) = （select 相同字段（多个） from 表 where 条件）and 条件
示例：查询"MYSQL"和"Java"的成绩
select * from score s where course_id IN (select course_id FROM score where course_id = 1 or course_id = 3 ); 
3.多列子查询
select 字段 from 表 where 字段(多个) = （select 相同字段（多个） from 表 where 条件）and 条件
返回多个列，外层列要与内层列匹配
示例：查询重复录入的数据
思路：1.首先找到重复录入的数据
2.要找到重复的数据首先得对数据分组，
3.对分完组的数据记录数据次数 count()
4.由于记录重复数据需要多个字段，所以用到多列
SELECT * FROM score where (score.course_id , score.student_id , score.score)
IN (SELECT score.course_id , score.student_id , score.score FROM score
GROUP BY score.course_id , score.student_id , score.score HAVING COUNT(*) > 1);
在from中使用子查询
MYSQL当查询出结果时，会将结果存储在临时表内，我们可以利用临时表来进行操作
示例：算出比java001班的平均分高的学生
思路：1.先算出平均分
SELECT AVG(sco.score) FROM score sco, class c, student s
where c.`name` = 'Java001班' and sco.student_id = s.id and s.class_id = c.id; -- 首先得到平均值 
2.再将算出平均分的临时表，将临时表命名（方便后面使用）
3.然后再利用嵌套
select * FROM (SELECT AVG(sco.score) AS temp_avg FROM score sco, class c, student s
where c.`name` = 'Java001班' and sco.student_id = s.id and s.class_id = c.id) as temp, score sco WHERE sco.score > temp.temp_avg;
7.合并查询
可以将多个select结果合并，得到一个结果集
使用场景：如果查询来自多个表，推荐使用
union去重合并
select * FROM student s WHERE s.id < 3 UNION SELECT * FROM student s WHERE s.id = 8; 
union all 纯合并
select * FROM student s WHERE s.id < 3 UNION ALL SELECT * FROM student s WHERE s.id = 8 or s.id = 1;
同学信息是基表，所以同学信息在主导