第三章SQL和OracleSQL函數(shù)

第三章SQL和OracleSQL函數(shù)本章要點SQL數(shù)據(jù)類型OracleSQL函數(shù)SQL是StructuredQueryLanguage（結構化查詢語言）的首字母縮寫詞SQL是數(shù)據(jù)庫語言，Oracle使用該語言存儲和檢索信息表是主要的數(shù)據(jù)庫對象，用于存儲數(shù)據(jù)通過SQL可以實現(xiàn)與Oracle服務器的通信SELECTenameFROMEmp;發(fā)送SQL查詢Oracle服務器enameBLAKESMITHALLENDAVIDMARTIN發(fā)送命令輸出到用戶端用戶wang3.1SQL簡介SQL支持下列類別的命令：數(shù)據(jù)定義語言（DDL）數(shù)據(jù)操縱語言（DML）事務控制語言（TCL）數(shù)據(jù)控制語言（DCL）數(shù)據(jù)定義語言CREATEALTERDROP數(shù)據(jù)操縱語言INSERTSELECTDELETEUPDATE事務控制語言COMMITSAVEPOINTROLLBACK數(shù)據(jù)控制語言GRANTREVOKE3.1SQL簡介字符數(shù)據(jù)類型CHARVARCHAR2LONG當需要固定長度的字符串時，使用CHAR數(shù)據(jù)類型。CHAR數(shù)據(jù)類型存儲字母數(shù)字值。CHAR數(shù)據(jù)類型的列長度可以是1到2000個字節(jié)。VARCHAR2數(shù)據(jù)類型支持可變長度字符串VARCHAR2數(shù)據(jù)類型存儲字母數(shù)字值VARCHAR2數(shù)據(jù)類型的大小在1至4000個字節(jié)范圍內LONG數(shù)據(jù)類型存儲可變長度字符數(shù)據(jù)LONG數(shù)據(jù)類型最多能存儲2GB(2千兆字節(jié))基本使用CLOB類型3.2Oracle數(shù)據(jù)類型HelloWorld11數(shù)據(jù)長度字節(jié)存儲在一個VARCHAR2(80)中的HelloWorldHelloWorld80…數(shù)據(jù)長度字節(jié)69空格存儲在一個CHAR(80)中的HelloWorld數(shù)值數(shù)據(jù)類型可以存儲整數(shù)、浮點數(shù)和實數(shù)最高精度為38位數(shù)值數(shù)據(jù)類型的聲明語法：NUMBER[(p[,s])]P表示精度，S表示小數(shù)點的位數(shù)日期時間數(shù)據(jù)類型存儲日期和時間值，包括年、月、日，小時、分鐘、秒主要的日期時間類型有：DATE-存儲日期和時間部分，精確到整個的秒TIMESTAMP-存儲日期、時間和時區(qū)信息，秒值精確到小數(shù)點后6位3.2Oracle數(shù)據(jù)類型

NUMBER類型的示例：格式輸入的數(shù)字實際的存儲NUMBER1234.5671234.567NUMBER(5,2)123.4567123.46NUMBER(4,2)12345.67超過了所指定精度，不能存儲日期類型的示例：NUMBER(5,2)：可以存儲介于-999.99~999.99之間所有值。不要依賴于一個默認日期格式！如：insertintot(date_column)values(‘01/02/03’);

較好的做法：insertintot(date_column)values(to_date(‘01/02/03’,‘DD/MM/YY’));更好的做法：insertintot(date_column)values(to_date(‘01/02/2003’,‘DD/MM/YYYY’));insertintoemp(DOB)values(to_date(‘01/02/10’,‘DD/MM/YY’));你能確定2010還是1910？向日期增加/減時間時間單位操作描述N秒DATE+n/86400一天86400秒相當于n/24/60/60DATE+NUMTODSINTERVAL(n,’second’)Numtodsinterval(n,‘second’)

日/秒數(shù)間隔函數(shù)時間單位操作描述N分鐘DATE+n/1440一天1440分鐘相當于n/24/60DATE+NUMTODSINTERVAL(n,’minute’)Numtodsinterval(n,‘minute’)時間單位操作描述N小時DATE+n/24一天24小時DATE+NUMTODSINTERVAL(n,’hour’)Numtodsinterval(n,‘hour’)時間單位操作描述N天DATE+n增加或減去n天時間單位操作描述N周DATE+7*n一周7天時間單位操作描述N月ADD_MONTHSADD_MONTHS(DATE,n)DATE+NUMTOYMINTERVAL(n,’month’)numtoyminterval(n,‘month’)時間單位操作描述N年ADD_MONTHSADD_MONTHS(DATE,12*n)DATE+NUMTOYMINTERVAL(n,’year’)numtoyminterval(n,‘year’)建議：1.使用NUMTODSINTERVAL函數(shù)來增加小時、分鐘和秒；2.使用ADD_MONTHS函數(shù)來增加月和年。

日期期增增加加示示例例：：altersessionsetnls_date_format=‘‘yyyy-mm-ddhh24:mi:ss’’;selectdt,add_months(dt,1)from(selectto_date(‘‘2001-02-28’’,’’yyyy-mm-dd’’)dtfromdual);selectdt,add_months(dt,1)from(selectto_date(‘‘2000-02-29’’,’’yyyy-mm-dd’’)dtfromdual);ADD_MONTHS函數(shù)數(shù)專專門門處處理理月月末末日日期期沒沒問問題題??！selectdt,dt+numtoyminterval(1,’’month’’)from(selectto_date(‘‘2000-01-30’’,’’yyyy-mm-dd’’)dtfromdual);Numtoyminterval函數(shù)數(shù)進進行行下下面面操操作作時時出出錯錯?。。。。。XTRACT函數(shù)數(shù)。格式式：：EXTRACT(fmtfromSYSDATE[SYSTIMESTAMP])fmt:YEAR、MONTH、DAY、HOUR、MINUTE、SECOND其中中HOUR、MINUTE、SECOND必須須與與TIMESTAMP類型型SelectSYSDATE““date””,EXTRACT(YEARfromSYSDATE)““year””,EXTRACT(MONTHfromSYSDATE)““month””,EXTRACT(DAYfromSYSDATE)““day””,EXTRACT(HOURfromSYSTIMESTAMP)““hour””,EXTRACT(MINUTEfromSYSTIMESTAMP)““minute””,EXTRACT(SECONDfromSYSTIMESTAMP)““second””,fromdual;請注意HOUR值！Oracle中偽列就就像一個個表列，，但是它它并沒有有存儲在在表中偽列可以以從表中中查詢，，但不能能插入、、更新和和刪除它它們的值值常用的偽偽列有ROWID和ROWNUMROWID是表中行行的存儲儲地址，，該地址址可以唯唯一地標標識數(shù)據(jù)據(jù)庫中的的一行，，可以使使用ROWID偽列快速速地定位位表中的的一行ROWNUM是查詢返返回的結結果集中中行的序序號，可可以使用用它來限限制查詢詢返回的的行數(shù)偽列SQL>SELECTROWID,ENAMEFROMEMPWHERESAL>2000;ROWIDAAAMgzAAEAAAAAgAADAAAMgzAAEAAAAAgAAFAAAMgzAAEAAAAAgAAGAAAMgzAAEAAAAAgAAHAAAMgzAAEAAAAAgAAIAAAMgzAAEAAAAAgAAMENAMEJONESBLAKECLARKSCOTTKINGFORDSQL>SELECTROWNUM,ENAME,JOB,SALFROMEMPWHEREROWNUM<=5;ROWNUMENAMEJOBSAL12345SMITHALLENWARDJONESMARTINCLERKSALESMANSALESMANMANAGERSALESMAN8001600125029751250ROWID：ROWNUM：3.2Oracle數(shù)據(jù)類型型-LOB類型Oracle支持4種類型的的LOB(largeobject)。CLOB：字符LOB。這種類類型用于于存儲大大量的文文本信息息，如XML或者純文文本。需要進行行字符集集轉化。。NCLOB：另一種種字符LOB。采用的的是數(shù)據(jù)據(jù)庫的國國家字符符集，而而不是數(shù)數(shù)據(jù)庫默默認的BLOB：二進制制LOB。存儲大大量的二二進制信信息，如如字處理理文檔，，圖像和和你能想想到的任何其其他數(shù)據(jù)據(jù)。BFILE：二進制制文件LOB。存儲的的只是操操作系統(tǒng)統(tǒng)中某個個文件的的一個指指針。3.3SQL函數(shù)Oracle提供一系系列用于于執(zhí)行特特定操作作的函數(shù)數(shù)SQL函數(shù)帶有有一個或或多個參參數(shù)并返返回一個個值以下是SQL函數(shù)的分分類：SQL函數(shù)單行函數(shù)數(shù)分析函數(shù)數(shù)分組函數(shù)數(shù)單行函數(shù)數(shù)分類單行函數(shù)數(shù)對于從從表中查查詢的每每一行只只返回一一個值可以出現(xiàn)現(xiàn)在SELECT子句中和和WHERE子句中單行函數(shù)數(shù)可以大大致劃分分為：日期函數(shù)數(shù)數(shù)字函數(shù)數(shù)字符函數(shù)數(shù)轉換函數(shù)數(shù)其他函數(shù)數(shù)日期函數(shù)數(shù)對日期期值進行行運算，，并生成成日期數(shù)數(shù)據(jù)類型型或數(shù)值值類型的的結果日期函數(shù)數(shù)包括：：ADD_MONTHSMONTHS_BETWEENLAST_DAYROUNDNEXT_DAYTRUNCEXTRACT日期函數(shù)數(shù)字符函數(shù)數(shù)2-1

函數(shù)

輸入

輸出Initcap(char)Selectinitcap(‘hello’)fromdual;HelloLower(char)Selectlower(‘FUN’)fromdual;funUpper(char)Selectupper(‘sun’)fromdual;SUNLtrim(char,set)Selectltrim(‘xyzadams’,’xyz’)fromdual;adamsRtrim(char,set)Selectrtrim(‘xyzadams’,’ams’)fromdual;xyzadTranslate(char,from,to)Selecttranslate(‘jack’,’j’,’b’)fromdual;backReplace(char,searchstring,[repstring])Selectreplace(‘jackandjue’,’j’,’bl’)fromdual;blackandblueInstr(char,m,n)Selectinstr(‘worldwide’,’d’)fromdual;5Substr(char,m,n)Selectsubstr(‘a(chǎn)bcdefg’,3,2)fromdual;cdConcat(expr1,expr2)Selectconcat(‘Hello’,’world’)fromdual;Helloworld字符函數(shù)數(shù)字符函數(shù)數(shù)接受字字符輸入入并返回回字符或或數(shù)值字符函數(shù)數(shù)2-2SQL>SELECTCHR(67)FROMdual;以下是一一些其它它的字符符函數(shù)：：CHR和ASCIILPAD和RPADTRIMLENGTHDECODESQL>SELECTLPAD(‘function’,15,’=’’)FROMdual;SQL>SELECTTRIM(9from9999876789999)FROMdual;SQL>SELECTLENGTH('frances')FROMdual;SQL>SELECTvencode,DECODE(venname,'frances','Francis')nameFROMvendor_masterWHEREvencode='v001';DECODE函數(shù)DECODE(input_value,value,result[,value,result……][,default_result]);格式：：示例：：selectid,decode(sign(score-85),1,'優(yōu)秀',0,'優(yōu)秀',-1,decode(sign(score-70),1,'良好',0,'良好',-1,decode(sign(score-60),1,'及格',0,'及格',-1,'不及格格')))fromstudent；數(shù)字函函數(shù)數(shù)字函函數(shù)接接受數(shù)數(shù)字輸輸入并并返回回數(shù)值值結果果

函數(shù)

輸入

輸出Abs(n)Selectabs(-15)fromdual;15Ceil(n)Selectceil(44.778)fromdual;45Cos(n)Selectcos(180)fromdual;-.5984601Cosh(n)Selectcosh(0)fromdual;1Floor(n)Selectfloor(100.2)fromdual;100Power(m,n)Selectpower(4,2)fromdual;16Mod(m,n)Selectmod(10,3)fromdual;1Round(m,n)Selectround(100.256,2)fromdual;100.26Trunc(m,n)Selecttrunc(100.256,2)fromdual;100.25Sqrt(n)Selectsqrt(4)fromdual;2Sign(n)Selectsign(-30)fromdual;-1數(shù)字函函數(shù)轉換函函數(shù)轉換函函數(shù)將將值從從一種種數(shù)據(jù)據(jù)類型型轉換換為另另一種種數(shù)據(jù)據(jù)類型型常用的的轉換換函數(shù)數(shù)有：：TO_CHARTO_DATETO_NUMBERSELECTTO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日"HH24:MI:SS')FROMdual;SELECTTO_CHAR(itemrate,‘‘C99999’)FROMitemfile;SELECTTO_DATE(‘2005-12-06’’,‘‘yyyy-mm-dd’)FROMdual;SELECTTO_NUMBER('100')FROMdual;其它函函數(shù)以下是是幾個個用來來轉換換空值值的函函數(shù)：：NVLNVL2NULLIFSELECTitemdesc,NVL(re_level,0)FROMitemfile;SELECTitemdesc,NVL2(re_level,re_level,max_level)FROMitemfile;SELECTitemdesc,NULLIF(re_level,max_level)FROMitemfile;函數(shù)的標準準格式是：：DUMP(expr[,return_fmt[,start_position][,length]])DUMP函數(shù)expr：這個參數(shù)數(shù)是要進行行分析的表表達式(數(shù)字或字符符串等，可可以是各個個類型的值值)return_fmt：指返回參參數(shù)的格式式：1)8：以8進制返回結結果的值2)10：以10進制返回結結果的值(默認)3)16：以16進制返回結結果的值4)17：以單字符符的形式返返回結果的的值5)1000：以上4種加上1000，表示在返返回值中加加上當前字字符集start_position：開始進行行返回的字字符位置length：需要返回回的字符長長度一些DUMP函數(shù)示例：：1.SELECTDUMP('abc')FROMDUAL;Typ=96Len=3:97,98,992.SELECTDUMP('abc',1016)FROMDUAL;Typ=96Len=3CharacterSet=UTF8:61,62,633.SELECTDUMP('abc',17,2,2)FROMDUAL;Typ=96Len=3:b,c4.createtablet(dtdate,tstimestamp(9));insertintotvalues(sysdate,systimestamp);selectdump(dt,10)dump,dump(ts,10)dumpfromt;Typ=12Len=7:120,114,3,16,18,19,55Typ=180Len=11:120,114,3,16,18,19,55,8,88,59,01VARCHAR22NUMBER8LONG12DATE23RAW24LONGRAW69ROWID96CHAR112CLOB113BLOB114BFILE180TIMESTAMP181TIMESTAMPWITHTIMEZONE182INTERVALYEARTOMONTH183INTERVALDAYTOSECOND208UROWID231TIMESTAMPWITHLOCALTIMEZONE分組函數(shù)分組函數(shù)基基于一組行行來返回結結果為每一組行行返回一個個值AVGMINMAXSUMCOUNTSELECTAVG(re_level)FROMitemfileWHEREp_category='accessories';SELECTMAX(max_level)FROMitemfile;SELECTSUM(itemrate*max_level)FROMitemfile;SELECTCOUNT(*)FROMitemfile;SELECTCOUNT(itemrate)FROMitemfile;SELECTCOUNT(DISTINCTqty_ha