SQL server 索引与视图.doc

第7章 索引与视图本章内容n 7.1 索引概述n 7.2 索引的操作n 7.3 视图概述n 7.4 视图的操作n 7.5 视图的应用7.1 索引概述n 索引的基本概念索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。7.1 索引概述n 索引的基本概念索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。建立索引的目的有以下几点：(1)加速数据检索(2)加速连接、ORDER BY和GROUP BY等操作(3)查询优化器依赖于索引起作用(4)强制实行的惟一性7.1 索引概述n 索引的分类根据数据库的功能，在SQL Server 2000中可创建3种类型的索引，即惟一性索引、主键索引和聚集索引。1惟一性索引在表中建立惟一性索引时，组成该索引的字段或字段组合在表中具有惟一值，也就是说，对于表中的任何两行记录来说，索引键的值都是各不相同。 2主键索引表中通常有一个字段或一些字段组的合，其值用来惟一标识表中的每一行记录，该字段或字段组合称为表的主键。 7.1 索引概述3聚集索引在聚集索引中，表中各记录的物理顺序与键值的逻辑(索引)顺序相同。只有在表中建立了一个聚集索引后，数据才会按照索引键值指定的顺序存储到表中。由于一个表中的数据只能按照一种顺序来存储，所以在一个表中只能建立一个聚集索引。 7.2 索引的操作n 创建索引 1使用企业管理器向导建立索引2使用企业管理器直接创建索引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 filegroup7.2 索引的操作例7-1 按employee表的employee_name列建立非聚集索引。CREATE NONCLUSTERED INDEX name_idx ON employee(employee_name)7.2 索引的操作n 查看索引 1使用企业管理器查看索引2使用系统存储过程查看索引语法格式：sp_helpindex objname=name其中objname=name子句为指定当前数据库中的表的名称。例7-2 在查询分析器中查看表employee的索引。USE SalesGOEXEC sp_helpindex employeeGO7.2 索引的操作n 编辑索引1使用企业管理器编辑索引2使用系统存储过程更改索引语法格式：sp_rename OldName,NewName,object_type例7-3 更改employee表中索引employee_index_1名称为employee_index_id。USE SalesGOEXEC sp_rename employee.employee_index_1,employee_index_id,index7.2 索引的操作n 删除索引索引会减慢INSERT、UPDATE和DELETE语句的执行速度。如果发现索引阻碍整体性能或不再需要索引，则可将其删除。1使用企业管理器删除索引7.2 索引的操作2使用Transact-SQL语句删除索引语法格式：DROP INDEX table_name.index_name ,.n 各选项的含义：(1)table_name：索引列所在的表。(2)index_name：要删除的索引名称。索引名必须符合标识符的规则。7.2 索引的操作例7-4 删除employee表内名为employee_index_2的索引。USE SalesIF EXISTS (SELECT name FROM sysindexes WHERE name = employee_index_2) DROP INDEX employee.employee_index_2GO7.2 索引的操作n 全文索引前面所讲的索引通常是建立在数值字段或较短的字符串字段上的，一般不会选择大的字段上作为索引字段。如果需要使用大的字符串字段来检索数据，则需要使用SQL Server所提供的全文索引(Full-Text Index)功能。7.2 索引的操作7.2 索引的操作n 全文索引前面所讲的索引通常是建立在数值字段或较短的字符串字段上的，一般不会选择大的字段上作为索引字段。如果需要使用大的字符串字段来检索数据，则需要使用SQL Server所提供的全文索引(Full-Text Index)功能。7.2 索引的操作全文管理实施的几个层次：(1)服务器。可以对服务器范围的某些属性(如resource_usage)加以设置，以便增加或减少全文服务所使用的系统资源数量。(2)数据库。必须启用数据库才能使用全文服务。可以在已启用的数据库中创建和删除一个或多个全文目录的元数据。(3)全文目录。全文目录包含数据库中的全文索引。每个目录可以用于数据库内的一个或多个表的索引需求。在每个服务器上最多可创建256个全文目录。(4)表 (5)列。可以从非活动的注册表中添加或删除支持全文查询的列。7.2 索引的操作2启动Microsoft Search服务启动方法有两种：(1)在Windows的“组件服务”窗口启动Microsoft Search服务(2)在SQL Server 2000企业管理器中启动Microsoft Search服务7.2 索引的操作3建立全文目录全文目录是存放全文索引的地方，其中记录着数据库中设置有全文索引的字段以及更新计划。一个数据库可以有多个全文目录，但全文目录并不是存储在这个数据库中，而是存放在指定的磁盘文件中。7.2 索引的操作4建立全文索引在一个数据库中建立了全文目录后，就可以在数据库中选择一个数据表来建立全文索引了。在一个全文目录中可以存储多个全文索引，但在一个表中只能建立一个全文索引。7.2 索引的操作5管理全文索引在数据库表中建立全文索引后，可以在企业管理器中对这个全文索引进行管理，主要有修改、删除和启动填充等。(1)修改全文索引(2)删除全文索引(3)填充全文索引7.2 索引的操作6.使用全文索引在一个表中建立全文索引并且填充索引键值以后，就可以使用SELECT语句从这个表中检索数据了。与普通的选择查询不同的是，对一个表进行全文查询时，需要在WHERE子句中使用CONTAINS和FREETEXT这样两个谓词。(1)使用CONTAINS进行全文查询语法格式：SELECT 字段列表FROM 表名WHERE CONTAINS(字段名|*,搜索条件)7.2 索引的操作例7-5 在Sales数据库中执行下列全文查询，从department表中搜索depart_description字段或department_name字段中包含“市场”一词的记录，写出所使用的SELECT语句。USE SalesSELECT * FROM department WHERE CONTAINS(*,市场)7.2 索引的操作(2)使用FREETEXT进行全文查询使用FREETEXT进行全文查询时，全文查询引擎将对指定的项目建立一个内部查询，可以从表中搜索一组单词或短语甚至完整的句子。语法格式：SELECT 字段列表FROM 表名WHERE FREETEXT(字段名|*,自由文本)7.3 视图概述n 视图的概念视图是一个虚拟表，并不表示任何物理数据，而只是用来查看数据的窗口而已。对视图的操作与对表的操作一样，可以对其进行查询、修改和删除，但对数据的操作要满足一定的条件。对视图所引用的基础表来说，视图的作用类似于筛选。7.3 视图概述n 视图的作用视图通常用来集中、简化和自定义每个用户对数据库的不同认识。视图可用作安全机制，方法是允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。 (1)简化操作(2)定制数据(3)导出数据(4)安全性7.3 视图概述n 视图的限制在创建或使用视图时，应遵守以下规定：(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语句。7.4 视图的操作n 创建视图1. 使用企业管理器创建视图2. 使用Transact-SQL语句建立视图语法结构：CREATE VIEW database_name.owner_name.view_name(column,) WITH ENCRYPTION|SCHEMABINDING|VIEW_METADATAASselect_statementWITH CHECK OPTION7.4 视图的操作例7-6 在Sales数据库中创建sell_view视图，该视图选择3个基表(employee,goods,sell_order)中的数据来显示员工销售货物情况的虚拟表。CREATE VIEW sell_viewASSELECT employee.employee_name, employee.employee_id, sell_order.order_num, sell_order.discount, goods.goods_name, goods.unit_price , sell_order.order_dateFROM employee INNER JOIN sell_order ON employee.employee_id = sell_order.employee_id INNER JOINgoods ON sell_order.goods_id = goods.goods_id 7.4 视图的操作n 修改视图1使用企业管理器修改视图2使用Transact-SQL语句修改视图可以使用ALTER VIEW语句来修改视图，其语法格式如下：ALTER VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 7.4 视图的操作n 删除视图1. 使用企业管理器删除视图2使用Transact-SQL语句删除视图可以使用DROP VIEW语句来删除视图，其语法格式如下：DROP VIEW view_name ,.n 其中View_name是要删除的视图名称，可以删除多个视图。例7-7 删除view1视图。USE SalesIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = view1) DROP VIEW view1GO7.4 视图的操作n 查看和修改视图属性1. 用企业管理器查看视图属性2. 使用系统存储过程sp_helptext查看视图语法格式：sp_helptext objname = name3. 使用系统存储过程重命名视图语法格式：sp_rename objname = object_name , newname = new_name , objtype = object_type 7.5 视图的应用n 通过视图检索表数据在建立视图后，可以用任一种查询方式检索视图数据，对视图可使用连接、GROUP BY子句、子查询等以及它们的任意组合。例7-8 查询例7-6所创建的视图sell_view中的姓名为钱达理的员工所销售的商品名称。 SELECT goods_name FROM sell_view WHERE employee_name=钱达理ORDER BY order_date 7.5 视图的应用n 通过视图添加表数据语法格式：INSERT INTO 视图名 VALUES(列值1，列值2，列值3，列值n)例7-9 在goods表中建立一个视图，利用视图插入一行数据。CREATE VIEW goods_viewAS SELECT goods_id, goods_name, classification_id, unit_price, stock_quantityFROM goods WHERE (classification_id = P001)INSERT INTO goods_viewVALUES (G0