进阶7：子查询
含义
一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询
特点：
1、子查询都放在小括号内
2、子查询可以放在
from后面仅仅支持子查询、
select后面支持标量子查询（单行）、
where后面having后面标量子查询，列子查询
，但一般放在条件的右侧，也可以放在
exists后面（相关子查询）表子查询
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空
② 多行子查询/列子查询（一列多行）
结果集有多行
行子查询，多行多列
Where或者having后面
标量子查询（单行子查询）
列子查询（多行子查询）
行子查询（多列多行）
特点：子查询都放在小括号内，子查询一般防止条件的右侧，标量子查询一般搭配单行操作符 > < >= ….
列子查询一般搭配多行操作符号一般搭配多行操作符使用：any/some、all、in、not in
in： 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替
标量子查询
#谁的工资比Abel高
- 查询Abel工资高
- 然后找比这个标量大的
mysql> select last_name from employees where salary>(select salary from employees where last_name='Abel');
#找job_id 与141号员工相同，salary比143号员工多的员工 姓名，job_id和工资
mysql> select job_id,salary from employees where job_id=(select job_id from employees where employee_id =141) andd salary>(select salary from employees where employee_id =143);
+----------+---------+
| job_id | salary |
+----------+---------+
| ST_CLERK | 3200.00 |
#返回公司工资最少的员工的last_name,job_id和salary
查询公司的最低工资
mysql> select last_name,job_id,salary from employees where salary=(select min(salary) from employees);
+-----------+----------+---------+
| last_name | job_id | salary |
+-----------+----------+---------+
| Olson | ST_CLERK | 2100.00 |
+-----------+----------+---------+
mysql> select last_name,job_id,salary from employees order by salary asc limit 1;
+-----------+----------+---------+
| last_name | job_id | salary |
+-----------+----------+---------+
| Olson | ST_CLERK | 2100.00 |
+-----------+----------+---------+
#查询最低工资大于50号部门最低工资的部门id和其最低工资
mysql> select department_id,min(salary) from employees group by department_id having min(salary)>(select min(salaary) from employees where department_id=50);
+---------------+-------------+
| department_id | min(salary) |
+---------------+-------------+
| NULL | 7000.00 |
| 10 | 4400.00 |
| 20 | 6000.00 |
| 30 | 2500.00 |
| 40 | 6500.00 |
+---------------+-------------+
列子查询
in/not in 等于列表中的任意一个
any/some 和子查询返回的某一个比较
all 和子查询返回的所有值比较
>any 可以替换成>min
>all 可以替换成>max
#返回location_id 是1400或者1700的部门中的所有员工姓名
- 查询location 1400或者1700
mysql> select last_name,department_id from employees where department_id in(select distinct department_id from departments s where location_id in(1400,1700));
+------------+---------------+
| last_name | department_id |
+------------+---------------+
| Hunold | 60 |
| Ernst | 60 |
mysql> select last_name,e.department_id from employees e join departments d on e.department_id=d.department_id where d.location_id in((1400,1700);
返回其他部门中比job_id 为’IT_PROG’部门任一工资低的员工号、姓名、job_id以及salary 任一不是任意
mysql> select employee_id,last_name,job_id,salary from employees where salary<any(select salary from employees where job_id='IT_PROG')) and job_id!='IT_PROG';
mysql> select employee_id,last_name,job_id,salary from employees where salary<(select max(salary) from employees where job_id='IT_PROGG') and job_id!='IT_PROG';
行子查询 一行多列或者多行多列
查询员工编号最小且工资最高的员工信息（不一定存在）
可以分开来查
两个条件都是等于（相同的关系）
mysql> select * from employees where(employee_id,salary)=(select min(employee_id),max(salary)from employees);
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| 100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
select后面仅仅支持标量子查询
查询每个部门的员工数
mysql> select d.*,(select count(*) from employees e where e.department_id=d.department_id) nums from departments d;
+---------------+-----------------+------------+-------------+------+
| department_id | department_name | manager_id | location_id | nums |
+---------------+-----------------+------------+-------------+------+
| 10 | Adm | 200 | 1700 | 1 |
查询员工号=102的部门名
mysql> select (select department_name from departments d inner join employees e on d.department_id=e.department_id where employee_id=1102)name;
+------+
| name |
+------+
| Exe |
+------+
1 row in set (0.00 sec)
from后面 子查询的表格充当数据源，必须起别名
查询每个部门的平均工资的工资等级
先查每个部门的平均工资
mysql> select ag.*,g.grade_level from (select avg(salary) ave,department_id from employees group by department_id) ag inner join job_grades g on ag.ave between lowest_sal and highest_sal;
+--------------+---------------+-------------+
| ave | department_id | grade_level |
+--------------+---------------+-------------+
| 7000.000000 | NULL | C |
| 4400.000000 | 10 | B |
| 9500.000000 | 20 | C |
Exists后面相关子查询 0或者1，有没有值
mysql> select exists(select employee_id from employees);
+-------------------------------------------+
| exists(select employee_id from employees) |
+-------------------------------------------+
| 1 |
mysql> select exists(select employee_id from employees where salary=20000000);
+-----------------------------------------------------------------+
| exists(select employee_id from employees where salary=20000000) |
+-----------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------+
查询有员工的部门名
mysql> select department_name from departments d where exists(select* from employees e where d.departmeent_id=e.department_id);
+-----------------+
| department_name |
+-----------------+
mysql> select department_name from departments where department_id in (select department_id from employees);
+-----------------+
| department_name |
+-----------------+
| Adm |
子查询 测 试
#查询工资最低的员工信息: last_name, salary
mysql> select last_name,salary from employees where salary=(select min(salary) from employees);
+-----------+---------+
| last_name | salary |
+-----------+---------+
| Olson | 2100.00 |
+-----------+---------+
1 row in set (0.00 sec)
#查询平均工资最低的部门信息
mysql> select * from departments d where d.department_id=(select department_id from employees group by department_id having avg(salary)=(select min(ag) from ( select department_id,avg(salary) ag from employees group by department_id) ag_dep));
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 50 | Shi | 121 | 1500 |
+---------------+-----------------+------------+-------------+
1 row in set (0.00 sec)
这样很麻烦，所以可以用limit
mysql> select * from departments where department_id =(select department_id from employees group by department_id order by avg(salary) asc limit 1);
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 50 | Shi | 121 | 1500 |
+---------------+-----------------+------------+-------------+
1 row in set (0.00 sec)
#查询平均工资最低的部门信息和该部门的平均工资
mysql> select d.*,dv.ag from departments d inner join (select avg(salary) ag,department_id from employees group by department_id) dv on d.department_id=dv.department_id order by dv.ag limit 1;
+---------------+-----------------+------------+-------------+-------------+
| department_id | department_name | manager_id | location_id | ag |
+---------------+-----------------+------------+-------------+-------------+
| 50 | Shi | 121 | 1500 | 3475.555556 |
+---------------+-----------------+------------+-------------+-------------+
#查询平均工资最高的 job 信息
mysql> select * from jobs where job_id=(select job_id from employees group by job_id order by avg(salary) asc limit 1);
+----------+------------------+------------+------------+
| job_id | job_title | min_salary | max_salary |
+----------+------------------+------------+------------+
| PU_CLERK | Purchasing Clerk | 2500 | 5500 |
+----------+------------------+------------+------------+
1 row in set (0.00 sec)
#查询平均工资高于公司平均工资的部门有哪些?
mysql> select department_id from employees group by department_id having avg(salary)>(select avg(salary) from employees);
#查询出公司中所有 manager 的详细信息.
mysql> select * from employees where employee_id=any( select distinct manager_id from employees);
#各个部门中 最高工资中最低的那个部门的 最低工资是多少
mysql> select department_id,min(salary) from employees group by department_id having department_id=(select department_id from employees group by department_id order by max(salary) asc limit 1);
+---------------+-------------+
| department_id | min(salary) |
+---------------+-------------+
| 10 | 4400.00 |
+---------------+-------------+
#查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
mysql> select last_name,department_id,email,salary from employees where employee_id=(select manager_id from departments where department_id=(select department_id from employees grroup by department_id order by avg(salary) desc limit 1));
+-----------+---------------+-------+----------+
| last_name | department_id | email | salary |
+-----------+---------------+-------+----------+
| K_ing | 90 | SKING | 24000.00 |
+-----------+---------------+-------+----------+
上述方法三层嵌套子查询
下方法将两个表链接后将第一步作为条件
mysql> select last_name,d.department_id,email,salary from employees e inner join departments d on d.manager_id=e.employee_id where d.department_id=(select department_id from employees group by department_id order by avg(salary) desc limit 1);
+-----------+---------------+-------+----------+
| last_name | department_id | email | salary |
+-----------+---------------+-------+----------+
| K_ing | 90 | SKING | 24000.00 |
+-----------+---------------+-------+----------+
##进阶8：分页查询
应用场景：
实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
语法：
select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;
特点：
1.起始条目索引从0开始
2.limit子句放在查询语句的最后
3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page
查询前五条员工信息
mysql> select * from employees limit 0,5;
或者limit 5
##进阶9：联合查询
引入：
union 联合、合并
语法：
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】
特点：
1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重