Transact-SQL程序设计
不同的数据库供应商一般都会对SQL语言进行不同程度的扩展，主要是基于两方面的原因：一是数据库供应商开发的系统早于SQL语言标准的制定时间；二是不同的数据库供应商为了达到特殊性能和实现新的功能而对标准的SQL语言进行了扩展。
Transact-SQL 是SQL Server 2000提供的查询语言。使用Transact-SQL编写应用程序可以完成所有的数据库管理工作。任何应用程序，只要目的是向SQL Server 2000的数据库管理系统发出命令以获得数据库管理系统的响应，最终都必须体现为以Transact-SQL语句为表现形式的指令。对用户来说，Transact-SQL是惟一可以和SQL Server 2000的数据库管理系统进行交互的语言。
4.1 Transact-SQL语言概述
尽管SQL Server 2000提供了使用方便的图形化用户界面，但各种功能的实现基础是Transact-SQL语言，只有Transact-SQL语言可以直接和数据库引擎进行交互。Transact-SQL语言是基于商业应用的结构化查询语言，是标准SQL语言的增强版本。
4.1.1 Transact-SQL语言特点
由于Transact-SQL语言直接来源于SQL语言，因此它也具有SQL语言的几个特点。
1. 一体化
Transact-SQL语言集数据定义语言、数据操纵语言、数据控制语言和附加语言元素为一体。其中附加语言元素不是标准SQL语言的内容，但是它增强了用户对数据库操作的灵活性和简便性，从而增强了程序的功能。
2. 两种使用方式，统一的语法结构
两种使用方式，即联机交互式和嵌入高级语言的使用方式。统一的语法结构使Transact-SQL语言可用于所有用户的数据库活动模型，包括系统管理员、数据库管理员、应用程序员、决策支持系统管理人员以及许多其他类型的终端用户。
3. 高度非过程化
Transact-SQL语言一次处理一个记录，对数据提供自动导航；允许用户在高层的数据结构上工作，可操作记录集，而不是对单个记录进行操作；所有的SQL语句接受集合作为输入，返回集合作为输出，并允许一条SQL语句的结果作为另一条SQL语句的输入。另外，Transact-SQL语言不要求用户指定对数据的存放方法，所有的Transact-SQL语句使用查询优化器，用以指定数据以最快速度存取的手段。
4. 类似于人的思维习惯，容易理解和掌握
SQL语言的易学易用性，而Transact-SQL语言是对SQL语言的扩展，因此也是非常容易理解和掌握的。如果对SQL语言比较了解，在学习和掌握Transact-SQL语言及其高级特性时就更游刃有余了。
4.1.2 Transact-SQL附加语言
Transact-SQL附加语言元素不是SQL的标准内容，而是Transact-SQL语言为了编程方便而增加的语言元素。这些语言元素包括变量、运算符、函数、流程控制语句和注释等内容。
1. 变量
变量对于一种语言来说是必不可少的组成部分。Transact-SQL语言允许使用两种变量：一种是用户自己定义的局部变量(Local Variable)，另一种是系统提供的全局变量(Global Variable)。
(1) 局部变量
局部变量使用户自己定义的变量，它的作用范围近在程序内部。通常只能在一个批处理中或存储过程中使用，用来存储从表中查询到的数据，或当作程序执行过程中暂存变量使用。局部变量使用DECLARE语句定义，并且指定变量的数据类型，然后可以使用SET或SELECT语句为变量初始化；局部变量必须以“@”开头，而且必须先声明后使用。其声明格式如下：
DECLARE @变量名变量类型[,@变量名变量类型…]
其中变量类型可以是SQL Server 2000支持的所有数据类型，也可以是用户自定义的数据类型。
局部变量不能使用“变量=变量值”的格式进行初始化，必须使用SELECT或SET语句来设置其初始值。初始化格式如下：
SELECT @局部变量=变量值
SET @局部变量=变量值
比如在pubs数据库中使用名为@find的局部变量检索所有姓以Ring开头的作者信息，代码如下：
USE pubs
DECLARE @find varchar(30)
SET @find = 'Ring%'
SELECT au_lname, au_fname, phone
FROM authors
WHERE au_lname LIKE @find
执行结果：
au_lname au_fname phone
---------------------------------------- -------------------- ------------
Ringer Albert 801 826-0752
Ringer Anne 801 826-0752
注意：如果声明字符型的局部变量，一定要在变量类型中指明其最大长度，否则系统认为其长度为1。
(2) 全局变量
全局变量是SQL Server 2000系统内部使用的变量，起作用范围并不局限于某一程序，而是任何程序均可随时调用。全局变量通常存储一些SQL Server 2000的配置设置值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或者Transact_SQL命令执行后的状态值。引用全局变量时，全局变量的名字前面要有两个标记符“@@”。不能定义与全局变量同名的局部变量。从SQL Server 7.0开始，全局变量就以系统函数的形式使用。全局变量的符号及其功能如表4.1所示。
表4.1 全局变量及其功能
| 全局变量 | 功 能 |
| @@CONNECTIONS | 自SQL Server 2000最近一次启动以来登录或试图登录的次数 |
| @@CPU_BUSY | 自SQL Server 2000最近一次启动以来CPU Server的工作时间 |
| @@CURRSOR_ROWS | 返回在本次连接最新打开的游标中的行数 |
| @@DATEFIRST | 返回SET DATEFIRST参数的当前值 |
| @@DBTS | 数据库的惟一时间标记值 |
| @@ERROR | 系统生成的最后一个错误，若为0则成功 |
| @@FETCH_STATUS | 最近一条FETCH语句的标志 |
| @@IDENTITY | 保存最近一次的插入身份值 |
| @@IDLE | 自CPU服务器最近一次启动以来的累计空闲时间 |
| @@IO_BUSY | 服务器输入输出操作的累计时间 |
| @@LANGID | 当前使用的语言的ID |
| @@LANGUAGE | 当前使用语言的名称 |
| @@LOCK_TIMEOUT | 返回当前锁的超时设置 |
| @@MAX_CONNECTIONS | 同时与SQL Server 2000相连的最大连接数量 |
| @@MAX_PRECISION | 十进制与数据类型的精度级别 |
| @@NESTLEVEL | 当前调用存储过程的嵌套级，范围为0～16 |
| @@OPTIONS | 返回当前SET选项的信息 |
| @@PACK_RECEIVED | 所读的输入包数量 |
| @@PACKET_SENT | 所写的输出包数量 |
| @@PACKET_ERRORS | 读与写数据包的错误数 |
| @@RPOCID | 当前存储过程的ID |
| @@REMSERVER | 返回远程数据库的名称 |
| @@ROWCOUNT | 最近一次查询涉及的行数 |
| @@SERVERNAME | 本地服务器名称 |
| @@SERVICENAME | 当前运行的服务器名称 |
| @@SPID | 当前进程的ID |
| @@TEXTSIZE | 当前最大的文本或图像数据大小 |
续表
| 全局变量 | 功 能 |
| @@TIMETICKS | 每一个独立的计算机报时信号的间隔(ms)数，报时信号为31.25ms或1/32s |
| @@TOTAL_ERRORS | 读写过程中的错误数量 |
| @@TOTAL_READ | 读磁盘次数(不是高速缓存) |
| @@TOTAL_WRITE | 写磁盘次数 |
| @@TRANCOUNT | 当前用户的活动事务处理总数 |
| @@VERSION | 当前SQL Server的版本号 |
2. 流程控制语句
Transact-SQL语言提供了一些可以用于改变语句执行顺序的命令，称为流程控制语句。流程控制语句允许用户更好地组织存储过程中的语句，方便地实现程序的功能。流程控制语句与常见的程序设计语言类似，主要包含以下几种。
(1) IF…ELSE语句
IF <条件表达式>
<命令行或程序块>
[ELSE [条件表达式]
<命令行或程序块>]
其中<条件表达式>可以是各种表达式的组合，但表达式的值必须是“真”或“假”。ELSE子句是可选的。IF…ELSE语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。如果不使用程序块，IF或ELSE只能执行一条命令。IF…ELSE可以嵌套使用，最多可嵌套32级。
(2) BEGIN…END语句
BEGIN
<命令行或程序块>
END
BEGIN…END用来设置一个程序块，该程序块可以被视为一个单元执行。BEGIN…END经常在条件语句中使用，如IF…ELSE语句。如果当IF或ELSE子句为真时，想让程序执行其后的多条语句，这时就要把这多条语句用BEGIN…END括起来使之成为一个语句块。在BEGIN…END语句中可以嵌套另外的BEGIN…END语句来定义另一程序块。
(3) CASE语句
CASE<运算式>
WHEN<运算式>THEN<运算式>
…
WHEN<运算式>THEN<运算式>
[ELSE<运算式>]
END
例如，在pubs数据库中查询每个作者所居住州的全名，可以使用如下代码实现：
SELECT au_fname, au_lname,
CASE state
WHEN 'CA' THEN 'California'
WHEN 'KS' THEN 'Kansas'
WHEN 'TN' THEN 'Tennessee'
WHEN 'OR' THEN 'Oregon'
WHEN 'MI' THEN 'Michigan'
WHEN 'IN' THEN 'Indiana'
WHEN 'MD' THEN 'Maryland'
WHEN 'UT' THEN 'Utah'
END AS StateName
FROM pubs.dbo.authors
ORDER BY au_lname
执行结果：
au_fname au_lname StateName
-------------------- ---------------------------------------- ----------
Abraham Bennet California
Reginald Blotchet-Halls Oregon
Cheryl Carson California
Michel DeFrance Indiana
Innes del Castillo Michigan
Ann Dull California
…
(4) WHILE…CONTINUE…BREAK语句
WHILE<条件表达式>
BEGIN
<命令行或程序块>
[BREAK]
[CONTINUE]
[命令行或程序块]
END
WHILE语句在设置的条件为真时会重复执行命令行或程序块。CONTINUE语句可以让程序跳过CONTINUE语句之后的语句，回到WHILE循环的第一行。BREAK语句则让程序完全跳出循环，结束WHILE循环的执行。WHILE语句也可以嵌套使用。
注意：如果嵌套了两个或多个WHILE循环，内层的BREAK语句将导致退出到下一个外层循环。首先运行内层循环结束之后的所有语句，然后下一个外层循环重新开始执行。
3. 注释
在Transact-SQL语言中可使用两种注释符：行注释和块注释。
行注释符为“--”，这是ANSI标准的注释符，用于单行注释。
块注释符为“/*…*/”，“/*”用于注释文字的开头，“*/”用于注释文字的末尾。块注释符可在程序中标识多行文字为注释。
4.1.3 查询分析器的使用
第2章简单介绍了SQL Server 2000提供的查询分析器工具，这里详细说明一下它的使用方法。
查询分析器使用一个图形用户界面，用以交互地设计和测试 Transact-SQL 语句、批处理和脚本，其提供以下几项功能。
用于输入Transact-SQL 语句的自由格式文本编辑器。
在Transact-SQL 语法中使用不同的颜色，以提高复杂语句的易读性。
使用对象浏览器和对象搜索工具可以轻松查找数据库中的对象和对象结构。
模板可用于加快创建 SQL Server 对象的 Transact-SQL 语句的开发速度。模板是包含创建数据库对象所需的 Transact-SQL 语句基本结构的文件。
有用于分析存储过程的交互式调试工具。
以网格或自由格式文本窗口的形式显示结果。
显示计划信息的图形关系图，用以说明内置在 Transact-SQL 语句执行计划中的逻辑步骤。这使程序员得以确定在性能差的查询中，具体是哪一部分使用了大量资源。之后，程序员可以试着采用不同的方法更改查询，使查询使用的资源减到最小同时仍返回正确的数据。
使用索引优化向导分析 Transact-SQL 语句及其所引用的表，以了解通过添加其他索引是否可以提高查询的性能。
在启动查询分析器并连接到数据库后，用户就可以进行操作了。如图4.1所示，查询分析器主界面有三个主要部分，左边部分为对象浏览器，可以在这里看到所有的数据库及其对象信息；上半部分为SQL命令的输入窗格，可以在这里输入并调试Transact-SQL语句；下半部分为执行结果输出窗格。用户可以在命令输入窗格中输入Transact-SQL应用程序，程序执行后可以在结果输出窗格中看见程序执行的结果或错误信息。用户可以在主菜单中选择查询结果的输出方式，由于这里选择了结果以表格的形式输出，所以有关程序执行的消息可以切换到【消息】选项卡中进行查看。
图4.1 查询分析器的编辑界面
SQL语言是所有关系数据库通用的标准语言，Transact-SQL语言在标准SQL语言的基础上进行了功能上的扩充，由命令语句、基本数据类型、函数和运算符组成。SQL Server 2000的Transact-SQL语言也有一些自己的特色，从而增加了用户对数据库操作的方便性和灵活性。
在SQL Server 2000中，每个变量、参数和表达式都有数据类型。所谓数据类型就是以数据的表现方式和存储方式来划分的数据的种类。SQL Server 2000中提供多种基本数据类型，如表4.2所示。
其中bigint和sql_variant是SQL Server 2000中新增的数据类型。另外， SQL Server 2000还新增了table基本数据类型，该数据类型可用于存储SQL语句的结果集。table数据类型不适用于表中的列，而只能用于Transact-SQL变量和用户定义函数的返回值。
表4.2 SQL Server 2000的基本数据类型
| binary | bigint | bit | char | datetime |
| decimal | float | image | int | money |
| nchar | ntext | nvarchar | numeric | real |
| smalldatetime | smallint | smallmoney | sql_variant | sysname |
| text | timestamp | tinyint | varbinary | varchar |
| uniqueidentifier |
|
|
|
|
4.2.1 整数数据类型
整数型数据包括bigint型、int型、smallint型和tinyint型。
bigint型数据的存储大小为8个字节，共64位。其中63位用于表示数值的大小，1位用于表示符号。bigint型数据可以存储的数值范围是-263～263-1，即 -9 223 372 036 854 775 808 ～9 223 372 036 854 775 807。
int型数据的存储大小为4个字节，共32位。其中31位用于表示数值的大小，1位用于表示符号。int型数据存储的数值范围是-231～231-1，即-2 147 483 648～ 2 147 483 647。
smallint型数据的存储大小为2个字节，共16位。其中15位用于表示数值的大小，1位用于表示符号。smallint型数据存储的数值范围是-215～215-1，即-32 768～ 32 767。
tinyint型数据的存储大小只有1个字节，共8位，全部用于表示数值的大小，由于没有符号位，所以tinyint型的数据只能表示正整数。tinyint型数据存储的数值范围是-27～27-1，即-256～255。
4.2.2 浮点数据类型
浮点数据类型用于存储十进制小数。在SQL Server 2000中浮点数值的数据采用上舍入(Round up)的方式进行存储，也就是说，要舍入的小数部分不论其大小，只要是一个非零的数，就要在该数字的最低有效位上加1，并进行必要的进位。由于浮点数据为近似值，所以并非数据类型范围内的所有数据都能精确地表示。
浮点数据类型包括real型、float型、decimal型和numeric型。
real型数据的存储大小为4个字节，可精确到小数点后第7位数字。这种数据类型的数据存储范围为从-3.40E+38～-1.18E-38，0和1.18E-38～3.40E+38。
float型的数据存储大小为8个字节，可精确到小数点后第15位数字。这种数据类型的数据存储范围为从-1.79E+308～-2.23E-308，0和2.23E+308～1.79E+308。
float型的数据可写成float[(n)]的形式。其中n是1～15之间的整数值，指定float型数据的精度。当n为1～7时，实际上用户定义了一个real型的数据，系统用4个字节存储；当n为8～15时，系统认为它是个float型的数据，用8个字节存储它。这样既增强了数据定义的灵活性，又节省了空间。
decimal数据类型和numeric数据类型的功能完全一样，它们都可以提供小数所需要的实际存储空间，但也有一定的限制，用户可以用2～17个字节来存储数据，取值范围是-1038+1～1038-1。
decimal型数据和numeric型数据的定义格式为decimal[(p,[s])]和numeric[(p,[s])]，其中p表示可供存储的值的总位数(不包括小数点)，默认值为18；s表示小数点后的位数，默认值为0；参数之间的关系是0≤s≤p。例如：decimal(15,5)表示共有15位数，其中整数10位，小数5位。
4.2.3 二进制数据类型
二进制数据类型用于存储二进制数据，包括binary型、varbinary型和image型。
binary型是固定长度的二进制数据类型，其定义形式为binary(n)，其中n表示数据的长度，取值为1～8 000。在使用时应指定binary型数据的大小，默认值为1个字节。binary类型的数据占用n+4个字节的存储空间。
在输入数据时必须在数据前加上字符“0X”作为二进制标识。例如：要输入“abc”则应输入“0Xabc”。若输入的数据位数为奇数，则系统会自动在起始符号“0X”的后面添加一个0。如上述输入“0Xabc”后，系统会自动变为“0X0abc”。
varbinary型是可变长度的二进制数据类型，其定义形式为varbinary(n)，其中n表示数据的长度，取值为1～8 000。如果输入的数据长度超出n的范围，则系统会自动截掉超出部分。
varbinary型具有变动长度的特性，因为varbinary型数据的存储长度为实际数值长度+4个字节。当binnary型数据允许null值时，将被视为varbinary型的数据。
一般情况下，由于binary型的数据长度固定，因此它比varbinary型的数据处理速度快。
image型的数据也是可变长度的二进制数据，其最大长度为231-1(2 147 483 647)个字节。
4.2.4 逻辑数据类型
逻辑数据类型只有一种bit型。bit数据类型只占用1个字节的存储空间，其值为0和1。只要输入的值为非0，系统都会当作1处理。另外，bit型不能定义为null值。
4.2.5 字符数据类型
字符数据类型是使用最多的数据类型，它可以用来存储各种字母、数字符号、特殊符号等。一般情况下，使用字符类型数据时，须在数据的前后加上单引号或双引号。字符数据类型包括char型、nchar型、varchar型和nvarchar型。
char型是固定长度的非Unicode字符数据类型，在存储时每个字符和符号占用一个字节的存储空间。其定义形式为char[(n)]，其中n表示所有字符所占的存储空间，取值为1～8 000，即可容纳8 000个ANSI字符，默认值为1。若输入的数据字符数小于n定义的范围，则系统自动在其后添加空格来填满设定好的空间；若输入的数据字符数超过n定义的范围，则系统自动截掉超出部分。
nchar型是固定长度的Unicode字符数据类型，由于Unicode标准规定在存储时每个字符和符号占用2个字节的存储空间，因此nchar型的数据比char型数据多占用一倍的存储空间。其定义形式为nchar[(n)]，其中n表示所有字符所占的存储空间，取值为1～4 000，即可容纳4 000个Unicode字符，默认值为1。
使用Unicode标准字符集的好处是由于它使用两个字节作存储单位，使得一个存储单位的容量大大增加，这样就可以将全世界的语言文字都囊括在内。当用户在一个数据列中同时输入不同语言的文字符号时，系统不会出现编码冲突。
varchar型是可变长度的非Unicode字符数据类型。其定义形式为varchar[(n)]。它与char型类似，n的取值范围是1～8 000。由于varchar型具有可变长度的特性，所以varchar型数据的存储长度为实际数值的长度。如果输入数据的字符数小于n定义的长度，系统也不会像char型那样在数据后面用空格填充；但是如果输入的数据长度大于n定义的长度，系统会自动截掉超出部分。
一般情况下，由于char型的数据长度固定，因此它比varchar型数据的处理速度快。
nvarchar型是可变长度的Unicode字符数据类型，其定义形式为nvarchar[(n)]。由于它采用了Unicode标准字符集，因此n的取值范围是从1～4 000。nvarchar型的其他特性与varchar类型相似。
4.2.6 文本和图形数据类型
文本和图形数据类型是用于存储大量的非Unicode和Unicode字符以及二进制数据的固定长度和可变长度数据类型，包括text型、ntext型和image型。
text型是用于存储大量非Unicode文本数据的可变长度数据类型，其容量理论上为231-1(2 147 483 647)个字节。在实际应用时需要视硬盘的存储空间而定。
在SQL Server 2000以前的版本中，数据库中一个text对象存储的实际上是一个指针，它指向一个以8KB为单位的数据页。这些数据页是动态增加并被逻辑连接起来的。在SQL Server 2000中，则将text和image型的数据直接存放到表的数据行中，而不是存放到不同的数据页中。这样就减少了用于存储text和image类型的空间，并相应减少了磁盘处理这类数据的I/O数量。
ntext型是用于存储大量Unicode文本数据的可变长度数据类型，其理论容量为230-1(1 073 741 823)个字节。ntext型的其他用法与text型基本一样。
image型是用于存储大量二进制数据的可变长度数据类型，其理论容量为231-1 (2 147 483 647)个字节。Image型数据的存储模式与text型数据相同，通常用来存储图形等OLE对象。在输入数据时，与输入二进制数据一样，必须在数据前加上起始符号“0X”作为二进制标识。
4.2.7 日期和时间数据类型
日期和时间数据类型代表日期和一天内的时间，包括datetime型和smalldatetime型。
datetime型是用于存储日期和时间的结合体的数据类型。它可以存储从公元1753年1月1日零时起到公元9999年12月31日23时59分59秒之间的所有日期和时间，其精确度可达3%秒。
datetime型数据所占用的存储空间为8个字节，其中前4个字节用于存储1900年1月1日以前或以后的天数，数值分正负，正数表示在此日期之后的日期，负数表示在此日期之前的日期；后4个字节用于存储从此日零时起所指定的时间经过的毫秒数。如果在输入时省略了时间部分，则系统将默认为12:00:00:000AM；如果省略了日期部分，系统将默认为1900年1月1日。
smalldatetime型与datetime型相似，但其存储的日期时间范围较小，从1900年1月1日到2079年6月6日。它的精度也较低，只能精确到分钟级，其分钟个位上的值是根据秒数并以30秒为界四舍五入得到的。
Smalldatetime型数据所占用的存储空间为4个字节，其中前两个字节存储从基础日期1900年1月1日以来的天数，后两个字节存储此日零时起所指定的时间经过的分钟数。
4.2.8 货币数据类型
货币数据类型用于存储货币或现金值，包括money型和smallmoney型。在使用货币数据类型时，应在数据前加上货币符号，以便系统辨识其为哪国的货币，如果不加货币符号，则系统默认为“￥”。
money型是一个有4位小数的decimal值，其取值从-263(-9 223 372 036 854 775 808) ～263-1(+9 223 372 036 854 775 807)，精确到货币单位的千分之十。存储大小为8个字节。
smallmoney型货币数据值介于-2 147 483 648～+2 147 483 647之间，精确到货币单位的千分之十。存储大小为 4 个字节。
4.2.9 特定数据类型
SQL Server 2000中包含了一些用于数据存储的特殊数据类型，包括timestamp型和 uniqueidentifier型。
timestamp数据类型提供数据库范围内的惟一值，它相当于binary(8)或varbinary(8)，但当它所定义的列在进行更新或插入数据行操作时，此列的值会自动更新。每个数据库表中只能有一个timestamp型数据列。如果表中的列名为“timestamp”，则该列的类型将被自动定义为timestamp型。
uniqueidentifier数据类型用于存储一个16位的二进制数据，此数据称为全局惟一标识符(Globally Unique Identifier，GUID)。此数据由SQL Server 2000的NEWID()函数产生，在全球各地的计算机经由此函数产生的数字不会相同。
4.2.10 用户自定义数据类型
sysname数据类型是系统提供给用户的，便于用户自定义的数据类型。该数据类型被定义为NVARCHAR(128)，即它可以存储128个Unicode字符或256个一般字符。
4.2.11 新增数据类型
前面讲到过SQL Server 2000新增了3种数据类型：bigint型、sql_variant型和table型。其中bigint型已经在整数类型中介绍过了。
sql_variant型是一种存储SQL Server支持的各种数据类型(text、ntext、timestamp 和sql_variant除外)值的数据类型。此数据类型大大方便了SQL Server的开发工作。
table型用于存储对表或视图处理后的结果集。table数据类型不适用于表中的列，而只能用于Transact-SQL变量和用户定义函数的返回值。这一类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便快捷。
运算符是一种符号，用来指定要在一个或多个表达式中指定的操作。SQL Server 2000中使用如下几种运算符：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符、字符串串联运算符和一元运算符。
1. 算术运算符
算术运算符用来在两个表达式上执行数学运算，这两个表达式可以是任意两个数字数据类型的表达式。算术运算符包括+(加)、-(减)、*(乘)、/(除)、%(模)五个。
在Transact-SQL中，“+”包含了三个方面的意义：
表示正号，即在数值前添加“+”号表示该数值是一个正数。
表示算术运算的加号，能将数值类型的两个数据相加。
连接两个字符型或binary型的数据，这时的“+”号叫做字符串串联运算符。
2. 赋值运算符
Transact-SQL 有一个赋值运算符，即等号(=)。
例如，下面的代码创建了@MyCounter变量。然后赋值运算符将@MyCounter设置成一个由表达式返回的值。
DECLARE @MyCounter INT
SET @MyCounter = 1
3. 位运算符
位运算符在两个表达式之间执行位操作，这两个表达式可以是任意两个整型数据类型的表达式。位运算符的符号及其定义如表4.3所示。
表4.3 位运算符
| 运 算 符 | 含 义 |
| & (按位AND) | 按位与(两个操作数) |
| | (按位OR) | 按位或(两个操作数) |
| ^ (按位互斥OR) | 按位异或(两个操作数) |
| ~ (按位NOT) | 按位取反(一个操作数) |
位运算符的操作数可以是整型或二进制字符串数据类型中的任何数据类型(但image 数据类型除外)，此外，两个操作数不能同时是二进制字符串数据类型中的某种数据类型。
4. 比较运算符
比较运算符用来测试两个表达式是否相同。除了 text、ntext 或 image 数据类型的表达式外，比较运算符可以用于所有的表达式。比较运算符的符号及其含义如表4.4所示。
表4.4 比较运算符
| 运 算 符 | 含 义 |
| = | 等于 |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| <> | 不等于 |
| != | 不等于(非 SQL-92 标准) |
| !< | 不小于(非 SQL-92 标准) |
| !> | 不大于(非 SQL-92 标准) |
比较运算符的结果是布尔数据类型，它有三种值：TRUE、FALSE和NULL。那些返回布尔数据类型的表达式被称为布尔表达式。
和其他SQL Server数据类型不同，不能将布尔数据类型指定为表列或变量的数据类型，也不能在结果集中返回布尔数据类型。
当SET ANSI_NULLS为ON时，带有一个或两个NULL表达式的运算符返回 NULL。当SET ANSI_NULLS为OFF时，上述规则同样适用，只不过如果两个表达式都为NULL，那么等号运算符返回TRUE。例如，如果SET ANSI_NULLS是OFF，那么NULL=NULL就返回TRUE。
在WHERE子句中使用带有布尔数据类型的表达式，可以筛选出符合搜索条件的行，也可以在流控制语言语句(例如 IF 和 WHILE)中使用这种表达式。
5. 逻辑运算符
逻辑运算符用来对某个条件进行测试，以获得其真实情况。逻辑运算符和比较运算符一样，返回带有TRUE或FALSE值的布尔数据类型。逻辑运算符的符号及其含义如表4.5所示。
表4.5 逻辑运算符
| 运 算 符 | 含 义 |
| ALL | 如果一系列的比较都为 TRUE，那么就为 TRUE |
| AND | 如果两个布尔表达式都为 TRUE，那么就为 TRUE |
| ANY | 如果一系列的比较中任何一个为 TRUE，那么就为 TRUE |
| BETWEEN | 如果操作数在某个范围之内，那么就为 TRUE |
| EXISTS | 如果子查询包含一些行，那么就为 TRUE |
| IN | 如果操作数等于表达式列表中的一个，那么就为 TRUE |
| LIKE | 如果操作数与一种模式相匹配，那么就为 TRUE |
| NOT | 对任何其他布尔运算符的值取反 |
| OR | 如果两个布尔表达式中的一个为 TRUE，那么就为 TRUE |
| SOME | 如果在一系列比较中，有些为 TRUE，那么就为 TRUE |
6. 一元运算符
一元运算符只对一个表达式执行操作，这个表达式可以是数字数据类型中的任何一种数据类型，如表4.6所示。
表4.6 一元运算符
| 运 算 符 | 含 义 |
| + (正) | 数值为正 |
| - (负) | 数值为负 |
| ~ (按位 NOT) | 返回数字的补数 |
数据定义语言(DDL)用来定义数据的结构，如创建、修改或者删除数据库对象，常用的数据定义语言有CREATE、ALTER、DROP等。
4.4.1 创建表
在创建表时要遵循严格的语法定义。在Transact-SQL语言中，必须满足以下规定：
每个表有一个名称，称为表名或关系名。表名必须以字母开头，最大长度为30个字符。
一张表包含若干列，列名惟一，列名也成为属性名。
同一列的数据必须要有相同的数据类型。
表中的每一列数值必须为一个不可分割的数据项。
表中的一行称为一条记录。
创建一张新表可以使用CREATE TABLE命令，其格式如下：
CREATE TABLE
[ database_name.[ owner ] .| owner.] table_name
( { < column_definition >| column_name AS computed_column_expression | < table_constraint > } [ ,...n ] )
[ ON { filegroup | DEFAULT } ] [ TEXTIMAGE_ON { filegroup | DEFAULT } ]
< column_definition > ::= { column_name data_type } [ [ DEFAULT constant_expression ]| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]] [ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]
各参数说明如下：
database_name指定创建新表的数据库名称。该名称必须是现有数据库的名称。如果不指定数据库，database_name 默认为当前数据库。而且连接SQL Server的用户名在指定的数据库中有建立表格的权限时，建表操作才能顺利执行。
owner指定新表的所有者，如果不指定，系统认为建表人就是表的拥有者。只有管理员或数据库拥有者才能将表的拥有者指定为其他人。owner必须是database_name所指定的数据库中的已经存在的用户名，owner 默认为与database_name所指定的数据库中的当前连接相关联的用户名。
table_name是新表的名称。表名必须符合标识符规则。数据库中的owner.table_ name组合必须惟一并且最多可包含128个字符。
column_name是表中的列名。列名必须符合标识符规则，在表内必须惟一，并且必须指定。以timestamp数据类型创建的列可以省略列名，默认为 timestamp。
computed_column_expression指定一个表达式，用于定义计算列的产生规律。计算列是物理上并不存储在表中的虚拟列。计算列由同一表中的其他列通过表达式计算得到。例如，计算列可以这样定义：cost AS price * qty。表达式可以是非计算列的列名、常量、函数、变量，也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能为子查询。
计算列可用于选择列表、WHERE 子句、ORDER BY 子句或任何其他可使用常规表达式的位置，但下列情况除外：
计算列不能用作 DEFAULT 或 FOREIGN KEY 约束定义，也不能与 NOT NULL 约束定义一起使用。但是，如果计算列由具有确定性的表达式定义，并且索引列中允许计算结果的数据类型，则可将该列用作索引中的键列，或用作 PRIMARY KEY 或 UNIQUE 约束的一部分。
计算列不能作为 INSERT 或 UPDATE 语句的作用对象。
ON {filegroup | DEFAULT}指定存储表的文件组。指定的文件组必须存在于数据库中。如果选择DEFAULT，或者忽略该参数，则表存储在默认文件组中。
TEXTIMAGE_ON是表示text、ntext和image列存储在指定文件组中的关键字。如果表中没有text、ntext或image列，则不能使用TEXTIMAGE ON。如果没有指定 TEXTIMAGE_ON，则text、ntext和image列将与表存储在同一文件组中。
data_type指定列的数据类型。可以是系统数据类型或用户自定义数据类型。
DEFAULT指定某列的默认值。如果某列定义了默认值，在输入数据时，如果不为该列输入专门的数据，则该列的值取默认值。
IDENTITY(seed,increment)用于指定某列为标识列。seed指定标识列的初始值，increment指定递增量。如果某列被指定为标识列，则在该表中所有记录的该列的值都是惟一的。该列的值第一行取seed参数的指定值，以后每增加一行，该列的值取前一行的值加上increment的值。
NOT FOR REPLICATION指定列的IDENTITY属性在把从其他表中复制的数据插入到表中时不发生作用。
ROWGUIDCOL表示新列是行的全局惟一标识符列。对于每个表只能指派一个 uniqueidentifier 列作为ROWGUIDCOL列。ROWGUIDCOL属性只能指派给uniqueidentifier 列。ROWGUIDCOL属性不会使列具有惟一性，也不会自动生成一个新的数值给插入的行。需要在INSERT语句中使用NEWID()函数或指定列的默认值为NEWID()函数。
另外，在创建表时，还要注意以下几点：
一个表至少有一列，最多不超过1 024列。
每个数据库中最多可以创建200万个表。
表在存储时使用的计量单位是盘区(Extent)。一个盘区分为8个数据项，每页8KB字节。在创建表时，会分配给它一个初始值为一个盘区的存储空间。当增加表的存储空间时，以盘区为单位增加。
例如下面的代码将建立一个学生表student。
CREATE TABLE student
(
ID_Card varchar(18) not null,
Sname varchar(10) not null,
Ssex char(2) not null,
Sage int not null,
Sschool_number char(6),
CONSTRAINT stud_ID_card primary key(ID_Card)
)
在创建表的时候通常要定义一些约束，例如学生的身份证号是惟一的，在表中应该作为主键值，学生的年龄不超过35岁等等。在SQL Server中约束可以分为列约束(Column Constraint)和表约束(Tbale Constraint)。列约束作为列定义的一部分制作用于此列本身；表约束作为表定义的一部分，可以作用于多个列。
表约束中经常用到的是主键约束，它能惟一地指定一行记录。每个表中只能有一列被指定为主键，且被指定的主键列不能允许空值属性。
下面的代码建立一个贷款单表，其中贷款金额不得超过10万元。
CREATE TABLE loan
(
loan_number char(6) not null,
amount money not null,
constraint pk_loan_num primary key(loan_number),
constraint chk_amount check(amount<=100000)
)
4.4.2 删除表
删除表指的是删除表定义及该表的所有数据、索引、触发器、约束和权限规范。语法格式如下：
DROP TABLE table_name
其中table_name就是要删除的表名。例如要删除前面建立的loan表，代码如下：
DROP TABLE loan
表的所有者可以除去任何数据库内的表。除去表时，表上的规则或默认值将解除绑定，任何与表关联的约束或触发器将自动除去。如果重新创建表，必须重新绑定适当的规则和默认值，重新创建任何触发器并添加必要的约束。在系统表上不能使用 DROP TABLE 语句。
4.4.3 修改表
Alter Table 语句可以完成对现有表的修改。可以更改、添加、除去列和约束，或者启用或禁用约束和触发器。语法格式如下：
ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
]
| ADD
{ [ < column_definition > ]
| column_name AS computed_column_expression
} [ ,...n ]
| [ WITH CHECK | WITH NOCHECK ] ADD
{ < table_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column } [ ,...n ]
| { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
}
通常表的结构可以用CREATE TABLE语句一次创建，但是当发现表的定义有不符合需要的情况或者是实际的业务需要建立新的约束，此时可以用Alter Table 来调整表结构。例如我们给前面建立的student学生表添加一个民族字段，代码如下：
ALTER TABLE student ADD Smingzu char(10)
4.4.4 创建和管理视图
对于视图的管理包括创建、修改和删除视图。
1. 定义视图
SQL语言用CREATE VIEW命令建立视图，其常用语法如下：
CREATE VIEW <视图名>[(<列名>[,<列名>]...)]
AS <子查询>
[WITH CHECK OPTION];
其中子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。
WITH CHECK OPTION表示对视图进行UPDATE、 INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。
如果CREATE VIEW语句仅指定了视图名，省略了组成视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。
2. 修改视图定义
要改变一个已经创建的视图的定义，用ALETER VIEW 语句，其常用语法如下：
CREATE VIEW <视图名>[(<列名>[,<列名>]...)]
AS <子查询>
[WITH CHECK OPTION];
3. 删除视图
用DROP VIEW语句从当前数据库中删除视图。删除视图时，将从 sysobjects、syscolumns、syscomments、sysdepends 和 sysprotects 系统表中删除视图的定义及其他有关视图的信息。其常用语法如下：
DROP TABLE table_name
4.4.5 创建和管理存储过程
对存储过程的管理包括创建、修改和删除存储过程。
1. 创建存储过程
SQL语言用CREATE PROCEDURE命令建立视图，其常用语法如下：
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
number是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起删除。例如名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将删除整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。
@parameter用于定义存储过程的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值)。存储过程最多可以有2 100个参数。使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其他过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。
sql_statement就是要建立的存储过程的主体，其中可以包含的任意数目和类型的 Transact-SQL 语句。
2. 修改存储过程
要改变一个已经创建的存储过程，用ALETER PROCEDURE语句，其常用语法如下：
ALTER PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION
| RECOMPILE , ENCRYPTION
}
]
[ FOR REPLICATION ]
AS
sql_statement [ ...n ]
3. 删除视图
用DROP PROCEDURE从当前数据库中删除存储过程。其常用语法如下：
DROP PROCEDURE procedure _name
建立了数据库和表后，就需要对这些表进行操作了，一个信息系统其实就是对信息数据进行增(加)、删(除)、(修)改和查(询)的操作。
4.5.1 向表中插入记录
INSERT语句用来向表中增加数据，可以一次追加一条数据，也可以从另外的表或查询中一次追加多条数据，其常用语法如下：
INSERT [ INTO]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
{ [ ( column_list ) ]
{ VALUES
( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement
}
}
例如，向student表中插入数据，代码如下：
INSERT INTO student(ID_Card,Sname,Ssex,Sage,Sschool_number)
VALUES('11015019821228003','孙晓明','男',20,'C_20')
如果不指定字段的列表，那么就必须对表中出现的字段一一赋值，或者使用字段的默认值。所以上面的语句也可以写成：
INSERT INTO student
VALUES('11015019821228003','孙晓明','男',20,'C_20')
当有大量数据需要插入到表中时，可以使用SELECT语句实现大量数据的插入。例如, 新建一个student2表，结构和student表一致并插入所有student表中的记录(已经向student表中输入了数据)，代码如下：
INSERT student2 SELECT * FROM student
4.5.2 从表中删除记录
当不再使用表中的记录时，可以使用DELETE语句将其删除。使用DELETE语句可以一次删除一条或多条记录，而且可以使用WHERE子句指定删除条件。其语法格式如下：
DELETE
[ FROM ] { table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
[ FROM { < table_source > } [ ,...n ] ]
[ WHERE
{ < search_condition >
}]
FROM是可选的关键字，可用在DELETE关键字与目标table_name、view_name或rowset_function_limited之间。
table_name是要从其中删除行的表的名称。
view_name是视图名称。在视图的FROM子句中，view_name引用的视图必须可更新且正确引用一个基表。
例如从student表中删除年龄为25的学生记录，代码如下：
DELETE FROM Student WHERE Sage=25
如果用户只想清除某个表中的所有数据，但不删除表结构，可以使用TRUNCATE TABLE语句。例如要删除student表中所有数据，代码如下：
TRUNCATE TABLE student
也可以用如下代码：
delete from student
4.5.3 修改和更新记录
可以使用UPDATE语句修改和更新表中已经存在的数据。UPDATE语句既可以一次修改一条记录，也可以一次修改多条记录，甚至可以一次修改表中的全部数据行。
在UPDATE语句中使用WHERE子句可以指定要修改的行，使用SET子句给出新的数据。其语法格式如下：
UPDATE
{table_name WITH ( < table_hint_limited > [ ...n ] )}
SET{ column_name = { expression | DEFAULT | NULL }
WHERE < search_condition >
table_name是需要更新的表的名称。如果该表不在当前服务器或数据库中，或不为当前用户所有，这个名称可用链接服务器、数据库和所有者名称来限定。
WITH ( < table_hint_limited > [ ...n ] )指定目标表所允许的一个或多个表提示。需要有WITH关键字和圆括号。
SET指定要更新的列或变量名称的列表。
column_name含有要更改数据的列的名称。column_name必须驻留于UPDATE子句中所指定的表或视图中。标识列不能进行更新。
Expression是变量、字面值、表达式或加上括号的返回单个值的子SELECT语句。expression返回的值将替换column_name中的现有值。
DEFAULT指定使用对列定义的默认值替换列中的现有值。如果该列没有默认值并且定义为允许空值，这也可用来将列更改为NULL。
例如把身份证号为110105197405060012的学生的贷款金额修改为20 000，代码如下：
UPDATE Loan SET amount=20000
WHERE Loan_number=
(SELECT Loan_number FROM Borrower
WHERE ID_Card='110105197405060012')
4.5.4 按条件查询数据
可以使用SELECT语句来进行数据的查询。SELECT语句只有三个关键字：SELECT、FROM和WHERE。但是SELECT语句中有很多子句，能够完成非常复杂的查询功能。SELECT语句常用的语法如下：
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
因为SELECT语句应用情况非常的复杂，下面以多个实例来演示实际中的应用，实例采用SQL Server 2000自带的pubs数据库。
1. 使用SELECT语句查询所有行和列
从pubs数据库内的authors表中返回所有行(没有指定WHERE子句)和所有列(使用*)，如图4.2所示。
2. 使用SELECT语句查询指定列
从pubs数据库内的authors表中返回所有行(没有指定WHERE子句)和指定列的一个子集(au_lname、au_fname、phone、city、state)。另外，还改变了列标题(列别名)。如图4.3所示。
3. 使用DISTINCT语句防止查询重复
使用DISTINCT语句防止检索重复的作者ID号，如图4.4所示。
图4.2 查询所有的数据
图4.3 查询指定列
图4.4 DISTINCT防止检索重复
4. 带查询条件的查询
查找价格超过$20的所有类型书籍的价格和预付款，如图4.5所示。
图4.5 带查询条件的查询
5. 使用通配符
通配符一般是通过LIKE使用的。SQL Server 2000中支持四种通配符，如表4.7所示。
表4.7 通配符及其功能
| 运 算 符 | 含 义 |
| % | 代表零个或多个任意字符 |
| _ | 代表一个任意字符 |
| [ ] | 指定范围内的任意单个字符 |
| [^] | 不在指定范围内的任意单个字符 |
例如，“AB%”表示以AB开始的任意字符串；“_cd”表示以cd结尾的三个字符的字符串；“[ef]%”表示以e或f开始的任意字符串；“[s-v]ing”表示开始是s到v，结尾是ing，长度为四个字符的字符串；“m[^c]%”表示以m开始且第二个字符不是c的任意字符串。
例如，使用通配符查询书的类型末尾有“cook”的书的信息，如图4.6所示。
6. 使用子查询
SELECT语句可以嵌套在其他许多语句中，这些嵌套的语句称为子查询。子查询是一个SELECT查询，它返回单个值且嵌套在SELECT、INSERT、UPDATE、DELETE语句或其他子查询中。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。当一个查询依赖于另外一个查询结果时，就可以使用子查询。
例如使用子查询检索书名为商业书籍的每个出版商名称，并且titles表和publishers表之间的出版商ID号要相匹配，如图4.7所示。
图4.6 带通配符的查询
图4.7 使用子查询
当然，上面的功能还可以通过不同的代码来实现，分别如图4.8和图4.9所示，其原理都是一样的。
图4.8 子查询的其他实现方法
图4.9 子查询的其他实现方法
相关子查询还可以用于外部查询的HAVING子句。例如要查找那些预付款最大金额是组平均值两倍以上的书籍类型，如图4.10所示。
图4.10 带HAVING子句的查询
4.5.5 数据连接多表查询
在实际的数据库操作中，往往需要同时从两个或两个以上的表中查询相关数据，连接就是满足这些需求的技术。使用连接技术查询时，其简单语法格式如下：
SELECT table_name.column_name,table_name.column_name,…
FROM table_name[join_type] JOIN table_name
ON search_conditions
WHERE search_conditions
在进行表之间的连接时，可以使用三种连接关键字。
INNER JOIN关键字，表示内连接，结果集中仅包含满足条件的行。
CROSS JOIN关键字，表示交叉连接，结果集中包含两个表中所有行的组合。
OUTER JOIN关键字，表示外连接，结果集中既包含那些满足条件的行，还包含某些不满足条件的数据。
例如，要查询在同一个城市的作者和出版商的信息，如图4.11所示。
图4.11 INNER JOIN连接查询
INNER JOIN连接查询可以采用另外一种语法实现，如图4.12所示，查询结果是一致的。
图4.12 INNER JOIN连接查询的另外一种写法
内连接是保证两个表中所有的行都要满足连接条件，而外连接返回FROM子句中提到的至少一个表的所有行，只要这些行符合任何WHERE或HAVING搜索条件。外连接包括以下三种形式：
LEFT OUTER JOIN，左外连接，包括了左表中的全部行。
RIGHT OUTER JOIN，右外连接，包括了右表中的全部行。
FULL OUTER JOIN，完整外连接，包括了左表和右表中所有不满足条件的行。
将authors作为左表，publishers作为右表，两表做左外连接，左表authors中的全部数据都显示出来了，就是查询结果的记录数和authors表的记录数一致。如果publishers表中有数据不满足连接条件的行，那么对应右表的数据是空值，如图4.13所示。
图4.13 LEFT OUTER JOIN左外连接查询
外连接查询的OUTER关键字可以省略，如图4.14所示，效果是一致的。
图4.14 省略了OUTER关键字
4.5.6 对查询结果排序
排序技术就是使用ORDER BY子句排列查询结果的顺序。ORDER BY子句按查询结果中的一列或多列对查询结果进行排序，用作排序依据的列总长度可达8 060。其语法格式如下：
SELECT column_name,column_name,…
FROM table_name
ORDER BY column_name[ASC|DESC],…
在ORDER BY子句中，既可以使用列名，也可以使用相对列号。排序可以是升序的 (ASC)，也可以是降序的 (DESC)。如果没有特别指定，默认为ASC。
例如，在titles表中查询pub_id、type、title_id三列的数据，并将查询结果按pub_id升序排列，如图4.15所示。
图4.15 将查询结果排序
4.5.7 数据统计分组查询
在实际应用中，往往需要对表中的原始数据做一些数学处理。统计函数就是满足这些需求的最好工具。常用的统计函数如AVG、SUM、COUNT、MAX和MIN等，都必须用在SELECT语句中，且伴随GROUP BY关键字一起使用。
例如，按照图书统计截止到现在的销售情况，并按照销售数量由大到小排序，如图4.16所示。
需要注意得是，如果要在查询条件里使用统计函数，则需要使用HAVING关键字代替WHERE关键字，例如，在上面的查询中如果只想看总销售数量超过30册的图书，则代码与结果如图4.17所示。
图4.16 按照图书分组统计销售
图4.17 在查询条件中使用统计函数
数据库的游标是类似于C语言指针一样的语言结构。通常情况下，数据库执行的大多数SQL命令都是同时处理集合内部的所有数据。但是，有时侯用户也需要对这些数据集合中的每一行进行操作。在没有游标的情况下，这种工作不得不放到数据库前端，用高级语言来实现。这将导致不必要的数据传输，从而延长执行的时间。通过使用游标，可以在服务器端有效地解决这个问题。游标提供了一种在服务器内部处理结果集的方法，它可以识别一个数据集合内部指定的工作行，从而可以有选择地按行采取操作。
游标的功能比较复杂，要灵活使用游标需要花费较长的时间练习和积累经验。本节只介绍使用游标最基本和最常用的方法。如果想进一步地学习，可以参考数据库的相关 书籍。
4.6.1 声明游标
在使用游标之前首先要声明游标，Transact-SQL在ANSI 92 SQL 的基础上对游标的功能作了一定的扩充，因此产生了与ANSI 92 SQL稍有不同的游标声明方法。
声明游标的语法如下：
DECLARE 游标名 [INSENSITIVE] [SCROLL] CURSOR
FOR SELECT 语句
[FOR READ ONLY | UPDATE [OF 列名1，列名2，列名3 …]
游标名为声明的游标所取的名字，声明游标必须遵守Transact-SQL对标识符的命名规则。
使用INSENSITIVE定义的游标，把提取出来的数据放入一个在tempdb数据库创建的临时表里。任何通过这个游标进行的操作，都在这个临时表里进行。所以所有对基本表的改动都不会在用游标进行的操作中体现出来。如果忽略了INSENSITIVE关键字，那么用户对基本表所做的任何操作，都将在游标中得到体现。
使用SCROLL关键字定义的游标，具有包括如下所示的所有取数功能：
FIRST 取第一行数据。
LAST 取最后一行数据。
PRIOR 取前一行数据。
NEXT 取后一行数据。
RELATIVE 按相对位置取数据。
ABSOLUTE 按绝对位置取数据。
如果没有在声明时使用SCROLL关键字，那么所声明的游标只具有默认的NEXT 功能。
READ ONLY声明只读光标，不允许通过只读光标进行数据的更新。
UPDATE [OF 列名1，列名2，列名3 …]定义在这个游标里可以更新的列。如果定义了[OF 列名1，列名2，列名3 …]，那么只有列在表中的列可以被更新；如果没有定义[OF 列名1，列名2，列名3 …]，那么游标里的所有列都可以被更新。
下面是两个声明游标的例子：
--这个游标可以在整个authors表中所有的数据行上进行操作
DECLARE authors_cursor CURSOR
FOR SELECT *
FROM authors
--这个例子声明了一个只读游标，并对游标可以处理的结果集进行了筛选和排序
DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id
FOR READONLY
4.6.2 打开游标
在可以使用游标之前，必须首先打开游标。打开游标的语法如下：
OPEN cursor_name
当执行打开游标的语句时，服务器执行声明游标时使用的SELECT语句，如果使用了INSENSITIVE关键字，则服务器会在 tempdb中建立一张临时表，以存放游标将要操作的数据集的副本。
在打开游标后，可以使用@@CURSOR_ROWS全局变量来返回当前声明的游标可以操作数据行的数量。
4.6.3 关闭游标
在打开游标以后，SQL Server服务器会专门为游标开辟一定的内存空间存放游标操作的数据结果集，同时游标的使用也会根据具体情况对某些数据进行封锁。所以，在不使用游标的时候，一定要关闭游标，以通知服务器释放游标所占用的资源。
关闭游标的语法如下：
CLOSE cursor_name
关闭游标以后，可以再次打开游标，在一个批处理中，也可以多次打开和关闭游标。
4.6.4 释放游标
游标结构本身也会占用一定的计算机资源，所以在使用完游标后，为了回收被游标占用的资源，应该将游标释放。
释放游标的语法是：
DEALLOCATE cursor_name
当释放完游标以后，如果要重新使用这个游标必须重新执行声明游标的语句。
4.6.5 使用游标取数
在打开游标以后，就可以打开游标提取数据了。使用游标提取某一行的数据应该使用下面的语法：
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE {n | @nvar}
| RELATIVE {n | @nvar}
]
FROM
]
cursor_name
[INTO @variable[,...n]]
在这个语法里，n和@nvar表示游标相对与作为基准的数据行所偏离的位置。
在使用INTO子句对变量赋值时，变量的数量和相应的数据类型必须和声明游标时使用的SELECT语句中引用到的数据列的数目、排列顺序和数据类型完全保持一致，否则服务器会提示错误。
事实上，使用游标取数的操作通常与WHILE循环紧密结合在一起。下面的代码演示了游标的使用方法：
USE pubs
GO
/*声明游标*/
DECLARE authors_cur CURSOR FOR
SELECT au_lname,au_fname FROM authors
WHERE state = "CA"
ORDER BY au_lname
/*打开一个游标*/
OPEN authors_cur
/*执行第一次取数操作*/
FETCH NEXT FROM authors_cur
/*检查@@FETCH_STATUS以确定是否还可以继续取数*/
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM authors_cur
END
/*关闭游标*/
CLOSE authors_cur
当游标移动到最后一行数据的时候，继续执行取下一行数据的操作，将返回错误信息，但这个信息只在@@FETCH_STATUS中体现，同时返回空白的数据，根据判断条件，程序现在就终止循环。
下面的例子定义了一个滚动游标，从而可以实现更灵活的数据提取。
USE pubs
GO
/*首先执行一遍查询语句以提供滚动游标操作成功与否的对比*/
SELECT au_lname, au_fname
FROM authors
WHERE state = "CA"
ORDER BY au_lname, au_fname
-- 定义滚动游标
DECLARE authors_cur SCROLL CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE state = "CA"
ORDER BY au_lname, au_fname
/*打开游标*/
OPEN authors_cur
/*提取数据集中的最后一行*/
FETCH LAST FROM authors_cur
/*提取当前游标所在行的上一行*/
FETCH PRIOR FROM authors_cur
/* 提取数据集中的第4行*/
FETCH ABSOLUTE 4 FROM authors_cur
/*提取当前行的前两行*/
FETCH RELATIVE -2 FROM authors_cur
/*关闭游标*/
CLOSE authors_cur
/*释放游标*/
DEALLOCATE authors_cur
GO
程序执行结果如下：
au_lname au_fname
---------------------------------------- --------------------
Bennet Abraham
Carson Cheryl
Dull Ann
…
Yokomoto Akiko
(所影响的行数为16行)
au_lname au_fname
---------------------------------------- --------------------
Yokomoto Akiko
(所影响的行数为1行)
…
(所影响的行数为1行)
下面的程序将所有取到的数据存放在变量里，再打印出来：
USE pubs
GO
/*定义变量*/
DECLARE @au_lname varchar(40), @au_fname varchar(20)
/*声明游标*/
DECLARE authors_cur CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE state = "CA"
ORDER BY au_lname, au_fname
/*打开游标*/
OPEN authors_cur
/*执行第一次提取数据操作*/
FETCH NEXT FROM authors_cur
INTO @au_lname, @au_fname
/*检查上一次操作的执行状态*/
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT "作者： " + @au_fname + " " + @au_lname
FETCH NEXT FROM authors_cur
INTO @au_lname, @au_fname
END
/*关闭并释放游标*/
CLOSE authors_cur
DEALLOCATE authors_cur
GO
4.6.6 利用游标修改数据
要使用游标进行数据的修改，其前提条件是该游标必须被声明为可更新的游标。在进行游标声明时，没有带READONLY关键字的游标都是可更新的游标。
在游标声明过程中可以使用SELECT语句对多个表中的数据进行访问，因此如果声明的是可更新游标，那么可以使用该游标对多表中的数据进行修改，但是这不是一个更改数据的好办法，因为这种不规范更新数据的途径很容易造成数据的不一致。在计算机编程过程中，经常会遇到这样的情况，具有充分灵活性的语法总是难于操纵，易于出错。
使用游标更新数据的常用语法如下：
UPDATE table_name
{SET column_name = expression}
[,...n]
WHERE CURRENT OF cursor_name
其中，CURRENT OF cursor_name表示当前游标的当前数据行。CURRENT OF子句只能使用在进行UPDATE和DELETE操作的语句中。
下面的例子声明了一个可更新的游标，并限定了可以更新的列，然后针对该列进行了更新运算。
USE pubs
GO
/*定义一个对价格可以进行更改的滚动游标*/
DECLARE a_ta_t CURSOR SCROLL FOR
SELECT au_lname , title ,price
FROM authors a, titleauthor ta, titles t
WHERE a.au_id = ta.au_id
AND ta.title_id = t.title_id
AND a.state = "CA"
FOR UPDATE OF price
DECLARE @fetch_status INT
/*打开一个游标*/
OPEN a_ta_t
/*取第1行数据*/
FETCH a_ta_t
SELECT @fetch_status = @@FETCH_STATUS
WHILE @fetch_status = 0
BEGIN
UPDATE titles
SET price = price / 1.5
WHERE CURRENT OF a_ta_t
/*继续取下一数*/
FETCH a_ta_t
SELECT @fetch_status = @@FETCH_STATUS
END
/*关闭并释放游标*/
CLOSE a_ta_t
DEALLOCATE a_ta_t
GO
/*再次取数进行验证*/
SELECT au_lname , title ,price
FROM authors a, titleauthor ta, titles t
WHERE a.au_id = ta.au_id
AND ta.title_id = t.title_id
AND a.state = "CA"
GO
这个例子查找来自加利福尼亚州的作者，并把他的书的价格减了价。由于这次声明的游标是用了UPDATE OF子句，所以只有包含在这个子句列表中的数据行可以被更新。
使用游标还可以进行数据的删除，语法是：
DELETE
FROM table_name
WHERE CURRENT OF cursor_name
在使用游标进行数据的更新或删除之前，必须事先获得相应数据库对象的更新或删除的特权，这是进行这类操作的前提。
SQL Server作为典型的关系数据库，为事务控制提供了完善的编程结构。在Transact-SQL中，事务处理控制语句有以下4个：
BEGIN TRAN[SACTION] [transaction_name ]
COMMIT [ TRAN[SACTION]] [transaction_name ]
ROLLBACK [ TRAN[SACTION] ][transaction_name ]
SAVE TRAN[SACTION] [savepoint_name ]
其对应的功能分别是开始、提交、回滚和保存事务。
保存点允许在一个事务处理内部做一些工作，在特定的条件下回滚这些工作。当回滚到保存点后，只有在保存点到回滚语句之间的操作被取消，其他的操作依然有效，而且，程序会接着从回滚的断点执行下去。
下面这个例子，定义了一个简单的事务：
BEGIN TRANSACTION
USE pubs
GO
UPDATE titles
SET advance = advance * 1.25
WHERE ytd_sales > 8000
GO
DELETE authors
WHERE state = "MA"
COMMIT TRANSACTION
GO
从BEGIN TRANSACTION到COMMIT TRANSACTION只有两个操作，按照事务的定义，这两个操作要么都执行成功，要么都不执行。
下面这个例子中使用到了保存点：
--开始一个事务
BEGIN TRANSACTION exampletrans
USE pubs
GO
--执行一次更新操作
UPDATE titleauthor
SET royaltyper = 35
FROM titleauthor, titles
WHERE royaltyper = 25
AND titleauthor.title_id = titles.title_id
AND title = 'The Gourmet Microwave'
GO
--设置保存点
SAVE TRANSACTION percentchange
--第二次更新操作
UPDATE titles
SET price = price * 1.2
WHERE title = 'The Gourmet Microwave'
GO
--回滚到保存点
ROLLBACK TRANSACTION percentchanged
PRINT "程序继续执行"
COMMIT TRANSACTION
这个例子一共执行了两次更新操作，第一次操作完成后，设置了一个保存点；第二次更新操作执行完后，程序执行了事务回滚，使得第二次更新操作被取消，但是第一次更新操作依然有效，程序继续从回滚处执行，打印出一行字。
483
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
