【MySQL基础篇重点】八、复合查询
文章目录
- Ⅰ. 基本查询回顾
- 1、查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J
- 2、按照部门号升序而雇员的工资降序排序
- 3、使用年薪进行降序排序
- 4、显示工资最高的员工的名字和工作岗位
- 5、显示工资高于平均工资的员工信息
- 6、显示每个部门的平均工资和最高工资
- 7、显示平均工资低于2000的部门号和它的平均工资
- 8、显示每种岗位的雇员总数，平均工资
- Ⅱ. 多表查询
- 1、显示雇员名、雇员工资、部门编号以及所在部门的名字
- 2、显示部门号为10的部门名，员工名和工资
- 3、显示各个员工的姓名，工资，及工资级别
- Ⅲ. 自连接
- 显示员工 FORD 的上级领导的编号和姓名
- ① 子查询方式
- ② 自连接方式
- Ⅳ. 子查询
- 🎏在 `where` 子句中的子查询
- 1、单行子查询
- 案例：显示与SMITH同一部门的员工
- 2、多行子查询
- ① **==`in` 关键字==**：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号（但是不包含10号自己），并且显示对应的员工属于哪一个部门的名字
- ② **==`all` 关键字==**：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
- ③ **==`any`关键字==**：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）
- 3、多列子查询
- 案例：查询和SMITH的部门编号和岗位完全相同的所有雇员，不含SMITH本人
- 🎏 在 `from` 子句中的子查询
- ① 显示每个部门中超过平均工资的员工的姓名、部门、工资、平均工资，以及所在位置
- ② 查找每个部门工资最高的人的姓名、工资、部门、最高工资
- ③ 显示每个部门的信息（部门名、编号、地址）和人员数量
- Ⅴ. 合并查询
- 1、`union`
- 案例：将工资大于2500或职位是MANAGER的人找出来
- 2、`union all`
- 案例：将工资大于2500或职位是MANAGER的人找出来
- 实战OJ
Ⅰ. 基本查询回顾
前面我们学习的 mysql 表的查询都是对一张表进行查询，在实际开发中这远远不够，所以我们就需要学习下面的复合查询！
在进行学习之前，我们先来简单的看些案例，巩固一下单表查询的操作！下面的操作案例中，使用的依然是表的增删查改笔记中的雇员信息表！
1、查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J
-- 1、使用substring函数来截取第一个字符判断
mysql> select * from emp where (sal>500 or job='MANAGER') and substring(ename, 1, 1)='J';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)-- 2、使用like进行模糊匹配来判断
mysql> select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)
2、按照部门号升序而雇员的工资降序排序
mysql> select * from emp order by deptno asc, sal desc;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
3、使用年薪进行降序排序
这里的年薪我们按照十二个月基本薪资加上奖金来算的，但是因为奖金有没有是 null，所以想要用 ifnull() 函数来判断一下，不然直接相加的话会变成 null：
mysql> select ename, sal*12+ifnull(comm,0) 年薪 from emp order by 年薪 desc;
+--------+----------+
| ename | 年薪 |
+--------+----------+
| KING | 60000.00 |
| SCOTT | 36000.00 |
| FORD | 36000.00 |
| JONES | 35700.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| ALLEN | 19500.00 |
| TURNER | 18000.00 |
| MARTIN | 16400.00 |
| MILLER | 15600.00 |
| WARD | 15500.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| SMITH | 9600.00 |
+--------+----------+
14 rows in set (0.00 sec)
4、显示工资最高的员工的名字和工作岗位
下面演示三种方式，其中后两者使用了一种复合查询的一种方式：子查询。
其实就像是一个嵌套，先执行括号内的子查询语句，然后返回的结果再用来执行外层的查询语句，这个我们下面会讲！
-- 1、通过排序+limit来筛选出工资最高
mysql> select ename,job,sal from emp order by sal desc limit 1;
+-------+-----------+---------+
| ename | job | sal |
+-------+-----------+---------+
| KING | PRESIDENT | 5000.00 |
+-------+-----------+---------+
1 row in set (0.00 sec)-- 2、使用子查询，通过排序+limit来筛选出工资最高
mysql> select ename,job,sal from emp where sal=(select sal from emp order by sal desc limit 1);
+-------+-----------+---------+
| ename | job | sal |
+-------+-----------+---------+
| KING | PRESIDENT | 5000.00 |
+-------+-----------+---------+
1 row in set (0.01 sec)-- 3、使用子查询，通过使用max函数筛选工资最高
mysql> select ename,job,sal from emp where sal=(select max(sal) from emp);
+-------+-----------+---------+
| ename | job | sal |
+-------+-----------+---------+
| KING | PRESIDENT | 5000.00 |
+-------+-----------+---------+
1 row in set (0.00 sec)
5、显示工资高于平均工资的员工信息
像这种类型的语句，其实用子查询会更方便一些，由子查询语句完成平均工资，然后通过外层的 where 条件进行筛选出工资大于子查询得到的平均工资的员工消息：
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)mysql> select * from emp where sal > (select avg(sal) from emp);
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 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 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)
6、显示每个部门的平均工资和最高工资
很明显，因为提到了每个部门，所以需要先做分组，然后再聚合统计！
mysql> select deptno, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno;
+--------+--------------+--------------+
| deptno | 平均工资 | 最高工资 |
+--------+--------------+--------------+
| 10 | 2916.666667 | 5000.00 |
| 20 | 2175.000000 | 3000.00 |
| 30 | 1566.666667 | 2850.00 |
+--------+--------------+--------------+
3 rows in set (0.00 sec)
7、显示平均工资低于2000的部门号和它的平均工资
从题目要求可以看出还是需要对部门进行分组的，然后进行聚合统计平均工资，最后通过限定条件也就是小于两千块钱的进行显示：
mysql> select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资<2000;
+--------+--------------+
| deptno | 平均工资 |
+--------+--------------+
| 30 | 1566.666667 |
+--------+--------------+
1 row in set (0.00 sec)
8、显示每种岗位的雇员总数，平均工资
mysql> select job, count(*) 雇员总数, avg(sal) 平均工资 from emp group by job;
+-----------+--------------+--------------+
| job | 雇员总数 | 平均工资 |
+-----------+--------------+--------------+
| ANALYST | 2 | 3000.000000 |
| CLERK | 4 | 1037.500000 |
| MANAGER | 3 | 2758.333333 |
| PRESIDENT | 1 | 5000.000000 |
| SALESMAN | 4 | 1400.000000 |
+-----------+--------------+--------------+
5 rows in set (0.00 sec)
Ⅱ. 多表查询
实际开发中往往数据来自不同的表，所以需要多表查询。
在 mysql 中，多表查询是指在一个查询语句中同时使用多个表来获取所需的数据，这可以通过使用 join 子句来实现，比如常见的内连接、左连接、右连接、全连接、自连接等等多表查询方式！
下面我们不多废话，直接用一个简单的公司管理系统，有三张表 emp、dept、salgrade 来演示如何进行最简单的多表查询！（这三张依旧是我们在增删查改中使用的样例表）
1、显示雇员名、雇员工资、部门编号以及所在部门的名字
因为上面的数据来自 emp 和 dept 两张表，所以我们需要将两张表一起查询。
其实非常简单，只需要 在 from 语句添上需要查询的表即可，表与表之间用逗号隔开，如下所示：
其实细心的同学已经能看出来，其实有这么多重复的行数据，是因为多个表的联合，是通过 穷举组合的方式联合的，在数学中，这种其实称为笛卡尔积，尚且不需要想的那么高大上，其实就是将两张表的每行元素进行枚举组合罢了，如下图所示：
但其实我们也知道，虽然得到的结果表有很多行数据，但是重复的并且没有意义的行数据有很多。比如说这里两张表的关联列字段是 deptno 也就是部门编号，那么肯定一个人的部门编号是一一对应的，那么那些不对应的编号其实就没有意义了，所以我们可以使用 where 语句进行筛选：
这才是我们需要的嘛！
最后我们根据题目要求，将特定的字段查询出来即可：
mysql> select ename, sal, emp.deptno, dname from emp, dept where emp.deptno=dept.deptno;
+--------+---------+--------+------------+
| ename | sal | deptno | dname |
+--------+---------+--------+------------+
| SMITH | 800.00 | 20 | RESEARCH |
| ALLEN | 1600.00 | 30 | SALES |
| WARD | 1250.00 | 30 | SALES |
| JONES | 2975.00 | 20 | RESEARCH |
| MARTIN | 1250.00 | 30 | SALES |
| BLAKE | 2850.00 | 30 | SALES |
| CLARK | 2450.00 | 10 | ACCOUNTING |
| SCOTT | 3000.00 | 20 | RESEARCH |
| KING | 5000.00 | 10 | ACCOUNTING |
| TURNER | 1500.00 | 30 | SALES |
| ADAMS | 1100.00 | 20 | RESEARCH |
| JAMES | 950.00 | 30 | SALES |
| FORD | 3000.00 | 20 | RESEARCH |
| MILLER | 1300.00 | 10 | ACCOUNTING |
+--------+---------+--------+------------+
14 rows in set (0.00 sec)
其中如果 在联合后的表中有重名的字段，要访问它的时候需要使用 “表名.列字段名” 的形式去访问，而不重名的字段可以不管！
2、显示部门号为10的部门名，员工名和工资
mysql> select emp.deptno, dname, ename, sal from emp, dept where emp.deptno=dept.deptno and emp.deptno=10;
+--------+------------+--------+---------+
| deptno | dname | ename | sal |
+--------+------------+--------+---------+
| 10 | ACCOUNTING | CLARK | 2450.00 |
| 10 | ACCOUNTING | KING | 5000.00 |
| 10 | ACCOUNTING | MILLER | 1300.00 |
+--------+------------+--------+---------+
3 rows in set (0.00 sec)
3、显示各个员工的姓名，工资，及工资级别
这次用到的表是 emp 和 salgrade 两张表，其它的操作都是一样的，只不过在筛选的时候，我们需要筛选的是对应的等级，就需要根据工资在哪个区间来判断，而工资等级区间是由 losal 和 hisal 来维护的！
mysql> select ename, sal, grade, losal, hisal from emp, salgrade where sal between losal and hisal;
+--------+---------+-------+-------+-------+
| ename | sal | grade | losal | hisal |
+--------+---------+-------+-------+-------+
| SMITH | 800.00 | 1 | 700 | 1200 |
| ALLEN | 1600.00 | 3 | 1401 | 2000 |
| WARD | 1250.00 | 2 | 1201 | 1400 |
| JONES | 2975.00 | 4 | 2001 | 3000 |
| MARTIN | 1250.00 | 2 | 1201 | 1400 |
| BLAKE | 2850.00 | 4 | 2001 | 3000 |
| CLARK | 2450.00 | 4 | 2001 | 3000 |
| SCOTT | 3000.00 | 4 | 2001 | 3000 |
| KING | 5000.00 | 5 | 3001 | 9999 |
| TURNER | 1500.00 | 3 | 1401 | 2000 |
| ADAMS | 1100.00 | 1 | 700 | 1200 |
| JAMES | 950.00 | 1 | 700 | 1200 |
| FORD | 3000.00 | 4 | 2001 | 3000 |
| MILLER | 1300.00 | 2 | 1201 | 1400 |
+--------+---------+-------+-------+-------+
14 rows in set (0.00 sec)
Ⅲ. 自连接
自连接指的是 将表与自身进行连接，一般用于处理具有层次结构的数据。
比如说这里我们拿一张小一点的表 salgrade 来试试看自连接：
mysql> select * from salgrade, salgrade;
ERROR 1066 (42000): Not unique table/alias: 'salgrade'
它直接给我们报了一个错误，其实是因为如果我们直接拿同一张去连接同一张表，是会有表的命名冲突的，所以我们 对于同一张表的自连接必须要进行重命名！
mysql> select * from salgrade t1, salgrade t2;
+-------+-------+-------+-------+-------+-------+
| grade | losal | hisal | grade | losal | hisal |
+-------+-------+-------+-------+-------+-------+
| 1 | 700 | 1200 | 1 | 700 | 1200 |
| 2 | 1201 | 1400 | 1 | 700 | 1200 |
| 3 | 1401 | 2000 | 1 | 700 | 1200 |
| 4 | 2001 | 3000 | 1 | 700 | 1200 |
| 5 | 3001 | 9999 | 1 | 700 | 1200 |
| 1 | 700 | 1200 | 2 | 1201 | 1400 |
| 2 | 1201 | 1400 | 2 | 1201 | 1400 |
| 3 | 1401 | 2000 | 2 | 1201 | 1400 |
| 4 | 2001 | 3000 | 2 | 1201 | 1400 |
| 5 | 3001 | 9999 | 2 | 1201 | 1400 |
| 1 | 700 | 1200 | 3 | 1401 | 2000 |
| 2 | 1201 | 1400 | 3 | 1401 | 2000 |
| 3 | 1401 | 2000 | 3 | 1401 | 2000 |
| 4 | 2001 | 3000 | 3 | 1401 | 2000 |
| 5 | 3001 | 9999 | 3 | 1401 | 2000 |
| 1 | 700 | 1200 | 4 | 2001 | 3000 |
| 2 | 1201 | 1400 | 4 | 2001 | 3000 |
| 3 | 1401 | 2000 | 4 | 2001 | 3000 |
| 4 | 2001 | 3000 | 4 | 2001 | 3000 |
| 5 | 3001 | 9999 | 4 | 2001 | 3000 |
| 1 | 700 | 1200 | 5 | 3001 | 9999 |
| 2 | 1201 | 1400 | 5 | 3001 | 9999 |
| 3 | 1401 | 2000 | 5 | 3001 | 9999 |
| 4 | 2001 | 3000 | 5 | 3001 | 9999 |
| 5 | 3001 | 9999 | 5 | 3001 | 9999 |
+-------+-------+-------+-------+-------+-------+
25 rows in set (0.00 sec)
并且我们可以看到确实是符合笛卡尔积的枚举形式的！
此外，我们是可以 在 from 子句中进行重命名的，并且这样子操作后几乎其它位置的子句都能使用该别名，因为 from 子句的执行优先级很高！
下面还是通过样例来加深这个概念的理解！
显示员工 FORD 的上级领导的编号和姓名
其中表 emp 中的列字段 mgr 是员工领导的编号，而每个人的编号是列字段 empno。
主要的思路很简单：第一步就是找到 FORD 这个人它的 mgr 字段；第二步就是通过 mgr 字段找到对应的 empno 字段，其对应的就是 FORD 的老板！
我们一共有两种做法，一种是子查询，一种是自连接，我们两种都来介绍一下：
① 子查询方式
mysql> select empno 员工编号, ename 姓名 from emp where empno=( select mgr from emp where ename='FORD' );
+--------------+--------+
| 员工编号 | 姓名 |
+--------------+--------+
| 007566 | JONES |
+--------------+--------+
1 row in set (0.00 sec)
② 自连接方式
mysql> select e2.empno, e2.ename from emp e1, emp e2 where e1.ename='FORD' and e2.empno=e1.mgr;
+--------+-------+
| empno | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec)
如果我们在 select 语句中不指定 e2 的话，那么查询出来的就是 e1 和 e2 的消息，那么就会报错，因为 mysql 不知道我们要的是哪个表的 empno，如下所示：
mysql> select empno, ename from emp e1, emp e2 where e1.ename='FORD' and e2.empno=e1.mgr;
ERROR 1052 (23000): Column 'empno' in field list is ambiguous
Ⅳ. 子查询
子查询是指嵌入在其他 sql 语句中的 select 查询语句，也叫 嵌套查询，相当于是一个函数调用一样。它可以作为主查询的一部分，用于提供更详细或特定的数据，以便满足查询的条件或限制。
子查询通常用于以下几种情况：
- 过滤条件：子查询可以用于过滤主查询的结果。例如，你可以使用子查询来查找满足某个条件的记录，然后将这些记录作为主查询的结果返回。
- 聚合函数：子查询可以用于计算聚合函数的结果。例如，你可以使用子查询来计算平均值、总和或计数等聚合函数的结果，并将其作为主查询的一部分返回。
- 子查询作为表：子查询可以作为表来使用，以便在主查询中进行连接操作或其他复杂的查询操作。例如，你可以使用子查询作为一个临时表，然后将其与其他表进行连接，以获取更复杂的查询结果。
在子查询中，你可以使用主查询中的字段或条件，以及其他适用的查询语句。
🎏在 where 子句中的子查询
下面讲的三种子查询的方式，都是位于 where 子句中的，后面我们还会讲子查询出现再 from 子句中的，它们其实功能差不多，但是含义却大不相同！下面我们先介绍前者！
1、单行子查询
其实这在之前我们就使用过了，其是 用于返回一行记录的子查询。
下面举个例子就懂了！
案例：显示与SMITH同一部门的员工
思路很简单，首先就是要找出 SMITH 在哪个部门，拿到其部门编号；然后通过该部门编号筛选出该部门编号内的员工。
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.00 sec)
2、多行子查询
用于 返回多行记录的子查询。简单地说，就是子查询的结果是多行的罢了！
还是一样，下面通过举例子来理解，顺序学习几个常用的关键字！
① in 关键字：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号（但是不包含10号自己），并且显示对应的员工属于哪一个部门的名字
首先我们要先知道 10 号部门有什么工作岗位：
select job from emp where deptno=10;
接着就是查询表中雇员的名字、岗位、工资、部门号：
select ename, job, sal, deptno from emp;
此时我们将两者结合起来，这里需要使用一个 in 关键字，其作用是在查询中指定一个条件，以便匹配多个值，它通常与 where 子句一起使用。
因为我们要查询表中的那些与 10 号部门有相同工作岗位的信息，所以指定条件就是 job ，所以应该是这样子表示：
mysql> select ename, job, sal, deptno from emp where job in (select job from emp where deptno = 10);
+--------+-----------+---------+--------+
| ename | job | sal | deptno |
+--------+-----------+---------+--------+
| JONES | MANAGER | 2975.00 | 20 |
| BLAKE | MANAGER | 2850.00 | 30 |
| CLARK | MANAGER | 2450.00 | 10 |
| KING | PRESIDENT | 5000.00 | 10 |
| SMITH | CLERK | 800.00 | 20 |
| ADAMS | CLERK | 1100.00 | 20 |
| JAMES | CLERK | 950.00 | 30 |
| MILLER | CLERK | 1300.00 | 10 |
+--------+-----------+---------+--------+
8 rows in set (0.00 sec)
就相当于我们拿了一张完整的但是只带名字、岗位、工资、部门号的 表1，然后又拿了一张 10 号部门的工作岗位表2用来筛选出当前 表1 需要的行数据！
然后我们进行 deptno 的筛选，去除 deptno = 10 的那些行数据：
mysql> select ename, job, sal, deptno from emp where job in (select job from emp where deptno = 10) and deptno<>10;
+-------+---------+---------+--------+
| ename | job | sal | deptno |
+-------+---------+---------+--------+
| JONES | MANAGER | 2975.00 | 20 |
| BLAKE | MANAGER | 2850.00 | 30 |
| SMITH | CLERK | 800.00 | 20 |
| ADAMS | CLERK | 1100.00 | 20 |
| JAMES | CLERK | 950.00 | 30 |
+-------+---------+---------+--------+
5 rows in set (0.00 sec)
最后还有一个条件，就是显示对应的员工属于哪一个部门的名字，这是在另一张表 dept 中的一个字段 dname，所以这就和多表查询结合在一起！
其实不难，我们可以把当前已经筛选完的这个表，作为一张新的表进行返回，相当于又是一层嵌套，然后对其进行重命名就能与 dept 进行多表查询拿到想要的结果了，其 sql 语句如下所示：
mysql> select ename, job, sal, tmp.deptno, dname -> from (select ename, job, sal, deptno from emp where job in (select job from emp where deptno<=>10) and deptno<>10) as tmp, dept -> where tmp.deptno<=>dept.deptno;
+-------+---------+---------+--------+----------+
| ename | job | sal | deptno | dname |
+-------+---------+---------+--------+----------+
| SMITH | CLERK | 800.00 | 20 | RESEARCH |
| JONES | MANAGER | 2975.00 | 20 | RESEARCH |
| ADAMS | CLERK | 1100.00 | 20 | RESEARCH |
| BLAKE | MANAGER | 2850.00 | 30 | SALES |
| JAMES | CLERK | 950.00 | 30 | SALES |
+-------+---------+---------+--------+----------+
5 rows in set (0.00 sec)
这里很容易搞混，所以要理清楚，这最后一步操作也涉及到了下面会讲的 多列子查询，这里提前引出来！
还要强调的一点就是 “mysql 中一切皆表”，也就是说我们每次查询出来的一个结果，其实都可以看作一个表，所以才允许我们做这些操作！
并且除了 in 关键字之外，还可以在其前面加上 not，即 not in 表示不存在该子查询表中的结果！
② all 关键字：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
首先我们要先知道 30 号部门所有员工的工资：
select sal from emp where dept=30;
接着就是查询表中雇员的名字、工资、部门号：
select ename, sal, deptno from emp;
此时我们要将两者结合起来，需要使用 all 关键字，其用于比较一个值与子查询返回的所有值是否都满足某个条件。
注意，all 关键字只能与比较运算符一起使用，不能与其他类型的运算符（如逻辑运算符）一起使用。
因为我们要的是比部门号为 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.00 sec)
下面是不使用
all关键字的做法，其实就是用一个max()函数筛选出30号部门的最高薪资，然后对比这个最高薪资即可，就不需要列举出30号部门所有员工的工资了！select * from emp where sal > (select max(sal) from emp where deptno=30);
③ any关键字：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）
在 mysql 中，any 是一个用于比较的关键字，其用法和 all 关键字基本是一样的，只不过 any 关键字的作用是将比较运算符应用于一组值，并返回与任何一个值匹配的行。
只要掌握了样例二的操作，那么这里也是没有问题，就不再赘述了，语句如下所示：
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)
3、多列子查询
我们前面讲的单行子查询是指子查询只返回单列、单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的。而这里讲的 多列子查询 则是指 查询返回多个列数据 的子查询语句。
下面结合案例来理解！
案例：查询和SMITH的部门编号和岗位完全相同的所有雇员，不含SMITH本人
首先我们先查询 SMITH 的部门编号以及岗位：
select deptno, job from emp where ename='SMITH';
可以发现此时上面的 select 语句中有两个列字段，也就说这个筛选完后的表，是一个多列的表，那么我们在外层的 where 语句中进行比较的时候，如果想一步到位同时比较部门编号和岗位的话，可以 用一个 () 圆括号将多个列字段括起来，与筛选完后的表进行判断比较，如下所示：
mysql> select * from emp where (deptno, job) = (select deptno, job 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 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
+--------+-------+-------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)
其实这里的 = 等号换成关键字 in 也是可以的，因为都是比较相同的结果！如下所示：
mysql> select * from emp where (deptno, job) in (select deptno, job 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 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
+--------+-------+-------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)
最后再添加一个限定条件也就是不能包含 SMITH 本人，这个比较简单：
mysql> select * from emp where (deptno, job) = (select deptno, job from emp where ename='SMITH') and ename<>'SMITH';
+--------+-------+-------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+-------+------+---------------------+---------+------+--------+
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
+--------+-------+-------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)
🎏 在 from 子句中的子查询
子查询语句可以出现在 from 子句中，这里要用到数据查询的技巧，把一个子查询当做一个临时表使用，这相当于是 子查询 + 多表查询！
下面通过案例来加深理解！
① 显示每个部门中超过平均工资的员工的姓名、部门、工资、平均工资，以及所在位置
如果说这里我们先用子查询找出每个部门的平均工资，然后通过 where 子句进行每个部门内数据的单独的筛选，那其实操作起来是比较麻烦的！
这是因为我们现在要查的部门有多个，而前面我们讲 where 子句中的子查询，其实更适合用于针对某个部门的筛选，对于多个部门或者 多个字段的共同筛选的话，其实要用 from 子句中的子查询会更加方便！
首先还是一样，我们得用子查询找出每个部门的平均工资：
mysql> select deptno, avg(sal) -> from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
接着就是最重要的环节了，将当前我们这个平均工资表重命名为 tmp（注意，在多表查询的时候用到子查询得到的新表是想要重命名才能使用的，因为它是一个临时表，没有重命名的话是找不到的），与员工表 emp 进行笛卡尔积，也就是多表查询：
mysql> select * -> from emp, (select deptno, avg(sal) from emp group by deptno) as tmp-> ;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | avg(sal) |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 10 | 2916.666667 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 10 | 2916.666667 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 10 | 2916.666667 |
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 20 | 2175.000000 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 20 | 2175.000000 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 20 | 2175.000000 |
……
这样子一来得到的新表中的每个员工都会匹配上 tmp 中的每一个平均值，这我们可以通过 where 条件筛选去掉没意义的部分，也就是部门编号不匹配的那些行记录：
mysql> select ename, tmp.deptno, sal, 平均工资 -> from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) as tmp -> where emp.deptno<=>tmp.deptno-> ;
+--------+--------+---------+--------------+
| ename | deptno | sal | 平均工资 |
+--------+--------+---------+--------------+
| SMITH | 20 | 800.00 | 2175.000000 |
| ALLEN | 30 | 1600.00 | 1566.666667 |
| WARD | 30 | 1250.00 | 1566.666667 |
| JONES | 20 | 2975.00 | 2175.000000 |
| MARTIN | 30 | 1250.00 | 1566.666667 |
| BLAKE | 30 | 2850.00 | 1566.666667 |
| CLARK | 10 | 2450.00 | 2916.666667 |
| SCOTT | 20 | 3000.00 | 2175.000000 |
| KING | 10 | 5000.00 | 2916.666667 |
| TURNER | 30 | 1500.00 | 1566.666667 |
| ADAMS | 20 | 1100.00 | 2175.000000 |
| JAMES | 30 | 950.00 | 1566.666667 |
| FORD | 20 | 3000.00 | 2175.000000 |
| MILLER | 10 | 1300.00 | 2916.666667 |
+--------+--------+---------+--------------+
现在我们得到的就是一个比较干净的表了，其中每个员工与各自部门的平均工资也都有了，我们只需要用 where 条件进行工资比较即可：
mysql> select ename, tmp.deptno, sal, 平均工资 -> from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) as tmp-> where emp.deptno<=>tmp.deptno and emp.sal > 平均工资-> ;
+-------+--------+---------+--------------+
| ename | deptno | sal | 平均工资 |
+-------+--------+---------+--------------+
| ALLEN | 30 | 1600.00 | 1566.666667 |
| JONES | 20 | 2975.00 | 2175.000000 |
| BLAKE | 30 | 2850.00 | 1566.666667 |
| SCOTT | 20 | 3000.00 | 2175.000000 |
| KING | 10 | 5000.00 | 2916.666667 |
| FORD | 20 | 3000.00 | 2175.000000 |
+-------+--------+---------+--------------+
6 rows in set (0.00 sec)
这些就是各个部门里的中流砥柱啦！上面唯一 需要注意的就是在使用列字段的时候，有冲突的列字段的话需要用表名指定！
最后还有一个工作，就是这些员工的所在位置，因为位置字段在 dept 表中，所以这里我们还得再套一层外壳用于多表查询，这个并不难，只是整个语句就很长罢了，注意不要搞混就行！
mysql> select ename, tmp2.deptno, sal, 平均工资, loc 位置 -> from dept, (select ename, tmp.deptno, sal, 平均工资 from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) as tmp where emp.deptno<=>tmp.ddeptno and emp.sal>平均工资) as tmp2 -> where tmp2.deptno<=>dept.deptno-> ;
+-------+--------+---------+--------------+----------+
| ename | deptno | sal | 平均工资 | 位置 |
+-------+--------+---------+--------------+----------+
| ALLEN | 30 | 1600.00 | 1566.666667 | CHICAGO |
| JONES | 20 | 2975.00 | 2175.000000 | DALLAS |
| BLAKE | 30 | 2850.00 | 1566.666667 | CHICAGO |
| SCOTT | 20 | 3000.00 | 2175.000000 | DALLAS |
| KING | 10 | 5000.00 | 2916.666667 | NEW YORK |
| FORD | 20 | 3000.00 | 2175.000000 | DALLAS |
+-------+--------+---------+--------------+----------+
6 rows in set (0.00 sec)
② 查找每个部门工资最高的人的姓名、工资、部门、最高工资
这个和上一道题其实是差不多的，只不过改成了求最高工资，那么通过多表查询+子查询之后，得到的每个员工的工资与最高工资，我们只需要获取工资等于最高工资的那些员工即可，就是我们要找的部门工资最高的人！
mysql> select ename, sal, tmp.deptno, 最高工资 -> from emp, (select deptno, max(sal) 最高工资 from emp group by deptno) as tmp -> where emp.deptno<=>tmp.deptno and emp.sal <=> 最高工资-> ;
+-------+---------+--------+--------------+
| ename | sal | deptno | 最高工资 |
+-------+---------+--------+--------------+
| 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.00 sec)
③ 显示每个部门的信息（部门名、编号、地址）和人员数量
万变不离其宗，这里其实就是要将两张表进行拼接也就是多表查询，然后其中因为人员数量是需要分组聚合统计的，所以要使用子查询进行嵌套，大体的思路还是不变的！
mysql> select dept.deptno, dname, loc, 人员数量-> from dept, (select deptno, count(*) 人员数量 from emp group by deptno) as tmp -> where dept.deptno<=>tmp.deptno-> ;
+--------+------------+----------+--------------+
| deptno | dname | loc | 人员数量 |
+--------+------------+----------+--------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
+--------+------------+----------+--------------+
3 rows in set (0.00 sec)
Ⅴ. 合并查询
在实际应用中，为了合并多个 select 的执行结果，可以使用集合操作符 union 和 union all，但是要注意的是，它们虽然是合并，但是 并不是我们所说的笛卡尔积，只是对它们的结果进行了合并集合而已！
1、union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
下面举个例子就懂了！
案例：将工资大于2500或职位是MANAGER的人找出来
mysql> select ename, sal, job from emp where sal > 2500 union -> select ename, sal, job from emp where job<=>'MANAGER'; --去掉了重复记录
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+
6 rows in set (0.00 sec)
可以看到结果集是 不存在重复的！
2、union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行
下面举个例子就懂了！
案例：将工资大于2500或职位是MANAGER的人找出来
mysql> select ename, sal, job from emp where sal > 2500 union all -> select ename, sal, job from emp where job<=>'MANAGER';
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+
8 rows in set (0.00 sec)
可以看到结果集是 存在重复的！
实战OJ
- 获取所有非manager的员工emp_no
- 获取所有员工当前的manager