中文字幕免费精品_亚洲视频自拍_亚洲综合国产激情另类一区_色综合咪咪久久

Oracle表連接

概述：SQL/Oracle使用表連接從多個表中查詢數(shù)據(jù)

格式：select 字段列表 from table1,table2 where table1.column1=table2.column2;

說明：當(dāng)被連接的多個表中存在同名字段時，必須在該字段前加上“table.”作為前綴

如果沒有限定where連接條件，否則就會出現(xiàn)笛卡爾集的不現(xiàn)實或沒有實用意義的結(jié)果

舉例：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標(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)：左外連接、右外連接、全外連接

補充：多表連接中可使用AND操作符增加查詢條件，使用表別名可以簡化查詢，使用表名(表別名)前綴可提高查詢效率

而連接n個表，則至少需要n-1個連接條件。如select a.ename, a.deptno, b.dname from emp a, dept b where a.deptno=b.deptno;

樣本：hr帳戶也是Oracle自帶的樣本帳戶，里面有很多樣本表，其數(shù)據(jù)量及復(fù)雜程度都要比scott強一些

hr帳戶缺省是鎖定的，可以在OEM中將其解鎖并設(shè)密碼。其中的employees表是scott中的emp表的增強版

另外還有departments和locations表的數(shù)據(jù)看起來都比較真實。這三個表通常用于復(fù)雜的多表連接查詢

等值連接(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)

概述：外連接運算符為(+)。使用外連接可以看到參與連接的某一方不滿足連接條件的記錄

傳統(tǒng)的外連接分為左外連接和右外連接兩種。實際開發(fā)中，外連接的使用頻率要比等值連接少一些

語法：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規(guī)范中，內(nèi)連接(Inner Join)只返回滿足連接條件的數(shù)據(jù)，而外連接還返回不滿足連接條件的行

分類：左外聯(lián)接(Left Outer Join)：即兩個表在連接過程中除返回滿足連接條件的行以外，還返回左表中不滿足條件的行

右外聯(lián)接(Right Outer Join)：即兩個表在連接過程中除返回滿足連接條件的行以外，還返回右表中不滿足條件的行

滿外聯(lián)接(Full Outer Join)：即兩個表在連接過程中除返回滿足連接條件的行以外，還返回兩個表中不滿足條件的行

舉例：select employee_id, last_name, salary, department_id, department_name

form employees left join departments using(department_id);--返回107行

自連接(Self join)

概述：自連接誒本質(zhì)就是把一個表當(dāng)作兩個表來使用，只是定義不同的別名而已

平時很少使用自連接，但有時確實只有采用自連接的方式才能解決某些問題

問題：如何查得每個員工及其上司的工號和姓名

舉例：select a.empno, a.ename, a.mgr, b.ename from emp a, emp b where a.mgr=b.empno;

SQL99連接語法

概述：SQL1999規(guī)范中規(guī)定的連接查詢語法。兩個以上的表進行連接時應(yīng)依次分別指定相臨的兩個表之間的連接條件

語法：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產(chǎn)生了一個笛卡爾集，其效果等同于在兩個表進行連接時未使用WHERE子句限定連接條件

舉例：select empno, ename, dname from emp a cross join dept b;--交叉連接實際上沒有太大的意義

自然連接(Natural join)

概述：Natural join基于兩個表中的全部同名列建立連接。從兩個表中選出同名列的值均對應(yīng)相等的所有行

如果兩個表中同名列的數(shù)據(jù)類型不同，則出錯。而且不允許在參照列上使用表名或別名作為前綴

舉例：select demno, ename, sal, deptno, dname from emp natural jon dept;

Using子句

概述：如果不希望參照被連接表的所有同名列進行等值連接，自然連接將無法滿足要求

可以在連接時使用USING子句來設(shè)置用于等值連接的列(參照列)名

同樣不允許在參照列上使用表名或別名作為前綴

舉例：select empno, ename, sal, deptno, dname form emp join dept using(deptno);

On子句

概述：如果要參照非同名的列進行等值連接，或想設(shè)置任意的連接條件，可以使用ON子句

舉例：select empno, ename, sal, emp.deptno, dname from emp join dept on(emp.deptno=dept.deptno);

子查詢(Sub Query)

概述：子查詢在主查詢前執(zhí)行一次，主查詢使用子查詢的結(jié)果。比如查詢所有比張三工資高的員工信息

子查詢分為單行子查詢(返回一行結(jié)果)和多行子查詢(返回多行結(jié)果)兩大類

語法：select 字段列表 form table where 表達(dá)式 operator(select 字段列表 from table);

比如select * from emp where sal>(select sal from emp where empno=7654);

注意：基于未知值的查詢應(yīng)考慮使用子查詢。子查詢必須包含在括號內(nèi)

建議將子查詢放在比較運算符的右側(cè)，以增強可讀性。除非進行Top—N分析，否則不要在子查詢中使用ORDER BY子句

對單行子查詢可以使用單行記錄比較運算符。而對多行子查詢則只能使用多行記錄比較運算符

空值：如果子查詢未返回任何行，則主查詢也不會返回任何結(jié)果

比如select * from where sal>(select sal from emp where empno=8888);--不會返回任何結(jié)果

多值：如果子查詢返回多行結(jié)果，則為多行子查詢，此時不允許對其使用單行記錄比較運算符

比如select * from emp where sal>(select avg(sal) from group by deptno);--非法

TopN查詢(TopN分析)

概述：即獲得按照某種規(guī)則排序之后的前n條的記錄。Oracle中通常采用子查詢的方式實現(xiàn)TOPN查詢

其實子查詢可以認(rèn)為是查到了一個臨時表，或沒有名字的臨時視圖

語法：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é)果中會隱含的增加一個字段rownum，即偽列。rownum用起來很靈活，但也很容易出錯

rownum偽列并不是數(shù)據(jù)表中或者子查詢的虛擬表中真實存在的列，它只是查詢結(jié)果中的一個偽列

它標(biāo)記的是符合查詢條件的結(jié)果的編號，第一條記錄的rownum值為1，第二條記錄的rownum值為2

可以理解為，符合查詢條件的第一行記錄編號為1，符合查詢條件的第二行記錄編號為2

例一：select * from emp where rownum>=5;--它執(zhí)行后的結(jié)果是沒有返回值

執(zhí)行時先取出結(jié)果集中的，或者說是數(shù)據(jù)表中的第一條記錄，并標(biāo)記第一條記錄的編號為1

判斷后得知1小于5，不符合條件。隨后便過濾掉這條記錄了，接著判斷下一條記錄是不是符合條件

于是就又取出下一條記錄，下一條記錄的rownum還是從1開始。而rownum永遠(yuǎn)是從1開始的，結(jié)果可想而知

接著的下一條記錄的編號還是1。即照此情形下去，記錄的編號永遠(yuǎn)不會符合大于等于5的條件

也就是說在這條SQL語句的環(huán)境下，rownum永遠(yuǎn)也不會大于等于5。所以就不能指望用rownum進行區(qū)間排序

也就是說rownum>=5 and rownum <=10是永遠(yuǎn)也不會成立的。這就是所謂的TopN分析

例二：select * from emp where rownum<=5 order by sal desc;

該句執(zhí)行后并不會得到預(yù)期的結(jié)果。雖然也會得到5條記錄，但并不是工資降序排列后的前5個值

它返回的是emp表中的前5行記錄，只不過顯示的時候是按照工資進行降序排列之后的效果

執(zhí)行時會先對where條件進行過濾，過濾后得到了原表中的前5條記錄。然后再對表的前5條記錄排序并輸出

很顯然這并不是我們想要得到的。我們希望的是先排序，排好了順序之后再獲取前面的5行信息

但若寫成select * from emp order by sal desc where rownum<=5;的話，是不符合select語法的，會出錯

所以只能通過子查詢的方式在一條語句中結(jié)合rownum偽列來實現(xiàn)TopN查詢

分頁：在JavaWeb編程中，經(jīng)常會遇到分頁顯示的問題。有時需要在某一頁顯示一個區(qū)間的記錄

比如顯示第21條到第30條記錄。在這種情況下，單純的TopN查詢顯然不能滿足要求

這時可以讓子查詢中的偽列變成一個真實存在的列，或者說讓它變成能夠進行比較運算的真實的列

述一：select rownum, a.* from (select * from emp order by sal desc) a;

這里如果將a.*寫成*的話，就會出現(xiàn)缺失表達(dá)式的錯誤。而子查詢不是真實的表，所以只能靠它的別名

它的運行結(jié)果是顯示原emp中的所有記錄，而且還多出了一列ROWNUM的記錄，列值是從1到14的連續(xù)數(shù)字

此時的rownum還是虛的，仍然不能執(zhí)行where rownum>=5 and rownum<=10的區(qū)間排序

因為rownum實際上是等于本次查詢14行記錄中的每一行記錄的偽列號

從第一行開始永遠(yuǎn)等于1，如果不符合大于等于5的條件的話，第一行記錄就會被過濾掉了

而下一行記錄的偽列號還是從1開始的，便又會出現(xiàn)“例一”中的結(jié)果，所以此時仍不能進行區(qū)間排序

述二：這時可以給rownum起一個別名，如myno。然后再把剛才的整條語句作為一個子查詢

即select * from (select rownum myno, a.* from (select * from emp order by sal desc) a);

整個括號括起來的又充當(dāng)了一個子查詢。這個子查詢會得到n+1條記錄，其中第一條記錄是myno字段

這時的myno就是一個實際存在的結(jié)果了。如果把子查詢當(dāng)作一個真實的表，這個表中應(yīng)該有n+1個字段

都是真實存在的字段，一個字段叫myno，其它的是原來emp中的所有字段

然后在語句中將查詢條件where myno>=5 and myno<=10寫在該語句的后面。整句就如“模板”中句子

這時再執(zhí)行整條語句，得到的就是預(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查詢實現(xiàn)分頁顯示效果的SQL語句，也可以把當(dāng)前的語法格式當(dāng)作一個模板來記住

子查詢可以當(dāng)作是一個表，假想這個表是物理存在的，里面有n+1個真實存在的字段，其中一個字段叫myno

如果這時把條件改成where rownum>=5 and rownum<=10，那么執(zhí)行結(jié)果是：未選定行或沒有選定任何內(nèi)容

因為這個rownum指的根本不是子查詢中的返回的結(jié)果rownum，而是這一次主查詢中又得到的一個偽列

更多信息請查看IT技術(shù)專欄