前言
对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：表示任一，即需满足任一条件
【MySQL学习】MySQL表的复合查询2：https://developer.aliyun.com/article/1384035
