《《基本SQL语句》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《基本SQL语句》PPT课件.ppt（52页珍藏版）》请在金锄头文库上搜索。
1、3Copyright 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 函
2、数函数函数函数函数函数输入输入参数参数参数参数1 1参数参数参数参数2 2参数参数参数参数n n函数执行函数执行输出输出结果结果结果结果3-4Copyright Oracle Corporation, 2001. All rights reserved.两种两种 SQL 函数函数函数函数函数函数单行函数单行函数单行函数单行函数多行函数多行函数多行函数多行函数3-5Copyright Oracle Corporation, 2001. All rights reserved.单行函数单行函数单行函数单行函数:操作数句对象操作数句对象接受函数返回一个结果接受函数返回一个结果只对一行进行变换只对一行
3、进行变换每行返回一个结果每行返回一个结果可以转换数据类型可以转换数据类型可以嵌套可以嵌套参数可以是一列或一个值参数可以是一列或一个值function_name (arg1, arg2,.)3-6Copyright Oracle Corporation, 2001. All rights reserved.单行函数单行函数转换转换转换转换字符字符字符字符数值数值数值数值日期日期日期日期通用通用通用通用单行函数单行函数单行函数单行函数3-7Copyright Oracle Corporation, 2001. All rights reserved.字符函数字符函数字符函数字符函数字符函数字符函数
4、LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE大小写控制函数大小写控制函数大小写控制函数大小写控制函数字符控制函数字符控制函数字符控制函数字符控制函数3-8Copyright Oracle Corporation, 2001. All rights reserved.函数函数结果结果大小写控制函数大小写控制函数这类函数改变字符的大小写。这类函数改变字符的大小写。LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Co
5、urse3-9Copyright Oracle Corporation, 2001. All rights reserved.大小写控制函数大小写控制函数显示员工显示员工 Higgins的信息的信息:SELECT employee_id, last_name, department_idFROM employeesWHERE last_name = higgins;no rows selectedno rows selectedSELECT employee_id, last_name, department_idFROM employeesWHERE LOWER(last_name) = h
6、iggins;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)HelloWorldHello106*2400024000*elloWorld函数函数结果结果字符控制函数字符控制函数这类函数控制字符这类函数控制字符:3-11Copyright Orac
7、le 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 employeesWHERE SUBSTR(job_id, 4) = REP;字符控制函数字符控制函数1231233-12Copyright Oracle Corporation, 2001. All rights reserved.数字函数数字函数ROUND: 四舍五入四舍
8、五入ROUND(45.926, 2)45.93TRUNC: 截断截断TRUNC(45.926, 2) 45.92MOD: 求余求余MOD(1600, 300)1003-13Copyright Oracle Corporation, 2001. All rights reserved.SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;ROUND 函数函数DUAL 是一个是一个伪表伪表，可以用来测试函数和表达式。，可以用来测试函数和表达式。1233123-14Copyright Oracle Corporation
9、, 2001. All rights reserved.SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;TRUNC 函数函数3121233-15Copyright Oracle Corporation, 2001. All rights reserved.SELECT last_name, salary, MOD(salary, 5000)FROM employeesWHERE job_id = SA_REP;MOD 函数函数3-16Copyright Oracle Corporation, 2001. All
10、rights reserved.日期日期Oracle 内部使用数字存储日期内部使用数字存储日期: 世纪世纪,年年,月月,日日,小时小时,分钟分钟,秒。秒。默认的日期格式是默认的日期格式是 DD-MON-RR.可以只指定年的后两位在可以只指定年的后两位在20世纪存放世纪存放21世纪的日期。世纪的日期。 同样可以在同样可以在21世纪存放世纪存放20世纪的日期。世纪的日期。SELECT last_name, hire_dateFROM employeesWHERE last_name like G%;3-17Copyright Oracle Corporation, 2001. All rights
11、 reserved.日期日期函数函数SYSDATE 返回返回:日期日期时间时间3-18Copyright Oracle Corporation, 2001. All rights reserved.日期的数学运算日期的数学运算在日期上加上或减去一个数字结果仍为日期。在日期上加上或减去一个数字结果仍为日期。两个日期相减返回日期之间相差的天数。两个日期相减返回日期之间相差的天数。可以用数字除可以用数字除24来向日期中加上或减去小时。来向日期中加上或减去小时。3-19Copyright Oracle Corporation, 2001. All rights reserved.日期的数学运算日期的数
12、学运算SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90;3-20Copyright Oracle Corporation, 2001. All rights reserved.日期函数日期函数两个日期相差的月数两个日期相差的月数MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 向指定日期中加上若干月数向指定日期中加上若干月数指定日期的下一个日期指定日期的下一个日期本月的最后一天本月的最后一天日期四舍五入日期四舍五入 日期
13、截断日期截断函数函数描述描述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.677419411-JUL-9408-SEP-9528-FEB-953-22Copyright Oracle Corporation, 2001. All rights reserved.ROUND(SYSDATE
14、,MONTH) 01-AUG-95ROUND(SYSDATE ,YEAR) 01-JAN-96TRUNC(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 righ
15、ts reserved.隐式数据类型转换隐式数据类型转换Oracle 自动完成下列转换：自动完成下列转换：VARCHAR2 or CHAR源数据类型源数据类型目标数据类型目标数据类型VARCHAR2 or CHARNUMBERDATENUMBERDATEVARCHAR2VARCHAR23-25Copyright Oracle Corporation, 2001. All rights reserved.隐式数据类型转换隐式数据类型转换表达式计算中表达式计算中, Oracle 自动完成下列转换自动完成下列转换:VARCHAR2 or CHAR源数据类型源数据类型目标数据类型目标数据类型VARCH
16、AR2 or CHARNUMBERDATE3-27Copyright Oracle Corporation, 2001. All rights reserved.TO_CHAR 函数对日期的转换函数对日期的转换格式格式:必须包含在单引号中而且大小写敏感。必须包含在单引号中而且大小写敏感。可以包含任意的有效的日期格式。可以包含任意的有效的日期格式。可以使用可以使用 fm 去掉多余的空格或者前导零。去掉多余的空格或者前导零。与日期指用逗号隔开。与日期指用逗号隔开。TO_CHAR(date, format_model)3-28Copyright Oracle Corporation, 2001. A
17、ll rights reserved.YYYY日期格式的元素日期格式的元素YEARMMMONTHDYDAY2004TWO THOUSAND AND FOUR02MONMONDAYJULYMONJULDD023-29Copyright Oracle Corporation, 2001. All rights reserved.日期格式的元素日期格式的元素时间格式时间格式使用双引号向日期中添加字符使用双引号向日期中添加字符日期在月份中的位置日期在月份中的位置HH24:MI:SS AM15:45:32 PMDD of MONTH12 of OCTOBERddspthfourteenth3-30Cop
18、yright Oracle Corporation, 2001. All rights reserved.TO_CHAR 函数对日期的转换函数对日期的转换SELECT last_name, TO_CHAR(hire_date, fmDD Month YYYY) AS HIREDATEFROM employees;3-31Copyright Oracle Corporation, 2001. All rights reserved.TO_CHAR 函数对数字的转换函数对数字的转换下面是在下面是在TO_CHAR 函数中经常使用的几种格式函数中经常使用的几种格式:TO_CHAR(number, fo
19、rmat_model)90$L.,数字数字零零美元符美元符本地货币符号本地货币符号小数点小数点千位符千位符3-32Copyright Oracle Corporation, 2001. All rights reserved.SELECT TO_CHAR(salary, $99,999.00) SALARYFROM employeesWHERE last_name = Ernst;TO_CHAR函数对数字的转换函数对数字的转换3-33Copyright Oracle Corporation, 2001. All rights reserved.TO_NUMBER 和和 TO_DATE 函数函数
20、 使用使用 TO_NUMBER 函数将字符转换成数字函数将字符转换成数字:使用使用 TO_DATE 函数将字符转换成日期函数将字符转换成日期:这些函数可以使用这些函数可以使用fx 修饰符。修饰符。 nTO_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 函数将字符转换成日
21、期函数将字符转换成日期:这些函数可以使用这些函数可以使用 fx 修饰符。修饰符。TO_NUMBER(char, format_model)TO_DATE(char, format_model)3-35Copyright Oracle Corporation, 2001. All rights reserved.RR 日期格式日期格式当前年当前年1995199520012001日期日期27-OCT-9527-OCT-1727-OCT-1727-OCT-95RR 格式格式1995201720171995YY 格式格式1995191720172095当前的年份当前的年份:04904950995099
22、The return date is in the current centuryThe return date is in the century after the current oneThe return date is in the century before the current oneThe return date is in the current century指定的年份指定的年份:3-36Copyright Oracle Corporation, 2001. All rights reserved.RR 日期格式日期格式SELECT last_name, TO_CHAR
23、(hire_date, DD-Mon-YYYY)FROM employeesWHERE hire_date TO_DATE(01-Jan-90, DD-Mon-RR);使用使用使用使用RRRRRRRR日期格式查找雇佣日期在日期格式查找雇佣日期在日期格式查找雇佣日期在日期格式查找雇佣日期在1990199019901990年之前的员工，年之前的员工，年之前的员工，年之前的员工，在在在在1999199919991999或现在使用下面的命令会产生相同的结果或现在使用下面的命令会产生相同的结果或现在使用下面的命令会产生相同的结果或现在使用下面的命令会产生相同的结果: : :3-37Copyright O
24、racle Corporation, 2001. All rights reserved.嵌套函数嵌套函数单行函数可以嵌套。单行函数可以嵌套。嵌套函数的执行顺序是由内到外。嵌套函数的执行顺序是由内到外。F3(F2(F1(col,arg1),arg2),arg3)步骤步骤1 = 结果结果1步骤步骤2 =结果结果2步骤步骤3 =结果结果33-38Copyright Oracle Corporation, 2001. All rights reserved.SELECT last_name, NVL(TO_CHAR(manager_id), No Manager)FROM employeesWHER
25、E 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
26、.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(commis
27、sion_pct, 0) AN_SALFROM employees;使用使用NVL函数函数12123-42Copyright Oracle Corporation, 2001. All rights reserved.SELECT last_name, salary, commission_pct, NVL2(commission_pct, SAL+COMM, SAL) incomeFROM employees WHERE department_id IN (50, 80);使用使用 NVL2 函数函数12123-43Copyright Oracle Corporation, 2001. Al
28、l rights reserved.SELECT first_name, LENGTH(first_name) expr1, last_name, LENGTH(last_name) expr2, NULLIF(LENGTH(first_name), LENGTH(last_name) resultFROM employees;使用使用 NULLIF 函数函数1231233-44Copyright Oracle Corporation, 2001. All rights reserved.使用使用 COALESCE 函数函数COALESCE 与与 NVL 相比的优点在于相比的优点在于 COAL
29、ESCE 可以同可以同时处理交替的多个值。时处理交替的多个值。如果第一个表达式费空如果第一个表达式费空,则返回这个表达式，对其他的参则返回这个表达式，对其他的参数进行数进行COALESCE 。3-45Copyright Oracle Corporation, 2001. All rights reserved.SELECT last_name, COALESCE(commission_pct, salary, 10) commFROM employeesORDER BY commission_pct;使用使用 COALESCE 函数函数3-46Copyright Oracle Corporat
30、ion, 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_ex
31、pr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND3-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_SALARYFROM employee
32、s;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 las
33、t_name, job_id, salary, DECODE(job_id, IT_PROG, 1.10*salary, ST_CLERK, 1.15*salary, SA_REP, 1.20*salary, salary) REVISED_SALARYFROM 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,
34、0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATEFROM employeesWHERE department_id = 80;使用使用decode函数的一个例子：函数的一个例子：3-52Copyright Oracle Corporation, 2001. All rights reserved.总结总结通过本章学习，您应该学会通过本章学习，您应该学会: 使用函数对数据进行计算使用函数对数据进行计算使用函数修改数据使用函数修改数据使用函数控制一组数据的输出格式使用函数控制一组数据的输出格式使用函数改变日期的显示格式使用函数改变日期的显示格式使用函数改变数据类型使用函数改变数据类型使用使用 NVL 函数函数使用使用IF-THEN-ELSE 逻辑逻辑
