《基本SQL语句》PPT课件.ppt

3 Copyright Oracle Corporation, 2001. All rights reserved. 单行函数 3-2Copyright Oracle Corporation, 2001. All rights reserved. 目标 通过本章学习，您将可以: SQL中不同类型的函数。 在 SELECT 语句中使用字符，数字和日期函数。 描述转换型函数的用途。 3-3Copyright Oracle Corporation, 2001. All rights reserved. SQL 函数 函数函数 输入 参数参数1 1 参数参数2 2 参数参数n n 函数执行 输出 结果结果 3-4Copyright Oracle Corporation, 2001. All rights reserved. 两种 SQL 函数 函数函数 单行函数单行函数多行函数多行函数 3-5Copyright Oracle Corporation, 2001. All rights reserved. 单行函数 单行函数: 操作数句对象 接受函数返回一个结果 只对一行进行变换 每行返回一个结果 可以转换数据类型 可以嵌套 参数可以是一列或一个值 function_name (arg1, arg2,.) 3-6Copyright Oracle Corporation, 2001. All rights reserved. 单行函数 转换转换 字符字符 数值数值 日期日期 通用通用 单行函数单行函数 3-7Copyright Oracle Corporation, 2001. All rights reserved. 字符函数 字符函数字符函数 LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR LPAD | RPAD TRIM REPLACE 大小写控制函数大小写控制函数字符控制函数字符控制函数 3-8Copyright Oracle Corporation, 2001. All rights reserved. 函数结果 大小写控制函数 这类函数改变字符的大小写。 LOWER(SQL Course) UPPER(SQL Course) INITCAP(SQL Course) sql course SQL COURSE Sql Course 3-9Copyright Oracle Corporation, 2001. All rights reserved. 大小写控制函数 显示员工 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 函数结果 字符控制函数 这类函数控制字符: 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; 字符控制函数 1 2 312 3 3-12Copyright Oracle Corporation, 2001. All rights reserved. 数字函数 ROUND: 四舍五入 ROUND(45.926, 2) 45.93 TRUNC: 截断 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 函数 DUAL 是一个伪表，可以用来测试函数和表达式。 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 函数 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 函数 3-16Copyright Oracle Corporation, 2001. All rights reserved. 日期 Oracle 内部使用数字存储日期: 世纪,年,月,日,小时,分 钟,秒。 默认的日期格式是 DD-MON-RR. 可以只指定年的后两位在20世纪存放21世纪的日期。 同样可以在21世纪存放20世纪的日期。 SELECT last_name, hire_date FROM employees WHERE last_name like G%; 3-17Copyright Oracle Corporation, 2001. All rights reserved. 日期 函数SYSDATE 返回: 日期 时间 3-18Copyright Oracle Corporation, 2001. All rights reserved. 日期的数学运算 在日期上加上或减去一个数字结果仍为日期。 两个日期相减返回日期之间相差的天数。 可以用数字除24来向日期中加上或减去小时。 3-19Copyright Oracle Corporation, 2001. All rights reserved. 日期的数学运算 SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90; 3-20Copyright Oracle Corporation, 2001. All rights reserved. 日期函数 两个日期相差的月数 MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY ROUND TRUNC 向指定日期中加上若干月数 指定日期的下一个日期 本月的最后一天 日期四舍五入 日期截断 函数描述 3-21Copyright Oracle Corporation, 2001. All rights reserved. MONTHS_BETWEEN (01-SEP-95,11-JAN-94) 日期函数 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 日期函数 Assume SYSDATE = 25-JUL-95: 3-23Copyright Oracle Corporation, 2001. All rights reserved. 转换函数 隐性隐性显性显性 数据类型转换数据类型转换 3-24Copyright Oracle Corporation, 2001. All rights reserved. 隐式数据类型转换 Oracle 自动完成下列转换： VARCHAR2 or CHAR 源数据类型目标数据类型 VARCHAR2 or CHAR NUMBER DATE NUMBER DATE VARCHAR2 VARCHAR2 3-25Copyright Oracle Corporation, 2001. All rights reserved. 隐式数据类型转换 表达式计算中, Oracle 自动完成下列转换: VARCHAR2 or CHAR 源数据类型目标数据类型 VARCHAR2 or CHAR NUMBER DATE 3-26Copyright Oracle Corporation, 2001. All rights reserved. 显式数据类型转换 NUMBERCHARACTER TO_CHAR TO_NUMBER DATE TO_CHAR TO_DATE 3-27Copyright Oracle Corporation, 2001. All rights reserved. TO_CHAR 函数对日期的转换 格式: 必须包含在单引号中而且大小写敏感。 可以包含任意的有效的日期格式。 可以使用 fm 去掉多余的空格或者前导零。 与日期指用逗号隔开。 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. 日期格式的元素 时间格式 使用双引号向日期中添加字符 日期在月份中的位置 HH24:MI:SS AM15:45:32 PM DD “of“ MONTH12 of OCTOBER ddspthfourteenth 3-30Copyright Oracle Corporation, 2001. All rights reserved. TO_CHAR 函数对日期的转换 SELECT last_name, TO_CHAR(hire_date, fmDD Month YYYY) AS HIREDATE FROM employees; 3-31Copyright Oracle Corporation, 2001. All rights reserved. TO_CHAR 函数对数字的转换 下面是在TO_CHAR 函数中经常使用的几种格式: TO_CHAR(number, format_model) 9 0 $ L . , 数字 零 美元符 本地货币符号 小数点 千位符 3-32Copyright Oracle Corporation, 2001. All rights reserved. SELECT TO_CHAR(salary, $99,999.00) SALARY FROM employees WHERE last_name = Ernst; TO_CHAR函数对数字的转换 3-33Copyright Oracle Corporation, 2001. All rights reserved. TO_NUMBER 和 TO_DATE 函数 使用 TO_NUMBER 函数将字符转换成数字: 使用 TO_DATE 函数将字符转换成日期: 这些函数可以使用fx 修饰符。 n TO_NUMBER(char, format_model) TO_DATE(char, format_model) 3-34Copyright Oracle Corporation, 2001. All rights reserved. TO_NUMBER 和 TO_DATE 函数 使用 TO_NUMBER 函数将字符转换成数字: 使用 TO_DATE 函数将字符转换成日期: 这些函数可以使用 fx 修饰符。 TO_NUMBER(char, format_model) TO_DATE(char, format_model) 3-35Copyright Oracle Corporation, 2001. All rights reserved. RR 日期格式 当前年 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 当前的年份: 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或现在使用下面的命令会产生相同的结果: 3-37Copyright Oracle Corporation, 2001. All rights reserved. 嵌套函数 单行函数可以嵌套。 嵌套函数的执行顺序是由内到外。 F3(F2(F1(col,arg1),arg2),arg3) 步骤1 = 结果1 步骤2 =结果2 步骤3 =结果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; 嵌套函数 3-39Copyright Oracle Corporation, 2001. All rights reserved. 通用函数 这些函数适用于任何数据类型，同时也适用于空值： NVL (expr1, expr2) NVL2 (expr1, expr2, expr3) NULLIF (expr1, expr2) COALESCE (expr1, expr2, ., exprn) 3-40Copyright Oracle Corporation, 2001. All rights reserved. NVL 函数 将空值转换成一个已知的值： 可以使用的数据类型有日期、字符、数字。 函数的一般形式: 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函数 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 函数 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 函数 1 2 3 123 3-44Copyright Oracle Corporation, 2001. All rights reserved. 使用 COALESCE 函数 COALESCE 与 NVL 相比的优点在于 COALESCE 可以同 时处理交替的多个值。 如果第一个表达式费空,则返回这个表达式，对其他的参 数进行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 函数 3-46Copyright Oracle Corporation, 2001. All rights reserved. 条件表达式 在 SQL 语句中使用IF-THEN-ELSE 逻辑。 使用两种方法: CASE 表达式 DECODE 函数 3-47Copyright Oracle Corporation, 2001. All rights reserved. CASE 表达式 在需要使用 IF-THEN-ELSE 逻辑时: 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 表达式 下面是使用case表达式的一个例子： 3-49Copyright Oracle Corporation, 2001. All rights reserved. DECODE 函数 在需要使用 IF-THEN-ELSE 逻辑时: DECODE(col|expression, search1, result1 , search2, result2,., , default) 3-50Copyright Oracle Corporation, 2001. All rights reserved. DECODE 函数 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 函数 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函数的一个例子： 3-52Copyright Oracle Corporation, 2001. All rights reserved. 总结 通过本章学习，您应该学会: 使用函数对数据进行计算 使用函数修改数据 使用函数控制一组数据的输出格式 使用函数改变日期的显示格式 使用函数改变数据类型 使用 NVL 函数 使用IF-THEN-ELSE 逻辑