首页 > 数据库 >MySQL:复杂查询(二)——联合查询02

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

时间:2024-08-13 20:24:20浏览次数:17  
标签:02 1.3 union 连接 score MySQL 查询 id

本篇博客接上篇,上篇已讲联合查询部分知识: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条件中包含了多个列。

例如:查询每个同学出现重复的成绩(学生相同、课程相同、成绩相同视为重复)

如下图,表中包含了三组重复的成绩:

我们可以根据以下思路写出代码:

  1. 重复的成绩student_id、course_id、score均相等,故我们可以以这三个列来分组
  2. 分组后在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班”平均分高的成绩信息:

  1. 成绩表中只有学生编号,没有班级编号,要将成绩表和班级表建立联系需要借助学生表
  2. 三表联合,通过where给出条件利用avg计算出“中文系2019级3班”的平均分
  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 UNIONUNION ALL的区别

union all与union的唯一区别就是union all不会去掉结果集中的重复行。

使用union合并(重复记录行被去除):

使用union all合并(重复记录行没有去除):

注意:

不管使用union还是union all进行合并,都要保证两个结果集中的字段是一致的,否则合并后为错误的结结果集!

2、综合练习

本题与上篇博客综合练习所用表相同。

2.1 问题一(外连接)

题目:查询哪位同学没有考试成绩

这里注意on和where使用的先后顺序及各自作用:

  1. 使用left join左外连接(使得没有成绩的学生也被展现)
  2. 先使用ON设置连接条件,使成绩和学生相对应
  3. 再使用where过滤出没有成绩的学生

2.2 问题二 (自连接)

题目:显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

  1. 首先查出两门课程的课程编号
  2. 接着使用自连接并where设置好连接条件以及过滤条件

 

标签:02,1.3,union,连接,score,MySQL,查询,id
From: https://blog.csdn.net/2401_83595513/article/details/141128432

相关文章

  • 如何购买并激活CleanMyMac X?2025最新终极购买指南!
    在数字时代,保持你的Mac电脑运行速度和效率至关重要。CleanMyMacX是市面上一款非常受欢迎的Mac优化和清洁工具,针对近期特别多Mac圈好友在咨询如何正规途径购买CleanmymacX?如何激活CleanmymacX?如何获取CleanmymacX激活码等问题。特制作这个2024CleanmymacX终极购买指南,详......
  • P7561 [JOISC 2021 Day2] 道路の建設案 (Road Construction)
    这篇文章主要讲一下问什么要二分以后还要check(l-1),以及怎么找距离小于等于\(k\)的边的数量。题目给定\(n\)个点,求出任意两个点的曼哈顿距离的集合的前\(k\)大。思路我们先将曼哈顿距离转化为切比雪夫距离:我们知道形如\((x,y)\)点之间的曼哈顿距离等于\((x+y,......
  • .NET周刊【8月第1期 2024-08-04】
    国内文章EFCore性能优化技巧https://www.cnblogs.com/baibaomen-org/p/18338447这篇文章介绍了在代码层面上优化EFCore实例池和拆分查询的方法。首先,文章建议使用DbContext实例池来重复利用实例,避免资源浪费,并提供相关使用示例。其次,文章讨论了笛尔卡乘积对复杂查询性能的影......
  • 【专题】2024无人驾驶网约车乘坐意愿调查报告合集PDF分享(附原数据表)
     原文链接:https://tecdat.cn/?p=37335 科技迅猛发展,无人驾驶技术从科幻走进现实,2024年无人驾驶网约车成热议话题。阅读原文,获取专题报告合集全文,解锁文末208份无人驾驶网约车相关行业研究报告。报告表明,近60%受访者期待,00后更积极,80后较谨慎。性别上男性更乐观,城市级别......
  • 尚硅谷MYSQL(第七章)
    从函数定义的角度出发,我们可以将函数分成内置函数和自定义函数。在SQL语言中,同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数,而自定义函数是我们根据自己的需要编写的里面有因子的话因子相同 随机数相同四舍五入中......
  • ChatGPT 大模型核心算法深度分析 2024
    在分析核心算法之前,我们先了解chatGPT相关技术发展进程首先介绍自然语言处理、大规模预训练语言模型以及ChatGPT技术的发展历程,接着就ChatGPT的技术优点和不足进行分析,然后讨论核心算法。1.1自然语言处理的发展历史人类语言(又称自然语言)具有无处不在的歧义性、高度......
  • 2024牛客暑期多校训练营9
    Preface久违的多校,又被徐神带飞力这场总体可做题挺多的,前期出题也还算稳,但中间祁神写H睿智错误频发直接红温爆交了7发但无所谓徐神会出手,上机把当时只过了两个队的G秒了,然后我爬上去把B,C写了然后对着D罚坐一整场赛后经典看不懂出题人的一句话题解,坐等视频讲解吧(虽......
  • 《SQL 中复杂条件多表关联查询的性能优化秘籍》
    在当今数据驱动的时代,数据库的操作和查询性能对于企业的业务运营至关重要。当面对复杂的业务逻辑和大规模的数据时,实现复杂条件的多表关联查询并确保高效的性能成为了数据库开发者和管理员面临的重要挑战。多表关联查询是在关系型数据库中获取全面和准确数据的常见操作。然......
  • DRM:清华提出无偏差的新类发现与定位新方法 | CVPR 2024
    论文分析了现有的新类别发现和定位(NCDL)方法并确定了核心问题:目标检测器往往偏向已知的目标,忽略未知的目标。为了解决这个问题,论文提出了去偏差区域挖掘(DRM)方法,以互补的方式结合类无关RPN和类感知RPN进行目标定位,利用未标记数据的半监督对比学习来改进表征网络,以及采用简单高效的m......
  • 2024亚太杯数学建模b题基于机器学习回归的洪水预测模型研究
    本届亚太杯中文赛项已经结束,本文分享我的解决思路。摘 要洪水的频率和严重程度与人口增长趋势相近。迅猛的人口增长,扩大耕地,围湖造田,乱砍滥伐等人为破坏不断地改变着地表状态,改变了汇流条件,加剧了洪灾程度。2023年,全球洪水造成了数十亿美元的经济损失。因此构建与研究洪水......