第7章 索引与视图.ppt

1、1,第7章 索引与视图,2,本章内容,7.1 索引概述 7.2 索引的操作 7.3 视图概述 7.4 视图的操作 7.5 视图的应用,3,7.1 索引概述,索引的基本概念 索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。 索引是一个表中所包含的值的列表，其中注明了表中包含各个值的行所在的存储位置。 建立索引的目的有以下几点： (1)加速数据检索 (2)加速连接、ORDER BY和GROUP BY等操作 (3)查询优化器依赖于索引起作用 (4)强制实行的惟一性 注意：索引将占用磁盘空间，并且降低添加、删除和修改的速度。在通常情况下，只有对表中经常查询的字段才创建索引。,4,

2、7.1 索引概述,索引的分类 根据数据库的功能，在SQL Server 2005中可创建3种类型的索引，即惟一性索引、主键索引和聚集索引。 1惟一性索引 在表中建立惟一性索引时，组成该索引的字段或字段组合在表中具有惟一值，也就是说，对于表中的任何两行记录来说，索引键的值都是各不相同。 2主键索引 表中通常有一个字段或一些字段组合的，其值用来惟一标识表中的每一行记录，该字段或字段组合称为表的主键。 在数据库关系图中为表定义一个主键时，将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每一个值是唯一的。,5,7.1 索引概述,3聚集索引 在聚集索引中，表中各记录的物理顺序与键值

3、的逻辑(索引)顺序相同。只有在表中建立了一个聚集索引后，数据才会按照索引键值指定的顺序存储到表中。由于一个表中的数据只能按照一种顺序来存储，所以在一个表中只能建立一个聚集索引。,6,7.2 索引的操作,创建索引 一般在创建其他相关对象时就创建了索引，例如在表中定义主键约束或唯一性约束时，也就创建了索引。 1使用SQL Server管理平台创建索引,7,7.2 索引的操作,2使用Transact-SQL语句创建索引 语法格式： CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX index_name ON table_name(column_nameASC|DES

4、C,n) WITH PAD_INDEX , FILLFACTOR=fillfactor , IGNORE_DUP_KEY , DROP_EXISTING , STATISTICS_NORECOMPUTE ON filegroup,8,7.2 索引的操作,例7-1 按employee表的employee_name列建立非聚集索引。 CREATE NONCLUSTERED INDEX name_idx ON employee(employee_name),9,7.2 索引的操作,查看索引 1用SQL Server管理平台查看修改索引 2使用系统存储过程查看索引 语法格式： sp_helpindex

5、 objname=name 其中objname=name子句为指定当前数据库中的表的名称。 例7-2 在查询分析器中查看表employee的索引。 USE Sales GO EXEC sp_helpindex employee GO,10,7.2 索引的操作,编辑索引 1使用SQL Server管理平台编辑索引 2使用系统存储过程更改索引 语法格式： sp_rename OldName,NewName,object_type 例7-3 更改employee表中索引employee_index_1名称为employee_index_id。 USE Sales GO EXEC sp_rename

6、employee.employee_index_1,employee_index_id,index,11,7.2 索引的操作,删除索引 索引会减慢INSERT、UPDATE和DELETE语句的执行速度。如果发现索引阻碍整体性能或不再需要索引，则可将其删除。 1使用SQL Server管理平台删除索引,12,7.2 索引的操作,2使用Transact-SQL语句删除索引 语法格式： DROP INDEX table_name.index_name ,.n 各选项的含义： (1)table_name：索引列所在的表。 (2)index_name：要删除的索引名称。索引名必须符合标识符的规则。,13

7、,7.2 索引的操作,例7-4 删除employee表内名为employee_index_name的索引。 USE Sales IF EXISTS (SELECT name FROM sysindexes WHERE name = employee_index_name) DROP INDEX employee.employee_index_name GO,14,7.3 视图概述,视图的概念 视图是一个虚拟表，并不表示任何物理数据，而只是用来查看数据的窗口而已。 数据库中只存放视图的定义，而不存放视图对应的数据，这些数据让存放在导出视图的基础表中。 对视图的操作与对表的操作一样，可以对其进行查

8、询、修改和删除，但对数据的操作要满足一定的条件。 对视图所引用的基础表来说，视图的作用类似于筛选。,15,7.3 视图概述,视图的作用 视图通常用来集中、简化和自定义每个用户对数据库的不同认识。视图可用作安全机制，方法是允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。 (1)简化操作 (2)定制数据 (3)导出数据 (4)安全性,16,7.3 视图概述,视图的限制 在创建或使用视图时，应遵守以下规定： (1)在一个批事务中，CREATE VIEW语句不能与其他SQL语句结合使用。 (2)不能在视图上建立触发器和索引。 (3)一个视图最多只能有250列。 (4)不能基于临时表建立

9、视图，由SELECT INTO语句可建立临时表，在CREATE VIEW语句中不能使用INTO关键字。 (5)在CREATE VIEW语句中不能使用联合操作符UNION。 (6)在插入或修改数据时，每个INSERT语句和UPDATE语句不能影响一个以上的视图基表。 (7)对视图中的计算列不允许使用INSERT语句插入数据。 (8)UPDATE语句不能够修改视图的计算列数据，也不允许它修改包含集合的函数和内置函数的视图列。 (9)在视图的Text和Image数据类型的列上不允许使用READTEXT和WRITETEXT语句。,17,7.4 视图的操作,创建视图 1.使用SQL Server管理平台

10、创建视图 2. 使用Transact-SQL语句建立视图 语法结构： CREATE VIEW (,) AS WITH CHECK OPTION;,18,说明：,WITH CHECK OPTION 表示对视图进行更新、插入或删除的行满足视图定义中的谓词条件 组成视图的属性列名或者全部省略，或者全部指定。或省略则表示视图属性列由子查询SELECT子句目标列中的字段组成。 以下三种情况必须明确指定视图的列名： 1)、目标列是集函数或列表达式而非单纯的属性名 2)、多表连接时出现同名列作为视图的字段 3)、在视图中为某个列指定新的更合适的名字,19,举例：,例一：建立计算机系学生的视图 CREATE

11、VIEW IS_STUDENT AS SELECT Sno,Sname,year(getdate()-year(birthday) as sage FROM Student WHERE departmno=(select departmno from department where departmname=计算机系); 例二：建立计算机系学生视图，要求进行修改和插入操作时仍需保证该视图只有计算机系学生。 CREATE VIEW IS_STUDENT AS SELECT Sno,Sname, Sno,Sname,year(getdate()-year(birthday) as sage FRO

12、M Student WHERE departmno=(select departmno from department where departmname=计算机系) WITH CHECK OPTION;,20,举例：,例三：建立计算机系选修了C1号课程的学生的视图 CREATE VIEW IS_S1(Sno,Sname,Grade) AS SELECT Student.Sno,Sname,Grade FROM Student,SC WHERE departmno=(select departmno from department where departmname=计算机系) AND Stu

13、dent.Sno=SC.Sno AND SC.Cno=C1; 例四：建立计算机系选修了C1号课程且成绩在90分以上的学生的视图 CREATE VIEW IS_S2 AS SELECT Sno,Sname,Grade FROM IS_S1 WHERE Grade=90;,21,举例,例五：定义一个反映学生年龄的视图 CREATE VIEW BT_S(Sno,Sname,age) AS SELECT Sno,Sname,year(getdate()-year(birthday) FROM Student;,22,7.4 视图的操作,例7-6 在Sales数据库中创建sell_view视图，该视图选

14、择3个基表(employee,goods,sell_order)中的数据来显示员工销售货物情况的虚拟表。 CREATE VIEW sell_view AS SELECT employee.employee_name, employee.employee_id, sell_order.order_num, sell_order.discount, goods.goods_name, goods.unit_price , sell_order.order_date FROM employee INNER JOIN sell_order ON employee.employee_id = sell_

15、order.employee_id INNER JOIN goods ON sell_order.goods_id = goods.goods_id,23,7.4 视图的操作,修改视图 1使用SQL Server管理平台修改视图 2使用Transact-SQL语句修改视图 可以使用ALTER VIEW语句来修改视图，其语法格式如下： ALTER VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA

16、 ,24,7.4 视图的操作,删除视图 1. 使用SQL Server管理平台删除视图 2使用Transact-SQL语句删除视图 可以使用DROP VIEW语句来删除视图，其语法格式如下： DROP VIEW ； 可以删除多个视图。 例如：删除视图IS_S1 DROP VIEW IS_S1; 例7-7 删除view1视图。 USE Sales IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = view1) DROP VIEW view1 GO,25,7.4 视图的操作,查看和修改视图属性

17、 1用SQL Server管理平台查看视图属性 2使用系统存储过程sp_helptext查看视图 语法格式： sp_helptext objname = name 3使用系统存储过程重命名视图 语法格式： sp_rename objname = object_name , newname = new_name , objtype = object_type ,26,7.5 视图的应用,通过视图检索表数据 在建立视图后，可以用任一种查询方式检索视图数据，对视图可使用连接、GROUP BY子句、子查询等以及它们的任意组合。 例7-8 查询例7-6所创建的视图sell_view中的姓名为钱达理的员工

18、所销售的商品名称。 SELECT goods_name FROM sell_view WHERE employee_name=钱达理 ORDER BY order_date,27,查询视图,例一：在计算机系学生的视图中找出年龄小于20岁的学生 SELECT Sno,Sage FROM IS_IStudent WHERE Sage20; 该查询等价于： SELECT Sno,Sage FROM Student WHERE departmno=(select departmno from department where departmname=计算机系) AND Sage20;,28,7.5 视

19、图的应用,通过视图添加表数据 语法格式： INSERT INTO 视图名 VALUES(列值1，列值2，列值3，列值n) 例7-9 在goods表中建立一个视图，利用视图插入一行数据。 CREATE VIEW goods_view AS SELECT goods_id，goods_name，classification_id，supplier_id，unit_price，stock_quantity FROM goods WHERE (classification_id = P001) INSERT INTO goods_view VALUES (G0008,SONY DVD,P002, S001，400,10),29,7.5 视图的应用,通过视图修改表数据 语法格式： UPDATE 视图名 SET 列1=列值1 列2=列值2 . 列n=列值n WHERE 逻辑表达式,30,更新视图,例一：将计算机系学生视图IS_Student中的学号为1