【MySQL】子查询

#第九章 子查询

#需求：谁的工资比Abel高？
#方式一：
SELECT salary
from employees
where last_name = 'Abel';

select last_name, salary
from employees
where salary > 11000;

#方式二：自连接
select e2.last_name ,e2.salary
from employees e1, employees e2
where e2.salary > e1.salary #多表的连接条件
and e1.last_name = 'Abel';

#方式三：子查询
select last_name, salary
from employees
where salary > (
               SELECT salary
                from employees
                where last_name = 'Abel'
                );

#2.称谓的规范：外查询（或主查询）、内查询（或子查询）
/*
子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用 。
注意事项：
子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应多行子查询
*/
#3. 子查询的分类
/*
角度1.从内查询返回结果的条目数  
      单行子查询 vs 多行子查询
角度2. 内查询是否被执行多次
      相关子查询 vs 不相关子查询
比如：相关子查询的需求：查询工资大于本部门平均工资的员工信息
      不相关查询的需求：查询工资大于本公司平均工资的员工信息
*/
#4. 单行子查询
#4.1 单行操作符
#题目：查询工资大于 149 号员工工资的员工的信息

#子查询的编写技巧：1.从里往外写 2.从外往里写
select employee_id, last_name
from employees
where salary > (
                select salary 
                from employees
                where employee_id = 149
               );
               
#题目：返回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
                );
                
#题目：返回公司工资最少的员工的last_name,job_id和salary
select last_name, job_id, salary
from employees
where salary = (
                 SELECT MIN(salary)
                 FROM employees
                );

#题目：查询与 141  号员工的manager_id和department_id相同
#的其他员工的employee_id，manager_id，department_id
#方式一：
select employee_id, manager_id, department_id
from employees
where  manager_id = (
                    select manager_id
                    from employees
                    where employee_id = 141
                    )
and department_id = (
                    select department_id
                    from employees
                    where employee_id = 141
                    )
and employee_id <> 141;
#方式二：（了解）
select employee_id, manager_id, department_id
from employees
where  (manager_id, department_id) =(
                                   select manager_id, department_id
                                   from employees
                                   where employee_id = 141
                                )
and employee_id <> 141;

#题目：查询最低工资大于 110 号部门最低工资的部门id和其最低工资
select department_id, min(salary)
from employees
group by department_id
having min(salary) > (
                     select min(salary)
                     from employees
                     where department_id = 110
                    );
#题目：显式员工的employee_id,last_name和location。
#其中，若员工department_id与location_id为 1800的department_id相同，
#则location为’Canada’，其余则为’USA’。
select employee_id, last_name, case when department_id =(
                                            select department_id 
                                            from departments
                                            where location_id = 1800
                                            ) then 'Canda'
                                            else 'USA'
                                            END location
from employees;

#4.2 子查询中的空值问题
select last_name, job_id
from employees
where job_id = (
                select job_id
                from employees
                where last_name = 'Haas'   #空值查询不会报错 但没有结果
                );

#4.3 非法子查询
SELECT employee_id, last_name
FROM employees
WHERE salary =
            (SELECT MIN(salary)
            FROM employees
            GROUP BY department_id);   #子查询返回了多行数据

#5.多行子查询
#5.1 多行子查询的操作符： IN ANY ALL SOME(同any)
#IN
SELECT employee_id, last_name
FROM employees
WHERE salary in
            (SELECT MIN(salary)
            FROM employees
            GROUP BY department_id); 
            
#ANY/ALL：
#题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的
#员工号、姓名、job_id 以及salary
select employee_id, last_name, job_id, salary
from employees
where job_id <> 'IT_PROG'
and salary < any (
                    select salary
                    from employees
                    where job_id = 'IT_PROG'
                    );
#题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的
#员工号、姓名、job_id 以及salary
select employee_id, last_name, job_id, salary
from employees
where job_id <> 'IT_PROG'
and salary < all (
                    select salary
                    from employees
                    where job_id = 'IT_PROG'
                    );
                    
#题目：查询平均工资最低的部门id
#mysql中聚合函数不能嵌套使用
#方式1：
select department_id
from employees
group by department_id
having avg(salary) = 
                    (select min(avg_sal)
                    from(
                        select avg(salary) avg_sal
                        from employees
                        group by department_id
                        )t_dept_avg_sal
                    );
#方式2：

select department_id
from employees
group by department_id
having avg(salary) <= all 
                    (
                        select avg(salary) avg_sal
                        from employees
                        group by department_id
                    );
#5.3 空值问题
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
            SELECT manager_id
            FROM employees
#           where manager_id is not null
            );

#6.相关子查询
#题目：查询员工中工资大于本公司平均工资的员工的last_name,salary和其department_id
select last_name, salary, department_id
from employees
where salary > (
                select avg(salary)
                from employees
                );
#题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方法一：使用相关子查询
select last_name, salary, department_id
from employees e1
where salary > (
                select avg(salary)
                from employees e2
                where department_id = e1.department_id  #外部表在内部表中被运用
                );
#方法二：在from中声明子查询
select e.last_name, e.salary, e.department_id
from employees e,(
                select department_id, avg(salary) avg_sal
                from employees
                group by department_id) t_dept_avg_sal
where e.department_id = t_dept_avg_sal.department_id
and e.salary > t_dept_avg_sal.avg_sal;

#题目：查询员工的id,salary,按照department_name 排序
select e.employee_id, e.salary,d.department_name
from  employees e left join departments d
on e.department_id = d.department_id
order by d.department_name;

select e.employee_id, e.salary
from  employees e 
order by (
              select department_name
              from departments d
              where e.department_id = d.department_id
            );
#结论：在select中，除了group by和limit中 都可以进行子查询
/*
SELECT ...,......,.....(存在聚合函数)
FROM ......(left / right)JOIN.....ON.....
(left / right)JOIN.....ON.....
WHERE 不包含聚合函数的过滤条件
GROUP BY ......,......
HAVING 包含聚合函数的过滤条件
ORDER BT .....,...(ASC/DESC)
LIMIT .....,.....
*/

#题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于 2 ，
#输出这些相同id的员工的employee_id,last_name和其job_id
#select * from job_history;

select employee_id, last_name, job_id
from employees e
            where 2<= (
              select count(*)
              from job_history j
              where e.employee_id = j.employee_id
            );

#6.2 exits 与 not exists 关键字 
#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
#方式一：
select DISTINCT mgr.employee_id,  mgr.last_name,  mgr.job_id,  mgr.department_id
from employees emp join employees mgr
on emp.manager_id = mgr.employee_id;

#方式二：子查询
select employee_id,last_name,job_id,department_id
from employees
where employee_id in
                (
                select distinct manager_id
                from employees
                );
                
#方式三：使用exists
select employee_id,last_name,job_id,department_id
from employees e1
where exists
                (
                select *
                from employees e2
                where e1.employee_id = e2.manager_id
                );

#查询departments表中，不存在于employees表中的部门的department_id和
#department_name
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
                FROM employees
                WHERE department_id = d.department_id);