oracle項目實戰(zhàn)教學錄像課堂筆記sql語法

1、課程名稱：SQL語2、知、上次課程的主要知識1、關(guān)于Oracle超級管理員：sys普通管理員：systemSELECT[DISTINCT*|[SELECT[DISTINCT*|[別名],[別名FROM[別名][WHERE條件(s)][ORDERBY[ASC|DESC],[ASC|DESCFROMWHEREBETWEEN..ANDIN、ISNULL、LIKESELECTORDERBY子句：針對于數(shù)據(jù)的排序操作，必須放在整個SQLSELECT、本次預(yù)計講解的知識1、單行函數(shù)的使用；2、多表查詢的使用；3、統(tǒng)計查詢的使用。3、具體內(nèi)、單行函數(shù)（重點SQLSQL這個標準是一定會共同遵守、字符串函字符串函數(shù)指的是針對于字符串數(shù)據(jù)進行的處理，常見的字符串函數(shù)有：UPPER()、LOWER()、INITCAP()、LENGTH() 、轉(zhuǎn)轉(zhuǎn)大寫：UPPER(|列轉(zhuǎn)小寫：LOWER(|列)；范例：驗證UPPER()、LOWER()SELECTUPPER('oSELECTUPPER('o!!!'), o!!!')FROMdualOracle中專門提供了一張dual的虛擬表，直接利用此表作為數(shù)據(jù)的查詢來源即可。SELECT*FROMempWHEREename=UPPER('&inputname')SELECT*FROMempWHEREename=UPPER('&inputname')2、開頭首字母大SELECTINITCAP(ename)FROMemp語法：INITCAP(|列)SELECTINITCAP(ename)FROMemp3、計算字符串長語法：LENGTH(|列SELECTename,LENGTH(ename)FROMemp范例：查詢出每一位雇員的，SELECTename,LENGTH(ename)FROMempSELECT*FROMempWHERELENGTH(ename)=5SELECT*FROMempWHERELENGTH(ename)=54、字符SELECT oworld.','l','_')FROMdual語法：字符串RE CE(字符串|列,要替換的舊內(nèi)容,要顯示的新內(nèi)容)SELECT oworld.','l','_')FROMdual5、字符語法，從指定位置截取到結(jié)尾：SUBSTR(|,截取開始點SELECT oworld',7), oworld',0,6), oworld',1,6)FROMSELECT oworld',7), oworld',0,6), oworld',1,6)FROMdualSELECTename,SUBSTR(ename,1,3)FROMemp范例：查詢出每一位雇SELECTename,SUBSTR(ename,1,3)FROMemp范例：查詢出每一位雇員的后三個字SELECTename,SUBSTR(ename,LENGTH(ename)-2)FROMempSELECTename,SUBSTR(ename,LENGTH(ename)-2)FROMempSELECTename,SUBSTR(ename,-3)FROMempSELECTename,SUBSTR(ename,-3)FROMemp面試題：OracleSUBSTR()01SUBSTR()01Oracle10，也會自動將其1，或者設(shè)置為負數(shù)，由后開始截取。6、字符SELECT o','x'), o','l'),o','o')o','l',SELECT o','x'), o','l'),o','o')o','l',4)FROMdual、數(shù)值函1、四舍五ROUND(197.528793),ROUND(197.528793,2),FROMdual語法：ROUND(|ROUND(197.528793),ROUND(197.528793,2),FROMdual2、截取語法：TRUNC(|[,保留小數(shù)位])；范例：TRUNC()TRUNC(197.528793),TRUNC(197.528793,2),FROMdual3、求模（求余數(shù)SELECTMOD(10,3)FROMdual語法：MOD(|,|SELECTMOD(10,3)FROMdual、日期函期時間是什么，或者理解為如何確定一個日期的開始點。為了解決取得當前日期的問題，在Oracle中專門提供了一個SYSDATE的偽列（可以在任何表中查詢?？梢灾苯硬樵兇肆羞M行操作。范例：如果要想確定當前日期，還是使用dualSELECTSELECTSYSDATEFROMdual那么取得了當前日期時間之后，實際上就可以利用以下的三個進行計算操作日期+數(shù)字=日期–數(shù)字= 畢業(yè)的日子 FROMdual 畢業(yè)的日子 FROMdual除了以上的基本之外，還提供了四個日期的操作函數(shù)：MONTHS_BETWEEN()、ADD_MONTHS()、NEXT_DAY()、1、計算兩個日期之間語法：MONTHS_BETWEEN(1|,2|列)；2、增加指定月之后的日語法：ADD_MONTHS(|3、求出下一個指定的一周時間數(shù)日語法：NEXT_DAY(|一周時間數(shù)')范例：')4、求出一個指定日期所在月的最后一語法：LAST_DAY(的函數(shù)，但是對于天數(shù)，只有一種求法“日期–、轉(zhuǎn)換函Oracle數(shù)據(jù)庫之中的三類數(shù)據(jù)類型：數(shù)字、字符串、日期，那么這三類數(shù)據(jù)類型之間也是1、將日期或數(shù)字變?yōu)樽址ǜ袷交僮髡Z法：TO_CHAR(||,轉(zhuǎn)換格式)；TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy-mm-ddhh24:mi:ss'),FROMdualTO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy-mm-ddhh24:mi:ss'),FROMdual范例：拆分TO_CHAR(SYSDATE,'yyyy')日,')FROMdualSELECTSELECTTO_CHAR(23479824789,'999,999,999,999,999')FROMdual所有使用TO_CHAR()2、將字符串格式化為日期型數(shù)語法：TO_DATE(|,轉(zhuǎn)換格式)；SELECTSELECTTO_DATE('2011-11-1111:11:11','yyyy-mm-ddhh:mi:ss')FROMdual3、將字符串變?yōu)閿?shù)字（根本沒用SELECT'1''2'FROMdual;SELECTSELECT'1''2'FROMdual;SELECTTO_NUMBER('1')+TO_NUMBER('2')FROMdual實際上針對數(shù)據(jù)類型的轉(zhuǎn)換，在Oracle之中是用默認支持的式，例如：最早的時候字符串可以變?yōu)槿掌?，字、通用函?shù)（特色函數(shù)1、針對NULL處語法：NVL(,默認值SELECT m)*12FROMSELECT m)*12FROMempNULL0NVL()SELECT m,(sal+NVL(comm,0))*12SELECT m,(sal+NVL(comm,0))*12,NVL(comm,0)FROMemp語法：DECODE(|,判斷值1,顯示1,2,2SELECTempno,ename,jobDECODE(job,'CLERK'辦事員SALESMAN銷售'MANAGER'經(jīng)理FROMempSELECTempno,ename,jobDECODE(job,'CLERK'辦事員SALESMAN銷售'MANAGER'經(jīng)理FROMempYST分析員', Oracle、思考13在emphiredate，每一個雇員有自己的雇傭日期，則每個雇傭日期所在月的最后一–=FROMemp范例：最終SELECTSELECTFROM2、找出早于1212年前雇傭，肯定要求年，求年的最精確做法是按照月/12兩個日期之間的月使用MONTHS_BETWEEN()SELECT*FROMSELECT*FROM3、找出在（任何年份的）2SELECT*SELECT*FROMempWHERETO_CHAR(hiredate,'mm')=2SELECT*FROMempWHERETO_CHAR(hiredate,'mm')='02'4、顯示滿10年服務(wù)年限的員工的和受雇日期SELECTSELECTename,hiredateFROM5、顯示員工的和受雇日期，根據(jù)其服務(wù)年限，將最老的員工排在最前面SELECTename,hiredateFROMSELECTename,hiredateFROMORDERBYhiredateASC要想求出年份和月份，使用TO_CHAR()SELECT子句之中為查詢的結(jié)果設(shè)置一個別名（月FROMempORDERFROMempORDERBYmonths,year730SELECTename,TRUNC(sal/30)SELECTename,TRUNC(sal/30)FROMemp8、以年月日的方式顯示所有員工的服務(wù)年限（選做，難度高81（1981-2013-05-21，那么這位員工到今天為止的雇傭年限是：32年，01月，19天。FROMemp;FROMemp;FROMemp|-現(xiàn)在唯一知道計算天數(shù)的操作“日期1–日期2=天數(shù)”，那么現(xiàn)在的問題是這兩個日期怎么定|-1，應(yīng)該是今天的日期，使用SYSDATE求出，SYSDATE–2=（31）FROMemp8題，不屬于掌握范圍之內(nèi)，如果有能力掌握。、多表查詢（重點、多表查詢的基本語SELECT[DISTINCT*|SELECT[DISTINCT*|[別名],[別名FROM數(shù)據(jù)[別名],表名稱[別名],表名稱[別名[WHERE條件[ORDERBY[ASC|DESC],[ASC|DESCSELECTCOUNT(*)FROMemp范例SELECTCOUNT(*)FROMemp范例：dept4SELECTSELECTCOUNT(*)FROMdeptSELECT*FROMSELECT*FROMemp,dept565614*4條記錄，這樣的操作在數(shù)據(jù)庫之中稱為積，或者將其稱為積。那么此時的情況如下。deptno來解決，畢竟empdeptnodeptdeptno定義的范圍是一致的。SELECTFROMWHEREemp.deptno=dept.deptnoSELECTFROMWHEREemp.deptno=dept.deptnoSELECTFROMempe,deptWHERESELECTFROMempe,deptWHEREe.deptno=d.deptno任何情況下，只要是存在了多表查詢的要求，那么數(shù)據(jù)表之中一定都會永恒的存在消除積的字段。范例：要求查詢出每一位雇員的編號、、職位、基本工資、部門名稱、部門位|-emp表：雇員的編號、、職位、基本工資|dept|-雇員和部門：emp.deptnoSELECTFROMempe第一步：查詢出每一位雇員的編號、、職位、基本工SELECTFROMempe第二步：查詢部門信息，F(xiàn)ROMdeptWHERE子句之中進行字段的關(guān)聯(lián)，以消除積的顯示，此處使用的是deptno字段完成。FROMempe,deptdWHEREe.deptno=d.deptno范例：查詢出每一位雇員的編號、、基本工資、職位、雇傭日期，工資所在公司FROMempe,deptdWHEREe.deptno=d.deptno|-emp表：雇員的編號、、基本工資、職位、雇傭日期|salgradeSELECTFROMempe|-雇員和工資等級：emp.salBETWEENsalgrade.losalANDsalgrade.hisal。SELECTFROMempeFROMempe,salgradesWHEREe.salBETWEENs.losalANDs.hisal第二步：查詢出工資等級，在FROM子句之中增加一個salgradeFROMempe,salgradesWHEREe.salBETWEENs.losalANDs.hisal范例：查詢出每位雇員的編號、、職位、工資、雇傭日期、部門名稱、部門位置、工資等|-emp表：雇員的編號、、職位、工資、雇傭日期|dept|salgradeAND|-SELECTFROMempe|-雇員和工資等級：emp.salBETWEENsalgrade.losalANDsalgrade.hisal；SELECTFROMempeFROMempe,deptdWHEREe.deptno=d.deptno第二步：增加deptFROMempe,deptdWHEREe.deptno=d.deptno第三步：增加salgradeFROMempe,deptd,salgradesWHEREe.deptno=d.deptnoANDe.salBETWEENs.losalANDs.hisal所有的查詢都不可能的出現(xiàn)結(jié)果，都必須采用分步的方式歸類完成、表的連對于數(shù)據(jù)表的連接，在SQL外連接：左（外）連接、右（外）連接、全（外）連接INSERTINTOINSERTINTOOracle中提供了一種非常簡便的操作符號“(+)”進行左外連接：WHERE=字段SELECTFROMempe,deptWHERESELECTFROMempe,deptWHEREe.deptno=d.deptno(+)SELECTFROMempe,deptWHEREe.deptno(+)=d.deptno范例：操作SELECTFROMempe,deptWHEREe.deptno(+)=d.deptno在emp表之中現(xiàn)在只有mgr字段沒有使用到了，此字段表示的是一個雇員所對應(yīng)的編號|-emp表：雇員的編號、、職位|-emp（）表：的、職位SELECTFROMempe|-雇員和：emp.mgr=memp.empno；SELECTFROMempeFROMempe,empmWHEREe.mgr=m.empno第二步：找到雇員的，增加一個關(guān)聯(lián)條件，F(xiàn)ROMempe,empmWHEREe.mgr=m.empno第三步：發(fā)現(xiàn)7839的雇員沒有，所以以上的等值條件就，那么此時需要它全部顯示就必須采用外連接FROMempe,empmWHEREe.mgr=m.empno(+)在進行多表查詢的時候關(guān)鍵性的問題就只有兩個：多表查詢時需要使用的數(shù)據(jù)表，另外一個消除積的條件。SELECT*FROMSELECT*FROMN行數(shù)據(jù)。SELECT*FROMempWHERESELECT*FROMempWHEREempnoNOTIN(null)bug，為了避免這種bug，所以才增加了此限制。問題2：多表查詢的性能問A80W條數(shù)據(jù)，表B20W條數(shù)據(jù)，那么這兩張表連接之后的數(shù)據(jù)、SQL:1999語法支FROMtable1[CROSSJOINtable2]|[NATURALJOIN[JOINFROMtable1[CROSSJOINtable2]|[NATURALJOIN[JOINtable2[JOINtable2[LEFT|RIGHT|FULLOUTERJOINtable21、交叉連接，語法如下SELECTSELECTFROMtable1CROSSJOINtable2SELECT*FROMempCROSSJOINdept范SELECT*FROMempCROSSJOINdept2、自然連接，語法如下SELECTSELECTFROMtable1NATURALJOINtable2SELECT*FROMempNATURALJOINdept范例：驗證自然連接，就相當于使用了等值連接，并且SELECT*FROMempNATURALJOINdept3、USING子句，語法SELECTSELECTFROMtable1JOINtable2USING列名稱SELECT*FROMempSELECT*FROMempJOINdeptUSING4、ON子句，語法如下SELECTSELECTFROMtable1JOINtable2ONSELECT*FROMempSELECT*FROMempeJOINdeptdON5、外連接，語法如下SELECTSELECTFROM[LEFT|RIGHT|FULLOUTERJOINSELECT*FROMempeSELECT*FROMempeFULLJOINdeptdONSQL:1999、查詢結(jié)果連SELECT[DISTINCT*|[別名],SELECT[DISTINCT*|[別名],[別名FROM[別名],[別名],[別名[WHERE條件(s)][ORDERBY[ASC|DESC],[ASC|DESC[UNION|UNIONALL|INTERSECT|SELECT[DISTINCT*|[別名],[別名FROM[別名],[別名],[別名[WHERE條件(s)][ORDERBY[ASC|DESC],[ASC|DESC[UNION|UNIONALL|INTERSECT|SELECT[DISTINCT*|[別名],[別名FROM[別名],[別名],[別名[WHERE條件(s)][ORDER[ORDERBY[ASC|DESC],[ASC|DESCSELECT*FROMempWHERESELECT*FROMempWHERESELECT*FROMemp范例：驗證UNIONALLSELECTSELECT*FROMempWHEREUNIONSELECT*FROMempSELECT*FROMempSELECT*FROMempWHERESELECT*FROMemp范例：驗證MINUSSELECTSELECT*FROMSELECT*FROMempWHEREdeptno=10SELECTename,job,hiredateSELECTename,job,hiredateFROMemp mFROMempWHEREdeptno=10、思考1、列出所有員工的編號、及其直接上級的編號、，顯示的結(jié)果按年工資的降序排列|-emp表：編號、|-emp表：上級的編號、、年工資（不顯示，作為排序條件SELECTFROMempe|-雇員和：emp.mgr=SELECTFROMempeFROMempe,empmWHEREORDERBYFROMempe,empmWHEREORDERBYm.sal*12DESC第三步：缺少KINGFROMempe,empmWHEREORDERORDERBYm.sal*12