oracle经典SQL查询语句.docx

1、oracle经典SQL查询语句Oracle查询语句select * from scott.emp ;1.-dense_rank()分析函数（查找每个部门工资最高前三名员工信息）select * from (select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) a from scott.emp) where a=3 order by deptno asc,sal desc ;结果：-rank()分析函数(运行结果与上语句相同)select * from (select deptno,ena

2、me,sal,rank() over(partition by deptno order by sal desc) a from scott.emp ) where a=3 order by deptno asc,sal desc ;结果：-row_number()分析函数（运行结果与上相同）select * from(select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) a from scott.emp) where a=3 order by deptno asc,sal desc ;

3、-rows unbounded preceding 分析函数（显示各部门的积累工资总和）select deptno,sal,sum(sal) over(order by deptno asc rows unbounded preceding) 积累工资总和 from scott.emp ;结果：-rows 整数值 preceding(显示每最后4条记录的汇总值)select deptno,sal,sum(sal) over(order by deptno rows 3 preceding) 每4汇总值 from scott.emp ;结果：-rows between 1 preceding a

4、nd 1 following（统计3条记录的汇总值【当前记录居中】）select deptno,ename,sal,sum(sal) over(order by deptno rows between 1 preceding and 1 following) 汇总值 from scott.emp ;结果：-ratio_to_report(显示员工工资及占该部门总工资的比例)select deptno,sal,ratio_to_report(sal) over(partition by deptno) 比例 from scott.emp ;结果：-查看所有用户select * from dba_

5、users ;select count(*) from dba_users ;select * from all_users ;select * from user_users ;select * from dba_roles ;-查看用户系统权限select * from dba_sys_privs ;select * from user_users ;-查看用户对象或角色权限select * from dba_tab_privs ;select * from all_tab_privs ;select * from user_tab_privs ;-查看用户或角色所拥有的角色select

6、* from dba_role_privs ;select * from user_role_privs ;- rownum:查询10至12信息select * from scott.emp a where rownum=3 and a.empno not in(select b.empno from scott.emp b where rownum(select min(x.rowid) from scott.emp x where x.empno=a.empno);-根据rowid来分页(一万条数据，查询10000至9980时间大概在0.03秒左右)select * from scott.

7、emp where rowid in(select rid from(select rownum rn,rid from(select rowid rid,empno from scott.emp order by empno desc) where rownum=1)order by empno desc ;结果：-根据分析函数分页(一万条数据，查询10000至9980时间大概在1.01秒左右)select * from(select a.*,row_number() over(order by empno desc) rk from scott.emp a ) where rk=1;结果：

8、-rownum分页(一万条数据，查询10000至9980时间大概在0.01秒左右)select * from(select t.*,rownum rn from(select * from scott.emp order by empno desc)t where rownum=1;select * from(select a.*,rownum rn from (select * from scott.emp) a where rownum=5 ;-left outer join:左连接select a.*,b.* from scott.emp a left outer join scott.

9、dept b on a.deptno=b.deptno ;-right outer join:右连接select a.*,b.* from scott.emp a right outer join scott.dept b on a.deptno=b.deptno ;-inner joinselect a.*,b.* from scott.emp a inner join scott.dept b on a.deptno=b.deptno ;-full join select a.*,b.* from scott.emp a full join scott.dept b on a.deptno

10、=b.deptno ;select a.*,b.* from scott.emp a,scott.dept b where a.deptno(+)=b.deptno ;select distinct ename,sal from scott.emp a group by sal having ;select * from scott.dept ;select * from scott.emp ;-case when then end (交叉报表)select ename,sal,case deptno when 10 then 会计部 when 20 then 研究部 when 30 then

11、 销售部 else 其他部门 end 部门 from scott.emp ;结果：select ename,sal,case when sal0 and sal=1500 and sal=3000 and sal4500 then 三级工资 else 四级工资 end 工资等级 from scott.emp order by sal desc ;结果：-交叉报表是使用分组函数与case结构一起实现select 姓名,sum(case 课程 when 数学 then 分数 end)数学,sum(case 课程 when 历史 then 分数 end)历史 from 学生 group by 姓名

12、;-decode 函数select 姓名,sum(decode(课程,数学,分数,null)数学,sum(decode(课程,语文,分数,null)语文,sum(decode(课程,历史,分数,null)历史 from 学生 group by 姓名 ;-level。connect by(层次查询)select level,emp.* from scott.emp connect by prior empno = mgr order by level ;结果：-sys_connect_by_path函数select ename,sys_connect_by_path(ename,/) from

13、scott.emp start with mgr is null connect by prior empno=mgr ;结果：-start with connect by prior 语法select lpad(ename,3*(level),)姓名,lpad(ename,3*(level),)姓名 from scott.emp where jobCLERK start with mgr is null connect by prior mgr = empno ;-level与prior关键字select level,emp.* from scott.emp start with ename

14、=SCOTT connect by prior empno=mgr;select level,emp.* from scott.emp start with ename=SCOTT connect by empno = prior mgr ;结果：-等值连接select empno,ename,job,sal,dname from scott.emp a,scott.dept b where a.deptno=b.deptno and (a.deptno=10 or sal2500);结果：-非等值连接select a.ename,a.sal,b.grade from scott.emp a,

15、scott.salgrade b where a.sal between b.losal and b.hisal ;结果：-自连接select a.ename,a.sal,b.ename from scott.emp a,scott.emp b where a.mgr=b.empno ;结果：-左外连接select a.ename,a.sal,b.ename from scott.emp a,scott.emp b where a.mgr=b.empno(+);结果：-多表连接select * from scott.emp ,scott.dept,scott.salgrade where sc

16、ott.emp.deptno=scott.dept.deptno and scott.emp.sal between scott.salgrade.losal and scott.salgrade.hisal ;结果：select * from scott.emp a join scott.dept b on a.deptno=b.deptno join scott.salgrade s on a.sal between s.losal and s.hisal where a.sal1000;select * from(select * from scott.emp a join scott.

17、dept b on a.deptno=b.deptno where a.sal1000) c join scott.salgrade s on c.sal between s.losal and s.hisal ;-单行子查询select * from scott.emp a where a.deptno=(select deptno from scott.dept where loc=NEW YORK);select * from scott.emp a where a.deptno in (select deptno from scott.dept where loc=NEW YORK);

18、结果：-单行子查询在 from 后select scott.emp.*,(select deptno from scott.dept where loc=NEW YORK) a from scott.emp ;-使用 in ,all,any 多行子查询-in：表示等于查询出来的对应数据select ename,job,sal,deptno from scott.emp where job in(select distinct job from scott.emp where deptno=10);-all:表示大于所有括号中查询出来的对应的数据信息select ename,sal,deptno

19、 from scott.emp where salall(select sal from scott.emp where deptno=30);-any:表示大于括号查询出来的其中任意一个即可（只随机一个）select ename,sal,deptno from scott.emp where salany(select sal from scott.emp where deptno=30);-多列子查询select ename,job,sal,deptno from scott.emp where(deptno,job)=(select deptno,job from scott.emp w

20、here ename=SCOTT);select ename,job,sal,deptno from scott.emp where(sal,nvl(comm,-1) in(select sal,nvl(comm,-1) from scott.emp where deptno=30);-非成对比较select ename,job,sal,deptno from scott.emp where sal in(select sal from scott.emp where deptno=30) and nvl(comm,-1) in(select nvl(comm,-1) from scott.e

21、mp where deptno=30);-其他子查询select ename,job,sal,deptno from scott.emp where exists(select null from scott.dept where scott.dept.deptno=scott.emp.deptno and scott.dept.loc=NEW YORK);select ename,job,sal from scott.emp join(select deptno,avg(sal) avgsal,null from scott.emp group by deptno) dept on emp.

22、deptno=dept.deptno where saldept.avgsal ;create table scott.test( ename varchar(20), job varchar(20);-drop table test ;select * from scott.test ;-Insert与子查询(表间数据的拷贝)insert into scott.test(ename,job) select ename,job from scott.emp ;-Update与子查询update scott.test set(ename,job)=(select ename,job from s

23、cott.emp where ename=SCOTT and deptno =10);-创建表时，还可以指定列名create table scott.test_1(ename,job) as select ename,job from scott.emp ;select * from scott.test_1 ;-delete与子查询delete from scott.test where ename in();-合并查询-union语法（合并且去除重复行，且排序）select ename,sal,deptno from scott.emp where deptno10 union selec

24、t ename,sal,deptno from scott.emp where deptno10 union all select ename,sal,deptno from scott.emp where deptno10 intersect select ename,sal,deptno from scott.emp where deptno(select sum(sal)/3 from scott.emp c,scott.dept d where c.deptno=d.deptno);结果：-使用with得到以上同样的结果with test as (select dname ,sum(s

25、al) sumsal from scott.emp ,scott.dept where scott.emp.deptno=scott.dept.deptno group by dname) select dname as 部门,sumsal as 工资总和 from scott.test where sumsal(select sum(sumsal)/3 from scott.test);结果：-分析函数select ename,sal,sum(sal) over(partition by deptno order by sal desc) from scott.emp ;-rows n pr

26、eceding(窗口子句一)select deptno,sal,sum(sal) over(order by sal rows 5 preceding) from scott.emp ;结果：-rum(.) over(.).select sal,sum(1) over(order by sal) aa from scott.emp ;select deptno,ename,sal,sum(sal) over(order by ename) 连续求和,sum(sal) over() 总和,100*round(sal/sum(sal) over(),4) as 份额 from scott.emp;

27、结果：select deptno,ename,sal,sum(sal) over(partition by deptno order by ename) 部门连续求和,sum(sal) over(partition by deptno) 部门总和,100*round(sal/sum(sal) over(),4) as 总份额 from scott.emp;结果：select deptno,sal,rank() over (partition by deptno order by sal),dense_rank() over(partition by deptno order by sal) f

28、rom scott.emp order by deptno ;结果；select * from (select rank() over(partition by 课程 order by 分数 desc) rk,分析函数_rank.* from 分析函数_rank) where rk=3 ;-dense_rank():有重复的数字不跳着排列-row_number()select deptno,sal,row_number() over(partition by deptno order by sal) rm from scott.emp ;结果：-lag()和lead()select deptno,sal,lag(sal) over(partition by deptno order by sal) 上一个,lead(sal) over(partition by deptno order by sal) from scott.emp ;结果：-max(),min(),avg()select deptno,sal,max(sal) over(partition by deptno order by sal)最大,min(sal) over