MySQL之复合查询与内外连接

目录

一、多表查询

二、自连接

三、子查询

四、合并查询

五、表的内连接和外连接

1、内连接

2、外连接

 

前面我们讲解的mysql表的查询都是对一张表进行查询，即数据的查询都是在某一时刻对一个表进行操作的。而在实际开发中，我们往往还需要对多个表同时进行查询。

我们这里使用的测试表，为雇员信息表(来自Oracle 9i的经典测试表)：EMP员工表，DEPT部门表，SALGRADE工资等级表。

EMP员工表：

9e6a91767c844cffb9490385365b5da3.png

DEPT部门表：

55b5468f92e54b12bc3bd854eeee5f7b.png

SALGRADE工资等级表：

0d9636c88da34da1b95067d1c3271a4f.png

一、多表查询

之前我们都是从一张表拿数据，但是实际开发中，我们需要的数据往往来自不同的表，所以需要进行多表查询。

~ 显示雇员名、雇员工资以及所在部门的名字

笛卡儿积

首先，我们需要介绍的就是笛卡尔积。

根据上面的需求，我们需要的数据是雇员名，雇员工资和雇员所在部门的名字，显而易见，雇员名和雇员工资均在emp表中，而雇员所在的部门的名字是在dept表中，这就明确要求我们需要去两张表中查询数据。

那么我就简单地去同时查询两张表：

0401d5a96d0d4c7d9758e5ef96fed5f5.png

我们发现，如果我们直接对两张表进行整合的话，其整合方式如下：

94284de58a49465d8032e45943c84dab.png

在进行多表查询时，只需要将多张表的表名依次放到from子句之后，用逗号隔开即可，这时MySQL将会对给定的这多张表取笛卡尔积，作为多表查询的初始数据。

对多张表取笛卡尔积，就是得到这多张表的记录的所有可能有序对组成的集合。即，拿一张表的一条记录与另一张表的所有记录进行组合，得到新的记录。所以，我们上面 select * from emp,dept 最终得到的结果便是员工表emp和部门表dept的笛卡尔积。

但是，对员工表和部门表取笛卡尔积时，员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合，而实际一个员工只有和自己所在的部门信息进行组合才是有意义的，因此需要从笛卡尔积中筛选出员工的部门号和部门的编号相等的记录。

mysql> select * from emp,dept where emp.deptno=dept.deptno;

f789d0799afc43e4868d5bf797b82ae6.png

所以说，最终我们可以这样解决这个需求：

mysql> select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;

 e89d7157e29040359541aba236f33ac8.png

~ 显示部门号为10的部门名，员工名和工资 

首先，我们需要明确，部门名在dept表中，而员工名和工资在emp表中，所以我们需要去两张表中查询数据。

select dname,ename,sal from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;

26d93ef82a494e84bbead2375356b16d.png

~ 显示各个员工的姓名，工资，及工资级别

首先，我们需要明确，工资级别在salgrade表中，而员工的姓名和工资在emp表中，所以我们需要去两张表中查询数据。

select ename,sal,grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal;

b8f19d7cf3f648cb9865b7f93989131a.png

二、自连接

自连接是指在同一张表进行数据查询，也就是说我们不仅可以取不同表的笛卡尔积，也可以对同一张表取笛卡尔积。

~ 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号）

为了解决这个需求，我们需要两步。第一步，在emp表中找到员工Ford上级领导的编号mgr；第二步，查找到的mgr就是emp表中某一个员工的empno，根据 mgr == empno 的条件，就可以找到Ford上级领导的编号和姓名。

而这两次查询均是在emp表中进行的，所以我们可以对同一张表进行笛卡尔积来进行查询。

select t2.empno,t2.ename from emp t1,emp t2 where t1.ename='FORD' and t1.mgr=t2.empno;

 6e3ef76852bf4b818bcb01f38d2ecc6c.png

三、子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

单行子查询（返回一行记录的子查询）

~ 显示SMITH同一部门的员工 

首先，我们分析一下需求，需要找到与Smith在同一个部门的员工。那么首先我们就需要找到Smith在哪个部门。

mysql> select ename,deptno from emp where ename='SMITH';

8201cfadc9a540d3b2337e743df96159.png  

然后我们就可以根据Smith所在的部门进行查询，进而找到与Smith在同一部门的员工。

select * from emp where deptno=(select deptno from emp where ename='SMITH');

5a73d5ab88e340cfba65bf15ff5d197d.png

查找出来的记录的deptno和Smith一样，都是20。 

多行子查询（返回多行记录的子查询） 

~ 查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的（in关键字） 

首先，分析一下需求，我们需要先找到10号部门有哪些工作岗位。

mysql> select job from emp where deptno=10;

4d374634e7594706b10d781db67194cc.png

也就是说，我们要查询的雇员信息数据是满足雇员的岗位属于 MANAGER，PRESIDENT，CLERK这三个中的一种的。

所以，我们可以将上述查询作为子查询，在查询员工表时在where子句中使用in关键字，判断员工的工作岗位是否是子查询得到的若干岗位中的一个。

select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10);

720388dd64e64e5ead94bf21cec7b975.png

由于要求筛选出来的员工需要不包含10号部门的，因此还需要在where子句中指明筛选条件为部门号不等于10。所以，最终的查询语句如下：

select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10) and deptno<>10;

b143f575f78e4d749e3669a960ff8beb.png

~ 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号（all关键字）

首先，分析一下需求，我们需要先找到30号部门所有员工的工资，在查询时最好对结果进行去重，因为30号部门的某些员工的工资可能是相同的，而我们可以不需要重复的结果。

mysql> select distinct sal from emp where deptno=30;

12037fcef7e942929dd8c21eebac6cf2.png

也就是说，我们要查询的雇员信息数据是满足雇员的工资大于上图中的最大工资的，也就是大于上图中所有的工资。

所以说，我们最终的查询语句如下：

select ename,sal,deptno from emp where sal>all(select distinct sal from emp where deptno=30);

24ba5582e8424e1c8a87338eaac03a29.png

~ 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工，any关键字）

上面我们已经找到了30号部门所有员工的工资，而我们要查询的雇员信息数据是满足雇员的工资大于30号部门任意一个员工的工资的雇员。

select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);

0b331d53f4be44f28d56275a0497569a.png

多列子查询 

单行子查询是指子查询只返回单列，单行数据。多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句。

~ 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

首先，分析一下需求，我们需要先找到Smith的部门和岗位。

select ename,deptno,job from emp where ename='SMITH';

a70861d69d4340ac9b93e284a391131b.png

然后将上述查询作为子查询，在查询员工表时在where子句中，指明筛选条件为部门号和岗位等于子查询得到的部门号和岗位，并且员工的姓名不为SMITH即可。

select ename from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH') and ename<>'SMITH';

989c4bcff9874026aba07072649c38f2.png

在from子句中使用子查询

子查询语句不仅可以出现在where子句中，也可以出现在from子句中。

子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。 

~ 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资 

首先，分析一下需求，我们需要先查询每个部门的平均工资。

mysql> select deptno,avg(sal) from emp group by deptno;

027e2901b9fb4faeb42c4ee2900881b3.png

上图所显示的数据中包含部门的平均工资，而且只有上表有平均工资的数据，现成的表emp，dept和salgrade都没有平均工资的数据。所以我们需要同时使用emp员工表和上述的查询结果进行多表查询，这时可以将上述查询作为子查询放在from子句中，然后对emp员工表和临时表取笛卡尔积，然后进行筛选。 

select t1.deptno,ename,t1.sal,t2.mysal from emp t1,(select deptno,avg(sal) mysal from emp group by deptno) t2 where t1.deptno=t2.deptnoptno and t1.sal>t2.mysal;

fd0b2081464f41c1af0a76d6f6461d96.png  

~ 查找每个部门工资最高的人的姓名、工资、部门、最高工资

首先，我们需要知道每个部门的最高工资。

mysql> select deptno,max(sal) from emp group by deptno;

a2d7bf8b54bf4b608a40b314ddcbc73e.png

然后，将上述查询作为子查询放在from子句中，然后对emp员工表和临时表取笛卡尔积，进而进行筛选。

select ename,sal,t1.deptno,maxsal from emp t1,(select deptno,max(sal) maxsal from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.sal=t2.maxsal;

 eb5d66393c4340fd9e6a7ebfd02fe9e4.png

~ 显示每个部门的信息（部门名，编号，地址）和人员数量

部门名，编号均来自emp表，地址则是来自dept表。很显然，这需要从两张表中进行查询。

select t1.deptno,dname,loc,mycount from dept t1,(select deptno,count(*) mycount from emp group by deptno) t2 where t1.deptno=t2.deptno;

93a00980fea84f30a64e7bafd3a8722e.png

注：在from子句中使用子查询时，必须给子查询得到的临时表取一个别名，否则查询将会出错。

四、合并查询

合并查询，是指将多个查询结果进行合并。

union：union的作用是取得两个查询结果的并集，union会自动去掉结果集中的重复行。 

union all ：union all的作用是取得两个查询结果的并集，但union all不会去掉结果集中的重复行。 

~ 显示工资大于2500或职位是MANAGER的员工 

查询工资大于2500的员工：

mysql> select ename,job,sal from emp where sal>2500;

cb5fefffb9ab4f29afba67677e385de8.png

查询职位是MANAGER的员工：

mysql> select ename,job,sal from emp where job='MANAGER';

 359915ed003444b185c50c22e75dc347.png

查询工资大于2500或职位是MANAGER的员工，可以使用union将上述的两条查询SQL语句连接起来。

select ename,job,sal from emp where sal>2500 union select ename,job,sal from emp where job='MANAGER';

21d5fac3cb1642f2bfb58d572ab0311f.png

当然，我们也可以使用union all将上述的两条查询SQL语句连接起来，但不会对合并后的结果进行去重。

select ename,job,sal from emp where sal>2500 union all select ename,job,sal from emp where job='MANAGER';

c7ebdd0248ef4f708f388ddbf1806f24.png

注：待合并的两个查询结果的列的数量必须一致，否则无法合并。 

五、表的内连接和外连接

1、内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

~ 显示SMITH的名字和部门名称 

我们之前的写法是直接使用笛卡尔积进行查询。

select ename,dname from emp,dept where emp.deptno=dept.deptno and ename='SMITH';

9d463c70383f4d6fa657e6863e53c45c.png

如果我们使用标准的内连接写法去写的话，就是如下的写法：

select ename,dname from emp inner join dept on emp.deptno=dept.deptno and ename='SMITH';

6f3426a1b17048d2a20429ac33342715.png 

2、外连接

外连接分为左外连接和右外连接。

左外连接

如果联合查询，左侧的表完全显示我们就说是左外连接。 

语法：

select 字段名  from 表名1 left join 表名2 on 连接条件;

右外连接 

如果联合查询，右侧的表完全显示我们就说是右外连接。 

语法：

select 字段 from 表名1 right join 表名2  on 连接条件;

 

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

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

相关文章

15分钟学 Python 第41天：Python 爬虫入门（六）第二篇

15分钟学 Python 第41天：Python 爬虫入门（六）第二篇

Day41&#xff1a;Python爬取猫眼电影网站的电影信息 1. 项目背景 在本项目中&#xff0c;我们将使用 Python 爬虫技术从猫眼电影网站抓取电影信息。猫眼电影是一个知名的电影信息平台&#xff0c;提供了丰富的电影相关数据。通过这个练习&#xff0c;您将深入学习如何抓取动…
阅读更多...
Android Compose的基本使用

Android Compose的基本使用

前言: Compose这个东西呢,好处我没发现,坏处就是学习成本和低版本兼容. 不过,看在官方力推的份儿上,有空就学一下吧. 当初的kotlin,很多人说鸡肋(包括我)!现在不也咔咔用纯kotlin做项目吗?哈哈哈哈. 未来的事情,谁说得清呢? 首先创建一个专用的Compose项目 对没错!看到E…
阅读更多...
大数据新视界 --大数据大厂之 从 Druid 和 Kafka 到 Polars：大数据处理工具的传承与创新

大数据新视界 --大数据大厂之 从 Druid 和 Kafka 到 Polars：大数据处理工具的传承与创新

&#x1f496;&#x1f496;&#x1f496;亲爱的朋友们&#xff0c;热烈欢迎你们来到 青云交的博客&#xff01;能与你们在此邂逅&#xff0c;我满心欢喜&#xff0c;深感无比荣幸。在这个瞬息万变的时代&#xff0c;我们每个人都在苦苦追寻一处能让心灵安然栖息的港湾。而 我的…
阅读更多...
【笔记】数据结构12

【笔记】数据结构12

文章目录 2013年408应用题41方法一方法二 看到的社区的一个知识总结&#xff0c;这里记录一下。 知识点汇总 2013年408应用题41 解决方法&#xff1a; 方法一 &#xff08;1&#xff09;算法思想 算法的策略是从前向后扫描数组元素&#xff0c;标记出一个可能成为主元素的元…
阅读更多...
elasticsearch ES DBA常用语句

elasticsearch ES DBA常用语句

一、 查看集群状态 curl -uelastic 连接串:端口/_cluster/health?pretty 集群健康有三种状态&#xff1a;green,yellow,red green&#xff1a;所有主要分片、复制分片都可用yellow&#xff1a;所有主要分片可用&#xff0c;但不是所有复制分片都可用red&#xff1a;不是所有…
阅读更多...
根据拍摄时间一键将图片分组

根据拍摄时间一键将图片分组

说在前面 最近裸辞出去玩了两个多月&#xff0c;旅行的过程中少不了拍照&#xff0c;祖国的大好河山太美了&#xff0c;一趟旅行下来产出了1w多张图片&#xff0c;所以回来后总要整理一下图片&#xff0c;我这边想要的是根据拍摄时间来对图片进行分组,所以回到家后我就写了这样…
阅读更多...
通信工程学习：什么是ICP网络内容服务商

通信工程学习：什么是ICP网络内容服务商

ICP&#xff1a;网络内容服务商 ICP&#xff0c;全称Internet Content Provider&#xff0c;即网络内容服务商&#xff0c;是指那些通过互联网向用户提供各种类型内容服务的组织或个人。ICP在数字化时代扮演着至关重要的角色&#xff0c;它们不仅是信息的传播者&#xff0c;更是…
阅读更多...
Linux高级编程_29_信号

Linux高级编程_29_信号

文章目录 进程间通讯 - 信号信号完整的信号周期信号的编号信号的产生发送信号1 kill 函数(他杀)作用&#xff1a;语法&#xff1a;示例&#xff1a; 2 raise函数(自杀)作用&#xff1a;示例&#xff1a; 3 abort函数(自杀)作用&#xff1a;语法&#xff1a;示例&#xff1a; 4 …
阅读更多...
苏州 数字化科技展厅展馆-「世岩科技」一站式服务商

苏州 数字化科技展厅展馆-「世岩科技」一站式服务商

数字化科技展厅展馆设计施工是一个综合性强、技术要求高的项目&#xff0c;涉及到众多方面的要点。以下是对数字化科技展厅展馆设计施工要点的详细分析&#xff1a; 一、明确目标与定位 在设计之初&#xff0c;必须明确展厅的目标和定位。这包括确定展厅的主题、目标受众、展…
阅读更多...
详解正确创建好SpringBoot项目后但是找不到Maven的问题

详解正确创建好SpringBoot项目后但是找不到Maven的问题

目录 问题 解决步骤&#xff1a; 找到File->Project Structure... 设置SDK 设置SDKs 问题 刚刚在使用IDEA专业版创建好SpringBoot项目后&#xff0c;发现上方导航栏的运行按钮是灰色的&#xff0c;而且左侧导航栏的pom.xml的图标颜色也不是正常的&#xff0c;与此同时我…
阅读更多...
PIKACHU | PIKACHU 靶场 XSS 后台配置

PIKACHU | PIKACHU 靶场 XSS 后台配置

关注这个靶场的其他相关笔记&#xff1a;PIKACHU —— 靶场笔记合集-CSDN博客 PIKACHU 自带了一个 XSS 平台&#xff0c;可以辅助我们完成 XSS 攻击&#xff0c;但是该后台需要配置数据库以后才能使用。本教程&#xff0c;就是教大家如何配置 PIKACHU XSS 平台的。 PIKACHU XS…
阅读更多...
在线教育的未来：SpringBoot技术实现

在线教育的未来：SpringBoot技术实现

1系统概述 1.1 研究背景 随着计算机技术的发展以及计算机网络的逐渐普及&#xff0c;互联网成为人们查找信息的重要场所&#xff0c;二十一世纪是信息的时代&#xff0c;所以信息的管理显得特别重要。因此&#xff0c;使用计算机来管理微服务在线教育系统的相关信息成为必然。开…
阅读更多...
Hadoop大数据入门——Hive-SQL语法大全

Hadoop大数据入门——Hive-SQL语法大全

Hive SQL 语法大全 基于语法描述说明 CREATE DATABASE [IF NOT EXISTS] db_name [LOCATION] path; SELECT expr, ... FROM tbl ORDER BY col_name [ASC | DESC] (A | B | C)如上语法&#xff0c;在语法描述中出现&#xff1a; []&#xff0c;表示可选&#xff0c;如上[LOCATI…
阅读更多...
基于深度学习的乳腺癌分类识别与诊断系统

基于深度学习的乳腺癌分类识别与诊断系统

温馨提示&#xff1a;文末有 CSDN 平台官方提供的学长 QQ 名片 :) 1. 项目简介 乳腺癌是全球最常见的癌症之一&#xff0c;早期诊断对于治疗效果至关重要。近年来&#xff0c;深度学习技术在医学图像分析领域取得了显著进展&#xff0c;能够从大量的医学影像数据中自动学习和提…
阅读更多...
【Android 14源码分析】WMS-窗口显示-第一步：addWindow

【Android 14源码分析】WMS-窗口显示-第一步：addWindow

忽然有一天&#xff0c;我想要做一件事&#xff1a;去代码中去验证那些曾经被“灌输”的理论。                                                                                  – 服装…
阅读更多...
网络安全概述：从认知到实践

网络安全概述：从认知到实践

一、定义 网络安全&#xff0c;即致力于保护网络系统所涵盖的硬件、软件以及各类数据&#xff0c;切实保障其免遭破坏、泄露或者篡改等不良情形的发生。 二、重要性 个人层面&#xff1a;着重于守护个人隐私以及财产安全&#xff0c;为个人在网络世界中的各项活动提供坚实的保…
阅读更多...
Redis篇（Redis原理 - 数据结构）（持续更新迭代）

Redis篇（Redis原理 - 数据结构）（持续更新迭代）

目录 一、动态字符串 二、intset 三、Dict 1. 简介 2. Dict的扩容 3. Dict的rehash 4. 知识小结 四、ZipList 1. 简介 2. ZipListEntry 3. Encoding编码 五、ZipList的连锁更新问题 六、QuickList 七、SkipList 八、RedisObject 1. 什么是 redisObject 2. Redi…
阅读更多...
使用JavaScript写一个网页端的四则运算器

使用JavaScript写一个网页端的四则运算器

目录 style(内联样式表部分) body部分 html script 总的代码 网页演示 style(内联样式表部分) <style>body {font-family: Arial, sans-serif;display: flex;justify-content: center;align-items: center;height: 100vh;background-color: #f0f0f0;}.calculator {…
阅读更多...
Python开发环境配置（mac M2）

Python开发环境配置（mac M2）

1. 前言 作为一名程序员&#xff0c;工作中需要使用Python进行编程&#xff0c;甚至因为项目需要还得是不同版本的Python如何手动管理多个版本的Python&#xff0c;如何给Pycharm&#xff08;IDE&#xff09;配置对应的interpreter等&#xff0c;都成为一个 “不熟练工” 的难…
阅读更多...
使用百度文心智能体创建多风格表情包设计助手

使用百度文心智能体创建多风格表情包设计助手

文章目录 一、智能定制&#xff0c;个性飞扬二、多元风格&#xff0c;创意无限 百度文心智能体平台为你开启。百度文心智能体平台&#xff0c;创建属于自己的智能体应用。百度文心智能体平台是百度旗下的智能AI平台&#xff0c;集成了先进的自然语言处理技术和人工智能技术&…
阅读更多...
最新文章

Copyright @ 2022~2023