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)whererownum10)wherern=1)orderbyempnodesc;结果：--根据分析函数分页(一万条数据，查询10000至9980时间大概在1.01秒左右)select*from(selecta.*,row_number()over(orderbyempnodesc)rkfromscott.empa)whererk10andrk=1;结果：--rownum分页(一万条数据，查询10000至9980时间大概在0.01秒左右)select*from(selectt.*,rownumrnfrom(select*fromscott.emporderbyempnodesc)twhererownum10)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;--innerjoinselecta.*,b.*fromscott.empainnerjoinscott.deptbona.deptno=b.deptno;--fulljoinselecta.*,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,casewhensal0andsal1500then'一级工资'whensal=1500andsal3000then'二级工资'whensal=3000andsal4500then'三级工资'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=10orsal2500);结果：--非等值连接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.sal1000;select*from(select*fromscott.empajoinscott.deptbona.deptno=b.deptnowherea.sal1000)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.empw