SQL Server 数据库 从入门到精通 09 SQL函数的使用

SQL函数的说明SQLServer的函数Oracle的函数将NULL更改为其他值的函数IF…ELSE逻辑函数除了在SQL查询中使用正常的表达式外，在具体的数据库管理系统中，还可以使用任意的内置函数或者用户编写的存储数据库函数。用户可以使用函数来执行计算或基于输入参数的其他操作，也可以将某种数据转换成其他数据类型或显示格式。本章将通过具体的例子介绍如何使用这些函数。SQL函数的使用第章SQL技术与网络数据库开发详解·112·9.1SQL函数的说明在介绍函数的使用之前，先应知道SQL函数是不通用的。SQL函数与前面所讲的SQL语句不同，它们在不同的数据库管理系统中不能通用，因为每一个数据库管理系统都有一套自己的SQL函数，只有很少的函数在大多数DBMS中都能使用。为了让读者感受这一点，表9.1列出了一些例子。表9.1不同DBMS的函数差异功能Access函数SQLServer函数Oracle函数或语句MySQL函数获取字符串的某部分MID()SUBSTRING()SUBSTR()SUBSTRING()获取当前日期NOW()GETDATE()SYSDATECURDATE()转换数据类型有多个具体函数，例如CDATE()用于将字符转换为日期，CSTR()将数字转换为字符CONVERT()有多个具体函数，例如TO_DATE()用于将字符串转换为日期，TO_CHAR()将数字或日期转换为字符串CONVERT()由于SQL函数不通用，因此，如果在数据库应用程序的开发中使用了数据库函数，则其通用性和移植性就会变得很差，所以在软件开发时应尽量采用程序设计语言中的函数，避免使用数据库函数。9.2SQLServer的函数本节将介绍SQLServer中的类型转换函数、日期函数、数值函数和字符函数，并对较经常使用的函数举例说明。9.2.1类型转换函数转换函数将具体DBMS的数据值转换成其他数据类型或对其进行格式化。经常用到的转换是，将日期和数字转换成指定的字符串格式，或者将字符串转换成有效的日期或数值。在SQLServer中，使用CONVERT()和CAST()两个函数转换数据类型。1．CONVERT()函数CONVERT()函数的语法格式为：CONVERT(datatype[(length)],expression,[style])其中，datatype为数据类型，如果是CHAR、VARCHAR、BINARY或VARBINARY数据类型，则可以选择length参数设置长度；expression为表达式，如果要将日期型数据转换为字符型数据，还可以使用style参数设置日期显示格式。style参数的取值与日期显示格式如表9.2所示。第9章SQL函数的使用·113·表9.2style参数取值及对应日期格式style值（返回yy）style值（返回yyyy）标准显示格式0（或者100）默认标准monddyyhh:miAM（或PM）1101美国mm/dd/yy2102ANSIyy.mm.dd3103英国/法国dd/mm/yy4104德国dd.mm.yy5105意大利dd-mm-yy6106ddmonyy7107mondd,yy8108hh:mi:ss9（或者109）默认标准+毫秒mondd,yyyyhh:mi:ss:msAM（或PM）10110美国mm-dd-yy11111日本yy/mm/dd12112ISOYymmdd13（或者113）欧洲默认+毫秒ddmonyyyyhh:mi:ss:ms（24小时）14114hh:mi:ss:ms（24小时）说明：style参数可以取两类值，如果从第一类取值，则返回日期的年份为2位；如果从第二类取值，则返回日期年份为4位。当把一个日期转换为字符串时，CONVERT()函数默认的输出格式是“monddyyhh:miAM(或PM)”。从表中可见，CONVERT()函数将日期转换为字符串时提供了大量的日期时间显示格式，这给用户提供了很大的方便。表9.3列出了几个使用CONVERT()函数转换数据类型的例子，供读者参考。表9.3CONVERT()函数的例子功能函数实现字符到数字CONVERT(numeric,'15')数字到字符CONVERT(char,12)字符到日期CONVERT(datetime,'15-09-1977')，CONVERT(datetime,'SEP15,1977')日期到字符CONVERT(char,GETDATE())，CONVERT(char,GETDATE(),102)十六进制到二进制CONVERT(binary,'3C'))二进制到十六进制CONVERT(char,二进制字段)获取当前系统时间CONVERT(char,GETDATE(),8)【例9.1】从Student表中，查询所有学生的姓名、出生日期，并将日期转换为德国标准日期格式的字符串显示，其中年份为4位。要求查询结果按出生日期升序排序。分析：将出生日期转换为字符串，应当使用CONVERT()函数，因为需要按照德国日期格式显示，所以从表9.2中查找设置德国标准的style值，又因为年份要求是4位，所以选择style参数的值为104。下面是具体的SELECT语句。SQL技术与网络数据库开发详解·114·SELECT姓名,CONVERT(CHAR,出生日期,104)AS生日FROMstudentORDERBY出生日期运行结果如图9.1所示。图9.1例9.1运行结果提示：如果SQL运行环境为Oracle，则设置别名时不能使用AS关键字，将SELECT中的AS去掉即可。2．CAST()函数CAST()函数是SQL92标准函数。使用CAST()函数也可以转换数据类型，但是在格式化日期时间数据方面不如CONVERT()函数方便。CAST()函数的语法格式为：CAST(expressionASdatatype[(length)])其中，expression为表达式，datatype为数据类型。如果是CHAR、VARCHAR、BINARY或VARBINARY数据类型，则可以选择length参数设置长度。【例9.2】从Student表中，查询所有学生的姓名、出生日期，并将日期转换为字符串显示。要求查询结果按出生日期升序排序。SELECT姓名,CAST(出生日期ASchar)AS生日FROMstudentORDERBY出生日期运行结果如图9.2（a）所示。从图中可以看出，出生日期已经被转换为字符串。如果只想要日期部分而不想要时间，则可以在类型后设置长度。上面的SELECT语句可以写为如下形式。SELECT姓名,CAST(出生日期ASchar(10))AS生日FROMstudentORDERBY出生日期运行结果如图9.2（b）所示。本例中，因为只给“出生日期”分配了10个字节的长度，所以只把前面的日期部分留下，而后面的时间部分就被自动截掉了。注意：CAST()函数不能改变原表字段的数据类型。第9章SQL函数的使用·115·（a）（b）图9.2例9.2运行结果9.2.2日期函数日期函数允许操作日期时间值。SQLServer支持的日期函数有GETDATE、DATEADD、DATEDIFF、DATENAME和DATEPART等。1．GETDATE函数GETDATE函数用于获取当前系统时间。其格式为：GETDATE()例如，在查询分析器中输入如下SELECT语句并运行后，即可获得当前系统时间。SELECTGETDATE()运行结果如图9.3所示。图9.3使用GETDATE获取当前时间2．DATEADD函数DATEADD函数用于在指定日期上增加年、月、日或者时间等，其返回值为日期型数据。其格式为：DATEADD(datepart,number,date)其中，datepart参数规定在日期的哪个部分（如年份、月份等）增加数值。表9.4列出了datepart参数的可用值。表9.4datepart参数的可用值datepart参数值参数值可用缩写参数值范围Yearyy,yyyy1753～9999quarterqq,q1～4Monthmm,m1～12Dayofyeardy,y1～366SQL技术与网络数据库开发详解·116·续表datepart参数值参数值可用缩写参数值范围Daydd,d1～31Weekwk,ww0～51Weekdaydw1～7（1为星期日）Hourhh0～23minutemi,n0～59secondss,s0～59millisecondms0～999了解datepart参数的可用值后，就可以控制在日期的哪个部分增加值。例如：DATEADD(year,10,GETDATE())是在当前时间的“年”上增加了10年，并返回10年后的日期。而DATEADD(month,10,GETDATE())是在当前时间的“月”上增加了10个月，并返回10个月后的日期。说明：datepart参数值也可以使用缩写。例如，DATEADD(mm,10,GETDATE())也是在当前时间上增加10个月。【例9.3】从Student表中，查询所有学生的姓名、出生日期、出生后的第10000天和出生后的第800个月，要求查询结果按出生日期升序排序。SELECT姓名,出生日期,DATEADD(DAY,10000,出生日期)AS出生后第10000天,DATEADD(MONTH,800,出生日期)AS出生后第800月FROMstudentORDERBY出生日期运行结果如图9.4所示。图9.4例9.3运行结果3．DATEDIFF函数DATEDIFF函数用于获取两个日期间的差，并返回数值数据。其格式为：DATEDIFF(datepart,date1,date2)第9章SQL函数的使用·117·其中，datepart参数的说明同上，date1和date2是日期或者日期格式的字符串。【例9.4】从Student表中，查询所有学生的姓名、出生日期和年龄。要求查询结果按出生日期降序排序。SELECT姓名,出生日期,DATEDIFF(year,出生日期,GETDATE())AS年龄FROMstudentORDERBY出生日期DESC运行结果如图9.5所示。图9.5例9.4运行结果查询语句中的DATEDIFF函数如下：DATEDIFF(year,出生日期,GETDATE())返回的是当前时间和出生日期之间的年份的差距，即年龄。如果写成如下形式：DATEDIFF(month,出生日期,GETDATE())则返回的是当前时间和出生日期之间的月份的差距，即返回相差多少个月。4．DATENAME函数DATENAME函数用于获取日期的一部分，并以字符串形式返回。其格式为：DATENAME(datepart,date)其中，datepart参数的说明同上，date是日期或者日期格式的字符串。例如，假设当前日期为2008年3月25日，则DATENAME(month,GETDATE())的结果为字符串'03'，DATENAME(dd,GETDATE())的结果为字符串'25'。注意：假设当前日期为2008年3月5日，则DATENAME(dd,GETDATE())返回的结果为字符串'5'，而并非是'05'。【例9.5】从Student表中，查询每个月1号出生的所有学生。要求查询结果按出生日期降序排序。SELECT*FROMstudentWHEREDATENAME(day,出生日期)='1'ORDERBY出生日期DESCSQL技术与网络数据库开发详解·118·注意：DATENAME函数返回的是字符串，因此必须与字符串（'1'）比较。运行结果如图9.6所示。图9.6例9.5运行结果5．DATEPART函数DATEPART函数用于获取日期的一部分，并以整数值返回。其格式为：DATEPART(datepart,date)其中，datepart参数的说明同上，date是日期或者日期格式的字符串。例如，假设当前日期为2008年3月25日，则DATEPART(month,GETDATE())的结果为数值3，DATEPART(