本书介绍了Microsoft SQL Server 2008数据库管理系统的基本原理和综合应用。全书共分十个项目，项目一至九讲述Microsoft SQL Server的基础知识，项目十为一个综合型任务实例(设计收视分析问卷调查系统)，介绍SQL Server数据库的具体应用。本书理论与实际相结合，既阐述了数据库的基本概念和方法，又结合SQL Server数据库管理系统基本而核心的内容详解其技术应用，阐述了典型数据库应用系统的设计与实现方法。本书可作为高等院校计算机相关专业数据库应用开发设计课程的教材，也可作为其他数据库设计开发人员的实用参考书。
本书介绍了Microsoft SQL Server 2008数据库管理系统的基本原理和综合应用。全书共分十个项目，项目一至九讲述Microsoft SQLServer的基础知识，项目十为一个综合型任务实例(设计收视分析问卷调查系统)，介绍SQLServer数据库的具体应用。本书理论与实际相结合，既阐述了数据库的基本概念和方法，又结合SQLServer数据库管理系统基本而核心的内容详解其技术应用，阐述了典型数据库应用系统的设计与实现方法。本书可作为高等院校计算机相关专业数据库应用开发设计课程的教材，也可作为其他数据库设计开发人员的实用参考书。
项目二
Transact-SQL语言
1. 项目要点
(1) 使用RETURN语句返回存储过程的参考值。
(2) 使用CURRENT_USER约束列。
2. 引言
Transact-SQL是SQL Server 2008的编程语言，是一种结构化查询语言，是SQL的增强版本。使用Transact-SQL语言，可以从数据库中提取数据，完成SQL语言的数据定义(DDL)、数据操作(DML)和数据控制(DCC)等行为。本项目首先对Transact-SQL语言进行简单概述，然后详细讲述Transact-SQL语言的基本功能。先介绍Transact-SQL语言的两种标识符，即常规标识符和分隔标识符，然后介绍Transact-SQL语言中常用的几种常量、变量、常用的运算符和常用的表达式。最后介绍Transact-SQL语言中常用的9种函数，并对典型函数进行举例。通过本项目的讲述，读者将能够较详细地了解Transact-SQL语言的基本功能，方便后续项目的学习。
3. 项目导入
陈倩用Transact-SQL语言编写程序，查询教学管理系统Teacher数据库student_grade表中成绩大于80的数据，看下面的语句：
SELECT * FROM student_grade WHERE 成绩>80
程序执行结果如图2-1所示。
图2-1 使用Transact-SQL进行查询
4. 项目分析
在如图2-1所示的查询中，比较运算符(>)的返回值为布尔数据类型，可以有3种值：TRUE、FALSE或NULL。比较表达式的条件成立时，返回TRUE，否则返回FALSE。如果打开ANSI_NULLS选项，则当比较操作数中任一个为NULL时，将返回NULL。
5. 能力目标
(1) 掌握Transact-SQL的基本语言。
(2) 掌握Transact-SQL的内置函数。
6. 知识目标
(1) 学习Transact-SQL常量、变量、运算符、表达式、控制流程语句。
(2) 学习数学函数、字符串函数、日期时间函数等。
任务1 使用RETURN语句返回存储过程的参考值
Transact-SQL是Microsoft公司在关系型数据库管理系统SQL Server中对SQL-3标准的实现，是微软对SQL的扩展，具有SQL的主要特点，同时增加了变量、运算符、函数、流程控制和注释等语言元素，使其功能更加强大。
知识储备
1. Transact-SQL概述
Transact-SQL对SQL Server十分重要，在SQL Server中，使用图形界面能够完成的所有功能，都可以通过Transact-SQL来实现。与SQL Server通信的所有应用程序都通过向服务器发送Transact-SQL语句来进行操作，而与应用程序的界面无关。
根据其完成的具体功能，可以将Transact-SQL语句分为4大类，分别为数据定义语句、数据操纵语句、数据控制语句和一些附加的语言元素。
(1) 数据定义语句：CREATE TABLE、DROP TABLE、ALTER TABLE、CREATE VIEW、DROP VIEW、CREATE INDEX、DROP INDEX、CREATE PROCEDURE、ALTER PROCEDURE、DROP PROCEDURE、CREATE TRIGGER、ALTER TRIGGER、DROP TRIGGER。
(2) 数据操纵语句：SELECT、INSERT、DELETE、UPDATE。
(3) 数据控制语句：GRANT、DENY、REVOKE。
(4) 附加的语言元素：BEGIN TRANSACTION / COMMIT、ROLLBACK、SET TRANSACTION、DECLARE OPEN、FETCH、CLOSE、EXECUTE。
2. 标识符
数据库对象的名称即为标识符。Microsoft SQL Server中的所有内容都可以有标识符，例如服务器、表、视图、列、索引、触发器、过程、约束及规则等。大多数对象都要求有标识符，但有些对象标识符是不可选的，例如，约束标识符是系统自动生成的，不需要用户提供。
对象标识符是在定义对象时创建的，标识符随后用于引用该对象。
【例1】创建一个标识符为student的表，该表中有两个列标识符，分别是Number和Address：
CREATE TABLE student
(Number INT PRIMARY KEY, Address nvarchar(80))
此表还有一个未命名的约束。PRIMARY KEY约束没有标识符。
标识符的排序规则取决于定义标识符时所在的级别。实例级对象的标识符指定的是实例的默认排序规则，例如登录名、数据库名。数据库对象的标识符分配数据库的默认排序规则，例如表、视图和列名。按照标识符的使用方式，可把标识符分为两类，即常规标识符和分隔标识符。这两种标识符包含的字符数必须在1~128之间。对于本地临时表，标识符最多可以有116个字符。
(1) 常规标识符
常规标识符的格式规则取决于数据库的兼容级别。可以使用ALTER DATABASE进行设置。当兼容级别为100时，适用下列规则。
① 第一个字符必须是下列字符之一：Unicode标准3.2所定义的字母，这些定义的字母包括拉丁字符a~z和A~Z，以及来自其他语言的字母字符；下划线(_)、符号@或数字符号#。
在Transact-SQL中，某些位于标识符开头位置的符号具有特殊意义。
以@符号开头的常规标识符始终表示局部变量或参数，并且不能用作任何其他类型的对象的名称。某些Transact-SQL函数的名称以两个符号(@@)开头。为了避免与这些函数混淆，不应使用以@@开头的名称命名。
以一个数字符号开头的标识符表示临时表或过程。以两个数字符号(##)开头的标识符表示全局临时对象。虽然数字符号或两个数字符号字符可用作其他类型对象名的开头，但这里不建议使用。
② 后续字符可以包括：Unicode标准3.2中所定义的字母；基本拉丁字符或其他国家/地区字符中的十进制数字；@符号、美元符号($)、数字符号(#)或下划线(_)。
③ 标识符一定不能是Transact-SQL保留字。SQL Server可以保留大写形式和小写形式的保留字。
④ 不允许嵌入空格或其他特殊字符。
⑤ 不允许使用增补字符。
例如，studentInformation、@number_20、Money_88$等是合法的常规标识符，而the student、My name_@、age 30等不是合法的标识符。
在Transact-SQL语句中，如果符合标识符的格式规则，在使用常规标识符时，不用将其分隔开。例2中使用的标识符均是合法的，不用进行分隔。
【例2】查询学号为15的学生信息：
SELECT *
FROM student
WHERE Number = 15
知识链接： 变量、函数和存储过程的名称必须符合Transact-SQL标识符的规则。在 Transact-SQL语句中使用标识符时，不符合上述规则的标识符必须用双引号或括号分隔，即我们下面要介绍的分隔标识符。
(2) 分隔标识符
分隔标识符是包含在双引号(“”)或者方括号([])内的标识符。使用双引号(“”)分隔的标识符称为引用标识符，使用方括号([])分隔的标识符称为括在括号内的标识符。默认情况下使用方括号([])分隔标识符。只有QUOTED_IDENTIFIER选项设置为ON时，才使用双引号(“”)分隔标识符。
在Transact-SQL语句中，符合标识符格式规则的标识符可以分隔，也可以不分隔。下面是对例2中的合法标识符进行分隔：
SELECT *
FROM [student]
WHERE [number] = 15
在Transact-SQL语句中，如果对象名称包含了Microsoft SQL Server中的保留字或使用了未列入限定字符的字符，均不符合标识符的格式规则，必须进行分隔。例3中包含不合法的标识符My student和保留字order，必须进行分隔。
【例3】查询序号为15的学生信息：
SELECT *
FROM [My student]
WHERE [order] = 15
注意： 美元符号($)的关键字通常用得较少，不为人们所熟知，应尽量避免使用，以提高程序的可读性。
3. 常量和变量
(1) 常量
常量是表示特定数据值的符号，也称为字面量，在整个程序运行过程中保持不变。常量的格式取决于它所表示的值的数据类型。Transact-SQL中常用的常量主要有字符串常量、整型常量、实型常量、Money常量、uniqueidentifier常量和日期时间常量等。
① 字符串常量
分为ASCII字符串常量和Unicode字符串常量两种。
ASCII字符串常量是用单引号括起来的由ASCII字符构成的符号串，每个ASCII字符用一个字节来存储。
Unicode字符串常量数据中的每个字符用两个字节存储，与ASCII字符串常量相似。N前缀必须为大写字母，例如N‘What is you name?’。
ASCII和Unicode常量被分配了当前数据库的默认排序规则，除非使用COLLATE子句分配特定的排序规则，例如：
'abc' COLLATE French_CI_AI或者N'lustig' COLLATE German_Phonebook_CS_AS
注意： 如果单引号中的字符串包含引号，可以使用两个单引号表示嵌入的单引号。
② 整型常量
按照整型常量表示方式的不同，可将整型常量分为二进制整型常量、十进制整型常量和十六进制整型常量。
* 二进制整型常量的表示：即数字0或1，并且不使用引号。如果使用一个大于1的数字，它将被转换为1。
* 十进制整型常量的标识：即不带小数点的十进制数，例如2012、9、+20120215、-20120215。
* 十六进制整型常量的表示：前辍0x，后跟十六进制数字串。例如0xAEBF、0x12Ff、0x48AEFD010E、0x。
③ 实型常量
实型常量按表示方式的不同，可分为定点表示和浮点表示。
* 定点表示：例如1894.1204、2.0、+145345234.2234、-2147483648.10。
* 浮点表示：例如101.5E5、0.5E-2、+123E-3、-12E5。
④ 日期时间常量
日期时间常量用单引号将表示日期时间的字符串括起来构成。SQL Server可以识别的日期和时间格式有字母日期格式、数字日期格式和未分隔的字符串格式。例如‘April 15, 2012’、‘4/15/1998’、‘20001207’、‘04:24:PM’、‘April 15, 2012 14:30:24’。
⑤ money常量
money常量是以$作为前缀的整型或实型常量数据。例如$12、$542023、-$45.56、+$423456.99。下面是将money常量应用到Transact-SQL中的例子：
SELECT Price +$6.50
FROM CommodityTable
⑥ uniqueidentifier常量
uniqueidentifier常量是表示全局唯一标识符(GUID)值的字符串。uniqueidentifier常量可以使用字符或者十六进制字符串格式来指定。例如‘642D-000F96D458AB19FF011-B04FC964FF’、0x2012ff6fd00c04fc964ff。
注意： 引用数值常量时不用单引号，引用日期、字符串常量时需要加单引号。
(2) 变量
变量是在程序运行过程中会发生改变的量。根据作用范围，可以将变量分为局部变量和全局变量两种。
① 局部变量
局部变量是指在批处理或脚本中用来保存数据值的对象，是用户自己定义的变量。局部变量一次只能保存一个值，它的作用范围仅在程序内部。
一般局部变量只在一个批处理或存储过程中使用，用来存储从表中查询到的数据，或当作程序执行过程中的暂存变量使用。通常，局部变量可以作为计数器，计算循环执行的次数或控制循环执行的次数；此外，利用局部变量还可以保存数据值，以供流程控制语句测试，以及保存由存储过程返回的数据值等。
局部变量使用Declare语句来声明，其语法格式如下：
Declare {变量名 数据类型}[...n]
例如：
DECLARE @name varchar(8)
DECLARE @seat int
声明完局部变量后，就可以对其进行赋值了，赋值格式如下：
SET @变量名 = 值 (普通赋值)
SELECT @变量名 = 值[,...](查询赋值)
使用SELECT语句赋值时，若返回多个值，结果为返回的最后一个值。若省略“=”及其后的表达式，可以将局部变量的值显示出来。
例如下面两个为变量赋值的语句：
SET @name = '张三'
SELECT @name = sname FROM student
WHERE snum = '001'
注意： 局部变量必须先声明后使用，初值为NULL。
【例4】编写程序，计算两个整数之和：
DECLARE @i int, @j int, @sum int
SET @i = 50
SET @j = 60
SELECT @sum = @i + @j
PRINT @sum
GO
语法说明如下：DECLARE声明局部变量。@i、@j、@sum为变量名，总是以@开始。int为变量的数据类型。SELECT和SET用来对局部变量进行赋值。SET一次只能给一个局部变量赋值，SELECT可以同时给多个局部变量赋值。
② 全局变量
全局变量也称配置函数，是SQL Server系统提供并赋值的变量，用于存储系统的特定信息，作用范围并不局限于某一程序，而是任何程序均可随时调用。
全局变量是在服务器级定义的，以@@开头。例如@@version。
全局变量对用户来说，是只读的，用户只能使用预先定义的全局变量，不能建立全局变量，也不能修改其值，但可在程序中用全局变量来测试系统的设定值或Transact-SQL命令执行后的状态值。
在Transact-SQL语句中，常用的全局变量如表2-1所示。
【例5】使用全局变量查看SQL Server的版本信息和服务器名称：
print '当前所用的SQL Server版本信息如下：'
print @@version
print ''
print '目前所用SQL Server服务器的名称为：' + @@servername
print语句用于显示char类型、varchar类型，或可自动转换为字符串类型的数据。
表2-1 Transact-SQL中的全局变量
变 量
说 明
@@CPU_BUSY
SQL Server自上次启动后的工作时间，单位：毫秒
@@CURSOR_ROES
打开上一个游标中当前限定行的数目
@@ERROR
上一条Transact-SQL语句报告的错误号
@@IDENTITY
最后插入的标识值
@@LANGID
当前使用语言的ID
@@NESTLEVEL
当前存储过程的嵌套级别(初始值为0)
@@PROCID
Transact-SQL当前模块的ID
@@ROWCOUNT
上一条Transact-SQL语句影响的行数
@@SERVERNAME
本地服务器的名称
@@SPID
当前用户进程的会话ID
@@VERSION
当前SQL Server的版本、处理器体系结构、生成日期
4. 运算符
运算符是一种符号，它们能够用来执行算术运算、字符串连接、赋值，以及在字段、常量和变量之间进行比较。在Transact-SQL中，常用到的运算符主要有算术运算符、赋值运算符、逻辑运算符、比较运算符、位运算符和字符串串联运算符。
(1) 算数运算符
算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。
Transact-SQL中提供了5种算术运算符，表2-2列出了这些运算符及作用。
表2-2 算数运算符
运 算 符
含 义
例 子
结 果
+
加法运算
6+8
14
-
减法运算
10-7
3
*
乘法运算
2*3
6
/
除法运算
14/5
2
%
取模运算
14%5
4
(2) 赋值运算符
赋值运算符的作用，是将数据值指派给特定的对象，也可以使用赋值运算符在列标题与为列定义值的表达式之间建立关系。
Transact-SQL中只有一个赋值运算符，即等号(=)。
【例6】下面的语句先定义一个int变量@number，然后将其值赋为20：
declare @number int
set @number = 20
(3) 逻辑运算符
逻辑运算符可以把多个逻辑表达式连接起来。逻辑运算符包括AND、OR和NOT等运算符。逻辑运算符和比较运算符一样，返回带有TRUE或FALSE值的布尔数据类型。
表2-3列出了Transact-SQL中常用的10种逻辑运算符。
表2-3 逻辑运算符
运 算 符
含 义
ALL
如果一组的比较都返回TRUE，则比较结果为TRUE
AND
如果两个布尔表达式都返回TRUE，则结果为TRUE
ANY
如果一组的比较中任何一个返回TRUE，则结果为TRUE
BETWEEN
如果操作数在某个范围之内，则结果为TRUE
EXISTS
如果子查询中包含了一些行，则结果为TRUE
IN
如果操作数等于表达式列表中的一个，则结果为TRUE
LIKE
如果操作数与某种模式相匹配，则结果为TRUE
NOT
对任何其他布尔运算符的结果值取反
OR
如果两个布尔表达式中的任何一个为TRUE，则结果为TRUE
SOME
如果在一组比较中，有些比较为TRUE，则结果为TRUE
【例7】从student表中查询年龄为20或姓为“王”的学生信息：
Select *
From student
Where stu_age = 20 or stu_name like '王'
【例8】查找由位于以字母B开头的城市中的任一出版商出版的书名。
方法1：
USE pubs
GO
SELECT title
FROM titles
WHERE EXIST
(SELECT *
FROM publishers
WHERE pub_id=titles.pub_id
AND city LIKE \'B%\')
GO
方法2：
USE pubs
GO
SELECT title
FROM titles
WHERE pub_id IN
(SELECT pub_id
FROM publishers
WHERE city LIKE \'B%\')
GO
(4) 位运算符
位运算符是在两个表达式之间执行位操作，这两个表达式可以是任意两个整型数据或者二进制数据(image数据类型除外)类型的表达式。此外，位运算符的两个操作数不能同时是二进制数据。
Transact-SQL中提供了3种位运算符，表2-4中列出了这些运算符及作用。
表2-4 位运算符
运 算 符
含 义
例 子
结 果
&
位与
170&75
10
|
位或
170|75
235
^
位异或
170^75
225
(5) 比较运算符
比较运算符用于比较两个表达式的大小是否相同，其比较的结果是布尔值。如果表达式的结果为真，用TRUE表示；如果表达式的结果为假，用FALSE表示；如果表达式的结果是空值，则用UNKNOWN表示。
空值不与任何值匹配，包括其他情况下的空值。
例如，搜索以字母“M”或“M”后的字母开始的姓名(name>=‘M’)，并且某些行不包含值，则无论使用何种比较运算符，这些行都不显示。
比较中所使用数据的数据类型必须匹配。即文本只能比较文本，数字只能比较数字。比较文本数据时，结果取决于当前使用的字符集。例如，如果表是使用斯堪的纳维亚语字符集创建的，搜索的结果可能会不同，这取决于当前的字符集(代码页)是斯堪的纳维亚语字符集还是另外一种字符集。
Transact-SQL中提供了9种比较运算符，表2-5中列出了这些运算符及作用。
表2-5 比较运算符
运 算 符
含 义
=
等于
>
大于
<
小于
>=
大于等于
<=
小于等于
<>
不等于
!=
不等于
!<
不小于
!>
不大于
【例9】查找编号为770的产品的名称和产品号：
USE Adventureworks2008;
GO
DECLARE @product0 int;
SET @product0 = 770;
IF (@product0 <> 0)
SELECT ProductID, Name, ProductNumber
FROM Production.product
WHERE ProductID = @product0
(6) 字符串串联运算符
字符串串联运算符允许通过加号(+)进行字符串串联，这个加号即被称为字符串串联运算符。
【例10】执行下面的语句：
SELECT 'abc' + 'def';
结果为‘abcdef’。
(7) 一元运算符
一元运算符只对一个表达式执行操作，这个表达式可以是数字数据类型中的任意数据类型。
Transact-SQL中提供了3种一元运算符，表2-6中列出了这些运算符及作用。
表2-6 一元运算符
运 算 符
含 义
+
正
-
负
~
位非
拓展提高： “+”和“-”运算符可以用于数字数据类型中的任意表达式，“~”运算符只能用于整型数据类型类别中的任意数据类型的表达式。
【例11】将一个变量设为正值：
DELARE @num decimal(10,2)
SET @num = +2012.45;
SELECT @num;
GO
结果为2012.45。
(8) 运算符的优先级
如果一个表达式中使用了多种运算符，则运算符的优先顺序决定计算的先后次序。计算时，从左向右依次先做优先级高的运算，再做优先级低的运算。
Transact-SQL中，运算符的优先级如表2-7所示。
表2-7 运算符的优先级
优 先 级
运 算 符
1
~(位非)
2
*(乘)、/(除)、%(取模)
3
+(正)、-(负)、+(加)、+(连接)、-(减)、&(位与)
4
=、>、<、>=、<=、<>、!=、!>、!<
5
^(位异或)、|(位或)
6
NOT
7
AND
8
ALL、ANY、BETWEEN、IN、LIKE、OR、SOME
9
=(赋值)
拓展提高： 当表达式中使用括号替代运算符优先级时，首先要对括号中的内容进行求值，然后再对括号外的运算符进行求值。当表达式中的多个运算符有相同的优先级时，要按照这些运算符在表达式中的位置，从左到右依次进行求值。
5. 表达式
在Transact-SQL语言中，表达式是由标识符、常量、变量、函数和运算符组成的式子。Transact-SQL语言中包括三种表达式：字段名表达式、目标表达式和条件表达式。
注意： 单个常量、变量或函数亦可称作表达式。
(1) 字段名表达式
字段名表达式可以是单一的字段名，或几个字段的组合，还可以是由字段及作用于字段的集函数和常量的任意算术运算组成的运算公式，例如+(加)、-(减)、*(乘)、/(除)。字段名表达式主要包括数值表达式、字符表达式、逻辑表达式、日期表达式4种。
(2) 目标表达式
目标表达式有如下4种构成方式。
* *：表示选择相应基表和视图的所有字段。
* <表名>.*：表示选择指定的基表和视图的所有字段。
* 集函数()：表示在相应的表中按集函数操作和运算。
* [<表名>.]<字段名表达式>[, [<表名>.]<字段名表达式>]…：表示按字段名表达式在多个指定的表中选择。
(3) 条件表达式
常用的条件表达式主要有比较大小、指定范围、是否在集合中、字符匹配、空值和多重条件6种。
① 比较大小的条件表达式由比较运算符构成，主要的比较运算符有=、>、<、>=、<=、!=、<>、!>(不大于)、!<(不小于)、NOT+(取非)。
② 指定范围的条件表达式由(NOT)BETWEEN ... AND...构成。
(NOT)BETWEEN ... AND ...表示查找字段值在(或不在)指定的记录范围内。BETWEEN后是范围的下限(即低值)，AND后是范围的上限(即高值)。
③ 集合的条件表达式由(NOT) IN构成。
(NOT) IN表示查找字段值属于(或不属于)指定集合内的记录。
④ 字符匹配的条件表达式格式如下：
(NOT) LIKE <匹配串> [ESCAPE <换码字符>]
(NOT) LIKE <匹配串> [ESCAPE <换码字符>]表示查找指定的字段值与<匹配串>相匹配(或不相匹配)的记录。<匹配串>可以是一个完整的字符串，也可以含有通配符_和%。其中，“_”代表任意单个字符，“%”代表任意长度的字符串。
⑤ 空值的条件表达式由IS (NOT) NULL构成。
IS (NOT) NULL表示查找字段值为空(或不为空)的记录。NULL不能用来表示无形值、默认值、不可用值以及取最低值或取最高值。Transact-SQL中规定，在含有运算符+、-、*、/的算术表达式中，若有一个值是空值，则该算术表达式的值也是空值；任何一个含有NULL比较操作结果的取值都为“假”。
⑥ 多重条件的条件表达式由AND和OR构成。
AND表示查找字段值满足所有与AND相连的查询条件的记录；OR表示查找字段值满足查询条件之一的记录。AND的优先级高于OR，但可通过括号来改变优先级。
6. 控制流语言
流程控制语句是指那些用来控制程序执行和流程分支的命令，在SQL Server 2008中，流程控制语句主要用来控制Transact-SQL语句、语句块和存储过程的执行流程。
控制流语句包括BEGIN ... END语句、IF ... ELSE语句、CASE语句、WHILE语句、CONTINUE、BREAK、GOTO语句、WAITFOR语句。
(1) BEGIN ... END语句
BEGIN ... END语句能够将多个Transact-SQL语句组合成一个语句块，并将它们视为一个单元来处理。在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGIN ... END语句，其语法如下：
BEGIN
{sql_statement | statement_block}
END
说明：
① BEGIN ... END：为语句关键字，允许嵌套。
② {sql_statement | statement_block}：指任何有效的Transact-SQL语句或语句组。
【例12】使用BEGIN ... END语句，将一组Transact-SQL语句组成语句组，并作为一个单元来运行：
USE AdventureWork2008
GO
BEGIN TRANSACTION;
GO
IF @@TRANCOUNT = 0
BEGIN
SELECT *
FROM PERSON.PERSON
WHERE LastName = 'SMITH';
ROLLBACK TRANSACTION
PRINT N'Rolling back the transaction two times would cause an error.'
END
ROLLBACK TRANSACTION
PRINT N'Rolled back the transaction.'
GO
(2) IF ... ELSE语句
IF ... ELSE语句是条件判断语句，用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。其中，ELSE子句是可选的。SQL Server允许嵌套使用IF ... ELSE语句，而且嵌套层数没有限制。
IF ... ELSE语句的语法格式如下：
IF Boolean_expression
{sql_statement | statement_block}
[ ELSE
{sql_statement | statement_block}
]
说明：
① IF ... ELSE构造可用于批处理、存储过程和即时查询。
② 可以在其他IF之后或在ELSE下面嵌套IF语句。
③ Boolean_expression：返回TRUE或FALSE的表达式。如果布尔表达式中含有SELECT语句，则必须用圆括号将SELECT语句括起来。
④ {sql_statement | statement_block}：指任何有效的Transact-SQL语句或语句组。
【例13】使用IF ... ELSE进行事务处理，正常则提交数据，否则回滚数据：
IF(@ErrorCode <> 0)
BEGIN
PRINT 'Last error encountered: ' + CAST(ErrorCode AS VARCHAR(10))
ROLLBACK
END
ELSE
BEGIN
PRINT 'No error encountered, committing.'
END
RETURN ErrorCode
(3) CASE语句
CASE语句用于计算条件列表，并将其中一个符合条件的结果表达式返回。CASE函数按照使用形式的不同，可以分为简单CASE函数和搜索CASE函数。
简单CASE函数用于将某个表达式与一组简单表达式进行比较，以确定结果。它的语法形式为：
CASE
WHEN when_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END
说明：
① WHEN when_expression：表示使用简单CASE格式时要与input_expression进行比较的表达式。
② n：占位符，这表明可以使用多个WHEN when_expression THEN result_expression子句。
③ THEN result_expression：表示当输入input_ expression = when_expression的结果为TRUE时返回的表达式。
④ ELSE else_result_expression：表示比较运算结果为FALSE时的表达式。
【例14】在SELECT语句中，使用简单的CASE函数检查表达式是否相等：
USE AdventureWork2008
GO
SELECT Productnumber AS '产品编号', N'种类'=CASE ProductLine
When 'R' THEN N '公路',
When 'M' THEN N '山地',
When 'T' THEN N '旅行',
When 'S' THEN N '其他项',
ELSE N '非卖品'
END,
Name As '名称'
FROM Production.product
ORDER By ProductNumber;
搜索CASE函数用于计算一组布尔表达式，以确定结果。它的语法形式为：
CASE
WHEN Boolean_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END
说明：
WHEN Boolean_expression：表示使用CASE搜索格式时所计算的布尔表达式。
【例15】使用CASE函数对Production.product数据表中的价格进行分段归类：
USE AdventureWork2008
GO
SELECT Productnumber AS '产品编号', Name As '名称'， N'价格范围'=
CASE ProductLine
When ListPrice = 0 THEN N'0元',
When ListPrice < 50 THEN N'50元以下',
When ListPrice >= 50 THEN N'250元以下',
When ListPrice >= 250 THEN N'1000元以下',
ELSE N'1000元以上'
END,
FROM Production.product
ORDER By ProductNumber;
(4) WHILE、CONTINUE和BREAK语句
WHILE、CONTINUE和BREAK语句用于设置重复执行Transact-SQL语句或语句块的条件。当指定的条件为真时，重复执行语句。
知识链接： CONTINUE语句可以使程序跳过CONTINUE语句后面的语句，回到WHILE循环的第一行命令。BREAK语句则使程序完全跳出循环，结束WHILE语句的执行。
语法格式如下：
WHILE Boolean_expression
{ sql_statement | statement_block }
[BREAK]
{ sql_statement | statement_block }
[CONTINUE]
{ sql_statement | statement_block }
说明：
① 如果嵌套了两个或多个WHILE循环，则内层的BREAK将退出到下一个外层循环。将首先运行内层循环结束之后的所有语句，然后重新开始下一个外层循环。
② Boolean_expression表达式返回TRUE或FALSE。
③ {sql_statement | statement_block}为Transact-SQL语句或语句块定义的语句分组。
④ BREAK导致从最内层的WHILE循环中退出。
⑤ CONTINUE使WHILE循环重新开始执行，忽略CONTINUE关键字后面的任何语句。
【例16】在嵌套的IF ... ELSE中使用BREAK和CONTINUE，根据条件判断是否继续执行WHILE循环，如果中断，则执行BREAK，如果继续，则执行CONTIOUE：
USE AdventureWork2008
GO
WHILE (SELECT AVG(ListPrice) FROM Production.product) > $600
BEGIN
UPDATE Production.product
SET ListPrice = ListPrice/2
SELECT MIN(ListPrice)
FROM Production.product
IF(SELECT MIN(ListPrice) FROM Production.product) < $200
BREAK
ELSE
CONTINUE
END
(5) GOTO语句
GOTO语句可以使程序直接跳到指定的标有标识符的位置继续执行。
GOTO语句和标识符可以用在语句块、批处理和存储过程中，标识符可以为数字与字符的组合，但必须以冒号“:”结尾。GOTO语句允许嵌套。
语法形式如下：
label:
some execution
GOTO label
说明：
① GOTO可出现在条件控制语句、语句块或过程中，但它不能跳转到该批处理以外的标签。GOTO分支可跳转到定义在GOTO之前或之后的标签。
② label：如果GOTO语句指向该标签，则该标签为处理的起点。
注意： 默认情况下，GOTO语句的权限授予任何有效用户。
【例17】使用GOTO语句建立循环结构，循环条件由IF语句和GOTO组成：
USE AdventureWork2008
GO
DELARE @table0 sysname
DELARE @count int
SET @table0 = N'Production.product'
SET @count = 0;
LOOP:
IF(@@ERROR = 0)
BEGIN
EXEC ('select''' + table + '''= count(*) FROM' + @table0)
SET @count = @count+1;
PRINT STR(@count) + N'执行完成'
END
IF(@@ERROR = 0 AND @count<2)
GOTO LOOP
GO
(6) WAITFOR语句
WAITFOR语句用于暂时停止执行Transact-SQL语句、语句块或者存储过程等，直到所设定的时间已过，或者所设定的时间已到，才继续执行。
WAITFOR语句的处理过程为：如果查询不能返回任何行，WAITFOR将一直等待，直到满足TIMEOUT条件为止。如果查询超出了设定的查询时间值，则WAITFOR语句参数不运行即可完成。
语法形式为：
WAITFOR
DELAY 'time_to_pass'
| TIME 'time_to_execute'
| (receive_statement) [, TIMEOUT timeout]
说明：
① DELAY：表示可以继续执行Transact-SQL语句、语句块或者存储过程之前，必须经过的指定时段(最长为24小时)。
② time_to_pass：指等待时间。
③ TIME：表示指定的运行Transact-SQL语句、语句块或者存储过程的时间。
④ time_to_execute：指WAITFOR语句完成时间。
⑤ receive_statement：指有效的RECEIVE语句。
⑥ TIMEOUT timeout：表示指定消息到达队列前的等待时间。
【例18】使用WAITFOR设置启动任务：
Begin
WAITFOR TIME '7:00';
EXECUTE sp_job;
END;
GO
(7) RETURN语句
RETURN语句用于无条件地终止一个查询、存储过程或批处理，此时位于RETURN语句之后的程序将不会被执行。RETURN的执行是即时且完全的，在任何时候都可以从批处理、过程或语句块中退出。
RETURN语句的语法形式为：
RETURN [integer_expression]
其中，integer_expression指返回的整型值。存储过程可向执行调用的过程或应用程序返回一个整数值。
任务实践
使用RETURN语句返回存储过程的参考值：
CREATE PROCEDURE findjob @n system = NULL
AS
IF @n IS NULL
BEGIN
PRINT 'you must give a user name'
RETURN
END
ELSE
BEGIN
SELECT O.name, o.id, o.uid
FROM sysobjects o INNER JOIN master.syslogins l
ON o.uid = l.sid
WHERE l.name = @n
END;
Exec findjob
EXEC findjob @n='sa'
任务2 使用CURRENT_USER约束列
知识储备
T-SQL中的内置函数很多，大体上可分为下面几类：数学函数、字符串函数、日期和时间函数、系统函数、系统统计函数、聚合函数、配置函数、游标函数、元数据函数、安全函数、排名函数、加密函数、行集函数以及文本和图像函数。这里仅就一些常用的函数进行介绍，如表2-8所示，列出了这些常用内置函数的作用。
表2-8 常用的系统内置函数
函数类别
作 用
聚合函数
执行的操作是将多个值合并为一个值。例如COUNT、SUM、MIN和MAX
配置函数
是一种标量函数，可返回有关配置设置的信息
转换函数
将值从一种数据类型转换为另一种
游标函数
返回有关游标状态的信息
日期和时间函数
可以更改日期和时间的值
数学函数
执行三角、几何和其他数字运算
元数据函数
返回数据库和数据库对象的属性信息
安全函数
返回有关用户和角色的信息
字符串函数
可更改char、varchar、nchar、nvarchar、binary和varbinary的值
系统函数
对系统级的各种选项和对象进行操作或报告
系统统计函数
返回有关SQL Server性能的信息
知识链接： SQL Server内置函数可以是确定或不确定的。如果任何时候用一组特定的输入值调用内置函数，返回的结果总是相同的，则这些内置函数为确定的。如果每次调用内置函数时，即使所用的是同一组特定输入值，也总是返回不同的结果，则这些内置函数为不确定的。
1. 数学函数
数学函数是对数值型的输入值执行计算，并返回一个数值。如算数函数ABS、CEILING、DEGREES、FLOOR、POWER、RADIANS和SIGN将返回与输入值具有相同数据类型的值，而三角函数和其他函数(如EXP、LOG、LOG10、SQUARE和SQRT)先将输入值转换为float类型后，再返回float类型值。数学函数都是标量函数。
表2-9列出了所有数学函数及其描述。
表2-9 数学函数
函 数
描 述
ABS(numeric_expression)
返回指定数值表达式的绝对值(正值)的数学函数。
numeric_expression表示精确数字或近似数字数据类型(bit数据类型除外)的表达式。
返回值类型与numeric_expression相同
ACOS(float_expression)
返回其余弦是所指定的float表达式的角(弧度)；也称为反余弦
ACOS(float_expression)
float_expression表示类型为float或类型可以隐式转换为float的表达式，取值范围是(-1~1)。对超过此范围的值，函数将返回NULL并报告域错误。
返回值类型为float
ASIN(float_expression)
返回以弧度表示的角，其正弦为指定float表达式；也称为反正弦。float_expression表示类型为float或可隐式转换为float类型的表达式，取值范围是(-1~1)。对超过此范围的值，函数将返回NULL并且报告域错误。
返回值类型为float
ATAN(float_expression)
返回以弧度表示的角，其正切为指定的float表达式。
也称为反正切函数。
float_expression表示float类型或可以隐式转换为float类型的表达式。
返回值类型为float
ATN2(float_expression,
float_expression)
返回以弧度表示的角，该角位于正X轴和原点至点(y, x)的射线之间，其中，x和y是两个指定的浮点表达式的值。
float_expression表示数据类型为float的表达式。
返回值类型为float
CEILING
(numeric_expression)
返回大于或等于指定数值表达式的最小整数。
numeric_expression表示精确数字或近似数字数据类型(bit 数据类型除外)的表达式。
返回值类型与numeric_expression相同
COS(float_expression)
返回指定表达式中以弧度表示的指定角的三角余弦。
float_expression表示数据类型为float的表达式。返回值类型为float
COT(float_expression)
返回指定的float表达式中所指定角度(以弧度为单位)的三角余切值。
float_expression表示属于float类型或能够隐式转换为float的表达式。
返回值类型为float
DEGREES
(numeric_expression)
返回以弧度指定的角的相应角度。
numeric_expression表示精确数字或近似数字数据类型类别(bit数据类型除外)的表达式。返回值类型与numeric_expression相同
续表
函 数
描 述
EXP(float_expression)
返回指定的float表达式的指数值。
float_expression表示float类型或能隐式转换为float类型的表达式。
返回值类型为float
SIGN(numeric_expression)
返回指定表达式的正号(+1)、零(0)或负号(-1)。
numeric_expression是精确数字或近似数字数据类型的表达式
SIN(float_expression)
以近似数字(float)表达式返回指定角度(以弧度为单位)的三角正弦值
SIN(float_expression)
float_expression属于float类型或能够隐式转换为float类型的表达式。
返回值类型为float
SQRT(float_expression)
返回指定浮点值的平方根。
float_expression是float类型或能够隐式转换为float类型的表达式。
返回值类型为float
SQUARE(float_expression)
返回指定浮点值的平方。
float_expression是float类型或能够隐式转换为float类型的表达式。
返回值类型为float
TAN(float_expression)
返回输入表达式的正切值。
float_expression是float类型或可隐式转换为float类型的表达式，解释为弧度数。
返回值类型为float
【例19】下例产生4个不同的随机数：
DECLARE @counter SMALLINT
SET @counter = 1
WHILE @counter < 5 BEGIN
PRINT RAND(@counter)
SET @counter = @counter + 1
END
【例20】计算指定的x向量和y向量的ATN2：
DECLARE @x float
DECLARE @y float
SET @x = 35.175643
SET @y = 129.44
SELECT 'The ATN2 of the angle is: ' + CONVERT(varchar,ATN2(@x,@y))
GO
注意： 除RAND以外的所有数学函数都为确定性函数。在每次使用特定的输入值集调用这些函数时，它们都将返回相同的结果。仅当指定种子参数时，RAND才是确定性函数。
2. 字符串函数
字符串函数对字符串执行操作，并返回字符串或数值。字符串函数也为标量函数。所有内置字符串函数都是具有确定性的函数。表2-10列出了所有字符串函数及其含义。
表2-10 字符串函数
函 数
描 述
ASCII(character_expression)
返回character_expression最左端字符的ASCII代码值。
character_expression为char或varchar类型的表达式。返回值为int型
CHAR(integer_expression)
以char(1)类型返回ASCII代码等于整型表达式integer_expression的值的字符。integer_expression是介于0~255之间的整数。如果该整数表达式不在此范围内，将返回NULL值
UNICODE
(ncharacter_expression)
返回给定字符串最左端字符的Unicode代码值。
ncharacter_expression是nchar或nvarchar表达式。返回值为int型
NCHAR(integer_expression)
返回Unicode代码等于整型表达式integer_expression的值的字符。
integer_expression是介于0~65535之间的正整数。如果指定了超出此范围的值，将返回NULL。返回值类型为nchar(1)
CHARINDEX(expression1, expression2[, start_location])
在expression2中从start_location位置开始搜索expression1的首次出现，返回首字符位置。注意字符位置从1开始计算。expression1包含要查找的序列的字符表达式。最大长度限制为8000个字符。expression2表示要搜索的字符表达式。
start_location表示搜索起始位置的整数或bigint表达式。如果未指定 start_location，或者start_location为负数或0，则将从expression2的开头开始搜索。若expression2的数据类型为varchar(max)、nvarchar(max)或varbinary(max)，则返回值类型为bigint，否则为int
SOUNDEX
(character_expression)
返回字符串的四字符代码，常用来评估两个字符串的相似性。
character_expression是字符数据的字母数字表达式，可以是常量、变量或列。
返回值类型为varchar
DIFFERENCE
(character_expression1, character_expression2)
返回两个字符表达式的SOUNDEX值的差别。
character_expression是类型为char或varchar的表达式，也可以是text 类型，但只有前8000个字节有效。
返回值类型为int
LEFT/RIGHT
(character_expression, count)
以varchar类型返回从字符串character_expression左边(右边)截取的长度为count的子串。
character_expression是字符或二进制数据表达式，可以是常量、变量
REPLICATE
(character_expression, count)
将给定字符串重复count次后返回。
string_expression是字符串或二进制数据类型的表达式，可以是字符或二进制数据。返回值类型与string_expression的类型相同
续表
函 数
描 述
REVERSE(
character_expression)
将给定字符串反转后返回。
string_expression是字符串或二进制数据类型的表达式，可以是常量、变量，也可以是字符列或二进制数据列。
返回值类型为varchar或nvarchar
STR(float_expression
[, length[, decimal]])
将给定数值转换成长度为length，小数位数为decimal的数字字符串。
float_expression是带小数点的近似数字(float)数据类型的表达式。
length表示总长度，包括小数点、符号、数字以及空格。默认值为10。
decimal指小数点右边的小数位数。decimal必须小于等于16。如果decimal大于16，则将结果截断为小数点右边的16位。
返回值类型为varchar
SPACE(count)
返回由count个空格组成的字符串。
integer_expression是指示空格个数的正整数。如果integer_expression为负，则返回空字符串。返回值类型为varchar
STUFF(string_expression1,
start, length,
string_expression2)
用字符串string_expression2替换string_expression1中从start开始的length个字符并返回替换结果。
character_expression是一个字符数据表达式，是常量、变量，也可以是字符列或二进制数据列。
start为指定删除和插入的开始位置。如果start或length为负，则返回空字符串。如果start比第一个character_expression长，则返回空字符串。start可以是bigint类型。
length指定要删除的字符数。如果length比第一个character_expression长，则最多删除到最后一个character_expression中的最后一个字符。
length可以是bigint类型。
如果character_expression是受支持的字符数据类型，则返回字符数据。如果character_expression是一个受支持的binary数据类型，则返回二进制数据
LEN(string_expression)
返回指定字符串表达式的字符数，其中不包含尾随空格。
string_expression要求值的字符串表达式，可以是常量、变量，也可以是字符列或二进制数据列。
如果expression的数据类型为varchar(max)、nvarchar(max)或 varbinary (max)，则为bigint；否则为int
LOWER/UPPER (character_expression)
将大(小)写字符数据转换为小(大)写字符数据后返回字符表达式。
character_expression是一个字符数据表达式，可以是常量、变量，也可以是字符列或二进制数据列。character_expression的数据类型必须可隐式转换为varchar，否则，应使用CAST显式转换。
返回值类型为varchar或nvarchar 续表
函 数
描 述
PATINDEX(‘%pattern%’,
expression)
返回指定表达式中某种模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零。
pattern是文字字符串数据类型的表达式，可以使用通配符，但pattern 之前和之后必须有%字符(搜索第一个或最后一个字符时除外)。
expression是一个字符串数据类型的表达式，通常为要在其中搜索指定模式的列。
如果expression的数据类型为varchar(max)或nvarchar(max)，则为bigint，否则为int
REVERT(
WITH COOKIE =
@varbinary_variable)
执行上下文切换回最后一个EXECUTE AS语句的调用方。
WITH COOKIE = @varbinary_variable是指定在相应的EXECUTE AS独立语句中创建的Cookie。
@varbinary_variable的数据类型为varbinary(100)
SUBSTRING(
value_expression,
start_expression,
length_expression)
返回字符表达式、二进制表达式、文本表达式或图像表达式的一部分。
value_expression是character、binary、text、ntext或image类型的表达式。start_expression指定返回字符的起始位置的整数或bigint表达式。如果start_expression小于1，则返回的表达式的起始位置为value_ expression中指定的第一个字符。在这种情况下，返回的字符数是start_expression与length_expression-1和0两者中的较大值。如果 start_expression的值大于表达式中的字符数，将返回一个零长度的表达式。length_expression是正整数或指定要返回的value_expression的字符数的bigint表达式。
如果length_expression是负数，会生成错误并终止语句。如果start_ expression与length_expression的和大于value_expression中的字符数，则返回起始位置为start_expression的整个值表达式。
如果expression是其中一个受支持的字符数据类型，则返回字符数据。如果expression是支持的binary数据类型中的一种数据类型，则返回二进制数据。返回的字符串类型与指定表达式的类型相同(表中显示的除外)
【例21】对“Du monde entier”字符串中的每个字符，设定一个ASCII字符集并返回ASCII值以及char字符：
SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current character string position
-- and for the character string.
DECLARE @position int, @string char(15)
-- Initialize the variables.
SET @position = 1
SET @string = 'Du monde entier'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
SET NOCOUNT OFF
GO
【例22】输出字符串“New Moon”中每个字符的ASCII值和字符：
SET TEXTSIZE 0
-- Create variables for the character string and for the current
-- position in the string.
DECLARE @position int, @string char(8)
-- Initialize the current position and the string variables.
SET @position = 1
SET @string = 'New Moon'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
GO
3. 日期时间函数
日期时间函数是对日期和时间输入值执行操作，并返回一个字符串、数字或日期和时间值。这些函数都是标量函数。日期时间函数可分为用来获取系统日期和时间值的函数、用来获取日期和时间部分的函数、用来获取日期和时间差的函数、用来修改日期和时间值的函数、用来设置或获取会话格式的函数和用来验证日期和时间值的函数等6类函数。下面分别对这6类函数进行介绍。
拓展提高： SQL Server 2008 R2使用GetSystemTimeAsFileTime() Windows API来获取日期和时间值。精确程度取决于运行SQL Server实例的计算机硬件和Windows版本。此API的精度固定为100纳秒。可通过使用GetSystemTimeAdjustment() Windows API来确定该精确度。
(1) 用来获取系统日期和时间值的函数
所有的系统日期和时间值均来自运行SQL Server实例的计算机操作系统。
用来获取系统日期和时间值的函数有SYSDATETIME()、SYSDATETIMEOFFSET()、SYSUTCDATETIME()、CURRENT_TIMESTAMP()、GETDATE()和GETUTCDATE()，共6种函数，如表2-11所示。
表2-11 获取系统日期和时间值的函数
函 数
描 述
SYSDATETIME()
返回包含计算机的日期和时间的datetime2(7)值，SQL Server的实例正在该计算机上运行。时区偏移量未包含在内
SYSDATETIMEOFFSET()
返回包含计算机的日期和时间的datetimeoffset(7)值，SQL Server的实例正在该计算机上运行。时区偏移量包含在内
SYSUTCDATETIME()
返回包含计算机的日期和时间的datetime2(7)值，SQL Server的实例正在该计算机上运行。日期和时间作为UTC时间(通用协调时间)返回
CURRENT_TIMESTAMP()
返回包含计算机的日期和时间的datetime2(7)值，SQL Server的实例正在该计算机上运行。时区偏移量未包含在内
GETDATE()
返回包含计算机的日期和时间的datetime2(7)值，SQL Server的实例正在该计算机上运行。时区偏移量未包含在内
GETUTCDATE()
返回包含计算机的日期和时间的datetime2(7)值，SQL Server的实例正在该计算机上运行。日期和时间作为UTC时间(世界标准时间)返回
【例23】使用日期和时间函数返回其他格式的日期时间：
SELECT SYSDATETIME() AS SYSDATETIME
,SYSDATETIMEOFFSET() AS SYSDATETIMEOFFSET
,SYSUTCDATETIME() AS SYSUTCDATETIME
,CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP
,GETDATE() AS GETDATE
,GETUTCDATE() AS GETUTCDATE;
【例24】使用6个返回当前日期和时间的SQL Server系统函数，来返回当前系统的日期和时间：
SELECT 'SYSDATETIME() ', SYSDATETIME();
SELECT 'SYSDATETIMEOFFSET()', SYSDATETIMEOFFSET();
SELECT 'SYSUTCDATETIME() ', SYSUTCDATETIME();
SELECT 'CURRENT_TIMESTAMP ', CURRENT_TIMESTAMP;
SELECT 'GETDATE() ', GETDATE();
SELECT 'GETUTCDATE() ', GETUTCDATE();
(2) 用来获取日期和时间部分的函数
用来获取日期和时间部分的函数有DATENAME()、DATEPART()、DAY()、MONTH()和YEAR()，共5种函数，如表2-12所示。
表2-12 获取日期和时间部分的函数
函 数
描 述
DATENAME(datepart, date)
返回表示指定日期的指定datepart的字符串
DATEPART(datepart, date)
返回表示指定date的指定datepart的整数
DAY(date)
返回表示指定date的“日”部分的整数
续表
函 数
描 述
MONTH(date)
返回表示指定date的“月”部分的整数
YEAR(date)
返回表示指定date的“年”部分的整数
【例25】返回指定日期：
SELECT DATENAME(datepart, '2007-10-30 12:15:32.1234567 +05:10')
【例26】使用YEAR()、MONTH()、DAY()函数返回年、月、日：
SELECT YEAR('2012-04-15T01:01:01.1234567-07:00')
SELECT MONTH('2012-04-15T01:01:01.1234567-07:00')
SELECT DAY('2012-04-15T01:01:01.1234567-07:00')
(3) 用来获取日期和时间差的函数
用来获取日期和时间差的函数只有DATEDIFF()函数。它的语法格式如下：
DATEDIFF(datepart, startdate, enddate)
该函数返回startdate和enddate之间所跨的日期或时间datepart边界的数目。
【例27】计算一个表的两列中的日期之间所跨越的日边界数：
CREATE TABLE dbo.Duration
(
startDate datetime2, endDate datetime2
)
INSERT INTO dbo.Duration(startDate,endDate)
VALUES('2007-05-06 12:10:09','2007-05-07 12:10:09')
SELECT DATEDIFF(day,startDate,endDate) AS 'Duration'
FROM dbo.Duration;
(4) 用来修改日期和时间值的函数
修改日期和时间值的函数有DATEADD()、SWITCHOFFSET()、TODATETIMEOFF-SET()，共3种函数，如表2-13所示。
表2-13 修改日期和时间值的函数
函 数
描 述
DATEADD(datepart,
number, date)
将指定的number时间间隔(有符号整数)与指定的date的指定datepart相加后，返回该date
SWITCHOFFSET (DATETIMEOFFSET, time_zone)
SWITCHOFFSET更改DATETIMEOFFSET值的时区偏移量，并保留UTC值
TODATETIMEOFFSET(expression, time_zone)
TODATETIMEOFFSET将datetime2值转换为datetimeoffset值。
datetime2值被解释为指定time_zone的本地时间
【例28】将用户定义的变量指定为number和date的参数：
DECLARE @days int;
DECLARE @datetime datetime;
SET @days = 365;
SET @datetime = '2000-01-01 01:01:01.111'; /* 2000 was a leap year */
SELECT DATEADD(day, @days, @datetime);
【例29】使用SWITCHOFFSET显示与数据库中所存储的值不同的时区偏移量：
CREATE TABLE dbo.test
(
ColDatetimeoffset datetimeoffset
);
GO
INSERT INTO dbo.test
VALUES ('1998-09-20 7:45:50.71345 -5:00');
GO
SELECT SWITCHOFFSET (ColDatetimeoffset, '-08:00')
FROM dbo.test;
GO
SELECT ColDatetimeoffset
FROM dbo.test;
(5) 用来设置或获取会话格式的函数
用来设置或获取会话格式的函数包括@@DATEFIRST()、SET DATEFIRST()、SET DATEFORMAT、@@LANGUAGE、SET LANGUAGE、sp_helplanguage，共3种函数，如表2-14所示。
表2-14 设置或获取会话格式的函数
函 数
描 述
@@DATEFIRST
返回对会话进行SET DATEFIRST操作所得结果的当前值
SET DATEFIRST {
number | @number_var }
将一周的第一天设置为1~7的一个数
SET DATEFORMAT {
format | @format_var }
设置用于输入datetime或smalldatetime数据的日期各部分(月/日/年)的顺序
@@LANGUAGE
返回当前使用的语言的名称。@@LANGUAGE不是日期或时间函数。但是，语言设置会影响日期函数的输出
SET LANGUAGE {
[N] ‘language’ | @language_var }
设置会话和系统消息的语言环境。SET LANGUAGE不是日期或时间函数。但是，语言设置会影响日期函数的输出
sp_helplanguage [
[@language = ] ‘language’ ]
返回有关所有支持语言日期格式的信息。sp_helplanguage不是日期或时间存储过程。但是，语言设置会影响日期函数的输出
【例30】将每周的第一天设为5(星期五)，并假定当天(Today)是星期六。该SELECT语句返回DATEFIRST值和当天是此周的第几天。代码如下：
SET DATEFIRST 5;
SELECT @@DATEFIRST AS 'First Day'
,DATEPART(dw, SYSDATETIME()) AS 'Today';
(6) 用来验证日期和时间值的函数
用来验证日期和时间值的函数只有ISDATE()。
其语法格式如下：
ISDATE(expression)
该函数用来确定datetime或smalldatetime输入表达式是否为有效的日期或时间值。
【例31】使用ISDATE函数测试某一字符串是否是有效的datetime：
IF ISDATE('2009-05-12 10:19:41.177') = 1
PRINT 'VALID'
ELSE
PRINT 'INVALID'
4. 聚合函数
聚合函数对一组值执行计算，并返回单个值。所有聚合函数均为确定性函数。这表示任何时候使用一组特定的输入值调用聚合函数，所返回的值都是相同的。一般情况下，若字段中含有空值，聚合函数会忽略，但COUNT除外。
聚合函数在下列位置可作为表达式使用：
* SELECT语句的选择列表(子查询或外部查询)。
* COMPUTE或COMPUTE BY子句。
* HAVING子句。
T-SQL中的聚合函数有AVG、MIN、CHECKSUM、SUM、HECKSUM_AGG、STDEV、COUNT、STDEVP、COUNT_BIG、VAR、GROUPING、VARP、MAX。
表2-15分别对这些函数进行了介绍。
表2-15 聚合函数
函 数
描 述
AVG(
[ALL | DISTINCT] expression)
返回组中各值的平均值。空值将被忽略，后面可以跟OVER子句。
ALL：对所有的值进行聚合函数运算。ALL是默认值。
DISTINCT：指定AVG只在每个值的唯一实例上执行，而不管该值出现了多少次。
expression：是精确数值或近似数值数据类别(bit数据类型除外)的表达式。不允许使用聚合函数和子查询
CHECKSUM
(* | expression[, ...n])
返回按照表的某一行或一组表达式计算出来的校验和值。
CHECKSUM用于生成哈希索引。
*：指定对表的所有列进行计算。如果有任一列是非可比数据类型，则CHECKSUM返回错误。非可比数据类型为text、ntext、image 和cursor，也可以将上述任一类型作为基类型的sql_variant。
expression：除可比数据类型之外的任何类型的表达式 续表
函
新书资讯