SQLServer2005数据库任务化教程 任务4 分销系统数据库索引和视图的设计.ppt

《SQLServer2005数据库任务化教程 任务4 分销系统数据库索引和视图的设计.ppt》由会员分享，可在线阅读，更多相关《SQLServer2005数据库任务化教程 任务4 分销系统数据库索引和视图的设计.ppt（51页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL Server 2005数据库任务化教程 梁竞敏 黄华林 宋广科 等编著 中国水利水电出版社任务四 分销系统数据库索引和视图的设计 一、任务目标一、任务目标n1 掌握索引的概念、索引的类型并熟悉索引的优点、缺点；n2 掌握索引的创建和维护。n3 掌握视图的概念、视图的创建方式；n4 掌握视图的应用，熟练通过视图进行的数据查询和数据维护操作。二、教学任务 n1 介绍索引的基本知识；n2 使用图形化工具创建索引；n3 使用Transact-SQL创建索引；n4 索引的维护；n5 为分销系统创建索引；n6介绍视图的基本知识；n7 使用图形化工具创建视图；n8 对视图的维护、管理；n9 通过视图

2、进行的数据查询和数据维护操作；n10 为分销系统创建视图。4.1 分销系统索引的设计分销系统索引的设计n用户对数据库最频繁的操作是进行数据查询。一般情况下，数据库在进行查询操作时需要对整个表进行数据搜索。当表中的数据很多时，搜索数据就需要很长的时间，这就造成了服务器的资源浪费。为了提高检索数据的能力，数据库引入了索引机制。索引是数据库中一种特殊数据类型的对象，不单可用来提高表中数据的查询速度，还能够实现某些数据完整性 4.1.1 索引的基础知识索引的基础知识 n索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引是依赖于

3、表建立的，它提供了数据库中编排表中数据的内部方法。一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面。索引就存放在索引页面上，通常，索引页面相对于数据页面来说小得多。当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，再直接通过指针从数据页面中读取数据。4.1.1 索引的基础知识索引的基础知识 n建立索引的目的有如下几点：n（1）保证数据记录的唯一性。n（2）可以大大加快数据检索速度。n（3）可以加速表与表之间的连接。n（4）加快ORDER BY和GROUP BY操作。n（5）提高系统性能。4.1.2 索引的分类索引的分类nSQL Server中提供了以下

4、几种索引：n（1）聚簇索引n（2）非聚簇索引n（3）唯一性索引4.1.3 索引的操作索引的操作n1.使用图形工具创建索引使用图形工具创建索引 n2.使用使用CREATE INDEX创建索引创建索引4.1.3 索引的操作索引的操作n任务4-1：为分销系统数据库中的客户资料表创建一个唯一性非聚集索引index_KHMC。4.1.3 索引的操作索引的操作n详细步骤如下：图4-3 新建索引窗口n 在SQL Server Management Studio的对象资源管理器中，展开分销系统数据库中的表客户资料。右击【索引】，在弹出的快捷键菜单中选择【新建索引】。n 打开【新建索引】窗口，在【索引名称】项中

5、输入索引名称index_KHMC；在【索引类型】的下拉列表中选择“非聚集”，并启用【唯一】复选项，如图4-3。4.1.3 索引的操作索引的操作n 在输入索引名称和选择索引类型后，接着要添加索引键列。单击【添加】按钮，在弹出的窗口的列表中选择【客户名称】复选框，如图4-3。图4-4 新建的索引n单击【确定】按钮，返回【新建索引】窗口，然后再单击【新建索引】窗口的【确定】按钮，【索引】节点下便生成了一个名为index_KHMC的索引，则说明该索引创建成功。4.1.3 索引的操作索引的操作n2.使用使用CREATE INDEX创建索引创建索引n在Transact-SQL语句中可以用Create In

6、dex语句在一个已经存在的表上创建索引，语法结构如下：nCREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_namenON table|view (column ASC|DESC ,.n )n WITH ,.n n ON filegroup n:=n PAD_INDEX|nFILLFACTOR=fillfactor|nIGNORE_DUP_KEY|nDROP_EXISTING|nSTATISTICS_NORECOMPUTE|nSORT_IN_TEMPDBn4.1.3 索引的操作索引的操作n任务4-2：用Transact-SQL语句完成任务4-1。n

7、在SQLQuery窗口中执行如下命令：nUSE 分销系统nGOnCREATE UNIQUE NONCLUSTERED INDEX index_KHMC ON 客户资料(客户名称)ngo4.1.3 索引的操作索引的操作n3.查看索引信息查看索引信息n可以使用系统存储过程Sp_helpindex也可查看特定表上的索引信息。n任务4-3：使用系统存储过程Sp_helpindex也可查看客户资料表上的索引信息。n在SQLQuery窗口中执行如下命令：nUSE 分销系统nGOnexec Sp_helpindex 客户资料图图4-5 客户资料表上的客户资料表上的索引信息索引信息ngo4.1.3 索引的操作

8、索引的操作n4.删除索引删除索引n在图形化工具中，右击某个表上的某个索引，执行右键菜单命令【删除】，即可删除该索引。n使用Transact-SQL语句也可以删除索引，格式如下：nDrop Index 表名.索引名n可以用一条Drop Index语句删除多个索引，索引之间要用逗号分开。4.1.3 索引的操作索引的操作n任务4-4：用Transact-SQL语句删除客户资料表的索引index_KHMC。n在SQLQuery窗口中执行如下命令：nuse 分销系统ngonDrop Index 客户资料.Index_KHMCngo4.1.4 设置索引的选项设置索引的选项 nCreate Index语句中

9、，有很多选项，对其中几个重要选项作如下介绍。4.1.4 设置索引的选项设置索引的选项 nCreate Index语句中，有很多选项，对其中几个重要选项作如下介绍。n1.FILLFACTOR选项选项 n2.PAD_INDEX选项选项 4.1.5 分销系统中的索引分销系统中的索引n在销售模块中，经常要通过客户名称来查询销售订单表中的信息，应该给销售订单表的客户名称列创建索引Index_KHMC；也经常会通过总金额来查询销售订单表中的信息或排序，也应该给销售订单表的总金额列创建索引Index_ZJE；还经常会通过查询某段日期期间的销售订单表的信息或进行统计，还应该给销售订单表的日期列创建索引Inde

10、x_RQ。4.1.5 分销系统中的索引分销系统中的索引n任务4-5：用Transact-SQL语句给销售订单表的总金额列创建索引Index_ZJE。n在SQLQuery窗口中执行如下命令：nUSE 分销系统nGOnCREATE NONCLUSTERED INDEX index_ZJE ON 销售订单(总金额)ngo4.1.5 分销系统中的索引分销系统中的索引n任务4-6：用Transact-SQL语句给销售订单表的日期列创建索引Index_RQ。n在SQLQuery窗口中执行如下命令：nUSE 分销系统nGOnCREATE NONCLUSTERED INDEX index_RQ ON 销售订单

11、(日期)ngo4.2 分销系统视图的设计分销系统视图的设计n视图是关系数据库中提供给用户以多种角度观察数据库中数据的重要机制。视图是基于某个查询结果的虚表，用户通过视图来浏览感兴趣的数据，而数据的物理存放位置仍在基础表中。4.2.1 视图的概念视图的概念 n视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。那些用于产生视图的表叫做视图的基础表。一个视图也可以从另一个视图产生。4.2.1 视图的概念视图的概念 n使用视图有很多的优点，主要表现

12、在：n（1）视点集中n（2）简化操作n（3）定制数据n（4）合并分割数据n（5）安全性4.2.2 视图的创建 n在SQL Server中，创建视图有两种方式：一种是在SQL Server Management Studio中使用图形化的工具创建，另一种是通过Transact-SQl语句中的Create View来创建。4.2.2 视图的创建 n任务4-7：为分销系统数据库创建一个视图，要求连接销售订单表和销售订单明细表。n详细步骤如下：图4-6 创建视图并添加表n 在SQL Server Management Studio的对象资源管理器中，展开分销系统数据库，击【视图】节点，在弹出的快捷菜单

13、中选择【新建视图】命令，如图4-6。n 在【添加表】窗口中，选择要创建的视图的基础表销售订单表和销售订单明细表，再单击选择【添加】按钮。如果还需要添加其他表，是可以继续选择添加基础表，如果不再需要添加，则单击【关闭】按钮。如图4-6所示。4.2.2 视图的创建 n 在视图窗口的【关系图窗格】中，显示销售订单表和销售订单明细表的全部列信息，在此可以选择视图中查询的列，这里选择了销售订单表的日期列与销售订单明细表的销售订单号、序号、商品名称、规格型号、数量、单价、金额。对应地，在【条件窗格】中就列出了选择的列，在【显示SQL】窗格中就就显示了两个表的连接语句，表示了这个视图包含的数据内容，如图4-

14、7所示。n 单击常用工具栏的【保存】按纽，在弹出的【选择名称】窗口中输入视图名称XSMX_view，单击【确定】按纽即可。然后就可以看到【视图】节点下增加了一个视图XSMX_view。4.2.2 视图的创建 n2.用用Create View语句创建视图语句创建视图n在SQL Server中，也可以用Create View语句来创建视图，语法如下：nCREATE VIEW database_name.owner_name.view_name(column,)n WITH ENCRYPTION|SCHEMABINDING|VIEW_METADATAnASnselect_statementnWITH

15、 CHECK OPTION4.2.2 视图的创建 n任务4-8：用Transact-SQL语句完成任务4-7。n在SQLQuery窗口中执行如下命令：nUSE 分销系统nGOncreate view XSMX_viewnasnSELECT 日期,销售订单明细表.销售订单号,序号,商品名称,规格型号,数量,单价,金额 nFROM 销售订单 INNER JOIN 销售订单明细表ON 销售订单.销售订单号=销售订单明细表.销售订单号ngo4.2.3 管理视图管理视图n创建视图后，需要对视图进行管理，比如，修改视图的定义、删除不再需要的视图、参看视图的定义文本以及查看视图与其它数据库对象之间的依赖关系

16、等各方面的管理。4.2.3 管理视图管理视图n1.使用图形化的工具修改和删除视图使用图形化的工具修改和删除视图n在SQL Server Management Studio的对象资源管理器中，展开分销系统数据库的【视图】节点，右击某个视图，在弹出的右键菜单中，选择【删除】命令即可删除视图；选择【修改】命令则打开与创建视图时一样的视图窗口，对视图的修改就和创建视图的过程类似了。4.2.3 管理视图管理视图n2.用用Transact-SQL语句修改和删除视图语句修改和删除视图n在SQL Server中，可以用Alter View语句来修改视图，语法如下：nALTER VIEW .view_name

17、(column ,.n )n WITH ,.n nASn select_statement n WITH CHECK OPTION n:=n ENCRYPTION|SCHEMABINDING|VIEW_METADATA 4.2.3 管理视图管理视图n任务4-9：修改视图XSMX_view，去掉视图中的“金额”列，新增一个由数量与单价的乘积得到的“计算金额”列。nUSE 分销系统nGOnalter view XSMX_viewnasnSELECT 销售订单.日期,销售订单明细表.销售订单号,序号,商品名称,规格型号,数量,单价,数量*单价 as 计算金额 nFROM 销售订单 INNER JOI

18、N 销售订单明细表 ON 销售订单.销售订单号=销售订单明细表.销售订单号ngo4.2.3 管理视图管理视图n若要删除视图，则可以使用DROP VIEW语句，其语法格式如下：nDROP VIEW view_name ,.n n其中View_name是要删除的视图名称，可以删除多个视图。4.2.3 管理视图管理视图n若要删除视图，则可以使用DROP VIEW语句，其语法格式如下：nDROP VIEW view_name ,.n n其中View_name是要删除的视图名称，可以删除多个视图。n任务4-10：写出删除视图XSMX_view的语句。nUSE 分销系统nGOnDrop view XSMX

19、_viewnGo4.2.3 管理视图管理视图n3.查看视图信息查看视图信息nSQL Server允许用户获得视图的一些有关信息，如视图的名称、视图的创建时间、视图的定义文本等。视图的信息存放在以下几个SQL Server系统表中：nSysobjects 存放视图名称等基本信息。nSyscolumns 存放视图中定义的列。nSysdepends 存放视图的依赖关系。nSyscomments 存放视图定义的文本。n可以使用系统存储过程SP_HELP来查看视图的创建者、创建时间等基本信息。4.2.3 管理视图管理视图n任务4-11：查看视图XSMX_view的基本信息。nUSE 分销系统nGOnSP

20、_HELP XSMX_viewnGon任务4-11：查看视图XSMX_view的文本信息。nUSE 分销系统nGOnSP_HELPTEXT XSMX_viewnGo4.2.4 视图的应用视图的应用 n利用视图可以完成某些和基础表相同的操作。通过视图可以对基础表中的数据进行检索、添加、修改和删除。4.2.4 视图的应用视图的应用 n使用视图维护数据时，需要注意以下几点：n（1）修改视图中的数据时，不能同时修改两个或者多个基表，可以对基于两个或多个基表或者视图的视图进行修改，但是每次修改都只能影响一个基表。n（2）不能修改那些通过计算得到的字段。n（3）如果在创建视图时指定了WITH CHECK

21、OPTION选项，那么所有使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。n（4）执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中。n（5）如果视图引用多个表时，无法用DELETE命令删除数据。4.2.4 视图的应用视图的应用 n1.通过视图检索表数据通过视图检索表数据n建立视图的一个最主要目的，是为了方便查询。将一些特定要求的查询或一些复杂表连接的查询定义为视图后，通过查询视图就可以很方便地查询到所关心的表数据。查询视图的操作跟查询基础表的操作一样。4.2.4 视图的应用视图的应用 n任务4-12：通过视图XSMX_view查询销售订单号为“

22、200710230018”的订单明细信息的销售订单号、商品名称、计算金额。nUSE 分销系统ngonselect 销售订单号,商品名称,计算金额 from XSMX_view nwhere 销售订单号=200710230018ngo4.2.4 视图的应用视图的应用 n2.通过视图添加表数据通过视图添加表数据n向视图插入数据时，使用INSERT语句，语法格式跟表操作完全一样，只需要将表名称改成视图名称即可。4.2.4 视图的应用视图的应用 n任务4-13：创建视图FKD_view，该视图包含付款单表中的所有记录的非空列（付款单号,付款日期,付款人,付款金额），然后往该视图添加一条记录。nUSE

23、分销系统ngoncreate view FKD_viewnas nselect 付款单号,付款日期,付款人,付款金额 from 付款单ngoninsert FKD_view values(F200804020023,2008-04-02,章艺谋,11000)ngo4.2.4 视图的应用视图的应用 n3.通过视图修改表数据通过视图修改表数据n使用UPDATE语句可以通过视图修改基础表的数据。语法格式与表操作完全一样。4.2.4 视图的应用视图的应用 n任务4-14：修改视图FKD_view中付款单号为“F200804020023”的付款金额为10000。nUSE 分销系统ngonUPDATE F

24、KD_view set 付款金额=10000 nwhere 付款单号=F200804020023ngo4.2.4 视图的应用视图的应用 n4.通过视图删除表数据通过视图删除表数据n使用DELETE语句可以通过视图删除基础表的数据。语法格式与表操作完全一样 4.2.4 视图的应用视图的应用 n任务4-15：删除视图FKD_view中付款人为“章艺谋”的付款记录。nUSE 分销系统ngondelete from FKD_view nwhere 付款人=章艺谋ngo3.2.5 分销系统中的视图分销系统中的视图n在商品出仓环节中，对于商品核对人员来说，他要清楚某个订单的商品明细，核对具体的商品编号、商

25、品名称、规格型号、单位和数量，而对于单价和金额则不是他的核对范围 4.2.5 分销系统中的视图分销系统中的视图n任务4-16：为商品核对人员创建一个视图，命为CCHD_view。nUSE 分销系统nGOncreate view CCHD_viewnasnSELECT 销售订单号,序号,商品编号,商品名称,规格型号,单位,数量 nFROM 销售订单明细表 ngo4.2.5 分销系统中的视图分销系统中的视图n任务4-16：为商品核对人员创建一个视图，命为CCHD_view。nUSE 分销系统nGOncreate view CCHD_viewnasnSELECT 销售订单号,序号,商品编号,商品名称,规格型号,单位,数量 nFROM 销售订单明细表 ngo