《锋利的SQL（第2版）》——1.7 常用函数

本节书摘来自异步社区出版社《锋利的SQL（第2版）》一书中的第1章，第1.7节，作者：张洪举 王晓文，更多章节内容可以访问云栖社区“异步社区”公众号查看。

1.7 常用函数

锋利的SQL（第2版）
为满足通常的程序设计需要，Transact-SQL语言提供了非常丰富的函数，包括聚合函数、配置函数、游标函数、日期和时间函数、数学函数等。本节将介绍一些常用的函数类型。

1.7.1　聚合函数

聚合函数对一组值执行计算并返回单个值。除了COUNT以外，聚合函数都会忽略空值。聚合函数经常与SELECT语句的GROUP BY子句一起使用。

例如，下面的语句使用COUNT函数来获取Employee表中的员工数量。

USE AdventureWorks2014;　-- 指定使用的数据库
SELECT COUNT(EmployeeID)
FROM HumanResources.Employee;
GO

又如，下面的语句使用SUM函数计算SalesOrderDetail表中的销售总额。

USE AdventureWorks2014;
SELECT SUM(LineTotal)
FROM Sales.SalesOrderDetail;
GO

1.7.2　配置函数

配置函数用于返回当前配置选项的设置信息。

例如，下面的语句使用@@LANGUAGE函数返回当前会话的语言名称。

SELECT @@LANGUAGE AS 语言名称;

1.7.3　游标函数

游标函数用于返回有关游标的信息。

下面的示例使用@@FETCH_STATUS函数和WHILE循环，逐步检索游标中的行，直至检索完毕。

DECLARE Employee_Cursor CURSOR FOR
SELECT EmployeeID, Title FROM AdventureWorks.HumanResources.Employee;　-- 建立游标
OPEN Employee_Cursor;　-- 打开游标
FETCH NEXT FROM Employee_Cursor;　-- 开始检索
WHILE @@FETCH_STATUS = 0
　 BEGIN
　　　FETCH NEXT FROM Employee_Cursor;　-- 检索下一行
　 END
CLOSE Employee_Cursor;　-- 关闭游标
DEALLOCATE Employee_Cursor;　-- 删除游标引用
GO

1.7.4　日期和时间函数
日期和时间函数用于对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。

1.7.5　数学函数

数学函数根据提供的输入值执行计算后返回一个数值。其中，算术函数（如ABS、CEILING、DEGREES、FLOOR、POWER、RADIANS和SIGN）返回与输入值相同数据类型的值。三角函数和其他函数（包括EXP、LOG、LOG10、SQUARE和SQRT）将输入值转换为float并返回float值。

1.7.6　数据类型转换函数
数据类型转换函数仅包括CAST和CONVERT函数，二者的功能类似。使用这两个函数可以显式地将一种数据类型的表达式转换为另一种数据类型的表达式。

下面分别是CAST和CONVERT函数的语法格式：

CAST ( expression AS data_type [ (length ) ])
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
expression是任何有效的表达式。data_type是目标数据类型（仅限于系统数据类型），包括xml、bigint和sql_variant。length是nchar、nvarchar、char、varchar、binary或varbinary数据类型的可选参数，对于CONVERT函数，如果未指定length，则默认为30个字符。

style用于以下三个方面。

指定在将datetime或smalldatetime数据转换为字符数据（nchar、nvarchar、char、varchar、nchar或nvarchar数据类型）时的日期格式的样式。
用于将float、real、money或smallmoney数据转换为字符数据时的字符串格式的样式。
用于指定二进制和字符型十六进制值之间数据转换时的格式（仅限于SQL Server 2008）。
表1-18列出了将datetime或smalldatetime数据转换为字符数据时的可用值。左侧的两列是style的值，将style值加100，将返回包括世纪数的四位年份格式。

表1-19列出了在将float或real转换为字符数据时的style可用值。

表1-20列出了在将money或smallmoney转换为字符数据时的style可用值。

在将numeric或decimal数据转换为字符数据时，如果要删除结果集尾随的零，可以使用128作为style的值。

表1-21列出了在将字符串转换为xml数据时的style可用值。

下面的语句使用CAST函数将Production.Product表中的ListPrice列由money转换为varchar数据类型。

USE AdventureWorks2014;
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product;
GO

返回的结果类似下列形式：

ListPrice
--------------------------
The list price is 8.09
The list price is 108.00
The list price is 7.16

使用CONVERT函数同样可以实现上面的功能，参考下面的语句。

USE AdventureWorks2014;
SELECT 'The list price is ' + CONVERT(varchar(12), ListPrice) AS ListPrice
FROM Production.Product;
GO

表1-22列出了二进制和字符型十六进制值转换时的style可用值。

参考下面的示例代码：

-- 转换二进制值0x4E616d65到一个字符值
SELECT CONVERT(char(8), 0x4E616d65, 0) AS 'Style 0, 二进制到字符';
-- 下面的示例演示了Style为1的情况下,如何强行截断结果值
-- 产生的结果值由于包含字符0x ，所以被截断
SELECT CONVERT(char(8), 0x4E616d65, 1) AS 'Style 1, 二进制到字符';
-- 下面的示例演示了Style为2的情况下，没有截断结果值
-- 这是因为0x字符未包含在结果中
SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style 2, 二进制到字符';
-- 转换字符值Name到一个二进制值
SELECT CONVERT(binary(8), 'Name', 0) AS 'Style 0, 字符到二进制';
SELECT CONVERT(binary(4), '0x4E616D65', 1) AS 'Style 1, 字符到二进制';
SELECT CONVERT(binary(4), '4E616D65', 2) AS 'Style 2, 字符到二进制';

结果如图1-5所示。

1.7.7　字符串函数

使用字符串函数可以对字符串输入值进行剪裁、替换等操作，并返回字符串或数值。字符串函数及其功能如表1-23所示。

1.7.8　文本和图像函数

使用文本和图像函数可以对文本或图像输入值或列执行操作。文本和图像函数及其功能如表1-24所示。

1.7.9　逻辑函数

从SQL server 2012开始，增加了IIF和CHOOSE两个逻辑函数。这两个函数都是非标准的，目的是为了支持更容易地从Microsoft Access数据库进行迁移。函数功能如表1-25所示。