上一节介绍了数据库的多表查询,这一节,我们深入了解一下多表查询。
同样,本次的测试用数据库还是为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)
在实际查询中,我们要根据具体的场景进行查询方式的选择。