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(...)

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

MAX(...)

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

MIN(...)

---

分组聚合统计

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

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

group by

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

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

• 查看emp中有哪些部门：

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

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

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

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

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

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

---

having

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

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

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

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

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

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

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

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

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

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

---

多表查询

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

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

• 同时查询dept和salgrade表：

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

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

员工所在的表是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表中，此时就要用到自连接。

如图：

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

---

子查询

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

单行子查询

语法：

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

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

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

---

多行子查询

语法：

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，

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的员工，再把两个查询结果合并。

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

union all

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

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

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

---

内连接

先前在多表查询中，我们对笛卡尔积后的表格利用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，对应的epm和stu中的数据也会变化，后续所有内连接的操作，都可以使用这个视图大大简化操作。 

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

drop view 视图名;