数据库（三）关系数据库标准语言（3）

数据更新

插入

insert into sc(sno,cno) values('201215128', '1');insert into dept_age(sdept,avg_age) select sdept,avg(sage) fromstudent group by sdept;

修改

update sc set grade=0 where sno in (select sno from student where sdept ='CS');

删除

delete from sc; 清空sc表delete from student where sno ='201215128';selete from sc wehere sno in (delete sno from student where sdept ='CS');

视图

只存放视图的定义，不存放对应的数据，是虚表。

建立视图

create view is_student as select sno, sname, sage from student where sdept = 'IS' with check option;

with check option 限制视图的更新需保证创建时的条件，即 sdept = ‘IS’

删除视图

drop view is_student;drop view is_student cascade; 还删除由is_student导出的所有视图

查询视图

大体同查询表

select * from (select sno,avg(grade) from sc group by sno ) as s_g(sno, gavg) where gavg>=90;

更新视图

大体同更新表

并非所有视图都能更新，有创建时的限制条件。

视图的作用

1.简化用户操作

2.使用户以多角度看待同一数据

3.对重构数据库提供了一定的逻辑独立性

4对机密数据提供安全保护

5.更清晰的表达查询