查看: 224|回复: 0

[Oracle数据库] (Les17 Retrieving Data Using Subqueries)[20180103]

发表于 7 天前
学习目标: -多列子查询 -SQL语句中使用标量子查询 -更新或删除行使用关联子查询 -使用EXISTS和NOT EXISTS操作符 -使用WITH子句 多列子查询 语法: Main query WHERE (,) IN Subquery; 列的比较 -非成对比较(两两比较) -成对比较
  1.     非成对比较
  2. SELECT <column>,[<column>,| <column>]
  3. FROM <table_name>
  4. WHERE <column> IN Subquery
  5. AND <column> IN Subquery;
  6. 成对比较
  7. SELECT <column>,[<column>,|<column>]
  8. FROM <table_name>
  9. WHRE (<column>,<column>) IN
  10. (SELECT <column>,<column> FROM <table_name>
  11. WHERE clause);
复制代码

标量子查询 -标量子查询表达式中的子查询返回一个值 -标量子查询表达式中可以含有decode和case函数 -标量子查询中需排除GROUP BY -标量子查询可以在UPDATESQL语句SET子句中和WHERE条件 标量子查询不能用于: -列的默认值和集群的散列表达式 -DML语句RETURNING条件中 -基于函数的索引 -GROUP BY子句、CHECK约束和WHEN子句 -CONNECT BY子句 -与查询无关的语句中,如CREATE PROFILE概要文件
  1.     例子:
  2. SELECT <column>,<column>
  3. (CASE
  4. WHEN <column> = (SELECT <column>
  5. FROM <table_name>
  6. WHERE clause)
  7. THEN 'expression' END) <column_alias>
  8. FROM <table_name>;
复制代码

关联子查询 -子查询引用主查询语句中的列值,Oracle执行相关子查询。 -对于主查询中的每一行,相关的子查询都会执行一次。 -主查询可以是SELECT/UPDATE或DELETE语句。 Nested Subqueries(嵌套子查询)或Correlated Subqueries(关联子查询) -嵌套子查询:子查询首先运行并执行一次,返回结果给主查询。 -关联子查询:关联子查询由外部的主查询驱动的,所以主查询中的每个列值子查询均会执行一次。 嵌套子查询执行顺序: -子查询执行一次并且返回一个值 -主查询使用子查询返回的值执行一次 关联子查询执行顺序: -主查询候选列值 -子查询使用主查询候选的列值运行并执行 -子查询值返回给主查询 -重复以上过程,直到没有候选列值
  1.     SELECT <columu1>,<column2>,....
  2. FROM <table_name> outer
  3. WHERE <column1> operator
  4. (SELECT <column1>,<column2>
  5. FROM <table_name>
  6. WHERE expr1 = outer.expr2);
复制代码

注意:关联子查询中可以使用ANY和ALL操作符
  1. 关联子查询效能验证与可替换方式:<br>16:32:16 SQL> col last_name format a30
  2. 16:32:16 SQL> set pagesize 1200
  3. 16:32:16 SQL> select last_name,salary,department_id
  4. 16:32:16 2 from employees outer
  5. 16:32:16 3 where salary >(select avg(salary)
  6. 16:32:16 4 from employees
  7. 16:32:16 5 where department_id=outer.department_id)
  8. 16:32:16 6 ;
  9. LAST_NAME SALARY DEPARTMENT_ID
  10. ------------------------------ ---------- -------------
  11. Hartstein 12990 20
  12. Raphaely 10990 30
  13. Weiss 7990 50
  14. Fripp 8190 50
  15. Kaufling 7890 50
  16. Vollman 6490 50
  17. Mourgos 5790 50
  18. Ladwig 3590 50
  19. Rajs 3490 50
  20. Sarchand 4190 50
  21. Bull 4090 50
  22. Chung 3790 50
  23. Dilly 3590 50
  24. Bell 3990 50
  25. Everett 3890 50
  26. <br>语句改写:<br>
  27. col last_name format a30
  28. set pagesize 1200
  29. select last_name,salary,department_id
  30. from employees outer
  31. where salary >(select avg(salary)
  32. from employees
  33. where department_id=outer.department_id)
  34. minus
  35. select outer.last_name,outer.salary,outer.department_id
  36. from employees outer,(select department_id,avg(salary) avg_salary from employees group by department_id) inner
  37. where outer.department_id=inner.department_id
  38. and outer.salary>inner.avg_salary;
  39. 对比两种写法:
  40. 16:45:11 SQL> set autotrace traceonly
  41. 16:45:21 SQL> alter system flush shared_pool;
  42. 已更改系統.
  43. 16:45:22 SQL> select last_name,salary,department_id
  44. 16:45:22 2 from employees outer
  45. 16:45:22 3 where salary >(select avg(salary)
  46. 16:45:22 4 from employees
  47. 16:45:22 5 where department_id=outer.department_id); --关联子查询
  48. 已選取 38 個資料列.
  49. 執行計畫
  50. ----------------------------------------------------------
  51. 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=17 Bytes=6
  52. 97)
  53. 1 0 MERGE JOIN (Cost=11 Card=17 Bytes=697)
  54. 2 1 SORT (JOIN) (Cost=5 Card=11 Bytes=286)
  55. 3 2 VIEW OF 'VW_SQ_1' (VIEW) (Cost=5 Card=11 Bytes=286)
  56. 4 3 HASH (GROUP BY) (Cost=5 Card=11 Bytes=77)
  57. 5 4 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4
  58. Card=107 Bytes=749)
  59. 6 1 FILTER
  60. 7 6 SORT (JOIN) (Cost=5 Card=107 Bytes=1605)
  61. 8 7 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4 C
  62. ard=107 Bytes=1605)
  63. 統計值
  64. ----------------------------------------------------------
  65. 351 recursive calls
  66. 0 db block gets
  67. 502 consistent gets
  68. 0 physical reads
  69. 0 redo size
  70. 1268 bytes sent via SQL*Net to client
  71. 449 bytes received via SQL*Net from client
  72. 4 SQL*Net roundtrips to/from client
  73. 50 sorts (memory)
  74. 0 sorts (disk)
  75. 38 rows processed
  76. 16:45:23 SQL> alter system flush shared_pool;
  77. 已更改系統.
  78. 16:45:45 SQL> select outer.last_name,outer.salary,outer.department_id
  79. 16:45:45 2 from employees outer,(select department_id,avg(salary) avg_salary from employees grou
  80. department_id) inner
  81. 16:45:45 3 where outer.department_id=inner.department_id
  82. 16:45:45 4 and outer.salary>inner.avg_salary; --join连接查询
  83. 已選取 38 個資料列.
  84. 執行計畫
  85. ----------------------------------------------------------
  86. 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=17 Bytes=6
  87. 97)
  88. 1 0 MERGE JOIN (Cost=11 Card=17 Bytes=697)
  89. 2 1 SORT (JOIN) (Cost=5 Card=11 Bytes=286)
  90. 3 2 VIEW (Cost=5 Card=11 Bytes=286)
  91. 4 3 HASH (GROUP BY) (Cost=5 Card=11 Bytes=77)
  92. 5 4 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4
  93. Card=107 Bytes=749)
  94. 6 1 FILTER
  95. 7 6 SORT (JOIN) (Cost=5 Card=107 Bytes=1605)
  96. 8 7 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4 C
  97. ard=107 Bytes=1605)
  98. 統計值
  99. ----------------------------------------------------------
  100. 191 recursive calls
  101. 0 db block gets
  102. 257 consistent gets
  103. 0 physical reads
  104. 0 redo size
  105. 1268 bytes sent via SQL*Net to client
  106. 449 bytes received via SQL*Net from client
  107. 4 SQL*Net roundtrips to/from client
  108. 19 sorts (memory)
  109. 0 sorts (disk)
  110. 38 rows processed
  111. 16:45:46 SQL>
  112. JOIN连接查询比关联子查询性能上都有所提高。
复制代码
EXISTS/NO EXISTS操作符 -EXISTS运算符经常与相关的子查询一起使用,测试子查询检索的值的结果集中是否存在主查询检索的值。 -如果子查询返回至少一行,则该运算符返回TRUE。 如果该值不存在,则返回FALSE。 -NOT EXISTS测试主查询检索的值是否是子查询检索的值的结果集的一部分。
  1. SELECT <column1>,<colum2>,...
  2. FROM <table_name> outer
  3. WHERE [EXISTS | NOT EXISTS] (SELECT <column1>,<column2>,....
  4. FROM <table_name>
  5. WHERE <column1> = outer.<column1>);
复制代码

关联UPDATE
  1. UPDATE <table_name> alias1
  2. SET <column> = (SELECT expression
  3. FROM <table_name> alias2
  4. WHERE alias1.column = alias2.column);
复制代码

注意:关联UPDATE如果主查询选定值在子查询中未找到,SET栏位值将被更新成NULL值。
  1.      create table ORA_01407_T0(
  2. id number not null,
  3. name varchar2(10) not null
  4. )
  5. /
  6. insert into ORA_01407_T0 values(1,'T01');
  7. insert into ORA_01407_T0 values(2,'T02');
  8. insert into ORA_01407_T0 values(3,'T03');
  9. commit;
  10. create table ORA_01407_T1(
  11. id number not null,
  12. name varchar2(10) not null
  13. )
  14. /
  15. insert into ORA_01407_T1 values(3,'T1_T03');
  16. insert into ORA_01407_T1 values(4,'T1_T04');
  17. commit;
  18. update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a.id=b.id);
  19. 16:47:15 SQL> update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a.id=b.id);
  20. update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a.id=b.id)
  21. *
  22. ERROR 在行 1:
  23. ORA-01407: 無法將 ("HR"."ORA_01407_T0"."NAME") 更新為 NULL
  24. 將NAME NOT NULL約束去掉
  25. 17:12:55 SQL> ALTER TABLE HR.ORA_01407_T0 MODIFY(NAME NULL);
  26. 已更改表格.
  27. 17:12:58 SQL> update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a
  28. 已更新 3 個資料列.
  29. 17:13:05 SQL> select * from ORA_01407_T0;
  30. ID NAME
  31. ---------- --------------------
  32. 1 ->可以看到關聯查詢中不匹配的記錄name欄被賦值為NULL。
  33. 2
  34. 3 T1_T03
复制代码
  1. 解決方案:
  2. 使用MERGE函數進行處理,匹配記錄UPDATE
  3. truncate table ORA_01407_T0;
  4. ALTER TABLE HR.ORA_01407_T0 MODIFY(NAME NOT NULL);
  5. insert into ORA_01407_T0 values(1,'T01');
  6. insert into ORA_01407_T0 values(2,'T02');
  7. insert into ORA_01407_T0 values(3,'T03');
  8. commit;
  9. merge into ORA_01407_T0 a using ORA_01407_T1 b on (a.id=b.id)
  10. when matched then update
  11. set a.name=b.name
  12. ;
  13. 17:20:34 SQL> merge into ORA_01407_T0 a using ORA_01407_T1 b on (a.id=b.id)
  14. 17:21:48 2 when matched then update
  15. 17:21:48 3 set a.name=b.name
  16. 17:21:48 4 ;
  17. 合併 1 個資料列.
  18. 17:21:49 SQL> select * from ORA_01407_T0;
  19. ID NAME
  20. ---------- --------------------
  21. 1 T01
  22. 2 T02
  23. 3 T1_T03
复制代码

 关联DELETE

  1. 10:51:14 SQL> select * from ora_01407_t0
  2. 10:51:41 2 ;
  3. ID NAME
  4. ---------- --------------------
  5. 1 T01
  6. 2 T02
  7. 3 T1_T03
  8. 10:51:42 SQL> select * from ora_01407_t1;
  9. ID NAME
  10. ---------- --------------------
  11. 3 T1_T03
  12. 4 T1_T04
  13. 10:51:46 SQL> delete
  14. 10:53:05 2
  15. 10:53:07 SQL>
  16. 10:53:07 SQL>
  17. 10:53:07 SQL> delete from ora_01407_t0 t1
  18. 10:53:10 2 where id=(select id from ora_01407_t1 t2
  19. 10:53:10 3 where t2.id=t1.id);
  20. 已刪除 1 個資料列.
  21. 10:53:11 SQL> select * from ora_01407_t0;
  22. ID NAME
  23. ---------- --------------------
  24. 1 T01
  25. 2 T02
复制代码

WITH子句 -使用WITH子句可以将多个相同的SELECT语句块组成一个复杂的查询 -WITH子句 中查询块返回值会保存在用户默认的临时表空间 -使用WITH子句可提高性能 WITH子句好处 -查询语句块便于阅读 -减少解析,查询块多次出现只会评估一次 -多数情况下,可提高查询性能   例子:查询部门薪水大于部门平均薪水的部门
  1. col department_name format a20
  2. with
  3. dept_costs as (
  4. select d.department_name,sum(salary) as dept_total
  5. from employees e join departments d
  6. on e.department_id = d.department_id
  7. group by d.department_name),
  8. avg_cost as (
  9. select sum(dept_total)/count(*) as dept_avg
  10. from dept_costs)
  11. select *
  12. from dept_costs
  13. where dept_total > (select dept_avg from avg_cost)
  14. order by department_name;
  15. DEPARTMENT_NAME DEPT_TOTAL
  16. -------------------- ----------
  17. Sales 304160
  18. Shipping 155950
复制代码

学习总结: 1.多行/多列子查询(成对/非成对比较) 2.关联子查询(主查询->子查询->返回值->主查询)和标量子查询(返回一个列值)、关联UPDATE/DELETE/SELECT 3.EXISTS和NOT EXISTS操作符 4.使用WITH子句


回复

使用道具 举报