MySQL（六）——多表查询

ops/2024/10/11 13:30:14/

文章目录

  • 多表查询
    • 连接查询
      • 内连接
      • 外连接
      • 自连接
    • 子查询
      • 标量子查询
      • 列子查询
      • 行子查询
      • 表子查询
    • 联合查询

多表查询

多表查询，也称为关联查询，是指两个或更多个表一起完成查询操作。这些表之间通过关联字段（可能是外键）建立关系，包括一对一、一对多或多对多等关系。多表查询从多个表中获取相关数据，以满足复杂的查询需求。

连接查询、子查询 和 联合查询都属于多表查询。

连接查询

连接查询包括内连接、外连接 和 自连接，以下图理解这三者的区别：

在这里插入图片描述

两个椭圆表示两张表，内连接 查询结果为 C 部分；外连接 查询结果是 A+C 或 B+C，分别代表左外连接和右外连接；自连接 并不直接对应于Venn图中的两个独立集合，因为自连接是在同一个表内部进行的。

先要了解如下SQL语句：

SELECT * FROM tbl_name1, tbl_name2 ...;

上述语句如果只有一张表，就是对这张表进行全列查询；对多张表来说（下面多以两张表为例），返回的结果集就是两表数据的笛卡尔积。

笛卡尔积：表示两个或多个集合中所有可能的有序对的集合，即多个集合的所有组合情况。

以两张表为例它们的笛卡尔积：

在这里插入图片描述

观察发现，取笛卡尔积的表中存在很多的 class_id 与 id(1) 不匹配的无效数据，我们在查询时需要过滤掉这些无效的笛卡尔积。

内连接

内连接是最常见的连接类型。它返回两个表中匹配连接条件的所有行。如果在一个表中存在匹配项，而在另一个表中不存在匹配项，则这些行不会出现在结果集中。内连接可以确保结果集中的每一行在两个表中都有匹配项。

【语法】

隐式内连接

SELECT 字段列表 FROM tbl_name1, tbl_name2... WHERE 过滤条件...;

显式内连接

SELECT 字段列表 FROM tbl_name1 [INNER] JOIN tbl_name2 ON 连接条件...;

两种语法的过滤条件和连接条件是一致的，都是用来过滤无效的笛卡尔积。确定过滤条件时，将所有相关表的字段放在一起，确定哪些字段的值匹配后才能为有效数据，进而确定过滤条件。

【示例】

对以下两表进行查询，要求查询出学生姓名以及所在班级：

在这里插入图片描述

# 隐式内连接写法
SELECT stu.name,cla.name 
FROM students stu,class cla
WHERE stu.class_id = cla.id;# 显式内连接写法
SELECT stu.name,cla.name 
FROM students stu 
INNER JOIN class cla 
ON stu.class_id = cla.id;

查询结果如下：

在这里插入图片描述

注意：

  • 当给表起了别名，就不能再用表名限制字段，必须使用别名；如果在已经给表名起了别名的情况下继续使用表名会报错！
  • 在内连接查询中，两张表的位置是可以任意调换的，这不会影响查询的结果。内连接关注的是两个表之间基于连接条件的匹配行，而不关心这些表在查询中的物理顺序。

外连接

外连接 分为 左外连接和 右外连接。左(右)外连接查询除了返回两个表中匹配连接条件的所有行外，还会返回左(右)表的所有数据行，不论是否为NULL。（左外连接查询左表所有数据以及两表交集数据；右外连接查询右表所有数据以及两表交集数据）

继续以下图为例：

在这里插入图片描述

A 作为左表，B 作为右表，左连接查询的结果集为 A+C，右连接查询的结果集为 B+C。

【语法】

# 左外连接
SELECT 字段列表 FROM tbl_name1 LEFT [OUTER] JOIN tbl_name2 ON 连接条件...;# 右外连接
SELECT 字段列表 FROM tbl_name2 RIGHT [OUTER] JOIN tbl_name2 ON 连接条件...;
  • 左外连接 和 右外连接 可以相互转换，仅需要将关键字LEFT和RIGHT互换，然后将左表和右表互换。

【示例】

查询所有学生和所在班级，没有分配班级的学生也要查询：

  • 内连接做不到以上需求，左外连接和右外连接都可以做到

在这里插入图片描述

自连接

自连接主要用于在同一张表内部进行数据的关联和查询，即自己和自己连接查询，自连接必须使用别名。

【语法】

SELECT 字段列表 FROM 表A 别名A {[INNER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN 表A 别名B ON 条件...;
  • 自连接可以是内连接，也可以是外连接，自连接时将两张表（两张表是同一张）作为不同的表对待，就可以转化为内连接或外连接问题了。
  • 自连接必须使用别名

【示例】

假设有一个员工表employee，包含员工ID（employee_id）、员工姓名（employee_name）和经理ID（manager_id）等字段。现在需要查询每个员工及其直接上级的姓名。由于经理也是员工，经理ID要和员工表的员工ID匹配，这时可以使用自连接来实现：

SELECT A.employee_name AS 员工姓名, B.employee_name AS 经理姓名  
FROM employee A, employee B  
WHERE A.manager_id = B.employee_id;

子查询

子查询，又称嵌套查询，是嵌套在另一个查询中的SQL查询。子查询可以出现在SELECT、INSERT、UPDATE或DELETE语句中，并且可以用于WHERE子句、HAVING子句、FROM子句或SELECT列表中。

  • 根据子查询返回的结果的不同，可以分为：
子查询类型特点
标量子查询结果为单个值
列子查询结果为一列
行子查询结果为一行
表子查询结果为多行多列
  • 另外，根据子查询位置不同，可以分为：WHERE之后、FROM之后、SELECT之后

涉及到子查询的查询往往比较复杂，如不同的子查询位置可以返回不同的结果，我们就以标量子查询、列子查询、行子查询和表子查询来介绍。

为了方便后续举例，我们直接给出一些表（外键关系已由箭头给出）：

在这里插入图片描述

标量子查询

标量子查询：返回单个值的子查询。它常用于WHERE子句中，作为一个比较值。

常用操作符：=、<>、>、>=、<、<=

【示例以及SQL】

# 演示标量子查询-- 需求1：查询“计算机系2019级1班”的所有同学的个人信息
SELECT * FROM student WHERE class_id = (SELECT class_id FROM class WHERE name = '计算机系2019级1班');-- 需求2：查询“许仙”的所有成绩
SELECT co.name,sc.score 
FROM score sc 
INNER JOIN course co 
ON sc.course_id = co.course_id 
WHERE student_id = (SELECT student_id FROM student WHERE name = '许仙');

需求1结果集：
在这里插入图片描述

需求2结果集：

在这里插入图片描述

列子查询

列子查询：返回一个列的值列表的子查询。

常用操作符：NOT IN、IN、ANY、SOME、ALL

【示例及SQL】

# 需求1：求“中文系2019级3班”所有同学的总成绩-- a. 嵌套子查询实现需求
SELECT stu.name,SUM(sc.score) 总分 
FROM student stu 
INNER JOIN score sc 
ON stu.student_id = sc.student_id 
GROUP BY sc.student_id 
HAVING name IN (SELECT name FROM student WHERE class_id = (SELECT class_id FROM class WHERE name = '中文系2019级3班'));-- b. 通过JOIN消除嵌套子查询
SELECT stu.name,SUM(sc.score) 总分 
FROM student stu 
INNER JOIN class cla ON stu.class_id = cla.class_id AND cla.name = '中文系2019级3班'
INNER JOIN score sc ON sc.student_id = stu.student_id
GROUP BY sc.student_id; # 进一步的需求2：查询“计算机系2019级1班”中总成绩高于“中文系2019级3班”所有同学的同学-- 1. 先求“计算机系2019级1班”所有同学的总成绩
SELECT stu.name,SUM(sc.score) '1班总分'
FROM student stu
INNER JOIN class cla ON cla.class_id = stu.class_id AND cla.name = '计算机系2019级1班'
INNER JOIN score sc ON sc.student_id = stu.student_id
GROUP BY sc.student_id;-- 2. 将需求1的SQL查询stu.name列删除，仅查询SUM(sc.score)，使得结果集仅剩总分一列
SELECT SUM(sc.score) 总分 
FROM student stu 
INNER JOIN class cla ON stu.class_id = cla.class_id AND cla.name = '中文系2019级3班'
INNER JOIN score sc ON sc.student_id = stu.student_id
GROUP BY sc.student_id;-- 3. 将第二步作为第一步的子查询，并添加WHERE过滤掉非需求数据，完成需求
SELECT name 
FROM (
SELECT stu.name,SUM(sc.score) 1班总分
FROM student stu
INNER JOIN class cla ON cla.class_id = stu.class_id AND cla.name = '计算机系2019级1班'
INNER JOIN score sc ON sc.student_id = stu.student_id
GROUP BY sc.student_id) AS q1
WHERE q1.1班总分 > ALL (SELECT SUM(sc.score) 总分 
FROM student stu 
INNER JOIN class cla ON stu.class_id = cla.class_id AND cla.name = '中文系2019级3班'
INNER JOIN score sc ON sc.student_id = stu.student_id
GROUP BY sc.student_id);

对于需求1，两种解决方案的结果集均为：
在这里插入图片描述

  • 解决方案a使用到了列子查询，并在其中嵌套了一层标量子查询

对于需求2，三步SQL的结果集分别如下图所示：

在这里插入图片描述

行子查询

行子查询：返回单个行而不仅仅是单个字段的值，可以同时返回多个列值。

常用操作符：=、<>、IN、NOT IN

【示例及SQL】

使用先前的表例并不能产生一个很好的行子查询的示例，创建一个简单的表演示：

在这里插入图片描述

# 需求：查询和“鑫哥”职位相同且薪资一样的员工SELECT name FROM emp WHERE (role,salary) = (SELECT role,salary FROM emp WHERE name = '鑫哥') AND name <> '鑫哥';

表子查询

表子查询：返回的结果集是一个多行多列的表数据。

常用操作符：IN；常将表子查询作为一张临时表进行进一步的查询。

【示例及SQL】

# 需求：查询邮箱为空的同学的信息及其所在的班级信息-- 1. 查询邮箱为空的同学的信息
SELECT sn,name,class_id FROM student WHERE mail <=> NULL;-- 2. 查询邮箱为空的同学的信息及其所在的班级信息
SELECT tmp.name 姓名,tmp.sn 学号,cla.name 班级
FROM (SELECT sn,name,class_id FROM student WHERE mail <=> NULL) tmp
INNER JOIN class cla
ON cla.class_id = tmp.class_id;

联合查询

联合查询是一种将多次查询的的结果集合并为一个结果集的操作。常用于需要从不同的表中检索相似类型的数据，或者对同一表应用不同的查询条件并将结果合并时。

联合查询涉及到两个关键字UNION和UNION ALL

【语法】

SELECT 字段列表 FROM 表A ... 
UNION [ALL] 
SELECT 字段列表 FROM 表B ...;
  • UNION ALL会将全部的数据直接合并起来，UNION会对合并之后的数据去重
  • 联合查询的多张表的列数必须一致，类型也要匹配。当列的数量匹配但类型不匹配时，可以合并返回结果集，该结果集没有意义，这一点需要人工规避。

【示例】

现有两张表stu_table1和stu_table2，分别代表两个活动的参与人员在这里插入图片描述

要求查询参与活动的所有男同学（有些同学参加了两个活动）：

# UNION ALL
SELECT name FROM stu_table1 WHERE gender = '男'
UNION ALL
SELECT name FROM stu_table2 WHERE gender = '男';# UNION
SELECT name FROM stu_table1 WHERE gender = '男'
UNION 
SELECT name FROM stu_table2 WHERE gender = '男';

在这里插入图片描述

多表查询会因复杂业务变得十分复杂，建议多加练习！


http://www.ppmy.cn/ops/104722.html

相关文章

QT学习ubuntu qt + desktop

QT学习ubuntu qt + desktop

环境搭建 ubuntu 安装QT 遇到kit 选择不了 通过sudo apt-get install qt5-default去安装SDK的时候报错&#xff1a; Package qt5-default is not available, but is referred to by another package. This may mean that the package is missing, has been obsoleted, or is …
阅读更多...
HTTP 状态码：解析与处理的全面指南

HTTP 状态码：解析与处理的全面指南

一、HTTP 状态码概述 HTTP 状态码&#xff08;HTTP Status Code&#xff09;是用以表示网页服务器 HTTP 响应状态的 3 位数字代码。它在客户端与服务器之间的通信中起着至关重要的作用。 其主要作用在于向客户端清晰准确地传达服务器对请求的处理结果。例如&#xff0c;当客户…
阅读更多...
回溯算法回顾

回溯算法回顾

多叉树问题&#xff0c;其实就是二叉树的拓展罢了 class Node:def __init__(self, val: int):self.val valself.children []# N 叉树的遍历框架 def traverse_n_ary_tree(root):if root is None:return# 前序位置for child in root.children:traverse_n_ary_tree(child)# 后…
阅读更多...
《机器学习》—— OpenCV 对图片的各种操作

《机器学习》—— OpenCV 对图片的各种操作

文章目录 1、安装OpenCV库2、读取、显示、查看图片3、对图片进行切割4、改变图像的大小5、图片打码6、图片组合7、图像运算8、图像加权运算 1、安装OpenCV库 使用pip是最简单、最快捷的安装方式 pip install opencv-python3.4.2还需要安装一个包含了其他一些图像处理算法函数的…
阅读更多...
TikTok运营：IP地址如何影响TikTok的内容运营？

TikTok运营：IP地址如何影响TikTok的内容运营？

TikTok作为外贸人宣传推广的重要平台&#xff0c;其运营成效与产品的实际转化率息息相关。然而&#xff0c;在TikTok的运营过程中&#xff0c;一个看似微不足道的元素—IP地址&#xff0c;却扮演着至关重要的角色。本文将深入探讨TikTok运营中IP地址的重要性&#xff0c;揭示其…
阅读更多...
SpringBoot 统⼀功能处理

SpringBoot 统⼀功能处理

大纲: 掌握拦截器的使⽤,及其原理学习统⼀数据返回格式和统⼀异常处理的操作了解⼀些Spring的源码 1. 拦截器 之前写登录程序时,后端程序根据Session来判断⽤⼾是否登录,但是实现⽅法是⽐较⿇烦的,需要修改很多代码,这里引入拦截器简化 1.1 什么是拦截器 拦截器是Spring框架…
阅读更多...
LPRNet: 端到端的车牌识别

LPRNet: 端到端的车牌识别

文章目录 1. 简介2. 网络2.1 Backbone2.2 进一步的提升表现&#xff1a;用global context embedding 来增强中间的特征图2.3 decoder 解码2.3.1 序列解码2.3.2 后过滤 3. CTC 损失函数3.1 CTC 损失函数的工作原理3.2 CTC 损失函数的特点3.3 CTC Loss 的局限性&#xff1a; 4. 实…
阅读更多...
【Three.js基础学习】20.Environment map

【Three.js基础学习】20.Environment map

提示&#xff1a;文章写完后&#xff0c;目录可以自动生成&#xff0c;如何生成可参考右边的帮助文档 前言 课程回顾&#xff1a; 模型的加载 GLTFLoader 环境贴图实现&#xff1b; CubeTextureLoader LDR:低动态范围 backgroundBlurriness&#xff1a;设置背景模糊 (不生效 为…
阅读更多...
最新文章

Copyright @ 2022~2023