SQLServer教案第10周-视图、索引和标识列

数据库原理与应用——SQLServer2005教案邹竞授课日期年月日第6周授课形式讲课授课时数4章节名称第10章视图、索引和标识列教学目的与要求①理解视图的概念和特点及其作用②理解视图和基表的关系与差别③掌握创建视图的CreateView语句和撤销视图的DropView语句④掌握在视图中进行数据操作（如查询）的方法⑤理解索引的概念和作用⑥掌握创建所引的CreateIndex语句和撤销视图的DropIndex语句。⑦理解索引对各种DML操作带来的影响，学会合理使用索引。⑧理解标识列的特点。⑨掌握使用IDENTITY关键字在表中创建标识列。教学重点视图教学难点无教学方法和手段讲授法结合课堂实例分析讨论教学过程与组织导入新课我们已经学习了简单的数据查询。前面所讲的数据查询，数据的来源都在一张表中。更多的情况下，需要查询的数据往往分布在多张表，这就需要进行关系代数的连接运算，在连接的结果上进行查询。讲授新课第10章视图、索引和标识列第01节视图10.1.1视图概述视图是个虚表，是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。视图是一个虚表，即视图所对应的数据不进行实际存储。数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。使用视图的优点和作用主要有：（1）视图可以使用户只关心他感兴趣的某些特定数据和他们所负责的特定任务，而那些不需要的或者无用的数据则不在视图中显示。（2）视图大大地简化了用户对数据的操作。（3）视图可以让不同的用户以不同的方式看到不同或者相同的数据集。（4）在某些情况下，由于表中数据量太大，因此在表的设计时常将表进行水平或者垂直分割，但表的结构的变化对应用程序产生不良的影响。而使用视图可以重新组织数据，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据。（5）视图提供了一个简单而有效的安全机制。使用视图时，要注意以下事项：（1）只有在当前数据库中才能创建视图。（2）视图的命名必须遵循标识符命名规则。（3）不能把规则、默认值或触发器与视图相关联。（4）不能在视图上建立任何索引，包括全文索引。10.1.2创建视图创建视图时应该注意以下情况：（1）只能在当前数据库中创建视图，在视图中最多只能引用1024列，视图中记录的数目限制只由其基表中的记录数决定。（2）如果视图引用的基表或者视图被删除，则该视图不能再被使用。数据库原理与应用——SQLServer2005教案邹竞（3）如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。（4）不能在视图上创建索引，不能在规则、默认、触发器的定义中引用视图。（5）当通过视图查询数据时，SQLServer要检查以确保语句中涉及的所有数据库对象存在，每个数据库对象在语句的上下文中有效，而且数据修改语句不能违反数据完整性规则。（6）视图的名称必须遵循标识符的规则，且对每个用户必须是惟一的。此外，该名称不得与该用户拥有的任何表的名称相同。1．通过Transact-SQL语句中的CREATEVIEW语句创建视图使用Transact-SQL语句中的CREATEVIEW创建视图，其语法形式如下：CREATEVIEW[schema_name.]视图名[(column[,...n])][WITHview_attribute[,...n]]ASselect_statement[WITHCHECKOPTION]{ENCRYPTION|SCHEMABINDING|VIEW_METADATA}列名：视图中包含的列，可以有多个列名，最多可引用1024个列。若使用与源表或视图中相同的列名时，则不必给出列名。ENCRYPTION：说明在系统表syscomments中存储CREATEVIEW语句时进行加密。查询语句：用来创建视图的SELECT语句。可在SELECT语句中查询多个表或视图，以表明新创建的视图所参照的表或视图，但对SELECT语句有以下的限制：①定义视图的用户必须对所参照的表或视图有查询权限，即可执行SELECT语句。②不能使用COMPUTE或COMPUTEBY子句。③不能使用ORDERBY子句。④不能使用INTO子句。⑤不能在临时表或表变量上创建视图。WITHCHECKOPTION：指出在视图上所进行的修改都要符合查询语句所指定的限制条件，这样可以确保数据修改后仍可通过视图看到修改的数据。例：创建一个反映学生学号、姓名、课程号、成绩的视图CreateViewS_CAsSelectStudent.SNo,Student.SName,Course.CName,SC.GradeFromStudentJoinSCOnStudent.SNo=SC.SNoJoinCourseOnSC.CNo=Course.CNo例：定义一个反映g001和g002系的学生年龄的视图CreateViewBT_S(学号,姓名,性别,年龄)AsSelectSNo,SName,SSex,DateDiff(year,birth,GetDate())FromStudentWhereDeptIn(‘g001’,’g002’)在视图上建立视图表示新建视图的数据源中有其他视图。作为数据源的视图必须是已经建立好的。例：CreateViewView1(学号,姓名,课程号,成绩)ASSelectBT_S.学号,BT_S.姓名,SC.CNo,SC.GradeFromBT_SJoinSCOnBT_S.学号=SC.SNo2．在SSMS中创建视图略。10.1.3利用视图进行数据查询通过视图进行数据的查询原则上和基本表的查询方法一样，既可以进行简单查询，也可以作带条数据库原理与应用——SQLServer2005教案邹竞件的查询等等。其语法格式如下：SELECT结果列表FROM[所有者.]视图名[WHERE查询条件1][GroupBy分组列[Having查询条件2]][OrderBy排序列[Asc|Desc]]例：SelectSNo,SNameFromS_CWhereCNameLike'%数据库%'10.1.4对视图进行数据更新通过更新视图数据（包括插入、修改和删除）可以修改基本表数据，但并不是所有的视图都可以更新，只有对满足更新条件的视图才能进行更新。1．插入数据使用INSERT可以通过视图向基本表插入数据。当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。即使视图所依赖的基本表只有一个，但是视图没有包含基本表具有主键约束和非空约束的字段，也不能不能向该视图插入数据2．修改数据使用UPDATE语句可以通过视图修改基本表的数据。若一个视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。3．删除数据使用DELETE语句可以通过视图删除基本表的数据。但要注意，对于依赖于多个基本表的视图，不能使用DELETE语句。对视图的更新操作也可通过企业管理器SSMS的界面进行，操作方法与对表数据的插入、修改和删除的界面操作方法基本相同，在此不在举例说明。在数据库开发中，不提倡通过视图更新基本表的数据。10.1.5修改视图的定义1．使用T-SQL语句修改视图定义使用ALTERVIEW语句修改视图，但首先必须拥有使用视图的权限，然后才能使用ALTERVIEW语句，该语句的语法形式如下：ALTERVIEW视图名[(column[,...n])][WITHENCRYPTION]ASselect_statement[WITHCHECKOPTION]例：修改了视图S_C，在该视图中增加了新的字段性别，并且定义一个新的字段名称SSex。AlterViewS_C(SNo,SName,SSex,CName,Grade)AsSelectStudent.SNo,Student.SName,Student.SSex,Course.CName,SC.GradeFromStudentJoinSCOnStudent.SNo=SC.SNoJoinCourseOnSC.CNo=Course.CNo2．在SSMS中修改视图定义在SQLSERVER管理平台中，右击要修改的视图，从弹出的快捷菜单中选择“设计视图”选项，出现视图修改对话框。该对话框与创建视图时的对话框相同，可以按照创建视图的方法修改视图。10.1.6重命名视图数据库原理与应用——SQLServer2005教案邹竞1．使用T-SQL语句重命名视图使用系统存储过程sp_rename来修改视图的名称，该过程的语法形式如下：sp_renameold_name,new_name例：把视图S_C重命名为S_C1。程序清单如下：sp_renameS_C,S_C12．在SSMS中重命名视图在SQLSERVER管理平台中，选择要修改名称的视图，并右击该视图，从弹出的快捷菜单中选择“重命名”选项。或者在视图上再次单击，也可以修改视图的名称。接着该视图的名称变成可输入状态，可以直接输入新的视图名称。10.1.7撤销视图如果确定某些视图不再需要使用，可以将它们删除。1．通过Transact-SQL语句中的DROPVIEW语撤销视图删除视图的SQL语句的格式为：DROPVIEW视图名例：删除表名为S_C的视图DropViewS_C注意：如果某个视图被删除，那么依赖于该视图之上的其他视图或数据库对象也将失效2．在SSMS中撤销视图略。第02节索引10.2.1索引的概念在数据库中，如果想在某个表中快速查找满足条件的记录，可以创建索引。索引可以使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。创建索引可以加快从表或视图中检索数据的速度。索引包含由表或视图中的一列或多列生成的键。当SQLServer进行数据查询时，查询优化器会自动计算现有的几种执行查询方案中，哪种方案的开销最小，速度最快，然后SQLServer就会按照该方案来查询。所以，可以通过高效的有序查找算法（如折半查找等）找到索引项，再根据索引项中记录的物理地址，找到查询结果的存储位置。使用索引可以大大提高系统的性能，其具体表现在：（1）可以大大加快数据检索速度。（2）通过创建唯一索引，可以保证数据记录的唯一性。（3）在使用ORDERBY和GROUPBY子句进行检索数据时，可以显著减少查询中分组和排序的时间。（4）使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。（5）可以加速表与表之间的连接。（6）建立索引的一般原则是：（7）对经常用来检索的字段建立索引。（8）对数据表中的主键建立索引。（9）对数据表中的外键建立索引。（10）对经常用于连接的字段建立索引。10.2.2索引的分类数据库原理与应用——SQLServer2005教案邹竞在SQLServer2005中提供的索引类型主要有以下几类：聚集索引、非聚集索引、唯一索引、包含性列索引、索引视图、全文索引以及XML索引。1．聚集索引和非聚集索引按照存储结构的不同，可以将索引分为两类：聚集索引和非聚集索引。每个表最多可以有1个聚集索引和249个非聚集索引。其中包括为支持表中所定义的PRIMARYKEY和UNIQUE约束而生成的索引。聚集索引对表在物理数据页中的数据按列进行排序，然后再重新存储到磁盘上。一个表只能有一个聚集索引。用聚集索引查找数据很快。聚集索引一般创建在表中经常搜索的列或者按顺序访问的列上。创建聚集索引时应该考虑以下几个因素：（1）每个表只能有一个聚集索引。（2）创建任何非聚集索引之前要首先创建聚集索引。（3）关键值的唯一性使用UNIQUE关键字或者由内部的唯一标识符明确维护。（4）要保证有足够的空间创建聚集索引。使用非聚集索引不会影响数据表中记录的实际存储顺序。非聚集索引中存储了组成非聚集索引的关键字值和行定位器。由于非聚集索引使用索引页存储，因此它比聚集索引需要较少的存储空间，但检索效率比聚集索引低。由于一个表只能建一个聚集索引，当用户需要建立多个索引时，就需要使用非聚集索引了。每个表中最多只能创建249个非聚集索引。在下列情况下，可以考虑使用非聚集索引：（1）含有大量唯一值的字段