第7章 索引与视图

null第7章 索引与视图第7章 索引与视图本章内容本章内容7.1 索引概述 7.2 索引的操作 7.3 视图概述 7.4 视图的操作 7.5 视图的应用7.1 索引概述7.1 索引概述索引的基本概念 索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。7.1 索引概述7.1 索引概述索引的基本概念 索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。 建立索引的目的有以下几点： (1)加速数据检索 (2)加速连接、ORDER BY和GROUP BY等操作 (3)查询优化器依赖于索引起作用 (4)强制实行的惟一性7.1 索引概述7.1 索引概述索引的分类 根据数据库的功能，在SQL Server 2005中可创建3种类型的索引，即惟一性索引、主键索引和聚集索引。 1．惟一性索引 在表中建立惟一性索引时，组成该索引的字段或字段组合在表中具有惟一值，也就是说，对于表中的任何两行记录来说，索引键的值都是各不相同。 2．主键索引 表中通常有一个字段或一些字段组的合，其值用来惟一标识表中的每一行记录，该字段或字段组合称为表的主键。 7.1 索引概述7.1 索引概述3．聚集索引 在聚集索引中，表中各记录的物理顺序与键值的逻辑(索引)顺序相同。只有在表中建立了一个聚集索引后，数据才会按照索引键值指定的顺序存储到表中。由于一个表中的数据只能按照一种顺序来存储，所以在一个表中只能建立一个聚集索引。 7.2 索引的操作7.2 索引的操作创建索引 1．使用SQL Server管理平台创建索引 7.2 索引的操作7.2 索引的操作2．使用Transact-SQL语句创建索引 语法 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 ： CREATE [UNIQUE][CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name(column_name[ASC|DESC],…n) [WITH [PAD_INDEX] [[,] FILLFACTOR=fillfactor] [[,] IGNORE_DUP_KEY] [[,] DROP_EXISTING] [[,] STATISTICS_NORECOMPUTE] ] [ON filegroup]7.2 索引的操作7.2 索引的操作例7-1 按employee表的employee_name列建立非聚集索引。 CREATE NONCLUSTERED INDEX name_idx ON employee(employee_name)7.2 索引的操作7.2 索引的操作查看索引 1．用SQL Server管理平台查看修改索引 2．使用系统存储过程查看索引 语法格式： sp_helpindex [@objname]='name' 其中[@objname]='name子句为指定当前数据库中的表的名称。 例7-2 在查询 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 器中查看表employee的索引。 USE Sales GO EXEC sp_helpindex employee GO7.2 索引的操作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 'employee.employee_index_1','employee_index_id','index'7.2 索引的操作7.2 索引的操作 删除索引 索引会减慢INSERT、UPDATE和DELETE语句的执行速度。如果发现索引阻碍整体性能或不再需要索引，则可将其删除。 1．使用SQL Server管理平台删除索引 7.2 索引的操作7.2 索引的操作2．使用Transact-SQL语句删除索引 语法格式： DROP INDEX table_name.index_name [ ,...n ] 各选项的含义： (1)table_name：索引列所在的表。 (2)index_name：要删除的索引名称。索引名必须符合标识符的规则。7.2 索引的操作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 GO7.3 视图概述7.3 视图概述视图的概念 视图是一个虚拟表，并不表示任何物理数据，而只是用来查看数据的窗口而已。 对视图的操作与对表的操作一样，可以对其进行查询、修改和删除，但对数据的操作要满足一定的条件。 对视图所引用的基础表来说，视图的作用类似于筛选。7.3 视图概述7.3 视图概述视图的作用 视图通常用来集中、简化和自定义每个用户对数据库的不同认识。视图可用作安全机制，方法是允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。 (1)简化操作 (2)定制数据 (3)导出数据 (4)安全性7.3 视图概述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语句。7.4 视图的操作7.4 视图的操作创建视图 1.使用SQL Server管理平台创建视图 2. 使用Transact-SQL语句建立视图 语法结构： CREATE VIEW [database_name.][owner_name.]view_name[(column[,])] [WITH {ENCRYPTION|SCHEMABINDING|VIEW_METADATA}] AS select_statement [WITH CHECK OPTION]7.4 视图的操作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 7.4 视图的操作7.4 视图的操作修改视图 1．使用SQL Server管理平台修改视图 2．使用Transact-SQL语句修改视图 可以使用ALTER VIEW语句来修改视图，其语法格式如下： ALTER VIEW [ < database_name > .] [ < owner > .] view_name [ ( column [ ,...n ] ) ] [ WITH < view_attribute > [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] < view_attribute > ::= { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }7.4 视图的操作7.4 视图的操作 删除视图 1. 使用SQL Server管理平台删除视图 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 GO7.4 视图的操作7.4 视图的操作查看和修改视图属性 1．用SQL Server管理平台查看视图属性 2．使用系统存储过程sp_helptext查看视图 语法格式： sp_helptext [ @objname = ] 'name‘ 3．使用系统存储过程重命名视图 语法格式： sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' [ , [ @objtype = ] 'object_type' ]7.5 视图的应用7.5 视图的应用通过视图检索表数据 在建立视图后，可以用任一种查询方式检索视图数据，对视图可使用连接、GROUP BY子句、子查询等以及它们的任意组合。 例7-8 查询例7-6所创建的视图sell_view中的姓名为钱达理的员工所销售的商品名称。 SELECT goods_name FROM sell_view WHERE employee_name='钱达理' ORDER BY order_date 7.5 视图的应用7.5 视图的应用通过视图添加表数据 语法格式： 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) 7.5 视图的应用7.5 视图的应用通过视图修改表数据 语法格式： UPDATE 视图名 SET 列1=列值1 列2=列值2 ……. 列n=列值n WHERE 逻辑表达式7.5 视图的应用7.5 视图的应用通过视图删除表数据 尽管视图不一定包含基础表的所有列，但可以通过视图删除基础表的数据行。 语法格式： DELETE FROM 视图名 WHERE 逻辑表达式 WHERE 逻辑表达式7.5 视图的应用7.5 视图的应用通过视图修改表数据 语法格式： UPDATE 视图名 SET 列1=列值1 列2=列值2 ……. 列n=列值n WHERE 逻辑表达式本章小结本章小结(1)索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。建立索引的主要目的是加速数据检索和连接、优化查询、强制实行惟一性等操作。主要有3种类型的索引，即惟一性索引、主键索引和聚集索引。 (2)在SQL Server 2005中对索引的基本操作包括创建索引、查看索引、更改索引、删除索引和全文索引，可以在企业管理器或通过Transact-SQL语句实现索引操作。 (3)视图是一种数据库对象，是从一个或多个表或视图中导出的虚拟表。 (4)视图的操作主要包括视图的创建、修改、删除和重命名等，其操作可以通过SQL Server企业管理器和Transact-SQL语句来实现。 (5)通过视图可以完成某些和基础表相同的一些数据操作，如数据的检索、添加、修改和删除。