Mysql 子查询

子查询含义：出现在其他语句中的select语句，称为子查询或内查询

外部查询语句称为主查询或父查询

举个例子

select first_name from employees where
department_id in(
select department_id from departments
where location_id = 1700
)

分类：

按子查询出现的位置：

select 后面
仅仅支持标量子查询
from 后面
支持表子查询
where或having后面
标量子查询（单行子查询）
列子查询（多行子查询）
行子查询
exists 后面（相关子查询）
表子查询

按结果集的行列数不同：

标量子查询(结果集只有一行一列)

列子查询（结果集只有一列多行）

行子查询（结果集有一行多列）

表子查询（结果集为多行多列）

 

一、where或having后面的子查询

1.标量子查询（单行子查询）

2.列子子查询（多行子查询）

3.行子查询 （用的较少）

特点：

1.子查询放在小括号内

2.子查询一般放在条件内测

3.标量子查询，一边拿搭配着单行操作符使用

> < >= <= = <>

列子查询，一般搭配着多行操作符使用

in any/some all

4.子查询优先于主查询执行

1.标量子查询

谁的工资比Abel高？

第一步：查Abel的工资

select salary
from employees
where last_name = 'Abel'

第二部：查询员工的信息，满足salary>第一步的结果

select *
from employees
where salary>(
select salary
from employees
where last_name = 'Abel'

)//因为这里Abel的工资可以变，所以不能直接给定一个值作为Abel的工资

返回job_id 与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

select last_name,job_id,salary
from employees
where job_id = (
select job_id from employees where employee_id = 141
)
and salary>(
select salary from employees where employee_id = 143)

查询最低工资大于50号部门最低工资的部门id和其最低工资

select department_id,min(salary)
from employees
group by department_id
having min(salary)>(
select min(salary)
from employees
where department_id = 50
);

2.列子查询（多行子查询）

特点：

返回多行

使用多行比较操作符如

in/not in 等于列表中的任意一个

any/some 和子查询返回的某一个值比较

例如
a >any(10,15,20)
a = 12则满足条件
也就是说a大于any中的任何一个就可以

all 和子查询返回的所有值比较

例如
a > all(10,15,20)
a = 22满足条件
也就是说a的值大于all中所有的数据项

 

使用in返回location_id是1400或1700的部门中的所有员工姓名

第一步查询location_id 是1400或1700的部门

第二步查询员工姓名，要求部门号是第一步结果列表中的一个

select last_name
from employees
where department_id in(
select department_id from departments where location_id = 1400 or location_id =1700
);

使用any返回其他部门中比job_id为‘IT_PROG’部门任一工资低的员工的工号、姓名、job_id已经salary

select employee_id,last_name,job_id,salary
from employees
where salary<any(
select distinct salary
from employees
where job_id ='IT_PROG'
)and job_id <>'IT_PROG';

使用all返回其他部门中比job_id为‘IT_PROG’部门所有工资低的员工的工号、姓名、job_id以及salary

 

select employee_id,last_name,job_id,salary
from employees
where salary<all(
select distinct salary
from employees
where job_id ='IT_PROG'
)and job_id <>'IT_PROG';

3.行子查询

查询员工编号最小并且工资最高的员工信息

select *
from employees
where employee_id =
(select min(employee_id)
from employees
)and salary=(
select max(salary)
from employees
);

我们可以使用多个标量子查询来进行查询，然而我们也可以用行子查询

select *
from employees
where (employee_id,salary)=(
select min(employee_id),max(salary)
from employees
);

只有在多个条件变量 的运算符相同时才可以用行子查询

二、放在select后面

只能返回一行一列

查询每个部门的员工个数

select d.*,(
select count(*)
from employees e
where e.department_id = d.department_id
)个数
from departments d;

这个用分组查询也可以做，但是差不了没有员工的部门

三、放在from后面

查询每个部门的平均工资的工资等级

select ag,department_id,grade_level
from (
select avg(salary),department_id
from employees
group by department_id
) ag_dep,
inner join job_grades g
on dg_dep.ag between lowest_sal and highest_sal;

查询每个部门中工资高于部门平均工资的员工姓名 工号 工资

1.首先查每个部门的平均工资

select avg(salary) g,department_id
from employees
group by department_id

2.如果用having 的话 由于工资是大于的关系，部门号是等于的关系，所以不能使用行子查询，但是又要保证比较的时候部门号一样所以只能将

两个表连接起来

select employee_id,last_name,salary
from employees e
inner join (select avg(salary) g,department_id
from employees
group by department_id
) a
on a.department_id = e.department_id
where salary >a.g;

四、exists后面（相关子查询）

先执行外查询，在执行子查询

select exists(select employee_id from employees);

查询结果为1

也就是说exists 是判断有没有符合条件的结果，如果有则返回1，反之返回0

查询有员工的部门名

select department_name
from departments
where exists(
select *
from employees e
where d.department_id = e.department_id
)

也可以用in来代替

select department_name
from departments d
where d.department_id in(
select department_id
from employees
);

查询没有女朋友的男神信息

select boy.*
from boy bo
where exists(
select boyfriend_id
from beauty b
where bo.id = b.boyfriend_id
);