第7章视图索引游标事务课件

网络数据库技术（第网络数据库技术（第2版）版）第第7章章 视图、索引、游标视图、索引、游标及事务及事务 逯燕玲逯燕玲 戴红戴红 李志明李志明 主编主编第第7章章 视图、索引、游标及事务视图、索引、游标及事务 n7.1 视图视图n7.2 索引索引 n7.3 游标游标n7.4 事务的使用事务的使用 27.1 视图视图7.1.1 视图概述视图概述 n视图是从一个表、多个表或视图中导出的表，其结视图是从一个表、多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。构和数据是建立在对表的查询基础上的。n视图不是真实存在的基础表，而是一个虚拟表，视视图不是真实存在的基础表，而是一个虚拟表，视图所对应的数据并不实际地以视图结构存储在数据图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。库中，而是存储在视图所引用的表中。37.1.2 创建视图的方法创建视图的方法1.使用企业管理器向导创建视图使用企业管理器向导创建视图n【例【例7-1】在学籍管理数据库】在学籍管理数据库（students）中创建一个仅包含基础）中创建一个仅包含基础课的视图。课的视图。n启动启动SQL Server企业管理器，在企企业管理器，在企业管理器窗口中展开业管理器窗口中展开Microsoft SQL Servers和和SQL Server组，选择组，选择SQL Server服务器。服务器。n在在“工具工具”菜单中选择菜单中选择“向导向导”菜菜单项，选择工具菜单中的单项，选择工具菜单中的“创建视创建视图向导图向导”，在出现的，在出现的“创建视图向创建视图向导导”界面中单击界面中单击“下一步下一步”。4n输入数据库名输入数据库名“学生学籍数据学生学籍数据库库”，单击，单击“下一步下一步”；输入；输入所选数据库的选择对象对话框所选数据库的选择对象对话框名名“课程基本信息表课程基本信息表”，单击，单击“下一步下一步”。n为对象中的列选择视图所显示为对象中的列选择视图所显示的字段，单击的字段，单击“下一步下一步”。用。用“where课程类型课程类型=基础基础”语句来定义所限制对话框，单语句来定义所限制对话框，单击击“下一步下一步”。n将对话框命名为将对话框命名为“课程基本信课程基本信息表息表_VIEW”，单击，单击“下一步下一步”。再选择。再选择“完成完成”，即可成，即可成功创建视图。功创建视图。57.1.2 创建视图的方法创建视图的方法2.用用T-SQL创建视图创建视图 语法：语法：CREATE VIEW database_name view_name(column,n)WITH,n AS sellect_statement6n【例【例7-2】在学籍管理数据库（】在学籍管理数据库（students）中）中创建一个视图，其中内容为一门课程的成绩创建一个视图，其中内容为一门课程的成绩单，包含学生姓名。单，包含学生姓名。n在在”工具工具”菜单中选择菜单中选择”向导向导”命令命令 CREATE VIEW SC CourseG WITH,n AS sellect SC.CNo FROM SC,Courses WHERE SC.CNo=Courses.CNo AND Courses.Cname =网络数据库网络数据库）；）；7n【例【例7-3】假设在学籍管理数据库（】假设在学籍管理数据库（students）中有三个结）中有三个结构相同的学生基本信息表：构相同的学生基本信息表：S1、S2、S3，分别存放，分别存放“计算计算机科学与技术机科学与技术”、“电子信息科学与技术电子信息科学与技术”和和“电子信息电子信息工程工程”专业的学生基本信息。创建一个视图将这三个表合专业的学生基本信息。创建一个视图将这三个表合并在一起。并在一起。USE students CREATE VIEW ALL_Students AS SELECT *FROM S1 UNION SELECT *FROM S2 UNION SELECT *FROM S387.1.3 视图信息的查看和修改视图信息的查看和修改 1.使用企业管理器使用企业管理器查看和修改视图查看和修改视图 n启动启动SQL Server企业管理器，登录到指定的服务器企业管理器，登录到指定的服务器。n打开要创建视图的数据库文件夹，选中打开要创建视图的数据库文件夹，选中view图标，此时在右图标，此时在右面的窗格中显示当前数据库的所有视图。面的窗格中显示当前数据库的所有视图。n右键单击要查看的视图，在弹出的快捷菜单中选择右键单击要查看的视图，在弹出的快捷菜单中选择“属性属性”，打开，打开View Properties对话框对话框。n在该对话框内可浏览到该视图的正文，也可对该视图进行修在该对话框内可浏览到该视图的正文，也可对该视图进行修改，然后单击改，然后单击“Check Syntax”按钮对语句合法性进行检查，按钮对语句合法性进行检查，若对视图的访问权限进行设置，单击若对视图的访问权限进行设置，单击“Permissions”按钮。按钮。92使用使用T-SQL查看和修改视图查看和修改视图 n【例【例7-4】查看在学籍管理数据库（】查看在学籍管理数据库（students）中创）中创建的视图建的视图DB_S_Grades。SELECT FROM DB_S_Grades10n当当需需要要在在视视图图中中对对数数据据进进行行插插入入、修修改改和和删删除除等等更更新新操操作作时时，可可与与操操作作表表数数据据窗窗口口相相似似，同同样样可可以以对对数数据进行插入、修改和删除等更新操作。据进行插入、修改和删除等更新操作。n还还可可以以使使用用INSERT、UPDATE和和DELETE语语句句对对视视图数据进行更新。图数据进行更新。7.1.4 视图数据的更新视图数据的更新11n在企业管理器窗口中单击在企业管理器窗口中单击“视图视图”选项，打开选项，打开“视视图列表图列表”。在视图列表中找到要删除的视图，右键。在视图列表中找到要删除的视图，右键单击该视图打开其快捷菜单，选择单击该视图打开其快捷菜单，选择“删除删除”。在出。在出现的现的“除去对象除去对象”对话框中，选择对话框中，选择“全部除去全部除去”即即可完成。可完成。n还可以使用还可以使用T-SQL删除视图，基本语法格式为：删除视图，基本语法格式为：DROP VIEW view_name,n 7.1.5 删除视图删除视图 127.2 索引索引 7.2.1 索引的分类索引的分类 n聚簇索引（聚簇索引（Clustered Index）：行的物理存储顺序与）：行的物理存储顺序与索引顺序完全相同，所以每个表只能有一个聚簇索索引顺序完全相同，所以每个表只能有一个聚簇索引，且在表的主键列上自动创建聚簇索引。引，且在表的主键列上自动创建聚簇索引。n非聚簇索引：逻辑顺序不等于物理顺序，仅仅记录非聚簇索引：逻辑顺序不等于物理顺序，仅仅记录指针，通过这些指针可以在表中快速准确的定位数指针，通过这些指针可以在表中快速准确的定位数据。据。n唯一性索引（唯一性索引（Unique Index）：能够保证在创建索引）：能够保证在创建索引的列或多列的组合上不包括重复的数据，聚簇或非的列或多列的组合上不包括重复的数据，聚簇或非聚簇索引都可以是唯一性索引。聚簇索引都可以是唯一性索引。13创建索引前考虑的问题创建索引前考虑的问题n只有表的拥有者才能在表上创建索引只有表的拥有者才能在表上创建索引n每一个表上只能创建一个聚簇索引每一个表上只能创建一个聚簇索引n每一个表上最多能创建每一个表上最多能创建249个非聚簇索引个非聚簇索引n一个索引最多包含一个索引最多包含16个列，最大为个列，最大为900字节字节147.2.2 创建索引创建索引1.使用企业管理器直接创建使用企业管理器直接创建n 用鼠标右键单击要创建索引的表，选择用鼠标右键单击要创建索引的表，选择”管理索引管理索引”菜单项。菜单项。2.使用向导创建索引使用向导创建索引n在在”工具工具”菜单中选择菜单中选择”向导向导”命令命令3.使用使用T-SQL创建索引创建索引 CREATE UNIQUECLUSTEREDNONCLUSTERED INDEX index_name ON table|view (columnASCDESC,n)WITH 参数参数 157.2.3 创建索引视图创建索引视图 n在视图上创建唯一聚集索引可以提高性能，执行该在视图上创建唯一聚集索引可以提高性能，执行该视图结果集在数据库中的存储方式与带聚集索引的视图结果集在数据库中的存储方式与带聚集索引的表的存储方式相同。表的存储方式相同。n在视图上创建索引的另一个好处是：查询优化器开在视图上创建索引的另一个好处是：查询优化器开始在查询中使用视图索引，而不是直接在子句中命始在查询中使用视图索引，而不是直接在子句中命名视图。这样可从索引视图检索数据而无需重新编名视图。这样可从索引视图检索数据而无需重新编码，由此带来的高效率也使现有查询获益。码，由此带来的高效率也使现有查询获益。167.2.4 删除索引删除索引1.使用企业管理器直接删除索引使用企业管理器直接删除索引 在要删除索引的表上单击右键在要删除索引的表上单击右键,选择选择”所有任务所有任务”中的中的”管理索引管理索引”命令。命令。2.用用T-SQL删除索引删除索引 语法：语法：DROP INDEX table.index|view.index,n例如：例如：DROP INDEX Student.DEPT17n用企业管理器查看修改索引用企业管理器查看修改索引n选择要查看的数据库表上单击右键，从快捷菜单中选择选择要查看的数据库表上单击右键，从快捷菜单中选择”所有任务中所有任务中的的“管理索引管理索引”项，出现项，出现“索引管理索引管理”对话框。对话框。n选择要查看或修改的索引，单击选择要查看或修改的索引，单击“编辑编辑”按钮，出现按钮，出现“修改索引修改索引”对话框。对话框。n可修改索引的大部分设置，还可单击可修改索引的大部分设置，还可单击“编辑编辑SQL”按钮，在显示的按钮，在显示的SQL脚本编辑框直接修改其脚本编辑框直接修改其SQL脚本，但不能修改索引名，程序方式脚本，但不能修改索引名，程序方式的索引名修改需要使用系统存储过程的索引名修改需要使用系统存储过程 Sp_rename。n要在企业管理器中修改索引名、改变其所属文件组等其它信息则需要要在企业管理器中修改索引名、改变其所属文件组等其它信息则需要在表的属性对话框中进行，该属性对话框是从在表的属性对话框中进行，该属性对话框是从”设计表结构设计表结构“对话框对话框中调用，而不是直接通过快捷菜单的中调用，而不是直接通过快捷菜单的“属性属性”菜单项调用。菜单项调用。7.2.5 查看与修改索引查看与修改索引187.3 游标游标 n游标是系统为用户在内存中开辟的一个数据缓冲区，用于存游标是系统为用户在内存中开辟的一个数据缓冲区，用于存放放SQL语句的查询结果，每个游标都有一个名字，通过宿主语句的查询结果，每个游标都有一个名字，通过宿主语言的循环使语言的循环使SQL逐一从游标中读取记录，赋给主变量，然逐一从游标中读取记录，赋给主变量，然后由宿主语言作进一步的处理。后由宿主语言作进一步的处理。n游标的操作一般分为如下几个步骤：游标的操作一般分为如下几个步骤：n定义游标定义游标 EXEC SQL DECLARE CURSOR FOR n打开游标打开游标 EXEC SQL OPEN n推进游标推进游标 EXEC SQL FETCH INTO n关闭游标关闭游标 EXEC SQL CLOSE n删除游标删除游标 DEALLOCATE 19【例【例7-5】查询各种职称的教师的名单。】查询各种职称的教师的名单。EXEC SQL BEGIN DECLARE SECTION；char xm8；char zc6；EXEC SQL END DECLARE SECTION；printf（”Enter 职称：职称：”）；）；scanf（“%s”，zc）；）；EXEC SQL DECLARE zc_cur CURSOR FORSELECT Tname，TtitleFROM TEACHERWHERE Ttitle=：zc；EXEC SQL OPEN zc_curWhile(1)EXEC SQL FETCH zc_cur INTO：xm，：，：zc；if（sqlca.sqlcode0）break；EXEC SQL CLOSE zc_cur；207.4 事务的使用事务的使用 7.4.1 事务概述事务概述 n所谓事务，实际上就是对于一个不可分割的操作序所谓事务，实际上就是对于一个不可分割的操作序列，控制它要么全部执行，要么都不执行。在每个列，控制它要么全部执行，要么都不执行。在每个事务结束时，数据库中的数据应该是一致的。事务结束时，数据库中的数据应该是一致的。n设计事务程序时应保证事务具有如下特性：设计事务程序时应保证事务具有如下特性：n原子性（原子性（Atomicity）n一致性（一致性（Consistency）n独立性（独立性（Isolation）n持久性（持久性（Durability）21三种执行事务的模式三种执行事务的模式 n显式事务：每个事务均以显式事务：每个事务均以BEGIN TRANSACTION语语句显式开始，而以句显式开始，而以COMMIT TRAN或或ROLLBACK TRAN等语句显式结束。等语句显式结束。n隐性事务：在隐性事务：在T-SQL脚本中，执行脚本中，执行“SET IMPLICIT_TRANSACTIONS ON”语句后，系统即语句后，系统即进入隐性事务模式。进入隐性事务模式。n自动提交事务：如果一个自动提交事务：如果一个T-SQL语句成功地完成，语句成功地完成，则自动提交该语句；如果遇到错误，则自动回滚该则自动提交该语句；如果遇到错误，则自动回滚该语句。语句。22n开始事务语句开始事务语句 BEGIN TRANSACTIONTRANSACTION_NAMEWITH MARKdescription n提交事务语句提交事务语句COMMIT TRANSACTIONTRANSACTION_NAME n回滚事务语句回滚事务语句 ROLLBACK TRANSACTION transaction_name|savepoint_name n保存事务断点语句保存事务断点语句 SAVE TRANSACTION SAVEPOINT_NAME 7.4.2 事务处理语句事务处理语句 23n【例【例7-6】给教工】给教工“高山高山”增加工资增加工资100元。元。BEGIN TRANSACTION UPDATE 教工登记表教工登记表 SET 基本工资基本工资=基本工资基本工资+100 WHERE 姓名姓名=高山高山 UPDATE 教工工资表教工工资表 SET 基本工资基本工资=基本工资基本工资+100 WHERE 姓名姓名=高山高山 COMMIT24n【例【例7-7】在】在“中高级职称名册中高级职称名册”中修改一条记录，中修改一条记录，并插入一条记录。并插入一条记录。BEGIN TRANSACTION UPDATE 中高级职称名册中高级职称名册 SET 职称职称=副教授副教授 WHERE 姓名姓名=王冠王冠 INSERT 中高级职称名册中高级职称名册 VALUES(SYS010,高山高山,男男,45,1,教授教授,4800,计计算机系算机系)SELECT *FROM 中高级职称名册中高级职称名册 COMMIT25n【例【例7-8】将当前连接设成隐性事务状态，并进行还书。】将当前连接设成隐性事务状态，并进行还书。假如在还书时必须完成修改假如在还书时必须完成修改“租借信息租借信息”表和表和“图书信息图书信息”表两个操作，可编写如下脚本。表两个操作，可编写如下脚本。DECLARE 借书证号借书证号 varchar(5),图书编号图书编号 varchar(6)SET 借书证号借书证号=00006SET 图书编号图书编号=100006SET IMPLICIT_TRANSACTIONS ONUPDATE 租借信息租借信息 SET 还书日期还书日期=getdate()WHERE 图书编号图书编号=图书编号图书编号 AND 借书证号借书证号=借书证号借书证号IF ERROR!=0 AND ROWCOUNT=0BEGIN ROLLBACK TRAN RETURN ENDUPDATE 图书信息图书信息 SET 状态状态=0WHERE 图书编号图书编号=图书编号图书编号IF ERROR!=0 AND ROWCOUNT=0BEGIN ROLLBACK TRAN RETURN ENDCOMMITPRINT 恭喜你还书成功！恭喜你还书成功！SET IMPLICIT_TRANSACTIONS OFF26n【例【例7-9】某一新生来借书，假如要完成办借书证和借书某一新生来借书，假如要完成办借书证和借书两个操作。办借书证操作可理解为向两个操作。办借书证操作可理解为向“学生信息学生信息”表中插表中插入一条办证记录；借书操作为了说明方便简化为只向入一条办证记录；借书操作为了说明方便简化为只向“租租借信息借信息”表中插入一条借书记录。编写脚本如下表中插入一条借书记录。编写脚本如下。DECLARE 学号学号 varchar(6),姓名姓名 varchar(10),班级班级 varchar(10)DECLARE 借书证号借书证号 char(5),图书编号图书编号 varchar(6),借阅借阅号号 intSET 图书编号图书编号=100002SELECT 学号学号=20030104,姓名姓名=李小军李小军,班级班级=2003-01BEGIN TRANSELECT 借书证号借书证号=借书证号借书证号 FROM 学生信息学生信息 ORDER BY 借书证号借书证号SET 借书证号借书证号=convert(varchar,convert(bigint,借书证号借书证号)+1)INSERT 学生信息学生信息(借书证号借书证号,学号学号,姓名姓名,班级班级)VALUES(借书证号借书证号,学号学号,姓名姓名,班级班级)IF error!=0BEGIN ROLLBACK TRAN PRINT 办证失败，不能借书办证失败，不能借书 RETURNENDPRINT 办证成功，借书证号是：办证成功，借书证号是：+借书证号借书证号SAVE TRAN AAIF NOT EXISTS(SELECT*FROM 图书信息图书信息 WHERE 图书编图书编号号=图书编号图书编号 AND 状态状态=0)PRINT 该书已经借出，借书失败该书已经借出，借书失败 ELSEBEGIN SELECT 借阅号借阅号=借阅号借阅号 FROM 租借信息租借信息 ORDER BY 借借阅号阅号 INSERT 租借信息租借信息(借阅号借阅号,借书证号借书证号,图书编号图书编号,借书日期借书日期)VALUES(借阅号借阅号+1,借书证号借书证号,图书编号图书编号,getdate()IF error!=0 BEGIN ROLLBACK TRAN aa PRINT 借书失败借书失败 END ELSE PRINT 恭喜您借书成功恭喜您借书成功ENDCOMMIT TRAN277.4.3 分布式事务分布式事务 n如果要在事务中存取多个数据库服务器中的数据，如果要在事务中存取多个数据库服务器中的数据，那么就必须使用那么就必须使用“分布式事务分布式事务”，将工作分散到多，将工作分散到多个服务器中进行。个服务器中进行。n执行分布式事务很简单，只要将执行分布式事务很简单，只要将 BEGIN TRANSACTION 换成换成BEGIN DISTRIBUTED TRANSACTION即可，即可，其他部分都不必改动。其他部分都不必改动。n【例【例7-10】假如向】假如向“服务器服务器1.数据库数据库1.表表1.”插入记录操作和插入记录操作和向向“服务器服务器2.数据库数据库2.所有者名所有者名.表表2.”中插入记录操作构成一中插入记录操作构成一个事务，那么这个事务就是分布式事务。个事务，那么这个事务就是分布式事务。28n【例【例7-10】假如向】假如向“服务器服务器1.数据库数据库1.表表1.”插入记录操插入记录操作和向作和向“服务器服务器2.数据库数据库2.所有者名所有者名.表表2.”中插入记录操中插入记录操作构成一个事务，那么这个事务就是分布式事务。主要作构成一个事务，那么这个事务就是分布式事务。主要脚本如下：脚本如下：BEGIN DISTRIBUTED TRANINSERT 服务器服务器1.数据库数据库1.表表1(a1,a2)VALUES(a1,a2)IF error!=0BEGIN ROLLBACK TRAN RETURNENDINSERT 服务器服务器2.数据库数据库2.所有者名所有者名.表表2(b1,b2)VALUES(b1,b2)IF error!=0BEGIN ROLLBACK TRAN RETURNENDCOMMIT TRAN2930写在最后写在最后成功的基础在于好的学习习惯成功的基础在于好的学习习惯The foundation of success lies in good habits谢谢大家荣幸这一路，与你同行ItS An Honor To Walk With You All The Way讲师：XXXXXX XX年XX月XX日