【MySQL】 复合查询

一、基本查询回顾

• 下面给出三张表，分别是员工表（emp）、部门表（dept）和工资等级表（salgrade）。
• 后续所要进行的查询操作都将以这三张表作为数据源，包括基本查询和复合查询。

通过下面的练习能够评估你的SQL查询的基本功。

雇员信息表数据库文件

---

• 三张表单

• 员工表（emp）
  雇员编号（empno）， 雇员姓名（ename），雇员职位（job），雇员领导编号（mgr），雇佣时间（hiredate）。
  工资月薪（sal），奖金（comm），部门编号（deptno）。

• 部门表（dept）

部门编号（deptno），部门名称（dname），部门所在地点（loc）。

• 工资等级表（salgrade）

等级（grade），此等级最低工资（losal），此等级最高工资（hisal）。

---

1.1 查询工资高于500或岗位为MANAGER的员工，同时要求员工姓名的首字母为大写的J

• 这些字段ename，job，sal都在emp表中，所以from中的表格应该是emp。
• 对于工资高于500或岗位为MANAGER的员工,我们可以使用（sal > 500 or job = MANAGER)。
• 对于要求员工姓名的首字母为大写的J我们只需要使用left(ename, 1) = 'J'
• 很明显题目要求条件1，与条件2同时成立，我们可以使用and完成

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

1.2 查询员工信息，按部门号升序而员工工资降序显示

• order by子句是支持按照多个属性进行排序的，且书写的顺序就是排序的优先级。
• 我们直接全列查询，然后按照部门号升序而员工工资降序，order by deptno asc, sal desc。

1.3 使用年薪进行降序排序

• 年薪 = 12 * 月薪 + 奖金 （当然有些公司不只是12薪！） ，我们直接对sal列进行数据运算即可求出年薪。
• 需要注意的是这里的奖金可能是NULL，由于NULL是无法参与运算的，所以我们还要对奖金进行特殊处理，如果奖金是NULL我们就使用0进行运算，如果是非NULL我们就直接运算，所以这里我们可以使用ifnull(comm，0)

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

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

• 最高的工资我们可以对sal是用聚合函数max来求出。
• 然后我们再拿着最高的工资去找工资最高的员工。

我们发现这样的SQL是能够满足我们的要求的，但是这样写法有一些散乱，我们其实可以使用子查询将它们连接在一起，（子查询的详细信息我们后面进行讲解）

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

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

• 我们可以先拿到平均工资的值，使用avg(sal)
• 然后我们再使用where条件进行按照工资大于平均工资进行晒选。

这里写成两条SQL的写法过于简单，我就不再书写了，这里我们仿照这上面的子查询写成一条。

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

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

• 对于平均工资我们可以使用avg(sal)
• 对于最高工资我们可以使用max(sal)

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

• 部门的平均工资，对于这个条件显然我们是要进行分组求平均值，在group by子句中指明按照部门号进行分组，然后使用avg函数求得每个部门的平均工资。
• 有了部门的平均工资，我们就可以判断每个部分的平均工资是否是低于2000的，满足条件我们就将他们筛选出来，在having子句中指明筛选条件为平均工资小于2000。

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

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

• 每种岗位，对于这个条件显然我们是要进行分组，在group by子句中指明按照job进行分组。
• 然后我们可以使用count函数统计雇员总数，使用avg()函数求得平均工资。

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

二、多表查询

上面的基础查询都是在一张表的基础上进行的查询，实际开发中往往数据来自不同的表，所以需要多表查询。

这里我们继续使用这三张表：EMP,DEPT,SALGRADE来演示如何进行多表查询。

1、多表查询

多表查询的语法：

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

....from table_1, table_2, table_4 ....

• 所谓的对多张表取笛卡尔积，就是得到这多张表的记录的所有可能性全部组合出来。

例如我们对下面这两张小表做笛卡尔积：

select * from dept, salgrade;

可以看出：对部门表和薪资等级表取笛卡尔积时，会先从薪资等级表中选出一条记录与部门表中的所有记录进行组合，然后再从薪资等级表中选出一条记录与部门表中的所有记录进行组合，以此类推，最终得到的就是这两张表的笛卡尔积。

2、笛卡尔积的过滤

需要注意的是，对多张表取笛卡尔积后得到的数据并不都是有意义的，比如对员工表和部门表取笛卡尔积时，员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合，而实际一个员工只有和自己所在的部门信息进行组合才是有意义的。

所以我们可以再where子句中指明：emp.deptno = dept.deptno，即从笛卡尔积中筛选出员工的部门号和部门的编号相等记录。

说明： 进行笛卡尔积的多张表中可能会存在相同的列名，这时在选中列名时需要通过表名.列名的方式进行指明。

可以看到，这种一个员工只和自己所在的部门信息进行组合的表格才是正真有意义的表格。

---

下面我们来进行一些练习：

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

• 员工名(ename)和工资(sal)这两个字段在emp表中，而部门名称在dept表中，所以我们要使用多表查询from emp, dept。
• 为了让两张表的笛卡儿积的结果是有效的，我们可以在where条件中使用emp.deptno = dept.deptno
• 由于我们只要部门号为10的部门，所以我们可以使用where条件进行筛选dept.deptno = 10，当然由于emp中也有deptno字段，所以我们也可以使用emp.deptno = 10。

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

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

• 员工名(ename)和工资(sal)这两个字段在emp表中，而工资级别在salgrade表中，所以我们要使用多表查询from emp, salgrade。
• 为了让两张表的笛卡儿积的结果是有效的，以及正确的显示工资级别，我们要在where子句中添加限制条件: sal between losal and hisal

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

3、自连接

刚才我们使用多表查询是：两张不同的表进行组合，那么对于两张相同的表能不能进行组合呢？

答案是可以的：对同一张表连接查询我们称之为自连接

语法：

我们直接像多表查询那样在from后面写上两次表名是不行的！因为两张表同名的话，我们后面对列进行各种操作时有歧义。

select * from dept,dept;

所以我们需要对表进行重命名，以保证我们后面对列进行各种操作时没有歧义！

select * from dept as t1, dept as t2;

---

下面我们来看一个例子帮我们更好的理解自连接：

3. 1 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号，empno是雇员编号）

• 首先理解题意：员工的上级领导也是员工，也有自己的员工编号。

此外员工表中的mgr字段能够将表中员工的信息和员工领导的信息关联起来

方法一：使用多条SQL

• 首先我们可以先拿到员工FORD其领导的编号

select mgr from emp where ename = 'FORD';

• 然后根据这个编号进行查找其上级领导

select * from emp where empno=7566;

方法二： 使用子查询

先对员工表进行查询得到FORD的领导的编号，然后再根据领导的编号对员工表进行查询得到FORD领导的信息。

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

方法三:使用自连接

我们发现两次查找都是在emp表中进行查找，所以我们可以对emp表进行自连接，然后对自连接的结果按照 :

e1表的雇员名称必须是FORD而且员工的领导编号等于领导的员工编号即：(e1.ename='FORD') and (e1.mgr=e2.empno)的方式进行过滤。

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

三、子查询

• 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
• 子查询可分为单行子查询、多行子查询、多列子查询，以及在from子句中使用的子查询

1、单行子查询

所谓的单行子查询就是，返回一行记录的子查询。

1.1 显示SMITH同一部门的员工

• 我们可以先找到SMITH的部门
• 然后按照SMITH的部门进行条件筛选。

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

2、多行子查询

我们知道select的查询结果可以是多条记录，所以返回多条记录的子查询我们称之为多行子查询。

下面我们借助一些问题，来学习一下多行子查询中的一些关键字。

• in关键字： 此关键字用于检查某个值是否存在于子查询返回的结果集中

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

首先理解题意：10号部门中有许多的工作岗位，我们需要在整张表中去筛选员工的工作岗位和10号部门中的工作岗位是相同的，最后我们还要去掉处于10号部门中的人。

• 首先我们可以先使用select将10号部门中的工作岗位全部筛选出来。
• 然后我们可以在where条件中，将第一步返回的结果作为一个筛选条件，由于第一步返回的是一张多行的表格，而我们只需要满足其中的一个条件就满足了要求，所以这里我们可以使用in关键字（****）
• 最后我们再去除隶属于10号部门的员工就完成了题目要求。

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

• all关键字: 此关键字通常与比较操作符（如=、>、<、>=、<=、<>或<>）一起使用，以将某个值与子查询返回的所有结果进行比较

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

方法一：使用单行子查询

• 比30号部门的所有员工的工资高，这句话意味着只要我们比30号部门的最高工资高我们就能够满足题目要求。

• 所以我们可以直接使用聚合函数max完成我们的要求，当然使用max其实意味着我们使用一个单行子查询就能完成我们要求。

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

方法二：多行子查询

我们就按题目的要求，要比30号部门的所有员工的工资高。

• 我们可以先拿到30号部门的所有工资。
• 然后将第一步返回的结果作为一个where筛选条件，由于第一步返回的是一张多行的表格，而我们需要满足所有条件才能满足了要求，所以这里我们可以使用all关键字（表示满足其中的所有的条件，才成立）
• 最后按照题目的要求拿到：员工的姓名、工资和部门号。

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

• any关键字： 此关键字用于与比较运算符一起使用，以比较某个值与子查询返回的任何一个结果。

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

和上面的题目一样，第一种方法我们直接找30号部门的最低工资，当然这里为了介绍any关键字，我们采用多行子查询的方式进行解决这个问题。

• 我们可以先拿到30号部门的所有工资。
• 然后将第一步返回的结果作为一个where筛选条件，由于第一步返回的是一张多行的表格，而我们只需要满足任意一个条件就能满足要求，所以这里我们可以使用any关键字（表示满足其中的任意一个条件，就能成立）
• 最后按照题目的要求拿到：员工的姓名、工资和部门号。

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

---

最后是关于in和any的辨析

• in关键字：

in关键字用于检查某个值是否存在于子查询返回的结果集中，如果子查询返回多个结果，in关键字会检查列的值是否匹配子查询中的任何一个结果。

• any关键字：

any关键字用于与比较运算符一起使用，以比较某个值与子查询返回的任何一个结果。

总结：

• in关键字是检查值是否存在于子查询的结果集中。
• any关键字是用于与比较运算符一起使用，以比较值与子查询的任何一个结果。

例如上面笔者给的in与any的练习中，你会发现in与any是不能相互替换的。

3、多列子查询

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

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

• 显然我们可以先使用子查询先找到SMITH的部门和岗位。
• 然后将第一步返回的结果作为一个where筛选条件，由于第一步返回的是一张单行多列的表格，而我们又需要同时比较两个条件，（其他人的部门和岗位要与SMITH完全相同），所以这里我们可以对要比较的数据加上一个()中间使用，分割要比较的字段，表示一次比较多个字段。

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

说明一下：

• 多列子查询得到的结果是多列数据，在比较多列数据时需要将待比较的多个列用圆括号括起来。
• 多列子查询返回的如果是多行数据，在筛选数据时也可以使用in、all和any关键字。

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

• 目前我们练习和使用的子查询，我们全都在where子句中，充当判断条件用的。
• 此外子查询还能够在form子句中使用，用于充当一个子表。

我们来看下面的案例：

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

• 首先我们可以先将每一个部门的的平均工资求取出来。

select deptno, avg(sal) from emp group by deptno

• 然后我们可以将我们的员工表与第一步得到的临时表进行笛卡尔积，这样我们就拿到了员工和平均工资组合的所有可能性。
• 但是有些组合是不可能的，所以我们需要对笛卡尔积的结果进行过滤，过滤的条件是员工表和临时表的部门号emp.deptno = tmp.deptno必须相同。
• 最后按照题目要求，我们必须在where条件中进行筛选员工的工资要高于平均工资sal > avg_sal

select ename, emp.deptno, sal, avg_sal from emp, (select deptno, avg(sal) avg_sal from emp group by deptno) as tmp where (emp.deptno=tmp.deptneptno) and (sal > avg_sal);

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

4.2 显示每个部门工资最高的员工的姓名、工资、部门和部门的最高工资

• 每个部门最高的工资，显然我们要分组然后使用max求出。

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

• 然后将员工表与第一步得到的临时表进行笛卡尔积，这样我们就拿到了员工和最高工资组合的所有可能性。
• 但是有些组合是不可能的，所以我们需要对笛卡尔积的结果进行过滤，过滤的条件是员工表和临时表的部门号emp.deptno = tmp.deptno必须相同。
• 最后按照题目要求，我们必须在where条件中进行筛选员工的工资要等于最高工资sal = max_sal

select ename, sal, emp.deptno, max_sal from emp, (select deptno,max(sal) max_sal from emp group by deptno) as tmp where (emp.deptno=tmp.deptnoptno) and (sal = max_sal);

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

• 显然每个部门人员的数量我们要使用分组聚合进行统计。

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

• 然后将部门表与第一步得到的临时表进行笛卡尔积，这样我们就拿到了部门信息和人员数量组合的所有可能性。
• 但是有些组合是不可能的，所以我们需要对笛卡尔积的结果进行过滤，过滤的条件是部门表和临时表的部门号dept.deptno = tmp.deptno必须相同。
• 最后按照题目要求，我们筛选出部门名，编号，地址和人员数量。

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

学习完这些复合查询以后我们可以体会到：

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

四、合并查询

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

• union all用于取得两个查询结果的并集，但union all不会去掉结果集中的重复行。
• union也用于取得两个查询结果的并集，union会自动去掉结果集中的重复行。

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

对于这个需求，我们使用where与or运算符很容易解决，当然这里为了介绍合并查询我们这里使用两条SQL语句：

1. 显示工资大于2500的员工

select * from emp where sal > 2500;

2. 显示职位是MANAGER的员工

select * from emp where job='MANAGER';

这里为了满足题目的要求我们我们使用union all将两条SQL连接起来：

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

结果确实是我们想要的，但是我们会发现结果中有重复的，因为有员工工资即大于2500,又是MANAGER

因此这里我们可以使用union，union会自动去掉结果集中的重复行。

说明：

• 待合并的两个查询结果的列的数量必须一致，否则无法合并。
• 待合并的两个查询结果对应的列属性可以不一样，但不建议这样做。