【MySQL系列】MySQL复合查询的学习 _ 多表查询 | 自连接 | 子查询 | 合并查询

「前言」文章内容大致是对MySQL复合查询的学习。

「归属专栏」MySQL

「主页链接」个人主页

「笔者」枫叶先生(fy)

一、基本查询回顾

前面篇章讲解的mysql表的查询都是对一张表进行查询，在实际开发中这远远不够，下面将讲解复合查询，首先回顾一下基本的查询。

使用的数据库是之前篇章的雇员信息表，员工表（emp）、部门表（dept）和工资等级表（salgrade）

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

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

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

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

使用年薪进行降序排序

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

注：

• 由于NULL与任何值做计算得到的结果都是NULL，因此在计算年薪时不能直接用月薪的12倍加上每个员工的奖金，这样可能导致得到的年薪为NULL值。
• 在计算每个员工的年薪时，应该通过ifnull函数判断员工的奖金是否为NULL，如果不为NULL则ifnull函数返回员工的奖金，如果为NULL则ifnull函数返回0，避免让NULL值参与计算

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

解决该问题需要进行两次查询

此外，这种问题还可以使用子查询，将两句查询语句合并起来，需要将第一次查询的SQL语句用括号括起来。

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

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

也是使用子查询解决

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

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

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

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

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

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

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

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

mysql> select job, count(*) 人数, format(avg(sal), 2) 平均工资 from emp group by job;

二、多表查询

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

• 在进行多表查询时，只需要将多张表的表名依次放到from子句之后，用逗号隔开即可，这时MySQL将会对给定的这多张表取笛卡尔积，作为多表查询的初始数据源
• 多表查询的本质，就是对给定的多张表取笛卡尔积，然后在产生的新表进行查询

笛卡尔积是指给定两个集合A和B，其中A中的每个元素和B中的每个元素都可以组成一个有序对，这些有序对的集合就是A和B的笛卡尔积。

例如，员工表和部门表进行笛卡尔积

员工表：

部门表：

两张表进行笛卡尔积

mysql> select * from emp, dept;

员工表和部门表的笛卡尔积由两部分组成，前半部分是员工表的列信息，后半部分是部门表的列信息

对员工表和部门表取笛卡尔积时，会先从员工表中选出一条记录与部门表中的所有记录进行组合，然后再从员工表中选出一条记录与部门表中的所有记录进行组合，以此类推，最终得到一张新表

对多张表取笛卡尔积后得到的数据并不都是有意义的。

比如对员工表和部门表取笛卡尔积时，员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合，而实际一个员工只有和自己所在的部门信息进行组合才是有意义的，因此需要从笛卡尔积产生的新表筛选出员工的部门号和部门的编号相等记录。

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

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

从题意可以看出，部门名只有dept表中才有，其他数据来源于emp表，即数据来自EMP和DEPT表，因此要联合查询，即多表查询

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

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

部门名只有部门表中才有，员工名和员工工资只有员工表中才有，因此需要同时使用员工表和部门表进行多表查询，在where子句中指明筛选条件为员工的部门号等于部门编号（筛选符合条件的信息）

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

注意：在筛选部门号等于10的部门时，可以使用员工表中的部门号，也可以使用部门表中的部门编号，因为两列都是一样的。

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

员工名和工资只有员工表中才有，而工资级别只有工资等级表中才有，因此需要同时使用员工表和工资等级表进行多表查询，在where子句中指明筛选条件为员工的工资在losal和hisal之间的记录

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

三、自连接

自连接是指在同一张表进行连接查询，也就是说我们不仅可以对不同表进行取笛卡尔积，也可以对同一张表取笛卡尔积。

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

可以使用子查询，先对员工表进行查询得到FORD的领导的编号，然后再根据领导的编号对员工表进行查询得到FORD领导的姓名

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

也可以使用多表查询（自查询），因为员工表中的mgr字段能够将表中员工的信息和员工领导的信息关联起来。

mysql> select leader.empno, leader.ename from emp leader, emp worder where leader.empno = worder.mgr and worder.ename = 'FORD';

注 由于自连接是对同一张表取笛卡尔积，因此在自连接时至少需要给一张表取别名，否则无法区分这两张表中的列。

四、子查询

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

4.1 单行子查询

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

显示SMITH同一部门的员工

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

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

此外，解决该问题也可以使用自连接

4.2 多行子查询

多行子查询，是指返回多行单列数据的子查询

使用in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

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

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

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

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

先查询30号部门员工的工资，进行去重

将上述查询作为子查询，在查询员工表时在where子句中使用all关键字，all关键字用于判断员工的工资是否高于子查询得到的所有工资

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

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

先查询30号部门员工的工资，然后在查询员工表时在where子句中使用any关键字，判断员工的工资是否高于子查询的得到的工资中的某一个

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

4.3 多列子查询

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

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

先查询SMITH所在部门的部门号和他的岗位，然后将上述查询作为子查询

mysql> select * from emp where (deptno,job) = (select deptno, job from emp where ename = 'SMITH') and ename <> 'SMITH';

注：

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

4.4 在from子句中使用子查询

• 子查询语句不仅可以出现在where子句中，也可以出现在from子句中
• 子查询语句出现from子句中，其查询结果将会被当作一个临时表使用

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

先查询每个部门的平均工资，这张表当做临时表使用

然后对员工表和上述的查询结果进行多表查询，在where子句中指明筛选条件为员工的部门号等于临时表中的部门号，并且员工的工资大于临时表中的平均工资

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

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

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

先查询每个部门的最高工资

然后对员工表和上述的查询结果进行取笛卡尔积，在where子句中指明筛选条件为员工的部门号等于临时表中的部门号，并且员工的工资等于临时表中的最高工资

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

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

按照部门号进行分组，分别查询每个部门的人员数量

述查询作为子查询放在from子句中，然后对员工表和临时表取笛卡尔积，在where子句中指明筛选条件为员工的部门号等于临时表中的部门号即可

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

上述也可以只使用多表查询解决

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

五、合并查询

合并查询，是指将多个查询结果进行合并，关键字union和union all

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

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

查询工资大于2500的员工，查询职位是MANAGER的员工

可以使用or操作符将where子句中的两个条件关联起来

也可以使用union将上述的两条查询SQL连接起来，这时将会得到两次查询结果的并集，并且会对合并后的结果进行去重

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

可以使用union all，结果是不去重

注意：待合并的两个查询结果的列的数量必须一致，否则无法合并
--------------------- END ----------------------

「 作者 」 枫叶先生
「 更新 」 2023.8.25
「 声明 」 余之才疏学浅，故所撰文疏漏难免，
          或有谬误或不准确之处，敬请读者批评指正。