资源描述
,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,第,7,章 索引与视图,1,本章内容,7.1,索引概述,7.2,索引的操作,7.3,视图概述,7.4,视图的操作,7.5,视图的应用,第,7,章 索引与视图,2,7.1,索引概述,索引的基本概念,索引,是对数据库表中一个或多个列的值进行排序的结构,可以利用索引快速访问数据库表中的特定信息。,(索引名称),索引项,记录号,索引值,1,记录号,1,索引值,2,记录号,2,索引值,n,记录号,n,3,建立索引的目的有以下几点:,(1),加速数据检索,(2),加速连接、,ORDER BY,和,GROUP BY,等操作,(3),查询优化器依赖于索引起作用,(4),强制实行的惟一性,7.1,索引概述,4,惟一索引和非惟一索引,聚集索引和非聚集索引,单列索引和复合索引,索引的分类,7.1,索引概述,5,7.1,索引概述,索引的分类,1,惟一性索引,在表中建立惟一性索引时,组成该索引的字段或字段组合在表中具有惟一值,也就是说,对于表中的任何两行记录来说,,索引键的值都是各不相同,。,6,2,主键索引,表中通常有一个字段或一些字段的组合,其值用来惟一标识表中的每一行记录,该字段或字段组合称为表的主键。,不能有空值,NULL,表中设,主键后,将自动创建主键索引,它是 唯一索引的特殊类型,7.1,索引概述,7,7.1,索引概述,3,聚集索引,在聚集索引中,,表中各记录的物理顺序与键值的逻辑,(,索引,),顺序相同,。只有在表中建立了一个聚集索引后,数据才会按照索引键值指定的顺序存储到表中。,由于一个表中的数据只能按照一种顺序来存储,所以在,一个表中只能建立一个聚集索引。,8,聚集索引的适用范围,数量有限的唯一值的列,经常要,搜索范围值的列,返回大,结果集,的查询,使用,ORDER BY,或,GROUP BY,子句,7.1,索引概述,9,非聚集索引适用于以下情况:,包含大量非重复值的列,,如姓名列。如果只有很少的非重复值,则大多数查询将不使用索引,因为此时使用表扫描查询数据更有效。,不返回大型结果集的查询,。,搜索条件(,WHERE,子句)要求,精确匹配的查询,中经常使用的列。,经常需要联接和分组查询语句,。应在联接和分组操作中使用的列上创建多个非聚集索引,在任何外键列上创建一个聚集索引。,10,7.2,索引的操作,创建索引,1,使用企业管理器向导建立索引,2,使用企业管理器直接创建索引,11,7.2,索引的操作,3,使用,Transact-SQL,语句创建索引,create,unique clustered|,nonclustered,index,index_name,table_name,(column_nameasc|desc,n),on,filegroup,12,说明:,Unique,、,clustered,、,nonclustered,用来指定索引的类型,无这些选项时系统默认创建的是非唯一索引、非聚集索引,由,ntext,、,text,或,image,数据类型组成的列不能指定为索引列。,ASC/DESC,确定索引列的排序方法,默认为,ASC,13,7.2,索引的操作,例,7-1,按,employee,表的,employee_name,列建立非聚集索引。,CREATE NONCLUSTERED,INDEX,name_idx,ON,employee(employee_name),14,例,:SC,表按学号升序和课程号降序建,唯一索引。,CREATE UNIQUE INDEX,SCno,ON,SC(Sno,ASC,,,Cno,DESC),7.2,索引的操作,15,7.2,索引的操作,查看索引,1,使用企业管理器查看索引,2,使用系统存储过程查看索引,语法格式:,sp_helpindex,objname,=name,其中,objname,=name,子句为指定当前数据库中的表的名称。,16,例,7-2,在查询分析器中查看表,employee,的索引。,USE Sales,GO,EXEC,sp_helpindex,employee,GO,7.2,索引的操作,17,7.2,索引的操作,编辑索引,1,使用企业管理器编辑索引,2,使用系统存储过程更改索引,语法格式:,sp_rename,oldname,newname,object_type,18,例,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,索引的操作,19,7.2,索引的操作,删除索引,索引会减慢,INSERT,、,UPDATE,和,DELETE,语句的执行速度。如果发现索引阻碍整体性能或不再需要索引,则可将其删除。,1,使用企业管理器删除索引,20,7.2,索引的操作,2,使用,Transact-SQL,语句删除索引,语法格式:,DROP INDEX,table_name,.index_name ,.n,各选项的含义:,(1),table_name,:,索引列所在的表。,(2)index_name,:,要删除的索引名称。索引名必须符合标识符的规则。,21,7.2,索引的操作,例,7-4,删除,employee,表内名为,index_2,的索引。,USE Sales,IF EXISTS,(SELECT name FROM,sysindexes,WHERE name=index_2),DROP INDEX,employee.index_2,GO,22,删除索引时注意:,执行,DROP INDEX,后,将自动释放索引占据的空间,如果在,CREATE TABLE,中创建的索引,只能用,ALTER TABLE,进行删除,如果用,CREATE INDEX,创建的可以用,DROP INDEX,删除,删除一个表时,表的索引也被删除,不能在由,PRIMARY KEY,约束或,UNIQUE,约束创建的索引上使用,DROP INDEX,,,要删除索引必须先删除约束。,DROP INDEX,不能用于系统表,23,复 习,1,、什么是索引?,2,、索引的分类?,3,、如何建立索引?,24,7.2,索引的操作,全文索引,如果需要使用大的字符串字段来检索数据,则需要使用,SQL Server,所提供的全文索引,(Full-Text Index),功能。,25,7.2,索引的操作,使用全文索引,与普通的选择查询不同的是,对一个表进行全文查询时,需要在,WHERE,子句中使用,CONTAINS,和,FREETEXT,这样两个谓词。,(1),使用,CONTAINS,进行全文查询,语法格式:,SELECT,字段列表,FROM,表名,WHERE CONTAINS(,字段名,|*,搜索条件,),26,7.2,索引的操作,例,7-5,在,Sales,数据库中执行下列全文查询,从,department,表中搜索,depart_description,字段或,department_name,字段中包含“市场”一词的记录,写出所使用的,SELECT,语句。,USE Sales,SELECT*FROM department,WHERE CONTAINS(*,市场,),27,7.2,索引的操作,(2),使用,FREETEXT,进行全文查询,使用,FREETEXT,进行全文查询时,全文查询引擎将对指定的项目建立一个内部查询,可以从表中搜索一组单词或短语甚至完整的句子。,语法格式:,SELECT,字段列表,FROM,表名,WHERE FREETEXT(,字段名,|*,自由文本,),28,7.3,视图概述,视图的概念,视图是一个,虚拟表,,并不存储任何物理数据,而只是用来,查看数据的窗口,而已。,对视图的操作与对表的操作一样,可以对其进行查询、修改和删除,但对数据的操作要满足一定的条件。,对视图所引用的基础表来说,视图的作用类似于筛选。,29,7.3,视图概述,视图的作用,视图通常用来,集中、简化和自定义,每个用户对数据库的,不同认识,。视图可,用作安全机制,,方法是允许用户通过视图访问数据,而不授予用户直接访问视图基础表的权限。,(1),简化操作,(2),定制数据,(3),导出数据,(4),安全性,30,7.3,视图概述,视图的限制,在创建或使用视图时,应遵守以下规定:,(1),在一个,批事务,中,,CREATE VIEW,语句不能与其他,SQL,语句结合使用。,(2),不能在,视图,上建立触发器和索引。,(3),一个视图最多只能有,250,列。,(4),不能基于,临时表,建立视图,由,SELECT INTO,语句可建立临时表,在,CREATE VIEW,语句中不能使用,INTO,关键字。,(5),在,CREATE VIEW,语句中不能使用联合操作符,UNION,。,31,(6),在插入或修改数据时,每个,INSERT,语句和,UPDATE,语句,不能影响一个以上的视图基表,。,(7),对视图中的,计算列,不允许使用,INSERT,语句,插入,数据。,(8)UPDATE,语句不能够,修改,视图的计算列数据,也不允许它修改包含集合的函数和内置函数的视图列。,7.3,视图概述,32,7.4,视图的操作,创建视图,1.,使用企业管理器创建视图,2.,使用,Transact-SQL,语句建立视图,语法结构:,create view,database_name.owner_name.,view_name(column,),with encryption,as,select_statement,with check option,33,WITH ENCRYPTION,:,给系统,表,syscomments,中视图定义的,SELECT,命令加密。,WITH CHECK OPTION,:,强制所有通过视图修改的数据满足,select,语句中指定的选择条件。,7.4,视图的操作,34,需要特别注意,:,CREATE VIEW,必须是,批中的惟一语句,,而且视图中的,SELECT,命令不能包括,INTO,、,ORDER BY,、,DISTINCT,等子句。,DBMS,执行,CREATE VIEW,语句时只是把视图的定义存入数据库,,,并不执行其中的,SELECT,语句,。,在对视图查询时,按视图的定义从基本表中将数据查出,7.4,视图的操作,35,定义一个反映学生出生年份的视图,CREATE VIEW,BT_S(Sno,,,Sname,,,Sbirth,),AS,SELECT,Sno,,,Sname,,,2009-Sage,FROM Student,设置一些派生属性列,也称为,虚拟列,-,Sbirth,带表达式的视图,必须明确定义,组成视图的,各个属性列名,7.4,视图的操作,36,7.4,视图的操作,例,7-6,在,Sales,数据库中创建,sell_view,视图,该视图选择,3,个基表,(employee,goods,sell_order),中的数据来显示员工销售货物情况的虚拟表。,37,CREATE VIEW,sell_view,AS,SELECT,e.employee_name,e.employee_id,s.order_num,s.discount,g.goods_name,g.unit_price,s.order_date,FROM,employee e,INNER JOIN,sell_order,s,ON,e.employee_id=s.employee_id,INNER JOIN,goods g,ON,s.goods_id=g.goods_id,38,7.4,视图的操作,修改视图,1,使用企业管理器修改视图,2,使用,Transact-SQL,语句修改视图,ALTER VIEW,.view_name (column ,.n ),WITH encryption,.n ,AS,select_statement,WITH CHECK OPTION,39,7.4,视图的操作,删除视图,1.,使用企业管理器删除视图,2,使用,Transact-SQL,语句删除视图,DROP VIEW view_name ,.n,例,7-7,删除,view1,视图。,USE Sales,IF EXISTS,(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS,WHERE TABLE_NAME=view1),DROP VIEW,view1,40,7.4,视图的操作,查看和修改视图属性,1.,用企业管理器查看视图属性,2.,使用系统存储过程,sp_helptext,查看视图,sp_helptext,objname,=name,3.,使用系统存储过程重命名视图,sp_rename,objname,=object_name,newname,=new_name,objtype,=object_type,41,7.5,视图的应用,通过视图检索表数据,从用户角度:,查询视图,与查询基本表相同,例,在信息系学生的视图中找出年龄小于,20,岁的学生。,SELECT,Sno,,,Sage,FROM,IS_Student,WHERE,Sage20,42,IS_Student,视图的定义,CREATE VIEW,IS_Student,AS,SELECT,Sno,,,Sname,,,Sage,FROM Student,WHERE,Sdept,=IS,7.5,视图的应用,43,转换后的查询语句为:,SELECT,Sno,,,Sage,FROM,Student,WHERE,Sdept,=IS AND Sage20,7.5,视图的应用,44,7.5,视图的应用,在视图中添加数据,就是对视图的基表添加数据,添加数据也使用,INSERT,命令。但要注意:,当视图有多个基表时,不能在一个语句中对视图中的多个基表同时进行修改,亦即,一次只能对视图的一个基表内的数据进行增、删、改操作,45,由于视图中可能仅仅包含基表中的部分列,因此通过视图向基表中插入数据时,要求基表中的其他非视图列应该是允许空(,NULL,),或含有默认值,如在视图定义中使用了,with check option,子句,则在视图上执行插入语句必须符合定义视图时设定的条件,7.5,视图的应用,46,例,向信息系学生视图,IS_S,中插入一个新的学生记录:,95029,,赵新,,20,岁,INSERT,INTO,IS_Student,VALUES(95029,赵新,20),47,转换为对基本表的更新:,INSERT INTO,Student,(Sno,,,Sname,,,Sage,Sdept,),VALUES(95029,,,赵新,,,20,,,IS,),48,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),49,7.5,视图的应用,通过视图修改表数据,语法格式:,UPDATE,视图名,SET,列,1=,列值,1,列,2=,列值,2,.,列,n=,列值,n,WHERE,逻辑表达式,50,使用,UPDATE,命令可以通过视图修改基表中的数据注意,一次只能修改一个表,,且修改后的数据不得违反基表中的各种约束关系。,若在视图定义中用了,with check option,子句,通过视图修改数据必须满足,select,语句中的选择条件,7.5,视图的应用,51,7.5,视图的应用,通过视图删除表数据,尽管视图不一定包含基础表的所有列,但可以通过视图删除基础表的数据行。,语法格式:,DELETE FROM,视图名,WHERE,逻辑表达式,52,若要删除的数据行不包含在视图的定义中,该数据行不能删除。,若删除语句的条件中指定的列是视图未包含的列,则无法通过视图删除基表数据行,7.5,视图的应用,53,
展开阅读全文