【MySQL】第十一弹---复合查询全攻略：多表、自连接、子查询与合并查询

server/2025/3/2 1:12:39/

✨个人主页： 熬夜学编程的小林

💗系列专栏： 【C语言详解】 【数据结构详解】【C++详解】【Linux系统编程】【MySQL】

目录

1. 复合查询

1.1 基本查询回顾

1.2 多表查询

1.3 自连接

1.4 子查询

1.4.1 单行子查询

1.4.2 多行子查询

1.4.3 多列子查询

1.4.4 在from子句中使用子查询

1.4.5 合并查询

1.4.5.1 union

1.4.5.3 union all

1. 复合查询

前面我们讲解的mysql表的查询都是对一张表进行查询，在实际开发中这远远不够。

1.1 基本查询回顾

  • 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J
# 使用%通配符
mysql> select * from emp where (sal > 500 or job ='MANAGER') and ename like 'J%';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007900 | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)# 使用字符串截取函数
mysql> select * from emp where (sal > 500 or job ='MANAGER') and substring(ename,1,1)='J';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007900 | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)

  • 按照部门号升序而雇员的工资降序排序 
select * from emp order by deptno asc,sal desc;

  • 使用年薪进行降序排序 

年薪 = 月薪 * 12 或者 年薪 = 月薪 * 12 + 奖金(comm)

年薪 = 月薪 * 12

select ename,sal*12 年薪 from emp order by 年薪 desc;

 年薪 = 月薪 * 12 + 奖金

select ename,sal*12+ifnull(comm,0) 年薪 from emp order by 年薪 desc;

  • 显示工资最高的员工的名字和工作岗位
mysql> select ename,job,sal from emp where sal = (select max(sal) from emp);
+-------+-----------+---------+
| ename | job       | sal     |
+-------+-----------+---------+
| KING  | PRESIDENT | 5000.00 |
+-------+-----------+---------+
1 row in set (0.00 sec)

 

  • 显示工资高于平均工资的员工信息 
mysql> select * from emp where sal > (select avg(sal) from emp);
+--------+-------+-----------+------+---------------------+---------+------+--------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
| 007782 | CLARK | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
| 007788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
| 007839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)

 

  •  显示每个部门的平均工资和最高工资
mysql> select deptno,avg(sal),max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     10 | 2916.666667 |  5000.00 |
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
+--------+-------------+----------+
3 rows in set (0.00 sec)

  • 显示平均工资低于2000的部门号和它的平均工资 
mysql> select deptno,avg(sal) myavg from emp group by deptno having myavg < 2000;
+--------+-------------+
| deptno | myavg       |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)

  •  显示每种岗位的雇员总数，平均工资
mysql> select deptno,count(*),avg(sal) from emp group by deptno;
+--------+----------+-------------+
| deptno | count(*) | avg(sal)    |
+--------+----------+-------------+
|     10 |        3 | 2916.666667 |
|     20 |        5 | 2175.000000 |
|     30 |        6 | 1566.666667 |
+--------+----------+-------------+
3 rows in set (0.00 sec)

1.2 多表查询

实际开发中往往数据来自不同的表，所以需要多表查询。本节我们用一个简单的公司管理系统，有三张表EMP,DEPT,SALGRADE来演示如何进行多表查询。

案例：

  • 显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询

其实我们只要emp表中的deptno = dept表中的deptno字段的记录 

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

  • 显示部门号为10的部门名，员工名和工资 
select dname,ename,sal from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

  •  显示各个员工的姓名，工资，及工资级别
select ename,sal,grade from emp,salgrade where sal>=losal and sal<=hisal;
select ename,sal,grade from emp,salgrade where sal between losal and hisal;

1.3 自连接

自连接是指在同一张表连接查询。

案例：

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

使用的子查询： 

select empno,ename from emp where empno = (select mgr from emp where ename = 'FORD');

使用多表查询（自查询） ：

1.4 子查询

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

1.4.1 单行子查询

返回一行记录的子查询

  • 显示SMITH同一部门的员工 
select * from emp where deptno = (select deptno from emp where ename = 'SMITH');

1.4.2 多行子查询

返回多行记录的子查询。

  • in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的 
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno = 10) and deptno != 10;select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno = 10) and deptno <> 10;

 不等于可以使用 != 也可以使用 <> 

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

查出30号部门的最高工资，大于这个最高工资的员工姓名、工资和部门号。

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

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

 

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

 查出30号部门的最低工资，大于这个最低工资的员工姓名、工资和部门号。

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

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

1.4.3 多列子查询

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

案例：

  • 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人 
select ename from emp where (deptno,job) = (select deptno,job from emp where ename = 'SMITH') and ename <> 'SMITH';

1.4.4 在from子句中使用子查询

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

案例：

  • 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资 
select ename,deptno,sal,format(asal,2) from emp,(select avg(sal) asal,deptno dt from emp group by deptno) tmp where emp.sal > tmp.asal and emp.deptno = tmp.dt;

  • 查找每个部门工资最高的人的姓名、工资、部门、最高工资 
select ename,sal,deptno,msal from emp,(select deptno dt,max(sal) msal from emp group by deptno) tmp where emp.deptno = tmp.dt and emp.sal = tmp.msal;

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

方法1：使用多表 

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

方法2：使用子查询 

select dept.deptno,dname,mycnt,loc from dept,(select count(*) mycnt,deptno from emp group by deptno) tmp where dept.depptno = tmp.deptno;

1.4.5 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。

1.4.5.1 union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

案例：

  • 将工资大于2500或职位是MANAGER的人找出来 
# 会去重
select * from emp where sal > 2500 union select * from emp where job = 'MANAGER';

1.4.5.3 union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

案例：

  • 将工资大于25000或职位是MANAGER的人找出来 
# 不去重
select * from emp where sal > 25000 union all select * from emp where job = 'MANAGER';


http://www.ppmy.cn/server/171667.html

相关文章

【MySQL】 数据类型

【MySQL】 数据类型

欢迎拜访&#xff1a;-CSDN博客 本篇主题&#xff1a;【MySQL】 数据类型 发布时间&#xff1a;2025.1.27 隶属专栏&#xff1a;MySQL 目录 数据类型分类数值类型 tinyint类型 数值越界测试结果说明 bit类型 基本语法使用注意事项 小数类型 float 语法使用注意事项 decimal 语…
阅读更多...
vue el-table-column 单元表格的 省略号 实现

vue el-table-column 单元表格的 省略号 实现

要对 el-table-column 的某一列中的每个单元格值进行处理&#xff0c;使其在文本内容超出指定宽度时显示省略号&#xff08;…&#xff09;&#xff0c;可以通过以下方法实现&#xff1a; 使用 scoped slots&#xff1a;利用 Element UI 提供的 scoped slots 自定义单元格内容…
阅读更多...
【华为OD机考】华为OD笔试真题解析(16)--微服务的集成测试

【华为OD机考】华为OD笔试真题解析(16)--微服务的集成测试

题目描述 现在有n个容器服务&#xff0c;服务的启动可能有一定的依赖性&#xff08;有些服务启动没有依赖&#xff09;&#xff0c;其次&#xff0c;服务自身启动加载会消耗一些时间。 给你一个 n n n \times n nn的二维矩阵useTime&#xff0c;其中useTime[i][i]10表示服务…
阅读更多...
Java集合性能优化面试题

Java集合性能优化面试题

Java集合性能优化面试题 初始化优化 Q1: 如何优化集合的初始化&#xff1f; public class CollectionInitializationExample {// 1. 合理设置初始容量public void initializationOptimization() {// 不好的实践&#xff1a;使用默认容量List<String> badList new Arr…
阅读更多...
如何利用爬虫获取淘宝评论API接口：技术解析与实战指南

如何利用爬虫获取淘宝评论API接口：技术解析与实战指南

在电商领域&#xff0c;商品评论数据是商家优化产品、提升用户体验以及进行市场分析的关键资源。淘宝作为国内领先的电商平台&#xff0c;提供了丰富的API接口&#xff0c;允许开发者通过编程方式获取商品评论信息。本文将详细介绍如何利用Python爬虫技术调用淘宝评论API接口&a…
阅读更多...
pytest下放pytest.ini文件就导致报错：ERROR: file or directory not found: #

pytest下放pytest.ini文件就导致报错：ERROR: file or directory not found: #

pytest下放pytest.ini文件就导致报错&#xff1a;ERROR: file or directory not found: # 如下&#xff1a; 项目文件目录如下&#xff1a; pytest.ini文件内容&#xff1a; [pytest] addopts -v -s --alluredir ./allure-results # 自动添加的命令行参数&#xff1a;# -…
阅读更多...
Deepseek 实战全攻略，领航科技应用的深度探索之旅

Deepseek 实战全攻略，领航科技应用的深度探索之旅

想玩转 Deepseek&#xff1f;这攻略别错过&#xff01;先带你了解它的基本原理&#xff0c;教你搭建运行环境。接着给出自然语言处理、智能客服等应用场景的实操方法与代码。还分享模型微调、优化技巧&#xff0c;结合案例加深理解&#xff0c;让你全面掌握&#xff0c;探索科技…
阅读更多...
STM32-智能台灯项目

STM32-智能台灯项目

一、项目需求 1. 红外传感器检测是否有人&#xff0c;有人的话实时检测距离&#xff0c;过近则报警&#xff1b;同时计时&#xff0c;超过固定时间则报警&#xff1b; 2. 按键 1 切换工作模式&#xff1a;智能模式、按键模式、远程模式&#xff1b; 3. 智能模式下&#xff0c;根…
阅读更多...
最新文章

Copyright @ 2022~2023