第7章索引与视图

1、1,第7章 索引与视图,2,本章内容,7.1 索引概述 7.2 索引的操作 7.3 视图概述 7.4 视图的操作 7.5 视图的应用,3,7.1 索引概述,索引的基本概念 索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。,4,7.1 索引概述,索引的基本概念 索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。 建立索引的目的有以下几点： (1)加速数据检索 (2)加速连接、ORDER BY和GROUP BY等操作 (3)查询优化器依赖于索引起作用 (4)强制实行的惟一性,5,7.1 索引概述,索引的分类 根据数据库的功能，在SQL Server 200

2、0中可创建3种类型的索引，即惟一性索引、主键索引和聚集索引。 1惟一性索引 在表中建立惟一性索引时，组成该索引的字段或字段组合在表中具有惟一值，也就是说，对于表中的任何两行记录来说，索引键的值都是各不相同。 2主键索引 表中通常有一个字段或一些字段组的合，其值用来惟一标识表中的每一行记录，该字段或字段组合称为表的主键。,6,7.1 索引概述,3聚集索引 在聚集索引中，表中各记录的物理顺序与键值的逻辑(索引)顺序相同。只有在表中建立了一个聚集索引后，数据才会按照索引键值指定的顺序存储到表中。由于一个表中的数据只能按照一种顺序来存储，所以在一个表中只能建立一个聚集索引。,7,7.1 索引概述,3聚

3、集索引 优点：查询速度快，因为一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后。 缺点：对表进行修改的速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。,8,7.2 索引的操作,创建索引 1使用企业管理器向导建立索引 2使用企业管理器直接创建索引,演示操作,9,7.2 索引的操作,3使用Transact-SQL语句创建索引 语法格式： CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX index_name ON table_name(column_n

4、ameASC|DESC,n) WITH PAD_INDEX , FILLFACTOR=fillfactor , IGNORE_DUP_KEY , DROP_EXISTING , STATISTICS_NORECOMPUTE ON filegroup,10,7.2 索引的操作,例7-1 按employee表的employee_name列建立非聚集索引。 CREATE NONCLUSTERED INDEX name_idx ON employee(employee_name),11,7.2 索引的操作,查看索引 1使用企业管理器查看索引 2使用系统存储过程查看索引 语法格式： sp_helpind

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

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

7、7.2 索引的操作,例7-4 删除employee表内名为employee_index_2的索引。 USE Sales IF EXISTS (SELECT name FROM sysindexes WHERE name = employee_index_2) DROP INDEX employee.employee_index_2 GO,16,7.2 索引的操作,删除索引的注意事项 用Create Index创建的索引才可以用Drop index语句删除 删除一个表的时候，该表的所有索引被删除 不能删除由PK或UNIQUE约束创建的索引，如果要删除该索引，必须先删除约束,17,7.3 视图概述

8、,视图的概念 视图是一个虚拟表，并不表示任何物理数据，而只是用来查看数据的窗口而已。 对视图的操作与对表的操作一样，可以对其进行查询、修改和删除，但对数据的操作要满足一定的条件。 对视图所引用的基础表来说，视图的作用类似于筛选。,18,7.3 视图概述,视图的作用 视图通常用来集中、简化和自定义每个用户对数据库的不同认识。视图可用作安全机制，方法是允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。 (1)简化操作 (2)定制数据 (3)导出数据 (4)安全性,19,7.3 视图概述,视图的限制 在创建或使用视图时，应遵守以下规定： (1)在一个批事务中，CREATE VIEW语句

9、不能与其他SQL语句结合使用。 (2)不能在视图上建立触发器和索引。 (3)一个视图最多只能有250列。 (4)不能基于临时表建立视图，由SELECT INTO语句可建立临时表，在CREATE VIEW语句中不能使用INTO关键字。 (5)在CREATE VIEW语句中不能使用联合操作符UNION。 (6)在插入或修改数据时，每个INSERT语句和UPDATE语句不能影响一个以上的视图基表。 (7)对视图中的计算列不允许使用INSERT语句插入数据。 (8)UPDATE语句不能够修改视图的计算列数据，也不允许它修改包含集合的函数和内置函数的视图列。 (9)在视图的Text和Image数据类型的

10、列上不允许使用READTEXT和WRITETEXT语句。,20,7.4 视图的操作,创建视图 1. 使用企业管理器创建视图 2. 使用Transact-SQL语句建立视图 语法结构： CREATE VIEW database_name.owner_name.view_name(column,) WITH ENCRYPTION|SCHEMABINDING|VIEW_METADATA AS select_statement WITH CHECK OPTION,21,7.4 视图的操作,例7-6 在Sales数据库中创建sell_view视图，该视图选择3个基表(employee,goods,sel

11、l_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 , sell_order ,goods where employee.employee_id = sell_order.employee_id and s

12、ell_order.goods_id = goods.goods_id,22,7.4 视图的操作,修改视图 1使用企业管理器修改视图 2使用Transact-SQL语句修改视图 可以使用ALTER VIEW语句来修改视图，其语法格式如下： ALTER VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA ,23,7.4 视图的操作,删除视图 1. 使用企业管理器删除视图 2使用Transact-S

13、QL语句删除视图 可以使用DROP VIEW语句来删除视图，其语法格式如下： DROP VIEW view_name ,.n 其中View_name是要删除的视图名称，可以删除多个视图。 例7-7 删除view1视图。 USE Sales IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = view1) DROP VIEW view1 GO,24,7.4 视图的操作,查看和修改视图属性 1. 用企业管理器查看视图属性 2. 使用系统存储过程sp_helptext查看视图 语法格式： sp_h

14、elptext objname = name 3. 使用系统存储过程重命名视图 语法格式： sp_rename objname = object_name , newname = new_name , objtype = object_type ,25,7.5 视图的应用,通过视图检索表数据 在建立视图后，可以用任一种查询方式检索视图数据，对视图可使用连接、GROUP BY子句、子查询等以及它们的任意组合。 例7-8 查询例7-6所创建的视图sell_view中的姓名为钱达理的员工所销售的商品名称。 SELECT goods_name FROM sell_view WHERE employee

15、_name=钱达理 ORDER BY order_date,26,7.5 视图的应用,通过视图检索表数据 通过视图检索数据时，如果视图所参照的基本表不存在，将会导致执行错误 如果使用select *子句创建视图，如果在基础表中增加新的列，那么这些新增的列将不会出现在视图中,27,7.5 视图的应用,通过视图添加表数据 语法格式： INSERT INTO 视图名 VALUES(列值1，列值2，列值3，列值n) 插入视图的列值个数、数据类型应该和视图定义的列数、基础表对应的数据类型一致 如果视图的定义只包含基础表的部分列，并且基础表的其余列至少有一列不允许为空，且该列没有设置默认值，也未设置系统自

16、动赋值，那么此时无法给视图中未出现的列赋值,28,7.5 视图的应用,通过视图添加表数据 例7-9 在goods表中建立一个视图，利用视图插入一行数据。 CREATE VIEW goods_view AS SELECT goods_id, goods_name, classification_id, unit_price, stock_quantity FROM goods WHERE classification_id = P001 INSERT INTO goods_view VALUES (G0008,SONY DVD,P002, 400,10),29,7.5 视图的应用,通过视图修改表

17、数据 语法格式： UPDATE 视图名 SET 列1=列值1 列2=列值2 . 列n=列值n WHERE 逻辑表达式,30,7.5 视图的应用,通过视图修改表数据 如果视图包含多个基础表，那么一次只能更新一个基础表的数据 例 update vexample2 set name=aa where id=001 update vexample2 set name=aaa, dname=工商1 where id=001,31,7.5 视图的应用,通过视图删除表数据 尽管视图不一定包含基础表的所有列，但可以通过视图删除基础表的数据行。 语法格式： DELETE FROM 视图名 WHERE 逻辑表达式,32,7.5 视图的应用,通过视图删除表数据 要删除的数据必须包含在视图中 例 Delete from goods_view where classification_id = P002 删除条件中制定的列必须是在视图中定义的列 例 Delete goods_view where supplier_id=s001,33,本章小结,(1)索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。建立索引的主要目的是加速数据检索和连接、优化