网络数据库 课件 项目5、6 索引和视图、程序设计

项目五

索引和视图任务1索引任务2视图任务1索引任务引入小林在创建大型数据库时发现信息比较混乱，要是能像书的目录一样通过搜索索引找到特定的值就好了。那么，怎么在数据库中创建索引？怎么查看和删除索引？知识准备可以利用索引快速访问数据库表中的特定信息。索引是对数据库表中一个或多个列的值进行排序的结构。索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。在数据库关系图中，可以为选定的表创建、编辑或删除索引/键属性页中的每个索引类型。当保存附加在此索引上的表或包含此表的数据库关系图时，索引同时被保存。一、创建索引SQLServer提供了两种方法来创建索引：直接创建索引：

间接创建索引：在创建索引时，需要指定索引的特征。这些特征包括下面几项：聚集还是非聚集。惟一还是不惟一。单列还是多列。索引中的列顺序为升序还是降序。覆盖还是非覆盖。1．使用SSMS创建索引选择“管理索引”命令“从dbo.student中选择列”对话框2．使用SQL语言创建索引只有表或视图的所有者才能为表创建索引。

设置参数

新建索引案例——在score表中的course_ID列上创建一个非聚集索引。二、查看和删除索引查看和删除索引均有两种方法：使用图形化界面和SQL语言。1．使用SSMS查看和删除索引创建IDX_ID索引“索引属性”对话框2．使用SQL语言查看和删除索引（1）显示索引要查看索引信息，可使用存储过程sp_helpindex。案例——显示student表上的索引信息。（2）删除索引删除索引使用DROPINDEX语句，其语法格式如下：DROPINDEX'table.index|view.index'[,…n]其中，“table”和“view”是索引列所在的表或索引视图；“index”是要除去的索引名称。索引名必须符合标识符的规则。“n”表示可以指定多个索引的占位符。显示索引任务2视图任务引入小林想将数据库中的几个表导出为视图，然后通过视图检索数据和修改数据。那么，怎么在数据库中创建视图？怎么在视图中进行数据检索？怎么通过视图修改数据？知识准备视图是从一个或者多个表中使用SELECT语句导出的。那些用来导出视图的表称为基表。视图也可以从一个或者多个其他视图中产生。导出视图的SELECT语句存放在数据库中，而与视图定义相关的数据并没有在数据库中另外保存一份，因此，视图也称为虚表。视图的行为和表类似，可以通过视图查询表的数据，也可以修改表的数据。对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。所以说，视图是一种SQL查询。在数据库中，存储的是视图的定义，而不是视图查询的数据。通过这个定义，对视图查询最终转换为对基表的查询。一、创建视图要使用视图，首先必须创建视图。视图在数据库中是作为一个独立的对象进行存储的。创建视图要考虑如下的原则：

只能在当前数据库中创建视图。视图名称必须遵循标识符的规则，且对每个用户必须惟一。此外，该名称不得与该用户拥有的任何表的名称相同。可以在其他视图和引用视图的过程之上建立视图。定义视图的查询不可以包含ORDERBY、COMPUTE或COMPUTEBY子句以及INTO关键字。不能在视图上定义全文索引。不能创建临时视图，也不能在临时表上创建视图。不能对视图执行全文查询，但是如果查询所引用的表被配置为支持全文索引，就可以在视图定义中包含全文查询。1．使用SSMS创建视图视图保存在数据库中而查询不是，因此创建新视图的过程与创建查询的过程不同。通过SSMS不但可以创建数据库和表，也可以创建视图。快捷菜单

“添加表”对话框添加表到视图选择需要的字段2．使用SQL语句创建视图案例——创建student_specialty视图，其中包括了学生姓名、专业和学院视图中可以使用的列最多可达1024列。另外，在创建视图时，视图的名称存储在sysobjects表中。有关视图中所定义的列的信息添加到syscolumns表中，而有关视图相关性的信息添加到sysdepends表中。另外，CREATEVIEW语句的文本添加到syscomments表中。输入视图名称

创建student_grade视图

创建student_specialty视图二、使用视图通过视图可以检索基表中的数据，也可以通过视图来修改基表中的数据，例如插入、删除和修改记录。1．使用视图进行数据检索视图是基于基表生成的，因此可以用来将需要的数据集中在一起，而不需要的数据则不需要显示。使用视图来检索数据，可以像对表一样来对视图进行操作。案例——使用创建的student_grade视图来查询成绩大于90分的学生案例——使用SSMS查看视图的数据查询成绩大于90分的学生通过视图检索数据2．通过视图修改数据通过视图修改其中的某些行时，SQLServer将把它转换为对基表的某些行的操作。对于简单的视图来说，可能比较容易实现，但是对于比较复杂的视图，可能就不能通过视图进行修改。案例——新建视图specialty1并添加数据案例——更新数据案例——新建视图，删除学号为16053113的数据插入数据后的specialty表

更新后的college表

通过视图删除数据三、删除视图在创建视图后，如果不再需要该视图，或想清除视图定义及与之相关联的权限，可以删除该视图。删除视图后，表和视图所基于的数据并不受影响。任何使用基于已删除视图的对象的查询将会失败，除非创建了同样名称的一个视图。在删除视图时，定义在系统表sysobjects、syscolumns、syscomments、sysdepends和sysprotects中的视图信息也会被删除，而且视图的所有权限也一并被删除。1．使用SSMS删除视图（1）在对象资源管理器中，展开“数据库”文节点，展开该视图所属的数据库，然后单击“视图”节点。（2）这里选择“school”数据库“视图”节点中的st2_degree，右击鼠标，在弹出的快捷菜单中选择“删除”选项，打开“删除对象”对话框，其中的“显示依赖关系”按钮用于查看删除此视图对数据库的影响。（3）单击“确定”按钮即可删除视图；单击“取消”按钮取消删除操作。2．使用SQL删除视图使用DROPVIEW语句可从当前数据库中删除一个或多个视图。项目总结项目实战实战一为emp表的“员工号”和“工作名称”列创建聚集索引，并且强制惟一性（1）因为这里要创建强制唯一性的索引，必须先删除emp中已经存在的索引。（2）在对象资源管理器中选取“factory”数据库，单击工具栏中的“新建查询”按钮创建IDX_no索引实战二创建View1视图，查询所有员工的姓名、员工号、所在部门以及工资

查询所有员工的所在部门以及工资项目六

程序设计任务1程序设计基础任务2事务处理任务3使用游标任务1程序设计基础任务引入小林不仅想查询数据，还想对表中的数据进行运算和控制。那么，怎么利用SQL语言进行编程满足要求呢？编写的程序中会用到什么变量以及流程控制语句呢？知识准备一、变量在SQLServer中，变量分为局部变量和全局变量。全局变量名称前面有两个at符号（＠＠），由系统定义和维护。局部变量前面有一个at符号（＠），由用户定义和使用。1．局部变最局部变量由用户定义，仅在声明它的批处理、存储过程或者触发器中有效。批处理结束后，局部变量将变成无效。各参数含义如下：@local_variable：是变量的名称。data_type：是任何由系统提供的或用户定义的数据类型。案例——查询并输出学院号为03的院长姓名案例——查询score表中选修课程号为“01054010”且成绩高于80的记录

查询03号学院的院长名称查询成绩高于80的记录2．全局变量全局变量记录了SQLServer的各种状态信息，它们不能被显式地赋值或声明，而且不能由用户定义。在SQLServer中，定义了全局变量，如表所示。二、流程控制语句SQL提供称为控制流的特殊关键字，用于控制SQL语句、语句块和存储过程的执行流。这些关键字可用于SQL语句、批处理和存储过程中。控制流语句就是用来控制程序执行流程的语句，使用控制流语句可以在程序中组织语句的执行流程，提高编程语言的处理能力。SQLServer提供的控制流语句如表所示。1．BEGIN…END语句BEGIN…END语句用于将多个SQL语句组合为一个逻辑块。在执行时，该逻辑块作为一个整体被执行。案例——设置变量值设置变量值下面几种情况经常要用到BEGIN和END语句：WHILE循环需要包含语句块。CASE函数的元素需要包含语句块。IF或ELSE子句需要包含语句块。2．IF…ELSE语句使用IF…ELSE语句，可以有条件地执行语句。案例——查询大学语文课程的成绩状况

查询成绩3．CASE语句使用CASE语句可以进行多个分支的选择。CASE具有两种格式：简单CASE格式将某个表达式与一组简单表达式进行比较以确定结果。搜索CASE格式计算一组布尔表达式以确定结果。（1）简单CASE格式案例——查询课程的课程类型（2）搜索CASE格式案例——查询学生成绩查询老师的单位和职称查询学生成绩4．WHILE语句WHILE语句可以设置重复执行SQL语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用BREAK和CONTINUE关键字在循环内部控制WHILE循环中语句的执行。案例——在查询到成绩表中没有学生成绩超过95分的情况下，将所有成绩提高5分，反复执行，直到成绩超过95分。5．GOTO语句GOTO语句可以实现无条件的跳转。高学生成绩案例——输出18032142学号学生的平均成绩6．RETURN语句使用RETURN语句，可以从查询或过程中无条件退出。可在任何时候用于从过程、批处理或语句块中退出，而不执行位于RETURN之后的语句。案例——查询18032142学号学生的成绩和平均分7．WAITFOR语句使用WAITFOR语句，可以在指定的时间或者过了一定时间后，执行语句块、存储过程或者事务。输出18032142学号学生的平均成绩输出18032142学号学生的成绩和平均分任务2事务处理任务引入小林需要对之前做的数据库进行大量的修改，但是他又担心修改后不对还得一点一点恢复，他咨询老师，怎么处理这个问题，老师告诉他可以采用事物处理来解决这一问题。那么，什么是事物，事物又分为哪些类别呢？显示事务有哪些步骤呢？知识准备事务是SQLServer中的单个逻辑单元，一个事务内的所有SQL语句作为一个整体执行，要么全部执行，要么都不执行。一个逻辑工作单元必须有4个属性，称为ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务。原子性（Atomicity）：事务必须是原子工作单元。一致性（Consistency）：事务在完成时，必须使所有的数据都保持一致状态。隔离性（Isolation）：由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。持久性（Durability）：事务完成之后，它对于系统的影响是永久性的。一、事务分类按事务的启动和执行方式，可以将事务分为3类：显式事务：也称为用户定义或用户指定的事务，即可以显式地定义启动和结束的事务。自动提交事务：自动提交模式是SQLServer的默认事务管理模式。

隐性事务：当连接以隐性事务模式进行操作时，SQLServer将在提交或回滚当前事务后自动启动新事务。二、显式事务显式事务需要显式地定义事务的启动和结束。它是通过BEGINTRANSACTION、COMMITTRANSACTION、COMMITWORK、ROLLBACKTRANSACTION或ROLLBACKWORK等SQL语句来完成的。1．启动事务启动事务使用BEGINTRANSACTION语句，该语句将@@TRANCOUNT加1。2．结束事务如果没有遇到错误，可使用COMMITTRANSACTION语句成功地结束事务。3．回滚事务如果事务中出现错误，或者用户决定取消事务，可回滚该事务。案例——启动一个事务向college表中插入一个记录，然后回滚该事务4．在事务内设置保存点设置保存点使用SAVETRANSACTION语句案例——在事务内设置保存点查询表

查询表5．标记事务WITHMARK选项使事务名置于事务日志中。将数据库还原到早期状态时，可使用标记事务替代日期和时间。6．不能用于事务的操作在事务处理中，并不是所有的SQL语句都可以取消执行，一些不能撤消的操作（如创建、删除和修改数据库的操作），即使SQLServer取消了事务执行或者对事务进行了回滚，这些操作对数据库造成的影响也是不能恢复的。因此，这些操作不能用于事务处理。这些操作如表所示。7．自动提交事务SQLServer使用BEGINTRANSACTION语句启动显式事务，或隐性事务模式设置为打开之前，将以自动提交模式进行操作。不能用于事务的操作三、隐式事务在为连接将隐性事务模式设置为打开之后，当SQLServer首次执行某些SQL语句时，都会自动启动一个事务，而不需要使用BEGINTRANSACTION语句。任务3使用游标任务引入小刘老师让小林帮忙从学校的数据库中查询他们专业前十名的学生信息并打印出来，可是小林查询出来是所有学生信息按名次排名，这时小林想到用游标来提取数据。那么，怎么使用游标呢？知识准备关系数据库中的操作会对整个行集产生影响。由SELECT语句返回的行集包括所有满足该语句WHERE子句中条件的行。由语句所返回的这一完整的行集称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制，以便每次处理一行或一部分行。游标就是用来提供这种机制的结果集扩展。游标包括以下两个部分：游标结果集（CursorResultSet）：由定义该游标的SELECT语句返回的行的集合