Oracle學習筆記 5

我們之前的查詢都是建立在一張表上，那麼如何從兩張，或者多張表中查詢資料呢？本次筆記來學習多表查詢。

    基本的文法：

select *  | (具體的列名，列名1 ，列名2 )

from table 1  別名1 ， table 別名2

where  限定條件

order by 列名(asc , desc )

等值串連：使用‘=’比較符作為串連條件的串連查詢，被稱為等值串連。使用除‘=’以外其他比較符作為串連條件的串連查詢，稱為不等串連。

內串連：合并兩個表或者多個表相匹配的行,如果其中一個表中的行找不到在另一個表中相匹配的行，則刪除這個行。結果集中不包含一個表與另一個表不匹配的行。

外串連：兩個表在串連過程中除了返回滿足串連條件的行以外還返回左（或右）表中不滿足條件的行，這種串連稱為

左（或右）外串連:沒有匹配的行時,      結果表中相應的列為空白(NULL)。外串連的 WHERE 子句條件類似於內部串連, 但串連條件中沒有匹配行的表的列後面要加外串連運算子, 即用圓括弧括起來的加號(+)。

交叉串連：串連查詢的時候不使用串連條件，容易產生笛卡爾積。

自串連：串連查詢在一個表上或者是視圖上進行，即：表與自己進行串連查詢。

 

上面給了各個串連的大體定義，下面我們通過樣本具體解釋!

內串連：

        串連兩個或者是多個表的資料。返回滿足串連條件的資料。刪除不滿足串連條件和匹配列中帶有null值的記錄。

例：使用多表查詢語句同時對emp和dept表進行操作，查詢僱員姓名和部門名稱。

SQL> select ename ,dname from emp , dept where emp.deptno = dept.deptno ;ENAME      DNAME---------- --------------SMITH      RESEARCHALLEN      SALESWARD       SALESJONES      RESEARCHMARTIN     SALESBLAKE      SALESCLARK      ACCOUNTINGSCOTT      RESEARCHKING       ACCOUNTINGTURNER     SALESADAMS      RESEARCHJAMES      SALESFORD       RESEARCHMILLER     ACCOUNTING 14 rows selected

 

   上面查詢語句中使用了'='運算子，為等值串連。如果各表之間的列名不相同，那麼就不需要在列名錢加表的首碼；如果在各表之間有相同的列名，則

要加上表名做首碼，以此來區分不同表中的相同列名。有時表名可能較長，我們可以給表起一個別名，來簡化操作和提高執行效率。

  SQL>select ename ,dname from emp e  , deptd  where e.deptno = d.deptno ;

  上面這條語句的查詢結果，與上面結果也是一樣的，此處不在贅述。

 

下面使用 between ... on 比較符作為串連條件，來實現不等串連。

在不等串連之前，先來瞭解soctt下的一個新表,salgrade --工資等級表。

SQL> select * from salgrade ;      GRADE      LOSAL      HISAL---------- ---------- ----------         1        700       1200         2       1201       1400         3       1401       2000         4       2001       3000         5       3001       9999

 

例：查詢僱員表中部門號為10的員工的工資等級。

SQL>select ename ,e.deptno , grade ,sal   2 from emp e ,dept  d , salgrade s   3 where e.deptno = d.deptno and sal between losal and hisal  and d.deptno = 10 ;ENAME      DEPTNO      GRADE       SAL---------- ------ ---------- ---------MILLER         10          2   1300.00CLARK          10          4   2450.00KING           10          5   5000.00

 

 上面的結果中，使用between...on...做為連結條件實現了工資等級的劃分。在n個表的串連中，至少要是使用 n-1個串連條件。多個串連條件之間使用and串連。

 

 外串連：

       外串連查詢是對內串連的擴充。內串連返回的結果是滿足串連條件的，外串連時在內串連的基礎上，返回被內連

接刪除掉的資料。外串連分為：左外串連，右外串連，全外串連。左外串連，添加會第一個表中不滿足串連條件的

行；右外串連，添加回第二個表中不滿足串連條件的行。全外串連，添加回兩個表中刪除的行。

 

例：查詢員工姓名 ，部門名稱 和 部門編號。

SQL> select  ename , dname ,d.deptno  2  from emp e ,dept d   3  where e.deptno  =  d.deptno  ; ENAME      DNAME          DEPTNO---------- -------------- ------SMITH      RESEARCH           20ALLEN      SALES              30WARD       SALES              30JONES      RESEARCH           20MARTIN     SALES              30BLAKE      SALES              30CLARK      ACCOUNTING         10SCOTT      RESEARCH           20KING       ACCOUNTING         10TURNER     SALES              30ADAMS      RESEARCH           20JAMES      SALES              30FORD       RESEARCH           20MILLER     ACCOUNTING         10 14 rows selected

觀察上面的結果，發現缺少了編號為40 的部門。對上面的SQL語句進行修改。

SQL> select ename , dname , d.deptno  2  from emp e , dept d  3  where e.deptno(+) = d.deptno ; ENAME      DNAME          DEPTNO---------- -------------- ------CLARK      ACCOUNTING         10KING       ACCOUNTING         10MILLER     ACCOUNTING         10JONES      RESEARCH           20FORD       RESEARCH           20ADAMS      RESEARCH           20SMITH      RESEARCH           20SCOTT      RESEARCH           20WARD       SALES              30TURNER     SALES              30ALLEN      SALES              30JAMES      SALES              30BLAKE      SALES              30MARTIN     SALES              30           OPERATIONS         40 15 rows selected

此時我們便查詢出了編號為40的部門資訊。我們發現，40號部門下並沒有員工。上面使用到了右串連。

(+) 在 = 號的左邊為右串連。此時返回的是from 語句中第二個表中的記錄。

(+)在=號的右邊為左串連。此時返回的是from語句中第一個表中的記錄。

例：查詢僱員表中每一個僱員的經理是誰 ？

SQL語句：

select e.ename ename ,m.ename mname
from emp e , emp m
where e.mgr = m.empno(+)

結果：

ENAME      MNAME---------- ----------SMITH      FORDALLEN      BLAKEWARD       BLAKEJONES      KINGMARTIN     BLAKEBLAKE      KINGCLARK      KINGSCOTT      JONESKING       TURNER     BLAKEADAMS      SCOTTJAMES      BLAKEFORD       JONESMILLER     CLARK 14 rows selected

 上面使用了左串連，雖然king的值不滿足串連條件，依然可以再結果中顯示。

自串連：

      上面的查詢已經使用了自串連的串連方式。自串連，自已與自己的串連，與兩張表的查詢方式沒有區別，不在贅述。

 

SQL：1999 文法對SQL的支援。

 

 交叉串連：

        cross  join  ，產生笛卡爾積。

SQL語句：

select * from emp cross join dept

沒有給出查詢條件，使用cross join 來串連兩個表。查詢結果滿足笛卡爾乘積，即，兩個表中每一條記錄都要串連在一起。

自然串連：

natural join ，自動進行關聯欄位的匹配。會以兩個表中有相同名字的列為條件建立等值串連。但是，兩個表中只是列名相同而資料的類型不同，則串連出錯。

SQL語句：

SQL> select * from emp natural join dept ;

僱員表和員工表會自動的按照deptno 進行串連。查詢結果與語句  select *  from emp e , dept d

where d.deptno = e.deptno ; 相同。但是如果兩張表中有多個相同的列名，此時的查詢結果就是多個列名連

接後共同作用產生的結果。此時需要去掉natural ，使用using(列名)來指定，表要按照哪一列來串連。也可以使用on來指定串連條件。SQL> select * from emp e join dept d  on(e.deptno = d.deptno);結果都是一樣的。

使用on字句建立多表串連文法。

SELECT 列名1, 列名2, 列名3

FROM   table1e

JOIN   table2 d

ON    d.列名 =e.列名

JOIN   table3 l

ON    d.列名 =l.列名;

右外串連、左外串連、滿外串連的文法：

SELECT 列名1, 列名2, 列名3

FROM   table1 e

ROGHT OUTER JOIN table2 d

ON   (e.列名=d.列名)

例：

SQL> select ename , dname
  2  from emp e right outer join dept d
  3  on(e.deptno   = d.deptno);

 ENAME      DNAME---------- --------------CLARK      ACCOUNTINGKING       ACCOUNTINGMILLER     ACCOUNTINGJONES      RESEARCHFORD       RESEARCHADAMS      RESEARCHSMITH      RESEARCHSCOTT      RESEARCHWARD       SALESTURNER     SALESALLEN      SALESJAMES      SALESBLAKE      SALESMARTIN     SALES           OPERATIONS 15 rows selected

 

SELECT 列名1, 列名2, 列名3

FROM   table1 e

LEFT OUTER JOIN table2 d

ON   (e.列名=d.列名) ;

 

SELECT 列名1, 列名2, 列名3

FROM   table1 e

FULL OUTER JOIN table2 d

ON  (e.列名=d.列名) ;