[IT认证]第八章SQL Server数据库应用开发技术.ppt

第8章 索引与视图,知识技能目标：1理解索引的概念、索引种类和创建索引的优缺点2熟练掌握索引的创建和删除3理解视图的概念及使用视图的优缺点4熟练掌握视图的创建、查看、修改、删除、更名5熟练掌握通过视图查询和更新数据方法,第8章 索引与视图,引导案例：前面的章节中已经创建了教务管理数据库，输入了相应的数据。数据库建立起来后，就可以操作数据库中的数据了，其中最主要的操作就是查询操作。为了提高查询的效率，就需要在相应的表上创建索引，为了增强查询的灵活性，就需要在表的基础上创建视图，满足用户复杂的查询需要。本章介绍这两方面的技术。,第8章 索引与视图,8.1 索引概念8.2 索引的创建与管理8.3 索引的维护8.4 视图概念8.5 视图的创建与管理8.6 通过视图查询和更新数据小结,8.1 索引概念,8.1.1 索引的概念用户对数据库最频繁的操作是数据查询，一般情况下，执行查询操作时，需要对整个表进行数据搜索。当表中的数据很多时，搜索就需要很长的时间，为了提高检索数据的效率，数据库引入了索引机制。在数据库中，使用索引无须对整个表扫描，就可以找到所需要的数据。索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。可以在表或视图的一列或多列上创建索引，基于两列或多列上建立的索引称为复合索引。如果表中任意两行被索引的列值不允许出现重复值，那么这种索引称为唯一（UNIQUE）索引。,返回目录,8.1 索引概念,8.1.2 聚簇索引和非聚簇索引 按照索引的组织方式，可以将索引分为聚集索引和非聚集索引。1聚簇索引（Clustered Index）对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚簇索引与数据是混为一体的，它的叶节点中存储的是实际的数据。可以在表或视图的一列或多列上的组合上创建索引，在实际开发中，一般在定义成主键的列建立聚集索引，且创建主键约束时，若表中没有聚集索引，则SQL Server将主键列作为聚集索引列。在每个表中只能创建一个聚集索引。,返回目录,8.1 索引概念,8.1.2 聚簇索引和非聚簇索引 2非聚簇索引（Nonclustered Index）非聚集索引与书中内容的组织方式类似，数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，索引中的项目按键值的顺序存储，而表中的数据按另一种顺序存储，具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。在表中，最多可以建立250个非聚集索引，或者249个非聚集索引和1个聚集索引。,返回目录,8.1 索引概念,8.1.3 创建索引的优缺点1创建索引的优点 通过创建唯一索引，可以保证数据记录的唯一性。可以大大加快数据检索速度。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。,返回目录,8.1 索引概念,8.1.3 创建索引的优缺点2创建索引的缺点 创建索引要花费时间和占用存储空间。建立索引加快了数据检索速度，却减慢了数据修改速度。,返回目录,8.1 索引概念,8.1.4考虑建索引的列和不考虑建索引的列1考虑建索引的列 如果在一个列上创建索引，该列就称为索引列。索引列中的值称为关键字值。考虑建索引的列有以下这些列：（1）主键：通常检索、存取表是通过主键来进行的。因此，应该考虑在主键上建立索引。（2）连接中频繁使用的列：用于连接的列若按顺序存放，系统可以很快地执行连接。如外键，除用于实现参照完整性外，还经常用于进行表的连接。（3）在某一范围内频繁搜索的列和按排序顺序频繁检索的列。,返回目录,8.1 索引概念,8.1.4考虑建索引的列和不考虑建索引的列2不考虑建索引的列建立索引需要产生一定的存储开销，在进行插入和更新数据的操作时，维护索引也要花费时间和空间。一般来说，如下一些列不考虑建立索引：（1）很少或从来不在查询中引用的列，因为系统很少或从来不根据这个列的值去查找数据行。（2）只有两个或很少几个值的列（如性别，只有两个值“男”或“女”），以这样的列创建索引并不能实现建立索引的好处。（3）以bit、text、image数据类型定义的列。（4）数据行数很少的小表一般也没有必要创建索引。,返回目录,8.2 索引的创建与管理,8.2.1 用T-SQL语句创建索引 用T-SQL语句创建索引，通过使用CREATE INDEX语句来实现。语法格式：CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX 索引名 ON 表名|视图名(列名ASC|DESC,.n)WITH PAD_INDEX,FILLFACTOR=填充因子,DROP_EXISTING,返回目录,8.2 索引的创建与管理,8.2.1 用T-SQL语句创建索引 说明：UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值 相同的两行。CLUSTERED：用于指定创建的索引为聚集索引。NONCLUSTERED：用于指定创建的索引为非聚集索引。PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。FILLFACTOR=填充因子：用于指定在创建索引时，每个索引页的数 据占索引页大小的百分比，填充因子的值 为1到100。DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的 聚集索引或者非聚集索引。,返回目录,8.2 索引的创建与管理,8.2.1 用T-SQL语句创建索引【例8.1】使用-SQL语句在“教务管理数据库”数据库的“学生表”上创建一个名“XS_XM_INDEX”的非唯一性非聚簇索引，索引关键字为“姓名”，升序，填充因子50%。【例8.2】使用-SQL语句在“教务管理数据库”的“学生表”上创建一个名为“XS_XH_INDEX”的唯一性聚集索引，索引关键字为“学号”，按降序排列，填充因子70%。【例8.3】使用-SQL语句在“教务管理数据库”的“学生表”上创建一名为“XS_XH_XM_INDEX”的唯一性聚集索引，索引关键字为“学号”和“姓名”，均按降序排序，删除并重新创建同名的索引文件。,返回目录,8.2 索引的创建与管理,8.2.2 用T-SQL语句管理索引 当索引创建之后，可以使用T-SQL语句查看、更名和删除索引。1查看索引在创建索引之前或在创建索引之后，可以用sp_helpindex系统存储过程查看表的所有索引信息。语法格式：SH_HELPINDEX 表名【例.4】用系统存储过程sp_helpindex查看教务管理数据库中“学生表”的索引信息。,返回目录,8.2 索引的创建与管理,8.2.2 用T-SQL语句管理索引 2重命名索引创建索引后，可以用sp_rename系统存储过程来更改索引的名称。语法格式：SP_RENAME 表名.原索引名,新索引名【例.5】用系统存储过程sp_ rename将“学生表”的索引XS_XM_INDEX 重新命名为XS_XM_INDEX 1。,返回目录,8.2 索引的创建与管理,8.2.2 用T-SQL语句管理索引 3删除索引在创建索引之后，如果该索引不再需要，使用DROP INDEX命令可以删除一个或者多个当前数据库中的索引。语法格式：DROP INDEX 表名.索引名,.n【例.6】用DROP语句将“学生表”的索引XS_XM_INDEX1删除。注意：删除索引时要注意，如果索引是在CREATE TABLE语句中创建的，只能用ALTER TABLE命令来删除索引，而不能用DROP INDEX来删除。,返回目录,8.2 索引的创建与管理,8.2.3 用SSMS创建与管理索引1使用SQL Server Management Studio创建索引1）打开SQL Server Management Studio。2）展开要创建索引的数据库，再展开“表”结点。3）展开要建索引的表。4）在“索引”结点上单击右键，选择“新建索引”。5）在“索引名称”文本框中，输入“ZYB_ _ZYH_INDEX”，在“索引类型”列表框中选择索引类型为“非聚集”，“唯一”索引。6）单击“添加”按钮，弹出对话框，选择要在其上创建索引的列。7）单击“确定”按钮，回到“新建索引”对话框，其中，“排序”列用于设置索引的排列顺序。如“升序”。8）单击“确定”按钮，即完成了索引的创建过程。,返回目录,8.2 索引的创建与管理,8.2.3 用SSMS创建与管理索引2使用SQL Server Management Studio查看索引1）打开SQL Server Management Studio。2）在对象资源管理器中展开要查看索引的数据库，再展开“表”结点3）展开要查看索引的表。4）展开“索引”结点，则会出现表中已存在的索引列表。5）双击某一索引名称，则出现索引属性对话框，用户可以查看索引的信息。,返回目录,8.2 索引的创建与管理,8.2.3 用SSMS创建与管理索引3使用SQL Server Management Studio删除索引1）打开SQL Server Management Studio。2）展开要查删除索引的数据库，再展开“表”结点。3）展开要删除索引的表。4）展开择“索引”结点，则会出现表中已存在的索引列表。5）选择某个需要删除的索引，如“ZYB_ _ZYH_INDEX”索引，单击鼠标右键，在弹出的菜单中单击“删除”菜单项，则打开删除索引对话框。6）单击“确定”按钮后，完成删除操作。,返回目录,8.3 索引的维护,在创建索引后，为了得到最佳的性能，必须对索引进行维护。因为随着时间的推移，用户频繁执行插入、修改和删除等一系列操作，这些操作会使数据变得支离破碎，从而造成索引性能的下降。SQL Server提供了多种方法来维护索引。1更新索引的统计信息在创建索引时，SQL Server会自动存储有关的统计信息，查询优化器会利用索引的统计信息来估算使用该索引查询数据的成本。但随着用户频繁执行插入、修改和删除等一系列操作后，这些统计信息可能已经过时了，因此有必要对索引的这些统计信息进行更新。,返回目录,8.3 索引的维护,1更新索引的统计信息可以使用UPDATE STATISTICS命令来更新索引的统计信息。语法格式：UPDATE STATISTICS 表名 索引名【例8.7】使用UPDATE STATISTICS命令，更新在“教务管理数据库”数据库的“学生表”的“XS_XH_INDEX”索引的统计信息。,返回目录,8.3 索引的维护,2使用DDCC SHOWCONTIG命令扫描表对表的频繁操作可能会导致表碎片，而表碎片会造成数据库查询性能的降底，用户可以使用DBCC SHOWCONTIG来扫描表，并通过返回值确定该表的索引页是否已经严重不连续。语法格式：DBCC SHOWCONTIG(表名，索引名)【例8.8】使用DBCC SHOWCONTIG命令获取“教务管理数据库”数据库的“学生表”的“XS_XH_INDEX”索引碎片信息,返回目录,8.3 索引的维护,3使用DDCC INDEXDEFRAG命令整理碎片可以使用DBCC INDEXDEFRAG命令对聚集索引和非聚集索引页上的碎片进行整理。语法格式：DBCC INDEXDEFRAG（数据库名,表名,索引名）【例8.9】使用DBCC INDEXDEFRAG命令对“教务管理数据库”数据库的“学生表”的“XS_XH_INDEX”索引进行碎片整理。,返回目录,8.4 视图概念,8.4.1 视图的概念 视图是用户查询表中数据的一种方式，用户通过它可以浏览表中的部分或全部数据，而数据的物理存放位置仍存储在表中，这些表称为视图的基表。视图不是真实存在的数据表，而是一个虚拟表，它仅是一个查询语句的定义，视图中的数据可以来自于一个或多个基表，也可以来自视图。视图可以使用户集中在他们关心的数据上，不必考虑那些不必要的数据。这样，用户只能看到视图中显示的数据。而不能看到视图所引用的基表中的其他数据。在一定程度上保证了数据的安全。,返回目录,8.4 视图概念,8.4.2 使用视图的优缺点1视图的优点 限定特定的数据简化数据操作安全保护2视图的缺点 降低性能限制修改,返回目录,8.5 视图的创建与管理,8.5.1 用T-SQL语句创建视图 使用T-SQL语句创建视图，通过CREATE VIEW命令来完成。语法格式：CREATE VIEW 视图名WITH ENCRYPTIONAS SELECT语句WITH CHECK OPTION说明：WITH ENCRYPTION:对视图的定义进行加密。WITH CHECK OPTION：强制通过视图执行的所有数据修改语句必须符合视图定义中设置的条件。,返回目录,8.5 视图的创建与管理,8.5.1 用T-SQL语句创建视图【例8.10】在教务管理数据库的学生表上创建一个XS_VIEW1视图，视图中只包括所有政治面貌为“共产党员”的学生信息。【例8.11】在教务管理数据库的学生表上创建一个XS_VIEW2视图，视图中只包括学号、姓名、性别、政治面貌等信息。【例8.12】在教务管理数据库的学生表、选课表上创建一个XS_VIEW3视图，视图中只包括jy071班学生的学号、姓名、班级、课程号等信息。【例8.13】在教务管理数据库的学生表上创建一个XS_VIEW4视图，视图中只包含每个学生的学号和所学课程的总成绩。【例8.14】在教务管理数据库中创建视图XS_VIEW5，视图中只包括女生的学号、姓名、性别、政治面貌等信息。,返回目录,8.5 视图的创建与管理,8.5.2 用T-SQL语句管理视图 视图创建好以后，可以使用T-SQL语句来查看、修改、删除视图和更改视图的名称。1查看视图可以使用系统存储过程查看视图信息，主要有如下的三个系统存储过程来查看视图的相关信息。（1）查看视图的名称、拥有者及创建日期等信息语法格式：SP_HELP 视图名【例8.15】查看“XS_VIEW1”视图的名称、拥有者及创建日期等信息。SP_HELP XS_VIEW1,返回目录,8.5 视图的创建与管理,8.5.2 用T-SQL语句管理视图（2）查看视图的定义信息 语法格式：SP_HELPTEXT 视图名【例8.16】查看“XS_VIEW1”视图的定义信息。SP_HELPTEXT XS_VIEW1（3）查看生成视图的对象和列 语法格式：SP_DEPENDS 视图名【例8.17】查看“XS_VIEW1”生成视图的对象和列。SP_DEPENDS XS_VIEW1,返回目录,8.5 视图的创建与管理,8.5.2 用T-SQL语句管理视图 2修改视图可以使用T-SQL语句中的ALTER VIEW命令来修改视图。语法格式：ALTER VIEW 视图名WITH ENCRYPTIONAS SELECT语句WITH CHECK OPTION【例8.18】修改教务管理数据库中的XS_VIEW1视图，视图中只包括所有政治面貌为“共青团员”的学生信息。,返回目录,8.5 视图的创建与管理,8.5.2 用T-SQL语句管理视图 3删除视图可以使用T-SQL语句中的DROP VIEW命令来删除视图。语法格式：DROP VIEW 视图名【例8.19】删除教务管理数据库中的XS_VIEW1视图 DROP VIEW XS_VIEW1,返回目录,8.5 视图的创建与管理,8.5.2 用T-SQL语句管理视图 4重命名视图可以使用系统存储过程sp_rename对视图重命名语法格式：SP_RENAME 原视图名,新视图名【例8.20】将“教务管理数据库”中的“XS_VIEW4”视图名重命名为“XS_VIEW_2”。SP_RENAME XS_VIEW4,XS_VIEW_2,返回目录,8.5 视图的创建与管理,8.5.3 用SSMS创建与管理视图 1使用SQL Server Management Studio创建视图1）打开SQL Server Management Studio。2）在对象资源管理器中展开要创建视图的数据库，如“教务管理数据库”。3）在“视图”结点上单击右键，选择“新建视图”菜单项弹出一个“添加表”对话框。4）选择相应的表或视图，单击“添加”按钮就可以添加创建视图的基表，如“学生表”，重复此操作，可以添加多个基表。添加完基表后，单击“关闭”按钮。进入“设计视图”窗口。,返回目录,8.5 视图的创建与管理,8.5.3 用SSMS创建与管理视图 1使用SQL Server Management Studio创建视图5）在第1个窗格中就可以看到新添加的“学生表”，在“学生表”每一列的左边有一个复选框，选择相应的复选框，被选择的列在视图中将被引用。6）在第2个窗格中是条件窗格，可以在它当中指定查询条件。其中，Filter列用于输入在视图中出现的筛选条件。7）在第1和第2个窗格中进行相应的设置，会在第3个窗格中自动生成对应的T-SQL语句。8）设计完视图后，单击“视图设计”窗口的“关闭”按钮，弹出“输入视图名称”对话框。9）输入视图名称后，单击“确定”按钮，完成视图的创建。,返回目录,8.5 视图的创建与管理,8.5.3 用SSMS创建与管理视图2使用SQL Server Management Studio查看视图1）打开SQL Server Management Studio。2）在对象资源管理器中展开要创建视图的数据库，如“教务管理数据库”。3）展开“视图”结点，则会出现数据库中已存在的视图列表。4）右击某一视图名称，在弹出的菜单中选择“属性”菜单项，用户可以查看视图的属性，包括视图的创建时间、视图名称、架构名称等。,返回目录,8.5 视图的创建与管理,8.5.3 用SSMS创建与管理视图 3使用SQL Server Management Studio修改视图1）打开SQL Server Management Studio。2）在对象资源管理器中展开要创建视图的数据库，如“教务管理数据库”。3）展开“视图”结点，则会出现数据库中已存在的视图列表。4）右击某一视图名称，在弹出的菜单中单击“设计”菜单项，用户可以重新修改视图的定义。5）修改完视图的定义后，单击“修改视图”窗口中“关闭”按钮，完成视图的修改。,返回目录,8.5 视图的创建与管理,8.5.3 用SSMS创建与管理视图 4、使用SQL Server Management Studio重命名视图1）打开SQL Server Management Studio。2）在对象资源管理器中展开要创建视图的数据库，如“教务管理数据库”。3）展开“视图”结点，则会出现数据库中已存在的视图列表。4）右击某一视图名称，在弹出的菜单中单击“重命名”菜单项，“重命名视图”窗口。用户可以重新输入视图的名称。5）输入新的名称后，单击任一空白处，完成视图的重命名。,返回目录,8.5 视图的创建与管理,8.5.3 用SSMS创建与管理视图 5使用SQL Server Management Studio删除视图1）打开SQL Server Management Studio。2）在对象资源管理器中展开要创建视图的数据库，如“教务管理数据库”。3）展开“视图”结点，则会出现数据库中已存在的视图列表。4）右击某一视图名称，如VIEW_1视图，在弹出的菜单中单击“删除”菜单项，进入“删除视图”窗口。5）单击“确定”按钮，完成视图的删除。,返回目录,8.6 通过视图查询和更新数据,8.6.1 用SQL Server Management Studio更新数据 视图创建好后，可以使用SQL Server Management Studio来通过视图进行数据的插入、修改和删除操作。方法如下：1）打开SQL Server Management Studio。2）在对象资源管理器中展开要创建视图的数据库，如“教务管理数据库”。3）展开“视图”结点，则会出现数据库中已存在的视图列表。4）右击某一视图名称，如VIEW_2视图，在弹出的菜单中单击“打开视图”菜单项，进入“视图数据操作”窗口。5）在窗口中可以插入、修改和删除视图中数据。,返回目录,8.6 通过视图查询和更新数据,8.6.2 用T-SQL语句查询和更新数据 1通过视图查询数据语法格式：SELECT 字段名列表|*FROM 视图名【例8.21】查询“XS_VIEW3”视图中的所有信息。SELECT*FROM XS_VIEW3【例8.22】查询“XS_VIEW3”视图中所有的学号、姓名和课程号信息。SELECT 学号，姓名，课程号 FROM XS_VIEW3,返回目录,8.6 通过视图查询和更新数据,8.6.2 用T-SQL语句查询和更新数据 2通过视图插入数据语法格式：INSERT 视图名(列名，)VALUES(值，)【例8.23】向“XS_VIEW2”视图中插入一行数据。INSERT INTO XS_VIEW2 VALUES(20070401101,李明,男,共青党员),返回目录,8.6 通过视图查询和更新数据,8.6.2 用T-SQL语句查询和更新数据 3通过视图修改数据命令格式：UPDATE 视图名 SET 列名=表达式,.nWHERE 说明：可以一次修改多个字段。如果有条件子句，只修改满足条件的行，否则所有行都被修改。【例8.24】将“XS_VIEW2”视图中学号为“20070401101”的学生的政治面貌改为“共产党员”。UPDATE XS_VIEW2 SET 政治面貌=共产党员 WHERE 学号=20070401101,返回目录,8.6 通过视图查询和更新数据,8.6.2 用T-SQL语句查询和更新数据 4通过视图删除数据命令格式：DELETE FROM 视图名 WHERE 说明：如果有条件子句，只删除视图中满足条件的行，否则视图中所有行都被删除。【例8.25】将“XS_VIEW2”视图中学号为“20070401101”的数据行删除。DELETE FROM XS_VIEW2 WHERE 学号=20070401101,返回目录,8.6 通过视图查询和更新数据,8.6.3 通过视图更新数据的限制使用视图可以更改基表中的数据，但使用视图修改数据时，有下几点限制：修改视图中的数据时，不能同时修改两个或者多个基表，可以对基于两个或多个基表或者视图的视图进行修改，但是每次修改都只能影响一个基表。不能修改那些通过计算得到的字段。如果在创建视图时指定了WITH CHECK OPTION选项，那么所有使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中。如果视图引用多个表时，无法用DELETE命令删除数据。,返回目录,小结,本章主要讲述了对索引和视图的操作，包括索引和视图的创建、修改、删除和重命名，以及索引的维护和视图的更新操作。通过本章的学习，应熟练掌握利用SQL Server Management Studio和T-SQL语句创建索引，对索引进行管理；熟练掌握利用SQL Server Management Studio和T-SQL语句创建视图，使用视图和管理视图。,返回目录,