《索引与视图》PPT课件.ppt

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 2000中可创建3种类型的索引，即惟一性索引、主键索引和聚集索引。 1惟一性索引 在表中建立惟一性索引时，组成该索引的字段或字段组合在表中具有惟一值，也就是说，对于表中的任何两行记录来说，索引键的值都是各不相同。 2主键索引 表中通常有一个字段或一些字段组的合，其值用来惟一标识表中的每一行记录，该字段或字段组合称为表的主键。,6,7.1 索引概述,3聚集索引 在聚集索引中，表中各记录的物理顺序与键值的逻辑(索引)顺序相同。只有在表中建立了一个聚集索引后，数据才会按照索引键值指定的顺序存储到表中。由于一个表中的数据只能按照一种顺序来存储，所以在一个表中只能建立一个聚集索引。,7,7.2 索引的操作,创建索引 1使用企业管理器向导建立索引 2使用企业管理器直接创建索引,演示操作,8,7.2 索引的操作,3使用Transact-SQL语句创建索引 语法格式： CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX index_name ON table_name(column_nameASC|DESC,n) WITH PAD_INDEX , FILLFACTOR=fillfactor , IGNORE_DUP_KEY , DROP_EXISTING , STATISTICS_NORECOMPUTE ON filegroup,9,7.2 索引的操作,例7-1 按employee表的employee_name列建立非聚集索引。 CREATE NONCLUSTERED INDEX name_idx ON employee(employee_name),10,7.2 索引的操作,查看索引 1使用企业管理器查看索引 2使用系统存储过程查看索引 语法格式： sp_helpindex objname=name 其中objname=name子句为指定当前数据库中的表的名称。 例7-2 在查询分析器中查看表employee的索引。 USE Sales GO EXEC sp_helpindex employee GO,演示操作,11,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 employee.employee_index_1,employee_index_id,index,演示操作,12,7.2 索引的操作,删除索引 索引会减慢INSERT、UPDATE和DELETE语句的执行速度。如果发现索引阻碍整体性能或不再需要索引，则可将其删除。 1使用企业管理器删除索引,演示操作,13,7.2 索引的操作,2使用Transact-SQL语句删除索引 语法格式： DROP INDEX table_name.index_name ,.n 各选项的含义： (1)table_name：索引列所在的表。 (2)index_name：要删除的索引名称。索引名必须符合标识符的规则。,14,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,15,7.2 索引的操作,全文索引 前面所讲的索引通常是建立在数值字段或较短的字符串字段上的，一般不会选择大的字段上作为索引字段。如果需要使用大的字符串字段来检索数据，则需要使用SQL Server所提供的全文索引(Full-Text Index)功能。,16,7.2 索引的操作,1全文索引概述,表7-1 全文索引和普通索引的区别,17,7.2 索引的操作,全文索引 前面所讲的索引通常是建立在数值字段或较短的字符串字段上的，一般不会选择大的字段上作为索引字段。如果需要使用大的字符串字段来检索数据，则需要使用SQL Server所提供的全文索引(Full-Text Index)功能。,18,7.2 索引的操作,全文管理实施的几个层次： (1)服务器。可以对服务器范围的某些属性(如resource_usage)加以设置，以便增加或减少全文服务所使用的系统资源数量。 (2)数据库。必须启用数据库才能使用全文服务。可以在已启用的数据库中创建和删除一个或多个全文目录的元数据。 (3)全文目录。全文目录包含数据库中的全文索引。每个目录可以用于数据库内的一个或多个表的索引需求。在每个服务器上最多可创建256个全文目录。 (4)表 (5)列。可以从非活动的注册表中添加或删除支持全文查询的列。,19,7.2 索引的操作,2启动Microsoft Search服务 启动方法有两种： (1)在Windows的“组件服务”窗口启动Microsoft Search服务 (2)在SQL Server 2000企业管理器中启动Microsoft Search服务,20,7.2 索引的操作,3建立全文目录 全文目录是存放全文索引的地方，其中记录着数据库中设置有全文索引的字段以及更新计划。一个数据库可以有多个全文目录，但全文目录并不是存储在这个数据库中，而是存放在指定的磁盘文件中。,演示操作建立全文目录,21,7.2 索引的操作,4建立全文索引 在一个数据库中建立了全文目录后，就可以在数据库中选择一个数据表来建立全文索引了。在一个全文目录中可以存储多个全文索引，但在一个表中只能建立一个全文索引。,演示操作建立全文索引,22,7.2 索引的操作,5管理全文索引 在数据库表中建立全文索引后，可以在企业管理器中对这个全文索引进行管理，主要有修改、删除和启动填充等。 (1)修改全文索引 (2)删除全文索引 (3)填充全文索引,23,7.2 索引的操作,6.使用全文索引 在一个表中建立全文索引并且填充索引键值以后，就可以使用SELECT语句从这个表中检索数据了。与普通的选择查询不同的是，对一个表进行全文查询时，需要在WHERE子句中使用CONTAINS和FREETEXT这样两个谓词。 (1)使用CONTAINS进行全文查询 语法格式： SELECT 字段列表 FROM 表名 WHERE CONTAINS(字段名|*,搜索条件),24,7.2 索引的操作,例7-5 在Sales数据库中执行下列全文查询，从department表中搜索depart_description字段或department_name字段中包含“市场”一词的记录，写出所使用的SELECT语句。 USE Sales SELECT * FROM department WHERE CONTAINS(*,市场),25,7.2 索引的操作,(2)使用FREETEXT进行全文查询 使用FREETEXT进行全文查询时，全文查询引擎将对指定的项目建立一个内部查询，可以从表中搜索一组单词或短语甚至完整的句子。 语法格式： SELECT 字段列表 FROM 表名 WHERE FREETEXT(字段名|*,自由文本),26,7.3 视图概述,视图的概念 视图是一个虚拟表，并不表示任何物理数据，而只是用来查看数据的窗口而已。 对视图的操作与对表的操作一样，可以对其进行查询、修改和删除，但对数据的操作要满足一定的条件。 对视图所引用的基础表来说，视图的作用类似于筛选。,27,7.3 视图概述,视图的作用 视图通常用来集中、简化和自定义每个用户对数据库的不同认识。视图可用作安全机制，方法是允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。 (1)简化操作 (2)定制数据 (3)导出数据 (4)安全性,28,7.3 视图概述,视图的限制 在创建或使用视图时，应遵守以下规定： (1)在一个批事务中，CREATE VIEW语句不能与其他SQL语句结合使用。 (2)不能在视图上建立触发器和索引。 (3)一个视图最多只能有250列。 (4)不能基于临时表建立视图，由SELECT INTO语句可建立临时表，在CREATE VIEW语句中不能使用INTO关键字。 (5)在CREATE VIEW语句中不能使用联合操作符UNION。 (6)在插入或修改数据时，每个INSERT语句和UPDATE语句不能影响一个以上的视图基表。 (7)对视图中的计算列不允许使用INSERT语句插入数据。 (8)UPDATE语句不能够修改视图的计算列数据，也不允许它修改包含集合的函数和内置函数的视图列。 (9)在视图的Text和Image数据类型的列上不允许使用READTEXT和WRITETEXT语句。,29,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,30,7.4 视图的操作,例7-6 在Sales数据库中创建sell_view视图，该视图选择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_order.employee_id INNER JOIN goods ON sell_order.goods_id = goods.goods_id,31,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 ,32,7.4 视图的操作,删除视图 1. 使用企业管理器删除视图 2使用Transact-SQL语句删除视图 可以使用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,33,7.4 视图的操作,查看和修改视图属性 1. 用企业管理器查看视图属性 2. 使用系统存储过程sp_helptext查看视图 语法格式： sp_helptext objname = name 3. 使用系统存储过程重命名视图 语法格式： sp_rename objname = object_name , newname = new_name , objtype = object_type ,34,7.5 视图的应用,通过视图检索表数据 在建立视图后，可以用任一种查询方式检索视图数据，对视图可使用连接、GROUP BY子句、子查询等以及它们的任意组合。 例7-8 查询例7-6所创建的视图sell_view中的姓名为钱达理的员工所销售的商品名称。 SELECT goods_name FROM sell_view WHERE employee_name=钱达理 ORDER BY order_date,35,7.5 视图的应用,通过视图添加表数据 语法格式： INSERT INTO 视图名 VALUES(列值1，列值2，列值3，列值n) 例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 VALU