举例：查询平均工资在7000以上的部门
解题思路：
第一步，求部门平均工资
mysql> select AVG(salary) as ag,dept.dname from employee,dept where employee.dept_id = dept.did group by dept.did;+--------------+-----------+| ag| dname|+--------------+-----------+| 10750.000000 | 研发部 || 6000.000000| 人事部 || 8000.000000| 测试部 || 5000.000000| 销售部 |+--------------+-----------+4 rows in set (0.00 sec)第二步，把部门平均工资表当作临时表，进行查询 。
select dname from(selectAVG(salary) as ag,dept.dnamefrom employee,dept where employee.dept_id = dept.didgroup by dept.did) as dept_avg_salarywhere dept_avg_salary.ag > 7000;运行结果：
mysql> select dname from (select AVG(salary) as ag,dept.dname from employee,dept where employee.dept_id = dept.did group by dept.did) as dept_avg_salary where dept_avg_salary.ag > 7000;+-----------+| dname|+-----------+| 研发部 || 测试部 |+-----------+2 rows in set (0.00 sec)7.2 作为查询条件使用举例：找出工资最高的员工的所有信息
select * from employee,deptwhere employee.dept_id = dept.didand employee.salary = (select MAX(salary) from employee);查询结果
mysql> select * from employee,dept where employee.dept_id = dept.did and employee.salary = (select MAX(salary) from employee);+----+-----------+-----+-----+----------+------------+---------+-----+-----------+| id | name| age | sex | salary| hire_date| dept_id | did | dname|+----+-----------+-----+-----+----------+------------+---------+-----+-----------+|2 | 奥力给 |30 |1 | 18000.00 | 2020-01-08 |1 |1 | 研发部 |+----+-----------+-----+-----+----------+------------+---------+-----+-----------+1 row in set (0.01 sec)举例：求工资大于所有人平均工资的员工的所有信息
select * from employee,deptwhere employee.dept_id = dept.didand employee.salary > (select AVG(salary) from employee);查询结果
mysql> select * from employee,dept where employee.dept_id = dept.did and employee.salary > (select AVG(salary) from employee);+----+-----------+-----+-----+----------+------------+---------+-----+-----------+| id | name| age | sex | salary| hire_date| dept_id | did | dname|+----+-----------+-----+-----+----------+------------+---------+-----+-----------+|1 | 菜虚鲲 |20 |2 | 10000.00 | 2020-01-10 |1 |1 | 研发部 ||2 | 奥力给 |30 |1 | 18000.00 | 2020-01-08 |1 |1 | 研发部 ||4 | 小张|25 |1 | 8000.00| 2020-01-10 |1 |1 | 研发部 ||9 | 张大骚 |30 |1 | 9000.00| 2020-01-07 |3 |3 | 测试部 |+----+-----------+-----+-----+----------+------------+---------+-----+-----------+4 rows in set (0.00 sec)7.3. 嵌套查询关键字7.3.1 ANY、SOME两者意义相同，与比较运算符(=、>、>=、<、<=、<>)结合起来使用，any/some作用于子查询语句，只要子查询语句中有一个符合条件，就返回true 。
select s1 from t1 where s1 > any (select s1 from t2);假设子查询语句返回结果有三个result1，result2，result3，则上述语句等同于：
select s1 from t1 where s1 > result1 or s1 > result2 or s1 > result3;7.3.2 INin 与 =any 相同 。相当于:
select s1 from t1 where s1 = result1 or s1 = result2 or s1 = result3;7.3.3 ALL当子查询语句中的所有项都符合条件时，才返回true 。
select s1 from t1 where s1 > all (select s1 from t2);等同于:
select s1 from t1 where s1 > result1 and s1 > result2 and s1 > result3;7.3.4 EXISTS、NOT EXISTS语法:
select ... from tableName exists(subquery);当子查询语句subquery返回列时，exists表达式为true，此时执行前面的查询语句 。子查询语句没有返回任何列时，exists语句为false，不执行前面的查询语句 。
mysql> select * from employee where exists (select * from employee where id =1);+----+-----------+-----+-----+----------+------------+---------+| id | name| age | sex | salary| hire_date| dept_id |+----+-----------+-----+-----+----------+------------+---------+|1 | 菜虚鲲 |20 |2 | 10000.00 | 2020-01-10 |1 ||2 | 奥力给 |30 |1 | 18000.00 | 2020-01-08 |1 ||3 | 老八|28 |1 | 7000.00| 2020-01-07 |1 ||4 | 小张|25 |1 | 8000.00| 2020-01-10 |1 ||5 | 小红|20 |2 | 6000.00| 2020-01-05 |2 ||6 | 小丽|23 |2 | 6500.00| 2020-01-05 |2 ||7 | 小花|21 |2 | 5500.00| 2020-01-10 |2 ||8 | 马小跳 |25 |1 | 7000.00| 2020-01-01 |3 ||9 | 张大骚 |30 |1 | 9000.00| 2020-01-07 |3 || 10 | 马冬梅 |31 |2 | 5000.00| 2020-01-07 |4 || 11 | 川坚果 |60 |1 | 100.00| 2020-01-08 | NULL|+----+-----------+-----+-----+----------+------------+---------+11 rows in set (0.00 sec)当子查询语句没有返回任何列时
mysql> select * from employee where exists (select * from employee where id =12);Empty set