MySQL·复合查询

基本查询回顾

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

案例2：按照部门号升序而雇员的工资降序排序

案例3：使用年薪进行降序排序

案例4：显示工资最高的员工的名字和工作岗位

案例5：显示工资高于平均工资的员工信息

案例6：显示每个部门的平均工资和最高工资

案例7：显示平均工资低于2000的部门号和它的平均工资

案例8：显示每种岗位的雇员总数，平均工资

多表查询

案例9：显示雇员名、雇员工资以及所在部门的名字

案例10：显示部门号为10的部门名，员工名和工资

案例11：显示各个员工的姓名，工资，及工资级别

自连接

案例12：显示员工FORD的上级领导的编号和姓名

子查询

单行子查询

案例13：显示SMITH同一部门的员工

多行子查询

案例14：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

补充1：in关键字(在集合中)

案例15：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

补充2：all关键字(表示所有)

案例16：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

补充3：any关键字(任意)

多列子查询

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

在from子句中使用子查询

案例18：显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

案例19：查找每个部门工资最高的人的姓名、工资、部门、最高工资

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

合并查询

union

案例21：将工资大于2500或职位是MANAGER的人找出来

union all

本篇主要使用的表，参考下文中的雇员表

MySQL·表数据的操作-CSDN博客

基本查询回顾

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

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

当然我们还可以使用之前说的函数去筛选，结果是一样的

案例2：按照部门号升序而雇员的工资降序排序

select * from EMP order by deptno, sal desc;

案例3：使用年薪进行降序排序

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

直接筛选是不行的，因为null不参与运算

需要使用ifnull函数

这样我们就得到了一张年薪表

我们再将这张表进行降序排序就好了，因为是先有数据再排序，所以我们可以直接使用重命名之后的名字进行排序

案例4：显示工资最高的员工的名字和工作岗位

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

传统思路分两步，首先查询最高工资的是多少，其次再用最高工资找到这个人，但是显然这种查询方式很粗糙，我们并不推荐这样去做

我们可以在查询中嵌套一个子查询的方式来达到我们想要的结果

案例5：显示工资高于平均工资的员工信息

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

案例6：显示每个部门的平均工资和最高工资

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

我们还可以用format函数进行格式化

案例7：显示平均工资低于2000的部门号和它的平均工资

select deptno, avg(sal) as avg_sal from EMP group by deptno having
avg_sal<2000;

案例8：显示每种岗位的雇员总数，平均工资

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

多表查询

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

案例9：显示雇员名、雇员工资以及所在部门的名字

select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno =
DEPT.deptno;

因为上面的数据来自EMP和DEPT表，因此要联合查询

多表笛卡尔积会有很多不符合常理的数据也被关联起来，使用条件将他们筛选出来即可拿到我们想要的一张合并并且筛选过后的表

分析如下

我们在将我们需要的数据拿出来即可

案例10：显示部门号为10的部门名，员工名和工资

select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and
DEPT.deptno = 10;

案例11：显示各个员工的姓名，工资，及工资级别

select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and
hisal;

我们得使用下面这张表，表的来源参考文章的开头注释

薪资等级要在最低和最高工资之间

自连接

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

想要对一张表进行笛卡尔积的话，需要重命名，这种行为我们称之为自连接

案例12：显示员工FORD的上级领导的编号和姓名

注意：这里的mgr是员工领导的编号--empno

我们可以直接使用复合查询进行筛选

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

也可以使用自连接的方式进行查询

-- 使用到表的别名
--from emp leader, emp worker，给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别 
select leader.empno,leader.ename from emp leader, emp worker where
leader.empno = worker.mgr and worker.ename='FORD';

子查询

单行子查询

返回一行记录的子查询

案例13：显示SMITH同一部门的员工

select * from EMP WHERE deptno = (select deptno from EMP where
ename='smith');

传统分两步，先找到SMITH对应的部门号，再用部门号进行筛选

我们可以使用复合查询来实现这两步

多行子查询

返回多行记录的子查询

案例14：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

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

补充1：in关键字(在集合中)

最后再去除10号部门

案例15：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

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

同理，我们可以分两步完成查询，先找到30号部门的最高工资，再用最高工资去筛选高于该工资的人员

合并起来，使用复合查询得到下面这种查找方法

补充2：all关键字(表示所有)

案例16：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

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

补充3：any关键字(任意)

多列子查询

单行子查询是指子查询只返回单列，单行数据；

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

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

第一步：找到部门

第二步：找到SMITH本人

第三步：复合查询

当然用in关键字也行

目前全部的子查询，全部都在where字句中充当判断，下面我们要介绍在from字句中使用子查询

在from子句中使用子查询

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

案例18：显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

-- 获取各个部门的平均工资，将其看作临时表
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;

第一步：拿到各部门的平均工资

第二步：用各部门的平均工资表与emp表做笛卡尔积，并筛选出正常的数据

注意第二张临时表需要重命名

第三步：用这张表进行单行查询即可

案例19：查找每个部门工资最高的人的姓名、工资、部门、最高工资

select EMP.ename, EMP.sal, EMP.deptno, ms from EMP,
(select max(sal) ms, deptno from EMP group by deptno) tmp
where EMP.deptno=tmp.deptno and EMP.sal=tmp.ms;

第一步：找到每个部门的最高工资

第二步：用这张临时表和emp表做笛卡尔积，并去除不合理的数据

第三步：用这张表进行当行查询

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

方法一：多表子查询

-- 1. 对EMP表进行人员统计
select count(*), deptno from EMP group by deptno;
-- 2. 将上面的表看作临时表
select DEPT.deptno, dname, mycnt, loc from DEPT,
(select count(*) mycnt, deptno from EMP group by deptno) tmp
where DEPT.deptno=tmp.deptno;

第一步：先查询到每个部门的人数

第二步：用这张临时表与dept表进行笛卡尔积，并过滤掉不合理的数据

第三步：找到需要的数据

方法2：使用多表

select DEPT.dname, DEPT.deptno, DEPT.loc,count(*) '部门人数' from EMP,
DEPT where EMP.deptno=DEPT.deptno
group by DEPT.deptno,DEPT.dname,DEPT.loc;

多表查询的指导思想：解决多表问题的本质：想办法将多表转化为单表，使用MySQL中，所有select的问题都可以转成单表问题

合并查询

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

union

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

案例21：将工资大于2500或职位是MANAGER的人找出来

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

分两步如下

再做union

union all

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

如同，相比union而言，会多几行数据出来，因为没有进行去除操作

不过视乎没有什么区别，这两用点较少