【MySQL系列】MySQL复合查询的学习 _ 多表查询 | 自连接 | 子查询 | 合并查询
「前言」文章内容大致是对MySQL复合查询的学习。
「归属专栏」MySQL
「主页链接」个人主页
「笔者」枫叶先生(fy)
目录
- 一、基本查询回顾
- 二、多表查询
- 三、自连接
- 四、子查询
- 4.1 单行子查询
- 4.2 多行子查询
- 4.3 多列子查询
- 4.4 在from子句中使用子查询
- 五、合并查询
一、基本查询回顾
前面篇章讲解的mysql表的查询都是对一张表进行查询,在实际开发中这远远不够,下面将讲解复合查询,首先回顾一下基本的查询。
使用的数据库是之前篇章的雇员信息表,员工表(emp)、部门表(dept)和工资等级表(salgrade)
查询工资高于500或岗位为MANAGER的雇员,同时还要满足他们的姓名首字母为大写的J
mysql> select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
按照部门号升序而雇员的工资降序排序
mysql> select * from emp order by deptno asc, sal desc;
使用年薪进行降序排序
mysql> select ename, sal*12+ifnull(comm, 0) as 年薪 from emp order by 年薪 desc;
注:
- 由于NULL与任何值做计算得到的结果都是NULL,因此在计算年薪时不能直接用月薪的12倍加上每个员工的奖金,这样可能导致得到的年薪为NULL值。
- 在计算每个员工的年薪时,应该通过ifnull函数判断员工的奖金是否为NULL,如果不为NULL则ifnull函数返回员工的奖金,如果为NULL则ifnull函数返回0,避免让NULL值参与计算
显示工资最高的员工的名字和工作岗位
解决该问题需要进行两次查询
此外,这种问题还可以使用子查询,将两句查询语句合并起来,需要将第一次查询的SQL语句用括号括起来。
mysql> select ename, job from emp where sal = (select max(sal) from emp);
显示工资高于平均工资的员工信息
也是使用子查询解决
mysql> select * from emp where sal > (select avg(sal) from emp);
显示每个部门的平均工资和最高工资
在group by子句中指明按照部门号进行分组,在select语句中使用avg函数和max函数,分别查询每个部门的平均工资和最高工资
mysql> select deptno, format(avg(sal), 2) 平均, max(sal) 最高 from emp group by deptno;
显示平均工资低于2000的部门号和它的平均工资
在group by子句中指明按照部门号进行分组,在select语句中使用avg函数查询每个部门的平均工资,在having子句中指明筛选条件为平均工资小于2000
mysql> select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资 < 2000;
显示每种岗位的雇员总数,平均工资
mysql> select job, count(*) 人数, format(avg(sal), 2) 平均工资 from emp group by job;
二、多表查询
上面的基础查询都是在一张表的基础上进行的查询,实际开发中往往数据来自不同的表,所以需要多表查询。
- 在进行多表查询时,只需要将多张表的表名依次放到from子句之后,用逗号隔开即可,这时MySQL将会对给定的这多张表取笛卡尔积,作为多表查询的初始数据源
- 多表查询的本质,就是对给定的多张表取笛卡尔积,然后在产生的新表进行查询
笛卡尔积是指给定两个集合A和B,其中A中的每个元素和B中的每个元素都可以组成一个有序对,这些有序对的集合就是A和B的笛卡尔积。
例如,员工表和部门表进行笛卡尔积
员工表:
部门表:
两张表进行笛卡尔积
mysql> select * from emp, dept;
员工表和部门表的笛卡尔积由两部分组成,前半部分是员工表的列信息,后半部分是部门表的列信息
对员工表和部门表取笛卡尔积时,会先从员工表中选出一条记录与部门表中的所有记录进行组合,然后再从员工表中选出一条记录与部门表中的所有记录进行组合,以此类推,最终得到一张新表
对多张表取笛卡尔积后得到的数据并不都是有意义的。
比如对员工表和部门表取笛卡尔积时,员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合,而实际一个员工只有和自己所在的部门信息进行组合才是有意义的,因此需要从笛卡尔积产生的新表筛选出员工的部门号和部门的编号相等记录。
注意:进行笛卡尔积的多张表中可能会存在相同的列名,这时在选中列名时需要通过表名.列名
的方式进行指明,如果有重复的不指明确切一列,就会报错。
显示雇员名、雇员工资以及所在部门的名字
从题意可以看出,部门名只有dept表中才有,其他数据来源于emp表,即数据来自EMP和DEPT表,因此要联合查询,即多表查询
mysql> select emp.ename, emp.sal, dept.deptno from emp, dept where emp.deptno = dept.deptno;
显示部门号为10的部门名,员工名和工资
部门名只有部门表中才有,员工名和员工工资只有员工表中才有,因此需要同时使用员工表和部门表进行多表查询,在where子句中指明筛选条件为员工的部门号等于部门编号(筛选符合条件的信息)
mysql> select ename, sal, emp.deptno, dname from emp, dept where emp.deptno = dept.deptno and dept.deptno = 10;
注意:在筛选部门号等于10的部门时,可以使用员工表中的部门号,也可以使用部门表中的部门编号,因为两列都是一样的。
显示各个员工的姓名,工资,及工资级别
员工名和工资只有员工表中才有,而工资级别只有工资等级表中才有,因此需要同时使用员工表和工资等级表进行多表查询,在where子句中指明筛选条件为员工的工资在losal和hisal之间的记录
mysql> select ename, sal, grade from emp, salgrade where sal between losal and hisal;
三、自连接
自连接是指在同一张表进行连接查询,也就是说我们不仅可以对不同表进行取笛卡尔积,也可以对同一张表取笛卡尔积。
显示员工FORD的上级领导的编号和姓名
可以使用子查询,先对员工表进行查询得到FORD的领导的编号,然后再根据领导的编号对员工表进行查询得到FORD领导的姓名
mysql> select empno, ename from emp where empno = (select mgr from emp where ename = 'FORD');
也可以使用多表查询(自查询),因为员工表中的mgr字段能够将表中员工的信息和员工领导的信息关联起来。
mysql> select leader.empno, leader.ename from emp leader, emp worder where leader.empno = worder.mgr and worder.ename = 'FORD';
注 由于自连接是对同一张表取笛卡尔积,因此在自连接时至少需要给一张表取别名,否则无法区分这两张表中的列。
四、子查询
- 子查询是指嵌入在其他SQL语句中的查询语句,也叫嵌套查询
- 子查询可分为单行子查询、多行子查询、多列子查询,以及在from子句中使用的子查询
4.1 单行子查询
单行子查询,是指返回单行单列数据的子查询
显示SMITH同一部门的员工
在子查询中查询SMITH所在的部门号,在where子句中指明筛选条件为员工部门号等于子查询返回的部门号
mysql> select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
此外,解决该问题也可以使用自连接
4.2 多行子查询
多行子查询,是指返回多行单列数据的子查询
使用
in
关键字;查询和10号部门的工作岗位相同的雇员的名字,岗位,工资,部门号,但是不包含10自己的
先查询10号部门有哪些工作岗位,在查询时要对结果进行去重,因为10号部门的某些员工的工作岗位可能是相同的
然后将上述查询作为子查询,在查询员工表时在where子句中使用in关键字,in关键字用于判断员工的工作岗位是子查询得到的若干岗位中的一个
mysql> select ename, job, deptno from emp -> where job in (select distinct job from emp where deptno=10) and deptno<>10;
实用
all
关键字;显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
先查询30号部门员工的工资,进行去重
将上述查询作为子查询,在查询员工表时在where子句中使用all关键字,all关键字用于判断员工的工资是否高于子查询得到的所有工资
mysql> select ename, sal, deptno from emp where sal > all(select distinct sal from emp where deptno=20);
使用
any
关键字;显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号(包含自己部门的员工)
先查询30号部门员工的工资,然后在查询员工表时在where子句中使用any关键字,判断员工的工资是否高于子查询的得到的工资中的某一个
mysql> select ename, sal, deptno from emp where sal > any(select distinct sal from emp where deptno=30);
4.3 多列子查询
单行子查询是指子查询只返回单列,单行数据;多行子查询是指返回单列多行数据,都是针对单列而言的,而多列子查询则是指查询返回多个列数据的子查询语句
查询和SMITH的部门和岗位完全相同的所有雇员,不含SMITH本人
先查询SMITH所在部门的部门号和他的岗位,然后将上述查询作为子查询
mysql> select * from emp where (deptno,job) = (select deptno, job from emp where ename = 'SMITH') and ename <> 'SMITH';
注:
- 多列子查询得到的结果是多列数据,在比较多列数据时需要将待比较的多个列用圆括号括起来
- 多列子查询返回的如果是多行数据,在筛选数据时也可以使用
in、all和any
关键字
4.4 在from子句中使用子查询
- 子查询语句不仅可以出现在where子句中,也可以出现在from子句中
- 子查询语句出现from子句中,其查询结果将会被当作一个临时表使用
显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
先查询每个部门的平均工资,这张表当做临时表使用
然后对员工表和上述的查询结果进行多表查询,在where子句中指明筛选条件为员工的部门号等于临时表中的部门号,并且员工的工资大于临时表中的平均工资
mysql> select ename, emp.deptno, sal, 平均工资 from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) tmp -> where emp.deptno=tmp.deptno and sal > 平均工资;
注意:在from子句中使用子查询时,必须给子查询得到的临时表取一个别名,否则查询将会出错
查找每个部门工资最高的人的姓名、工资、部门、最高工资
先查询每个部门的最高工资
然后对员工表和上述的查询结果进行取笛卡尔积,在where子句中指明筛选条件为员工的部门号等于临时表中的部门号,并且员工的工资等于临时表中的最高工资
mysql> select ename, sal, emp.deptno, 最高工资 from emp, (select max(sal) 最高工资, deptno from emp group by deptno) tmp -> where emp.deptno=tmp.deptno and sal=最高工资;
显示每个部门的信息(部门名,编号,地址)和人员数量
按照部门号进行分组,分别查询每个部门的人员数量
述查询作为子查询放在from子句中,然后对员工表和临时表取笛卡尔积,在where子句中指明筛选条件为员工的部门号等于临时表中的部门号即可
mysql> select dname, dept.deptno, loc, 部门人数 from dept, (select deptno, count(*) 部门人数 from emp group by deptno) -> tmp where dept.deptno = tmp.deptno;
上述也可以只使用多表查询解决
mysql> select dname, dept.deptno, loc, count(*) 人数 from emp, dept -> where emp.deptno = dept.deptno -> group by dept.deptno, dname, loc;
五、合并查询
合并查询,是指将多个查询结果进行合并,关键字union
和union all
- union用于取得两个查询结果的并集,union会自动去掉结果集中的重复行
- union all也用于取得两个查询结果的并集,但union all不会去掉结果集中的重复行
将工资大于2500或职位是MANAGER的人找出来
查询工资大于2500的员工,查询职位是MANAGER的员工
可以使用or操作符将where子句中的两个条件关联起来
也可以使用union
将上述的两条查询SQL连接起来,这时将会得到两次查询结果的并集,并且会对合并后的结果进行去重
mysql> select ename, job, sal from emp where sal > 2500 union-> select ename, job, sal from emp where sal > 2500 or job = 'MANAGER';
可以使用union all
,结果是不去重
注意:待合并的两个查询结果的列的数量必须一致,否则无法合并
--------------------- END ----------------------
「 作者 」 枫叶先生
「 更新 」 2023.8.25
「 声明 」 余之才疏学浅,故所撰文疏漏难免,或有谬误或不准确之处,敬请读者批评指正。
相关文章:

【MySQL系列】MySQL复合查询的学习 _ 多表查询 | 自连接 | 子查询 | 合并查询
「前言」文章内容大致是对MySQL复合查询的学习。 「归属专栏」MySQL 「主页链接」个人主页 「笔者」枫叶先生(fy) 目录 一、基本查询回顾二、多表查询三、自连接四、子查询4.1 单行子查询4.2 多行子查询4.3 多列子查询4.4 在from子句中使用子查询 五、合并查询 一、基本查询回顾…...
微信小程序使用本地图片在真机预览不显示的问题解决
开发工具上本地图片可以显示,但是在真机上预览的时候不能显示 通常我们代码路径是代码是这样写的: <view class"logo"><image src"../../img/e8591fd7b1043bd3b4eb07d86243b5b.png"></image> </view> 最…...

Texlive2023与Texstudio2023卸载与安装(详细全程)
早在两年前安装了texlive2020,最近重新使用总是报错,好像是因为版本过低。我就找了个时间更新一下texlive版本,全程如下。 1、卸载texlive老版本 1)找到texlive目录,比如我的是D:\texlive\2022\tlpkg\installer&…...
塞浦路斯公司注册 塞浦路斯公司开户 塞浦路斯公司年审
一、为什么选择塞浦路斯 1、没有外汇管制; 2、注册公司无需验资实缴; 3、塞浦路斯公司分红没有税收; 4、塞浦路斯拥有欧洲蕞低的企业所得税,为净利润的 12.5%; 5、除某些特定业务要在经营前获得许可,基…...

XSS盲打练习(简单认识反射型、存储型XSS和cookie欺骗)
文章目录 挖掘cms网站XSS漏洞利用XSS平台盲打CMS,获取后台管理cookiecookie欺骗登录管理员账户 挖掘cms网站XSS漏洞 来到cms网站主页,发现有一个搜索框,输入任意内容后搜索,发现内容会回显,这里可能存在反射型XSS漏洞…...
Shell脚本:基础知识和使用指南
Shell脚本是一种用于自动化任务和进程的强大工具。它们允许你编写一系列的命令,这些命令可以在shell环境中执行,从而避免了手动输入每个命令的需要。Shell脚本对于减少重复的工作、提高效率以及构建复杂的自动化过程非常有用。 什么是Shell?…...

Resource Hacker下载
下载路径 Resource Hacker (angusj.com)http://www.angusj.com/resourcehacker/#download 应用方法示例 【2023年更新】手把手教你去除 WinRAR 的弹窗广告_winrar广告怎么去除_areosun的博客-CSDN博客https://blog.csdn.net/haiyunzhiqiu/article/details/119176038#comment…...

Kubernetes入门 十、HPA 自动扩/缩容
目录 概述安装metrics-server使用HPA 概述 我们已经可以通过手动执行 kubectl scale 命令实现Pod的扩缩容,但是这显然不符合 Kubernetes 的定位目标–自动化和智能化。Kubernetes 期望可以通过监测Pod的使用情况,实现 Pod 数量的自动调整,于…...

Django报错:SystemCheckError: System check identified some issues解决办法
今天练习django自定义标签时,一开始在APPbook中写了自定义标签book_tags.py 测试成功,之后新建了一个APPblogs,测试在blogs中创建模板使用自定义标签,于是直接把book/templatetags包直接赋值到blogs目录里。在页面里加载自定义标…...

JavaScript设计模式(一)——构造器模式、原型模式、类模式
个人简介 👀个人主页: 前端杂货铺 🙋♂️学习方向: 主攻前端方向,正逐渐往全干发展 📃个人状态: 研发工程师,现效力于中国工业软件事业 🚀人生格言: 积跬步…...
python 读文件,跳过有问题的那行
报错: UnicodeDecodeError: gbk codec cant decode byte 0xfa in position 4781: illegal multibyte sequence 解决方案: f open("20091012134152.txt", encoding"gbk", mode"r") 改成 f open("20091012134152.tx…...

Spring Boot Actuator的Env端点存在本地文件包含(LFI)漏洞CVE-2020-5421
文章目录 0.前言1.参考文档2.基础介绍3.漏洞利用原理3.解决方案1. 升级Spring Boot版本2. 限制端点的访问3. 禁用环境端点4. 不公开敏感的Actuator端点5. 开启安全审计 0.前言 背景: Spring Boot Actuator的Env端点存在本地文件包含(LFI)漏洞CVE-2020-5421。被扫描到…...
Leetcode 最后一个单词的长度
给你一个字符串 s,由若干单词组成,单词前后用一些空格字符隔开。返回字符串中 最后一个 单词的长度。 单词 是指仅由字母组成、不包含任何空格字符的最大子字符串。 示例 1: 输入:s “Hello World” 输出:5 解释&a…...
css ,less和sass的区别[简洁易懂
CSS、Less和Sass都是用于样式表编写和管理的技术,它们之间有以下区别: CSS(层叠样式表)是一种标准的样式表语言,用于描述网页的外观和样式。它是前端开发中最基础和常用的技术,所有网页都需要使用CSS来定义…...

有了ChatGPT的帮助,开发者的生产力会提高10-100倍
在过去的几十年里,可以说没有哪个领域像软件开发那样,以如此激进的方式发展。相关的技术层出不穷,速度惊人,生成式人工智能的出现更是将让这些发展继续加速。 尽管有些人猜测生成式人工智能产品将会终结软件开发这个人类职业&am…...
win10+QT5.15+cryptopp562 完整配置开发
1、准备如下几项内容: a、WIN10环境下的QT5.15.2安装包,QTCreator对应版本安装。(自行安装) b、cryptopp562安装包下载,官网:https://www.cryptopp.com/,这里没选择最新的8.7是因为mingw-32编译…...
多线程学习之多线程的案例
练习一:赠送礼物 需求:有100份礼品,两人同时发送,当剩下的礼品小于10份的时候则不再送出。利用多线程模拟该过程并将线程的名字和礼物的剩余数量打印出来. 示例: public class MyRunable implements Runnable {//第二种方式实现…...

iTOP-RK3588开发板Android12 设置系统默认不休眠
修改文件: device/rockchip/rk3588/overlay/frameworks/base/packages/SettingsProvider/res/values/defaults. xml 文件,如下图所示: - <integer name"def_screen_off_timeout">60000</integer> <integer name&q…...
java系统问题定位思路
一、在不同环境排查问题,有不同的方式 1、如果是在自己的开发环境排查问题,那你几乎可以使用任何自己熟悉的工具来排查,甚至可以进行单步调试。只要问题能重现,排查就不会太困难,最多就是把程序调试到 JDK 或三方类库内…...
redis jedis 单元测试 报错集锦 汇总 junit
redis报错汇总 在单元测试时,使用jedis通常遇到如下报错: 实例化报错->连接报错->权限报错。此报错是有顺序的:例如,若连接报错,说明实例化完成,即配置文件配对了。若权限报错,说明连接…...
浅谈 React Hooks
React Hooks 是 React 16.8 引入的一组 API,用于在函数组件中使用 state 和其他 React 特性(例如生命周期方法、context 等)。Hooks 通过简洁的函数接口,解决了状态与 UI 的高度解耦,通过函数式编程范式实现更灵活 Rea…...

铭豹扩展坞 USB转网口 突然无法识别解决方法
当 USB 转网口扩展坞在一台笔记本上无法识别,但在其他电脑上正常工作时,问题通常出在笔记本自身或其与扩展坞的兼容性上。以下是系统化的定位思路和排查步骤,帮助你快速找到故障原因: 背景: 一个M-pard(铭豹)扩展坞的网卡突然无法识别了,扩展出来的三个USB接口正常。…...

springboot 百货中心供应链管理系统小程序
一、前言 随着我国经济迅速发展,人们对手机的需求越来越大,各种手机软件也都在被广泛应用,但是对于手机进行数据信息管理,对于手机的各种软件也是备受用户的喜爱,百货中心供应链管理系统被用户普遍使用,为方…...

CTF show Web 红包题第六弹
提示 1.不是SQL注入 2.需要找关键源码 思路 进入页面发现是一个登录框,很难让人不联想到SQL注入,但提示都说了不是SQL注入,所以就不往这方面想了 先查看一下网页源码,发现一段JavaScript代码,有一个关键类ctfs…...

MFC内存泄露
1、泄露代码示例 void X::SetApplicationBtn() {CMFCRibbonApplicationButton* pBtn GetApplicationButton();// 获取 Ribbon Bar 指针// 创建自定义按钮CCustomRibbonAppButton* pCustomButton new CCustomRibbonAppButton();pCustomButton->SetImage(IDB_BITMAP_Jdp26)…...

3.3.1_1 检错编码(奇偶校验码)
从这节课开始,我们会探讨数据链路层的差错控制功能,差错控制功能的主要目标是要发现并且解决一个帧内部的位错误,我们需要使用特殊的编码技术去发现帧内部的位错误,当我们发现位错误之后,通常来说有两种解决方案。第一…...

vscode(仍待补充)
写于2025 6.9 主包将加入vscode这个更权威的圈子 vscode的基本使用 侧边栏 vscode还能连接ssh? debug时使用的launch文件 1.task.json {"tasks": [{"type": "cppbuild","label": "C/C: gcc.exe 生成活动文件"…...
【学习笔记】深入理解Java虚拟机学习笔记——第4章 虚拟机性能监控,故障处理工具
第2章 虚拟机性能监控,故障处理工具 4.1 概述 略 4.2 基础故障处理工具 4.2.1 jps:虚拟机进程状况工具 命令:jps [options] [hostid] 功能:本地虚拟机进程显示进程ID(与ps相同),可同时显示主类&#x…...

OPenCV CUDA模块图像处理-----对图像执行 均值漂移滤波(Mean Shift Filtering)函数meanShiftFiltering()
操作系统:ubuntu22.04 OpenCV版本:OpenCV4.9 IDE:Visual Studio Code 编程语言:C11 算法描述 在 GPU 上对图像执行 均值漂移滤波(Mean Shift Filtering),用于图像分割或平滑处理。 该函数将输入图像中的…...
Java多线程实现之Thread类深度解析
Java多线程实现之Thread类深度解析 一、多线程基础概念1.1 什么是线程1.2 多线程的优势1.3 Java多线程模型 二、Thread类的基本结构与构造函数2.1 Thread类的继承关系2.2 构造函数 三、创建和启动线程3.1 继承Thread类创建线程3.2 实现Runnable接口创建线程 四、Thread类的核心…...