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

第5章视图及其应用

5.1任务描述本章完成项目的第5个任务：在大学生选课管理数据库Student中，完成如下操作：1．建立教师的有关任课信息视图。2．建立学生的有关选课信息视图。3．建立每门课程被选修的状况视图。5.2视图综述

5.2.1视图的基本概念视图可以被看成是虚拟表或存储查询。除非是索引视图，否则视图的数据不会作为非重复对象存储在数据库中。数据库中存储的是Select语句。Select语句的结果集构成视图所返回的虚拟表。用户可以采用引用表所使用的方法，在SQL语句中引用视图名称来使用此虚拟表。视图是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。和真实的表一样，视图也包括几个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据行来源于其所引用的表。因此，视图不是真实存在的基础表而是一个虚拟表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。5.2.2视图的优点和作用

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

5.3创建视图

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

1．启动SQLServerManagementStudio，并连接到SQLServer2008中的数据库，在“对象资源管理器”窗口中展开“数据库”节点，再展开新建视图所属的数据库名（比如Student），右击其“视图”节点，出现弹出菜单，如图5-1所示。图5-1新建视图2．在弹出菜单中，执行【新建视图】命令，系统弹出“添加表”对话框,如图5-2所示。图5-2“添加表”对话框5.3.1使用SQLServerManagementStudio创建视图

3．在添加表对话框中，选择创建视图所用的表名和视图名，单击“添加”按钮，将表添加到视图设计器中，在视图设计器窗口的显示区域内显示出新加表的所有字段。添加完毕后，最后关闭“添加表”对话框，系统出现“视图设计器”界面窗口，如图5-3所示。图5-3“视图设计器”窗口5.3.1使用SQLServerManagementStudio创建视图

4．在视图设计器窗口中，从添加表中选择视图中显示的字段显示于“列”项中，可通过“别名”项设置相关字段的别名，可通过“输出”选项设置是否输出相关字段的值，可通过“排序类型”项下的下拉框设置视图的排序字段及排序类型，可通过“筛选器”项下的输入框输入视图记录的筛选条件等，如图5-4所示。

图5-4“视图设计器”窗口5.3.1使用SQLServerManagementStudio创建视图

5．设置完成后，单击工具栏上的【保存】按钮，保存视图，完成视图的创建，如图5-5所示。

图5-5保存视图5.3.1使用SQLServerManagementStudio创建视图

5.3.2使用SQL命令创建视图

语法形式：CREATEVIEWview_name[(column[,...n])]

[WITHENCRYPTION]AS

select_statement

[WITHCHECKOPTION]

其中：view_name指定视图的名称。column指定视图中的列名。若没有指定，其列名由SELECT命令指派，

即为SELECT命令中的列名。注意：视图中的列名个数得与SELECT命令中的列项数相同。WITHENCRYPTION表示SQLServer加密包含CREATEVIEW语句文本在内的系统表列。select_statement用于创建视图的SELECT语句，利用SELECT命令可以从表中或者视图中选择列构成新视图的列。WITHCHECKOPTION用于强制视图上执行的所有数据修改语句都必须符合由select_statement设置的准则。【例5-1】在数据库Teaching中，基于表sgrade建立一视图，视图名为sview_1，要求只能是中文、英文和数学都及格的学生记录的学号，中文，英文，数学及这三门的平均成绩。UseTeachingGoCreateViewsview_1ASSelectxh,zw,yw,sx,(zw+yw+sx)/3pjcjFromsgradeWherezw>=60andyw>=60andsx>=60Go【例5-2】在数据库Teaching中，基于表sgrade建立一视图，视图名为sview_2，要求只能是各个系和各个系的学生人数、学生的平均英语成绩。要求重新命名视图的列，并加密视图。(学生自主练习，具体实现参考教材P103)5.3.2使用SQL命令创建视图

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

Fromttab,tctabWherettab.jsh=tctab.jshANDttab.zcIN(‘教授’,‘副教授’)Go【例5-4】在大学生选课管理数据库Student中，建立一视图，视图名为st_view2，该视图含有所选修的课程至少有一门及格的学生的学号、姓名、入学时间和所属系，且按他们入学时间的先后顺序排列。(学生自主练习，具体实现参考教材P104)5.3.2使用SQL命令创建视图

5.4操作视图

5.4.1使用SQLServerManagementStudio操作视图

1．修改视图（1）启动SQLServerManagementStudio，并连接到SQLServer2008中的数据库，在“对象资源管理器”窗口中展开“数据库”节点，再展开修改的视图所属的数据库名（比如Student），再展开其“视图”节点，右击要修改的视图名，系统出现弹出菜单，如图5-6所示。图5-6修改视图（2）执行弹出菜单中的【设计】命令，则进入“视图设计器”窗口，如图5-4所示，在视图设计器窗口中，修改视图的有关选项即可，同创建视图一样，修改完毕后，要重新保存视图。2．查看视图的定义信息同修改视图的操作一样，只是查看视图的定义信息，而不进行修改。5.4.1使用SQLServerManagementStudio操作视图3．删除或重命名视图或查看视图属性（1）启动SQLServerManagementStudio，并连接到SQLServer2008中的数据库，在“对象资源管理器”窗口中展开“数据库”节点，再展开操作的视图所属的数据库名（比如Student），展开其“视图”节点，右击要删除或重命名或查看属性的视图名，系统出现弹出菜单，如上图5-6所示。（2）执行弹出菜单中的【删除】命令，可删除视图。（3）执行弹出菜单中的【重命名】命令，可重命名视图。（4）执行弹出菜单中的【属性】命令，可查看视图的属性。4．查看视图的内容因为视图本身也是一个表，因此查看其内容同表操作一样，执行上图5-6弹出菜单中的【编辑前200行】命令即可查看视图内容。5.4.1使用SQLServerManagementStudio操作视图5.4.2使用SQL命令操作视图1．查看视图内容使用视图查看有关数据信息，同查看表内容一样。语法格式：Select*From视图名【例5-5】在大学生选课管理数据库Student中，通过视图st_view1查看所有教授和副教授的编号、姓名、年龄和他们所能够讲授课程的编号及相应酬金。UseStudentGoSelect*Fromst_view1Go2.修改视图语法格式：ALTERVIEWview_name[(column[,...n])]

[WITHENCRYPTION]

AS

select_statement

[WITHCHECKOPTION]其中各项参数同创建视图SQL命令中的一样。【例5-6】将大学生选课管理数据库Student中的视图st_view2的筛选条件变为“xhNOTIN(SelectDistinctxhFromsctab)”，并给视图加密。UseStudentGoAlterViewst_view2WITHENCRYPTIONASSelectxh,xm,rxsj,ssxFromstabWherexhNOTIN(SelectDistinctxhFromsctab)OrderByrxsjASCGo5.4.2使用SQL命令操作视图3．删除视图语法格式：DROPVIEWview_name【例5-7】将上述数据库Teaching中的视图sview_1删掉。

UseTeachingGoDropViewsview_1Go4．重命名视图语法格式：EXECsp_rename源视图名,新视图名5．查看视图定义信息EXECsp_helptext视图名5.4.2使用SQL命令操作视图5.5视图的应用

5.5.1利用视图查询数据因为视图本身也是一个表，因此利用视图查询数据同表的查询一样。这里只介绍利用视图完成一些复杂的查询操作，即先查询所需的中间数据，并形成一视图，然后再对该视图进行所需的查询操作，待查询完毕后，最后将视图删除。【例5-8】从数据库Teaching中的表sgrade中，查询系学生人数在所有系的平均学生人数之上的系和该系学生的人数、平均中文，平均英文及平均数学成绩。分析：显然不能直接查询所需的数据，我们可借助于视图完成。即先查询各个系和各个系的学生人数、平均中文，平均英文及平均数学成绩，并将其形成一视图st_view3。UseTeachingGoCreateViewsview_3(szx,xsrs,pjzw,pjyw,pjsx)ASSelectszx,Count(*),Avg(zw),Avg(yw),Avg(sx)FromsgradeGroupByszxGo/*从视图sview_3中查询所需的最终数据*/Selectszx所在系,xsrs学生人数,pjzw平均中文,pjyw平均英文,pjsx平均数学

Fromsview_3Wherexsrs>=(SelectAvg(xsrs)Fromsview_3)GoDropViewsview_3Go

5.5.1利用视图查询数据【例5-9】在大学生选课管理数据库Student中，查询选课门数最多的学生的学号、姓名和所选课程的门数及选课平均成绩，并将查询结果按所选课的平均成绩降序排列。UseStudentGoCreateViewst_view3ASSelectstab.xh学号,stab.xm姓名,Count(*)选课门数,Avg(sctab.cj)平均成绩

Fromstab,sctabWherestab.xh=sctab.xhGroupBystab.xh,stab.xmGo/*从视图st_view3中查询所需的最终数据*/Select*Fromst_view3Where选课门数>=ALL(Select选课门数Fromst_view3)OrderBy平均成绩DescGoDropViewst_view3Go

5.5.1利用视图查询数据5.5.2利用视图修改数据

使用视图修改数据时，需要注意以下几点：（1）修改视图中的数据时，不能同时修改两个或者多个基表，可以对基于两个或多个基表或者视图的视图进行修改，但是每次修改都只能影响一个基表。（2）不能修改那些通过计算得到的字段。（3）如果在创建视图时指定了WITHCHECKOPTION选项，那么所有使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。（4）执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中。（5）如果视图引用多个表时，无法用DELETE命令删除数据。1.通过视图插入记录【例5-10】在数据库Teaching中，利用视图向表sgrade中添加一个学生记录。UseTeachingGo/*先创建一个基于表sgrade的视图sview_4*/CreateViewsview_4ASSelect*FromsgradeWherexm=’李芳’

Go/*通过视图sview_4向表sgrade中添加记录*/Insertintosview_4values（‘100022’,’孙大名’,’男’,’2008-9-1’,‘机械系’,‘08制造’,80,50,70）Go5.5.2利用视图修改数据

2.通过视图更新记录内容使用视图可以更新数据记录，但应该注意的是，更新的只是数据库中的基表。【例5-11】在数据库Teaching中，创建一个基于表sgrade的视图sview_5，然后通过该视图修改表sgrade中的记录。UseTeachingGo/*先创建一个基于表sgrade的视图sview_5*/CreateViewsview_5ASSelect*FromsgradeGo/*通过视图sview_5修改表sgrade中的记录*/Updatesview_5Setxm=’李丽霞’Wherexm=’李芳’Go5.5.2利用视图修改数据

3.通过视图删除记录

使用视图删除记录，可以删除任何基表中的记录，直接利用DELETE语句删除记录即可。但应该注意，必须指定在视图中定义过的字段来删除记录。【例5-12】在数据库Teaching中，利用视图sview_5删除表sgrade中姓名为李丽霞的记录。UseTeachingGoDeleteFromsview_5Wherexm=’李丽霞’

Go5.5.2利用视图修改数据

5.6任务实现

1．建立教师的有关任课信息视图（1）在数据库Student中，建立一视图st_view4，该视图能查询当前所有任课教师的编号、姓名、性别、职称和年龄信息。UseStudentGoCreateViewst_view4ASSelectjsh编号,xm姓名,xb性别,zc职称,year(getdate())-year(csrq)年龄

FromttabWherejshIN(SelectDistinctjshFromsctab)Go（2）在数据库Student中，建立一视图st_view5，该视图能查询每个能任课教师的编号、姓名、职称和所能够担任的课程。

UseStudentGoCreateViewst_view5ASSelectttab.jch教师号,ttab.xm姓名,ttab.zc职称,ctab.kcm能任课程

Fromttab,ctab,tctabWherettab.jch=tctab.jshANDctab.kch=tctab.kchGo5.6任务实现

（3）在数据库Student中，建立一视图st_view6，该视图能查询每个教师的编号、姓名、职称和所能够担任课程的门数。UseStudentGoCreateViewst_view6ASSelectttab.jsh教师号,ttab.xm姓名,ttab.zc职称,Count(*)任课门数

Fromttab,tctabWherettab.jsh=tctab.jshGroupByttab.jsh,ttab.xm,ttab.zcGo附注：也可使用子查询完成。(学生自主练习，具体实现参考教材P110)5.6任务实现

（4）在数据库Student中，建立一视图st_view7，该视图能查询当前每个任课教师的编号、姓名和所正讲授的课程门数及所教的学生人数。UseStudentGoCreateViewst_view7ASSelectttab.jsh教师号,ttab.xm姓名,Count(DISTINCTsctab.kch)讲课门数,Count(DISTINCTsctab.xh)学生人数

Fromttab,sctabWherettab.jsh=sctab.jshGroupByttab.jsh,ttab.xmGo5.6任务实现

2．建立学生的有关选课信息视图（1）在数据库Student中，建立一视图st_view8，该视图能查询每个选课学生的学号、姓名、所选课程和该课程所选的任课教师及所选课程成绩。UseStudentGoCreateViewst_view8ASSelectstab.x