当前位置:首页 >> IT/计算机 >>

SQL Sever 2005 复习


第 1 页 共 61 页

SQL Sever 2005 复习 SQL 语句 一数据查询 1.抽取特定属性 (1)定制显示及排序 (2)通配符 (3)抽取来自表顶部的结果(top 关键字) (4)抽取不重复的结果(distinct 关键字) 2.使用函数定制结果集 (1)字符串函数 (2)日期函数 (3)属性函数 (4)次序函数 (5)系统函数 二.汇总和分组数据 1. 通过集合函数总结数据 (1)avg 返回数字表达式的平均值所有的或者不同的 (2)count 返回表达式中的唯一的数据 (3)min 返回表达式的最小值 (4)sum 返回数之表达式的总数 2.分组数据 (1)group by (2)compute 和 compute by 3.pivot 关键字查询 三.使用连接和子查询查询数据 1. 使用连接查询数据 (1)使用内连接 (2)使用外连接 (3)使用交叉连接 (4)使用等值连接 (5)使用自连接 2. 使用子查询查询数据 (1)使用操作符和 in,exists 关键字 (2)使用修改的比较操作符 (3)使用集合函数显示 (4)使用嵌入子查询

第 2 页 共 61 页

(5)使用相关子查询 四.管理数据库和表 1.管理数据库 (1)系统数据库 (2)识别数据库文件 (3)创建用户自定义数据库 (4)管理用户自定义数据库 2.管理表 (1)管理表 (2)数据完整性 (3)创建约束 (4)应用规则 (5)使用用户定义数据类型 五.在表中操纵数据 1.使用 DML 语句操纵数据 (1)在表中添加数据 (2)从现有表中复制数据到新表 (3)更新表 (4)从表中删除数据 (5)删除表中全部记录 2.操纵 XML 数据 (1)在表中存储 XML 数据 (2)从表中抽取 XML 数据 六.实现索引,视图和全文搜索 1.创建和管理索引 (1)索引介绍 (2)创建索引 (3)管理索引 2.创建和管理视图 (1)创建视图 (2)通过视图修改数据时的限制 (3)索引视图 (4)管理视图 3.实现全文搜索 (1)配置全文搜索 (2)使用全文搜索搜索数据

第 3 页 共 61 页

七.实现存储过程和函数 1.实现批处理 (1)创建批处理的指导方针 (2)使用结构 (3)处理错误和异常 2.实现存储过程 3.创建带参数的存储过程 (1)从存储过程返回值 (2)从另一个过程调用一个过程 4.实现函数 (1)创建 UDFs (2)创建标量函数 (3)创建表值函数 八.使用触发器 1.实现和识别触发器 (1)DML 触发器 (2)DDL 触发器 2.创建触发器 (1)创建删除触发器 (2)创建更新触发器 (3)创建后触发器 3.管理触发器 (1)改变触发器 (2)删除触发器 (3)重命名触发器 4.实现事务 (1)事务属性 (2)回滚事务 (3)锁 (4) SQL Sever 中的锁 *游标 SQL 语句 数据定义语言(DDL) 用于定义数据库,数据类型,结构和数 据上的约束 create:用于创建新数据库对象,如表,索引,存储过程及 函数

第 4 页 共 61 页

alter:用于修改数据库对象 drop:删除对象 数据库操纵语言(DML) 用于数据库中对象的操作 insert:在表中插入新数据 update:修改表中现存记录 delete:删除表中数据 数据控制语言(DCL) 控制数据库中对象的访问 grant:对指定的用户授予访问权利 revoke:阻止某些用户的访问 数据库查询语言(DQL) 从数据库中查询对象 select:从数据库中以不同的方式,格式选择数据的命令 一 数据查询 (使用 AdventureWorks 数据库) ⒈抽取特定属性 ⑴定制显示及排序 抽取职位是 Tool Designer,支付率大于 19 的雇员编号,以 编号升序排列,并以下面的格式显示 雇员编号 支付率

Select EmployeeID ‘雇员编号’ ,Rate‘支付率’ from HumanResources.Employee where Title=’Tool Designer’ and Rate>’19’ order by EmployeeID asc ⑵通配符 通配符 描述 % 表示任何字符可是 0 个或更多 _ 表示单个字符 [] 在指定范围内的任何字符 [^] 不在指定范围内的任一字符 SQL Sever 所提供的这些字符可用于关键字 从部门表中抽取以’Pro’开始的名称值记录 Select *from HumanResources.Department where Name like ‘Pro%’ 从部门表中抽取部门名称是五个字符长度,并且以’Sal’开 头,而第四个字符在 e,o 中选择,第五个字符为任何字符 Select * from HumanResources.Department where Name like ‘Sal[e,o]_’

第 5 页 共 61 页

⑶抽取来自表顶部的记录,使用 top 关键字 从员工表中抽取前 3 个记录,其中 HireDate 应该大于等于 1 / 1 / 98, 并 且 小 于 等 于 12 / 31 / 98, 记 录 应 该 基 于 SickleaveHours 列以升序显示 Select top 3 * from HumanResources.Employee where HireDate >=’1/1/98’ and HireDate <=’12/31/98’ order by SickleaveHours asc ⑷使用 distinct 关键字抽取不含重复值的记录 从员工表中抽取以 PR 开始的所有职务: Select distinct Title from HumanResources.Employee where Title like ‘PR%’ ⒉使用函数定制结果集 SOL Sever2005 提供的内建函数可以分为:字符串函数,日期 函数,数学函数,等级函数和系统函数 ⑴使用字符串函数 函数名称 示例 描述 ascii select ascii( ABC’ 返回 65,最左边字符’ ‘ ) A’ 的 ASCII 码 char char(65) 返回’A’,字符的等价 ASCII 代码值 charindex charindex(‘e’,’ 返回 2,检索字符并返回 hello’) 字符在字符串中的位置 patindex patindex( ‘%box%’ 返回 7,检索第一字符在 ,’ actionbox’) 字符串中出现的位置, 若是 0,则是没找到字符 left left(‘richard’,4) 返回’rich’,从左向右 截取 4 位字符 right right( ‘richard’ ,4) 返回’hard’,从右向左 截取 4 位字符 substring substring( ‘ weathe 返回’eat’,从第 2 位 r’,2,3) 起截取 3 位字符 upper upper(‘richard’) 返回’RICHARD’,将小 写转换成大写 lower lower(‘RICHARD’) 返回’richard’,将大 写转换成小写

第 6 页 共 61 页

len

返回 7,字符串中字符的 数量 reverse reverse(‘action’) 返回’noitca’,原字符 串的反向字符串 str str(123.45,6,2) 返回’123.45’,将表达 式转换成字符串,小数 点后两位 differenc difference( ‘ HELLO 返回 4,比较两字符串相 e ’,’hello’) 似性,级别从 0 到 4,4 表 示相似度最高 stuff stuff( ‘ weather ’ , 返回’weiher’,删除从 3,2,’i) 第 3 位起的 2 个字符并 用代换 space ‘ richard ’ 返回’ richard hill’ , +space(2)+’hill’ 在前后两字符串之间加 入 2 个空格组成 1 个字 符串 ltrim ltrim( ‘ richard’ 返回’richard’,删除 ) 字符串前端空白 rtrim rtrim(‘richard’) 返回’richard’,删除 字符串后端空白 Name,DepartmentID,GroupName 由来自部门表的数据抽取, 并且名称列的数据是以用户定义的标题以大写显示,格式如 下所示: DepartmentID DepartmentName GroupName select upper(Name) ’ DepartmentName,DepartmentID,GroupName from HumanResources.Department ⑵使用日期函数 函数名称 参数 描述 dateadd (日期部分,数字, 添加日期部分的数字到日期 日期) datediff (日期部分,日期 计算两个日期间的日期部分 1,日期 2) 的数值

len(‘richard’)

第 7 页 共 61 页

getdate () 返回当前日期和时间 getutcdate 0 返回世界标准时间 datename (日期部分,日期) 返回列出日期的日期部分结 果以字符型显示 datepart (日期部分,日期) 返回列出日期的日期部分结 果以整型显示 year (日期) 返回一个整型,表示年份 month (日期) 返回一个整型,表示月份 day (日期) 返回一个整型,表示天 计算当前日期和 AdventureWorks,Inc 里员工生日间的差值 Select datediff(yy,BirthDate,getdate())’Age’ from HumenResources.Employee 查询员工的工龄 Select datediff(yy,HireDate,getdate()) ’ year of working’ from HumanResources.Employee ⑶使用属性函数 函数名称 参数 描述 abs (浮点表达式) 返回数值的绝对值 degrees (数值表达式) 返回大于等于指定值的最小 整数 floor (数值表达式) 返回小于等于指定值的最大 整数 sign (数值表达式) 返回正,负或零 round (数值表达式,长 返回作为整数表达式舍入到 度) 指定长度的数字值 rand ([seed]) 返回介于 0 和 1 之间的随机 数 sprt (浮点表达式) 指定值的平方根 exp (浮点表达式) 返回指定值的幂值 log (浮点表达式) 返回指定值的自然对数 log10 (浮点表达式) 返回指定值以 10 为底的对数 power (数值表达式,y) 返回数值表达式的次幂 pi () 返回 3.141592653589793 的 常幂值 radians (数值表达式) 将度数转换为弧度

第 8 页 共 61 页

返回角度的弧度值 ,它的余 弦,正弦或正切是一个浮点 数 cos,sin,c (浮点表达式) 返回弧度的余弦,正弦,余切 ot,tan 和正切值 从 EmployeePayHistory 表 中 为 指 定 的 雇 员 的 id, 抽 取 EmployeeID 和 Rate: Select EmployeeID,round(Rate,2)’ Hourly Pay Rate’from HumanResources.EmployeePayHistory where EmployeeID=3 当使用 round 函数的时候,如果长度是正值,那么表达式被舍 入到小数点的右侧,如果长度是负值,那么表达式被舍入到 小数点的左侧 函数 输出 round(1234.567,2) 1234.570 round(1234.567,1) 1234.600 round(1234.567,0) 1235.000 round(1234.567,-1) 1230.000 round(1234.567,-2) 1200.000 round(1234.567,-3) 1000.000 AdventureWork 的管理层想增加换班时间 select ShifID,StartTime, ’ EndTime ’ =dateadd(hh,10,StartTime) from HumanResources.Shift ⑷ 使用次序函数 次序函数返回每行的顺序植,但是,基于规则,超过一行可 以得到相同的次序,可试用以下函数排列记录 row_number rank dense_rank 这些函数使用 over 从句,这个从句决定被指定等级行的排 列(升序或降序) 1> row_number 函数 SQL 查询使用 row_number 函数显示列的顺序数: select EmployeeID, Rate,row_number ) ( over(order by Rate dese) as rank from HumanResources.EmployeeHistory 显示结果 P2.32

acos,asin (浮点表达式) ,atan

第 9 页 共 61 页

2> rank 函数 rank 函数返回基于指定规则的结果集的每行的顺序: select EmployeeID,Rate,rank() over (order by rate desc) as rank from HumanResources.EmployeePayHistory 显示结果 P2.33 3> dense_rank 函数 此函数用于连续的顺序值需要基于特定的规则被给出的地 方,例如你想基于一年内产品的销售排列,若 A,B 产品销 售额相同,两个都指定相同的等级,销售额次序的下一个产 品将指定为下一个顺序值 Select EmployeeID,Rate,dense_rank() over (order by Rate desc) as rank from HumanResources.EmployeePayHistory 显示结果 P2.34 *这三种函数显示结果的不同之处在于 rank 列的输出排序 ⑸使用系统函数 函数 定义 host_id( ) 返回客户进程的当前主 进程 ID 号 host_name( ) 返回客户进程当前主机 的名称 db_id([‘db_name’]) 返回数据库标识号 Db_name([‘db_id’] 返回数据库名称 Object_id(‘objname’) 返回数据库对象 ID 号 Object_name(‘obj_id’) 返回数据库对象名称 User_id([‘name_in_db’]) 返回相应于用户名称的 数据库标识号 User_name([user_id]) 返回根据数据标识号的 用户名 Suser_id([‘login_name’]) 返回相应于用户登陆名 的登陆标识(ID)号 Suser_sid([‘login_name]’ ) 返回相应于用户登陆名 的安全标识号(SID) Suser_sname([server_user_id] ) 返回相当于安全标识号 的登陆名称

第 10 页 共 61 页

SQL Sever 2005 中也提供转换函数将一个表达式的数据类 型转换为另一种数据类型,使用 convert 函数 语法: Convert (datatype [(length)],expression [style]) Datatype 只能是系统定义数据类型 Length 是可选的 char,varchar 或二进制数据类型参数 Expression 是任何有效的要被转换到的另一种数据类型的 表达式 Style 是在转换数据类型到字符数据类型的时候表示日期 的方法 例: 显示来自员工表的 title 和 hrie date 对于它,需将日期数 据的雇用到字符数据类型,然后以 yy.mm.dd 的格式显示 Select Title,convert(char(10),HireDate, Hire Date’ 2)’ from HumanResources.Employee Adventure Works,Inc 的管理层想浏览一个报表,它显示 employeeID,designation 和作为市场经理或市场专员的员 工的年龄,数据将以大写显示 Select EmployeeID,upper(Title) ’ as Designation,datediff(yy,BirthDate,getdates)as Age from HumanResources.Employee where Title=’Marketing Manager’ or Title=’Marketing Specialist’ 二汇总和分组数据 数据的汇总包含有助于在广度层次进行数据分析的集合集, SQL Sever 系统提供集合函数来产生汇总数据 1 通过集合函数总结数据 (1)avg 返回数字表达式的平均值所有的或者不同的 从 EmployeePayHistory 表中的 Rates 列抽取平均值并且用 用户自定义的标题 Select avg(Rate) ‘ Average Rate ’ from HumanResources.EmployeePayHistory (2)count 返回表达式中的唯一的数据(或所有的,或者不同 的) 从 EmployeePayHistory 表中使用用户自定义标题抽取唯一 比率值: Select count (distinct Rate) ’ unique Rate ’ from

第 11 页 共 61 页

HumanResources.EmployeePayHistory Count 函数也接收(*)作为它的参数,但是它计算查询返 回的行数 (3)min 返回表达式的最小值: 从 EmployeePayHistory 表中使用用户自定义标题抽取最小 比率值: Select min(Rate) ’ Minmum Rate ’ from HumanResources.EmployeePayHistory (4)sum 返回数之表达式的总数,或者所有,或者不同的 从 EmployeePayHistory 表中使用用户自定义的标题抽取所 有唯一比率值的总和 Select sum ( distinct Rate ) ‘ sum ’ from HumanResources.EmployeePayHistory 2.分组数据 在 SQL Sever 2005 可通过使用 group by,compute,compute by,pivot 从句分组数据 (1)group_by group by…having 从句与 select…where 从句相同,group by 从句收集满足条件的数据,并且汇总它到一个表达式以产 生单一的一组值,having 从句消除所有不满足条件的这些组 查询抽取所有职务和雇员的平均空闲小时,当空闲小时大于 30 并且分组平均值大于 55 的时候: select Title,avg(VacatoinHours) ‘Average Vacatoin Hours ’ from HumanResources.Employee where VacationHours>30 group by Title having avg(VacatoinHours)>55 group by 中的 all 关键字被用于显示所有组,包括那些不包 含在 where 从句中的如果不使用 all ,group by 从句不会 显示没满足条件的行的分组,如果使用 all,即使在分组中没 有满足搜索条件 group by 也将显示所有行 从员工职务中抽取记录,他们被 where 从句的条件删除: Select Title, ‘VacationHours’= sum(VacatoinHours) from HumanResources.Employee where Title in (‘Recruiter’,’Stocker’,’Design Engineer’) group by all Title (2)compute 和 compute_by

第 12 页 共 61 页

Compute 从句被用于产生汇总行,compute by 从句被用来以 数据分组计算结果集中的汇总值,数据被分组的列在 by 关键 字之后被提到 Group by 从句用来产生分组的汇总报告,并且不产生结果集 中的个别表行, compute 从句被用于控制中断的总结报告 而 程序,它产生结果集中的详细信息 Select Title,VacationHours‘Average VacationHours’ , SickLeaveHours ‘ Average SickLeave Hours ’ from HumanResources.Employee where Title inn ( ‘ Recruiter ’ , ’ Stocker ’ ) order by Title,VacationHours,SickLeaveHours compute avg(VacationHours),avg(SickLeaveHours) by Title 输出结果 P244 使用 computer by 从句计算 Designation 列中的每个值的 VacationHours 和 SickLeaveHours 小 计 .computer 从 句 计算 VacationHours 和 SickLeaveHours 总和 Select Title, ’ Total VacationHours ’ =VacationHours,SickLeaveHours ‘Total SickLeaveHours’ from HumanResources.Employee where Title in ( ‘ Recruiter ’ , ’ Stocker ’ ) order by Title,VacationHours,SicLeaveHours computer sum (VacationHours),sum(SickLeaveHours) 3.pivot Pivot 操作符被用于将列集转换为值,pivot 通过将来自列的 唯一值以表达式的形式转换为输出中的多个列来轮换表达 式.如果有输出中的多个列来轮换表达式.如果有输出的需 要它将在剩下的列值上完成集合. 显示某些员工下的购买定单的数量,并且包含提供商, Select VendorID ,[164] as Emp1,[198]as Emp2,[223] as Emp3,[231] as Emp4,[233] as Emps From (select PurchaseOrderID ,EmpoyeeID ,VendorID from Purchasing.PurchaseHeader)p Pivot (count (Purchase OrderID) for EmployeeID in ([164],[198],[223],[231],[233]))as pvt order by VendorID

第 13 页 共 61 页

*注:1.computer 子句中所用到的列必须出现在 select 表 中 三.使用连接和子查询查询数据 1.使用连接查询数据 SQL Sever 允许使用连接,连接允许在单一结果集中从相关 的表中浏览数据,可以基于共同属性连接多个表 依赖于浏览多个表的数据的需求,可以应用不同类的连接, 有内连接,外连接交叉连接,等值连接或字连接. (1)使用内连接 内连接在公共的列上使用比较操作符从表中抽取数据,当内 连接被使用的时候仅满足公共列中的连接条件的值的行被 显示,内连接取交集.两个表中不满足条件的行不被显示 内连接是默认连接,因此可以通过使用 join 关键字来应用内 连接,而且还可以用 inner join 关键字 显 示 Employee 表 的 每 个 员 工 的 EmployeeID ,Title 和 EmployeePayHistory 表的 Rate 和 PayFrequency 列 select e.EmployeeID,e.Title,eph.Rate,eph.PayFrequency from HumanResources.Employee e join HumanResources.EmployeePayHistory eph on e.EmployeeID=eph.EmployeeID 从 Employee 表 中 抽 取 所 有 支 付 率 大 于 40 的 员 工 的 employeeid 和 designation 这 个 比 较 是 以 公 共 属 性 EmployeeID,将 Employee 表和 EmployeePayHiatory 表连接 后在 Rate 列上完成的 Select e.EmployeeID’ EmployeeID’ ,e.Title’ desination’ from HumaanResources.Employee e inner join HumanResources.EmployeePayHistory eph on e.EmployeeID=eph.EmployeeeID where eph.Rate>40 (2)使用外连接 外连接显示包含来自一个表中所有行和来自另一个表中匹 配行的结果集,例如:在表 A 和表 B 上创建了外连接,结果中 将会显示表 A 中所有记录,和来自表 B 中那些公共列保持条 件为真的记录 外连接若没找到匹配记录的相关表的列为 null

第 14 页 共 61 页

外连接有 3 种类型: 左连接 右连接 完全连接 1> 使用左连接 左连接返回 left outer join 关键字左侧指定的表的所有行 和右侧指定的表的匹配的行,对于来自左侧的表的行,在右 侧指定的表中没有发现匹配的行,那么来自右侧指定的表中 获得数据的列中将显示 null 值 SpecialOfferProduction 表 中 包 含 一 系 列 特 价 产 品 ,SalesOrderDetail 表 存 储 所 有 销 售 事 务 的 详 细 情 况,AdventureWorks,Inc 的用户需要浏览没有事务交易的特 价产品的 ProductID Select p.ProductID,p1.SalesOrderID,p1.UnitPrice from Sales.SpecialOfferProduct p left outer join Sales.SalesOrderDtail p1 on p.ProductID=p1.ProductID where SaleesOrderID is null 左连接显示左表中所有记录与右表相匹配的行列 2>使用右连接 右连接返回 right outer join 关键字右边指定的表中的所 有记录和来自左边指定的表中的匹配行 显示右表所有记录与左表相匹配的记录 JoCandiidate 表中存储所有职位候选人的详细信息,需抽取 所有候选人的列表需要找到哪个候选人已经被 AdventureWorks,Inc 雇用 Select e.Title,d.JobCandidateID from HumanResources.Employee e right outer join HumanResources.JobCandidate d on e.EmployeeID=d.EmployeeID 3>使用完全外连接 完全外连接是左,右连接的组合,此连接返回来自两个表中 所有匹配和非配置的行,匹配记录只显示一次,在非配置行 的情况下,对于数据不可用的列将显示 null 值 产生一个显示所有员工最高学历的详细情况的列表 select e.EmployeeID,e.EmployeeName ,ed.EmployeeEducaationC

第 15 页 共 61 页

ode,ed.Education from HumanResources.Employee e full outer join HumanResources.Education ed on e.EmployeeEducationCode = ed.EmployeeEducationCode (3)使用交叉连接 交叉连接也被称笛卡儿积,在两个表中的每行与另一个表中 的每行连接结果集中行的数量是第一个表中行的数量的乘 积,即:如果表 A 有 10 行,表 B 有 5 行,那么表 A 的 10 行与表 B 的 5 行相连接,结果集将包含 50 行 若已经存在 ComputerDetails 表中保存了计算机的配置和价 格的详情,商店也销售外设,并且 AddonDetails 表中存储了 这些外设的详情,为了确定一台计算机和所有外设的总价 格: select c.CompDescription,a.AddOnDescription,c.Prince+a.Pri nce’ Total Cost’ from ComputerDetails c cross join AddOnDetails a (4)使用等值连接 等值连接使用外键连接表,但是被用于显示两个表的所有 列,所有连接的表的公共列被显示,与内连接的区别有两个 方面,一方面输出内容不同,另一方面连接符号不同,等值 连接符号只能是’=’,内连接符号是’=,!=,<,>’ 使 用 公 共 列 EmployeeID 在 EmployeeDepartmentHistory,Employee 和 Department 表之 间应用相等连接的例子 select * from HumanResources.EmpoyeeDepartmentHistory d join HumanResources.Employee e on d.EmployeeID=e.EmployeeID join HumanResources.Department p on p.DepartmentID=d.departmentID (5)使用自连接 在一个自连接中,一个表与自己连接,结果在一个表中的一 行与同一个表中的另一行相关,在自连接中,表名在查询中 使用两次,因此为了区别同一个表的两个实例表被给出两个 别名 Employee 表与自己连接以显示 EmployeeID 属性和所有职务 为经理的员工的职务:

第 16 页 共 61 页

Select a.EmployeeId,a.Title ‘ Employee Designation ’ ,a.ManagerID,b.Title ’ Manager Designation ’ from HumanResources.Employee a join HumanResources.Employee b on a.ManagerID=b.EmployeeID 2.使用子查询查询数据 子查询是一个 SQL 语句被用于另外一个 SQL 语句中,子查询 被嵌入到 select,insert,update 和 delete 语句的 where 或 having 从句中,代表查询的查询被称为外部查询,代表子查 询的查询被称为内部查询.数据库引擎首先执行内部查询, 返回结果给外部查询以计算结果. (1) 使用操作符和 in,exists 关键字 1>子查询可以返回一个或多个值,操作符的返回值是单列单 值型 显示 EmployeeID 为 46 的员工部门的名称 select Name from HumanResources.Department where DepartmentID=(select DepartmentID from HumanResources.EmployeeDepartmentHistory where EmployeeID=46 and EndDate is null) 2>使用 in 关键字子查询返回多于一个值,即返回值是单列多 值型 从 Employee 表 中 抽 取 所 有 住 在 Bothell 的 员 工 的 EmployeeID 属性,需使用一个查询获得包含词 Bothell 的所 有 地 址 的 AddressID, 然 后 再 从 Employee 表 中 获 得 EmployeeID select EmployeeID from HumanResources.EmployeeAddress where AddressID in(select AddressID from Person.Address where City=’Bothell’ 3>exists 从句可以检查记录是否存在.exists 关键字总是返 回一个 ture 或 false 值 Exists 关键字不在任何列名,常量或其他表达式前面,并且 它在内部查询的 select 列表中包含星号(*) 查询以显示一个包含和所有在任何时候在市场部门工作的 员工的 EmployeeID 和 Title,其中市场部门的 DepartmentID 是4 select EmloyeeID,Title from HumanResources.Employee where exists(select * from

第 17 页 共 61 页

HumanResources.EmployeeDepartmentHistory where EmployeeID=HumanReources.Employee.EmployeeID and DepartmentID=4) 注:子查询必须使用括号包围并且不能使用 order by 或 compte by 子句 (2)使用修改的比较操作符 使用子查询的时候,你可以使用=,>和<比较操作符以创建检 查子查询返回的值的条件,当子查询返回多于一个值的时候, 你可能需要对子查询返回的值使用操作符,SQL 语句可以用 于修改存在比较操作符的 all 和 any 关键字 使用 all 和 any 关键字的操作符 操 作 描述 符 >all 大于列表中最大值 column_name>all(10,20,30)表示大于 30 <all 小于列表中最小值 c_n<all(10,20,30)表示小于 30 <>all 不 等 于 列 表 中 所 有 值 c_n<>all(10,20,30) 表 示 不 等 于 10,20 和 30 >any 大于列表中的最小值 c_n>any(10,20,30)表示大于 10 <any 小于列表中的最大值 c_n<any(10,20,30)表示小于 30 <>any 不等于列表中的某一值 c_n<>(10,20,30)不等于 10 或 20 或 30 =any 等于列表中的任何值 c_n=any(10,20,30)等于 10 或 20 或 30 显示所有休假小时大于职务为 Recruiter 的员工的休假小时 的 Title 和 EmployeeID: select EmployeeID,Title from HumanResources.Employee where VacationHours >all(select VacationHours from HumanResorces.Emloyee where Title=’Recruiter’) (3)使用集合函数显示那些休假小时大于职务为’市场助理’ 的平均值的员工的 EmployeeID: select EmployeeID from HumanResources.Employee where VacationHoiurrs>(select avg(VacationHours) from HumanResources.Employee wher Title= ’ Marketing Assistant’)

第 18 页 共 61 页

(4)使用嵌入子查询 子查询可以包含一个或多个子查询,子查询依赖另一个查询 的结果的时候被使用,它同样依赖于另一个子查询结果 浏览一个电子邮件地址为 taylor@adventure-works.com 的 员工的 DepartmentID select DepartmentID from HumanResources.EmployeeDepsrtmentHistory where EmployeeID=(select EmployeeID from HumanResources.Employee where ContactID=(select ContactID from Person.Contact where EmailAddress= ’ taylor@adventure-works.com’)) (5)使用相关子查询 相关子查询被定义为依赖于它评估的外部查询的查询,在相 关子查询中 where 从句引用在 from 从句的表中,这表示内部 查询为外部查询指定的表的每行进行求值 查询显示员工的 ID,职务和所有休假小时大于他们职务的平 均休假小时的员工花费在休假的小时数: select EmployeeID,Title from HumanResources.Employee where EmployeeID in (select EmployeeID from HumanResources.EmployeePayHistory where Rate>40) 四.管理数据和表 1.管理数据库 (1)系统数据库 系统数据库是在 SQL Server2005 的每个实例中都存在的标 准数据库,这些数据库包含一个特殊的表集,它们被用于存 储服务器端特定的配置,其它数据库的模板,而且这些数据 库包含一个需要用来查询数据库的临时存储区 SQL Server2005 中包含如下数据库: master tempdb model msdb resource 1> master数据库记录所有器特定的配置信息,包括认证用 户,数据库系统配置和远程服务器,而且它记录了全部实 例的原数据,此数据库包含控制SQL Server运行的关键数

第 19 页 共 61 页

据,它也存储在SQL Server的初始化信息,因此,如果 master数据库不可用, SQL Server数据库引擎将不能启 动,建议不要在master数据库上给任何用户授权,更新 master数据库的备份以反映数据库中的变化也是很重要 的,因为master数据库记录所有的其他数据库的存在和 其他数据库文件的位置,master数据库是SQL Server系统 数据库的主数据库 2>tempdb数据库 tempdb数据库是一个临时数据库,它保存所有临时表和存储 过程,它自动被服务器使用来解析大型或嵌入的查询或显示 结果给户之前排序数据group by,order by和distinct从句 产生的所有临时的表和结果都被存储在tempdb数据库中,不 应将数据库在此数据库中,因为这个数据库在SQL Server每 次启动的时候被重建,这将导致保存的数据丢失 3>model数据库 model数据库作为新数据库的模板或原型,不论数据库何时 被创建, model数据库的内容被复制到新数据库.model数据 库可以为各种在数据定义语言(DDL)语句中指定的参数设置 默认值以创建数据库对象,如果想让每个新数据库包含特定 的数据库对象,你可以在model数据库加入对象,添加对象后 无论何时创建一个新的数据库,对象都会添加到新数据库中 4>msdb数据库 msdb数据库支持SQL Server代理, SQL Server代理是一个计 划SQL Server周期活动的工具,例如备份和数据库邮件,msdb 数据库包含任务计划,异常处理,警告管理和SQL Server执行 服务需要的系统操作符信息,msdb数据库包含一些系统定义 表,它们是特定于数据库的 5>资源数据库 资源数据库是一个只读数据库,包含所有系统对象,例如系 统定义的过程和SQL Server 2005包含的视图,资源数据库不 包含用户数据或用户原数据 (2)识别数据库文件 1>主数据文件:主数据文件包含数据库对象,主文件可以被 用于系统表和对象,而第二数据文件被用于存储用户数据和 对象,主数据文件的扩展名为.mdf .ndf

第 20 页 共 61 页

.ldf (3)创建用户自定义数据库 create database Country on ( name=country1, filename='e:\country1,mdf', size=3mb, maxsize=100mb, filegrowth=2mb ) , ( name=country2, filename='e:\country2.ndf', size=3mb, maxsize=100mb, filegrowth=2mb ) log on ( name=country_log, filename='e:\country_log.ldf', size=3mb, maxsize=100mb, filegrowth=10% ) (4)管理用户自定义数据库 1>查询数据库 Sp_helpdb country 2>重命名 Sp_renamedb country,address 3>增加文件 Alter database Country Add file(or add log file) (

第 21 页 共 61 页

name=country1, filename='e:\country3,ndf', size=3mb, maxsize=100mb, filegrowth=2mb ) 4> 修改文件 alter database Country modify file ( name=country3, filename='e: \country3,ndf', size=5mb, maxsize=200mb, filegrowth=2mb ) 注:在修改文件时名称不能改变,最大存储空间容易从小变 大,不容易从大变小 5>删除数据文件 Remove database country 6>删除数据库 Drop database country 2.管理表 (1)管理表 1>创建表 dbo.作为表的前缀,是默认模式 create table Student ( StudentID int not null, StudentName char(30) not null, StudentAge int not null, Course char(30) not null ) 新建表的另一种方式是在已经打开的数据库表上右击新建 表 2>增加字段

第 22 页 共 61 页

alter table Student add Address varchar(50) not null 3>修改字段 alter table Student alter column Course char(30) null 4>删除字段 alter table Student drop column Course 6>查询表 Sp_help table Student (2)数据完整性 数据完整性是指维护数据的一致性和完整性,数据完整性被 增强以确保数据库的数据是准确的,一致的和可靠的 1>实体完整性:确保每行都可以被一个称为主键的属性唯一 确定,主键列包含所有行中的唯一值,而且这个列不能为 null 2>域完整性:确保仅在列中保存值的有效范围,它可以通过 限制数据的类型,值的范围和数据的格式来执行 3>引用完整性:确保外键的值与相应主键的值相匹配 4>用户定义完整性:指用户指定的一系列规则 (3)创建约束 1>主键约束 一张表中只能创建一个主键,但是主键可以不止一个,主键 所在的列不能为null alter table Student add constraint pkStudentName primary key(StudentID,StudentName) 2>外键约束 一张表中可创建多个外键 alter table Student add constraint fkStudentName foreign key (StudentName) references StudentDetail(StudentName) 3>检查约束 alter table Student add constraint chkStudentAge check(StudentAge between 10 and 80)

第 23 页 共 61 页

4>默认约束 alter table Student add constraint defCourse default 'English' for Course create default defCourse as 'English' sp_binddefault 'defCourse','Student.Course' 5>唯一约束 唯一约束用来增强非主键列的唯一性,它与主键类似,与主 键的区别是它允许 null 值 (4)应用规则: 规则为列或用户定义数据类型增强了域完整性 ,规则在 insert 或 updata 语句被执行之前被应用到列或用户定义数 据类型,换句话说,规则在指定列值或用户定义数据类型上 的限制,规则被用于实现商业相关的限制或约束,规则可以 使用 create rule 语句创建 create rule rulStudentAge as @age between 15 and 30 sp_bindrule ‘ rulStudentAge ’ , ’ dbo.Student.StudentAge’ (5)使用用户定义数据类型 用户定义数据类型允许修改数据库中使用的组合数据类型 1> sp_add type,typeCity,’char(20)’,’not null’ 2> create type typeCity from char(20) not null alter table Student add City typeCity 五.在表中操纵数据 1.使用 DML 语句操纵数据 (1)在表中添加数据 在 Student 表 中 添 加 StudentID,StudentName,StudentAge,City 等信息 Insert into Student(StudentID,StudentName,StudentAge,City) Values(1,’jack’,20,’nj’) 将多组数据添加到表: 所添加的内容,顺序,个数,类型应与表相匹配

第 24 页 共 61 页

(2)从现有表中复制数据到新表 将 EmployeePayHistory 表复制数据到 PerferedEmployee 表 中: Select * into PerferredEmployee from HumanResources.EmployeePayHistory where Rate>=35 (3)更新表 更新数据的指导方针: 一次更新只能在一个表中完成,如果一个更新违反完整性约 束,那么整个更新被回滚 在 Employee 表中更新一个员工 Lynn Tsoflias 的职位为’ Sales Executive’在此过程中需参 Contact 考表来获得 ContactID: Update HumanResources.Employee set Title=’Sales Executive’ from HumanResources.Employee e,Person.Contact c where e.ContactID=c.ContactID and c.FirstName=’Lynn’ and c.LastName=’Tsoflias’ 更新 Address 表中 AddressID 为 104 的 AddressLine2 的属 性 Update Address set AddressID=’Plaza.Palace’ where AddressID=’104’ (4)从表中删除数据 Delete Address where AddressID=’104’ Employee 表包含的那些从公司退休的员工的数据需要从表 中删除 Delete from HumanResources.Employee where BirthDate<dateadd(yy,-60,gatedate()) (5)删除表中全部记录 删除 Address 表中全部记录: 1> delete Address 2> truncate table Address 注:truncate table 执行很快,并且不支持 where 从句,当它 被使用时删除不进入事务日志 2.操纵 XML 数据

第 25 页 共 61 页

(1)在表中存储 XML 数据 (2)从表中抽取 XML 数据 有以下方法可以抽取 XML 数据 在 Select 语句中使用 for XML 从句 使用 XQuery 1>在语句中使用 for XML 从句 此语句中可通过以下模式使用 for XML 从句来抽取 XML 数据: raw auto path explicit 2>使用 raw 模式 显示带有 EmployeeID 为 1 或 2 的员工的详情: Select EmployeeID,ContactID,LoginID,Title from HumanResources.Employee where EmployeeID=1 or EmployeeID=2 for XML raw 3>使用 auto 模式 Select EmployeeID,ContactID,LoginID,Title from HumanResources.Employee where EmployeeID=1 or EmployeeID=2 for XML auto(,elements) 如果可选指示符在 for XML 从句中被指定,在 select 从句中 列出的列被映射为子元素 4>使用 path 模式 path 被用来返回特定值 select EmployeeID”@EmplyeeID”, FirstName”EmployeeName/First”, MiddleName”@EmployeeName/Middle”, LastName”@EmployeeName/Last” from HumanResources.Employee e join Person.Contact c on e.ContactID=c.ContactID and e.EmployeeID=1 for XML path 可以在 path 模式查询中使用 ElementName 参数以修改默认 行元素的名称 select EmployeeID”@EmplyeeID”, FirstName”EmployeeName/First”, MiddleName”@EmployeeName/Middle”,

第 26 页 共 61 页

LastName”@EmployeeName/Last” from HumanResources.Employee e join Person.Contact c on e.ContactID=c.ContactID and e.EmployeeID=1 for XML path(‘Employee’) 六.实现索引,视图和全文搜索 1.创建和管理索引 (1)数据库表中的数据以数据页的形式存储,每个数据页是 8KB 大小 索引的作用: 1> 加快连接表的查询和完成排序和分组(快速定位) 2> 增加唯一性 索引中的键在内存中以 B-树(2 叉树)的形式存储,当修改了 索引列的数据的时候,相关的索引自动被更新 SQL Sever 中允许创建的索引类型: 簇索引 非簇索引 1>簇索引基于它们的键值(主键或唯一键)排序和存储表中 的数据行,因此当在其上定义了簇索引的时候,数据在表中 被物理排序一个表上仅有一个簇索引可以被创建,因此,应 该在具有唯一值的高分比和不常被修改的属性上创建簇索 引,簇索引确定行实际被存储的顺序,因此可以仅在表上建 立一个簇索引 2>非簇索引包含索引键值和行定位器,在非簇索引中,行的 物理顺序与索引顺序不同(物理顺序:严格按照时间等顺序 来排序的.索引顺序:为完成某种功能,如成绩高低而排列的 次序) 非簇索引通常是使用连接和 where 从句在列上创建的,可以 在值经常变动的列上创建,使用 create index 命令的时 候,SQL Sever 默认创建非簇索引,每个表最多可创建 249 个 非簇索引 (2)创建索引 在 Employee 表 中 维 护 员 工 的 详 情 , 在 Employee 表 的 EmployeeID 属性上创建簇索引: Create custered index IX_EmployeeID on Employee(EmpoyeeID) (with fillfactor=10)~在簇索引上 的预留空间

第 27 页 共 61 页

在 Employee 表的 ManagerID 属性上创建一个非簇索引: Create nonclustered index ID_Employee_ManagerID on Employee(ManagerID) 注:当在表上创建 paimary key 或 unique 约束的时候索引以 与约束同样的名称被自动创建 创建索引的指导方针: 1> 在有唯一值或非空值的列上创建簇索引 2> 不要创建一个不常用的索引,你需要时间和资源来维护 3> 要在创建非簇索引之前创建簇索引,创建簇索引改变行的 顺序,非簇索引将需要重建,如果它在簇索引之前创建 4> 在查询中经常使用的谓词和连接条件的所有列上创建非 簇索引 (3)管理索引 1>禁止索引 如果簇索引被禁止,那么表数据对用户不可用,但数据仍存 在于表中,但是对于数据修改语言操纵不可用指定索引被删 除或重建 为了创建和使用禁止的索引,使用 alter index rebuild 语 句或 create index with drop_existing 语句 在 Employee 表上禁止非簇索引,IX_EmployeeID Alter index IX_EmployeeID on Employee disable 2>激活索引 索引被禁止后,它仍然保持禁止状态直到它被重建或删除, 可使用语句: 使用 rebuild 从句的 alter index 语句 使用 drop_existing 从句的 create index 语句 使用 dbcc dbreindex 索引被重建并且被设为可用 online 选项设为 on 是可重建一 个被禁止的簇索引 Alter index pk_Employee_EmployeeID on HumanResources.Employee ebuild with(PAD_Index=off,Statistics_norecompute=off allow row online=on) 3> 重命名索引 在 JodCandidate 表上重命名 IX_JobCandidate_EmployeeID 索引为 IX_EmployeeID

第 28 页 共 61 页

Exec sp_rename ‘ HumanRresources.JobCandidate.IX_JobCandidate_Empl oyeeID’,’IX_EmployeeID’,’index’ 4> 删除索引 在删除索引的进程中,不能删除由 primary key 或 unique 约束使用的索引,除非你将约束删除 将 Employee 表上的索引 IDX_Employee_ManagerID 删除 Drop index IDX_Employee_ManagerID on Employee 5> 优化索引 当大量插入和更新操作表上完成的时候,通常产生碎片,碎 片数据将导致 SQL Sever 完成不必要的数据读取,索引碎片 整理程序来加速查询性能 决定使用哪个碎片清理方法,首先要确定索引碎片的程序, 可使用 sp.dm_db_index_physical_stats 系统函数来检测索 引碎片 显示 Employee 表上的所有索引列表和它们的碎片等级: Select a .index_id ‘ IndexID ’ ,name ’ IndexName ’ ,avg_fragmentation_in_percent ’ Framentation ’ from sys.dm_db_index_physical_stats(DB_ID(N ’ AdventureWorks’),object_ID(‘HumanResources.Employ ee ’ )null,null,null) as a join sys.index as b on a.object_id=b.object_id and a .index_id=b.index_id order by Fragmentation desc 在得知碎片等级之后,碎片需被更正 碎片等级 要采取的行动 >50%&&<=30% Alter index reorganize >30% Alter index rebuild with (online=on) 2.创建和管理视图 视图也有助于简化查询执行当查询包含从多表中应用连接 抽取数据的时候,视图是一个虚拟表,它提高对来自一个或 多个表的列的子集的访问,就是查询在数据库中存储为对象, 它没有自己的数据,视图可以从一个或多个表中产生,其中 表被称为基表或内含表 (1)创建视图

第 29 页 共 61 页

视图是一个数据库对象,它被用于在数据库中浏览来自表的 数据,视图与表有一样的结构,它不含任何数据,但从内含的 表中产生数据 视图创建的指导方针: 1> 视图的名称必须遵守标识符的规则并且不必与它所基于 的表的名称一样 2> 视图仅可以在有 select 授权的基表上创建 3>视图不能从临时表中产生数据 4>在视图中 order by 不能用于 select 语句中 让 所 有 的 员 工 访 问 EmployeeID,Mariltal,StatusDepartmentID 可以创建以下 视图: Create view HumanResources.vwEmployeeDepData As Select e.EmployeeID,MaritalStatus,DepartmentID from HumanResources.Employee e join HumanResources.EmployeeDepaartmentHistory d on e.EmployeeID=d.EmployeeID (2)通过视图修改数据时的限制 视图不维护独立的数据副本,但仅显示基表中的数据显示, 所以可以通过修改视图中的数据修改基表 ,但当通过视图 插入,更新或删除数据的时候,存在限制: 1> 若修改的数据仅影响一个表,那么视图中的数据可修改, 若修改时影响多个表则不能修改视图的数据 2> 不能改变计算结果的列,如一个计算列或集合函数 显示 EmployeeID,ManagerID 和员工 Rate 的视图: Create view vwSal As Select i.EmployeeID,i.ManagerID,j.Rate from HumanResooources.Employee i join HumanResources.EmployeePayHistory j on i.EmployeeID=j.EmployeeID 对创建的视图更新: update vwSal set ManagerID=2,Rate=12.45 where EmployeeID=1

第 30 页 共 61 页

上述执行的语句发生错误,因为修改的数据是在两个表中通 过一个更新表语句完成的,所以除了单一的 update 语句,你 需要为每个表执行一个 update 语句: update vwSal set ManagerID=2 where EmployeeID=1 update vwSal set Rate=12.45 where EmployeeID=1 因此为了通过视图修改两个或更多内含表的数据,需要为每 个表执行独立的 update 语句,如果修改多于一个内含表,你 可以在视图上创建 instead of 触发器以在视图中修改数据 (3)索引视图 在默认情况下表上创建的视图是非索引的,可以在内含表的 数据容量很大并且不常被更新的时候索引视图,索引视图有 助于提高查询的性能 当索引一个视图时,首先需在视图上创建一个唯一的簇索引, 然后才可以创建额外的非簇索引 创建索引视图的指导方针: 1> 在创建任何索引之前必须建立唯一簇索引 2> 索引视图可以不用参考其他视图而仅参考基表 3> 索引视图参考的基表必须在同一个数据库中,并且与视图 是同一个拥有者 4>视图必须带 schemabinding 选项创建,模式绑定将视图与 内含基表的模式绑定 使 用 create index 语 句 在 视 图 上 创 建 索 引 , 在 vwEmployeeDepData 视图上创建唯一簇索引: Create unique cluster index idx_vwEmployeeDepData on HumanResources.vwEmployeeDepData(EmployeeID,Departm entID) vwEmployeeDepData 视图在创建的时候并没有绑定到模式 上,因此在执行上述语句前要将 vwEmployeeDepData 绑定到 模式: alter view vwEmployeeDepData with schemabinding as select e.EmployeeID,MaritalStatus,DepartmentID from HumanResources.Employee e join

第 31 页 共 61 页

HumanResources.DepartmentHistory d on e.EmployeeID=d.EmployeeID (4)管理视图 1>修改视图 从 Employee 和 EmployeeDepartmentHistory 表中创建了一 个视图以抽取选择的数据需要从 Employee 表包含 LoginID 属性来修改视图定义: alter view vwEmployeeDepData as select e.EmployeeID,LoginID,MaritalStatus,DepartmentID from HumanResources.EmployeeDepartmentHistory d join HumanResources.Employee e on e.EmployeeID=d.EmployeeID 2>重命名视图 将 vwSal 重命名为 vwSalary: sp_rename vwSal,vwSalary 重命名时必须确保: 视图必须存在当前数据库中 视图的新名称必须遵守标识符的规则视图仅可以被它的拥 有者重命名,数据库的拥有者也可对它重命名 3>删除视图 删除视图 vwEmployeeDepData: drop view vwEmployeeDepData 3.实现全文搜索 在 SQL Sever 2005 中,默认情况下全文搜索是禁止的 (1)配置全文搜索: 在数据库中激活全文搜索 创建全文目录 创建唯一索引 创建全文索引 放置全文索引 1>激活数据中的全文搜索 use AdventureWorks go sp_fulltext enable go 2>创建全文目录

第 32 页 共 61 页

全文目录作为一个容器以存储全文索引,全文目录可以有多 个全文索引 create fulltext catalog Cat1 as default 3>创建唯一索引 在 Production.ProductDescription 表上创建一个唯一索 引: create unique index IX_Desc on Production.ProductDescription(ProductDescriptionID) 4>创建全文索引 全文索引可以在基表上创建,但不能在视图或系统表上创建 在 Description 列上创建全文索引: create fulltext inbdex on Production.ProductDescription(Description) key index IX_Desc (2)关联全文索引 完全关联 改变基于跟踪的关联 基于时间?的增量关联 (3)使用全文索引搜索数据 freetext:搜索在搜索列中给出的任何词或词组的变体,语 句在乎的只是 freetext 的含义, 只要与 freetext 意义相似, 就会被查询出来,搜索结果较多 考虑自行车比赛的场景,使用 freetext 谓词来得到预期的 输出: slect Description from Production.ProductDescription where freetext(Description,’race winners’) contains:搜索精确词根或与词根相匹配的词,也可以查找 与词根相同的词,如 swim,swam 在 ProductDescription 表中搜索与’ Bike’ 相近的词’ Ride’ select Description from Production.ProductDesscription where contains(Description,’ride near bike’) 七.实现存储过程和函数 1.实现批处理 批处理是一组一起提交给 SQL Sever 执行的 SQL 语句,批处 理执行是 SQL Sever 将批处理的语句编译到一个成为执行计

第 33 页 共 61 页

划的可执行单元,这有助于节省执行时间 在 AdventureWorks 数据库中存储新员工的详情: insert into Person.Contact values(0,null,’Robert’,’J’,’Langdoon’,null,’ rbl@adventure-works.com’,0,’1(11)500 555-0172’,’ 9E685 — ACDO-4218-AD7F-60DDF224C452 ’ , ’ 20310EW=’,null,newid(),getdate()) insert into HumanResources.Employee values( ‘AS01AS25R2E365W’ .19978,’ robert1’ ,16,’ Tool Designer ’ , ’ 1972-05-15 ’ , ’ S ’ , ’ M ’ , ’ 1996-07-31’.0,16,20,1,newid(),getdate()) go 批处理提交以后,如果发生编译错误,如语法错误,执行计划 不被创建,如果批处理在运行过程中发生错误,批处理被停 止执行,在此情况下,遇到运行时错误的语句之前执行的语 句不受影响 使用变量和显示用户定义消息 declare @empid int ,@Rate money select(set) @empid=EmployeeID,@Rate=max(Rate) from HumanResources.EmployeePayHistory print @Rate go declare @a int,@b int,@max int select @a=32,@b=64 if @a>@b set @max=@a else set @max=@b print @max go set 关键字一次只能对 1 个变量赋值而 select 可同时对多个 变量赋值 在批处理中可使用注释实体来写出代码的描述,注释实体可 以以两种方式写出: 使用/*和*/包围多行注释实体 单一注释实体以__开始

第 34 页 共 61 页

(1)创建批处理的指导方针 指导方针: 1>在创建批处理时,不能将语句组合,例如 create default, create function,create produre,create rule,create trigger 和 create view 与其它语句 2>可以在批处理中使用 execute 语句,当它不是批处理的第 一条语句的时候,否则 execute 语句暗中执行 3>限制: 不能绑定规则和默认值到列并且在同一批处理中使用他们 不能在同一批处理中定义和使用 check 约束 不能在同一批处理中删除对象并且重建它们 不能通过添加列修改表并且之后指向批处理中之前创建的 新列 (2)使用结构 结构包括:顺序结构,选择结构,循环结构 SQL Sever 允许在批处理中为语句的条件执行使用编程结构, 可使用 if…else 语句,case 语句和 while 语句来控制语句的 流程: 1> if…else 语句 从 EmployeePayHitory 表抽取员工的支付率到变量@Rate 中, @Rate 变量的值使用<比较操作符与 15 比较,基于条件,不同 消息被显示 declare @Rate money select @Rate=Rate from Humanesources.EmployeePayHistory where EmployeeID=23 if @Rate<15 print’Review of the rate is required’ else begin print ’Review of the rate is not required’ print ‘Rate=’ print @Rate end go 将考试分数的百分制转换成五级制

第 35 页 共 61 页

declare @score int,@level char(2) set @score=85 if @sore>=90 and @score<100 set @level=’A’ else if @sore>=80 set @level=’B’ else if @sore>=70 set @level=’C’ else if @sore>=60 set @level=’D’ else set @level=’E’ print @level 检查被完成来浏览 Sales 部门的存在性,如果销售部门存在 所有详情被显示,否则显示一个用户定义的销售 if exists(select * from HumanResources.Department where Name=’Sales’) begin select * from HumanResources.Department where Name=’Sales’ end else print’Department detail not available’ go 2> 使用 case 语句 case 语句评估条件的一个列表并且返回可能结果中的一个, 可使用 if 语句完成同样的任务.当有多于两个条件为不同值 检查公共变量的时候可使用 case 语句 case 结构被包含在 select 语句中以显示’Married’或’ Single’为结婚状态: select EmployeeID,’Marital status’= case MaritalStatus when ‘M’ then ‘Marital’ when ‘S’ then ‘Single’ else ‘Not specified’ end

第 36 页 共 61 页

from HumanResources.Employee go 百分制与等级制的转换: declare @cj int,@dj char(2) set @cj=85 select @dj= case @cj/10= when 10 then ‘A’ when 9 then ‘A’ when 8 then ‘B’ when 7 then ‘C’ when 6 then ‘D’ else ‘E’ end print @dj 3> 使用 while 语句 AdventureWorks,Inc 的 HR 部门决定浏览所有员工薪水,按 照当前的 HR 政策,所有员工时薪平均值应该大约 20,需要增 加所有员工的时薪指定时薪平均值接近$20,你需要确保最 大时薪不应该超过$127 while(select avg(Rate)+1 from HumanResources.EmployeePayHistory)<20 begin update HumanResources.EmployeePayHistory set Rate=Rate+1 from HumanResources.EmployeePayHistory if (select max(Rate)+1 from HumanResources.EmployeePyHistory)>127 break else continue end (3)处理错误和异常 在执行查询的时候,在语句执行期间由某种原因如不正确的 数据,发生的错误被称为异常 捕捉异常可以提高程序的健壮性

第 37 页 共 61 页

数据库服务器提供数据库支持商业程序的时候,在执行 SQL 语句的时候产生的错误可使用以下两种处理方式: 使用 try-catch 结构通过添加错误处理代码到批处理中 通过使用 raiserror 语句并且在程序中处理错误,返回错误 到商业程序 1> 使用 try-catch try-catch 结构返回系统错误,结构包括一个 try 块,之后跟 一个 catch 块 try 块是一组包含批处理,存储过程,触发器或函数中的 SQL 语句组,在 try 块的任何语句发生了错误,控制将被转移到 catch 块内的另一个语句组中 catch 块必须立刻跟在一个 try 块之后,如果在 try 块中包含 的代码没有错误,控制被立即转移到相应的 catch 块之后的 语句中,在这种情况下,包含在 catch 块中的语句没有被执行 try-catch 结构是可以嵌套的,一个 try 块或 catch 块可以包 含嵌套的结构,catch 可以包含一个内嵌的 try-catch 结构 来处理 catch 代码遇到的错误 在 catch 块中可使用系统函数来确定关于错误的信息: a) error_line():返回发生错误的行号 b) error_message():指出应该返回程序的消息文本,文本包 括为任何可替代的参数提供的值,如长度,对象,名称或时 间 c) error_number():返回错误号 d) error_procedure():返回发生错误的存储过程或触发器 的名称,如果错误不是发生在存储过程或触发器的内部函 数将返回值 e) error_everity():返回严重性 0-25 等级,0-18 是用户可 处理的错误,19-25 是系统管理员处理的 f) error_status():返回错误状态 在 AdventureWorks 数据库 Employee 表的 EmployeeID 属性 是一个 identity 列并且它的值不能被指定插入新记录的时 候,如果此时在 inert 语句中为 EmloyeeID 指定的值将产生 一个错误: begin try insert into Person.Contact values(0,null,’Robert’,’J’,’Langdoon’,null,’

第 38 页 共 61 页

rbl@adventure-works.com’,0,’1(11)500 555-0172’,’ 9E685 — ACDO-4218-AD7F-60DDF224C452 ’ , ’ 20310EW=’,null,newid(),getdate()) insert into HumanResources.Employee values( ‘AS01AS25R2E365W’ .19978,’ robert1’ ,16,’ Tool Designer ’ , ’ 1972-05-15 ’ , ’ S ’ , ’ M ’ , ’ 1996-07-31’.0,16,20,1,newid(),getdate()) end try begin catch select ‘There was an error!’+ error_message() as ErrorMessage error_line() as ErrorLine error_number() as ErrorNumber error_procedure() as ErrorProcedure error_everity() as ErrorEverity error_status() as ErrorStatus end catch go 2> 使用 raiserror raiserror 语句被用于返回消息到商业程序 raiserror 手动的抛出用户自定义的错误信息 AdventureWorks 数据库存储员工工作的轮班详情,需要更新 Shift 表以更新轮换的时间,当更新轮换的详情的时候,需确 保开始时间和结束时间之差是 8 小时,如果它小于 8 小时错 误发生,并且更新过程停止: begin try declare @start datetime declare @end datetime declare @Date_diff int select @start=’1900-01-01 23:00:00.0000’,@end= ’1900-01-02 06:00:00.0000’ select @Date_diff=datediff(hh,@start,@end) if(@Date_diff!=8) raiserror(‘Error Raised’,16,1) else begin

第 39 页 共 61 页

update HumanResources.Shift select @start=StartTime,@end=EndTime where ShiftID=3 end end try begin catch print’The difference between the start and end time should be 8’ end catch go 2.实现存储过程 存储过程是数据库中保存的预编译对象,存储过程可以调用 数据定义语言(DDL)和数据操语言(DML)语句并且返回值. 存储过程的作用:减轻网络阻塞,提高性能 用户自定义存储过程的前缀 PRC 临时存储过程前缀# 扩展存储过程前缀 XP 创建一个存储过程以浏览来自 Department 表的部门名称: create procedure prcDept as begin select Name from HumanResources.Department end 1> 编译存储过程的步骤: 解析:过程被编译,并且它的组件被分成各种片 分解:引用对象的存在性,例如表和视图被检查 存储名称及代码:过程的名字被存储在 sysobjects 表,代码 存储在 syscomments 表中 保存执行计划:过程被编译并且查询将如何运行的蓝图被创 建,这个蓝图被称为执行计划,执行计划被保存在过程缓存 中 读取,优化运行过程:当过程首次执行时执行计划将被读取 和完全优化,然后运行,下一次过程以同样的次序被执行,它 将从缓存中直接读取,这提高了性能,因为没有重复编译 在创建存储过之后,可以通过使用 sp_helptext 命令来浏览

第 40 页 共 61 页

过程的代码 2>创建存储过程的指导方针: 不能在单个批处理中将 create procedure 语句和其它 SQL 语句结合 必须在数据库中有 create procedure 授权以创建一个存储 过程,并且在模式上有 alter 授权,这里过程被创建 可以仅在当前数据库中创建一个存储过程 3>执行存储过程 过程可通过使用 exec procedure 语句执行 Robert 已经创建了一个名为 DisEmpdetail 的存储过程,它 显示员工的详情,Kim 需要执行过程,但没有执行的权利,在 此情况下,可以使用 Robert 的执行权利 exec procedure prcDept 4>修改存储过程 对存储过程进行修改,DepartmentID 属性将与部门名称一起 显示 alter proc prcDept as begin select DepartmentID,Name from HumanResource.Department end 5>删除存储过程 过程一旦被删除就不能被抽取 删除 prcDept 存储过程: drop procedure prcDept 2. 创建带参数的存储过程 参数被用于运行时传递值到存储过程,这些值通过标准变量 被传递,传递值的参数被定义为输入参数,存储过程最多使 用 2100 个参数每个参数有名称,数据,类型和默认值 创建一个存储过程,它显示员工 ID,登陆 ID 和员工职务,它 在执行期间有与作为提高的职务相同的值: create proc prcListEmployee @title char(50) as begin print ‘List of Employees’

第 41 页 共 61 页

select EmployeeID,LoginID,Title from HumanResources.Employee where Title=@title end execute prcListEmployee ‘Tool Designer’ execute prcListEmployee @title=‘Tool Designer’ (1)从存储过程返回值 可以从过程返回作为输出的值,值通过输出参数返回到调用 程序,为了指定一个参数为输出参数,可使用 output 关键字 output 关键字必须在 create procedure 和 execute 语句中 被指定,如果 output 关键字漏掉过程依然执行,但不会返回 任何值,如果 output 关键字没有使用那么参数被认为是输入 参数,使用 reture 语句从存储过程返回值, reture 语句允许 存储过程仅返回一个整型值到调用程序, reture value,value 是任何整型,如果没有被指定,那么存储过程返 回默认值 0 以指明失败和 1 指明成功 显示员工的详情,它的员工 ID 被作为输入提供,需创建一个 过程 prcGetEmployeeDtail,它接受员工 ID 作为输入并且返 回部门名称和员工工作的轮换 ID: create procedure prcGetEmployeeDetail @EmpId int, @DepName char(50) output,@ShiftId int output as begin if exists(select * from HumanResources.Employee where EmployeeID=@EmpId) begin select @DepName=d.Name,@shiftId=h.ShiftID from HumanResources.EmployeeDepartmentHistory h join HumanResources. Department d on h.DepartmentID=d.DepartmentID where EmployeeID=@EmpId and h.EndDate is null return 0 end else return1 end

第 42 页 共 61 页

(2)从另一个过程调用过程 prcGetEmployeeDetail 过程返回给员工 ID 的员工详情,创 建 prcDisplayEmployeeStatus 过程,它接受员工的员工 ID 作为输入并且显示部门名称和与经理 ID 一起的员工工作的 轮换 ID 和员工的职务: create procedure prcDisplayEmployeeStatus @EmpId int as begin declare @DepName char(50) declare @ShiftId int declare @ReturnValue int exec @ReturnValue=prcGetEmployeeDetail @EmpId, @DepName output,@shiftId output if(@ReturnValue=0) begin print’The details of an employee with ID:’ +convert(char(10)@EmpId) print’Department Name:’+@DepName print’ShiftID:’+convert(char(1),@shiftId) select ManagerID,Title from HumanResources.Employee where EmployeeID=@EmpId end else print’No record found for the given employee exec prcDisplayEmployeeStatus 2 SQL Sever 提供了一个函数,@@Rowcount 它返回最后语句影 响的行数,你可以在结构中使用这个语句检查最后执行的语 句的结果 人力资源部门需要修改员工的支付详情,需创建一个存储过 程来得到百分比值,通过这个值来增加支付率,而且确保被 修改的员工的支付率在最近六个月没有被修改过: create procedure PayRateIncrease @EmpId int,@percent float as begin

第 43 页 共 61 页

declare @maxRate float declare @RevisedRate float declare @PayFre int if exists(select*from HumanResources.EmployeePayHistory where datediff(mm, RateChangeDate,getdate())>6 and EmployeeID=@EmpId) begin select @maxRate=Rate from HumanResources.EmployeePayHistory where EmployeeID=@EmpId if (@maxRate*@percent>200.00) begin print’Rate of an employee cannot be greater than 200.00’ end else begin select @Revise Rate=Rate,@PayFre=PayFrequency from HumanResources.EmployeePayhistory where EmployeeID=@EmpId set @RevisedRate=@RevisedRate*@percent insert into Humanresources.EmployeePayHistory values(@EmpId,getdate(),@RevisedRate,@PayFre, getdate()) end end end 执行存储过程 exec PayRateIncrease 6,2 验证结果 select*from HumanResources.EmployeePayhistory where EmployeeID=6 order by ModifiedDate desc 4.实现函数 与存储过程类似,可以创建函数来永久存储一系列 T_SQL 语 句,这些函数也指用户定义的函数(UDFs), UDF 是包含一系

第 44 页 共 61 页

列 T_SQL 语句,接收参数,完成动作并且返回动态的结果作为 值的数据库对象,返回值可以是单个标量值或结果集,UDFs 具有有限的范围,UDFs 有不同的类型:标量函数和表值函数 (1)创建 UDFs 一个 UDF 包含的组件有: 1> 带可选模式(或拥有者名称)的函数名 2> 输入参数名和数据类型 3> 可应用于输入参数的选项 4> 返回参数数据类型和选项名 5> 应用于返回参数的选项 6> 一个或多个 T_SQL 语句 在函数名前应写出模式,模式又称为架构,函数名必须遵守 标识符的规范并且函数名必须在数据库和它的所属模式中 唯一,数据中的默认模式是 dbo. (2)创建标量函数 标量函数接收一个参数并且返回在 returns 从句中指定的类 型的一个数据值 计算员工月薪,它接收支付率作为输入并且返回一个值,在 将小时数和天数与它相乘后: create function HumanResources.MonthlySal(@PayRate float) return float as begin return(@PayRate*8*30) end 使用语句执行函数: declare @PayRate float set @PayRate=humanResources.MonthlySal(12,25) print @PayRate (3)创建表值函数 表值函数返回输出作为表数据类型,表数据类型是用于存储 一系列行的特殊数据类型的变量,内联表值函数和多语句表 值函数 1>内联表函数内联表值函数从一个 select 语句的结果集返 回一个数据类型的变量,内联函数不在 begin 和 end 语句中

第 45 页 共 61 页

包含函数体,内联函数用于执行一个查询语句,变量的数据 类型不仅是 int, float,double,money,char 等,还可以是 table 内联函数体可对临时表进行操作 一个内联表函数,fx_Department_GName 接收一组名称作为 参数并且返回来自 Dpartment 表属于组的部门的详情,可使 用以下语句创建函数: create function fx_Department_GName(@GName nvchar(20) return table as return (select*from Hmanreources.department where GroupName = @GrNam ) go 执行带指定函数的 fx_Department_GName 函数: select*from fx_Department_GName(‘Manufacturing’) 建立一个内联函数,它接收支付率作为参数并且返回所有的 记录,这些记录具有大于参数值的支付率: create function HumanResources.Emp_Pay(@Rate int) return table as return (select e.EmployeeID,e.Title,er.Rate from HumanResources.Employee e join Humanresources.EmployeePayhistory er on e.EmployeeID= er.EmployeeID where er.Rate>@Rate) go 2>多语句表值函数 多语句表值函数使用多个语句来创建表,它被返回给调用语 句,函数体包含 begin…end 块,它保存一系列 T_SQL 语句以 创建和插入行到临时表,临时表被在结果集中返回,并且基 于函数中提到的规范创建 创建 PayRate 来返回来自 EmployeePayhistory 表的一系列 记录: create function PayRate(@rate money) return @table table

第 46 页 共 61 页

(EmployeeID int not null, RateChangeDate datetime not null, Rate money not null, PayFrequency tinyint not null, ModifiedDate datetime not null) as begin insert @table select*fromHumanResources.EmployeePayhistory where Rate>@rate return end 执行函数: select*from PayRate 依赖于函数返回的结果集,函数可以分为确定的不确定的, 确定函数总是返回同样的结果集不论何时它们被带有输入 的特定集调用,但是每次它们被带有输入值的特定集调用的 时候, 非确定函数可能返回不同结果,确定函数的例子是 dateadd,它为它的三个参数的任何函数值的给定集返回同 样的结果,getdate 是非确定的函数,因为它总是不带任何参 数被调用,但返回值在每次执行的时候改变 创建函数 创建一个函数,它接收员工的员工 ID 并且返回 EmployeeID, 员工姓名,职务以及下属员工的数量: create function EmployeeDetail(@Eid int) return table as return (select t1.EmpoyeeID,t2.FristName’ Name’,t1.Title,(select count(EmployeeID from HumanResources.Employee where ManagerID=@Eid)’ Number of employees’ from Humanresorces.Employee t1 join Person.Contact t2 on t1.ContactID=t2.ContactID where t1.EmployeeID=@Eid group by t2.FristName,t1.EmployeeID,t1.Title) 执行函数:

第 47 页 共 61 页

select*from EmployeeDetails(16) 八.使用触发器和事务 1.实现和识别触发器 触发器是一块代码,是一种特殊存储过程一系列响应某些动 作激发的 T_SQL 语句组成它会自动被 SQL 语句触发,无需手 动触发,创建后可直接执行 在 SQL Sever 中有各种类型的触发器可以用来进行不同数据 操纵的类型 SQL Sever 支持的触发器类型: 数据修改语言(DML)触发器:(insert update delete) 数据定义语言(DDL)触发器:(create alter drop) (1)DML 触发器 当关联的表被 DML 语句影响时,DML 触发器被触发,这些触发 器有助于维护一致性,可靠性和表中的正确数据,它们能够 完成复杂的动作串联到其它相依赖的表,串联是在一个表中 所做的变更反映在表中的过程 DML 触发器的特性: 1>任何数据修改语句被提交的时候它都会由 SQL Sever 自动 触发 2>像存储过程一样,不能被显示的调用或执行 3>防止错误,未授权和数据中的不一致变更 4>不能返回数据给用户 5>可以被嵌套最高层 32 层,当触发器完成一个激发其它触发 器的动作的时候嵌套触发器发生 触发器被作为 insert,update,delete 语句的响应触发, SQL Sever 创建两个临时表被成为魔表,魔表是概念表,并且在结 构上与定义触发器的表相似 魔表有两种:insert 魔表,delete 魔表 insert 魔表:将要插入的数据放入缓冲区(即 insert 魔表), 然后在放入触发器表中 delete 魔表:delete 操作也是先将要删除的数据放入 delete 魔表中,若能将数据删除则删除,不能删除数据将回 滚 已插入的表中包含在触发器中插入的所有记录的副本,已删 除的表中包含在触发器中删除的所有的记录,无论何时在表 中更新数据,触发器都使用已插入或已删除的表

第 48 页 共 61 页

1>插入触发器:在触发器表中插入数据时此触发器被触发当 insert 语句执行的时候新列被添加到触发器中,原数据被放 入已删除表中 2>删除触发器:在表中删除数据时被触发,delete 语句执行 时触发器表中的特定行被删除,并且添加到已删除表中 通过使用触发器实现数据完整性: 串联方法:从主表中删除数据时,依赖表中的数据也被删除 限制方法:相关记录在从表中出现,则主表中的数据不能被 删除 无效方法:记录从主表中删除,从表中的记录变为 null 值 3>更新触发器:当 update 语句在触发器表中执行时候被触发, 它用于它作用的两个逻辑表,在所有的更新完成之后,已删 除表和已插入表被生成并且触发器被触发 (2)DDL 触发器 DDL 触发器响应 DDL 语句,如 create table 或 alter table,DDL 触发器可以被用于完成管理任务,例如数据库审 计.数据库审计有助于监视 DDL 操作,DDL 操作包括表或视图 的创建,或表的修改等,如果想让数据库管理员在主数据库 上有表的创建的时候被通知,可使用 DDL 触发器实现 根据触发器被触发的方式将触发器分为: 后触发器:after 触发器 替代触发器:instead of 触发器 嵌套触发器 递归触发器 1>后触发器 后触发器可在任何表中插入,与其它触发器比较,后触发器 的功能主要区别在于它是在它所定义的 DML 操作执行之后被 触发,后触发器的执行次序落后于约束,当表上有多个触发 器被创建时,默认情况下它们执行的顺序是它们被创建的顺 序 2>替代触发器 替代触发器可用于以下操作: <1>忽略批处理部分 <2>未处理批处理的部分和记录问题行 <3>当遇到错误条件的时候,采取替代操作 替代触发器与后触发器的区别在于它不能在一张表或视图

第 49 页 共 61 页

上创建多个替代触发器 3>嵌套触发器 嵌套触发器通过其它触发器的触发而触发 4>迭代触发器 迭代触发器是一种特殊的嵌套触发器,它在数据库级提供支 持,并最终调用自己,可分为两种类型:直接和间接迭代触发 器 <1>直接迭代触发器 它可在同样的表上完成同样的操作导致触发器再次触发自 己 <2>间接迭代触发器 间接迭代触发器在另一张表上触发一个触发器,并且最终嵌 套触发器,结束于再次触发第一个触发器,例如在表 1 上触发 一个触发器,导致表 2 上的触发器被触发,表 2 上的数据更新, 这又导致了表 3 上的触发器被触发,当表 3 完成更新后又导 致了表 1 上的更新触发器被再次触发 修改日期以设置为当前日期,新记录被加入到表中: create trigger trginsertShift on HumanResources.Shift for insert as begin declare @modifiedDate datetime select @modifiedDate=ModifiedDate from inserted if(@modifiedDate!=getdate()) begin print’The modifiedDate should be the current date.Hence,cannot insert’ rollback transaction end return end rollback transaction 语句被用于回滚事务,在 trginserthift 触发器上 rollback transaction 语句被用于 被撤消插入操作 2.创建触发器

第 50 页 共 61 页

在未建立触发器的情况下在视图上对表的数据进行修改,一 次修改只能影响一张表,而创建触发器之后就能同时对多个 表上的数据进行修改.如: 修改基于多表的视图 vwSal 上的数据进行修改(未创建触发 器): update vwSal set Title=’www’ where EmployeeID=1 update vwSal set Rate=78 where EmployeeID=1 在视图上创建触发器: create trigger trgupdate_vwSal on vwSal instead of update as begin declare @eid int,@title nvarchar(50),@rate money select @eid=EmployeeID from inserted select @title=Title from inserted select @rate=Rate from inserted update vwSal set Title=@title where EmployeeID=@eid set Rate=@rate where EmployeeID=@eid end 修改数据: update vwSal set Title=’www’,Rate=78 where EmployeeID=1 在 Student,Class 两个表上建立触发器,使得 Student 表每 增加一个人时 Class 表中的人数也相应的增 加,Student(StudentID, StudentName,ClassID),Class(ClassID,ClassName,ClassN umber),ClassNumber 初始值为 0:

第 51 页 共 61 页

create trigger trgInsertStudent on Student for insert as begin declare @classid int set @classid=ClassID from inserted update Class set ClassNumber=ClassNumber+1 where ClassID=@clasid end 在表中插入数据: insert into Student values(1,’Zhang San’,2) select*from Student select*from Class (1)创建删除触发器 创建一个触发器以禁止从 Department 表中删除行: create trigger trgDeleteDepartment on HumanResources.Department for delete as print’Deletion of Department is not allowed’ rollback transaction return 在 Class 表上创建一个删除触发器: create trigger trgDeleteClass on Class for delete as begin declare @cid int select @cid=ClassID from deleted if exists(select*from Student where ClassID=@cid) begin print’delete student’ delete from Student

第 52 页 共 61 页

where ClassID=@cid end end 执行触发器: delete from Class where ClassID=1 (2)创建更新触发器 创建一个触发器以确保 EmployeePayHistory 表中 Rate 增加 时,Rate 列的平均值不应该超过 20: create trigger trgUpdateEmployeePayHistory on HumanReources.EmployeePayHistory for update as if update(Rate) begin declare @avgRate float select @avgRate=avg(Rate) from HumanResources.EmployeePayHistory if(@avgRate>20) begin print’The average value of rate cannot be more than 20’ rollback transaction end end 在 AdventureWorks 数据库的 EmployeeDepartmentHistory 表上创建触发器: create trigger HumanResources.trgDepartment on HumanResources.Department after update as begin update HumanResources.Department set HumanResources.Department.ModifiedDate=getdate() from inserted where inserted.DepartmentID=

第 53 页 共 61 页

HumanResource.Department.DepartmentID end 显示魔表中的数据: create trigger HumanResources.trgmagic on HumanResources EmployeeDepartmentHistory after update as begin select*from deleted select*from inserted end 上述语句在 HumanResources.EmployeeDepartmentHistory 表上创建一个更新触发器,当语句在 HumanResources.EmployeeDepartmentHistory 表上被触发 的时候,trgmagic 触发器被执行并且在表上显示之前的和已 更新的值 (3)创建后触发器 从 Employee 表中删除一个记录之后显示一条消息: create trigger trgDeleteShift on HumanResources.Shift after delete as print’Deletion successful’ 当一个操作设置了多个后触发器,可使用 sp_settriggerorder 系统存储过程改变这些触发器的执行 顺序,语法: sp_settriggerorder <triggername>,<order-value>,<DML-operation> order-value 是指定的触发器的执行顺序,可输入的值有 first, last 和 none(随机) 默认情况下触发器的执行顺序是它创建的顺序 在 Shift 表创建一个后触发器 trgDeleteShift1,但是需要 在执行第一个 trgDeleteShift 触发器之前执行 trgDeleteShift1 触发器: sp_settriggerorder ‘HumanResources.trgDeleteShift1’ First’ Delete’ ,’ ,’

第 54 页 共 61 页

return 为了检查触发器的存在性使用 sp_help <trigger_name> 4.管理触发器 (1)改变触发器 修改 trgInsertShift 触发器,它被用来检查 ModifiedDate 属性是否是当前日期,如果 ModifiedDate 属性不是当前日期, 触发器应该显示一条消息,’已修改日期不是当前日期,事务 不能被处理’: alter trigger HumanResources.trgInsertShift on Humanresources.Shift for inert as declare @modifiedDate datetime select @modifiedDate=ModifiedDate from inserted if(@modifiedDate!=getdate()) begin raiserror(The modified date is not the current date. The transcation cannot be processed.’,10,1) roolback transaction end return (2)删除触发器 删除触发器可使用 drop trigger 语句: drop trigger trgDeleteShift1 (3)重命名触发器 sp_rename ‘trgInsertStudent’ trgInsertStudentnew’ ,’ 此方法将触发器重命名后列名表中的触发器名称不变,但在 执行 SQL 语句时数据库已将触发器重新命名,所以重命名的 最好方法是将原触发器删除重建 AdventureWorks,Inc 已经建立视图 vwEmployee: create view vwEmployee as select e.EmployeeID’EmployeeID’,h.FirstName’ EmployeeName’, g.Name’DepartmentName’,e.HireDate’ Date of joining’, j.AddressLine1’EmployeeAddress’ from HumanResources.Employee e

第 55 页 共 61 页

join HumanResources.EmployeeDepartmentHistory f on e.EmployeeID=f.EmployeeID join HumanResources.Department g on f.DepartmentID=g.DepartmentID join Person.Contact h on e.ContactID=h.ContactID join HumanResources.EmployeeAddress i on e.EmployeeID=i.EmployeeID join Person.Address j on i.AddressID=j.AddressID 在视图上创建一个替代触发器 如果视图是基于多表的,不能使用修改所有基表的数据,为 此需要使用一个替代触发器: create trigger trgEmployee on vwEmployee intead of update as begin update Person.Contact set FirstName=(select EmployeeName from inserted) where ContactID=(select ContactID from Humanresources.Employee where employeeID=(select EmployeeID from inserted)) update HumanResources.EmployeeDepartmentHistory set DepartmentID=(select DepartmentID from HumanResources.DepartmentName from inserted)) where Name=(select EmployeeID from inserted) end 执行语句,验证功能: update vwEmployee set EmployeeName=’ Ron’ ,DepartmentName=’ Sals’ where EmployeeId=51 4.实现事务 (1)事务属性 事务可以被定义为一系列作为一个单一工作逻辑单元一起 完成的操作工作的一个单元必须拥有四个属性,称为 ACID(原子性,一致性,隔离性和持久性) 1>原子性:要么所有数据修改被执行,要么它们一个都不执

第 56 页 共 61 页

行 2>一致性:当事务成功完成之后,所有数据达到一致的状态, 所有关系数据库中规则必须被应用到事务的修改当中以维 护完全的数据完整性 3>隔离性:任何并发事务所做的修改与其它并发事务所做的 修改是隔离的 4>持久性:保证事务对数据产生的改变是永久保持的,因此, 即使在系统故障的情况下,事务对数据的修改也会存在,这 是通过备份概念和恢复事务日志完成的 数据库系统确保每个事务物理完整性是很重要的,为完成 ACID 属性的需求,SQL Sever 提供了以下特征: 1>事务管理:确保所有事务的原子性和一致性,事务必须在 它们启动之后成功完成,或 SQL Sever 撤消自从事务启动之 后的所有的数据修改,事务完成可完成某一功能的操作组合 2>锁:保护事务的持久性和隔离性,锁是自动生成的 SQL Sever 中有两种实现事务的方式: 1>自动提交事务:它是 SQL Sever 的默认管理模式,基于每个 T_SQL 语句的完成,事务自动被提交或回滚,如果成功完成被 提交,如果遇到错误,被回滚 2>显示事务:它是在事务的开始和结束都显示定义的事务, 显示事务在 SQL Sever 以前的版本中被称为用户定义或用户 特定事务,显示事务使用 begin tranaction 和 commit transaction 语句指定当工作在自动提交模式的时候,你可 以使用 begin tranaction 语句来重载默认的自动提交模 式,SQL Sever 返回自动提交或回滚(rollback) 开始一个事务 begin transaction 语句标识事务的开始,语法: begin transaction transaction_name/@tran_name_variable transaction_name 是指定给事务的名称,这个参数必须遵守 标识符规则并且不能超过 32 个字符, @tran_name_variable 是用户定义的变量名称,包含一个有效的事务名称,这个变 量名称必须声名为 char,varchar,nchar 或 nvarchar 数据类 型 (2)回滚事务 transaction_name 语句回滚一个显示或隐式的事务的开始

第 57 页 共 61 页

或到事务内的保存点 transaction_name 语法: transaction_name transaction_name/@tran_name_variable |@savepoint_name| savepoint_name 指定保存点的名称 @savepoint_variable 是用户定义的变量名称,它包含一个 有效的保存点名称,这个变量必须被声明为 char,varchar,nchar 或 nvarchar 数据类型 当更新员工的个人详情的时候,如果查询失败,所有语句被 回滚: begin transaction tr1 begin try update Person.Contact set EmailAddress=’jolyn@yahoo.com’ where ContactID=1070 update HumanResources.EmployeeAddress set AddressID=32533 where EmployeeID=1 commmit transaction tr1 select ‘transaction executed’ end try begin catch rollback transaction tr1 select ‘transaction rollbacked’ end catch (3)锁 SQL Sever 使用锁的概念来确保事务的完整性,在一个多用 户环境中,锁防止用户同时修改同一数据,在 SQL Sever 中锁 被自动实现,但是可以显示的使用锁,锁被应用于确保资源 的当前用户对那个资源有一致的视图,从开始到操作结束 对于一个事务处理数据库,DBMS 解决两个不同过程的潜在冲 突,它们试图同时改变同一个信息,事务并发性是多个事务 同时访问或改变共享数据的能力,事务并发在试图修改数据 的事务阻止其它事务读取数据的时候受到影响 锁的需要

第 58 页 共 61 页

在没有锁的情况下,如果多个事务同时使用数据库中的数据, 就会发生问题: 丢失更新 未提交依赖(脏读) 不一致分解 幻影读 1>丢失更新 丢失更新发生在两个或更多事务试图修改同一行的数据,在 这个情况中,每个事务都没有意识到其它事务,在事务队列 中最后更新的事务覆盖了前面的事务所作的更新,这导致前 面事务完成的数据操作丢失 2>未提交依赖(脏读) 一个未提交依赖也被称为脏读,这个问题发生在事务从表中 查询数据而另外一个事务在修改数据的过程的时候,例:所 有产品详情保存在数据库的产品表中,用户正在执行查询以 更新所有产品的价格,当进行了变更的时候,另一个用户从 同一个表中产生了一个报告,报告也包括所有以前作的改变, 并且将它发布给潜在的读者,更新查询最后提交并且表现在 被更新,在此之中,发布的报告包含不存在的数据并且应该 被看作多余的,为了避免这样的问题,不应该允许任何用户 读取表直到数据库开发人员确定变更 3>不一致分解 不一致分解问题也被称为非重复问题,这个问题出现在数据 在一个用户连续读取之间发生改变的时候,例:银行程序中, 用户产生一个报告来显示所有帐户的结余,用户使用这个结 果集来更新数据,接着,用户再次抽取相同结果集来反映改 变,在两个查询执行期间,另一个用户更新了原始表,当员工 第二次查的时候数据被修改,着导致混乱 4>幻影读 幻影读也被称为幻影问题,这个问题发生在当用户插入的新 记录被事务识别的时候,这个事务开始比插入语句早,例如: 在一个预定程序中,用户 User1 开始一个事务并且查询可用 的座位,查询返回值 X,接着,事务试图保留 X 座位,同时另一 个用户 User2 保留 X-2 座位,当用户 1 的事务试图保留 X 座 位时,可用的座位数不能显示 (4)SQL Sever 中的锁

第 59 页 共 61 页

1>共享锁 共享(s)锁允许并发事务读取(select)一个资源,资源上存 在共享(s)锁时,任何其它事务都不能修改数据,一旦读取数 据便立即释放资源上的共享(s)锁,除非将事务隔离级别设 置为可重复读或更高级别,或者在事务生存周期内使用锁定 提示保留共享(s)锁 2>排他锁 排他锁(x),按照它们的功能排他锁限制并发事务访问资源, 没有其他事务可以读取或修改排他锁锁定的数据 3>更新锁 更新锁(u)在共享和排他锁之间失败,例如:为了更新所有价 格高于$10 的产品,可以在表行运行一个更新语句,为了确定 需要被更新的记录,查询将在表上获得一个共享锁和排他锁 的间隙任何其他事务也可能改变你将要更新的数据,因此需 要更新锁,更新锁被应用到带有共享锁的表,它组织其他事 务更新表直到更新完成 4>意向锁 意向锁(I)按照它的功能,表示 SQL Sever 想在层次结构的低 端的一些资源上需要共享和排他锁,例如:当共享意向锁试 图在表等级实现时,事务将在表的页或行上放置共享锁 在表级实现意向锁确保没有其他事务随后可以在包含那个 页的表上获得排他锁意向锁提高 SQL Sever 的性能,因为 SQL Sever 仅在表级检查意向锁以确定事务是否可以安全的在那 个表上获得,因此你必须检查每行或表上的页锁来确定事务 是否可以锁定整个表 有他们各种特性的意向共享(IS)意向排他(IX)和意向排他 共享(SIX)锁 5>模式锁 当 DDL 操作在表上执行的时候,SQL Sever 认为是模式修改 (Sch-M)锁,当编译查询的时候,SQL Sever 认为是模式稳定 (Sch-S)锁,一个 Sch-S 锁不阻塞其他锁包括排他(X)锁,因此, 其他事务包括那些表上的排他(X)锁可以在查询被编译的时 候运行 6>批量更新锁 批量更新锁(BU)确保你的表对于任何其他标准 T-SQL 语句安 全,但是多个 bulk insert 语句或批量复制程序可同时执行

第 60 页 共 61 页

7>控制锁 锁在 SQL Sever 中自动实现,默认 SQL Sever 锁定查询的每 行 5.解决死锁 *游标 游标:在一个给定的数据集中可以逐行的访问和处理数据库 对象 步骤: 1>定义游标: declare cursor_name cursor for select_statement 2>打开游标: open cursor_name 3>从游标中读取数据(一条 fetch 语句只能显示一条数据查 询,多条数据可用循环,其中@@fetch_status=0 标志着所有 数据已读取) 4>对数据处理 5>关闭游标: close cursor_name 6>释放资源: deallocate cursor_name 建立一个游标显示 EmployeeID,Rate 及 Mark,当 Rate>=25 时为 high,Rate<25 时为 low: decare @mark char(6) declare @eid int declare @rate money declare curPay cursor for select EmployeeID,Rate from HumanResource.EmployeePayHistory open curPay print’ EmployeeID’ +’ Rate’ +’ Mark’ fetch from curPay into @eid,@rate while @@fetch_status=0 begin if(@rate>=25) set @mark=’high’ else set @mark=’low’

第 61 页 共 61 页

if(@rate>=25) print convert(char(5),@eid)+’ˉˉˉˉˉˉˉ’ +convert(char(10), @rate)+’ˉˉˉˉˉˉ’ +convert(char(7),@mark) fetch from curPay int @eid,@rate end close curPay deallocate curPay


相关文章:
SQL Server 2005简要复习.doc
SQL Server 2005简要复习_其它_高等教育_教育专区。服务器 SQL Server 2005 服务器的组成主要包括数据库引擎和数据库两部分。 服务器是 SQL Server 2005 数据库...
sql server2005数据库复习题一(含答案).doc
sql server2005数据库复习题一(含答案)_从业资格考试_资格考试/认证_教育专区。sql server2005数据库复习题一(含答案) 1. ( A )是位于用户与操作系统之间的一...
SQL SERVER2005复习.doc
SQL SERVER2005复习_IT认证_资格考试/认证_教育专区。第一章:SQL SERVER 基础知识 1、数据库(DataBase,DB)是长期存储在计算机内,有组织、可共享的数据集合。数据...
SQL+Server+2005考试复习题--课堂.doc
SQL+Server+2005考试复习题--课堂_从业资格考试_资格考试/认证_教育专区。SQL Server 2005 考试复习题 一 、单项选择 1. SQL Server 2005 采用的身份验证模式有...
SQLSERVER 2005 复习题.doc
SQLSERVER 2005 复习题_工学_高等教育_教育专区。SQL SERVER 2000 复习题 1. 数据库管理经历了哪三个阶段?它们各有什么特点? 2. 数据冗余有什么缺点?数据共享...
SQLsever2005期末复习总结.doc
SQLsever2005期末复习总结_管理学_高等教育_教育专区。总结了SQLsever2005一些基础的知识,较全面。 1、Data 和 Log 文件夹 2、创建脚本文件: 右击选择数据库, ...
SQL Server 2005复习资料 (1)_图文.ppt
SQL Server 2005复习资料 (1)_IT/计算机_专业资料。SQL Server 2005相关学校资料 第2章 数据库系统的数据模型 ?本章导读 建立数据库系统离不开数据模型,本章...
SQL Server2005数据库应用复习题.doc
SQL Server2005数据库应用复习题_简历_求职/职场_实用文档。SQL Server2005数据库应用复习题 1.以下哪个工具主要用于创建、执行和测试 Transact-SQL 语句( a )。 ...
SQL Server 2005复习资料 (5)_图文.ppt
SQL Server 2005复习资料 (5)_工学_高等教育_教育专区。SQL Server 2005复习资料 (5) 第6章 查询管理 ?本章导读 查询管理主要介绍如何从数据库中使用SELECT...
《SQL Server 2005中文版基础教程》测试题(二套)答案.doc
SQL Server 2005 基础教程测试题(二套)一 填空题(每题 2 分,共 24 分) SQL Server 2005 的实例是一套完整的服务程序,实例中的服务程序多达几百个,大致 ...
SQL Server 2005 复习.doc
SQL Server 2005 复习_IT认证_资格考试/认证_教育专区。第一章 小结 SQL Server 2000 是数据库产品, 而 SQL Server 2005 是一款整合数据库和数据分析服务的平 ...
SQL Server 2005复习资料 (3)_图文.ppt
SQL Server 2005复习资料 (3)_工学_高等教育_教育专区。SQL Server 2005复习资料 (3) 第4章 数据库管理 ?本章导读 本章主要介绍了有关数据表的操作,包括表...
2013最新SQL_Server复习题汇总.doc
复习题 一、选择题 1、数据库系统的主要特征是( A、数据的冗余度小 C、数据...使用语句___ 36、SQL Server 2005使用行存储的方式,行中存放指向数据存储位置...
SQL Server2005期末考试复习纲要.doc
SQL Server2005期末考试复习纲要_文学_高等教育_教育专区。一.相关
SQL Server 2005安装及测试步骤.doc
SQL Server 2005安装及测试步骤_IT/计算机_专业资料。安装及测试 及测试步骤 SQL Server 2005 安装及测试步骤注意:在安装 SQL Server 2005 之前最好先安装好 IIS...
SQL Server 2005SQL Server 复习资料.doc
SQL Server 2005SQL Server 复习资料_计算机软件及应用_IT/计算机_专业资料。复习资料 SQL 、、 SQL Server 复习资料 第 1 章 数据库基础知识 重点掌握: 1、...
SQL Server 2005复习指南 上机内容.doc
SQL Server 2005复习指南 上机内容_其它_高等教育_教育专区。SQL Server 2005复习指南 SQL server 2005 数据库及应用上机实验内容实验一:SQL Server 应用基础(第一...
SQLServer单科测试题(带答案).doc
SQL Server 2005 数据库中,有一个 book(图书)表,包含字
第17周 SQL Server 2005 复习_图文.ppt
第17周 SQL Server 2005 复习_理学_高等教育_教育专区。SQL Server 2005 SQL Server数据库 Server数据库 开发与实现 软件工程系SS2024数据库开发与实现 ...
SQL2005(复习).doc
sql2005答案 5页 5财富值 sql2005安装 3页 10财富值 装SQL2005 16页 1财富值 SQL2005安装图解 23页 免费喜欢此文档的还喜欢 SQL Server复习题 15页 免费 (...
更多相关文章: