视图

数据库建表

1.视图创建代码

代码如下：

CREATE VIEW 计算机系学生 AS 
select Sno,Sname,Sage,Sdept 
from Student where Sdept='计算机系'

CREATE VIEW 选课学生 AS 
select Student.Sno,Sname,Ssex,Grade,SC.Cno 
from Student  Join SC ON Student.Sno=SC.Sno
join Course  ON Course.Cno=SC.Cno
where Sdept='计算机系' and SC.Cno='c01'

CREATE VIEW _选课学生  AS 
select * from 选课学生 where  Grade>80

CREATE VIEW 出生年份 AS
SELECT 2020-Sage FROM Student

CREATE VIEW 信息管理系学生 AS
SELECT Sno,Sname,Sage,Sdept from Student where Sdept='信息管理系'

CREATE VIEW AS
SELECT AVG(Grade)平均成绩,SUM(Grade)总成绩,Student.Sno FROM  Student  Join SC ON Student.Sno=SC.Sno
join Course  ON Course.Cno=SC.Cno

CREATE VIEW 计算机系年龄大于的学生(姓名) AS
select Sname from Student where Sage >20

CREATE VIEW 平均成绩大于的学生(姓名) AS
select Sname from Student join SC on SC .Sno =Student .Sno
group by Sname having AVG (Grade )>80

CREATE VIEW 选修c01课程的学生(姓名) AS
select Sname from Student join SC on SC .Sno =Student .Sno join Course on Course .Cno =SC .Cno 
where sc.Cno ='c01'

总结

视图的作用：. 1、视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。. 2、视图提供了一个统一访问数据的接口。. （即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）. 3、从而加强了安全性，使用户只能看到视图所显示的数据。. 4、视图还可以被嵌套，一个视图中可以嵌套另一个视图。