oracle经典SQL查询语句.docx
《oracle经典SQL查询语句.docx》由会员分享,可在线阅读,更多相关《oracle经典SQL查询语句.docx(28页珍藏版)》请在冰豆网上搜索。
oracle经典SQL查询语句
Oracle查询语句
select*fromscott.emp;
1.--dense_rank()分析函数(查找每个部门工资最高前三名员工信息)
select*from(selectdeptno,ename,sal,dense_rank()over(partitionbydeptnoorderbysaldesc)afromscott.emp)wherea<=3orderbydeptnoasc,saldesc;
结果:
--rank()分析函数(运行结果与上语句相同)
select*from(selectdeptno,ename,sal,rank()over(partitionbydeptnoorderbysaldesc)afromscott.emp)wherea<=3orderbydeptnoasc,saldesc;
结果:
--row_number()分析函数(运行结果与上相同)
select*from(selectdeptno,ename,sal,row_number()over(partitionbydeptnoorderbysaldesc)afromscott.emp)wherea<=3orderbydeptnoasc,saldesc;
--rowsunboundedpreceding分析函数(显示各部门的积累工资总和)
selectdeptno,sal,sum(sal)over(orderbydeptnoascrowsunboundedpreceding)积累工资总和fromscott.emp;
结果:
--rows整数值preceding(显示每最后4条记录的汇总值)
selectdeptno,sal,sum(sal)over(orderbydeptnorows3preceding)每4汇总值fromscott.emp;
结果:
--rowsbetween1precedingand1following(统计3条记录的汇总值【当前记录居中】)
selectdeptno,ename,sal,sum(sal)over(orderbydeptnorowsbetween1precedingand1following)汇总值fromscott.emp;
结果:
--ratio_to_report(显示员工工资及占该部门总工资的比例)
selectdeptno,sal,ratio_to_report(sal)over(partitionbydeptno)比例fromscott.emp;
结果:
--查看所有用户
select*fromdba_users;
selectcount(*)fromdba_users;
select*fromall_users;
select*fromuser_users;
select*fromdba_roles;
--查看用户系统权限
select*fromdba_sys_privs;
select*fromuser_users;
--查看用户对象或角色权限
select*fromdba_tab_privs;
select*fromall_tab_privs;
select*fromuser_tab_privs;
--查看用户或角色所拥有的角色
select*fromdba_role_privs;
select*fromuser_role_privs;
--rownum:
查询10至12信息
select*fromscott.empawhererownum<=3anda.empnonotin(selectb.empnofromscott.empbwhererownum<=9);
结果:
--notexists;查询emp表在dept表中没有的数据
select*fromscott.empawherenotexists(select*fromscott.deptbwherea.empno=b.deptno);
结果:
--rowid;查询重复数据信息
select*fromscott.empawherea.rowid>(selectmin(x.rowid)fromscott.empxwherex.empno=a.empno);
--根据rowid来分页(一万条数据,查询10000至9980时间大概在0.03秒左右)
select*fromscott.empwhererowidin(selectridfrom(selectrownumrn,ridfrom(selectrowidrid,empnofromscott.emporderbyempnodesc)whererownum<10)wherern>=1)orderbyempnodesc;
结果:
--根据分析函数分页(一万条数据,查询10000至9980时间大概在1.01秒左右)
select*from(selecta.*,row_number()over(orderbyempnodesc)rkfromscott.empa)whererk<10andrk>=1;
结果:
--rownum分页(一万条数据,查询10000至9980时间大概在0.01秒左右)
select*from(selectt.*,rownumrnfrom(select*fromscott.emporderbyempnodesc)twhererownum<10)wherern>=1;
select*from(selecta.*,rownumrnfrom(select*fromscott.emp)awhererownum<=10)wherern>=5;
--leftouterjoin:
左连接
selecta.*,b.*fromscott.empaleftouterjoinscott.deptbona.deptno=b.deptno;
--rightouterjoin:
右连接
selecta.*,b.*fromscott.emparightouterjoinscott.deptbona.deptno=b.deptno;
--innerjoin
selecta.*,b.*fromscott.empainnerjoinscott.deptbona.deptno=b.deptno;
--fulljoin
selecta.*,b.*fromscott.empafulljoinscott.deptbona.deptno=b.deptno;
selecta.*,b.*fromscott.empa,scott.deptbwherea.deptno(+)=b.deptno;
selectdistinctename,salfromscott.empagroupbysalhaving;
select*fromscott.dept;
select*fromscott.emp;
--casewhenthenend(交叉报表)
selectename,sal,casedeptnowhen10then'会计部'when20then'研究部'when30then'销售部'else'其他部门'end部门fromscott.emp;
结果:
selectename,sal,casewhensal>0andsal<1500then'一级工资'whensal>=1500andsal<3000then'二级工资'whensal>=3000andsal<4500then'三级工资'else'四级工资'end工资等级fromscott.emporderbysaldesc;
结果:
--交叉报表是使用分组函数与case结构一起实现
select姓名,sum(case课程when'数学'then分数end)数学,sum(case课程when'历史'then分数end)历史from学生groupby姓名;
--decode函数
select姓名,sum(decode(课程,'数学',分数,null))数学,sum(decode(课程,'语文',分数,null))语文,sum(decode(课程,'历史','分数',null))历史from学生groupby姓名;
--level。
。
。
。
connectby(层次查询)
selectlevel,emp.*fromscott.empconnectbypriorempno=mgrorderbylevel;
结果:
--sys_connect_by_path函数
selectename,sys_connect_by_path(ename,'/')fromscott.empstartwithmgrisnullconnectbypriorempno=mgr;
结果:
--startwithconnectbyprior语法
selectlpad(ename,3*(level),'')姓名,lpad(ename,3*(level),'')姓名fromscott.empwherejob<>'CLERK'startwithmgrisnullconnectbypriormgr=empno;
--level与prior关键字
selectlevel,emp.*fromscott.empstartwithename='SCOTT'connectbypriorempno=mgr;
selectlevel,emp.*fromscott.empstartwithename='SCOTT'connectbyempno=priormgr;
结果:
--等值连接
selectempno,ename,job,sal,dnamefromscott.empa,scott.deptbwherea.deptno=b.deptnoand(a.deptno=10orsal>2500);
结果:
--非等值连接
selecta.ename,a.sal,b.gradefromscott.empa,scott.salgradebwherea.salbetweenb.losalandb.hisal;
结果:
--自连接
selecta.ename,a.sal,b.enamefromscott.empa,scott.empbwherea.mgr=b.empno;
结果:
--左外连接
selecta.ename,a.sal,b.enamefromscott.empa,scott.empbwherea.mgr=b.empno(+);
结果:
--多表连接
select*fromscott.emp,scott.dept,scott.salgradewherescott.emp.deptno=scott.dept.deptnoandscott.emp.salbetweenscott.salgrade.losalandscott.salgrade.hisal;
结果:
select*fromscott.empajoinscott.deptbona.deptno=b.deptnojoinscott.salgradesona.salbetweens.losalands.hisalwherea.sal>1000;
select*from(select*fromscott.empajoinscott.deptbona.deptno=b.deptnowherea.sal>1000)cjoinscott.salgradesonc.salbetweens.losalands.hisal;
--单行子查询
select*fromscott.empawherea.deptno=(selectdeptnofromscott.deptwhereloc='NEWYORK');
select*fromscott.empawherea.deptnoin(selectdeptnofromscott.deptwhereloc='NEWYORK');
结果:
--单行子查询在from后
selectscott.emp.*,(selectdeptnofromscott.deptwhereloc='NEWYORK')afromscott.emp;
--使用in,all,any多行子查询
--in:
表示等于查询出来的对应数据
selectename,job,sal,deptnofromscott.empwherejobin(selectdistinctjobfromscott.empwheredeptno=10);
--all:
表示大于所有括号中查询出来的对应的数据信息
selectename,sal,deptnofromscott.empwheresal>all(selectsalfromscott.empwheredeptno=30);
--any:
表示大于括号查询出来的其中任意一个即可(只随机一个)
selectename,sal,deptnofromscott.empwheresal>any(selectsalfromscott.empwheredeptno=30);
--多列子查询
selectename,job,sal,deptnofromscott.empwhere(deptno,job)=(selectdeptno,jobfromscott.empwhereename='SCOTT');
selectename,job,sal,deptnofromscott.empwhere(sal,nvl(comm,-1))in(selectsal,nvl(comm,-1)fromscott.empwheredeptno=30);
--非成对比较
selectename,job,sal,deptnofromscott.empwheresalin(selectsalfromscott.empwheredeptno=30)andnvl(comm,-1)in(selectnvl(comm,-1)fromscott.empwheredeptno=30);
--其他子查询
selectename,job,sal,deptnofromscott.empwhereexists(selectnullfromscott.deptwherescott.dept.deptno=scott.emp.deptnoandscott.dept.loc='NEWYORK');
selectename,job,salfromscott.empjoin(selectdeptno,avg(sal)avgsal,nullfromscott.empgroupbydeptno)deptonemp.deptno=dept.deptnowheresal>dept.avgsal;
createtablescott.test(
enamevarchar(20),
jobvarchar(20)
);
--droptabletest;
select*fromscott.test;
--Insert与子查询(表间数据的拷贝)
insertintoscott.test(ename,job)selectename,jobfromscott.emp;
--Update与子查询
updatescott.testset(ename,job)=(selectename,jobfromscott.empwhereename='SCOTT'anddeptno='10');
--创建表时,还可以指定列名
createtablescott.test_1(ename,job)asselectename,jobfromscott.emp;
select*fromscott.test_1;
--delete与子查询
deletefromscott.testwhereenamein('');
--合并查询
--union语法(合并且去除重复行,且排序)
selectename,sal,deptnofromscott.empwheredeptno>10unionselectename,sal,deptnofromscott.empwheredeptno<30;
selecta.deptnofromscott.empaunionselectb.deptnofromscott.deptb;
--unionall(直接将两个结果集合并,不排序)
selectename,sal,deptnofromscott.empwheredeptno>10unionallselectename,sal,deptnofromscott.empwheredeptno<30;
selecta.deptnofromscott.empaunionallselectb.deptnofromscott.deptb;
--intersect:
取交集
selectename,sal,deptnofromscott.empwheredeptno>10intersectselectename,sal,deptnofromscott.empwheredeptno<30;
--显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和
selectdnameas部门,sum(sal)as工资总和fromscott.empa,scott.deptbwherea.deptno=b.deptnogroupbydnamehavingsum(sal)>(selectsum(sal)/3fromscott.empc,scott.deptdwherec.deptno=d.deptno);
结果:
--使用with得到以上同样的结果
withtestas(selectdname,sum(sal)sumsalfromscott.emp,scott.deptwherescott.emp.deptno=scott.dept.deptnogroupbydname)selectdnameas部门,sumsalas工资总和fromscott.testwheresumsal>(selectsum(sumsal)/3fromscott.test);
结果:
--分析函数
selectename,sal,sum(sal)over(partitionbydeptnoorderbysaldesc)fromscott.emp;
--rowsnpreceding(窗口子句一)
selectdeptno,sal,sum(sal)over(orderbysalrows5preceding)fromscott.emp;
结果:
--rum(..)over(..)..
selectsal,sum
(1)over(orderbysal)aafromscott.emp;
selectdeptno,ename,sal,sum(sal)over(orderbyename)连续求和,sum(sal)over()总和,100*round(sal/sum(sal)over(),4)as份额fromscott.emp;
结果:
selectdeptno,ename,sal,sum(sal)over(partitionbydeptnoorderbyename)部门连续求和,sum(sal)over(partitionbydeptno)部门总和,100*round(sal/sum(sal)over(),4)as总份额fromscott.emp;
结果:
selectdeptno,sal,rank()over(partitionbydeptnoorderbysal),dense_rank()over(partitionbydeptnoorderbysal)fromscott.emporderbydeptno;
结果;
select*from(selectrank()over(partitionby课程orderby分数desc)rk,分析函数_rank.*from分析函数_rank)whererk<=3;
--dense_rank():
有重复的数字不跳着排列
--row_number()
selectdeptno,sal,row_number()over(partitionbydeptnoorderbysal)rmfromscott.emp;
结果:
--lag()和lead()
selectdeptno,sal,lag(sal)over(partitionbydeptnoorderbysal)上一个,lead(sal)over(partitionbydeptnoorderbysal)fromscott.emp;
结果:
--max(),min(),avg()
selectdeptno,sal,max(sal)over(partitionbydeptnoorderbysal)最大,min(sal)over