Oracle基礎教程：多表查詢、SQL99

多表查詢
 等串連
  通過兩個表具有相同意義的列,建立串連條件.
  查詢結果只顯示兩個列中的值是等值條件的行資料
  表中同名列被選擇時必須添加表名首碼進行修飾 否則無法確定這一列是屬於哪個表
  N張表等值串連，條件至少有N-1個，否則會產生笛卡爾積
 不等串連
  A表中的某列資料和B表中一列或多列的關係是非等值關係,大於,小於,不等於,等條件都屬於不等串連的範疇
 自串連
  資料都來自一張表,所以在from字句中需要對錶添加別名,添加表別名後才能合法化的參考資料表中的列名.
  本質就是將一張表虛擬成了兩張表
 外串連
  即是選擇出滿足等串連條件及其以外的行
  (+)修飾符號用法:放置在選出結果只包含等串連的列後,則另一列的結果就是等值行+非等值行
 全串連（Oracle 不提供串連關鍵字，需要用集合模式實現）
 
 SQL1999（SQL99） 有全串連的關鍵詞

當資料需要從多個表中獲得時

idle> select * from emp,dept;
...
...
56 rows selected.
這樣得到的結果是 56 行,其實兩個表產生了笛卡爾集 14 * 4
idle> select count(*) from emp;

  COUNT(*)
----------
 14

idle> select count(*) from dept;

  COUNT(*)
----------
  4

idle>

在串連中給定一個等值條件 一般是主鍵和外鍵的關係
例如deptno在dept表中是主鍵 在emp表中是外鍵
觀看dept(部門表)和emp(員工表)的關係
他們是主從關係,dept是主表,emp是從表
emp中的deptno一定要隸屬於dept中的deptno
所以他們兩個列是等值的
具體的主鍵和外鍵關係 我們在以後建表時再詳細闡述

 

等串連

按deptno的等值關係聯合兩張表的所有列

idle> select * from dept,emp where dept.deptno=emp.deptno;

    DEPTNO DNAME   LOC       EMPNO ENAME      JOB        MGR HIREDATE        SAL COMM  DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
 10 ACCOUNTING   NEW YORK       7782 CLARK      MANAGER       7839 1981-06-09 00:00:00      2450       10
 10 ACCOUNTING   NEW YORK       7839 KING       PRESIDENT     1981-11-17 00:00:00      5000       10
 10 ACCOUNTING   NEW YORK       7934 MILLER     CLERK       7782 1982-01-23 00:00:00      1300       10
 20 RESEARCH   DALLAS       7566 JONES      MANAGER       7839 1981-04-02 00:00:00      2975       20
 20 RESEARCH   DALLAS       7902 FORD       ANALYST       7566 1981-12-03 00:00:00      3000       20
 20 RESEARCH   DALLAS       7876 ADAMS      CLERK       7788 1987-05-23 00:00:00      1100       20
 20 RESEARCH   DALLAS       7369 SMITH      CLERK       7902 1980-12-17 00:00:00       800       20
 20 RESEARCH   DALLAS       7788 SCOTT      ANALYST       7566 1987-04-19 00:00:00      3000       20
 30 SALES   CHICAGO       7521 WARD       SALESMAN       7698 1981-02-22 00:00:00      1250  500      30
 30 SALES   CHICAGO       7844 TURNER     SALESMAN       7698 1981-09-08 00:00:00      1500    0      30
 30 SALES   CHICAGO       7499 ALLEN      SALESMAN       7698 1981-02-20 00:00:00      1600  300      30
 30 SALES   CHICAGO       7900 JAMES      CLERK       7698 1981-12-03 00:00:00       950       30
 30 SALES   CHICAGO       7698 BLAKE      MANAGER       7839 1981-05-01 00:00:00      2850       30
 30 SALES   CHICAGO       7654 MARTIN     SALESMAN       7698 1981-09-28 00:00:00      1250 1400      30

14 rows selected.

idle>
這才是我們要的結果.笛卡爾積幾乎我們不會需要.多表查詢時基本都帶有where子句來描述多個表的關係 避免笛卡爾集

當兩個表中有相同的列名時,為了區分 要在列前加上表名作首碼.
不衝突時 直接使用
idle> select deptno,empno,ename,dname,sal from emp a,dept b where b.deptno=a.deptno;
select deptno,empno,ename,dname,sal from emp a,dept b where b.deptno=a.deptno
       *
ERROR at line 1:
ORA-00918: column ambiguously defined
因為deptno 沒有表首碼 衝突

idle>
在聯合的兩個表內取資料:描述scott在哪個部門
idle> select empno,ename,dname,sal from emp,dept where emp.deptno=dept.deptno and ename='SCOTT';

     EMPNO ENAME      DNAME      SAL
---------- ---------- -------------- ----------
      7788 SCOTT      RESEARCH     3000

idle>

表的別名
 格式:表名 別名
給表取別名是很有必要的,因為有的表名很長 不便於引用時書寫.

idle> select a.deptno,empno,ename,dname,sal from emp a,dept b where b.deptno=a.deptno and ename='SCOTT';

    DEPTNO EMPNO ENAME  DNAME         SAL
---------- ---------- ---------- -------------- ----------
 20  7788 SCOTT  RESEARCH       3000

idle>

不等串連
 就是排除完全相等條件以外的 >,<,!=, <=, >=, between and
 主要在於不同表之間顯示特定範圍的資訊(也可以理解成內含項目關聯性)
例如: SALGRADE表把工資劃分了5個等級
idle> select * from SALGRADE;

     GRADE LOSAL    HISAL
---------- ---------- ----------
  1   700     1200
  2  1201     1400
  3  1401     2000
  4  2001     3000
  5  3001     9999

idle>

查處每個員工的工資等級
idle> select ename,sal,grade from emp a,salgrade b where sal between losal and hisal;

ENAME    SAL    GRADE
---------- ---------- ----------
SMITH    800        1
JAMES    950        1
ADAMS   1100        1
WARD   1250        2
MARTIN   1250        2
MILLER   1300        2
TURNER   1500        3
ALLEN   1600        3
CLARK   2450        4
BLAKE   2850        4
JONES   2975        4
SCOTT   3000        4
FORD   3000        4
KING   5000        5

14 rows selected.

idle>

練習:
 1.查詢名字是S開頭的員工名,員工號,部門號,部門名,部位地理位置
 select empno,ename,emp.deptno,dept.dname,dept.loc
 from emp,dept
 where ename like 'S%' and emp.deptno=dept.deptno;
 2.查詢員工名,部門名,工資,工資等級
 select empno,ename,grade
 from emp,salgrade
 where sal between losal and hisal;
 3.查詢工作在NEW YORK的所有員工
 select empno,ename,emp.deptno,dept.dname,dept.loc
  from emp,dept
   where emp.deptno=dept.deptno and dept.loc='NEW YORK';

 

自串連
 同一張表內的串連查詢 即把一個表映射成兩個表
 主要用於表的自參照關係 比如emp中的上下級或層次關係
 因為自串連是同一張表之間的連結查詢 所以必須定義表別名

例子:根據empno和mgr的對應關係,可以尋找出KING的下屬都有誰

idle> select b.ename||'''s manager is '||a.ename from emp a,emp b where a.empno = b.mgr and a.ename='KING';

B.ENAME||'''SMANAGERIS'||A.ENAME
----------------------------------
BLAKE's manager is KING
JONES's manager is KING
CLARK's manager is KING

idle>

1 2 3 下一頁