帮助|留言交流|
首 页 阅览室 馆友 我的图书馆
来自：fangfei8866 > 馆藏分类
配色： 字号：大中小
oracle常用sql查询语句部分集合
2014-01-10 | 阅：  转：  |  分享 
  
Oracle查询语句



selectfromscott.emp;







1.--dense_rank()分析函数（查找每个部门工资最高前三名员工信息）

selectfrom(selectdeptno,ename,sal,dense_rank()over(partitionbydeptnoorderbysaldesc)afromscott.emp)wherea<=3orderbydeptnoasc,saldesc;

结果：







--rank()分析函数(运行结果与上语句相同)

selectfrom(selectdeptno,ename,sal,rank()over(partitionbydeptnoorderbysaldesc)afromscott.emp)wherea<=3orderbydeptnoasc,saldesc;

结果：







--row_number()分析函数（运行结果与上相同）

selectfrom(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;

结果：







--查看所有用户

selectfromdba_users;

selectcount()fromdba_users;

selectfromall_users;

selectfromuser_users;

selectfromdba_roles;



--查看用户系统权限

selectfromdba_sys_privs;

selectfromuser_users;



--查看用户对象或角色权限

selectfromdba_tab_privs;

selectfromall_tab_privs;

selectfromuser_tab_privs;



--查看用户或角色所拥有的角色

selectfromdba_role_privs;

selectfromuser_role_privs;



--rownum:查询10至12信息

selectfromscott.empawhererownum<=3anda.empnonotin(selectb.empnofromscott.empbwhererownum<=9);

结果：







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

selectfromscott.empawherenotexists(selectfromscott.deptbwherea.empno=b.deptno);

结果：







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

selectfromscott.empawherea.rowid>(selectmin(x.rowid)fromscott.empxwherex.empno=a.empno);



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

selectfromscott.empwhererowidin(selectridfrom(selectrownumrn,ridfrom(selectrowidrid,empnofromscott.emporderbyempnodesc)whererownum<10)wherern>=1)orderbyempnodesc;

结果：







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

selectfrom(selecta.,row_number()over(orderbyempnodesc)rkfromscott.empa)whererk<10andrk>=1;

结果：





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

selectfrom(selectt.,rownumrnfrom(selectfromscott.emporderbyempnodesc)twhererownum<10)wherern>=1;



selectfrom(selecta.,rownumrnfrom(selectfromscott.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;



selectfromscott.dept;

selectfromscott.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(+);

结果：







--多表连接

selectfromscott.emp,scott.dept,scott.salgradewherescott.emp.deptno=scott.dept.deptnoandscott.emp.salbetweenscott.salgrade.losalandscott.salgrade.hisal;

结果：







selectfromscott.empajoinscott.deptbona.deptno=b.deptnojoinscott.salgradesona.salbetweens.losalands.hisalwherea.sal>1000;



selectfrom(selectfromscott.empajoinscott.deptbona.deptno=b.deptnowherea.sal>1000)cjoinscott.salgradesonc.salbetweens.losalands.hisal;



--单行子查询

selectfromscott.empawherea.deptno=(selectdeptnofromscott.deptwhereloc=''NEWYORK'');

selectfromscott.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;

selectfromscott.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;

selectfromscott.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()总和,100round(sal/sum(sal)over(),4)as份额fromscott.emp;

结果：





selectdeptno,ename,sal,sum(sal)over(partitionbydeptnoorderbyename)部门连续求和,sum(sal)over(partitionbydeptno)部门总和,100round(sal/sum(sal)over(),4)as总份额fromscott.emp;

结果：





selectdeptno,sal,rank()over(partitionbydeptnoorderbysal),dense_rank()over(partitionbydeptnoorderbysal)fromscott.emporderbydeptno;

结果；





selectfrom(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(partitionbydeptnoorderbysal)最小,avg(sal)over(partitionbydeptnoorderbysal)平均fromscott.emp;

结果：





--first_value(),last_value()

selectdeptno,sal,first_value(sal)over(partitionbydeptno)最前,last_value(sal)over(partitionbydeptno)最后fromscott.emp;

结果：





--分组补充groupbygroupingsets

selectdeptno,sal,sum(sal)fromscott.empgroupbygroupingsets(deptno,sal);



selectnull,sal,sum(sal)fromscott.empgroupbysalunionallselectdeptno,null,sum(sal)fromscott.empgroupbydeptno;

结果：





--rollup

selectdeptno,job,avg(sal)fromscott.empgroupbyrollup(deptno,job);

--理解rollup等价于

selectdeptno,job,avg(sal)fromscott.empgroupbydeptno,jobunionselectdeptno,null,avg(sal)fromscott.empgroupbydeptnounionselectnull,null,avg(sal)fromscott.emp;

结果：





selectdeptno,job,avg(sal)afromscott.empgroupbycube(deptno,job);



--理解CUBE

selectdeptno,job,avg(sal)fromscott.empgroupbycube(deptno,job);

--等价于

selectdeptno,job,avg(sal)fromscott.empgroupbygroupingsets((deptno,job),(deptno),(job),());

结果：





--查询工资不在1500至2850之间的所有雇员名及工资

selectename,salfromscott.empwheresalnotin(selectsalfromscott.empwheresalbetween1500and2850);



--部门10和30中的工资超过1500的雇员名及工资

selectdeptno,ename,salfromscott.empawherea.deptnoin(10,30)anda.sal>1500orderbysaldesc;

结果：





--在1981年2月1日至1981年5月1日之间雇佣的雇员名，岗位及雇佣日期，并以雇佣日期先后顺序排序

selectenameas姓名,jobas岗位,hiredateas雇佣日期fromscott.empawherea.hiredatebetweento_date(''1981-02-01'',''yyyy-mm-dd'')andto_date(''1981-05-01'',''yyyy-mm-dd'')orderbya.hiredateasc;

结果：





selectfromscott.empwherehiredate>to_date(''1981-02-01'',''yyyy-MM-dd'');



--查询获得补助的所有雇佣名，工资及补助额，并以工资和补助的降序排序

selectename,sal,commfromscott.empawherea.comm>all(0)orderbycommdesc;



--工资低于1500的员工增加10%的工资，工资在1500及以上的增加5%的工资并按工资高低排序（降序）

selectenameas员工姓名,salas补发前的工资,casewhensal<1500then(sal+sal0.1)else(sal+sal0.05)end补助后的工资fromscott.emporderbysaldesc;

结果：





--查询公司每天，每月，每季度，每年的资金支出数额

selectsum(sal/30)as每天发的工资,sum(sal)as每月发的工资,sum(sal)3as每季度发的工资,sum(sal)12as每年发的工资fromscott.emp;

结果：





--查询所有员工的平均工资，总计工资，最高工资和最低工资

selectavg(sal)as平均工资,sum(sal)as总计工资,max(sal)as最高工资,min(sal)as最低工资fromscott.emp;

结果：





--每种岗位的雇员总数和平均工资

selectjobas岗位,count(job)as岗位雇员总数,avg(sal)as平均工资fromscott.empgroupbyjoborderby平均工资desc;

结果：





--雇员总数以及获得补助的雇员数

selectcount()as公司雇员总数,count(comm)as获得补助的雇员人数fromscott.emp;



--管理者的总人数





--雇员工资的最大差额

selectmax(sal),min(sal),(max(sal)-min(sal))as员工工资最大差额fromscott.emp;



--每个部门的平均工资

selectdeptno,avg(sal)fromscott.empagroupbya.deptno;

结果：





--查询每个岗位人数超过2人的所有职员信息

selectfromscott.empa,(selectc.job,count(c.job)asslfromscott.empcgroupbyc.job)bwhereb.sl>2anda.job=b.job;

结果：





selectfromscott.empawherea.empnoin(selectmgrfromscott.emp)and(selectcount(mgr)fromscott.emp)>2;

结果：





--处理重复行数据信息（删除，查找，修改）

selectfroma1awherenotexists(selectb.rdfrom(selectrowidrd,row_number()over(partitionbyLOAN,BRANCHorderbyBEGIN_DATEdesc)rnfroma1)bwhereb.rn=1anda.rowid=b.rd);



--查询emp表数据信息重复问题

selectfromscott.empawhereexists(selectb.rdfrom(selectrowidrd,row_number()over(partitionbyename,job,mgr,hiredate,sal,comm,deptnoorderbyempnoasc)rnfromscott.emp)bwhereb.rn=1anda.rowid=b.rd);



--initcap：返回字符串，字符串第一个字母大写

selectinitcap(ename)Uppfromscott.emp;

结果：





--ascii:返回与指定的字符对应的十进制数

selectascii(a.empno)as编号,ascii(a.ename)as姓名,ascii(a.job)as岗位fromscott.empa;

结果：





--chr:给出整数，返回对应的字符

selectchr(ascii(ename))as姓名fromscott.emp;

结果：





--concat:连接字符串

selectconcat(a.ename,a.job)||a.empnoas字符连接fromscott.empa;

结果：





--instr:在一个字符串中搜索指定的字符,返回发现指定的字符的位置

selectinstr(a.empno,a.mgr,1,1)fromscott.empa;



--length:返回字符串的长度

selectename,length(a.ename)as长度,a.job,length(a.job)as长度fromscott.empa;



--lower：返回字符串，并将所返回的字符小写

selecta.enameas大写,lower(a.ename)as小写fromscott.empa;

结果：





--upper：返回字符串，并将返回字符串都大写

selectlower(a.ename)as小写名字,upper(a.ename)as大写名字fromscott.empa;

结果：





--rpad:在列的右边粘贴字符，lpad:在列的左边粘贴字符(不够字符则用来填满)

selectlpad(rpad(a.ename,10,''''),16,'''')as粘贴fromscott.empa;

结果：





--like不同角度的使用

selectfromscott.empwhereenamelike''%XXR%'';



selectfromscott.empwhereenamelike''%S'';



selectfromscott.empwhereenamelike''J%'';



selectfromscott.empwhereenamelike''S'';



selectfromscott.empwhereenamelike''%S_'';



--每个部门的工资总和

selecta.ename,sum(sal)fromscott.empagroupbyename;



--每个部门的平均工资

selecta.deptno,avg(sal)fromscott.empagroupbydeptno;



--每个部门的最大工资

selecta.deptno,max(sal)fromscott.empagroupbydeptno;



--每个部门的最小工资

selecta.deptno,min(sal)fromscott.empagroupbydeptno;



--查询原工资占部门工资的比率

selectdeptno,sal,ratio_to_report(sal)over(partitionbydeptno)sal_ratiofromscott.emp;



--查询成绩不及格的所有学生信息（提示：没有对应的表，只是意思意思。不及格人数大于等于三才能查）

selectfromscott.empwhereempnoin(selectdistinctempnofromscott.empwhere3<(selectcount(sal)fromscott.empwheresal<3000)andempnoin(selectempnofromscott.empwheresal<3000));

结果：





--查询每个部门的平均工资

selectdistinctdeptno,avg(sal)fromscott.empgroupbydeptnoorderbydeptnodesc;



--union组合查出的结果，但要求查出来的数据类型必须相同

selectsalfromscott.empwheresal>=all(selectsalfromscott.emp)unionselectsalfromscott.emp;



selectfromscott.empawherea.empnobetween7227and7369;--只能从小到大



---------创建表空间要用拥有createtablespace权限的用户，比如sys

createtablespacetbs_datdatafile''c:\oradata\tbs_dat.dbf''size2000M;



---------添加数据文件

altertablespacetbs_datadddatafile''c:\oradata\tbs_dat2.dbf''size100M;



---------改变数据文件大小

alterdatabasedatafile''c:\oradata\tbs_dat.dbf''resize250M;



---------数据文件自动扩展大小

alterdatabasedatafile''c:\oradata\tbs_dat.dbf''autoextendonnext1mmaxsize20m;



---------修改表空间名称

altertablespacetbs_datrenametotbs_dat1;



---------删除表空间anddatafiles表示同时删除物理文件

droptablespacetbs_datincludingcontentsanddatafiles;



--substr(s1,s2,s3)：截取s1字符串,从s2开始,结束s3

selectsubstr(job,3,length(job))fromscott.emp;



--replace：替换字符串

selectreplace(ename,''LL'',''aa'')fromscott.emp;



selectfromscott.test;

insertintoscott.test(ename,job)values(''weather'',''好'');

insertintoscott.test(ename,job)values(''wether'',''差'');



--soundex：返回一个与给定的字符串读音相同的字符串

selectenamefromscott.testwheresoundex(ename)=soundex(''wether'');



--floor：取整数

selectsal,floor(sal)as整数fromscott.emp;



--log（n,s）：返回一个以n为低，s的对数

selectempno,log(empno,2)as对数fromscott.emp;



--mod(n1,n2)：返回一个n1除以n2的余数

selectempno,mod(empno,2)as余数fromscott.emp;

结果：





--power(n1,n2)：返回n1的n2次方根

selectempno,power(empno,2)as方根fromscott.emp;



--round和trunc：按照指定的精度进行舍入

selectround(41.5),round(-41.8),trunc(41.6),trunc(-41.9)fromscott.emp;



--sign：取数字n的符号,大于0返回1,小于0返回-1,等于0返回0

selectsign(45),sign(-21),sign(0)fromscott.emp;

结果：





selectfromscott.emp;





oracle相关的数据库SQL查询语句：



在职员表中查询出基本工资比平均基本工资高的职工编号。

查询一个或者多个部门的所有员工信息，该部门的所有员工工资都高于公司的平均工资。

现有张三的出生日期：1985-01-1501:27:36，请各自新建表，将此日期时间插入表中，并计算出张三的年龄，显示张三的生日。

生日的输出格式要求为MM-DD（未满两位的用0不全），张三的生日为01-15。

算年龄要求用三个方式实现。

生日要求用两个方式实现。







在数据库表中有以下字符数据，如：

13-1,14-2,13-15，13-2，13-108，13-3，13-10,13-200,13-18,100-11,14-1

现在希望通过一条SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排序，输出要拍成如下所示：13-1,13-2,13-3,13-10,13-15,13-18,13-108,13-200,14-1,14-2,100-11

数据库表名：SellRecord;字段ListNumber;

显示所有雇员的姓名以及满10年服务年限后的日期。

显示雇员姓名，根据其服务年限，将最老的雇员排在最前面。

10显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日期所在月排序，将最早年份的职员排在最前面。

显示假设一个月为30天的情况下所有雇员的日薪金。

找出在（任何年份的）2月受聘的所有雇员（用两种方式实现）。

对于每个雇员，显示其加入公司的天数。

以年，月和日的方式显示所有雇员的服务年限（入职多少年/入职了多少月/入职了多少天）。

找出各月最后一天受雇的所有雇员。

找出早于25年之前受雇的雇员（用两种方式实现）。

工资最低1500的职员增加10%，1500以上的增加5%的工资，用一条update语句实现（用两种方式实现）。

按照部门统计每种岗位的平均工资，要求输出的格式如下图所示：



18.



19.



20.



21,。



22.





本人声明：以上内容出现任何错误与不足，皆与本人无关。
献花(0)
+1
(本文系fangfei8866...首藏)