oracle学习笔记一

子查询 ：

 

    显示高于部门平均工资的雇员信息
select ename,job,sal from emp,
(select deptno,avg(sal) avgsal from emp
  group by deptno) dept
where dept.deptno=emp.deptno and sal>dept.avgsal

 

emp表数据装载到employee表

insert  into employee (id,name,tite,salary)
  select empno,ename,job,sal from emp;

 

将smith同岗位的雇员工资和补助更新为与smith的工资和补助完全相同
update emp set (sal,comm) =
(select sal,comm from emp where ename='SMITH')
where job=(select job from emp where ename='SMITH');
  select empno,ename,job,sal from emp;

 

 

 

删除sales部门的所有雇员
delete from emp where dept=(select dept from emp where dname='sales')

 

 

建立new_emp表，并将emp表的数据复制到该表为例

create table new_emp (id,name,sal,job,deptno) as
 select empno,ename,sal,job,deptno from emp;

 

 

实体化视图使用子查询
create materialized view summary_emp as
select deptno,jb,avg(sal) avgsal,sum(sal) sumsal
from emp group by cube(deptno,job);

 

 

合并查询

 

1 UNION
UNION 获取两个结果集的并集，自动去掉结果集的重复行，显示工资高于2500的
雇员和岗位为"manager"的雇员为例
 select ename,sal.job frm emp where sal>2500
union
select ename,sal,job from emp where job='manager'

 

2 INTERSECT

用于获取连个结果集的交集

 

3 MINUS

显示两个结果集中的差集

 

 

复杂查询

 

1     层次查询

 

col ename format a15
col job format a15
select lpad(' ',3*(LEVEL-1)) ||ename ename,
LPAD(' ',3*(LEVEL-1))||job job from emp
where job<>'CLERK' start with mgr is null
connect by mgr=prior empno;

 

2     使用case表达式
select ename.sal,case when sal>3000 then 3
when sal>2000 then 2 else 1 end grade
from emp where deptno=10;

 

倒叙查询
查看历史数据
select ename,sal from emp as of timestamp to_timestamp('2003-05-18 19:59:00','yyyy-mm-dd hh24:mi:ss'') where ename='clark'

 

 

使用dbms_frashback包获取特定scn数据

exec dbms_flashback.enable_at_system_change_number(717402)

 

 

with语句

 

例1   显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和

select dname,sum(sal) as dept_total from emp,dept
where emp.deptno=dept.deptno group by dname
having sum(sal) >
(select sum(sal) * 1/3 from emp,dept where emp.deptno=dept.deptno);

 

例2  

显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和（使用with子句重用
子查询）
with summary as (
  select dname,sum(sal) as dept_total from emp.dept
  where emp.deptno=dept.deptno group by dname
)
select dname,dept_total from summery where dept_total>
( select sum(dept_total) * 1/3 from summery )