当前位置：首页 > news >正文

MySQL：复杂查询(二)——联合查询02

news 来源：原创 2024/9/20 8:43:13

本篇博客接上篇，上篇已讲联合查询部分知识：MySQL：复杂查询(一)——聚合函数&分组查询&联合查询01-CSDN博客

1、联合查询

1.1 外连接

1.1.1 右外连接 RIGHT JOIN

1.1.2 左外连接 LEFT JOIN

1.2 自连接

1.3 子查询

1.3.1 单行子查询

1.3.2 多行子查询 [NOT] IN

1.3.3 多列包含

1.3.4 [NOT] EXISTS关键字

1.3.5 from子句使用子查询

1.4 合并查询

1.4.1 UNION

1.4.2 UNION ALL

1.4.3 UNION和UNION ALL的区别

2、综合练习

2.1 问题一（外连接）

2.2 问题二（自连接）

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;