oracle系列教程三 oracle復(fù)雜sql語句

《oracle系列教程三 oracle復(fù)雜sql語句》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫(kù)。

1、oracle復(fù)雜SQL語句一、sql語句的執(zhí)行順序1)、常見的select、from、where的順序：1,from2,where3,select2)、完整的select、from、where、groupby、having、orderby的順序：1,from2,where3,groupby4,having5,select6,orderby二、selectcasewhen的的使用1)、語法：casewhen條件1thenaction1when條件2thenaction2when條件3thenaction3

2、...elseactionnend[case]eg、selectcasewhensubstr(to_char(SYSDATE,'yyyymmdd'),5,2)='01'then'一月份'whensubstr(to_char(SYSDATE,'yyyymmdd'),5,2)='02'then'二月份'whensubstr(to_char(SYSDATE,'yyyymmdd'),5,2)='03'then'三月份'whensubstr(to_char(SYSDATE,'yyyymmdd'),5,2)='04

3、'then'四月份'else'other'endcasefromdual;2)、語法：語法：caseselectorwhenvalue1thenaction1whenvalue2thenaction2whenvalue3thenaction3...elseactionnend[case]eg、SELECTCASEsubstr(to_char(SYSDATE,'yyyymmdd'),5,2)WHEN'01'THEN'一月份'WHEN'02'THEN'二月份'WHEN'03'THEN'三月份'WHEN'04

4、'THEN'四月份'ELSE'other'ENDCASEFROMDUAL;3)、案例createtable成績(jī)(snonumber,kmvarchar2(10),scorenumber,gradechar(6));insertinto成績(jī)values(1,'語文',65,null);insertinto成績(jī)values(2,'數(shù)學(xué)',76,null);insertinto成績(jī)values(3,'英語',86,null);insertinto成績(jī)values(4,'語文',94,null);i、SELEC

5、TSNO,CASEWHENSCORE>=90THEN'優(yōu)秀'WHENSCORE>=80THEN'良好'WHENSCORE>=70THEN'中等'WHENSCORE>=60THEN'及格'ELSE'不及格'ENDGRADE–GRADE別名，END后面不能包含CASE關(guān)鍵字，否則報(bào)錯(cuò)FROM成績(jī)查詢結(jié)果如下：ii、把每個(gè)學(xué)生的grade列，用相應(yīng)的等級(jí)來更新。--UPDATE成績(jī)setgrade=(SELECTgradeFROM(xxxxxx)awhere成績(jī).sno=a.sno);update成績(jī)setg

6、rade=(selectgradeFROM(SELECTSNO,CASEWHENSCORE>=90THEN'優(yōu)秀'WHENSCORE>=80THEN'良好'WHENSCORE>=70THEN'中等'WHENSCORE>=60THEN'及格'ELSE'不及格'ENDGRADEFROM成績(jī))awhere成績(jī).sno=a.sno);三、復(fù)雜更新語句的使用表T1里有a,b,c...N個(gè)字段，表T2里有a,b,c三個(gè)字段，然后想在T1中"c"與表T2中"c"相同的情況下從表T2中將a,b覆蓋表T1中的a,b怎么做

7、？i、createtableT1(aint,bint,cint,dint,eint);createtableT2(aint,bint,cint);insertintoT1values(1,2,3,4,5);insertintoT1values(10,20,3,4,5);insertintoT1values(10,20,4,40,50);insertintoT2values(-1,-1,3);insertintoT2values(-2,-2,4);ii、UPDATET1SETA=(SELECTAFROMT

8、2WHERET1.C=T2.C),B=(SELECTBFROMT2WHERET1.C=T2.C)WHERET1.CIN(SELECTCFROMT2);四、DECODE函數(shù)的使用i、selectdecode(x,1,'xis1',2,'xis2','others')fromdual當(dāng)x等于1時(shí)，則返回‘xis1’。當(dāng)x等于2時(shí)，則返回‘xis2’。否則，返回‘others’。eg、createtablestudent(idnumber,nam