资源简介

(共47张PPT)
6
视图与索引
第
章
本章导读
为了应对数据库中频繁的查询操作，SQL Server提供了视图和索引来提升查询的效率。视图是查询语句的结果集，它隐藏了底层的表结构，既简化了数据操作语句又保证了数据库的安全性；索引可以将数据的逻辑排序存储在物理空间中，在数据量巨大的情况下缩减查询时间。
本章将介绍视图与索引的基本概念，以及使用SSMS与T-SQL创建、删除及修改视图或索引的方法。
知识目标
理解视图的基本概念。
理解索引的基本概念，熟悉索引的类型。
能力目标
能够使用SSMS创建、修改、删除及更新视图。
能够使用T-SQL创建、修改、删除、查询及更新视图。
能够使用SSMS创建、删除、查看及修改索引。
能够使用T-SQL创建、删除、查看及修改索引。
素质目标
注重学思结合，知行合一，培养勇于探究的精神。
提高逻辑思维能力，树立追求卓越的奋斗意识。
项目导航
视图
1
索引
2
01
视图
6.1.1视图的基本概念——视图1视图是一种数据库对象，它是由数据表或视图上的查询所定义的虚拟表。简单来说，视图就是一条SELECT语句所对应的结果集，结果集的名字就是视图名。视图在被定义之后便存储在数据库中，但相应的数据并不会再存储一遍，通过视图看到的数据是所引用的数据表中的数据。所以，当数据表中的数据更新时，视图中的数据也会随之更新。同样，视图中的数据更新时，数据表中的数据也会随之更新。——视图的优点与作用2（1）简单化。视图可以只提取出用户需要的数据，即“看到的就是需要的”，便于管理和使用。经常使用的查询可以创建成视图，使用户之后使用时只需查询单个视图，就能够获取需要的信息，不需要在每次使用时都指定全部的查询条件。（2）安全性。数据库管理系统能够为不同的用户定制不同的视图，使他们只能查询和读写部分数据，未授权的数据既看不到也取不到，在一定程度上提供了对数据的安全保护机制。视图的基本操作（3）逻辑数据独立性。视图为数据提供了一定的逻辑独立性。视图对应三级模式中的外模式，当数据库的逻辑结构发生变化时，只需要修改视图的定义，就可以保证用户的外模式不变，对应的应用程序也就不必修改了。6.1.1视图的基本概念6.1.2创建、修改与删除视图——1．创建视图（1）使用SSMS创建视图。【例 6-1】使用SSMS创建一个学生平均成绩的视图v_savg，其中包括学号和平均成绩。步骤1 打开SSMS并登录。在“对象资源管理器”窗口中打开“scs”节点，右击其下方的“视图”节点，在弹出的快捷菜单中选择“新建视图”菜单项，打开“添加表”对话框（同时底层会打开未命名的视图设计窗口），如图6-1所示。图6-1“添加表”对话框6.1.2创建、修改与删除视图——1．创建视图步骤2 在“表”选项卡中选中“sgrade”，单击“添加”按钮，然后单击“关闭”按钮，返回视图设计窗口，如图6-2所示。图6-2视图设计窗口——步骤3 在界面上方的表结构中勾选“sno”及“grade”复选框，然后在界面中间的列表中右击字段sno行前的实心三角按钮，在弹出的快捷菜单中选择“添加分组依据”菜单项，如图6-3所示。图6-3为数据表sgrade添加分组依据6.1.2创建、修改与删除视图1．创建视图——步骤4 使用同样的方法为字段grade添加分组依据，然后单击右侧的“分组依据”栏，在其下拉列表中选择“Avg”选项，在“别名”栏中输入“avggrade”，如图6-4所示。图6-4定义字段grade的分组依据步骤5 设置完成后单击工具栏中的“保存”按钮，打开“选择名称”对话框，在“输入视图名称”文本框中输入“v_savg”，单击“确定”按钮，完成视图的创建。6.1.2创建、修改与删除视图1．创建视图——（2）使用T-SQL创建视图。创建视图的T-SQL命令为CREATE VIEW，具体语法格式如下：CREATE VIEW<视图名>[(列名)][WITH[ENCRYPTION][,SCHEMABINDING]]AS <SELECT语句>[WITH CHECK OPTION]①(列名)可以全部省略或全部指定。②ENCRYPTION表示视图加密，设置此选项后，将无法修改视图。③SCHEMABINDING表示视图与引用表的定义绑定，设置此选项后，引用表将不能随意更改表结构，如列的数据类型等，如果必须更改，则需要先删除与之绑定的视图。④AS关键字指明视图的结构，其后的<SELECT语句>可以是任意查询语句，但通常不包含ORDER BY子句和DISTINCT关键字。⑤WITH CHECK OPTION表示对视图执行的修改语句必须符合SELECT语句中的条件。设置此选项可以确保通过视图修改数据后，仍可通过视图看到相关数据。6.1.2创建、修改与删除视图1．创建视图——【例 6-2】使用T-SQL创建一个计算机系教师信息的视图v_t，其中包括教师编号、教师姓名及职称。CREATE VIEW v_tAS SELECT tno,tn,profFROM teacherWHERE dept='计算机'其中，对教师信息的访问限制在了计算机系之内，且只能查看tno、tn、prof三个字段的内容，保证了数据的安全性。6.1.2创建、修改与删除视图1．创建视图——【例 6-3】使用T-SQL实现例6-1。CREATE VIEW v_savg(sno,avggrade)AS SELECT sno,AVG(grade)FROM sgradeGROUP BY sno其中，因为对字段grade使用了AVG()函数，所以必须在列名处指定列别名。6.1.2创建、修改与删除视图1．创建视图6.1.2创建、修改与删除视图——2．修改视图（1）使用SSMS修改视图。【例 6-4】使用SSMS修改学生平均成绩视图v_savg，在其中添加学生姓名字段。步骤1 在“对象资源管理器”窗口中右击数据库scs视图节点中的“dbo.v_savg”节点，在弹出的快捷菜单中选择“设计”菜单项，打开视图v_savg的视图设计窗口。步骤2 在界面上方的空白处右击，接着在弹出的快捷菜单中选择“添加表”菜单项，如图6-5所示。图6-5向视图中添加新表——步骤3 打开“添加表”对话框，在“表”选项卡中选中“student”，单击“添加”按钮，然后单击“关闭”按钮，返回视图v_savg的视图设计窗口。步骤4 在界面上方的表结构“student”中勾选“sn”复选框，如图6-6所示。图6-6添加字段sn6.1.2创建、修改与删除视图2．修改视图——步骤5 在界面中间的列表中，将鼠标移到字段“sn”行上，按住鼠标左键不放，拖动到列表的第二行后松开鼠标，调整字段的顺序，如图6-7所示。步骤6 单击工具栏中的“保存”按钮，完成视图的修改。图6-7调整字段的顺序6.1.2创建、修改与删除视图2．修改视图——（2）使用T-SQL修改视图。ALTER VIEW <视图名>[(列名)]AS <SELECT语句>修改视图的T-SQL命令为ALTER VIEW，具体语法格式如下：【例 6-5】使用T-SQL修改学生平均成绩视图v_savg，在其中添加学生姓名字段。ALTER VIEW v_savg(sno,sn,avggrade)AS SELECT student.sno,sn,AVG(grade)FROM student JOIN sgrade ON student.sno=sgrade.snoGROUP BY student.sno,sn6.1.2创建、修改与删除视图2．修改视图6.1.2创建、修改与删除视图——3．删除视图（1）使用SSMS删除视图。【例 6-6】使用SSMS删除视图v-t。步骤1 在“对象资源管理器”窗口中右击数据库scs视图节点中的“dbo.v_t”，在弹出的快捷菜单中选择“删除”菜单项。步骤2 打开“删除对象”对话框，单击“确定”按钮，完成视图的删除，如图6-8所示。图6-5向视图中添加新表——（2）使用T-SQL删除视图。删除视图的T-SQL命令为DROP VIEW，具体语法格式如下：DROP VIEW <视图名>【例 6-7】使用T-SQL删除视图v_t。DROP VIEW v_t删除视图只是删除了视图的定义，与视图有关的数据表中的数据不会受到任何影响。6.1.2创建、修改与删除视图3．删除视图6.1.3利用视图查询与更新数据——1．利用视图查询数据在视图中查询数据与在数据表中查询数据的T-SQL命令相同，不同之处在于FROM子句中指明的是视图名。【例 6-8】使用T-SQL在视图v_savg中查询学生“冯明”的信息，查询结果如图6-9所示。SELECT *FROM v_savgWHERE sn='冯明'图6-9例6-8的查询结果——本例的执行过程为先找到视图v_savg的定义，将这一定义与查询语句结合起来，转换成等价的针对数据表的查询语句。其中的转换过程称为视图消解。不使用视图实现本例的T-SQL语句如下：SELECT student.sno,sn,AVG(grade) as avggradeFROM student,sgradeWHERE sn='冯明' AND student.sno=sgrade.snoGROUP BY student.sno,sn按前文步骤创建一个视图v_s，仅显示学生的学号、姓名与性别。练一练6.1.3利用视图查询与更新数据1．利用视图查询数据6.1.3利用视图查询与更新数据——2．利用视图更新数据（1）使用SSMS利用视图更新数据。【例 6-9】使用SSMS向视图v_s插入一条记录（12004，吴艮，女）。步骤1 在“对象资源管理器”窗口中右击数据库scs视图节点中的“dbo.v_s”，在弹出的快捷菜单中选择“编辑前200行”菜单项，打开视图v_s的视图编辑窗口。步骤2 在视图v_s的视图编辑窗口的空白行中输入要插入的数据，如图6-10所示。步骤3 单击工具栏中的“保存”按钮，完成视图数据的插入。图6-10向视图v_s中插入数据——【例 6-10】使用SSMS删除视图v_s中学生“吴艮”的记录。参考例6-9打开视图v_s的视图编辑窗口后，右击学生“吴艮”所在的数据行，在弹出的快捷菜单中选择“删除”菜单项，完成视图中数据的删除，如图6-11所示。图6-11从视图v_s中删除数据6.1.3利用视图查询与更新数据2．利用视图更新数据——（2）使用T-SQL利用视图更新数据。由于视图是一张虚表，所以对视图的更新最终会变成对数据表的更新，相关语法格式均与数据表的更新操作一致。【例 6-11】使用T-SQL实现例6-9。INSERT INTO v_s(sno,sn,sex)VALUES ('12004','吴艮','女')【例 6-12】将视图v_s中学生“吴艮”的性别改为“男”。UPDATE v_sSET sex='男'WHERE sn='吴艮'6.1.3利用视图查询与更新数据2．利用视图更新数据——【例 6-13】删除视图V_S中同学“吴艮”的记录。DELETE FROM v_sWHERE sn='吴艮'提示因为有些视图中的数据可能来自多个数据表，更新视图时有些操作会受限制。更新视图需注意如下3种情况：① 若视图是基于多个表使用连接操作导出，那么对这个视图执行更新操作时，每次只能影响其中一个表；② 若视图包含分组和聚合操作，则不允许对这个视图执行更新操作；③ 若视图中包含了表的主码且无连接操作，则可对这个视图执行更新操作。6.1.3利用视图查询与更新数据2．利用视图更新数据——课堂总结① 视图的基本概念②创建、修改与删除视图③ 利用视图查询与更新数据
02
索引
6.2.1索引的基本概念——简单理解，索引类似书籍中的目录，使用它可以快速找出某个或多个列中的某一特定值所在的行。例如，某数据表中有10000条记录，此时要查询第8000条记录，如果没有索引，必须遍历整个数据表，直到查询至第8000条记录；如果为该数据表增加id列并建立索引，查询时便可以直接在索引中找到8000，从而定位到第8000条记录。索引是一个单独的、存储在磁盘上的数据库结构，它包含所有记录的引用指针。索引是以表的字段（列）为基础建立的，是一种实现数据快速定位与加快数据访问速度的技术手段。1．索引6.2.1索引的基本概念——2．索引的特点索引的优点1① 极大地提升了数据的查询速度，这也是索引最主要的优点。② 通过创建唯一索引，可以保证数据库中的各行数据具有唯一性。③ 建立在外码上的索引可以加速表与表之间的连接，益于实现数据的参照完整性。④ 在数据量较大时显著减少分组和排序查询所使用的时间。⑤ 执行查询操作时，数据库引擎会根据索引对查询进行优化，使检索速度更快。索引的缺点2① 索引会占用物理存储空间，需要一定的额外物理存储来存放索引文件。② 索引的维护需要耗费时间，对数据表更新数据之后，相应的索引也需要动态维护。6.2.1索引的基本概念——3．设计索引的注意事项索引设计得不合理会对数据库及应用程序造成不良影响，所以设计索引时应注意如下几点。（1）索引并非越多越好，一个数据表中如果含有许多索引，不仅占用磁盘空间，也会增加维护成本。（2）对经常用于查询的列可以建立索引提高效率，但应避免插入不必要的数据。（3）数据量较小的数据表最好不要建立索引，因为可能不会产生明显的优化效果。（4）对于不同值较少的列，不要建立索引。例如，性别字段只有“男”和“女”两个不同值，在该列上建立索引对相关的查询操作没有明显的优化效果，反而会降低更新数据的速度。6.2.2索引的类型——聚集索引1聚集索引的键值顺序与数据存储的物理顺序相同。由于数据的物理存储顺序只有一个，所以一个数据表只能包含一个聚集索引。在默认情况下，系统会对PRIMARY KEY约束所在的列自动创建聚集索引，这也是数据表中的数据行通常以主码为基准排列的原因。非聚集索引2非聚集索引与聚集索引有类似的索引结构，但不同的是，非聚集索引的索引排序与物理排序相互独立。也就是说，数据行的物理存储顺序与索引键值的顺序不一致。一个数据表可以包含多个非聚集索引。与聚集索引类似，非聚集索引能够有效提升查询数据的速度，但同时也会降低更新数据的速度，并且占用一定的存储空间。6.2.2索引的类型——其他索引3（1）唯一索引。唯一索引能够保证索引中不包含重复值，从而使数据表中的每一行在某种条件下具有唯一性。聚集索引和非聚集索引都可以是唯一索引。知识库为数据表创建UNIQUE约束时，将默认创建唯一非聚集索引。（2）视图索引。视图索引即在视图上建立的索引，它将视图具体化，并把结果集永久存储在索引中。如果在查询中频繁引用进行复杂处理的视图，则可以在视图上创建索引。提示如果经常更新视图索引中的数据表，那么维护视图索引的开销将远远大于使用它的收益。（3）全文索引。全文索引是一种特殊类型的基于标记的索引，由SQL Server全文引擎生成及维护，用于快速查询某个字符出现的位置。6.2.3创建与删除索引——1．创建索引（1）使用SSMS创建索引。【例 6-14】使用SSMS在数据表sgrade的sno和cno列上建立名为sc_i的非聚集索引。提示本例中的索引sc_i存储了以字段sno与cno为基准的一种排列顺序，即先按sno递增排序，sno相同时再按cno递增排序。索引的基本操作——步骤1 在“对象资源管理器”窗口中打开数据库scs中数据表sgrade节点，右击其中的“索引”节点，在弹出的快捷菜单中选择“新建索引”→“非聚集索引”菜单项，如图6-12所示。图6-12创建索引6.2.3创建与删除索引1．创建索引——步骤2 打开“新建索引”窗口，在“索引名称”文本框中输入“sc_i”，然后单击“添加”按钮，如图6-13所示图6-12创建索引6.2.3创建与删除索引1．创建索引——步骤3 打开“从‘dbo.sgrade’中选择列”窗口，在列表中勾选“sno”和“cno”复选框，单击“确定”按钮，如图6-14所示。图6-12创建索引步骤4 返回“新建索引”窗口，单击“确定”按钮，完成索引的创建。6.2.3创建与删除索引1．创建索引——（2）使用T-SQL创建索引。创建索引的T-SQL命令为CREATE INDEX，具体语法格式如下：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX <索引名>ON <表名或视图名> (<列名>[ASC|DESC] [ , … n])[WITH <索引选项>[ , … n]]（1）[UNIQUE]表示在数据表或视图上创建唯一索引。（2）CLUSTERED表示在数据表或视图上创建聚集索引，NONCLUSTERED表示在数据表或视图上创建非聚集索引。省略索引类型时，表示创建非聚集索引。（3）<表名或视图名>指明索引所属的数据表或视图。（4）<列名>指明索引基于哪列，当设置多个列名时，表示创建组合索引。（5）<索引选项>可以指明关于索引的其他设置，具体内容读者可查阅相关资料。6.2.3创建与删除索引1．创建索引——【例 6-15】使用T-SQL实现例6-14。CREATE INDEX sc_i ON sgrade(sno,cno)【例 6-16】使用T-SQL在数据表teacher的tn列上建立名为t_i的非聚集索引。CREATE INDEX t_i ON teacher(tn)6.2.3创建与删除索引1．创建索引6.2.3创建与删除索引——2．删除索引（1）使用SSMS删除索引。【例 6-17】使用SSMS删除索引t_i。在“对象资源管理器”窗口中右击数据表teacher的索引节点“t_i”，在弹出的快捷菜单中选择“删除”菜单项，打开“删除对象”对话框，单击“确定”按钮，完成索引的删除。（2）使用T-SQL删除索引。删除索引的T-SQL命令为DROP INDEX，具体语法格式如下：DROP INDEX <数据表或视图.索引名>【例 6-18】使用T-SQL实现例6-17。DROP INDEX t_i ON teacher6.2.4修改和查看索引——1．修改索引（1）使用SSMS修改索引名。【例 6-19】使用SSMS将数据表sgrade的索引sc_i重命名为sc_i1。在“对象资源管理器”窗口中右击“索引”节点下的“sc_i”节点，在弹出的快捷菜单中选择“重命名”菜单项，输入“sc_i1”后按Enter键，完成索引名的修改。（2）使用T-SQL命令修改索引名的语法格式如下：EXEC sp_rename <'表名或视图名.索引名'>,<'新索引名'>【例 6-20】使用T-SQL实现例6-19。EXEC sp_rename 'sgrade.sc_i','sc_i1'6.2.4修改和查看索引——2．查看索引（1）使用SSMS查看索引。使用SSMS查看索引（以索引sc_i为例），可在“对象资源管理器”窗口中双击想要查看的索引（或是右击索引，在弹出的快捷菜单中选择“属性”菜单项），打开“索引属性- sc_i”窗口，查看索引的相关信息，如图6-15所示。图6-15查看索引sc_i的信息——（2）使用T-SQL查看索引。sp_helpindex存储过程可以返回数据表中所有索引的信息，具体语法格式如下：EXEC sp_helpindex <数据表名>【例 6-21】使用T-SQL查看数据表sgrade的索引，所得查询结果如图6-16所示。EXEC sp_helpindex sgrade图6-16查看数据表sgrade的索引6.2.4修改和查看索引2．查看索引实战训练——在订货管理系统数据库中应用视图与索引1．实战目的（1）掌握视图的创建、修改及删除等操作。（2）掌握索引的创建等操作。2．实战内容（1）基于前面章节实战训练创建的数据表goods与ordering，创建一个商品订购信息视图v_g，其中包括商品编号、商品名称、客户编号及商品数量。CREATE VIEW v_gAS SELECT goods.gid,gn,cid,numFROM goods,orderingWHERE goods.gid=ordering.gid实战训练——在订货管理系统数据库中应用视图与索引（2）基于数据表goods，创建一个商品信息视图v_g1，其中包括商品名称与商品价格。CREATE VIEW v_g1AS SELECT gn,priceFROM goods（3）修改视图v_g1，在其中添加生产日期字段。ALTER VIEW v_g1(gn,gdate,price)AS SELECT gn,gdate,priceFROM goods实战训练——在订货管理系统数据库中应用视图与索引（4）删除视图v_g1。DROP VIEW v_g1（5）通过视图v_g查询商品“吐司面包”的订购信息。SELECT *FROM v_gWHERE gn='吐司面包'（6）在数据表ordering的oid列上建立名为pc_i的唯一索引。CREATE UNIQUE INDEX pc_i ON ordering(oid)——课堂总结① 索引的基本概念② 索引的类型③ 创建与删除索引④ 修改与查看索引

展开更多......

收起↑