20210418SQL基于视图的操作

您尽心尽力去做事了嘛？

复习

1.插入 insert into values

插入多条元组，直接子查询可以一次插入多条元组

Where

2.更新

3.删除 delete from 和drop table的区别

区别：表还在不在？

基于视图的操作

1.查询 90%都是查询；

只是存放，不能更新和删除，视图中没有数据

基本表的数据变化视图才会变化，跟新视图本质是更新基本表；

操作90%都是查询；

2.删除

3.受限更新

通过视图修改会有格外的限制

4.定义基于视图的新视图；

❖语句格式

CREATE VIEW <视图名> [(<列名> [，<列名>]…)]   --重新给某个属性列命名（三种情况）
AS <子查询>
[WITH CHECK OPTION]；  --将来通过视图增删改查是不能破坏的谓词

⬧ 子查询不允许含有ORDER BY子句和DISTINCT短语
⬧ WITH CHECK OPTION
➢透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）

[例3.84] 建立信息系学生的视图。

CREATE VIEW IS_Student --没有带列名，就是用的原表的名字
AS
SELECT Sno, Sname, Sage
FROM student
WHERE Sdept= 'IS' 
WITH CHECK OPTION 
--如果要插入的时候，假设没有给所在系赋值，可以插入但是会自动赋值为计算机系，Sdept= 'IS'

-- 视图 -- 设计  --可以看到Sdept的筛选器变成了  = 'IS'

--从单个基本表导出,只是去掉了基本表某些行和某些列,保留了码————行列子集视图

select *
from is_student;

--[例3.86] 建立信息系选修了1号课程的学生视图。

CREATE VIEW IS_S1(Sno，Sname，Grade)
AS
SELECT Student.Sno，Sname，Grade
FROM Student，SC
WHERE Student.Sno=SC.Sno AND
	Sdept= 'IS' AND
	SC.Cno= '1'；


--[例3.87]建立信息系选修了1号课程且成绩在90分以上的学生视图。

CREATE VIEW IS_S2
AS
SELECT Sno，Sname，Grade
FROM IS_S1     --基于上一个视图；	在IS_S1的基础之上；
WHERE Grade>=90；

[例3.89] 将学生的学号及他的平均成绩定义为一个视图。
--视图中应该有两列


CREATE VIEW S_G(Sno，Gavg)
AS 
SELECT Sno，AVG(Grade)   --平均成绩
FROM SC				    
GROUP BY Sno；		--根据学号分组


--显示
select *from is_a;

删除视图

❖语句格式

DROP VIEW <视图名> [CASCADE]；

⬧ 该语句从数据字典中删除指定的视图定义
⬧ 由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删
除
⬧ 删除基表时，由该基表导出的所有视图定义都必须显式删除
⬧ 如果CASCADE选项，则删除该视图时会把由它导出的视图一块删除

drop view is_a;

查询视图

❖从用户角度：查询视图与查询基本表相同
❖DBMS实现视图查询的方法
⬧ 实体化视图（View Materialization） 			这个方法不推荐！！！
	➢有效性检查：检查所查询的视图是否存在
	➢执行视图定义，将视图临时实体化，**生成临时表**  会造成数据的冗余	

	➢**查询视图转换为查询临时表**

	➢查询完毕删除被实体化的视图(临时表)

视图的消解法

⬧ 视图消解法（View Resolution）  不存在数据的冗余
	➢进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图
的定义
	➢把视图定义中的子查询与用户的查询结合起来，转换成**等价的对基本表的查询**
	
	➢执行修正后的查询！

并不需要用户来做什么

--[例3.92] 在信息系学生的视图中找出年龄小于20岁的学生。

SELECT Sno，Sage
FROM IS_Student
WHERE Sage<20；

--◼ 视图消解法
--转换后的查询语句为：

SELECT Sno，Sage 
FROM Student
WHERE Sdept= 'IS' AND Sage<20；


--[例3.94]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩。
SELECT *
FROM S_G
WHERE Gavg>=90；
--错误示范
SELECT Sno，AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno；
--查询转换：
SELECT Sno，AVG(Grade)
FROM SC
GROUP BY Sno； --因为视图中没有数据
HAVING AVG(Grade)>=90；

更新视图

❖用户角度：更新视图与更新基本表相同
❖DBMS实现视图更新的方法

	⬧ 视图实体化法（View Materialization）
	⬧ 视图消解法（View Resolution）

❖指定WITH CHECK OPTION子句后

	DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新.

[例3.95] 将信息系学生视图IS_Student中学号201215122的学生姓名改为“刘辰” 。
UPDATE IS_Student
SET Sname= '刘辰'
WHERE Sno= ' 201215122 '；
--转换后的语句： 
--转成对基本表的操作
--修改的是本身视图可以看到的数据
UPDATE student
SET Sname= '刘辰'
WHERE Sno= ' 201215122 ' AND Sdept = ‘IS’；

删除信息系学生的记录

Delete 
from is_studnet
where sno= '201215129'；


Delete 
from studnet
where sno= '201215129' and sdept = 'IS'；

更新视图

❖一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义
地转换成对相应基本表的更新(对两类方法均如此)
--例：视图S_G为不可更新视图。
--非行列子集视图，涉及到聚簇函数的不能更新；
CREATE VIEW S_G (Sno，Gavg) 
--基本表没有平均分这个项所以不可更新；无意义
AS 
SELECT Sno，AVG(Grade)
FROM SC
GROUP BY Sno；

视图的作用

❖视图能够**简化**用户的操作
❖视图使用户能以多种角度看待同一数据
❖视图对重构数据库提供了一定程度的逻辑独立性
❖视图能够对机密数据提供安全保护
❖适当的利用视图可以更清晰的表达查询

逻辑独立性 ：三级模式 外模式（视图） 内模式（多个存储文件） 模式 （基本表）