前面我们学习的 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) 可以看到结果集是存在重复的！