第七章复合查询

之前我们所讲的查询都是来自于一张表，然后有很多种方式进行查询，但在实际开发中这可能远远不够，因此本章的复合查询就是一些关于多表张表一块进行查询的操作。

多表查询

本次示例一共用三张表来进行，建表过程省略，以下进行表内容的展示。

MariaDB [scott]> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+
3 rows in set (0.00 sec)
MariaDB [scott]> 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)
MariaDB [scott]> 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)
MariaDB [scott]> 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)

接下来我们要显示雇员名、雇员工资以及所在部门的名字，由于这几条信息分布在emp表及dept表中，所以我们需要用到多表查询。

MariaDB [scott]> select emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno = dept.deptno;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| SMITH  |  800.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| JONES  | 2975.00 | RESEARCH   |
| MARTIN | 1250.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| CLARK  | 2450.00 | ACCOUNTING |
| SCOTT  | 3000.00 | RESEARCH   |
| KING   | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES      |
| ADAMS  | 1100.00 | RESEARCH   |
| JAMES  |  950.00 | SALES      |
| FORD   | 3000.00 | RESEARCH   |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
14 rows in set (0.00 sec)

以上这段指令就是显示了emp表中的ename,sal和dept表中的dname字段，并且在最后加入了限定条件要求emp表中的deptno要和dept表中的deptno值相同，由此即可打印出相匹配的信息。

那么我们不加限定条件的话会怎样呢？

MariaDB [scott]> select emp.ename, emp.sal, dept.dname from emp, dept;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| SMITH  |  800.00 | ACCOUNTING |
| SMITH  |  800.00 | RESEARCH   |
| SMITH  |  800.00 | SALES      |
| SMITH  |  800.00 | OPERATIONS |
| ALLEN  | 1600.00 | ACCOUNTING |
| ALLEN  | 1600.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| ALLEN  | 1600.00 | OPERATIONS |
| WARD   | 1250.00 | ACCOUNTING |
| WARD   | 1250.00 | RESEARCH   |
| WARD   | 1250.00 | SALES      |
| WARD   | 1250.00 | OPERATIONS |
| JONES  | 2975.00 | ACCOUNTING |
| JONES  | 2975.00 | RESEARCH   |
| JONES  | 2975.00 | SALES      |
| JONES  | 2975.00 | OPERATIONS |
| MARTIN | 1250.00 | ACCOUNTING |
| MARTIN | 1250.00 | RESEARCH   |
| MARTIN | 1250.00 | SALES      |
| MARTIN | 1250.00 | OPERATIONS |
| BLAKE  | 2850.00 | ACCOUNTING |
| BLAKE  | 2850.00 | RESEARCH   |
| BLAKE  | 2850.00 | SALES      |
| BLAKE  | 2850.00 | OPERATIONS |
| CLARK  | 2450.00 | ACCOUNTING |
| CLARK  | 2450.00 | RESEARCH   |
| CLARK  | 2450.00 | SALES      |
| CLARK  | 2450.00 | OPERATIONS |
| SCOTT  | 3000.00 | ACCOUNTING |
| SCOTT  | 3000.00 | RESEARCH   |
| SCOTT  | 3000.00 | SALES      |
| SCOTT  | 3000.00 | OPERATIONS |
| KING   | 5000.00 | ACCOUNTING |
| KING   | 5000.00 | RESEARCH   |
| KING   | 5000.00 | SALES      |
| KING   | 5000.00 | OPERATIONS |
| TURNER | 1500.00 | ACCOUNTING |
| TURNER | 1500.00 | RESEARCH   |
| TURNER | 1500.00 | SALES      |
| TURNER | 1500.00 | OPERATIONS |
| ADAMS  | 1100.00 | ACCOUNTING |
| ADAMS  | 1100.00 | RESEARCH   |
| ADAMS  | 1100.00 | SALES      |
| ADAMS  | 1100.00 | OPERATIONS |
| JAMES  |  950.00 | ACCOUNTING |
| JAMES  |  950.00 | RESEARCH   |
| JAMES  |  950.00 | SALES      |
| JAMES  |  950.00 | OPERATIONS |
| FORD   | 3000.00 | ACCOUNTING |
| FORD   | 3000.00 | RESEARCH   |
| FORD   | 3000.00 | SALES      |
| FORD   | 3000.00 | OPERATIONS |
| MILLER | 1300.00 | ACCOUNTING |
| MILLER | 1300.00 | RESEARCH   |
| MILLER | 1300.00 | SALES      |
| MILLER | 1300.00 | OPERATIONS |
+--------+---------+------------+
56 rows in set (0.00 sec)

结果会如上所示，这有点像集合中的笛卡尔积，会将所有记录以此都匹配一遍。以下还有几个例子。

MariaDB [scott]> select ename, sal, dname from emp, dept where emp.deptno = dept.deptno and dept.deptno = 10;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| CLARK  | 2450.00 | ACCOUNTING |
| KING   | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
3 rows in set (0.00 sec)

这个例子是打印部门号为10的部门名，员工和工资。

MariaDB [scott]> select ename, sal, grade from emp, salgrade where emp.sal between losal and hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)

这个例子是打印各个员工的姓名，工资，及工资级别。

自连接

自连接是在一张表中进行的连接查询

MariaDB [scott]> select empno, ename from emp where emp.empno=(select mgr from emp where ename = 'ford');
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.02 sec)

这个例子中，我们首先进行了后面括号中的查询，查询到了ename为ford的员工信息的mgr字段信息，然后再进行前半部分查询，查找empno为后半部分查找结果的所有员工的empno和ename信息。所以连起来就是查找了empno与ename为ford的mgr相等的员工的empno及ename值。这里用到了子查询的知识，在后面有详细讲解。

除了单一表的自连接我们还有多表的自连接，这里需要用到别名的配合。

MariaDB [scott]> select leader.empno, leader.ename from emp leader, emp worker where leader.empno = worker.mgr and worker.ename = 'ford';
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.01 sec)

这里的功能和第一个例子的功能是一样的，但是用到了两张表，虽然两张表是相同的但是我们通过赋予不同的别名来达到识别不同的表的功能。

子查询

子查询是指嵌入在其他查询语句中的查询语句，往往能完成多次的更为复杂的查询工作。

单行子查询

单行子查询只满足查询结果的一个条件，往往最先执行的查询结果也只有一行。

MariaDB [scott]> select * from emp where deptno = (select deptno from emp where ename = 'smith');
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
| 007876 | ADAMS | CLERK   | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |
| 007902 | FORD  | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+---------+------+---------------------+---------+------+--------+
5 rows in set (0.00 sec)

这个例子是查找了和smith相同部门的员工的信息。这里我这先查找了smith的部门编号，然后找和他编号相同的员工的信息。

多行子查询

多行子查询往往首先查询的查询结果有很多条，这里需要用到几个关键字来进行条件控制。

in：表示某某条件其中一条相同即可。

MariaDB [scott]> select ename,job,sal,empno from emp where job in (select distinct job from emp
    -> where deptno=10) and deptno<>10;
+-------+---------+---------+--------+
| ename | job     | sal     | empno  |
+-------+---------+---------+--------+
| SMITH | CLERK   |  800.00 | 007369 |
| JONES | MANAGER | 2975.00 | 007566 |
| BLAKE | MANAGER | 2850.00 | 007698 |
| ADAMS | CLERK   | 1100.00 | 007876 |
| JAMES | CLERK   |  950.00 | 007900 |
+-------+---------+---------+--------+
5 rows in set (0.00 sec)

查询和10号部门的工作相同的雇员的名字，岗位，工资，部门号。（不包括10号部门本身的雇员）
all：表示全部。

MariaDB [scott]> select ename, sal, deptno from emp where sal > all(select sal from emp where deptno=30);
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
4 rows in set (0.00 sec)

显示工资比30号部门的所有员工的工资还要搞得员工的姓名、工资和部门号。
any：表示多个里面选取任意个。

MariaDB [scott]> select ename, sal, deptno from emp where sal > any(select sal from emp where deptno=30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+
12 rows in set (0.00 sec)

显示工资比部门30的任意员工的工资搞得员工的姓名、工资和部门号。

多列子查询

我们之前的查找无论单行还是多行子查询返回的都只是一列数据，子查询所查找的都是单一字段。那么多列子查询中的子查询所返回就是多行多列的结果。

MariaDB [scott]> select ename from emp where (deptno, job)=(select deptno, job from emp where ename='smith') and ename != 'smith';
+-------+
| ename |
+-------+
| ADAMS |
+-------+
1 row in set (0.00 sec)

这里就是利用了多列子查询做到了多个字段之间匹配的情况，查询出了和smith的部门和岗位完全相同的所有雇员，并且不含smith本人。

在from子句中使用子查询

我们之前的子查询都是在where子句中进行嵌套查询的。而我们也可以在from子句中使用子查询，这样我们所查找的目标表就是子查询所返回的表。

MariaDB [scott]> select ename, deptno, sal, format(asal,2) from emp,(select avg(sal) asal, deptno dt from emp group by deptno) tmp where emp.sal > tmp.asal and emp.deptno = tmp.dt;
+-------+--------+---------+----------------+
| ename | deptno | sal     | format(asal,2) |
+-------+--------+---------+----------------+
| ALLEN |     30 | 1600.00 | 1,566.67       |
| JONES |     20 | 2975.00 | 2,175.00       |
| BLAKE |     30 | 2850.00 | 1,566.67       |
| SCOTT |     20 | 3000.00 | 2,175.00       |
| KING  |     10 | 5000.00 | 2,916.67       |
| FORD  |     20 | 3000.00 | 2,175.00       |
+-------+--------+---------+----------------+
6 rows in set (0.01 sec)

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

MariaDB [scott]> select emp.ename, emp.sal, emp.deptno, ms from emp, (select max(sal) ms, deptno from emp group by deptno) tmp
    -> where emp.deptno = tmp.deptno and emp.sal = tmp.ms;
+-------+---------+--------+---------+
| ename | sal     | deptno | ms      |
+-------+---------+--------+---------+
| BLAKE | 2850.00 |     30 | 2850.00 |
| SCOTT | 3000.00 |     20 | 3000.00 |
| KING  | 5000.00 |     10 | 5000.00 |
| FORD  | 3000.00 |     20 | 3000.00 |
+-------+---------+--------+---------+
4 rows in set (0.00 sec)

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

合并查询

在实际操作中我们往往会遇到需要将多个查询结果合并起来的查询，这样的操作我们成为合并查询。在这里我们要用到两个关键字。
union：该操作符用于取地两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

MariaDB [scott]> select ename, sal, job from emp where sal > 2500 union
    -> select ename, sal, job from emp where job = 'manager';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+
6 rows in set (0.00 sec)

这样就将两个查询结果进行了合并。相当于取了查询结果的并集。
union all这个操作符是将两个结果集完全合并到一起，不会去掉其中的重复行。

MariaDB [scott]> select ename, sal, job from emp where sal > 2500 union all select ename, sal, job from emp where job = 'manager';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+
8 rows in set (0.00 sec)

和union对比union all并没有去掉两个结果中的重复结果，比如jones的数据就在最终结果中出现了两次。

第七章 复合查询