Oracle表連接
概述：SQL/Oracle使用表連接從多個表中查詢數據
格式：select 字段列表 from table1,table2 where table1.column1=table2.column2;
說明：當被連接的多個表中存在同名字段時，必須在該字段前加上“table.”作為前綴
如果沒有限定where連接條件，否則就會出現笛卡爾集的不現實或沒有實用意義的結果
舉例：select empno, ename, sal, emp.deptno, dname, loc from emp, dept;--這時得到的就是笛卡爾集。此時得到70條記錄
select empno, ename, sal, emp.deptno, dname, loc from emp, dept where emp.deptno=dept.deptno;--得到14條記錄
類型：Oracle8i之前的表連接(支持SQL92標准)：等值連接(Equijoin)、非等值連接(Non—Equijoin)、自連接(Self join)
外連接(Outer join)：左外連接、右外連接
Oracle9i新引入的連接形式(支持SQL99規范)：交叉連接(Cross join)、自然連接(Natural join)、使用Using或On子句建立連接
內連接(Inner join)、外連接(Outer join)：左外連接、右外連接、全外連接
補充：多表連接中可使用AND操作符增加查詢條件，使用表別名可以簡化查詢，使用表名(表別名)前綴可提高查詢效率
而連接n個表，則至少需要n-1個連接條件。如select a.ename, a.deptno, b.dname from emp a, dept b where a.deptno=b.deptno;
樣本：hr帳戶也是Oracle自帶的樣本帳戶，裡面有很多樣本表，其數據量及復雜程度都要比scott強一些
hr帳戶缺省是鎖定的，可以在OEM中將其解鎖並設密碼。其中的employees表是scott中的emp表的增強版
另外還有departments和locations表的數據看起來都比較真實。這三個表通常用於復雜的多表連接查詢
等值連接(Equijoin)
舉例：select empno, ename, emp.deptno, dname from emp, dept where emp.deptno=dept.deptno;
非等值連接(Non—Equijoin)
問題：如何查得每個員工的工資等級
舉例：select empno, ename, sal, grade 工資等級 from emp,salgrade where sal between losal and hisal;
外連接(Outer join)
概述：外連接運算符為(+)。使用外連接可以看到參與連接的某一方不滿足連接條件的記錄
傳統的外連接分為左外連接和右外連接兩種。實際開發中，外連接的使用頻率要比等值連接少一些
語法：select 字段列表 from table1,table2 where table1.column1(+)=table2.column2;
select 字段列表 from table1,table2 where table1.column1=table2.column2(+);
說明：table1.column1(+)=table2.column2;--顯示所有符合條件的記錄，同時table2中不符合連接條件的記錄也會顯示出來
table1.column1=table2.column2(+);--顯示所有符合條件的記錄，同時table1中不符合連接條件的記錄也會顯示出來
補充：在SQL99規范中，內連接(Inner Join)只返回滿足連接條件的數據，而外連接還返回不滿足連接條件的行
分類：左外聯接(Left Outer Join)：即兩個表在連接過程中除返回滿足連接條件的行以外，還返回左表中不滿足條件的行
右外聯接(Right Outer Join)：即兩個表在連接過程中除返回滿足連接條件的行以外，還返回右表中不滿足條件的行
滿外聯接(Full Outer Join)：即兩個表在連接過程中除返回滿足連接條件的行以外，還返回兩個表中不滿足條件的行
舉例：select employee_id, last_name, salary, department_id, department_name
form employees left join departments using(department_id);--返回107行
自連接(Self join)
概述：自連接诶本質就是把一個表當作兩個表來使用，只是定義不同的別名而已
平時很少使用自連接，但有時確實只有采用自連接的方式才能解決某些問題
問題：如何查得每個員工及其上司的工號和姓名
舉例：select a.empno, a.ename, a.mgr, b.ename from emp a, emp b where a.mgr=b.empno;
SQL99連接語法
概述：SQL1999規范中規定的連接查詢語法。兩個以上的表進行連接時應依次分別指定相臨的兩個表之間的連接條件
語法：select 字段列表
from table1
[cross join table2]|
[natural join table2]|
[join table2 using(字段名)]|
[join table2 on(table1.column_name=table2.column_name)]|
[(left|right|full outer)join table2 on(table1.column_name=table2.column_name)];
[cross join table3]|
[natural join table3]|
[join table3 using(字段名)]|
[join table3 on(table2.column_name=table3.column_name)]|
[(left|right|full outer)join table3 on(table2.column_name=table3.column_name)]...;
說明：如果感覺新語法比較混亂，在沒有強制性要求的時候，完全可以使用舊語法，二者不存在效率上的差異
交叉連接(Cross join)
概述：Cross join產生了一個笛卡爾集，其效果等同於在兩個表進行連接時未使用WHERE子句限定連接條件
舉例：select empno, ename, dname from emp a cross join dept b;--交叉連接實際上沒有太大的意義
自然連接(Natural join)
概述：Natural join基於兩個表中的全部同名列建立連接。從兩個表中選出同名列的值均對應相等的所有行
如果兩個表中同名列的數據類型不同，則出錯。而且不允許在參照列上使用表名或別名作為前綴
舉例：select demno, ename, sal, deptno, dname from emp natural jon dept;
Using子句
概述：如果不希望參照被連接表的所有同名列進行等值連接，自然連接將無法滿足要求
可以在連接時使用USING子句來設置用於等值連接的列(參照列)名
同樣不允許在參照列上使用表名或別名作為前綴
舉例：select empno, ename, sal, deptno, dname form emp join dept using(deptno);
On子句
概述：如果要參照非同名的列進行等值連接，或想設置任意的連接條件，可以使用ON子句
舉例：select empno, ename, sal, emp.deptno, dname from emp join dept on(emp.deptno=dept.deptno);
子查詢(Sub Query)
概述：子查詢在主查詢前執行一次，主查詢使用子查詢的結果。比如查詢所有比張三工資高的員工信息
子查詢分為單行子查詢(返回一行結果)和多行子查詢(返回多行結果)兩大類
語法：select 字段列表 form table where 表達式 operator(select 字段列表 from table);
比如select * from emp where sal>(select sal from emp where empno=7654);
注意：基於未知值的查詢應考慮使用子查詢。子查詢必須包含在括號內
建議將子查詢放在比較運算符的右側，以增強可讀性。除非進行Top—N分析，否則不要在子查詢中使用ORDER BY子句
對單行子查詢可以使用單行記錄比較運算符。而對多行子查詢則只能使用多行記錄比較運算符
空值：如果子查詢未返回任何行，則主查詢也不會返回任何結果
比如select * from where sal>(select sal from emp where empno=8888);--不會返回任何結果
多值：如果子查詢返回多行結果，則為多行子查詢，此時不允許對其使用單行記錄比較運算符
比如select * from emp where sal>(select avg(sal) from group by deptno);--非法
TopN查詢(TopN分析)
概述：即獲得按照某種規則排序之後的前n條的記錄。Oracle中通常采用子查詢的方式實現TOPN查詢
其實子查詢可以認為是查到了一個臨時表，或沒有名字的臨時視圖
語法：select 字段列表 from (select 字段列表 from table order by 排序字段) where rownum<=n;
舉例：select * from (select * from emp order by sal desc) where rownum <=5;
偽列rownum
概述：SELECT查詢結果中會隱含的增加一個字段rownum，即偽列。rownum用起來很靈活，但也很容易出錯
rownum偽列並不是數據表中或者子查詢的虛擬表中真實存在的列，它只是查詢結果中的一個偽列
它標記的是符合查詢條件的結果的編號，第一條記錄的rownum值為1，第二條記錄的rownum值為2
可以理解為，符合查詢條件的第一行記錄編號為1，符合查詢條件的第二行記錄編號為2
例一：select * from emp where rownum>=5;--它執行後的結果是沒有返回值
執行時先取出結果集中的，或者說是數據表中的第一條記錄，並標記第一條記錄的編號為1
判斷後得知1小於5，不符合條件。隨後便過濾掉這條記錄了，接著判斷下一條記錄是不是符合條件
於是就又取出下一條記錄，下一條記錄的rownum還是從1開始。而rownum永遠是從1開始的，結果可想而知
接著的下一條記錄的編號還是1。即照此情形下去，記錄的編號永遠不會符合大於等於5的條件
也就是說在這條SQL語句的環境下，rownum永遠也不會大於等於5。所以就不能指望用rownum進行區間排序
也就是說rownum>=5 and rownum <=10是永遠也不會成立的。這就是所謂的TopN分析
例二：select * from emp where rownum<=5 order by sal desc;
該句執行後並不會得到預期的結果。雖然也會得到5條記錄，但並不是工資降序排列後的前5個值
它返回的是emp表中的前5行記錄，只不過顯示的時候是按照工資進行降序排列之後的效果
執行時會先對where條件進行過濾，過濾後得到了原表中的前5條記錄。然後再對表的前5條記錄排序並輸出
很顯然這並不是我們想要得到的。我們希望的是先排序，排好了順序之後再獲取前面的5行信息
但若寫成select * from emp order by sal desc where rownum<=5;的話，是不符合select語法的，會出錯
所以只能通過子查詢的方式在一條語句中結合rownum偽列來實現TopN查詢
分頁：在JavaWeb編程中，經常會遇到分頁顯示的問題。有時需要在某一頁顯示一個區間的記錄
比如顯示第21條到第30條記錄。在這種情況下，單純的TopN查詢顯然不能滿足要求
這時可以讓子查詢中的偽列變成一個真實存在的列，或者說讓它變成能夠進行比較運算的真實的列
述一：select rownum, a.* from (select * from emp order by sal desc) a;
這裡如果將a.*寫成*的話，就會出現缺失表達式的錯誤。而子查詢不是真實的表，所以只能靠它的別名
它的運行結果是顯示原emp中的所有記錄，而且還多出了一列ROWNUM的記錄，列值是從1到14的連續數字
此時的rownum還是虛的，仍然不能執行where rownum>=5 and rownum<=10的區間排序
因為rownum實際上是等於本次查詢14行記錄中的每一行記錄的偽列號
從第一行開始永遠等於1，如果不符合大於等於5的條件的話，第一行記錄就會被過濾掉了
而下一行記錄的偽列號還是從1開始的，便又會出現“例一”中的結果，所以此時仍不能進行區間排序
述二：這時可以給rownum起一個別名，如myno。然後再把剛才的整條語句作為一個子查詢
即select * from (select rownum myno, a.* from (select * from emp order by sal desc) a);
整個括號括起來的又充當了一個子查詢。這個子查詢會得到n+1條記錄，其中第一條記錄是myno字段
這時的myno就是一個實際存在的結果了。如果把子查詢當作一個真實的表，這個表中應該有n+1個字段
都是真實存在的字段，一個字段叫myno，其它的是原來emp中的所有字段
然後在語句中將查詢條件where myno>=5 and myno<=10寫在該語句的後面。整句就如“模板”中句子
這時再執行整條語句，得到的就是預期中的結果，即emp中工資排名在第5到第10名之間的員工信息
模板：select * from (select rownum myno, a.* from (select * from emp order by sal desc) a) where myno>=5 and myno<=10;
這就是在Oracle中利用TopN查詢實現分頁顯示效果的SQL語句，也可以把當前的語法格式當作一個模板來記住
子查詢可以當作是一個表，假想這個表是物理存在的，裡面有n+1個真實存在的字段，其中一個字段叫myno
如果這時把條件改成where rownum>=5 and rownum<=10，那麼執行結果是：未選定行或沒有選定任何內容
因為這個rownum指的根本不是子查詢中的返回的結果rownum，而是這一次主查詢中又得到的一個偽列
