Oracle从小白到精通第八天（ 高级子查询）（尚硅谷学习Oracle笔记）

1.多列子查询

主查询与子查询返回的 多个列 进行比较
例子
查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id
没有学习多列子查询的时候是这样写的

select employee_id,manager_id,department_id
from employees e1
where manager_id in (
            select manager_id
            from employees
            where employee_id in(141,174)
            )
and department_id in(
            select department_id
            from employees
            where employee_id in(141,174)
            )
and employee_id not in (141,174)
学习了多列子查询后，可以把两列合并成一列，列与列要一一对应
select employee_id,manager_id,department_id
from employees e1
where (manager_id,department_id) in (
            select manager_id,department_id
            from employees
            where employee_id in(141,174)
            )
and employee_id not in (141,174)

2.在from子句中使用子查询

问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
解答—方法1
select last_name,department_id,salary,
            (select avg(salary)from employees e3
            where e1.department_id = e3.department_id
            group by department_id) avg_salary
from employees e1
where salary >
            (select avg(salary)
            from employees e2
            where e1.department_id = e2.department_id
            group by department_id
            )
解答—方法2
SELECT a.last_name, a.salary, a.department_id, b.salavg
FROM employees a, (SELECT department_id,
                                    AVG(salary) salavg
                                    FROM employees
                                    GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;

3.单列子查询

单列子查询表达式是在一行中只返回一列的子查询
问题： 显示员工的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 ‘Canada’ ELSE ‘USA’ END) location
FROM employees;
问题：查询员工的employee_id,last_name,要求按照员工的department_name排序
SELECT employee_id, last_name
FROM employees e
ORDER BY
                (SELECT department_name
                FROM departments d
                WHERE e.department_id = d.department_id);

4.相关子查询

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询

5.exists操作符

查询公司管理者的employee_id,last_name,job_id,department_id信息
方法一
select employee_id,last_name,job_id,department_id
from employees e1
where e1.employee_id in (
        select e2.manager_id
        from employees e2
        where e1.employee_id=e2.manager_id
        )
方法二
select distinct e1.employee_id,e1.last_name,e1.job_id,e1.department_id
from employees e1,employees e2
where e1.employee_id =e2.manager_id
方法三使用exists在简化操作

not exists与exists正好相反

6.相关更新

使用相关子查询依据一个表中的数据更新另一个表的数据
第一步，建立一个emp011的表
第二步，添加一列，department_name
第三步，把department _name的数据给添加上也就是更新

相关删除

7.with。。。。。as（）子句

使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中
使用 WITH 子句可以提高查询效率
问题：查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息

with子句个人认为就是把复杂的东西先求出来，然后在带入