《基本SQL語(yǔ)句》PPT課件.ppt

3 Copyright Oracle Corporation, 2001. All rights reserved. 單行函數(shù) 3-2Copyright Oracle Corporation, 2001. All rights reserved. 目標(biāo) 通過本章學(xué)習(xí)，您將可以: SQL中不同類型的函數(shù)。 在 SELECT 語(yǔ)句中使用字符，數(shù)字和日期函數(shù)。 描述轉(zhuǎn)換型函數(shù)的用途。 3-3Copyright Oracle Corporation, 2001. All rights reserved. SQL 函數(shù) 函數(shù)函數(shù) 輸入 參數(shù)參數(shù)1 1 參數(shù)參數(shù)2 2 參數(shù)參數(shù)n n 函數(shù)執(zhí)行 輸出 結(jié)果結(jié)果 3-4Copyright Oracle Corporation, 2001. All rights reserved. 兩種 SQL 函數(shù) 函數(shù)函數(shù) 單行函數(shù)單行函數(shù)多行函數(shù)多行函數(shù) 3-5Copyright Oracle Corporation, 2001. All rights reserved. 單行函數(shù) 單行函數(shù): 操作數(shù)句對(duì)象 接受函數(shù)返回一個(gè)結(jié)果 只對(duì)一行進(jìn)行變換 每行返回一個(gè)結(jié)果 可以轉(zhuǎn)換數(shù)據(jù)類型 可以嵌套 參數(shù)可以是一列或一個(gè)值 function_name (arg1, arg2,.) 3-6Copyright Oracle Corporation, 2001. All rights reserved. 單行函數(shù) 轉(zhuǎn)換轉(zhuǎn)換 字符字符 數(shù)值數(shù)值 日期日期 通用通用 單行函數(shù)單行函數(shù) 3-7Copyright Oracle Corporation, 2001. All rights reserved. 字符函數(shù) 字符函數(shù)字符函數(shù) LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR LPAD | RPAD TRIM REPLACE 大小寫控制函數(shù)大小寫控制函數(shù)字符控制函數(shù)字符控制函數(shù) 3-8Copyright Oracle Corporation, 2001. All rights reserved. 函數(shù)結(jié)果 大小寫控制函數(shù) 這類函數(shù)改變字符的大小寫。 LOWER(SQL Course) UPPER(SQL Course) INITCAP(SQL Course) sql course SQL COURSE Sql Course 3-9Copyright Oracle Corporation, 2001. All rights reserved. 大小寫控制函數(shù) 顯示員工 Higgins的信息: SELECT employee_id, last_name, department_id FROM employees WHERE last_name = higgins; no rows selectedno rows selected SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = higgins; 3-10Copyright Oracle Corporation, 2001. All rights reserved. CONCAT(Hello, World) SUBSTR(HelloWorld,1,5) LENGTH(HelloWorld) INSTR(HelloWorld, W) LPAD(salary,10,*) RPAD(salary, 10, *) TRIM(H FROM HelloWorld) HelloWorld Hello 10 6 *24000 24000* elloWorld 函數(shù)結(jié)果 字符控制函數(shù) 這類函數(shù)控制字符: 3-11Copyright Oracle Corporation, 2001. All rights reserved. SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, a) “Contains a?“ FROM employees WHERE SUBSTR(job_id, 4) = REP; 字符控制函數(shù) 1 2 312 3 3-12Copyright Oracle Corporation, 2001. All rights reserved. 數(shù)字函數(shù) ROUND: 四舍五入 ROUND(45.926, 2) 45.93 TRUNC: 截?cái)?TRUNC(45.926, 2) 45.92 MOD: 求余 MOD(1600, 300) 100 3-13Copyright Oracle Corporation, 2001. All rights reserved. SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL; ROUND 函數(shù) DUAL 是一個(gè)偽表，可以用來測(cè)試函數(shù)和表達(dá)式。 12 3 312 3-14Copyright Oracle Corporation, 2001. All rights reserved. SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL; TRUNC 函數(shù) 312 12 3 3-15Copyright Oracle Corporation, 2001. All rights reserved. SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = SA_REP; MOD 函數(shù) 3-16Copyright Oracle Corporation, 2001. All rights reserved. 日期 Oracle 內(nèi)部使用數(shù)字存儲(chǔ)日期: 世紀(jì),年,月,日,小時(shí),分 鐘,秒。 默認(rèn)的日期格式是 DD-MON-RR. 可以只指定年的后兩位在20世紀(jì)存放21世紀(jì)的日期。 同樣可以在21世紀(jì)存放20世紀(jì)的日期。 SELECT last_name, hire_date FROM employees WHERE last_name like G%; 3-17Copyright Oracle Corporation, 2001. All rights reserved. 日期 函數(shù)SYSDATE 返回: 日期 時(shí)間 3-18Copyright Oracle Corporation, 2001. All rights reserved. 日期的數(shù)學(xué)運(yùn)算 在日期上加上或減去一個(gè)數(shù)字結(jié)果仍為日期。 兩個(gè)日期相減返回日期之間相差的天數(shù)。 可以用數(shù)字除24來向日期中加上或減去小時(shí)。 3-19Copyright Oracle Corporation, 2001. All rights reserved. 日期的數(shù)學(xué)運(yùn)算 SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90; 3-20Copyright Oracle Corporation, 2001. All rights reserved. 日期函數(shù) 兩個(gè)日期相差的月數(shù) MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY ROUND TRUNC 向指定日期中加上若干月數(shù) 指定日期的下一個(gè)日期 本月的最后一天 日期四舍五入 日期截?cái)?函數(shù)描述 3-21Copyright Oracle Corporation, 2001. All rights reserved. MONTHS_BETWEEN (01-SEP-95,11-JAN-94) 日期函數(shù) ADD_MONTHS (11-JAN-94,6) NEXT_DAY (01-SEP-95,FRIDAY) LAST_DAY(01-FEB-95) 19.6774194 11-JUL-94 08-SEP-95 28-FEB-95 3-22Copyright Oracle Corporation, 2001. All rights reserved. ROUND(SYSDATE,MONTH) 01-AUG-95 ROUND(SYSDATE ,YEAR) 01-JAN-96 TRUNC(SYSDATE ,MONTH) 01-JUL-95 TRUNC(SYSDATE ,YEAR) 01-JAN-95 日期函數(shù) Assume SYSDATE = 25-JUL-95: 3-23Copyright Oracle Corporation, 2001. All rights reserved. 轉(zhuǎn)換函數(shù) 隱性隱性顯性顯性 數(shù)據(jù)類型轉(zhuǎn)換數(shù)據(jù)類型轉(zhuǎn)換 3-24Copyright Oracle Corporation, 2001. All rights reserved. 隱式數(shù)據(jù)類型轉(zhuǎn)換 Oracle 自動(dòng)完成下列轉(zhuǎn)換： VARCHAR2 or CHAR 源數(shù)據(jù)類型目標(biāo)數(shù)據(jù)類型 VARCHAR2 or CHAR NUMBER DATE NUMBER DATE VARCHAR2 VARCHAR2 3-25Copyright Oracle Corporation, 2001. All rights reserved. 隱式數(shù)據(jù)類型轉(zhuǎn)換 表達(dá)式計(jì)算中, Oracle 自動(dòng)完成下列轉(zhuǎn)換: VARCHAR2 or CHAR 源數(shù)據(jù)類型目標(biāo)數(shù)據(jù)類型 VARCHAR2 or CHAR NUMBER DATE 3-26Copyright Oracle Corporation, 2001. All rights reserved. 顯式數(shù)據(jù)類型轉(zhuǎn)換 NUMBERCHARACTER TO_CHAR TO_NUMBER DATE TO_CHAR TO_DATE 3-27Copyright Oracle Corporation, 2001. All rights reserved. TO_CHAR 函數(shù)對(duì)日期的轉(zhuǎn)換 格式: 必須包含在單引號(hào)中而且大小寫敏感。 可以包含任意的有效的日期格式。 可以使用 fm 去掉多余的空格或者前導(dǎo)零。 與日期指用逗號(hào)隔開。 TO_CHAR(date, format_model) 3-28Copyright Oracle Corporation, 2001. All rights reserved. YYYY 日期格式的元素 YEAR MM MONTH DY DAY 2004 TWO THOUSAND AND FOUR 02 MON MONDAY JULY MON JUL DD02 3-29Copyright Oracle Corporation, 2001. All rights reserved. 日期格式的元素 時(shí)間格式 使用雙引號(hào)向日期中添加字符 日期在月份中的位置 HH24:MI:SS AM15:45:32 PM DD “of“ MONTH12 of OCTOBER ddspthfourteenth 3-30Copyright Oracle Corporation, 2001. All rights reserved. TO_CHAR 函數(shù)對(duì)日期的轉(zhuǎn)換 SELECT last_name, TO_CHAR(hire_date, fmDD Month YYYY) AS HIREDATE FROM employees; 3-31Copyright Oracle Corporation, 2001. All rights reserved. TO_CHAR 函數(shù)對(duì)數(shù)字的轉(zhuǎn)換 下面是在TO_CHAR 函數(shù)中經(jīng)常使用的幾種格式: TO_CHAR(number, format_model) 9 0 $ L . , 數(shù)字 零 美元符 本地貨幣符號(hào) 小數(shù)點(diǎn) 千位符 3-32Copyright Oracle Corporation, 2001. All rights reserved. SELECT TO_CHAR(salary, $99,999.00) SALARY FROM employees WHERE last_name = Ernst; TO_CHAR函數(shù)對(duì)數(shù)字的轉(zhuǎn)換 3-33Copyright Oracle Corporation, 2001. All rights reserved. TO_NUMBER 和 TO_DATE 函數(shù) 使用 TO_NUMBER 函數(shù)將字符轉(zhuǎn)換成數(shù)字: 使用 TO_DATE 函數(shù)將字符轉(zhuǎn)換成日期: 這些函數(shù)可以使用fx 修飾符。 n TO_NUMBER(char, format_model) TO_DATE(char, format_model) 3-34Copyright Oracle Corporation, 2001. All rights reserved. TO_NUMBER 和 TO_DATE 函數(shù) 使用 TO_NUMBER 函數(shù)將字符轉(zhuǎn)換成數(shù)字: 使用 TO_DATE 函數(shù)將字符轉(zhuǎn)換成日期: 這些函數(shù)可以使用 fx 修飾符。 TO_NUMBER(char, format_model) TO_DATE(char, format_model) 3-35Copyright Oracle Corporation, 2001. All rights reserved. RR 日期格式 當(dāng)前年 1995 1995 2001 2001 日期 27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95 RR 格式 1995 2017 2017 1995 YY 格式 1995 1917 2017 2095 當(dāng)前的年份: 049 0495099 5099 The return date is in the current century The return date is in the century after the current one The return date is in the century before the current one The return date is in the current century 指定的年份: 3-36Copyright Oracle Corporation, 2001. All rights reserved. RR 日期格式 SELECT last_name, TO_CHAR(hire_date, DD-Mon-YYYY) FROM employees WHERE hire_date TO_DATE(01-Jan-90, DD-Mon-RR); 使用RR日期格式查找雇傭日期在1990年之前的員工， 在1999或現(xiàn)在使用下面的命令會(huì)產(chǎn)生相同的結(jié)果: 3-37Copyright Oracle Corporation, 2001. All rights reserved. 嵌套函數(shù) 單行函數(shù)可以嵌套。 嵌套函數(shù)的執(zhí)行順序是由內(nèi)到外。 F3(F2(F1(col,arg1),arg2),arg3) 步驟1 = 結(jié)果1 步驟2 =結(jié)果2 步驟3 =結(jié)果3 3-38Copyright Oracle Corporation, 2001. All rights reserved. SELECT last_name, NVL(TO_CHAR(manager_id), No Manager) FROM employees WHERE manager_id IS NULL; 嵌套函數(shù) 3-39Copyright Oracle Corporation, 2001. All rights reserved. 通用函數(shù) 這些函數(shù)適用于任何數(shù)據(jù)類型，同時(shí)也適用于空值： NVL (expr1, expr2) NVL2 (expr1, expr2, expr3) NULLIF (expr1, expr2) COALESCE (expr1, expr2, ., exprn) 3-40Copyright Oracle Corporation, 2001. All rights reserved. NVL 函數(shù) 將空值轉(zhuǎn)換成一個(gè)已知的值： 可以使用的數(shù)據(jù)類型有日期、字符、數(shù)字。 函數(shù)的一般形式: NVL(commission_pct,0) NVL(hire_date,01-JAN-97) NVL(job_id,No Job Yet) 3-41Copyright Oracle Corporation, 2001. All rights reserved. SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0) AN_SAL FROM employees; 使用NVL函數(shù) 12 1 2 3-42Copyright Oracle Corporation, 2001. All rights reserved. SELECT last_name, salary, commission_pct, NVL2(commission_pct, SAL+COMM, SAL) income FROM employees WHERE department_id IN (50, 80); 使用 NVL2 函數(shù) 12 1 2 3-43Copyright Oracle Corporation, 2001. All rights reserved. SELECT first_name, LENGTH(first_name) “expr1“, last_name, LENGTH(last_name) “expr2“, NULLIF(LENGTH(first_name), LENGTH(last_name) result FROM employees; 使用 NULLIF 函數(shù) 1 2 3 123 3-44Copyright Oracle Corporation, 2001. All rights reserved. 使用 COALESCE 函數(shù) COALESCE 與 NVL 相比的優(yōu)點(diǎn)在于 COALESCE 可以同 時(shí)處理交替的多個(gè)值。 如果第一個(gè)表達(dá)式費(fèi)空,則返回這個(gè)表達(dá)式，對(duì)其他的參 數(shù)進(jìn)行COALESCE 。 3-45Copyright Oracle Corporation, 2001. All rights reserved. SELECT last_name, COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct; 使用 COALESCE 函數(shù) 3-46Copyright Oracle Corporation, 2001. All rights reserved. 條件表達(dá)式 在 SQL 語(yǔ)句中使用IF-THEN-ELSE 邏輯。 使用兩種方法: CASE 表達(dá)式 DECODE 函數(shù) 3-47Copyright Oracle Corporation, 2001. All rights reserved. CASE 表達(dá)式 在需要使用 IF-THEN-ELSE 邏輯時(shí): CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr END 3-48Copyright Oracle Corporation, 2001. All rights reserved. SELECT last_name, job_id, salary, CASE job_id WHEN IT_PROG THEN 1.10*salary WHEN ST_CLERK THEN 1.15*salary WHEN SA_REP THEN 1.20*salary ELSE salary END “REVISED_SALARY“ FROM employees; CASE 表達(dá)式 下面是使用case表達(dá)式的一個(gè)例子： 3-49Copyright Oracle Corporation, 2001. All rights reserved. DECODE 函數(shù) 在需要使用 IF-THEN-ELSE 邏輯時(shí): DECODE(col|expression, search1, result1 , search2, result2,., , default) 3-50Copyright Oracle Corporation, 2001. All rights reserved. DECODE 函數(shù) SELECT last_name, job_id, salary, DECODE(job_id, IT_PROG, 1.10*salary, ST_CLERK, 1.15*salary, SA_REP, 1.20*salary, salary) REVISED_SALARY FROM employees; 3-51Copyright Oracle Corporation, 2001. All rights reserved. DECODE 函數(shù) SELECT last_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_RATE FROM employees WHERE department_id = 80; 使用decode函數(shù)的一個(gè)例子： 3-52Copyright Oracle Corporation, 2001. All rights reserved. 總結(jié) 通過本章學(xué)習(xí)，您應(yīng)該學(xué)會(huì): 使用函數(shù)對(duì)數(shù)據(jù)進(jìn)行計(jì)算 使用函數(shù)修改數(shù)據(jù) 使用函數(shù)控制一組數(shù)據(jù)的輸出格式 使用函數(shù)改變?nèi)掌诘娘@示格式 使用函數(shù)改變數(shù)據(jù)類型 使用 NVL 函數(shù) 使用IF-THEN-ELSE 邏輯