《SQL Server 2005 编程入门经典》学习笔记

最新推荐文章于 2017-09-01 18:26:00 发布

hehe6378

最新推荐文章于 2017-09-01 18:26:00 发布

阅读量1.9k

点赞数 1

分类专栏： SQL Server

SQL Server 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

第一章 RDBMS基础：SQL Server数据库构成

数据库中的对象

数据库管理系统中包含许多对象。对于SQL Server，它常包含以下重要的数据库对象：

数据库索引事务日志程序集表报表文件集全文本目录图表用户自定义数据类型视图角色存储过程用户用户自定义函数

SQL Server的4个系统数据库

在给定的SQL Server中，数据库实际上是最高层对象。在SQL Server中，大部分其他对象为数据库对象的子对象。安装好的SQL Server第一次启动时包含4个系统数据库：

主数据库（master）——主数据库保存一组特殊的表（系统表）以用于系统的总体控制。
模型数据库（model）——模型数据库是指可以基于该模型得到一个副本。模型数据库构成新建数据库的模版。也就是说，如果想要改变新建数据库的样式，则可以根据需要更改模型数据库。注意：由于模型数据库作为其他任意数据库的模版，因此系统中必须保留该数据库，禁止删除它。
msdb——msdb数据库是SQL代理进程保存任意系统作业的场所，如计划对一数据库在每夜进行备份和执行一次计划好的存储过程。
tempdb——tempdb数据库是服务器主要工作区域之一。只要执行一个复杂或者大型的查询操作，则SQL Server需要建立一些中间表，而建立的中间表就是在tempdb数据库中。只要建立临时表，则这些表会建立在tempdb数据库中，即使您是在当前数据库中建立的这些表。只要需要临时保存数据，则很可能是将数据保存在tempdb数据库中。tempdb数据库与其他任意数据库不同。不仅数据库中的对象是临时的，连数据库本身也是临时的。在每次SQL Server启动时，tempdb数据库会被完全重建。

最基本的对象：表

表由称为域的数据（列）和实体数据（行）构成。数据库中实际的数据都存储在表中。表的定义也包含了描述表中包含数据的类型，即元数据。每一列具有该列可存储数据类型的一组规则。

索引

索引是仅在特定表或视图架构内存在的对象。索引的功能非常类似百科全书中的目录。索引中有以某一特定方式排序的查找值，使用索引可以快速查找数据库中的实际信息。

索引分为两类：

集群索引——每一个表只能有一个集群索引。如果是集群索引，其含义为：集群索引对应的表按照其索引进行物理排序。如果为百科全书做索引，则集群索引是书的页码；按页码顺序保存百科全书中的信息。
非集群索引——每一个表可以有多个非集群索引。非集群索引的含义与普通"索引"的含义更接近。如百科全书，非集群索引指的是百科全书后面的关键字目录。

触发器

触发器是存在于表框架内的对象。触发器是在表操作时（如进行插入、更新或删除等）自动执行的一段逻辑代码。触发器有多种用途，但主要用于在插入时复制数据或更新时检查数据，确保数据满足相应标准。

约束

约束是仅在表的限制中存在的另一对象。约束就是限制表中数据满足的某种条件。约束在某种方式上类似触发器，尽可能解决数据完整性问题，但他们有所不同，各自具有不同的优点。

文件组

数据库中所有的表及其他对象（日志除外）都存储在文件中。这些文件组成了一些所谓的文件组。每个文件组中可以有超过32000个文件。一个数据库仅能有一个主要文件组，可以有最多255个辅助文件组。

视图

视图是一种虚拟表。除了视图本身不包含任意数据外，视图的使用基本与表的使用类似。事实上视图仅仅是存储在表中的数据的映射和表示，它以查询的形式存储在数据库中。应用视图的主要目的是控制用户所要显示的数据。这有两方面的原因：安全和易于使用。

存储过程

存储过程是SQL Server编程功能的基础。存储过程通常是逻辑单元中的Transact-SQL语句的有序集合。存储过程允许使用变量和参数，也可使用选择和循环结构。与单条语句相比，服务器中使用存储过程有一下几个优点：

不使用长SQL语句字符串而使用短存储过程名，可减少运行存储过程中的代码所要的网络传输。
预先优化和编译，节省存储过程每次运行的时间。
通常考虑安全原因，或仅仅是简化数据库的复杂性，可以将过程封装。
可以调用其他的存储过程，使得它们可以在有限的意义上重用。

但是要注意，存储过程不是函数，它的返回值只能为整数。当存储过程成功执行后会默认返回0。完全可以忽略它的返回值，但如果需要根据返回值确定存储过程是否成功执行的话，需要在存储过程的定义中指明返回值。从这点来说，存储过程更像是一个可执行程序，会根据执行情况返回0或其他值。

用户自定义函数

用户自定义函数（UDF）更符合传统意义上的函数的概念。它和存储过程的不同处有以下几点：

返回值的数据类型包括大部分SQL Server数据类型。不包括的返回值类型是：text、ntext、image、cursor及timestamp。
基本没有"副作用"，即用户自定义函数不能完成在其范围之外的功能，比如更改表、发电子邮件或更改系统或数据库参数。
UDF类似于编程语言中使用的函数。函数可以有多个输入变来那个，可以有一个返回值。UDF中，传送到函数的所有变量都是按值传递。UDF还可以返回一种特殊类型的数据类型——表。

用户和角色

用户和角色相互并存。用户（user）等价于登录。简言之，该对象表示登录SQL Server的标识符。登录SQL Server的任何人都映射到一个用户。用户属于一个或多个角色（role）。SQL Server中可以直接赋予用户或角色执行某操作的权限，一个或多个用户可属于同一角色。

规则

规则和约束（CHECK）都是限制插入到表中的数据类型的信息。与规则不同的是，约束本身并不是对象，而是描述特定表的多个元数据。规则是为了向下兼容的需要，在新的开发中应该使用CHECK约束，避免使用规则。

默认值

SQL Server中有两种类型的默认值。包括对象本身的默认值，以及表中特定列的元数据的默认值（而非真正对象）。与此非常类似，约束是针对对象，而规则是针对元数据。当插入一条记录时，如果没有提供该列的值，且该列具有其默认值，则自动插入默认值。

用户自定义的数据类型

用户自定义的数据类型是系统定义数据类型的扩展。自SQL Server 2005版本开始，用户自定义数据类型几乎可定义任意数据。

SQL Server 2005中的数据类型

数据类型名	类	长度（以字节为单位）	数据特点
Bit	整形	1	表中的第一个Bit数据类型占1个字节；其余7个位也用作Bit数据类型。允许空格使其占用一个额外的字节。
Bigint	整形	8	可处理日常用到的越来越大的数，其取值范围为-263~263-1。
Int	整形	4	取值范围为-2147483648~ 2147483647。
SmallInt	整形	2	取值范围-32768~32768。
TinyInt	整形	1	取值范围0~255。
Decimal/Numeric	数字型	可变	固定精度，取值范围为-1038-1~1038-1。
Money	货币	8	货币单位，取值范围为-263~263，精确到4个小数位。注意货币单位可以是任意货币，不限于美元。
SmallMoney	货币	4	货币单位，取值范围为-214748.3648~214748.3647。
Float(Real)	近似小数	可变	由一参数（如Float(20)）决定其长度与精度。注意参数值表示位数，不是字节数。
DateTime	日期/时间	8	日期与时间，取值范围为1753年1月1日~9999年12月31日，精确到0.03秒。
SmallDateTime	日期/时间	4	日期与时间，取值范围为1900年1月1日~2079年6月6日，精确到分钟。
Cursor	特殊小数	1	指向光标的指针，只占用一个字节，记住组成实际光标的结果集也占内存，占用内存的大小取决于结果集。
Timestamp/rowversion	特殊小数（二进制）	8	给定数据库的唯一特定值。即使UPDATE语句没有timestamp列（时间标记），但其值在插入或更新记录的时间自动由数据库设定（不允许直接更新timestamp对象）。
UniqueIdentifier	特殊小数（二进制）	16	全球唯一标识符（GUID），必须保证在内存空间和时间内唯一。
Char	字符	可变	定长字符数据。比设定长度短时使用空格填充，为非Unicode数据，最大长度为8000字符。
VarChar	字符	可变	长度可变的字符数据。按需存储，为非Unicode数据。允许最大长度为8000字符，但使用max关键字（varchar(max)）时表示其长度可足够大（231字节）。
Text	字符	可变	SQL Server 2005保持向后兼容的需要。可使用varchar(max)代替。
NChar	Unicode字符	可变	定长Unicode字符数据。最大长度为4000字符，比设定长度短时使用空格填充。
NVarChar	Unicode字符	可变	长度可变的Unicode字符数据。按需存储。允许最大长度为8000字符，但使用max关键字（nvarchar(max)）时表示其长度可足够大（231字节）。
NText	Unicode字符	可变	SQL Server 2005保持向后兼容的需要。可使用nvarchar(max)代替。
Binary	二进制	可变	定长二进制数，最大长度为8000字节。
VarBinary	二进制	可变	可变长度二进制数，最大特定长度为8000字节，可使用max关键字（varbinary(max)）使其作为大对象字段（可达231字节）。
Image	二进制	可变	SQL Server 2005保持向后兼容的需要。可使用varbinary(max)代替
Table	其他	-	主要用于结果集，通常作为用户自定义函数返回。在表的定义中不作为可用的数据类型。
SQL_Variant	其他	-	用于保存SQL Server数据类型的容器。当列或函数需要处理多种数据类型时可使用这种数据类型。
XML	字符	可变	定义一字符字段用作XML数据。提供不符合XML模式的数据而面向XML函数的使用的功能。

说明：

SQL Server中没有无符号整数类型。

Server对象标识符

SQL Server中的所有对象都需要命名，即使在创建时没有指定名称（如表中的约束），SQL Server也会自动生成一个名称。

SQL Server中的命名规则非常简单，规则允许名字中内嵌空格，甚至允许名字是关键字。主要的命名规则如下：

对象的名字必须以Unicode 2.0规范定义的任意字母开头。大小写是否敏感取决于服务器配置的方式。
正常对象的名字最多128个字符，临时对象的名字最多116个字符。
与SQL Server关键字相同或包含内嵌空格的名字必须使用双引号（""）或方括号（[]）。哪一个视为关键字取决于设置数据库的兼容水平。

注意：

只有在设置了SET QUOTED IDENTIFIERON，双引号才可以作为列名中的分界符。特定的对象类型还存在其他命名规则。

第2章 T-SQL语言基础

T-SQL是SQL Server的结构化查询语言的"方言"。T-SQL语言遵守公共语言运行库（CLR），简言之，T-SQL为.NET语言。SQL Server 2005可以使用任何.NET语言来访问数据库，而T-SQL只保留了操作SQL Server的核心功能。

基本SELECT语句

SELECT语句的基本语法规则如下：

SELECT <column list>

[FROM <source tables>]

[WHERE <restrictive condition>]

[GROUP BY <column name or expression using a column in the SELECT list>]

[HAVING <restrictive condition based on the GROUP BY results>]

[ORDER BY <column list> [ASC|DESC]]

WHERE子句中的逻辑运算符

运算符	实例	功能
=, >, <, >=, <=, <>, !=, !>, !<	<Column Name> = <Other Column Name> <Column Name> = 'Bob'	标准的比较运算符。要注意 1. "大于"、"小于"和"等于"可能因情况不同而改变。如比较字符串时是否区分大小写。 2. !=和<>都表示"不等于"，而!<和!>分别表示"不小于"和"不大于"。
AND, OR, NOT	<Column1> = <Column2> AND <Column3> >= <Column4> <Column1> != "MyLiteral" OR <Column2> = "MyOtherLiteral"	标准的逻辑运算符。运算优先级为NOT、AND、OR。
BETWEEN	<Column> BETWEEN 1 AND 5	第一个值在第二个与第三个值之间时其值为TRUE，其等价于A>=B AND A <= C。
LIKE	<Column> Like "ROM%"	可使用%和_作为通配符。%表示可以和任意长度的字符串匹配。_表示和任意的单个字符匹配。[]指定一个字符、字符串或范围，匹配其中的任一个对象。[^]匹配指定字符串以外的任意字符。
IN	<Column> IN　(List of Numbers)	IN左边的表达式与IN右边的列表中的任意值匹配时返回TRUE。
ALL, ANY, SOME	<Column\|Expression> 比较运算符 <ANY\|SOME>(子查询)	子查询中的条件全部/任一满足比较运算符时返回TRUE。ALL指表达式要匹配结果集中的所有值。ANY和SOME相同，在表达式中匹配结果集中的任一值时返回TRUE。
EXISTS	EXISTS (子查询)	子查询返回至少一行记录时为TRUE。

常见的统计函数

函数	说明
SUM()	求和
COUNT()	统计返回的行数（除非使用COUNT(*)，否则会忽略NULL值）
AVG()	计算平均值
MIN()	计算最小值
MAX()	计算最大值

DISTINCT和ALL谓词

DISTINCT和ALL均放在SELECT的后面。DISTINCT表示去除重复的行，ALL表示保留重复的行。

SELECT语句默认是保留重复行的，使用SELECT DISTINCT <columns...>将返回没有重复的结果集（每行多个字段整体没有重复，而不是单个字段没有重复）。DISTINCT还可应用与统计函数中，表示统计时首先去除重复的行，所以"COUNT(DISTINCT OrderID)"将比"COUNT(OrderID)"返回的行更少。但是在AVG函数中使用DISTINCT没有任何意义。

ALL用于保留重复的行，这是SELECT语句的默认设置。但在使用UNION语句时默认会去除重复行，这是可以使用ALL指定保留（"SELECT... UNION ALL SELECT..."）。

使用INSERT语句添加数据

INSERT语句的语法如下：

INSERT [INTO] <table> [(column_list)]

VALUES (data_values)

注意：

在插入中可以使用DEFAULT关键字指定列使用默认值，使用NULL关键字指定列为NULL值。
如果要插入和数据与表的每列一一对应，插入语句可以忽略列名列表可选项。
插入数值类型数据不需要使用引号，而插入字符串或者日期型数据时需要使用引号。
常用的日期型数据格式为MM/DD/YYYY以及YYYY-MM-DD。

存储过程sp_help

存储过程sp_help的功能是给出任意数据库对象、用户定义的数据类型或SQL Server数据类型的信息。执行存储过程sp_help的语法结构如下：

EXEC sp_help <name>

要查看sales表的属性，只要输入一下命令：

EXEC sp_help sales

INSERT INTO... SELECT语句

INSERT INTO... SELECT语句可完成一次插入一个数据块的功能。其语法结构为INSERT语句与SELECT语句语法结构的组合，如下：

INSERT INTO <table_name>

[<column list>]

由SELECT语句产生的结果集为INSERT语句中插入的数据。

用UPDATE语句更改数据

UPDATE语句的语法结构如下：

UPDATE <table_name>

SET <column> = <value> [,<column> = <value>]

[FROM <source table(s)>]

[WHERE <restrictive condition>]

示例：

UPDATE stores

SET city = 'There'

WHERE stor_id = 'TEST'

此外，SET子句还可以使用表达式：

UPDATE titles

SET price = price * 1.1

WHERE title_id LIKE 'BU%'

DELETE语句

语法结构如下：

DELETE <table_name>

[WHERE <search condition>]

SQL Server不允许删除作为外键约束引用的行。如果一行使用外键约束引用另一行，则要先删除被引用行后才能删除引用行。

第3章连接

几种形式的JOIN子句

内部连接（INNER JOIN）
外部连接（<LEFT|RIGHT> [OUTER] JOIN）
完全连接（FULL JOIN）
交叉连接（CROSS JOIN）

连接的语法结构

SELECT <select list>

FROM <first_table> <join_type> <second_table>

[ON <join_condition>]

别名

使用AS关键字（可以省略）给列或者表取别名。同一个查询中的多个表中，可以选择哪些表使用别名，哪些表不使用别名，代码中别名和表名可以混合使用，但是只要确定了使用表的别名，则这个表必须一直使用别名。

内部连接（INNER JOIN）

内部连接根据一个或几个相同的字段将记录匹配在一起，仅仅返回那些匹配的记录。示例：

SELECT *

FROM Products p

INNER JOIN Suppliers s

ON p.SupplierID = s.SupplierID

外部连接（OUTER JOIN）

外部连接语法结构：

SELECT <select list>

FROM <left table>

<LEFT|RIGHT> [OUTER] JOIN <right table>

ON <join condition>

LEFT OUTER JOIN会使LEFT表中的所有记录都包含到结果集中，即使在RIGHT表中相没有匹配的记录。而RIGHT OUTER JOIN会使RIGHT表中的所有记录都包含到结果集中，即使在LEFT表中相没有匹配的记录。

注意NULL值无法连接NULL值。因为NULL值和NULL值是不相等的。

示例：

USE Northwind

SELECT c.CustomerID, CompanyName

FROM Customers c

LEFT JOIN Orders o

ON c.CustomerID = o.CustomerID

WHERE o.CustomerID IS NULL

完全连接（FULL JOIN）

完全连接用来将JOIN两侧的数据完全匹配，并返回所有的记录，无论是记录在JOIN的哪一侧表中。完全连接的目的是返回没有参考的记录之间的所有关系，要返回的是连接两侧表的所有记录，而且不丢弃任何记录。

交叉连接（CROSS JOIN）

交叉连接不使用ON运算符，并将CROSS JOIN的左侧的所有记录与右侧的所有记录连接。简言之，返回的是JOIN两侧的笛卡尔积。

示例：

SELECT v.VendorName, a.Address

FROM Vendors v

CROSS JOIN Address a

联合（UNION）

UNION用于将一个特殊的运算符，用于将两个或两个以上的查询产生一个结果集。使用UNION处理查询时，要注意以下关键几点：

所有UNION的查询必须在SELECT列表中有相同的列数。即如果在第一个查询中选择了3列，则在第二个查询中也要选择3列。
UNION返回的结果集的标题仅从第一个查询获得，而忽略其他查询的列标题。
查询中的对应列的数据类型必须隐式一致。
与其他非UNION查询不同，UNION查询的默认返回选项为DISTINCT，而不是ALL。可以在UNION查询中使用ALL关键字（UNION ALL），才能返回重复的行。

示例：

USE Northwind

SELECT CompanyName AS Name, Address, City, Region, PostalCode, Country

FROM Customers

UNION

SELECT CompanyName AS Name, Address, City, Region, PostalCode, Country

FROM Suppliers

UNION

SELECT FirstName + ' ' + LastName AS Name, Address, City, Region, PostalCode, Country

FROM Employees

第4章创建和修改数据表

4.1 SQL Server中的对象名

SQL Server表有4层命名约定。完全限定命名如下所示：

[ServerName.[DatabaseName,[SchemaName.]]]ObjectName

4.1.1 模式名称

如果使用模式，那么需要指定对象是在哪种模式下的。不同模式下可以有两个同名的对象。如果想访问不在默认模式下的对象，那么需要特别指明对象的模式名称（即广为人知的所有权）。

1. 关于模式的进一步讨论

在以前的发布中，所有权实际上很像它字面上的意思：即是通过完全限定名称来识别是谁"拥有"这个对象。通常，所有这或者是创建该对象的用户，或者是数据库的所有者（通常指dbo）。一个所有者与特定的登录相关，而模式可以在多个登录之间共享，一个登录也可以拥有多个模式。

在默认情况下，只有当用户或者是sysadmin系统角色的成员，或者是db_owner或db_ddladmin数据库角色时，才能在数据库中创建对象。

用户可以被授予一定权限来创建特定类型的数据库及系统对象。如果这些个体用户已经创建了一个对象，那么在默认情况下，这个对象被授予那个登录下默认的模式。

注意：

存在一个特征，并不是说就应该使用这个特征。授予CREATE权限给普通用户可能会出现不愉快的事情。简单来说，将CREATE权限限制在sa账户或sysadmins成员或db_owner安全角色之内。

2. 默认模式：dbo

无论谁创建了数据库，都被认为是"数据库所有者"，即dbo。在数据库里面创建的任何对象都带有dbo模式，而不是个体的用户名。

另外，sa（或者sysadmin角色的成员）总是dbo的别名。即无论是谁实际上拥有数据库，sa总拥有完全的权限，就好像是dbo一样。而且sa登录创建的任何对象都显示所有权为dbo。

例如：假如某个数据库的普通用户MySchema，被赋予了CREATE TABLE权限。如果该用户创建了一个名为MyTable的表，那么带有所有者限定的对象名称是MySchema.MyTable。注意，由于这时这个表有特定的所有者（MySchema），除了MySchema之外的其他用户需要提供所有者限定名称才能正确解析这个表的名称（MySchema.MyTable）。现在，假如还有一个用户，登录名为Fred。但是Fred是这个数据库的所有者（不仅仅是db_owner的成员）。假如Fred使用与MySchema同样的语句创建了名为MyTable的表，那么带所有者限定名称的表名称是dbo.MyTable。还有，因为dbo正好是默认的所有者，所以任何用户都可以用MyTable来引用该表。注意，db_owner数据库角色的成员创建的对象的默认模式不是dbo，这些对象将被赋予特定用户所设定的默认模式。

4.1.2 数据库名称

需要在当前数据库以外的数据库检索数据时，需要使用数据库限定的命名。记住，当前数据库总是默认的数据库，所以，如果只需要当前数据库中的数据，那么不需要在完全限定的名称中包括数据库名称。

4.2 CREATE语句

CREATE语句用来创建数据库中的对象。CREATE的第一部分看起来总是这样的：

CREATE <object type> <object name>

4.2.1 CREATE DATABASE

CREATE DATABASE <database name>

代码示例：

CREATE DATABASE Accounting

(

NAME = 'Accounting',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ AccountingData.mdf',

SIZE = 10MB,

MAXSIZE = 50MB,

FILEGROWTH = 5MB

)

LOG ON

(

NAME = 'AccountingLog',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ AccountingLog.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB

)

各选项含义：

1. ON

ON用在两个地方：一个定义存储数据的文件的位置，二是定义存储日志的文件的位置。

2. NAME

指定定义的文件的名称，但是只是一个逻辑名称——即SQL Server在内部使用该名称引用该文件。

3. FILENAME

指定文件的物理名称。数据文件的推荐扩展名为.mdf，日志文件的推荐扩展名为.ldf，附属文件的推荐扩展名为.ndf。

4. SIZE

指定文件的初始大小。默认情况下，大小的单位是MB（兆字节），还可以使用KB、GB或者TB。要记住，这个值至少与模型数据库一样大，而且必须是整数，否则将出错。默认的值与模版数据库一样。

5. MAXSIZE

指定文件长度的最大值。默认情况下，大小的单位是MB。这个选项没有默认值，如果没有提供该选项，则表示不限制最大值。

6. FILEGROWTH

指定当扩张文件时每次的扩张量，可以提供一个值来说明文件每次增加多少字节，或者提供一个百分比，指定文件每次增长的百分比。

7. LOG ON

指定日志文件。注意日志文件默认的大小是数据文件大小的25%。其他方面，日志文件和数据库文件的说明参数相同。

4.2.2 创建数据表

创建表的语法如下：

CREATE TABLE [database_name.[owner].]table_name

(

[[DEFAULT <constant expression>]

| [IDENTITY [(seed, increment) [NOT FOR REPLICATION]]]]

[NULL | NOT NULL]

[<column constraints>]

| [<column name> AS <computed column expression>]

| [<table constraint>]

[,...n]

)

1. 表和列名称

表和列的推荐命名规则：

名称的每个单词，首字母大写，其他字母小写。
名称尽量短，但是要具有描述性。
限制使用缩写，只使用大家都能理解的缩写。例如"ID"表示标识、"No"表示数字、"Org"表示组织。
当基于其他表来构建表时，需要在新的表名中包含其他父表的名称。
当名称中有两个单词时，不要用任何分隔符。

2. 数据类型

注意没有默认的数据类型

3. DEFAULT

如果要使用默认值，就必须紧跟在数据类型之后给定这个值。

4. IDENTITY

当你设定一个列为标识列时，SQL Server自动分配一个顺序号给你插入的每个行。注意IDENTITY列和PRIMARY KEY列是完全不同的概念，既不会因为有一个IDENTITY列就说明这个值是唯一的（例如，可以重新设置种子，使用前面用过的值）。IDENTITY值通常用于PRIMARY KEY列，但并不是必须这样使用。

5. NOT FOR REPLICATION

NOT FOR REPLICATION参数决定：当列（通过复制）发布到另外一个数据库时，是否为新的数据库分配一个新的标识值，还是保留已有的值。

6. NULL/NOT NULL

默认的设置是列值是NOT NULL，除非指定允许为空。然而，有很多不同的设置可以改变这个设置，从而影响这个默认值。

7. 列约束

列约束就是对单个列设置的关于该列可插入数据的限制和规则。

8. 计算列

该列值是由表中其他列动态生成的。具体的语法如下：

例如：

ExtendedPrice AS Price * Quantity

ListPrice AS Cost * 1.2

4.3 ALTER语句

ALTER语句用来更改对象。ALTER语句总是有相同的开头：

ALTER <object type> <object name>

4.3.1 ALTER DATEBASE

示例：

ALTER DATABASE Accounting

MODIFY FILE

(

NAME = Accounting,

SIZE = 100MB

)

4.3.2 ALTER TABLE

更经常的情况是改变表的结构。这个可以是增加、删除一列或者改变一列的数据类型等。示例：

ALTER TABLE Employees

ADD

PreviousEmployer VARCHAR(30) NULL,

DataOfBirth DATETIME NULL,

LastRaiseDate DATETIME NOT NULL, DEFAULT '2005-01-01'

4.4 DROP语句

DROP语句用来删除对象。

DROP <object type> <object name>[, ...n]

如果需要，可以同时删除两个表：

USE Accounting

DROP TABLE Customers, Employees

删除整个数据库：

DROP DATABASE Accounting

第5章约束

确保数据的完整性不是使用数据的程序的责任，而是数据库本身的责任。将数据完整性的责任移到数据库本身是数据库管理的一次革命。

3种不同类型的约束：

实体约束
域约束
参照完整性约束

具体的约束类型：

PRIMARY KEY约束
FOREIGN KEY约束
UNIQUE约束
CHECK约束
DEFAULT约束

5.1 约束的类型

5.1.1 域约束

域约束处理一个或多个列，确保一个特定列或一组特定列满足特定的标准。

5.1.2 实体约束

实体约束都是关于每个行的。这种形式的约束并不关心一个整体的列，只对特定的行感兴趣，如PRIMARY KEY约束和UNIQUE约束。

5.1.3 参照完整性约束

参照完整性约束是在某列的值必须与其他列的值匹配时创建的，列可以在同一个表中，或者更通常的是在不同的表中，如FOREIGN KEY约束。

5.2 约束命名

常见的约束的推荐命名规则如下：

CHECK约束以CK开头、主键约束以PK开头、外键约束以FK开头、唯一约束以UN开头。
后接表名、列名。

如在Customers表上对PhoneNo列设置约束：CK_Customers_PhoneNo，Customers表上的主键约束：PK_Custoemrs_CustomerID。

5.3 键约束

常用的键类型：主键、外键、唯一约束。

5.3.1 主键约束

1. 在创建表的时候创建主键约束。

CREATE TABLE Customers

(

CustomerNo INT IDENTITY NOT NULL PRIMARY KEY,

......

)

2. 在已存在的表上创建主键约束。

USE Accounting

ALTER TABLE Employees

ADD CONSTRAINT PK_EmployeeID

PRIMARY KEY (EmployeeID)

5.3.2 外键约束

在CREATE语句中设置一列或几列外键约束的语法如下所示：

FOREIGN KEY REPERENCES <table name>(<column name>)

[ON DELETE {CASCADE|NO ACTION|SET NULL|SET DEFAULT}]

[ON UPDATE {CASCADE|NO ACTION|SET NULL|SET DEFAULT}]

示例：

USE Accounting

CREATE TABLE Orders

(

OrderID INT IDENTITY NOT NULL

PRIMARY KEY,

CustomerNo INT NOT NULL

FOREIGN KEY REFERENCES Customers(CustomerNo),

OrderDate SMALLDATETIME NOT NULL,

EmpoyeeID INT NOT NULL

)

1. 在已存在的表中添加一个外键

ALTER TABLE Orders

ADD CONSTRAINT FK_EmployeeCreatesOrder

FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)

2. 使一个表自引用

在实际创建自引用约束之前，很关键的一点是在添加外键之前表中至少有一行。

ALTER TABLE Employees

ADD CONSTRAINT FK_EmployeeHasManager

FOREIGN KEY (ManagerEmpID) REFERENCES Employees(EmployeeID)

3. 级联动作

外键是双向的，即不仅是限制子表的值必须存在于父表中，还在每次对父表操作后检查子行。SQL Server的默认行为是在子表存在时"限制"父表不被删除。然而，有时会自动删除任何相关记录，而不是防止删除被引用的记录。同样，在更新记录时，可能希望相关的记录自动引用刚刚更新的记录。这种进行自动删除和更新的过程称为级联。通过修改声明外键的语法——添加ON子句，来定义级联操作。

USE Accounting

CREATE TABLE OrderDetails

(

OrderID INT NOT NULL,

PartNo VARCHAR(10) NOT NULL,

Description VARCHAR(25) NOT NULL,

Qty INT NOT NULL,

CONSTRAINT PK_OrderDetails

PRIMARY KEY (OrderID, PartNo),

CONSTRAINT FK_OrderContainsDetails

FOREIGN KEY (OrderID)

REFERENCES Orders(OrderID)

ON UPDATE NO ACTION

ON DELETE CASCADE

)

如果对外键定义了CASCADE，则操作会从父表级联到子表中。即，如果从父表删除了某项，子表中依赖该项的项都会被删除；如果从父表中更新了某项，则子表中依赖该项的字段也会被更新。

值得注意的是：CASCADE动作所能影响的深度没有限制。

4. 其他操作

NO ACTION为默认操作，即如果子表有依赖，则禁止对父表中的该字段进行删除和更新操作。

SET NULL操作会在父表中的该字段被删除或者更新时，将子表中的依赖项设为NULL，前提是子表中的该项可为NULL值。

SET DEFAULT操作会在父表中的该字段被删除或者更新时，将子表中的依赖项设为在子表中定义的默认值，当然前提是在子表中该字段有默认值。

5.3.3 唯一约束

唯一约束不会自动防止您设置一个NULL值。是否允许NULL值取决于表中相应列的NULL选项的设置。然而，要记住如果您确实允许NULL值，那么只能插入一个NULL。

在创建表时设置唯一约束：

CREATE TABLE Shippers

(

ShipperID INT IDENTITY NOT NULL PRIMARY KEY,

ShipperName VARCHAR(30) NOT NULL,

Address VARCHAR(30) NOT NULL,

City VARCHAR(25) NOT NULL,

State CHAR(2) NOT NULL,

Zip VARCHAR(10) NOT NULL,

PhoneNo VARCHAR(14) NOT NULL UNIQUE

)

在已存在的表中创建唯一约束：

ALTER TABLE Employees

ADD CONSTRAINT AK_EmployeeSSN

UNIQUE (SSN)

在约束名称中的AK前缀代表"交替键（Alternate Key）"，也可以使用前缀UQ或者简单的U，代表唯一约束。

5.4 CHECK约束

CHECK约束使用与WHERE字句一样的规则来定义。CHECK约束标准的示例如下：

目标	SQL
限制Month列为合适的数字	BETWEEN 1 AND 12
合适的SSN格式	LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
限制Shippers的一个特定列表	IN ('UPS', 'Fed Ex', 'USPS')
价格必须为正	UnitPrice >= 0
在同一行中引用另外一个列	ShipDate >= OrderDate

在已存在的表中添加CHECK约束：

ALTER TABLE Customers

ADD CONSTRAINT CK_CustomerDateInSystem

CHECK (DateInSystem <= GETDATE())

试着插入违反CHECK约束的记录会得到错误。

5.5 DEFAULT约束

DEFAULT约束定义了当插入新行时，在您定义了默认约束的列中没有数据时填充的默认值。要注意：

默认值只在INSERT语句中使用——在UPDATE语句和DELETE语句中被忽略。
如果在INSERT语句中提供了任意的值（包括NULL值），那么就不使用默认值。
如果没有提供值，那么总是使用默认值。

5.5.1 在CREATE TABLE语句中定义DEFAULT约束

示例：

CREATE TABLE Shippers

(

ShipperID INT IDENTITY NOT NULL

PRIMARY KEY,

ShipperName VARCHAR(30) NOT NULL,

DataInSystem SMALLDATETIME NOT NULL

DEFAULT GETDATE()

)

5.5.2 在已存在的表中添加DEFAULT约束

示例：

ALTER TABLE Customers

ADD CONSTRAINT DF_CustomerDefaultDateInSystem

DEFAULT GETDATE() FOR DateInSystem

5.6 使约束失效

5.6.1 在创建约束时忽略无效的数据

默认情况下，除非已存在的数据满足约束标准，否则SQL Server将不会创建约束。要想在创建约束时不检查已经在表中的数据是否满足约束，可以在添加约束时添加WITH NOCHECK选项。示例：

ALTER TABLE Customers

WITH NOCHECK

ADD CONSTRAINT CK_CustomerPhoneNo

CHECK

(Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

5.6.2 临时使已存在的约束失效

使用NOCHECK选项关闭约束，而不是删除它。示例：

ALTER TABLE Customers

NOCHECK

CONSTRAINT CK_CustomerPhoneNo

当准备重新让约束起效时，使用CHECK选项代替NOCHECK：

ALTER TABLE Customers

CHECK

CONSTRAINT CK_CustomerPhoneNo

第6章在查询中添加更多内容

6.1 子查询的概念

子查询是嵌套在另外一个查询中的正常的T-SQL查询。在有一个SELECT语句作为部分数据或者另外一个查询的条件的基础时，通过使用括号创建子查询。

子查询通常用于满足下列需求之一：

将一个查询分隔为一系列的逻辑步骤。
提供一个列表作为WHERE子句或者IN、EXISTS、ANL、SOME、ALL的目标
为父查询中的每个记录提供一个查询表。

6.2 嵌套的子查询

嵌套的子查询只在一个方向嵌套——返回在外部查询中使用的单个值，或者在IN运算符中使用的一个完整的值列表。

在最松散的意义上说，查询语法看起来像下面的两个语法模板：

SELECT <select list>

FROM <some table>

WHERE <some column> = (

SELECT <single column>

FROM <some table>

WHERE <condition that results in only one row returned>)

或者：

SELECT <select list>

FROM <some table>

WHERE <some column> IN (

SELECT <single column>

FROM <some table>

WHERE <where condition >)

6.2.1 使用单个值的SELECT语句的嵌套查询

例如，假设希望知道每一天通过系统销售的产品的每个条目的ProductID：

SELECT DISTINCK o.OrderDate, od.ProductID

FROM Orders o

INNER JOIN OrderDetails od

ON o.OrderID = od.OrderID

WHERE o.OrderDate = (SELECT MIN(OrderDate) FROM Orders)

6.2.2. 使用返回多个值的子查询的嵌套查询

例如，查看所有具有折扣记录的商店列表：

USE Pubs

SELECT stor_id AS "Store ID", stor_name AS "Store Name"

FROM Stores

WHERE stor_id IN (SELECT stor_id FROM Discounts)

6.2.3. 使用嵌套的SELECT来发现孤立的记录

这种嵌套的SELECT和前面示例几乎相同，区别是添加了NOT运算符。这个不同点时的在转化连接语法时设置等于外部连接而不是内部连接。例如，需要查询所有在Pubs数据库中没有匹配的折扣记录的商店：

SELECT stor_id AS "Store ID", stor_name AS "Store Name"

FROM Stores

WHERE stor_id NOT IN

(SELECT stor_id FROM Discounts WHERE stor_id IS NOT NULL)

6.3 相互关联的子查询

6.3.1 相互关联的子查询的工作原理

在相互关联的子查询中，内部查询在外部查询提供的信息上运行，反之亦然。有3个步骤的处理过程：

外部查询获得一个记录，然后将该记录传递到内部查询。
内部查询根据传递的值执行。
内部查询然后将结果值传回到外部查询，外部查询利用这些值完成处理过程。

6.3.2 在WHERE子句中的相互关联的子查询

例如，需要查询系统中每个顾客第一个订单的OrderID和OrderDate：

SELECT o1.CustomerID, o1.OrderID, o1.OrderDate

FROM Orders o1

WHERE o1.OrderDate = (

SELECT MIN(o2.OrderDate)

FROM Orders o2

WHERE o2.CustomerID = o1.CustomerID)

6.3.3 在SELECT列表中的相互关联的子查询

例如，现在需要查询顾客的姓名和在哪天开始订购商品：

SELECT cu.CompanyName,

(SELECT MIN(OrderDate)

FROM Orders o

WHERE o.CustomerID = cu.CustomerID) AS "Order Date"

FROM Customers cu

6.3.4 处理NULL数据——ISNULL函数

ISNULL()接受一个变量或者表达式来验证是否是一个空值。如果值确实是NULL，那么函数返回其他预指定的值。如果原来的值不是NULL，那么返回原来的值。语法如下：

ISNULL(<expression to test>, <replacement value if null>)

因此，示例如表所示：

ISNULL表达式	返回值
ISNULL(NULL, 5)	5
ISNULL(5, 15)	5
ISNULL(MyColumnName, 0) where MyColumnName IS NULL	0
ISNULL(MyColumnName, 0) where MyColumnName = 3	3
ISNULL(MyColumnName, 0) where MyColumnName = 'Fred Farmer'	Fred Farmer

使用示例：

SELECT cu.CompanyName,

ISNULL(CAST((SELECT MIN(o.OrderDate)

FROM Orders o

WHERE o.CustomerID = cu.CustomerID) AS VARCHAR), 'NEVER ORDERED')

AS "Order Date"

FROM Customers cu

6.3 派生表

例如，现在需要查询既订购了Chocolade又订购了Vegie-spread的所有公司名称。查询代码如下所示：

SELECT DISTINCT c.CompanyName

FROM Customers c

INNER JOIN (

SELECT CustomerID

FROM Orders o

INNER JOIN OrderDetails od

ON o.OrderID = od.OrderID

INNER JOIN Products p

ON od.ProductID = p.ProductID

WHERE p.ProductName = 'Chocolade') AS spen

ON c.CustomerID = spen.CustomerID

INNER JOIN (

SELECT CustomerID

FROM Orders o

INNER JOIN OrderDetails od

ON o.OrderID = od.OrderID

INNER JOIN Products p

ON od.ProductID = p.ProductID

WHERE p.ProductName = 'Vegie-spread') AS spap

ON c.CustomerID = spap.CustomerID

6.4 EXISTS运算符

使用EXISTS时，根据是否存在数据满足查询中EXISTS语句所建立的标准，返回一个简单的TRUE和FALSE。例如：

SELECT CustomerID, CompanyName

FROM Customers cu

WHERE EXISTS (

SELECT OrderID

FROM Orders o

WHERE o.CustomerID = cu.CustomerID)

当使用EXISTS关键字时，SQL Server不需要执行一行一行的连接，而是寻找记录，知道找到第一个匹配的记录，停止在那里。只要有一个匹配，EXISTS就为真，不需要继续查找。

如果需要查询没有订购任何产品的客户，可以使用NOT EXISTS：

SELECT CustomerID, CompanyName

FROM Customers cu

WHERE NOT EXISTS (

SELECT OrderID

FROM Orders o

WHERE o.CustomerID = cu.CustomerID)

6.5 数据类型转换：CAST和CONVERT

CAST和CONVERT都可以执行数据类型转换。在大部分情况下，两者执行相同的功能，不同的是CONVERT还提供一些日期格式转换，而CAST没有这个功能。

注意，CAST是ANSI兼容的，而CONVERT不是。

各自的语法如下：

CAST (expression AS data type)

CONVERT (data type, expression[, style])

CAST和CONVERT可以进行很多数据类型转换，在SQL Server不进行隐式转换时，需要这种转换。例如：

SELECT 'The Customer has an Order numbered ' + CAST(OrderID AS VARCHAR)

FROM Orders

WHERE CustomerID = 'ALFKI'

例如，需要将timestamp列转换为正常数字。一个timestamp是个二进制数字，因此需要转换：

SELECT CloTS AS "Uncoverted", CAST(ColTS AS INT) AS "Converted"

FROM ConvertTest

还可以转换日期：

SELECT OrderDate, CAST(OrderDate AS VARCHAR) AS "Converted"

FROM Orders

WHERE OrderID = 11050

CONVERT还可以控制日期格式：

SELECT OrderDate, CONVERT(VARCHAR, OrderDate, 111) AS "Converted"

FROM Orders

WHERE OrderID = 11050

CONVERT函数最后一个代码说明需要的格式。注意，任何以超过100表示的是4位的年份；小于100的是两位数字的年份，不过有很少的一些例外，并且小于100表示的格式加上100后即为对应的4位的年份表示的格式。

第7章视图

视图的核心实际上仅仅是一个存储的查询。重要的是可以将来自于基本表（或者其他视图）的数据混合以及匹配，以创建在大多数方面上像另一个基本表那样起作用的对象。

7.1 简单的视图

视图语法的基本形式：

CREATE VIEW <view name>

[WITH ENCRYPTION]

WITH CHECK OPTION

示例——创建简单的视图：

USE Accounting

CREATE VIEW CustomerPhoneList_vw

SELECT CustomerName, Contact, Phone

FROM Customers

对Employees表创建视图，隐藏隐私信息：

USE Accounting

CREATE VIEW Employees_vw

SELECT EmployeeID,

FirstName,

MiddleInitial,

LastName,

Title,

HireDate,

ManagerEmpID,

Department

FROM Employees

7.2 作为过滤器的视图

在创建视图时使用WHERE字句来过滤查询中的结果。

示例：

CREATE VIEW CurrentEmployees_vw

SELECT EmployeeID,

FirstName,

MiddleInitial,

LastName,

Title,

HireDate,

TerminationDate,

ManagerEmpID,

Department

FROM Employees

WHERE TerminationDate IS NULL

7.3 更加复杂的视图

在简单视图的基础上添加连接。示例——查询订单、零件和消费者信息：

USE Northwind

CREATE VIEW CustomerOrders_vw

SELECT cu.CompanyName,

o.OrderID,

o.OrderDate,

od.ProductID,

p.ProductName,

od.Quantity,

od.UnitPrice,

od.Quantity * od.UnitPrice AS ExtendedPrice

FROM Customers AS cu

INNER JOIN Orders AS o

ON cu.CustomerID = o.CustomerID

INNER JOIN OrderDetails AS od

ON o.OrderID = od.OrderID

INNER JOIN Products AS p

ON od.ProductID = p.ProductID

数据库的用户可以方便地查询到消费者的订单信息，而不需要关心四个表的连接：

SELECT CompanyName, ExtendedPrice

FROM CustomerOrders_vw

WHERE OrderDate = '1996-9-3'

7.4 通过视图改变数据

从使用的观点来看，视图非常像表那样地工作。但是，现在来看看一些不同之处。

你可以成功地对视图执行INSERT、UPDATE以及DELETE语句。但是，当通过视图改变数据的时候，有一些内容需要注意：

如果视图包含连接，在大多数情况下，除非使用INSTEAD OF触发器，否则不能对数据执行INSERT或者DELETE操作。在一些情况下，UPDATE可以不使用INSTEAD OF触发器来工作，但是仅限于个别情况。
如果视图仅仅引用单个的表，那么在表中所有需要的字段都在视图中或者有默认值得情况下，可以直接执行INSERT操作。否则，需要使用INSTEAD OF触发器。
在一个有限的范围内，可以限制在视图中可以插入和更新的内容以及不可以插入和更新的内容。

1. 以连接的数据方式处理视图的变化

如果视图包含连接，在大多数情况下，除非使用INSTEAD OF触发器，否则不能对数据执行INSERT或者DELETE操作。

2. 必要的字段必须在视图中出现或者具有默认值

3. 约束插入到视图中的内容——WITH CHECK OPTION

WITH CHECK OPTION的规则很简单——为了通过使用视图更新或者插入数据，结果行必须符合要求以出现在视图结果中。即如果通过视图插入的值不能在视图中显示出来，则禁止插入该行记录。

7.5编辑视图

ALTER语句会完全替换现有的视图。使用ALTER VIEW语句和先删除后新建视图的区别在于：

ALTER VIEW期望找到一个已存在的视图，而CREATE则相反。
ALTER VIEW保留了视图上任何已经建立的权限信息。
ALTER VIEW保留了任何依赖信息。

7.6 删除视图

DROP VIEW <view name> [, <view name> [...n]]

7.7 保护代码：加密视图

使用WITH ENCRYPTION选项加密视图，注意和WITH CHECK OPTION出现的位置不同：

ALTER VIEW CustomerOrders_vw

WITH ENCRYPTION

SELECT cu.COmpanyName,

o.OrderDate,

od.ProductID,

p.ProductName,

od.Quantity,

od.UnitPrice,

od.Quantity * od.UnitPrice AS ExtendedPrice

FROM Customers AS cu

INNER JOIN Orders AS o

ON cu.CustomerID = o.CustomerID

INNER JOIN OrderDetails AS od

ON o.OrderID = od.OrderID

INNER JOIN Products AS p

ON od.ProductID = p.ProductID

注意，一旦源代码被加密了，就没有办法恢复。

第8章脚本与批处理

8.1 脚本基础

脚本示例：

USE Northwind

DECLARE @Ident INT

INSERT INTO Orders

(CustomerID, OrderDate)

VALUES

('ALFKI', DATEADD(day, -1, GETDATE()))

SELECT @Ident = @@IDENTITY

INSERT INTO OrderDetails

(OrderID, ProductID, UnitPrice, Quantity)

VALUES

(@Ident, 1, 50, 25)

SELECT 'The OrderID of the INSERTed row is ' + CONVERT(VARCHAR(8), @Ident)

8.1.1 USE语句

USE语句用于设置当前数据库。USE语句会影响在完全限定对象名的数据库部分使用默认值的任何地方。

8.1.2 声明变量

DECLARE语句具有相当简单的语法：

DECLARE @<variable name> <variable type> [, …]

可以一次仅仅声明一个变量，也可以一次声明几个变量。变量开始的值将总是为NULL，直到显示地将变量设置为一些其他的值。

1. 为变量设置值

目前在变量中设置值的方法有两种。可以使用SELECT语句或者SET语句。从功能上看，它们的作用几乎是相同的，不同的是SELECT语句具有使得源值来自SELECT语句中的某一列的能力。

使用SET设置变量

SET通常用于以更加程序化的语言中所使用的方式来设置变量。经典的使用示例是：

SET @TotalCost = 10

SET @TotalCost = @UnitCost * 1.1

使用SET，不能将查询得到的值赋给变量——必须将查询和SET分开。例如：

USE Northwind

DECLARE @Test MONEY

SET @Test = (SELECT MAX(UnitPrice) FROM OrderDetails)

SELECT @Test

注意：

尽管这个语法可以起作用，但习惯上，从来不采用这种方法实现代码。

使用SELECT设置变量

当变量中存储的信息来源于查询的时候，经常用SELECT给变量赋值。例如，上面最后的示例中使用SELECT是更加常用的做法：

USE Northwind

DECLARE @Test MONEY

SELECT @Test = MAX(UnitPrice) FROM OrderDetails

SELECT @Test

2. 系统函数概述

注意这些系统函数常被人们认为是"系统常量"，但在SQL Server中更规范的名称为"系统函数"。其中最值得关心的如下所示：

系统函数	用途	注释
@@DATEFIRST	返回当前设置的每个星期的第一天（比如星期日或者星期一）	是一个系统范围的设置——如果有人改变了这个设置，就不能得到所期望的结果。
@@ERROR	返回在当前连接上的最近的T-SQL语句错误的数目。如果没有错误，返回0	在每个新的语句下重新设置。如果需要保存这个值，应该立刻把这个值移动到一个局部变量中。
@@IDENTITY	返回插入的最近的标识值，作为最近的INSERT或者SELECT INTO语句的结果	如果没有标识值产生，那么设置为NULL。即使缺少标识值是由于一个运行的语句的失败，也是如此。如果通过一个语句执行多个插入，那么只返回最后的标识值。
@@REMSERVER	仅仅在存储过程中使用。返回称为存储过程的服务器的数值	在希望sproc根据远程服务器不同表现出不同的行为时，这个选项是很方便的。
@@ROWCOUNT	一个最经常使用的系统函数。返回最近的语句所影响的行的数目。	一般在非运行时错误检查时使用。例如，如果尝试通过使用一个WHERE字句删除一行，并且没有行被影响，那么那将意味着一些不期望的事情发生了。

8.1.3 使用@@IDENTITY

@@IDENTITY是所有的系统函数中最重要的一个。标识列是这样的列，在那里没有提供一个值，而是SQL Server自动地插入一个值。任何的INSERT或者INSERT INTO语句都会更新这个函数的返回值。如果没有新的标识列被插入，将返回NULL。如果插入了多个行，生成了多个标识值，则@@IDENTITY将返回最后生成的标识值。如果语句触发了一个或多个触发器，该触发器又执行了生成标识值的插入操作，那么，在语句执行后立即调用@@IDENTITY将返回触发器生成的最后一个标识值。如果对包含标识列的表执行插入操作后触发了触发器，并且触发器对另一个没有标识列的表执行了插入操作，则@@IDENTITY将返回第一次插入的标识值。出现INSERT或SELECT INTO语句失败或大容量复制失败，或者事务被回滚的情况时，@@IDENTITY值不会恢复为以前的设置。

8.1.4 使用@@ROWCOUNT

@@ROWCOUNT说明上一个SQL语句（SELECT、UPDATE、INSERT和DELETE等）影响了多少行。示例：

USE Northwind

DECLARE @RowCount INT

SELECT * FROM Categories

SELECT @RowCount = @@ROWCOUNT

PRINT 'The value of @@ROWCOUNT was ' + CAST(@RowCount AS VARCHAR(5))

则最后一行显示：

The value of @@ROWCOUNT was 8

8.2 批处理

批处理是进入一个逻辑单元的T-SQL语句组。一个批处理中的所有语句被组合为一个执行计划，因此对所有语句一起进行语法分析，并且必须通过语法验证，否则将没有一个语句会执行。但是，这并不能防止运行时错误的发生。如果发生运行时错误，那么任何在发生运行时错误之前执行的语句将仍然是有效的。简言之，如果一个语句不能通过语法分析，那么不会运行任何语句。如果一个语句在运行时失败，那么产生错误语句之前的所有语句都已经运行了。

可以将一个脚本分开为多个批处理，方法是使用GO语句。GO语句：

必须自成一行（只有注释可以在相同的行上）。
使得从脚本或者上一个GO语句开始的所有语句编译成一个执行计划并发送到服务器，与任何其他批处理无关。
不是T-SQL命令，而是由各种SQL Server命令实用程序识别的命令。

代码示例：

USE AdventureWorks

DECLARE @MyVarchar VARCHAR(50) – This DECLARE only lasts for this batch!

SELECT @MyVarchar = 'Honey, I''m home…'

PRINT 'Done with first batch…'

PRINT @MyVarchar – This generates an error since @MyVarchar isn't declared in this batch

PRINT 'Done with second batch'

PRINT 'Done with third batch' – Notice that this still gets executed even after the error

结果如下所示：

Done with first batch…

Msg 137, Level 15, State 2, Line 2

Must declare the scalar variable "@MyVarchar"

Done with third batch

8.2.1 批处理中的错误

批处理中的错误分成两类：

语法错误
运行时错误

如果查询分析器发现一个语法错误，那么批处理的处理过程会立即取消。因为语法检查发生在批处理编译或者执行之前，所以在语法检查期间的失败意味着还没有批处理被执行。

运行时错误的工作方式则不同。因为任何在遇到运行时错误之前执行的语句已经完成了，所以除非是未提交的事务的一部分，否则这些语句所做的任何事情的影响将保留下来。一般而言，运行时错误将终止从错误发生地方到批处理末端的批处理的执行。

8.2.2 什么时候使用批处理

批处理有几个目的，但是所有的批处理具有一个共同点——在脚本中当一些事情必须发生在另外一件事之前或者分开发生时，使用批处理。

1. 要求有自己的批处理的语句

有一些命令必须完全是它们自己的批处理的一部分。这些命令包括：

CREATE DEFAULT
CREATE PROCEDUER
CREATE RULE
CREATE TRIGGER
CREATE VIEW

如果你想在一个脚本中将这些语句中的任意一些和其他的语句进行组合，那么需要通过使用GO语句将它们分开为各自的批处理。

注意：

注意，如果DROP一个对象，那么应该将DROP语句放在它自己的批处理中或者至少和其他DROP语句在一个批处理中。

2. 使用批处理建立优先权

使用批处理语句的最可能如果在下一个任务开始之前，需要全部完成上一个任务。例如，在尝试使用新数据库时，需要先完成CREATE DATABASE语句：

CREATE DATABASE Test

USE Test

CREATE TABLE TestTable

(

col1 INT,

col2 INT

)

另外，当使用ALTER TABLE语句显著地修改一个列的类型或者添加列时，直到执行修改任务的批处理已经完成时，才能利用这些变化。

USE Test

ALTER TABLE TestTable

ADD col3 INT

INSERT INTO TestTable(col1, col2, col3)

VALUES (1, 1, 1)

第9章存储过程和流控制语句

存储过程（stored procedure）有时也称为sproc。存储过程存储于数据库中而不是在单独的文件中，有输入参数、输出参数以及返回值等。

9.1 创建存储过程：基本语法

在数据库中，创建存储过程和创建其他对象的过程一样，除了它使用的AS关键字外。存储过程的基本语法如下：

CREATE PROCDUER|PROC <sproc name>

[<parameter_name>[schema.]<data_type> [VARYING][=<default_value>][OUT [PUT]][,

...]]

<code>

示例：

USE Northwind

CRREATE PROC spShippers

SELECT * FROM Shippers

执行这个存储过程：

EXEC spShippers

9.2 使用ALTER改变存储过程

当使用T-SQL编辑存储过程的时候，需要记住的是它完全取代了现存的存储过程。使用ALTER PROC和CREATE PROC的区别在于：

ALTER PROC期望找到现存的存储过程，而CREATE则不是。
ALTER PROC保留了已经建立的存储过程的任何权限。它在系统对象中保留了相同的对象ID并允许保留依赖关系。
ALTER PROC在其他对象上保留了任何依赖关系的信息，这些对象可以调用修改的存储过程。

注意：

如果执行DROP，然后使用CREATE，这和使用ALTER PROC语句一样，几乎都能得到相同的效果，除了一个很重要的区别——如果使用DROP和CREATE，则需要完全重新建立权限，权限规定了可以使用以及不能使用存储过程的用户。

9.3 删除存储过程

这个过程非常简单：

DROP PROC|PROCEDURE <sproc name>

9.4 参数化（Parameterization）

声明参数

声明参数需要以下2到4部分信息：

名称
数据类型
默认值
方向

语法如下：

@parameter_name [AS] datatype[= default|NULL] [VARYING] [OUTPUT|OUT]

名称有一个简单的规则集合。首先，它必须以@开始。此外，命名规则除了不能有嵌套的空格外，它和SQL的命令规则是相同的。

数据类型可以使用SQL Server内置的或用户自定义的类型。

注意：

声明CURSOR类型参数的时候，必须也使用VARYING和OUTPUT选项。
OUTPUT可以简写为OUT。

示例：

USE Northwind

CREATE PROC spInsertShipper

@CompanyName NVARCHAR(40),

@Phone NVARCHAR(24)

INSERT INTO Shippers

VALUES

(@CompanyName, @Phone)

可以使用这个新的存储过程来插入新的数据：

EXEC spInstertShipper 'Speedy Shippers, Inc.', '(503)555-5566'

因为并没有为任何参数提供默认值，所以需要提供两个参数。这意味着为了成功运行该存储工程，则必须提供两个参数。

1. 提供默认值

示例：

USE Northwind

CREATE PROC spInsertShipperOptionalPhone

@CompanyName NVARCHAR(40),

@Phone NVARCHAR(24) = NULL

INSERT INTO Shippers

VALUES (@CompanyName, @Phone)

重新发出命令，但是使用新的存储过程：

EXEC spInsertShipperOptionalPhone 'Speedy Shippers, Inc'

这次一切顺利，成功插入了新的纪录。

2. 创建输出参数

示例：

USE Northwind

CREATE PROC spInsertOrder

@CustomerID NVARCHAR(5),

@EmployeeID INT,

@OrderDate DATETIME = NULL,

@RequiredDate DATETIME = NULL,

@ShippedDate DATETIME = NULL,

@ShipVia INT,

@Freight MONEY,

@ShipName NVARCHAR(40) = NULL,

@ShipAddress NVARCHAR(60) = NULL,

@ShipCity NVARCHAR(15) = NULL,

@ShipRegion NVARCHAR(15) = NULL,

@ShipPostalCode NVARCHAR(10) = NULL,

@ShipCountry NVARCHAR(15) = NULL,

@OrderID INT OUTPUT

INSERT INTO Orders

VALUES

(

@CustomerID,

@EmployeeID,

@OrderDate,

@RequiredDate,

@ShippedDate,

@ShipVia,

@Freight,

@ShipName,

@ShipAddress,

@ShipCity,

@ShipRegion,

@ShipPostalCode,

@ShipCountry

)

SELECT @OrderID = @@IDENTITY

执行该存储过程的代码如下：

USE Northwind

DECLARE @MyIdent INT

EXEC spInsertOrder

@CustomerID = 'ALFKI',

@EmployeeID = 5,

@OrderDate = '5/1/1999'

@ShipVia = 3,

@Freight = 5.00,

@OrderID = @MyIdenty OUTPUT

SELECT @MyIdent AS IdentityValue

SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipName

FROM Orders

WHERE OrderID = @MyIdent

需要注意以下几点：

在存储过程声明中，输出参数需要使用OUTPUT关键字。
调用存储过程的时候也必须使用OUTPUT关键字，才能保证参数被正确的输出。注意如果没有使用OUTPUT关键字，不会产生任何错误，但是此时输出参数的值将是无法保证的。
赋给输出变量的变量不需要和存储过程中的内部参数拥有相同的名称。例如在本例中，内部参数叫做@OrderID，而传给值的变量叫做@MyIdent。
需要使用EXEC（或EXECUTE）关键字来调用存储过程。

9.5 流控制语句

T-SQL提供了大多数流控制语句的典型的选择，同样也有CASE语句，但是它没有像其他语言中预期的那种流控制级的能力。

9.5.1 IF...ELSE语句

IF...ELSE语句的实现方式和C语言是接近相同的。基本的语法如下：

IF <Boolean Expression>

<SQL statement> | BEGIN <code series> END

[ELSE

<SQL statement> | BEGIN <code series> END]

其中的表达式可以是取布尔值的任意表达式。

提示：

不恰当的使用NULL值是个常见的陷阱。例如经常会有如下错误出现：

IF @MyVar = NULL

在大多数系统上（遵循ANSI标准）这样的表达式永远都不会为真，并且为绕过所有的NULL值结束。想要判断一个值是否为空应该这样来写：

IF @MyVar IS NULL

不要忘记了NULL不等于任何值——甚至是NULL。不要使用"="而要使用"IS"。

1. ELSE子句

注意：

结果返回值为NULL的表达式会被当作FALSE从而进入ELSE子句。也就是说，如果IF子句中的语句返回值为FALSE或者NULL，则执行ELSE子句中的语句。

示例：

USE Northwind

ALTER PROC spInsertOrder

@CustomerID NVARCHAR(5),

@EmployeeID INT,

@OrderDate DATETIME = NULL,

@RequiredDate DATETIME = NULL,

@ShippedDate DATETIME = NULL,

@ShipVia INT,

@Freight MONEY,

@ShipName NVARCHAR(40) = NULL,

@ShipAddress NVARCHAR(60) = NULL,

@ShipCity NVARCHAR(15) = NULL,

@ShipRegion NVARCHAR(15) = NULL,

@ShipPostalCode NVARCHAR(10) = NULL,

@ShipCountry NVARCHAR(15) = NULL,

@OrderID INT OUTPUT

DECLARE @InsertedOrderDate SMALLDATETIME

IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7

SELECT @InsertedOrderDate = NULL

ELSE

SELECT @InsertedOrderDate =

CONVERT(DATETIME, CONVERT(VARCHAR, @OrderDate, 112))

INSERT INTO Orders

VALUES

(

@CustomerID,

@EmployeeID,

@OrderDate,

@RequiredDate,

@ShippedDate,

@ShipVia,

@Freight,

@ShipName,

@ShipAddress,

@ShipCity,

@ShipRegion,

@ShipPostalCode,

@ShipCountry

)

SELECT @OrderID = @@IDENTITY

2. 把代码分组为块

SQL Server提供了把代码分组为块的方法，可以认为这个块是属于一起的。这个块以BEGIN语句开始，然后直到END语句结束。

现在可以修改订单插入的存储过程如下：

USE Northwind

ALTER PROC spInsertOrder

@CustomerID NVARCHAR(5),

@EmployeeID INT,

@OrderDate DATETIME = NULL,

@RequiredDate DATETIME = NULL,

@ShippedDate DATETIME = NULL,

@ShipVia INT,

@Freight MONEY,

@ShipName NVARCHAR(40) = NULL,

@ShipAddress NVARCHAR(60) = NULL,

@ShipCity NVARCHAR(15) = NULL,

@ShipRegion NVARCHAR(15) = NULL,

@ShipPostalCode NVARCHAR(10) = NULL,

@ShipCountry NVARCHAR(15) = NULL,

@OrderID INT OUTPUT

DECLARE @InsertedOrderDate SMALLDATETIME

IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7

BEGIN

SELECT @InsertedOrderDate = NULL

PRINT 'Invalid Order Date'

PRINT 'Supplied Order Date was greater than 7 days old.'

PRINT 'The value has been reset to NULL'

END

ELSE

BEGIN

SELECT @InsertedOrderDate =

CONVERT(DATETIME, CONVERT(VARCHAR, @OrderDate, 112))

PRINT 'The time of Day in Order Date was truncated'

END

INSERT INTO Orders

VALUES

(

@CustomerID,

@EmployeeID,

@OrderDate,

@RequiredDate,

@ShippedDate,

@ShipVia,

@Freight,

@ShipName,

@ShipAddress,

@ShipCity,

@ShipRegion,

@ShipPostalCode,

@ShipCountry

)

SELECT @OrderID = @@IDENTITY

9.5.2 CASE语句

CASE语句在某种程度上与一些编程语言中的一些不同语句是等价的。例如：

C、C++、Delphi中的switch
Visual Basic中的select case
COBOL中的evaluate

在T-SQL中使用CASE语句的一个很大的缺点是：在很多方面，它更像替换运算符而非流控制语句。

编写CASE语句的方式不只一种——可以使用输入表达式或者布尔表达式。第一种方法是使用一个输入表达式来与每个WHEN子句中用到的值进行比较。SQL Server文档把这种方法称为简单CASE：

CASE <input expression>

WHEN <when expression> THEN <result expression>

[...n]

[ELSE <result expression>]

END

第二种方法将提供一个表达式，其中每个WHEN子句的值将为TRUE或者FALSE。相关文档把它称为搜索CASE：

CASE

WHEN <Boolean expression> THEN <result expression>

[...n]

[ELSE <result expression>]

END

可以使用CASE语句最好的方式是把它与SELECT语句放一起使用。

1. 简单CASE

简单CASE使用结果等于布尔值的表达式。示例：

USE Northwind

SELECT TOP 10 OrderID, OrderID % 10 AS 'Last Digit', Position =

CASE OrderID % 10

WHEN 1 THEN 'First'

WHEN 2 THEN 'Second'

WHEN 3 THEN 'Third'

WHEN 4 THEN 'Fourth'

ELSE 'Something Else'

END

FROM Orders

2. 搜索CASE

搜索CASE语句和简单CASE语句非常相同，它只有两个很细微的不同点：

没有输入表达式。
WHEN表达式必须为布尔值。

示例：

USE Northwind

SELECT TOP 10 OrderID % 10 AS "Last Digit", ProductID, "How Close?" =

CASE

WHEN (OrderID % 10) < 3 THEN 'Ends with less than three'

WHEN ProductID = 6 THEN 'ProductID is 6'

WHEN ABS(OrderID % 10 - ProductID) <= 1 THEN 'Within 1'

ELSE 'More than one apart'

END

FROM OrderDetails

WHERE ProductID < 10

ORDER BY OrderID DESC

注意SQL Server求值的工作方式：

即使两个条件都为真，但只使用第一个条件。
不需要使用"break"语句，在一个条件满足后自动终止。
可以在条件表达式中混合和匹配正在使用的字段。
只要最后等于布尔值的结果，则可以执行任何表达式。

9.5.3 使用WHILE语句循环

语法如下：

WHILE <boolean expression>

<sql statement> |

[BEGIN

[BREAK]

[CONTINUE[

END]

在WHILE语句中必须跟上BEGIN...END，其中包含整个语句块。

9.6 通过返回值确认成功或失败

返回值指示了存储过程的成功或者失败，甚至是成功或失败的范围或属性。

RETURN的工作方式

不管是否提供返回值，程序都会收到一个返回值。SQL Server默认地会在完成存储过程时自动返回0。

为了从存储过程向调用代码返回值，只需要使用RETURN语句：

RETURN [<integer value to return>]

注意：

返回值必须是整数。
RETURN语句是无条件地从存储过程中退出的。

示例：

USE Northwind

CREATE PROC spTestReturns

DECLARE @MyMessage VARCHAR(50)

DECLARE @MyOtherMessage VARCHAR(50)

SELECT @MyMessage = 'Hi, it''s that line before the RETURN'

PRINT @MyMessage

RETURN

SELECT @MyOtherMessage = 'Sorry, but we won''t get this far'

PRINT @MyOtherMessage

RETURN

为了能获取RETURN语句的值，需要在EXEC语句中把值赋给变量。

DECLARE @Return INT

EXEC @Return = spTestReturns

SELECT @Return

直接RETURN会默认返回0，需要返回其他整数可以直接写RETURN <integer>。

第10章用户自定义函数

用户自定义函数和存储过程非常相似，但它们也有一些行为和能力的区别。

10.1用户自定义函数的定义

用户自定义函数（UDF）是有序的T-SQL语句集合，该语句集合能够预先优化和编译，并且可以作为一个单元来调用。它和存储过程的主要区别在于返回结果的方式。为了能支持多种不同的返回值，UDF比存储过程有更多地限制。

可以在使用存储过程的时候传入参数，也可以以参数的形式得到返回值。存储过程可以返回值，不过该值是为了指示成功或失败的，而非返回数据。

然而，可以在使用UDF的时候传入参数，但是可以不传出任何值。UDF还可以返回标量（scalar）值，这个值可以是大部分SQL Server的数据类型。UDF还可以返回表。

按照返回值的类型，UDF有两种类型：

返回标量的UDF
返回表的UDF

10.2 返回标量值的UDF

这种类型的UDF和大多数SQL Server内建的函数一样，会向调用脚本或存储过程返回标量值，例如GETDATE()和USER()函数就会返回标量值。

UDF可以返回除了BLOB、CURSOR和TIMESTAMP以外的任何SQL Server中有效的数据类型（包含用户自定义类型）。如果想返回整数，UDF也和存储过程不同的是：

UDF返回值的目的是提供有意义的数据，而不是说明成功或失败。
在查询中可以内联地执行函数，而使用存储过程则不行。

示例——返回去掉时分秒的日期：

CREATE FUNCTION DayOnly(@Date DATETIME)

RETURNS VARCHAR(12)

BEGIN

RETURN CONVERT(VARCHAR(12), @Date, 101)

END

函数的使用方法如下：

SELECT *

FROM Orders

WHERE DayOnly(OrderDate) = DayOnly(GETDATE())

在一个UDF中调用另一个UDF：

CREATE FUNCTION AveragePrice()

RETURNS MONEY

WITH SCHEMABINDING

BEGIN

RETURN (SELECT AVG(Price) FROM Titles)

END

CREATE FUNCTION PriceDifference(@Price MONEY)

RETURN MONEY

BEGIN

RETURN @Price – AveragePrice()

END

使用UDF可以大大增加查询语句的可读性，并实现了代码重用：

USE pubs

SELECT Title,

Price,

AveragePrice() AS Average,

PriceDifference(Price) AS Difference

FROM Titles

WHERE Type = 'popular_comp'

10.3 返回表的UDF

可以对UDF返回的表执行JOIN，甚至对结果应用WHERE条件。相对简单的函数示例如下：

USE pubs

CREATE FUNCTION fnAuthorList()

RETURN TABLE

RETURN (

SELECT au_id,

au_lname + ', ' + au_fname AS au_name

address AS address1,

city + ', ' + state + ', ' + zip AS address2

FROM authors

)

这样的话，使用这个函数就像使用表一样：

SELECT *

FROM fnAuthorList()

使用返回表的UDF比使用视图的好处在于可以在UDF中将条件参数化，而视图不得不包含不想要的数据，然后再通过WHERE子句过滤。例如：

USE pubs

CREATE FUNCTION fnSalesCount(@SalesQty BIGINT)

RETURNS TABLE

RETURN (

SELECT au.au_id,

au.aulname + ', ' + au.au_fname AS au_name,

au.address AS address1,

city + ', ' + state + ', ' + zip AS address2,

SUM(s.qty) AS SalesCount

FROM authors au

INNER JOIN titleauthor ta

ON au.au_id = ta.au_id

INNER JOIN sales s

ON ta.title_id = s.title_id

GROUP BY au.au_id,

au.au_lname + ', ' + au.au_fname,

au.address,

au.city + ', ' + au.state + ', ' + zip

HAVING SUM(qty) > @SalesQty

)

为了执行该函数，只需要调用它并提供参数：

SELECT *

FROM fnSalesCount(25)

再进一步，如果需要查询每一个销售超过25本书以上的作者和出版社的信息，这需要连接UDF返回的表：

SELECT DISTINCT p.pub_name, a.au_name

FROM dbo.fnSalesCount(25) AS a

INNER JOIN titleauthor AS ta

ON a.au_id = ta.au_id

INNER JOIN titles AS t

ON ta.title_id = t.title_id

INNER JOIN publishers AS p

ON t.pub_id = p.pub_id

这里对函数进行了连接，就好像它是表或视图一样。唯一的区别在于可以对它进行参数化。

再进一步，UDF也可以递归调用，并同样存在最深32层的限制。

第11章事务和锁

11.1 事务

事务是关于原子性（atomicity）的。原子性的概念是指可以把一些东西当作一个单元来看待。

事务要有非常明确的开始和结束点。事实上，在SQL Server中发出的每一个SELECT、INSERT、UPDATE和DELETE语句都是隐式事务的一部分。即使只发出一条语句，也会把这条语句当作一个事务——要么执行语句中的所有内容，要么什么都不执行。确实，这一条语句默认地将作为事务的长度。

关于事务的操作有：

BEGIN事务：设置起始点。
COMMIT事务：使得事务成为数据库中永久的、不可撤回的一部分。
ROLLBACK事务：本质上说想要忘记它曾经发生过。
SAVE事务：创建一个特有的标记符，从而可以做部分的回滚工作。

11.1.1 BEGIN TRAN

语法如下：

BEGIN TRAN|TRANSACTION [<transaction name>|<@transaction variable>]

11.1.2 COMMIT TRAN

事务的提交是完成事务的终点。COMMIT的语法类似于BEGIN：

COMMIT TRAN|TRANSACTION [<transaction name>|<@transaction variable>]

11.1.3 ROLLBACK TRAN

ROLLBACK可以回到开始的地方或者其中的任何一个保存点。ROLLBACK的语法如下：

ROLLBACK TRAN|TRANSACTION [<transaction name> | <save point name> | <@transaction variable> | <@savepoint variable>]

11.1.4 SAVE TRAN

保存事务从本质上说是创建书签。在建立"书签"之后，可以在回滚中引用它。它的好处是可以回滚到代码中想要的点上。SAVE的语法如下：

SAVE TRAN|TRANSACTION [<save point name>|<@savepoint variable>]

关于保存点需要记住的是ROLLBACK会清除它们——执行ROLLBACK后之前保存过的保存点都会消失。

11.2 SQL Server记录日志的工作方式

在数据库的正常操作中，大多数执行的活动都是"记录"在事务日志上，而非直接写入数据库中。检查点是指强制地把数据库现在所使用的脏页写入磁盘的周期性操作。脏页是指日志或数据页，它们在读入到缓存后已经被修改，但是所进行的修改还没有写入到磁盘。

11.2.1 失败和恢复

恢复发生在SQL Server每次启动的时候。SQL Server获得数据库文件，并且在最后的检查点以后应用日志中的任何提交的改变。日志中任何没有对应提交的改变都会回滚。

第12章触发器

一些常见的使用触发器的情况包括：

实施参照完整性，例如数据库或服务器中的参照完整性以及许多复杂的关系类型。
创建审计跟踪。
与CHECK约束的功能相似，但是用于表、数据库、甚至是服务器之间。
用自己的语句代替用户的操作语句（通常用于允许复杂语句中的插入操作）。

12.1 触发器的概念

触发器是一种特殊类型的存储过程，响应特定的事件。有两种类型的触发器：数据定义语言（DDL）触发器和数据操作语言（DML）触发器。

DDL触发器激活了人们以某些方式（CREATE、ALTER、DROP等）对数据库结构进行修改的响应。DML触发器是一些加在特殊表或试图上的代码片段。只要加在触发器上的事件在表中发生，触发器中的代码就会自动地运行。不能显式地调用触发器——唯一的做法是执行指派给表所需的操作。触发器也没有参数和返回值，因为都不需要。

在SQL Server中可以使用3种类型的触发器，并可以相互混合和匹配：

INSERT触发器
DELETE触发器
UPDATE触发器

注意，有些语句不会激活触发器，比如TRUNCATE TABLE有与DELETE语句相似的删除行的效果，但是不会触发任何DELETE触发器。

除了触发器需要加在一个表上外，创建触发器的语法类似于其他CREATE语法：

CREATE TRIGGER <trigger name>

ON [<schema name>.]<table or view name>

[WITH ENCRYPTION]

{{{FOR | ALTER} [DELETE] [,] [INSERT} [,] [UPDATE]}}

12.1.1 ON子句

对创建触发器的对象进行命名。注意如果触发器的类型是AFTER（或FOR）触发器，那么ON字句的目标就必须是一个表（而不能是视图），视图只接受INSTEAD OF触发器。

12.1.2 WITH ENCRYPTION子句

加密触发器代码。注意ALTER语句不会自动加密，如需加密需要再次指明WITH ENCRYPTION选项。

12.1 3 FOR|AFTER子句

还需要对激活触发器的定时时间做出选择。虽然可以使用长期接触的FOR触发器（也可以使用关键字ATFER来替换），而且这也是人们经常考虑的一种触发器，但是也可以使用INSTEAD OF触发器。对这两种触发器的选择将影响到是在修改数据之前还是之后来输入触发器。

SQL Server会将两张表放在一起——其中的INSERTED表保存插入记录的副本，另一张DELETED表保存删除的任何记录的副本。使用INSTEAD OF触发器，创建这两张工作表是发生在检查任何约束之前，而使用FOR触发器，这些表的创建是发生在检查约束之后。使用INSTEAD OF触发器的重点在于可以在视图中清除任何不确定的插入问题。这也意味着在检查约束之前可以采取运动清除违反约束的情况。

使用FOR或ATFER声明的触发器，与INSTEAD OF触发器最大的区别在于它们是在检查完约束之后建立工作表的。

FOR（AFTER）子句指明了想要在哪种动作下激活触发器。例如：

FOR INSERT, DELETE

注意之前提到过，使用FOR或AFTER子句声明的触发器只能加在表上，而不允许加在视图上。

1. INSERT触发器

每当有人向表中插入全新的数据行的时候，都会执行在代码中通过FOR INSERT标记声明的触发器的代码。对于插入的每一行来说，SQL Server会创建该新行的副本并把它插入到称为INSERTED的表中，该表只在触发器的作用域内存在。

2. DELETE触发器

每个删除的额记录的副本将插入到成为DELETED表中，该表同样只在触发器的作用域内存在。

3. UPDATE触发器

SQL Server会把每一行当作先删除了现有的记录，并插入了全新的行，所以INSERTED和DELETED表均存在。当然，这两个表会有完全相同数量的数据行。而DELETED表中的为改变前的数据，INSERTED表中为改变后的数据。

12.2 为了数据完整性规则使用触发器

触发器可以完成CHECK约束和DEFAULT约束一样的功能，但可以使用CHECK约束和DEFAULT约束完成的功能不应该再设置触发器。但触发器还是可以完成更多的功能：

业务规则需要引用单个表中的数据。
业务规则需要检查更新的增量（更新前后的区别）。
需要一个定制的错误信息。

12.2.1 处理来自于其他表的需求

例如，客户支持部门的人员不断发出已经停止供应的产品的订单，应该在订单进入系统之前拒绝这些订单的录入。

CREATE TRIGGER OrderDetailNotDiscontinued

ON OrderDetails

FOR INSERT, UPDATE

IF EXISTS (

SELECT 'TRUE'

FROM Inserted i

INNER JOIN Products p

ON i.ProductID = p.ProductID

WHERE p.Discontinued = 1)

BEGIN

PAISERROR('Order Item is discontinued. Transaction Failed.', 16, 1)

ROLLBACK TRAN

END

12.2.2 使用触发器来检查更新的增量

例如，Northwind的存货部门要求不能发出任何销售某个产品超过其一般库存单位的订单。

CREATE TRIGGER ProductIsRationed

ON Products

FOR UPDATE

IF EXISTS (

SELECT 'TRUE'

FROM Inserted i

INNER JOIN Deleted d

ON i.ProductID = d.ProductID

WHERE (d.UnitsInStock – i.UnitsInStock) > d.UnitsInStock / 2

AND d.UnitsInStock – i.UnitsInStock > 0

)

BEGIN

RAISERROR('Cannot reduce stock by more than 50%% at onece.', 16, 1)

ROLLBACK TRAN

END

12.3 可以关闭触发器而不删除它

可以使用ALTER TABLE语句来打开或关闭触发器，语法如下：

ALTER TABLE <table name>

{ENABLE|DISABLE} TRIGGER {ALL|<trigger name>}

12.4 删除触发器

和删除其他对象一样：

DROP TRIGGER <trigger name>

转载地址为http://www.cnblogs.com/qwertWZ/archive/2013/05/05/3061743.html

hehe6378

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
《SQL Server 2005 编程入门经典》学习笔记

第一章 RDBMS基础：SQL Server数据库构成数据库中的对象数据库管理系统中包含许多对象。对于SQL Server，它常包含以下重要的数据库对象：数据库索引事务日志程序集表报表文件集全文本目录图表用户自定义数据类型视图角色存储过程用户用户自定义函数 SQL Server的4个系统数据库在给定的SQL Server中，数据库实际
复制链接

扫一扫

专栏目录