本篇博客接上篇,上篇已讲联合查询部分知识:MySQL:复杂查询(一)——聚合函数&分组查询&联合查询01-CSDN博客
目录
1、联合查询
1.1 外连接
外连接分为左外连接和右外连接。
在联合查询中,左侧表完全显示称为左外连接;右侧表完全显示称为右外连接。
使用外连接需要使用 join...on 的语法形式:
join相当于联合表,on来设置连接条件。
注意:
- ON子句中的条件仅影响连接操作本身,决定哪些行参与连接。在外连接中,不满足ON子句条件的行可能会被包括在结果集中。
- 使用ON设置连接条件后,再使用where进行结果集的过滤。
当right在join左边时,为右外连接;当left在join左边时,为左外连接。
1.1.1 右外连接 RIGHT JOIN
右外连接就是以join右边的表为基准,使右侧表的信息全部展现出来,不管有没有与之匹配的数据都会展现,若左表中没有与之匹配的信息则用null来填充。
下图所示,没有一个学生的班级是java66班的:
此时,我们就可以通过右外连接来使右侧班级表中的信息全部展现出来:
1.1.2 左外连接 LEFT JOIN
左外连接就是以join左边的表为基准,使左侧表的信息全部展现出来,不管有没有与之匹配的数据都会展现,若右表中没有与之匹配的信息则用null来填充。
下图所示,没有一个同学的班级编号为100,而并没有编号为100的班级:
若使用普通的联合查询则该同学的信息就不会被展现,
此时我们就可以使用左外连接展现出所有的同学以及其班级信息:
注意:在MySQL中不支持全外连接 FULL JOIN
1.2 自连接
自连接:即自己与自己进行表连接。
自连接的作用就是将行转化为列,在查询时可以使用where条件进行过滤,最终实现行与行之间的比较功能。
如下图表的设计,可在一行中进行列之间的比较:
但在下图中,同一个学生的成绩在不同的行中,我们无法做到行与行之间的比较:
而通过自连接,就可以将不同行的数据转化进同一行中,这样就可以进行比较了。
对自己进行表连接时,因为是自己与自己连接,要避免表名相同,所以我们要给两张表起不同的别名。
接下来,我们就可以根据实际情况设置where条件来达到查询目的。
1.3 子查询
子查询也叫做嵌套查询,就是将一条SQL语句的查询结果当做另一条SQL语句的查询条件,可以嵌套很多层。
由于子查询的嵌套没有限制,所以工作中要谨慎使用。
注意:外层查询条件的列一定要与内层查询列表的列相匹配!
1.3.1 单行子查询
单行子查询:返回一行记录的子查询
可以理解为,只返回一个对象。
例如:查询 '许仙' 的同班同学:
1.3.2 多行子查询 [NOT] IN
多行子查询:返回多行记录的子查询
可以理解为,返回的是一个集合,集合中有多个对象。
例如:查询“语文”或“英文”课程的成绩信息(使用IN)
例如:查询非“语文”或“英文”课程的成绩信息(使用NOT IN)
1.3.3 多列包含
多列包含是指在where条件中包含了多个列。
例如:查询每个同学出现重复的成绩(学生相同、课程相同、成绩相同视为重复)
如下图,表中包含了三组重复的成绩:
我们可以根据以下思路写出代码:
- 重复的成绩student_id、course_id、score均相等,故我们可以以这三个列来分组
- 分组后在having子句中使用聚合函数count()判断每组中记录的条数,若>1,则成绩重复
我们可是先写出内层查询的代码:
-- 内层查询
SELECT
student_id,course_id,score,COUNT(*)
FROM
score
GROUP BY
student_id,course_id,score
HAVING
COUNT(*) > 1;
接着,我们可以添加外层查询来实现多列包含
-- 查询重复的成绩
-- 添加外层查询 实现多列包含
SELECT
*
FROM
score
WHERE
(student_id,course_id,score)
IN (SELECT
student_id,course_id,score
FROM
score
GROUP BY
student_id,course_id,score
HAVING
COUNT(*) > 1);
1.3.4 [NOT] EXISTS关键字
exists就相当于一个if判断语句,内层查询出的为非空集合返回true,为空集合返回false,为true则执行外层查询,为false则不执行外层查询。
注意:select null返回的是非空集合,只不过数值为null:
1.3.5 from子句使用子查询
在from子句中使用子查询:子查询语句出现在from子句中。这里要用到数据查询的技巧,把一个 子查询当做一个临时表使用,可以使用select返回的临时表和真实的表实现表连接。
例如:查询所有比“中文系2019级3班”平均分高的成绩信息:
- 成绩表中只有学生编号,没有班级编号,要将成绩表和班级表建立联系需要借助学生表
- 三表联合,通过where给出条件利用avg计算出“中文系2019级3班”的平均分
- 再将上面所得临时表与成绩表实现表连接,过滤得到高于平均分的成绩
-- 查询所有比“中文系2019级3班”平均分高的成绩信息:
SELECT * FROM
score sc,
(SELECT
avg(sc.score) score
FROM
score sc,student st,class c
WHERE
sc.student_id = st.student_id AND
st.class_id = c.class_id AND
c.`name` = '中文系2019级3班') tmp
WHERE
sc.score > tmp.score;
1.4 合并查询
合并查询:合并多个查询结果到一个结果集中。
union和union all可以进行合并查询。
使用合并查询时,因为是将结果合并到一个结果集中,所以前后查询的结果集中,字段需要一致。
合并查询在大多数情况下用于多表合并,单表合并其实就相当于OR的作用。
1.4.1 UNION
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。
因为前后查询的结构集要求相同,我们先使用like关键字创建出一个与学生表结构相同的副本表(只是复制结构,并没有导入数据),插入测试数据,使用union将两表联合,观察结果:
1.4.2 UNION ALL
该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行。
union all依然可用于合并两个结果集。
1.4.3 UNION和UNION ALL的区别
union all与union的唯一区别就是union all不会去掉结果集中的重复行。
使用union合并(重复记录行被去除):
使用union all合并(重复记录行没有去除):
注意:
不管使用union还是union all进行合并,都要保证两个结果集中的字段是一致的,否则合并后为错误的结结果集!
2、综合练习
本题与上篇博客综合练习所用表相同。
2.1 问题一(外连接)
题目:查询哪位同学没有考试成绩
这里注意on和where使用的先后顺序及各自作用:
- 使用left join左外连接(使得没有成绩的学生也被展现)
- 先使用ON设置连接条件,使成绩和学生相对应
- 再使用where过滤出没有成绩的学生
2.2 问题二 (自连接)
题目:显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
- 首先查出两门课程的课程编号
- 接着使用自连接并where设置好连接条件以及过滤条件
标签:02,1.3,union,连接,score,MySQL,查询,id From: https://blog.csdn.net/2401_83595513/article/details/141128432