第05章 数据库内置函数

第五章数据库内置函数本章主要内容5.1前言介绍内置函数。5.2数学函数描述各个数学函数的语法。5.3字符函数描述各个字符函数的语法。5.4日期函数描述各个日期函数的语法。5.5其他函数描述各个其他函数的语法。5.1前言介绍常用的一些内置函数，它们在数据库的一些操作中有着很便捷的也很重要的作用。尤其是一些常用的数学函数，字符函数，日期函数等。SQL函数函数输入参数1参数2参数n函数执行输出结果数学函数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取小于或等于指定表达式的最大整数FLOORSELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMSYSIBM.SYSDUMMY1;ROUND函数SYSIBM.SYSDUMMY1是一个‘伪表’，可以用来测试函数和表达式。123312SELECTTRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2)FROMSYSIBM.SYSDUMMY1;TRUNC函数312123SELECT50,6,MOD(50,6)FROMSYSIBM.SYSDUMMY1;MOD函数字符串函数举例描述函数名SELECTLENGTH('SQLServer课程')返回：12返回传递给它的字符串长度LENGTHSELECTLOWER('SQLServer课程')返回：sqlserver课程把传递给它的字符串转换为小写LOWERSELECTUPPER('sqlserver课程')返回：SQLSERVER课程把传递给它的字符串转换为大写UPPERSELECTLTRIM('周智宇')返回：周智宇（后面的空格保留）清除字符左边的空格LTRIMSELECTRTRIM('周智宇')返回：周智宇（前面的空格保留）清除字符右边的空格RTRIMSELECTRIGHT('买卖提.吐尔松',3)返回：吐尔松从字符串右边返回指定数目的字符RIGHTSELECTREPLACE('莫乐可切.杨可','可','兰')返回：莫乐兰切.杨兰替换一个字符串中的字符REPLACE字符函数字符函数LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE大小写控制函数字符控制函数函数结果大小写控制函数这类函数改变字符的大小写。LOWER('SQLCourse')UPPER('SQLCourse')INITCAP('SQLCourse')sqlcourseSQLCOURSESqlCourseCONCAT('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函数结果字符控制函数这类函数控制字符:日期函数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()取得当前的系统年、日期，时间，秒，月份，分钟，小时日期函数可以使用英语来执行日期和时间计算SELECTcurrentdate+1YEARFROMSYSIBM.SYSDUMMY1;SELECTcurrentdate+3YEARS+2MONTHS+15DAYSFROMSYSIBM.SYSDUMMY1;SELECTcurrenttime+5HOURS-3MINUTES+10SECONDSFROMSYSIBM.SYSDUMMY1;日期的数学运算两个日期相减返回日期之间相差的天数。如果想将日期或时间值与其它文本相衔接，那么需要先将该值转换成字符串。为此，只要使用CHAR()函数：SELECTdays(currentdate)-days(date('1999-10-22'))FROMSYSIBM.SYSDUMMY1;SELECT'日期为：'||char(currentdate)FROMSYSIBM.SYSDUMMY1;SELECT'时间为：'||char(currenttime)FROMSYSIBM.SYSDUMMY1;日期的数学运算将字符串转换成日期或时间值SELECTTIMESTAMP('2002-10-20-12.00.00.000000');FROMSYSIBM.SYSDUMMY1;SELECTTIMESTAMP('2002-10-2012:00:00');FROMSYSIBM.SYSDUMMY1;SELECTDATE('2002-10-20');FROMSYSIBM.SYSDUMMY1;SELECTDATE('10/20/2002');FROMSYSIBM.SYSDUMMY1;SELECTTIME('12:00:00');FROMSYSIBM.SYSDUMMY1;SELECTTIME('12.00.00');FROMSYSIBM.SYSDUMMY1;日期操作的内置函数表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()从已使用字符模板解释的字符串返回时间戳记转换函数隐性显性数据类型转换隐式数据类型转换自动完成下列转换：VARCHAR2orCHAR源数据类型目标数据类型VARCHAR2orCHARINTDATEINTDATEVARCHAR2VARCHAR2嵌套函数单行函数可以嵌套。嵌套函数的执行顺序是由内到外。F3(F2(F1(col,arg1),arg2),arg3)步骤1=结果1步骤2=结果2步骤3=结果3通用函数这些函数适用于任何数据类型，同时也适用于空值：NVL(expr1,expr2)Toconvertanullvaluetoanactualvalue,usetheNVLfunctionNULLIF(expr1,expr2)TheNULLIFfunctioncomparestwoexpressions.Iftheyareequal,thefunctionreturnsnull.Iftheyarenotequal,thefunctionreturnsthefirstexpression.YoucannotspecifytheliteralNULLforfirstexpression.COALESCE(expr1,expr2,...,exprn)TheCOALESCEfunctionreturnsthefirstnon-nullexpressioninthelistNVL函数将空值转换成一个已知的值：可以使用的数据类型有日期、字符、数字。函数的一般形式:NVL(referred,0)NVL(stubirth,‘2009-10-10')NVL(job_id,'NoJobYet')NVL(数值类型，0)SELECTCUSTOMER,CUSTOMER_NAME,REFERREDFROMTBL_CUSTOMER;SELECTCUSTOMER,CUSTOMER_NAME,NVL(REFERRED,0)FROMTBL_CUSTOMER;NVL(日期类型，‘2009-10-10’)SELECTSTUNO,STUNAME,STUBIRTHFROMTBL_STUDENTINFO;SELECTSTUNO,STUNAME,NVL(STUBIRTH,’2009-10-10’FROMTBL_STUDENTINFO;NVL(字符串类型，‘aaa’)SELECTCUSTOMER,CUSTOMER_NAME,TEL_NOFROMTBL_CUSTOMER;SELECTCUSTOMER,CUSTOMER_NAME,NVL(TEL_NO,’025-12345678’)FROMTBL_CUSTOMER;SELECTTitle,NVL(Cost,0)Cost,NVL(Retail,0)Retail,(NVL(Retail,0)-NVL(Cost,0))/NVL(Retail,1)profitFROMTBL_BOOKS;使用NVL函数121233SELECTTitle,Cost,Retail,NULLIF(Cost,Retail)expFROMTBL_BOOKSorderbyIsbn;使用NULLIF函数131232使用COALESCE函数COALESCE与NVL相比的优点在于COALESCE可以同时处理交替的多个值。如果第一个表达式非空,则返回这个表达式，对其他的参数进行COALESCE。使用COALESCE函数SELECTREFERRED,TEL_NO,CUSTOMER_NAMEFROMTBL_CUSTOMER;SELECTCOALESCE(CHAR(REFERRED),TEL_NO,CUSTOMER_NAME)FROMTBL_CUSTOMER;条件表达式在SQL语句中使用IF-THEN-ELSE逻辑。使用两种方法:CASE表达式DECODE函数CASE表达式在需要使用IF-THEN-ELSE逻辑时:CASEexprWHENcomparison_expr1THENreturn_expr1[WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_expr]ENDSELECTOrder,Cust