|
课程目标： - 多列子查询
- 标量子查询
- 关联子查询
- exists运算符
- with语句
1 、多列子查询 语法： 有2种多列比较： 例子：成对比较子查询，查找与姓为John的人员在同一部门且属于同一上司的员工信息 SQL> select employee_id,manager_id,department_id from employees where first_name='John'; EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID ----------- ---------- ------------- 110 108 100 145 100 80 139 123 50 SQL> select employee_id,manager_id,department_id from employees 2 where (manager_id,department_id) in( 3 select manager_id,department_id from employees where first_name='John') 4 and first_name<>'John'; EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID ----------- ---------- ------------- 137 123 50 15 rows selected. 例子：非成对比较子查询，查询与first_name为John的人员同一个部门同事以及与first_name为John的人员同一个上司的同事 SQL> select employee_id,manager_id,department_id from employees 2 where manager_id in(select manager_id from employees where first_name='John') 3 and department_id in(select department_id from employees where first_name='John') 4 and first_name<>'John'; EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID ----------- ---------- ------------- 120 100 50 20 rows selected. 2、标量子查询表达式（scalar subquery） 只返回一个记录的子查询。 scalar subquery用于如下情况： - decode和case的条件和表达式部分
- 没有group by的select的语句
- update语句的set和where部分
例子：标量子查询，当部门编号等于位置编号为1800的部门编号，则显示位置为Canada，否则显示USA SQL> select employee_id,last_name, 2 (case when department_id=(select department_id from departments where location_id=1800) 3 then 'Canada' else 'USA' end) location 4 from employees; EMPLOYEE_ID LAST_NAME LOCATI ----------- ------------------------- ------ 198 OConnell USA 3、关联子查询 在关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。另外，在关联子查询中信息流是双向的。外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策。 语法： 处理过程如下： 1）外查询取出一行 2）从取出行中存储子查询需要的字段 3）根据存储的字段执行子查询 4）在外查询where语句中对子查询执行结果进行确认 5）重复执行以上过程 例子：查询比部门平均薪水高的人员信息 SQL> select last_name,salary,department_id from employees; SQL> select department_id,avg(salary) from employees group by department_id; SQL> select last_name,salary,department_id from employees outer 2 where salary>(select avg(salary) from employees where department_id=outer.department_id); LAST_NAME SALARY DEPARTMENT_ID ------------------------- ---------- ------------- Fay 6000 10 例子：查询职位变动过至少2次的人 SQL> select e.employee_id,last_name,e.job_id from employees e 2 where 2<=(select count(*) from job_history where employee_id=e.employee_id); EMPLOYEE_ID LAST_NAME JOB_ID ----------- ------------------------- ---------- 200 Whalen AD_ASST 4、exists运算符 比较子查询的结果是否有记录的存在，如果有记录，则子查询返回true且结束搜索，如果没有记录，则子查询返回false且继续搜索。 例子：查询至少有一个下属的领导信息 SQL> select employee_id,last_name,job_id,department_id from employees outer 2 where exists(select 'X' from employees where manager_id=outer.employee_id); EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID ----------- ------------------------- ---------- ------------- 201 Hartstein MK_MAN 20 其中'X'是一个占位符，没有任何意义，使用数字也可以哦。 也可以使用in SQL> select employee_id,last_name,job_id from employees outer 2 where employee_id in(select manager_id from employees where manager_id=outer.employee_id); EMPLOYEE_ID LAST_NAME JOB_ID ----------- ------------------------- ---------- 201 Hartstein MK_MAN 例子：查询没有人员的部门 SQL> select department_id,department_name from departments d 2 where not exists(select 1 from employees where department_id=d.department_id); DEPARTMENT_ID DEPARTMENT_NAME ------------- ------------------------------ 120 Treasury SQL> select department_id,department_name from departments outer 2 where department_id not in (select department_id from employees where department_id is not null); DEPARTMENT_ID DEPARTMENT_NAME ------------- ------------------------------ 120 Treasury SQL> select department_id,department_name from departments outer 2 where department_id not in(select department_id from employees where department_id=outer.department_id); DEPARTMENT_ID DEPARTMENT_NAME ------------- ------------------------------ 120 Treasury 5、关联更新 使用关联子查询用一张表的数据对另一张表进行更新 语法： 例子：使用部门表的部门名字更新人员表的部门名字 SQL> create table empl6 as select * from employees; Table created. SQL> alter table empl6 add(department_name varchar2(25)); Table altered. SQL> update empl6 e 2 set department_name=(select department_name from departments where department_id=e.department_id); 108 rows updated. 更新empl6表，将人员编号大于180的人员的部门名称修改为abc，部门编号修改为500 SQL> update empl6 set department_name='abc',department_id=500 where employee_id>180; 27 rows updated. SQL> commit; Commit complete. 再次进行关联更新 SQL> update empl6 e 2 set department_name=(select department_name from departments where department_id=e.department_id); 108 rows updated. SQL> select department_name,department_id from empl6 where employee_id>180; DEPARTMENT_NAME DEPARTMENT_ID ------------------------- ------------- 500 500 500 可以看到这儿部门名称被更新为null了，这儿要注意，如果子查询里面的表没有对应的记录，那么更新的记录会被置为null，而不是保持不变。那么如果要使这些记录保持不变，可以使用where语句加上条件只更新在departments表里面有的部门名称。 先回滚 SQL> rollback; Rollback complete. 再来进行关联更新 SQL> update empl6 e 2 set department_name=(select department_name from departments where department_id=e.department_id) 3 where exists(select department_name from departments where department_id=e.department_id); 80 rows updated. 查看确认 SQL> select department_name,department_id from empl6 where employee_id>180; DEPARTMENT_NAME DEPARTMENT_ID ------------------------- ------------- abc 500 6、关联删除 使用关联子查询用一张表的数据对另一张表进行删除 语法： 例子：删除表empl6里面更换过工作的人员 SQL> delete from empl6 e 2 where employee_id in (select employee_id from job_history where employee_id=e.employee_id); 8 rows deleted. SQL> select distinct employee_id from job_history; EMPLOYEE_ID ----------- 101 8 rows selected. 7、with语句 - 存储在用户的临时表空间
- 可以提高性能
- 只能用于select statements
- 可以包含多个查询，用逗号分隔
- 先定义，后使用，对其后面的的查询块及主查询可见
例子：使用with语句，查询每个部门的名称及其薪水总计，同时这些部门的薪水总计大于公司所有部门的平均薪水 使用with语句定义每个部门的薪水总计及所有部门的平均薪水 SQL> with 2 dept_costs as( 3 select d.department_name,sum(e.salary) as dept_total 4 from employees e join departments d on e.department_id=d.department_id 5 group by d.department_name), 6 avg_cost as( 7 select sum(dept_total)/count(*) as dept_avg from dept_costs) 8 select * from dept_costs where dept_total>(select dept_avg from avg_cost) order by department_name; DEPARTMENT_NAME DEPT_TOTAL ------------------------------ ---------- Sales 304500 Shipping 206400 查看他的执行计划，创建了一个临时表，提高了性能 SQL> explain plan for 2 with 3 dept_costs as( 4 select d.department_name,sum(e.salary) as dept_total 5 from employees e join departments d on e.department_id=d.department_id 6 group by d.department_name), 7 avg_cost as( 8 select sum(dept_total)/count(*) as dept_avg from dept_costs) 9 select * from dept_costs where dept_total>(select dept_avg from avg_cost) order by department_name; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3967601111 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 27 | 810 | 12 (25)| 00:00:01 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6610_25A4FE | | | | | | 3 | HASH GROUP BY | | 27 | 621 | 7 (29)| 00:00:01 | | 4 | MERGE JOIN | | 107 | 2461 | 6 (17)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ | 6 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 | |* 7 | SORT JOIN | | 108 | 756 | 4 (25)| 00:00:01 | | 8 | TABLE ACCESS FULL | EMPLOYEES | 108 | 756 | 3 (0)| 00:00:01 | | 9 | SORT ORDER BY | | 27 | 810 | 5 (20)| 00:00:01 | |* 10 | VIEW | | 27 | 810 | 2 (0)| 00:00:01 | | 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_25A4FE | 27 | 621 | 2 (0)| 00:00:01 | | 12 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | | 13 | SORT AGGREGATE | | 1 | 13 | | | | 14 | VIEW | | 27 | 351 | 2 (0)| 00:00:01 | | 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_25A4FE | 27 | 621 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 10 - filter("DEPT_TOTAL"> (SELECT "DEPT_AVG" FROM (SELECT SUM("DEPT_TOTAL")/COUNT(*) "DEPT_AVG" FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "DEPARTMENT_NAME","C1" "DEPT_TOTAL" FROM "SYS"."SYS_TEMP_0FD9D6610_25A4FE" "T1") "DEPT_COSTS") "AVG_COST")) 8、相关习题： （1）Which two statements are true regarding the execution of the correlated subqueries ?(Choose two) A.The nested query executes after the outer query returns the row. B.The nested query executes first and then the outer query executes. C.The outer query executes only once for the result returned by the inner query. D.Each row returned by the outer query is evaluated for the results returned by the inner query. 答案：AD （2）Which two statements are true regarding the EXISTS operator used in the correlated subqueries?(choose two) A.The outer query stops evaluating the result set of the inner query when the first value is found. B.It is used to test whether the values retrieved by the inner query exist in the result of the outer query. C.It is used to test whether the values retrieved by the outer query exist in the result set of the inner query. D.The outer query continues evaluating the result set of the inner query until all the values in the result set are processed. 答案：AC （3）View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables. You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get the maximum salary in their respective departments. The following SQL statement was written: WITH SELECT employee_id, last_name, salary FROM employees WHERE (department_id, salary) = ANY (SELECT * FROM dept_max) dept_max as ( SELECT d.department_id, max(salary) FROM departments d JOIN employees j ON (d.department_id = j.department_id) GROUP BY d.department_id)? Which statement is true regarding the execution and the output of this statement? A.The statement would execute and give the desired results. B.The statement would not execute because the = ANY comparison operator is used instead of =. C.The statement would not execute because the main query block uses the query name before it is even created. D.The statement would not execute because the comma is missing between the main query block and the query name 答案：C （4）View the Exhibit and examine DEPARTMENTS and the LOCATION tables. Evaluate the following SQL statement: SELECT location_id, city FROM locations l WHERE NOT EXISTS (SELECT location_id FROM departments WHERE location_id <> l.location_id)? This statement was written to display LOCATION_ID and CITY where there are no departmnts located. Which statement is true regarding the execution and output of the command? A.The statement would execute and would return the desired results. B.The statement would not execute because the = comparison operator is missing in the WHERE clause of the outer query. C.The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of <>. D.The statement would not execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result. 答案：C （5）The following are the steps for a correlated subquery, listed in random order: 1) The WHERE clause of the outer query is evaluated. 2) The candidate row is fetched from the table specified in the outer query. 3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed. 4) Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery. A.4, 2, 1, 3 B.4, 1, 2, 3 C.2, 4, 1, 3 D.2, 1, 4, 3 答案：C （6）Which two statements best describe the benefits of using the WITH clause？(Choose two.)
A.It enables users to store the results of a query permanently.
B.It enables users to store the query block permanently in the memory and use it to create complex queries.
C.It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
D.It can improve the performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace. 答案：CD （7）Which statements are true regarding the usage of the WITH clause in complex correlated subqueries？(Choose all that apply.)
A.It can be used only with the SELECT clause.
B.The WITH clause can hold more than one query.
C.If the query block name and the table name were the same, then the table name would take precedence.
D.The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block. 答案：ABD （8）View the Exhibit and examine the structure of EMPLOYEES and JOB_HISTORY tables. The EMPLOYEES table maintains the most recent information regarding salary, department, and job for all the employees. The JOB_HISTORY table maintains the record for all the job changes for the employees. You want to delete all the records from the JOB_HISTORY table that are repeated in the EMPLOYEES table. Which two SQL statements can you execute to accomplish the task?(Choose two.) A.DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id) AND job_id = (SELECT job_id FROM employees e WHERE j.job_id = e.job_id)；
B.DELETE FROM job_history j WHERE (employee_id, job_id) = ALL (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id )；
C.DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id )；
D.DELETE FROM job_history j WHERE (employee_id, job_id) = (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id )； 答案：CD
| 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2015-12-21 18:30:02
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对