使用SQL语句创建视图5.ppt

《使用SQL语句创建视图5.ppt》由会员分享，可在线阅读，更多相关《使用SQL语句创建视图5.ppt（54页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 第4讲 视图什么是视图？基表（基表（base table)base table)：独立存在的表独立存在的表视图视图虚拟表。在虚拟表。在SQLSQL中只存储视图的定义，不存放视图所对应的记录中只存储视图的定义，不存放视图所对应的记录视图的概念 视图视图是从一个或者多个表或视图中导出的表，其是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。和真结构和数据是建立在对表的查询基础上的。和真实的表一样，视图也包括几个被定义的数据列和实的表一样，视图也包括几个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据多个数据行，但从本质上讲，这些数据列和数据行来源于其所引用的表

2、。行来源于其所引用的表。视图概述视图的作用视图的作用使用户只关注所关心的数据使用户只关注所关心的数据提高了数据的安全性提高了数据的安全性提高客户端的查询效率提高客户端的查询效率注意，创建视图时应该注意以下情况：注意，创建视图时应该注意以下情况：只能在当前数据库中创建视图。只能在当前数据库中创建视图。如果视图引用的基表或者视图被删除，则该视图不能再被如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。使用，直到创建新的基表或者视图。如果视图中某一列是函数、数学表达式、常量或者来自多如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。

3、个表的列名相同，则必须为列定义名称。不能在视图上创建索引不能在视图上创建索引视图的名称必须遵循标识符的规则，且对每个用户必须是视图的名称必须遵循标识符的规则，且对每个用户必须是唯一的。此外，该名称不得与该用户拥有的任何表的名称唯一的。此外，该名称不得与该用户拥有的任何表的名称相同。相同。视图应用视图为基表的行的子集视图为基表的行的子集 【问题问题】在在XK数据库创建视图数据库创建视图v_Student，只显示只显示Student表中班级编码为表中班级编码为20000001的班级的学生信息的班级的学生信息方法方法1：使用企业管理器：使用企业管理器方法方法2：使用：使用SQL语句语句方法方法3：使

4、用创建视图向导：使用创建视图向导创建视图SQL语句定义如下：use xk go create view v_student with encryption as select *from student where classno=20000001 go练习1.创建视图创建视图v_Class，只显示只显示Class表中系表中系部编码为部编码为01的系部的班级信息的系部的班级信息2.创建视图创建视图v_StuCouSub，只显示只显示StuCou表中的表中的StuNo列和列和CouNo列列创建视图视图为多个表、视图连接组成的复杂查询视图为多个表、视图连接组成的复杂查询最常用的情况最常用的情况【例

5、例】创建视图创建视图v_StuCou，显示学生的学显示学生的学号、姓名和所选的课程名称号、姓名和所选的课程名称特点：特点：多表查询（其实只需要在正确的多表查询（其实只需要在正确的select语语句前加上句前加上CREATE VIEWAS即可）即可）创建视图添加表并选择要输出的行练习创建视图创建视图v_CouByCpt，显示计算机应用工程系承担的显示计算机应用工程系承担的选修课程的信息（课程名称、授课老师、上课时间）选修课程的信息（课程名称、授课老师、上课时间）use xk go create view v_coubycpt as select CouName,Teacher,SchoolTim

6、e,DepartName from Course,Department where Course.DepartNo=Department.DepartNo and DepartName=计算机应用工程系计算机应用工程系 go创建视图（4）视图为基表的统计汇总视图为基表的统计汇总【问题问题】创建视图创建视图v_CouByKind,按课程分类统计报按课程分类统计报名人数。名人数。特点：特点：视图定义中使用了视图定义中使用了GROUP BY子句子句注意：注意：在下面的情况下必须明确指明视图每一列的列名：在下面的情况下必须明确指明视图每一列的列名：1.视图中的某一列是算术表达式、函数或者常量视图中的某

7、一列是算术表达式、函数或者常量2.多表连接时，有两个或两个以上的列具有相同的多表连接时，有两个或两个以上的列具有相同的列名列名思考：能否将课本思考：能否将课本P51图图2-2-26的查询结果作为视图的查询结果作为视图创建视图创建视图 use xk go create view V_coubykind as select kind as 课程分类课程分类,sum(willnum)as 报名总数报名总数 from course group by kindgo练习创建视图创建视图v_CouByDep，显示各个系部开显示各个系部开设的选修课的数量和报名人数设的选修课的数量和报名人数创建视图SQL语句

8、use xk go create view v_CouByDep as select departname 课程名称课程名称,课程数量课程数量=count(*),报名人数报名人数=sum(willnum)from department,course where department.departno=course.departno group by departname go显示视图信息显示视图信息显示视图信息显示视图的定义信息显示视图的定义信息sp_helptext显示视图的参照对象和字段信息显示视图的参照对象和字段信息 sp_depends例：例：显示显示v_coubycpt的定义信息、参

9、照对象和字段的定义信息、参照对象和字段信息。信息。sp_helptext v_coubycpt go sp_depends v_coubycpt go修改视图格式：格式：ALTER VIEW view_nameASselect_statement【问题问题】将将v_ CouByCpt进行修改，使其显示旅进行修改，使其显示旅游系所开设的所有课程信息。游系所开设的所有课程信息。加密视图加密视图WITH ENCRYPTION修改视图 use xk go alter view v_coubycpt as select CouName,Teacher,SchoolTime,DepartName from

10、 Course,Department where Course.DepartNo=Department.DepartNo and DepartName=旅游系旅游系 go显示视图信息显示视图信息显示视图信息显示视图的定义信息显示视图的定义信息sp_helptext显示视图的参照对象和字段信息显示视图的参照对象和字段信息 sp_depends例：例：显示显示v_coubycpt的定义信息、参照对象和字段的定义信息、参照对象和字段信息。信息。sp_helptext v_coubycpt go sp_depends v_coubycpt go重命名视图重命名视图重命名视图使用企业管理器使用企业管理器

11、使用系统存储过程使用系统存储过程sp_rename例：例：将视图将视图v_coubycpt重新命名重新命名为为v_coucount.sp_rename v_coubycpt,v_coucountgo 删除视图删除视图通过企业管理器修改通过企业管理器修改应用应用SQL语句来删除语句来删除SQL语句格式如下：语句格式如下：DROP VIEW view_name也可以同时删掉多个视图。也可以同时删掉多个视图。例：删除试图例：删除试图v_coubycpt和和v_stucou。Drop view v_coubycpt,v_stucou更新视图INSERT操作操作UPDATE操作操作DELETE操作操作注

12、意：由于视图实际上并不存储记录，因注意：由于视图实际上并不存储记录，因此更新视图的操作最终都转化为对基表的此更新视图的操作最终都转化为对基表的更新操作更新操作使用使用WITH CHECK OPTION：保证在进行保证在进行视图的更新操作时，所插入、更新、删除视图的更新操作时，所插入、更新、删除的记录，满足视图定义中查询的条件表达的记录，满足视图定义中查询的条件表达式式更新视图使用视图修改数据时，需要注意以下几点：使用视图修改数据时，需要注意以下几点：修修改改视视图图中中的的数数据据时时，不不能能同同时时修修改改两两个个或或者者多多个个基基表表，可可以以对对基基于于两两个个或或多多个个基基表表或

13、或者者视视图图的的视视图图进进行行修修改改，但但是每次修改都只能影响一个基表。是每次修改都只能影响一个基表。不能修改那些通过计算得到的字段。不能修改那些通过计算得到的字段。如如果果在在创创建建视视图图时时指指定定了了WITH CHECK OPTION选选项项，那那么么所所有有使使用用视视图图修修改改数数据据库库信信息息时时，必必须须保保证证修修改改后后的的数数据满足视图定义的范围。据满足视图定义的范围。执执行行UPDATE、DELETE命命令令时时，所所删删除除与与更更新新的的数数据据必必须包含在视图的结果集中。须包含在视图的结果集中。如果视图引用多个表时，无法用如果视图引用多个表时，无法用D

14、ELETE命令删除数据。命令删除数据。更新视图利用视图添加新记录利用视图添加新记录例例：首首先先创创建建了了一一个个新新的的视视图图v_teacher，该该视视图图基基于于表表teacher创建。创建。create view v_teacherasselect teacherno,teachername,departno,salaryfrom teacherWhere salary3000goInsert into v_teacherValues(006,李田李田,3100,02)go更新视图 use xk go alter view v_teacher as select ID,teache

15、rname,salary,departno from teacher Where salary3000 with check option go Insert into v_teacher Values(10,李田李田,2900,02)go更新视图利用视图修改记录利用视图修改记录将视图将视图v_teacher中名为张胜利的老师改为王中名为张胜利的老师改为王静西。静西。use xk go update v_teacher set teachername=王静西王静西 where teachername=张胜利张胜利 go更新视图使使用用视视图图删删除除记记录录，可可以以删删除除任任何何基基表表中

16、中的的记记录录，直直接接利利用用DELETE语语句句删删除除记记录录即即可可。但但应应该该注注意意，必必须须指指定定在在视视图图中中定定义义过的字段来删除记录。过的字段来删除记录。例例：利利用用视视图图v_teacher删删除除表表teacher中中姓姓名为王静西的记录。名为王静西的记录。delete from v_teacher where name=王静西王静西练习创建视图创建视图v_StuSQL，显示显示00电子商务电子商务班并选修了班并选修了SQL Server实用技术实用技术的学生的学生信息（学号、姓名）。信息（学号、姓名）。练习use xk go create view v_stu

17、sql as select student.stuno,stuname,classno from student,class,course,stucou where student.stuno=stucou.stuno and student.classno=class.classno and course.couno=stucou.couno and classname=00电子商务电子商务 and couname=SQL Server实用技术实用技术 go思考问题：问题：对于上述建立的试图对于上述建立的试图V_stusql,我们可我们可否做以下操作。否做以下操作。向该视图中插入新的一行向该

18、视图中插入新的一行（00000061，林思雨林思雨,20000001），如果可以插入那么在什如果可以插入那么在什么情况下可以插入。么情况下可以插入。注：原来的注：原来的student表中并不存在学号表中并不存在学号00000061。思考思考：将视图定义改变如下 use xk go alter view v_stusql as select student.stuno,stuname,student.classno from student,class,course,stucou where student.stuno=stucou.stuno and student.classno=class

19、.classno and course.couno=stucou.couno and classname=00电子商务电子商务 and couname=SQL Server实用技术实用技术 with check option go思考问题：问题：1.此时还能否将下列一行记录插入到该视图此时还能否将下列一行记录插入到该视图中。中。（00000062，林雨林雨,20000001）2.能否通过视图将学号为能否通过视图将学号为00000001的学的学生的姓名改为生的姓名改为林小斌林小斌。3.能否将视图中下面所示的这行记录删除。能否将视图中下面所示的这行记录删除。（00000001，林斌林斌）小结视图可被看成是虚拟表或存储查询视图可被看成是虚拟表或存储查询使用视图可提高数据库性能和安全性使用视图可提高数据库性能和安全性对视图进行修改会影响基表数据对视图进行修改会影响基表数据