数据库sql语言学习(十）

1.数据库视图的定义：

/*
视图的几个特点：
1.视图是一种命名的导出表，是从一个或几个基本表（或视图）导出的表
2.与基本表不同，视图的数据并不物理地存储在数据库中（物化视图除外）
3.查询时，凡是能够出现基本表的地方，都允许出现视图
4.只有可更新的视图才允许更新。




*/
/*


可能用到的表结构
Departments(Dno, Dname,Dheadno)  
院系（院系号，院系名，院主任）
Teachers (Tno,Tname,Sex,Birthday,Title,Dno)
老师（教工号，教师名，性别，生日，职称，所在院系号）
Students(Sno,Sname,Sex,Birthday,Speciality,Dno)
学生（学号，名字，性别，生日，专业名，院系号）
Course(Cno, Cname,Credit)
课表（课程号，课程名，学分）
Teaches(Tno,Cno，TCscore)
实体教（教工号，课程号,评估得分）
SC(Sno,Cno,Greade)
选课（学生学号，课程号，学生成绩）


*/




/*


1.  定义视图
使用create view 语句可以创建视图，其语句格式为：


    create view <视图名> [ (<列名> , …, <列名>)]
    as <查询表达式> 
    [with check option]  


其中<视图名>是标识符，对定义的视图命名；
圆括号中包括一个或多个<列名>，中间用逗号隔开，为<查询表达式（子查询）>结构的诸列命名
<查询表达式>通常是一个select查询，其中不包含ditinct短语和order by子句(重要:这里指的是单单一个子句不能是这两个)
注意：
当select子句中的结果列都是属性名时，(<列名> , …, <列名>)可以缺省，并用select子句的结果列作为视图表的属性
当视图定义中包含可选短语with check option时，该视图应当是可更新的，并且在更新时考虑<查询表达式>的查询条件。
*/




-- 建立计算机专业学生的视图SE_Students，它包含Students中除Speciality
-- 之外的所有属性和计算机专业所有学生的信息


create view SE_Student as
(select Sno,Sname,Sex,Birthday,Dno
from Students
where Speciality = '计算机')
with check option ;
-- select子句中的结果列都是属性，它们成为视图表的属性 




-- 建立信息学院学生选课视图EI_SC，它与SC具有相同的属性，但只包含信息工程学院学生的选课记录
create view EI_SC as
(select *
from SC
where Sno in
(select Sno 
from Students
where Students.Sno = SC.Sno and Dno = 'CS'));


-- 视图还可以基于多个表定义:
-- 建立学生成绩视图Student_Grades，它包含如下属性：
-- 学号、学生姓名、课程名和成绩
create view Student_Grades(Sno,Sname,Cname,Grade) as
(select S.Sno,Sname,Cname,Greade
from Students S,SC,Courses C
where S.Sno = SC.Sno and C.Cno = SC.Cno);




-- SQL还允许定义基于视图的视图，即利用已经定义的视图定义 新的视图:
-- 建立计算机专业学生成绩视图CS_Student_Grades，它包含如下属性：学号、学生姓名、课程名和成绩
create view CS_Student_Grades(Sno,Sname,Cname,Grade) as
(select S.Sno,S.Sname,Cname,Grade
from Students S ,Student_Grades SG
where S.Sno = SG.Sno and Speciality = '计算机');


-- 在定义视图的查询表达式中还可以使用聚集函数，这样定义的视图称为聚集视图:
-- 定义学生平均成绩视图Student_Avg_Grades，它包括如下属性：学生的学号、姓名和平均成绩（Avg_Grade）


create view Student_Avg_Grades(Sno,Sname,Avg_Grade) as
(select SC.Sno,Sname,avg(Greade)
from Students S,SC
where S.Sno = SC.Sno
group by SC.Sno,Sname);

2.数据库是视图的操作

/*
视图的删除语句的格式为：
     drop view <视图名> [ cascade | restrict ]
删除视图就是把视图的定义从数据字典中删除


cascade或restrict是可选的，缺省时为  restrict 
cascade导致级联删除，即同时删除基于该视图定义的视图，并继续该过程
restrict将限制删除，仅当没有其它成分依赖于该视图时才删除


*/


-- 删除在上一篇文章中创建的视图Student_Grades，
-- 不能直接使用默认删除视图Student_Grades，因为视图CS_Student_Grades的定义依赖于它


drop view Student_Grades cascade;


-- 删除在上一篇文章中创建的视图EI_SC，这个视图没有任何视图依赖它
-- 可以直接使用默认的即可
drop view EI_SC;






/*
从用户角度讲，查询时用视图与使用基本表并无区别
至于系统如何实现基于视图的查询，一般用户不必关心
其实，视图是命名的导出表


因为有了视图，其实很多的基本表的查询可以借助视图将中间的结果进行导出成一个表


*/

/*
基于视图的更新
1.所有视图都是直接或间接由基本表定义的
2.基于视图的更新最终要转换成对定义视图的基本表的更新


然而，对于某些视图，不能将更新唯一地转换成对定义它的基本表的更新，
这种视图称为不可更新的视图，而其它视图称为可更新的视图


为了防止用户在更新视图时，有意或无意破坏基本表中的数据，
可以在定义视图时加上with check option子句，
这样，系统在更新视图时会自动检查视图定义中的条件，不满足条件则拒绝更新 




*/




/*


我们定义的视图Student_Avg_Grades就是不可更新的视图：
因为学生的平均成绩是该生的多门课程的平均值，
从视图中插入和删除学生学生的平均成绩没有意义，
而将一个学生的平均成绩从80修改为82存在许多种修改学生单科成绩的方法
使用聚集函数定义的视图是不可更新的


注意：具体系统允许更新的视图是可更新视图的一个子集。
下面的讨论针对可更新视图，但只适合具体系统的允许更新的视图 
*/




-- 在SE_Student视图中添加一行记录
insert into SE_Student(Sno, Sname, Birthday, Sex, Dno)
values ('200605109', '吴畅', '1987-05-04', '女', 'CS');




-- 上面的语句显式列出了视图的属性，
-- 只需要保证VALUES子句按列举的属性次序为诸属性提供值。
-- 系统会自动的将上述操作转换为对基本表的插入




-- 等价于执行如下语句：
insert into Students (Sno, Sname, Birthday, Sex, Dno, Speciality)
values ('200605109', '吴畅', '1987-05-04', '女', 'CS', '计算机');


-- 也就是说，实际向表Students插入元组时，
-- 考虑了定义视图SE_Students的查询语句中的查询条件Speciality ='计算机'，
-- 自动地在插入的新元组的Speciality属性上添加值'计算机'(但是有一些sql软件不是这样处理的)




-- 注意不同的系统可能处理不一样；






-- 类似地，对视图的删除和修改也要转换成对相应基本表的删除和修改
-- 删除计算机专业学号为200705201的学生。
delete from SE_Students
where Sno='200705201';
-- 等价于：
delete from Students
where Sno='200705201' and  Speciality ='计算机' ;
-- 即从表Students删除时，自动增加Speciality = '计算机'为删除条件
-- 注意：Sno是码，它唯一确定被删除的元组。但是，条件Speciality = '计算机'
-- 保证不会因为提供错误的学号导致删除其他专业的学生元组 














总结：
视图的作用：
1.  使用视图可以使一些查询表达更加简洁
视图是命名的导出表，可以用很复杂的查询定义，但却可以像基本表一样使用。
这样，使用视图就屏蔽了实现细节，可以简化查询表达式


2.  视图提供了一定程度的逻辑独立性
使用视图可以定义外模式，而应用程序可以建立在外模式上。
这样，当模式（即基本表）发生变化时，可以定义新的视图或修改视图的定义，通过视图屏蔽表的变化，从而保持建立在外模式上的应用程序不需要修改


3.  视图的安全保护作用
视图与授权（参见第5章）配合使用，可以在某种程度上对数据库起到保护作用。
我们可以对不同用户定义不同视图，并利用授权将不同视图上的访问权限授予不同的用户，
而不允许他们访问定义视图的基本表。这样，每个用户只能看到他有权看到的数据，从而实现对机密数据的保护
4.  视图使得用户能够以不同角度看待相同的数据
从用户角度，视图就是表。这使得在相同的数据库模式下，用户透过不同的视图可以看到不同的数据组织形式