《《索引与视图》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《索引与视图》PPT课件.ppt（39页珍藏版）》请在金锄头文库上搜索。
1、8/20/20241第第7章章 索引与视图索引与视图18/20/20242本章内容本章内容n7.1 索引概述索引概述n7.2 索引的操作索引的操作n7.3 视图概述视图概述n7.4 视图的操作视图的操作n7.5 视图的应用视图的应用28/20/202437.1 索引概述索引概述n索引的基本概念索引的基本概念索引是对数据库表中一个或多个字段的值进行排序而创索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。建的一种分散存储结构。38/20/202447.1 索引概述索引概述n索引的基本概念索引的基本概念索引是对数据库表中一个或多个字段的值进行排序而创索引是对数据库表中一个或多个
2、字段的值进行排序而创建的一种分散存储结构。建的一种分散存储结构。建立索引的目的有以下几点：建立索引的目的有以下几点：(1)加速数据检索加速数据检索(2)加速连接、加速连接、ORDER BY和和GROUP BY等操作等操作(3)查询优化器依赖于索引起作用查询优化器依赖于索引起作用(4)强制实行的惟一性强制实行的惟一性48/20/202457.1 索引概述索引概述n索引的分类索引的分类根据数据库的功能，在根据数据库的功能，在SQL Server 2000中可创建中可创建3种类型种类型的索引，即惟一性索引、主键索引和聚集索引。的索引，即惟一性索引、主键索引和聚集索引。1惟一性索引惟一性索引在表中建立
3、惟一性索引时，组成该索引的字段或字段组在表中建立惟一性索引时，组成该索引的字段或字段组合在表中具有惟一值，也就是说，对于表中的任何两行记录合在表中具有惟一值，也就是说，对于表中的任何两行记录来说，索引键的值都是各不相同。来说，索引键的值都是各不相同。 2主键索引主键索引表中通常有一个字段或一些字段组的合，其值用来惟一表中通常有一个字段或一些字段组的合，其值用来惟一标识表中的每一行记录，该字段或字段组合称为表的主键。标识表中的每一行记录，该字段或字段组合称为表的主键。 58/20/202467.1 索引概述索引概述3聚集索引聚集索引在聚集索引中，表中各记录的物理顺序与键值的逻辑在聚集索引中，表中
4、各记录的物理顺序与键值的逻辑(索索引引)顺序相同。只有在表中建立了一个聚集索引后，数据才顺序相同。只有在表中建立了一个聚集索引后，数据才会按照索引键值指定的顺序存储到表中。由于一个表中的数会按照索引键值指定的顺序存储到表中。由于一个表中的数据只能按照一种顺序来存储，所以在一个表中只能建立一个据只能按照一种顺序来存储，所以在一个表中只能建立一个聚集索引。聚集索引。 68/20/202477.2 索引的操作索引的操作n创建索引创建索引 1使用企业管理器向导建立索引使用企业管理器向导建立索引2使用企业管理器直接创建索引使用企业管理器直接创建索引78/20/202487.2 索引的操作索引的操作3使用
5、使用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 filegroup88/20/202497.2 索引的操作索引的操作例例7-1 按按employee表的表的employee_name列建立非聚集索
6、引。列建立非聚集索引。CREATE NONCLUSTERED INDEX name_idx ON employee(employee_name)98/20/2024107.2 索引的操作索引的操作n查看索引查看索引 1使用企业管理器查看索引使用企业管理器查看索引2使用系统存储过程查看索引使用系统存储过程查看索引语法格式：语法格式：sp_helpindex objname=name其中其中objname=name子句为指定当前数据库中的表的名称。子句为指定当前数据库中的表的名称。例例7-2 在查询分析器中查看表在查询分析器中查看表employee的索引。的索引。USE SalesGOEXEC s
7、p_helpindex employeeGO108/20/2024117.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,index118/20
8、/2024127.2 索引的操作索引的操作n 删除索引删除索引索引会减慢索引会减慢INSERT、UPDATE和和DELETE语句的执行速语句的执行速度。如果发现索引阻碍整体性能或不再需要索引，则可将其度。如果发现索引阻碍整体性能或不再需要索引，则可将其删除。删除。1使用企业管理器删除索引使用企业管理器删除索引128/20/2024137.2 索引的操作索引的操作2使用使用Transact-SQL语句删除索引语句删除索引语法格式：语法格式：DROP INDEX table_name.index_name ,.n 各选项的含义：各选项的含义：(1)table_name：索引列所在的表。：索引列所在
9、的表。(2)index_name：要删除的索引名称。索引名必须符合标：要删除的索引名称。索引名必须符合标识符的规则。识符的规则。138/20/2024147.2 索引的操作索引的操作例例7-4 删除删除employee表内名为表内名为employee_index_2的索引。的索引。USE SalesIF EXISTS (SELECT name FROM sysindexes WHERE name = employee_index_2) DROP INDEX employee.employee_index_2GO148/20/2024157.2 索引的操作索引的操作n全文索引全文索引前面所讲的索
10、引通常是建立在数值字段或较短的字符串前面所讲的索引通常是建立在数值字段或较短的字符串字段上的，一般不会选择大的字段上作为索引字段。如果需字段上的，一般不会选择大的字段上作为索引字段。如果需要使用大的字符串字段来检索数据，则需要使用要使用大的字符串字段来检索数据，则需要使用SQL Server所提供的全文索引所提供的全文索引(Full-Text Index)功能。功能。158/20/2024167.2 索引的操作索引的操作普通索引普通索引全文索引全文索引存存储在数据在数据库中中存存储在文件系在文件系统中中每个表允每个表允许有多个普通索引有多个普通索引每个表只允每个表只允许有一个全文索引有一个全文
11、索引当插入、更新或当插入、更新或删除数据除数据时，SQL Server会自会自动更新索引内容更新索引内容只能通只能通过任任务调度或度或执行存行存储过程程来填充全文索引来填充全文索引普通索引不能分普通索引不能分组在同一个数据在同一个数据库内的多个全文索引内的多个全文索引可以可以组织为一个全文目一个全文目录使用使用SQL Server企企业管理器、向管理器、向导或或Transact-SQL语句句创建和建和删除除使用使用SQL Server企企业管理器、向管理器、向导或存或存储过程程创建、管理和建、管理和删除除1全文索引概述全文索引概述表表7-1 全文索引和普通索引的区别全文索引和普通索引的区别16
12、8/20/2024177.2 索引的操作索引的操作n全文索引全文索引前面所讲的索引通常是建立在数值字段或较短的字符串前面所讲的索引通常是建立在数值字段或较短的字符串字段上的，一般不会选择大的字段上作为索引字段。如果需字段上的，一般不会选择大的字段上作为索引字段。如果需要使用大的字符串字段来检索数据，则需要使用要使用大的字符串字段来检索数据，则需要使用SQL Server所提供的全文索引所提供的全文索引(Full-Text Index)功能。功能。178/20/2024187.2 索引的操作索引的操作全文管理实施的几个层次：全文管理实施的几个层次：(1)服务器。可以对服务器范围的某些属性服务器。
13、可以对服务器范围的某些属性(如如resource_usage)加以设加以设置，以便增加或减少全文服务所使用的系统资源数量。置，以便增加或减少全文服务所使用的系统资源数量。(2)数据库。必须启用数据库才能使用全文服务。可以在已启用的数数据库。必须启用数据库才能使用全文服务。可以在已启用的数据库中创建和删除一个或多个全文目录的元数据。据库中创建和删除一个或多个全文目录的元数据。(3)全文目录。全文目录包含数据库中的全文索引。每个目录可以用全文目录。全文目录包含数据库中的全文索引。每个目录可以用于数据库内的一个或多个表的索引需求。在每个服务器上最多可创建于数据库内的一个或多个表的索引需求。在每个服务
14、器上最多可创建256个全文目录。个全文目录。(4)表表 (5)列。可以从非活动的注册表中添加或删除支持全文查询的列。列。可以从非活动的注册表中添加或删除支持全文查询的列。188/20/2024197.2 索引的操作索引的操作2启动启动Microsoft Search服务服务启动方法有两种：启动方法有两种：(1)在在Windows的的“组件服务组件服务”窗口启动窗口启动Microsoft Search服务服务(2)在在SQL Server 2000企业管理器中启动企业管理器中启动Microsoft Search服务服务198/20/2024207.2 索引的操作索引的操作3建立全文目录建立全文目
15、录全文目录是存放全文索引的地方，其中记录着数据库中全文目录是存放全文索引的地方，其中记录着数据库中设置有全文索引的字段以及更新计划。一个数据库可以有多设置有全文索引的字段以及更新计划。一个数据库可以有多个全文目录，但全文目录并不是存储在这个数据库中，而是个全文目录，但全文目录并不是存储在这个数据库中，而是存放在指定的磁盘文件中。存放在指定的磁盘文件中。208/20/2024217.2 索引的操作索引的操作4建立全文索引建立全文索引在一个数据库中建立了全文目录后，就可以在数据库中在一个数据库中建立了全文目录后，就可以在数据库中选择一个数据表来建立全文索引了。在一个全文目录中可以选择一个数据表来建
16、立全文索引了。在一个全文目录中可以存储多个全文索引，但在一个表中只能建立一个全文索引。存储多个全文索引，但在一个表中只能建立一个全文索引。218/20/2024227.2 索引的操作索引的操作5管理全文索引管理全文索引在数据库表中建立全文索引后，可以在企业管理器中对在数据库表中建立全文索引后，可以在企业管理器中对这个全文索引进行管理，主要有修改、删除和启动填充等。这个全文索引进行管理，主要有修改、删除和启动填充等。(1)修改全文索引修改全文索引(2)删除全文索引删除全文索引(3)填充全文索引填充全文索引228/20/2024237.2 索引的操作索引的操作6.使用全文索引使用全文索引在一个表中
17、建立全文索引并且填充索引键值以后，就可在一个表中建立全文索引并且填充索引键值以后，就可以使用以使用SELECT语句从这个表中检索数据了。与普通的选择语句从这个表中检索数据了。与普通的选择查询不同的是，对一个表进行全文查询时，需要在查询不同的是，对一个表进行全文查询时，需要在WHERE子句中使用子句中使用CONTAINS和和FREETEXT这样两个谓词。这样两个谓词。(1)使用使用CONTAINS进行全文查询进行全文查询语法格式：语法格式：SELECT 字段列表字段列表FROM 表名表名WHERE CONTAINS(字段名字段名|*,搜索条件搜索条件)238/20/2024247.2 索引的操作
18、索引的操作例例7-5 在在Sales数据库中执行下列全文查询，从数据库中执行下列全文查询，从department表中搜索表中搜索depart_description字段或字段或department_name字段中包含字段中包含“市场市场”一词的记录，写出所一词的记录，写出所使用的使用的SELECT语句。语句。USE SalesSELECT * FROM department WHERE CONTAINS(*,市场市场)248/20/2024257.2 索引的操作索引的操作(2)使用使用FREETEXT进行全文查询进行全文查询使用使用FREETEXT进行全文查询时，全文查询引擎将对指进行全文查询
19、时，全文查询引擎将对指定的项目建立一个内部查询，可以从表中搜索一组单词或短定的项目建立一个内部查询，可以从表中搜索一组单词或短语甚至完整的句子。语甚至完整的句子。语法格式：语法格式：SELECT 字段列表字段列表FROM 表名表名WHERE FREETEXT(字段名字段名|*,自由文本自由文本)258/20/2024267.3 视图概述视图概述n视图的概念视图的概念视图是一个虚拟表，并不表示任何物理数据，而只是用视图是一个虚拟表，并不表示任何物理数据，而只是用来查看数据的窗口而已。来查看数据的窗口而已。对视图的操作与对表的操作一样，可以对其进行查询、对视图的操作与对表的操作一样，可以对其进行查
20、询、修改和删除，但对数据的操作要满足一定的条件。修改和删除，但对数据的操作要满足一定的条件。对视图所引用的基础表来说，视图的作用类似于筛选。对视图所引用的基础表来说，视图的作用类似于筛选。268/20/2024277.3 视图概述视图概述n视图的作用视图的作用视图通常用来集中、简化和自定义每个用户对数据库的视图通常用来集中、简化和自定义每个用户对数据库的不同认识。视图可用作安全机制，方法是允许用户通过视图不同认识。视图可用作安全机制，方法是允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。访问数据，而不授予用户直接访问视图基础表的权限。 (1)简化操作简化操作(2)定制数据定制数
21、据(3)导出数据导出数据(4)安全性安全性278/20/2024287.3 视图概述视图概述n 视图的限制视图的限制在创建或使用视图时，应遵守以下规定：在创建或使用视图时，应遵守以下规定：(1)在一个批事务中，在一个批事务中，CREATE VIEW语句不能与其他语句不能与其他SQL语句结合使用。语句结合使用。(2)不能在视图上建立触发器和索引。不能在视图上建立触发器和索引。(3)一个视图最多只能有一个视图最多只能有250列。列。(4)不能基于临时表建立视图，由不能基于临时表建立视图，由SELECT INTO语句可建立临时表，在语句可建立临时表，在CREATE VIEW语句中不能使用语句中不能使
22、用INTO关键字。关键字。(5)在在CREATE VIEW语句中不能使用联合操作符语句中不能使用联合操作符UNION。(6)在插入或修改数据时，每个在插入或修改数据时，每个INSERT语句和语句和UPDATE语句不能影响一个语句不能影响一个以上的视图基表。以上的视图基表。(7)对视图中的计算列不允许使用对视图中的计算列不允许使用INSERT语句插入数据。语句插入数据。(8)UPDATE语句不能够修改视图的计算列数据，也不允许它修改包含集合的语句不能够修改视图的计算列数据，也不允许它修改包含集合的函数和内置函数的视图列。函数和内置函数的视图列。(9)在视图的在视图的Text和和Image数据类型
23、的列上不允许使用数据类型的列上不允许使用READTEXT和和WRITETEXT语句。语句。288/20/2024297.4 视图的操作视图的操作n创建视图创建视图1. 使用企业管理器创建视图使用企业管理器创建视图2. 使用使用Transact-SQL语句建立视图语句建立视图语法结构：语法结构：CREATE VIEW database_name.owner_name.view_name(column,) WITH ENCRYPTION|SCHEMABINDING|VIEW_METADATAASselect_statementWITH CHECK OPTION298/20/2024307.4 视图
24、的操作视图的操作例例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.o
25、rder_dateFROM employee INNER JOIN sell_order ON employee.employee_id = sell_order.employee_id INNER JOINgoods ON sell_order.goods_id = goods.goods_id 308/20/2024317.4 视图的操作视图的操作n修改视图修改视图1使用企业管理器修改视图使用企业管理器修改视图2使用使用Transact-SQL语句修改视图语句修改视图可以使用可以使用ALTER VIEW语句来修改视图，其语法格式如下：语句来修改视图，其语法格式如下：ALTER VIEW .
26、 . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 318/20/2024327.4 视图的操作视图的操作n 删除视图删除视图1. 使用企业管理器删除视图使用企业管理器删除视图2使用使用Transact-SQL语句删除视图语句删除视图可以使用可以使用DROP VIEW语句来删除视图，其语法格式如下：语句来删除视图，其语法格式如下：DROP VIEW view_name ,.n 其中其中View_name是要
27、删除的视图名称，可以删除多个视图。是要删除的视图名称，可以删除多个视图。例例7-7 删除删除view1视图。视图。USE SalesIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = view1) DROP VIEW view1GO328/20/2024337.4 视图的操作视图的操作n查看和修改视图属性查看和修改视图属性1. 用企业管理器查看视图属性用企业管理器查看视图属性2. 使用系统存储过程使用系统存储过程sp_helptext查看视图查看视图语法格式：语法格式：sp_helptext
28、 objname = name3. 使用系统存储过程重命名视图使用系统存储过程重命名视图语法格式：语法格式：sp_rename objname = object_name , newname = new_name , objtype = object_type 338/20/2024347.5 视图的应用视图的应用n通过视图检索表数据通过视图检索表数据在建立视图后，可以用任一种查询方式检索视图数据，在建立视图后，可以用任一种查询方式检索视图数据，对视图可使用连接、对视图可使用连接、GROUP BY子句、子查询等以及它们的子句、子查询等以及它们的任意组合。任意组合。例例7-8 查询例查询例7-6
29、所创建的视图所创建的视图sell_view中的姓名为钱达中的姓名为钱达理的员工所销售的商品名称。理的员工所销售的商品名称。 SELECT goods_name FROM sell_view WHERE employee_name=钱达理钱达理ORDER BY order_date 348/20/2024357.5 视图的应用视图的应用n通过视图添加表数据通过视图添加表数据语法格式：语法格式：INSERT INTO 视图名视图名 VALUES(列值列值1，列值，列值2，列值，列值3，列值，列值n)例例7-9 在在goods表中建立一个视图，利用视图插入一行数表中建立一个视图，利用视图插入一行数据
30、。据。CREATE VIEW goods_viewAS SELECT goods_id, goods_name, classification_id, unit_price, stock_quantityFROM goods WHERE (classification_id = P001)INSERT INTO goods_viewVALUES (G0008,SONY DVD,P002, 400,10)358/20/2024367.5 视图的应用视图的应用n通过视图修改表数据通过视图修改表数据语法格式：语法格式：UPDATE 视图名视图名SET 列列1=列值列值1 列列2=列值列值2 . 列列
31、n=列值列值n WHERE 逻辑表达式逻辑表达式368/20/2024377.5 视图的应用视图的应用n通过视图删除表数据通过视图删除表数据尽管视图不一定包含基础表的所有列，但可以通过视图尽管视图不一定包含基础表的所有列，但可以通过视图删除基础表的数据行。删除基础表的数据行。语法格式：语法格式：DELETE FROM 视图名视图名WHERE 逻辑表达式逻辑表达式 WHERE 逻辑表达式逻辑表达式378/20/2024387.5 视图的应用视图的应用n通过视图修改表数据通过视图修改表数据语法格式：语法格式：UPDATE 视图名视图名SET 列列1=列值列值1 列列2=列值列值2 . 列列n=列值
32、列值n WHERE 逻辑表达式逻辑表达式388/20/202439本章小结本章小结(1)索引是对数据库表中一个或多个字段的值进行排序而创建的一种索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。建立索引的主要目的是加速数据检索和连接、优化查询、分散存储结构。建立索引的主要目的是加速数据检索和连接、优化查询、强制实行惟一性等操作。主要有强制实行惟一性等操作。主要有3种类型的索引，即惟一性索引、主键索种类型的索引，即惟一性索引、主键索引和聚集索引。引和聚集索引。(2)在在SQL Server 2000中对索引的基本操作包括创建索引、查看索引、中对索引的基本操作包括创建索引、查看
33、索引、更改索引、删除索引和全文索引，可以在企业管理器或通过更改索引、删除索引和全文索引，可以在企业管理器或通过Transact-SQL语句实现索引操作。语句实现索引操作。(3)视图是一种数据库对象，是从一个或多个表或视图中导出的虚拟视图是一种数据库对象，是从一个或多个表或视图中导出的虚拟表。表。 (4)视图的操作主要包括视图的创建、修改、删除和重命名等，其操视图的操作主要包括视图的创建、修改、删除和重命名等，其操作可以通过作可以通过SQL Server企业管理器和企业管理器和Transact-SQL语句来实现。语句来实现。(5)通过视图可以完成某些和基础表相同的一些数据操作，如数据的通过视图可以完成某些和基础表相同的一些数据操作，如数据的检索、添加、修改和删除。检索、添加、修改和删除。39
