常用函數(shù)哈多表查詢

Oracle深入詳解

回顧導(dǎo)入數(shù)據(jù)精確查詢模糊查詢排序本章目標(biāo)連接查詢交叉連接，內(nèi)連接，外連接；子查詢聚合函數(shù)及分組統(tǒng)計(jì)偽表及oracle常用函數(shù)sysdate/to_date()/to_char()；decode()；nvl()；sysdate；lpad()；連接查詢連接查詢分：交叉連接、內(nèi)連接、外連接；交叉連接不帶WHERE子句，它返回被連接的兩個表所有數(shù)據(jù)行的笛卡爾積，返回到結(jié)果集合中的數(shù)據(jù)行數(shù)等于第一個表中符合查詢條件的數(shù)據(jù)行數(shù)乘以第二個表中符合查詢條件的數(shù)據(jù)行數(shù)。內(nèi)連接查詢操作列出與連接條件匹配的數(shù)據(jù)行，它使用比較運(yùn)算符比較被連接列的列值。內(nèi)連接分三種：等值連接、自然連接、不等連接；最常見的是等值連接。外連接返回到查詢結(jié)果集中的不僅包含符合連接條件的行，而且還包括左表(左外連接或左連接)、右表(右外連接或右連接)或兩個邊接表(全外連接)中的所有數(shù)據(jù)行；外連接：左連接、右連接、全連接。交叉連接交叉連接是不帶WHERE子句的多表查詢，它返回被連接的兩個表所有數(shù)據(jù)行的笛卡爾積SELECT*

FROMT_A,T_B內(nèi)連接(等值連接)等值連接：在連接條件中使用等于號(=)運(yùn)算符比較被連接列的列值，其查詢結(jié)果中列出被連接表中的所有列，包括其中的重復(fù)列SELECT*

FROMT_AINNER

JOINT_BONT_A.SID=T_B.SIDSELECT*

FROMT_A,T_B

WHERET_A.SID=T_B.SID或按如下實(shí)現(xiàn)內(nèi)連接(自然連接)自然連接：在連接條件中使用等于(=)運(yùn)算符比較被連接列的列值，但它使用選擇列表指出查詢結(jié)果集合中所包括的列，并刪除連接表中的重復(fù)列SELECT

T_A.SID,T_A.ANAME,T_B.BNAME

FROMT_AINNER

JOINT_B

ONT_A.SID=T_B.SIDSELECTT_A.SID,T_A.ANAME,T_B.BNAME

FROMT_A,T_B

WHERET_A.SID=T_B.SID或按如下實(shí)現(xiàn)內(nèi)連接(不等連接)不等連接：在連接條件使用除等于運(yùn)算符以外的其它比較運(yùn)算符比較被連接的列的列值。這些運(yùn)算符包括>、>=、<=、<、!>、!<和<>SELECT*

FROMT_AINNER

JOINT_BONT_A.SID<>T_B.SIDSELECT*

FROMT_A,T_B

WHERET_A.SID<>T_B.SID或按如下實(shí)現(xiàn)外連接(左連接)返回包括左表中的所有記錄和右表中聯(lián)結(jié)字段相等的記錄；即左外連接就是在等值連接的基礎(chǔ)上加上主表中的未匹配數(shù)據(jù)SELECT*FROMT_ALEFT[OUTER]JOINT_BONT_A.sid=T_B.sidSELECT*FROMT_A,T_BWhereT_A.sid=T_B.sid(+)或按如下實(shí)現(xiàn)外連接(右連接)返回包括右表中的所有記錄和左表中聯(lián)結(jié)字段相等的記錄；即右外連接是在等值連接的基礎(chǔ)上加上被連接表的不匹配數(shù)據(jù)SELECT*FROMT_ARIGHT[OUTER]JOINT_BONT_A.sid=T_B.sidSELECT*FROMT_A,T_BWhereT_A.sid(+)=T_B.sid或按如下實(shí)現(xiàn)外連接(全連接)全外連接是在等值連接的基礎(chǔ)上將左表和右表的未匹配數(shù)據(jù)都加上SELECT*FROMT_AFULL[OUTER]JOINT_BONT_A.sid=T_B.sidSELECT*

FROMT_A,T_B

WhereT_A.sid(+)=T_B.sid

union

SELECT*

FROMT_A,T_B

WhereT_A.sid=T_B.sid(+)或按如下實(shí)現(xiàn)子查詢子查詢即一個查詢語句嵌到另一個查詢語句的子句；selectnid,sname,(selectsnamefromt_mc_typewherenid=nmaxid)asmax_name,(selectsnamefromt_mc_typewherenid=nminid)asmin_namefromt_mcselectnid,scuser,ntotalpricefromt_orderwherenidin(selectnorderidfromt_order_itemwheresmcnamelike'%聯(lián)想%')聚合函數(shù)聚合函數(shù)一般用于統(tǒng)計(jì)，常用如下:count(field)//記錄數(shù)avg(field)//平均值min(field)//最小值max(field)//最大值sum(field)//總和聚合函數(shù)的使用selectcount(*)as記錄數(shù)fromt_userselectavg(nage)as平均年齡fromt_userselectmax(nage)as最大年齡fromt_userselectmin(nage)as最小年齡fromt_userselectsum(nage)as年齡總和fromt_user分組查詢格式分組查詢通常用于統(tǒng)計(jì)，一般和聚合函數(shù)配合使用注：分組查詢有一個原則，就是select后面的所有列中沒有使用聚合函數(shù)的列，必須出現(xiàn)在groupby后面Select分組字段或聚合函數(shù)from

表where條件groupby

分組字段having

條件orderby

字段分組查詢格式分組統(tǒng)計(jì)selectssex,count(*)as總?cè)藬?shù)fromt_usergroupbyssexselectssex,slock,count(*)as總?cè)藬?shù)fromt_userwheresname<>'宇凡'groupbyssex,slockorderby總?cè)藬?shù)[或count(*)]分組統(tǒng)計(jì)分組統(tǒng)計(jì)并排序oracle函數(shù)oracle提供一系列用于執(zhí)行特定操作的函數(shù)；函數(shù)帶有一個或多個參數(shù)并返回一個值；oracle函數(shù)很多，我們介紹下面幾個：sysdate/to_date()/to_char()/to_number()decode()nvl()lpad()常用函數(shù)(日期)Selectsysdatefromdual;sysdateinsertintot_user(dregtime)values(to_date(‘2008-03-28’,’yyyy-mm-dd’));to_dateselectto_char(sysdate,’yyyy-mm-ddhh24:mi:ss’)fromdual;selectto_char(dregdate,’yyyy-mm-ddhh24:mi:ss’)fromt_user;to_char常用函數(shù)(decode)decode(field,k1,v1,k2,v2,vd);decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,缺省值)該函數(shù)的含義如下:IF條件=值1THENRETURN(翻譯值1)ELSIF條件=值2THENRETURN(翻譯值2)......ELSERETURN(缺省值)ENDIF

selectdecode(slock,'0','否','1','是','數(shù)據(jù)出錯')as是否凍結(jié)fromt_userselectdecode(ssex,'男','帥哥','女','美女','不明')as性別fromt_user常用函數(shù)(nvl)selectnvl(saddress,'未填寫')fromt_usernvlnvl(field,v1)當(dāng)field為空時,返回v1,否則返回原值常用函數(shù)(lpad/rpad)selectlpad(sid,5,'0')fromT_Alpadlpad(string1,length,[pad_string])其中string1是被操作的字符串；length是返回的字符串的長度，如果這個長度比原字符串長度要短，那么會按照length的值進(jìn)行截取；pad_string是填充到string1左邊的內(nèi)容，如果未設(shè)置該參數(shù)，則填充空格rpad和lpad類似selectrpad(sid,5,'0')fromT_Arpaddistinct關(guān)鍵字distinct用于消除查詢結(jié)果集中重復(fù)的記錄；select

distinctsbh,sxmfromT_E集合操作（后面為課后自己學(xué)習(xí)）集合操作符專門用于操作多條select語句的結(jié)果，包括：UNION,UNIONALL,INTERSECT,MINUS。當(dāng)使用集合操作符時，必須確保不同查詢的列個數(shù)和數(shù)據(jù)類型匹配。集合操作符具有以下注意事項(xiàng)：集合操作符不適用于LOB、VARRAY和嵌套表列。UNION、INTERSECT、MINUS操作符不使用于LONG列。如果選擇列表中包含有表達(dá)式或者函數(shù)，那么必須為表達(dá)式或者函數(shù)定義列別名。UNIONUNION(無重復(fù)并集)：當(dāng)執(zhí)行UNION時，自動去掉結(jié)果集中的重復(fù)行，并以第一列的結(jié)果進(jìn)行升序排序。select*fromT_A

union

select*fromT_Dselect*fromT_A

union

select*fromT_EUNIONALLUNIONALL(有重復(fù)并集)：不去掉重復(fù)行，并且不對結(jié)果集進(jìn)行排序。select*fromT_A

unionall

select*fromT_Dselect*fromT_A

unionall

select*fromT_EINTERSECTINTERSECT(交集)：取兩個結(jié)果集的交集，并且以第一列的結(jié)果進(jìn)行升序排列。select*fromT_A

intersect

select*from