MySQL：复合查询

- 聚合统计
- 分组聚合统计
- - group by
  - having
- 多表查询
- 自连接
- 子查询
- - 单行子查询
  - 多行子查询
  - 多列子查询
  - from子查询
- 合并查询
- - union
  - union all
- 内连接
- 外连接
- - 左外连接
  - 右外连接
  - 全外连接
- 视图

MySQL 复合查询是数据分析和统计的强大工具，本博客将介绍如何使用 MySQL 的复合查询功能来提取和处理复杂数据。

本博客使用的示例数据库如下：

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;USE `scott`;DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (`deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',`loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',`ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',`job` varchar(9) DEFAULT NULL COMMENT '雇员职位',`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',`hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',`sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (`grade` int(11) DEFAULT NULL COMMENT '等级',`losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',`hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

数据库scott中包含三张表，dept、emp和salgrade，如下：

在这里插入图片描述

dept：部门表

在这里插入图片描述

emp：员工表

在这里插入图片描述

salgrade：工资等级表

在这里插入图片描述

聚合统计

聚合统计用于汇总数据的操作，如总和、平均值、计数、最大值、最小值等。聚合统计依赖于MySQL的函数实现，常用聚合统计函数如下：

SUM()：计算一列数值的总和

SUM(...)

示例：求出所有员工工资总和

在这里插入图片描述

AVG()：计算一列数值的平均值

AVG(...)

示例：求出所有员工的工资平均值

在这里插入图片描述

COUNT()：计算行的数量

COUNT(...)

示例：求出员工的数量

在这里插入图片描述

其实emp有多少行，就有多少个员工，所以使用counr()就可以完成人数统计。

MAX()：获取一列中的最大值

MAX(...)

示例：求出工资最大值

在这里插入图片描述

MIN()：获取一列中的最小值

MIN(...)

示例：求出工资最小值

在这里插入图片描述

分组聚合统计

以上所有统计，都是对整张的所有成员进行统计，有时我们需要将表中的数据分为几个组，然后再进行统计，这就是分组聚合统计。

比如emp表中，每个员工都有自己的部门，部门号是deptno。

group by

select ...
from ...
where...
group by column1, column2, ...;

group by后是分组的依据，当group by后面的列值相同，会被视为同一个分组。

查看emp中有哪些部门：

在这里插入图片描述

原先查询emp表，共有14行数据，由于group by，相同的deptno被融合成了一行数据，所以最后只剩下3行数据了，也说明共有三个部门。

分组常结合聚合统计，此时可以统计每个分组的数据。

查看每个部门的平均工资：

在这里插入图片描述

另外的，在group by后面可以跟多个列，依据多个条件分组。

查看每个部门deptno的每个岗位job的平均工资：

在这里插入图片描述

此处分组有两个依据，deptno和job，其执行逻辑为：

先将deptno相同的列视为同一组
再在每个分组内部，把job相同的视为一组

经过以上操作，一共分为了9个组，最后avg进行聚合统计，求出每个组的平均值。

注意：在分组聚合统计中，select后面只允许出现group by后面的列，以及聚合统计函数！

比如说：

查看每个部门有哪些员工：

select deptno, ename from emp group by deptno;

在这里插入图片描述

这就是一个错误示例，首先利用group by将deptno分组，此时整个表就被分为了三个组。随后在每个组中查询ename。

在group by后面的每个分组，最后一定表现为一行数据，最后有几个分组就输出几行数据。由于聚合统计函数本身就是将众多数据统计为一条数据，所以可以用一行描述一个组的聚合信息。

但是对于没有出现在group by后面的ename，一个组内部可以有多种ename值，无法用一行数据表示，所以此时会发生错误。

如果想要完成这个查询，可以将ename也加入分组依据：

在这里插入图片描述

having

有时我们需要对分组聚合统计后的数据再做筛选，此时就需要用到having。

select ...
from ...
where...
group by ...
having ...;

having执行顺序晚于group by，在分完组后才进行条件筛选，用法与where没有区别。

查询每个部门的平均工资，并找出平均工资低于2500的部门：

这就是需要在聚合统计之后再进行筛选的情况，最大特点是筛选条件中包含平均，最大，最小等聚合统计，此时就要用having筛选统计后的值。

在这里插入图片描述

原先查询部门平均工资，有三个部门，经过having筛选，只剩下了两条数据。

现在再总结一下MySQL中关键词的执行顺序：

from：先确定要查询的表，取出表中数据
where：根据条件筛选表中的信息
group by：对数据分组
having：将聚合统计结果再次筛选
select：生成输出列，重命名
order by：对最终结果排序
limit：限制返回的行数

但是有一个小特例，在having中可以访问select取的别名，这导致很多人以为select比having先执行，其实不然。

当 SQL 查询被解析时，SQL 引擎会预先加载select后面的内容。预先加载select不代表先执行select，逻辑上select在having后面执行，但是由于select后面的内容会预先加载，所以having可以访问到别名。

多表查询

有时候在查询时，可能需要用到多张表的数据，此时就需要多表查询。想要一次查询多张表的内容，只需要在from后面列出要查询的表名即可：

select ... from 表1, 表2 ...

同时查询dept和salgrade表：

在这里插入图片描述

dept和salgrade如下：

在这里插入图片描述

两张表原先加起来只有4 + 5 = 9条数据，为什么多表查询后出现了20条数据？

多表查询的过程，其实是两张表进行笛卡尔积，如下图：

在这里插入图片描述

所谓笛卡尔积，其实就是把两张表之间的数据进行排列组合，第一张表的数据依次和第二张表的数据进行组合，最后两张表查询出来的数据数目就是4 * 5 = 20 个。

有时候多表查询时，会出现列名相同的列，比如同时查询dept和emp表：

在这里插入图片描述

此时deptno就出现了两次，此时就要用表名.列名来区别不同的列。比如emp.deptno和dept.deptno。

查询所有员工所在的部门的名称：

员工所在的表是emp，而部门名称所在的表是dept，此时就要用多表查询。对两张表进行多表查询后，此时就会进行笛卡尔积，随后使用where子句对笛卡尔积后的表进行筛选。

如图：

在这里插入图片描述

笛卡尔积后，员工SMITH同时与四个部门进行了匹配，但是SMITH应该只属于一个部门，所以要用where进行筛选emp.deptno = dept.deptno，此时筛选出来的数据就是每个员工以及对应的部门。

如图：

在这里插入图片描述

由于只要员工的名称和部门名称，最后再select ename, dname即可：

在这里插入图片描述

查询各个员工的姓名，工资以及工资级别：

此处工资级别再工资表sagrade中，而员工姓名与工资在员工表emp中，所以要用多表查询。而员工的工资sal与工资级别grade的关系是：工资sal介于该级别的最高工资hisal和最低工资losal之间。

查询如下：

在这里插入图片描述

自连接

自连接是一种特殊的多表查询，可以理解为自己与自己之间进行多表查询。这话听起来很奇怪，回忆一下，多表查询的本质其实就是多张表之间进行笛卡尔积，那么自己与自己能不能进行笛卡尔积呢？是可以的，让表自己与自己进行笛卡尔积就是自连接。

select ... from 表名 as 别名1, 表名 as 别名2;

如图：

在这里插入图片描述

上图就是让dept自连接，笛卡尔积的两张表本质是同一张表，为了区别这两张表，自连接时必须对表进行重命名！

那么自连接有什么意义呢？

在班级中，会出现“学生管学生”的情况，比如小组长。不论是小组长还是普通学生，都在学生的范围内，自然就存储在学生表中。如果想要查询每个同学的小组长是谁，此时就需要用学生表进行自连接，一张表代表“学生”，一张表代表“组长”。

查询每个员工名称以及对应的领导名称：

这个查询中需要“员工”与“领导”，而两者都在emp表中，此时就要用到自连接。

如图：

在这里插入图片描述

select * from emp as worker, emp as leader;

此处将员工表重命名为worker，领导表命名为leader。随后要根据条件筛选，让每个员工与领导匹配，在emp中，mgr表示领导的编号，即领导的empno，所以筛选条件为worker.mgr = leader.empno。

select worker.ename worker, leader.ename leader 
from emp as worker, emp as leader 
where worker.mgr = leader.empno;

在这里插入图片描述

子查询

子查询是指在select内部再嵌套一层select，也叫做嵌套查询。

单行子查询

语法：

select ... from ... where 列名 = (select ... from ...);

此处(select ... from ...)的查询结果必须是单行单列的值，否则无法进行判等操作。

查询与SMITH相同部门的员工名称：

在这里插入图片描述

首先通过子查询select deptno from emp where ename = 'SMITH'得到SMITH所在的部门，随后交给外层查询的where进行条件筛选，此时就可以完成查询。

多行子查询

语法：

select ... from ... where 列名 in (select ... from ...);
select ... from ... where 列名 比较操作符 all(select ... from ...);
select ... from ... where 列名 比较操作符 any(select ... from ...);

在单行子查询中，子查询的结果必须是单行数据，这样才能进行=。如果是多行查询，那么此时就不能进行判等，而是使用in，all，any这三个关键字，来进行范围判断。

in：判断是否是多行数据中的一个

查询与SMITH或者ALLEN岗位相同的员工名称和岗位：

首先要查询出SMiTH和ALLEN的岗位，即select job from emp where ename = 'SMITH' or ename = 'ALLEN'。

以上查询结果为多行，将以上查询结果作为子查询。外层查询则是查询岗位在子查询结果中的行，即job in (子查询)，此处注意不能是job = (子查询)，因为子查询结果为多行。

查询语句：

select ename, job from emp 
where job in (select job from emp where ename = 'SMITH' or ename = 'ALLEN');

在这里插入图片描述

all：判断是否所有数据都满足条件

查询比部门30的所有员工工资都高的员工的姓名，工资，部门号：

首先要查询出部门30的所有员工的工资，即select sal from emp where deptno = 30。因为要比所有员工的工资都高，所以判断条件为sal > all(子查询)。

查询语句：

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

在这里插入图片描述

any：判断是否有数据满足条件

查询比部门30的任意员工工资高的员工的姓名，工资，部门号：

相比于上一题，只需要把all改为any即可：

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

在这里插入图片描述

多列子查询

以上所有子查询，结果都是单列的，如果查询结果为多列，此时语法会略有差别：

select ... from ...
where (列1, 列2) 逻辑运算符 (select 列1, 列2 from ...);

其中(列1, 列2)与后面的select 列1, 列2 from一一对应。

查询和SMITH的部门和岗位完全相同的员工：

首先查询出SMITH的部门和岗位：select deptno, job from emp where ename = 'SMITH'，查询结果有两列，此时要用多列子查询，因为部门和岗位都要完全相同，所以筛选条件为：(deptno, job) = (子查询)。

查询语句：

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

在这里插入图片描述

from子查询

先前的所有子查询都在where中充当判断条件，由于子查询的结果本质是一张表，所以可以再次被查询，即from后面也可以跟子查询，而不是只有where后面可以。

语法：

select ... from (子查询) as 别名 where ...;

注意：子查询结果在from后面时，必须重命名，否则没有表名。

一般来说，在from中使用子查询，都是配合多表查询的，因为如果只是单表查询，没必要使用子查询，直接在where中添加条件即可。、

比如这个语句：

select * from (select * from emp where deptno = 30) as tmp where sal > 1000;

其目的为查询部门30中所有工资大于1000的员工，但是其实完全没必要用子查询，直接一个and就可以解决：

select * from emp where sal > 1000 and deptno = 30;

查询高于自己部门平均工资的员工：

这个查询首先要求出一个部门的平均工资，看到平均这个字眼，毫无疑问要用聚合统计：select deptno, avg(sal) from emp group by deptno，这样就求出了每个部门平均工资：

在这里插入图片描述

可以看到，这个查询结果的本质也是一张表，将其与emp进行笛卡尔积：

select * from emp, (select deptno, avg(sal) as avg_sal from emp group by deptno) as tmp;

随后进行条件筛选，首先要将员工与部门匹配：emp.deptno = tmp.deptno，又要求员工的工资高于部门平均工资，即sal > avg_sal。

查询语句：

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

在这里插入图片描述

合并查询

在实际应用中，有时会合并多个表格的查询结果，此时可以用集合操作符union和union all

union

union用于取出两张表的并集，使用该操作符时会去掉结果中的重复行。

语法：

select ... union select ...

查询工资大于2500或者奖金不为NULL的员工：

如果利用合并查询的思想，此时可以分两次查询，第一次查询工资大于2500的员工，第二次查询奖金不为NULL的员工，再把两个查询结果合并。

查询工资大于2500的员工：

select * from emp where sal > 2500;

查询奖金不为NULL的员工：

select * from emp where comm is not null;

将两个查询结果用union合并即可：

select * from emp where sal > 2500 union select * from emp where comm is not null;

在这里插入图片描述

union all

union all用于取出两张表的并集，使用该操作符时不会去掉结果中的重复行。

语法：

select ... union all select ...

查询工资大于2500或者职位是MANAGER的员工：

查询工资大于2500的员工：

select * from emp where sal > 2500;

职位是MANAGER的员工：

select * from emp where job = 'MANAGER';

将两个查询结果用union all合并：

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

在这里插入图片描述

此时第一行与倒数第三行都是JONES，因为两张表都包含JONES，使用union all合并时没有去重。

内连接

先前在多表查询中，我们对笛卡尔积后的表格利用where子句进行筛选，让数据匹配。比如输出每个员工所在部门的名称：

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

内连接将外部的按照指定要求连接到表中，本质就是以上过程：先对表进行笛卡尔积，后依据条件筛选出合理的数据。

语法：

select ... from 表1 inner join 表2 on 连接条件 where 筛选条件;

内连接语法其实是对多表查询的一种优化，在以前的多表查询中，连接条件往往会写在where中，导致连接条件与筛选条件混合在一起。而内连接将连接条件分离出来，使得语义更加明确。

通过一个示例来说明：

查询岗位是MANAGER的员工所在的部门的名称：

对于以前的多表查询写法：

select ename, dname from emp, dept 
where emp.deptno = dept.deptno and job = 'MANAGER';

内连接写法：

select ename, dname 
from emp inner join dept on emp.deptno = dept.deptno 
where job = 'MANAGER';

经过内连接后，where内容简单了很多，而emp.deptno = dept.deptno 的意义更加明确，就是用于连接条件，用于筛选笛卡尔积后合理的数据。

外连接

外连接本质也是多表查询，依据一定条件将两张表合并起来。

现在增加两张表：

create table stu(id int, name varchar(30));
insert into stu values(1, 'jack'),(2,'tom'),(3,'kity'),(4,'nono');create table exam(id int, grade int);
insert into exam values(1,56),(2,76),(5,88),(6,79);

以上语句创建了一个学生表和一个成绩表：

在这里插入图片描述

可以发现，学生表与成绩表不是一一对应的，有学生没有成绩，也有成绩没有学生。

通过内连接合并表：

select * from stu inner join exam on stu.id = exam.id;

在这里插入图片描述

此时会发现，只有id完全一样的会显示，3 4 5 6都被丢弃了，因为没有对应的数据。如果没有成绩的学生也想展示，此时就不能使用内连接，而要使用外连接。外连接的作用就是保留无法匹配的数据。

外连接分为左外连接和右外连接。

左外连接

左外连接会保留from后面的表的所有数据，语法：

select ... from 表1 left join 表2 on 连接条件 where ...;

此时表1的所有数据都会被保留。

在这里插入图片描述

如图，对于stu表，虽然3 4没有匹配到对应的成绩，但是依然显示了，不过成绩显示为NULL。

右外连接

右外连接会保留join后面的表的所有数据，语法：

select ... from 表1 right join 表2 on 连接条件 where ...;

此时表2的所有数据都会被保留。

在这里插入图片描述

如图，虽然成绩5 6没有人认领，但是依然被保留了，只是学生设置为了NULL。

全外连接

全外连接会保留所有表的所有数据，MySQL中没有直接支持全外连接的语法，需要用union合并左外连接和右外连接进行模拟：

select ... from 表1 left join 表2 on 连接条件 where ...
union
select ... from 表1 right join 表2 on 连接条件 where ...;

在这里插入图片描述

视图

视图是一张虚拟表，用于简化操作，比如说我们经常将emp和dept两张表合并起来查询，但是每次都要进行内连接：

select * from emp inner join dept on emp.deptno = dept.deptno;

在这里插入图片描述

这一大段语句每一次都要写，为了简化操作，此时可以将这个结果保存为一个表，这张表就称为视图。

语法：

create view 视图名 as select ...;

示例：

create view test_view as 
select * from emp inner join dept 
on emp.deptno = dept.deptno;

在这里插入图片描述

此时发生错误了，因为两张表都有deptno，此时选择保留一个即可：

create view test_view as 
select emp.*, dept.dname, dept.loc
from emp inner join dept 
on emp.deptno = dept.deptno;

创建完毕后，数据库中就多出了一个名为test_view的表：

在这里插入图片描述

视图不是一张简单的表，如果操纵这个test_view，对应的epm和stu中的数据也会变化！后续所有内连接的操作，都可以使用这个视图大大简化操作。

如果想要删除视图，语法：

drop view 视图名;