项目7 CJGL数据库索引和视图设计

1、 SQL Server 2008项目实训教程项目实训教程项目项目7 CJGL7 CJGL数据库索引和视图数据库索引和视图设计设计 SQL Server 2008项目实训教程项目实训教程技能目标技能目标 理解索引的优缺点；学会根据实际需求设计理解索引的优缺点；学会根据实际需求设计CJGLCJGL数据数据库中的数据表的索引，提高数据检索的速度。库中的数据表的索引，提高数据检索的速度。 学会使用视图。在学会使用视图。在CJGLCJGL数据库中，结合实际需求灵活数据库中，结合实际需求灵活地运用视图，提高数据库的开发效率和安全性。地运用视图，提高数据库的开发效率和安全性。 SQL Server 2008

2、项目实训教程项目实训教程知识目标知识目标u理解索引的优点和缺点；理解索引的优点和缺点；u了解聚集索引和非聚集索引的特点；了解聚集索引和非聚集索引的特点；u掌握使用掌握使用CREATE INDEXCREATE INDEX语句创建索引的方式；语句创建索引的方式；u掌握查看、删除和修改索引；掌握查看、删除和修改索引；u掌握视图的概念、特点和类型；掌握视图的概念、特点和类型；u掌握创建视图、修改视图和删除视图的方法；掌握创建视图、修改视图和删除视图的方法；u掌握通过视图修改基表中的数据；掌握通过视图修改基表中的数据； SQL Server 2008项目实训教程项目实训教程索引的作用索引的作用 索引是一

3、种重要的数据对象，它由一行行的记录组成，索引是一种重要的数据对象，它由一行行的记录组成，而每一行记录都包括数据表中一列或若干列值的集合，而而每一行记录都包括数据表中一列或若干列值的集合，而不是数据表中的所有记录，因而能够提高数据的查询效率。不是数据表中的所有记录，因而能够提高数据的查询效率。此外，索引还确保列的惟一性，从而保证数据的完整性。此外，索引还确保列的惟一性，从而保证数据的完整性。 SQL Server 2008项目实训教程项目实训教程索引的分类索引的分类u 聚集索引聚集索引u非聚集索引非聚集索引u惟一索引惟一索引 SQL Server 2008项目实训教程项目实训教程索引的分类（续）

4、索引的分类（续）1 1、聚集索引（也称簇索引或簇集索引）、聚集索引（也称簇索引或簇集索引） 在聚集索引中，表中的行的物理存储顺序和索引顺序完全相同。在聚集索引中，表中的行的物理存储顺序和索引顺序完全相同。聚集索引对表的物理数据页，按列进行排序，然后再重新存储到聚集索引对表的物理数据页，按列进行排序，然后再重新存储到磁盘上。磁盘上。 因此查找数据很快，但所占空间也要特别多。一个表只因此查找数据很快，但所占空间也要特别多。一个表只能有一个聚集索引。能有一个聚集索引。使用聚集索引的候选列如下：使用聚集索引的候选列如下：经常按范围存取的列，如出生日期经常按范围存取的列，如出生日期1989-1-1；经常

5、在经常在where子句中使用并插入是随机的主键列；子句中使用并插入是随机的主键列；在在group by 或或order by中使用的列；中使用的列；在连接操作中使用的列。在连接操作中使用的列。 SQL Server 2008项目实训教程项目实训教程索引的分类（续）索引的分类（续）2 2、非聚集索引（也称非簇索引或非簇集索引）、非聚集索引（也称非簇索引或非簇集索引） 非簇索引具有与表的数据行完全分离的结构，非聚集索引的叶非簇索引具有与表的数据行完全分离的结构，非聚集索引的叶节点存储了组成非聚集索引的关键字值和一个指针，指针指向数节点存储了组成非聚集索引的关键字值和一个指针，指针指向数据页中的数据

6、行，该行具有与索引键值相同的列值，非聚集索引据页中的数据行，该行具有与索引键值相同的列值，非聚集索引不改变数据行的物理存储顺序，因而一个表可以有多个非聚集索不改变数据行的物理存储顺序，因而一个表可以有多个非聚集索引。引。使用非聚集索引的候选列如下：使用非聚集索引的候选列如下：常用于聚合函数的列；常用于聚合函数的列；常用于常用于join、order by、group by的列；的列；查询出的数据不超过表中数据量的查询出的数据不超过表中数据量的20%。 SQL Server 2008项目实训教程项目实训教程索引的分类（续）索引的分类（续）3 3、惟一索引、惟一索引 如果为了保证表或视图的每一行在某

7、种程度上是惟一的，可如果为了保证表或视图的每一行在某种程度上是惟一的，可以使用惟一索引，也就是说索引值是惟一的。创建数据表时如果以使用惟一索引，也就是说索引值是惟一的。创建数据表时如果设置了主键，则设置了主键，则SQL Server 2005就会默认建立一个惟一索引。就会默认建立一个惟一索引。 注意：注意：避免对经常更新的表进行过多的索引，且索引中包含的列应尽可避免对经常更新的表进行过多的索引，且索引中包含的列应尽可能少；能少；使用多个索引可以提高更新少而数据量大的查询的性能；使用多个索引可以提高更新少而数据量大的查询的性能；对小表进行索引可能不会产生优化效果。对小表进行索引可能不会产生优化效

8、果。 SQL Server 2008项目实训教程项目实训教程索引和约束的关系索引和约束的关系 对列定义对列定义PRIMARY KEYPRIMARY KEY约束和约束和 UNIQUEUNIQUE约束时，会自动创建索引。约束时，会自动创建索引。1 1、PRIMARY KEYPRIMARY KEY约束和索引约束和索引 如果创建表时，将一个特定列标识为主键，如果创建表时，将一个特定列标识为主键， 自动对该列创建自动对该列创建PRIMARY KEY约束和惟一聚集索引。约束和惟一聚集索引。2 2、UNIQUE UNIQUE 约束和索引约束和索引u 默认情况下，创建默认情况下，创建UNIQUE约束，自动对该

9、列创建惟一非聚集索引。约束，自动对该列创建惟一非聚集索引。u当用户从表中删除主键约束或惟一约束时，创建在这些约束列上的当用户从表中删除主键约束或惟一约束时，创建在这些约束列上的索引也会被自动删除。索引也会被自动删除。3 3、独立索引、独立索引 使用使用 CREATE INDEX 语句或语句或 SQL Server Management Studio 对象资源管理器中的对象资源管理器中的【新建索引新建索引】对话框创建独立于约束的索引对话框创建独立于约束的索引 SQL Server 2008项目实训教程项目实训教程创建索引创建索引 u使用使用Management Studio Management

10、 Studio u使用使用CREATE INDEXCREATE INDEX语句语句 CREATE UNIQUE CLUSTERED | NONCLUSTERED /* 索引的类型索引的类型*/INDEX 索引名索引名ON 表名表名 列名列名 ASC | DESC ,.n) SQL Server 2008项目实训教程项目实训教程创建索引（续）创建索引（续）【例例1 1】在学生表上创建学号的聚集索引。在学生表上创建学号的聚集索引。 CREATE CLUSTERED INDEX IX_student ON 学生表学生表 (学号学号)【例例2 2】在在CJGLCJGL数据库中，经常要按照学生姓名查询信

11、息，数据库中，经常要按照学生姓名查询信息，希望提高查询速度。希望提高查询速度。CREATE UNIQUE INDEX IX_sName ON 学生表学生表 (姓名姓名)【例例3 3】在课程表上创建课程名称的非聚集索引。在课程表上创建课程名称的非聚集索引。CREATE NONCLUSTERED INDEX IX_cnameON 课程表课程表 (课程名称课程名称) SQL Server 2008项目实训教程项目实训教程删除索引删除索引 u使用使用Management Studio Management Studio 删除独立于约束的索引删除独立于约束的索引u使用使用DROP INDEXDROP I

12、NDEX语句删除独立于约束的索引语句删除独立于约束的索引 DROP INDEX DROP INDEX 索引名索引名说明：说明： 如果聚集索引是在表创建主键约束时自动创建的索引，那如果聚集索引是在表创建主键约束时自动创建的索引，那么无法利用么无法利用DROP INDEX语句来删除。语句来删除。 SQL Server 2008项目实训教程项目实训教程查看索引查看索引 u使用使用Management StudioManagement Studiou用系统存储过程用系统存储过程 sp_helpindexsp_helpindex 可以返回表的所有索引信息，它的语法结构如下。可以返回表的所有索引信息，它的

13、语法结构如下。 sp_helpindex objname=name例例 使用存储过程查看学生表中的索引信息使用存储过程查看学生表中的索引信息use CJGLexec sp_helpindex 学生表学生表 SQL Server 2008项目实训教程项目实训教程视图的定义视图的定义 视图是一种常用的数据库对象，视图是一种常用的数据库对象， 可以把它看成从一可以把它看成从一个或几个基本表导出的虚表或存储在数据库中的查询。个或几个基本表导出的虚表或存储在数据库中的查询。 SQL Server 2008项目实训教程项目实训教程视图的定义视图的定义 视图中数据可来自于当前或其它数据库的一个或多个视图中数

14、据可来自于当前或其它数据库的一个或多个表，或者其它视图。表，或者其它视图。 数据库中只存放视图的定义，而不存放视图中的数据，数据库中只存放视图的定义，而不存放视图中的数据，视图中的数据还存放在原表中，当原表中的数据发生变化视图中的数据还存放在原表中，当原表中的数据发生变化时，视图中相对应的数据也会发生相应的变化。但当修改时，视图中相对应的数据也会发生相应的变化。但当修改视图中的数据时，原表中相应的数据也会发生相应的变化。视图中的数据时，原表中相应的数据也会发生相应的变化。 SQL Server 2008项目实训教程项目实训教程视图的作用视图的作用 u简化操作简化操作u提高数据安全性提高数据安全

15、性 u屏蔽数据库的复杂性屏蔽数据库的复杂性 u数据即时更新数据即时更新 说明：说明： 视图一经定义后，就可以像基本表一样可以被查询、删除。视视图一经定义后，就可以像基本表一样可以被查询、删除。视图为查看和存取数据提供了另外一种途径。图为查看和存取数据提供了另外一种途径。 SQL Server 2008项目实训教程项目实训教程创建视图创建视图u使用使用Management StudioManagement Studiou使用使用Create ViewCreate View语法格式如下：语法格式如下：CREATE VIEW 视图名视图名 (column,.n)WITH ENCRYPTIONASse

16、lect_statement WITH CHECK OPTION 其中，其中， WITH ENCRYPTIONWITH ENCRYPTION表示将创建视图的代码加密；表示将创建视图的代码加密；WITH CHECK OPTIONWITH CHECK OPTION表示对视图进行表示对视图进行insertupdatedeleteinsertupdatedelete操作时，只有满足操作时，只有满足视图定义条件的记录才可被操作。视图定义条件的记录才可被操作。 SQL Server 2008项目实训教程项目实训教程视图设计器视图设计器 SQL Server 2008项目实训教程项目实训教程创建视图创建视图

17、-使用使用Create ViewCreate View（续）（续）【例例1 1】在在CJGLCJGL数据库中，创建一个学生视图，包括学号、姓名、性别、出生数据库中，创建一个学生视图，包括学号、姓名、性别、出生日期。日期。create view view_studentasselect 学号学号, 姓名姓名, 性别性别, 出生日期出生日期 from 学生表学生表 SQL Server 2008项目实训教程项目实训教程创建视图创建视图-使用使用Create ViewCreate View（续）（续）【例例2 2】在在CJGLCJGL数据库中，为数据库中，为“信息工程系信息工程系”学工部门用户创建视

18、图学工部门用户创建视图“view_XG”view_XG”，包括学号、姓名、性别、班级名称、系部名称、出生日期、，包括学号、姓名、性别、班级名称、系部名称、出生日期、族别和政治面貌，并对创建视图文本进行加密。族别和政治面貌，并对创建视图文本进行加密。create view view_XGwith encryptionasselect 学号学号, 姓名姓名, 性别性别, 班级名称班级名称, 系部系部名称名称, 出生日期出生日期, 族别族别,政政治面貌治面貌From 部门表部门表 D join 班级表班级表 C on D.系部编号系部编号 = C.系部编号系部编号 join 学生表学生表 S ON

19、C.班级编号班级编号 = S.班级编号班级编号where 系部名称系部名称= 信息工程系信息工程系 SQL Server 2008项目实训教程项目实训教程创建视图创建视图-使用使用Create ViewCreate View（续）（续）【例例3 3】在在CJGLCJGL数据库中，为教务管理部门用户创建视图数据库中，为教务管理部门用户创建视图“view_class”view_class”，包，包括班级编号、班级名称、所属系部名称和人数，并按照系部名称升序排序。括班级编号、班级名称、所属系部名称和人数，并按照系部名称升序排序。create view view_classasselect C.班级编

20、号班级编号, 班级名称班级名称,系部名称系部名称,count(学号学号) 人数人数from 班级表班级表 C join 部门表部门表 D on C.系部编号系部编号 = D.系部编号系部编号 join 学生表学生表 S on C.班级编号班级编号 = S.班级编号班级编号group by C.班级编号班级编号, 班级名称，系部名称班级名称，系部名称order by 系部名称系部名称 SQL Server 2008项目实训教程项目实训教程创建视图创建视图-使用使用Create ViewCreate View（续）（续）例例4 4】在在CJGLCJGL数据库中，对视图进行如下数据操纵：数据库中，对

21、视图进行如下数据操纵：1 1）在视图）在视图“view_XG”view_XG”中查询中查询“1313软件技术软件技术”班的所有学生信息。班的所有学生信息。2 2）在视图）在视图“view_XG”view_XG”中将学号为中将学号为“004”004”的学生的政治面貌改为的学生的政治面貌改为“党员党员”。1） Select * from view_XG where 班级名称班级名称=13软件技术软件技术2）update view_XG set 政治面貌政治面貌=党员党员 where 学号学号=004 SQL Server 2008项目实训教程项目实训教程创建视图创建视图-使用使用Create Vi

22、ewCreate View（续）（续）例例5 5】在在CJGLCJGL数据库中，对视图进行如下数据操纵：数据库中，对视图进行如下数据操纵：3 3）通过视图）通过视图“view_XG”view_XG”新增如下记录：新增如下记录：（007007，何新，男，何新，男，1313艺术设计艺术设计1 1班，艺术系，班，艺术系，1990-12-131990-12-13，汉族，团员），汉族，团员）3） insert into view_XG values(007, 何新何新, 男男,13艺术设计艺术设计1班班,艺术系艺术系, 1990-12-13,汉族汉族,团员团员) SQL Server 2008项目实训教

23、程项目实训教程在创建或使用视图时限制情况在创建或使用视图时限制情况（1 1）只能在当前数据库中创建视图，在视图中最多只能引）只能在当前数据库中创建视图，在视图中最多只能引1024 1024 列。列。（2 2）不能在规则、默认值、触发器的定义中引用视图。）不能在规则、默认值、触发器的定义中引用视图。（3 3）不能在视图上创建索引；）不能在视图上创建索引；（4 4）如果视图引用的表被删除，则当使用该视图时将返回一条）如果视图引用的表被删除，则当使用该视图时将返回一条错误信息；如果创建具有相同表的结构的新表来替代已删除的错误信息；如果创建具有相同表的结构的新表来替代已删除的表，视图可以使用，否则必须

24、重新创建视图。表，视图可以使用，否则必须重新创建视图。（5 5）如果视图中某一列是函数、数学表达式、常量或来自多个）如果视图中某一列是函数、数学表达式、常量或来自多个表的列名相同，则必须为列定义名字。表的列名相同，则必须为列定义名字。（6 6）当通过视图查询数据时，）当通过视图查询数据时，SQL Server SQL Server 不仅要检查视图引用不仅要检查视图引用的表是否存在，是否有效，而且还要验证对数据的修改是否违的表是否存在，是否有效，而且还要验证对数据的修改是否违反了数据的完整性约束。反了数据的完整性约束。 SQL Server 2008项目实训教程项目实训教程视图的管理视图的管理 u修