SQL基礎(chǔ)：單行函數(shù)

單行函數(shù)目標(biāo)通過本章學(xué)習(xí)，您將可以:SQL中不同類型的函數(shù)。在SELECT

語句中使用字符，數(shù)字和日期函數(shù)。描述轉(zhuǎn)換型函數(shù)的用途。SQL函數(shù)函數(shù)輸入?yún)?shù)1參數(shù)2參數(shù)n函數(shù)執(zhí)行輸出結(jié)果兩種SQL函數(shù)單行函數(shù)多行函數(shù)每行返回一個(gè)結(jié)果多行返回一個(gè)結(jié)果函數(shù)單行函數(shù)單行函數(shù):處理數(shù)據(jù)項(xiàng)。接受參數(shù)并返回一個(gè)值。作用于每個(gè)返回的行。為每行返回一個(gè)結(jié)果?？梢孕薷臄?shù)據(jù)類型。可以嵌套。接受的參數(shù)可以是一列，也可以是一個(gè)表達(dá)式。function_name[(arg1,arg2,...)]單行函數(shù)轉(zhuǎn)換函數(shù)字符函數(shù)數(shù)值函數(shù)日期函數(shù)常規(guī)函數(shù)單行函數(shù)字符函數(shù)字符函數(shù)LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE大小寫處理函數(shù)字符處理函數(shù)大小寫處理函數(shù)這類函數(shù)改變字符的大小寫:函數(shù)結(jié)果LOWER('SQLCourse')sqlcourseUPPER('SQLCourse')SQLCOURSEINITCAP('SQLCourse')SqlCourseSELECTemployee_id,last_name,department_idFROMemployeesWHERELOWER(last_name)='higgins';使用大小寫處理函數(shù)顯示員工Higgins的信息:SELECTemployee_id,last_name,department_idFROMemployeesWHERElast_name='higgins';norowsselected字符處理函數(shù)這類函數(shù)處理字符:函數(shù)結(jié)果CONCAT('Hello','World')HelloWorldSUBSTR('HelloWorld',1,5)HelloLENGTH('HelloWorld')10INSTR('HelloWorld','W')6LPAD(salary,10,'*')*****24000RPAD(salary,10,'*')24000*****REPLACE

('JACKandJUE','J','BL')BLACKandBLUETRIM('H'FROM'HelloWorld')elloWorldSELECTemployee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INSTR(last_name,'a')"Contains'a'?"FROMemployeesWHERESUBSTR(job_id,4)='REP';字符控制函數(shù)231213數(shù)字函數(shù)ROUND:將值舍入到指定的小數(shù)位。TRUNC:將值截?cái)嗟街付ǖ男?shù)位。MOD:返回除法運(yùn)算的余數(shù)。函數(shù)結(jié)果ROUND(45.926,2)45.93TRUNC(45.926,2)45.92MOD(1600,300)100SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMDUAL;ROUND

函數(shù)DUAL

是一個(gè)‘偽表’，可以用來測試函數(shù)和表達(dá)式.331212TRUNC

函數(shù)SELECTROUND(45.923,2),ROUND(45.923),ROUND(45.923,-1)FROMDUAL;331212SELECTlast_name,salary,MOD(salary,5000)FROMemployeesWHEREjob_id='SA_REP';MOD

函數(shù)計(jì)算職稱為銷售代表的所有員工的薪金除以5000之后的余數(shù)。SELECTlast_name,hire_dateFROMemployeesWHEREhire_date<'01-FEB-88';處理日期Oracle數(shù)據(jù)庫以內(nèi)部數(shù)字格式存儲(chǔ)日期：世紀(jì)，年，月，日，小時(shí)，分鐘和秒。默認(rèn)的日期格式是DD-MON-RR.可以只指定年的后兩位在20世紀(jì)存放21世紀(jì)的日期。同樣可以在21世紀(jì)存放20世紀(jì)的日期。處理日期函數(shù)SYSDATE

返回:日期

時(shí)間與日期有關(guān)的運(yùn)算在日期上加上或減去一個(gè)數(shù)字結(jié)果仍為日期.兩個(gè)日期相減返回日期之間相差的天數(shù).可以用數(shù)字除以24來向日期中加上或減去小時(shí).SELECTlast_name,(SYSDATE-hire_date)/7ASWEEKSFROMemployeesWHEREdepartment_id=90;使用算數(shù)運(yùn)算處理日期日期函數(shù)函數(shù)描述MONTHS_BETWEEN兩個(gè)日期相差的月數(shù)ADD_MONTHS向指定日期中加上若干月數(shù)NEXT_DAY指定日期的下一天LAST_DAY月份的最后一天ROUND 日期按指定的格式四舍五入TRUNC日期按指定的格式截?cái)嗍褂萌掌诤瘮?shù)函數(shù)結(jié)果MONTHS_BETWEEN('01-SEP-95','11-JAN-94')19.6774194ADD_MONTHS('11-JAN-94',6)'11-JUL-94'NEXT_DAY('01-SEP-95','FRIDAY')'08-SEP-95'LAST_DAY('01-FEB-95')'28-FEB-95'使用日期函數(shù)假設(shè)

SYSDATE='25-JUL-03':函數(shù)結(jié)果ROUND(SYSDATE,'MONTH')01-AUG-03ROUND(SYSDATE,'YEAR')01-JAN-04TRUNC(SYSDATE,'MONTH')01-JUL-03TRUNC(SYSDATE,'YEAR')01-JAN-03練習(xí)3:概覽1本練習(xí)包括下列主題:書寫一個(gè)顯示當(dāng)前日期的查詢創(chuàng)建一個(gè)需要用到數(shù)值、字符以及日期函數(shù)的查詢?cè)趀mployee表中執(zhí)行對(duì)員工工作時(shí)間的計(jì)算。轉(zhuǎn)換函數(shù)隱式數(shù)據(jù)類型轉(zhuǎn)換顯式數(shù)據(jù)類型轉(zhuǎn)換數(shù)據(jù)類型轉(zhuǎn)換隱式數(shù)據(jù)類型轉(zhuǎn)換對(duì)賦值操作，oracle自動(dòng)轉(zhuǎn)換以下數(shù)據(jù)類型:源數(shù)據(jù)類型目標(biāo)數(shù)據(jù)類型VARCHAR2orCHARNUMBERVARCHAR2orCHARDATENUMBERVARCHAR2DATEVARCHAR2隱式數(shù)據(jù)類型轉(zhuǎn)換表達(dá)式計(jì)算中，oracle自動(dòng)轉(zhuǎn)換以下數(shù)據(jù)類型:源數(shù)據(jù)類型目標(biāo)數(shù)據(jù)類型VARCHAR2orCHARNUMBERVARCHAR2orCHARDATE顯式數(shù)據(jù)類型轉(zhuǎn)換NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE使用TO_CHAR

函數(shù)處理日期

格式樣式具有以下特性:必須包含在單引號(hào)中。大小寫敏感?？梢园我獾挠行У娜掌诟袷健？梢允褂胒m

去掉多余的空格或者前導(dǎo)零。與日期值之間通過逗號(hào)分隔。TO_CHAR(date,'format_model')日期格式的樣式元素元素描述YYYY用數(shù)字表示的完整的年份YEAR用字母拼寫的年份MM代表月份的兩位數(shù)值MONTH月份的全名MON代表月份的三個(gè)字母的縮寫DY代表星期幾的三個(gè)字母的縮寫DAY星期幾的全名DD用數(shù)字表示的月份中的某日日期格式的樣式元素時(shí)間格式:

可以添加字符串，字符串要放在雙引號(hào)中:

日期在月份中的位置:DD"of"MONTH12ofOCTOBERddspthfourteenthHH24:MI:SSAM15:45:32PMSELECTlast_name,TO_CHAR(hire_date,'fmDDMonthYYYY')ASHIREDATEFROMemployees;使用TO_CHAR

函數(shù)對(duì)日期轉(zhuǎn)換…使用TO_CHAR

函數(shù)對(duì)數(shù)字轉(zhuǎn)換以下是一些格式元素，可以將它們用于TO_CHAR函數(shù)，以便將數(shù)據(jù)值作為字符進(jìn)行顯示:元素含義9代表一個(gè)數(shù)字0強(qiáng)制顯示零$放置一個(gè)浮動(dòng)的美元記號(hào)L使用浮動(dòng)的當(dāng)?shù)刎泿欧?hào).顯示小數(shù)點(diǎn),顯示千位指示符TO_CHAR(number,'format_model')SELECTTO_CHAR(salary,'$99,999.00')SALARYFROMemployeesWHERElast_name='Ernst';TO_CHAR

函數(shù)對(duì)數(shù)字的轉(zhuǎn)換使用TO_NUMBER

和TO_DATE

函數(shù)

使用TO_NUMBER

函數(shù)將字符串轉(zhuǎn)換成數(shù)字格式:使用

TO_DATE

函數(shù)將字符串轉(zhuǎn)換成日期格式:這些函數(shù)都有一個(gè)fx限定符，此限定符用于指定與TO_DATE函數(shù)的字符參數(shù)和日期樣式完全匹配的項(xiàng).TO_NUMBER(char[,'format_model'])TO_DATE(char[,'format_model'])RR

日期格式當(dāng)前年1995199520012001日期27-OCT-9527-OCT-1727-OCT-1727-OCT-95RR格式1995201720171995YY格式1995191720172095當(dāng)前年份的后兩位:0–490–4950–9950–99返回的日期在當(dāng)前世紀(jì)中返回的日期在當(dāng)前世紀(jì)的下一個(gè)世紀(jì)中返回的日期在當(dāng)前世紀(jì)的前一個(gè)世紀(jì)中返回的日期在當(dāng)前世紀(jì)中指定的兩位數(shù)年份:RR

日期格式示例使用RR日期格式查找雇傭日期在1990年之前的員工，此格式會(huì)使命令無論是在1999年執(zhí)行還是在現(xiàn)在運(yùn)行都會(huì)產(chǎn)生同樣的結(jié)果:SELECTlast_name,TO_CHAR(hire_date,'DD-Mon-YYYY')FROMemployeesWHEREhire_date<TO_DATE('01-Jan-90','DD-Mon-RR');嵌套函數(shù)單行函數(shù)可以嵌套任意層。嵌套函數(shù)的執(zhí)行順序是由內(nèi)到外。F3(F2(F1(col,arg1),arg2),arg3)步驟1=結(jié)果

1步驟2=結(jié)果

2步驟3=結(jié)果3SELECTlast_name,UPPER(CONCAT(SUBSTR(LAST_NAME,1,8),'_US'))FROMemployeesWHEREdepartment_id=60;嵌套函數(shù)常規(guī)函數(shù)這些函數(shù)可以使用任何數(shù)據(jù)類型，同時(shí)也適用于空值:NVL(expr1,expr2)NVL2(expr1,expr2,expr3)NULLIF(expr1,expr2)COALESCE(expr1,expr2,...,exprn)NVL

函數(shù)將空值轉(zhuǎn)換成一個(gè)已知的值:可以使用的數(shù)據(jù)類型有日期、字符、數(shù)字.數(shù)據(jù)類型必須匹配:NVL(commission_pct,0)NVL(hire_date,'01-JAN-97')NVL(job_id,'NoJobYet')SELECTlast_name,salary,NVL(commission_pct,0),(salary*12)+(salary*12*NVL(commission_pct,0))AN_SALFROMemployees;使用NVL

函數(shù)…1122SELECTlast_name,salary,commission_pct,NVL2(commission_pct,'SAL+COMM','SAL')incomeFROMemployeesWHEREdepartment_idIN(50,80);使用NVL2

函數(shù)1221SELECTfirst_name,LENGTH(first_name)"expr1",last_name,LENGTH(last_name)"expr2",NULLIF(LENGTH(first_name),LENGTH(last_name))resultFROMemployees;使用NULLIF

函數(shù)…123123使用COALESCE

函數(shù)COALESCE

與

NVL

相比的優(yōu)點(diǎn)在于

COALESCE

可以使用多個(gè)備選值.如果第一個(gè)表達(dá)式非空,則返回該表達(dá)式，否則,將對(duì)余下的表達(dá)式執(zhí)行COALESCE運(yùn)算.SELECTlast_name,COALESCE(manager_id,commission_pct,-1)commFROMemployeesORDERBYcommission_pct;使用COALESCE

函數(shù)…條件表達(dá)式在SQL語句中使用IF-THEN-ELSE邏輯使用兩種方法:CASE

表達(dá)式DECODE

函數(shù)CASE

表達(dá)式通過執(zhí)行IF-THEN-ELSE語句的任務(wù),可以簡化條件查詢:CASEexprWHENcomparison_expr1THENreturn_expr1

[WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_expr]ENDSELECTlast_name,job_id,salary,CASEjob_idWHEN'IT_PROG'THEN1.10*salaryWHEN'ST_CLERK'THEN1.15*salaryWHEN'SA_REP'THEN1.20*salaryELSEsalaryEND"REVISED_SALARY"FROMemployees;使用CASE

表達(dá)式下面是使用case表達(dá)式的一個(gè)例子：……DECODE

函數(shù)在需要使用

IF-THEN-ELSE邏輯時(shí):DECODE(col|expression,search1,result1

[,search2,result2,...,]

[,default])SELECTlast_name,job_id,salary,DECODE(job_id,'IT_PROG',1.10*salary,'ST_CLERK',1.15*salary,