数据库技术：《数据库技术(SQL Server 2000)》电子教案2（蔡伯峰）

泰州职业技术学院电子工程系 课程授课教案 课程名称 数据库技术（SQL Server 2000）  主讲教师 蔡 伯 峰  授课班级 0 3 网 商     使用教材 《数据库原理与SQL Server2000教程》北京工业大学出版社，苏啸编著  课程学分 6 周学时 理论 3     实践 3    授课题目（章、节） 第十讲 SQL Server2000数据类型  学时 3 授课时间 周3第3～5节 第( 10 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、了解各个数据类型的使用场合； 2、能根据问题的需要选择合适的数据类型并熟练运用。  教学重点、难点： 1、根据问题的需要选择合适的数据类型  大体内容 教学方法 时间安排  Transact-SQL概述 面授   SQL Server支持的数据类型 面授   用户定义的数据类型 面授                       教研室审阅意见： 教研室主任签名： 年 月 日   教学过程(基本内容) 辅助手段 备注   时间分配    一、Transact-SQL概述 SQL（结构化数据查询语言，Structure Query Language），是关系型数据库（RDBMS）的应用语言。 Transact-SQL是在包含了大多数ANSI SQL 89和ANSI SQL 92功能的基础上对ANSI SQL92的扩展，对语法作了一些精简，增强了可编程和灵活性，它是非标准的SQL，是基于商业应用的SQL，用于编写具有实际意义的C/S（客户/服务器）模式下的数据库应用程序，要想移植到其它平台上只需修改少量语法即可。它是Server 2000的核心组件之一。 尽管SQL Server 2000提供了使用方便的图形化用户界面，但各种功能的实现基础是Transact-SQL语言，只有Transact-SQL可以直接和数据库引擎进行交互。 1．Transact-SQL的特点 一体化的特点。Transact-SQL语言集数据定义语言、数据操纵语言、数据控制语言和附加语言元素（包括变量、运算符、函数、流程控制语句和注释等）为一体。 两种使用方式，统一的语法结构。两种使用方式即联机交互式和嵌入高级语言的使用方式。 高度非过程化。Transact－SQL语言一次处理一个/多个记录，所有的 SQL语句接受集合作为输入，返回集合作为输出，并允许一条 SQL语句的结果作为另一条 SQL语句的输入。另外，Transact-SQL语言不要求用户指定对数据的存放方法，所有的 Transact-SQL语句使用查询优化器，用以指定数据以最快速度存取的手段。 类似于人的思维习惯，容易理解和掌握。 2．SQL语句的构成及提交 构成：任何一条Transact-SQL语句至少需要包含一个命令动词，即一个明确动作含义的动词。SQL语句中的命令动词是一种对SQL服务器有特定意义的关键字，而其它关键字则是在SQL语句中引入的其它限制条件。 提交：用户通过查询分析器或应用程序向服务器提交Transact-SQL语句时均采用批处理的形式（在书写批处理语句时，需要使用GO语句作为批处理命令的结束标志。当编译器读取到GO语句时，它会把GO语句前面所有的语句当作一个批处理，并将这些语句打包发送给服务器。GO语句本身并不是Transact-SQL语句的组成部分，它只是一个用于表示批处理结束的前端指令）。 3．服务器处理SQL语句的过程 当服务器收到一条SQL语句后，将依次进行： 1）分析 2）优化 3）编译 4）执行 二、SQL Server支持的数据类型（26种，参见“数据表的创建与维护”） 在SQL中，数据类型通常指字段、存储过程参数和局部变量的数据特征。 数据类型通常由SQL系统定义，称为系统数据类型。SQL Server也支持用户定义的数据类型（并非真正的用户定义，而是对现有类型的一种具体化描述）。 （一）、字符型 字符型（由单引号括起）用来存储非统一编码型或统一编码的数据。分为四种：定长char及nchar、可变长varchar及nvarchar。 1．字符型字段的长度 定义字符型字段时，必须规定它的最大长度，以表明它所能存储的最大字符数。 选择char或varchar类型的原则：字段中字符长度是否固定。 对char，若实际长度不足，则以空格填充到尾部。 可变长度的字符结构需要额外的存储开销（主要是开辟一字节用于指明究竟多长） 尽管varchar需要额外的存储开销，但能够删除字段尾部的空格 2．关于截断字符串 向表中插入字符串时，如果实际长度大于字段的定义长度，则系统将报告错误信息，且不向表中插入该记录。 （二）、十六进制型（也即二进制型） 有两种：固定长度binary、可变长度varbinary。用于存储十六进制数据或十六进制字符串。 十六进制数据由0—9、A—F或a—f组成：由0x开头，其中每两个字符为一组，共同构成一个字节，不需加引号。若输入的十六进制数据位数为单数，则在最高位加0变成双数，以便按整字节存放，再在尾部补0。 输入十六进制型数据只能通过SQL 的insert进行，在企业管理器中不能向表中直接输入数据。 （三）、文本型和图像型 即：text、ntext、image text/ntext 字符型，用来存储大量的非统一编码/统一编码型字符数据。其容量理论上为1到231-1个字节，但实际应用时要根据硬盘的存储空间而定。 对text、ntext和image字段的任何更新操作，都会引起对这些字段的初始化操作，SQL Server为它们分配至少2kB的存储空间，故应慎用更新操作。 在 SQL Server 2000中，小的text、ntext可以直接存储在记录中，大的text、ntext或image值被存储在页集合中。每个表只有一个保存text、ntext和image数据的页集合，text、ntext和 image数据可以保存在同一页中。在sysindexes中indid=255的记录保存了这个页集合的起始位置。当text、ntext或image值被存储在页集合中时，在记录的相应字段中将存放一个16位的指针来代替text、ntext或image的信息，该指针指向页集合。 例1： create table text_ex1(PID int not null,name char(8),text_field text) insert text_ex1(PID,text_field) values(101,null) insert text_ex1(pid) values(102) insert text_ex1(PID,text_field) values(103,'我们胜利了！') insert text_ex1(PID,text_field) values(104,replicate('我们已取得了雅典奥运会的的全面胜利！',100)) go select * from text_ex1   例2： create table text_ex2(PID int not null,name char(8),image_field image) insert text_ex2(PID,image_field) values(104,0x4ab5435bf556d) go select * from text_ex2   （四）、时间戳型 如果在表中定义了时间戳型的字段，则无论何时添加记录、修改记录，系统都会向该字段中加入一个计数器值或修改计数器值。 时间戳字段的值为二进制格式的数据，与系统时间没有关系，它表示对该记录的操作顺序。占8Bytes。 一个表中只能有一个时间戳字段。在数据库中，该值保持唯一。 时间戳字段的值由系统自动确定，不允许用户修改或设置。用insert插入数据时，除该字段外的字段需列出，不能全部省略。 时间戳字段可以用于防止多个用户同时修改表的同一记录。 例1： create table timestamp_ex(PID int, name char(8), tis timestamp) insert timestamp_ex(PID,name) values(101,'cbf1') insert timestamp_ex(PID,name) values(102,'cbf2') select * from timestamp_ex  例2： update timestamp_ex set name='cbf22' where tis=0x0000000000000192 --此处tis的值应为表中某个记录中的值   或： update timestamp_ex set name='cbf22' where PID=102 and tsequal(tis,0x00000000000001F5) –此处“PID=102”不能少，因为tsequal要求首先定位记录。   （五）、日期时间型 datetime和smalldatetime的区别： 项目 datetime Smalldatetime  最小值 1753-1-1 1900-1-1  最大值 9999-12-31 2079-6-6  精度 3ms 1min  占用空间 8Bytes 4Bytes  datetime类型的数据以字符串的形式传递给服务器，服务器负责进行转换和合法性验证。 服务器默认的日期格式为mm/dd/yy（mdy格式，见“区域”设置）。可以使用SET Dateformat [ymd]语句来设置日期格式，如mdy、ymd、dmy、ydm、myd、dym。年月日之间可以使用/、-和.分隔。 datetime的值四舍五入为3ms，smalldatetime的值四舍五入为1min。 对于没有时间的数据，默认时间为00：00：00；对于没有日期的时间值，默认日期为1900年1月1日(不论datetime还是smalldatetime)，但没有日期的时间毫无意义。 所有小于50的年份被认为是21世纪，>=50的两位年份被认为是20世纪。最好写4位数。 例1：演示多种日期的输入格式，并显示其结果 Set dateformat mdy create table date_ex(PID int not null ,date_field datetime) insert date_ex values(101,'2003-8-30') insert date_ex values(102,'2003/8/30') insert date_ex values(103,'2003.8.30') insert date_ex values(1033,'8.30.2003') insert date_ex values(10333,'8.2003.30') --当4位年份写全时，不受mdy限制，此时可等价使用mdy、myd、ymd insert date_ex values(104,'2003-8-30 20:30:21') insert date_ex values(105,'2003-8-30 20:30:21.765') insert date_ex values(106,'20:30:21') insert date_ex values(107,'8-30-48') --因日期格式为mdy insert date_ex values(108,'8-30-56 20:30:21') select * from date_ex  例2：根据日期（不含时间）进行查询。当日期字符串中不含时间时，默认时间为00：00：00。 例3：根据日期（含时间）进行查询。 例4：根据时间（不含日期）进行查询。当日期中不含有年月日信息时，默认为1900年1月1日。 例5：表示某一天的日期数据：date_field>=’2003-4-9’ and date_field <=’2003-4-9 23:59:59.999’ （六）、位型 bit是SQL中支持的逻辑数据类型，用于存储0或1。 bit字段通常用于表示ON/OFF、TRUE/FALSE。用作状态标志位、活动帐户指示器、项目使用标志等。 bit类型字段的长度为1Byte数据，如果在表中包含多个bit型字段，则它们可以共用同一字节。 bit字段不要使用空值，因为毫无意义。 例： create table bit_ex(编号 char(9) not null,性别 bit,婚否 bit not null , 党员否 bit) /*1-男 0-女*/ insert bit_ex values('200308001',1,0,0) insert bit_ex values('200308002',0,1,1) SELECT * FROM bit_ex   （七）、数值数据类型 整数类 int、smallint、tinyint、bigint 浮点类 float、real 精确数值 numeric、decimal 货币类 money、smallmoney 1．整数类型 用于存储精确的整数数值，其存储结构效率高，并且在本地进行处理，在各种平台上处理速度均较快，使用较多。四种整数类型的比较： 类型 最小值 最大值 占用空间  Bigint -263 263-1 8Bytes  Int -231（约-21亿） 231-1（约21亿） 4Bytes  smallint -215（约-3万） 215-1（约3万） 2Bytes  tinyint 0 255 1Bytes  例1： create table int_ex (编号 int identity(1,3) , 姓名 char(8), 工资 smallint,年龄 tinyint) insert int_ex values('张三',1200,28) insert int_ex values('张四',980,33) insert int_ex values('张五',1800,24) select * from int_ex  关键字identity为自动增量字段，类型可为整型、精确数值型（小数位数必须为0），它用于标识该字段的值自动增加（初始种子值和增量值会四舍五入成整数）： create table int_ex1(编号 numeric identity(2.3,4.6) , 姓名 char(8), 工资 smallint,年龄 tinyint) --初始种子值：2 增量值：5；也可用decimal insert int_ex values('张三',1200,28) insert int_ex values('张四',980,33) insert int_ex values('张五',1800,24) select * from int_ex1   2．近似值类型（浮点类型） 类型 最小值 最大值 精度 占用空间  float -1.79E+308 +1.79E+308 15位(均精确) 8Bytes  real -3.40E+38 +3.40E+38 7位(均精确) 4Bytes  E+308代表10308，称为阶数，1.79称为尾数。在SQL中，float、real通常采用科学计数法表示。 精度，即数据的总长度，不包括符号和小数点。 用于科学计算或统计方面，因为其数值往往很大，但无需绝对的精度。 例： create table real_ex(编号 int identity(2,4) , 姓名 char(8), 工资 real) insert real_ex values('张三',1200.82673) --注意7位精度的处理 insert real_ex values('张四',980) insert real_ex values('张五',1800.95) select * from real_ex  3．精确数值类型 decimal[p[,s]]和numeric[p[,s]]，其中p表示精度，s表示小数位数（常四舍五入）。 两种数据类型可以相互转换(以前只有numeric可以带identity关键字，现在不同了)。 占9Bytes。精度可达28位。 缺省p时表示10，缺省s时表示0（不能只缺省p而给出s!） 例： create table decimal_ex(编号 int identity(2,4) , 姓名 char(8), 工资 decimal(11,4)) insert decimal_ex values('张三',1200.82673) insert decimal_ex values('张四',980) insert decimal_ex values('张五',1800.95) select * from decimal_ex  4．货币类型 money（8Bytes，-263---263-1）、smallmoney（4Bytes，-231---231-1） 均精确到小数点后4位（四舍五入）。 计算时是先按照整数进行算术运算，再将小数点移到正确的位置。故不同于float、real、numeric、decimal的计算。 插入数据时，数字间不能用逗号，只有通过字符并转换（用CAST）才行。 在SQL语句中表示货币时可以带$标志符。 例： create table money_ex(编号 int identity(2,4) , 姓名 char(8), 工资 money) insert money_ex values('张三',1200.82673) insert money_ex values('张四',980) insert money_ex values('张五',$1800.95) insert money_ex values('张五',CAST('$1,800.95' AS money)) select * from money_ex  特别注意：只有char类、binary类、精确数值类的数据类型后可带小括号以进一步说明长度或精度，其它数据类型均不能带小括号。除char类、binary类外，所有数据类型均占固定的字节数。 （八）、sql_variant数据类型 sql_variant数据类型是SQL Server2000中新增的功能。通过使用sql_variant数据类型，可以在一个单独的字段、参数或变量中存储不同类型的数据值。用于存储除text、image和timestamp类型数据外的其它任何合法的SQL Server数据。 例如：一个定义为sql_variant数据类型的字段中能够同时保存int、decimal、char、binary和nchar等多种类型的数据值。 sql_variant数据类型遵循的规则：常规赋值；使用sql_variant数据类型；sql_variant数据类型的比较。 函数与sql_variant数据：如果需要使用sql_variant参数，或需要使用函数返回sql_variant类型的值，必须留意并不是所有的函数都支持使用sql_variant类型的参数，以及返回sql_variant类型的值。 不支持sql_variant数据的语句有：在使用LIKE语句时不支持sql_variant数据的字段；在全文检索中不支持sql_variant数据的字段；在进行数字或字符串串联运算时，不能使用sql_variant的字段。 例： CREATE TABLE 特殊数据 ( Sql_variant_data sql_variant ) INSERT INTO 特殊数据 VALUES('This is a sql_variant data type test') INSERT INTO 特殊数据 VALUES(123456) INSERT INTO 特殊数据 VALUES('April 15，1998') G0 SELECT * FROM 特殊数据 （九）、table数据类型 用于存储对表或者视图处理后的结果集。这种新的数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。是SQL Server新增的数据类型，类似于一个临时表，可以用于存储一个结果集以便以后处理。该类型只能用于定义table类型的局部变量和用户定义函数的返回值。 例： DECLARE @Table_Example TABLE (num int PRIMARY KEY, memo char(50)) INSERT INTO @Table_Example VALUES (1,'this is a') INSERT INTO @Table_Example VALUES (2, 'table data type example') --从临时表中检索 SELECT * FROM @Table_Example 三、系统表systypes 系统的数据类型，它们存储在systypes表中。在systypes表中既包括系统数据类型，也包括用户定义的数据类型（sysname相当于varchar(256)，是一种特殊的用户自定义类型）。 四、用户定义的数据类型 用户定义的数据类型并不是真正的新数据类型，它是描述已有数据类型一种方式。可通过企业管理器或T-SQL语句创建和删除。 T-SQL语句创建和删除：使用系统存储过程进行创建和删除： sp_addtype usertypename,’systemtypename’,’not null’ sp_droptype usertypename 例1：创建 Exec sp_addtype telephone, 'varchar(24)','not null'   然后，可像其它标准数据类型一样在create table等中使用： create table usertype_ex(pid int identity,name char(8), tel telephone) insert usertype_ex values('张三','666383874') insert usertype_ex values('李四','6856658') select * from usertype_ex  例2：删除 exec sp_droptype telphone      授课题目（章、节） 第十一讲 Transact-SQL变量、运算符与函数  学时 3 授课时间 周3第3～5节 第( 11 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、熟练掌握各种运算符的使用方法； 2、能正确地定义和使用局部变量，了解常用全局变量使用场合； 3、了解6类内建函数的基本功能并能熟练使用常见的函数；  教学重点、难点： 1、局部变量的定义和使用 2、常用内建函数的使用  大体内容 教学方法 时间安排  Transact-SQL运算符 面授   Transact-SQL的局部变量和全局变量 面授   Transact-SQL内建函数 面授   用户自定义函数 面授                   教研室审阅意见： 教研室主任签名： 年 月 日  教学过程(基本内容) 辅助手段 备注   时间分配   一、Transact-SQL运算符 1．算术运算符 +-*/ 四种可用于所有数值类型，% 只能用于整数类型，/ 既可用于整数又可用于实数（如5/4为1，5.0/4为1.25，3%4为0）。 2．位运算符(&、|、^、~) 位运算符的操作数可以是整型或二进制数据类型分类中的任何数据类型（但 image 数据类型除外），此外，两个操作数不能同时是二进制数据类型分类中的某种数据类型，bit不与二进制数据类型一起运算。 左边操作数 右边操作数  binary、varbinary bigint、int、smallint、tinyint  bigint、int、smallint、tinyint bigint、int、smallint、tinyint、bit、binary、varbinary  Bit bigint、int、smallint、tinyint、bit  对于~运算 bigint、int、smallint、tinyint 、bit  例1： create table ty_ex (x binary, y binary) insert ty_ex values(1,4) select x|y from ty_ex  3．比较（关系）运算符 >，>=，<，<=，=，!=，<>，!<，!> 比较运算符的运算结果为布尔值。 4．连接运算符（‘+’） + 用于将两个字符串连接合并成一个字符串。 操作数类型为：char、varchar、nchar、nvarchar（不用于text、ntext）。 5．逻辑运算符 ALL、ANY、BETWEEN、EXISTS、IN、LIKE、NOT、AND、OR.。 6．运算符的优先级 高 低 括号运算符（） 求反~、+（正号）、-（负号） * 、/、 % + 、-、 +（连接）、& 比较运算符 异或^、| NOT AND OR、ALL、ANY、BETWEEN、IN、LIKE   二、Transact-SQL变量 SQL标识符：标识符用于表示服务器名、数据库名、数据库对象名、常量、变量和存储过程名等。 标识符的命名规则： 标识符的长度范围为1—30个字符。不区分大小写。 标识符的第一个字符必须为字母、下划线、@或#。以@为首的标识符表示一个局部变量；以一个#为首的标识符表示一个局部临时对象；两个##表示全局临时对象。 标识符第一个字符后面可以是字母、数字、#、@、$或_。 不区分大小写。 默认情况下，标识符中不允许出现空格；不允许用关键字作标识符；可以以引号来定义特殊标识符（此时可包含空格、关键字等）。 在中文版中，汉字也可以作为标识符。 例：CAT、Cat、xYZ、_SMALL、@from、#proc、##func、@x_sum、”select”、 ”a ss”、x#yz等均合法。而from、From、select等不合法。 变量在使用之前必须定义（即说明数据类型）。变量分为：局部变量、全局变量（由系统定义和维护）。 1．局部变量 在使用之前以DECLARE语句定义（不能同时赋值）。 局部变量的生存期：生存于声明所在的批处理、存储过程或触发器中，生存期结束后，变量消失。 可以通过PRINT或SELECT语句（效果不同；print一次只能显示一个值）将变量的值显示给用户，存储过程能够返回变量的值。 注意：使用DECLARE语句时：必须提供变量名和数据类型；变量前必须有一个@符号；同时定义多个变量时中间用逗号分隔。 2．局部变量的赋值（通过set、select、update进行，但若想从表中提取数据则必须用select或update） SELECT @变量名=表达式[,…] [FROM<表名>] [WHERE<条件>] 或 SET @变量名=表达式 --一次只能给一个变量赋值  例1： declare @aaa CHAR(9),@bbb real select @aaa=姓名 from 学生表 where 学号='200203001'  --看不到显示！ set @bbb=12.3456 print @aaa,@bbb --此句应成2个print书写，因print后只能跟一个变量。 select 'xm' =@aaa,@bbb select @aaa as xm,@bbb select @aaa xm,@bbb  当变量未赋值时，其值为NULL。 使用SELECT由表对变量赋值时，变量的值由表中最后一行确定。 例2：将学生表中字段的值赋值到变量中并显示。 declare @aaa CHAR(9) select @aaa=姓名 from 学生表 --注意返回的只是最后一条记录的值。 select @aaa  可以通过表的主键或统计函数唯一确定变量的值。 declare @aaa int select @aaa=max(年龄) from 学生表 - select @aaa  变量可应用在SELECT中。 declare @aaa varchar(3) set @aaa='李%' select * from 学生表 where 姓名 like @aaa  给变量赋值不能与数据检索结合在同一条SELECT中使用。 declare @aaa CHAR(9) select @aaa=姓名,学号 from 学生表   3．利用UPDATE为局部变量赋值 例1： declare @x int,@y int update 学生表 set @x=5,@y=4 select @x,@y  例2： select * from学生表 declare @x int update 学生表 set @x=年龄 --哪一个记录的？ select @x   例3： declare @x int update 学生表 set @x=(select avg(年龄) from 学生表) select @x  例4：错误。因为SELECT子句返回了多个值。 declare @x int update 学生表 set @x=(select 年龄 from 学生表) select @x  4．全局变量 全局变量用于跟踪服务器范围内和特定会话期间的信息，不能显式被声明或赋值。 全局变量不能由程序员定义 全局变量提供了用户当前的会话信息 全局变量是不可赋值的，且在所有程序中都可直接使用 在某时刻，各用户的值是各不相同的 全局变量 含义  @@rowcount 前一条SQL语句处理的记录条数  @@error 前一条SQL语句执行后的错误号  @@trancount 当前连接的活动事务数  @@transtate 当前事务的状态  @@tranchained 当前事务的模式  @@servername 本地服务器的名称  @@version 服务器和操作系统的版本信息  @@spid 当前进程的ID  @@identity 上次操作中使用的identity值  @@fetch_status 存储上一条fetch next语句的状态  @@nestlevel 当前存储过程、触发器中的嵌套层次  （1）@@rowcount 除了DECLARE外，其它任何语句都可以自动改变@@rowcount的值。因此，如果需要重复使用该变量的值，则需要另外定义局部变量来保存它的值。如： select * from 学生表 declare @x int select @x=@@rowcount select @@rowcount as rowwcount1,@x -------------------------------------结果：1,7 select * from 学生表 where 性别='女' select @@rowcount rowcount1,@x ---结果：4,7  当程序从触发器中退出运行时，@@rowcount被恢复成进入触发器之前的值。 任何不返回行的语句将@@rowcount设置为0，如print语句： if 2=2 print 2 --若将print改为select、set则不同！ if @@rowcount=0 print 'rowcount is 0'。 再如： 例1： update 成绩表 set 成绩=成绩+10 where 课程号='C888' if @@rowcount=0 print 'Did not modify'  故，可通过上述方法来判断是否存在满足条件的记录。 （2）@@error 在每一条语句执行完毕后，应该通过@@error来检查语句是否存在错误。当@@error为非0值时，表示语句执行时产生了错误。@@error的值会随着每一条SQL语句的运行发生变化。 select * from 学生表 select @@error  （3）@@spid：当前进程和服务器进程标识符。如： select @@spid,system_user 登录用户,user 用户名 （4）@@identity：最后插入的标识值。如： create table 标识表(aa int identity , 姓名 char(9)) insert 标识表(姓名) values('aaa') select @@identity insert 标识表(姓名) values('bbb') select @@identity select * from 标识表  （5）* @@fetch_status 游标：相当于指针，用于对表中的记录一条一条地操作（用fetch next语句）。@@fetch_status表示FETCH语句执行后游标的状态。值有0—FETCH语句成功，-1—FETCH语句不成功，-2—所取数据的行没有找到。 四、函数（P103） 可使用户根据其返回的信息进行相关操作，它们可用于SELECT语句的SELECT子句、WHERE子句、GROUP BY子句、表达式中。内建函数可分类为（8类）： 系统函数：用于返回与SQL Server系统、数据库、数据库对象、用户有关的信息 日期函数：用来操作datetime、smalldatetime类型的数据 字符串函数：用来操作二进制、字符串数据。 数学函数 类型转换函数 文本和图象函数：用于对文本、图象类型的数据进行操作。 合计函数：avg、count、max、min、sum等。 游标函数：对用户查询的结果进行逐行处理：@@cursor_rows、cursor_status、@@fetch_status。 以下只讲部分内容。 （一）系统函数：用于返回与SQL Server系统、数据库、数据库对象、用户有关的信息 1．APP_NAME()—应用程序的名称，返回值类型为nvarchar。 例1： print app_name() -- SQL 查询分析器  2．COALESCE()—多分支函数，返回多个表达式中第一个非NULL值的表达式的值（如果所有表达式的值均为NULL，则函数返回NULL值或出错）。 COALESCE (<表达式1>[,…表达式n]) (coalesec：合并、结合、组合) 例1： declare @x char(2),@y char(4) select @x='22',@y='a1' print coalesce(@x,@y)  例2*：要求所有表达式值的类型一致(虽然有些情况下可自动转换)。 declare @x int,@y char(4) select @y='a1' print coalesce(@x,@y)  3．COL_LENGTH()—求字段的长度(占用字节数)，函数返回值类型为int。 COL_LENGTH (<’表名’>,<’字段名’>) 例1： print col_length('学生表','入学年份')  例2： create table cbf(c1 varchar(40) , c2 nvarchar(40) ) select col_length('cbf','c1'),col_length('cbf','c2') --40,80  4．COL_NAME()—求字段的名称 COL_NAME (<table_id>,<column_id>) 例1： declare @x int set @x=1 while @x<6 begin print col_name(object_id('学生表'),@x) set @x=@x+1 end  例2*： Select col_name(object_id('学生表'),ordinal_position) from information_schema.columns where table_name='学生表'  例3*： Select column_name from information_schema.columns  5．DATALENGTH()—求数据的实际长度，函数返回值类型为int ，NULL值的长度为NULL 例1： Print datalength('abcdefg') select 姓名,datalength(住址) 住址长度 from 学生表  6．DB_ID()—求数据库的编号，函数返回值类型为smallint。如果没有指定数据库名，则返回当前数据库的编号。 DB_ID ([‘数据库名’]) 例1： Print db_id('学生课程数据库') print db_id()  例2*： use master select name,db_id(name) ,dbid from sysdatabases  7．DB_NAME()—求数据库的名称，函数返回值类型为nvarchar。如果没有指定数据库的编号，则返回当前数据库的名称。 DB_NAME (database_id) 例1： Print db_name(db_id('学生课程数据库')) print db_name()  例2*： use master select dbid,db_name(dbid) from sysdatabases order by dbid  8．HOST_ID()—求服务器端计算机标识号，函数返回值类型为nchar。 HOST_ID () 例：print host_id() 9．HOST_NAME()—返回服务器计算机的名称，函数返回值类型为char(8)。 HOST_NAME () 例：print host_name() 10．IDENTITY()—向新表中插入identity字段，只用于SELECT…INTO语句中，函数返回值类型与data_type类型相同。 IDENTITY (<data_type>[, seed, increment ]) [ AS column_name] data_type可以是整型的分类或decimal类型。 例1： select 学号 as num, 姓名 as xm, identity(int,1,1) as bh into cbf1 from 学生表 select * from cbf1  例2*： select 学号 as num, 姓名 as xm, bh=identity(int,1,1) into cbf2 from 学生表  11．ISDATE()—判断是否为有效的日期，如果是则返回1，否则返回0。 ISDATE (表达式) 例1： print isdate('2003/3/3') print isdate('2003-3/3') print isdate('2003=3/3')  12．ISNULL()—用确定值替换空值（类似于coalesce） ISNULL (<check_expression>,<replacement_value>) 当check_expression的值为NULL时，函数返回replacement_value的值，否则返回check_expression的值。 要求check_expression和replacement_value的类型相同或可以转换。 例1： print isnull('a',1) declare @x int print isnull(@x,1)  例2：因为先修课字段的类型为char(4)，所以结果只显示'没有先修课'的前两个汉字。 select 课程名,isnull(先修课,'没有先修课') from 课程表  13．ISNUMERIC()—判断是否为合法的数值表达式，是则返回1，否则返回0。 ISNUMERIC (<表达式>) 例1： print isnumeric(1+3/4) declare @a int print isnumeric(1+@a) set @a=1 print isnumeric(1+@a)  14．NULLIF()—判断表达式是否相等 NULLIF (<表达式1>,<表达式2>) 当表达式1和表达式2的值相等时，函数返回NULL值，否则返回表达式1 的值。 例1： print isnull(nullif(1,1),'1') print isnull(nullif(2,1),'1')  15．USER_ID()—求数据库用户的标识号，函数根据用户名返回数据库用户的ID号，返回值类型为int。 USER_ID ([‘user_name’]) 注意：登录名用于登录到SQL Server（如sa）,也称为登录用户名或用户安全帐户名。登录成功后要使用某一数据库，必须要有该数据库的数据库用户名。 例1： print user_id() print user_id ('guest') print user_id ('dbo')  16．USER_NAME()—返回数据库的用户名，函数返回值的类型为nchar。 USER_NAME ([user_id]) 例1： print user_name(user_id('dbo'))  17．SUSER_SID()—求登录用户名（即用户安全账户名）的标识号，返回值为int类型。如果不指定用户名，则返回当前用户的SID。 SUSER_SID ([‘login_name’]) 18．SUSER_SNAME()—求登录用户名（即用户安全账户名），返回值类型为nchar。 SUSER_SNAME ([server_user_sid]) 例1： print suser_sname(0x01) （二）日期函数：用来操作datetime、smalldatetime类型的数据 1．DAY(日期表达式)—求日期表达式中的日期值（天数）。 2．MONTH(日期表达式)—求月份值。 3．YEAR(日期表达式)—求年份值。 4．DATEADD()—求日期olddate加上额外日期number后得到的新日期 DATEADD (<datepart>,<number>,<olddate>) datepart：用来指定构成日期增量（减量）的类别。 number：可正可负。 例1： print dateadd(year,1,'2003-5-4') print dateadd(month,1,'2003-5-4') print dateadd(day,1,'2003-5-4') print dateadd(week,1,'2003-5-4') print dateadd(hour,1,'2003-5-4') print dateadd(minute,1,'2003-5-4') print dateadd(second,1,'2003-5-4') print dateadd(millisecond,1,'2003-5-4') print dateadd(quarter,1,'2003-5-4') -- quarter指季度（3个月） print dateadd(quarter,-1,'2003-5-4')  5．DATEDIFF()—求两个日期之间的日期时间差：date2-date1 DATEDIFF (<datepart>,<date1>,<date2>) 例1： print datediff(day,'2002-5-4','2003-6-5') print datediff(month,'2002-5-4','2003-6-5') print datediff(year,'2002-5-4','2003-6-5') print datediff(week,'2002-5-4','2003-6-5')  例2：查询已入学2年以上的学生名单。 select * from 学生表 where datediff(year,入学年份,getdate())>=2 --比较： select * from 学生表 where datediff(day,入学年份,getdate())>=365*2  6*．DATENAME()—获取日期中指定部份的名称，为字符串，而非数值，故不同于day()、month()、year()。 DATENAME (<datepart>,<date>) 例1： print datename(year,getdate()) print datename(month,getdate()) print datename(day,getdate()) print datename(week,getdate()) print datename(hour,getdate()) print datename(minute,getdate()) print datename(second,getdate()) select 姓名,datename(year,入学年份) as 年 from 学生表  7*．DATEPART()—获取日期中指定部份的数值，故等同于day()、month()、year()。 DATEPART (<datepart>,<date>) 例1： print datepart(year,getdate()) print datepart (month,getdate()) print datepart (day,getdate()) print datepart (week,getdate()) print datepart (hour,getdate()) print datepart (minute,getdate()) print datepart (second,getdate()) select 姓名,datepart(year,入学年份) as 年 from 学生表  8．GETDATE()—获取系统当前的日期和时间，函数返回值类型为datetime。 （三）字符串函数：用来操作二进制（将它解释为字符串，如0x413035解释为’A05’）、字符串数据。 具体分为： 字符转换函数：ASCII、CHAR、LOWER、UPPER、STR 去空格函数：LTRIM、RTRIM 取子串函数：LEFT、RIGHT、SUBSTRING 串比较函数：CHARINDEX、PATINDEX 串操作函数：REVERSE、REPLACE、SPACE、STUFF、REPLICATE、QUOTENAME 1．ASCII()—返回返回字符串最左端字符的ASCII码 ASCII (字符串表达式) 注意：纯数字串可不用单引号括起。 2．CHAR()—返回ASCII码对应的字符，如果参数在[0,255]之外，则返回NULL。 CHAR (整数表达式) 3．LOWER()—返回全部小写的字符串 LOWER (字符串表达式) 4．UPPER()—返回全部大写的字符串。 5．STR()—将数字转换为字符串 STR (<数值表达式>[,<长度>[,<小数位数>]]) 缺省长度为10，缺省小数位数为0。当小数位数不够时，将进行四舍五入操作。 例1： print str(123.45678,4) print str(123.45678,2) print str(123.45678,9,6) print str(123.45678,9,3)   6．LTRIM()—去除字符串左侧的空格 7．RTRIM()—去除字符串右侧的空格 8．LEFT()—从左侧取子串 LEFT (<字符串表达式>,<子串长度>) 9．RIGHT()—从右侧取子串 RIGHT (<字符串表达式>,<子串长度>) 10．SUBSTRING()—取子串 SUBSTRING (<字符串表达式>,<开始位置s>,<长度n>) 例： select left(所在院系,2),right(所在院系,6),substring(所在院系,2,3) from 学生表 注意：当汉字与空格、字符在一起时，每个汉字长度为1，每个字符长度也为1。但 select datalength('所asd在院系')为11。 11．CHARINDEX()—查找子串第一次出现的位置 CHARINDEX (<子串>,<字符串表达式>) 例： select charindex('xP','Windows XP') --不区分大小写 select charindex('cbf','Windows XP') 12．PATINDEX()—查找子串出现的位置（pattern指模式） PATINDEX (<’%子串%’>,<字符串表达式>) 函数可用于查找带有通配符的子串位置。要求子串（第一个参数）的前后必须有%，否则返回值为0。 例： select patindex('_k','abakjcfhkd') --返回0 select patindex('%_k%','kbakjcfhkd') --查找位于第一个k前面的任一字符位置。该例返回3。‘_’相当于‘？’ select patindex('%k_g%','kbakjcfhkdgl') --查找以k开头以g结尾的第一个子串的位置。该例返回9 select patindex('%k%%','kbakjcfhkd') --查找以k开头的第一个子串的位置。该例返回1 select 姓名,所在院系 from 学生表 where patindex('%计%%',所在院系) <>0 13．QUOTENAME()—返回用特定符号括起的字符串（quote:：引用，括起） QUOTENAME (<’字符串表达式’>[,引用符号]) 函数的第二个参数缺省值为[]。可用[、’、(、{、”进行。 例1： print quotename('abcd') --返回 [abcd] print quotename('abcd','(') --可只输入一个‘（’，也可输入2个： print quotename('abcd','()') print quotename('abcd','''') --单引号时必须输入2个 print quotename('abcd','"')  14．REPLICATE()—重复字符串 REPLICATE (<字符串表达式>,<整数表达式n>) 当n为负数时，函数返回NULL值。 例1： select replicate('abcd',1) select replicate('abcd',2) select replicate('abcd',0) select replicate('abcd',-1)  15．REVERSE()—字符串反排 REVERSE (<字符串表达式>) 例1： print reverse('笑哈哈')  16．REPLACE()—替换所有子串 REPLACE (<字符串表达式>,<子串>,<新子串>) 例1： print replace('abcdefgabcdef','abc','12') print replace('abcdefgabcdef','abc','')  17．SPACE()—返回指定长度的空格 SPACE (<整数>) 18．STUFF()—删除并插入子串 STUFF (<字符串1>,<开始位置>,<长度>,<字符串2>) 例1： select stuff('1234567',2,1,'abc') -- 1abc34567 select stuff('1234567',0,1,'abc') --null select stuff('1234567',2,0,'abc') -- 1abc234567 select stuff('1234567',2,8,'abc') -- 1abc   （四）数学函数 函数名 功 能  ACOS 反三角函数，返回值为float类型的弧度值  ASIN 反三角函数，返回值为float类型的弧度值  ATAN 反三角函数，返回值为float类型的弧度值  ATN2 ata2(表达式1，表达式2)，返回值正切是表达式1/表达式2的float类型的弧度值  SIGN 符号函数，取参数的符号  ABS 符号函数，取绝对值  CEILING 近似值函数，返回比参数大的最小整数  FLOOR 近似值函数，返回比参数小的最大整数  ROUND round(表达式，小数点后的位数)，四舍五入  EXP 指数函数，返回en值  POWER 幂指函数，返回xy值  LOG10 对数函数，返回对数值  SQRT 平方根函数，返回平方根值  SQUARE 平方函数，返回平方值  LOG 对数函数，返回自然对数值  RAND 随机函数，返回0—1之间的随机浮点数  PI 返回π的近似值  SIN 三角函数，要求参数为弧度  COS 三角函数，要求参数为弧度  TAN 三角函数，要求参数为弧度  COT 三角函数，要求参数为弧度  DEGREES 转换函数，弧度(角度  RADIANS 转换函数，角度(弧度  例： select sin(pi()/2),atan(1),log10(10),rand(),sign(-2),power(2,3.0) select ceiling(3.2),floor(-2.8) select round(58.6453378,2),round(58.6453378,0),round(58.6453378,-2) （五）类型转换函数 在SQL表达式的一些类型中可以自动进行类型转换—隐式类型转换。如：可以将字符型表达式与日期型表达式进行相互赋值、比较；能够将integer类型赋值给字符型；整型内部自动进行转换（如转换为smallint类型）。 1．CAST()—显式类型转换 CAST ( <表达式> AS <数据类型> [(长度)] ) 注意：“长度”通常省略。 2．CONVERT()—显式类型转换 CONVERT (<数据类型>[(长度)],<表达式>[,格式]) 其中：“格式”专用于datetime数据。取值范围为：0—14（不带时间且2位数年份）、100—114（不带时间且4位数年份）、20、21（带时间且4位数年份）。缺省时带时间且4位数年份。 注意：“长度”通常省略。 例1： select cast(32767 as char),cast(10+9 as char(3)) select convert(int,4.672),convert(char(6),69.023) select 学号,姓名,cast(入学年份 as char) as 入学年份,convert(char,年龄) as 年龄 from 学生表 where 年龄=(select min(年龄) from 学生表)  例2： select getdate(),convert(char,getdate(),1) select getdate(),convert(char,getdate(),101) select getdate(),convert(char,getdate(),2) select getdate(),convert(char,getdate(),102)   （六）*、文本和图象函数：用于对文本（text、ntext）、图象(image)类型的数据进行操作。 1.PARTINDEX：返回表达式中某模式第一次出现的起始位置。（前面已讲） 2.TEXTPTR：以varbinary格式返回对应于text、ntext、image数据的文本指针值。 TEXTPTR（column） 3．TEXTVALID：检查给定文本指针是否有效。有效：1，无效：0 TEXTVALID(‘table.column’，text_ptr) 例： alter table 学生表 add 备注 text go update 学生表 set 备注='qwrweterhrjhtjkuykuy' select * from 学生表 select 姓名,textptr(备注) as 备注指针 from 学生表 select 姓名,textvalid('学生表.备注',textptr(备注)) as 备注指针有效否 from 学生表   五、用户自定义函数 为了扩展Transact-SQL的编程能力，SQL Sever2000允许在应用程序中使用用户自定义函数。可以传递0个或多个参数，并返回一个简单的数值。 CREATE FUNCTION 函数名称(参数名称 AS 数据类型) RETURNS 返回数据类型 BEGIN 函数内容 RETURN 表达式 END 例：定义一个计算员工奖金的函数，并调用该函数输出员工的奖金。 CREATE FUNCTION bonus(@Salary AS INT) RETURNS INT BEGIN RETURN (@Salary*0.3) END SELECT姓名，dbo.bonus(工资) AS 奖金 --输出员工姓名和奖金数目 FROM数据表    授课题目（章、节） 第十二讲 Transact-SQL程序设计  学时 3 授课时间 周3第3～5节 第( 12 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求：. 1、熟练掌握流控制命令的使用方法； 2、能根据要求编写程序； 3、了解游标的使用方法； 4、掌握常见错误的处理方法。  教学重点、难点： 1、根据要求编写程序  大体内容 教学方法 时间安排  程序语句 面授   关于错误处理 面授                           教研室审阅意见： 教研室主任签名： 年 月 日  教学过程(基本内容) 辅助手段 备注   时间分配    SQL 2000提供了流程控制命令和语法结构，可进行顺序、分支、循环、存储过程、触发器程序设计，编写结构化的模块代码（类似于C语言），并将其放置到数据库服务器上。 服务器端程序组成： 批处理 注释、变量 流控制命令 错误和消息的处理 编程工具：查询分析器。 一、注释符 --：用于单行注释（ANSI标准） /* */：可用于多行注释 注意：分析器及优化器会忽略所有的注释。 二、块语句BEGIN…END 整个块语句被看作一条语句。 允许嵌套。 常用于while、if…else…中。 三、选择语句 （一）判断语句IF…ELSE… 用于对条件表达式进行判断，根据条件来决定执行哪条语句。 IF <条件表达式> <SQL命令行或块语句> [ELSE <SQL命令行或块语句> ]  可嵌套。 条件表达式可以是各种表达式的组合，但其结果必须为逻辑类型。 多条语句时若不用begin…end括起，则只执行第一条语句或出错。 例1*：求
的解（a=20,b=80）。 declare @a real,@b real,@x real select @a=20,@b=80 if @a=0 begin print 'no root' print 'but this question has a root' end else begin select @x=-@b/@a print @x end  例2：求
的解（a=20,b=80,c=15）。 declare @a real,@b real,@c real,@d real,@x real select @a=20,@b=80,@c=15 set @d=@b*@b-4*@a*@c if @d<0 print 'no root' else if @d=0 begin print 'one root:' set @x=-@b/(2*@a) print @x end else begin print 'two root:' set @x=(-@b+sqrt(@d))/(2*@a) print @x set @x=(-@b-sqrt(@d))/(2*@a) print @x end  例3：在数据查询中的应用。 if (select max(年龄) from 学生表)>19 print '有超过19岁的' else print '没有'   （二）IF EXISTS语句 用于检测所查询的数据是否存在，而不管存在多少行。与if count(*)>0等价，但它效率更高，因为它只要找到一条即停止检测。 IF [NOT] EXISTS ( SELECT 子查询) <SQL命令行或语句块> [ELSE <SQL命令行或语句块> ]  当SELECT子查询只要找到一个匹配的记录，就停止检测，此时条件为真。 例1： if exists(select * from 学生表 where 姓名='李涛') print '存在' else print '不存在'   （三）CASE语句 多分支结构通过n个条件控制n+1个值，它不同于if…else…，它一般用于SQL命令中。 简单形式 CASE <待判断表达式> --如变量、字段表达式等???? WHEN <算术表达式a1> THEN <算术表达式b1>????????[ ...n ]???? [ELSE <算术表达式b n+1>]END  搜索形式 CASE???? WHEN <条件表达式a1> THEN <算术表达式b1>????????[ ...n ]???? [ELSE <算术表达式b n+1>]END  注意：若有多个条件同时成立，则取第一个。 例1：将学号为200203001的所有课程成绩加5分，200206001的所有成绩加8分，其余加3分。 update 成绩表 set 成绩= case 学号 when '200203001 ' then 成绩+5 when '200206001' then 成绩+8 else 成绩+3 end 或 update 成绩表 set 成绩=成绩+ case 学号 when '200203001' then 5 when '200206001' then 8 else 3 end  再如*： Select 姓名, (case 性别 when '男' then 'male' when '女' then 'female' else '不确定' end) as xb from 学生表  例2：将成绩表复制到新表“成绩等次”，并在成绩等次中增加一个字段“等次”，用于存放“优秀”、“良好”、“中等”、“及格”或“不及格”。 select *,case when 成绩>=60 and 成绩<70 then '及格' when 成绩>=70 and 成绩<80 then '中等' when 成绩>=80 and 成绩<90 then '良好' when 成绩>=90 then '优秀' else '不及格' end AS 等次 into 成绩等次 from 成绩表 select * from 成绩等次   四、WHILE语句 WHILE<条件表达式> BEGIN <SQL命令行或语句块> [BREAK] [CONTINUE] <SQL命令行或语句块> END  例1： declare @a int set @a=1 while @a<=5 begin print @a set @a=@a+1 end  例2*： declare @a int set @a=1 while @a<15 begin set @a=@a+1 if @a % 2<>0 continue print @a end  例3*： declare @a int select @a=1 while @a<15 begin select @a=@a+1 if @a % 2<>0 break print @a end   五*、GOTO语句 GOTO 标识符 标号的定义：可以是字符与数字的组合，但必须以冒号结束。 例1：使用IF和GOTO语句构成循环结构。 declare @a int set @a=1 L1: print @a set @a=@a+1 if @a>15 goto L2 goto L1 L2:   六、WAITFOR语句 用于使查询在某一时刻或在一段时间间隔后继续向下执行。当某进程执行到waitfor语句时，SQL Server将此进程放入一个睡眠队列中，当等待的时间（刻）到来时再唤醒该进程。 WAITFOR { DELAY '时间' | TIME '时刻' |ERROREXIT | PROCESSEXIT | MIRROREXIT }  DELAY：用于设定等待的时间，最多可达24小时 TIME：用于设定等待结束的时间点 ERROREXIT：直到处理非正常中断 PROCESSEXIT：直到处理正常或非正常中断 MIRROREXIT：直到镜像设备失败 时间、时刻格式均为datetime，不包括日期。最长为24小时。 例1：等待1分钟后显示字符串。 waitfor delay '0:1:00' print 'abc'  例2：等待到某一时刻后显示字符串。 waitfor time '15:11:00' print 'abc'   七、使用游标 通常情况下，关系数据库中的操作总是会对整个记录集产生影响，但在实际应用中，经常需要每次处理一条或部分记录。此时，可使用游标在服务器内部处理结果集，它有助于识别一个数据集合内部指定的记录，从而可以有选择地按记录执行操作。 1、声明游标 使用游标之前需先声明，语法为： DECLARE 游标名称 CURSOR [ LOCAL | GLOBAL ] [FORWARD_ONLY|SCROLL] [READ_ONLY] FOR选择语句 [FOR UPDATE [OF 字段名称l，字段名称2，…]] 参数说明如下。 LOCAL|GLOBAL：用于指定该游标的作用域是局部还是全局的。 FORWARD_ONLY：游标只能从第一行滚动到最后一行。SCROLL：可使用相应关键字指定游标的移动位置。如果没有指定SCROLL关键字，那么该游标只能进行NEXT操作。 READ_ONLY：禁止通过该游标进行数据更新。 选择语句：即标准的SELECT语句，它定义游标将要处理的结果集。 UPDATE：定义游标内可更新的列。 例： DECLARE PM_Cursor CURSOR READONLY FOR SELECT * FROM 学生表 WHERE 性别='男' ORDER BY 姓名 2、打开和使用游标 在为结果集声明了游标之后,还需打开游标才能使用之，语法为： OPEN 游标名称 打开游标之后，SQL Server服务器会特地为这个游标开辟一定的内存空间，这些内存空间将用来存放游标操作的数据结果集，并且游标使用过程中，SQL Server服务器还会根据具体情况封锁某些数据。 例： DECLARE PM_Cursor CURSOR FOR SELECT * FROM 学生表 WHERE 性别='男' ORDER BY 姓名 OPEN PM_Cursor FETCH NEXT FROM PM_Cursor --读取结果集中的数据，next可省略 WHILE @@FETCH_STATUS=0 --根据@@FETCH_STATUS来确定是否继续读取数据 BEGIN FETCH FROM PM_cursor END 另外： Fetch prior --存取前一条记录 Fetch next --存取下一条记录 Fetch last --存取最后一条记录 Fetch absolute n --存取第n条记录 3、关闭和释放游标 在游标打开以后，如果不再使用某个游标，一定要将该游标关闭，这样服务器就会释放该游标所占用的相关资源。语法为： CLOSE 游标名称 如果关闭了一个游标，可以在需要时再次打开和使用它。在一个批处理中，也可以多次打开和关闭同一个游标。 但即使是关闭的游标结构，它本身也会占用一定的计算机资源，所以如果某个游标不再使用，应该及时地将游标释放，从而收回被游标占用的计算机资源。语法为： DEALLOCATE 游标名称 如果完成了释放游标的操作，那么在重新使用这个游标时，将重新执行声明该游标的语句。 八、RETURN语句：使程序从批处理、存储过程或触发器中无条件退出。 RETURN (整数值) 如果没有指定返回值，则系统根据程序执行的情况返回一个内定值。如0则表示程序执行成功、-1表示找不到对象、-2表示数据类型错误、-3表示死锁、-4表示违反权限原则、-5表示语法错误、-6表示用户造成一般性错误，、-7表示资源错误、-8表示非致命的内部错误、-9表示已达到系统的极限等。 九、设置选项：set SQL Server中设置了一些选项用以影响服务器处理特定条件的方式。它们存在于用户与服务器的连接期间或用户的存储过程和触发器中。可使用set语句进行设置。 格式：set condition on | off | value 如： /*请求显示执行时间*/ set statistics time on /*请求服务器只返回记录的前50项*/ set rowcount 50 /*请求服务器停止汇报返回的记录个数*/ set nocount on /*请求分析、优化但不执行查询*/ set noexec on 例如： set statistics time on select * from 学生表 set statistics time off set rowcount 3 select * from 学生表 set nocount on select * from 学生表 set noexec on select * from 学生表 十、关于错误处理 一般，SQL Server可自动为用户处理大部分程序所出现的错误，并向用户发送出错的信息，但有时用户需要自己识别出错条件并进行处理（如识别存储过程、复杂的批处理中出现的错误并处理），此时可用RAISERROR语句（引发错误）进行。 1．系统中的错误信息 错误信息中包含4项内容： 错误号 是一个特殊的整数，每种类型的错误有唯一的错误号。 严重性 用以描述错误的基本类型。级别值包括0和10~25之间的任何整数。0和10是通知性的，返回给用户时值都为0；11—16是用户能够纠正的错误（语法错误、违反安全性、违反数据合法性等）；17—19表示软件或硬件出现了问题；20—25表示系统出现问题。 状态 描述错误的调用状态。值在1—127之间。 消息 错误文本。 当要对一个错误进行处理时，必须提供错误号或错误文本。若仅传递了错误号，则系统会在master.sysmessages表查找对应的错误文本；若仅传递了错误文本，则系统会自动为@@error赋予一个消息号@@error=50000。如： if @@error=3 raiserror(' result in a error：',3,1) --严重性为3，状态为1，错误号自动设为50000 raiserror('%s error %d',3,1,'此处',555) 2*．管理SQL错误消息 SQL提供了与预定义事件相关的错误消息，这些消息存放在master数据库的sysmessages表中，用户可以向该表中添加自定义的消息，并可以利用RAISEERROR语句（引发错误）访问这些消息。 例如，在运行某语句时输入存在语法错误时，查询分析器提示如下错误： 服务器: 消息 170，级别 15，状态 1，行 2 第 2 行: 'go主' 附近有语法错误。  可以使用语句select * from sysmessages where error=170到sysmessage表中查看对应的消息内容。 SQL系统提供了3个存储过程来供用户管理自己的错误消息： sp_addmessage用于向表中添加消息 Sp_addmessgae msg_id, severity, 文本信息 [, language[, TRUE | FALSE[, REPLACE ]]  其中，msg_id为消息的错误号（必须大于50000），severity为错误的严重性，TRUE|FALSE表示当产生此错误时是否将其记录在日志中。如果是修改已经存在的消息，则需要使用REPLACE关键字。language表示消息语言的版本，对于同一错误号，可以有多种不同语言的消息（必须先添加us_english版本，才能添加中文版本。添加中文版本时不需指定language值，但添加us_english版本时必须指定language值）。 文本信息中可包括%s、%d等。 Sp_addmessage 50002,1,'hello',’us_english’ --添加us_english版本 go sp_addmessage 50002,1,'hello' --添加中文版本 sp_addmessage 50003,1,'%s hello', 'us_english' select * from sysmessages where error>50000  sp_altermessage用于修改消息的登录行为（是否将消息保存到日志中） sp_altermessage msg_id, WITH_LOG , TRUE | FALSE  例1： sp_altermessage 50002,WITH_LOG,TRUE select * from sysmessages where error>50000  sp_dropmessage用于从表中删除错误消息 sp_dropmessage msg_id [,language |'all' ]  如果没有指明消息的语言，则默认为本地语言（中文版中为中文），如果使用'ALL'，则将删除消息号对应所有语言的消息。 例2： sp_dropmessage 50002,'all' select * from sysmessages where error>50000  3*．RAISERROR语句：引发错误 返回用户定义的错误信息并设置系统标志。用于应用程序中编程时用。 RAISERROR (msg_id | msg_str , severity ,state [,argument [,…n]] ) [WITH LOG] 或 RAISERROR msg_id msg_str  msg_id为存储在sysmessages表中的用户定义的错误信息号。 msg_str表示特殊的消息，其格式与C语言中的输出字符串格式相似，该消息中最多可包含400个字符，超过时显示前397个及省略号。此时将使用severity ,state动态地生成消息号为50000的消息。 severity错误的严重性。超过18的级别必须有WITH LOG选项。 state：错误状态。为1—127之间的任意整数，默认为1。 argument用于代替msg_str中出现的变量（如%s、%d等）或msg_id对应消息所需的参数。 例1： raiserror(' error' ,1,1) raiserror('%s error %d',1,1,'此处',555) raiserror(50002,12,1) raiserror(50003,12,1, 'cbf')      授课题目（章、节） 第十三讲 数据库索引与视图  学时 3 授课时间 周3第3～5节 第( 13 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、能通过企业管理器创建索引和视图； 2、能使用SQL语句创建、删除索引； 3、能使用SQL语句创建、删除各种视图； 4、能熟练使用SQL语句对视图进行操作。  教学重点、难点： 1、创建视图； 2、使用SQL语句对视图进行操作。  大体内容 教学方法 时间安排  索引、视图的概念 面授   创建与删除索引 面授   创建与删除视图 面授   查询和更新视图 面授                   教研室审阅意见： 教研室主任签名： 年 月 日  教学过程(基本内容) 辅助手段 备注   时间分配   数据库索引概述 索引主要用于提高查询的效率，同时能加速ORDER BY和GROUP BY子句的操作；但它会降低插入、更新数据的速度。 例如：如何快速地找到“GROUP BY”词组在教科书中的位置？在无教科书目录时：只能在书中漫无目的地、随机翻寻直到找到为止；在有教科书目录时：先在目录中按一定的方法（如折半查找）查找到所在页码，再将书翻到大概一半处，若要找的页码比一半处的页码小，则继续在前半本书中折半查找，否则在后半本书中折半查找等等。上述教科书目录就是索引。 索引：在字段列上建立的一种数据库对象，是表中数据（按一定方式排列后）和相应存储位置的列表。它使得表中的记录能够按照数据的逻辑顺序来存取，提高了数据的检索速度。 索引由一组页（Page）组成，这些页构成树型结构：根页通过指向另外2个或多个页，把一个表的记录从逻辑上分为2个或多个部分；而这2个或多个页又分别把记录分割成更小的部分；这样继续下去直到到达叶子页。可有多级索引。 索引分类（按结构分）： 聚簇索引：可以保证表中记录的物理存储次序与索引表中的顺序（即逻辑顺序）相同（如书的内容和目录之间的关系：目录的顺序与实际的页码顺序相同）。在SQL数据库中，每个表只能有1个聚簇索引，因为表只能以一种物理顺序存储。一般按主关键字建立聚簇索引（也可按其它字段进行）。 非聚簇索引：表中记录的物理存储次序与索引表中的顺序（即逻辑顺序）一般不相同（也可相同）（如书后的关键字索引表：目录的顺序与实际的页码顺序不相同）。 两种索引的对比 存取数据时（比如存取若干条记录），聚簇索引的表比非聚簇索引的表速度快。 在数量上，每个表只能有1个聚簇索引，可以有多个（249）非聚簇索引（但太多的索引会耗费空间和内存）。 在存储空间上，非聚簇索引需要更多的磁盘空间和内存。 随着表中数据的改变，聚簇索引会自动更新，但非聚簇索引需人工更新。 索引分类（按功能分）： 唯一索引：每个索引值是唯一的。 非唯一索引：索引值可以相同。 索引分类（按来源分）： 基表索引：根据基本表创建的索引。 视图索引：根据视图创建的索引。 索引的创建与删除 1．创建索引的SQL语句 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX <索引名> ON {表名} (字段名 [ ASC | DESC ][,…n ]) [with [PAD_INDEX][[,]FILLFACTOR=fillfactor][[,]IGNORE_DUP_KEY][[,]DROP_EXISTING]???? [[,]STATISTICS_NORECOMPUTE] [[,]SORT_IN_TEMPDB]] [ ON filegroup ]  UNIQUE 指定创建唯一索引。要求索引的字段值唯一，如对学生的“姓名“字段不能创建UNIQUE索引，因为可能会存在相同的姓名。 CLUSTERED|NONCLUSTERED 指定所创建索引的类型（聚簇索引/非聚簇索引）。缺省时创建非聚簇索引。 PAD_INDEX：填充索引，即指定索引中间级每个页（节点）上保持开放的空间，以便当增加索引值时有空间存放，否则需花费时间将页面分裂才能放入新索引值。 FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的叶级数据占索引页大小的百分比，fillfactor的值为1到100。 IGNORE_DUP_KEY：用于控制当往包含于一个唯一索引中的列中插入重复数据时SQL Server所作的反应。 DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。 STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。 SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。 ON filegroup：用于指定存放索引的文件组。 2．通过SQL创建聚簇索引 聚簇索引最适合于范围搜索，因为逻辑上相邻的记录被物理地存放在相同或相邻近的页面上。 在创建表时，若表中存在PRIMARY KEY字段，则会自动建立一个聚簇索引，索引名称为PK_表名称。 例1：为学生表按照学号降序建立一个聚簇索引，名为“学号索引”。（当表中含有主键时，由于已有聚簇索引，此时用户不能再创建聚簇索引。必须先删除主键及聚簇索引再重新创建聚簇索引。） create unique clustered index 学号索引 on 学生表(学号 desc) with pad_index, fillfactor=20, ignore_dup_key, drop_existing, statistics_norecompute  通过企业管理器创建聚簇索引 右击相应的表，选择“所有任务”中的“管理索引”进行。 除去：当对现有索引修改时，是否删除同名索引。因为“修改索引”相当于用新的参数重新创建该索引。 填充索引：在索引的每个内部节点上留出空间，以便当增加索引值时有空间存放，否则需花费时间将页面分裂才能放入新索引值。 填充因子：指定 SQL Server 在创建索引过程中，对各索引页的叶级所进行填充的程度。 通过SQL创建非聚簇索引 注意：同一字段上既可有聚簇索引，也可同时有非聚簇索引。 --先涣散出索引，再 create clustered index 学号索引 on 学生表(学号 desc) create index 学号索引1 on 学生表(学号)  通过企业管理器创建非聚簇索引 右击相应的表，选择“所有任务”中的“管理索引”进行。 6．通过企业管理器和SQL创建唯一索引 唯一索引的特征： 不允许两行具有相同的索引值。 能够实现实体完整性。 在创建主键约束和唯一约束时自动创建。 例1：按学号为学生表创建一个唯一索引“学号索引”。 企业管理器中（略） create unique nonclustered index 学号索引2 on 学生表(学号)  例2：按姓名为学生表创建一个唯一索引“姓名索引”。因为学生表中姓名存在重复值，所以在执行本题语句时将出现错误。 7．创建复合索引（即由两个或多个字段构成的索引） 复合索引的特征： 多个字段的索引 将多个索引字段作为一个单元（整体）进行搜索 索引字段的次序可以与表中次序不同 注意点： 只有当WHERE子句中指定了索引的第一个字段时才使用该复合索引。 被频繁访问的字段都应创建（复合）索引。 索引字段不要过多（<=3个最好）。 索引字段的次序很重要。如在（字段1,字段2）的组合和（字段2,字段1）上创建的索引的作用是不同的。 在次序上应首先定义最具唯一性的字段。 例：按学号和姓名为学生表创建一个复合索引“学号姓名索引”。 企业管理器中（略） create index 学号姓名索引 on 学生表(学号,姓名)  8．删除索引 drop index 学号姓名索引 9．通过向导创建索引 打开企业管理器工具栏上的“向导”展开“数据库”即可进行。 10、通过索引优化向导创建和优化索引 打开企业管理器工具栏上的“向导”展开“管理”即可进行。对话框中的“工作负荷”指保存的查询等代码文件（在查询分析器中保存即可）。 数据库视图概述 视图（也叫查询视图）是从逻辑上描述一个或多个表中的记录。为数据库对象。 基本表：本身独立存在的表。 视图：从一个或多个基本表中导出的结果。 如果在SQL中创建了视图，则数据库中仅存放视图的定义，而没有保存视图相应的记录数据，故也称视图为虚表，但用户可以像对基本表一样对它进行各种操作。当基本表的数据发生变化，相应的视图也会随之变化，即视图相当于一个窗口，通过它可以看到数据库中自己感兴趣的数据及其变化。 相对于表而言，视图可节省空间，只有运行时才占用空间来暂时存放结果；相对于查询而言，视图定义可保存在数据库中，而查询不能。视图常用来将用户与基本表隔离开来以保护基本表，即将用户常用的数据以视图的形式提供给用户，用户只能访问视图。 视图的创建 1．通过企业管理器创建视图 例1：创建一个视图，它只显示学生表中专业是计算机的学生的学号、姓名、年龄和所在院系。 打开企业管理器中的学生课程数据库，右击视图，选择新建视图。在关系图窗格中右击添加表。 注意：视图属性对话框中的With Ties：如果查询包含 ORDER BY 子句和基于百分比的 TOP 子句，WITH TIES 将非常有用。如果设置该选项，并且百分比截止在 ORDER BY 子句中具有相同值的一组行中间，则查询将扩大百分比的范围直到包含所有这样的行。对非百分比同样适用。 *Group By扩展项：参阅F1帮助文件（用多个分组字段才看得出来）。 2．使用SQL语句创建视图 （1）创建基本视图 CREATE VIEW <视图名>[(<字段名>[,<字段名>]…)] AS <Select子查询> [WITH CHECK OPTION]  WITH CHECK OPTION表示在对视图进行数据操作(insert、update)时必须满足子查询中的条件表达式。 字段表达式只能全部列出（与子查询返回的字段相同）或全部省略（由子查询返回的字段构成）。 以下情况中必须列出所有字段名（非单纯字段名可用别名，但不允许缺省）： 子查询中某个列不是单纯的字段名，而是集合函数或字段表达式 在多表连接时选择了同名字段作为视图的字段 需要在视图中给字段重新命名（直接在视图字段名中用新名，不要再写原来的字段名或as等） 在语句中不能使用ORDER BY、INTO、DISTINCT等子句或短语。子查询不能是update、delete?。 例1：创建一个视图，它只显示学生表中专业是计算机的学生的学号、姓名、年龄和所在院系。 create view 计算机学生视图 as select 学号,姓名,年龄,所在院系 from 学生表 where 所在院系='计算机'  例2：检查上例创建的视图。 select * from 计算机学生视图  注意：不能将例1、例2放在一起执行，除非在它们之间加上“go”。 例3：创建计算机系中选修了C801课程的学生视图。 create view 计算机学生选课 as select S.学号,姓名,年龄,性别,所在院系 from 学生表 S,成绩表 C where S.学号=C.学号 and 所在院系='计算机' and  课程号=’C801’ go select * from 计算机学生选课  例4：创建计算机系中选修了C801课程并且性别为男的学生视图。 本例是在上例创建的视图上操作： create view 计算机男学生选课 as select 学号,姓名,年龄,性别,所在院系 from 计算机学生选课 where 性别=’男’ go select * from 计算机男学生选课  （2）创建表达式视图（即视图中的某些字段来自于子查询中的字段表达式）、分组视图（即集合函数视图） 例1：创建一个学生出生年份的视图。 Create view学生出生年份(学号,姓名,出生年份) as select 学号,姓名,2003-年龄 from 学生表 go select * from 学生出生年份  例2：将学生的学号和平均成绩定义为一个视图。 Create view学号分组成绩(学号,平均成绩) as select 学号,AVG(成绩) from 成绩表 group by 学号 go select * from 学号分组成绩  （3）使用WITH CHECK OPTION子句 使用下述语句创建一个视图： Create view 计算机学生 as select 学号,姓名,年龄,所在院系 from 学生表 where 所在院系='计算机' with check option go select * from 计算机学生  然后再通过insert语句向视图中插入记录('200203011','MIKE',18,'计算机') 和('200203012','joe',18,'电子学')，查看运行结果。再将'200203011'的“所在院系”改一下。 注意：若在企业管理器中直接修改，不会出错！ 对视图进行查询 完全类似于对表的查询。 例1：在“计算机学生视图”上，列出年龄小于20的学生。 例2：在“学号分组成绩”视图上，列出平均成绩大于80分的学号和平均成绩。 例3：根据“计算机学生视图”及相关表，查询计算机系学生选修了C804的学生清单。 Select J.学号,姓名,年龄,所在院系 from计算机学生视图 J,成绩表 C where J.学号=C.学号 and 课程号=’C804’   更新视图 对视图所作的数据更新操作，最终影响的是基本表。建议最好在创建视图时加上“WITH CHECK OPTION”以防止不符合条件的数据进入基本表。 1．插入操作 INSERT INTO 视图名称（字段列表）VALUES（值列表） 例：向“计算机学生”视图中插入一条新记录('200303007','KKK',17,'物理学')。 再到学生表中查看一下。 2．修改操作 UPDATE 视图名称 SET 字段=值[…] WHERE 条件 例：将“计算机学生”视图中学号为'200203001'的姓名改为’MMM’。 3．删除操作 DELETE FROM 视图名称 WHERE 条件 例：将“计算机学生”学号为'200203001'的学生删除。 注意：并不是所有的视图都是可更新的，如表达式视图、分组视图等， 例：update 学生出生年份 set 出生年份=1978 where 学号='200203003'是错误的。 删除视图 drop view 视图名    授课题目（章、节） 第十四讲 事务处理、存储过程  学时 3 授课时间 周3第3～5节 第( 14 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、了解显式事务与隐式事务的异同； 2、能根据需要编写显式事务程序； 3、了解SQL Server的锁机制； 4、掌握存储过程的使用方法； 5、能根据需要熟练编写存储过程。  教学重点、难点： 1、根据需要编写显式事务程序； 2、根据需要编写存储过程。  大体内容 教学方法 时间安排  事务处理控制语句 面授   SQL Server 的锁机制 面授   事务的编程 面授   创建、修改、执行、删除存储过程 面授   存储过程及其参数、存储过程的状态值 面授    面授           教研室审阅意见： 教研室主任签名： 年 月 日   教学过程(基本内容) 辅助手段 备注   时间分配   事务是并发控制的基本单位。事务是一个逻辑工作单元，它必须完整地执行，或者全都不执行，它是一个不可分割的工作单位。事务处理是保证数据库一致性状态的重要方法。 一、事务处理概述 举例说明事务操作的作用：银行资金转帐(A帐户提取1万元，存入B帐户)；库存出货与收费等过程。 事务常由2次或多次数据库操作组成。而1次数据库操作相当于在事务处理中的一个SQL语句。 一个事务的操作必须具备以下4个属性，即 ACID： 原子性（Atomicity）：事务必须作为工作的最小单位，即原子单位，其所进行的操作要么全部执行，要么全不执行。 一致性（Consistency）：事务完成后，所有的数据必须保持其合法性，即所有数据必须遵守数据库的约束和规则。现有的DBMS系统大多都提供了事务管理功能用以保证事务的一致性。 隔离性（Isolation）：一个事务所做的修改必须与其他事务所做的修改隔离。一个事务所使用的数据必须是另一个并发事务完成前或完成后的数据，而不能是另一事务执行过程中的中间数据。就是说，两个事务是相互隔离的，其中间状态的数据是不可见的。 持久性（Durability）：事务执行完成后，其对数据库的修改将永久保持。 注意：只包含1个操作的事务也可能因服务器故障而无法进行下去，此时也能自动回到该操作开始处。 在 SQL Server 2000中事务的模式(即事务以何种方式出现)可分为显式事务、隐式事务2种。 1．显式事务 显式事务（即用户自定义事务）。它是由用户使用 TransactSQL语言的事务语句定义的事务，具有明显的开始和结束的标志，由1条或多条TransactSQL语句组成。 2．隐式事务 隐式事务指在当前事务提交或回滚后，由 SQL Server自动启动的新事务。所以在隐式事务模式中，用户不需要使用BEGIN TRANSACTION语句标识事务的开始，也不需要使用 COMMIT TRANSACTION、ROLLBACK TRANSACTION 提交或回滚每个事务，它也由1条或多条TransactSQL语句组成。 当 SQL Server处于隐式事务模式时，碰到以下任一语句时，SQL Server都会认为是开始了一个事务（即好象看到了BEGIN TRANSACTION和COMMIT TRANSACTION语句）： CREATE、DROP、TRUNCATE TABLE、SELECT、INSERT、UPDATE、DELETE、GRANT、FETCH 二、数据一致性问题 当要求连续完成的操作在进行的过程中受到其它因素的干扰而导致部分操作未完成时，必然会导致某些数据的不一致。 如果引入了事务处理的方式，则当上述情况发生时可以将已经被修改的数据恢复到处理之前的状态。 事务是由用户或程序员根据实际情况制定的，而不是由SQL系统决定的。所以，如果事务不符合业务规则或者是错误的，SQL系统也会遵照执行。 三、事务处理控制语句 SQL Server通过事务处理控制语句将个语句集合分组后，形成独立的逻辑工作单元。 1．BEGIN TRANSACTION [事务名] 表示事务的开始。 注意：TRANSACTION可简写为TRAN。 2．COMMIT TRANSACTION [事务名] 提交事务中的所有操作。 3．ROLLBACK TRANSACTION [事务名 | 保存点] 撤消事务所进行的操作（主要指与数据变化有关的操作，对select，显示出的结果仍在，撤消不撤消无关），恢复到事务之前或保存点之前的数据状态。 4．SAVE TRANSACTION 保存点 注意：任一事务以“BEGIN TRANSACTION”开始（此时@@trancount自动加1），到ROLLBACK TRANSACTION或COMMIT TRANSACTION（此时@@trancount自动减1）结束。 在SQL中，事务管理包含3个方面： 事务控制语句 锁机制：用于封锁正在被一个事务修改的数据 事务日志 四*、SQL的锁机制 锁：是一种安全机制，用于控制多个用户的并发操作。在事务执行前、后数据库将处于一致性的状态，但在事务的执行期间数据库可能处于暂时的不一致状态。若在数据库不一致状态时读取数据，将产生不一致的问题，为此，可对所修改的对象（表、字段等）进行封锁，其它事务必须等到此事务解锁之后才能访问该数据。 1．封锁 可以封锁的对象有：字段、记录、表和数据库。 SQL中具有自动封锁和强制封锁的功能。 封锁的类型： 共享锁，又称为读锁（S锁）。如果数据被加了S锁，则其它事务也只能对它加S锁，其它事务可以读该数据，但不能修改。 排它锁，又称写锁（X锁）。如果数据被加了X锁，则其它事务不能对它加任何锁，也不能读取该数据。 更新锁，又称U锁。用于预定对某数据要加X锁，此时其它事务可以读数据（其实此时相当于共享锁），但不能加U锁或X锁。当数据要被更新时，则升级为X锁。 锁的相容性：读操作（如SELECT）可获得共享锁，写操作（如INSERT、DELETE）获得排它锁，更新操作（如UPDATE）首先获得更新锁，然后升级为排它锁。 共享锁 更新锁 排它锁  共享锁 是 是 否  更新锁 是 否 否  排它锁 否 否 否  封锁的粒度（封锁对象的大小）：封锁的对象可以是逻辑单元，也可以是物理单元，可以是数据库、表、行、列、页、块等。 获取锁的信息：sp_lock，即进程拥有的锁的信息，进程的标识号存储在master.dbo.sysprocesses中。 例如： sp_lock sp_lock 52 ----52为查询分析器进程标识号，在master.dbo.sysprocesses中 2．死锁 如果存在2个事务T1和T2： T1存取数据X和Y； T2存取数据Y和X。 此时X被T1封锁，Y被T2封锁，而T1等待T2释放Y，T2等待T1释放X，形成无限制的等待而造成死锁。 SQL系统能够自动发现并解除死锁。当发现死锁时，系统选择累计CPU时间最少的用户先停止，让其它用户继续执行，此时系统发送错误号1205给被停止的用户。 避免死锁应遵循的原则： 在所有事务中都按同一顺序访问各个表。 事务应该尽量小且尽快提交 尽量避免人工输入出现在事务中 尽量避免同时执行INSERT、UPDATE、DELETE等数据修改操作 五、事务的编程 1．显式事务 例1：通过事务将C804课程的成绩加15％，C807课程的成绩加10％。 Begin tran update 成绩表 set 成绩=成绩*1.15 where 课程号='c804' update 成绩表 set 成绩=成绩*1.10 where 课程号='c807' commit tran  例2：通过事务将C804课程的成绩减15％，C802课程的成绩减10％。最后取消。 Begin tran abc update 成绩表 set 成绩=成绩/1.15 where 课程号='c804' update 成绩表 set 成绩=成绩/1.10 where 课程号='c802' rollback tran abc  例3：通过事务将C804课程的成绩减15％，C802课程的成绩减10％。最后取消后一个操作。 Begin tran update 成绩表 set 成绩=成绩/1.15 where 课程号='c804' save tran breakpoint1 update 成绩表 set 成绩=成绩/1.10 where 课程号='c802' rollback tran breakpoint1 select * from 成绩表   2．隐式事务 没有使用BEGIN TRANSACTION的语句被认为是一个事务—隐式事务。 例如： insert into 学生表 values('200308002','吴娜','女',21,'国际贸易','国贸1班','8/29/2003') insert into 学生表 values('200308003','张娜','女',22,'计算机','软件1班','8/29/2003') 相当于： begin tran insert into 学生表 values('200308002','吴娜','女',21,'国际贸易','国贸1班','8/29/2003') commit tran begin tran insert into 学生表 values('200308003','张娜','女',22,'计算机','软件1班','8/29/2003') commit tran 3．事务与批处理 一组T-SQL语句可能只有部分完成。若希望要么全部操作，要么一个都不执行，其解决办法就是把该组语句组织成一个事务（并包含回滚语句），如上述的例1及例2。 事务中还常使用判断语句。 例： Begin transaction update 学生表 set 年龄=年龄+1 insert into 学生表 values('200308004','吴尼','女',22,'国际贸易','国贸2班','8/29/204') if @@error=0 begin select '插入成功' select * from 学生表 where 学号='200308004' commit transaction end else begin select '插入失败' rollback transaction end  批处理是指包含一条或多条Transact-SQL语句的语句组，这样的语句组将从应用程序 一次性地发送到SQLServer服务器执行。而SQL Server服务器将批处理语句编译成一个可 执行单元，这种单元称为执行计划(如果批处理中某一条语句发生编译错误，那么将导致执行计划无法编译，从而批处理中的任何语句都无法执行。事务也是如此)。 经过编译后的批处理仍然可能在运行时产生错误，通常情况下，这些运行错误将导致批处理停止执行当前语句，而继续执行批处理中其他所有语句。事务虽然也导致停止执行当前语句并继续向下执行，但当它遇到回滚语句时可取消已执行的结果。 在书写批处理语句时，需要使用GO语句作为批处理命令的结束标志。当编译器读取到GO语句时，它会把GO语句前面所有的语句当作一个批处理，并将这些语句打包发送给服务器。GO语句本身并不是Transact-SQL语句的组成部分，它只是一个用于表示批处理结束的前端指令。 六、显式事务中不能包含的语句 Create Database、Alter Database、Drop Database、Restore Database Backup Log、Reconfigure、Restore Log、Update Statistics 七、存储过程 （一）、概述 存储过程是由被编译在一起的一组SQL语句组成的集合，它能通过调用被执行，其参数可以被传递，其出错代码可以被检验。它是独立于表之外的数据库对象。系统预装了许多系统存储过程(sp_)到master中，以便进行系统表的检索和修改（见P90系统存储过程表）。 SQL中存储和执行存储过程的2种方法： 存储在本地计算机上，并将它发送给服务器和应用程序； 存储在SQL服务器上直接执行。 存储过程的特点： 可以接收参数，并能够通过参数返回多个值 可以包含对其它存储过程的调用 可以给调用者返回一个状态值，表示操作是否成功 可以使用Execute调用存储过程存储过程不能用于表达式中 能够在服务器上存储用户的应用程序，在服务器上就可以对数据做出有关决策，而不把结果返回给客户机 使用存储过程（而不使用T-SQL程序）的原因： 模块化编程：可反复被调用。 快速执行：第一次执行时进行分析优化并驻留内存，以后直接执行。 减少网络通信量：只要一条调用语句就可执行存储在服务器上的存储过程。 安全机制：可只能执行而不让修改。 （二）、创建和修改存储过程 可利用向导、企业管理器、T-Sql进行。以下只讲T-Sql。 1．创建存储过程 用户只能在当前数据库中创建存储过程 数据库所有者有默认的权限创建存储过程，也可以将该权限转让给其它用户 存储过程的名称存放在sysobjects表中(select * from sysobjects where name='……')，文本存放在syscomments表中(select * from syscomments where id='……')。 CREATE PROC [ EDURE ] procedure_name [ ; number ]????[ { @parameter data_type }????????[ VARYING ] [ = default ] [ OUTPUT ]????] [ ,...n ]  [ WITH????{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]  [ FOR REPLICATION ]  AS sql_statement [ ...n ]  Procedure_name是新存储过程的名称，在名称之前加#可以创建局部临时过程，在名称之前加##可以创建全局临时过程。 Number用于对同名的过程分组以便可用一条drop proc语句全部删除它们。同名的第一个存储过程默认为’；1’。 @Parameter用于指明过程中的参数，最多可以有2100个参数。 Data_type为参数的类型。 VARYING 指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。 =default用于指明参数的默认值。 OUTPUT表明参数是返回参数 RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION表示执行时是否重新编译（当经常使用非典型值或临时值时常用它）或加密（对syscomments表中的文本加密）。 一般，对非典型值（或临时值）使用的优化方案应有所区别，如果不用RECOMPILE，则对它们使用的优化方案均为第一次执行时所使用的驻留在内存中的优化方案，这对于它们显然是不合适的。如： create proc abc @max money,@min money AS select * from 表 where price between @min and @max 那么第一次执行时若满足条件的记录较多则查询优化器会使用扫描全表的方式查询，反之使用索引方式查询。以后不管数据如何变化，只要不重新编译，则均使用第一次的查询优化方案。 AS表示过程所对应的SQL语句。 FOR REPLICATION ：创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。 2．修改存储过程 修改的格式与创建相似。 ALTER PROC [ EDURE ] procedure_name [ ; number ]????[ { @parameter data_type }????????[ VARYING ] [ = default ] [ OUTPUT ]????] [ ,...n ]  [ WITH????{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]  [ FOR REPLICATION ]  AS sql_statement [ ...n ]  例1： --定义 create proc padd1 @x int=0,@y int=0,@z int output --定义时参数可加括号，但调用时不可加！ as set @z=@x+@y --调用 declare @a int exec padd1 7,9,@a --可以不使用输出变量 select @a --null exec padd1 7,default,@a output --使用缺省值用’default’ select @a --7 exec padd1 7,2,@a output select @a --9   注意：以上定义语句和调用语句不能一起执行，除非加了go。否则调用语句会被当成定义的一部分，因其跟在as后。 例2： --定义 create proc padd2 @x int=0,@y int=0,@z int output as select @z=@x+@y go --调用 declare @a int declare @i int,@j int select @i=9,@j=8 exec padd2 @i,@j,@a output select @a  例3： --定义 create proc find @name char(8) as select * from 学生表 where 姓名=@name go --调用 exec find '李涛'  例4： --定义 alter proc find @name char(8) with recompile,encryption as select * from 学生表 where 姓名=@name go --调用 exec find '李涛'  例5： --定义 alter proc find @name char(8),@n int output as select * from 学生表 where 姓名=@name set @n=@@rowcount go --调用 declare @xx int exec find '李涛',@xx output select @xx  3．临时存储过程 临时存储过程可以由任何用户创建。 局部临时过程只有创建者可以调用，在当前会话结束时会自动删除之；全局临时过程可以由所有用户调用，在当前会话结束时也会自动删除之。 （三）、执行存储过程 执行存储过程有两种方法： 直接通过查询窗口执行； 需要运行多个存储过程时，可以通过Execute（或Exec）调用。 （四）、删除、重命名、查看存储过程 1、删除：drop procedure 存储过程名 如：drop proc find --删除所有find，包括find；1、find；2等 drop proc find；3 --只删除find；3 2、重命名：sp_rename 原存储过程名，新存储过程名 3、查看 sp_help [[@objname=] name] --显示存储过程的参数及其数据类型，name为要查看的存储过程的名称。 sp_helptext [[@objname=] name] --显示存储过程的源代码sp_depends [@objname=] name --显示和存储过程相关的数据库对象sp_stored_procedures --返回当前数据库中的存储过程列表 （五）、存储过程及其参数 输入参数：允许调用程序向存储过程传送数据。 输出参数：允许存储过程将数据或指针变量传回调用程序。 存储过程向调用程序返回一个整型返回代码，如果存储过程没有显式地指定返回代码值则返回0值。 1．输入参数 在Create Proc语句中定义，也需要@开头。最多255个参数。 如： create proc myproc1 @sex char(2) as select * from 学生表 where 性别=@sex return go exec myproc1 '女' go 2．输出参数 在create 和 exec 中都必须使用‘OUTPUT’。 如： create proc myproc2 @name char(8)=null,@age int output as if @name is null begin print '查找谁？' return end select @age=年龄 from 学生表 where 姓名=@name return go declare @ages int exec myproc2 '沈香娜',@ages output select @ages 3．参数的顺序 遵循一一对应的原则。但当参数以名字传递时，顺序任意，否则必须与定义顺序相同。 如： declare @a int exec find @y=1,@x=2,@z=@a output select @a exec find @x=2,@y=1,@z=@a output select @a exec find @x=2,@z=@a output,@y=1 select @a   （六）、存储过程的状态值（通过RETURN返回） 如果返回值为0，则表示成功执行了存储过程；若返回-99 — -1则表示没有成功；若返回大于0或小于-99则表示返回的为用户定义的状态值。 1．用RETURN语句返回状态值 return 整数表达式 2．捕获返回的状态值 exec @变量名=proc_name 参数 例1： --定义(不带参数) create proc pa1 as if exists(select * from 学生表 where 姓名='李涛') return 2 else return 3 go --调用 declare @x int exec @x=pa1 if @x=2 print '已找到！' else if @x=3 print '没找到'   例2： --定义（带参数） create proc pa2 @name char(8)=null as if @name is null --不能用‘@name=null’判断！ return 1 else if exists(select * from 学生表 where 姓名=@name) return 2 else return 3 go --调用 declare @x int exec @x=pa2 '李涛' if @x=1 print '没有给出名字！' else if @x=2 print '已找到！' else if @x=3 print '没找到'   3．SQL内部定义的状态值 状态值 含义  0 成功  -1 丢失参照对象  -2 数据类型不匹配  -3 发生死锁  -4 权限错误  -5 语法错误  -6 用户错误  -7 资源错误  -8 内部产生错误  -9 系统资源到极限  -10 内部不一致  -11 致命的内部不一致  -12 表或索引错误  -13 数据库错误  -14 硬件错误      授课题目（章、节） 第十五讲 触发器与数据库安全  学时 3 授课时间 周3第3～5节 第( 15 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、了解触发器的工作原理； 2、掌握触发器的使用方法； 3、能根据需要编写触发器； 4、掌握保障数据库安全的方法。  教学重点、难点： 1、根据需要编写触发器； 2、根据需要进行数据库安全性设置和管理  大体内容 教学方法 时间安排  触发器及其功能、工作原理 面授   触发器的创建、删除和修改、显示 面授   触发器和事务 面授   使用企业管理器备份和恢复数据库 面授   两种身份验证模式及其设置 面授   创建和管理用户、角色 面授   权限管理 面授       教研室审阅意见： 教研室主任签名： 年 月 日  教学过程(基本内容) 辅助手段 备注   时间分配   一、触发器及其功能 触发器（Trigger）是一种特殊类型的存储过程，只要对它所保护的数据进行修改，它就会自动触发，即当对被保护的数据修改时，系统会自动调用相关的触发器。 触发器有3种类型：insert触发器、update触发器和delete触发器。 触发器的主要作用： 能够实现数据的完整性和数据的一致性 增强约束：它能实现比check约束更为复杂的约束。 跟踪、监督数据库：它能检测到数据库内的所有操作。 级联触发 存储过程的调用：它可调用一个或多个过程。 二、创建触发器 1．使用语句创建 CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { FOR | AFTER | INSTEAD OF } {[DELETE][,][INSERT][,][UPDATE]} [ WITH APPEND ]????????[ NOT FOR REPLICATION ]?????? AS [IF UPDATE(column) [{AND|OR}UPDATE(column)] […n] --进行insert、update时均用update(column)判断 ] sql_statement[…n]  WITH APPEND：指定应该添加现有类型的其它触发器。 for、after为不同版本中的情况，高版本中将取消for型。AFTER：指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定 FOR 关键字，则 AFTER 是默认设置。不能在视图上定义 AFTER 触发器。 INSTEAD OF：指定执行触发器而不是执行触发的SQL 语句，从而替代触发语句的操作。 例1： create trigger ta on 学生表 for update,insert as if update(年龄) print '某条记录的年龄被修改了' update 学生表 set 年龄=20 where 姓名='沈香娜' insert into 学生表(学号,年龄) values('900898768',20) delete from学生表 where 学号='900898768'   例2： Create trigger tb on 学生表 for delete as if @@rowcount<>0 print '某条记录已delete了' else print '没有记录delete' delete from 学生表 where 学号='900898768'   例3： Create trigger tc on 学生表 for delete as delete from 成绩表 where 学号 in (select 学号 from deleted) select * from 成绩表  注：在触发器中，可以使用特殊的表deleted和inserted，用于判断哪些记录是刚被删除或插入的。 2．使用企业管理器创建触发器 右击一个表，选择所有任务/管理触发器 3．注意事项 一个触发器只能用于一个表 因触发器是特殊的存储过程，故也是数据库对象。     教学过程(基本内容) 辅助手段 备注   时间分配   三、触发器的工作原理 每个触发器都有两个特殊的表：deleted（存储被删除的记录）和inserted（存储刚插入的记录），它们主要用来在发生故障时恢复数据用。 它们是逻辑表（不会、是物理表），由系统管理 存储在内存中，用户不能修改。当触发器完成时，被自动删除，故只能在触发器内部使用。 与触发器作用的表有相同的结构 1．inserted表 例： create trigger td on 学生表 for insert as print '插入了记录！' select * from inserted rollback tran go insert into 学生表(学号,年龄) values('900011001',24) 2．deleted表 例1： create trigger te on 学生表 for delete as insert into 学生表 select * from deleted go delete from 学生表 where 姓名='李涛' select * from 学生表  3．UPDATE操作的本质 对于表的删除和插入操作是基本操作，而更新操作是一个复合操作。 当进行更新操作时，先将需要更新的记录删除，然后再将新值的记录插入到表中。所以在deleted表中存放了旧值，inserted表中存放了新值。 例： create trigger tg on 学生表 for update as print 'deleted表' select * from deleted print 'inserted表' select * from inserted rollback tran go update 学生表 set 年龄=年龄+1 where 性别= '女' 四、删除和修改触发器 1．修改触发器 修改的格式与创建相似。 ALTER TRIGGER trigger_name ON { table | view }  FOR {[DELETE][,][INSERT][,][UPDATE]} AS sql_statement[…n]  2．删除触发器 对于同一类（如delete属于一类）触发器，同一时刻一个表只能有一个触发器发挥作用。所以为表创建同类 (如delete类或insert类或update类) 的其它名称触发器时，会自动替换掉原有的同类触发器。 当一个表被删除时，该表的所有触发器均同时被删除。 DROP TRIGGER {trigger}[…n] 例1：drop trigger tc 例2： create trigger th on 学生表 for insert,update as print '插入了记录！' select * from inserted rollback tran 和 create trigger ti on 学生表 for insert as print '插入完了吗？' 则ti中的insert会替换掉th中的insert；但th中的update不变。 五*、显示触发器 1．使用企业管理器显示触发器的内容 右击表，选择‘所有任务/管理触发器’后可看到各个触发器的名字、文本。 2．使用系统存储过程查看触发器 sp_help trigger_name ：显示触发器的一般信息（名字、所有者、创建日期等） sp_helptext trigger_name ：显示触发器的文本 sp_depends table_name ：显示表中各种依赖关系，即：哪一个触发器会使该表的内容发生变化。该表是出现在create trigger的AS中的表，而不是出现在ON后的表。 sp_depends trigger_name ：显示触发器所引用的表，该表也是出现在create trigger的AS中的表，而不是出现在ON后的表。 例： create trigger tk on 学生表 for delete as insert into 成绩表(学号,成绩) values('900100001',36) rollback tran go sp_depends 成绩表 sp_depends tk 3．查询sysobjects获得触发器信息 触发器存储在sysobjects表中 在sysobjects表中包含有指向insert、update、delete触发器的指针（即instrig、updtrig、deltrig字段） 触发器的程序文本存储在syscomments中，通过上述指针可找到 例： select id,name,object_name(instrig), object_name(updtrig), object_name(deltrig) from sysobjects where type='U' --U表示用户表 六、触发器与事务 1．事务与锁 在一个事务期间，SQL SERVER会对修改的页面自动加排它锁，一直锁到遇到了一个COMMIT TRAN或ROLLBACK TRAN为止，此时@@trancount置0。 2．触发器与事务 触发器被认为是执行数据修改事务的一部分（如被认为是UPDATE的一部分，即UPDATE与触发器一起组成了一个完整的事务，正因为如此，往往只写rollbak tran而省略begin tran）。 应用触发器主要是用来进行复杂的校验，故常在触发器中进行判断并进行回滚，但使用rollbak tran时要注意： 由触发器已执行的所有语句都被回滚，即回滚该事务（包括上述的update、insert、delete语句）。 触发器将继续执行触发器内rollbak tran之后的语句。 批处理中激发触发器以后的语句将不再被执行，即触发器中的rollbak tran将终止触发器外的批处理。（不同于存储过程：存储过程中的rollbak tran语句并不影响调用此存储过程的批处理之后的语句的继续执行） 例： create trigger rollback_ex on 学生表 for insert as print '回滚之前' rollback tran print '回滚之后' return go print '批处理，插入之前' insert into 学生表(学号,年龄) values('900200001',30) print '批处理，插入之后' --不会执行 select * from 学生表 --不会执行 七、数据库维护：备份和恢复数据库 1、数据库备份的理由 备份就是对SQL Server数据库或事务日志进行备份，数据库备份记录了在进行备份这一操作时数据库中所有数据的状态，以便在数据库遭到破坏时能够及时地将其恢复。备份的数据文本称为后备副本。 当系统发生如下故障时，会导致数据库中数据的丢失，因此需要对数据库进行定期的备份，以防止不必要的损失： 系统崩溃或死机、设备受破坏；无意或恶意地删除、修改数据；自然灾害；需要在计算机之间的传输数据；需要保存永久的数据档案等。 2、备份的种类 静态备份：备份期间不允许对数据库进行任何存取、修改活动。会降低数据库的可用性。 动态备份：备份期间允许对数据库进行存取或修改，即备份和用户事务可以并发执行。但是，备份结束后，后备副本上的数据并不能保证正确有效。 3、备份的考虑事项 将备份到何种介质上—磁盘还是磁带：考虑性能、考虑可恢复性。 什么时候进行备份：取决于因执行备份而产生的可接受的损失大小以及数据被改变的程度。通常DBA在用户数据吞吐最少的时候备份，这段时间称为维护窗口。 备份存储在何处：最好你的备份不要存储在数据所在的同一台机器上，应该为备份磁带找一个存放地点。 备份将保存多久：解决这个问题的一个常用备份策略是祖父—父亲—儿子备份计划，即祖父—月备份 —永远保存；父亲—周备份 —保存一年；儿子—日备份 —保存一月。 备份将如何被验证：当你第一次安装服务器时，在实际使用之前，运行备份，并把备份恢复到同一台服务器上，保证硬件都能正常运行；可以另外选择在开发用的或有质保的服务器上备份和恢复以保证能正常运行；找一家公司为你校验备份磁带。 4、SQL Server 2000四种备份方式 完全数据库备份（Dadabase-complete） 差异备份或称增量备份（Dadabase-differential） 事务日志备份（Transaction log） 数据库文件和文件组备份（File and filegroup） 5、创建备份设备 在进行备份以前首先必须指定或创建备份设备，备份设备是用来存储数据库、事务日志或文件和文件组备份的存储介质，备份设备可以是硬盘、磁带或管道。当使用磁盘时，SQL Server允许将本地主机硬盘和远程主机上的硬盘作为备份设备，备份设备在硬盘中是以文件的方式存储的。创建备份设备的两种方法： 使用SQL Server 企业管理器创建备份设备：展开树中的“管理”，右击“备份”。 使用系统存储过程创建备份设备，语法如下： sp_addumpdevice {‘device_type’} [,’logical_name’][,’physical_name’][,{{controller_type|’device_status’}}] device_type指disk、tape、pipe。 例：在磁盘上创建一个备份设备 sp_addumpdevice 'disk', 'backup_northwind', 'd:\cbf\bk_northwind.bak' 6、删除备份设备 删除备份设备与创建的过程类似，只须在企业管理器中选中要删除的备份设备，在弹出的菜单中选择删除选项即可删除该备份设备。或者使用sp_dropdevice语句来删除备份设备。其语法如下： sp_dropdevice [‘logical_name’][,’delfile’] 例：删除上面创建的备份设备 sp_dropdevice 'backup_northwind' 7、备份的执行和恢复：有三种数据库备份操作的方法： (1)SQL Server 企业管理器： (2)备份向导 (3)Transact-SQL语句：语法如下： Backup database {database_name|@database_name_var} to <backup_device>[,…n] [with [[,]format][[,]{init|noinit}][[,]restart]] <backup_device>::={backup_file_name|@backup_file_evar}|{disk|tape|pipe} ={temp_file_name|@temp_file_name_evar} 例：Backup database northwind to backup_northwind 8、恢复数据库 恢复是指将数据库备份加载到系统中的过程。系统在恢复数据库的过程中，自动执行安全性检查、重建数据库结构以及完整数据库内容。 使用企业管理器恢复数据库：选择还原数据库命令。 T-Sql.语法如下：? Restore database dbname [from <backup_device[],…n>] [with [[,]file=file_number] [[,]move ‘logical_file_name’ to ‘operating_system_file_name’] [[,]replace] [[,]{norecovery|recovery|standby=undo_file_name}] ] <backup_device>::={{backup_device_name|@backup_device_name_evar} |{disk|tape|pipe} ={temp_backup_device|@temp_backup_device_var} 例：use master Restore database northwind from backup_northwind 9、*恢复系统数据库 （1）关闭SQL Server，运行系统安装目录下的bin子目录下的rebuilem.exe文件(个人版中无)，这是个命令行程序，运行后可以重新创建系统数据库。 （2）系统数据库重新建立后，启动SQL Server。 （3）SQL Server启动后，系统数据库是空的，没有任何系统信息。因此，需要从备份数据库中恢复。一般是先恢复master数据库，再恢复msdb数据库，最后恢复model数据库。 10、数据库的维护 利用数据库的维护计划向导可以方便地设置数据库的核心维护任务，以便于定期地执行这些任务。 八、数据库安全 1、两种身份验证 Windows身份验证 SQL Server身份验证  当用户登陆到Windows域时，用户名和密码在被传送到Windows域控制器之前被加密 Windows系统从不验证用户  支持复杂加密、密码的截止日期和最短长度等密码策略 不支持密码策略  支持帐户锁定策略，在使用无效密码进行多次尝试后锁定帐户 不支持帐户锁定功能  在Windows98/me中不能使用 在Windows98/me中可以使用   2、选择身份验证模式 Windows身份验证模式：只能使用Windows身份验证。 SQL Server数据库系统通常运行在Windows NT服务器平台上，而NT作为网络操作系统，本身就具备管理登录、验证用户合法性的能力，因此Windows NT认证模式正是利用了这一用户安全性和帐号管理的机制，允许SQL Server使用NT的用户名和口令：用户只需要通过Windows NT的认证，就可以连接到SQL Server，而SQL Server本身也就不需要管理一套登录数据。 SQL Server混合模式：可以使用Windows和SQL Server身份验证。 混合认证模式允许用户使用Windows NT安全性或SQL Server安全性连接到SQL Server，即用户可以使用NT帐号（需先在SQL Server的“安全性”中映射成登录帐号）或SQL Server中新建的登录帐号登录到SQL Server系统。 3、利用企业管理器进行身份验证模式的设置 在企业管理器的树中，右键服务器名，从快捷菜单中选择“属性/安全性”进行设置（在启动服务帐户中设置当启动并运行SQL Server时默认的登录者中哪一位用户）。 4、创建和管理用户登录 （1）数据库用户名和登录名的关系 登录对象和用户对象是SQL Server进行权限管理的两种不同的对象。 登录对象是服务器方的一个实体，使用一个登录名可以与服务器上的所有数据库进行交互。用户对象是登录对象在数据库中的映射，登录对象可以在每个数据库中均进行映射，映射名（即用户、用户名、用户帐号）可以不同，可以对用户对象进行授权，以便为登录对象提供对数据库的访问权限。 一个登录名在同一数据库中只能映射一次，在master数据库中的syslogins表中，保存所有的登录名及口令。而每个数据库中都会有一个叫sysusers的表，这个表包含了在数据库中的所有用户对象，以及和它们相对应的登录名的标识。 必须在删除登录名前将其映射的所有用户名全部删除，以确保不会在库中留下孤儿型的用户。数据库所有者不能被删除，但是能够使用sp_changedbowner存储过程将数据库所有者改变到其他的登录名上。 （2）利用企业管理器创建、管理SQL Server登录帐号 在企业管理器的树中，找到服务器的安全性文件夹，右键登录图标。或直接用工具栏上的“登录”。 （3）使用SQL Server的创建登录向导工具创建登录帐号。 5、创建和管理角色 通过认证（登录）阶段只表明用户可以进入SQL Server服务器进行一些服务器操作，并不表明用户能够访问SQL Server服务器上数据库中的数据，还必须要有访问许可权限（通过用户帐号来实现）才行。用户只有在具有访问数据库的权限之后，才能够对服务器上的数据库进行权限许可下的各种操作。管理权限通过角色进行，而一个用户帐号属于一个或多个角色。 角色：一组具有相同权限的用户。只要对角色进行权限设置便可以实现对该角色下所有用户权限的设置，大大减少了管理员的工作量。角色包括预定义的服务器角色和数据库角色。 服务器角色：（在“安全性”中）指根据SQL Server的管理任务，以及这些任务相对的重要性等级来把具有SQL Server管理职能的用户划分为不同的用户组，每一组所具有的管理SQL Server的权限都是SQL Server内置的，即不能对其进行添加、修改和删除，只能向其中加入登录用户。 7种常用的固定服务器角色： 系统管理员：拥有SQL Server所有的权限许可。 服务器管理员：管理SQL Server服务器端的设置。 磁盘管理员：管理磁盘文件。 进程管理员：管理SQL Server系统进程。 安全管理员：管理和审核SQL Server系统登录。 安装管理员：增加、删除连接服务器，建立数据库复制以及管理扩展存储过程。 数据库创建者：创建数据库，并对数据库进行修改。 数据库角色：（在相应的数据库中）是为某一用户或某一组用户授予不同级别的管理或访问数据库以及数据库对象的权限，这些权限是数据库专有的，并且还可以使一个用户具有属于同一数据库的多个角色。SQL Server提供了两种类型的数据库角色：即固定的数据库角色和用户自定义的数据库角色。 固定的数据库角色： public：维护全部默认许可。 db_owner：数据库的所有者，可以对所拥有的数据库执行任何操作。 db_accessadmin：可以增加或者删除数据库用户、工作组和角色。 db_addladmin：可以增加、删除和修改数据库中的任何对象。 db_securityadmin：执行语句许可和对象许可。 db_backupoperator：可以备份和恢复数据库。 db_datareader：能且仅能对数据库中的任何表执行select操作，从而读取所有表的信息。 db_datawriter：能够增加、修改和删除表中的数据，但不能进行select操作。 db_denydatareader：不能读取数据库中任何表中的数据。 db_denydatawriter：不能对数据库中的任何表执行增加、修改和删除数据操作。 用户自定义角色：如果一组用户需要执行在SQL Server中指定的一组操作并且不存在对应的Windows NT组，或者没有管理Windows NT用户帐号的许可，就可以在数据库中建立一个用户自定义的数据库角色。用户自定义的数据库角色有两种类型：即标准角色和应用程序角色。 标准角色：通过对用户权限等级的认定而将用户划分为不用的用户组，使用户总是相对于一个或多个角色，从而实现管理的安全性。 应用程序角色：是一种比较特殊的角色，没有成员。当我们打算让某些用户只能通过特定的应用程序间接地存取数据库中的数据而不是直接地存取数据库数据时，就应该考虑使用应用程序角色。当某一用户使用了应用程序角色时，他便放弃了已被赋予的所有数据库专有权限，他所拥有的只是应用程序角色被设置的角色。 创建应用程序角色：sp_addapprole role,password 应用程序使用之前必须激活：sp_setapprole role,password 删除应用程序角色：sp_dropapprole role 使用系统存储过程创建数据库角色： sp_addrole ‘role’ 使用系统存储过程删除数据库角色： sp_droprole 'role' 使用系统存储过程添加数据库角色成员： sp_addrolemember [@rolename =] 'role'，[@membername =] 'security_account‘ 使用系统存储过程删除数据库角色成员： sp_droprolemember [@rolename =] 'role'，[@membername =] 'security_account‘ 使用企业管理器管理数据库角色： 在企业管理器中，展开指定的服务器以及指定的数据库，然后用右键单击角色图标，从快捷菜单中选择新建数据库角色选项。 使用存储过程管理服务器角色： sp_addsrvrolemember login，role --将某一登录帐号加入 sp_dropsrvrolemember login，role --将某一登录帐号从某一服务器角色中删除 其它： sp_helprole [‘role’] --显示当前数据库的某一角色的信息 sp_helprolemember [‘role’] --用于显示数据库某一角色的成员信息 6、权限：用于控制用户在SQL Server中执行特定任务的能力。 在数据库里分配权限有几个不同的层次，DBA最主要的责任之一是保证把适当的权限分配给需要它的用户。 （1）权限分类： 对象权限：用户对数据库对象进行操作的权限。 针对表和视图的操作：select、insert、update、delete。 针对表和视图的记录的操作：insert、delete。 针对表和视图的列的操作：select、update。 针对存储过程、用户函数的操作：exec。 语句权限：指用户是否有权限来执行某一语句。 CREATE DATABASE CREATE DEFAULT CREATE PROCEDURE CREATE RULE CREATE TABLE CREATE VIEW BACKUP DATABASE BACKUP LOG 暗示性权限：指系统预定义的固定服务器角色成员、数据库拥有者（dbo）所拥有的权限。 （2）用户权限管理 一个登录帐号在一特定数据库中对应于一个用户帐号，SQL Server有2个默认的用户帐号：即dbo(对应于sa登录)、guest（对应于任意的在该数据库中无用户帐号的登录）。 企业管理器可创建某一数据库的用户帐号（即用户），也可给用户帐号施加对数据库对象操作的权限（在某一用户上右键选“属性/权限”或某一表或视图上右键选“所有任务/管理权限”均可）。 T-SQL创建某一数据库的用户帐号: sp_grantdbaccess 'login','name_in_db' T-SQL删除某一数据库的用户帐号: sp_revokedbaccess 'name_in_db' T-SQL 语句使用grant、deny和revoke三种命令来实现管理对象权限和语句权限： Grant语句其语法形式如下： （１）授予语句权限 Grant {all|statement[,…n]} to security_account[,…n] Statement指create database、create table等。 （２）授予对象权限 grant { {all [priviledges]|permission[,…n]} [（column[,…n]）] on {table|view} |on {table|view} [（column[,…n]）] |on {stored_procedure|extended_procedure} } to security_account[,…n] [as {group|role}] permission指select、update等 Deny语句其语法形式如下： （１）拒绝语句的权限 deny {all|statement[,…n]} to security_account [,…n] （２）拒绝对象的权限 deny { {all [priviledges]|permission[,…n]} [（column[,…n]）] on {table|view} |on {table|view} [（column[,…n]）] |on {stored_procedure|extended_procedure} } to security_account[,…n] Revoke语句其语法形式如下： （１）删除语句上所授予或拒绝的权限 revoke {all|statement[,…n]} from security_account [,…n] （２）删除对象上所授予或拒绝的权限 revoke [grant option for] { {all [priviledges]|permission[,…n]} [（column[,…n]）] on {table|view} |on {stored_procedure|extended_procedure} } from security_account[,…n] [as {group|role}]    授课题目（章、节） 第十六讲 数据完整性  学时 3 授课时间 周3第3～5节 第( 16 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、了解数据完整性的含义； 2、能根据要求实施数据的完整性； 3、熟练掌握规则和默认的使用方法。  教学重点、难点： 1、规则的创建和绑定  大体内容 教学方法 时间安排  数据完整性的含义 面授   规则 面授   默认 面授                       教研室审阅意见： 教研室主任签名： 年 月 日   教学过程(基本内容) 辅助手段 备注   时间分配   一、数据完整性的含义 数据完整性是指数据的一致性和可靠性。它是为防止数据库中存在不符合语义规定的数据，防止因错误的输入、输出而造成无效的操作或错误信息而提出的。 可在创建表时通过各种约束或通过规则（RULE）、默认（DEFAULT）数据库对象来保证数据完整性。其中，规则和默认可多次使用到同一表中或不同表中的字段上，提高了资源的利用率，而约束必须与表的创建在一起进行，它是从属与某一具体表的。 数据完整性分为： 1．实体完整性 要求表的每一行在表中是唯一的。 可通过UNIQUE、PRIMARY KEY、IDENTITY等约束来实现之（参见用户定义的完整性）。 2．域完整性 也称列（字段）完整性，要求列中的数据具有正确的数据类型、格式和有效的数据范围。 可通过外键、约束以及默认、规则等数据库对象来实现之（参见用户定义的完整性）。 3．参照完整性 参照完整性是指两个表的主键和外键的数据之间必须一致（通过“关系图”数据库对象进行）。 参照完整性保证了被参照表和参照表之间的数据一致性 参照完整性防止了数据丢失或无意义的数据在数据库中扩散 参照完整性建立在外键和主键（或外键和唯一性关键字）之间的关系上 在SQL Server中，参照完整性的作用表现在： 禁止向表中插入含有主表中不存在的关键字的记录 禁止改变已被参照的主键的值 禁止删除包含已被参照的主键的记录 也可通过FOREIGN KEY约束来实现。 以上均可归结于“用户定义的完整性”，即 用户定义的完整性允许用户定义不属于其它任何一类完整性（常在创建表时进行）的特定规则，它是针对某个特定数据库的约束条件的，它反映某一具体应用所涉及的数据必须满足的语义要求。 用户定义的完整性包括：规则、默认、约束和触发器。 二、规则 规则是对数据库中表的字段或用户自定义数据类型中值的规定和限制。 规则是单独存储的独立的数据库对象，它和表以及用户自定义数据类型是相互独立的，即表以及用户自定义数据类型的删除、修改不会影响规则（当规则已与表中字段绑定后，不能删除规则）。 规则提供了一种加强字段或用户自定义数据类型约束的机制。 注意：规则必须在INSERT和UPDATE语句之前给出。 1．使用CREATE RULE语句创建规则 CREATE RULE 规则名 AS 条件表达式 其中，条件表达式可以是WHERE子句中使用的任何形式的条件。 注意：规则不能引用字段或其它数据库对象，可以包含不引用数据库对象的内置函数。 条件表达式中包含一个局部变量（以@开始），该变量引用通过INSERT或UPDATE语句输入的值。（该局部变量不需要定义） 例1：创建入学日期的规则，将入学日期限制在某一时间范围内。 create rule enter_college_date_rule as @riqi>'1980-1-1' and @riqi<=getdate() 例2：创建年龄规则，年龄必须在18—30之间。 create rule age_rule as @nianling between 18 and 30 例3：要求性别只能是男或女之一。 create rule sex_rule as @xingbie in('男','女') 例4：要求课程成绩只能在0—100之间。 例5：要求学生的姓名至少2个汉字： create rule name_rule as datalength(rtrim(@a))>=4 2．使用企业管理器创建规则 在企业管理器中打开某数据库，右击规则，选择“新建规则”。 在新建的同时可以将规则绑定到字段。 3．使用存储过程sp_bindrule绑定规则 sp_bindrule <规则名称>,<’表.字段名’> 例：将上述各规则绑定到相关字段。 sp_bindrule name_rule,'学生表.姓名' insert into 学生表 values('888888889','张','女',19,'计算机','软件2班','08/22/2003') select * from 学生表 注意：同一个规则可绑定到多个字段上。 4．使用存储过程sp_unbindrule解除绑定 sp_unbindrule <’表.字段名’> 例：sp_unbindrule '学生表.姓名' 5．使用DROP RULE删除规则 DROP RULE <规则名称> 例：drop rule name_rule 6．使用企业管理器完成上述操作 7．使用规则的限制条件 规则只能处理常量和函数，不能用来查找表或比较表中的列。 一个字段只能与一个规则绑定，当字段已经绑定了一个规则时，继续绑定另一规则，则只有后一规则对该字段有效(前一规则已自动解除绑定)。 当规则已经被绑定到字段或用户数据类型时，则该规则不能被删除。 向系统中大量拷贝数据时，规则不产生作用。 使用规则时，应保证规则中值与绑定字段的数据类型一致。 三、默认 默认（default）也是一种数据库对象，它与字段的默认值约束的功能一样。 当输入数据时，如果没有明确某字段的值，该字段将使用它的默认数据。 默认用于向无值的字段提供一个预先指定的值。 默认在创建后，也需要绑定到字段才有效。 1．使用CREATE DEFAULT语句创建默认 CREATE DEFAULT <默认名称> AS <常量表达式> 默认只对后插入的记录字段有效，对绑定之前已经插入的记录字段无效。 创建表格时设置的默认值具有较高的级别，此时不能再向该字段绑定其它默认。 例1：create default age_default as 19 例2：create default time_default as getdate() 2．使用企业管理器创建默认 3．使用存储过程sp_bindfault绑定默认 sp_bindfault <默认名>,<’表.字段’> 例： sp_bindefault age_default,'学生表.年龄' insert into 学生表(学号,姓名) values('888888887','张宁') select * from 学生表 sp_bindefault time_default,'学生表.入学年份' insert into 学生表(学号,姓名) values('888888886','张宁') select * from 学生表 4．使用存储过程sp_unbindefault解除绑定 sp_unbindefault <’表.字段名’> 5．使用DROP DEFAULT删除默认 DROP DEFAULT <默认名称> 当默认没有绑定到任何字段或用户类型时，可以删除该默认。 6．在创建表时指定默认值 CREATE TABLE table_name (column_name data_type [DEFAULT 常量表达式] {[NULL|NOT NULL][identity] [PRIMARY KRY|UNIQUE] } [,……] )  例： create table default_ex(pid int not null, name char(8),sex char(2) default '女') insert into default_ex(pid,name) values(20,'李利') select * from default_ex 7．默认和默认值的使用限制 使用DEFAULT和绑定设置字段的默认值，两种方法中只能选择一种 默认或默认值中只能使用常量或SQL函数 一个字段只能与一个默认绑定，当字段已经绑定了一个默认时，继续绑定另一默认，则只有后一默认对该字段有效(前一默认已自动解除绑定)。 要求默认值和字段的数据类型一致 在删除默认之前，必须先解除默认的绑定 8．在UPDATE中使用默认值 update default_ex set sex=default where pid=200 四、查看规则和默认 select name from sysobjects where type in('r','d')  （补充）全文索引（运行？） 一 建立全文目录 在当前数据库中启用全文索引： sp_fulltext_database 'enable' 在当前数据库中创建全文索引： sp_fulltext_catalog 'Book' , 'create' （在当前数据库中重建全文索引：sp_fulltext_catalog 'Book' , 'rebuild'） 在当前数据库的表中创建全文索引： sp_fulltext_table '学生表','create','Book', 'PK_学生表' 在当前数据库的表中列上创建全文索引： sp_fulltext_column '学生表','姓名','add' 激活全文索引： sp_fulltext_table '学生表','activate' 二、全文查询 （一）CONTAINS 1．词或者短语 例如，下面的SQL语句搜索包含“实例”两个字的书籍名称和价格： USE bookdb GO SELECT book_name,price FROM book WHERE CONTAINS(book_name, '实例') GO 2．词或者短语的前缀 例如，下面的SQL语句是查询以Win开始的书名： USE bookdb GO SELECT book_name,price FROM book WHERE CONTAINS(book_name, '"Win*"') GO 但是如果修改如下： USE bookdb GO SELECT book_name,price FROM book WHERE CONTAINS(book_name, 'Win*') GO 3．使用相近的字符串来查询 例如，下面的SQL语句查询关于3D的实例类书籍： USE bookdb GO SELECT book_name,price FROM book WHERE CONTAINS(book_name, '3D NEAR 实例') GO 4．衍生字 例如，下面的SQL语句查询具有dry形式的词的所有产品：dried和drying等等： USE Northwind GO SELECT ProductName FROM Products WHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ') GO 5．给字符串赋予权重 例如，下面的SQL语句就是使用了权重的例子，给“网络”赋予0.8的权重，给“实例”字符串赋予0.4的权重： USE bookdb SELECT book_name,price FROM book WHERE CONTAINS(book_name, 'ISABOUT (网络 weight(.8), 实例 weight(.4))' ) GO 6．使用变量 在CONTAINS关键字中，还可以使用变量来进行查询。下例就是使用变量而非特定的搜索术语进行查询的例子： USE bookdb GO DECLARE @SearchWord varchar(30) SET @SearchWord ='网络管理' SELECT book_name,price FROM book WHERE CONTAINS(book_name, @SearchWord) GO （二）FREETEXT 例如，下面就是一个使用FREETEXT关键字的例子： USE bookdb GO DECLARE @SearchWord varchar(30) SET @SearchWord ='网络管理' SELECT book_name,price FROM book WHERE FREETEXT(book_name, @SearchWord) GO （三）CONTAINSTABLE函数 例如，下面的SQL语句就是使用CONTAINSTABLE函数的例子： USE bookdb GO SELECT book_name,price,TTTable.[KEY],TTTable.Rank FROM book INNER JOIN CONTAINSTABLE(book,book_name,'实例') AS TTTable ON book_id=TTTable.[KEY] GO （四）FREETEXTTABLE函数 例如，下面的SQL语句用于查询 USE bookdb GO SELECT book_name,price,FTable.[KEY],FTable.Rank FROM book INNER JOIN FREETEXTTABLE(book,book_name,'实例') AS FTable ON book_id=FTable.[KEY] GO