复合查询与子查询：深入解析 scott 样例数据库中的多表查询
在当前数字化管理的商业环境中，运用数据库进行精确的信息检索显得尤为重要。然而，其中的子查询操作宛如一道复杂的谜题，不少数据库用户在此过程中可能会遇到难题。以下，我们选取数据库中的scott样例数据库（即一个简易的公司管理系统）作为研究对象，对复合查询和子查询的相关操作进行深入探讨。
mysql> 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.02 sec)
Scott样例数据库概况
Scott样例数据库模拟了公司管理系统的基本功能。其中，它包含了三个核心的表格：EMP职员表、DEPT部门表以及工资表。这些表格记录了公司的基础信息，是进行查询操作的基础。就像在现实公司中，员工基本信息表、部门架构表和薪资表构成了公司运营的框架一样。在这样的结构中，精确地获取所需数据，成为了数据分析和管理决策的关键。在现实企业里，无论是哪个部门或业务，若要依靠数据来辅助决策，都离不开这种数据库结构的支撑。
mysql> 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.01 sec)
单行子查询
单行子查询在查询特定信息方面具有其独特优势，就好比解决一个针对性强的问题。比如，当我们需要查看与SMITH同部门的员工时，就可以运用单行子查询。通过参考数据库中的记录，根据关联字段，在EMP表中精确筛选出同部门的员工。在实际应用中，只要掌握了这一技巧，当需要查找与特定员工具有相同个别条件的记录时，就能快速锁定目标数据。以大型公司为例，若想了解与某位明星员工同部门的人员信息，单行子查询便能发挥其作用。
mysql> 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 |
+-------+---------+---------+--------+
| JONES | MANAGER | 2975.00 | 007566 |
| BLAKE | MANAGER | 2850.00 | 007698 |
| SMITH | CLERK | 800.00 | 007369 |
| ADAMS | CLERK | 1100.00 | 007876 |
| JAMES | CLERK | 950.00 | 007900 |
+-------+---------+---------+--------+
5 rows in set (0.01 sec)
多行子查询，这是一个功能相当强大的工具。在需要获取多行记录时，我们便不得不借助它。
首先关注in关键字，通过它我们可以查询与10号部门工作相同的雇员信息，包括姓名、岗位、工资和部门号，但排除了10号部门自身。这一用法凸显了in关键字在多行子查询中的特殊作用，它能有效地筛选出满足多个条件的数据。接着看all关键字，比如要找出工资高于部门30所有员工的员工姓名、工资和部门号，使用all关键字可以确保结果的准确性。而any关键字则允许我们查询工资高于部门30中任意员工的员工姓名、工资和部门号，满足了特定查询需求。在处理复杂的员工薪资和岗位结构时，运用多行子查询结合这些关键字，可以轻松解决一系列复杂的数据筛选难题。
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.01 sec)
From子句中的子查询
mysql> 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.01 sec)
子查询若置于from子句中，情形颇为有趣。它此时如同一个临时的表格。在处理复杂数据源或需临时生成数据样本进行查询的实际应用中，from子句中的子查询便能大显身手。以跨部门、业务逻辑复杂的场景为例，单纯使用原始表查询，效率可能不高。然而，若将子查询视作临时表，与from子句结合，便能快速高效地操作。此方法可有效地整合与分析数据，使数据库的数据运用更为灵活。
Union操作符
mysql> 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) |
+-------+--------+---------+----------------+
| KING | 10 | 5000.00 | 2,916.67 |
| JONES | 20 | 2975.00 | 2,175.00 |
| SCOTT | 20 | 3000.00 | 2,175.00 |
| FORD | 20 | 3000.00 | 2,175.00 |
| ALLEN | 30 | 1600.00 | 1,566.67 |
| BLAKE | 30 | 2850.00 | 1,566.67 |
+-------+--------+---------+----------------+
6 rows in set (0.09 sec)
Union操作符确实是个十分实用的工具。用它，结果集会自动清除重复的行。想象一下，面对一个数据庞大、重复信息繁多的文件，谁不想有个工具能自动清理掉重复的内容？比如，当我们需要找出工资超过2500元或职位名称特定的人时，Union操作符就能确保结果既精确又整齐，避免重复带来的困扰。在企业进行多条件人员分组统计时，这一功能非常有用，它能保证数据的条理清晰和统计结果的精确无误。
UnionAll和Union这两个操作符虽有许多共通之处，却也各有不同。UnionAll的作用是合并两个结果集，形成它们的并集，且在合并过程中不会删除任何重复的行。这就像我们在收集各种数据时，希望保留所有信息，保持数据的原始状态。例如，当我们需要找出工资超过25000或职位名称特定的员工时，UnionAll就不会对可能重复的数据进行删除，从而更好地保留了数据的完整性。这种操作在整合部分业务的原生数据时尤其有用。
mysql> 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.01 sec)
在使用数据库进行查询时，你是否遇到过难以克服的数据筛选和整合难题？期待你的点赞，同时欢迎你将这篇文章转发。若能在评论区分享你的数据库查询经验，那就更完美了。
mysql> select DEPT.dname, DEPT.deptno, DEPT.loc,count(*) '部门人数' from EMP,
DEPT where EMP.deptno=DEPT.deptno group by DEPT.deptno,DEPT.dname,DEPT.loc;
+------------+--------+----------+--------------+
| dname | deptno | loc | 部门人数 |
+------------+--------+----------+--------------+
| ACCOUNTING | 10 | NEW YORK | 3 |
| RESEARCH | 20 | DALLAS | 5 |
| SALES | 30 | CHICAGO | 6 |
+------------+--------+----------+--------------+
3 rows in set (0.02 sec)