SQL Server数据库技术及应用(第四讲)——视图

更新视图
• 利用视图修改记录 将视图v_teacher中名为张胜利的老师改为王 将视图 中名为张胜利的老师改为王 静西。 静西。 use xk go update v_teacher set teachername='王静西 王静西' 王静西 where teachername=‘张胜利 张胜利' 张胜利 go
更新视图
使用视图修改数据时，需要注意以下几点： 使用视图修改数据时，需要注意以下几点： 修改视图中的数据时，不能同时修改两个或者多个基表， 修改视图中的数据时 ， 不能同时修改两个或者多个基表 ， 可以对基于两个或多个基表或者视图的视图进行修改， 可以对基于两个或多个基表或者视图的视图进行修改， 但 是每次修改都只能影响一个基表。 是每次修改都只能影响一个基表。 不能修改那些通过计算得到的字段。 不能修改那些通过计算得到的字段。 如果在创建视图时指定了WITH CHECK OPTION选项，那 选项， 如果在创建视图时指定了 选项 么所有使用视图修改数据库信息时， 么所有使用视图修改数据库信息时， 必须保证修改后的数 据满足视图定义的范围。 据满足视图定义的范围。 执行UPDATE、 DELETE命令时 ， 所删除与更新的数据必 命令时， 执行 、 命令时 须包含在视图的结果集中。 须包含在视图的结果集中。 如果视图引用多个表时，无法用DELETE命令删除数据。 如果视图引用多个表时，无法用 命令删除数据。 命令删除数据
重命名视图
• 重命名视图 – 使用企业管理器 – 使用系统存储过程 使用系统存储过程sp_rename 将视图v_coubycpt重新命名为 例：将视图 重新命名为 v_coucount. sp_rename v_coubycpt,v_coucount go
• 删除视图 – 通过企业管理器修改 – 应用 应用SQL语句来删除 语句来删除 SQL语句格式如下： 语句格式如下： 语句格式如下 DROP VIEW view_name 也可以同时删掉多个视图。 也可以同时删掉多个视图。 删除试图v_coubycpt和v_stucou。 例：删除试图 和 。 Drop view v_coubycpt, v_stucou
练习
• 创建视图 创建视图v_StuSQL，显示’00电子商务’ ，显示’ 电子商务 电子商务’ 班并选修了‘ 实用技术’ 班并选修了‘SQL Server实用技术’的学 实用技术 生信息（学号、姓名）。 生信息（学号、姓名）。
练习 use xk go create view v_stusql 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
创建视图
• 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列 表中的 列和 列
更新视图
• • • • INSERT操作 操作 UPDATE操作 操作 DELETE操作 操作 注意：由于视图实际上并不存储记录， 注意：由于视图实际上并不存储记录，因 此更新视图的操作最终都转化为对基表的 更新操作 • 使用 使用WITH CHECK OPTION：保证在进行 ： 视图的更新操作时，所插入、更新、 视图的更新操作时，所插入、更新、删除 的记录， 的记录，满足视图定义中查询的条件表达 式
创建视图
SQL语句
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
第4讲 视图
• 什么 是视 图？
基表（ table)： 基表（base table)：独立存在的表 视图——虚拟表。 SQL中只存储视图的定义 ——虚拟表 中只存储视图的定义， 视图——虚拟表。在SQL中只存储视图的定义，不存放视图所对应的记录
视图的概念
视图是从一个或者多个表或视图中导出的表， 视图是从一个或者多个表或视图中导出的表，其 是从一个或者多个表或视图中导出的表 结构和数据是建立在对表的查询基础上的。 结构和数据是建立在对表的查询基础上的。和真 实的表一样， 实的表一样，视图也包括几个被定义的数据列和 多个数据行，但从本质上讲， 多个数据行，但从本质上讲，这些数据列和数据 行来源于其所引用的表。 行来源于其所引用的表。
显示视图信息
• 显示视图信息 – 显示视图的定义信息 显示视图的定义信息sp_helptext – 显示视图的参照对象和字段信息 sp_depends 显示v_coubycpt的定义信息、参照对象和字 的定义信息、 例：显示 的定义信息 段信息。 段信息。 sp_helptext v_coubycpt go sp_depends v_coubycpt go
修改视图
• 格式： 格式： ALTER VIEW view_name AS select_statement 问题】 进行修改， 【问题】将v_ CouByCpt进行修改，使其显示 进行修改 旅游系所开设的所有课程信息。 旅游系所开设的所有课程信息。 • 加密视图 WITH ENCRYPTION
视图概述
视图的作用
– 使用户只关注所关心的数据 – 提高了数据的安全性 – 提高客户端的查询效率
注意，创建视图时应该注意以下情况： 注意，创建视图时应该注意以下情况：
• 只能在当前数据库中创建视图。 只能在当前数据库中创建视图。 • 如果视图引用的基表或者视图被删除，则该视图不能再被 如果视图引用的基表或者视图被删除， 使用，直到创建新的基表或者视图。 使用，直到创建新的基表或者视图。 • 如果视图中某一列是函数、数学表达式、常量或者来自多 如果视图中某一列是函数、数学表达式、 个表的列名相同，则必须为列定义名称。 个表的列名相同，则必须为列定义名称。 • 不能在视图上创建索引 • 视图的名称必须遵循标识符的规则，且对每个用户必须是 视图的名称必须遵循标识符的规则， 唯一的。此外， 唯一的。此外，该名称不得与该用户拥有的任何表的名称 相同。 相同。
修改视图
use xk go alter view v_coubycpt as select CouName, Teacher, SchoolTime, DepartName from 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
更新视图
• 使用视图删除记录 ， 可以删除任何基表中 使用视图删除记录， 的记录， 直接利用DELETE语句删除记录 的记录 ， 直接利用 语句删除记录 即可。 但应该注意， 即可 。 但应该注意 ， 必须指定在视图中定 义过的字段来删除记录。 义过的字段来删除记录。 利用视图v_teacher删除表 删除表teacher中姓 例 ： 利用视图 删除表 中姓 名为王静西的记录。 名为王静西的记录。 delete from v_teacher where name=’王静西’ 王静西’ 王静西
创建视图（4）
• 视图为基表的统计汇总
【问题】创建视图v_CouByKind,按课程分类统计报 问题】创建视图 按课程分类统计报 名人数。 名人数。 特点： 特点： 视图定义中使用了GROUP BY子句 视图定义中使用了 子句 注意：在下面的情况下必须明确指明视图每一列的列名： 注意：在下面的情况下必须明确指明视图每一列的列名： 1. 视图中的某一列是算术表达式、函数或者常量 视图中的某一列是算术表达式、 2. 多表连接时，有两个或两个以上的列具有相同的 多表连接时， 列名 • 思考：能否将课本P51图2-2-26的查询结果作为视图 思考：能否将课本 图 的查询结果作为视图
更新视图
• 利用视图添加新记录
例 ： 首 先 创 建 了 一 个 新 的 视 图 v_teacher ， 该 视 图 基 于 表 teacher创建。 创建。 创建 create view v_teacher as select teacherno, teachername, departno,salary from teacher Where salary>3000 go Insert into v_teacher Values(‘006’,‘李田 李田',3100,'02') 李田 go
更新视图
use xk go alter view v_teacher as select ID, teachername, salary,departno from teacher Where salary>3000 with check option go Insert into v_teacher Values('10','李田 李田',2900,'02') 李田 go
Βιβλιοθήκη Baidu
视图应用
视图为基表的行的子集
数据库创建视图v_Student， 【问题】在XK数据库创建视图 问题】 数据库创建视图 ， 只显示Student表中班级编码为 表中班级编码为20000001 只显示 表中班级编码为 的班级的学生信息
• 方法 ：使用企业管理器 方法1： • 方法 ：使用 方法2：使用SQL语句 语句 • 方法3：使用创建视图向导 方法 ：
创建视图
• 视图为多个表、视图连接组成的复杂查 视图为多个表、 询——最常用的情况 最常用的情况 创建视图v_StuCou，显示学生的 【例】创建视图 ， 学号、 学号、姓名和所选的课程名称 • 特点： 特点： – 多表查询（其实只需要在正确的 多表查询（其实只需要在正确的select语 语 句前加上CREATE VIEW…AS即可） 即可） 句前加上 即可
创建视图
创建视图
use xk go create view V_coubykind as select kind as ‘课程分类’, 课程分类’ 课程分类 sum(willnum) as ‘报名总数’ 报名总数’ 报名总数 from course group by kind go
练习
• 创建视图 创建视图v_CouByDep，显示各个系部开 ， 设的选修课的数量和报名人数
创建视图
• 添加表并选择要输出的行
练习
• 创建视图 创建视图v_CouByCpt，显示计算机应用工程系承担 ， 的选修课程的信息（课程名称、授课老师、上课时间） 的选修课程的信息（课程名称、授课老师、上课时间） use xk go create view v_coubycpt as select CouName, Teacher, SchoolTime, DepartName from Course,Department where Course.DepartNo = Department.DepartNo and DepartName = '计算机应用工程系 计算机应用工程系' 计算机应用工程系 go