6.3 Transact-SQL程序设计基础
6.3 Transact-SQL程序设计基础
SQL虽然和高级语言不同，但是它本身也具有运算、流控制等功能，也可以利用SQL语言进行编程，因此就需要了解SQL语言的基础知识，本节将主要介绍Transact-SQL语言程序设计的基础概念。
6.3.1 标识符
在SQL Server中，标识符就是指用来定义服务器、数据库、数据库对象和变量等的名称。标识符包含的字符数必须在1到128之间，对于本地临时表，最多可以有116个字符，可以分为常规标识符和分隔标识符。
1．常规标识符
常规标识符就是不需要使用分隔标识符进行分隔的标识符，它必须符合标识符的格式规则，在Transact-SQL语句中使用常规标识符时不用将其分隔。
例如，“UPDATE dic SET mean= '汉语中第一个数字' WHERE id=1”中的dic、mean、id都是常规标识符。
常规标识符的格式规则取决于数据库的兼容级别，兼容级别可以用sp_dbcmptlevel来设置。当兼容级别为80时，规则如下：
（1）第一个字符必须是如下字符之一。
· Unicode标准2.0所定义的字母。Unicode 中定义的字母包括拉丁字母a-z和A-Z，以及来自其他语言的字母字符。
· 下画线（_）、at符号（@）或者数字符号（#）。
· 在SQL Server中，某些处于标识符开始位置的符号具有特殊意义。以at符号开始的标识符表示局部变量或参数。以一个数字符号开始的标识符表示临时表或过程。以双数字符号（##）开始的标识符表示全局临时对象。
· 某些Transact-SQL函数的名称以双at符号（@@）开始。为避免混淆这些函数，建议不要使用以@@开始的名称。
（2）后续字符可以是以下几种。
· Unicode 2.0所定义的字母。
· 来自基本拉丁字母或其他国家地区脚本的十进制数字。
· at符号、美元符号（$）、数字符号或下画线。
（3）标识符不能是Transact-SQL的保留字。SQL Server保留其保留字的大小写形式。
（4）不允许嵌入空格或其他特殊字符。
2．分隔标识符
在Transact-SQL语句中，对不符合所有标识符规则的标识符必须进行分隔。而符合标识符规则的标识符可以分隔，也可以不分隔。
例如，如果要查询一张表中的全部记录，且该表的名称为“Blanks In Table Name”，若执行如下语句：
SELECT *
FROM Blanks In Table Name
则系统将会提示如下错误，如图6-29所示。
服务器: 消息156，级别15，状态1，行2
在关键字'In' 附近有语法错误。
图6-29 表名缺少分隔符
在上述语句中，“Blanks In Table Name”含有空格，不符合标识符规则，在系统分析查询时，将In当成了语句中的关键字，而不认为是表名的一部分，所以必须用分隔符方括号“[ ]”进行分隔。
在SQL Server中，Transact-SQL所使用的分隔标识符类型有两种。
· 被引用的标识符用双引号（"）分隔：SELECT * FROM "Blanks in Table Name"。
· 括在括号中的标识符用方括号（[ ]）分隔：SELECT * FROM [Blanks In Table Name]。
分隔标识符在下列情况下使用。
· 当在对象名称或对象名称的组成部分中使用保留字时，推荐不要使用保留关键字作为对象名称。从SQL Server早期版本升级的数据库可能含有标识符，这些标识符包括早期版本中未保留而在SQL Server 2000中保留的字。可用分隔标识符引用对象直到可改变其名称。
· 当使用未被列为合法标识符的字符时，SQL Server 允许在分隔标识符中使用当前代码页中的任何字符。但是，不加选择地在对象名称中使用特殊字符将使SQL 语句和脚本难以阅读和维护。
分隔标识符的格式规则如下。
· 分隔标识符可以包含与常规标识符相同的字符数（1~128个，不包括分隔符字符）。本地临时表标识符最多可以包含116个字符。
· 标识符的主体可以包含当前代码页内字母（分隔符本身除外）的任意组合。例如，分隔符标识符可以包含空格、对常规标识符有效的任何字符以及下列任何字符：代字号（~）、连字符（-）、惊叹号（!）、左括号（{）、百分号（%）、右括号（}）、插入号（^）、撇号（'）、and号（&）、句号（.）、左圆括号（（）、反斜杠（\）、右圆括号（））、重音符号（`）。
使用引号分隔标识符时，仅当QUOTED_IDENTIFIER选项设置为ON时才有效。QUOTED_IDENTIFIER叫做连接选项。在默认情况下，用于SQL Server的Microsoft OLE DB提供程序和SQL Server ODBC驱动程序连接时，都将之设置为ON，而DB-Libarary则不将QUOTED_IDENTIFIER设置为ON。不管使用何种接口，各应用程序和用户可随时更改设置。
SQL Server提供了多种方法来指定该选项。在SQL Server企业管理器和SQL查询分析器中，该选项可在对话框中设置。例如，在SQL查询分析器中，与服务器连接成功后，可选择主菜单中的“查询”→“当前连接属性”菜单项，或者在“查询”窗口中右击，在弹出的快捷菜单中选择“当前连接属性”菜单项，然后程序会弹出如图6-30所示对话框。若要将QUOTED_IDENTIFIER设置为OFF，则单击取消选择“设置quoted_identifier”标签前的复选框；若要将QUOTED_IDENTIFIER设置为ON，则单击选择“设置quoted_identifier”标签前的复选框，设置完成后，单击“确定”按钮保存设置。
图6-30 设置当前连接属性
在Transact-SQL中，还可以使用SET QUOTED_IDENTIFIER、sp_dboption的quoted identifier或sp_configure的user options选项来设置。例如，可以使用如下两条SET语句来打开或关闭该选项：
SET QUOTED_IDENTIFIER ON
SET QUOTED_IDENTIFIER OFF
3．使用标识符
数据库对象的名称被看成是该对象的标识符，SQL Server中每个内容都可带有标识符。服务器、数据库和数据库对象都有标识符，大多数对象要求带有标识符，但对有些对象（如约束），标识符是可选项。
一个对象的完整名称包括4个标识符：服务器名称、数据库名称、所有者名称和对象名称。其格式如下：
[ [ [ server. ] [ database ] .] [ owner_name ] .] object_name
服务器、数据库和所有者的名称即对象名称限定符。在实际使用时，使用全称比较烦琐，因此当引用一个对象时，并不指定服务器、数据库和所有者，而是利用句号标出它们的位置，从而省略限定符。对象名的有效格式如下：
server.database.owner_name.object_name
server.database..object_name
server..owner_name.object_name
server...object_name
database.owner_name.object_name
database..object_name
owner_name.object_name
object_name
在上面的简写格式中，没有指明的部分将使用如下默认设置值。
· server：本地服务器。
· database：当前数据库。
· owner_name：在指定的数据库中与当前连接会话的登录标识相对应的数据库用户或数据库拥有者。
例如，当一个用户名为osfans的用户登录到cllab服务器上使用new数据库时，使用了下面的语句查询dic表中所有的内容：
SELECT *
FROM dic
若要写成完整形式，则要写成：
SELECT *
FROM cllab.new.osfans.dic
6.3.2 数据类型
数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。包含数据的对象都具有一个相关的数据类型，此数据类型定义对象所能包含的数据种类（字符、整数、二进制数等）。
在SQL Server中，以下对象可以具有数据类型。
· 表和视图中的列。
· 存储过程中的参数。
· 变量。
· 返回一个或多个特定数据类型数据值的Transact-SQL函数。
· 具有一个返回代码的存储过程。
SQL Server中提供了各种数据类型，跟以前的版本相比，SQL Server 2000又新增了几种系统数据类型。在SQL Server中的数据类型可以分为系统数据类型和用户定义类型两种，用户定义数据类型是在SQL Server系统数据类型的基础上使用系统存储过程sp_addtype所建立的数据类型。
1．系统数据类型
SQL Server所提供的系统数据类型如下。
· bigint：从-263 (-9 223 372 036 854 775 808)到263-1(9 223 372 036 854 775 807)的整型数据（所有数字）。
· int：从-231 (-2 147 483 648)到231 -1(2 147 483 647)的整型数据（所有数字）。
· smallint：从-215 (-32 768)到215-1(32 767)的整数数据。
· tinyint：从0到255的整数数据。
· bit：1或0的整数数据。
· decimal：从-1038 +1到1038+1的固定精度和小数位的数字数据。
· numeric：功能上等同于decimal。
· money：货币数据值介于-263 (-9 223 372 036 854 775 808)与
263-1 (+9 223 372 036 854 775 807)之间，精确到货币单位的万分之一。
· smallmoney：货币数据值介于-2 147 483 648与+2 147 483 647之间，精确到货币单位的万分之一。
· float：从-1.79×10308到1.79×10308的浮点精度数字。
· real：从-3.40×1038 到3.40×1038的浮点精度数字。
· datetime：从1753年1月1日到9999年12月31日的日期和时间数据，精确到千分之三秒（或3毫秒）。
· smalldatetime：从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。
· char：固定长度的非Unicode字符数据，最大长度为8 000个字符。
· varchar：可变长度的非Unicode数据，最长为8 000个字符。
· text：可变长度的非Unicode数据，最大长度为231 -1(2 147 483 647)个字符。
· nchar：固定长度的Unicode数据，最大长度为4 000个字符。
· nvarchar：可变长度Unicode数据，其最大长度为4 000字符。sysname是系统提供用户定义的数据类型，在功能上等同于nvarchar(128)，用于引用数据库对象名。
· ntext：可变长度Unicode数据，其最大长度为230 -1(1 073 741 823)个字符。
· binary：固定长度的二进制数据，其最大长度为8 000个字节。
· varbinary：可变长度的二进制数据，其最大长度为8 000个字节。
· image：可变长度的二进制数据，其最大长度为231 -1(2 147 483 647)个字节。
· cursor：游标的引用。
· sql_variant：一种存储SQL Server支持的各种数据类型（text、ntext、timestamp和sql_variant除外）值的数据类型。
· table：一种特殊的数据类型，存储供以后处理的结果集。
· timestamp：数据库范围的唯一数字，每次更新时也进行更新。
· uniqueidentifier：全局唯一标识符（GUID）。
可以按照存放在数据库中的数据的类型对SQL Server提供的所有这些系统数据类型进行分类。
· 整数型：tinyint、smallint、int、bigint。
· 小数数据类型：decimal、numeric。
· 浮点数据型：float、real。
· 字符型：char、varchar、text。
· 逻辑数值型：bit。
· 货币型：smallmoney、money。
· Unicode字符型：nchar、nvarchar、ntext。
· 二进制数据型：binary、varbinary、image。
· 日期时间型：smalldatetime、datetime。
· 其他数据类型：cursor、sql_variant、table、timestamp、uniqueidentifer。
下面将详细介绍几种常用的数据类型。
（1）整数型。
对于整数类型，在使用时不需要其他参数，而直接使用。比如要在new数据库建立一张新表，而且包含4个字段，分别为4种不同的整数类型，则可以执行如下SQL语句：
USE new
CREATE TABLE int4table
(
i1 tinyint,
i2 smallint,
i3 int,
i4 bigint
)
（2）小数数据类型。
在SQL Server中，小数数据使用decimal或numeric数据类型存储。存储decimal或numeric数值所需的字节数取决于该数据的数字总数和小数点右边的小数位数。在声明小数数据类型时，可以定义数据的精度和小数位数，格式如下：
decimal [(p [, s])]
和
numeric [(p [, s])]
其中p和s的含义如下。
· p（精度）：指定小数点左边和右边可以存储的十进制数字的最大个数。精度必须是从1到最大精度之间的值。最大精度为38。
· s（小数位数）：指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从0到p之间的值。默认小数位数是0，因而0≤s≤p。最大存储大小基于精度而变化。
若在建立数据表时为某个字段指定了小数数据类型，则在赋值时必须保证符合其定义的精度和小数位数，否则系统将会报错。
例如，通过下列SQL语句创建一个小数表decimal3table。
USE new
CREATE TABLE decimal3table
(
d1 decimal(3,2),
d2 decimal(3),
d3 decimal
)
然后输入如下语句，插入一行记录：
INSERT INTO decimal3table VALUES (4.567, 4.567, 4.567)
最后输入SELECT语句查询表中所有的记录：
SELECT * FROM decimal3table
在主菜单中选择“查询”→“文本显示结果”菜单项，或者单击工具栏中“执行模式”按钮右侧的下三角按钮，在弹出的快捷菜单中选择“文本显示结果”菜单项，最后执行查询，则可得到如图6-31所示结果，系统结果如下：
d1 d2 d3
----- ----- --------------------
4.57 5 5
（所影响的行数为1行）
由于d1的小数位数设置为2，则系统自动为4.567保留两位小数，结果为4.57；而d2未指定小数位数，则默认为0，系统自动为4.567取整为5；d3既未指定精度也未指定小数位数，也自动取整为5。
图6-31 文本形式显示查询结果
而当执行如下语句插入新记录时，
INSERT INTO decimal3table VALUES (45.67, 45.67, 45.67)
系统给出错误提示。
服务器: 消息8115，级别16，状态8，行1
将numeric转换为数据类型numeric 时发生算术溢出错误。
语句已终止。
这正是由于45.67不满足d1的数据类型所致，因为d1的最大位数是3，而小数部分最大为2，所以整数部分的最大位数为1，而45.67中整数部分的位数为2，故出错。
因此，在插入小数时，数据的整数部分的位数不能大于p-s。这就要求用户在声明小数数据数据时，需要注意数据最大的整数位数和小数位数，如果最大的整数位数为a，最大的小数位数为s，则可将该字段的数据类型声明为“decimal ( a+s, s )”。
（3）浮点数据型。
SQL Server还提供了用于表示浮点数值的数据类型，可以近似地表示数据的数值，这些数值所保留的精度由二进制数字系统的精度决定，SQL Server中可以使用如下两种浮点数据型。
· float [(n)]：可以表示从-1.79×10308到1.79×10308的浮点精度数字。其中的参数n用于存储科学记数法时float数尾的位数，并以此指示其精度和存储大小。n必须为1到53之间的值。其中，当n为1到24之间时，其精度为7位数，存储大小为4字节，而当为25到53之间时，其精度为15位数，存储大小为8字节。
· real：从-3.40×1038到3.40×1038的浮点精度数字。存储大小为4字节。
当系统显示这些数据时，通过按照科学记数法来表示，a×10b会被表示成为aEb，其中a为1到10之间的小数，而b为整数。
（4）字符型。
字符串存储时，通过采用字符型数据类型，它可以由字母、数字甚至符号组成。例如，“928”、“abc”、“我的（My）”等，这些都是有效的字符数据。
在实际使用中，可以根据字符串的长度来指定其数据类型。
当列中各项为同一固定的长度且长度小于8000时，应考虑使用char数据类型，并用char(n)来指定其长度，n必须在1～8000之间，此时，存储该字段的大小为n个字节。
若各项值的长度都不超过8000，但其长度却各不相同，则可以使用varchar数据类型，并用varchar(n)限制其最长的长度，n必须在1～8000之间，此时，存储每项值的空间与字符串的长度相关，n只是最大的字节数。
若字符串的长度超过8000，则考虑使用text数据类型，它可以支持最大长度为231-1 (2 147 483 647)个字符。它不需要指定字符串的长度，SQL Server会根据数据的长度自动分配空间，它的存储大小也取决于字符串的实际长度。
以上三种字符数据类型都是用于存储非UNICODE字符串的，若要在SQL Server中存储国际化字符数据，或者要支持多语言，则应考虑使用存储UNICODE字符串的nchar、nvarchar、ntext数据类型，从而减少字符转换等问题。不过应该注意的是，nchar与nvarchar的最大长度为4000，而ntext数据型的最大长度为230 – 1，与非UNICODE字符串的数据类型并不一致。
在使用UNICODE字符时，应当在前面加上一个标识符N，比如“N'这是一个UNICODE字符串'”。但是在存储时并不会保存该标识符，而且在打印显示时，也不会显示出该标识符，例如，执行以下语句：
DECLARE @var NCHAR(25)
SET @var=N'这是一个UNICODE字符串'
PRINT @var
其中，DECLARE为var声明数据类型，SET为该变量赋值，PRINT为打印该变量的值，执行结果如图6-32所示。
图6-32 显示UNICODE字符串
（5）逻辑数值型。
SQL Server支持逻辑数据类型，它可以存储整型数据1、0或NULL。如果输入0时，其值为0，输入NULL时，其值为NULL，而输入其他值时，SQL Server将它们当作1看待。
例如，执行以下SQL语句，可以创建一个4个字段均为逻辑数据类型的新表，然后插入一行数据，并且查询该表中所有的数据。
CREATE TABLE bit4table
(
b1 bit,
b2 bit,
b3 bit,
b4 bit
)
INSERT INTO bit4table VALUES (1,0,NULL,-2)
SELECT * FROM bit4table
最终执行结果如图6-33所示，其中前3项的值均保持原来插入的值，而插入其他值时，都被存储为1。
图6-33 使用bit数据类型
（6）货币型。
货币数据表示正的或负的货币值。在SQL Server中，可以使用money或smallmoney数据类型来存储货币数据，存储的精确度为精确到货币单位的万分之一，其中money数据类型可存储的货币数据值介于-263 (-9 223 372 036 854 775 808)与263-1(+9 223 372 036 854 775 807)之间，而smallmoney可存储的货币数据值介于-2 147 483 648与+2 147 483 647之间。而如果超出了上述范围，则一般考虑使用decimal数据类型代替。
在实际使用时，货币数据不需要用引号隔开，但是，在货币数值之前，必须带有适当的货币符号。例如，若要指定100英镑，则使用￡100，而若要指定100美元，则使用$100。
（7）二进制数据类型。
二进制数据由十六进制表示，在SQL Server中，使用binary、varbinary、image数据类型来存储。
通常，如果二进制数据的长度是固定的，而且均小于8000个字节，则可以使用binary数据类型，在声明时使用binary[(n)]来指定它的固定长度，其中n必须在1～8000之间，其存储空间大小为n+4。
若二进制数据的长度仍然小于8000个字节，但长度不固定，则可以使用varbinary数据类型，在声明时使用varbinary[(n)]来指定它的最大字节数，其中n必须在1～8000之间，其存储空间大小并不为n，而是与实际数据长度相关。
若二进制数据的长度超过8000字节，则考虑使用image数据类型，它可以支持最大长度为231 -1 (2 147 483 647)个字节。它不需要指定最大字节数，SQL Server会根据数据的长度自动分配空间。通过可以使用该数据类型存储办公文档、音乐文件、图像文件等各种二进制文件。
二进制数据以0x开头，后面接着十六进制，每两位算一个字节。例如0xF5，就表示16进制中的F5，相当于十进制数的245，这是一个字节。
执行如下语句，可以发现varbinary与binary两者的区别：
DECLARE @var varbinary(5)
SET @var=0xF5
PRINT @var
DECLARE @var2 binary(5)
SET @var2=0xF5
PRINT @var2
结果如图6-34所示，其中varbinary(5)类型的@var变量，其大小仍为0xF5，而binary(5)类型的@var2变量，显示它的值时变成了“0xF500000000”，在值不足5个字节时，全部用0来填充。因此，这两种的存储空间并不相同，varbinary与实际长度有关，而binary则与指定的n的值相关。
图6-34 使用二进制数据类型
（8）日期时间型。
SQL Server中还提供了专门的日期时间类型，包括smalldatetime和datetime两种数据类型，两者的时间范围和精确度都不相同。
· datetime：从1753年1月1日到9999年12月31日的日期和时间数据，精确到千分之三秒（或3毫秒）。
· smalldatetime：从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。其中，若秒钟的值小于或等于29.998秒，则将其舍去，若大于或等于29.999秒，则向上进位。
SQL Server可以识别以下列格式括在单引号（'）中的日期和时间。
· 字母日期格式（例如，'April 15, 1998'）。
· 数字日期格式（例如，'4/15/1998'、'April 15，1998'）。
· 未分隔的字符串格式（例如'19981207'、'December 12，1998'）。
字母日期格式中，既可以使用当前语言中给出的月的全名，又可以使用月的缩写，下面是SQL Server日期数据的合法字母格式，其中括号内的字符是可选字符。
Apr[il] [15][,] 1996
Apr[il] 15[,] [19]96
Apr[il] 1996 [15]
[15] Apr[il][,] 1996
15 Apr[il][,][19]96
15 [19]96 apr[il]
[15] 1996 apr[il]
1996 APR[IL] [15]
1996 [15] APR[IL]
而数字日期格式中，可以使用斜杠（/）、连字符（-）、小数点（.）作为分隔符来界定年月日，当语言被设置为美国英语时，默认的日期顺序为月日年，“4/5/6”将代表2006年4月5日，当语言设置为中文时，则默认的顺序为年月日，“4.5.6”将代表2004年5月6日。为了更加直观，还可以使用“SET DATEFORMAT”直接设定日期的识别的顺序，有效的参数为mdy、dmy、ymd、ydm、myd 和dym，其中m代表月，d代表日、y代表年。例如，要将日期按日月年的顺序识别，则可以执行“SET DATEFORMAT dmy”语句。
未分隔的字符串格式是指用来表示日期的数字间不需要分隔符号的字符串，可以使用4位、6位或8位来表示日期。其中4位被解释为年份，如果使用6位或8位的话，月和日都必须占两位，例如“060504”或“20060504”。
SQL Server可识别以下时间数据格式，用单引号（'）把每一种格式括起来，方括号中表示可选的内容。
14:30
14:30[:20:999]
14:30[:20.9]
4am
4 PM
[0]4[:30:20:500]AM
可以用AM或PM的后缀（不区分大小写）来表示时间是上午还是下午，小时数可以采用12进制或者24进制，不同值时解释不同。
· 小时值0表示午夜（AM）后的小时，不论是否指定AM。当小时值等于0时不能指定PM。
· 如果未指定AM和PM，小时值1到11表示中午以前的小时。当指定AM时，也表示中午以前的小时。当指定PM时，则表示中午以后的小时。
· 如果未指定AM和PM，小时值12表示始于中午的小时。如果指定为AM，则表示始于午夜的小时。如果指定为PM，则表示始于中午的小时。例如：12:01是指中午过后1分钟，即12:01 PM，而12:01AM是指午夜过后1分钟。指定为12:01AM与指定为00:01或00:01 AM相同。
· 如果指定AM或PM，小时值13到23表示中午以后的小时。当指定PM时，也表示中午以后的小时。当小时值为13到23时，不能指定为AM。
· 小时值24无效，用12:00AM或00:00表示午夜。
可以在毫秒之前加上冒号（:）或者小数点（.），两者所代表的时间可能会不同。如果前面加冒号，这个数字表示毫秒。如果前面加句号，单个数字表示十分之一秒，两个数字表示百分之一秒，3个数字表示毫秒。例如，12:30:20:1 表示12:30过了20又1毫秒，而12:30:20.1表示12:30过了20又十分之一秒。
2．用户定义的数据类型
用户定义的数据类型总是根据系统数据类型进行定义的，当在几个表中存储同一种数据类型时，为保证存放这种数据的列具有相同的数据类型、长度、可空性，可以使用用户定义的数据类型。例如，可以基于int数据类型创建名为telno的用户定义数据类型。
创建用户定义的数据类型时必须提供以下3个参数。
· 数据类型的名称。
· 新数据类型所依据的系统数据类型。
· 为空性（数据类型是否允许空值），如果为空性未明确定义，系统将依据数据库或连接的ANSI Null默认设置进行指派。
可以使用系统存储过程创建或删除用户定义的数据类型。
创建用户定义的数据类型，可以利用系统存储sp_add来实现，其语法格式如下：
sp_addtype {<类型>}, [ , <系统数据类型> ], [, { NULL | NOT NULL | NONULL } ]
例如，要创建一个名为birthday的数据类型，其基于的系统数据类型是DateTime，而且允许空，则可以通过下列语句来添加：
sp_addtype birthday, DateTime, 'NULL'
当用户定义的数据类型不需要时，可以通过sp_droptype来实现，其语法如下：
sp_droptype {'<类型>'}
若要删除birthday数据类型，则可执行如下语句：
sp_droptype {'birthday'}
用户还可以通过企业管理器来管理用户定义的数据类型。要添加一个数据类型，操作步骤如下：
（1）打开要添加数据类型的数据库，右击“用户定义的数据类型”选项，在弹出的快捷菜单中选择“新建用户定义数据类型”命令，如图6-35所示。
图6-35 新建用户定义数据类型
（2）接着弹出如图6-36所示对话框。在“名称”文本框中可以指定新数据类型的名称，在“数据类型”下拉列表框中可以选择所依据的系统类型，单击“允许NULL值”复选框可设置该数据类型的可空性。
图6-36 用户定义数据类型属性
（3）单击“确定”按钮，即在企业管理器窗口中出现刚创建的数据类型。
若要在其中删除一个用户定义的数据类型，右击要删除的数据类型，在弹出的快捷菜单中选择“删除”命令，即可删除该数据类型。
6.3.3 运算符
运算符是一种符号，用来指定要在一个或多个表达式中执行的操作。SQL Server 2000使用下列几类运算符：算术运算符、赋值运算符、按位运算符、比较运算符、逻辑运算符、字符串串联运算符、一元运算符等。
1．算术运算符
算术运算符在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。
· +：加法。
· -：减法。
· *：乘法。
· /：除法。如果被除数与除数都是整数，则最后所得的结果将舍去小数部分，从而保证一个整数。
· %：返回一个除法的整数余数。例如，12%5=2，这是因为12除以5，余数为2。
另外，加和减运算符也可用于对datetime及smalldatetime值执行算术运算，其格式为“日期+i”或“日期-i”，其值为该日期后i天或前i天。
例如，若要找出score1表（包括sno、chinese、english、maths等4个字段）3门考试总分在100～199分之间的学生的学号，已知每门的分数都是100分以内的整数，则可以执行如下的语句。
SELECT sno
FROM score1
WHERE (chinese + english + maths ) / 100 = 1
显然当一个整数除以100的商为1时，其值必在100～199之间。
再例如，若要抽查score1表中学号尾数为8的学生的各科成绩，则可以执行如下的语句：
SELECT *
FROM score1
WHERE sno % 10 = 8
显然当一个整数除以10的余数为8时，其个位数必定为8。
2．赋值运算符
Transact-SQL有一个赋值运算符，即等号（=），它将表达式的值赋给一个变量，例如，下面的语句中先声明了一个变量，再将一个表达式的值赋给它，最后打印出结果。
DECLARE @var varbinary(5)
SET @var=0xF5
PRINT @var
也可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。例如，要通过score1表查看所有学生的成绩，其中学生的学号要以“******”的形式显示，则可以执行如下的语句：
SELECT sno='******', chinese, english, maths
FROM score1
最终的输出结果如下：
sno chinese english maths
------ ----------- ---------- ---------
****** 80 80 80
****** 70 90 80
****** 62 57 99
****** 75 85 85
****** 90 88 88
****** 82 58 90
3．按位运算符
按位运算符在两个表达式之间执行位操作，这两个表达式可以为整型数据类型分类中的任何数据类型。按位运算符包括&（按位与）、|（按位或）、^（按位异或）。
按位运算符的操作数可以是整型或二进制字符串数据类型分类中的任何数据类型（但image 数据类型除外）。此外，两个操作数不能同时是二进制字符串数据类型分类中的某种数据类型，即不能都为varbinary或binary数据类型中的任意两个组合。
在进行运算时，可以首先把整数数据转换为二进制数据，再对二进制数据进行按位运算。如果两个二进制数中对应数位上的数均为1，则按位与为1，按位或为1，按位异或为0；对应数位上的数均为0，则按位与为0，按位或为0，按位异或为0；对应数位上的数分别为0和1，则按位与为0，按位或为1，按位异或为1。
例如，当a=5且b=6时，首先将a化为二进制即101，将b化为二进制即110，则两者的按位与a&b=100，即十进制中的4，两者的按位或a|b=111，即十进制中的7，两者的按位异或a^b=11，即十进制的3。可以通过如下的语句检验计算的结果：
DECLARE @a INT, @b INT
SET @a=5
SET @b=6
SELECT 'a&b'=@a&@b, 'a|b'=@a|@b, 'a^b'=@a^@b
运行结果如下所示：
a&b a|b a^b
----------- ----------- -----------
4 7 3
（所影响的行数为1 行）
4．比较运算符
比较运算符测试两个表达式是否相同。除了text、ntext或image数据类型的表达式外，比较运算符可以用于所有的表达式。在WHERE子句中使用比较运算符，可以筛选出符合搜索条件的行。
SQL Server 2000中提供了如下几种比较运算符。
· =（等于）。
· >（大于）。
· <（小于）。
· >=（大于或等于）。
· <=（小于或等于）。
· <>（不等于）。
· !=（不等于）。
· !< （不小于）。
· !> （不大于）。
比较运算符的结果有布尔数据类型，它有3种值：TRUE、FALSE 及UNKNOWN。那些返回布尔数据类型的表达式被称为布尔表达式。
和其他SQL Server数据类型不同，不能将布尔数据类型指定为表列或变量的数据类型，也不能在结果集中返回布尔数据类型。但是在WHERE子句中可以使用该表达式，从而筛选出使得表达式返回值为TRUE的记录行。
例如要在score1表中查找语文成绩及格（即大于等于60分）的所有成绩记录，可以执行如下的语句。
SELECT *
FROM score1
WHERE chinese >= 60
5．逻辑运算符
逻辑运算符对某个条件进行测试，以获得其真实情况。逻辑运算符和比较运算符一样，返回带有TRUE或FALSE值的布尔数据类型。SQL Server总共提供了如下逻辑运算符。
· ALL：如果一系列的比较都为TRUE，那么就为TRUE。
· AND：如果两个布尔表达式都为TRUE，那么就为TRUE。
· ANY：如果一系列的比较中任何一个为TRUE，那么就为TRUE。
· BETWEEN：如果操作数在某个范围之内，那么就为TRUE。
· EXISTS：如果子查询包含一些行，那么就为TRUE。
· IN：如果操作数等于表达式列表中的一个，那么就为TRUE。
· LIKE：如果操作数与一种模式相匹配，那么就为TRUE。
· NOT：对任何其他布尔运算符的值取反。
· OR：如果两个布尔表达式中的一个为TRUE，那么就为TRUE。
· SOME：如果在一系列比较中，有些为TRUE，那么就为TRUE。
其中ALL、ANY、EXISTS、IN、SOME等通过与子查询一起使用，这将在6.4节中进行介绍。其中SOME是SQL-92标准的ANY的等效，所以这里推荐使用ANY。
AND、OR、NOT这几个逻辑运算符经常在WHERE子句中使用，例如要在score1表中查找语文成绩及格（即大于等于60分）、英语成绩优良（70～79分之间）、数学成绩优秀（大于等于85分）的所有的学生的学号记录，可以执行如下的语句。
SELECT *
FROM score1
WHERE NOT ( chinese < 60 ) AND english >=70 AND english <80 AND maths>=85
另外BETWEEN运算符运用起来比较直观，其语法格式如下：
<表达式> [NOT] BETWEEN <最小值> AND <最大值>
如果设最小值为min，最大值为max，则a BETWEEN min AND max成立的条件为min≤a≤max，即与“a>=min AND a<=max”等价；a NOT BETWEEN min AND max成立的条件为a<min或者a>max，即与“a<min OR a>max”等价。
LIKE运算符可以用来检验操作数是否与给定的模式相匹配。在查询数据库时，可以在WHERE子句中使用该运算符来匹配符合条件的字符串，除了6.2.5节中介绍的使用“%”匹配零个或多个字符，使用“_”匹配一个字符，还可以使用“[ ]”匹配指定范围（例如[a～f]）或集合（例如[abcdef]）内的任何单个字符，还可以使用“[^]”匹配不属于指定范围（例如[^a～f]）或集合（例如[^abcdef]）内的任何单个字符。
例如，要找出所有姓王的两个字的学生信息，则可以使用如下语句。
SELECT *
FROM studentinfo
WHERE sname LIKE '王_'
如果要找出所有不是姓“赵钱孙李周吴郑王”的学生的信息，则可以执行如下带有LIKE关键字的SQL语句。
SELECT *
FROM studentinfo
WHERE sname LIKE '[^赵钱孙李周吴郑王]%'
6．字符串串联运算符
字符串串联运算符允许通过加号（+）进行字符串串联，将两个或多个字符串合并成一个字符串。这个加号也被称为字符串串联运算符。其他所有的字符串操作都可以通过字符串函数进行处理。
例如，要查询score1表中所有学生的成绩的分数，并以“语文：*分”的格式显示分数（“*”为具体的分数，比如某个学生语文成绩为80分，则显示为“语文：80分”），可以执行如下的语句。
SELECT sno, chinese= '语文：' + CAST(chinese AS varchar) + '分'
FROM score1
其中CAST是数据类型转换函数，在这里将chinese转换为字符型，然后与“语文：”和“分”等字符串相联。
7．一元运算符
一元运算符只对一个表达式执行操作，这个表达式可以是数字数据类型分类中的任何一种数据类型。SQL Server提供了如下3种一元运算符。
· +：数值为正。
· -：数值为负。
· ～：（按位非）返回数字的补数。
“+”和“-”运算符可以用于数字数据类型分类的任何数据类型的表达式。“～”（按位非）运算符只可以用于整型数据类型分类的任何数据类型的表达式。
6.3.4 变量
在SQL Server中，变量分为局部变量和全局变量。局部变量名称前面有一个@符号，由用户定义和使用；全局变量名称前有两个@符号，由系统定义和维护。
1．局部变量
局部变量由用户定义，仅在声明它的批处理、存储过程或触发器中有效，批处理结束后，局变量将变成无效。可以使用DECLARE语句进行声明，语法如下：
DECLARE { <变量名> <数据类型> } [,...n]
其中变量名必须以@开头，而且一次可以定义多个变量。例如，
DECLARE @i int,@name char(8)
如果要给变量赋值，可以使用SET和SELECT语句，其语法分别如下：
SET <变量名>=<表达式>
SELECT { <变量名>=<表达式> } [,...n]
2．全局变量
全局变量记录了SQL Server中的各种状态消息，它们不能被显示地声明或赋值，而且不能由用户定义。在SQL Server中常用的全局变量如下。
· @@CONNECTIONS：返回自SQL Server本次启动以来，所接受的连接的次数。
· @@CURSOR_ROWS：返回游标打开后的行数。
· @@ERROR：返回最后执行的Transact-SQL语句的错误代码。
· @@FETCH_STATUS：返回被FETCH语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
· @@LANGUAGE：返回当前使用的语言名。
· @@MAX_CONNECTIONS：返回SQL Server上允许的同时用户连接的最大数。
· @@ROWCOUNT：返回受上一语句影响的行数。
· @@TRANCOUNT：返回当前连接的活动事务数。
· @@VERSION：返回SQL Server当前安装的日期、版本和处理器类型。
6.3.5 批处理
批处理是包含一个或多个Transact-SQL语句的组，从应用程序一次性地发送到SQL Server执行。SQL Server将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条。一个批处理语句以GO结束。
编译错误使执行计划无法编译，从而导致批处理中的任何语句均无法执行。
运行时错误可能会产生以下两种影响。
· 大多数运行时错误将停止执行批处理中当前语句和它之后的语句。
· 少数运行时错误（如违反约束）仅停止执行当前语句。而继续执行批处理中其他所有语句。
在遇到运行错误之前执行的语句不受影响。唯一的例外是如果批处理在事务中而且错误导致事务回滚，回滚时，错误之前所进行的未提交的数据修改。
假定在批处理中有10条语句，如果第4条语句有一个语法错误，则不执行批处理中的任何语句。如果编译了批处理，而第二条语句在执行时失败，则第一条语句的结果不受影响，因为它已经执行。
6.3.6 注释
注释是程序代码中不执行的文本字符串，也称为注解。注释可用于说明代码或暂时禁用正在进行诊断的部分语句和批处理。使用注释对代码进行说明，可使程序代码更易于维护，更具可读性。注释通常用于记录程序名称、作者姓名和主要代码更改的日期，还可用于描述复杂计算或解释编程方法。
SQL Server支持两种类型的注释字符。
· --（双连字符）：这些注释字符可与要执行的代码处在同一行，也可另起一行。从双连字符开始到行尾均为注释。对于多行注释，必须在每个注释行的开始使用双连字符。
· /*……*/（正斜杠-星号对）：这些注释字符可与要执行的代码处在同一行，也可另起一行，甚至在可执行代码内。从开始注释对(/*) 到结束注释对(*/) 之间的全部内容均视为注释部分。对于多行注释，必须使用开始注释字符对(/*) 开始注释，使用结束注释字符对(*/) 结束注释。注释行上不应出现其他注释字符。注释不能跨越批处理。整个注释必须包含在一个批处理内。
6.3.7 控制流语句
控制流语句是Transact-SQL提供特殊关键字，用于控制Transact-SQL 语句、语句块和存储过程的执行流。这些关键字可用于Transact-SQL语句、批处理和存储过程中。
若不使用控制流语句，则各语句按其出现顺序依次执行。控制流语句使用与程序设计相似的构造，使语句得以互相连接、关联和相互依存，从而提高编程语言的处理能力。SQL Server提供的控制语句如下。
· BEGIN...END：定义语句块。
· BREAK：退出最内层的WHILE循环。
· CONTINUE：重新开始WHILE循环。
· GOTO <标签>：跳到“标签”所定义的语句处继续进行处理。
· IF...ELSE：定义条件以及当一个条件为FALSE时的操作。
· RETURN：无条件退出。
· WAITFOR：为语句的执行设置延迟。
· WHILE：当条件为TRUE时重复执行语句。
这些语句跟Visual Basic基本相同，应该注意的是IF语句中不包括“THEN”，其格式如下：
IF <条件>
{sql语句}
[ ELSE
{sql语句} ]
下面举例说明循环语句的使用方法。例如，要计算从1到36所有整数的和，可以使用WHILE语句来实现。其具体语句如下：
DECLARE @i int,@s int
SET @i=1
SET @s=0
WHILE @i<=36
BEGIN
SET @s=@s+@i
SET @i=@i+1
END
PRINT '1+2+3+…36='+ CAST( @s AS varchar)
其中CAST函数为数据类型转换函数，在此将@s由int转换为varchar型，然后再与前面的加法的字符串相连接，最终运行的结果如下：
1+2+3+…+36=666
除了可以利用这些语句来实现变量的运算外，也可以用来将变量与数据表中的列值比较，从而实现比较高级的SELECT查询。比如要查询dic表中序号为斐波拉契数列（即1，2，3，5，8……，后一项是前两项的和）的记录行，可以执行如下语句。
DECLARE @i int,@s int
SET @i=1
SET @s=1
WHILE @i<=2000
BEGIN
SET @s=@s+@i
SET @i=@s-@i
SELECT * FROM dic WHERE 序号=@i
END
反过来，也可以把SELECT查询的结果赋值给变量，从而能够通过这些变量值输出较为友好的信息。例如：
DECLARE @i int, @max int, @min int
SET @i = (SELECT count(*) FROM dic)
SET @max = (SELECT max(len(字)) FROM dic)
SET @min = (SELECT min(len(字)) FROM dic)
PRINT 'dic 表中共有'+ CAST(@i AS varchar) + ' 行记录'
PRINT ''
PRINT '每行收有' + CAST(@min AS varchar) + ' 到' + CAST(@max AS varchar) + ' 个汉字'
执行的结果如下：
dic 表中共有3880 行记录
每行收有1 到114 个汉字
另外WAITFOR语句也是SQL Server中一个有特色的语句，可以在指定的时间或延迟之后执行语句块、存储过程。其语法如下：
WAITFOR { DELAY '<时间>' | TIME '<时间>' }
其中，
· DELAY：指示SQL Server一直等到指定的时间过去，最长可达24小时。
· '<时间>'：要等待的时间。可以按datetime数据可接受的格式指定time，也可以用局部变量指定此参数。不能指定日期，在datetime值中不允许有日期部分。
· TIME：指示SQL Server等待到指定时间。
6.3.8 函数
函数是由一个或多个Transact-SQL语句组成的子程序，可用于封装代码以便重新使用，任何代码都可以调用这些函数，从而提高编程的效率。SQL Server 2000内置了丰富的功能强大的函数，还支持用户自定义函数。
1．内置函数
SQL Server 2000提供了如下几类函数。
· 行集函数：返回行集，这些行集可用在Transact-SQL语句中表引用所在的位置。
· 聚合函数：对一组值操作，但返回单一的汇总值。
· 配置函数：返回当前配置信息。
· 游标函数：返回有关游标状态的信息。
· 日期和时间函数：对日期和时间输入值执行操作。
· 数学函数：执行三角、几何和其他数字运算。
· 元数据函数：返回有关数据库和数据库对象的信息。
· 安全函数：返回有关用户和角色的信息。
· 字符串函数：操作char、varchar、nchar、nvarchar、binary和varbinary值。
· 系统函数：对系统级别的各种选项和对象进行操作或报告。
· 系统统计函数：返回系统的统计信息。
· 文本和图像函数：对文本或图像输入值或列执行操作，返回有关这些值的信息。
在这些函数中，行集函数返回行集；聚合函数对一组值操作，但返回单一的汇总值；而其余函数则对单一值进行操作，返回单一值，故也称为标量函数。各类之中具体的函数功能可查看SQL Server的联机丛书。
使用函数时，总是带有圆括号，即便没有参数也是如此。有些参数是可选的，是具有默认值的，比如数据库、计算机名等，若不给定参数值，则默认为当前的数据库、计算机。
2．用户定义函数
除了使用系统内置函数，SQL Server中还允许用户自己定义函数，可使用CREATE FUNCTION语句创建、使用ALTER FUNCTION语句修改、以及使用DROP FUNCTION语句除去用户定义函数。应该注意的是，必须被授予CREATE FUNCTION权限才能创建、修改或除去用户定义函数，而且每个完全合法的用户定义函数名必须唯一。创建一个函数的一般格式如下：
CREATE FUNCTION <函数名> (<参数表>)
RETURNS (<返回数据类型描述>) AS
BEGIN
(<函数主体>)
END
用户也可以在企业管理器中新建用户定义的函数，与新建用户定义数据类型相似，打开要添加用户定义的函数的数据库，右击“用户定义的函数”选项，在弹出的快捷菜单中选择“新建用户定义的函数”命令，在弹出的对话框中的编辑框中输入创建函数的语句即可。