第4章 T-SQL进阶
SQL语言是数据库数据操作最重要的语言，而T-SQL是标准SQL程序设计语言的增强版，它是用来让应用程序与SQL Server沟通的主要语言。T-SQL提供标准SQL的DDL和DML功能，加上延伸的函数、系统预存程序以及程序设计结构，让程序设计更有弹性。本章将主要介绍T-SQL的重要函数、计算方法以及一些组合判断相关的语句。
4.1 计算
T-SQL计算相关的方法主要包括计数、求和、求最大最小值以及均值的操作函数。
注意：由于计算函数需要对符合条件的数据进行全表扫描，所以需要尽量控制计算的记录范围，以避免因为扫描计算的数据量太大而导致性能上的损失。
4.1.1 计数COUNT
COUNT函数是指获取符合条件的数据集合中的记录数。COUNT函数返回的结果是一个int类型的值，表示查询组中的记录个数。
COUNT函数有两种基本的使用方式，可以通过COUNT（*）获取所有结果集的行数，也可以通过COUNT（DISTINCT）组合获取某一列唯一记录的行数。下面首先介绍COUNT（*）的使用方法。如下代码所示，该代码获取Product表中的所有行数。
SELECT COUNT(＊) from Production.Product
SQL语句的运行结果如下所示。
-----------
504
(1 row(s) affected)
如果对Product表中的ProductSubcategoryID字段使用DISTINCT语句，则表示获取该表中唯一ProductSubcategoryID内容的所有记录数，运行的SQL语句及结果如下所示。
SELECT COUNT(distinct ProductSubcategoryID) from Production.Product
-----------
37
(1 row(s) affected)
4.1.2 求和SUM
SUM函数用于返回表达式中所有值的和，或者仅非重复值的和。SUM函数只能用于数字列，空值将被忽略。SUM函数可以对某一个结果集合中的某几列进行求和操作，也可以对分组之后的结果进行组内的求和操作。
下面的SQL语句中，进行的操作是颜色为黑色的产品的ListPrice和StandardCost列进行求和。
SELECT SUM(ListPrice), SUM(StandardCost)
FROM Production.Product WHERE Color ='black'
GROUP BY Color
ORDER BY Color
GO
该语句的运行结果如下。
67436.26 38636.5002
SUM函数也可以基于分组语句进行某一分组内的求和，有关分组函数的介绍，见后续小节。
SELECT Color ,SUM(ListPrice), SUM(StandardCost)
FROM Production.Product WHERE Color is not null
GROUP BY Color
ORDER BY Color
上述语句的运行结果如下。
Black 67436.26 38636.5002
Blue 24015.66 14746.1464
Grey 125.00 51.5625
Multi 478.92 272.2542
Red 53274.10 32610.7661
Silver 36563.13 20060.0483
Silver/Black 448.13 198.97
White 36.98 13.5172
Yellow 34527.29 21507.6521
4.1.3 极值MAX/MIN
极值函数表示获取结果集合中的最大值和最小值，其中MAX函数表示结果中的最大值，而MIN函数表示结果中的最小值。
例如下面的SQL语句，表示获取产品表Product中ListPrice列的最大值和最小值的操作，其结果是按每种颜色分组显示的。
SELECT Color ,Max(ListPrice), Min(ListPrice)
FROM Production.Product WHERE Color is not null
GROUP BY Color
ORDER BY Color
执行语句之后的结果如下。
Black 3374.99 0.00
Blue 2384.07 34.99
Grey 125.00 125.00
Multi 89.99 8.99
Red 3578.27 34.99
Silver 3399.99 0.00
Silver/Black 80.99 40.49
White 9.50 8.99
Yellow 2384.07 53.99
4.1.4 均值AVG
AVG函数用于返回查询数据集合中各值的平均值，其中空值将被忽略。下面的SQL语句，表示获取产品表Product中ListPrice列的平均值，其结果是按每种颜色分组显示的。
SELECT Color ,AVG(ListPrice)
FROM Production.Product WHERE Color is not null
GROUP BY Color
ORDER BY Color
执行语句之后的结果如下。
Black 725.121
Blue 923.6792
Grey 125.00
Multi 59.865
Red 1401.95
Silver 850.3053
Silver/Black 64.0185
White 9.245
Yellow 959.0913
4.2 组合与判断
组合与判断语句用于数据库操作中的分组、唯一以及包含等操作的数据库语法。其中主要的函数包括HAVING语句、GROUP BY语句和DISTINCT语句。
4.2.1 HAVING
HAVING语句用来指定组或聚合的搜索条件。
注意：HAVING只能与SELECT语句一起使用。
以下示例使用简单的HAVING语句从Product表中检索价格超过10000的每种颜色的价格总计。
SELECT Color, SUM(ListPrice)
FROM Production.Product WHERE Color is not null
GROUP BY Color
HAVING SUM(ListPrice) > 10000
ORDER BY Color
运行结果如下所示。
Black 67436.26
Blue 24015.66
Red 53274.10
Silver 36563.13
Yellow 34527.29
4.2.2 GROUP BY
GROUP BY语句读者在前文中已经接触过一部分了，这个语句用来对某一列或者某几列的数值进行分组，然后对每一组再使用聚合函数进行操作，如前面介绍过的Product表中的颜色字段，就是一个分组的字段依据。
4.2.3 DISTINCT
DISTINCT关键字可从SELECT语句的结果中消除重复的行。如果没有指定DISTINCT，将返回所有行，包括重复的行。
技巧：DISTINCT关键字也可以跟计算函数如COUNT、SUM、MAX、MIN、AVG函数等进行组合操作，用于计算无重复项的结果值。
下面比较一组使用了DISTINCT关键字和没有使用DISTINCT关键字的查询结果。
SELECT DISTINCT ProductSubcategoryID from Production.Product
SELECT ProductSubcategoryID from Production.Product
第一条语句执行之后共查询出38行记录，而第二条语句执行完毕之后，共查询出504条记录。可见DISTINCT将相似的ProductSubcategoryID列作为一条记录进行了确定的查询。
4.3 函数
函数是SQL语句中的一个重要组成部分。通过SQL的函数，数据处理变得更加容易。这里的函数同一般编程中的函数概念基本一致，即通过函数的输入值，经过相应的运算之后，返回函数的运算结果，供程序的进一步处理或者直接返回。本节主要介绍日期时间函数、字符串处理函数和一部分系统自带的函数。
说明：数据库中函数的功能，与程序中代码里的函数作用基本相同，都是为了代码的重复利用，从而实现主逻辑代码的代码量。
4.3.1 日期时间函数
日期和时间是SQL语句中的一个重要变量类型，对于这种重要的类型，SQL Server提供了多种处理函数，这些函数包括：DATEADD、DATEDIFF、DATENAME、DATEPART、DAY、GETDATE、GETUTCDATE、MONTH、YEAR。
（1）DATEADD函数：返回给指定的日期值增加一段时间的函数。函数的使用语法如下所示。
DATEADD (datepart , number, date )
● datepart是指需要增加时间段的表示形式，该表现形式可以为天数或者月数等时间段单位，具体的时间表示形式如表4-1所示。
表4-1 datepart时间表示形式
● number部分表示增加的单位个数，比如datepart指定的天数为day，则number表示增加的具体天数。
● date表示指定的日期，即需要基于指定的日期或时间变量进行增加。
比如，需要对WorkOrder表中的DueDate字段增加2天的SQL语句如下所示。
SELECT DATEADD(day, 2, DueDate)AS TimeDeadLine
FROM Production.WorkOrder
（2）DATEDIFF函数：返回给定两个时间之间的时间间隔。函数的使用语法如下所示。
DATEDIFF ( datepart , startdate , enddate )
● datepart是指返回时间段的表示形式，具体的时间表示形式如表4-1所示。
● startdate表示计算时间段的开始时间。
● enddate表示计算时间段的结束时间。
比如，需要求出WorkOrder表中的DueDate日期字段与系统当前日期的天数差，SQL语句如下所示。
SELECT DATEDIFF (day, DueDate,getdate()) AS DaysPeriod
FROM Production.WorkOrder
（3）DATENAME函数：表示获取指定日期部分的时间名称。函数的使用语法如下所示。
DATENAME ( datepart ,date )
● datepart是指返回的时间段的表示形式，具体的时间表示形式如表4-1所示。
● date表示需要提取名称的日期变量。
比如，需要获取当前时间所对应月份名称的SQL语句如下所示。
SELECT DATENAME(month, GETDATE()) AS 'Month Name'
（4）DATEPART函数：获取指定日期中，指定时间部分的整数值，函数的使用语法如下所示。
DATEPART ( datepart , date )
● datepart是指返回的时间段的表示形式。具体的时间表示形式如表4-1所示。
● date表示需要提取数值的日期变量。
比如，需要获取当前时间所对应天数的SQL语句如下所示。
SELECT DATEPART (day, GETDATE()) AS 'DaysCount'
（5）DAY函数：返回指定日期的天数部分，此函数等价于下面的语句。
DATEPART(dd, date)
函数的使用语法如下所示。
DAY ( date )
● date表示需要提取数值的日期变量。
（6）GETDATE函数：返回当前的系统时间，函数的使用语法如下所示。
GETDATE ( )
比如，需要获取当前时间的SQL语句如下所示。
SELECT GETDATE() AS 'NowDate
（7）GETUTCDATE函数：获取当前系统的UTC表示的时间，函数的使用语法如下所示。
GETUTCDATE()
比如，需要获取当前UTC时间的SQL语句如下所示。
SELECT GETUTCDATE () AS 'NowUTCDate
（8）MONTH函数：返回指定日期的月数部分，此函数等价于下面的语句。
DATEPART(month, date)
函数的使用语法如下所示。
MONTH ( date )
● date表示需要提取数值的日期变量。
（9）YEAR函数：返回指定日期的年数部分，此函数等价于下面的语句。
DATEPART(year, date)
函数的使用语法如下所示。
YEAR ( date )
● date表示需要提取数值的日期变量。
4.3.2 字符串处理函数
字符串处理函数是指在SQL中处理字符串变量的方法，通过这些方法可以对数据库字符串类型的变量进行操作。
（1）ASCII函数：返回字符串最左侧字符的ASCII值。函数的使用语法如下所示。
ASCII ( character_expression )
（2）NCHAR函数：根据Unicode标准的定义，返回具有指定的整数代码的Unicode字符。函数的使用语法如下所示。
NCHAR ( integer_expression )
（3）CHAR函数：将int型的ASCⅡ值转换为字符。函数的使用语法如下所示。
CHAR ( integer_expression )
（4）PATINDEX函数：返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零。函数的使用语法如下所示。
PATINDEX ( '%pattern%' , expression )
● pattern：使用通配符查找的字符串。如%name%表示字符串中包含name字段。
● expression：待查找的字符串。
（5）SPACE函数：返回由指定重复空格个数组成的字符串，函数的使用语法如下所示。
SPACE ( integer_expression )
● integer_expression：重复空格的个数。
（6）CHARINDEX函数：返回字符串变量中，指定输入字符串的开始位置。函数的使用语法如下所示。
CHARINDEX ( expression1 ,expression2 [ , start_location ] )
● expression1表示需要查找的字符串。
● expression2表示指定查找的源字符串。
● start_location表示开始查找的位置，如果未指定则从expression2的开始位置查找expression1。
（7）REPLACE函数：用第三个表达式替换第一个字符串表达式中出现的所有第二个指定字符串表达式的匹配项。函数的使用语法如下所示。
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
● string_expression1表示要搜索的字符串。
● string_expression2表示要查找的字符串表达式。
● string_expression3表示用于替换的字符串表达式。
（8）STUFF函数：删除指定长度的字符，并在指定的起点处插入另一组字符。函数的使用语法如下所示。
STUFF ( character_expression , start , length ,character_expression )
● character_expression表示字符数据表达式。
● start表示一个整数值，指定删除和插入的开始位置。
● length表示一个整数，指定要删除的字符数。
（9）LEFT函数：返回字符串中从左边开始指定个数的字符。函数的使用语法如下所示。
LEFT ( character_expression , integer_expression )
● character_expression表示字符或二进制数据表达式。
● integer_expression正整数，指定character_expression将返回的字符数。
（10）REPLICATE函数：以指定的次数重复字符表达式。函数的使用语法如下所示。
REPLICATE ( character_expression ,integer_expression )
● character_expression字符数据的字母数字表达式。
● integer_expression一个正整数。
（11）SUBSTRING函数：返回字符串的子字符串。函数的使用语法如下所示。
SUBSTRING ( expression ,start , length )
● expression表示字符表达式。
● start指定子字符串开始位置的整数。
● length一个正整数，指定要返回的expression的字符数或字节数。
（12）LEN函数：清除给定字符串尾部空格之后的字符串。函数的使用语法如下所示。
LEN ( string_expression )
（13）REVERSE函数：返回字符串的反向字符串。函数的使用语法如下所示。
REVERSE ( character_expression )
（14）LOWER函数：将给定字符串的字符全部转换为小写之后的字符串。函数的使用语法如下所示。
LOWER ( character_expression )
（15）RIGHT函数：返回字符串中从右边开始指定个数的字符。函数的使用语法如下所示。
RIGHT ( character_expression , integer_expression )
● character_expression表示字符或二进制数据表达式。
● integer_expression正整数，指定character_expression将返回的字符数。
（16）UPPER函数：将给定字符串的字符全部转换为大写之后的字符串。函数的使用语法如下所示。
UPPER ( character_expression )
（17）LTRIM函数：返回删除字符串前面空格之后的字符串，函数的使用语法如下所示。
LTRIM ( character_expression )
（18）RTRIM函数：返回删除字符串后面空格之后的字符串，函数的使用语法如下所示。
RTRIM ( character_expression )
4.3.3 系统函数
系统函数是指由SQL Server本身提供的、用于获取系统参数或者设定值的函数，通过使用这些函数，可以使读者更方便地处理数据，实现复杂的业务逻辑。下面重点介绍几个常用的系统函数。
（1）CASE函数：该函数为实现分段功能的操作，通过给定值的几种可能，然后根据这几种不同的选择，给出后续可能的数据操作。以下示例使用CASE函数更改产品系列类别的显示。
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
（2）CAST函数：用于将一种类型的变量转换成另外一种类型的变量。函数的使用语法如下所示。
CAST ( expression AS data_type [ (length ) ])
下面是使用CAST函数进行变量类型转换的实例。
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
（3）CONVERT函数：同CAST函数类似，该函数也提供了数据类型的一种转换方法。函数的使用语法如下所示。
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
使用CONVERT函数进行转换的实例如下所示。
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
（4）COALESCE函数：返回其参数中第一个非空表达式。函数的使用语法如下所示。
COALESCE ( expression [ ,...n ] )
函数的使用实例如下所示。
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
END
（5）DATALENGTH函数：返回用于表示任何表达式的字节数。函数的使用语法如下所示。
DATALENGTH ( expression )
（6）ISNULL函数：判断输入变量是否为空。
（7）ISDATE函数：判断输入的变量是否为有效的日期类型。
（8）ISNUMERIC函数：判断输入的变量是否为有效的数值类型。
（9）NEWID函数：创建唯一标志类型值的函数。
（10）NULLIF函数：如果两个指定的表达式等价，则返回空值。函数的使用语法如下所示。
NULLIF ( expression , expression )
（11）@@ROWCOUNT函数：返回上一个受语句影响的行数。
（12）@@TRANCOUNT函数：返回当前连接的活动事务数。
（13）@@ERROR函数：返回执行的上一个Transact-SQL语句的错误号。
（14）@@IDENTITY函数：返回最后插入标识值的系统函数。
4.4 小结
本章介绍了T-SQL中常用的各个函数，这些函数有涉及计算的求和、求均值的函数，也有涉及对结果进行分组和包含操作的语句，还有对日期和字符串以及系统变量获取的函数。通过对这些函数和语句的学习，读者可以更好地使用SQL本身提供的处理数据的功能。多种函数和语句的结合使用，不但可以实现复杂的数据操作，还可以优化数据库处理数据的性能等。