MySQL5 复合查询

复合查询

  • 1. 连接方式
    • 笛卡尔乘积
    • 内连接 - join 或 inner join
    • 左外连接 - left join
    • 右外连接 - right join
    • 全外连接 - full join
    • 交叉连接 - cross join
    • 自连接
  • 2. 子查询
    • 比较关键字 - all
    • 比较关键字 - any
    • 与空集的比较
  • 3. 联合查询
    • union - 取两个结果的并集,去重
    • union all - 取两个结果的并集,不去重
  • 4. 排名分析函数
    • 并列同名间断 - rank()
      • 语法
      • 示例
    • 严格顺序排名 - row_number()
      • 语法
      • 示例
    • 并列同名不间断 - dense_rank()
      • 语法
      • 示例
      • 与 rank 和 row_number 区别

  • 在 MySQL 中,复合查询是指将多个查询组合在一起以实现更复杂的查询需求的操作
  • 在MySQL中,一切皆表

1. 连接方式

笛卡尔乘积

  • 笛卡尔乘积是指在数学中,两个集合 X 和 Y 的笛卡尔积(Cartesian product),又称直积,表示为 X × Y
  • 假设有两个表 table1 和 table2,table1 中有 3 行数据,table2 中有 4 行数据,那么它们的笛卡尔乘积结果集将包含 3 * 4 = 12 行数据
  • 笛卡尔乘积特点:两张表进行穷举组合,不加过滤条件
  • 注意事项:笛卡尔乘积通常会产生大量不必要的数据,因为其中很多组合可能在实际业务中并无意义。在实际查询中,一般需要通过有效的连接条件来避免产生笛卡尔乘积,以获取有意义的查询结果。
SELECT * FROM table1, table2;
  • 上述查询没有指定连接条件,因此会返回 table1 和 table2 的笛卡尔乘积结果集,包含了所有可能的行组合。

内连接 - join 或 inner join

  • 功能:内连接是最常用的连接方式之一,它返回两个表中满足连接条件的行的组合。只有当两个表中的连接列的值相匹配时,对应的行才会被包含在结果集中。
SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON s.student_id = c.id; # 只有在 students 表中的 student_id 与 courses 表中的 id 相等的行才会被显示
结果:返回学生姓名和他们所选课程的名称

左外连接 - left join

  • 功能:左连接返回左表中的所有行以及右表中满足连接条件的行。如果右表中没有匹配的行,则对应的列将显示为 NULL
SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON s.student_id = c.id;
students 表中的所有学生都会被列出,即使他们没有对应的课程记录。

右外连接 - right join

  • 功能:右连接与左连接类似,但它返回右表中的所有行以及左表中满足连接条件的行。如果左表中没有匹配的行,则对应的列将显示为 NULL
SELECT s.name, c.course_name
FROM students s
RIGHT JOIN courses c ON s.student_id = c.id;
此查询会列出所有课程,即使某些课程没有学生选修。

全外连接 - full join

  • 功能:全连接返回两个表中的所有行,无论是否有匹配的行。如果某一行在另一个表中没有匹配的行,则对应的列将显示为 NULL。
SELECT s.name, c.course_name
FROM students s
FULL JOIN courses c ON s.student_id = c.id;
结果集中将包含所有学生和所有课程的组合,对于没有匹配的情况,相应的列将填充 NULL 值。

交叉连接 - cross join

  • 功能:交叉连接返回两个表的笛卡尔积,即两个表中所有行的组合,不使用任何连接条件。
SELECT * FROM students
CROSS JOIN courses;
等同于:SELECT * FROM student,courses;

自连接

  • 自连接是指在同一张表上进行连接操作,即将表与自身进行连接
  • 注意:在使用自连接时,需要为表指定不同的别名,以便在查询中区分不同的表实例。
SELECT st1.name FROM student st1;
取别名时,直接在表后面空格 + 别名即可。

示例:假设有一个名为 employees 的表,其中包含 id、name、department_id 和 manager_id 等列,要查找与每个员工同部门的其他员工,可以使用自连接来实现。

SELECT e1.name AS employee_name, e2.name AS colleague_name
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id AND e1.id!= e2.id;
  • 将 employees 表别名为 e1 和 e2,通过 department_id 列进行连接,并排除自身连接的情况(即 e1.id!= e2.id),从而获取到每个员工同部门的其他员工的姓名组合。

2. 子查询

  • 一个查询的结果做另一个查询的条件
SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);
  • 内部子查询 (SELECT AVG(age) FROM students) 计算出学生的平均年龄,然后外部查询根据这个平均年龄筛选出年龄大于平均年龄的学生记录。
  • 即:select 出平均年龄 做 另一个 select 的条件

比较关键字 - all

  • ALL:当使用 ALL 时,主查询中的条件将与子查询结果集中的所有值进行比较,只有当条件对于子查询结果集中的所有值都满足时,主查询才会返回相应的行。
SELECT * FROM students
WHERE score > ALL (SELECT min_score FROM grades WHERE grade_level = 'A');
子查询获取了 'A' 等级的最低分,主查询则筛选出成绩高于该最低分的所有学生记录。

比较关键字 - any

  • ANY:与 ALL 不同,当使用 ANY 时,主查询中的条件只需与子查询结果集中的至少一个值满足比较条件,主查询就会返回相应的行。
SELECT * FROM students
WHERE score > ANY (SELECT min_score FROM grades WHERE grade_level = 'B');
只要学生的成绩高于子查询返回的 'B' 等级最低分中的任意一个值,该学生的记录就会被查询出来。

与空集的比较

  • 当子查询返回的结果集为空时,使用 ALL 的条件始终为真,而使用 ANY 的条件始终为假。这是因为不存在任何值可以与主查询中的条件进行比较,对于 ALL 来说,由于没有不满足条件的值,所以条件为真;对于 ANY 来说,由于没有满足条件的值,所以条件为假

3. 联合查询

  • 联合查询用于将多个 SELECT 语句的结果集合并成一个结果集。要求这些 SELECT 语句所查询的列数必须相同并且对应的列的数据类型要兼容。联合查询可以使用 UNION、UNION ALL 等关键字来实现。

union - 取两个结果的并集,去重

SELECT name FROM students
UNION
SELECT name FROM teachers;
查询所有学生的姓名以及所有教师的姓名,并将结果合并在一起,去除重复的行。

union all - 取两个结果的并集,不去重

SELECT name FROM students
UNION ALL
SELECT name FROM teachers;
查询所有学生的姓名以及所有教师的姓名,并将结果合并在一起。

4. 排名分析函数

并列同名间断 - rank()

语法

  • RANK() OVER (ORDER BY column_name [ASC|DESC])
  • 其中,column_name 是用于确定排名顺序的列,ASC 表示升序排列,DESC 表示降序排列,默认为升序。
  • 根据指定列的值对结果集进行排名。但当遇到相同值时,RANK() 会分配相同的排名,且下一个不同值的行的排名会跳过相应的数量,导致排名不连续

示例

  • 假设存在一个名为 students 的表,包含 id、name 和 score 列,要根据学生的成绩对学生进行排名
SELECT id, name, score, RANK() OVER(ORDER BY score DESC) AS ranking
FROM students;
  • 若有两个学生并列第一名,则下一个学生的排名为第三名。

严格顺序排名 - row_number()

语法

  • ROW_NUMBER() OVER (ORDER BY column_name [ASC|DESC])
  • 按照指定列的值对结果集中的行进行严格顺序的排名,会为每一行分配一个连续的排名,即使在排序列上的值相同,也会按照行的顺序依次分配不同的排名不会出现排名相同的情况

示例

  • 假设存在一个名为 students 的表,包含 id、name 和 score 列,要根据学生的成绩对学生进行排名
SELECT id, name, score, ROW_NUMBER() OVER(ORDER BY score DESC) AS ranking
FROM students;
  • 所有学生排名不会相同

并列同名不间断 - dense_rank()

语法

  • DENSE_RANK() OVER(ORDER BY column_name [ASC|DESC])
    其中,column_name 是用于确定排名顺序的列,ASC 表示升序排列,DESC 表示降序排列,默认为升序。
  • 该函数会根据指定列的值对结果集中的行进行排名。如果有多个行在排序列上的值相同,它们将共享相同的排名,并且下一个不同值的行的排名将是连续的,不会跳过任何数字。

示例

  • 假设存在一个名为 students 的表,包含 id、name 和 score 列,要根据学生的成绩对学生进行排名
SELECT id, name, score, DENSE_RANK() OVER(ORDER BY score DESC) AS ranking
FROM students;
  • DENSE_RANK() OVER(ORDER BY score DESC) 会根据 score 列的值对学生进行降序排名,并将排名结果作为 ranking 列添加到结果集中。

与 rank 和 row_number 区别

  • 与 RANK() 函数相比,RANK() 函数在遇到相同值时会分配相同的排名,但下一个不同值的行的排名会跳过相应的数量,导致排名不连续。例如,如果有两个学生并列第一名,那么下一个学生的排名将是第三名
  • 与 ROW_NUMBER() 函数相比,ROW_NUMBER() 函数会严格按照顺序为每一行分配一个连续的排名,不会出现排名相同的情况,即使在排序列上的值相同,也会按照行的顺序依次分配不同的排名。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/924086.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

GoogleTest做单元测试

目录 环境准备GoogleTest 环境准备 git clone https://github.com/google/googletest.git说cmkae版本过低了,解决方法 进到googletest中 cmake CMakeLists.txt make sudo make installls /usr/local/lib存在以下文件说明安装成功 中间出了个问题就是,…

Android 11 三方应用监听关机广播ACTION_SHUTDOWN

前言 最近有项目过程中,有做app的同事反馈,三方应用无法监听关机广播。特地研究了下关机广播为啥监听不到。 1.原因:发送关机广播的类是ShutdownThread.java,添加了flag:Intent.FLAG_RECEIVER_FOREGROUND | Intent.FLAG_RECEIVER…

一篇文章了解Linux

目录 一:命令 1 ls命令作用 2 目录切换命令(cd/pwd) (1)cd切换工作目录命令 3 相对路径、绝对路径和特殊路径 (1)相对路径和绝对路径的概念和写法 (2)几种特殊路径的表示符 (3)练习题: 4 创建目录命令&#x…

css—动画

一、背景 本文章是用于解释上一篇文章中的问题,如果会动画的小伙伴就不用再次来看了,本文主要讲解一下动画的设定规则,以及如何在元素中添加动画,本文会大篇幅的讲解一下,动画属性。注意,这是css3的内容&am…

MATLAB下的RSSI定位程序,二维平面上的定位,基站数量可自适应

文章目录 引言程序概述程序代码运行结果待定位点、锚点、计算结果显示待定位点和计算结果坐标 引言 随着无线通信技术的发展,基于 R S S I RSSI RSSI(接收信号强度指示)的方法在定位系统中变得越来越流行。 R S S I RSSI RSSI定位技术特别适…

排序算法之选择排序堆排序

算法时间复杂度辅助空间复杂度稳定性选择排序O(N^2)O(1)不稳定堆排序O(NlogN)O(1)不稳定 1.选择排序 这应该算是最简单的排序算法了,每次在右边无序区里选最小值,没有无序区时,就宣告排序完毕 比如有一个数组:[2,3,2,6,5,1,4]排…

电视网络机顶盒恢复出厂超级密码大全汇总

部分电视机顶盒在按遥控器设置键打开设置时,会弹出设置密码弹窗,需输入密码才能操作其中内容。 如下图所示: 部分电视机顶盒在选择恢复出厂设置时,会出现设置密码弹窗,只有输入操作密码后才能进行恢复出厂设置的操作。…

继续完善wsl相关内容:基础指令

文章目录 前言一、我们需要安装wsl,这也是安装docker desktop的前提,因此我们在这篇文章里做了介绍:二、虽然我们在以安装docker desktop为目的时,不需要安装wsl的分发(distribution),但是装一个分发也是有诸多好处的:三、在使用wsl时,不建议把东西直接放到系统里,因…

基于STM32的智能风扇控制系统

基于STM32的智能风扇控制系统 持续更新,欢迎关注!!! ** 基于STM32的智能风扇控制系统 ** 近几年,我国电风扇市场发展迅速,产品产出持续扩张,国家产业政策鼓励电风扇产业向高技术产品方向发展,国内企业新增投资项目投…

Zero to JupyterHub with Kubernetes中篇 - Kubernetes 常规使用记录

前言:纯个人记录使用。 搭建 Zero to JupyterHub with Kubernetes 上篇 - Kubernetes 离线二进制部署。搭建 Zero to JupyterHub with Kubernetes 中篇 - Kubernetes 常规使用记录。搭建 Zero to JupyterHub with Kubernetes 下篇 - Jupyterhub on k8s。 参考&…

docker-compose搭建xxl-job、mysql

docker-compose搭建xxl-job、mysql 1、搭建docker以及docker-compose2、下载xxl-job需要数据库脚本3、创建文件夹以及docker-compose文件4、坑来了5、正确配置6、验证-运行成功 1、搭建docker以及docker-compose 略 2、下载xxl-job需要数据库脚本 下载地址:https…

HTTP有哪些风险?是怎么解决的?

一、风险 HTTP是通过明文传输的,存在窃听风险、篡改风险以及冒充风险。 二、如何解决 HTTPS在HTTP的下层加了一个SSL/TLS层,保证了安全,通过混合加密解决窃听风险、数字签名解决篡改风险、数字证书解决冒充风险。 (1&#xff0…

《Django 5 By Example》阅读笔记:p339-p358

《Django 5 By Example》学习第13天,p359-p382总结,总计24页。 一、技术总结 1.session (1)session 存储方式 Database sessions File-based sessions Cached sessions Cached database sessions Cookie-based sessions (2)设置 CART_SESSION_I…

Python数据分析(OpenCV)

第一步通过pip安装依赖包,执行一下命令 pip install opencv-python 如果是Anaconda请在工具中自行下载 下载好咋们就可以在环境中使用了。 人脸识别的特征数据可以到 github上面下载,直接搜索OpenCV 然后我们在源码中通过cv2的级联分类器引入人脸的特征…

(免费送源码)计算机毕业设计原创定制:Java+ssm+JSP+Ajax SSM棕榈校园论坛的开发

摘要 随着计算机科学技术的高速发展,计算机成了人们日常生活的必需品,从而也带动了一系列与此相关产业,是人们的生活发生了翻天覆地的变化,而网络化的出现也在改变着人们传统的生活方式,包括工作,学习,社交…

工业AI质检 AI质检智能系统 尤劲恩(上海)信息科技有限公司

来的现代化工厂,将逐步被无人化车间取代,无人工厂除了产线自动化,其无人质检将是绕不开的话题。尤劲恩致力于帮助工业制造领域上下游工厂减员增效、提高品质效率,真正实现无人质检IQC/IPQC/OQC的在线质检系统。分析生产环节真实品…

C 语言数组与函数:核心要点深度剖析与高效编程秘籍

我的个人主页 我的专栏:C语言,希望能帮助到大家!!!点赞❤ 收藏❤ 目录 引言数组基础 2.1 数组的定义与初始化 2.2 一维数组的基本操作 2.3 二维数组及其应用 2.4 数组与指针的关系函数基础 3.1 函数的定义与调用 3.2…

XML JSON

XML 与 JSON 结构 XML(eXtensible Markup Language) 1. 定义 XML 是一种标记语言,用于描述数据的结构和内容。主要用于数据存储与交换。 2. 特点 可扩展性:用户可以自定义标签。层次化结构:数据以树形结构组织&…

蓝桥杯备赛笔记(一)

这里的笔记是关于蓝桥杯关键知识点的记录,有别于基础语法,很多内容只要求会用就行,无需深入掌握。 文章目录 前言一、编程基础1.1 C基础格式和版本选择1.2 输入输出cin和cout: 1.3 string以下是字符串的一些简介:字符串…

[代码随想录Day24打卡] 93.复原IP地址 78.子集 90.子集II

93.复原IP地址 一个合法的IP地址是什么样的: 有3个’.分割得到4个数,每个数第一个数不能是0,不能含有非法字符,不能大于255。 这个是否属于合法IP相当于一个分割问题,把一串字符串分割成4部分,分别判断每…