MSQL子查询

子查询
-- 出现在其他语句中的select语句，称为子查询或内查询，外部查询的查询语句，称为主查询或外查询
-- 分类
-- 按子查询出现的位置
-- SELECT  后面
-- from  后面
-- where 或having后面   标量子查询   单行
-- exists后面
-- 按结果集的行列数
SELECT  first_name  from  employees  WHERE  department_id  in
(SELECT  department_id  from  departments  WHERE
location_id=1700
)


where 或  having后面
按量子查询
列子查询
行子查询

子查询会放在括号内
子查询一般放在条件的右侧
按量查询一般搭配单行操作符使用
列子查询  一般搭配单行操作符使用
in,any/some,all



WHERE后面的单行子查询(标量子查询)
案例  谁的工资比Abel高
查询Abel的工资
SELECT  salary  from   employees  WHERE  last_name='Abel'

查询员工信息，满足salary>1的结果
SELECT  *from  employees  
WHERE  salary>(
SELECT  salary  from   employees  WHERE  last_name='Abel'
);


案例2返回job_id与141号员工相同，salary比143号员工多的员工  姓名，job_id和工资
1查询141员工的job_id
SELECT  job_id  from employees  
WHERE  employee_id=141

2查询143号员工的salary
SELECT salary  from  employees  where  employee_id=143

3查询员工的姓名  job_id和工资 要求job_id=1并且salary>2
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
)


案例3  返回公司的工资最少的员工的last_name，job_id和salary
1查询公司的最低工资
SELECT  min(salary)  from employees

查询last_name，job_id,salary  要求salry=1
SELECT  last_name,job_id,salary  from  employees
WHERE  salary=(
SELECT  MIN(salary)  from  employees
)


案例四
查询最低工资大于50号部门最低工资的部门ID和其最低工资
1查询50号部门的最低工资
SELECT  MIN(salary)  from employees
WHERE  department_id=50

2查询每个部门的最低工资
SELECT  MIN(salary),department_id
from  employees
GROUP BY  department_id;


3筛选2满足Min(salary)>1
SELECT  MIN(salary),department_id
from  employees
GROUP BY  department_id
HAVING  MIN(salary)>(
SELECT  MIN(salary)  from employees
WHERE  department_id=50
)


WHERE后面的列子查询(多行子查询)
in  等于列表中的任意一个
ANY   SOME和子查询返回的某个值比较
ALL  和子查询返回的所有值比较


返回location——id是1400或1700的部门中的所有员工姓名
查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id 
from  departments  
WHERE  location_id  in(1400,1700)

查询员工姓名，要求部门号是1列表中的某一个
SELECT  last_name  from employees  where department_id  in
(
SELECT DISTINCT department_id 
from  departments  
WHERE  location_id  in(1400,1700)
)


案例2  返回其他工种中比job_id为‘IT_PROG’部门任意工资低的员工的员工号，姓名，job_id,salary

1查询job_id为‘IT_PROG’部门任一工资
SELECT  DISTINCT   salary  from  employees
WHERE  job_id='IT_PROG'


2查询员工名，姓名，job_id以及salary，salary<1的任意一个
SELECT  last_name,employee_id,job_id,salary
from  employees
WHERE  salary<ANY(
SELECT  DISTINCT   salary  from  employees
WHERE  job_id='IT_PROG'
)  and  job_id<>'IT_PROG'


第二种

SELECT  last_name,employee_id,job_id,salary
from  employees
WHERE  salary<(
SELECT   MAX(salary)  from  employees
WHERE  job_id='IT_PROG'
)  and  job_id<>'IT_PROG'



案例3  返回其他部门中比job_id为'IT_PROG' 部门所有工资都低的员工的员工号，姓名，job_id以及salary

SELECT  last_name,employee_id,job_id,salary
from  employees
WHERE  salary<ALL(
SELECT  DISTINCT   salary  from  employees
WHERE  job_id='IT_PROG'
)  and  job_id<>'IT_PROG'
或

SELECT  last_name,employee_id,job_id,salary
from  employees
WHERE  salary<(
SELECT  MIN(salary)  from  employees
WHERE  job_id='IT_PROG'
)  and  job_id<>'IT_PROG'



where后面的行子查询（结果集一行多列或多行多列）  
案例   查询员工编号最小并且工资最高的员工信息
行子查询
SELECT  *from  employees  WHERE(employee_id,salary)=(
SELECT  MIN(employee_id),MAX(salary)  from employees
)



1查询最小的员工编号
SELECT  MIN(employee_id)  from employees

查询最高工资
SELECT  MAX(salary)  from  employees

查询员工信息
SELECT  *  from  employees  where 
employee_id=(
SELECT  MIN(employee_id)
from  employees
)  AND  salary=(
SELECT  MAX(salary)  FROM  employees
)


SELECT后面的子查询  支持单行查询   标量查询
案例  查询每个部门的员工个数
SELECT  d.*,(
SELECT  COUNT(*) from  employees  e 
where
e.department_id=d.department_id
) 个数
from  departments d;

案例2  查询员工工号=102的部门名

select (
SELECT  department_name  from  departments d
INNER  JOIN  employees  e
on d.department_id=e.department_id
WHERE  e.employee_id=102
)


from后面子查询

案例  查询每个部门的平均工资的工资等级
查询每个部门的平均工资
SELECT  AVG(salary),department_id  from  employees
GROUP BY  department_id


连接1的结果集合job_grade表筛选条件平均工资  BETWEEN   lowest_sal  and  highest_sal


SELECT 
ag_dep.*,g.grade_level

from  (
SELECT  AVG(salary),department_id  from  employees
GROUP BY  department_id
)  ag_dep
INNER  JOIN  job_grades  g
on  ag_dep.ag  BETWEEN  lowest_sal  AND  highest_sal;


EXISTS后面  子查询
SELECT  EXISTS  (SELECT  employee_id  from  employees  WHERE salary=30000)