数据库编程（简易）

来源：互联网发布：京东和淘宝哪个好编辑：程序博客网时间：2024/05/18 02:55

（节选来源：百度文库，基于SQL Server 2000）

第2章 Transact-SQL语言

2.1 T-SQL 语言概述

SQL（structured query language，sequel）的特征：面向集合的语言；非过程性语言；集DDL、DML、DCL语言于一体；包括查询（query）、操纵(manipulation)、定义(definition)和控制(control)四方面功能；两种使用方式：可独立运行，也可嵌入到其它高级语言中。

T-SQL编程语言是对SQL的扩展，是编程语言，可实现数据查询和数据库管理。

T-SQL的语言元素：数据控制语言DCL（grant/deny/revoke）；数据定义语言DDL(create/alter/drop)；数据操纵语言DML(select/update/delete)。

数据库对象由其名称(标识符)标识。标识符分常规标识符和分隔标识符。常规标识符的命名规则如下：

（1）不能超过128个字符

（2）第一个字符必须是字母

（3）以@开头的表示局部变量或参数

（4）以@ @开头的表示全部变量

（5）以#开头的表示临时表或过程

（6）以##开头的表示全局临时变量

（7）后续字符可以字母、下划线、数字、@$#_符号

（8）标识符不能是T-SQL保留字

（9）不允许嵌入空格或其它特殊字符

T-SQL的其它语言要素有局部变量、全局变量、释符、运算符、通配符、流程控制语句、批处理、调试语句、切换数据库命令等。

2.2 数据类型

一、系统提供的数据类型

1、逻辑类型bit

SQL Server中的位（bit）型数据相当于其他语言中的逻辑型数据，它只存储0和1，长度为一个字节。

2、整数型

整数包括bigint、int、smallint和tinyint，从标识符的含义就可以看出，它们的表示数范围逐渐缩小。

bigint：大整数，其精度为19，小数位数为0，长度为8字节，数据范围为 -2⁶³ (-9223372036854775808)～2⁶³-1(9223372036854775807)。

int：整数，其精度为10，小数位数为0，长度为4字节。数据范围为 -2³¹ (-2,147,483,648) ～2³¹ -1(2,147,483,647)。

smallint：短整数，其精度为5，小数位数为0，长度为2字节，数据数范围为 -2¹⁵ (-32768) ～2¹⁵ -1 (32767)。

tinyint：微短整数，其精度为3，小数位数为0，长度为1字节，数据范围为 0～255。

3、精确整数型

decimal和numeric可存储从-10³⁸ +1 到 10³⁸–1 的固定精度和小数位的数字数据，它们的存储长度随精度变化而变化，最少为5字节，最多为17字节。

精度为1～9时，存储字节长度为5；精度为10～19时，存储字节长度为9；精度为20～28时，存储字节长度为13；精度为29～38时，存储字节长度为17。

4、浮点型

float[(n)]：n 为用于存储科学记数法float数尾数的位数，同时指示其精度和存储大小。定义中的n取值范围是1～53，用于指示其精度和存储大小。数据范围为-1.79E +308 到 1.79E+308。n为1~24时4字节，n为25~53为8字节。

real：使用4字节存储数据，数据精度为7位有效数字，数据范围为-3.40E+38 到3.40E +38。相当于float(24)。

5、货币型

Ÿmoney：其精度为19，小数位数为4，长度为8字节，数据的数范围为-2⁶³ (-922337203685477.5808)～2⁶³-1 (922337203685477.5807)。

Ÿsmallmoney：其精度为10，小数位数为4，长度为4字节，数范围为–2³¹ (-2,147,48.3648) ～2³¹ - 1 (2,147,48.3647)。

6、字符型

字符型数据用于存储字符串，字符串中可包括字母、数字和其它特殊符号（如#、@、&等等）。 SQL Server字符型包括两类：固定长度(char)或可变长度(varchar)字符数据类型。

char[(n)]：长度为 n 个字节的固定长度且非 Unicode 的字符数据。n必须是一个介于 1 和 8,000 之间的数值。存储大小为n 个字节。

varchar[(n)]：长度为 n 个字节的可变长度且非 Unicode 的字符数据。n必须是一个介于 1 和 8,000 之间的数值。存储大小为输入数据的字节的实际长度，而不是n个字节。所输入的数据字符长度可以为零。

7、Unicode字符型

Unicode是“统一字符编码标准”，用于支持国际上非英语语种的字符数据的存储和处理。

nchar[(n)]：nchar[(n)]为包含n个字符的固定长度 Unicode 字符型数据，n 的值在 1 与 4,000 之间，缺省为1。长度为2n字节。若输入的字符串长度不足n，将以空白字符补足。

nvarchar[(n)]：nvarchar[(n)]为最多包含n个字符的可变长度 Unicode 字符型数据，n 的值在 1 与 4,000之间，缺省为1。

8、文本型

文本型包括text和ntext两类，分别对应ASCII字符和Unicode字符。text类型可以表示最大长度为 2³¹-1 (2,147,483,647) 个字符，其数据的存储长度为实际字符数个字节。

ntext可表示最大长度为 2³⁰ - 1 (1,073,741,823) 个Unicode字符，其数据的存储长度是实际字符个数的两倍（以字节为单位）。

9、二进制型

binary[(n)]：固定长度的n个字节二进制数据。n取值范围为 1 到 8,000，缺省为1。存储空间大小为 n+4 字节。

varbinary[(n)]：n个字节变长二进制数据。n取值范围为 1 到 8,000，缺省为1。存储长度为实际输入数据长度+4个字节。

Image型：最大可达2³¹-1

10、日期时间类型

datetime类型数据长度为8字节，日期和时间分别使用4个字节存储，从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确到百分之三秒（或 3.33 毫秒）。

smalldatetime类型数据长度为4字节，日期和时间分别使用2个字节存储，从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据，精确到分钟。

11、时间戳型

标识符是timestamp。若创建表时定义一个列的数据类型为时间戳类型，那么每当对该表加入新行或修改已有行时，都由系统自动将一个计数器值加到该列，即将原来的时间戳值加上一个增量。

二、用户定义数据类型

1、定义：sp_addtype 类型名，系统类型，是否空，拥有者

用户定义的数据类型名称在数据库中必须是唯一的，但是名称不同的用户定义的数据类型可以有相同的定义。

除非将 master 作为当前数据库来执行 sp_addtype，否则，执行 sp_addtype 会创建用户定义的数据类型，并且将其添加到某个特定数据库的 systypes 系统表中。如果想在所有新的用户定义的数据库中都可以使用这一用户定义的数据类型，请将其添加到 model。示例如下：

（1）创建不允许空值的用户定义数据类型

下面的示例创建一个名为 ssn（社会保险号）的用户定义数据类型，它基于 SQL Server 提供的 varchar 数据类型。ssn 数据类型用于那些保存 11 位数字的社会保险号 (999-99-9999) 的列。该列不能为 NULL。请注意，varchar(11) 由单引号引了起来，这是因为它包含了标点符号（圆括号）。

USE master

EXEC sp_addtype ssn, 'VARCHAR(11)', 'NOT NULL'

（2）创建允许空值的用户定义数据类型

下面的示例创建了一个名为 birthday 的用户定义数据类型（基于 datetime），该数据类型允许空值。

USE master

EXEC sp_addtype birthday, datetime, 'NULL'

（3）创建另外的用户定义的数据类型

下面的示例为国内及国际电话和传真号码另外创建两个用户定义的数据类型 telephone和 fax。

USE master

EXEC sp_addtype telephone, 'varchar(24)', 'NOT NULL'

EXEC sp_addtype fax, 'varchar(24)', 'NULL'

2、删除：sp_droptype 类型名

USE master

EXEC sp_droptype ' fax '

3、使用方法：（与系统类型使用方法一样）

2.3 变量

一、局部变量

1、声明：declare @variable_name datatype [, @variable_name datatype]

2、赋值：set @variable_name =expression

或 select @variable_name =expression [, @variable_name =expression ]

DECLARE @pub_id char(4), @hire_date datetime

SET @pub_id = '0877'

SET @hire_date = '1/01/93'

SELECT @pub_id , @hire_date

二、全局变量

全局变量是由系统提供且预先声明的变量，通过在名称前保留两个 (@@) 符号区别于局部变量。例如@@ERROR，@@ROWCOUNT，@@VERSION。

@@ERROR返回最后执行的 Transact-SQL 语句的错误代码如果语句执行成功，则 @@ERROR 设置为 0。@@ROWCOUNT返回受上一语句影响的行数，任何不返回行的语句将这一变量设置为 0 ，如 IF 语句。@@VERSION返回 Microsoft® SQL Server™ 当前安装的日期、版本和处理器类型。例如：

USE pubs

UPDATE authors SET au_id = '172 32 1176' WHERE au_id = '172-32-1176'

IF @@ERROR = 547 print 'A check constraint violation occurred'

UPDATE authors SET au_lname = 'Jones' WHERE au_id = '999-888-7777'

IF @@ROWCOUNT = 0 print 'Warning: No rows were updated'

2.4 注释符、运算符与通配符

一、注释符

行内注释：“--”; 块注释：“/* */”

二、运算符

1、算术运算符

算术运算符有：+（加）、-（减）、*（乘）、/（除）和%（求模）五种运算。+ (加) 和–(减) 运算符也可用于对 datetime 及 smalldatetime 值进行算术运算。

2、赋值运算符

指给局部变量赋值的SET和SELECT语句中使用的“=”。

3、位运算符

位运算符在两个表达式之间执行位操作，这两个表达式的类型可为整型或与整型兼容的数据类型（如：字符型，等，但不能为 image 类型），位运算符如表所示。

4、比较运算符

比较运算符(又称关系运算符)，用于测试两个表达式的值是否相同，其运算结果为逻辑值，可以为三种之一：TRUE、FALSE、UNKNOWN。其结果作为条件使用，不能直接输出。

5、字符串联接运算符

通过运算符“+”实现两个字符串的联接运算。

6、逻辑运算符

逻辑运算符用于对某个条件进行测试，运算结果为 TRUE 或 FALSE。

7、一元运算符

一元运算符有+（正）、-（负）和~（按位取反）三个。+、-运算符是大家熟悉的。对于按位取反运算符举例如下：

设a的值为12（0000 0000 0000 1100），计算：~a的值为：1111 1111 1111 0011

运算符有优先等级。在较低等级的运算符之前先对较高等级的运算符进行求值。当一个表达式中的两个运算符有相同的运算符优先等级时，基于它们在表达式中的位置来对其从左到右进行求值。运算符优先等级由高到低如下：

+（正）、-（负）、~（按位 NOT）

*（乘）、/（除）、%（模）

+（加）、（+ 串联）、-（减）

=, >, <, >=, <=, <>, !=, !>, !< 比较运算符

^（位异或）、&（位与）、|（位或）

NOT

AND

ALL、ANY、BETWEEN、IN、LIKE、OR、SOME

=（赋值）

三、通配符

通配符

描述

示例

包含零个或更多字符的任意字符串。

WHERE title LIKE '%computer%' 将查找处于书名任意位置的包含单词 computer 的所有书名。

_（下划线）

任何单个字符。

WHERE au_fname LIKE '_ean' 将查找以 ean 结尾的所有 4 个字母的名字（Dean、Sean 等）。

[ ]

指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符。

WHERE au_lname LIKE '[C-P]arsen' 将查找以arsen 结尾且以介于 C 与 P 之间的任何单个字符开始的作者姓氏，例如，Carsen、Larsen、Karsen 等。

[^]

不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。

WHERE au_lname LIKE 'de[^l]%' 将查找以 de 开始且其后的字母不为 l 的所有作者的姓氏。

2.5 流程控制语句

Transact-SQL 提供称为控制流语言的特殊关键字，用于控制 Transact-SQL 语句、语句块和存储过程的执行流。这些关键字可用于 Transact-SQL 语句、批处理和存储过程中。不使用控制流语言，则各 Transact-SQL 语句按其出现的顺序分别执行。控制流语言使用与程序设计相似的构造使语句得以互相连接、关联和相互依存。当您需要 Transact-SQL 进行某种操作时，这些控制流关键字非常有用。例如，当在一个逻辑块中包含多个 Transact-SQL 语句时，请使用 BEGIN...END 语句对。使用 IF...ELSE 语句对的情况是：IF（如果）满足某条件，则执行某些语句或语句块，而如果不满足此条件（ELSE 条件）则执行另一条语句或语句块。控制流语句不能跨越多个批处理或存储过程。以下是控制流关键字。

BEGIN...END

WAITFOR

GOTO

WHILE

IF...ELSE

BREAK

RETURN

CONTINUE

一、BEGIN...END

相当于C语言中的“{}”，多条语句用BEGIN...END构成为一条语句。

IF (@@ERROR <> 0)

BEGIN

SET @ErrorSaveVariable = @@ERROR

PRINT 'Error encountered, ' + CAST(@ErrorSaveVariable AS VARCHAR(10))

END

二、IF...ELSE

根据条件选择语句执行。

(1) IF <条件表达式>

<T-SQL语句(块)1>

ELSE

<T-SQL语句(块)2>

(2) IF 条件表达式

<T-SQL语句(块)>

三、GOTO

GOTO 语句使 Transact-SQL批处理的执行跳转到标签。不执行 GOTO 语句和标签之间的语句。尽量少使用 GOTO 语句。使用下列语法定义标签名：

label_name:

四、RETURN

RETURN 语句无条件终止查询、存储过程或批处理。存储过程或批处理中 RETURN 语句后面的语句都不执行。

当在存储过程中使用 RETURN 语句时，此语句可以指定返回给调用应用程序、批处理或过程的整数值。如果 RETURN 未指定值，则存储过程返回 0。大多数存储过程按常规使用返回代码表示存储过程的成功或失败。没有发生错误时存储过程返回值 0。任何非零值表示有错误发生。

五、WHILE

设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。语法如下：

WHILE <条件>

[BEGIN]

[ BREAK ]

[ CONTINUE ]

[END]

WHILE循环示例：如果平均价格少于 $30，WHILE 循环就将价格加倍，然后选择最高价。如果最高价少于或等于$50，WHILE 循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过$50，然后退出 WHILE 循环并打印一条消息。

USE pubs

WHILE (SELECT AVG(price) FROM titles) < $30

BEGIN

UPDATE titles SET price = price * 2

SELECT MAX(price) FROM titles

IF (SELECT MAX(price) FROM titles) > $50

BREAK

ELSE

CONTINUE

END

PRINT 'Too much for the market to bear'

六、WAITFOR

指定触发语句块、存储过程或事务执行的时刻、或需等待的时间间隔。

语法格式：WAITFOR { DELAY 'time' | TIME 'time' }

说明：DELAY 'time'：用于指定SQL Server必须等待的时间，最长可达 24 小时，time可以用datetime 数据格式指定，用单引号括起来，但在值中不允许有日期部分，也可以用局部变量指定参数。TIME 'time'：指定SQL Server 等待到某一时刻， time值的指定同上。

执行 WAITFOR 语句后，在到达指定的时间之前将无法使用与 SQL Server 的连接。若要查看活动的进程和正在等待的进程，使用sp_who。

如下语句设定在早上八点执行存储过程sp_addrole。

BEGIN

WAITFOR TIME '8:00'

EXECUTE sp_addrole ‘Managers’

END

七、CASE

计算条件列表并返回多个可能结果表达式之一。 CASE 具有两种格式：

1、简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。

2、CASE 搜索函数计算一组布尔表达式以确定结果。

两种格式都支持可选的 ELSE 参数。

(1)简单的 CASE 函数

CASE input_expression

WHEN when_expression THEN result_expression

[ ...n ]

[ ELSE else_result_expression]

END

在 SELECT 语句中，简单 CASE 函数仅检查是否相等，而不进行其它比较。下面的示例使用 CASE 函数更改图书分类显示，以使其更易于理解。

USE pubs

SELECT Category =

CASE type

WHEN 'popular_comp' THEN 'Popular Computing'

WHEN 'mod_cook' THEN 'Modern Cooking'

WHEN 'business' THEN 'Business'

WHEN 'psychology' THEN 'Psychology'

WHEN 'trad_cook' THEN 'Traditional Cooking'

ELSE 'Not yet categorized'

END,

CAST(title AS varchar(25)) AS 'Shortened Title',

price AS Price

FROM titles WHERE price IS NOT NULL ORDER BY type, price

(2)CASE 搜索函数：

CASE

WHEN Boolean_expression THEN result_expression

[ ...n ]

[ ELSE else_result_expression]

END

在 SELECT 语句中，CASE 搜索函数允许根据比较值在结果集内对值进行替换。下面的示例根据图书的价格范围将价格（money 列）显示为文本注释。

USE pubs

SELECT 'Price Category' =

CASE

WHEN price IS NULL THEN 'Not yet priced'

WHEN price < 10 THEN 'Very Reasonable Title'

WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'

ELSE 'Expensive book!'

END,

CAST(title AS varchar(20)) AS 'Shortened Title'

FROM titles ORDER BY price

2.6 其它命令

一、GO

GO命令用信号通知 Microsoft® SQL Server™ 实用工具一批 Transact-SQL 语句的结束。GO 不是 Transact-SQL 语句,GO 命令和Transact-SQL 语句不可在同一行上。注意：

1、不是所有语句都可以组合在一个批处理中。如create procedure/create rule/create default /create trigger/create view

2、规则和默认不能在同一个批中既绑定到列又使用

3、check约束不能在同一个批中既定义又使用

4、在同一批中不能既删除对象又重建

5、set语句改变的选项在批处理结束时生效

6、同一批中不能改变一个表再立即引用新列

USE pubs

DECLARE @MyMsg VARCHAR(50)

SELECT @MyMsg = 'Hello, World.'

PRINT @MyMsg

SELECT @@VERSION;

sp_who

二、调试命令

1、PRINT语句

PRINT <字符串>

例：IF EXISTS (SELECT zip FROM authors WHERE zip = '94705') PRINT 'Berkeley author'

PRINT 'This message was printed on ' + RTRIM(CONVERT(varchar(30), GETDATE())) + '.'

2、RAISERROR语句

RAISERROR ( { msg_id | msg_str } { , severity , state }[ , argument [ ,...n ] ] )

参数说明：

msg_id

存储于 sysmessages 表中的用户定义的错误信息。用户定义错误信息的错误号应大于 50,000。由特殊消息产生的错误是第 50,000 号。

msg_str

是一条特殊消息，其格式与 C 语言中使用的 PRINTF 格式样式相似。此错误信息最多可包含 400 个字符。如果该信息包含的字符超过 400 个，则只能显示前 397 个并将添加一个省略号以表示该信息已被截断。所有特定消息的标准消息ID 是14,000。

severity

用户定义的与消息关联的严重级别。用户可以使用从 0 到 18 之间的严重级别。19 到 25 之间的严重级别只能由 sysadmin 固定服务器角色成员使用。若要使用 19 到 25 之间的严重级别，必须选择 WITH LOG 选项。

注意：20 到 25 之间的严重级别被认为是致命的。如果遇到致命的严重级别，客户端连接将在收到消息后终止，并将错误记入错误日志和应用程序日志。

state

从 1 到 127 的任意整数，表示有关错误调用状态的信息。state的负值默认为 1。

argument

是用于取代在 msg_str 中定义的变量或取代对应于 msg_id 的消息的参数。可以有 0 或更多的替代参数；然而，替代参数的总数不能超过 20 个。每个替代参数可以是局部变量或这些任意数据类型：int1、int2、int4、char、varchar、binary 或 varbinary。不支持其它数据类型。

下面的示例在返回给应用程序的消息中替换了 DB_ID 和 DB_NAME 函数的值：

DECLARE @DBID INT

SET @DBID = DB_ID()

DECLARE @DBNAME NVARCHAR(128)

SET @DBNAME = DB_NAME()

RAISERROR('The current database ID is:%d, the database name is: %s.', 16, 1, @DBID, @DBNAME)

替换参数可以在由用户定义的错误中指定，并且当 RAISERROR 语句执行时由替换参变量填充。

sp_addmessage 50001, 16,

'The current database ID is:%d, the database name is: %s.','us_english'

DECLARE @DBID INT

SET @DBID = DB_ID()

DECLARE @DBNAME NVARCHAR(128)

SET @DBNAME = DB_NAME()

RAISERROR (50001, 16, 1, @DBID, @DBNAME)

2.7 常用内置函数

下面的使用的示例数据库是pubs。

一、聚合函数(AVG, SUM, COUNT,MAX,MIN)

SELECT AVG(advance), SUM(ytd_sales), MAX(price),MIN(price), AVG(DISTINCT price), COUNT(*), COUNT(DISTINCT price) FROM titles WHERE type = 'business'

二、数学函数(ABS, CEILING, FLOOR, POWER, SQRT,ROUND)

SELECT ABS(-1.0), CEILING(12345.67), FLOOR(12345.67), POWER(2.0, 3.0), SQRT(2.0)

SELECT ROUND(748.58, 1),ROUND(748.58, 2),ROUND(748.58, -1),ROUND(748.58, -2),

ROUND(748.58, -3)

三、字符串函数(Char,charindex,left,len, lower,ltrim,right,rtrim,upper)

例、下面的示例将打印字符串"New Moon"中每个字符的 ASCII 值和字符。

DECLARE @position int, @string char(8)

SET @position = 1

SET @string = 'New Moon'

WHILE @position <= DATALENGTH(@string) – DATALENGTH求内存字节数

BEGIN

SELECT ASCII(SUBSTRING(@string, @position, 1)),

CHAR(ASCII(SUBSTRING(@string, @position, 1)))

SET @position = @position + 1

END

例、CHARINDEX示例

SELECT CHARINDEX('wonderful', notes), CHARINDEX('wonderful', notes, 5)

FROM titles

WHERE title_id = 'TC3218'

四、日期函数(Dateadd,datediff,datepart,day,getdate,month,year)

DATEADD ( datepart , number, date )

DATEDIFF ( datepart , startdate , enddate )

DATEPART ( datepart , date )

Datepart参数说明：是规定了应在日期的哪一部分计算差额的参数。下表列出了 Microsoft® SQL Server™ 识别的日期部分和缩写。

日期部分

缩写

year

yy, yyyy

quarter

qq, q

Month

mm, m

dayofyear

dy, y

Day

dd, d

Week

wk, ww

Hour

minute

mi, n

second

ss, s

millisecond

例、日期函数。

SELECT DATEADD(day, 21, pubdate), DATEDIFF(day, pubdate, getdate()),

DATEPART(month, GETDATE()) FROM titles

五、转换函数（Cast，Convert）

（1）Cast (expression as data_type)

（2）Convert(data_type[(length)],expression[,style]

例、转换函数。

SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales FROM titles

WHERE CAST(ytd_sales AS char(20)) LIKE '3%'

-- WHERE CONVERT(char(20), ytd_sales) LIKE '3%'

六、系统函数（Current_user,datalength, host_name, system_user, user_name）

Datalength ( expression )：返回任何表达式所占用的字节数。

ISNULL ( check_expression , replacement_value )：

check_expression将被检查是否为 NULL的表达式。check_expression 可以是任何类型的。replacement_value在check_expression 为 NULL时将返回的表达式。replacement_value必须与check_expresssion 具有相同的类型。

2.8 用户定义函数

一、概述

编程语言中的函数是用于封装经常执行的逻辑的子例程。任何代码若必须执行函数所包含的逻辑，都可以调用该函数，而不必重复所有的函数逻辑。Microsoft® SQL Server™ 2000 支持两种函数类型：内置函数和用户定义函数。

用户定义函数是用 CREATE FUNCTION 语句定义自己的 Transact-SQL 函数。用户定义函数接受零个或更多的输入参数，并返回单值。一些用户定义函数返回单个的标量数据值，如 int、char 或 decimal 值。用户定义函数用 ALTER FUNCTION 修改，用 DROP FUNCTION 除去。

例如，下面的语句创建一个返回 decimal 的简单函数：

CREATE FUNCTION CubicVolume(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),

@CubeHeight decimal(4,1) )

RETURNS decimal(12,3)

BEGIN

RETURN ( @CubeLength * @CubeWidth * @CubeHeight )

END

然后可以在允许整型表达式的任何地方（如表的计算列中）使用该函数：

CREATE TABLE Bricks(

BrickPartNmbr int PRIMARY KEY,

BrickColor nchar(20),

BrickHeight decimal(4,1),

BrickLength decimal(4,1),

BrickWidth decimal(4,1),

BrickVolume AS dbo.CubicVolume(BrickHeight,BrickLength, BrickWidth)

)

SQL Server 2000 还支持返回 table 数据类型的用户定义函数：该函数可声明内部 table 变量，将行插入该变量，然后将该变量作为返回值返回；一类称为内嵌函数的用户定义函数，将 SELECT 语句的结果集作为变量类型 table 返回。这些函数可用在能指定表表达式的地方。返回 table 的用户定义函数可以是替代视图的强大方式。返回 table 的用户定义函数可用在Transact-SQL 查询中允许表或视图表达式的地方。视图受限于单个 SELECT 语句，而用户定义函数可包含附加的语句，使函数所包含的逻辑比视图可能具有的逻辑更强大。

返回 table 的用户定义函数还可替换返回单个结果集的存储过程。由用户定义函数返回的 table 可在 Transact-SQL 语句的 FROM 子句中引用，而返回结果集的存储过程则不能。例如，fn_EmployeesInDept 是返回 table 的用户定义函数，可由SELECT 语句唤醒调用：

SELECT * FROM tb_Employees AS E, dbo.fn_EmployeesInDept('shipping') AS EID

WHERE E.EmployeeID = EID.EmployeeID

下面的语句在 Northwind 数据库中创建将要返回 table 的函数：

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )

RETURNS @OrderShipperTab TABLE(

ShipperID int,

ShipperName nvarchar(80),

OrderID int,

ShippedDate datetime,

Freight money

)

BEGIN

INSERT @OrderShipperTab

SELECT S.ShipperID, S.CompanyName, O.OrderID, O.ShippedDate, O.Freight

FROM Shippers AS S INNER JOIN Orders AS O ON (S.ShipperID = O.ShipVia)

WHERE O.Freight > @FreightParm

RETURN

END

在这个函数中，返回的本地变量名是@OrderShipperTab。函数中的语句在 @OrderShipperTab 变量中插入行，以生成由该函数返回的 table 结果。外部语句唤醒调用该函数以引用由该函数返回的 table：SELECT * FROM LargeOrderShippers( $500 )。

在返回 table 的用户定义函数中：

•RETURNS 子句为函数返回的表定义局部返回变量名。RETURNS 子句还定义表的格式。局部返回变量名的作用域位于函数内。

•函数主体中的 Transact-SQL 语句生成行并将其插入 RETURNS 子句所定义的返回变量。

•当执行 RETURN 语句时，插入变量的行以函数的表格格式输出形式返回。RETURN 语句不能有参数。

函数中返回 table 的 Transact-SQL 语句不能直接将结果集返回用户。函数返回用户的唯一信息是由该函数返回的 table。

与系统函数一样，用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE 语句执行。

二、创建

1、标量函数

CREATE FUNCTION [ owner_name.] function_name

([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS scalar_return_data_type

[ WITH < function_option> [ [,] ...n] ]

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END

2、内嵌表值函数

CREATE FUNCTION [ owner_name.] function_name

([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS TABLE

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

RETURN [ ( ] select-stmt [ ) ]

3、多语句表值函数

CREATE FUNCTION [ owner_name.] function_name

([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS @return_variable TABLE < table_type_definition >

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

BEGIN

function_body

RETURN

END

< function_option > ::=

{ ENCRYPTION | SCHEMABINDING }

< table_type_definition > ::=

( { column_definition | table_constraint } [ ,...n ] )

参数说明具体见帮助文档。

示例（use booksys）如下：

A.标量函数

create function funCount(@age int=19)

returns int

begin

declare @res int

select @res=count(*) from reader where Age=@age

return @res

end

select dbo.funCount(default)

B.内嵌表值函数

内嵌表值函数可用于实现参数化视图。

借阅视图borrowInfo定义：

create view borrowInfo as

select reader.readerNo,readerName, book.bookNo, bookName, BDate, RDate

from reader,book,borrow

where reader.readerNo=borrow.readerNo and book.bookNo=borrow.bookNo and

reader.readerNo='r1' and book.bookNo='b1'

借阅函数funBorrow定义：

create function funBorrow(@rno varchar(10), @bno varchar(10))

returns table

return (select reader.readerNo,readerName, book.bookNo, bookName, BDate, RDate

from reader,book,borrow

where reader.readerNo=borrow.readerNo and book.bookNo=borrow.bookNo and

reader.readerNo=@rno and book.bookNo=@bno)

select * from funBorrow('r1', 'b1')—调用

C.多语句表值函数

若想得到不同出版社的图书总数和最高价格，显示如下，该如何？

建立多语句表值函数是个好的选择。

create function funCountBook(@pub varchar(10))

returns @result table(des varchar(20),res int)

begin

insert @result select '图书总数',count(*) from book where pubNo=@pub

insert @result select '最高价格',max(price) from book where pubNo=@pub

return

end

select * from funCountBook('p0')

三、修改

语法上只要把CREATE FUNCTION中的CREATE改为ALTER即可。

四、删除

使用DROP FUNCTION从当前数据库中删除一个或多个用户定义的函数。语法为：

DROP FUNCTION { [ owner_name .] function_name } [ ,...n ]

例：drop function funCountBook --删除上面定义的函数funCountBook

作业

1、定义一种新的数据类型，名称为：mid, 长度为8的字符串型，不允许为空。

2、自定义一个函数，返回学生表中男生的记录数。

3、自定义一个函数，返回学生表中男生的记录。

4、自定义一个多语句表值函数，返回的表包含有学生学号、姓名、总分、平均分字段。

第三章管理数据库

3.1 系统表与系统数据库介绍

数据库的两种类型：系统数据库、用户数据库。系统数据库有：master，model，tempdb，msdb；系统另带有pubs 和 northwind两个用户数据库样本。

一、数据库系统表

所有的数据库都有系统表。这些表为每个数据库存储数据库级系统信息。每个数据库中都有的系统表如下：

syscolumns

sysindexkeys

syscomments

sysmembers

sysconstraints

sysobjects

sysdepends

syspermissions

sysfilegroups

sysprotects

sysfiles

sysreferences

sysforeignkeys

systypes

sysfulltextcatalogs

sysusers

sysindexes

sysobjects表，在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行。只有在 tempdb 内，每个临时对象才在该表中占一行。常用字段如下：

列名

数据类型

描述

name

sysname

对象名。

int

对象标识号。

uid

smallint

所有者对象的用户 ID。

parent_obj

int

父对象的对象标识号（例如，对于触发器或约束，该标识号为表 ID）。

crdate

datetime

对象的创建日期。

type

char(2)

对象类型。可以是下列值之一：

C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
FN = 标量函数
IF = 内嵌表函数
K = PRIMARY KEY 或 UNIQUE 约束
L = 日志
P = 存储过程
R = 规则
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
V = 视图
X = 扩展存储过程

二、系统和示例数据库

安装 Microsoft® SQL Server™ 2000 之后，安装程序将创建下表中显示的数据库和日志文件。

数据库

数据库文件

日志文件

master

Master.mdf

Mastlog.ldf

model

Model.mdf

Modellog.ldf

msdb

Msdbdata.mdf

Msdblog.ldf

tempdb

Tempdb.mdf

Templog.ldf

pubs

Pubs.mdf

Pubs_log.ldf

Northwind

Northwnd.mdf

Northwnd.ldf

master、model、msdb 和 tempdb 都是系统数据库。pubs 和 Northwind 示例数据库作为学习工具提供。SQL Server 联机丛书中的大多数示例都基于这些数据库。pubs 和 Northwind 示例数据库在本讲义中涉及的主要关系图分别如下：

1、Master数据库

master包含了SQL Server诸如登录账号、系统配置、数据库位置及数据库错误信息等，用于控制用户数据库和SQL Server的运行。

2、msdb数据库

msdb为SQL Server Agent调度信息和作业记录提供存储空间。

3、model数据库

为新的数据库充当模板。对其进行的任何修改都会反映到新数据库中。

4、tempdb数据库

tempdb为临时表和临时存储过程提供存储空间，所有与系统连接的用户的临时表和临时存储过程都存储于该数据库中。

5、pubs与northwind数据库

系统自带的示例数据库。

3.2 创建数据库

默认情况下，新建的SQL SEVER 2000数据库包含两个文件：数据文件和日志文件。文件存储在安装目录的data子目录下。创建新数据库时，使用系统数据库Model作为模板。

一、创建新数据库的注意事项

1、只有具有create database权限的用户才能创建。

2、数据库名字要符合命名规范

（1）长度不超过30

（2）第一个字符是字母或下划线或@或#

（3）后续字母是字符、数字或@或#

（4）名称中不能出现空格，除非将名字用引号引起来

3、所有的新数据库都是Model数据库的复制

4、单个数据库的数据可存在一个或多个文件上

5、数据库的大小可以被扩展或收缩

6、新建数据库时，系统自动更新master数据库的sysdatabases表

二、文件与文件组

SQL Server 2000使用一组操作系统文件来存储数据库的各种逻辑成分，包括三类文件：

（1）主数据文件

主数据文件简称主文件，正如其名字所示，该文件是数据库的关键文件，包含了数据库的启动信息，并且存储数据。每个数据库必须有且仅能有一个主文件，其默认扩展名为.MDF。

（2）辅助数据文件

辅助数据文件简称辅（助）文件，用于存储未包括在主文件内的其他数据。辅助文件的默认扩展名为.NDF。

（3）日志文件

日志文件用于保存恢复数据库所需的事务日志信息。每个数据库至少有一个日志文件，也可以有多个。日志文件的扩展名为.LDF。

SQL Server 2000有两类文件组：

（1）主文件组

主文件组包含主数据文件和任何没有明确指派给其它文件组的其它文件。系统表存储在主文件组中。主文件组为默认文件组，可指定其它文件组为默认文件组。默认情况下，用户创建的数据及对象存储在默认文件组中。

（2）用户定义文件组

三、使用企业管理器创建数据库

例1、创建数据库test，初始大小为5MB，最大大小50MB，数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB，最大可增长到5MB（默认为不限制），按1MB增长（默认是按10%比例增长）；数据文件和日志文件都存于D：盘下；所有者是Administrator。

四、使用命令创建数据库

CREATE DATABASE database_name

[ ON

[ < filespec > [ ,...n ] ]

[ , < filegroup > [ ,...n ] ]]

[ LOG ON { < filespec > [ ,...n ] } ]

[ COLLATE collation_name ]

[ FOR LOAD | FOR ATTACH ]

< filespec > ::=

[ PRIMARY ]

( [ NAME = logical_file_name , ]

FILENAME = 'os_file_name'

[ , SIZE = size ]

[ , MAXSIZE = { max_size | UNLIMITED } ]

[ , FILEGROWTH = growth_increment ] ) [ ,...n ]

< filegroup > ::=

FILEGROUP filegroup_name < filespec > [ ,...n ]

例2、下面的示例创建名为 Sales 的数据库。因为没有使用关键字 PRIMARY，第一个文件 (Sales_dat) 成为主文件。因为 Sales_dat 文件的 SIZE 参数没有指定 MB 或 KB，因此默认为 MB，以兆字节为单位进行分配。Sales_log 文件以兆字节为单位进行分配，因为 SIZE 参数中显式声明了 MB 后缀。

USE master

CREATE DATABASE Sales

( NAME = Sales_dat,

FILENAME = 'c:\program files\microsoft sql server\mssql\data\saledat.mdf',

SIZE = 10,

MAXSIZE = 50,

FILEGROWTH = 5 )

LOG ON

( NAME = 'Sales_log',

FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB )

五、数据库对象属性

通过企业管理器或存储过程sp_dboption可更改数据库的属性设置。

例1、将 pubs 数据库设置为只读。

USE master

EXEC sp_dboption 'pubs', 'read only', 'TRUE'

例2、将 pubs 数据库重新设置为可写。

USE master

EXEC sp_dboption 'pubs', 'read only', 'FALSE'

3.3 更改数据库

一、使用企业管理器更改数据库

二、使用命令更改数据库

使用ALTER DATABASE命令对数据库可进行以下修改：

1、增加或删除数据文件

2、改变数据文件的大小和增长方式

3、改变日志文件的大小和增长方式

4、增加或删除日志文件

5、增加或删除文件组

语法格式：

ALTER DATABASE database_name

{

ADD FILE <filespec>[,…n][ TO FILEGROUP filegroup_name ]

| ADD LOG FILE <filespec>[,…n]

| REMOVE FILE logical_file_name

| ADD FILEGROUP filegroup_name

| REMOVE FILEGROUP filegroup_name

| MODIFY FILE <filespec>

| MODIFY NAME = new_dbname

| MODIFY FILEGROUP filegroup_name{filegroup_property|NAME =new_filegroup_name}

| SET < optionspec > [ ,...n ] [ WITH < termination > ]

| COLLATE < collation_name >

}

例1、设已经创建了数据库TEST，它只有一个主数据文件，其逻辑文件名为TEST_Data，物理文件名为e:\sql\data\MSSQL\data\TEST_Data.mdf，大小为5MB，最大大小为50MB，增长方式为按10%增长；改为最大大小不限，增长方式为5MB。

Step 1、将主数据文件的最大大小改为不限制

ALTER DATABASE TEST MODIFY FILE (NAME = TEST_Data, MAXSIZE = UNLIMITED)

Step 2、将主数据文件的增长方式改为按5MB增长

ALTER DATABASE TEST MODIFY FILE (NAME = TEST_Data,FILEGROWTH = 5MB)

例2、将数据库改名为：JUST_TEST

ALTER DATABASE TEST MODIFY NAME = JUST_TEST

3.4 删除数据库

语法格式：DROP DATABASE database_name[,…n]

例3、要删除数据库TEST，使用命令：

DROP DATABASE TEST

自动压缩数据库：选中数据库“属性”/“选项”/“自动压缩”选项。

3.5 用户和安全性管理

数据的安全性是指保护数据以防止因不合法的使用而造成数据的泄密和破坏。对用户的访问进行两阶段验证：“身份验证”阶段和“权限验证”阶段。身份验证可采用两种安全模式：windows身份验证和混合身份验证。用户登录服务器后，要访问数据库或数据库中的对象还需通过权限验证。用户要访问的数据库中要有与登录账号相对应的用户账号。该用户账号还要有对要访问的数据对象的访问权限。有两种账号：登录账号，使用用户能登录到服务器；用户账号，在特定数据库内创建，关联一个登录名，使用户可以访问数据库。

windows身份验证模式的优点：管理员可以不用管理用户账号；Windows NT/2000有更强的SQL SERVER 2000不具备的用户账号管理工具；Windows NT/2000的组策略支持多个用户同时授权访问SQL SERVER 2000。

混合身份验证的优点：创建Windows NT/2000之外的另一个安全层次；支持更大范围的用户；一个应用程序可以使用单个的SQL SERVER 2000登录。

设置验证模式的方法：选择数据库服务器，右击选“属性”，在“安全性”选项卡中选择。

查看登录账号的方法：在“安全性”文件夹中选“登录”进行查看。

新建登录账号的方法：在“安全性”文件夹中选“登录”，右击，选“新建登录”，输入帐号、密码后确定。

角色：一组用户构成的组。分服务器角色和数据库角色。

固定服务器角色

描述

sysadmin

可以在 SQL Server 中执行任何活动。

serveradmin

可以设置服务器范围的配置选项，关闭服务器。

setupadmin

可以管理链接服务器和启动过程。

securityadmin

可以管理登录和 CREATE DATABASE 权限，还可以读取错误日志和更改密码。

processadmin

可以管理在 SQL Server 中运行的进程。

dbcreator

可以创建、更改和除去数据库。

diskadmin

可以管理磁盘文件。

bulkadmin

可以执行 BULK INSERT 语句

固定数据库角色

描述

db_owner

在数据库中有全部权限。

db_accessadmin

可以添加或删除用户 ID。

db_securityadmin

可以管理全部权限、对象所有权、角色和角色成员资格。

db_ddladmin

可以发出 ALL DDL，但不能发出 GRANT、REVOKE 或 DENY 语句。

db_backupoperator

可以发出 DBCC、CHECKPOINT 和 BACKUP 语句。

db_datareader

可以选择数据库内任何用户表中的所有数据。

db_datawriter

可以更改数据库内任何用户表中的所有数据。

db_denydatareader

不能选择数据库内任何用户表中的任何数据。

db_denydatawriter

不能更改数据库内任何用户表中的任何数据。

可以在企业管理器中修改和删除登录账号。

新增数据库用户的方法：展开数据库，然后选“用户”项，右击，选“新建数据库用户”。

权限设置方法：右击选中的数据库用户名，然后点“权限”按钮，打开权限设置对话框。

权限类型：(1）对象权限：执行对数据库中的数据进行增、删、改和查（执行DML）所需要的权限。(2）语句权限：创建数据库或数据库中的项（执行DDL）所需要的权限。(3）暗示性权限：系统定义的角色或数据对象所有者所拥有的默认权限。

数据库对象的操作权限有三种状态：废除、授权和拒绝。

为方便权限的管理，SQL SERVER将若干权限集中成一组并称之为角色。有服务器角色和数据库角色之分。数据库角色可分为两种：（1）标准角色；（2）应用程序角色。若一数据库用户或一登录账号属于某一角色的成员，则它具有该角色包含的所有权限。

固定角色：数据库创建时，系统创建10个固定的标准角色。

使用角色：右击选中的角色，选属性，可查看属性。在属性对话框中，点“添加”按钮，则可将数据库用户添加到该角色中。

新建角色：展开所选的数据库，在“角色”选项上右击，选“新建数据库角色”进行新建。

3.6 数据库备份和恢复

备份和恢复的工作主要由数据库管理员来完成。备份：指对SQL SERVER数据库或事务日志进行复制。恢复：把受到破坏或丢失的数据或出现错误的数据库恢复到原来正常的状态。

SQL SERVER 2000数据库备份的类型：数据库备份；事务日志备份；差异备份；文件和文件组备份。

数据库备份：指对数据库的完全备份，包括所有的数据和数据库对象。在完全备份中，所有未完成的事务或发生在备份过程中的事务都不会被备份。

还原数据库备份将重新创建数据库和备份完成时数据库中存在的所有相关文件。但是，自创建备份后所做的任何数据库修改都将丢失。若要还原创建数据库备份后所发生的事务，必须使用事务日志备份或差异备份。

事务日志备份：事务日志是自上次备份事务日志后对数据库执行的所有事务的一系列记录。可以使用事务日志备份将数据库恢复到特定的即时点（如输入多余数据前的那一点）或恢复到故障点。

还原事务日志备份时，Microsoft® SQL Server™ 前滚事务日志中记录的所有更改。当 SQL Server 到达事务日志的最后时，已重新创建了与开始执行备份操作的那一刻完全相同的数据库状态。如果数据库已经恢复，则 SQL Server 将回滚备份操作开始时尚未完成的所有事务。

差异备份：差异数据库备份只记录自上次数据库备份后发生更改的数据。差异数据库备份比数据库备份小而且备份速度快，因此可以更经常地备份，经常备份将减少丢失数据的危险。

文件和文件组备份：指对数据库文件或文件组进行备份。可提高数据库恢复的速度。恢复时仍需事务日志备份来保证数据库的一致性。

进行备份之需先创建备份设备。备份设备是用来存储数据库、事务日志或文件和文件组的存储介质，可以硬盘、磁带或管道。备份设备在硬盘中是以文件的方式存储的。

使用企业管理器创建备份设备方法：选择要增加备份设备的服务器，打开“管理”文件夹，右击“备份”选项，选“新建备份设备……”，输入名称、选择路径后确定。

使用企业管理器管理备份方法：选择要备份的数据库，右击选“所有任务”，选“备份数据库”，进行备份。也可以使用BACKUP DATABASE命令备份数据库。

用企业管理器恢复数据库方法：右击“数据库”，选“所有任务”，选“还原数据库”，进行还原。还原后的数据库名称与原数据库名称不同，可以重新指定还原后的数据库文件的存储位置。也可以使用RESTORE DATABASE命令进行恢复。

作业

1、SQL Server 2000种使用哪几种操作系统文件？作用分别是什么？

2、SQL Server有哪几个系统数据库，分别有什么用途？

3、用SQL语言实现：创建一个名为factory的数据库，要求：含一个主文件组，一个用户自定义文件组fg1，主文件组包含文件factory.mdf，自定义文件组中包含一个factory.ndf。数据库目录为：d:\data。

4、写出创建产品销售数据库CPXS的T-SQL语句：数据库初始大小为10MB，最大大小100MB，数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB，最大可增长到5MB（默认为不限制），按1MB增长（默认是按10%比例增长）；其余参数自定。

5、将第4题中所创建的CPXS数据库的增长方式改为按5MB增长。

6、压缩CPXS数据库，压缩后有30%的空闲空间，空闲空间返回给操作系统。

第4章管理数据库表及完整性

4.1 创建数据库表

一、概述

SQL SERVER 2000数据库表的性质：行的顺序任意；列的顺序任意；表名唯一；列名唯一；表中可出现完全相同的行，但不提倡，可通过设主码或候选码禁止相同行的出现。

表设计过程需要完成的任务：定义表的列及列的数据类型，要使表达到3NF；决定哪些列允许空；决定是否在表中使用及何时使用约束、默认值或规则；所需索引类型、哪里需要索引哪些列是主键哪些是外键。

可以使用企业管理器建表，也可以用SQL语句建立表。在企业管理器中建表步骤为：确定数据库名，及数据库中所需的表的表名（包括与实体对应的表、与实体间的联系对应的表）；定义各数据库表（包括约束等）；实现数据库表。

二、使用SQL语句创建表

1、语法格式：

CREATE TABLE
    [ database_name.[ owner ] .| owner.] table_name
    ( { < column_definition >
        | column_name AS computed_column_expression
        | < table_constraint > ::= [ CONSTRAINT constraint_name ] }

            | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
    )

[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

< column_definition > ::= { column_name data_type }
    [ COLLATE < collation_name > ]
    [ [ DEFAULT constant_expression ]
        | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
    ]
    [ ROWGUIDCOL]
    [ < column_constraint > ] [ ...n ]

< column_constraint > ::= [ CONSTRAINT constraint_name ]
    { [ NULL | NOT NULL ]
        | [ { PRIMARY KEY | UNIQUE }
            [ CLUSTERED | NONCLUSTERED ]
            [ WITH FILLFACTOR = fillfactor ]
            [ON {filegroup | DEFAULT} ] ]
        ]
        | [ [ FOREIGN KEY ]
            REFERENCES ref_table [ ( ref_column ) ]
            [ ON DELETE { CASCADE | NO ACTION } ]
            [ ON UPDATE { CASCADE | NO ACTION } ]
            [ NOT FOR REPLICATION ]
        ]
        | CHECK [ NOT FOR REPLICATION ]
        ( logical_expression )
    }

< table_constraint > ::= [ CONSTRAINT constraint_name ]
    { [ { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        { ( column [ ASC | DESC ] [ ,...n ] ) }
        [ WITH FILLFACTOR = fillfactor ]
        [ ON { filegroup | DEFAULT } ]
    ]
    | FOREIGN KEY
        [ ( column [ ,...n ] ) ]
        REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
        [ ON DELETE { CASCADE | NO ACTION } ]
        [ ON UPDATE { CASCADE | NO ACTION } ]
        [ NOT FOR REPLICATION ]
    | CHECK [ NOT FOR REPLICATION ]
        ( search_conditions )
    }

参数说明具体见帮助文档。

2、示例

A. 使用 PRIMARY KEY 约束

下例显示在示例数据库 pubs 的 jobs 表中，job_id列中具有聚集索引的 PRIMARY KEY 约束的列定义；此例由系统提供约束名。

job_id smallint PRIMARY KEY CLUSTERED

下例显示如何为 PRIMARY KEY 约束提供名称。此约束用于 employee表中的 emp_id 列。此列基于用户定义数据类型。

emp_id empid CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED

B. 使用 FOREIGN KEY 约束

FOREIGN KEY 约束用于引用其它表。FOREIGN KEY 可以是单列键或多列键。下例显示 employee 表上引用 jobs表的单列 FOREIGN KEY 约束。对于单列 FOREIGN KEY 约束，只需要 REFERENCES 子句。

job_id smallint NOT NULL DEFAULT 1 REFERENCES jobs(job_id)

也可以显式使用 FOREIGN KEY 子句并复述列特性。注意在这两个表中列名不必相同。

FOREIGN KEY (job_id) REFERENCES jobs(job_id)

多列键约束作为表约束创建。在 pubs 数据库中，sales 表包含多列 PRIMARY KEY。下例显示如何从其它表中引用此键（可选择显式约束名）。

CONSTRAINT FK_sales_backorder FOREIGN KEY (stor_id, ord_num, title_id)

REFERENCES sales (stor_id, ord_num, title_id)

C. 使用 UNIQUE 约束

UNIQUE 约束用于强制非主键列的唯一性。PRIMARY KEY 约束列自动包含唯一性限制；但是，UNIQUE 约束允许存在空值。下例显示表 authors 中名为 pseudonym 的列。该列强制作者笔名必须唯一。

pseudonym varchar(30) NULL

UNIQUE NONCLUSTERED

下例显示在 stor_id 实际上是 PRIMARY KEY 的 stores 表中，stor_name 列和 city 列上创建的 UNIQUE 约束；同一个城市中的商店不应同名。

CONSTRAINT U_store UNIQUE NONCLUSTERED (stor_name, city)

D. 使用 DEFAULT 定义

使用 INSERT 和 UPDATE 语句时，如果没有提供值，则默认值会提供值。在 pubs 数据库中，使用了许多 DEFAULT 定义以确保输入有效的数据或占位符。

在 jobs 表上，当没有显式输入实际的描述信息时，默认的字符串将提供描述信息（列 job_desc）。

DEFAULT 'New Position - title not formalized yet'

在 employee 表中，员工可以受雇于子公司或母公司。如果没有显式提供公司信息，则输入母公司（注意在表定义中可以嵌套注释，如下所示）。

DEFAULT ('9952')

除了常量以外，DEFAULT 定义还可以包含函数。使用下例获取输入项的当前日期：

DEFAULT (getdate())

niladic 函数也可以提高数据的完整性。若要跟踪插入行的用户，请使用 niladic 函数 USER（niladic 函数不使用括号）：

DEFAULT USER

E. 使用 CHECK 约束

下例显示对输入到 jobs 表中的 min_lvl 列和 max_lvl 列的值的限制。这两个约束都未命名：

CHECK (min_lvl >= 10)

与

CHECK (max_lvl <= 250)

下例显示对输入到 employee 表的 emp_id 列中的字符数据具有模式限制的命名约束。

CONSTRAINT CK_emp_id CHECK (emp_id LIKE

'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR

emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')

下例指定 pub_id 必须在特定的列表中或遵循给定的模式。此约束用于 publishers表中的 pub_id 列。

CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')

OR pub_id LIKE '99[0-9][0-9]')

F. 完整的表定义

下例显示 pubs 数据库中所创建的三个表（jobs、employee 和 publishers）的完整表定义，其中包含所有的约束定义。

CREATE TABLE jobs

(

job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,

job_desc varchar(50) NOT NULL DEFAULT 'New Position - title not formalized yet',

min_lvl tinyint NOT NULL CHECK (min_lvl >= 10),

max_lvl tinyint NOT NULL CHECK (max_lvl <= 250)

)

CREATE TABLE employee

(

emp_id empid CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED

CONSTRAINT CK_emp_id CHECK (emp_id LIKE

'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or

emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),

fname varchar(20) NOT NULL,

minit char(1) NULL,

lname varchar(30) NOT NULL,

job_id smallint NOT NULL DEFAULT 1 REFERENCES jobs(job_id),

job_lvl tinyint DEFAULT 10,

pub_id char(4) NOT NULL DEFAULT ('9952') REFERENCES publishers(pub_id),

hire_date datetime NOT NULL DEFAULT (getdate())

)

CREATE TABLE publishers

(

pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED

CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')

OR pub_id LIKE '99[0-9][0-9]'),

pub_name varchar(40) NULL,

city varchar(20) NULL,

state char(2) NULL,

country varchar(30) NULL DEFAULT('USA')

)

G. 在列中使用 uniqueidentifier 数据类型

下例创建含有 uniqueidentifier 列的表。该表使用 PRIMARY KEY 约束以确保用户不会在表中插入重复的值，并在 DEFAULT 约束中使用 NEWID() 函数为新行提供值。

CREATE TABLE Globally_Unique_Data

(

guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWID(),

Employee_Name varchar(60),

CONSTRAINT Guid_PK PRIMARY KEY (Guid)

)

H. 对计算列使用表达式

下例显示如何使用表达式 ((low + high)/2) 计算 myavg 计算列。

CREATE TABLE mytable

(

low int,

high int,

myavg AS (low + high)/2

)

I. 对计算列使用 USER_NAME 函数

下例在 myuser_name 列中使用 USER_NAME 函数。

CREATE TABLE mylogintable

(

date_in datetime,

user_id int,

myuser_name AS USER_NAME()

)

J. 使用 NOT FOR REPLICATION

下例显示如何在订阅了复制的表中使用 IDENTITY 属性。此表包含 CHECK 约束，以确保此系统生成的 SaleID 值不会增长到为复制发布服务器指派的范围内。

CREATE TABLE Sales

(

SaleID INT IDENTITY(100000,1) NOT FOR REPLICATION,

CHECK NOT FOR REPLICATION (SaleID <= 199999),

SalesRegion CHAR(2),

CONSTRAINT ID_PK PRIMARY KEY (SaleID)

)

3、查看表，使用存储过程sp_help <table_name>。删除表使用语句：drop table <表名>[,<表名>……]，不能删除系统表，不能直接删除foreign key 约束参照的用户表。修改表使用语句：alter table，详见下节。

4.2 修改表

可以在企业管理器中修改表，也可使用SQL语句ALTER TABLE修改表。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 ] }
}

示例如下：

A. 下例添加一个允许空值的列，而且没有通过 DEFAULT 定义提供值。各行的新列中的值将为 NULL。

CREATE TABLE doc_exa ( column_a INT)

ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL

B. 下例修改表以删除一列。

CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)

ALTER TABLE doc_exb DROP COLUMN column_b

C. 下例向表中添加具有 UNIQUE 约束的新列。

CREATE TABLE doc_exc ( column_a INT)

ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE

D.下例向表中的现有列上添加约束。该列中存在一个违反约束的值；因此，利用 WITH NOCHECK 来防止对现有行验证约束，从而允许该约束的添加。

CREATE TABLE doc_exd ( column_a INT)

INSERT INTO doc_exd VALUES (-1)

ALTER TABLE doc_exd WITH NOCHECK ADD CONSTRAINT exd_check CHECK (column_a > 1)

EXEC sp_help doc_exd

E. 下例向表中添加多个带有约束的新列。第一个新列具有 IDENTITY 属性；表中每一行的标识列都将具有递增的新值。

CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE)

ALTER TABLE doc_exe ADD column_b INT IDENTITY CONSTRAINT column_b_pk PRIMARY KEY,

column_c INT NULL CONSTRAINT column_c_fk REFERENCES doc_exe(column_a),

column_d VARCHAR(16) NULL CONSTRAINT column_d_chk CHECK

(column_d IS NULL OR

column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR

column_d LIKE

"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),

column_e DECIMAL(3,3) CONSTRAINT column_e_default DEFAULT .081

EXEC sp_help doc_exe

DROP TABLE doc_exe

F. 下例添加可为空的、具有 DEFAULT 定义的列，并使用 WITH VALUES 为表中的各现有行提供值。如果没有使用 WITH VALUES，那么每一行的新列中都将具有 NULL 值。

ALTER TABLE MyTable ADD AddDate smalldatetime NULL CONSTRAINT AddDateDflt

DEFAULT getdate() WITH VALUES

G. 下例禁用用于限制可接受的薪水数据的约束。WITH NOCHECK CONSTRAINT 与 ALTER TABLE 一起使用，以禁用该约束并使正常情况下会引起约束违规的插入操作得以执行。WITH CHECK CONSTRAINT 重新启用该约束。

CREATE TABLE cnst_example(

id INT NOT NULL, name VARCHAR(10) NOT NULL, salary MONEY NOT NULL CONSTRAINT salary_cap CHECK (salary < 100000))

INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)

INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)

INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)

ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap

INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)

ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap

INSERT INTO cnst_example VALUES (4,"Eric James",110000)

H. 下例使用 ALTER TABLE 的 DISABLE TRIGGER 选项来禁用触发器，以使正常情况下会违反触发器条件的插入操作得以执行。然后下例使用 ENABLE TRIGGER 重新启用触发器。

CREATE TABLE trig_example (id INT, name VARCHAR(10),salary MONEY)

CREATE TRIGGER trig1 ON trig_example FOR INSERT

IF (SELECT COUNT(*) FROM INSERTED WHERE salary > 100000) > 0

BEGIN

print "TRIG1 Error: you attempted to insert a salary > $100,000"

ROLLBACK TRANSACTION

END

INSERT INTO trig_example VALUES (1,"Pat Smith",100001)

ALTER TABLE trig_example DISABLE TRIGGER trig1

INSERT INTO trig_example VALUES (2,"Chuck Jones",100001)

ALTER TABLE trig_example ENABLE TRIGGER trig1

INSERT INTO trig_example VALUES (3,"Mary Booth",100001)

4.3 创建和使用规则

规则用于实现数据完整性。规则作为一个独立的数据库对象存在。定义一次可以绑定到一列或多列及用户定义的数据类型。每列只能绑定一个规则，但可有多个check约束。规则与check约束作用相同。

一、创建规则

create rule name as condition_expression

说明：条件表达式包含一个局部变量；规则可以是where子句中有效的表达式，可有运算符和谓词等元素；不能包含数据库对象及引用其它列；可包含不使用数据库对象的内置函数。

二、绑定规则

使用sp_bindrule将规则绑定到列或用户定义的数据类型。

1、语法

sp_bindrule [ @rulename = ] 'rule' ,[ @objname = ] 'object_name'[ , [ @futureonly = ] 'futureonly_flag' ]

@futureonly仅当将规则绑定到用户定义的数据类型时才使用。将此参数在设置为 futureonly 时，它会防止用户定义数据类型的现有列继承新规则。如果futureonly_flag 为 NULL，那么新规则将绑定到用户定义数据类型的每一列，条件是此数据类型当前无规则或者使用用户定义数据类型的现有规则。sp_bindefault中此参数含义类似。

2、示例

A. 假设已经用 CREATE RULE 语句在当前数据库中创建名为 today 的规则，此示例将规则绑定到 employees 表的 hire date 列。将行添加到 employees 时，按照 today 规则检查 hire date 列的数据。

USE master

EXEC sp_bindrule 'today', 'employees.[hire date]'

B. 假设存在名为 rule_ssn 的规则和名为 ssn 的用户定义数据类型，此示例将 rule_ssn 绑定到 ssn。在 CREATE TABLE 语句中，类型 ssn 的列继承 rule_ssn 规则。类型 ssn 的现有列也继承 rule_ssn 规则，除非为futureonly_flag指定了 futureonly 或者在 ssn 上直接绑定了规则。绑定到列的规则始终优先于绑定到数据类型的规则。

USE master

EXEC sp_bindrule 'rule_ssn', 'ssn'

C. 此示例将 rule_ssn 规则绑定到用户定义数据类型 ssn。因为已指定 futureonly，所以不影响类型 ssn 的现有列。

USE master

EXEC sp_bindrule 'rule_ssn', 'ssn', 'futureonly'

D. 下例创建一个规则，用以限制插入该规则所绑定的列中的整数范围。

CREATE RULE range_rule AS @range >= $1000 AND @range < $20000

E. 下例创建一个规则，用以将输入到该规则所绑定的列中的实际值限制为只能是该规则中列出的值。

CREATE RULE list_rule AS @list IN ('1389', '0736', '0877')

F. 下例创建一个遵循这种模式的规则：任意两个字符的后面跟一个连字符和任意多个字符（或没有字符），并以 0 到 9 之间的整数结尾。

CREATE RULE pattern_rule AS @value LIKE '_ _-%[0-9]'

三、取消规则绑定

使用sp_unbindrule，在当前数据库中为列或用户定义数据类型解除规则绑定。

sp_unbindrule [@objname =] 'object_name'[, [@futureonly =] 'futureonly_flag']

四、删除规则

Drop rule <rul_name>

4.4 创建和使用默认

默认是一种独立的数据库对象与默认值约束作用一样。默认可多次应用于不同列或用户定义数据类型。删除表时默认不会被删除。

一、创建默认

CREATE DEFAULT default AS constant_expression

二、绑定默认

sp_bindefault将默认值绑定到列或用户定义的数据类型。

sp_bindefault [ @defname = ] 'default' ,[ @objname = ] 'object_name' [ , [ @futureonly = ] 'futureonly_flag' ]

三、取消默认的绑定

使用sp_unbindefault在当前数据库中为列或者用户定义数据类型解除（删除）默认值绑定。

sp_unbindefault [@objname =] 'object_name'[, [@futureonly =] 'futureonly_flag']

四、默认的删除

Drop default default_name

五、示例

A.下面的示例创建字符默认值 unknown。

USE pubs

CREATE DEFAULT phonedflt AS 'unknown'

B.下面的示例绑定示例 A 中创建的默认值。只有当 authors 表的 phone 列没有输入项时，该默认值才起作用。因为phone 列原来就具有默认值，所以下列 Transact-SQL 语句将失败，除非先把默认值删除。

USE pubs

EXEC sp_bindefault phonedflt, 'authors.phone'

4.5 索引

一、索引概述

数据库中的索引与书籍中的索引类似。在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是一个表中所包含的值的列表，其中注明了表中包含各个值的行所在的存储位置。可以为表中的单个列建立索引，也可以为一组列建立索引；索引采用 B 树结构。索引包含一个条目，该条目有来自表中每一行的一个或多个列（搜索关键字）。B 树按搜索关键字排序，可以在搜索关键字的任何子词条集合上进行高效搜索。例如，对于一个 A、B、C 列上的索引，可以在 A以及 A、B 和 A、B、C 上对其进行高效搜索。

大多数书中包含一个关于词汇、名称、地点等等的总索引。数据库则包含分别关于所选类型或数据列的索引：这好比在一本书中分别为人名和地名建立索引。当创建数据库并优化其性能时，应该为数据查询所使用的列创建索引。

在随 Microsoft® SQL Server™ 2000 提供的 pubs 示例数据库中，employee 表在 emp_id 列上有一个索引。下面的示意图显示索引如何存储每个 emp_id 值并指向表中包含各个值的数据行。

当 SQL Server 执行一个语句，在 employee 表中根据指定的 emp_id 值查找数据时，它能够识别 emp_id 列的索引，并使用该索引查找所需数据。如果该索引不存在，它会从表的第一行开始，逐行搜索指定的 emp_id 值。

SQL Server 为某些类型的约束（如 PRIMARY KEY 和 UNIQUE 约束）自动创建索引。可以通过创建不依赖于约束的索引，进一步对表定义进行自定义。

不过，索引为性能所带来的好处却是有代价的。带索引的表在数据库中会占据更多的空间。另外，为了维护索引，对数据进行插入、更新、删除操作的命令所花费的时间会更长。在设计和创建索引时，应确保对性能的提高程度大于在存储空间和处理资源方面的代价。

二、创建索引

1、概述

如果一个表没有创建索引，则数据行不按任何特定的顺序存储。这种结构称为堆集。SQL Server 索引的两种类型为：

（1）聚集

聚集索引基于数据行的键值在表内排序和存储这些数据行。由于数据行按基于聚集索引键的排序次序存储，因此聚集索引对查找行很有效。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。数据行本身构成聚集索引的最低级别。

只有当表包含聚集索引时，表内的数据行才按排序次序存储。如果表没有聚集索引，则其数据行按堆集方式存储。可以比较下面2组SQL语句组的结果。

create table test(aa int primary key, bb int )

insert into test values(2,11)

insert into test values(1,22)

select * from test

create table test1(aa int, bb int )

create unique index uit on test1(aa)

insert into test1 values(2,11)

insert into test1 values(1,22)

select * from test1

（2）非聚集

非聚集索引具有完全独立于数据行的结构。非聚集索引的最低行包含非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。数据行不按基于非聚集键的次序存储。

在非聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针。对于有聚集索引的表，行定位器是聚集索引键。

只有在表上创建了聚集索引时，表内的行才按特定的顺序存储。这些行就基于聚集索引键按顺序存储。如果一个表只有非聚集索引，它的数据行将按无序的堆集方式存储。

索引可以是唯一的，这意味着不会有两行有相同的索引键值。另外，索引也可以不是唯一的，多个行可以共享同一键值。

2、使用语句创建索引

（1）语法:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n] )

（2）示例(USE pubs)

A. 下面的示例为 authors 表的 au_id 列创建索引。

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'au_id_ind')

DROP INDEX authors.au_id_ind

CREATE INDEX au_id_ind ON authors (au_id)

B. 下面的示例为 emp_pay 表的 employeeID 列创建索引，并且强制唯一性。因为指定了 CLUSTERED 子句，所以该索引将对磁盘上的数据进行物理排序。

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'emp_pay')

DROP TABLE emp_pay

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'employeeID_ind')

DROP INDEX emp_pay.employeeID_ind

CREATE TABLE emp_pay(

employeeID int NOT NULL,base_pay money NOT NULL,commission decimal(2, 2) NOT NULL)

INSERT emp_pay VALUES (1, 500, .10)

INSERT emp_pay VALUES (2, 1000, .05)

INSERT emp_pay VALUES (3, 800, .07)

CREATE UNIQUE CLUSTERED INDEX employeeID_ind ON emp_pay (employeeID)

三、查看与删除索引

查看索引：使用企业管理器或使用存储过程sp_helpindex或sp_help查看。

删除索引：drop index <table.index> [,…n]

作业

1、举例说明用create table语句建表的方法（要包括主码、外码、唯一性、check及default约束）.

2、举例说明用alter table语句为表增加、删除列、约束及修改列定义的方法。

3、用sp_help查看student表的结构信息(SQL)

4、删除student表(SQL)

5、各举一例说明规则、默认的创建、绑定、取消绑定和删除的方法。

6、规则与check约束有何异同？

7、哪些列不宜建索引？

8、为什么要建索引？

9、聚簇索引与非聚簇索引的主要区别是什么？

10、用SQL语言实现以下各题

1）为选课表在学号列上创建一个非簇索引；

2）为选课表在学号、课号列上创建一个唯一复合索引，填充因子45 ；

3）查看第一题创建的索引；

4）删除第一题创建的索引;

第5章操纵数据库

5.1 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 子句

指定由查询返回的列。

SELECT [ ALL | DISTINCT ][ TOP n [ PERCENT ] [ WITH TIES ] ]< select_list >

< select_list > ::=

{* | { table_name | view_name | table_alias }.*|{ column_name | expression | IDENTITYCOL | ROWGUIDCOL }[ [ AS ] column_alias ]| column_alias = expression}[ ,...n ]

二、into子句

创建新表并将结果行从查询插入新表中。

[ INTO new_table ]

例1、select sno,sname into t1 from s where sdept=‘信息工程系’

例2、select sno,sname into #t1 from s where sdept=‘信息工程系’

三、from 子句

指定从其中检索行的表。

[ FROM { < table_source > } [ ,...n ] ] < table_source > ::=table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]| view_name [ [ AS ] table_alias ]| rowset_function [ [ AS ] table_alias ]| OPENXML| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]| < joined_table >

< joined_table > ::= < table_source > < join_type > < table_source > ON < search_condition >| < table_source > CROSS JOIN < table_source >| < joined_table >

< join_type > ::=[ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ][ < join_hint > ]JOIN

四、where 子句

指定用于限制返回的行的搜索条件。

[ WHERE < search_condition > | < old_outer_join > ]

< old_outer_join > ::=column_name { * = | = * } column_name

例3、SELECT Tab1.name, Tab2.id FROM Tab1, Tab2 WHERE Tab1.id *=Tab2.id

例4、select * from sc inner join student on student.sno=sc.sno

五、group by 子句

用来指定查询结果的分组条件。

[ GROUP BY [ ALL ] group_by_expression [ ,...n ][ WITH { CUBE | ROLLUP } ] ]

六、having 子句

指定组或聚合的搜索条件。HAVING 只能与 SELECT 语句一起使用。通常在 GROUP BY 子句中使用它。

Having <搜索条件>

七、UNION操作符

将多个结果集合并为一个结果集。条件是：结果集中列的数目与顺序一致；列的数据类型要兼容。

{ < query specification > | ( < query expression > ) }UNION [ ALL ]< query specification | ( < query expression > )[ UNION [ ALL ] < query specification | ( < query expression > )[ ...n ] ]

八、 order by子句

指定结果集的排序。语法：

[ ORDER BY { order_by_expression [ ASC | DESC ] }[ ,...n ] ]

九、 compute子句

生成合计作为附加的汇总列出现在结果集的最后。当与 BY 一起使用时，COMPUTE 子句在结果集内生成控制中断和分类汇总。可在同一查询内指定 COMPUTE BY 和 COMPUTE。

[ COMPUTE{ { AVG | COUNT | MAX | MIN | STDEV | STDEVP| VAR | VARP | SUM }( expression ) } [ ,...n ][ BY expression [ ,...n ] ]

1、AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM

指定要执行的聚合。

2、如果使用 COMPUTE BY，则必须也使用 ORDER BY 子句。

5.2 简单查询

使用的表是S(sno, sn, sex, age, sdept), C(cno,cn,cpno), SC(sno,cno,g)。

一、简单查询举例

select sno,100-g 扣分 from sc

select count(*) from sc

select count(distinct sno) 人数 from sc

select max(g),min(g),sum(age),avg(age) from sc

select * from s where sex='男' and age=20

select * from s where sex='男' or age<20

select * from s where not(age=20)

select * from s where age between 18 and 20

select * from s where age=18 or age=19 or age=20

select * from s where age>=18 and age<=20

select * from s where age in(18,19,20)

select sno,sn from s where sn like '_三%'

select * from sc where cn like '%数据库%'

select sno from sc where g is null and cn='db'

select * from s order by age desc,sex asc

select top 1 * from s order by age desc

select sex 性别,count(*) 人数 from s group by sex

select sex 性别,count(*) 人数 from s group by sex having count(*)>1

select sno from s where sex='男' union select sno from sc where g>85

二、在group by 中使用with cube、with rollup选项

[ GROUP BY [ ALL ] group_by_expression [ ,...n ][ WITH { CUBE | ROLLUP } ]]

使用 CUBE 或 ROLLUP 时，不支持区分聚合，如 AVG(DISTINCTcolumn_name)、COUNT(DISTINCTcolumn_name) 和 SUM(DISTINCT column_name)。如果使用这类聚合，SQL Server 将返回错误信息并取消查询。

CUBE

指定在结果集内不仅包含由 GROUP BY 提供的正常行，还包含汇总行。在结果集内返回每个可能的组和子组组合的 GROUP BY 汇总行。GROUP BY 汇总行在结果中显示为 NULL，但可用来表示所有值。使用 GROUPING 函数确定结果集内的空值是否是 GROUP BY 汇总值。

结果集内的汇总行数取决于 GROUP BY 子句内包含的列数。GROUP BY 子句中的每个操作数（列）绑定在分组 NULL 下，并且分组适用于所有其它操作数（列）。由于 CUBE 返回每个可能的组和子组组合，因此不论指定分组列时所使用的是什么顺序，行数都相同。

ROLLUP

指定在结果集内不仅包含由 GROUP BY 提供的正常行，还包含汇总行。按层次结构顺序，从组内的最低级别到最高级别汇总组。组的层次结构取决于指定分组列时所使用的顺序。更改分组列的顺序会影响在结果集内生成的行数。

5.3 连接查询

连接查询包括：等值连接，非等值连接，自连接，外连接，复合条件连接等。例如：

SELECT S.*, SC.* FROM S, SC WHERE S.Sno=SC.Sno;

SELECT S1.* FROM S S1, S S2 WHERE S1.sage<S2.sage;

SELECT FIRST.Cno, SECOND. cpno FROM C FIRST, C SECOND

WHERE FIRST.cpno=SECOND.Cno;

SELECT S.*, Cno, g FROM S, SC WHERE S.Sno*=SC.Sno;

SELECT S.*, Cno, g From s inner join sc on S.Sno=SC.Sno;

--外连接用：left | right | full [outer] join

SELECT S.Sno, dn FROM S, SC WHERE S.Sno=SC.Sno AND SC.Cno='2' AND SC.g>90;

SELECT Sno, sn, Sdept FROM S S1, S S2 WHERE S1.Sdept=S2.Sdept AND S2.sn='刘晨';

5.4 嵌套子查询

包括简单子查询（In，比较运行符，any、all等）和相关子查询。在having子句中也可以带子查询。例如：

SELECT Sno, sn, SdeptFROM S WHERE Sdept IN( SELECT Sdept FROM S WHERE sn=‘刘晨’);

SELECT Sno, sn FROM S WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM C WHERE cn=‘数据库’));

SELECT Sno,sn FROM S,SC WHERE ‘数据库’ IN(SELECT cn FROM C WHERE C.Cno=SC.Cno) ;

SELECT Sno,sn FROM S,SC,C WHERE C.Cno=SC.Cno AND S.Sno=SC.Sno AND C.cn= ‘数据库’;

SELECT Sno, sn FROM S WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM C WHERE cn=‘数据库’));

SELECT sn, age FROM S WHERE age <ALL (SELECT age FROM S WHERE Sdept=‘信息管理’) AND Sdept <> ‘信息管理’ORDER BY Sage DESC;

SELECT sn FROM S WHERE EXISTS (SELECT * FROM SC WHERE Sno=S.Sno and Cno=‘1’);

SELECT sn FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=SC.Sno AND Cno=C.Cno));

SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno='95002' AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno));

5.5 存储查询结果

一、存储结果于表中

用select…into语句，需将数据库选项“select into/bulk copy”设置为true/on, 否则只能将结果存在于临时表中。

Exec sp_dboption ‘northwin’,’select into’,true

Select * into #temp from products

二、存储结果于变量中

Declare @x varchar(50)

Declare @y float

Select @x=productname,@y=unitprice from products where productid=10010001

Select @x as productname,@y as unitproce

5.6 全文索引概述

全文索引是一种特殊类型的基于标记的功能性索引。使用全文搜索可以快速、灵活地为存储在 Microsoft SQL Server 数据库中的文本数据的基于关键字的查询创建索引。与仅适用于字符模式的 LIKE 谓词不同，全文查询将根据特定语言的规则对词和短语进行操作，从而针对此数据执行语言搜索。对大量非结构化的文本数据进行查询时，使用全文搜索获得的性能优势会得到充分的表现。对数百万行文本数据执行的 LIKE 查询可能需要花费几分钟时间才能返回结果；但对同样的数据，全文查询只需要几秒或更少的时间，具体取决于返回的行数。全文搜索用于提供企业级搜索功能。

使用全文索引前须先启动Microsoft Search服务。方法1：用企业管理器启动；方法2：用操作系统系统服务管理程序启动。

创建全文索引目录：在企业管理器中选“数据库”文件夹，右击选“新建”，选“新建全文目录”。

创建全文索引：在企业管理器中选择要创建全文索引的数据库或表，右击选“new full text indexs”命令，进行选择。全文索引定义完成后，在企业管理器中选择所建的全文索引所在的全文索引目录，右击，选start full population选项（填充键值）完成全文索引过程。

全文查询：在where 子句中使用contains或freetext谓词。

5.7 添加数据

一、插入单行数据

INSERT INTO〈表名〉[(〈属性列1〉[，〈属性列2〉]…)] VALUES(<属性值表>)

例1、Insert into sc表 values( ‘98001’,’c01’,90)

例2、Insert into s(sno，sn) values(‘98001’, ‘李平’)

例3、假设选课表中有一字段名为ID，是identity列，请插入一条ID=4的记录。

Set identity_insert sc on

Insert into sc (ID,sno,cno,g) values( 4，‘98001’,’c01’,90)

二、插入多行数据

INSERT INTO〈表名〉[(〈属性列1〉[，〈属性列2〉]…)] <子查询>

例4、求每个系学生的平均年龄，并把结果存入数据库中。

CREATE TABLE avgAge (dept CHAR(20)，avgAge SMALLINT)

INSERTINTO avgAge SELECT dept，AVG(age) FROM s GROUP BY dept

三、使用动态SQL语句插入数据

例5、求每个系学生的平均年龄，并把结果存入数据库中。

CREATE TABLE avgAge (dept CHAR(20)，avgAge SMALLINT)

INSERT INTO avgAge EXECUTE (‘SELECT dept，AVG(age) FROM s GROUP BY sdept’)

5.8 修改数据

一、修改一行数据

Update s set age=20 where sno=‘001’

二、修改多行数据

update sc set g=g*1.1 where cno='001'

三、带子查询的更新

update sc set g=g*1.1 where cno in (select cno from c where cn like '数据库%')

例11、假设有一选课表（SC），另有一结构（都是学号，课号，成绩）相同的数据库成绩表。请用数据库(课号001)成绩表（SC1）中的成绩替换选课表中数据库课程的成绩。

update sc set sc.grade=sc1.grade

from sc,sc1

where sc1.sno=sc.sno and sc.cno='001'

四、大量数据的更新

要注意，有可能数据更新未完成，事务日志就满了，这时会产生错误信息。解决方法1：使用大的事务日志存储空间；解决方法2：分解更新操作并及时清理日志。

例12、将订单表中的订购数清0。

Update 订单表 set 订购数=0 where 订单号<20000

Backup log mydb with truncate only –清除日志

Update 订单表 set 订购数=0 where 订单号>=20000

5.9 删除数据

一、删除一行数据

delete from s where sno='001'

二、删除多行数据

delete from sc

delete from s where sname like '张%'

三、含子查询的删除

delete from sc where cno in(select cno from c where cname='数据库')

四、删除当前游标行数据

delete from s where current of cursorStudent

五、使用truncate table

truncate table Sc

5.10 视图

视图是定义在一个或多个基表或视图上的一系列SQL select语句。导出视图的select语句存放在数据库中，而与视图定义相关的数据来自它所基于的表，所以视图也称虚表。视图的行为如同一个表，可以通过视图查询表中的数据或修改表中的数据。

例题相关表：Student（sno, sname, sex, age,dept），Course(cno,cname)，Sc(sno, cno, grade)

例、建一个视图，包含所有年龄大于等于20岁的学生信息。

Create view myview As Select * from student where sage>=20

视图的优点：为用户简化查询,为用户定制数据,简化用户权限的管理,导出数据；视图的缺点：降低性能，修改限制。

一、创建视图

1、通过企业管理器创建视图

2、使用create view 命令创建视图

CREATE VIEW [ < database_name > .] [ < owner > .] view_name [ ( column [ ,...n ] ) ] AS select_statement [ WITH CHECK OPTION ]

例、定义视图v1查年龄大于20的学生。（水平视图）

create view v1 as select * from student where sage>20

例、定义视图，查所有学生的学号与姓名。（投影视图）

Create view v2 as select sno, sname from student

例、定义视图，查学号，课号及成绩的110%。（使用计算列的视图）

Create view v3 as select sno, sname,grade*1.2 from sc

例、定义视图，计算学生的出生年份。（使用函数的视图）

Create view v4(sno, sname,syear) as select sno,sname,year(getdate())-sage from student

例、定义视图，查学生的总成绩和平均成绩。（使用函数的视图）

Create view v5(sno, total,average) as select sno, sum(grade),avg(grade) from sc group by sno

例、使用with check option选项，插入与修改要检查是否满足where中的条件。

create view v6 as select * from student where sno=‘98001’ with check option

例、使用with encryption加密视图，使用“Sp_helptext v7”看不到视图定义的语句。

Create view v7 with encryption as Select * from student where sage=22

例、定义视图查学号、姓名、课号、成绩。（复杂视图）

Create view v8 as select student.sno,sname,cno,grade from sc,student where student.sno=sc.sno

例、定义视图查年龄为22和24的学生。

Create view v9 as select * from student where sage=24

Union all

select * from student where sage=22

例、在V9的基础上定义视图查性别为“男”的学生。（创建视图的视图）

Create view v10 as select * from v9 where ssex=‘男’

3、定义视图的限制

（1)create view 语句不能包含compute或compute by子句

（2)不能包含order by子句

（3)不能包含into关键字

（4)不能引用临时表或表变量

（5)视图引用的列不能超过1024个

（6)create view 不能在一个批中与其它T-SQL语句一起使用.

二、管理视图

1、查看视图定义

（1) 使用企业管理器

（2）使用系统存储过程

sp_helptext <视图名>

2、编辑视图：

（1）使用企业管理器

（2）使用alter view命令(语法与create view相似)

3、删除视图

drop view <视图名>

4、通过视图，可以查询数据、插入数据、删除数据、更改数据。

5.11 事务

一、事务的由来

Delete与update命令每次只能操作一个表，当一个处理过程同时需对多个表执行更新操作时，需执行多次相同的命令，此时若这些命令不能同时完成，数据库中就会产生数据不一致。为保证这些操作同时完成，引入事务的概念。

二、事务的概念

事务是数据库的一个操作序列，序列中包含的操作要么全做要么全不做。事务是一个不可分割的工作逻辑单元。事务的四个基本特性：原子性；一致性；隔离性；持久性。

三、事务的使用

开始一个事务：

BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable[ WITH MARK [ 'description' ] ] ]

结束一个事务：

COMMIT [ TRAN [ SACTION ] [ transaction_name | @tran_name_variable ] ]

例20、删除学号为001的学生的所有信息

Begin tran

delete from 选课表 where 学号=‘001’

delete from 学生表 where 学号=‘001’

Commit tran

四、事务回滚

是指当事务中的某一语句执行失败时，将对数据的操作恢复到事务执行前或某个指定的位置。将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。

ROLLBACK [ TRAN [ SACTION ][ transaction_name | @tran_name_variable| savepoint_name | @savepoint_variable ] ]

例21、事务回滚。

Declare @tranName varchar(32)

Select @tranName=‘mytran’

Begin tran @tranName

delete from sc where sno=‘001’

delete from student where sno=‘001’

If @@error=0

begin

rollback tran

commit tran

end

Else

commit tran

第6章存储过程和触发器

6.1 存储过程

是预先编译好的一组Transact-SQL语句，作为一个单元存储。存储过程的名字保存在sysobjects中，内容保存在syscomments系统表中。

一、存储过程的种类

1、局部存储过程，存放于用户数据库中。

2、系统存储过程，以sp_开头，存放于master数据库中。

3、临时存储过程

如果存储过程的名称前面有一个“#”或“##”，该存储过程就称为局部或全局临时存储过程，局部临时存储过程只能在一个用户会话中使用。全局临时存储过程可在所有会话中使用。SQL SERVER关闭后，临时存储过程不再存在。

二、存储过程的优点：模块化程序设计；提高执行速度；减少网络流量；提供安全机制。

6.2 创建存储过程

例题相关表：Student（sno, sname, sex, age,dept），Course(cno,cname)，Sc(sno, cno, grade)

一、创建

Create proc[edure] <存储过程名> [<参数定义部分>] As <主体部分>

例1、创建一个名为myproc的存储过程，查询计算机系年龄小于20的学生。

Create proc myproc As Select * from Student where dept =‘计算机系’ and age<20

二、执行存储过程

exec[ute] 存储过程名 [<参数定义部分>]

例2、执行上例的存储过程

execute myproc

6.3 修改存储过程

一、修改

Alter proc[edure] <存储过程名> [<参数定义部分>] As <主体部分>

例3、修改例1的存储过程，把其中的年龄改为22.

alter proc myproc As Select * from Student where dept =‘计算机系’ and age<22

二、删除存储过程

drop proc[edure] <存储过程名>

例4、删除例1创建的存储过程myproc。

Drop proc myproc

6.4 参数化存储过程

一、带输入参数的存储过程

1、存储过程可带输入输出参数，参数的定义要求与局部变量的定义相似。

2、存储过程参数可为任意的数据类型，包括用户自定义数据类型。

例5、创建一个存储过程myProc_2,该存储过程根据用户输入的学号统计该学生的总成绩。

Create proc myProc_2

@num char(10)

select sum(grade) from sc where sno=@num

执行方式1：

exec myProc_2 ‘1001’

执行方式2：

Declare @num char(10)

Set @num=‘1001’

Exec myProc_2 @num

二、指定参数的默认值

1、为输入参数指定默认值，可创建带可选参数的存储过程。

2、使用存储过程时，可为可选参数指定其它值，如不指定则使用默认值。

3、如有可选参数最好给其指定合适的默认值。

例6、创建一个与例5使用相同的存储过程，但要求为参数指定默认值。

创建：

Create proc myProc_3

@num char(10)=‘1001’

select sum(grade) from sc where sno=@num

执行：

exec myproc_3

exec myproc_3 ‘1001’

Declare @num char(10）

Select @num=‘1002’

exec myproc_3 @num

三、带输出参数的存储过程

1、为存储过程指定output关键字，可创建带输出参数的存储过程。

2、存储过程执行后，保存在输出参数的值可被调用者使用。

3、调用带输出参数的存储过程时，也要在该参数上指定output关键字。

例7、创建一个存储过程，通过输出参数的方式将指定学生的姓名返回给调用者。

Create proc myproc_4

@num char(0)=‘1001’,

@sname char(20) output

select @sname=sname from student where sno=@num

调用方式：

Declare @sname char(20)

Exec myproc_4 ‘1002’, @sname output

Print @sname

思考：写一存储过程，以实现根据用户指定的学号和课程名查询该生该课程的成绩，成绩要保存在输出变量（参数）中。

6.5 存储过程中的错误处理

1、在批处理、存储过程、触发器中通过全局变量@@error识别错误。

2、每执行一条SQL语句后，@@error被重置，值为0表示执行正确，否则，返回错误号。

3、警告信息不会影响@@error的值。因此批处理、存储过程、触发器对警告没有可见性。

一、错误处理

例：修改学生年龄的存储过程。

create proc myupdate

@num char(10),

@age int

update student set age=@age where sno=@num

if @@error=0

print 'success'

else

print 'fail'

二、用户自定义错误信息

用户使用raiserror语句可自定义错误消息。

例8、用户修改的年龄不大于10时输出错误信息。

create proc myupdate1

@num char(10),

@age int

if @age>10

update student set age=@age where sno=@num

else

raiserror ('年龄须大于。',16,1)

例9、格式化错误信息。

create proc myupdate2

@num char(10),

@age int

if @age>10

update student set age=@age where sno=@num

else

raiserror ('%s年龄须大于。',16,1,@num)

6.6 触发器

触发器是一种特殊类型的存储过程，当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时，触发器会生效：UPDATE、INSERT 或 DELETE。

触发器可以查询其它表，而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则或要求。例如，可以控制是否允许基于顾客的当前帐户状态插入定单。

触发器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。

使用触发器的优点如下：

1、触发器是自动的：它们在对表的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。

2、触发器可以通过数据库中的相关表进行层叠更改。

3、触发器可以强制限制，这些限制比用 CHECK 约束所定义的更复杂。与 CHECK 约束不同的是，触发器可以引用其它表中的列。

两种类型的触发器如下：

1、after触发器：相应的更新语句完成后触发。

2、instead of 触发器：用来代替相应的更新语句。

6.7 管理触发器

一、创建触发器

触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。Microsoft® SQL Server™ 允许为任何给定的 INSERT、UPDATE 或 DELETE 语句创建多个触发器。

1、语法

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask)
                { comparison_operator } column_bitmask [ ...n]
        } ]
        sql_statement [ ...n ]
    }
}

2、示例

USE pubs

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR')

DROP TRIGGER reminder

CREATE TRIGGER reminder ON titles

FOR INSERT, UPDATE

AS RAISERROR (50009, 16, 10)

EXEC sp_help 'reminder'

EXEC sp_helptext 'reminder'

二、删除触发器

语法：drop trigger <触发器名>

例如：drop trigger reminder

6.8 触发器工作原理

1、约束与规则在instead of 触发器执行之后被检查。如冲突则回滚触发器。

2、约束与规则在afer 触发器执行之前被检查。如冲突，触发器将不会被执行。

一、inserted与deleted表

1、是由触发器使用的两张特殊的表。

2、inserted表保存将被插入的记录或修改后的记录。

3、deleted表保存被删除的记录或修改前的记录。

以下触发器可显示增删修改记录时inserted表与deleted表的内容：

Create trigger mytrigger on student

For insert,update,delete

select * from deleted

select * from inserted

二、触发器的类型

1、instead of 触发器：用于代替触发触发器的insert /update/delete语句（ insert /update/delete语句不执行只执行触发器）。

2、after触发器： insert /update/delete语句正常执行完后触发after触发器的执行。

三、触发器的限制

1、create trigger必须是批处理中的第一条语句

2、如一个表的外键在delete/update上定义了级联，则不能在该表上定义instead of delete/update触发器。

3、触发器中可执行set语句，但所作设置仅在触发器执行过程中有效。

4、使用set nocount可避免在触发器中进行变量赋值时返回结果集。

5、truncate table 不会激活delete触发器。

6、触发器中不能使用以下SQL语句：

Alter table/create database/drop database/等等

四、触发器的嵌套

1、一个触发器的执行触发另一个触发器，即触发器的嵌套。

2、触发器可嵌套至32层。可通过设置服务器中的嵌套触发器选项禁止嵌套。

3、触发器不会递归调用，除非设置了递归触发器数据库选项。

4、有两种递归方式：

（1）直接递归：触发器执行一个操作，该操作又触发相同的触发器。

（2）间接递归：触发器执行一个操作触发另一表中的触发器，第二个触发器执行的操作又触发第一个触发器。

6.9 instead of 触发器

一、instead of insert触发器

例1、为学生表建一个instead of insert触发器

create trigger in_trig on student instead of insert as

insert into student(sno,sname,sage)

select sno,sname,sage+20 from inserted

测试：

insert into student(sno,sname,sage,sdept) values('123','12330',2,'cs')

二、instead of update触发器

例2、为学生表建一instead of update触发器.

create trigger updateStudent on student instead of update as

if update(sno)

begin

insert into student select * from inserted

update sc set sno=(select sno from inserted) where sno=(select sno from deleted)

delete from student where sno in (select sno from deleted)

end

三、instead of delete触发器

例3、为学生表定义一个instead of delete触发器。

create trigger delStudent on student instead of delete as

delete from sc where sno=(select sno from deleted)

delete from student where sno=(select sno from deleted)

6.10 触发器的应用

一、insert型触发器

例1、在选课数据库中创建一触发器，当向SC表插入一记录时，检查该记录的学号在student表是否存在，检查课程号在course表中是否存在，若有一项为否，则不允许插入。

USE student_course

IF EXISTS (SELECT name FROM sysobjects WHERE name = ‘incheck_trig' AND type = 'TR')

DROP TRIGGER incheck_trig

CREATE TRIGGER incheck_trig ON sc FOR INSERT AS

If exists(SELECT * FROM inserted a WHERE a.sno NOT IN (SELECT b.sno FROM student b) OR a.cno NOT IN (SELECT c.cno FROM course c)

BEGIN

RAISERROR ('违背数据的一致性.', 16, 1)

ROLLBACK TRANSACTION

END

二、update型触发器

例2、在选课表上创建一触发器，若对学号列和课程号列修改，则给出提示信息，并取消修改操作。学号列和课程号列为最前2列。

CREATE TRIGGER update_trigON sc FOR update AS

IF (COLUMNS_UPDATED() & 3) > 0

BEGIN

RAISERROR ('违背数据的一致性.', 16, 1)

ROLLBACK TRANSACTION

END

三、delete型触发器

例3、为SC表建一delete触发器，禁止删除记录

Create trigger delSc on sc For delete As

BEGIN

RAISERROR ('禁止删除记录.',16,1)

ROLLBACK TRAN

END

第7章游标

7.1 游标简介

关系数据库中的操作会对整个行集产生影响。由 SELECT 语句返回的行集包括所有满足该语句 WHERE 子句中条件的行。由语句所返回的这一完整的行集被称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。

游标能扩展结果处理：（1）允许定位在结果集的特定行；（2）从结果集的当前位置检索一行或多行；（3）支持对结果集中当前位置的行进行数据修改；（4）为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持；（5）提供脚本、存储过程和触发器中使用的访问结果集中的数据的 Transact-SQL 语句。

Transact-SQL 游标使用过程：（1）把游标与 Transact-SQL 语句的结果集相关联，并且定义游标的特征，如是否能够更新游标中的行。（2）执行 Transact-SQL 语句以填充游标。（3）检索想要查看的游标中的行。从游标中检索一行或多行的操作称为提取。执行一系列的提取操作以向前或向后检索行的操作称为滚动。（4）根据需要，对游标中当前位置的行执行修改操作（更新或删除）。（5）关闭游标。

一、游标声明

使用Transact-SQL 扩展语法，如下：

例1、声明游标。

例题相关表：Student（sno, sname, sex, age）,Book(bookNo,bookName, author, price, pub)

Declare cursorBook cursor for select bookNo, bookName from book

二、游标的使用

1、打开游标

声明游标后，要使用游标从中提取数据，就必须先打开游标。在T-SQL中，使用OPEN语句打开游标，其格式为：

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }

变量：@@cursor_rows返回游标结果集的行数，为n表示有n行，为0表示未打开，为-1表示动态游标，为-m表示游标异步填充。

例2、定义游标cursorBook1，然后打开该游标，输出其行数。

USE booksys

Declare cursorBook1 cursor for select bookNo, bookName from book

Open cursorBook1

Select '记录数' = @@cursor_rows

2、存取游标

游标打开后，就可以使用FETCH语句从中读取数据。语法格式：

FETCH

[ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar} ]

FROM ]

{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }

[ INTO @variable_name [ ,…n ] ]

例3、从游标cursorBook中提取数据。

Fetch next from cursorBook

例4、从游标cursorBook1中提取数据。

Fetch first from cursorBook1

例5、使用游标cursorBook遍历数据。

Declare @bno varchar(10), @bn varchar(10)

Fetch next from cursorBook into @bno, @bn

While @@fetch_status=0

Begin

print @bno + ' ' + @bn

Fetch next from cursorBook into @bno,@bn

End

3、定位游标

Where current of <游标名>

例6、删除游标所在数据行。

USE booksys

Declare cursorBook2 cursor scroll optimistic for select * from book

Open cursorBook2

Fetch last from cursorBook2

delete from book where current of cursorBook2

4、关闭游标

Close <游标名>

例7、Close cursorBook2

5、释放游标

Deallocate <游标名>

例8、Deallocate cursorBook2

7.2 游标的应用

使用游标可以查询、更改、删除数据。

一、使用游标查询

例9、使用游标遍历学生表中的记录，输出每个学生的学号姓名及学生数。

declare @sno char(10),@sname char(20),@mycount int

declare mycursor cursor for select sno,sname from student

open mycursor

fetch mycursor into @sno,@sname

select @mycount=0

while @@fetch_status=0

begin

select @mycount=@mycount+1

print '学号：'+@sno+'姓名：'+@sname

fetch mycursor into @sno,@sname

end

close mycursor

deallocate mycursor

print @mycount

二、使用游标更改数据

例10、使用游标更新学生表中的姓名。

declare @sno char(10),@sname char(20)

declare mycursor cursor for select sno,sname from student for update of sname

open mycursor

fetch mycursor into @sno,@sname

while @@fetch_status=0

begin

if @sno='98001' update student set sname='xxx' where current of mycursor

fetch mycursor into @sno,@sname

end

close mycursor

deallocate mycursor

三、使用游标删除数据

例11、使用游标删除图书信息。

Declare cursorBook cursor for select bookNo, bookName from book for update

Open cursorBook

Fetch next from cursorBook

While @@fetch_status=0

Begin

delete from book where current of cursorBook

Fetch next from cursorBook

End

Deallocate cursorBook

0 0

数据库编程（简易）

（节选来源：百度文库，基于SQL Server 2000）

第2章 Transact-SQL语言

2.1 T-SQL 语言概述

2.2 数据类型

2.3 变量

2.4 注释符、运算符与通配符

2.5 流程控制语句

2.6 其它命令

2.7 常用内置函数

2.8 用户定义函数

第三章管理数据库

3.1 系统表与系统数据库介绍

3.2 创建数据库

3.3 更改数据库

3.4 删除数据库

3.5 用户和安全性管理

3.6 数据库备份和恢复

第4章 管理数据库表及完整性

4.1 创建数据库表

4.2 修改表

4.3 创建和使用规则

4.4 创建和使用默认

4.5 索引

第5章 操纵数据库

5.1 select 语句

5.2 简单查询

5.3 连接查询

5.4 嵌套子查询

5.5 存储查询结果

5.6 全文索引概述

5.7 添加数据

5.8 修改数据

5.9 删除数据

5.10 视图

5.11 事务

第6章 存储过程和触发器

6.1 存储过程

6.2 创建存储过程

6.3 修改存储过程

6.4 参数化存储过程

6.5 存储过程中的错误处理

6.6 触发器

6.7 管理触发器

6.8 触发器工作原理

6.9 instead of 触发器

6.10 触发器的应用

第7章 游标

7.1 游标简介

7.2 游标的应用

第4章管理数据库表及完整性

第5章操纵数据库

第6章存储过程和触发器

第7章游标