Oracle入门第六天（下）——高级子查询

一、概述

　　主要内容：

　　

二、子查询介绍

　　1.简单子查询（WHERE子查询）

SELECT last_name
FROM   employees
WHERE  salary > 
                (SELECT salary
                 FROM   employees
                 WHERE  employee_id = 149) ;

　　2.多列子查询

　　

　　成对比较：

SELECT    employee_id, manager_id, department_id
FROM    employees
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);

　　不成对比较：

SELECT  employee_id, manager_id, department_id
FROM    employees
WHERE   manager_id IN                   (SELECT  manager_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND     department_id IN                   (SELECT  department_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND    employee_id NOT IN(174,141);

　　3.在FROM子句中使用子查询

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;

　　4.单列子查询

　　　　在CASE中使用单列子查询

--问题：显式员工的employee_id,last_name和location。
--其中，若员工department_id与location_id为1800的department_id相同，
--则location为’Canada’,其余则为’USA’。
SELECT employee_id,last_name,
       (CASE department_id
        WHEN  
             (SELECT department_id  FROM departments WHERE location_id = 1800) 
        THEN 'Canada'
        ELSE 'USA'
        END       
             ) location
        --(CASE
        --WHEN department_id =
        --(SELECT department_id FROM departments
                 --WHERE location_id = 1800)      
        --THEN 'Canada' ELSE 'USA' END) location
FROM employees

　　5.相关子查询

　　　　相关子查询就是每一次子查询都与外层主查询相关，

　　　　而非相关子查询就是每次都返回一致的结果，与主查询无关，例如只是提供类似一个固定的筛选条件

　　

　　语法：

　　

　　示例：

--问题：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees outer
WHERE salary >
             (
               SELECT AVG(salary)
               FROM employees
               WHERE department_id = outer.department_id
             )

　　6.EXISTS操作符

--问题：查询公司管理者的employee_id,last_name,job_id,department_id信息
SELECT employee_id,last_name,job_id,department_id
FROM employees outer
WHERE EXISTS
            (
              SELECT 1 
              FROM employees
              WHERE manager_id = outer.employee_id
            )

　　NOT EXISTS同理

　　7.相关更新　　

　　　　使用相关子查询依据一个表中的数据更新另一个表的数据

　　语法：

UPDATE table1 alias1
SET    column = (SELECT expression
                 FROM   table2 alias2
                 WHERE  alias1.column =    
                        alias2.column);

　　示例：

UPDATE employees e
SET    department_name = 
              (SELECT department_name 
           FROM   departments d
           WHERE  e.department_id = d.department_id);

 　　相关删除同理：

DELETE FROM employees E
WHERE employee_id =  
           (SELECT employee_id
            FROM   emp_history 
            WHERE  employee_id = E.employee_id);

　　8.WITH子句

　　　　有点类似ODPS的CTE

　　　　　　   使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块

　　　　　　WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中

　　　　　　使用 WITH 子句可以提高查询效率

　　　　一般而言，可以用于UNION等操作，来提高可读性！

　　语法：

–针对一个别名
with tmp as (select * from tb_name)

–针对多个别名
with
   tmp as (select * from tb_name),
   tmp2 as (select * from tb_name2),
   tmp3 as (select * from tb_name3),
   …

　　示例：

WITH TT
AS
     (SELECT employee_id,salary FROM employees)
     
SELECT * FROM TT

　　更多WITH示例，参考：https://www.cnblogs.com/linjiqin/archive/2013/06/24/3152667.html