项目六+索引和视图的使用（课件）-《网站数据库应用技术-SQL+Server》

项目六

索引和视图的使用学习目标学习完本项目后，将能够：理解索引和视图的概念掌握如何创建索引掌握如何创建并使用视图知识重点视图的应用创建并使用索引知识难点视图的应用什么是索引汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等我们可以根据拼音或偏旁部首，快速查找某个字词在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无需对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。数据库中的索引是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。基础知识：索引什么是索引SQLServer中的数据也是按页存放。索引：是SQLServer编排数据的内部方法。它为SQLServer提供一种方法来编排查询数据。索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。基础知识：索引索引类型唯一索引：唯一索引不允许两行具有相同的索引值主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个基础知识：索引建立索引的原则：（1）定义主键的数据列一定要建立索引。（2）定义有外键的数据列一定要建立索引。（3）对于经常查询的数据列最好建立索引。（4）对于需要在指定范围内的快速或频繁查询的数据列。（5）经常用在WHERE子句中的数据列。（6）经常出现在关键字orderby、groupby、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。基础知识：索引（7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。（8）对于定义为text、image和bit的数据类型的列不要建立索引。（9）对于经常存取的列避免建立索引。（10）限制表上的索引数目。（11）对复合索引，按照字段在查询条件中出现的频度建立索引。基础知识：索引什么是视图视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上视图中并不存放数据，而是存放在视图所引用的原始表（基表）中同一张原始表，根据不同用户的不同需求，可以创建不同的视图基础知识：视图什么是视图视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度将多个物理数据表抽象为一个逻辑数据表基础知识：视图使用视图的优点：简化查询语句。通过视图可以将复杂的查询语句变得很简单。利用视图，用户不必了解数据库及实际表的结构，就可以方便地使用和管理数据。增加可读性。由于在视图中可以只显示有用的字段，并且可以使用字段别名，因此能方便用户浏览查询的结果。在视图中可以使用户只关心自己感兴趣的某些特定数据，而那些不需要的或者无用的数据则不在视图中显示出来。保证数据逻辑独立性。视图对应数据库的外模式。如果应用程序使用视图来存取数据，那么当数据表的结构发生改变时，只需要更改视图定义的查询语句即可，不需要更改程序，方便程序的维护，保证了数据的逻辑独立性增加数据的安全性和保密性。针对不同的用户，可以创建不同的视图，此时的用户只能查看和修改其所能看到的视图中的数据，而真正的数据表中的数据甚至连数据表都是不可见不可访问的，这样可以限制用户浏览和操作的数据内容基础知识：视图学习任务一：创建、修改和删除索引子任务1：使用SQLServerManagementStudio创建索引（1）在对象资源管理器中，展开xjglxt数据库下的xsxxb表，选中“索引”节点，单击鼠标右键，从弹出的快捷菜单中选择“新建索引”下的“非聚集索引（N）…”命令。（2）在弹出的“新建索引”对话框中，可以在“常规”选择页中命名索引名称及选择是否为唯一非聚集索引），如图6.1所示。（3）单击“添加（A）…”按钮，打开“选择列”对话框选择需要创建索引的xm列，如图6.2示。（4）设置好索引的属性后，单击“确定”按钮，索引创建成功。子任务2：使用T-SQL语句在cjb表中创建单列索引在查询编辑器中输入如下代码：USExjglxt/*如果索引已存在,先删除*/IFEXISTS(SELECTnameFROMsysindexesWHEREname='IX_cj')DROPINDEXcjb.IX_cje/*成绩列(cj)创建非聚集索引：填充因子为%30*/CREATENONCLUSTEREDINDEXIX_cjONcjb(cj)WITHFILLFACTOR=30执行后的结果如图所示，创建成功。子任务3：使用T-SQL语句在cjb表中创建组合关键字索引在查询编辑器中输入如下代码：USExjglxt/*创建索引,按kcbh升序排序,kchh相同的再按cj降序排列*/CREATENONCLUSTEREDINDEXIX_kcbh_cjbONcjb(kcbhASC,cjDESC)WITHFILLFACTOR=30/*按照新建的组合键查询成绩表*/SELECT*FROMcjbWITH(INDEX(IX_kcbh_cjb))执行后的结果如图所示，创建成功子任务4：使用DROPINDEX删除cjb表中的IX_cj索引在查询编辑器中输入如下代码：USExjglxtDROPINDEXIX_cjONdbo.cjbGO（3）查看执行结果。学习任务二：创建、修改和删除视图子任务1：使用SQLServerManagementStudio创建视图创建、修改和删除视图的方法有两种：使用SQLServerManagementStudio和T-SQL语句要求：在xjglxt数据库中创建学生成绩视图：包括姓名、学号、课程名称和成绩4列。具体操作步骤如下：（1）启动SQLServerManagementStudio，在“对象资源管理器”中，依次展开“数据库”“xjglxt”和“视图”节点。（2）选择“视图”并单击右键，在弹出的快捷菜单中，选择“新建视图”，如图6.5所示。（3）在弹出的“添加表”对话框中，首先选中xsxxb表、kcxxb表和cjb表，然后单击“添加”按钮，添加创建视图所需要的基本表xsxxb、kcszb、cjb，如图6.6所示，然后单击“关闭”按钮。（4）选择添加到视图的xh、xm、kcbh和cj列，修改列的别名、指定筛选条件和排序方式等，如图6.7所示。如图6.7所示的视图设计器中，包括4个窗格：关系图窗格、条件窗格、SQL窗格和结果窗格。这四个窗格可以通过单击右键，从弹出的快捷菜单中选择“窗格”选项来打开和关闭指定的窗格，如图6.8所示。（5）右键单击创建视图区域，在弹出的快捷菜单中选择“执行SQL（X）”命令选项，或者单击工具栏中的“执行”按钮，可以看到视图对应的结果集，如图6.9所示。（6）单击工具栏中的“保存”按钮，打开“选择名称”对话框，输入新的视图名称，如图6.10所示，输入名称后单击“确定”按钮保存视图。视图创建成功完成。（7）查看结果，在“视图”节点下已存在创建的视图。SQLServer将视图创建结果保存为虚拟表，然后就可以像打开普通表一样使用它。子任务2：使用T-SQL创建视图要求：在xjglxt数据库中创建学生成绩视图：包括姓名、学号、课程名称和成绩4列。USExjglxt/*检测视图是否已存在,如果存在则先删除,视图存放在系统表sysobjects中*/IFEXISTS(SELECT*FROMsysobjectsWHEREname='View_xh_xm_kcmc_cj1')DROPVIEWView_xh_xm_kcmc_cj1GO/*创建视图*/CREATEVIEWView_xh_xm_kcmc_cj1ASSELECTxsxxb.xhAS学号,xsxxb.xmAS姓名,kcxxb.kcmcAS课程名称,cjb.cjAS成绩FROMcjbINNERJOINkcxxbONcjb.kcbh=kcxxb.kcbhINNERJOINxsxxbONcjb.xh=xsxxb.xhGO/*使用视图：视图是一个虚拟表,可以像物理表一样打开*/SELECT*FROMView_xh_xm_kcmc_cj1运行结果如图所示，完成视图View_xh_xm_kcmc_cj1的创建子任务3：修改View_xh_xm_kcmc_cj1视图要求：修改xjglxt数据库中的视图View_xh_xm_kcmc_cj1，使其只包括xh、kcbh和cj这3列。实现方法有两种方式：（1）使用SQLServerManagementStudio修改视图。选中View_xh_xm_kcmc_cj1视图，单击右键，从弹出的快捷菜单中选择“设计（G）”，打开“视图设计”窗口，其修改的操作方法跟创建视图的方法一样。（2）使用T-SQL语句修改视图：T-SQL语句修改视图的语法为：ALTERVIEWview_nameAS<SELECT语句>在查询编辑器中输入如下代码：USExjglxt/*修改视图*/GOALTERVIEWView_xh_xm_kcmc_cj1ASSELECTxsxxb.xhAS学号,kcxxb.kcmcAS课程名称,cjb.cjAS成绩FROMcjbINNERJOINkcxxbONcjb.kcbh=kcxxb.kcbhINNERJOINxsxxbONcjb.xh=xsxxb.xhGO/*使用视图*/SELECT*FROMView_xh_xm_kcmc_cj1查看运行结果，视图View_xh_xm_kcmc_cj1显示学号、课程名称和成绩3列，修改成功子任务4、使用DROPVIEW语句删除视图View_xh_xm_kcmc_cj1在T-SQL语句里使用DROPVIEW命令删除视图。实例：具体操作步骤如下：在查询编辑器中输入如下代码：USExjglxtDROPVIEWView_xh_xm_kcmc_cj1GO查看结果，视图View_xh_xm_kcmc_cj1不存在，完成删除。知识扩展：在查询编辑器中输入如下代码：USExjglxtALTERINDEXIX_kcbh_cjbONcjbDISABLE查看执行结果一、使用ALTERINDEX的DISABLE语句禁用cjb表的IX_kcbh_cjb索引

知识扩展：二、加密视图1．查看“INFORMATION_SCHEMA.VIEWS”视图2．创建加密视图如果不想别人看到视图的内容，可以使用WITHENCRYPTION命令进行加密。实例：创建加密视图View_Class的代码如下USExjglxtGOCREATEVIEWView_ClassWITHENCRYPTIONASSELECTxsxxb.xh,xsxxb.xm,cjb.c