MySQL从零开始 16-多表查询进阶

 上一节介绍了数据库的多表查询，这一节，我们深入了解一下多表查询。

 同样，本次的测试用数据库还是为scott数据库，大家可以在我的GitHub进行scott数据库创建脚本的下载。

 接下来，对多表查询中几个常见的技巧进行介绍。

1. 自连接

自连接是指在同一张表上连接查询。

• 例1：显示员工FORD的上级领导姓名

 我们先使用常规的子查询进行查找。

mysql> select ename from emp where empno=(select mgr from emp where ename='FORD');
+-------+
| ename |
+-------+
| JONES |
+-------+
1 row in set (0.04 sec)

 对于这个问题我们也可以使用自连接，即将emp表通过别名看做两张表进行查询。

-- 将emp表进行别名操作分成两张表，as可以省略
mysql> select leader.ename from EMP as worker, EMP as leader where worker.mgr=leader.empno and worker.ename='FORD';
+-------+
| ename |
+-------+
| JONES |
+-------+
1 row in set (0.03 sec)

2. 子查询

 子查询在上面的例子中已经用过了，其定义就是指嵌入在其它SQL语句中的select查询语句，也叫做嵌套查询。

 子查询又分为单行子查询，多行子查询和多列子查询。

2.1 单行子查询

 单行子查询即返回一行记录的查询，看下面的例子：

• 例2：显示SMITH同一部门的员工。

mysql> select * from emp where deptno=(select deptno from emp where ename='SMITH') and ename <> 'SMITH';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 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 |
+--------+-------+---------+------+---------------------+---------+------+--------+
4 rows in set (0.00 sec)

2.2 多行子查询

 顾名思义，多行子查询就是返回多行记录的子查询，经常与in，all，any等关键字搭配使用。

• 例3：如何查询和10号部门的工作相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的。

 关键字in必须符合期内所有字段的要求，搭配子查询如下所示：

mysql> select * from emp where job in(select distinct job from emp where deptno=10) and deptno <> 10;
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
| 007369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
| 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 |
+--------+-------+---------+------+---------------------+---------+------+--------+
5 rows in set (0.00 sec)

• 例4：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。

 all关键字表示需满足所有条件才能成立，与本例场景符合，使用如下：

mysql> 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.03 sec)

• 例5： 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号。

 可以搭配any关键字实现需求，如下：

mysql> select ename, sal, deptno from emp where sal > any(select sal from emp where deptno=30) and deptno <> 30;
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| JONES  | 2975.00 |     20 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+
7 rows in set (0.00 sec)

2.3 多列子查询

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

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

mysql> 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)

3. 在from子句中使用子查询

 我们可以将子查询语句放在from子句中即把一个子查询利用as作为一个临时表来使用，和自连接有些类似。

• 例7：显示高于自己部门平均工资的员工的信息。

mysql> select ename, emp.deptno, sal, avg_sal from emp, (select avg(sal) as avg_sal, deptno from emp group by deptno) as tmp where emp.deptno=tmp.deptno and emp.sal>tmp.avg_sal;
+-------+--------+---------+-------------+
| ename | deptno | sal     | avg_sal     |
+-------+--------+---------+-------------+
| KING  |     10 | 5000.00 | 2916.666667 |
| JONES |     20 | 2975.00 | 2175.000000 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| FORD  |     20 | 3000.00 | 2175.000000 |
| ALLEN |     30 | 1600.00 | 1566.666667 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
+-------+--------+---------+-------------+
6 rows in set (0.03 sec)

• 例8：查找每个部门工资最高的人的详细资料。

mysql> select emp.ename, emp.deptno, emp.job, max_sal from emp, (select max(sal) as max_sal, deptno from emp group by deptno) as tmp where emp.deptno=tmp.deptno and emp.sal=tmp.max_sal;
+-------+--------+-----------+---------+
| ename | deptno | job       | max_sal |
+-------+--------+-----------+---------+
| BLAKE |     30 | MANAGER   | 2850.00 |
| SCOTT |     20 | ANALYST   | 3000.00 |
| KING  |     10 | PRESIDENT | 5000.00 |
| FORD  |     20 | ANALYST   | 3000.00 |
+-------+--------+-----------+---------+
4 rows in set (0.02 sec)

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

mysql> select dname, dept.deptno, loc, tmp.count from dept, (select count(*) as count, deptno from emp group by deptno) as tmp where dept.deptno=tmp.deptno;
+------------+--------+----------+-------+
| dname      | deptno | loc      | count |
+------------+--------+----------+-------+
| ACCOUNTING |     10 | NEW YORK |     3 |
| RESEARCH   |     20 | DALLAS   |     5 |
| SALES      |     30 | CHICAGO  |     6 |
+------------+--------+----------+-------+
3 rows in set (0.00 sec)

 这个问题其实用多表查询更为方便一点。

mysql> select dname, emp.deptno, loc, count(*) from emp, dept where emp.deptno=dept.deptno group by emp.deptno;
+------------+--------+----------+----------+
| dname      | deptno | loc      | count(*) |
+------------+--------+----------+----------+
| ACCOUNTING |     10 | NEW YORK |        3 |
| RESEARCH   |     20 | DALLAS   |        5 |
| SALES      |     30 | CHICAGO  |        6 |
+------------+--------+----------+----------+
3 rows in set (0.11 sec)

 在实际查询中，我们要根据具体的场景进行查询方式的选择。