MySQL复合查询

最新推荐文章于 2024-07-22 17:39:53 发布
最新推荐文章于 2024-07-22 17:39:53 发布
阅读量420 收藏 1
点赞数 2
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Ryujianli/article/details/132580536
版权

文章目录

MySQL复合查询

1. 基本查询回顾

(1) 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

mysql> select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';

在这里插入图片描述

也可以使用内置函数来查询首字母为大写的J

mysql> select * from emp where (sal > 500 or job = 'MANAGER') and 
    -> substring(ename, 1, 1) = 'J';

在这里插入图片描述

(2) 按照部门号升序而雇员的工资降序排序

mysql> select * from emp order by deptno asc, sal desc;

在这里插入图片描述

(3) 使用年薪进行降序排序

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

  • 这里有的comm为NULL，NULL与任何值做计算得到的结果都是NULL，所以若是直接用NULL来计算得到结果还是NULL。
  • comm的运算可以使用ifnull函数来comm判断是否为NULL，若不为NULL则ifnull函数返回员工的comm，如果为NULL则ifnull函数返回0，避免让NULL值参与计算

直接用comm的NULL参与运算

在这里插入图片描述

正确写法：

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

在这里插入图片描述

(4) 显示工资最高的员工的名字和工作岗位

解决该问题有两种方案:

  1. 使用两次查询
  2. 使用一次子查询(推荐)

两次查询

mysql> select max(sal) from emp;
mysql> select ename, job from emp where sal = '5000';

在这里插入图片描述

一次子查询，将两条查询语句合并起来，where条件筛选中再使用select进行筛选，此语句执行的顺序是由内向外，先执行where中的select，再执行整句中的select

mysql> select ename, job from emp where sal = (select max(sal) from emp);

在这里插入图片描述

(5) 显示工资高于平均工资的员工信息

同样使用子查询

mysql> select * from emp where sal > (select avg(sal) from emp);

在这里插入图片描述

(6) 显示每个部门的平均工资和最高工资

在group by子句中指明按照部门号进行分组，在select语句中使用avg函数和max函数，分别查询每个部门的平均工资和最高工资

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

在这里插入图片描述

(7) 显示平均工资低于2000的部门号和它的平均工资

在group by子句中指明按照部门号进行分组，在select语句中使用avg函数查询每个部门的平均工资，在having子句中指明筛选条件为平均工资小于2000

mysql> select deptno, avg(sal) as 平均工资 from emp group by deptno having 平均工资 < 2000;

在这里插入图片描述

(8) 显示每种岗位的雇员总数，平均工资

mysql> select job, count(*), format(avg(sal),2) from emp group by job;

在这里插入图片描述

2. 多表查询

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

(1) 笛卡尔积

员工表：

在这里插入图片描述

部门表：

在这里插入图片描述

我们想让两个表联合显示为一张表

mysql> select * from emp, dept;

在这里插入图片描述

我们会发现上面的表是这样显示的

整张表由两个表构成，前半部分是员工表的列信息，后半部分是部门表的列信息。

在这里插入图片描述

同时员工表中每条记录与部门表进行穷举组合，员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合。

在这里插入图片描述

  • 我们这种从第一张表中选取一条记录与第二张表中的所有记录进行穷举组合且不加过滤条件，得到的结果称为笛卡尔积。

  • 但是在实际开发中，一个员工只有和自己所在的部门信息进行组合才是有意义的，因此需要我们在笛卡尔积产生的新表中筛选出真正需要的数据

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

笛卡尔积的表中可能会存在相同的列名，这时在选中列名时需要通过表名.列名的方式进行指明，若有重复的不指明确切的一列，就会报错

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

在这里插入图片描述

(3) 显示部门号为10的部门名，员工名和工资

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

在这里插入图片描述

(4) 显示各个员工的姓名，工资，及工资级别

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

在这里插入图片描述

3. 自连接

自连接是指在同一张表连接查询，即我们可以对同一张表取笛卡尔积

(1) 显示员工FORD的上级领导的编号和姓名

可以分为2步：

  1. 先找到FORD的领导的编号
  2. 根据领导的编号，找领导信息

这2步合并为1步使用子查询

  • 使用子查询
mysql> select empno, ename from emp where empno = (select mgr from emp where ename =          'FORD');

在这里插入图片描述

  • 使用多表查询(自查询)

对同一张表做笛卡尔积要给自己的表起别名

mysql> select * from emp e1, emp e2;

图只放了一部分
在这里插入图片描述

mysql> select e2.empno, e2.ename from emp e1, emp e2 where e1.ename='FORD' 
    -> and e1.mgr = e2.empno;

在这里插入图片描述

4. 子查询

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

4.1 在where子句中充当判断条件

4.1.1 单行子查询

返回一行记录的子查询

(1) 显示SMITH同一部门的员工

在子查询中查询SMITH所在的部门号，在where子句中指明筛选条件为员工部门号等于子查询返回的部门号

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

在这里插入图片描述

4.1.2 多行子查询

返回多行记录的子查询

(1) 查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

in关键字: 判断某个对应列值是否在集合当中

可以先查询10号部门有哪些岗位，查询时要对结果进行去重，因为10号部门的某些员工的工作岗位可能是相同的

在这里插入图片描述

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

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

在这里插入图片描述

(2) 查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，所在的部门名称，但是不包含10自己的

拿着查询出来的表结构和其他查询结果做笛卡尔积

mysql> select ename, job,sal,dname from(select ename,job,sal, deptno from emp where job in(select distinct job from emp where deptno = 10) and deptno<>100) as tmp, dept where dept.deptno=tmp.deptno;

在这里插入图片描述

(3) 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

all关键字

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

在这里插入图片描述

(4) 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号(包含自己部门的员工)

any关键字

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

在这里插入图片描述

4.1.3 多列子查询

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

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

#这里也可以把=改成in
mysql> select * from emp where (deptno, job) in (select deptno, job from emp where ename ='SMITH') and ename<>'SMITH';

在这里插入图片描述

  • 上面除(2)外所有的子查询，全部在where子句中，充当判断条件

  • 任何时候，查询出来的临时结构，本质在逻辑上也是表结构，即MySQL中一切皆表

4.2 在from子句中使用子查询

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

多表查询的指导思想：

解决多表问题的本质：想办法将多表转化成单表，所以MySQL中，所有select的问题全部都可以转成单表问题

(1) 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
# 显示每个部门的平均工资,将其看作临时表
mysql> select deptno, avg(sal) mysal from emp group by deptno;

# 多表查询
mysql> select * from emp, (select deptno, avg(sal) mysal from emp group by deptno) tmp      
    -> where sal > tmp.mysal and emp.deptno=tmp.deptno;

# 单表查询
mysql> select ename,emp.deptno,sal,mysal from emp, (select deptno, avg(sal) mysal from emp group by deptno) tmp where sal > tmp.mysal and emp.deptno=tmp.deptno;

在这里插入图片描述

(2) 显示每个高于自己部门平均工资的员工的姓名、工作地点、部门
mysql> select * from dept;

# 多表查询
mysql> select * from dept, (select ename,emp.deptno,sal,mysal from emp, (select deptno,      
    -> avg(sal) mysal from emp group by deptno) tmp where sal > tmp.mysal and                 
    -> emp.deptno=tmp.deptno) t1;

mysql> select * from dept, (select ename,emp.deptno,sal,mysal from emp, (select deptno,      
    -> avg(sal) mysal from emp group by deptno) tmp where sal > tmp.mysal and                
    -> emp.deptno=tmp.deptno) t1 where t1.deptno=dept.deptno;

# 单表查询
mysql> select t1.ename,dept.loc,t1.deptno from dept, (select ename,emp.deptno,sal,mysal       
    -> from emp, (select deptno, avg(sal) mysal from emp group by deptnoo) tmp where sal    
    -> > tmp.mysal and emp.deptno=tmp.deptno) t1 where t1.deptno=dept.deptno;

在这里插入图片描述

(3) 查找每个部门工资最高的人的姓名、工资、部门、最高工资
# 每个部门的最高工资
mysql> select deptno, max(sal) from emp group by deptno;

# 多表查询
mysql> select * from emp e1, (select deptno, max(sal) from emp group by deptno) e2;

mysql> select * from emp e1, (select deptno, max(sal) mysal from emp group by deptno) e2 where e1.deptno=e2.deptno and e1.sal=e2.mysal;

# 单表查询
mysql> select ename, sal, e1.deptno, mysal from emp e1, (select deptno, max(sal) mysal      
    -> from emp group by deptno) e2 where e1.deptno=e2.deptno and e1.sal=e2.mysal;

在这里插入图片描述

(4) 显示每个部门的信息（部门名，编号，地址）和人员数量
  • 使用子查询多表
# 每个部门的人数
mysql> select deptno, count(*) dept_num from emp group by deptno;

# 多表查询
mysql> select * from dept t1, (select deptno, count(*) dept_num from emp group by deptno)    
    -> t2 where t1.deptno=t2.deptno;

# 单表查询
mysql> select dname,t1.deptno,loc, dept_num from dept t1, (select deptno, count(*)           
    -> dept_num from emp group by deptno) t2 where t1.deptno=t2.deptno;

在这里插入图片描述

5. 合并查询

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

5.1 union

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

(1) 将工资大于2500或职位是MANAGER的人找出来
mysql> select * from emp where job='MANAGER';
mysql> select * from emp where sal > 2500 union select * from emp where job='MANAGER';
mysql> select * from emp where sal > 2500 union select * from emp where job='MANAGER';

在这里插入图片描述

5.2 union all

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

(1) 将工资大于25000或职位是MANAGER的人找出来
mysql> select * from emp where sal > 2500 union all select * from emp where job = 'MANAGER';

在这里插入图片描述

Ryujianli
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL 联合查询
fwdwqdwq的博客
06-06 55
笛卡尔积：多表查询的核心操作。 笛卡尔积的计算很简单，就类似于排列组合。笛卡尔积是针对任意两张表之间的运算。 举例： 笛卡尔积计算过程：先拿第一张表的第一条记录，和第二张表的每个记录，分别组合，得到一组新的记录。然后再拿第一张表的第二条记录，和第二张表的每条记录，分别组合，又得到新的记录，最终得到的记录就是笛卡尔积。针对两张表计算笛卡尔积，笛卡尔积的列数，就是 A 的列数 + B 的列数。笛卡尔积的行数，就是 A 的行数 * B 的行数。笛卡尔积的效率不高，但是可以借助来完成发复杂的操作。 笛卡尔积用法
【MySQL】复合查询
zhao_leilei的博客
04-09 1431
前面我们讲解的mysql表的查询都是对一张表进行查询，在实际开发中这远远不够。 1.多表查询 实际开发中往往数据来自不同的表，所以需要多表查询。本节我们用一个简单的公司管理系统，有三张表EMP,DEPT,SALGRADE来演示如何进行多表查询。 示例： •显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询 其实我们只要emp表中的deptno = dept表中的deptno字段的记录 select EMP.ename, EMP.sal, DEPT.dname fro
[MySQL] MySQL复合查询（多表查询、子查询）
weixin_67596609的博客
12-08 3372
前面我们学习了MySQL简单的单表查询。但是我们发现，在很多情况下单表查询并不能很好的满足我们的查询需求。本篇文章会重点讲解MySQL中的多表查询和一些复杂查询。希望本篇文章会对你有所帮助。
MySQL复合查询(多表查询)
分享学习的知识，记录我的技术成长道路
10-17 1137
通常当进行自查询的时候，为了避免冲突，通常需要为表取别名，由于from是在select之前就执行了的，因此select中可以使用from中起的别名。多列子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句。书写复杂的查询过程，一定要先明确执行顺序，子查询是第一个执行的，from语句是第二个执行的，where是第三个执行的，select是最后执行的。在了解复合查询之前，我们需要先复习一下在单表中的查询：这些查询都是在oracle标准表中进行的。使用关键字进行比较。
MySQL复合查询（灰常重要o）
01-19
1 基本查询回顾 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J select * from EMP where (sal>500 or job='MANAGER') and ename like 'J%'; 按照部门号升序但雇员的工资降序排序 ...
MySQL连接查询实例详解
09-09
在本篇文章中，我们将通过实例详细讲解MySQL中的连接查询，包括表的创建、内连接、左连接、右连接以及复合条件连接查询。 首先，创建表是进行连接查询的前提。例如，我们创建了名为`suppliers`的供应商表，用于存储...
Mysql查询优化详解（含示例）
05-06
本文深入探讨了MySQL查询优化的多种策略和技巧，旨在帮助数据库管理员和开发人员提升MySQL数据库的性能。首先，介绍了使用EXPLAIN命令分析查询执行计划的重要性，以便识别潜在的性能瓶颈。接着，详细阐述了索引优化...
MySQL千万级大数据SQL查询优化知识点总结
09-08
MySQL千万级大数据SQL查询优化是数据库管理中的关键环节，尤其对于处理海量数据的应用来说，高效的查询性能至关重要。以下是一些核心的优化知识点： 1. **建立索引**：索引可以显著提高查询速度，特别是在`WHERE`和...
MySQL 创建主键,外键和复合主键的语句
09-11
本文将详细介绍如何在MySQL中创建主键、外键以及复合主键。 1. **创建主键** 主键是数据库表中一个或一组字段，其值在全表中具有唯一性，并且不能为空。它用于唯一标识表中的每一行记录。在MySQL中，创建主键的...
MySQL：SELECT 语句
Zachyy的博客
07-20 364
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
测试开发面试题---MySQL
m0_53302824的博客
07-22 704
索引是数据库管理系统中用于提高数据检索速度的数据结构，它可以帮助快速定位数据不需要扫描全表。提高查询速度；唯一性约束，确保数据不会重复；辅助排序和分组；减少磁盘IO。
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 704
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
【MySQL】根据binlog日志获取回滚sql的一个开发思路
weixin_45385457的博客
07-19 456
不同客户端之间会交替追加在 binlog 中，需要通过 👆的 binlog 匹配流程来控制匹配。行，需要再次寻找 thread_id 相同的行，匹配到后执行上一个流程。在 binlog 中的线程 ID 记录为 thread_id。一个线程执行多个 sql 回滚到同一个文件可能带来的问题。对应 binlog 日志中的 thread_id=指定 mysql 客户端 开始时间和结束时间。打开 mysql 客户端 开始时间。关闭 mysql 客户端 结束时间。先匹配 thread_id 相同的。
Mysql----内置函数
m0_74979625的博客
07-18 336
Mysql--内置函数
Mysql高价语句
最新发布
Alone8046的博客
07-22 608
8.子查询：内查询，嵌套查询，selsect语句当中有嵌套了一个select，嵌套的select才是子查询，查询的时候是先执行子查询语句，外部的select再根据子条件的结果进行过滤查找；左连接以左表为基础，接收左表的所有行，以左表的记录和右表的记录进行匹配，匹配左表的所有，以及右表中符合条件的行，不符合的显示null。5.表和列的别名：因为在实际工作中，表的名字和列的名字可能会很长，书写起来不太方便，多次声明表和列时完整的展示太复杂，设置别名可以使书写简化，可读性增加，简洁明了。是多表数据的集合体。
mysql、oracle、db2数据库连接参数
卿本佳人的博客
07-18 363
mysql、oracle、db2数据库连接参数 参数/数据库 driver url Mysql com.mysql.jdbc.Driver 或 com.mysql.cj.jdbc.Driver jdbc:mysql://localhost:3306/数据库名 Oracle oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@localhost:1521:orcl //orcl为数据库SID DB2 com.ibm.db2.jcc.D
使用Docker 实现 MySQL 循环复制（一）
m0_60511809的博客
07-18 1065
复制是 MySQL 的一项非常重要的技术，它使服务器能够将更改从一个实例复制到另一个实例，复制技术被广泛应用在水平横向扩展、商业智能和分析、地理数据分布、高可用性与容灾等关键性场景中。在某些地区或网络环境下，直接从官方 Docker Hub 下载镜像可能会出现速度慢、不稳定甚至无法连接等问题。典型的复制拓扑是主/从属服务器的一对多关系，高级复杂复制拓扑有双向复制、循环复制以及多源复制等。本次实验将在一台虚拟机中使用 Docker 容器构建三个 MySQL 循环复制拓扑。
MySQL中不等于筛选时会漏掉null值的问题
山子的博客
07-22 193
MySQL中使用不等于进行筛选数据时，若筛选值为`null`，则该条数据不会被选中，如何解决该问题？
mysql复合条件查询
08-19
在MySQL中，你可以使用逻辑运算符和比较运算符来进行复合条件查询。 下面是一个示例，展示了如何在MySQL中进行复合条件查询： ```sql SELECT * FROM table_name WHERE condition1 AND condition2; ``` 在上面的...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值