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.empainner joinscott.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)sumsal fromscott.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(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;

结果：

select*fromscott.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+sal*0.1)else(sal+sal*0.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人的所有职员信息

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

结果：

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

结果：

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

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

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

select*fromscott.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不同角度的使用

select*fromscott.empwhereenamelike"%XXR%";

select*fromscott.empwhereenamelike"%S";

select*fromscott.empwhereenamelike"J%";

select*fromscott.empwhereenamelike"S";

select*fromscott.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;

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

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

结果：

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

selectdistinctdeptno,avg(sal)fromscott.empgroupbydeptno orderbydeptnodesc;

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

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

select*fromscott.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;

select*fromscott.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;

结果：

select*fromscott.emp;

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

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

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

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

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

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

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

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

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;

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

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

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

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

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

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

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

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

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

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

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

18.

19.

20.

21,。

22.

本人声明：以上内容出现任何错误与不足，皆与本人无关。