12 复合查询下

复合查询下

1. 子查询

子查询就是 在一个查询里面套另一个查询，让内层查询先跑，结果再给外层查询用，可以理解成：先算出一个表/值，然后外层再用它做筛选、计算或关联。本质就是一个 SELECT 嵌在另一个 SELECT 里，里面的叫子查询，外面的叫主查询。

1. 单行子查询

子查询的结果只有 1 行 1 列，常用在 =、>、< 等比较操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select ename, sal 
from emp 
where sal > (select avg(sal) from emp);	# 单行单列
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

2. 多行子查询

子查询返回 多行 1 列，用 IN / ANY / ALL（IN 最常用，ANY 和 ALL 用得少）

  • IN（等于其中任意一个）：只要匹配其中任意一个就行，简单理解就是 判断值在不在这个列表里（相当于多个 OR）。
  • ANY（大于其中最小的）：跟列表里任意一个值比较满足就行（注意：= ANY 就等于 IN。）。
  • ALL（大于其中最大的）：跟列表里所有值比较都得满足。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# 工资等于 SALES 部门任意一个人工资的员工
mysql> select ename,sal,deptno from emp 
where sal in (select sal from emp where deptno=30);		# 多行
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| TURNER | 1500.00 |     30 |
| JAMES  |  950.00 |     30 |
+--------+---------+--------+

# 工资 > SALES 部门任意工资的员工
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 |
+--------+---------+--------+

# 工资 > SALES 部门所有工资的员工（比最高的还高）
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 |
+-------+---------+--------+

3. 多列子查询

子查询查出来几列（多行多列），外层 WHERE 就得用几列包成一组去比。把列名用括号括起来 (col1, col2)，后面接 IN 或者 = 就行。 比如里面查两列，外面就得拿两列一起比，用括号把列名括起来 (job, deptno)，后面写 IN 或 =。

注意： (col1, col2) 这不是函数参数，这叫 “多列比较” 或者 “行构造器”，其实就是把两列（或多列）打包成一个整体 去比较。平时我们比较是单挑：

1
>WHERE job = 'MANAGER'  # 只比 job

现在是“双打”：

1
>WHERE (job, deptno) IN (...)

意思是：必须 job 和 deptno 这两列的组合，跟子查询查出来的组合一模一样，才算匹配。 就像找坐标 (x, y)，必须 x 和 y 都对上才行，光 x 对上没用。

1
2
3
4
5
6
7
8
9
10
11
12
13
# 找出和 CLARK 同部门、同职位的员工
mysql> select ename, job, deptno from emp 
    -> where (deptno, job) in (select deptno, job from emp where ename='CLARK');
+-------+---------+--------+
| ename | job     | deptno |
+-------+---------+--------+
| CLARK | MANAGER |     10 |
+-------+---------+--------+
1 row in set (0.00 sec)

# 注意：顺序必须死一致：第一个对第一个，第二个对第二个！
mysql> select ename, job, deptno from emp  where (deptno, job) in (select job, deptno from emp where ename='CLARK');
Empty set, 15 warnings (0.00 sec)

4. 在 FROM 子句中使用子查询（派生表/内联视图）

子查询放在 FROM 里，把子查询结果当成一个 临时表 用，必须给子查询起别名！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
# 每个部门平均工资，并和部门表连起来
select d.dname, t.avg_sal 
from dept d
# 括号里是个子查询，先算出每个部门的平均工资，当成一张临时表，别名叫 t
join (select deptno, avg(sal) as avg_sal from emp group by deptno) t 
# 拿着部门表 d 和 临时表 t 连起来，连接条件就是部门号相等
on d.deptno = t.deptno;
+------------+-------------+
| dname      | avg_sal     |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
| RESEARCH   | 2175.000000 |
| SALES      | 1566.666667 |
+------------+-------------+

# 显示每个部门工资最高的员工的姓名、工资、部门和部门的最高工资
select e.ename 姓名, e.sal 工资, d.dname 部门, e.sal 部门最高工资
from emp e join dept d on e.deptno = d.deptno
# 核心是后面这个子查询：每扫描一行员工，都去算一下他所在部门的最高工资
# 如果当前员工工资等于部门最高工资，那就留下来
where e.sal = (select max(sal) from emp where deptno = e.deptno);
+--------+---------+------------+--------------------+
| 姓名   | 工资    | 部门       | 部门最高工资       |
+--------+---------+------------+--------------------+
| BLAKE  | 2850.00 | SALES      |            2850.00 |
| SCOTT  | 3000.00 | RESEARCH   |            3000.00 |
| KING   | 5000.00 | ACCOUNTING |            5000.00 |
| FORD   | 3000.00 | RESEARCH   |            3000.00 |
+--------+---------+------------+--------------------+

# 显示每个部门的部门名、部门编号、所在地址和人员数量
select d.dname 部门名, d.deptno 部门编号, d.loc 地址, count(e.empno) 人员数量
from dept d
# 用左连接，保证就算没人的部门（比如 40 号）也能查出来，人数显示 0
left join emp e on d.deptno = e.deptno
# 分组后才能用 count 统计每个组的人数，非聚合列都要写在这里
group by d.deptno, d.dname, d.loc;
+------------+--------------+----------+--------------+
| 部门名     | 部门编号     | 地址     | 人员数量     |
+------------+--------------+----------+--------------+
| ACCOUNTING |           10 | NEW YORK |            3 |
| RESEARCH   |           20 | DALLAS   |            5 |
| SALES      |           30 | CHICAGO  |            6 |
| OPERATIONS |           40 | BOSTON   |            0 |
+------------+--------------+----------+--------------+

2. 合并查询

合并查询（UNION） 也叫联合查询、Union 查询。

作用：把两个或多个 SELECT 查询的结果 垂直合并 成一个结果集（注意：每个查询列数和类型要对应上）。

基本语法

1
2
3
4
5
6
7
8
SELECT 列1, 列2 ... FROM 表A
UNION	# 合并结果并去掉重复行
SELECT 列1, 列2 ... FROM 表B;

# 或者：
SELECT 列1, 列2 ... FROM 表1 WHERE 条件1
UNION  # 或者 UNION ALL（带ALL表示不去重）
SELECT 列1, 列2 ... FROM 表2 WHERE 条件2;

注意：列数必须相同、对应列的数据类型要兼容、列名以第一个 SELECT 为准！示例：

列出所有员工姓名和所有部门名称（合并成一列显示）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select ename name from emp union select dname from dept;
+------------+
| name       |
+------------+
| SMITH      |
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
| BLAKE      |
| CLARK      |
| SCOTT      |
| KING       |
| TURNER     |
| ADAMS      |
| JAMES      |
| FORD       |
| MILLER     |
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+

合并显示所有员工的职位（job）和所有部门地点（loc），去重

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select job jobAndLoc from emp union select loc from dept;
+-----------+
| jobAndLoc |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
| NEW YORK  |
| DALLAS    |
| CHICAGO   |
| BOSTON    |
+-----------+

列出所有部门编号（deptno）和所有员工编号（empno），并标注来源

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 先查部门表，把部门号当成 id，再加一列常量文字 '部门'
select deptno as id, '部门' as type from dept 
union all  
# 再查员工表，把工号当成 id，加一列常量文字 '员工'
# union all 就是把俩结果上下拼起来，不去重，性能比 union 好
# 列数必须一样，类型得兼容，列名以第一个查询为准
select empno, '员工' as type from emp 
order by id;  # 最后把拼好的结果按 id 列排个序
+--------+--------+
| id     | type   |
+--------+--------+
| 007369 | 员工   |
| 007499 | 员工   |
| 007521 | 员工   |
| 007566 | 员工   |
| 007654 | 员工   |
| 007698 | 员工   |
| 007782 | 员工   |
| 007788 | 员工   |
| 007839 | 员工   |
| 007844 | 员工   |
| 007876 | 员工   |
| 007900 | 员工   |
| 007902 | 员工   |
| 007934 | 员工   |
| 10     | 部门   |
| 20     | 部门   |
| 30     | 部门   |
| 40     | 部门   |
+--------+--------+