oracle 操作视图,oracle查询操作和视图操作

--检索所有学生的姓名，年龄和选课名称

select sn, age, cn from s, c, sc where s.sno=sc.sno and c.cno=sc.cno

--查询所有学生的学号，姓名，选课名称及成绩(没有选课的同学的选课成绩信息显示为空)

select s.sno,sn,cn,score from s left outer join sc on s.sno=sc.sno left outer join c on c.cno=sc.cno

--查询与‘刘伟’教师职称相同的教师号，姓名

select tno,tn from t where prof=(select prof from t where tn='刘伟')

--查询讲授课程号为c5的教师姓名,其中any的含义为任意一个

select tn from t where (tno=any (select tno from tc where cno='c5'))

select tn from t,tc where t.tno=tc.tno and tc.cno='c5'

--查询其他系中比计算机系某一个教师工资高的教师的姓名和工资

select tn, sal from t where (sal > any (select sal from t where dept='计算机')) and dept <>'计算机'

select tn, sal from t where sal > (select min(sal) from t where dept='计算机') and dept <>'计算机'

--查询讲授课程号为c5的教师姓名(使用in)

select tn from t where (tno in (select tno from tc where cno='c5'))

--查询讲授课程号为c5的教师姓名(使用exists)，相关子查询

select tn from t where exists (select * from tc where tno=t.tno and cno='c5')

--查询其他系中比计算机系所有教师工资都高的教师的姓名和工资

select tn, sal from t where (sal > all (select sal from t where dept='计算机'))and dept <>'计算机'

select tn, sal from t where sal > (select max(sal) from t where dept='计算机') and dept <>'计算机'

--查询不讲授课程号为c5的教师姓名,也可以用not in代替<>all(引用父查询属性值tno，称为相关子查询)，子查询和相关子查询的先后顺序不一样，后者由父表的行数决定

select distinct tn from t where ('c5'<>all (select cno from tc where tno=t.tno))

--查询不讲授课程号为c5的教师姓名，相关子查询使用not exists

select tn from t where not exists (select * from tc where tno=t.tno and cno='c5')

--查询选修所有课程的学生姓名

select sn from s where (not exists (select *from c where not exists(select *from sc where sno=s.sno and cno=c.cno)))

--合并查询，从sc数据表中查询出学号为‘s1’同学的学号和总分，再从sc数据表中查询出学号为‘s5’的同学的学号和总分，然后合并两个查询

select sno as 学号, sum(score) as 总分 from sc where (sno='s1')group by sno union select sno as 学号, sum(score) as 总分 from sc where (sno='s5')group by sno

select sno as 学号, sum(score) as 总分 from sc where (sno='s1' or sno='s5')group by sno

--存储查询结果到表中

create table cal_table as select sno as 学号, sum(score) as 总分 from sc group by sno

--在sc表中插入一条选课记录

insert into sc(sno, cno, score) values('s5','c3',88)

--插入多行记录

create table avgsal(department varchar(40), average smallint);

insert into avgsal select dept, avg(sal) from t group by dept;

--修改一行

update t set dept='信息' where tn='刘伟'

--修改多行

update s set age=age+1

--把讲授c5课程的教师的岗位津贴增加100元

update t set comm=comm+100 where(tno in(select t.tno from t,tc where t.tno=tc.tno and tc.cno='c5'))

--删除数据记录

delete from t where t='刘伟';

delete from tc;

delete from tc where(tno=(select tno from t where tn='刘伟'));

--视图是虚表，其数据不进行存储，其记录来自基本表，只在数据库中存储其定义。视图在概念上与基本表等同，用户可以在视图上再定义视图，可以对视图进行查询，删除和更新操作

--创建一个计算机系教师情况的视图SUB_T

create view sub_t as select tno,tn,prof from t where dept='计算机'

--创建一学生情况视图S_SC_C(包括学号，姓名，课程名及成绩)

create view s_sc_c(sno, sn, cn, score)as select s.sno,sn,cn,score from s,c,sc where s.sno=sc.sno and sc.cno=c.cno

--创建一学生平均成绩视图s_avg

create view s_avg(sno,avg) as select sno, avg(score) from sc group by sno

--删除视图

drop view sub_t

--查找视图sub_t中职称为教授的教师号和姓名。

select tno,tn from sub_t where(prof='教授')

--更新视图，向sub_t中插入一条记录(t6,李丹，副教授)

insert into sub_t(tno, tn, prof)values('t6','李丹','副教授')

--将计算机系的教师视图sub_t中刘伟的职称改为‘副教授’

update sub_t set prof='副教授' where(tn='刘伟')

--删除视图sub_t中刘伟教师的记录

delete from sub_t where(tn='刘伟')

--sql系统中，有两个安全机制，一种是视图机制；一个是权限机制。权限可分为系统权限和对象权限

--为用户zhangsan授予create table的系统权限

grant create table to zhangsan

--收回用户zhangsan所拥有的create table的系统权限

revoke create table from zhangsan

--对象权限与角色的授予

grant all on s to zhangsan

--将对c表的查询权限授予所有用户

grant select on c to public

--将查询t表和修改教师职称的权限授予zhangsan，并允许将此权限授予其他用户

grant select,update(prof) on t to zhangsan with grant option

--对象权限和角色的收回

revoke select on t from zhangsan;

revoke update on t from zhangsan;