SQL第7章视图和索引课件.ppt

《SQL第7章视图和索引课件.ppt》由会员分享，可在线阅读，更多相关《SQL第7章视图和索引课件.ppt（51页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、1第七章第七章第七章第七章 视图和索引视图和索引视图和索引视图和索引7.1 视图简介视图简介 7.2 创建视图创建视图 7.3 查看与修改视图查看与修改视图 7.4 编辑视图中的记录编辑视图中的记录 7.5 视图定义信息查询视图定义信息查询 7.6 删除视图删除视图7.7 索引索引27.1 7.1 7.1 视图简介视图简介视图简介视图简介视图简介视图简介1.1.视图视图的基本概念的基本概念的基本概念的基本概念 视图视图是一个虚是一个虚是一个虚是一个虚拟拟表表表表，其内容由，其内容由，其内容由，其内容由查询语查询语句定句定句定句定义义生成。表生成。表生成。表生成。表是是是是视图视图的基的基的基的

2、基础础。数据数据数据数据库库中只存中只存中只存中只存储储了了了了视图视图定定定定义义，而，而，而，而不存放不存放不存放不存放视图视图所所所所对应对应的的的的数据数据数据数据，视图视图所所所所对应对应的数据仍存放在的数据仍存放在的数据仍存放在的数据仍存放在视图视图所引用的基表中。所引用的基表中。所引用的基表中。所引用的基表中。视图视图中的数据是中的数据是中的数据是中的数据是视图视图在被使用在被使用在被使用在被使用时动态时动态生成的生成的生成的生成的,它随着基它随着基它随着基它随着基表数据的表数据的表数据的表数据的变变化而化而化而化而发发生生生生变变化。化。化。化。37.1 7.1 7.1 视图简

3、介视图简介视图简介视图简介视图简介视图简介 2.2.视图视图的作用的作用的作用的作用 1)1)视图视图可以集中数据，可以集中数据，可以集中数据，可以集中数据，满满足不同用足不同用足不同用足不同用户对户对数据的不同要求。数据的不同要求。数据的不同要求。数据的不同要求。2)2)视图视图可以可以可以可以简简化复化复化复化复杂查询杂查询的的的的结结构，从而方便用构，从而方便用构，从而方便用构，从而方便用户对户对数据数据数据数据的操作。的操作。的操作。的操作。3)3)视图视图能能能能够对够对数据提供安全保数据提供安全保数据提供安全保数据提供安全保护护。4)4)便于便于便于便于组织组织数据数据数据数据导导

4、出。出。出。出。47.2 7.2 7.2 创建视图创建视图创建视图创建视图创建视图创建视图创创建建建建视图时应该视图时应该注意以下：注意以下：注意以下：注意以下：创创建建建建者者者者必必必必须须拥拥有有有有创创建建建建视视图图的的的的权权限限限限，对对视视图图中中中中引引引引用用用用的的的的基基基基表表表表或或或或视图视图有有有有许许可可可可权权。只能在当前数据只能在当前数据只能在当前数据只能在当前数据库库中中中中创创建建建建视图视图在在在在一一一一个个个个视视图图中中中中最最最最多多多多引引引引用用用用10241024列列列列，视视图图中中中中记记录录的的的的行行行行数数数数限限限限制制制制

5、由由由由基表中基表中基表中基表中记录记录数目决定。数目决定。数目决定。数目决定。视视图图的的的的名名名名称称称称必必必必须须遵遵遵遵循循循循标标识识符符符符的的的的定定定定义义规规则则，对对于于于于每每每每个个个个用用用用户户来来来来说说也也也也必必必必须须唯唯唯唯一一一一，而而而而且且且且视视图图名名名名称称称称不不不不允允允允许许与与与与该该用用用用户户拥拥有有有有的的的的表表表表重重重重名名名名。视视图图中中中中列列列列的的的的名名名名称称称称一一一一般般般般继继承承承承其其其其基基基基表表表表中中中中列列列列的的的的名名名名称称称称，如如如如果果果果视视图图中中中中某某某某一一一一列列

6、列列是是是是算算算算术术表表表表达达达达式式式式、函函函函数数数数、常常常常量量量量或或或或者者者者来来来来自自自自多多多多个个个个表表表表的的的的列列列列名名名名相同相同相同相同，必必必必须须要要要要为为视图视图中的中的中的中的列重新定列重新定列重新定列重新定义义名称名称名称名称。57.2 7.2 7.2 创建视图创建视图创建视图创建视图创建视图创建视图可可可可以以以以将将将将视视图图创创建建建建在在在在其其其其他他他他视视图图上上上上，SQL SQL Server Server 20052005中中中中允允允允许许3232层层的的的的视图视图嵌套嵌套嵌套嵌套。不不不不能能能能在在在在视视图

7、图上上上上创创建建建建全全全全文文文文索索索索引引引引，不不不不能能能能将将将将规规则则、默默默默认认绑绑定定定定在在在在视图视图上。上。上。上。不能在不能在不能在不能在临时临时表上表上表上表上创创建建建建视图视图，也不能，也不能，也不能，也不能创创建建建建临时视图临时视图。定定定定义义视视图图的的的的查查询询语语句句句句中中中中不不不不能能能能包包包包含含含含计计算算算算子子子子句句句句COMPUTECOMPUTE、COMPUTE BYCOMPUTE BY子句和子句和子句和子句和INTOINTO关关关关键键字字字字。67.2 7.2 7.2 创建视图创建视图创建视图创建视图创建视图创建视图7

8、.2.1 使用使用SSMS创建建视图 7.2.2 使用使用CREATE VIEW语句句创建建视图7.2.3 重命名重命名视图 77.2.1 7.2.1 7.2.1 使用使用使用使用使用使用SSMSSSMSSSMS创建视图创建视图创建视图创建视图创建视图创建视图 示范案例示范案例1 使用使用SQL Server Management Studio在在“Northwind”数据数据库中中创建一个建一个视图“V_dingdanxiangxi”，用于，用于查询一个一个订单是由哪是由哪个雇个雇员签订的，的，签订的是什么的是什么时候，候，买了什么了什么产品，品，价格是多少，由价格是多少，由谁来送来送货，送

9、，送货的地址是什么等内的地址是什么等内容。容。8使用使用CREATE VIEW语句句创建建视图语法格式如下：法格式如下：CREATE VIEW .view_name (column ,.n )WITH ENCRYPTION AS AS select_statement WITH CHECK OPTION 7.2.2 7.2.2 7.2.2 使用使用使用使用使用使用CREATE VIEWCREATE VIEWCREATE VIEW创建视图创建视图创建视图创建视图创建视图创建视图97.2.2 7.2.2 7.2.2 使用使用使用使用使用使用CREATE VIEWCREATE VIEWCREATE

10、VIEW创建视图创建视图创建视图创建视图创建视图创建视图 示范案例示范案例2 使用使用T-SQL语句在句在“Northwind”数据数据库中中创建一个建一个视图“V_ProductInfo”，用于，用于查询所有所有产品的品的产品号品号“productid”、产品名称品名称“productname”和和产品价格品价格“unitprice”。使用的基本表。使用的基本表为“Products”表。表。程序清程序清单如下：如下：CREATE VIEW V_ProductInfo AS SELECT productid,productname,unitprice FROM products 107.2.3

11、 7.2.3 7.2.3 重命名视图重命名视图重命名视图重命名视图重命名视图重命名视图 1.1.使用使用使用使用SSMSSSMS 2.2.使用系使用系使用系使用系统统存存存存储过储过程程程程sp_rename sp_rename 其其其其语语法格式如下：法格式如下：法格式如下：法格式如下：sp_rename old_name,new_namesp_rename old_name,new_name 示范案例示范案例示范案例示范案例3 3 使用使用使用使用T-SQLT-SQL语语句将句将句将句将NorthwindNorthwind数据数据数据数据库库中中中中视图视图“V_SelectProduct

12、Info1V_SelectProductInfo1”重命名重命名重命名重命名为为“vv_spinfovv_spinfo”程序清程序清程序清程序清单单如下：如下：如下：如下：EXEC sp_renameEXEC sp_rename v_selectproductinfo1,vv_spinfo v_selectproductinfo1,vv_spinfo 117.3 7.3 7.3 查查查查查查看与看与看与看与看与看与修改视图修改视图修改视图修改视图修改视图修改视图7.3.1 查看看视图 7.3.2 在在SQL Server Management Studio中修改中修改视图 7.3.3 用用CR

13、EATE VIEW修改修改视图 127.3.1 7.3.1 7.3.1 查查查查查查看看看看看看视图视图视图视图视图视图1.在在SQL Server Management Studio中中查看看视图内内容的方法与容的方法与查看数据表内容的方法几乎一看数据表内容的方法几乎一样。2.在在T-SQL语句里，使用句里，使用Select语句可以句可以查看看视图的内容，其用法与的内容，其用法与查看数据表内容的用法一看数据表内容的用法一样，区，区别只是把数据表名改只是把数据表名改为视图名。名。137.3.2 7.3.2 7.3.2 在在在在在在SSMSSSMSSSMS中修改视图中修改视图中修改视图中修改视图

14、中修改视图中修改视图 使用使用SQL Server Management Studio修改修改视图事事实上只是修改上只是修改该视图所存所存储的的T-SQL语句。句。147.3.3 7.3.3 7.3.3 用用用用用用ALTER VIEWALTER VIEWALTER VIEW修改视图修改视图修改视图修改视图修改视图修改视图其其语法格式如下：法格式如下：ALTER VIEW view_name (column ,.n )WITH ENCRYPTION AS select_statement注注:对于于加加密密或或未未加加密密的的视图都都可可以以用用此此语句句进行修改。行修改。157.3.3 7.

15、3.3 7.3.3 用用用用用用ALTER VIEWALTER VIEWALTER VIEW修改视图修改视图修改视图修改视图修改视图修改视图 示范案例示范案例4 使用使用T-SQL语句修改句修改Northwind数据数据库中的中的视图“V_SelectProductInfo”，用于，用于查询类别名名“categoryname”为“Beverages”的所有的所有产品的品的详细信息，如信息，如产品号品号“productid”、产品名称品名称“productname”和供和供应商的公司名称商的公司名称“companyname”。167.3.3 7.3.3 7.3.3 用用用用用用ALTER VIE

16、WALTER VIEWALTER VIEW修改视图修改视图修改视图修改视图修改视图修改视图程序清程序清单如下：如下：ALTER VIEW V_SelectProductInfo AS SELECT products.productid,products.productname,panynameFROM products JOIN categories ON products.categoryid=categories.categoryid JOIN suppliers ON products.supplierid=suppliers.supplieridWHERE categories.cat

17、egoryname=Beverages177.4 7.4 7.4 编辑视图中的记录编辑视图中的记录编辑视图中的记录编辑视图中的记录编辑视图中的记录编辑视图中的记录7.4.1 编辑视图中中记录的限制的限制 7.4.2 在在SSMS中操作中操作视图记录 7.4.3 用用T-SQL语句操作句操作视图记录187.4.1 7.4.1 7.4.1 编辑视图中记录的限制编辑视图中记录的限制编辑视图中记录的限制编辑视图中记录的限制编辑视图中记录的限制编辑视图中记录的限制在使用在使用视图修改数据修改数据时，要注意下列一些事，要注意下列一些事项：不能在一个不能在一个语句中句中对多个基表使用数据修改多个基表使用数据

18、修改语句句。如果要如果要修改由两个或两个以上基表得到的修改由两个或两个以上基表得到的视图，必，必须进行多次修改，行多次修改，每次修改只能影响一个基表。每次修改只能影响一个基表。对于基表中需更新而又不允于基表中需更新而又不允许空空值的所有列，它的所有列，它们的的值在在 INSERT 语句或句或 DEFAULT 定定义中指定。中指定。这将确保基表中所将确保基表中所有需要有需要值的列都可以的列都可以获取取值。不能修改那些通不能修改那些通过计算得到算得到结果的列果的列。197.4.1 7.4.1 7.4.1 编辑视图中记录的限制编辑视图中记录的限制编辑视图中记录的限制编辑视图中记录的限制编辑视图中记录

19、的限制编辑视图中记录的限制在在视图定定义中使用了中使用了 WITH CHECK OPTION 子句，子句，则所有在所有在视图上上执行的数据修改行的数据修改语句都必句都必须符合定符合定义视图的的 SELECT 语句中所句中所设定的条件。定的条件。在基表的列中修改的数据必在基表的列中修改的数据必须符合符合对这些列的些列的约束条件束条件，如是否如是否为空、空、约束、束、DEFAULT 定定义等。等。207.4.2 7.4.2 7.4.2 在在在在在在SSMSSSMSSSMS中操作视图记录中操作视图记录中操作视图记录中操作视图记录中操作视图记录中操作视图记录1.修改修改视图中的中的记录2.在在视图中插

20、入中插入记录3.在在视图中中删除除记录217.4.3 7.4.3 7.4.3 用用用用用用T-SQLT-SQLT-SQL语句操作视图记录语句操作视图记录语句操作视图记录语句操作视图记录语句操作视图记录语句操作视图记录 1.更新更新记录UPDATE V_ProductInfoSET unitprice=16WHERE productid=4227.4.3 7.4.3 7.4.3 用用用用用用T-SQLT-SQLT-SQL语句操作视图记录语句操作视图记录语句操作视图记录语句操作视图记录语句操作视图记录语句操作视图记录2.删除除记录DELETE V_ProductInfoWHERE producti

21、d=4237.4.3 7.4.3 7.4.3 用用用用用用T-SQLT-SQLT-SQL语句操作视图记录语句操作视图记录语句操作视图记录语句操作视图记录语句操作视图记录语句操作视图记录3.3.插入插入记录INSERT V_ProductInfoVALUES(苹果苹果,$3)247.5 7.5 7.5 视图定义信息查询视图定义信息查询视图定义信息查询视图定义信息查询视图定义信息查询视图定义信息查询1.使用使用SQL Server Management Studio查看看视图信息信息2.使用系使用系统存存储过程程查看看视图信息信息 EXEC sp_helptext objname 257.6 7.

22、6 7.6 删除视图删除视图删除视图删除视图删除视图删除视图 1.在在SSMS中中删除除视图2.使用使用DROP VIEW语句句删除除视图 语法格式如下：法格式如下：DROP VIEW view ,.n 267.6 7.6 7.6 删除视图删除视图删除视图删除视图删除视图删除视图 示范案例示范案例5 使用使用T-SQL语句句删除除“Northwind”数据数据库中的中的视图“V_SelectProductInfo”。程序清程序清单如下：如下：DROP VIEW V_SelectProductInfo2023/5/29277.7 7.7 索引索引索引索引问题如何提高查询速度？某些列如何保证没有重

23、复值或满足一定条件？如何保证数据的参照完整性？2023/5/29287.77.77.77.7索索索索 引引引引索引是一个记录表中所包含的值以及相应的存储位置的列表。索引通过记录表中的关键值来指向表中的记录，这样数据库引擎就不用扫描整个表而能定位到相关的记录。2023/5/2929索引的作用索引的作用索引的作用索引的作用创建索引的好处主要有以下两点：（1）加快数据查询（2）加快表的连接、排序和分组工作创建索引也有它的不足，如下所述：（1）创建索引需要占用数据空间和时间（2）建立索引会减慢数据修改的速度2023/5/2930索引的分类索引的分类索引的分类索引的分类按照表中建立索引的那一列（或列组合

24、）中的数据是否各不相同，可以将索引分为惟一索引和非惟一索引。按照索引的结构，可以将其划分为两大类，聚集索引（Clustered index）和非聚集索引（Nonclustered index）。2023/5/29311惟一索引和非惟一索引 惟一索引要求所有数据行中任意两行中的被索引列或索引列组合不能存在重复值，包括不能有两个空值NULL，而非惟一索引则不存在这样的限制。2023/5/29322聚集索引和非聚集索引聚集索引会对表进行物理排序，所以这种索引对查询非常有效。表中只能有一个聚集索引。当建立主键约束时，如果表中没有聚集索引，SQL Server会用主键列作为聚集索引键。非聚集索引不会对表

25、进行物理排序。2023/5/2933通常，下列情况需要在表中的某一列或某些列上建立索引：（1）经常用作查询条件的列。（2）需要频繁地按范围搜索的列。（3）连接中频繁使用的列。（4）在ORDER BY子句中经常使用的列。（5）主键或外键的列。（6）值是惟一的列。2023/5/2934由于建立索引表后将占用系统资源，且索引的建立会影响数据的修改速度，每执行一次增删改操作都要重新维护一次索引，因此在没有必要的情况下不要建立索引。2023/5/2935创建索引创建索引创建索引创建索引索引可以在创建表时建立，也可以在定义表以后的任何时候建立；既可以在表的一列上建立一个索引，也可以在列组合上建立一个索引；

26、一个表中既可以建立一个索引，也可以建立多个索引（当然，其中只有一个聚集索引）。2023/5/2936可以利用企业管理器建立或利用T-SQL建立索引。建立索引的条件如下：（1）只有表的拥有者才能建立索引。（2）每个表只能建立一个聚集索引。（3）每个表最多可以建立250个非聚集索引。（4）索引最多可以包含16列。2023/5/2937 （5）建立惟一索引时，应保证建立索引的列不包括重复的数据，并且没有两个以上的NULL。（6）text、ntext、image列不能建立索引。2023/5/2938创建索引方法（一）创建索引方法（一）创建索引方法（一）创建索引方法（一）系统自动创建索引 在创建或修改表

27、时，如果添加了一个主键或惟一键约束，则系统将自动在该表上，以该键值作为索引列，创建一个惟一索引。2023/5/2939创建索引方法（二）创建索引方法（二）创建索引方法（二）创建索引方法（二）使用企业管理器【问题】在Student表上创建基于StuName列，名为IX_StuName的非聚集、非惟一索引。【注意】创建主键约束和惟一约束时，会自动在这些列上创建惟一索引。2023/5/2940创建索引方法（三）创建索引方法（三）创建索引方法（三）创建索引方法（三）-使用使用使用使用SQLSQL语句语句语句语句常用索引命令常用格式如下：CREATE UNIQUE CLUSTEREDNONCLUSTER

28、ED INDEX index_name ON table_nameview_name (column_name ASC|DESC ,n)WITH index_property,n2023/5/2941 【练习】使用T-SQL语句在StuCou表上创建名为IX_StuNoCouNo的聚集、惟一、复合索引，该索引基于StuNo列和CouNo列创建。CREATE UNIQUE CLUSTERED INDEX IX_StuNoCouNo ON StuCou(StuNo,CouNo)GO2023/5/2942复合索引说明复合索引说明复合索引说明复合索引说明在（字段1，字段2）上创建的复合索引和在（字段2

29、，字段1）上创建的复合索引是不同的。查找数据时，只有在where子句中指定了索引的第一个字段时才使用该复合索引。复合索引中字段的顺序很重要：在次序上首先定义最具惟一性的字段列。2023/5/2943删除索引删除索引删除索引删除索引使用企业管理器 【问题】使用企业管理器删除Student表中的IX_StuName索引。2023/5/2944使用SQL语句格式：DROP INDEX table_name.index_name2023/5/2945【问题】删除StuCou表中的IX_StuNoCouNo索引。DROP INDEX StuCou.IX_StuNoCouNo2023/5/2946【思考】

30、student表中的索引PK_Student可以删除吗？2023/5/2947【注意】1.不能用DROP INDEX语句删除由PRIMARY KEY约束或UNIQUE约束创建的索引。要删除这些索引必须先删除PRIMARY KEY约束或UNIQUE约束。2.在删除聚集索引时，表中的所有非聚集索引都将被重建。2023/5/2948管理索引管理索引管理索引管理索引显示索引信息重命名索引2023/5/2949显示索引信息1.企业管理器2.使用系统存储过程sp_helpindex table_name【练习】使用系统存储过程sp_helpindex查看Xk数据库中Student表的索引信息。EXEC sp_helpindex Student2023/5/2950重命名索引1.企业管理器2.使用Transact_SQL命令语句来实现。EXEC sp_rename table_name.old_index_name,new_index_name2023/5/2951 【练习】使用Transact_SQL语句将Student表的索引IX_StuName重新命名为IX_StuNameNew。EXEC sp_rename Student.IX_StuName,IX_StuNameNew