15、Oracle中的高级子查询

标签：15 查询 department employee Oracle WHERE id SELECT

最近项目要用到Oracle，奈何之前没有使用过，所以在B站上面找了一个学习视频，用于记录学习过程以及自己的思考。
视频链接：
【尚硅谷】Oracle数据库全套教程，oracle从安装到实战应用
如果有侵权，请联系删除，谢谢。

学习目标：

书写多列子查询
在 FROM 子句中使用子查询
在SQL中使用单列子查询
书写相关子查询
使用 EXISTS 和 NOT EXISTS 操作符
使用子查询更新和删除数据
使用 WITH 子句

1、子查询

子查询是嵌套在 SQL 语句中的另一个SELECT 语句

SELECT	select_list
FROM	table
WHERE	expr operator (SELECT select_list
		         FROM	  table);

子查询 (内查询) 在主查询执行之前执行
主查询(外查询)使用子查询的结果

子查询应用举例
问题：查询工资大于149号员工工资的员工的信息

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

2、多列子查询

主查询与子查询返回的多个列进行比较

多列子查询中的比较分为两种:

成对比较
不成对比较

2.1、成对比较举例

问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id

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);

(column1, column2) in (column1, column2)
以及最后面的排查不需要的数据 NOT IN (141,174);

2.2、不成对比较举例

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);

与上面的成对比较不同，成对比较是需要前面的(column1, column2)和后面的相同才可以。

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

问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资

方法一：

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
          )

方法二：

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、单列子查询表达式

单列子查询表达式是在一行中只返回一列的子查询
Oracle8i 只在下列情况下可以使用, 例如:
- SELECT 语句 (FROM 和 WHERE 子句)
- INSERT 语句中的VALUES列表中
Oracle9i中单列子查询表达式可在下列情况下使用:
- DECODE 和 CASE
- SELECT 中除 GROUP BY 子句以外的所有子句中

4.1、单列子查询应用举例

在 CASE 表达式中使用单列子查询

问题：显示员工的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;

在 ORDER BY 子句中使用单列子查询

问题：查询员工的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);

5、相关子查询

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

重要的时看懂这里的执行顺序

 SELECT column1, column2, ...
 FROM   table1  outer
 WHERE  column1 operator 
			  (SELECT  colum1, column2
                       FROM    table2
                       WHERE   expr1 = outer.expr2);

其中的outer只是表的别名，取其他名称也可以

子查询中使用主查询中的列

5.1 相关子查询举例

问题：查询员工中工资大于本部门平均工资的员工的last_name,
salary和其department_id

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

SELECT e.employee_id, last_name,e.job_id
FROM   employees e 
WHERE  2 <= (SELECT COUNT(*)
             FROM   job_history 
             WHERE  employee_id = e.employee_id);

执行顺序：
1、先获取到主查询中的列
2、将主查询用到的列用于子查询中，注意：这里会取主查询中用到的表里的每一行数据，用于下面的步骤（即3、4）。
3、判断 employees 表中的employee_id与job_history相同，计算相同的相同的数量
4、判断子查询得到的数量是否 >= 2，条件成立的则返回。

6、EXISTS 操作符

EXISTS 操作符检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行:
- 不在子查询中继续查找
- 条件返回 TRUE
如果在子查询中不存在满足条件的行:
- 条件返回 FALSE
- 继续在子查询中查找

6.1、EXISTS 操作符应用举例

问题：查询公司管理者的employee_id,last_name,job_id,department_id信息

SELECT employee_id, last_name, job_id, department_id
FROM   employees outer
WHERE  EXISTS ( SELECT 'X'
                 FROM   employees
                 WHERE  manager_id = 
                        outer.employee_id);

注意查询条件是 manager_id = outer.employee_id。

6.2、NOT EXISTS 操作符应用举例

问题：查询departments表中，不存在于employees表中的部门的department_id和department_name

7、相关更新

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

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

7.1、相关更新应用举例

1、
ALTER TABLE employees
ADD(department_name VARCHAR2(14));


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

8、相关删除

 DELETE FROM table1 alias1
 WHERE  column operator 
	(SELECT expression
 	 FROM   table2 alias2
 	 WHERE  alias1.column = alias2.column);

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

8.1、相关删除应用举例

问题：删除表employees中，其与emp_history表皆有的数据

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

9、WITH 子句

使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中
使用 WITH 子句可以提高查询效率

4、WITH 子句应用举例

问题：查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息

WITH 
dept_costs  AS (
   SELECT  d.department_name, SUM(e.salary) AS dept_total
   FROM    employees e, departments d
   WHERE   e.department_id = d.department_id
   GROUP BY d.department_name),
avg_cost    AS (
   SELECT SUM(dept_total)/COUNT(*) AS dept_avg
   FROM   dept_costs)
SELECT * 
FROM   dept_costs 
WHERE  dept_total >
        (SELECT dept_avg 
         FROM avg_cost)
ORDER BY department_name;

标签：15,查询,department,employee,Oracle,WHERE,id,SELECT
From： https://www.cnblogs.com/huageyiyangdewo/p/18352120