Oracle 學習筆記 14 -- 集合操作和進階子查詢

標籤：

Oracel提供了三種類型的集合操作：各自是並(UNION) 、交(INTERSECT)、 差(MINUS)

UNION :將多個操作的結果合并到一個查詢結果中，返回查詢結果的並集，自己主動去掉反覆的部分。

UNION ALL：將多個操作的結果合并到一個查詢結果中，可是保留反覆的內容。

INTERSECT： 返回多個操作結果中同樣的部分。

MINUS：返回兩個查詢結果的差集，去掉反覆的部分。

基本的文法格式為：

SELECT * FROM  table_name 1

[union , union all , intersect , minus]

SELECT * FROM  table_name2

注意：僅僅能有一個Order by 語句 ，而且該語句要放在末尾。

語句運算式要使用第一個select的列名，別名或者是位

置編號號。在select 列表中的列名和運算式在數量和資料類型上也要相應。

 

進階子查詢

子查詢：前面已經說過，即：SQL語句內部嵌套還有一個select語句。內部查詢要先於外邊查詢進行。內部查詢返回的

結果要給外部查詢使用。

多列子查詢

在筆記7中的子查詢。都是基於單列的查詢，如今學習的是多列的子查詢。主查詢要與子查詢返回的多個列進行比

較。不在是一個列。

基本的文法：

SELECT * FROM table_name

where (column1 , column2) = | in (

   SELECT  column1 , column2 from table_name2  where ...

)

返回的結果不在是單列，而是多列。

外層查詢中where語句後面的多個列名要用括弧括起來。

相互關聯的子查詢

子查詢中使用主查詢的列。主查詢的每一行的資料都運行一次子查詢。

例，求emp表中工資大於平均工資的員工資訊

select * from emp e
where sal > (
      select avg(sal) from emp
      where deptno  = e.deptno    
)

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7900 JAMES      CLERK      7698 1981/12/3     2000.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 
6 rows selected

EXISTS操作符

    EXISTS操作符檢查在子查詢中是否存在滿足條件的行，若存在。不在子查詢中繼續尋找。返回true，如不存在。返回false 。繼續子查詢中尋找。NOT EXISTS操作符檢查在子查詢中是否不存在滿足條件的行 。

with子句      

使用 WITH子句, 能夠避免在 SELECT語句中反覆書寫同樣的語句塊WITH子句將該子句中的語句塊運行一次並儲存到使用者的暫時資料表空間中使用 WITH子句能夠提高查詢效率

嵌套子查詢

 例：查詢比本部門工資高的員工資訊

select deptno  ,  ename,sal , avgsal
from  emp e ,(select avg(sal) avgsal from emp  group by  deptno )
where  sal > avgsal

DEPTNO ENAME            SAL     AVGSAL
------ ---------- --------- ----------
    30 JAMES        2000.00 1741.66666
    10 CLARK        2450.00 1741.66666
    30 BLAKE        2850.00 1741.66666
    20 JONES        2975.00 1741.66666
    20 SCOTT        3000.00 1741.66666
    20 FORD         3000.00 1741.66666
    10 KING         5000.00 1741.66666
    10 CLARK        2450.00       2175
    30 BLAKE        2850.00       2175
    20 JONES        2975.00       2175
    20 SCOTT        3000.00       2175
    20 FORD         3000.00       2175
    10 KING         5000.00       2175
    20 JONES        2975.00 2916.66666
    20 SCOTT        3000.00 2916.66666
    20 FORD         3000.00 2916.66666
    10 KING         5000.00 2916.66666
 
17 rows selected

在from語句中，使用了子查詢，查詢出部門的平均工資，從而實現了員工工資與部門平均工資的比較。

Oracle 學習筆記 14 -- 集合操作和進階子查詢