幻灯片 1

Size: px

Start display at page:

Download "幻灯片 1"

泗严
5 years ago
Views:

2 目标完成本课后, 您应当能够 : 写多列子查询在返回空值时描述并解释子查询的行为写一个在 FROM 子句中的子查询 SQL 中使用标量子查询描述能够用相关子查询解决的问题类型写相关子查询用相关子查询更新和删除行使用 EXISTS 和 NOT EXISTS 操作使用 WITH 子句 18-2 Copyright Oracle Corporation, All rights reserved. 课程目标在本课中, 你将学习怎样写多列子查询和在 SELECT 语句中 FROM 子句的子查询你还将学习怎样用标量有相互关系的子查询和 WITH 子句解决问题中国科学院西安网络中心 2005 Oracle SQL 入门 18-2

3 什么是子查询? 子查询是一个嵌入在另一个 SQL 语句的子句中的 SELECT 语句主查询 SELECT... FROM... WHERE... (SELECT... FROM... WHERE...) 子查询 18-3 Copyright Oracle Corporation, All rights reserved. 什么是子查询? 子查询是一个嵌入在另一个称为父语句的 SQL 语句的子句中的 SELECT 语句子查询 ( 内查询 ) 返回一个被父语句使用的值使用一个嵌套的子查询相当于执行两个连续的查询, 并且内查询的结果作为外查询 ( 主查询 ) 的搜索值子查询可以用于下面的用途 : 为 SELECT 语句的 WHERE HAVING 和 START WITH 子句提供条件值定义要 INSERT 或 CREATE TABLE 语句的目的表中的行集定义要包括在 CREATE VIEW 或 CREATE SNAPSHOT 语句中的视图或快照的行集定义一个或多个值指定给存在于 UPDATE 语句中的行定义一个被包含查询操作的表 ( 你可以在 FROM 子句中放置子查询可以在 INSERT UPDATE 和 DELETE 语句中这样做 ) 注 : 对于整个父语句子查询只求值一次中国科学院西安网络中心 2005 Oracle SQL 入门 18-3

4 子查询 SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); 子查询 ( 内嵌查询 ) 在主查询中执行一次子查询的结果被用于主查询 ( 外查询 ) 18-4 Copyright Oracle Corporation, All rights reserved. 子查询使用子查询, 你可以用简单的语句构造出强大的语句从表中用依赖于该表或其它表中的数据条件选择行时, 子查询可能是非常有用的在写基于一个或多个未知条件值 SQL 语句时, 子查询是非常有用的在语法中 : operator 包括一个比较运算符, 例如 > = 或 IN 注 : 比较运算符分为两类 : 单行运算 (> = >= < <> <=) 和多行运算 (IN ANY ALL) 子查询通常被作为一个嵌套的 SELECT 子 -SELECT 或内 SELECT 语句引用内或外查询既可以从同一个表也可以从不同的表取回数据中国科学院西安网络中心 2005 Oracle SQL 入门 18-4

使用子查询 SELECT last_name 10500 WHERE salary > (SELECT salary WHERE employee_id = 149) ; 18-5 Copyright Oracle Corporation, 2001. All rights reserved.

5 使用子查询 SELECT last_name WHERE salary > (SELECT salary WHERE employee_id = 149) ; 18-5 Copyright Oracle Corporation, All rights reserved. 使用子查询在幻灯片的例子中, 内查询返回 employee_id 为 149 的雇员的薪水, 外查询使用内查询的结果显示所有薪水高于这个数的雇员的名字例显示所有薪水低于该公司平均薪水的雇员的名字 SELECT last_name, job_id, salary WHERE salary < (SELECT AVG(salary) ); 中国科学院西安网络中心 2005 Oracle SQL 入门 18-5

6 多列子查询 Main query WHERE (MANAGER_ID, DEPARTMENT_ID) IN Subquery 主查询的每行与一个多行多列子查询的值比较 18-6 Copyright Oracle Corporation, All rights reserved. 多列子查询到目前为止你已经写了内 SELECT 语句仅返回一列并由父 SELECT 语句求表达式的值的单行子查询和多行子查询如果你想要比较两个或多个列, 你必须用逻辑运算写一个复合的 WHERE 子句使用多列子查询, 你可以联合多个 WHERE 条件到一个单个的 WHERE 子句中语法 SELECT column, column,... FROM table WHERE (column, column,...) IN (SELECT column, column,... FROM table WHERE condition); 幻灯片图解了 : 来自主查询的 MANAGER_ID 和 DEPARTMENT 的值与由子查询返回的 MANAGER_ID 和 DEPARTMENT_ID 值比较, 因为被比较的列数多于一列, 该例子就是一个多列子查询中国科学院西安网络中心 2005 Oracle SQL 入门 18-6

7 列比较在一个多列子查询中的列比较能够被 : 成对地比较非成对的比较 18-7 Copyright Oracle Corporation, All rights reserved. 成对比较与非成对比较在一个多列子查询中, 列可以被成对比较, 也可以非成对比较在下一个幻灯片的例子中, 在 WHERE 子句中执行了一个成对比较在 SELECT 语句中的每一个候选行中必须同时有与 EMPLOYEE_ID 为 178 或 174 的雇员的 MANAGER_ID 列和 DEPARTMENT_ID 列相同的列多列子查询也可以是非成对比较的在非成对比较中, 来自父 SELECT 语句的 WHERE 子句中的每一行分别与内 SELECT 语句返回的多个值比较单独的列可以匹配任何由内查询语句返回的值但总体来说, 所有主 SELECT 语句的多个条件必须满足被显示的行后面的页中举例说明了非成对比较中国科学院西安网络中心 2005 Oracle SQL 入门 18-7

8 成对比较子查询显示雇员的细节, 这些雇员被同一个经理管理, 并且, 工作在同一个部门, 但不包括 EMPLOYEE_ID 为 178 或 174 的雇员 SELECT employee_id, manager_id, department_id WHERE (manager_id, department_id) IN (SELECT manager_id, department_id WHERE employee_id IN (178,174)) AND employee_id NOT IN (178,174); 18-8 Copyright Oracle Corporation, All rights reserved. 成对比较子查询幻灯片中的例子是一个多列子查询, 因为子查询返回多于一个列该语句用 EMPLOYEE_ID 为 178 或 174 的那些雇员的 MANAGER_ID 列和 DEPARTMENT_ID 列的值与 EMPLOYEES 表中每一行的 MANAGER_ID 列和 DEPARTMENT_ID 列的值进行比较首先, 子查询取回那些 EMPLOYEE_ID 为 178 或 174 的雇员的 MANAGER_ID 和 DEPARTMENT_ID 值, 用 EMPLOYEES 表中每一行的 MANAGER_ID 列和 DEPARTMENT_ID 列的值与这些取回的值进行比较, 如果值匹配, 该行被显示在输出中, 那些 EMPLOYEE_ID 为 178 或 174 的雇员的记录将不会被显示, 幻灯片的下面是查询的输出中国科学院西安网络中心 2005 Oracle SQL 入门 18-8

非成对比较子查询显示经理与雇员 174 或 141 相同, 并且, 工作在同一个部门, 不包括 EMPLOYEE_ID 为 174 或 141 的雇员的详细信息 SELECT employee_id, manager_id, department_id WHERE manager_id IN (SELECT manager_id WHERE employee_id IN (174,141))

9 非成对比较子查询显示经理与雇员 174 或 141 相同, 并且, 工作在同一个部门, 不包括 EMPLOYEE_ID 为 174 或 141 的雇员的详细信息 SELECT employee_id, manager_id, department_id WHERE manager_id IN (SELECT manager_id WHERE employee_id IN (174,141)) AND department_id IN (SELECT department_id WHERE employee_id IN (174,141)) AND employee_id NOT IN(174,141); 18-9 Copyright Oracle Corporation, All rights reserved. 非成对比较子查询该例显示列的非成对比较它显示任何其 manager ID 与那些 employee ID 是 174 或 141 的雇员的 manager ID 相匹配, 并且, department ID 与那些 employee ID 是 174 或 141 的雇员的 department ID 相匹配的雇员的 EMPLOYEE_ID MANAGER_ID 和 DEPARTMENT_ID 首先, 子查询取回那些 EMPLOYEE_ID 为 174 或 174 的雇员的 MANAGER_ID 值, 类似地, 第二个子查询取回那些 EMPLOYEE_ID 为 174 或 174 的雇员的 DEPARTMENT_ID 值, 用 EMPLOYEES 表中每一行的 MANAGER_ID 列和 DEPARTMENT_ID 列的值与这些取回的值进行比较, 如果 EMPLOYEES 表中行的 MANAGER_ID 列与内查询返回的任一 MANAGER_ID 值相匹配, 并且, 如果 EMPLOYEES 表中行的 DEPARTMENT_ID 列与第二个子查询返回的任一 DEPARTMENT_ID 值相匹配, 记录被显示在输出中, 那些 EMPLOYEE_ID 为 178 或 174 的雇员的记录将不会被显示, 幻灯片的下面是查询的输出中国科学院西安网络中心 2005 Oracle SQL 入门 18-9

在 FROM 子句中使用子查询 SELECT a.last_name, a.salary, a.department_id, b.salavg a, (SELECT department_id, AVG(salary) salavg GROUP BY department_id) b WHERE a.department_id = b.department_id AND a.salary > b.

10 在 FROM 子句中使用子查询 SELECT a.last_name, a.salary, a.department_id, b.salavg a, (SELECT department_id, AVG(salary) salavg GROUP BY department_id) b WHERE a.department_id = b.department_id AND a.salary > b.salavg; Copyright Oracle Corporation, All rights reserved. 在 FROM 子句中使用子查询你可以在一个 SELECT 语句的 FROM 子句中使用子查询, 这非常类似于视图的使用一个在 SELECT 语句的 FROM 子句的子查询也被称为内联 (inline) 视图一个在 SELECT 语句的 FROM 子句的子查询为特殊的 SELECT 语句定义一个数据源, 并且仅是 SELECT 语句幻灯片中的例子显示所有那些薪水高于他所在的部门平均薪水的雇员的名字薪水部门号和部门平均薪水 FROM 子句中的子查询命名为别名 b, 并且外查询引用该表别名的 SALAVG 列教师注释该例子示范了一个有用的技术, 即在相同的输出中组合明细行值和统计数据中国科学院西安网络中心 2005 Oracle SQL 入门 18-10

11 标量子查询表达式一个标量子查询表达式是一个从一行中返回确切的一个列值的子查询在 Oracle8i 中, 标量子查询仅在一些有限情况的情况下被支持, 例如 : SELECT 语句 (FROM 和 WHERE 子句 ) 在一个 INSERT 语句中的列表值在 Oracle9i 中, 标量子查询能够被用于 : DECODE and CASE 的条件和表达式部分除 GROUP BY 以外所有的 SELECT 子句 Copyright Oracle Corporation, All rights reserved. SQL 中的标量子查询子查询从一行中返回确切的一个列值也称为标量子查询多列子查询比较两个或更多的列, 使用复合的 WHERE 子句和逻辑运算, 不能算作标量子查询标量子查询表达式的值是子查询的 select 列表项的值如果子查询返回 0 行, 标量子查询表达式的值为 NULL 如果子查询的返回多于一行,Oracle 服务器返回一个错误 Oracle 服务器总是支持在 SELECT 语句中的标量子查询的用法标量子查询的用法在 Oracle9i 中得到了增强你现在可以在下面的情况中使用标量子查询 : DECODE 和 CASE 条件和表达式部分除了 GROUP BY 以外的所有 SELECT 的字句在 UPDATE 语句的 SET 子句和 WHERE 子句中运算符的左边在下面的位置中标量子查询不是正确的表达式 : 作为列的默认值和用于聚合的哈希表达式在 DML 语句的 RETURNING 子句中作为基于函数的索引的依据在 GROUP BY 子句 CHECK 约束 WHEN 条件中 HAVING 子句在 In START WITH 和 CONNECT BY 子句中在与查询无关的语句中, 例如 CREATE PROFILE 中国科学院西安网络中心 2005 Oracle SQL 入门 18-11

12 标量子查询 : 例子在 CASE 表达式中的标量子查询 SELECT employee_id, last_name, (CASE 20 WHEN department_id = (SELECT department_id FROM departments WHERE location_id = 1800) FROM THEN 'Canada' ELSE 'USA' END) location employees; 在 ORDER BY 子查询中的标量子查询 SELECT employee_id, last_name e ORDER BY (SELECT department_name FROM departments d WHERE e.department_id = d.department_id); Copyright Oracle Corporation, All rights reserved. 标量子查询 : 例子在幻灯片的第一个例子中演示了标量子查询可以被用在 CASE 表达式中内查询返回值 20, 这是 location ID 为 1800 的部门的 department ID 在外查询中的 CASE 表达式使用内查询的结果显示 employee ID last names 和 Canada 或 USA 值, 该值是 Canada 还是 USA, 取决于内查询取回记录的 department ID 是否是 20 前面例子的结果如下 : 中国科学院西安网络中心 2005 Oracle SQL 入门 18-12

13 标量子查询 : 例子 ( 续 ) 在幻灯片中的第二个例子示范了标量子查询可以用在 ORDER BY 子查询中例子用 DEPARTMENTS 表中的 DEPARTMENT_ID 匹配 EMPLOYEES 表中的 DEPARTMENT_ID, 基于 DEPARTMENT_NAME 排序输出该比较在 ORDER BY 子句中的标量子查询中进行第二个例子的结果如下 : 第二个例子使用一个相关子查询, 该子查询从在父查询中涉及的表中引用一个列相关子查询在本课的后面说明中国科学院西安网络中心 2005 Oracle SQL 入门 18-13

14 相关子查询相关子查询被用于 row-by-row 处理对外查询的每一行, 每个子查询被执行一次 GET 取来自外查询的候选行 EXECUTE 用候选行值执行内查询 USE 用内查询的值确认或取消候选行 Copyright Oracle Corporation, All rights reserved. 相关子查询当子查询从一个在父语句涉及的表中引用一个列时 Oracle 服务器执行相关子查询对于由父语句处理的每一行相关子查询都执行一次父语句可以是 SELECT UPDATE 或 DELETE 语句嵌套子查询与相关子查询在通常的嵌套子查询中, 内 SELECT 查询首先执行并且只执行一次, 返回值被主查询使用但相关子查询对由外查询考虑的每个候选行都执行一次, 换句话说, 内查询由外查询驱动嵌套子查询的执行内查询首先执行并且查找值外查询用内查询的值执行一次相关子查询的执行取得候选行 ( 由外查询取回 ) 用候选行的值执行内查询用来自内查询的值确认或取消候选行重复直到无剩余的候选行中国科学院西安网络中心 2005 Oracle SQL 入门 18-14

15 相关子查询 SELECT column1, column2,... FROM table1 outer WHERE column1 operator (SELECT colum1, column2 FROM table2 WHERE expr1 = outer.expr2); 子查询参考在父查询中的表的一个列 Copyright Oracle Corporation, All rights reserved. 相关子查询 ( 续 ) 相关子查询是一种读表中每一行并且依靠相关数据比较每行的值的方法当一个子查询必须对每个由主查询考虑的候选行返回一个不同的结果或结果集时, 可以使用相关子查询换句话说, 你用一个相关子查询来回答一个其答案依据在父语句中处理的每一行的值的多部分问题当子查询从父查询的一个表中引用一个列时 Oracle 服务器执行相关子查询注 : 在相关子查询中你可以使用 ANY 和 ALL 运算中国科学院西安网络中心 2005 Oracle SQL 入门 18-15

16 使用相关子查询找出所有的雇员, 他们挣的薪水高于该部门的平均薪水 SELECT last_name, salary, department_id outer WHERE salary > (SELECT AVG(salary) WHERE department_id = outer.department_id) ; 外查询中的行每被处理一次, 内查询就求值一次 Copyright Oracle Corporation, All rights reserved. 使用相关子查询幻灯片中的例子确定那些雇员挣比他们所在部门的平均薪水高的薪水在这种情况下, 相关子查询计算每个部门的平均薪水因为外查询和内查询的 FROM 子句中都使用 EMPLOYEES 表, 为了明确, 在外 SELECT 语句中给 EMPLOYEES 表以的别名, 在内查询中用 outer.department_id 引用外查询的 EMPLOYEES 表别名不仅使得整个 SELECT 更易读, 而且如果没有别名查询将不能正常工作, 因为内语句将不能从外表的列中区分内表的列教师注释别名的使用是语法的要求在这里使用别名 OUTER 是强制的, 而不象在另外一些情况下使用别名是为了增加 SQL 语句的清晰性和易读性中国科学院西安网络中心 2005 Oracle SQL 入门 18-16

17 使用相关子查询显示雇员的详细信息, 这些雇员至少变换过两次工作 SELECT e.employee_id, last_name,e.job_id e WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id); Copyright Oracle Corporation, All rights reserved. 使用相关子查询幻灯片中的例子显示那些变换过至少两次工作的雇员的详细情况 Oracle 服务器如下计算相关子查询 : 1. 从在外查询指定的表中选择一行这将是当前的候选行 2. 存储在子查询中引用的来自该候选行的列的值 ( 在幻灯片的例子中, 在子查询中引用的列是 E.EMPLOYEE_ID) 3. 用它的条件引用来自外查询候选行的值执行子查询 ( 在幻灯片的例子中, 组函数 COUNT(*) 基于在第 2 步中获得的 E.EMPLOYEE_ID 列值进行计算 ) 4. 计算基于在第 3 步中执行的子查询的结果的外查询的 WHERE 子句这将确定是否候选行被外查询选择 ( 在该例子中, 一个雇员变换工作的次数, 该次数由子查询计算, 在外查询的 WHERE 子句中与 2 进行比较如果条件满足, 雇员的记录被显示 ) 5. 对表的下一个候选行重复这个过程, 并且继续这个过程直到表中所有的行都被处理依据在子查询中来自外查询的一个元素确定相关性在本例中, 相关性由语句 EMPLOYEE_ID = E.EMPLOYEE_ID 确定, 在其中, 用外查询中表的 EMPLOYEE_ID 列与子查询中表的 EMPLOYEE_ID 列进行比较中国科学院西安网络中心 2005 Oracle SQL 入门 18-17

18 EXISTS 操作 EXISTS 操作对在子查询的结果集中存在的行进行检验 : 如果一个子查询行值被找到 : 在内查询中的搜索不再继续条件被标记为 TRUE 如果一个子查询行值未找到 : 条件被标记为 FALSE 在内查询中的搜索继续 Copyright Oracle Corporation, All rights reserved. EXISTS 操作对嵌套的 SELECT 语句, 所有的逻辑操作都是有效的另外, 你还可以使用 EXISTS 操作该操作经常用于相关子查询来测试是否一个由外查询取回的值存在于由内查询取回的值的结果集中如果自粗嗄逊子查询返回至少一行, 该操作返回 TRUE 如果值不存在, 该操作返回 FALSE 因此,NOT EXISTS 测试是否一个由外查询取回的值不是内查询取回的值的结果集的一部分中国科学院西安网络中心 2005 Oracle SQL 入门 18-18

查找至少有一个雇员的经理使用 EXISTS 操作 SELECT employee_id, last_name, job_id, department_id outer WHERE EXISTS ( SELECT 'X' WHERE manager_id = outer.employee_id); 18-19 Copyright Oracle Corporation, 2001.

19 查找至少有一个雇员的经理使用 EXISTS 操作 SELECT employee_id, last_name, job_id, department_id outer WHERE EXISTS ( SELECT 'X' WHERE manager_id = outer.employee_id); Copyright Oracle Corporation, All rights reserved. 使用 EXISTS 操作使用下面的条件, 当至少找到一个经理号和雇员号相匹配的记录时,EXISTS 操作确保在内查询中的搜索不再继续 : WHERE manager_id = outer.employee_id. 注意内 SELECT 查询不需要返回一个特定的值, 所以可以选择一个常数从执行性能来看, 选择一个常数比一个列更快一些注 : 在内查询的 SELECT 子句中的 EMPLOYEE_ID 会引起对该列的表扫描用文字 X, 或者任何一个常数代替它, 可以改善性能这比使用 IN 操作更为有效 IN 构造可以用于代替 EXISTS 操作, 如下面的例子所示 : SELECT employee_id,last_name,job_id,department_id WHERE employee_id IN (SELECT manager_id WHERE manager_id IS NOT NULL); 中国科学院西安网络中心 2005 Oracle SQL 入门 18-19

20 使用 NOT EXISTS 操作找出所有的没有任何雇员的部门 SELECT department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT 'X' WHERE department_id = d.department_id); Copyright Oracle Corporation, All rights reserved. 使用 NOT EXISTS 操作可选的解决办法 NOT IN 构造可以用于代替 NOT EXISTS 操作, 如下面的例子所示 : SELECT department_id, department_name FROM departments WHERE department_id NOT IN (SELECT department_id ); 但是, 如果集合任何一个成员是 NULL 值,NOT IN 求得值为 FALSE 因此, 即使在部门表中有满足 WHERE 条件的行, 你的查询也不会返回任何行中国科学院西安网络中心 2005 Oracle SQL 入门 18-20

21 相关 UPDATE UPDATE table1 alias1 SET column = (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column); 用一个相关子查询来更新在一个表中的行, 该表中的行基于另一个表中的行 Copyright Oracle Corporation, All rights reserved. 相关 UPDATE 在 UPDATE 语句的情况下, 你可以用相关子来更新一个表中的行, 该表中的行基于另一个表中的行中国科学院西安网络中心 2005 Oracle SQL 入门 18-21

22 相关 UPDATE 用一个附加的列来存储部门名称, 反向规格化 EMPLOYEES 表用相关更新填充表 ALTER TABLE employees ADD(department_name VARCHAR2(14)); UPDATE employees e SET department_name = (SELECT department_name FROM departments d WHERE e.department_id = d.department_id); Copyright Oracle Corporation, All rights reserved. 相关 UPDATE ( 续 ) 幻灯片中的例子演示了 EMPLOYEES 表被添加一行以存储部门名, 然后用相关更新组装表下面是相关更新的另一个例子问题语句用一个相关子查询来更新在 EMPLOYEES 表中的行这些行基于 REWARDS 表中的行 : UPDATE employees SET salary = (SELECT employees.salary + rewards.pay_raise FROM rewards WHERE employee_id = employees.employee_id AND payraise_date = (SELECT MAX(payraise_date) FROM rewards WHERE employee_id = employees.employee_id)) WHERE employees.employee_id IN (SELECT employee_id FROM rewards); 教师注释为了演示注释中的代码例子, 你首先必须运行脚本文件 \labs\cre_reward.sql, 该文件创建 REWARDS 表并且插入记录到表中如果你演示幻灯片中的脚本, 不要忘记回滚事务这是非常重要的, 因为如果不这样做, 在练习中的输出将不匹配中国科学院西安网络中心 2005 Oracle SQL 入门 18-22

23 相关 UPDATE ( 续 ) 该例子使用 REWARDS 表 REWARDS 有这样一些列 EMPLOYEE_ID PAY_RAISE 和 PAYRAISE_DATE 每当一个雇员涨薪水时, 一个有该雇员的 employee ID 增加薪水的数和薪水增加收据日期的详细信息的记录被插入到 REWARDS 表中对一个雇员来说,REWARDS 表可以包含多条记录 PAYRAISE _DATE 列用于标识一个雇员最近收到的增加的薪水在例子, 在 EMPLOYEES 表中的 SALARY 列被更新为反映该雇员最近收到的增长的薪水这个操作用来自 REWARDS 表的相应的薪水增长添加当前雇员的薪水来完成中国科学院西安网络中心 2005 Oracle SQL 入门 18-23

24 相关 DELETE DELETE FROM table1 alias1 WHERE column operator (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column); 用一个相关子查询删除表中的行, 该表基于另一个表中的行 Copyright Oracle Corporation, All rights reserved. 相关 DELETE 在 DELETE 语句的情况下, 你可以使用相关子查询来只删除那些已经存在于另一张表中的行如果你决定只维护 JOB_HISTORY 表中最后 4 条 job 历史记录, 那么当一个雇员第 5 次改变工作时, 对于该雇员, 你需要依据 JOB_HISTORY 表, 对于 MIN(START_DATE) 查找并删除最老的 JOB_HISTORY 行下面的代码举例说明前面的操作怎样用一个相关删除来完成 : DELETE FROM job_history JH WHERE employee_id = (SELECT employee_id E WHERE JH.employee_id = E.employee_id AND start_date = (SELECT MIN(start_date) FROM job_history JH WHERE JH.employee_id = E.employee_id) AND 5 > (SELECT COUNT(*) FROM job_history JH WHERE JH.employee_id = E.employee_id GROUP BY employee_id HAVING COUNT(*) >= 4)); 中国科学院西安网络中心 2005 Oracle SQL 入门 18-24

25 相关 DELETE 用一个相关子查询删除哪些在 EMPLOYEES 表和 EMP_HISTORY 表中的 employee_id 列值相同的行 DELETE E WHERE employee_id = (SELECT employee_id FROM emp_history WHERE employee_id = E.employee_id); Copyright Oracle Corporation, All rights reserved. 相关 DELETE ( 续 ) 例在这个例子中要使用下面两个表 : EMPLOYEES 表, 给出所有当前雇员的详细信息 EMP_HISTORY 表, 给出以前雇员的详细信息 EMP_HISTORY 包含关于以前雇员的数据, 所以如果相同雇员的记录同时存在于 EMPLOYEES 和 EMP_HISTORY 表中, 它将是错误的你可以用显示在幻灯片中的相关子查询删除这样的错误记录教师注释为了演示幻灯片中的代码例子, 你必须首先运行脚本文件 \labs\cre_emphistory.sql 创建 EMP_HISTORY 表, 并且插入记录到表中中国科学院西安网络中心 2005 Oracle SQL 入门 18-25

26 WITH 子句当一个查询块在一个复杂的查询中出现多次时, 使用 WITH 子句, 能够用在 SELECT 语句中使用相同查询块 WITH 子句取回查询块的结果, 并且将它存在用户的临时表空间中 WITH 子句可以改善性能 Copyright Oracle Corporation, All rights reserved. WITH 子句用 WITH 子句, 你可以在一个查询中使用一个定义的查询块 WITH 子句 ( 通常称为 subquery_factoring_clause) 使你能够在一个复杂查询中的 SELECT 语句中多次重用相同的查询块当一个查询多次涉及相同的查询块并且有连接何聚合时, 这是特别有用的使用 WITH 子句, 当计算查询块的代价较高并且它在一个复杂的查询中出现多次时你可以重用相同的查询使用 WITH 子句,Oracle 服务器取回一个查询的结果并且将它存储在用户的临时表空间中这可以增进性能 WITH 子句的优点使得查询易于阅读即使一个子句在查询中多次出现, 只进行一次计算, 因此可以提高性能中国科学院西安网络中心 2005 Oracle SQL 入门 18-26

27 WITH 子句 : 例子用 WITH 子句, 写一个查询来显示部门名称和该部门的合计薪水, 哪些人的合计薪水高于各部门的平均薪水 Copyright Oracle Corporation, All rights reserved. WITH 子句 : 例在幻灯片中的问题将需要下面的中间计算 : 1. 计算每个部门的合计工资, 并且用 WITH 子句存储结果 2. 计算所有部门的平均薪水, 并且用 WITH 子句存储结果 3. 用在第二步中计算出的平均薪水比较在第一步中计算出的合计薪水如果某个特定部门的合计薪水高于所有部门的平均薪水, 显示该部门的名字与和合计薪水上述问题的解答在下一页中中国科学院西安网络中心 2005 Oracle SQL 入门 18-27

28 WITH 子句 : 例子 WITH dept_costs AS ( SELECT d.department_name, SUM(e.salary) AS dept_total 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; Copyright Oracle Corporation, All rights reserved. WITH 子句 : 例 ( 续 ) 幻灯片中的 SQL 代码是一个可以改善性能并且使用 WITH 子句更简单地写 SQL 的例子该查询创建名字为 DEPT_COSTS 和 AVG_COST 的查询, 然后再主查询体重使用它们在内部,WITH 子句分解为一个内嵌的视图或一个临时表, 优化器会依据成本或临时存储 WITH 子句结果的益处选择适当的解决方案注 : 在一个 SELECT 语句的 FROM 子查询中也会调用一个内嵌的视图在幻灯片中由 SQL 代码产生的输出如下 : DEPARTMENT_NAME DEPT_TOTAL Executive Sales WITH 子句用法注释只能同 SELECT 语句一起使用一个查询名字对所有在其后定义的 WITH 元素查询块 ( 包括它们的子查询块 ) 和主查询块本身 ( 包括它的子查询块 ) 是可见的当查询名字和一个已存在的表相同时, 解析器从内向外查找, 查询块名优先于表名 WITH 子句可以拥有多个查询, 每个查询之间用逗号隔开中国科学院西安网络中心 2005 Oracle SQL 入门 18-28

29 小结在本课中, 您应该已经学会如何 : 返回多于一列的多列子查询多列比较可以成对或非成对地进行一个多列子查询也能够被用于一个 SELECT 语句的 FROM 子句标量子查询在 Oracle9i 中得到了增强 Copyright Oracle Corporation, All rights reserved. 小结你可以用多列子查询来组合多 WHERE 条件为一个单个的 WHERE 子句在一个多列子查询中的列比较可以是成对比较或是非成对比较你可以用一个子查询来定义一个由包含查询操作的表 Oracle 9i 增强了标量子查询使用, 标量子查询现在可以用在下面的情况中 : DECODE 和 CASE 的条件和表达式部分除 GROUP BY 以外的所有 SELECT 子句 UPDATE 语句的 SET 子句和 WHERE 子句中国科学院西安网络中心 2005 Oracle SQL 入门 18-29

30 小结无论何时一个子查询必须对每一个侯选行返回不同的结果, 这时, 相关子查询是有用的 EXISTS 操作是测试值的存在性的布尔操作相关子查询能够用于 SELECT, UPDATE, and DELETE 语句在 SELECT 语句中你能够通过 WITH 子句多次使用相同的查询块 Copyright Oracle Corporation, All rights reserved. 小结 ( 续 ) 当子查询从一个父语句涉及的表中引用一个列时,Oracle 服务器执行一个相关子查询对于父语句处理的每一行相关子查询都会进行一次求值父语句可以是 SELECT UPDATE 或 DELETE 语句使用 WITH 子句, 当查询块的重新求值代价昂贵并且它在一个复合查询中多次出现时, 你可以重用相同的查询中国科学院西安网络中心 2005 Oracle SQL 入门 18-30

31 练习 18 概览本章练习包括下面的主题 : 创建多列子查询写相互关联的子查询使用 EXISTS 操作使用标量子查询使用 WITH 子句 Copyright Oracle Corporation, All rights reserved. 练习 18 概览在本练习中, 你将学习写多列子查询相关子查询和标量子查询你还将练习用 WITH 子句解决问题教师注释在学生开始练习之前你可能应该带领他们复习一下 ALL 和 ANY 操作, 对于下面这些问题这是必须的 ALL: 比较一个值与一个列表中的或由一个查询返回的每一个值 ALL 前面必须用 =,!=, >, <, <=, >= 如果查询没有返回的行其值为 TRUE SELECT * WHERE salary > = ALL ( 1400, 3000); ANY: 比较一个值与一个列表中的或由一个查询返回的每一个值 ANY 前面必须用 =,!=, >, <, <=, >= 如果查询没有返回的行其值为 FALSE SELECT * WHERE salary = ANY (SELECT salary WHERE department_id = 30); 中国科学院西安网络中心 2005 Oracle SQL 入门 18-31

32 练习写一个子查询显示任何一个其部门号和薪水都与任何挣佣金的雇员的部门号和薪水相匹配的雇员的名字部门号和薪水 ELECT last_name, department_id, salary WHERE (salary, department_id) IN (SELECT salary, department_id WHERE commission_pct IS NOT NULL); 2. 显示任何其薪水和佣金与任何位于 location ID 为 1700 的雇员薪水和佣金相匹配的雇员的名字部门号和薪水 SELECT last_name, department_name, salary e, departments d WHERE e.department_id = d.department_id AND (salary, NVL(commission_pct,0)) IN (SELECT salary, NVL(commission_pct,0) e, departments d WHERE e.department_id = d.department_id AND d.location_id = 1700); 3. 创建一个查询来显示所有其薪水和佣金与 Kochhar 相同的雇员的名字受雇日期和薪水注 : 在结果集中不显示 Kochhar 中国科学院西安网络中心 2005 Oracle SQL 入门 18-32

33 SELECT last_name, hire_date, salary WHERE (salary, NVL(commission_pct,0)) IN (SELECT salary, NVL(commission_pct,0) WHERE last_name = 'Kochhar') AND last_name!= 'Kochhar'; 4. 创建一个查询来显示那些所挣薪水高于所有销售经理 (JOB_ID = SA_MAN ) 的雇员的名字受雇日期和薪水依据薪水从最高到最低排序结果集 SELECT last_name, job_id, salary WHERE salary > ALL (SELECT salary WHERE job_id = 'SA_MAN') ORDER BY salary DESC; 5. 显示那些住在城市名字以 T 开头的城市的雇员的 ID 名字和部门号 SELECT employee_id, last_name, department_id WHERE department_id IN (SELECT department_id FROM departments WHERE location_id IN (SELECT location_id FROM locations WHERE city LIKE 'T%')); 中国科学院西安网络中心 2005 Oracle SQL 入门 18-33

34 6. 写一个查询来查找所有其薪水多于他所在部门的平均薪水的雇员显示名字部门号和部门的平均薪水按平均薪水排序象输出例子显示的那样对于由查询返回的列使用列别名 SELECT e.last_name ename, e.salary salary, e.department_id deptno, AVG(a.salary) dept_avg e, employees a WHERE e.department_id = a.department_id AND e.salary > (SELECT AVG(salary) WHERE department_id = e.department_id ) GROUP BY e.last_name, e.salary, e.department_id ORDER BY AVG(a.salary); 7. 查找所有不是管理人员的雇员 a. 先用 NOT EXISTS 操作来做 SELECT outer.last_name 中国科学院西安网络中心 2005 Oracle SQL 入门 18-34

35 outer WHERE NOT EXISTS (SELECT 'X' inner WHERE inner.manager_id = outer.employee_id); b. 能用 NOT IN 操作来做吗? 怎样做? 或为什么不能做? SELECT outer.last_name outer WHERE outer.employee_id NOT IN (SELECT inner.manager_id inner); 这个可选的解答不是一个好方法因为子查询会检索到 NULL 值, 因此整个查询无返回行原因是所有与空值比较的条件其结果都为空只要空值可能是值集合的一部分, 就不能用 NOT IN 代替 NOT EXISTS 8. 写一个查询显示所挣薪水低于他们所在的部门的雇员的名字 SELECT last_name outer WHERE outer.salary < (SELECT AVG(inner.salary) inner WHERE inner.department_id = outer.department_id); 9. 写一个查询显示那些在其所在的部门中有一个或多个来的更晚但薪水更高的同事的雇员的名字中国科学院西安网络中心 2005 Oracle SQL 入门 18-35

36 SELECT last_name outer WHERE EXISTS (SELECT 'X' inner WHERE inner.department_id = outer.department_id AND inner.hire_date > outer.hire_date AND inner.salary > outer.salary); 10. 写一个查询显示所有雇员的 ID 名字和部门名字注 : 在 SELECT 语句中用一个标量子查询来取回部门名 SELECT employee_id, last_name, 中国科学院西安网络中心 2005 Oracle SQL 入门 18-36

37 (SELECT department_name FROM departments d WHERE e.department_id = d.department_id ) department e ORDER BY department; 11. 写一个查询显示那些其合计薪水成本超出全公司合计薪水成本八分之一 (1/8) 的部门的部门名使用 WITH 子句写这个查询命名该查询为 SUMMARY DEPARTMENT_NAME DEPT_TOTAL Executive Sales WITH summary AS ( SELECT department_name, SUM(salary) AS dept_total, departments WHERE employees.department_id = departments.department_id GROUP BY department_name) SELECT department_name, dept_total FROM summary WHERE dept_total > ( SELECT SUM(dept_total) * 1/8 FROM summary) ORDER BY dept_total DESC; 中国科学院西安网络中心 2005 Oracle SQL 入门 18-37