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,ename,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 ;

--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 and 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_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 * 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<=9);

结果：

--not exists；查询emp表在dept表中没有的数据

select * from scott.emp a where not exists(select * from scott.dept b where a.empno=b.deptno) ;

结果：

--rowid;查询重复数据信息

select * from scott.emp a where a.rowid>(select min(x.rowid) from scott.emp x where x.empno=a.empno);

--根据rowid来分页(一万条数据，查询10000至9980时间大概在0.03秒左右)

select * from scott.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<10)where rn>=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<10 and rk>=1;

结果：

--rownum分页(一万条数据，查询10000至9980时间大概在0.01秒左右)

select * from(select t.*,rownum rn from(select * from scott.emp order by empno desc)t where rownum<10) where rn>=1;

select * from(select a.*,rownum rn from (select * from scott.emp) a where rownum<=10) where rn>=5 ;

--left outer join:左连接

select a.*,b.* from scott.emp a left outer join scott.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 join

select 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=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 '销售部' else '其他部门' end 部门 from scott.emp ;

结果：

select ename,sal,case when sal>0 and sal<1500 then '一级工资' when sal>=1500 and sal<3000 then '二级工资' when sal>=3000 and sal<4500 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 姓名 ;

--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 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 job<>'CLERK' start with mgr is null connect by prior mgr = empno ;

--level与prior关键字

select level,emp.* from scott.emp start with ename='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 sal>2500);

结果：

--非等值连接

select a.ename,a.sal,b.grade from scott.emp a,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 scott.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.sal>1000;

select * from(select * from scott.emp a join scott.dept b on a.deptno=b.deptno where a.sal>1000) 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');

结果：

--单行子查询在 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 from scott.emp where sal>all(select sal from scott.emp where deptno=30);

--any:表示大于括号查询出来的其中任意一个即可（只随机一个）

select ename,sal,deptno from scott.emp where sal>any(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 where 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.emp 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.deptno=dept.deptno where sal>dept.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 scott.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 deptno>10 union select ename,sal,deptno from scott.emp where deptno<30 ;

select a.deptno from scott.emp a union select b.deptno from scott.dept b ;

--union all(直接将两个结果集合并，不排序)

select ename,sal,deptno from scott.emp where deptno>10 union all select ename,sal,deptno from scott.emp where deptno<30 ;

select a.deptno from scott.emp a union all select b.deptno from scott.dept b ;

--intersect:取交集

select ename,sal,deptno from scott.emp where deptno>10 intersect select ename,sal,deptno from scott.emp where deptno<30;

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

select dname as 部门,sum(sal) as 工资总和 from scott.emp a,scott.dept b where a.deptno=b.deptno group by dname having sum(sal)>(select sum(sal)/3 from scott.emp c,scott.dept d where c.deptno=d.deptno);

结果：

--使用with得到以上同样的结果

with test as (select dname ,sum(sal) 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 preceding(窗口子句一)

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;

结果：

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) from scott.emp order by deptno ;

阅读剩余部分