1、项目五 视图和索引操作,2018/9/8,项目五内容,任务1,视图操作,任务2,索引操作,用户使用视图的目的是为了集中数据，满足不同用户对数据的不同要求，简化对数据的操作，保证数据的安全性。索引的作用是可以加快数据的查询速度。本章将通过任务的形式来学习视图和索引。通过本章的学习，读者应掌握以下内容： 视图的创建、修改方法 视图的作用 视图的使用方法 索引的作用及分类 索引的创建方法,本项目以企业ERP系统的真实数据库UFDATA_999_2011中的数据表Code(科目表)、Inventory(存货档案)、so_somain(销售订单主表)、SO_SODetails (销售订单子表)表的基础上
2、完成视图和索引的操作。,任务1,视图操作,数据的分组、连接查询及排序和汇总工作是一项数据库专业操作。采购部员工基本是非计算机专业人员，不可能随时写出复杂的数据库语句。同时为了保证数据库中数据的安全性，公司也不允许普通员工直接接触到表中的数据。数据表不对普通员工开放，只有部分管理人员可查阅、操作表中的数据。但日常工作中采购部员工又必须随时查看平均报价大于5500元的各种存货的存货编码、平均报价、数量，查询结果按照平均报价降序排列，并汇总该存货的总平均报价。,1、根据任务确定查询SO_SODetails (销售订单子表)中的存货编码、平均报价、数量，而平均价格必须通过平均函数AVG()函数来实现
3、2、查询结果使用Order By子句实现排序 3、最后使用Compute子句明细行汇总 4、使用视图保存以上查询结果，既保证了数据的安全性，又简化了查询操作,1.1视图概述 1.1.1 视图的概念视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由
4、于逻辑上的原因，有些视图可以修改对应的基表，有些则不能（仅仅能查询）。,1.1.2 视图特点及注意事项视图具有如下的一些特点：简单性。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使用户不必为以后的操作每次都指定全部的条件。安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上。, 逻辑数据独立性。视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应
5、用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。视图也存在一些缺点，有如下注意事项： 性能：SQL Server必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，SQL Server也把它变成一个复杂的结合体，需要花费一定的时间。 修改限制：当用户试图修改视图的某些行时，SQL Server必须把它转化为对基本表的某些行的修改。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的。,1.2 创建视图 1.2.1 使用图形界面创建视图通过树形列表点击指定数据库下的视图
6、，单击右键选择“新建视图”菜单，首先显示添加表对话框，如下图5.1。添加完表后，点击关闭，进入图5.2所示视图定义界面。,图5.1 视图定义中添加表,在视图定义界面中选择要显示的列：ccode_name、cbook_type、bdept、bproperty，点击保存。,图5.2 视图定义,1.2.2 使用T-SQL语句创建视图视图的创建语法：CREATE VIEW schema_name . view_name (column ,.n ) WITH ,.n AS select_statement ; WITH CHECK OPTION ,【例1】 在UFDATA_999_2011数据库中，以科
7、目表(Code表)为基础建立一个视图，其名称为“V_科目”，其包含列为科目表中的所有列，筛选记录条件为“账页格式 = 外币金额式”。,CREATE VIEW V_科目AS SELECT * FROM CodeWHERE 账页格式 = 外币金额式,1.3 管理和维护视图 1.3.1 查看视图当SQL Server视图创建完成后，可以使用系统存储过程查询视图定义的信息SQL Server提供系统存储过程sp_help和sp_helptext来获取视图定义信息。sp_help系统存储过程是报告有关数据库对象、用户定义数据类型或SQL Server所提供的数据类型的信息。 语法：sp_help vie
8、w_name语法：sp_helptext view_name参数说明： (1)view_name：表示要查看的视图名。 (2)sp_helptext系统存储过程是用来显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。,【例2】 使用sp_help系统存储过程获取V_科目视图的信息。,EXEC sp_help V_科目,1.3.2 修改视图 语法格式如下： ALTER VIEW view_name ( column ,.n ) AS select_statement WITH CHECK OPTION 【例3】 将上面的“ V_科目”视图中的筛选条件变为账页格式 = 金额式,
9、ALTER VIEW V_科目AS SELECT * FROM Code WHERE 账页格式 = 金额式,1.3.3 删除视图语法格式如下： DROP VIEW view ,.n 【例4】删除视图V_科目。,DROP VIEW V_科目,1.3.4 通过视图操作数据使用视图管理表中的数据包括查询数据记录、插入数据记录、更新数据记录、删除数据记录四种操作。 1、查询数据记录对于视图，可以象使用基表一样对其进行查询。【例5】查询“V_科目”视图中全体记录2、插入数据记录向视图插入数据时，使用INSERT语句。语法格式与表操作完全一样，只需将表名称改成视图名称即可。【例6】向“V_科目”视图中插入
10、一条新记录。,SELECT * FROM V_科目,Insert into V_科目 values,3、修改数据记录:使用update命令通过视图更新基本表的数据 【例7】通过视图将科目编号为101201的账页格式改为外汇金额,update V_科目 set cbook_type=外汇金额 where ccode=101201,4、删除数据记录使用delete语句通过视图修改基本表的数据。语法格式与表操作完全一样，只需将表名改为视图名就可以了。 【例8】通过视图删除账页格式改为外币金额式的所有记录,delete from V_科目 where 账页格式 = 外币金额式,1、创建视图2、采购部员
11、工通过视图查询数据，大大简化了操作，同时保证了数据的安全性,Create view 销售订单视图 as select cinvcode,AVG(iQuotedPrice) as avg_iQuotedPrice from SO_SODetails group by cinvcode having AVG(iQuotedPrice)5500 order by avg_iQuotedPrice compute sum(AVG(iQuotedPrice),Select * from 销售订单视图,本任务主要通过任务提出任务分析相关知识，相关知识主要包括视图的概念，视图的创建、修改与删除，以及通过视图
12、来查询、插入、修改与删除表数据。,任务2,索引操作,公司每天处理的销售订单数量庞大，so_somain(销售订单主表)中存放了大量数据。工作人员的日常工作经常要用到so_somain(销售订单主表)中的数据，但由于so_somain(销售订单主表)中数据量庞大导致查询速度非常慢，如何提高了SQL Server系统的性能，加快so_somain表中数据的查询速度，减少系统的响应时间？,1、对so_somain(销售订单主表)中的主键列建立索引。2、对于so_somain(销售订单主表)经常查询的数据列cSOCode建立索引。,2.1 索引概述表的索引是一张表，保存了表中每个行的存储地址。建立了索
13、引后，SQL Server将先查找数据的地址，然后根据地址直接定位数据.索引的分类: (1)惟一索引和非惟一索引(2)聚集索引和非聚集索引(3)单列索引和复合索引,2.2 创建索引 2.2.1 使用图形界面创建索引为SO_SODetails表(销售订单子表)的主键列创建索引。通过树形列表选择指定数据库下的指定表,单击右键选择“修改”菜单,显示表结构对话框，如下图5.3。点击表结构对话框左上方的图标“管理索引/键”,显示索引定义对话框,进入图5.4所示,图5.3 表结构,在索引定义对话框中点击“添加”按钮,新建索引IX_SO_SOMain，在右边编辑区列一栏中选择要建立索引的主键ID。点击“关闭
14、”按钮回到表结构对话框，如图5.5所示,图5.4 索引定义,点击保存，保存新建的索引。索引创建完毕,图5.5表结构,2.2.2 使用T-SQL语句创建索引T-SQL的CREATE INDEX语句可以创建索引.语法格式： CREATE UNIQUE CLUSTERED|NOCLUSTRERED INDEX index_name ON table|view (columnASC|DESC ,n) ON filegroup【例1】为so_somain(销售订单主表)的主键列创建索引。,CREATE INDEX so_ind ON so_somain(ID),【例2】根据so_somain(销售订单主
15、表)的“ID”列(销售订单主表标识)和cSOCode列(销售订单号)创建复合索引。【例3】为so_somain表(销售订单主表)的“ID”列创建唯一聚集索引,CREATE INDEX xh_so_ind ON so_somain(ID，cSOCode),CREATE UNIQUE CLUSTERED INDEX soid_ind ON so_somain(ID),2.3 维护与管理索引 2.3.1 查看和维护索引 查看索引的语法格式如下： EXEC sp_helpindex 索引的维护包括： 1、重建索引在下面情况下需要重建索引：(1)数据和使用模式大幅度变化。(2)排序的顺序发生改变。(3)
16、要进行大量插入操作或已经完成。(4)使用大块I/O的查询的磁盘读次数比预料的要多。(5)由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。(6)dbcc检查出索引有问题。,2、索引统计信息的更新 在下面情况下应该运行update statistics命令： (1)数据行的插入和删除修改了数据的分布。 (2)对用truncate table删除数据的表上增加数据行。 (3)修改索引列的值。,2.3.2 删除索引DROP INDEX 表名.索引名,. 【例3】用DROP INDEX命令删除so_somain表中的“xh_so_ind”索引。用DROP INDEX命令删除索引
17、时，需要注意如下事项: （1）不能用DROP INDEX命令删除由PRIMARY KEY约束或UNIQUE约束创建的索引。这些索引必须通过删除PRIMARY KEY约束或UNIQUE约束，由系统自动删除。 （2）在删除聚集索引时，表中的所有非聚集索引都将被重建。,DROP INDEX so_somain.xh_so_ind,索引是一个列表，这个列表中包含了某个表中一列过着若干列的集合，以及这些值的记录在数据表中的储存位置的物理地址。在数据库表中查询符合某种条件的记录时，可以通过索引快速查询结果。读者通过本章的学习，应掌握索引的定义、修改和删除的方法，了解索引的分析与维护。,1、对so_soma
18、in(销售订单主表)中的主键列建立索引。2、对于so_somain(销售订单主表)经常查询的数据列cSOCode建立索引。,CREATE INDEX indx_id ON so_somain(ID),CREATE INDEX indx_cSOCode ON so_somain(cSOCode),项目小结,本项目主要介绍视图和索引的创建和使用。 （1）视图包括视图的概念、创建视图、修改视图，查询视图。利用视图更新数据和删除视图，重点掌握如何使用查询分析器对视图进行各种操作。 （2）索引是一个列表，这个列表中包含了某个表中的一列或者若干列的集合，以及这些值的记录在数据表中得存储位置的物理地址，在数据库表中查询符合某种条件的记录时，可以通过索引快速的查询结果。应掌握索引的定义，修改和删除的方法，了解索引的分析与维护。,自测习题(参考教材),
