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

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

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

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

1. 自连接

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

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

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)

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