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
升级会员 