sql3.7视图

视图

1从一个或几个基本表导出的表(虚表)
2用户通过视图可以看到自己感兴趣的数据及其变化。
3数据库中只存放视图的定义。
4基表中的数据发生变化，从视图中查询出的数据也随之改变。
5视图可以和基本表一样被查询，对视图的更新有一定的限制。

优点

简单性。视图不仅可以简化用户对数据的理解，也可以简化操作。那些经常使用的查询可以被定义为视图，从而使用户不必为以后的操作每次都指定全部条件。
安全性。通过视图用户只能查询和修改能见到的数据。数据库中的其它数据既看不见也取不到。

语句格式

create view 视图名
as 子查询
with check option

子查询可以是任意复杂的SELECT语句，但不含有ORDER BY子句和DISTINCT短语;
with check option限制视图更新后的每一条数据仍然要满足创建视图时指定的where条件

DBMS对视图的处理

DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的
SELECT语句。
在对视图查谊时，按视图的定义从基本表中将数据实时查出。

定义视图

行列子集视图，单个表，保留了主码

例1：建立信息系学生的视图（学号，姓名，年龄）

create view is_student
as
select sno,sname,sage
from student
where sdept='is';
此时并未存在真正的视图，只是存在这么一个定义
删除视图
drop view is_student

例2：建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生

不加with chect option
select *
from is_student

对该视图进行增删改查操作
insert into is_student(sno,sname)
values('1','zhangsan');

select *
from student
此时查看视图没有张三这个人，因为张三的sdept为空
select *
from is_student
更新数据，视图年龄全部加一
update is_student
set sage=sage+1;
删除，会失败，因为张三现在不在视图里
delete from is_student
where sno='1';
给张三加上系，就存在视图内了
update student
set sdept='IS'
where sname='zhangsan';
再次删除zhangsan
delete from is_student
where sno='1';
总结：没有with check option的视图，都可以进行增删改查

加上with check option
create view is_stu
as
select sno,sname,sage
from student
where sdept='is'
with check option

对该视图进行增删改查操作
insert into is_stu(sno,sname)
values('1','zhangsan');
插入失败，没有sdept的属性值，所以插入失败
select *
from student
select *
from is_stu
更新（可以正常执行）
update is_stu
set sage=sage+1
删除（可以正常执行）
delete
from is_stu
where sno='200215124';

解决无法插入方法
给表增加约束
alter table student
add constraint df_stu_sdept default('IS') for sdept;
删除约束
alter table student
drop constraint df_stu_sdept;

接着加数据
insert into is_stu(sno,sname)
values('1','zhangsan');
增加成功，张三的默认系别是IS
执行完毕，删除张三
delete
from is_stu
where sno='1';

/基于多个基表的视图/

例3：建立信息系选修了1号课程的学生的视图(包括学生学号，姓名和成绩)。

create view is_c1
as
select s.sno,sname,grade
from student s,sc
where s.sno=sc.sno and sdept='is' and sc.cno='1';
将欧阳哲一号课程成绩改成90
update is_c1
set grade=90
where sno='200215128';
查看视图
select *
from is_c1

加上列名（1）
create view is_c2
as
select s.sno 学号,sname 姓名,grade 成绩
from student s,sc
where s.sno=sc.sno and sdept='is' and sc.cno='1';

select *
from is_c2

加上列名（2）
create view is_c3(学号,姓名,成绩)
as
select s.sno,sname,grade
from student s,sc
where s.sno=sc.sno and sdept='is' and sc.cno='1';

select *
from is_c3

基于视图的视图

例4: 建立信息系选修了1号课程且成绩在90分以上的学生的视图(学号,姓名,成绩）is_s2

create view is_s2
as
select sno,sname,grade
from is_c1
where grade>=90;

drop view is_s2

select *
from is_s2
带表达式的视图
在子查询里，需要一些函数或者其他一些表达式进行运算，将运算的结果显示在视图里。
如果子查询里有表达式，一定要给那一列起上别名，称为虚列。
带表达式的视图必须明确定义组成视图的各个属性列名。

例5:定义一个反映学生出生年份的视图(学号，姓名，出生年份)(bt_s)

create view bt_s
as
select sno,sname,2020-sage Sbirth
from student

select *
from bt_s

或者
create view bt_s2(姓名,学号,出生日期)
as
select sno,sname,2020-sage
from student

select *
from bt_s2

分组视图

例6: 将学生的学号及他的平均成绩定义为一个视图。(s_g)

create view s_g(sno,gavg)
as
select sno,avg(grade)
from sc
group by sno

select *
from s_g

可以在子查询中出现*

例7:将Student表中所有女生记录定义为一个视图。(f_student)

create view f_student
as
select *
from student
where ssex='女';

select *
from f_student

新增一列scome后，再查询建立在基本表上的视图
alter table student add scome datetime;
查询基本表发现多了一列scome
select *
from student
再次查询视图
select *
from f_student
视图没有发生改变
从视图中查出的结构和基本表不同。
删掉那一列
alter table student drop column scome

例8: 删除视图IS_ S1。

一个语句可同时删除多个视图定义
若该视图还导出其他视图则需显式删除
删除基表时，由该基表导出的所有视圄定义都必须显式删除。

drop view IS_S1

查询视图

视图实体化法(View Materialization)
有效性检查:检查所查询的视图是否存在
执行视图定义，将视图临时实体化，生成临时表
查询视图转换为查询临时表
查询完毕删除被实体化的视图(临时表)
视图消解法(View Resolution) :用视图的定义替换查询中的条件,转换成对基本表的查询。
进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义;
把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询;
执行修正后的查询。

例9:在信息系学生的视图中找出年龄小于20岁的学生。

视图实体化
select sno,sage
from is_stu
where sage<20
视图消解
select sno,sage
from student
where sdept='IS' and sage<20

例10:查询信息系选修了1号课程的学生的学号与姓名。

已知is_stu为信息系学生，只需sc中cno为1即可，视图和基本表可以联合查询
select is_stu.sno,sname
from is_stu,sc
where is_stu.sno=sc.sno and sc.cno='1';
或者是
select is_stu.sno,sname
from is_stu join sc
on is_stu.sno=sc.sno and sc.cno='1';

例11：在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

一些视图是不可更新的，因为对这些视图的更新不能有意义地转换成对相应基本表的更新。

例如:视图S G为不可更新视图。
CREATE VIEW S_G(Sno, Gavg)
AS
SELECT Sno, AVG(Grade)
FROM
SC
GROUP BY Sno;

视图的更新

• 允许对行列子集视图进行更新
• 若视图由两个以上基本表导出，一次则可允许更新一个基表中的数据，不允许同时更新(update)个基表中的数据
• 若视图由两个以上基本表导出，不允许执行insert,delete操作，因为这些操作涉及了多个表
• 若视图的字段来自字段表达式，常数，则不允许更新;
• 若视图定义中含有聚集函数，GROUP BY子句、DISTINCT短语,则不允许更新
• 在不允许更新的视图.上定义的视图也不允许更新。