数据更新
插入
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.更清晰的表达查询