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

1、会利用视图操作数据库系统的数据；会利用索引提高数据库系统的检索速度，改善数据库的性能 。模块1 公司数据库系统中视图的 应用教学目标：1、能正确理解视图的含义和作用。2、会使用企业管理器和TransactSQL语句管理视图。3、会利用视图操作数据表的数据。工作任务1、创建有关女职工的信息数据表，包括雇员ID、姓名、性别、出生年月和特长等信息。2、创建有关客户订购产品信息数据表，包括客户名称、产品名、订购数量等信息。3、创建有关雇员接受订单信息的数据表。4、创建有关订购“鼠标”的客户信息的视图。一、视图的概述 视图是一个虚表，它可以访问来自一个或多个表的列的子集。视图是从一个或多个表中派生出的数

2、据对象。派生出视图的表称为基本表。 视图一经定义后，就可以像表一样被查询、修改、删除和更新。二、 创建视图创建视图时应该注意以下情况： 只能在当前数据库中创建视图。如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。不能在视图上创建索引，不能在规则、缺省、触发器的定义中引用视图。当通过视图查询数据时，SQL Server要检查以确保语句中涉及的所有数据库对象存在，而且数据修改语句不能违反数据完整性规则。视图的名称必须遵循标识符的规则。1. 利用向导创建视图【例1】创建有关产品的类

3、别、产品名、单价和库存量有关的视图。操作步骤：在“工具”菜单上单击“向导”命令。展开“数据库”文件夹。双击“创建视图向导”。完成向导中的步骤。 2. 利用企业管理器创建视图（1） 查询设计器和视图设计器都由四个窗格组成：关系图窗格、网格窗格、SQL 窗格以及结果窗格。 关系图窗格：显示正在查询的表和其它表结构化对象。每个矩形代表一个表或表结构化对象，并显示可用的数据列以及表示每列如何用于查询的图标。网格窗格：包含一个类似电子表格的网格，用户可以在其中指定选项，比如要显示哪些数据列、要选择什么行、如何对各行进行分组等等。2. 利用企业管理器创建视图（2）2. 利用企业管理器创建视图（3）SQL

4、窗格：显示用于查询或视图的 SQL 语句。可以对设计器所创建的 SQL 语句进行编辑，也可以输入自己的 SQL 语句。结果窗格：显示含有由查询或视图检索的数据的网格。在查询设计器中，该窗格显示最近执行的选择查询的结果。任务1 创建女职工的视图（girl_view），包括雇员ID、姓名、性别、出生年月和特长等信息。操作步骤：单击要创建视图的companyinfo数据库。【新建】|【视图】【添加表】命令，将打开“添加表”对话框。在 “添加表”对话框中，选中要创建视图的基表，选择所需要的字段。设置字段限制。单击“运行”按钮。 单击工具栏中的“保存”按钮。3. 利用CREATE VIEW命令创建视图语

5、法： CREATE VIEW view_name ( column ,.n ) WITH ENCRYPTION AS select_statement WITH CHECK OPTION 参数说明：view_name:视图的名称Column:用于指定视图中的字段名称。如果未指定 column，则视图列将获得与 SELECT 语句中的列相同的名称。WITH ENCRYPTION：在syscomments系统表对视图的文本进行加密。select_statement：用于创建视图的SELECT语句。WITH CHECK OPTION:用于强制执行准则。【例2】在pubs数据库，创建有关书的标题、类型

6、、单价和出版日期的视图。 USE pubs GO CREATE VIEW titles_view AS SELECT title, type, price, pubdate FROM titles GO 【例3】创建书的平均价格视图B_TITLEs_AVG。 CREATE VIEW b_titles_avg (price_avg) AS SELECT avg(price) FROM titles_view 说明：创建视图时，源表可以是基本表，也可以是视图。【例4】显示名为 CAonly 的视图，该视图使得只对加利福尼亚州（CA）的作者应用数据修改。CREATE VIEW CAonly AS S

7、ELECT au_lname, au_fname, city, state FROM authors WHERE state = CA任务 2 创建客户订购产品信息视图，包括客户名称、产品名、订购数量等信息。程序清单：CREATE VIEW cu_orderasSELECT customer.公司名称, p_order.数量, product.产品名FROM customer INNER JOIN p_order ON customer.客户ID = p_order.客户ID INNER JOIN product ON p_order.产品ID = product.产品ID任务 3 创建有关雇

8、员接受订单信息的视图.程序清单：CREATE VIEW em_orderasSELECT em.姓名 , cu.公司名称, pd.产品名 , po.数量FROM customer AS cu JOIN p_order AS po ON cu.客户ID = po.客户ID JOIN product AS pd ON po.产品ID = pd.产品ID JOIN employee AS em ON po.雇员ID = em.雇员ID任务4 创建有关订购“鼠标”的客户信息的视图。CREATE VIEW mouse_orderASSELECT customer.公司名称, customer. 联系人姓

9、名, p_order.数量FROM customer JOIN p_order ON customer.客户ID = p_order.客户ID JOIN productON p_order.产品ID = product.产品IDWHERE (product.产品名 = 鼠标)三、 修改和重命名视图 1. 修改视图（1）利用企业管理器修改视图。（2）使用ALTER VIEW语句修改视图。语法： ALTER VIEW view_name (column,.n) WITH ENCRYPTION ASselect_statement WITH CHECK OPTION 【例5】将任务4中创建的有关订购

10、“鼠标”的客户信息的视图改为有关“打印纸”的信息。ALTER VIEW mouse_orderASSELECT customer.公司名称, customer. 联系人姓名, p_order.数量FROM customer JOIN p_order ON customer.客户ID = p_order.客户ID JOIN productON p_order.产品ID = product.产品IDWHERE (product.产品名 = 打印纸)2. 重命名视图 （1）利用企业管理器重命名视图 。（2）可以使用系统存储过程sp_rename修改视图的名称，该过程的语法形式如下： sp_renam

11、e old_name,new_name【例6】把视图MOUSE_ORDER重命名为PAPER_ORDER。其程序清单如下： sp_rename mouse_order,paper_order3、查看视图信息 可以使用系统存储过程sp_help显示视图特征，使用sp_helptext显示视图在系统表中的定义。语法：（1）sp_help 数据库对象名称（2）sp_helptext 视图（触发器、存储过程）四. 删除视图 （1）使用企业管理器删除视图。 （2）使用Transact-SQL语句DROP VIEW删除视图的语法形式如下： DROP VIEW view_name ,n 【例7】同时删除pu

12、bs数据库中的视图titleview和CAonly 。 drop view titleview , CAonly五、通过视图修改记录 需要注意以下几点： 不能同时修改两个或者多个基表。不能修改那些通过计算得到的字段。如果在创建视图时指定了WITH CHECK OPTION选项，那么所有使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中。如果视图引用多个表时，无法用DELETE命令删除数据。1、数据查询【例8】查询订购打印纸的客户信息。 select 公司名称，数量 from paper_order

13、2插入数据记录【例9】利用已创建的girl_view视图，向表中添加一条新的数据记录。该记录对应的值为：(29, 王玉, 1980-9-9，, 乒乓球)。INSERT INTO girl_viewVALUES(29, 王玉,1980-9-9,乒乓球)【例10】向paper_view视图中插入一条记录，查看运行结果。 INSERT INTO paper_view VALUES (中通,张小平,56) 说明：当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。2更新数据记录注意：更新的只是数据库中的基表。不能同时修改两个或者多个基表，可以对基于两个或多个基表或者视图的视图

14、进行修改，但是每次修改都只能影响一个基表。不能修改那些通过计算得到的字段如果指定了WITH CHECK OPTION选项，必须保证修改后的数据满足视图定义的范围。【例12】利用已创建的视图girl_view，修改employee表中李立三的姓名为张宏明。UPDATE girl_view SET 姓名 =张宏明WHERE 姓名=李立三3 删除数据记录 如果视图引用多个表时，无法用DELETE命令删除数据。【例13】利用视图gril_view,删除表employee中姓名为姜玲娜的记录。DELETE FROM girl_viewWHERE 姓名= 姜玲娜模块2 数据库系统中索引的应用一、教学目标1

15、能正确理解索引的概念、作用和分类。2会使用企业管理器和TransactSQL语句创建和管理索引。二、工作任务 通过在数据库系统中创建索引，达到提高数据检索速度，改善数据库性能的目的。1为employee表创建立一个姓名的非聚集索引文件。2为表employee创建立一个姓名和出生日期的复合索引。3为表employee创建立一个雇员ID的唯一聚集索引。（一）索引的概述 索引是SQL Server使用的内部表结构，它是基于表中的一个或多个列的值，提供对表中行的快速存取。1、使用索引的优点：加快数据检索速度。保证数据记录的唯一性。加速了表之间的连接。2、使用所有的缺点：创建索引要花费时间每个创建的索引

16、连同原先的数据源（表）都需要磁盘空间来存放数据。每次修改数据时索引都需要更新。3、索引分类簇索引非簇索引1）簇索引（也可称为聚集索引、簇集索引） 在簇索引中，表中的行的物理存储顺序和索引顺序完全相同。每个表只允许建立一个簇索引。数据按列进行排序，然后再重新存储到磁盘上。建立簇索引改变表中的数据行的物理顺序。聚集索引的结构注意：以姓名为关键字2）非聚集索引 非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。非聚集索引的叶节点存储了组成非聚集索引的关键字值和一个指针，指针指向数据页中的数据行，该行具有与索引键值相同的列值。 在默认情况下，CREATE INDEX

17、建立的索引为非聚集索引。从理论上，一个表可以建立最多249个非聚集索引。非聚集索引的结构3、用户创建索引的原则在经常需要搜索的列上；在作为主键的列上；在经常用在连接的列上；在经常使用在WHERE子句中的列。在经常需要排序的列上；在经常需要根据范围进行搜索的列上；（二）索引的创建和管理 数据库系统的索引有两种渠道生成：系统自动生成的索引；用户创建的索引利用索引向导创建索引 利用Transact-SQL语句中的CREATE INDEX命令创建索引。任务1 利用企业管理器为employee表创建立一个姓名的非聚集索引文件。1在企业管理器中，选择要创建索引的表，用右键的快捷菜单中选择【所有任务】|【管

18、理索引】选项，将打开“管理索引”对话框。2在打开的“管理索引”对话框中，单击“新建”按钮，将打开 “新建索引”对话框。3在“新建索引”对话框中的“索引名称”文本框中输入新建索引的名称，这里输入“i_xm_enployee”。（三）利用TransactSQL语句创建和管理索引 使用T-SQL语句中的 CREATE INDEX，既可以创建聚集索引或非聚集索引。语法：CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX 索引名 ON 表名|视图名 列名 ASC | DESC ,.n)提示：在SQL Server 2000数据库中，为表定义一个主键，将会自动在主键所在列上创建一个唯一索引，称之为主键索引。主键索引是唯一索引的特殊类型。参数说明如下： UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。 CLUSTERED：用于指定创建的索引为聚集索引。 NONCLUSTERED：用于指定创建的索引为非聚集索引。任务 2 创建姓名和出生日期的复合索引文件。程序清单：CREATE INDEX i_xmrq_employeeON employee (姓名,出生年月)任务3 为表employe