数据库内置函数

第1页第五章数据库内置函数

第2页本章主要内容5.1序言介绍内置函数。5.2数学函数描述各个数学函数语法。5.3字符函数描述各个字符函数语法。5.4日期函数描述各个日期函数语法。5.5其它函数描述各个其它函数语法。第3页5.1序言

介绍惯用一些内置函数，它们在数据库一些操作中有着很便捷也很主要作用。尤其是一些惯用数学函数，字符函数，日期函数等。第4页SQL函数函数输入参数1参数2参数n函数执行输出结果第5页数学函数SELECTSQRT(9)返回：3取浮点表示式平方根SqrtSELECTABS(-4)FROMSYSIBM.SYSDUMMY1;返回：4取数值表示式绝对值ABS举例描述函数名SELECTCEILING(43.5)返回：44返回大于或等于所给数字表示式最小整数CEILINGSELECTPOWER(5,2)返回：25取数值表示式幂值POWERSELECTROUND(43.543,1)返回：43.5将数值表示式四舍五入为指定精度ROUNDSELECTSIGN(-43)返回：-1对于正数返回+1，对于负数返回-1，对于0则返回0SignSELECTFLOOR(43.5)返回：43取小于或等于指定表示式最大整数FLOOR第6页SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMSYSIBM.SYSDUMMY1;ROUND函数SYSIBM.SYSDUMMY1是一个‘伪表’，能够用来测试函数和表示式。123312第7页SELECTTRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2)FROMSYSIBM.SYSDUMMY1;TRUNC函数312123第8页SELECT50,6,MOD(50,6)FROMSYSIBM.SYSDUMMY1;MOD函数第9页字符串函数举例描述函数名SELECTLENGTH('SQLServer课程')返回：12返回传递给它字符串长度LENGTHSELECTLOWER('SQLServer课程')返回：sqlserver课程把传递给它字符串转换为小写LOWERSELECTUPPER('sqlserver课程')返回：SQLSERVER课程把传递给它字符串转换为大写UPPERSELECTLTRIM('周智宇')返回：周智宇（后面空格保留）去除字符左边空格LTRIMSELECTRTRIM('周智宇')返回：周智宇（前面空格保留）去除字符右边空格RTRIMSELECTRIGHT('买卖提.吐尔松',3)返回：吐尔松从字符串右边返回指定数目标字符RIGHTSELECTREPLACE('莫乐可切.杨可','可','兰')返回：莫乐兰切.杨兰替换一个字符串中字符REPLACE第10页字符函数字符函数LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE大小写控制函数字符控制函数第11页函数结果大小写控制函数这类函数改变字符大小写。LOWER('SQLCourse')UPPER('SQLCourse')INITCAP('SQLCourse')sqlcourseSQLCOURSESqlCourse第12页CONCAT('Hello','World')SUBSTR('HelloWorld',1,5)LENGTH('HelloWorld')INSTR('HelloWorld','W')DB2=POSSTRLPAD(24000,10,'*')RPAD(24000,10,'*')TRIM('H'FROM'HelloWorld')HelloWorldHello106*****2400024000*****elloWorld函数结果字符控制函数这类函数控制字符:第13页日期函数SELECTCURRENTDATEFROMSYSIBM.SYSDUMMY1;VALUESCURRENTDATE;取得当前系统日期CURRENTDATE举例描述函数名SELECTCURRENTTIMEFROMSYSIBM.SYSDUMMY1;VALUESCURRENTTIME;取得当前系统时间CURRENTTIMESELECTCURRENTTIMESTAMP返回：当前日期+时间取得当前系统日期+时间CURRENTTIMESTAMPSELECTYEAR(CURRENTTIMESTAMP)FROMSYSIBM.SYSDUMMY1SELECTDATE(CURRENTTIMESTAMP)SELECTTIME(CURRENTTIMESTAMP)SELECTSECOND(CURRENTTIMESTAMP)SELECTMONTH(CURRENTTIMESTAMP)SELECTMINUTE(CURRENTTIMESTAMP)YEAR()DATE()TIME()SECOND()MONTH()MINUTE()HOUR()取得当前系统年、日期，时间，秒，月份，分钟，小时第14页日期函数能够使用英语来执行日期和时间计算SELECTcurrentdate+1YEAR

FROMSYSIBM.SYSDUMMY1;SELECTcurrentdate+3YEARS+2MONTHS+15DAYS

FROMSYSIBM.SYSDUMMY1;SELECTcurrenttime+5HOURS-3MINUTES+10SECONDS

FROMSYSIBM.SYSDUMMY1;第15页日期数学运算两个日期相减返回日期之间相差日期。假如想将日期或时间值与其它文本相衔接，那么需要先将该值转换成字符串。为此，只要使用CHAR()函数：SELECTdays(currentdate)-days(date('1999-10-22'))FROMSYSIBM.SYSDUMMY1;SELECT'日期为：'||char(currentdate)FROMSYSIBM.SYSDUMMY1;SELECT'时间为：'||char(currenttime)FROMSYSIBM.SYSDUMMY1;第16页日期数学运算将字符串转换成日期或时间值SELECTTIMESTAMP('-10-20-12.00.00.000000')FROMSYSIBM.SYSDUMMY1;SELECTTIMESTAMP('-10-2012:00:00')FROMSYSIBM.SYSDUMMY1;SELECTDATE('-10-20')FROMSYSIBM.SYSDUMMY1;SELECTDATE('10/20/')FROMSYSIBM.SYSDUMMY1;SELECTTIME('12:00:00')FROMSYSIBM.SYSDUMMY1;SELECTTIME('12.00.00')FROMSYSIBM.SYSDUMMY1;第17页日期操作内置函数表DAYNAME()返回一个大小写混合字符串，对于参数日部分，用星期表示这一天名称（比如，Friday）。DAYOFWEEK()返回参数中星期几，用范围在1-7整数值表示，其中1代表星期日DAYOFYEAR()返回参数中一年中第几天，用范围在1-366整数值表示DAYS()返回日期整数表示JULIAN_DAY()返回从公元前4712年1月1日（儒略日历开始日期）到参数中指定日期值之间天数，用整数值表示MONTHNAME()对于参数月部分月份，返回一个大小写混合字符串（比如，January）TO_CHAR()返回已用字符模板进行格式化时间戳记字符表示TO_DATE()从已使用字符模板解释过字符串返回时间戳记WEEK()返回参数中一年第几周，用范围在1-54整数值表示。以星期日作为一周开始TIMESTAMP_FORMAT()从已使用字符模板解释字符串返回时间戳记第18页转换函数隐性显性数据类型转换第19页隐式数据类型转换自动完成以下转换：VARCHAR2orCHAR源数据类型目标数据类型VARCHAR2orCHARINTDATEINTDATEVARCHAR2VARCHAR2第20页嵌套函数单行函数能够嵌套。嵌套函数执行次序是由内到外。F3(F2(F1(col,arg1),arg2),arg3)步骤1=结果1步骤2=结果2步骤3=结果3第21页通用函数这些函数适合用于任何数据类型，同时也适合用于空值：NVL(expr1,expr2)Toconvertanullvaluetoanactualvalue,usetheNVLfunctionNULLIF(expr1,expr2)TheNULLIFfunctioncomparestwoexpressions.Iftheyareequal,thefunctionreturnsnull.Iftheyarenotequal,thefunctionreturnsthefirstexpression.YoucannotspecifytheliteralNULLforfirstexpression.COALESCE(expr1,expr2,...,exprn)TheCOALESCEfunctionreturnsthefirstnon-nullexpressioninthelist第22页NVL函数将空值转换成一个已知值：能够使用数据类型有日期、字符、数字。函数普通形式:NVL(referred,0)NVL(stubirth,‘-10-10')NVL(job_id,'NoJobYet')第23页NVL(数值类型，0)SELECTCUSTOMER,CUSTOMER_NAME,REFERRED

FROMTBL_CUSTOMER;SELECTCUSTOMER,CUSTOMER_NAME,NVL(REFERRED,0)

FROMTBL_CUSTOMER;第24页NVL(日期类型，‘-10-10’)SELECTSTUNO,STUNAME,STUBIRTH

FROMTBL_STUDENTINFO;SELECTSTUNO,STUNAME,NVL(STUBIRTH,’-10-10’

FROMTBL_STUDENTINFO;第25页NVL(字符串类型，‘aaa’)SELECT CUSTOMER, CUSTOMER_NAME,

TEL_NO

FROMTBL_CUSTOMER;SELECT CUSTOMER, CUSTOMER_NAME, NVL(TEL_NO,)FROMTBL_CUSTOMER;第26页SELECTTitle,NVL(Cost,0)Cost,NVL(Retail,0)Retail,(NVL(Retail,0)-NVL(Cost,0))/NVL(Retail,1)profit

FROMTBL_BOOKS;使用NVL函数121233第27页SELECTTitle,Cost,Retail,NULLIF(Cost,Retail)expFROMTBL_BOOKSorderbyIsbn;使用

NULLIF函数131232第28页使用

COALESCE函数COALESCE与

NVL相比优点在于

COALESCE能够同时处理交替多个值。假如第一个表示式非空,则返回这个表示式，对其它参数进行COALESCE。第29页使用

COALESCE函数SELECTREFERRED,TEL_NO,CUSTOMER_NAMEFROMTBL_CUSTOMER;SELECTCOALESCE(CHAR(REFERRED),TEL_NO,CUSTOMER_NAME)

FROMTBL_CUSTOMER;第30页条件表示式在

SQL

语句中使用IF-THEN-ELSE逻辑。使用两种方法:CASE表示式DECODE函数第31页CASE表示式在需要使用

IF-THEN-ELSE逻辑时:CASEexpr

WHENcomparison_expr1THENreturn_expr1[WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_expr]END第32页CASE表示式下面是使用case表示式一个例子：SELECT Order, Customer, Orderdate, Shipdate, Shipcity, CASEShipcity WHEN'上海'THEN'直辖市' WHEN'南京'THEN'省内' WHEN'无锡'THEN'省内' WHEN'常州'THEN'省内' ELSE'省外' END"Province"fromTBL_ORDERS;第33页DECODE函数在需要使用

IF-THEN-ELSE逻辑时:DECODE(col|expression,search1,result1 [,search2,result2,...,] [,default])第34页DECODE函数SELECTOrder,Customer,Orderdate,Shipdate,Shipcity,DECODE(Shipcity