講稿單行函數(shù)

使用單行函數(shù)EpisodeIII，PartII函數(shù)EpisodeIII結(jié)構(gòu)目標預(yù)備知識

回顧內(nèi)容

總結(jié)

作業(yè)目標了解SQL函數(shù)的類型在SELECT中使用字符/數(shù)值/日期函數(shù)熟悉轉(zhuǎn)換函數(shù)中可用的類型在SELECT中使用轉(zhuǎn)換函數(shù)在SELECT中使用條件表達式預(yù)備知識回顧可以在WHERE子句中使用使用比較運算符使用BETWEEN…AND,IN,LIKE,ISNULL使用NOT，AND，OR能夠使用ORDERBY子句進行排序SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable[WHEREcondition(s)][ORDERBY{column,expr,alias}[ASC|DESC]];內(nèi)容單行函數(shù)字符函數(shù)數(shù)值函數(shù)日期和日期函數(shù)SQL函數(shù)函數(shù)輸入?yún)?shù)1參數(shù)2參數(shù)n函數(shù)完成某些動作輸出結(jié)果值SQL函數(shù)的兩種類型單行函數(shù)多行函數(shù)對于每一行返回一個結(jié)果對于一組行返回一個結(jié)果函數(shù)單行函數(shù)單行函數(shù)功能：操作數(shù)據(jù)接受參數(shù)返回一個值每一行返回一個值可以修改數(shù)據(jù)類型可以被嵌套使用參數(shù)可以是列或者表達式函數(shù)名[(參數(shù)1,參數(shù)2,...)]單行函數(shù)分類轉(zhuǎn)換函數(shù)字符函數(shù)數(shù)值函數(shù)日期函數(shù)通用函數(shù)單行函數(shù)字符函數(shù)字符函數(shù)LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE大小寫轉(zhuǎn)換函數(shù)字符操作函數(shù)大小寫轉(zhuǎn)換函數(shù)這些函數(shù)用來轉(zhuǎn)換字符大小寫sqlcourseLOWER('SQLCourse')SqlCourseINITCAP('SQLCourse')SQLCOURSEUPPER('SQLCourse')ResultFunction使用大小寫轉(zhuǎn)換函數(shù)SELECTemployee_id,last_name,department_idFROMemployeesWHERELOWER(last_name)='higgins';SELECTemployee_id,last_name,department_idFROMemployeesWHERElast_name='higgins';字符操作函數(shù)用來操作字符串BLACKandBLUEREPLACE

('JACKandJUE','J','BL')10LENGTH('HelloWorld')6INSTR('HelloWorld','W')*****24000LPAD(salary,10,'*')24000*****RPAD(salary,10,'*')HelloWorldCONCAT('Hello','World')elloWorldTRIM('H'FROM'HelloWorld')HelloSUBSTR('HelloWorld',1,5)結(jié)果函數(shù)字符操作函數(shù)的使用SELECTemployee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INSTR(last_name,'a')"Contains'a'?"FROMemployeesWHERESUBSTR(job_id,4)='REP';231213數(shù)值函數(shù)ROUND：四舍五入TRUNC：去尾MOD：求余數(shù)100MOD(1600,300)45.93ROUND(45.926,2)45.92TRUNC(45.926,2)ResultFunctionROUND函數(shù)SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMDUAL;DUAL是一個虛表，當要顯示的內(nèi)容與表無關(guān)時，可以使用此表331212TRUNC函數(shù)SELECTTRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-1)FROMDUAL;331212MODSELECTlast_name,salary,MOD(salary,5000)FROMemployeesWHEREjob_id='SA_REP';日期Oracle數(shù)據(jù)庫在內(nèi)部使用數(shù)字格式存儲日期：世紀，年，月，日，小時，分，秒默認的日期格式為DD-MON-RRSELECTlast_name,hire_dateFROMemployeesWHEREhire_date<'01-FEB-88';RR格式CurrentYear1995199520012001

27-OCT-9527-OCT-1727-OCT-1727-OCT-95

1995201720171995

1995191720172095當前年份:0–490–4950–9950–99返回日期為當前世紀返回日期為下個世紀返回日期為上個世紀返回日期為當前世紀給定年份:YY格式年份RR格式年份給定日期當前年日期函數(shù)SYSDATE能夠返回當前服務(wù)器的日期和時間SELECTsysdateFROMdual;日期上的計算在日期上加減一個數(shù)值會返回一個日期兩個日期相減會得到兩個日期間相差天數(shù)在一個日期上加小時需要除以24日期相關(guān)的運算SELECTlast_name,(SYSDATE-hire_date)/7ASWEEKSFROMemployeesWHEREdepartment_id=90;日期操作函數(shù)查看下一個周幾是哪天NEXT_DAY查看月末幾號LAST_DAY對日期四舍五入ROUND 對日期進行截斷TRUNC兩個日期之間相差月數(shù)MONTHS_BETWEEN在一個日期上加上多少個月ADD_MONTHS結(jié)果函數(shù)'08-SEP-95'NEXT_DAY('01-SEP-95','FRIDAY')'28-FEB-95'LAST_DAY('01-FEB-95')19.6774194MONTHS_BETWEEN('01-SEP-95','11-JAN-94')‘29-FEB-96'ADD_MONTHS(‘31-JAN-96',1)功能函數(shù)假設(shè)

SYSDATE

=

'25-JUL-03':01-JUL-03TRUNC(SYSDATE,'MONTH')01-JAN-03TRUNC(SYSDATE,'YEAR')01-AUG-03ROUND(SYSDATE,'MONTH')01-JAN-04ROUND(SYSDATE,'YEAR')結(jié)果函數(shù)贈品EXTRACT函數(shù)數(shù)據(jù)轉(zhuǎn)換隱式轉(zhuǎn)換顯式轉(zhuǎn)換數(shù)據(jù)類型轉(zhuǎn)換隱式的數(shù)據(jù)類型轉(zhuǎn)換在表達式中oracle會自動對數(shù)據(jù)類型進行轉(zhuǎn)換，可以轉(zhuǎn)換的是NUMBERVARCHAR2orCHARDATEVARCHAR2orCHAR到從VARCHAR2orCHARNUMBERVARCHAR2orCHARDATE到從顯式的數(shù)據(jù)類型轉(zhuǎn)換數(shù)值字符TO_CHARTO_NUMBER日期TO_CHARTO_DATETO_CHAR格式化字符串必須使用單引號定界大小寫敏感可以使用fm去掉前導(dǎo)空格或者前導(dǎo)零與日期使用逗號分隔TO_CHAR(date,'format_model')格式化字符串中格式元素兩個字母簡寫的周幾DY全寫的周幾DAY兩位月份MM月份全名MONTH三個字母簡寫月份MON兩位數(shù)字的日期DD四位年份YYYY用英語拼寫的年份YEAR結(jié)果元素格式元素DD"of"MONTH12ofOCTOBERddspthfourteenthHH24:MI:SSAM15:45:32PMTO_CHARSELECTlast_name,TO_CHAR(hire_date,'fmDDMonthYYYY')ASHIREDATEFROMemployees;…TO_CHARTO_CHAR(number,'format_model')輸出一個小數(shù)點.輸出一個千分符,顯示美元符$顯示本地貨幣符號L表示一個數(shù)字9強制顯示00結(jié)果元素TO_CHARSELECTTO_CHAR(salary,'$99,999.00')SALARYFROMemployeesWHERElast_name='Ernst';TO_NUMBER，TO_DATE轉(zhuǎn)換字符到數(shù)值轉(zhuǎn)換字符到日期可以包含fx前綴。TO_NUMBER(char[,'format_model'])TO_DATE(char[,'format_model'])SELECTlast_name,TO_CHAR(hire_date,'DD-Mon-YYYY')FROMemployeesWHEREhire_date<TO_DATE('01-Jan-90','DD-Mon-RR');函數(shù)的嵌套調(diào)用單行函數(shù)可以無限的嵌套嵌套函數(shù)計算是從里到外的F3(F2(F1(col,arg1),arg2),arg3)Step1=Result1Step2=Result2Step3=Result3SELECTlast_name,UPPER(CONCAT(SUBSTR(LAST_NAME,1,8),'_US'))FROMemployeesWHEREdepartment_id=60;通用函數(shù)可以轉(zhuǎn)換NULL值:NVL

(expr1,expr2)NVL2

(expr1,expr2,expr3)NULLIF

(expr1,expr2)COALESCE

(expr1,expr2,...,exprn)NVLFunction可以用來轉(zhuǎn)換NULL值到一個其他值:可以使用數(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')eFROMemployeesWHEREdepartment_idIN(50,80);NVL21221SELECTfirst_name,LENGTH(first_name)"expr1",last_name,LENGTH(last_name)"expr2",NULLIF(LENGTH(first_name),LENGTH(last_name))resultFROMemployees;NULLIF…123123SELECTlast_name,employee_id,COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id), 'Nocommissionandnomanager')FROMemployees;COALESCE……CASECASEexprWHENcomparison_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………DECODEDECODE(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,'SA_REP',1.20*salary,salary)REVISED_SALARYFROMemployees;DECODE………SELECTlast_name,salary,DECODE(TRUNC(salary/2000,0),0,0.00,1,0.09,2,0.20,3,0.30,4,0.40,5,0.42,6,0.44,0.45)TAX_RATEFROMemployeesWHE