第6章视图和索引

1第6章视图和索引2020/2/22第6章视图和索引学习目标1、熟练掌握视图的创建、修改、删除2、灵活运用视图简化查询操作3、使用视图实现数据库的安全管理4、了解索引的作用、使用索引来提高检索的效率5、熟练掌握索引的创建、删除6、分析、规划和维护索引2第6章视图和索引2020/2/22第6章视图和索引目录小结视图的基本概念6.1视图的创建和查询6.2视图的维护6.3通过视图修改表数据6.4创建索引6.6管理和维护索引6.7索引概述6.53第6章视图和索引2020/2/226.1视图的基本概念6.1.1视图的基本概念视图是一种在一个或多个表上观察数据的途径，可以把视图看做是一个能把焦点定在用户感兴趣的数据上的监视器。视图是一种数据库对象，视图是从一个或者多个表或视图中导出的虚拟表，其结构和数据是建立在对表的查询基础上的。上一页返回目录下一页4第6章视图和索引2020/2/226.1视图的基本概念和真实的表一样，视图也包括多个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据行来源于其所引用的基表。视图中的数据是通过视图定义语句由其基本表中动态查询得来的。因此，视图不是真实存在的基础表而是一个虚拟表。视图被定义后便存储在数据库中。上一页下一页返回目录5第6章视图和索引2020/2/226.1视图的基本概念在视图的实现上就是由SELECT语句构成的，基于选择查询的虚拟表。其内容是通过选择查询来定义的，数据的形式和表一样由行和列组成，而且可以像表一样作为SELECT语句的数据源。[例1]在视图设计器下建立货品信息的视图。（多表查询：货品信息表和供应商信息表）上一页下一页返回目录6第6章视图和索引2020/2/226.1视图的基本概念6.1.2视图的优点和缺点（1）隐蔽数据库的复杂性。（2）为用户集中提取数据。（3）简化数据库用户权限的管理。（4）方便数据的交换。视图的缺点主要表现在对数据修改的限制上。上一页下一页返回目录7第6章视图和索引2020/2/226.2视图的创建和查询注意事项：①只能在当前数据库中创建视图。②如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。③如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须给列定义名称。上一页下一页返回目录8第6章视图和索引2020/2/226.2视图的创建和查询创建视图的方法：一、使用SSMS创建视图二、使用Transact-SQL语句创建视图格式：CREATEVIEW〈视图名〉[WITHENCRYPTION]——对视图进行加密AS〈SELECT语句〉[WITHCHECKOPTION]——检查上一页下一页返回目录9第6章视图和索引2020/2/226.2视图的创建和查询[例2]在查询设计器下建立“客户订购视图”，该视图中包含所有订购货品的客户及他们订购货品的名称和供应商。上一页返回目录下一页CREATEVIEW客户订购视图ASSELECTD.编号,D.姓名,B.名称AS货品名称,A.名称AS供应商FROM供应商信息AINNERJOIN货品信息BONA.编码=B.供应商编码INNERJOIN订单信息CONB.编码=C.货品编码INNERJOIN客户信息DONC.客户编号=D.编号10第6章视图和索引2020/2/226.2视图的创建和查询[例3]在SSMS中查看或修改视图右击视图——“修改”[例4]在“客户订购视图”中查询赵英的订货情况。上一页下一页SELECT*FROM客户订购视图WHERE姓名='赵英'返回目录11第6章视图和索引2020/2/226.2视图的创建和查询练习1：创建一个视图。该视图能显示每张订单的客户名、货物名称、订货数量、订货日期、销售人员名单、所属部门、供应商名称以及联系人。上一页返回目录下一页CREATEVIEW订单视图ASSELECTa.订单号,e.姓名AS客户姓名,a.数量AS订货数量,a.订货日期,b.名称AS货物名称,c.姓名AS销售人员姓名,d.名称AS部门名称,f.名称AS供应商名称FROM订单信息a,货品信息b,销售人员c,部门信息d,客户信息e,供应商信息fWHEREa.销售工号=c.工号ANDa.货品编码=b.编码ANDa.客户编号=e.编号ANDb.供应商编码=f.编码ANDc.部门编号=d.编号12第6章视图和索引2020/2/226.3视图的维护6.3.1查看视图的定义信息1、使用SSMS查看视图信息2、使用系统存储过程查看视图信息sp_help数据库对象名称sp_helptext视图（触发器、存储过程）[例5]查看货品视图信息。EXECSP_helptext货品视图上一页下一页返回目录13第6章视图和索引2020/2/226.3视图的维护6.3.2查看视图与其他对象的依赖关系1、使用SSMS查看视图信息2、使用系统存储过程查看视图信息sp_depends数据库对象名称[例6]查看客户订购视图与其他对象的依赖关系。EXECSP_depends客户订购视图上一页下一页返回目录14第6章视图和索引2020/2/226.3视图的维护6.3.3修改视图格式：上一页下一页ALTERVIEW视图名[WITHENCRYPTION]ASSELECT语句[WITHCHECKOPTION]返回目录15第6章视图和索引2020/2/226.3视图的维护6.3.3修改视图[例7]建立客户订购视图3，然后用命令修改，使其包含订货数量，并要求加密。上一页下一页--先建立视图CREATEVIEW客户订购视图3ASSELECTA.编号,A.姓名,C.货品名称,C.供应商FROM客户信息AINNERJOIN订单信息BONA.编号=B.客户编号INNERJOIN货品视图CONB.货品编码=C.编码GO返回目录16第6章视图和索引2020/2/226.3视图的维护上一页下一页--修改视图ALTERVIEW客户订购视图3WITHENCRYPTIONASSELECTA.编号,A.姓名,C.货品名称,B.数量,C.供应商FROM客户信息AINNERJOIN订单信息BONA.编号=B.客户编号INNERJOIN货品视图CONB.货品编码=C.编码GO--使用该视图SELECT*FROM客户订购视图3--查看该视图，由于已经加密则不能看到定义信息EXECsp_helptext客户订购视图3返回目录17第6章视图和索引2020/2/226.3视图的维护6.3.4删除视图•使用SSMS删除视图•使用Transact-SQL语句删除视图格式：DROPVIEW〈视图名〉[例8]删除客户订购视图DROPVIEW客户订购视图上一页下一页返回目录18第6章视图和索引2020/2/226.3视图的维护6.3.5重命名视图1、使用SSMS重命名视图2、使用系统存储过程重命名视图sp_rename旧名,新名[例9]将视图“客户订购视图3”重新命名为客户订购视图。EXECSP_rename客户订购视图3，客户订购视图上一页下一页返回目录19第6章视图和索引2020/2/226.4通过视图修改表数据对视图进行的修改操作有以下限制：（1）若视图的字段来自表达式或常量，则不允许对该视图执行INSERT和UPDATE操作，但允许执行DELETE操作。（2）若视图的字段来自集合函数，则此视图不允许修改操作。（3）若视图定义中含有GROUPBY子句，则此视图不允许修改操作上一页下一页返回目录20第6章视图和索引2020/2/226.4通过视图修改表数据（4）若视图定义中含有DISTINCT关键字，则此视图不允许修改操作。（5）若视图的定义不允许被修改，则视图也不允许修改操作。[例10]对视图“客户订购视图2”进行修改操作，修改编号5的客户姓名为“欣明”。上一页下一页UPDATE客户订购视图2SET姓名='欣明'WHERE编号=5GO返回目录21第6章视图和索引2020/2/22作业1、创建一个名为”赵英订货”的视图。要求该视图能显示客户“赵英”所订货品的名称、数量、订货日期。2、显示视图“赵英订货”的信息。3、修改视图“赵英订货”，要求对其加密。4、删除视图“赵英订货”。上一页下一页返回目录22第6章视图和索引2020/2/226.5索引概述索引的概念：索引与书中的目录类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书的目录是一个章节列表，其中注明了每一章节的页码。而索引则是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。上一页下一页返回目录23第6章视图和索引2020/2/226.5.1SQLServer2005中数据的存储与访问1．数据的存储在SQLServer2005中，数据存储的基本单位是页，页的大小是8KB。每页的开始部分是96个字节的页首，用于存储系统信息，如页的类型、页的可用容量、拥有页的对象ID等。上一页下一页返回目录24第6章视图和索引2020/2/226.5.1SQLServer2005中数据的存储与访问2．数据的访问SQLServer2005提供了两种数据访问的方法：表扫描法索引法上一页下一页返回目录25第6章视图和索引2020/2/226.5.1SQLServer2005中数据的存储与访问（1）表扫描法在没有建立索引的表内进行数据访问时，SQLServer2005通过表扫描法来获取所需要的数据。当SQLServer2005执行表扫描时，它从表的第一行开始进行逐行查找，直到找到符合查询条件的行。（2）索引法在建有索引的表内进行数据访问时，SQLServer2005通过使用索引来获取所需要的数据。上一页下一页返回目录26第6章视图和索引2020/2/226.5.1SQLServer2005中数据的存储与访问当SQLServer2005使用索引时，它会通过遍历索引树来查找所需行的存储位置，并通过查找的结果提取所需的行。通常由于索引加速了对表中数据行的检索，所以使用索引可以加快SQLServer2005访问数据的速度，减少数据访问时间。上一页下一页返回目录27第6章视图和索引2020/2/226.5.2索引的作用创建索引的好处主要有以下两点：（1）加快数据查询。（2）加快表的连接、排序和分组工作。创建索引也有它的不足：（1）创建索引需要占用数据空间和时间。（2）建立索引会减慢数据修改的速度。上一页下一页返回目录28第6章视图和索引2020/2/226.5.3索引的分类按照索引值的特点分类，可以将索引分为唯一索引和非唯一索引；按照索引结构的特点分类，可以将索引分为聚集索引和非聚集索引。1．唯一索引和非唯一索引唯一索引要求所有数据行中任意两行中的被索引列或索引列组合不能存在重复值，包括不能有两个空值NULL；而非唯一索引则不存在这样的限制。上一页下一页返回目录29第6章视图和索引2020/2/226.5.3索引的分类2．聚集索引和非聚集索引聚集索引对表中的数据按列进行物理排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的，它存储的是实际的数据。一个表中只能有一个聚集索引。当建立主键约束时，如果表中没有聚集索引，SQLServer2005会用主键列作为聚集索引键。上一页下一页返回目录30第6章视图和索引2020/2/226.5.3索引的分类2．聚集索引和非聚集索引非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将表中的数据进行物理排序。非聚集索引存储了组成非聚集索引的关键字值和行定位器。一个表中最多可建立249个非聚集索引上一页下一页返回目录31第6章视图和索引2020/2/226.6创建索引SQLServer2005创建索引的方法：（1）在创建表的约束时由系统自动创建（2）利用SSMS创建索引（3）利用T-SQL语句中的CREATEINDEX命令创建索引上一页下一页返回目录32第6章视图和索引2020/2/226.6.1系统自动创建索引在创建或修改表时，如果添加了一个主键或唯一键约束，则系统