【MySQL】复合查询

发布时间:2023年12月28日

前面我们讲解的 mysql 表的查询都是对一张表进行查询,在实际开发中这远远不够,接下来我们要学习多表查询,即符合查询。

一、基本查询回顾

接下来我们回顾一下以前学的基本查询,我们继续使用雇员表测试表。

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

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

在这里插入图片描述

  • 按照部门号升序而雇员的工资降序排序

      			select * from emp order by deptno, sal desc;
    

在这里插入图片描述

  • 使用年薪进行降序排序

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

在这里插入图片描述

  • 显示工资最高的员工的名字和工作岗位

使用查询 select 语句:

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

在这里插入图片描述

使用分页筛选:

				select ename, job from emp order by sal desc limit 1 offset 0;

在这里插入图片描述

  • 显示工资高于平均工资的员工信息

      			select ename, sal from emp where sal > (select avg(sal) from emp);
    

在这里插入图片描述

  • 显示每个部门的平均工资和最高工资

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

在这里插入图片描述

  • 显示平均工资低于 2000 的部门号和它的平均工资

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

在这里插入图片描述

  • 显示每种岗位的雇员总数,平均工资

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

在这里插入图片描述

二、多表查询

实际开发中往往数据来自不同的表,所以需要多表查询。我们继续使用一个简单的公司管理系统,有三张表 emp,dept,salgrade 来演示如何进行多表查询。

例如,显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自 empdept 表,因此要联合查询,我们可以使用以下语句进行联合查询:

				select * from emp, dept;

上面语句的含义就是将 emp 表和 dept 表进行联合,那么它是怎样进行联合的呢?原理如下图:

在这里插入图片描述

emp 表的每一个 deptnodept 表的每一个 deptno 进行组合,形成新的一行,当 emp 表中的所有 deptnodept 表中的 deptno 全部组合完成,说明联合完毕,形成新的一个表。其中这种将数据进行穷举组合的方式,我们称作为笛卡尔积

但是我们会发现,当 emp 中的 deptnodept 中的 deptno 组合时,会出现 deptno 不对应的情况,这种情况对我们来说没有意义,所以我们可以使用 where 把它筛选开,我们还可以使用 表名.字段 指定显示哪一个字段:

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

在这里插入图片描述

如上图,就把两张表联合完成。

下面我们看一些实例:

  • 显示部门号为 10 的部门名,员工名和工资

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

在这里插入图片描述

  • 显示各个员工的姓名,工资,及工资级别

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

在这里插入图片描述

三、自连接

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

例如,显示员工 WARD 的上级领导的编号和姓名(mgr 是员工领导的编号)

  • 使用子查询

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

在这里插入图片描述

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

      		select leader.empno, leader.ename 
      		    -> from emp leader, emp worker
      		    -> where leader.empno = worker.mgr
      		    -> and worker.ename = 'WARD';
    

使用到表的别名 - - from emp leader, emp worker,给自己的表起别名,因为要先做笛卡尔积,所以别名可以先识别。

在这里插入图片描述

四、子查询

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

1. 单行子查询

返回一行记录的子查询。

例如:

  • 显示 JAMES 同一部门的员工

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

在这里插入图片描述

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;
    

在这里插入图片描述

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

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

在这里插入图片描述

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

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

在这里插入图片描述

3. 多列子查询

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

例如,查询和 WARD 的部门和岗位完全相同的所有雇员,不含 WARD 本人

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

在这里插入图片描述

截至目前,目前全部的子查询,全部都在 where 子句中充当判断条件!任何时刻,查询出来的临时结构,本质在逻辑上也是表结构!

4. 在 from 子句中使用子查询

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

实例:

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

先获取各个部门的平均工资,将其看作临时表

			select deptno dt, avg(sal) 平均工资 from emp group by deptno;

然后将各个部门的平均工资作为 from 的条件:

			select ename, deptno, sal, format(平均工资, 2) from emp,
			    -> (select deptno dt, avg(sal) 平均工资 from emp group by deptno) tmp
			    -> where emp.sal> tmp.平均工资
			    -> and emp.deptno = tmp.dt;

在这里插入图片描述

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

先获取每个部门最高工资的人的信息:

			select deptno, max(sal) 最高工资 from emp group by deptno;

再将上面语句作为 from 的条件代入:

			select emp.ename, emp.sal, emp.deptno, 最高工资 from emp,
			    -> (select deptno, max(sal) 最高工资 from emp group by deptno) tmp
			    -> where emp.deptno = tmp.deptno
			    -> and emp.sal = tmp.最高工资;

在这里插入图片描述

  • 显示每个部门的信息(部门名,编号,地址)和人员数量
  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;
    

在这里插入图片描述

  1. 使用子查询

先对 emp 表的各部门人数进行统计:

			select count(*), deptno from emp group by deptno;

将上面的表看作临时表作为 from 条件:

			select dept.deptno, dname, 部门人数, loc from dept,
			    -> (select count(*) 部门人数, deptno from emp group by deptno) tmp
			    -> where dept.deptno = tmp.deptno;

在这里插入图片描述

总结,解决多表问题的本质:想办法将多表转化为单表,所以 mysql 中,所有 select 的问题全部都可以转成单表问题!这就是多表查询的思想!

5. 合并查询

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

(1)union

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

实例:

  • 将工资大于 2500 或职位是 MANAGER 的人找出来

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

在这里插入图片描述

自动去掉了结果集中的重复记录。

(2)union all

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

实例:

  • 将工资大于 2500 或职位是 MANAGER 的人找出来

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

在这里插入图片描述

没有将重复记录去掉。

五、练习

  1. 查找所有员工入职时候的薪水情况

  2. 获取所有非manager的员工emp_no

  3. 获取所有员工当前的manager

文章来源:https://blog.csdn.net/YoungMLet/article/details/135043671
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。