《SQL server数据库》课件第5章 视图及应用 .ppt

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

2、可以采用引用表所使用的方法，在SQL语句中引用视图名称来使用此虚拟表。视图是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。和真实的表一样，视图也包括几个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据行来源于其所引用的表。因此，视图不是真实存在的基础表而是一个虚拟表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。5.2.2 视图的优点和作用视图的优点和作用 1可以使用视图集中数据、简化和定制不同用户对数据库的不同数据要求。2使用视图可以屏蔽数据的复杂性，方便用户对数据的操作，用户不必了解数据库的结构，就可以方便地使用和管理

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

4、用户以不同的方式看到不同或者相同的数据集。5.3 创建视图创建视图 创建视图时应该注意以下情况：（1）只能在当前数据库中创建视图。（2）如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。（3）如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义别名。（4）不能在视图上创建索引，不能在规则、缺省、触发器的定义中引用视图。（5）当通过视图查询数据时，SQL Server要检查以确保语句中涉及的所有数据库对象存在，而且数据修改语句不能违反数据完整性规则。（6）视图的名称必须遵循标识符的规则，且对每个用户必须是唯一的。此外，该名称不得与该用户

5、拥有的任何表的名称相同。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“添加表”对话框5.3.1 使用使用SQL Server Management Studio创建视图创建视图 3在

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

7、筛选条件等，如图5-4所示。图5-4“视图设计器”窗口 5.3.1 使用使用SQL Server Management Studio创建视图创建视图 5设置完成后，单击工具栏上的【保存】按钮，保存视图，完成视图的创建，如图5-5所示。图5-5 保存视图5.3.1 使用使用SQL Server Management Studio创建视图创建视图 5.3.2 使用使用SQL命令创建视图命令创建视图 语法形式：CREATE VIEW view_name (column ,.n )WITH ENCRYPTION AS select_statement WITH CHECK OPTION 其中：view

8、_name指定视图的名称。column指定视图中的列名。若没有指定，其列名由SELECT命令指派，即为SELECT命令中的列名。注意：视图中的列名个数得与SELECT命令中的列项数相同。WITH ENCRYPTION表示 SQL Server 加密包含 CREATE VIEW 语句文本在内的系统表列。select_statement用于创建视图的SELECT语句，利用SELECT命令可以从表中或者视图中选择列构成新视图的列。WITH CHECK OPTION用于强制视图上执行的所有数据修改语句都必须符合由 select_statement 设置的准则。【例例5-1】在数据库Teaching中，

9、基于表sgrade建立一视图，视图名为sview_1，要求只能是中文、英文和数学都及格的学生记录的学号，中文，英文，数学及这三门的平均成绩。Use TeachingGo Create View sview_1 AS Select xh,zw,yw,sx,(zw+yw+sx)/3 pjcj From sgrade Where zw=60 and yw=60 and sx=60Go【例例5-2】在数据库Teaching中，基于表sgrade建立一视图，视图名为sview_2，要求只能是各个系和各个系的学生人数、学生的平均英语成绩。要求重新命名视图的列，并加密视图。(学生自主练习，具体实现参考教材P

10、103)5.3.2 使用使用SQL命令创建视图命令创建视图【例例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

11、(教授,副教授)Go【例例5-4】在大学生选课管理数据库Student中，建立一视图，视图名为st_view2，该视图含有所选修的课程至少有一门及格的学生的学号、姓名、入学时间和所属系，且按他们入学时间的先后顺序排列。(学生自主练习，具体实现参考教材P104)5.3.2 使用使用SQL命令创建视图命令创建视图 5.4 操作视图操作视图 5.4.1 使用使用SQL Server Management Studio操作视图操作视图 1修改视图修改视图（1）启动SQL Server Management Studio，并连接到SQL Server 2008中的数据库，在“对象资源管理器”窗口中展开“

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

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

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

15、 ASselect_statement WITH CHECK OPTION 其中各项参数同创建视图SQL命令中的一样。【例例5-6】将大学生选课管理数据库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 ASCGo

16、5.4.2 使用使用SQL命令操作视图命令操作视图3删除视图删除视图语法格式：DROP VIEW view_name 【例例5-7】将上述数据库Teaching中的视图sview_1删掉。Use Teaching Go Drop View sview_1 Go4重命名视图重命名视图语法格式：EXEC sp_rename 源视图名 ,新视图名5查看视图定义信息查看视图定义信息EXEC sp_helptext 视图名5.4.2 使用使用SQL命令操作视图命令操作视图5.5 视图的应用视图的应用 5.5.1 利用视图查询数据利用视图查询数据 因为视图本身也是一个表，因此利用视图查询数据同表的查询因为

17、视图本身也是一个表，因此利用视图查询数据同表的查询一样。这里只介绍利用视图完成一些复杂的查询操作，即先查询所需一样。这里只介绍利用视图完成一些复杂的查询操作，即先查询所需的中间数据，并形成一视图，然后再对该视图进行所需的查询操作，的中间数据，并形成一视图，然后再对该视图进行所需的查询操作，待查询完毕后，最后将视图删除。待查询完毕后，最后将视图删除。【例例5-8】从数据库Teaching中的表sgrade中，查询系学生人数在所有系的平均学生人数之上的系和该系学生的人数、平均中文，平均英文及平均数学成绩。分析：显然不能直接查询所需的数据，我们可借助于视图完成。即先查询各个系和各个系的学生人数、平均

18、中文，平均英文及平均数学成绩，并将其形成一视图st_view3。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 平均中文,pjyw平均英文,pjsx平均数学 From sview_3 Where xsrs=(Select Avg(xsrs)From sview_3)GoDrop V

19、iew sview_3Go 5.5.1 利用视图查询数据利用视图查询数据【例例5-9】在大学生选课管理数据库Student中，查询选课门数最多的学生的学号、姓名和所选课程的门数及选课平均成绩，并将查询结果按所选课的平均成绩降序排列。Use Student Go Create View st_view3ASSelect stab.xh 学号学号,stab.xm 姓名姓名,Count(*)选课门数选课门数,Avg(sctab.cj)平均成绩平均成绩 From stab ,sctab Where stab.xh=sctab.xh Group By stab.xh ,stab.xmGo/*从视图从视图

20、st_view3中查询所需的最终数据中查询所需的最终数据*/Select *From st_view3 Where 选课门数选课门数=ALL(Select 选课门数选课门数 From st_view3)Order By 平均成绩平均成绩 Desc Go Drop View st_view3 Go 5.5.1 利用视图查询数据利用视图查询数据5.5.2 利用视图修改数据利用视图修改数据 使用视图修改数据时，需要注意以下几点：（1）修改视图中的数据时，不能同时修改两个或者多个基表，可以对基于两个或多个基表或者视图的视图进行修改，但是每次修改都只能影响一个基表。（2）不能修改那些通过计算得到的字段。

21、（3）如果在创建视图时指定了WITH CHECK OPTION选项，那么所有使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。（4）执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中。（5）如果视图引用多个表时，无法用DELETE命令删除数据。1.通过视图插入记录通过视图插入记录【例例5-10】在数据库Teaching中，利用视图向表sgrade中添加一个学生记录。Use TeachingGo/*先创建一个基于表sgrade的视图sview_4*/Create View sview_4ASSelect *From sgrade Where xm=李

22、芳 Go /*通过视图sview_4向表sgrade中添加记录*/Insert into sview_4 values（100022,孙大名,男,2008-9-1,机械系 ,08制造 ,80 ,50 ,70）Go5.5.2 利用视图修改数据利用视图修改数据2.通过视图更新记录内容通过视图更新记录内容使用视图可以更新数据记录，但应该注意的是，更新的只是数据库中的基表。【例例5-11】在数据库Teaching中，创建一个基于表sgrade的视图sview_5，然后通过该视图修改表sgrade中的记录。Use TeachingGo/*先创建一个基于表sgrade的视图sview_5*/Create

23、View sview_5ASSelect *From sgrade Go/*通过视图sview_5修改表sgrade中的记录*/Update sview_5 Set xm=李丽霞 Where xm=李芳Go5.5.2 利用视图修改数据利用视图修改数据3.通过视图删除记录通过视图删除记录 使用视图删除记录，可以删除任何基表中的记录，直接利用DELETE语句删除记录即可。但应该注意，必须指定在视图中定义过的字段来删除记录。【例例5-12】在数据库Teaching中，利用视图sview_5删除表sgrade中姓名为李丽霞的记录。Use TeachingGoDelete From sview_5 Wh

24、ere xm=李丽霞 Go5.5.2 利用视图修改数据利用视图修改数据5.6 任务实现任务实现 1建立教师的有关任课信息视图建立教师的有关任课信息视图（1）在数据库Student中，建立一视图st_view4，该视图能查询当前所有任课教师的编号、姓名、性别、职称和年龄信息。Use StudentGoCreate View st_view4 AS Select jsh 编号,xm 姓名,xb 性别,zc 职称,year(getdate()-year(csrq)年龄 From ttab Where jsh IN(Select Distinct jsh From sctab)Go（2）在数据库Stu

25、dent中，建立一视图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 AND ctab.kch=tctab.kchGo5.6 任务实现任务实现（3）在数据库Student中，建立一视图st_view6，该视图能查询每个教师的编号、姓名、职称和所能够担任课程的门数。Use Stude

26、ntGoCreate 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附注：也可使用子查询完成。(学生自主练习，具体实现参考教材P110)5.6 任务实现任务实现（4）在数据库Student中，建立一视图st_view7，该视图能查询当前每个任课教师的编号、姓名和所正讲授的课程门数及所教的学生人数。Use StudentGoCreate View s

27、t_view7ASSelect ttab.jsh 教师号 ,ttab.xm 姓名,Count(DISTINCT sctab.kch)讲课门数,Count(DISTINCT sctab.xh)学生人数 From ttab ,sctab Where ttab.jsh=sctab.jsh Group By ttab.jsh ,ttab.xmGo5.6 任务实现任务实现 2建立学生的有关选课信息视图建立学生的有关选课信息视图（1）在数据库Student中，建立一视图st_view8，该视图能查询每个选课学生的学号、姓名、所选课程和该课程所选的任课教师及所选课程成绩。Use StudentGoCreat

28、e View st_view8ASSelect stab.xh 学号,stab.xm 姓名,ctab.kcm 课程,ttab.xm 教师,sctab.cj 成绩 From stab,ctab,ttab,sctab Where stab.xh=sctab.xh AND ctab.kch=sctab.kch AND ttab.jsh=sctab.jshGo5.6 任务实现任务实现（2）在数据库Student中，建立一视图st_view9，该视图能查询每个学生的学号、姓名、所属系和所选课程的门数及平均成绩。Use StudentGoCreate View st_view9ASSelect xh 学号

29、,xm 姓名,ssx 所属系,(Select Count(*)From sctab Where xh=stab.xh)选课门数,(Select Avg(cj)From sctab Where xh=stab.xh)平均成绩 From stab Go5.6 任务实现任务实现 3建立每门课程被选修的状况视图建立每门课程被选修的状况视图在数据库Student中，建立一视图st_view10，该视图能查询每门课程的编号、名称、所选学生人数和他们（选这门课）的平均成绩。要求把无人选修课程的相应信息加在后面。Use StudentGo Create View st_view10ASSelect ctab.kch 课程号 ,ctab.kcm 课程名,Count(*)选修人数,Avg(sctab.cj)平均成绩 From ctab ,sctab Where ctab.kch=sctab.kchGroup By ctab.kch ,ctab.kcmUNIONSelect kch ,kcm ,0 ,0 From ctab Where kch NOT IN(Select Distinct kch From sctab)Go5.6 任务实现任务实现