Lesson单行函数.ppt

《Lesson单行函数.ppt》由会员分享，可在线阅读，更多相关《Lesson单行函数.ppt（76页珍藏版）》请在三一办公上搜索。

1、单行函数,学习目标,通过本节课的学习，将掌握以下知点:SQL中不同类型的函数在 SELECT 语句中使用字符，数字和日期函数转换型函数的应用,SQL 函数,函数,函数执行,两种 SQL 函数,函数,单行函数,多行函数,单行函数,单行函数:操作数据对象接受函数返回一个结果只对一行进行变换每行返回一个结果可以转换数据类型可以嵌套参数可以是一列或一个值,function_name(arg1,arg2,.),单行函数,转换,字符,数值,日期,通用,单行函数,字符函数,LOWERUPPERINITCAP,CONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE,大小写转换函

2、数,这类函数改变字符的大小写,使用大小写转换函数,显示员工 SMITH的员工号、名字、部门号:,SELECT empno,ename,deptnoFROM empWHERE ename=smith;no rows selected,Where 中字符比较是大小写敏感的,字符处理函数,这类函数控制字符:,扩展：考虑下面的问题Select trim(a from aaahhsbbb)from dual结果是多少？,字符处理函数,数字函数,ROUND：4舍5入到指定的小数位ROUND(45.926,2)45.93TRUNC：将值截断到指定的小数位TRUNC(45.926,2)45.92MOD：返回相

3、除后的余数MOD(1600,300)100,SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROM DUAL;,ROUND 函数,DUAL 是一个伪表，可以用来测试函数和表达式。,1,2,3,3,1,2,SELECT TRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2)FROM DUAL;,TRUNC 函数,3,1,2,1,2,3,MOD 函数,日期,Oracle 内部使用数字存储日期:世纪,年,月,日,小时,分钟,秒。默认的日期格式是：DD-MON-RR.(如：31-12月-01)可以只指

4、定年的后两位在20世纪存放21世纪的日期。同样可以在21世纪存放20世纪的日期。SYSDATE 是返回日期和时间的函数，返回当前Oracle系统的是日期时间,SELECT ename,hiredateFROM empWHERE ename like A%;,日期,函数SYSDATE 返回:日期时间,日期的数学运算,在日期上加上或减去一个数字结果仍为日期。两个日期相减返回日期之间相差的天数。可以用数字除24来向日期中加上或减去小时。,日期的运算示例：,从日期中加或减一个数值,还是得到一个日期结果值 SELECT to_date(2011-03-30,yyyy-mm-dd)+2 FROM dual

5、两个日期相减以便得到他们相差多少天 SELECT to_date(2011-03-20,yyyy-mm-dd)-to_date(2010-03-20,yyyy-mm-dd)FROM dual注：日期不能加日期！,日期的数学运算,日期函数,两个日期相差的月数,MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC,向指定日期中加上若干月数,指定日期的下一个日期,一个月的最后一天的日期,日期四舍五入,日期截断,函数,描述,使用日期函数,ROUND(SYSDATE,MONTH)01-AUG-95,ROUND(SYSDATE,YEAR)01-JA

6、N-96,TRUNC(SYSDATE,MONTH)01-JUL-95,TRUNC(SYSDATE,YEAR)01-JAN-95,日期函数,如果当前日期为：25-JUL-95，阅读下面的示例:,使用日期函数ROUND,select ROUND(to_date(2010-07-01,yyyy-mm-dd),yyyy)from dual或select ROUND(to_date(2010-07-01,yyyy-mm-dd),YEAR)from dual或select ROUND(to_date(2010-07-01,yyyy-mm-dd),yy)from dual,01-1月-11注：Oracle的

7、ROUND函数在对年进行四舍五入的判断是：超过一年的一半(即6月30日)，Round函数则返回下一年，否则返回当年的1月1日,select ROUND(to_date(2010-06-30,yyyy-mm-dd),yyyy)from dual或select ROUND(to_date(2010-06-30,yyyy-mm-dd),YEAR)from dual或select ROUND(to_date(2010-06-30,yyyy-mm-dd),yy)from dual,01-1月-10注：Oracle的ROUND函数在对年进行四舍五入的判断是：超过一年的一半(即6月30日)，Round函数则

8、返回下一年，否则返回当年的1月1日,使用日期函数ROUND,select ROUND(to_date(2010-07-16,yyyy-mm-dd),mm)from dual或select ROUND(to_date(2010-07-16,yyyy-mm-dd),MONTH)from dual,01-8月-10注：Oracle的ROUND函数在对月进行四舍五入的判断是：超过一个月的一半(即超过当月15号)，Round函数则返回下一个月1日，否则返回当月的1日,使用日期函数ROUND,select ROUND(to_date(2010-07-15,yyyy-mm-dd),mm)from dual或

9、select ROUND(to_date(2010-07-15,yyyy-mm-dd),MONTH)from dual,01-7月-10注：Oracle的ROUND函数在对月进行四舍五入的判断是：超过一个月的一半(即超过当月15号)，Round函数则返回下一个月1日，否则返回当月的1日,使用日期函数ROUND,select ROUND(to_date(2010-09-21 12:00:01,yyyy-mm-dd hh24:mi:ss),dd)from dual,22-9月-10说明：示例中当前的时间是下午 12:00:01分，已经超过了12:00:00 AM这个中界线，所以Round返回22-

10、9月-10，而非21-9月-10,使用日期函数ROUND,select ROUND(to_date(2010-09-21 11:59:59,yyyy-mm-dd hh24:mi:ss),dd)from dual,21-9月-10说明：示例中当前的时间是上午 11:59:59分，没有超过了12:00:00 AM这个中界线，所以Round返回21-9月-10,使用日期函数ROUND,select ROUND(to_date(2010-09-21 12:00:01,yyyy-mm-dd hh24:mi:ss),day)from dual,19-9月-10,使用日期函数ROUND,说明：示例中当前的时

11、间是下午 12:00:01分，已经超过了12:00:00 AM这个中界线，但是是按day进行四舍五入，即对星期进行四舍五入，一旦一个星期超过了星期三，则自动返回下一个星期的第一天，由于示例中的日期对应的是星期二，所以Round只返回19-9月-10，即本周第1天。按星期四舍五入时，不考虑时间部分,select ROUND(to_date(2010-09-21,yyyy-mm-dd),dd)from dual,21-9月-10说明：示例中没有指定时间字段，所以Round只返回21-9月-10，即当前指定的日期,使用日期函数ROUND,select ROUND(to_date(2010-09-21

12、,yyyy-mm-dd),day)from dual,19-9月-10,使用日期函数ROUND,说明：示例中没有指定时间字段，而且使用的是按day进行四舍五入，即对星期进行四舍五入，一旦一个星期超过了星期三，则自动返回下一个星期的第一天，由于示例中的日期对应的是星期二，所以Round只返回19-9月-10，即本周第1天。,select trunc(to_date(2010-09-21,yyyy-mm-dd),yyyy)from dual或select trunc(to_date(2010-09-21,yyyy-mm-dd),YEAR)from dual或select trunc(to_date

13、(2010-09-21,yyyy-mm-dd),yy)from dual,01-1月-10注：返回当年第一天,使用日期函数-TRUNC,使用日期函数-TRUNC,select TRUNC(to_date(2010-09-21,yyyy-mm-dd),mm)from dual或 select TRUNC(to_date(2010-09-21,yyyy-mm-dd),MONTH)from dual,01-9月-10注：返回当月第一天,select TRUNC(to_date(2010-09-21,yyyy-mm-dd),dd)from dualselect TRUNC(to_date(2010-0

14、9-21,yyyy-mm-dd),day)from dual,21-9月-10注：返回当天,19-9月-10注：返回本周第一天,使用日期函数-TRUNC,写出下列SQL语句的结果select Round(sysdate,Month)Month,round(sysdate,mm)MM,round(sysdate,dd)dd,round(sysdate,day)day from dualselect sysdate S1,trunc(sysdate)S2,trunc(sysdate,year)YEAR,trunc(sysdate,month)MONTH,trunc(sysdate,day)DAY,

15、trunc(sysdate,dd)dd from dual注：sysdate包含了时间字段,转换函数,注：尽管可以使用隐式数据类型转换，但是还是建议执行显式的数据类型转换，以确保SQL语句的可靠性。,隐式数据类型转换,Oracle 自动完成下列转换：,VARCHAR2 or CHAR,源数据类型,目标数据类型,VARCHAR2 or CHAR,NUMBER,DATE,NUMBER,DATE,VARCHAR2,VARCHAR2,隐式数据类型转换,表达式计算中,Oracle 自动完成下列转换:,显式数据类型转换,TO_CHAR 函数对日期的转换,格式:必须包含在单引号中而且大小写敏感可以包含任意的

16、有效的日期格式可以使用 fm 去掉多余的空格或者前导零与日期值用逗号隔开,TO_CHAR(date,format_model),YYYY,日期格式的元素,YEAR,MM,MONTH,DY,DAY,以数字表示全年，如：2013,年的拼写,月的两数字值,周中的天的三字母缩写,周中的天的全名,月的全名,MON,月的三字母缩写,DD,月的数字天,日期格式基础,时间格式作为日期的一部分,ddspth 日期的序数词拼写,fourteenth,select to_char(sysdate,ddspth)from dual,select to_char(sysdate,DD of MONTH)from dua

17、l,select to_char(sysdate,HH24:MI:SS AM)from dual,用双引号括起来以向日期中添加字符串,数值拼写，日期在月份中的位置,TO_CHAR 函数对日期的转换,其中：fm可省略,TO_CHAR 函数对数字的转换,下面是在TO_CHAR 函数中经常使用的几种格式:,TO_CHAR(number,format_model),select to_char(sal,L999,999.99)from emp,SELECT TO_CHAR(sal,$99,999.00)SALARYFROM empWHERE ename=SMITH;,TO_CHAR函数对数字的转换,T

18、O_NUMBER 和 TO_DATE 函数,使用 TO_NUMBER 函数将字符转换成数字:使用 TO_DATE 函数将字符转换成日期:这些函数可以使用 fx 修饰符。,TO_NUMBER(char,format_model),TO_DATE(char,format_model),RR 日期格式,RR 日期格式,使用RR日期格式查找雇佣日期在1990年之前的员工，在1999或现在使用下面的命令会产生相同的结果:,TO_NUMBER和TO_DATE函数,使用TO_NUMBER函数将字符转换为数字,使用TO_DATE函数将字符转换为日期,TO_NUMBER(char,fmt),TO_DATE(ch

19、ar,fmt)insert into t_user_hibernate(i_id,v_name,v_password,create_time,create_ts)values(aaaa,vvv,123,to_date(2010-11-09 10:21:09,yyyy-mm-dd hh24:mi:ss),to_date(2010-11-09 10:21:09,yyyy-mm-dd hh24:mi:ss)其中：create_ts字段类型为timestamp，date类型的字段也是可以插入时间的，但是在默认查询时，时间部分并不列出。查询可使用如下SQL语句：select to_char(create

20、_time,yyyy-mm-dd hh24:mi:ss)from t_user_hibernate,嵌套函数,单行函数可以嵌套。嵌套函数的执行顺序是由内到外。,嵌套函数,通用函数,这些函数适用于任何数据类型，同时也适用于空值：NVL(expr1,expr2)NVL2(expr1,expr2,expr3)NULLIF(expr1,expr2)COALESCE(expr1,expr2,.,exprn),NVL 函数,将空值转换成一个实际的值：可以使用的数据类型有日期、字符、数字。数据类型必须匹配，即NVL中的两个参数类型必须一致函数的一般形式:NVL(comm,0)NVL(hiredate,01-

21、1月-97)NVL(job,No Job Yet)示例：select nvl(comm,0)from emp,使用NVL函数,使用 NVL2 函数,使用 NULLIF 函数,使用 COALESCE 函数,COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。如果第一个表达式非空,则返回这个表达式，对其他的参数进行COALESCE。,使用 COALESCE 函数,条件表达式,在 SQL 语句中使用IF-THEN-ELSE 逻辑使用两种方法:CASE 表达式DECODE 函数,CASE 表达式,在需要使用 IF-THEN-ELSE 逻辑时:,CASE expr

22、WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND,CASE 表达式,下面是使用case表达式的一个例子：,DECODE 函数,在需要使用 IF-THEN-ELSE 逻辑时:,DECODE(col|expression,search1,result1,search2,result2,.,default),DECODE 函数,SELECT ename,job,sal,DECO

23、DE(job,CLERK,1.10*sal,SALESMAN,1.15*sal,MANAGER,1.20*sal,sal)REVISED_SALARYFROM emp;,DECODE 函数,使用decode函数的一个例子：,UNION ALL,INTERSECT,MINUS操作符,UNION 返回每个查询的所有去重的结果UNION ALL 返回每一个查询的结果，包括重复记录INTERSECT 返回两个查询的交集，不包括重复记录MINUS 返回第一个查询中存在而第二个查询中不存在的记录，即两个查询的差集，不包括重复记录,集合操作,在 Oracle 中提供了三种类型集合操作：并（UNION）、交（

24、INTERSECT）、差（MINUS）UNION：将多个查询的结果组合到一个查询结果之中，没有重复内容UNION ALL：也是将多个查询结果组合到一个查询之中，但是包含重复值INTERSECT：返回多个查询结果中相同的部分MINUS：返回两个查询结果的差集（去掉两个集合中相同的结果）,下面复制 emp 表，将 20 部门的雇员信息取出来，复制到emp20表中，便于演示各种集合操作：CREATE TABLE emp20 AS SELECT*FROM emp WHERE deptno=20;,UNION,返回两个查询的所有内容，但是不包含重复的内容 SELECT*FROM empUNIONSELE

25、CT*FROM emp20;,UNION ALL,返回两个查询中的所有内容（包括重复内容）SELECT*FROM emp UNION ALL SELECT*FROM emp20;,INTERSECT（交集）,返回两个查询结果中相同的部分 SELECT*FROM emp INTERSECT SELECT*FROM emp20;因为两张表中只有部门20的内容是重复的，所以只返回了 20 部门的数据。,MINUS,返回两个查询结果中不相同的部分。SELECT*FROM emp MINUS SELECT*FROM emp20;由于两个表中都有部门20的信息，所以返回的结果是将部门20（两个表中相同）的

26、记录全部去掉。,小结,通过本章学习，您应该学会:使用函数对数据进行计算使用函数修改数据使用函数控制一组数据的输出格式使用函数改变日期的显示格式使用函数改变数据类型使用 NVL 函数使用IF-THEN-ELSE 逻辑,练习 3,1.写一个查询显示当前日期，列标签显示为 Date。2.对每一个雇员，显示 empno、ename、sal 和 sal 增加 15%，并且表示成整数，列标签显示为 New Salary。3.写一个查询用首字母大写，其它字母小写显示雇员的 last names，显示last names名字的长度，对所有名字开始字母是 J、A 或 M 的雇员，给每列一个适当的标签。用雇员的

27、last names 排序结果。4.对每一个雇员，显示其的ename，并且计算从雇员受雇日期到今天的月数，列标签MONTHS_WORKED。按受雇月数排序结果，四舍五入月数到最靠近的整数月。,5.写一个查询对每个雇员做计算：earns monthly but wants。列标签 Dream Salaries，sal要使用$99,999.00 的格式显示。如果有时间，完成下面的练习：6.创建一个查询显示所有雇员的 ename 和 sal。格式化为 15 个字符长度，用$左填充，列标签 SALARY。7.显示每一个雇员的ename、hiredate 和 sal，检查日期，该日期是服务六个月后的第一个星期一，列标签 REVIEW。格式化日期显示看起来象“Monday,the Thirty-First of July,2000”的样子。,如果你想要接受更大的挑战，完成下面的练习：8.用 DECODE 函数，写一个查询，按照下面的数据显示所有雇员的基于 JOB_ID 列值的级别。工作 级别 PRESIDENT A MANAGER B ANALYST C SALESMAN D CLERK E 不在上面的 0 9.用 CASE 语法，完成第8题。,