【MySQL】多表查询(笛卡尔积现象,联合查询、内连接、左外连接、右外连接、子查询)-通过练习快速掌握法

embedded/2025/3/18 20:42:10/

在DQL的基础查询中,我们已经学过了多表查询的一种:联合查询(union)。本文我们将系统的讲解多表查询。

笛卡尔积现象

首先,我们想要查询emp表和stu表两个表,按照我们之前的知识栈,我们直接使用:

sql">select * from emp,stu;

当查询emp时:15条记录被查询

当查询stu时:5条记录被查询

但是让我们来观察结果:

哇,查询到了70条记录 。而且根据结果我们可以看出:左表emp的每条记录都会与右表stu的每条记录组成一条新的记录,也就是14*5=70条记录。这种现象非常符合离散数学中学到的笛卡尔积的结果,所以我们将这种现象称为笛卡尔积现象。

笛卡尔积(Cartesian Product):表示两个集合之间的所有可能的有序对的集合

笛卡尔积的性质包括:

  1. 笛卡尔积的结果是一个新集合。
  2. 如果 AA 和 BB 其中一个为空集,则结果也为空集。
  3. 笛卡尔积的顺序是重要的,即 A×B≠B×A。

我们如何实现15+4的结果呢?直接使用上面的select肯定是不行了。

那么,此时有一个叫联合查询的方式出现在脑海里:

联合查询

关键字:【union all】

sql">select empno,ename,job from emp
union all
select id,nick,pwd from stu;

观察结果:19=15+4条记录 (使用union代替union all可以实现去重的功能)

但是为了将记录查询出来,我们 必须合适选择每个表的字段,将两个表查询的字段的数据类型一一对应。empno int = id int,ename varchar = nick varchar ......

如果数据类型对应不上,那么将无法查询,结果是:

直接查询的条件限制法

那么联合查询也不能符合我们对查询结果的预期,这时候需要我们转换思路。从笛卡尔积现象开始:【需求:查询员工表以及每个员工对应的部门信息】

首先直接查询:

对于查询的结果,虽然有重复, 但至少有我们需要的结果,那么只需要将这个表中的有效记录提取出来,就可以了。也就是使用where条件进行限定:

此时我们查询的结果就符合我们的预期了。但注意,这时候我们操作时必须给每个字段指定上是哪个表的字段,不然的话,该字段属于二义性字段,无法通过语法分析,也就不能执行了。

内连接

sql">select field from tb1 
[inner] join tb2 on condition;

等值连接

eg.根据一个编号查另一个表中改编号对应的内容。常见于:根据子表外键连接父表主键

【练习:查询员工表以及每个员工对应的部门信息】

sql">select * from emp [inner]join dept on emp.DEPTNO = dept.DEPTNO;

 

非等值连接

eg.根据一个表的某个字段,查另一个表中该字段属于哪段区间的信息。实际用途:等级划分

【练习:根据员工的薪水查出薪水的等级】

自连接

eg.自连接是某个表的某个字段信息存储的数据是本表的另一条记录的信息。常用于:事物关联

【练习:根据员工表的领导编号查询领导的名字】

自连接的流程:为显示的字段起别名(避免两个结果字段名冲突,非必须)=》from选择查询表=》join 连接表(本表),并起别名(避免二义性,必须)=》连接条件。[过程中的每个字段都需要明确指出是哪个表] 

外连接

由于内连接会将连接条件的字段中空值的记录给过滤掉,所以为了显示较为全面的记录,我们采用外连接的方式进行多表查询。

左外连接

左外连接就是(left [outer] join ... on...)。显示主表的所有字段,并将被连接的从表符合连接条件的记录连接到主表,如果没有,主表显示原本记录,从表的字段中为空。

【练习:查询员工表以及每个员工对应的部门信息---显示所有员工】

右外连接

右外连接就是(right [outer] join ... on...)。与左外连接类似。

【练习:查询员工表以及每个员工对应的部门信息---显示所有部门】

我们对比发现,右外连接显示的记录比左外连接的记录多一条,多出的一条是部门表中的数据,但该部门在员工表中没有员工,所以全部显示为空。

:外连接查询的结果记录数 >= 内连接查询到的结果记录数

左外连接【左图】、右外连接【右图】

子查询

子查询:嵌套在其它SQL语句内的查询语句,且必须出现在圆括号内(查询一般是指select语句):子查询的结果可以作为外层查询的过滤条件或计算字段。

标量子查询

子查询返回结果是单个值,如数字、字符串、日期等最简单的形式。这种子查询称为标量子查询。【常用的操作符:| = | <> | > | >= | < | <= |】

【练习:查询销售部的部门员工信息】

第一步:查询销售部的部门编号

sql">select deptno from dept where dname="SALES";

第二步:查询部门编号为上述结果的员工

sql">select * from emp where deptno = 上条语句的结果;

第三步:合并一条语句:

sql">select * from emp where deptno = (select deptno from dept where dname="SALES");

标量子查询可以在子句中使用聚合函数、而且子句的位置还可以出现在select后作为字段出现:

【练习:查询部门名,以及每个部门的人数】

sql">select dname, (select count(*) from emp where dept.deptno=emp.deptno) emps 
from dept;

列子查询

子查询的结果是一列(或者多列),这种子查询称为列子查询

【常用操作符:in、not in、any、some、all】

IN:在指定的集合范围之内,多选一

NOT IN:不再指定的集合范围之内

ANY:子查询返回列表中,有任意一个满足即可【相当于集合所有元素作 or 运算】

SOME:与ANY相同,SOME与ANY等价

ALL:子查询返回列表的所有值都要满足【相当于集合所有元素之间作 and 运算】

【练习:查询销售部(SALES)和调研部(RESEARCH)所有员工信息】

sql">select * 
from emp 
where deptno in (select deptno from dept where dname in ("SALES","RESEARCH"));-- or:
select * 
from emp 
where deptno in (select deptno from dept where dname="SALES" or dname="RESEARCH");

【练习:查询比销售部的所有人的工资都高的员工信息】

比所有人都高,也就是sal > all( {...} )

通过这个练习,我们不仅练习了all运算,我们还知道了,子句可以嵌套子句。

行子查询

子查询的返回结果是一行(可以是多行),这种子查询称为行子查询

【常用操作符:| = | <> | in | not in】

【练习:查询与“SMITH”的 薪资以及直属领导 都相同的员工信息】

sql">-- (单行结果)
select * from emp where (sal,mgr) = (select sal,mgr from emp where ename = "SMITH");-- (多行结果)
select * from emp where (sal,mgr) in (select sal,mgr from emp where ename = "SMITH");

通过该练习,我们掌握了新的知识:

(field1,field2,...,fieldn) 可以通过加圆括号的方式直接与行结果进行运算【= | <> | in | not in】 

表子查询

子查询的结果可以是多行多列,产生这种结果的子查询称为表子查询。【常用操作符:IN】

这种就是行子查询的 in 操作。

sql">-- (多行结果)
select * from emp where (sal,mgr) in (select sal,mgr from emp where ename = "SMITH");

感谢大家!欢迎指导、询问、探讨知识!


http://www.ppmy.cn/embedded/173672.html

相关文章

Qt6.8实现麦克风音频输入音频采集保存wav文件

一.本文目的 实现在Qt中接收麦克风数据并保存为WAV文件,使用QAudioInput来录音,并使用QFile来保存数据到WAV文件。 开发环境:QT6.8 本文用极简代码实现,核心代码只需不到100行。 二.代码实现

K8S学习之基础三十四:K8S之监控Prometheus部署pod版

使用 Kubernetes Pod 的方式部署 Prometheus 是一种常见的方法&#xff0c;尤其是在容器化和微服务架构中。以下是详细的步骤&#xff1a; 1. 创建命名空间&#xff08;可选&#xff09; 为了方便管理&#xff0c;可以为 Prometheus 创建一个单独的命名空间。 yaml 复制 a…

Python 视频爬取教程

文章目录 前言基本原理环境准备Python安装选择Python开发环境安装必要库 示例 1&#xff1a;爬取简单直链视频示例 2&#xff1a;爬取基于 HTML5 的视频&#xff08;以某简单视频网站为例&#xff09; 前言 以下是一个较为完整的 Python 视频爬取教程&#xff0c;包含基本原理…

文件解析漏洞靶场---解析详解

⽂件解析漏洞是由于中间件错误的将任意格式的⽂件解析成⽹⻚可执⾏⽂件&#xff0c;配合⽂件上传漏洞进⾏ GetShell的漏洞&#xff01; 一、IIS解析漏洞 3个 1. IIS6.X 2个 环境需求&#xff1a;windows2003iis6 安装windows2003&#xff1b; 安装iis&#xff1a;控…

Django之旅:第二节--启动运行django

1、确保app已配置完(settings.py文件里面配置&#xff09; INSTALLED_APPS [django.contrib.admin,django.contrib.auth,django.contrib.contenttypes,django.contrib.sessions,django.contrib.messages,django.contrib.staticfiles,app.apps.AppConfig #配置已经注册好的app…

基于Python+SQLite实现(Web)验室设备管理系统

实验室设备管理系统 应用背景 为方便实验室进行设备管理&#xff0c;某大学拟开发实验室设备管理系统 来管理所有实验室里的各种设备。系统可实现管理员登录&#xff0c;查看现有的所有设备&#xff0c; 增加设备等功能。 开发环境 Mac OSPyCharm IDEPython3Flask&#xff…

TK矩阵:提高多账号管理效率的利器

随着TikTok的火爆&#xff0c;越来越多的人开始利用这个平台进行内容创作和社交互动。无论是个人创作者、品牌方&#xff0c;还是营销公司&#xff0c;TikTok都提供了巨大的机会&#xff0c;但同时也带来了运营上的挑战&#xff0c;尤其是在管理多个账户时。每个账号的维护、内…

qt 线程

以下是一个完整的Qt线程池实现示例&#xff0c;结合任务优先级、线程安全、UI交互等高级功能&#xff0c;并包含异常处理机制&#xff1a; cpp // mytask.h #ifndef MYTASK_H #define MYTASK_H#include <QRunnable> #include <QObject> #include <QMutex> #…