项目六公司管理数据库系统中视图和索引的应用.ppt

《项目六公司管理数据库系统中视图和索引的应用.ppt》由会员分享，可在线阅读，更多相关《项目六公司管理数据库系统中视图和索引的应用.ppt（45页珍藏版）》请在三一文库上搜索。

1、项目六 公司管理数据库系统中视图和索引的应用,总体教学目标：会利用视图操作公司管理数据库系统的数据；会利用索引提高公司管理数据库系统的检索速度，改善数据库的性能 。,模块1 公司管理数据库系统中 视图的应用,教学目标： 1、能正确理解视图的含义和作用。 2、会使用企业管理器和TransactSQL语句管理视图。 3、会利用视图操作数据表的数据。,工作任务,1、创建有关女职工的信息的视图，包括雇员ID、姓名、性别、出生年月和特长等信息。 2、创建有关客户订购产品信息的视图，包括客户名称、产品名、订购数量等信息。 3、创建有关雇员接受订单信息的的视图，包括雇员姓名、客户名称、产品名称、订购数量。

2、4、创建有关订购“鼠标”的客户信息的视图，包括公司名称, 联系人姓名, 数量。,一、视图的概述,视图是从一个或多个表（物理表）中导出的虚表（简称虚表）,包括数据列和数据行，这些数据列和数据行来源于其所引用的表（称作视图的基表），用户通过视图来浏览表中感兴趣的部分或全部数据，而数据的物理存放位置仍然在视图所引用的基表中，视图中的保存的只是SELECT查询语句。 视图一经定义后，就可以像表一样被查询、修改、删除和更新。,视图的优点：,（1）屏蔽数据的复杂性、简化数据操作。 （2）为用户提供相关的数据。 （3）提供安全保护功能。,二、 创建视图,创建视图时应该注意以下情况： 只能在当前数据库中创建视

3、图。 如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。 如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义别名。 当通过视图查询数据时，SQL Server要检查以确保语句中涉及的所有数据库对象存在，而且数据修改语句不能违反数据完整性规则。 视图的名称必须遵循标识符的规则。,1. 利用向导创建视图,【例1】创建有关产品的类别、产品名、单价和库存量有关的视图。 操作步骤： 在“工具”菜单上单击“向导”命令。 展开“数据库”文件夹。双击“创建视图向导”。 完成向导中的步骤。,2. 利用企业管理器创建视图（1） 查询设计器和视图设计器都

4、由四个窗格组成：关系图窗格、网格窗格、SQL 窗格以及结果窗格。,关系图窗格：显示正在查询的表和其它表结构化对象。每个矩形代表一个表或表结构化对象，并显示可用的数据列以及表示每列如何用于查询的图标。 网格窗格：包含一个类似电子表格的网格，用户可以在其中指定选项，比如要显示哪些数据列、要选择什么行、如何对各行进行分组等等。,2. 利用企业管理器创建视图（2）,2. 利用企业管理器创建视图（3）,SQL 窗格：显示用于查询或视图的 SQL 语句。可以对设计器所创建的 SQL 语句进行编辑，也可以输入自己的 SQL 语句。 结果窗格：显示含有由查询或视图检索的数据的网格。在查询设计器中，该窗格显示最

5、近执行的选择查询的结果。,任务1 选择employee表中的雇员id、姓名和出生日期四个字段，以及product表中的产品id、产品名和类别id三个字段创建名为v_employee的视图（要求用企业管理器完成）。 操作步骤： 单击要创建视图的companyinfo数据库。 【新建】|【视图】【添加表】命令，将打开“添加表”对话框。 在 “添加表”对话框中，选中要创建视图的基表，选择所需要的字段。 设置字段限制。 单击“运行”按钮。 单击工具栏中的“保存”按钮。,3. 利用CREATE VIEW命令创建视图,语法： CREATE VIEW view_name ( column ,.n ) WIT

6、H ENCRYPTION AS select_statement WITH CHECK OPTION ,参数说明：,view_name:视图的名称 Column:用于指定视图中的字段名称。如果未指定 column，则视图列将获得与 SELECT 语句中的列相同的名称。 WITH ENCRYPTION：在syscomments系统表对视图的文本进行加密。 select_statement：用于创建视图的SELECT语句。 WITH CHECK OPTION:用于强制执行准则。,任务2 利用employee表中的全部字段和记录创建名为view1_employee的视图（要求用sql语句完成）。 U

7、SE copanyinfo GO CREATE VIEW view1_employee AS SELECT * FROM employee GO,任务3 选择employee的雇员id、姓名、性别出生年月和雇佣日期字段，以及所有女职工的记录创建名为view2_employee的视图（要求用sql语句完成）。 程序清单： CREATE VIEW view2_employee as SELECT employee. 雇员id， employee.姓名， employee. 性别， employee. 出生年月， employee. 雇用日期 FROM employee Where employee

8、.性别=女,三、 修改和重命名视图,1. 修改视图 （1）利用企业管理器修改视图。 （2）使用ALTER VIEW语句修改视图 。 语法： ALTER VIEW view_name (column,.n) WITH ENCRYPTION AS select_statement WITH CHECK OPTION ,【例2】将任务3显示女职工信息改为显示男职工信息。,ALTER VIEW view2_employee AS SELECT employee. 雇员id， employee.姓名， employee. 性别， employee. 出生年月， employee. 雇用日期 FROM e

9、mployee Where employee.性别=男,2. 重命名视图,（1）利用企业管理器重命名视图 。 （2）可以使用系统存储过程sp_rename修改视图的名称，该过程的语法形式如下： sp_rename old_name,new_name 【例3】把视图v_employee重命名为view_employee。其程序清单如下： sp_rename v_employee, view_employee,3、查看视图信息,可以使用系统存储过程sp_help显示视图特征，使用sp_helptext显示视图在系统表中的定义。 语法： （1）sp_help 数据库对象名称 （2）sp_helpte

10、xt 视图（触发器、存储过程）,四. 删除视图,（1）使用企业管理器删除视图。 （2）使用Transact-SQL语句DROP VIEW删除视图的语法形式如下： DROP VIEW view_name ,n 【例4】同时删除companinfo数据库中的视图titleview和CAonly 。 drop view 1_employee , view2_employee,五、通过视图修改记录,需要注意以下几点： 不能同时修改两个或者多个基表。 不能修改那些通过计算得到的字段。 如果在创建视图时指定了WITH CHECK OPTION选项，那么所有使用视图修改数据库信息时，必须保证修改后的数据满足

11、视图定义的范围。 执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中。 如果视图引用多个表时，无法用DELETE命令删除数据。,1插入数据记录,【例5】利用已创建的view2_employee视图，向表中添加一条新的数据记录。该记录对应的值为：(008, 王玉, 女，, 1980-1-1，2009-3-3)。 INSERT INTO view2_employee VALUES(008, 王玉, 女，, 1980-1-1，2009-3-3) 说明：当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。,2更新数据记录,注意：更新的只是数据库中

12、的基表。 不能同时修改两个或者多个基表，可以对基于两个或多个基表或者视图的视图进行修改，但是每次修改都只能影响一个基表。 不能修改那些通过计算得到的字段 如果指定了WITH CHECK OPTION选项，必须保证修改后的数据满足视图定义的范围。,【例6】利用已创建的视图view2_employee ，修改employee表中王玉的姓名为王玉玉。,UPDATE view2_employee SET 姓名 =王玉玉 WHERE 姓名=王玉,3 删除数据记录,如果视图引用多个表时，无法用DELETE命令删除数据。 【例7】利用视图view2_employee,删除表employee中姓名为王玉玉的记

13、录。 DELETE FROM girl_view WHERE 姓名= 王玉玉,模块2 公司管理数据库系统中 索引的应用,一、教学目标 1能正确理解索引的概念、作用和分类。 2会使用企业管理器和TransactSQL语句创建和管理索引。,二、工作任务,通过在公司管理数据库系统中创建索引，达到提高数据检索速度，改善数据库性能的目的。 1为employee表创建立一个姓名的惟一非聚集索引文件。 2为表employee创建立一个姓名和出生日期的复合惟一聚集索引。 。,（一）索引的概述,索引是一个单独的、物理的数据库结构，它能对表中的一个或者多个字段建立一种排序关系，以加快在表中查询数据的速度。索引是依

14、赖于表建立的，它提供了数据库中编排表中数据的内部方法。一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面。索引就存放在索引页面上。,1、使用索引的优点： 加快数据检索速度。 创建惟一索引可以保证数据记录的唯一性。 2、使用所有的缺点： 创建索引要花费时间 每个创建的索引连同原先的数据源（表）都需要磁盘空间来存放数据。 每次修改数据时索引都需要更新。,3、索引分类,簇索引 非簇索引 1）簇索引（也可称为聚集索引、簇集索引） 在簇索引中，表中的行的物理存储顺序和索引顺序完全相同。每个表只允许建立一个簇索引。数据按列进行排序，然后再重新存储到磁盘上。建立簇索引改变表中的数

15、据行的物理顺序。,簇索引的结构,注意：以姓名为关键字,2）非聚集索引,非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。非聚集索引的叶节点存储了组成非聚集索引的关键字值和一个指针，指针指向数据页中的数据行，该行具有与索引键值相同的列值。 在默认情况下，CREATE INDEX建立的索引为非聚集索引。从理论上，一个表可以建立最多249个非聚集索引。,非簇索引的结构,3、用户创建索引的原则,一 、在下列几种情况下应考虑使用簇索引： 包含大量非重复列的值； 使用between、=等比较运算符返回一个返回值的查询； 在经常用在连接的列上； 二、在下列几种情况下应考虑

16、使用非簇索引： 包含大量非重复列的值； 经常返回精确匹配的查询的搜索条件中使用的列； 在经常需要经行连接和分组操作的列上；,（二）索引的创建和管理,数据库系统的索引有两种渠道生成： 系统自动生成的索引； 用户创建的索引 利用索引向导创建索引 利用Transact-SQL语句中的CREATE INDEX命令创建索引。,任务1 利用企业管理器为employee表创建立一个姓名的非聚集索引文件。,1在企业管理器中，选择要创建索引的表，用右键的快捷菜单中选择【所有任务】|【管理索引】选项，将打开“管理索引”对话框。 2在打开的“管理索引”对话框中，单击“新建”按钮，将打开 “新建索引”对话框。 3在“

17、新建索引”对话框中的“索引名称”文本框中输入新建索引的名称，这里输入“i_xm_enployee”。,（三）利用TransactSQL语句创建和管理索引,使用T-SQL语句中的 CREATE INDEX，既可以创建聚集索引或非聚集索引。 语法： CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX 索引名 ON 表名|视图名 (列名 ASC | DESC ,.n),提示：在SQL Server 2000数据库中，为表定义一个主键，将会自动在主键所在列上创建一个唯一索引，称之为主键索引。主键索引是唯一索引的特殊类型。,参数说明如下：,UNIQUE：用于指定为表

18、或视图创建唯一索引，即不允许存在索引值相同的两行。 CLUSTERED：用于指定创建的索引为聚集索引。 NONCLUSTERED：用于指定创建的索引为非聚集索引。,任务 2 创建姓名和出生日期的复合索引文件。,程序清单： CREATE INDEX i_xmrq_employee ON employee (姓名,出生年月),任务3 为表employee创建立一个雇员ID的唯一聚集索引。,程序清单： CREATE UNIQUE CLUSTERED INDEX number_ind ON employees (雇员ID),（四） 用企业管理器创建查看索引,方法： 用企业管理器创建查看索引 用系统存储过程查看和更改索引名称,1、用企业管理器创建查看索引,2、用系统存储过程查看和更改索引名称,系统存储过程sp_helpindex可以返回表的所有索引信息。 语法如下： sp_helpindex objname=name objname=name：指定当前数据库中的表的名称。 【例14】 sp_helpindex employee,（五）使用企业管理器重命名索引,（六）删除索引,方法： 使用企业管理器删除索引 使用T-SQL语句中的DROP INDEX。 语法： DROP INDEX 表名.索引名 | 视图名.索引名 ,.n ,