视图及其应用.docx

1、视图及其应用第5章 视图及其应用 教学目标通过本章学习，使学生掌握视图的基本概念和作用，掌握视图的建立、修改和操作方法，会根据实际问题的需要，能够熟练地建立相关视图，会利用视图查询数据和修改数据。 教学要求知识要点能力要求关联知识视图概念和作用(1)掌握视图的基本概念和作用视图概念和作用视图的建立和操作(1)掌握视图的建立方法(2)掌握视图的操作方法SQL Server Management Studio建立和操作视图，CREATE VIEW，ALTER VIEW等SQL命令视图的应用(1) 掌握利用视图查询数据的方法(2) 掌握利用视图修改数据的方法视图的应用 重点难点 视图的概念和作用 视

2、图的建立和操作方法 利用视图查询数据的方法5.1任务描述本章完成项目的第5个任务：在大学生选课管理数据库Student中，完成如下操作：1建立教师的有关任课信息视图。2建立学生的有关选课信息视图。3建立每门课程被选修的状况视图。5.2视图综述5.2.1 视图的基本概念视图可以被看成是虚拟表或存储查询。除非是索引视图，否则视图的数据不会作为非重复对象存储在数据库中。数据库中存储的是Select 语句。Select语句的结果集构成视图所返回的虚拟表。用户可以采用引用表所使用的方法，在SQL语句中引用视图名称来使用此虚拟表。视图是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上

3、的。和真实的表一样，视图也包括几个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据行来源于其所引用的表。因此，视图不是真实存在的基础表而是一个虚拟表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。 5.2.2 视图的优点和作用1可以使用视图集中数据、简化和定制不同用户对数据库的不同数据要求。用户使用数据库中的数据时，最关心对自己有用的信息。对于庞大的数据，用户可只将自己所需的数据集中到一个视图内，而那些不需要的或者无用的数据则不在视图中显示，从而集中精力处理有用的数据。2使用视图可以屏蔽数据的复杂性，方便用户对数据的操作，用户不必了解数据库的结构，就

4、可以方便地使用和管理数据，简化数据权限管理和重新组织数据以便输出到其他应用程序中。3视图便于组织数据导出，当需要将多个表中的相关数据导出时，可以将数据集中到一个视图内，通过视图导出相关数据，从而简化了数据的交换操作，也大大地简化了用户对数据的操作。 4在某些情况下，由于表中数据量太大，因此在表的设计时常将表进行水平或者垂直分割，但表的结构的变化会对应用程序产生不良的影响。 5视图提供了一个简单而有效的安全机制，能够对数据提供安全保护，视图可以定制显示数据库中的数据信息。因此，数据库管理者为用户创建视图时，就可以只将允许用户使用的数据加入视图。再通过设置有关权限，使用户不能访问基表。6视图可以跨

5、服务器组合分区数据，在视图中可以使用UNION集合运算符，将两个或多个查询结果集组合到一个单一的结果集中，方便用户使用。视图还可以让不同的用户以不同的方式看到不同或者相同的数据集。5.3创建视图创建视图时应该注意以下情况： （1）只能在当前数据库中创建视图。（2）如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。（3）如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义别名。（4）不能在视图上创建索引，不能在规则、缺省、触发器的定义中引用视图。（5）当通过视图查询数据时，SQL Server要检查以确保语句中涉及的所有数据库对象存在，

6、而且数据修改语句不能违反数据完整性规则。（6）视图的名称必须遵循标识符的规则，且对每个用户必须是唯一的。此外，该名称不得与该用户拥有的任何表的名称相同。5.3.1 使用SQL Server Management Studio创建视图1启动SQL Server Management Studio，并连接到SQL Server 2008中的数据库，在“对象资源管理器”窗口中展开“数据库”节点，再展开新建视图所属的数据库名（比如Student），右击其“视图”节点，出现弹出菜单，如图5-1所示。图5-1 新建视图2在弹出菜单中，执行【新建视图】命令，系统弹出“添加表”对话框,如图5-2所示。图5-2

7、“添加表”对话框3在添加表对话框中，选择创建视图所用的表名和视图名，单击“添加”按钮，将表添加到视图设计器中，在视图设计器窗口的显示区域内显示出新加表的所有字段。添加完毕后，最后关闭“添加表”对话框，系统出现“视图设计器”界面窗口，如图5-3所示。图5-3“视图设计器”窗口4在视图设计器窗口中，从添加表中选择视图中显示的字段显示于“列”项中，可通过“别名”项设置相关字段的别名，可通过“输出”选项设置是否输出相关字段的值，可通过“排序类型”项下的下拉框设置视图的排序字段及排序类型，可通过“筛选器”项下的输入框输入视图记录的筛选条件等，如图5-4所示。 图5-4“视图设计器”窗口5设置完成后，单击

8、工具栏上的【保存】按钮，保存视图，完成视图的创建，如图5-5所示。图5-5 保存视图5.3.2 使用SQL命令创建视图语法形式：CREATE VIEW view_name ( column ,.n ) WITH ENCRYPTION AS select_statement WITH CHECK OPTION 其中： view_name指定视图的名称。 column指定视图中的列名。若没有指定，其列名由SELECT命令指派，即为SELECT命令中的列名(注意：视图中的列名个数要与SELECT命令中的列项数相同)。 WITH ENCRYPTION表示 SQL Server 加密包含 CREATE

9、VIEW 语句文本在内的系统表列。 select_statement用于创建视图的SELECT语句，利用SELECT命令可以从表中或者视图中选择列构成新视图的列。 WITH CHECK OPTION用于强制视图上执行的所有数据修改语句都必须符合由 select_statement 设置的准则。【例5-1】在数据库Teaching中，基于表sgrade建立一视图，视图名sview_1，要求只能是中文、英文和数学都及格的学生记录的学号，中文、英文、数学成绩及这三门课程的平均成绩。Use TeachingGo Create View sview_1 AS Select xh , zw , yw ,

10、sx , (zw+yw+sx)/3 pjcj From sgrade Where zw=60 and yw=60 and sx=60Go【例5-2】在数据库Teaching中，基于表sgrade建立一视图，视图名为sview_2，要求该视图含有各个系和各个系的学生人数、学生的平均英语成绩。要求重新命名视图的列，并加密视图。Use TeachingGo Create View sview_2 (szx , xsrs , pjyw) WITH ENCRYPTION /*加密视图*/AS Select szx , Count(*) , Avg(yw) From sgrade Group By szx

11、Go【例5-3】在大学生选课管理数据库Student中， 建立一视图，视图名为st_view1，要求该视图含有所有教授和副教授的编号、姓名、年龄和他们所能够讲授课程的编号及相应酬金。Use StudentGo Create View st_view1ASSelect ttab.jsh 教师号 , ttab.xm 姓名 , year(getdate()-year(ttab.csrq) 年龄 , tctab.kch 课程号 , tctab.cj 酬金 From ttab , tctab Where ttab.jsh=tctab.jsh AND ttab.zc IN(教授 , 副教授)Go【例5-4

12、】在大学生选课管理数据库Student中， 建立一视图，视图名为st_view2，要求该视图含有所选修的课程至少有一门及格的学生的学号、姓名、入学时间和所属系，且按他们入学时间的先后顺序排列。Use StudentGo Create View st_view2ASSelect xh , xm , rxsj , ssx From stab Where xh IN(Select Distinct xh From sctab Where cj=60) Order By rxsj ASCGo5.4操作视图5.4.1 使用SQL Server Management Studio操作视图1修改视图（1）启

13、动SQL Server Management Studio，并连接到SQL Server 2008中的数据库，在“对象资源管理器”窗口中展开“数据库”节点，再展开修改的视图所属的数据库名（比如Student），再展开其“视图”节点，右击要修改的视图名，系统出现弹出菜单，如图5-6所示。图5-6 修改视图（2）执行弹出菜单中的【设计】命令，则进入“视图设计器”窗口，如上图5-4所示，在视图设计器窗口中，修改视图的有关选项即可，同创建视图一样，修改完毕后，要重新保存视图。2查看视图的定义信息同修改视图的操作一样，只是查看视图的定义信息，而不进行修改。3删除或重命名视图或查看视图属性（1）启动SQL

14、 Server Management Studio，并连接到SQL Server 2008中的数据库，在“对象资源管理器”窗口中展开“数据库”节点，再展开操作的视图所属的数据库名（比如Student），展开其“视图”节点，右击要删除或重命名或查看属性的视图名，系统出现弹出菜单，如上图5-6所示。（2）执行弹出菜单中的【删除】命令，可删除视图。（3）执行弹出菜单中的【重命名】命令，可重命名视图。（4）执行弹出菜单中的【属性】命令，可查看视图的属性。4查看视图的内容因为视图本身也是一个表，因此查看其内容同表操作一样，执行上图5-6弹出菜单中的【编辑前200行】命令即可查看视图内容。5.4.2 使用

15、SQL命令操作视图1查看视图内容使用视图查看有关数据信息，同查看表内容一样。语法格式：Select * From 视图名【例5-5】在大学生选课管理数据库Student中， 通过视图st_view1查看所有教授和副教授的编号、姓名、年龄和他们所能够讲授课程的编号及相应酬金。Use StudentGoSelect * From st_view1Go2. 修改视图语法格式：ALTER VIEW view_name (column,.n) WITH ENCRYPTION ASselect_statement WITH CHECK OPTION 其中各项参数同创建视图SQL命令中的一样。【例5-6】将

16、大学生选课管理数据库Student中的视图st_view2的筛选条件变为“xh NOT IN(Select Distinct xh From sctab)”，并给视图加密。Use StudentGo Alter View st_view2WITH ENCRYPTIONASSelect xh , xm , rxsj , ssx From stab Where xh NOT IN(Select Distinct xh From sctab ) Order By rxsj ASCGo3删除视图语法格式：DROP VIEW view_name 【例5-7】将上述数据库Teaching中的视图sview

17、_1删掉。 Use Teaching Go Drop View sview_1 Go4重命名视图语法格式：EXEC sp_rename 源视图名 , 新视图名5查看视图定义信息EXEC sp_helptext 视图名5.5视图的应用5.5.1 利用视图查询数据因为视图本身也是一个表，因此利用视图查询数据同表的查询一样。这里只介绍利用视图完成一些复杂的查询操作，即先查询所需的中间数据，并形成一视图，然后再对该视图进行所需的查询操作，待查询完毕后，最后将视图删除。【例5-8】从数据库Teaching中的表sgrade中，查询系学生人数在所有系的平均学生人数之上的系和该系学生的人数，及学生平均中文成

18、绩、平均英文成绩、平均数学成绩。分析：显然不能直接查询所需的数据，可借助于视图完成。即先查询各个系和各个系的学生人数，及平均中文成绩，平均英文成绩、平均数学成绩，并将其形成一视图sview_3。Use TeachingGoCreate View sview_3(szx , xsrs , pjzw , pjyw , pjsx)ASSelect szx , Count(*) , Avg(zw) , Avg(yw) , Avg(sx) From sgrade Group By szxGo/*从视图sview_3中查询所需的最终数据*/Select szx 所在系 , xsrs 学生人数 , pjzw

19、 平均中文 , pjyw平均英文 , pjsx平均数学 From sview_3 Where xsrs=(Select Avg(xsrs) From sview_3)GoDrop View sview_3Go【例5-9】在大学生选课管理数据库Student中，查询选课门数最多的学生的学号、姓名、所选课程的门数及选课平均成绩，并将查询结果按所选课的平均成绩降序排列。分析：显然不能直接查询所需的数据，可借助于视图完成。即先查询每个选课学生的学号、姓名、所选课程的门数及选课平均成绩，并将其形成一视图st_view3。 Use Student Go Create View st_view3ASSele

20、ct stab.xh 学号 , stab.xm 姓名 , Count(*) 选课门数 , Avg(sctab.cj) 平均成绩 From stab , sctab Where stab.xh=sctab.xh Group By stab.xh , stab.xmGo/*从视图st_view3中查询所需的最终数据*/ Select * From st_view3 Where 选课门数=ALL(Select 选课门数 From st_view3) Order By 平均成绩 Desc Go Drop View st_view3 Go附注：在进行复杂的查询时，若使用一次查询不易实现，可使用多次查询来

21、完成。一般借助于临时视图来完成，即先查询所需中间数据形成一视图，然后再借助于视图查询所需最终数据。查询完成后，最后将中间视图删除。5.5.2 利用视图修改数据使用视图修改数据时，需要注意以下几点： （1）修改视图中的数据时，不能同时修改两个或者多个基表，可以对基于两个、多个基表或者视图的视图进行修改，但是每次修改都只能影响一个基表。（2）不能修改那些通过计算得到的字段。（3）如果在创建视图时指定了WITH CHECK OPTION选项，那么在使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。（4）执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中。

22、（5）如果视图引用多个表时，无法用DELETE命令删除数据。1. 通过视图插入记录 【例5-10】在数据库Teaching中，利用视图向表sgrade中添加一个学生记录。Use TeachingGo/*先创建一个基于表sgrade的视图sview_4*/Create View sview_4ASSelect * From sgrade Where xm=李芳 Go /*通过视图sview_4向表sgrade中添加记录*/Insert into sview_4 values（100022 , 孙大名 , 男 , 2008-9-1 , 机械系 , 08制造 , 80 , 50 , 70）Go2.

23、通过视图更新记录内容使用视图可以更新数据记录，但应该注意的是，更新的只是数据库中的基表。【例5-11】在数据库Teaching中，创建一个基于表sgrade的视图sview_5，然后通过该视图修改表sgrade中的记录。Use TeachingGo/*先创建一个基于表sgrade的视图sview_5*/Create View sview_5ASSelect * From sgrade Go/*通过视图sview_5修改表sgrade中的记录*/Update sview_5 Set xm=李丽霞 Where xm=李芳Go3. 通过视图删除记录 使用视图删除记录，可以删除任何基表中的记录，直接利

24、用DELETE语句删除记录即可。但应该注意，必须指定在视图中定义过的字段来删除记录。【例5-12】在数据库Teaching中，利用视图sview_5删除表sgrade中姓名为李丽霞的记录。Use TeachingGoDelete From sview_5 Where xm=李丽霞 Go5.6 任务实现1建立教师的有关任课信息视图（1）在数据库Student中，建立一视图st_view4，该视图能查询当前所有任课教师的编号、姓名、性别、职称和年龄信息。Use StudentGoCreate View st_view4 AS Select jsh 教师号 , xm 姓名 , xb 性别 , zc

25、职称 ,year(getdate()-year(csrq) 年龄 From ttab Where jsh IN(Select Distinct jsh From sctab)Go（2）在数据库Student中，建立一视图st_view5，该视图能查询每个能任课教师的编号、姓名、职称和所能够担任的课程。 Use StudentGoCreate View st_view5 AS Select ttab.jch 教师号 , ttab.xm 姓名 , ttab.zc 职称 , ctab.kcm 能任课程 From ttab , ctab , tctab Where ttab.jch=tctab.jsh

26、 AND ctab.kch=tctab.kchGo（3）在数据库Student中，建立一视图st_view6，该视图能查询每个教师的编号、姓名、职称和所能够担任课程的门数。Use StudentGoCreate View st_view6 AS Select ttab.jsh教师号 , ttab.xm 姓名 , ttab.zc职称 , Count(*) 任课门数 From ttab , tctab Where ttab.jsh=tctab.jsh Group By ttab.jsh , ttab.xm , ttab.zc Go 附注：也可使用子查询完成Use StudentGoCreate V

27、iew st_view6ASSelect jsh 教师号 , xm 姓名 , zc 职称 , (Select Count(*) From tctab Where jsh=ttab.jsh) 任课门数 From ttab Go（4）在数据库Student中，建立一视图st_view7，该视图能查询当前每个任课教师的编号、姓名和所正讲授的课程门数及所教的学生人数。Use StudentGoCreate View st_view7ASSelect ttab.jsh 教师号 , ttab.xm 姓名 , Count(DISTINCT sctab.kch) 讲课门数 , Count(DISTINCT s

28、ctab.xh) 学生人数 From ttab , sctab Where ttab.jsh=sctab.jsh Group By ttab.jsh , ttab.xmGo2建立学生的有关选课信息视图（1）在数据库Student中，建立一视图st_view8，该视图能查询每个选课学生的学号、姓名、所选课程和该课程所选的任课教师及所选课程成绩。Use StudentGoCreate View st_view8ASSelect stab.xh 学号 , stab.xm 姓名 , ctab.kcm 课程 , ttab.xm 教师 , sctab.cj 成绩 From stab , ctab , tt

29、ab , sctab Where stab.xh=sctab.xh AND ctab.kch=sctab.kch AND ttab.jsh=sctab.jshGo（2）在数据库Student中，建立一视图st_view9，该视图能查询每个学生的学号、姓名、所属系和所选课程的门数及平均成绩。Use StudentGoCreate View st_view9ASSelect xh 学号 , xm 姓名 , ssx 所属系 , (Select Count(*) From sctab Where xh=stab.xh) 选课门数 , (Select Avg(cj) From sctab Where xh=stab.xh) 平均成绩 From stab Go3建立每门课程被选修的状况视图在数据库Student中，建立一视图st_view10，该视图能查询每门课程的编号、名称、所选学生人数和他们（选这门课）的平均成绩。要求把无人选修课程的相应信息加在后面。