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