MySQL表复合查询的实现

前言

对MySQL表的基本查询还远远达不到实际开发过程中的需求，因此还需要掌握对数据库表的复合查询。本文介绍了多表查询、子查询、自连接、内外连接等复合查询的案例。

一、案例准备

来自oracle 9i的经典测试表：

emp员工表

mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

dept部门表

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

salgrade工资等级表

mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

二、基本查询

MySQL表的基本查询都是针对一张表进行的查询操作，在实际开发过程中还远远不够。以下是以下基本查询的案例：

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

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

使用年薪进行降序排序

注意：年薪 = 月薪 * 12 + 绩效奖，其中有的绩效comm为NULL，在MySQL中有NULL参与运算的结果都为NULL，因此要使用到ifnull函数。

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

注意：因为要使用到max聚合函数，因此不能直接将聚会函数返回的结果作为where筛选的条件去找某一个具体的记录。因此可以先找出最大的薪资，在根据薪资找到该条记录。

但是这样的话就要使用两条SQL语句，因此可以使用子查询：

内部select查询到的结果，作为外部where筛选的条件。

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

和上面的一样，也需要用到子查询。

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

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

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

三、多表查询

实际开发中，数据往往来自不同的表，因此需要多表查询。以下是使用emp、dept、salgrade三张表进行多表查询的案例：

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

由于以上要查询的数据分别来自于emp表和dept表，因此要联合这两张表进行查询：

使用上面的查询方法查询出来的包含许多错误的结果，因此需要使用emp.deptno = dept.deptno条件来进行查询：

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

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

四、子查询

子查询是指嵌入到其他SQL语句中的select语句，也叫嵌套查询。

4.1 单行子查询

单行子查询指的是返回一行记录的子查询，例如：

显示SMITH同一部门的员工

首先从emp表中找出SMITH所在部门的部门号：

然后将该部门号作为筛选的条件，筛选出与该部门号相同的员工信息，并且不包含SMITH：

由此可见，子查询就是将第一次select查询的结果，作为第二次select查询的筛选条件。

4.2 多行子查询

多行子查询就是返回多行记录的子查询，此时一般会用于IN、ALL、ANY 这些关键字：

IN：表示存在，即需满足存在条件ALL：表示所有，即需满足所有条件ANY：表示任一，即需满足任一条件

查询案例：

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

首先查询出10号部门所有的岗位

然后将这些岗位信息作为下一次查询的筛选条件进行查询

最后去掉10号部门的员工信息

ALL关键字：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

首先查找出30号部门所有的员工工资

然后将其作为筛选条件查找出比30号部门的所有员工工资都高的员工信息

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

该案例的前面部分和上面的案例一样，也是首先找出30号部门所有员工的工资，然后再使用ANY关键字找出比部门30的任意员工的工资高的员工信息：

任一当然也包含了30号部门的内部员工，因此只需大于30号部门最低的员工工资的员工都会被筛选出来。

4.3 多列子查询

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

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

首先查找出SMITH的部门号和岗位信息

然后以SMITH的部门号和岗位信息作为筛选条件进行筛选

最后去掉SMITH的相关信息

4.4 FROM子句中的子查询

FROM子句中的子查询就是指子查询语句出现在FROM后面，其实就是把子查询的结果当成一张临时表使用。

查询案例：

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

首先对部门进行分组，获取其部门号即平均工资

将查询结果作为一张临时表，获取其与emp表的笛卡尔积

最后在笛卡尔积表当中筛选出每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

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

首先分组查询获取每个部门的部门号和最高工资

然后将查询结果作为临时表，并获取其与emp表的笛卡尔积

从获取的笛卡尔积中筛选出每个部门工资最高的人的姓名、工资、部门、最高工资

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

方法一：使用子查询

首先对部门进行分组，查找每个部门对应的人数

将查询的结果作为临时表，获取其与dept表的笛卡尔积

从笛卡尔积表中筛选出每个部门的信息及其部门人数

方法二：使用多表

4.5 合并查询

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

4.5.1 UNION

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

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

可以发现，使用 OR 和 UNION 查询出来的结果相同。

4.5.2 UNION ALL

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

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

此时可以发现UNION和UNION ALL的唯一区别就是前者会对查找结果进行去重，而后者不会。

五、自连接

所谓的自连接是指在同一张表连接查询。
查询案例：

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

方法一：子查询

首先从emp表中找出FORD领导的编号，然后将其作为筛选条件查找出FORD的领导信息。

方法二：自连接

首先将两张emp表分别作为leader表和worker表，查找出所有领导与员工之间的关系表

然后从关系表中，查找出员工为FORD的领导信息

六、内外连接

6.1 内连接

内连接实际上就是利用WHERE子句对两张表形成的笛卡尔积进行筛选，因此前面所有的复合查询操作都属于内连接，同时内连接也是实际开发过程中使用最多的连接查询。

内连接语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

案例：显示SMITH的名字和部门名称

方法一：使用前面的查询方式

方法二：使用标准内连接查询

首先通过内连接查询出所有员工与其所在部门名之间的关系

从以上关系中筛选出SMITH与其部门名

6.2 外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接，右侧的表完全显示就是右外连接。

6.2.1 左外连接

语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

案例：

-- 建两张表
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),(11, 8);

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

可以发现，左外连接以左表的内容为准，显示其全部内容，如果右边没有对应信息，则显示为NULL。

6.2.2 右外连接

语法：

select 字段 from 表名1 right join 表名2 on 连接条件；

案例：

对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来

对dept表和emp表联合查询，列出部门名称和这些部门的员工信息，同时列出没有员工的部门

dept表左外连接emp表：

emp表右外连接dept表：

由此可见左外连接和右外连接可以相互转换。