关系数据库操作基础

关系数据库操作基础
4.5 视图
用户1:教师 用户2:学生
本人所售课程学生 名单
本学期为自己的课程安 排
教师开课的情况，教 室使用的情况 学生的平均成绩，最 高成绩，最低成绩 等
学生 数据库
用户3:教务员 用户4:领导
理解视图
视图的特点 • 虚拟表，是从一个或几个基本表（或视图） 导出的表 • 只存放视图的定义，不会出现数据冗余 • 基表中的数据发生变化，从视图中查询出 的数据也随之改变
基于视图的视图
[例4] CREATE VIEW HBstudent_2
AS
SELECT sno,sname,age
FROM
HBstudent
WHERE sex=‘女’
带表达式的视图
[例5]
CREATE VIEW HBstudent_3 AS SELECT sno , sname , sex , year(getdate())-year, place FROM student WHERE place LIKE ‘湖北’
已知视图：
• CREATE 分) AS • • •
VIEW avg_score(课程号,平均
SELECT CourseNo,AVG(grade) FROM study GROUP BY CNo
• 预对视图进行如下查询： select * from avg_score where平均分>=90 • 如果采用视图消减法，则极有可能转化为下面 的语句： SELECT CourseNo,AVG(grade) FROM study WHERE AVG(grade)>=90 GROUP BY CNo
• 而实际上，这是一条错误的转换。正确 的转换应该是： SELECT CourseNo,AVG(grade) FROM study GROUP BY CNo HAVING AVG(grade)>=90
更新视图
• 用户角度：更新视图与更新基本表相同
• DBMS实现视图更新的方法
– 视图实体化法（View Materialization） – 视图消解法（View Resolution）
定义
• 把视图定义中的子查询与用户的查询结合起来，
转换成等价的对基本表的查询 • 执行修正后的查询
• 视图HBstudent中查找年龄大于20岁的 学生基本信息 SELECT sno , sname , sex , age FROM HBstudent WHERE age > 20
查询视图（续）
创建视图（续）
• WITH CHECK OPTION 强制视图上执行的所有数据修改语句都必须符合 由 <视图的定义>设置的准则。通过视图修 改行时，WITH CHECK OPTION 可确保提 交修改后，仍可通过视图看到修改的数据。
创建视图举例
[例1]建立一个湖北考籍的学生信息视图
CREATE VIEW HBstudent_1 AS SELECT sno , sname , sex , age,place FROM student WHERE place LIKE ‘湖北’
通过建立一个视图使用户的外模式保持不变
程序员对数据库逻辑结构的修改不会影 响到用户的视觉与操作.
视图在一定程度上保证了数据的逻辑独立性
4. 视图能够对机密数据提供安全保护
• 对不同用户定义不同视图，使每个用户
只能看到他有权看到的数据 • 通过WITH CHECK OPTION对关键数 据定义操作时限制
1. 视图能够简化用户的操作
当视图中数据不是直接来自基本表时，定义视图能够简 化用户的操作 – 基于多张表连接形成的视图 – 基于复杂嵌套查询的视图 – 含导出属性的视图
2. 视图使用户能以多种角度看待同一 数据 • 视图机制能使不同用户以不同方式看待 同一数据，适应数据库共享的需要
3.视图对重构数据库提供了一定程度的逻辑独立性
WITH CHECK OPTION；
更新 WITH CHECK OPTION的视图举例
update HBstudent_1
set place=‘河北’ where sno=‘1‘
基于多个基表的视图
CREATE VIEW Student_Study AS
SELECT student.sno, cno, grade FROM student, study WHERE student.sno = study.sno AND place LIKE ‘%湖北%’
(title,year,length,incolor) 该语句执行时将转换成对student表的插入： INSERT INTO student values('M9',2005,102,1) VALUES（’04031’,’戴敏’,’女’,21,’湖北’）
更新视图—修改数据
将视图HBstudent中学号为03061的同学年龄改为20岁。 UPDATE HBstudent SET age=20 WHERE sno LIKE ’03061’ 该语句执行时将转换成对student表的修改： UPDATE student SET age=20 WHERE sno LIKE ’03061’ AND place LIKE ‘湖北’
• 用视图消解法解释查询的执行过程
转换后的查询语句为：
SELECT sno , sname , sex , age FROM HBstudent WHERE age > 20
查询视图（续）
• 视图消解法的局限
–有些情况下，视图消解法不能生成正确查 询。采用视图消解法的DBMS会限制这类查询。查询视图（续）• 有性检查：检查所查询的视图是否存在
• 执行视图定义，将视图临时实体化，生成临时表 • 查询视图转换为查询临时表 • 查询完毕删除被实体化的视图(临时表)
查询视图的工作原理（续）
– 视图消解法（View Resolution）
• 进行有效性检查，检查查询的表、视图等是否
存在。如果存在，则从数据字典中取出视图的
更新视图的限制
• 一些视图是不可更新的，因为对这些视图的更新不能 唯一地有意义地转换成对相应基本表的更新(对两类方 法均如此)
– 例：CREATE VIEW avg_score(课程号,平均分) AS
SELECT CNo,AVG(grade) FROM study GROUP BY CNo
视图的可更新性
设置一些派生属性列, 也称为虚拟列—距今年份
带表达式的视图必须明确定义组成视图的各个属性列名
删除视图
• DROP VIEW <视图名>；
– 该语句从数据字典中删除指定的视图定义
– 由该视图导出的其他视图定义仍在数据字典
中，但已不能使用，必须显式删除 – 删除基表时，由该基表导出的所有视图定义 都必须显式删除
SQL-Server规定可以按下列方式通过视图修改数据:
视图在视图定义的 FROM 子句中包含至少一个表；视图不能只基于 一个表达式。 在选择列表中没有使用聚合函数（AVG、COUNT、SUM、MIN、 MAX、GROUPING、STDEV、STDEVP、VAR、VARP）或 GROUP BY、UNION、DISTINCT 或 TOP 子句。但是，可以在 FROM 子 句定义的子查询范围内使用聚合函数，前提条件是聚合函数生成的派 生值没有修改过。 在选择列表中不使用派生列。派生列是由除简单列引用以外的一切构 成的结果集列。 如果在视图中删除数据,在视图定义的 FROM 子句中只能列出一个表。
基于视图的操作
基于视图的操作
• 查询
• 删除
• 受限更新
• 定义基于该视图的新视图
创建视图
Create view <视图名> AS <视图的定义>
• DBMS执行CREATE VIEW语句时只是把视图的 定义存入数据字典，并不执行其中的SELECT语句。 • 在对视图查询时，按视图的定义从基本表中将数据 查出。
创建视图举例
[例1]建立一个湖北考籍的学生信息视图
CREATE VIEW HBstudent AS SELECT sno , sname , sex , age FROM student WHERE place LIKE ‘湖北’
创建视图举例
[例2]建立一个学习了C语言的所有学生信息 的视图
CREATE VIEW C_stud_info（sno , sname , sex , place） AS SELECT sno , sname , sex , place FROM student WHERE sno IN （ SELECT sno FROM study WHERE cno IN （ SELECT cno FROM course WHERE cname LIKE ‘C语言’ ） ）
删除视图(续）
[例7] 删除视图HBstudent_3
DROP VIEW HBstudent_3
视图的查询
• SELECT …….. • FROM 视图或者表 • WHERE ……..
查询视图的工作原理
• 从用户角度：查询视图与查询基本表相同 • DBMS实现视图查询的方法 – 实体化视图（View Materialization）
• 指定WITH CHECK OPTION子句后，DBMS在更
新视图时会进行检查，防止用户通过视图对不属于视图范 围内的基本表数据进行更新
更新视图—通过视图添加数据
在湖北考籍的学生视图HBstudent中插入一湖 北考生信息，该学生信息为： （04031，戴敏，女，21）
INSERT INTO HBstudent VALUES（’04031’,’戴敏’,’女’,21） insert into part_movie