常用函数  

SQL Server 2008为Transact-SQL语言提供了大量功能强大的系统函数，利用该函数不需要写很多代码就能够完成很多任务。在SQL Server 2008中，函数主要用来获得有关信息，进行算术计算、统计分析、实现数据类型转换等操作。本节将介绍常用的聚合、数学、字符串、日期和时间函数，以及如何自定义函数等。

4.4.1  聚合函数

聚合函数常用于GROUP BY子句，用于聚合分组的数据。所有聚合函数均为确定性函数，也就是说只要使用一组特定输入值调用聚合函数，该函数总是返回同类型的值。例如，计算一组整数型数值的总和或者平均值，结果将同样会返回整数型的数值。
该函数在和GROUP BY子句一起使用时显示出其强大功能，但聚合函数的使用也不是只限于分组查询。如果查询语句中使用了聚合函数，而没有用GROUP BY子句，则聚合函数是用于聚合整个结果集（匹配WHERE子句的所有行）。例如，不使用GROUP BY子句，SELECT列表中AVG只能和SUM对应，但不能对应特定列。
SQL Server 2008中提供了大量的聚合函数，表4-8中列出了一些常用聚合函数。
表4-8  聚合函数

函数名称	含义
AVG	返回组中各值的平均值，如果为空将被忽略
CHECKSUM	用于生成哈希索引，返回按照表的某一行或一组表达式计算出来的校验和值
CHECKSUM_AGG	返回组中各值的校验和，如果为空将被忽略
COUNT	返回组中项值的数量，如果为空也将计数
COUNT_BIG	返回组中项值的数量。与COUNT函数唯一的差别是他们的返回值。COUNT_BIG始终返回bigint数据类型值。COUNT始终返回int数据类型值
GROUPING	当行由CUBE或ROLLUP运算符添加时，该函数将导致附加列的输出值为1；当行不由CUBE或ROLLUP运算符添加时，将导致附加列的输出值为0
MAX	返回组中值列表的最大值
MIN	返回组中值列表的最小值
SUM	返回组中各值的总和
STDEV	返回指定表达式中所有值的标准偏差
STDEVP	返回指定表达式中所有值的总体标准偏差
VAR	返回指定表达式中所有值的方差
VARP	返回指定表达式中所有值的总体方差

 

在SQL Server 2008提供的所有聚合函数中，除了COUNT函数以外，聚合函数都会忽略空值。

 
下面通过示例演示下聚合函数的具体应用。例如，下面的语句按【体育场管理系统】数据库中【管理员信息】表的“管理员名称”进行分组，并统计该管理员负责场馆的个数，并且统计该管理员负责的场馆的平均单价，具体代码如下所示：
执行上述语句，可以查询出管理员姓名为“李玺”所负责的场馆个数及平均单价，如图4-9所示。

图4-9  使用聚合函数

4.4.2  数学函数

数学函数用于对数字表达式进行数学运算并返回运算结果。在SQL Server 2008中，数学函数可以对系统提供的数字数据进行运算：decimal、integer、float、real、money、smallmoney、smallint 和 tinyint。默认情况下，对float数据类型数据的内置运算的精度为六个小数位数。SQL Server提供了20多个用于处理整数与浮点值的数学函数。下面表4-9列出了部分常用的数学函数。
表4-9  数学函数

函数	描述
ABS	返回数值表达式的绝对值
EXP	返回指定表达式以e为底的指数
CEILING	返回大于或等于数值表达式的最小整数
FLOOR	返回小于或等于数值表达式的最大整
LN	返回数值表达式的自然对数
LOG	返回数值表达式以10为底的对
POWER	返回对数值表达式进行幂运算的结果
ROUND	返回舍入到指定长度或精度的数值表达式
SIGN	返回数值表达式的正号(+)、负号(-)或零(0)
SQUARE	返回数值表达式的平方
SQRT	返回数值表达式的平方根

例如，下面创建一个使用ROUND函数的示例，具体代码如图所示：
执行上述代码，结果如图5-6所示。

图4-10  使用ROUND函数
 

数学函数（例如 ABS、CEILING、DEGREES、FLOOR、POWER、RADIANS 和 SIGN）返回与输入值具有相同数据类型的值。三角函数和其他函数（包括 EXP、LOG、LOG10、SQUARE 和 SQRT）将输入值转换为 float 并返回 float 值。

4.4.3  字符串函数

字符串函数用于计算、格式化和处理字符串参数，或将对象转换为字符串。与数学函数一样，SQL Server 2008为了方便用户进行字符型数据的各种操作和运算提供了功能全面的字符串函数。字符串函数也是经常使用的一种函数，常见的字符串函数如表4-10所示。
表4-10  字符串函数

字符串函数	描述
ASCII	ASCII函数，返回字符表达式中最左侧的字符的ASCII代码值
CHAR	ASCII代码转换函数，返回指定ASCII代码的字符
LEFT	左子串函数，返回字符串中从左边开始指定个数的字符
LEN	字符串函数，返回指定字符串表达式的字符（而不是字节）数，其中不包含尾随空格
LOWER	小写字母函数，将大写字符数据转换为小写字符数据后返回字符表达式
LTRIM	删除前导空格字符串，返回删除了前导空格之后的字符表达式
REPLACE	替换函数，用第三个表达式替换第一个字符串表达式中出现的所有第二个指定字符串表达式的匹配项
REPLICATE	复制函数，以指定的次数重复字符表达式
RIGHT	右子串函数，返回字符串中从右边开始指定个数的字符
RTRIM	删除尾随空格函数，删除所有尾随空格后返回一个字符串
SPACE	空格函数，返回由重复的空格组成的字符串
STR	数字向字符转换函数，返回由数字数据转换来的字符数据
SUBSTRING	子串函数，返回字符表达式、二进制表达式、文本表达式或图像表达式的一部分
UPPER	大写函数，返回小写字符数据转换为大写的字符表达式

例如，对于下面的查询演示了如何使用字符串函数：
在上述语句中同时使用了RIGHT、ASCII、LEN、SPACE、LTRIM和SUBSTRING共6个字符串函数，返回姓氏为“王”的所有客户信息，其执行结果如图5-7所示。

图4-11  使用字符串函数

4.4.4  日期和时间函数

SQL Server 2008提供了9个日期和时间处理函数。其中的一些函数接受datepart变元，这个变元指定函数处理日期与时间所使用的时间粒度。表5-11列出了datepart变元的可能设置。
表4-11  SQL Server datepart常量

常量	含义	常量	含义
yy或yyyy	年	dy或y	年日期（1到366）
qq或q	季	dd或d	日
mm或m	月	Hh	时
wk或ww	周	mi或n	分
dw或w	周日期	ss或s	秒
ms	毫秒

在表4-12中列出了SQL Server 2008提供的9个日期和时间函数。
表4-12  日期和时间函数

日期函数	描述
DATEADD	返回给指定日期加上一个时间间隔后的新datetime值。
DATEDIFF	返回跨两个指定日期的日期边界数和时间边界数。
DATENAME	返回表示指定日期的指定日期部分的字符串。
DATEPART	返回表示指定日期的指定日期部分的整数。
DAY	返回一个整数，表示指定日期的天DATEPART部分。
GETDATE	以datetime值的SQL Server 2008标准内部格式返回当前系统日期和时间。
GETUTCDATE	返回表示当前的UTC时间（通用协调时间或格林尼治标准时间）的datetime值。当前的UTC时间得自当前的本地时间和运行Microsoft SQL Server 2008实例的计算机操作系统中的时区设置。
MONTH	返回表示指定日期的“月”部分的整数。
YEAR	返回表示指定日期的年份的整数。

上述日期函数中，DATENAME、GETDATE和GETUTCDATE具有不确定性。而DATEPART除了用作DATEPART(dw,date)外都具有确定性。其虽dw是weekday的日期部分，取决于设置每周的第一天的SET DATEFIRST所设置的值。除此之外的上述日期函数都具有确定性。
为了有助于读者更好的掌握日期和时间函数的用法，下面举出DATEPART()和DATEDIFF()函数的应用实例，具体代码及执行结果如图5-8所示。

图4-12  使用日期函数

4.4.5  自定义函数

除了使用系统函数外，用户还可以创建自定义函数，以实现更独特的功能。自定义函数可以接受零个或多个输入参数，其返回值可以是一个数值，也可以是一个表，但是自定义函数不支持输出参数。在SQL Server 2008中，使用CREATE FUNCTION语句来创建自定义函数，根据函数返回值形式的不同，可以创建三类自定义函数，分别是标量值自定义函数、内联表值自定义函数和多语句表值自定义函数。
1．标量值函数
标量值自定义函数返回一个确定类型的标量值，其返回的值类型为除text、ntext、image、cursor、timestamp和table类型外的其他数据类型。也就是说，标量值自定义函数返回的是一个数值。
标量值自定义函数的语法结构如下所示：
语法中各参数含义如下：
l   function_name  自定义函数的名称；
l   @parameter_name  输入参数名；
l   scalar_ para meter_data_type  输入参数的数据类型；
l   RETURNS scalar_return_data_type  该子句定义了函数返回值的数据类型，该数据类型不能是text、ntext、image、cursor、timestamp和table类型；
l   WITH  该子句指出了创建函数的选项。如果指定了ENCRYPTION参数，则创建的函数是被加密的，函数定义的文本将以不可读的形式存储在syscomments表中，任何人都不能查看该函数的定义，包括函数的创建者和系统管理员；
l   BEGIN…END  该语句块内定义了函数体（function_body），以及包含RETURN语句，用于返回值。
了解了语法格式及参数含义之后，下面来创建一个标量值函数，他使用一个整形参数指定订单号，返回该订单的客户的姓名。
执行上述语句后在【教务管理系统】数据库中创建一个名称为GetName的标量值函数，在查询中调用该函数，具体的代码及结果如图5-10所示。

图4-13  使用自定义标量值函数
2．内联表值函数
内联表值函数以表的形式返回一个返回值，即他返回的是一个表。内联表值自定义函数没有由BEGIN…END语句块中包含的函数体，而是直接使用RETURN子句，其中包含的SELECT语句将数据从数据库中筛选出来形成一个表。使用内联表值自定义函数可以提供参数化的视图功能。
内联表值自定义函数的语法结构如下所示：
该语法结构中各参数的含义与标量值函数语法机构中参数含义相似。
例如创建一个内联表值函数来返回一个管理员负责的所有场馆信息，代码如下所示：
这里创建的函数名称为GetPalaestra，他的字符串参数@Pid指定要查询的班级编号，RETURNS TABLE指定这是一个内联表值函数。创建完成后，使用SELECT语句来查看管理员编号为102所负责的所有场馆信息，如图5-11所示。

图4-14  使用内联表值函数
3．多语句表值函数
多语句表值自定义函数可以看作标量型和内联表值型函数的结合体。该类函数的返回值是一个表，但他和标量值自定义函数一样，有一个用BEGIN…END语句块中包含起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，他可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值自定义函数的不足。