Oracle查询语句
select*from scott.emp ;
1.--dense_rank()分析函数（查找每个部门工资最高前三名员工信息）
select*from(selectdeptno,ename,sal,dense_rank()over(partitionby deptno orderby sal desc) a fromscott.emp)where a<=3orderbydeptno asc,sal desc;
附:
partiton by 在很多语法中都有用到。根本一点就是分区
例如select name ，row_number()over(partitionby year ,montn order by year,month)
from psss
很高兴为你解答， 相信group by你一定用过吧， 先对比说下
partition by关键字是oracle中分析性函数的一部分，它和聚合函数不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录，partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，它有一部分函数既是聚合函数也是分析函数，比如avg、max，也有一部分是特有的，比如first、rank，除了order by子句外，分析函数在一个查询中优先级最低。至于partition by和group by谁的性能更好，要看具体情况而定，partition by的作用仅用于分组，那么性能可能比不上group by
结果：
--rank()分析函数(运行结果与上语句相同)
select*from(selectdeptno,ename,sal,rank()over(partitionby deptno orderby sal desc) a fromscott.emp )where a<=3orderbydeptno asc,sal desc;
结果：
--row_number()分析函数（运行结果与上相同）
select*from(select deptno,ename,sal,row_number()over(partitionby deptno orderby sal desc) a fromscott.emp)where a<=3orderbydeptno asc,sal desc;
--rowsunbounded preceding 分析函数（显示各部门的积累工资总和）
select deptno,sal,sum(sal)over(orderby deptno ascrowsunboundedpreceding)积累工资总和 from scott.emp ;
结果：
--rows 整数值 preceding(显示每最后4条记录的汇总值)
select deptno,sal,sum(sal)over(orderby deptno rows3preceding)每4汇总值 fromscott.emp ;
结果：
--rows between1 preceding and 1 following（统计3条记录的汇总值【当前记录居中】）
select deptno,ename,sal,sum(sal)over(orderby deptno rowsbetween1precedingand1following)汇总值 from scott.emp ;
结果：
--ratio_to_report(显示员工工资及占该部门总工资的比例)
select deptno,sal,ratio_to_report(sal)over(partitionby deptno)比例 fromscott.emp ;
结果：
--查看所有用户
select*from dba_users ;
selectcount(*)fromdba_users ;
select*from all_users ;
select*from user_users ;
select*from dba_roles ;
--查看用户系统权限
select*from dba_sys_privs ;
select*from user_users ;
--查看用户对象或角色权限
select*from dba_tab_privs ;
select*from all_tab_privs ;
select*from user_tab_privs ;
--查看用户或角色所拥有的角色
select*from dba_role_privs ;
select*from user_role_privs ;
--rownum:查询10至12信息
select*from scott.emp a whererownum<=3and a.empno notin(select b.empno from scott.emp b whererownum<=9);
结果：
--notexists；查询emp表在dept表中没有的数据
select*from scott.emp a wherenotexists(select*from scott.dept b where a.empno=b.deptno);
结果：
--rowid;查询重复数据信息
select*from scott.emp a where a.rowid>(selectmin(x.rowid)from scott.emp x where x.empno=a.empno);
--根据rowid来分页(一万条数据，查询10000至9980时间大概在0.03秒左右)
select*from scott.emp whererowidin(select rid from(selectrownum rn,rid from(selectrowid rid,empno from scott.emp orderby empno desc)whererownum<10)where rn>=1)orderby empno desc;
结果：
--根据分析函数分页(一万条数据，查询10000至9980时间大概在1.01秒左右)
select*from(select a.*,row_number()over(orderby empno desc) rk fromscott.emp a )where rk<10and rk>=1;
结果：
、
--rownum分页(一万条数据，查询10000至9980时间大概在0.01秒左右)
select*from(select t.*,rownum rn from(select*fromscott.emp orderby empno desc)t whererownum<10)where rn>=1;
select*from(select a.*,rownum rn from(select*from scott.emp) a whererownum<=10)where rn>=5;
--leftouter join:左连接
select a.*,b.*from scott.emp a leftouterjoin scott.dept b on a.deptno=b.deptno ;
--rightouter join:右连接
select a.*,b.*from scott.emp a rightouterjoin scott.dept b on a.deptno=b.deptno ;
--innerjoin
select a.*,b.*from scott.emp a inner joinscott.dept b on a.deptno=b.deptno ;
--fulljoin
select a.*,b.*from scott.emp a fulljoin scott.dept b on a.deptno=b.deptno ;
select a.*,b.*from scott.emp a,scott.dept b where a.deptno(+)=b.deptno ;
selectdistinct ename,sal from scott.emp a groupby sal having;
select*from scott.dept ;
select*from scott.emp ;
--casewhen then end (交叉报表)
select ename,sal,casedeptno when10then'会计部'when20then'研究部'when30then'销售部'else'其他部门'end部门 fromscott.emp ;
结果：
select ename,sal,casewhen sal>0and sal<1500then'一级工资'when sal>=1500and sal<3000then'二级工资'when sal>=3000and sal<4500then'三级工资'else'四级工资'end工资等级 from scott.emp orderby sal desc;
结果：
--交叉报表是使用分组函数与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。。。。connect by(层次查询)
selectlevel,emp.*fromscott.emp connectbyprior empno = mgr orderbylevel;
结果：
--sys_connect_by_path函数
select ename,sys_connect_by_path(ename,'/')fromscott.emp startwith mgr isnullconnectbyprior empno=mgr ;
结果：
--startwith connect by prior 语法
selectlpad(ename,3*(level),'')姓名,lpad(ename,3*(level),'')姓名 fromscott.emp where job<>'CLERK'startwith mgr isnullconnectbyprior mgr =empno ;
--level与prior关键字
selectlevel,emp.*fromscott.emp startwith ename='SCOTT'connectbypriorempno=mgr;
selectlevel,emp.*fromscott.emp startwith ename='SCOTT'connectbyempno =prior mgr ;
结果：
--等值连接
select empno,ename,job,sal,dname from scott.emp a,scott.dept b where a.deptno=b.deptno and(a.deptno=10or sal>2500);
结果：
--非等值连接
select a.ename,a.sal,b.grade from scott.emp a,scott.salgrade b wherea.sal between b.losal and b.hisal ;
结果：
--自连接
select a.ename,a.sal,b.ename from scott.emp a,scott.emp b where a.mgr=b.empno ;
结果：
--左外连接
select a.ename,a.sal,b.ename from scott.emp a,scott.emp b where a.mgr=b.empno(+);
结果：
--多表连接
select*from scott.emp ,scott.dept,scott.salgrade where scott.emp.deptno=scott.dept.deptnoand scott.emp.sal between scott.salgrade.losal andscott.salgrade.hisal ;
结果：
select*from scott.emp a join scott.dept b on a.deptno=b.deptno join scott.salgrade s ona.sal between s.losal and s.hisal where a.sal>1000;
select*from(select*from scott.emp a join scott.dept b on a.deptno=b.deptno where a.sal>1000) c join scott.salgrade s onc.sal between s.losal and s.hisal ;
--单行子查询
select*from scott.emp a where a.deptno=(selectdeptno from scott.dept where loc='NEW YORK');
select*from scott.emp a where a.deptno in(selectdeptno from scott.dept where loc='NEW YORK');
结果：
--单行子查询在 from 后
select scott.emp.*,(selectdeptno from scott.dept where loc='NEW YORK') a fromscott.emp ;
--使用 in ,all,any 多行子查询
--in：表示等于查询出来的对应数据
select ename,job,sal,deptno from scott.emp where job in(selectdistinct job from scott.emp where deptno=10);
--all:表示大于所有括号中查询出来的对应的数据信息
select ename,sal,deptno from scott.emp where sal>all(select sal fromscott.emp where deptno=30);
--any:表示大于括号查询出来的其中任意一个即可（只随机一个）
select ename,sal,deptno from scott.emp where sal>any(select sal fromscott.emp where deptno=30);
--多列子查询
select ename,job,sal,deptno from scott.emp where(deptno,job)=(select deptno,job from scott.emp where ename='SCOTT');
select ename,job,sal,deptno from scott.emp where(sal,nvl(comm,-1))in(select sal,nvl(comm,-1)fromscott.emp where deptno=30);
--非成对比较
select ename,job,sal,deptno from scott.emp where sal in(select sal from scott.emp where deptno=30)andnvl(comm,-1)in(selectnvl(comm,-1)from scott.emp where deptno=30);
--其他子查询
select ename,job,sal,deptno from scott.emp whereexists(selectnullfrom scott.dept where scott.dept.deptno=scott.emp.deptnoand scott.dept.loc='NEW YORK');
select ename,job,sal from scott.emp join(select deptno,avg(sal) avgsal,nullfromscott.emp groupby deptno) dept on emp.deptno=dept.deptno where sal>dept.avgsal ;
createtable scott.test(
ename varchar(20),
job varchar(20)
);
--droptable test ;
select*from scott.test ;
--Insert与子查询(表间数据的拷贝)
insertinto scott.test(ename,job)select ename,job from scott.emp ;
--Update与子查询
update scott.test set(ename,job)=(select ename,job from scott.emp where ename='SCOTT'anddeptno ='10');
--创建表时，还可以指定列名
createtable scott.test_1(ename,job)asselect ename,job from scott.emp ;
select*from scott.test_1 ;
--delete与子查询
deletefrom scott.test where ename in('');
--合并查询
--union语法（合并且去除重复行，且排序）
select ename,sal,deptno from scott.emp where deptno>10unionselectename,sal,deptno from scott.emp where deptno<30;
select a.deptno from scott.emp a unionselect b.deptno from scott.dept b ;
--unionall(直接将两个结果集合并，不排序)
select ename,sal,deptno from scott.emp where deptno>10unionallselectename,sal,deptno from scott.emp where deptno<30;
select a.deptno from scott.emp a unionallselect b.deptno from scott.dept b ;
--intersect:取交集
select ename,sal,deptno from scott.emp where deptno>10intersectselectename,sal,deptno from scott.emp where deptno<30;
--显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和
select dname as部门,sum(sal)as工资总和 from scott.emp a,scott.dept b where a.deptno=b.deptno groupby dname havingsum(sal)>(selectsum(sal)/3from scott.emp c,scott.dept d where c.deptno=d.deptno);
结果：
--使用with得到以上同样的结果
withtestas(selectdname ,sum(sal) sumsal fromscott.emp ,scott.dept where scott.emp.deptno=scott.dept.deptnogroupby dname)select dname as部门,sumsalas工资总和 fromscott.test where sumsal>(selectsum(sumsal)/3fromscott.test);
结果：
--分析函数
select ename,sal,sum(sal)over(partitionby deptno orderby sal desc)fromscott.emp ;
--rowsn preceding(窗口子句一)
select deptno,sal,sum(sal)over(orderby sal rows5preceding)fromscott.emp ;
结果：
--rum(..)over(..)..
select sal,sum(1)over(orderby sal) aa from scott.emp ;
select deptno,ename,sal,sum(sal)over(orderby ename)连续求和,sum(sal)over()总和,100*round(sal/sum(sal)over(),4)as份额 fromscott.emp;
结果：
select deptno,ename,sal,sum(sal)over(partitionby deptno orderby ename)部门连续求和,sum(sal)over(partitionbydeptno)部门总和,100*round(sal/sum(sal)over(),4)as总份额 fromscott.emp;
结果：
select deptno,sal,rank()over(partitionbydeptno orderby sal),dense_rank()over(partitionby deptno orderby sal)from scott.emp orderby deptno ;
结果；
select*from(selectrank()over(partitionby课程 orderby分数 desc) rk,分析函数_rank.*from分析函数_rank)where rk<=3;
--dense_rank():有重复的数字不跳着排列
--row_number()
select deptno,sal,row_number()over(partitionby deptno orderby sal) rm from scott.emp ;
结果：
--lag()和lead()
select deptno,sal,lag(sal)over(partitionby deptno orderby sal)上一个,lead(sal)over(partitionbydeptno orderby sal)from scott.emp ;
结果：
--max(),min(),avg()
select deptno,sal,max(sal)over(partitionby deptno orderby sal)最大,min(sal)over(partitionby deptno orderby sal)最小,avg(sal)over(partitionby deptno orderby sal)平均 from scott.emp ;
结果：
--first_value(),last_value()
select deptno,sal,first_value(sal)over(partitionby deptno)最前,last_value(sal)over(partitionby deptno )最后 from scott.emp ;
结果：
--分组补充 group by grouping sets
select deptno ,sal,sum(sal)from scott.emp groupbygroupingsets(deptno,sal);
selectnull,sal,sum(sal)fromscott.emp groupby sal unionallselect deptno,null,sum(sal)from scott.emp groupby deptno ;
结果：
--rollup
select deptno,job,avg(sal)from scott.emp groupbyrollup(deptno,job);
--理解rollup等价于
select deptno,job,avg(sal)from scott.emp groupby deptno,job unionselect deptno ,null,avg(sal)from scott.emp groupby deptno unionselectnull,null,avg(sal)fromscott.emp ;
结果：
select deptno,job,avg(sal) a from scott.emp groupbycube(deptno,job);
--理解CUBE
select deptno,job,avg(sal)from scott.emp groupbycube(deptno,job);
--等价于
select deptno,job,avg(sal)from scott.emp groupbygroupingsets((deptno,job),(deptno),(job),());
结果：
--查询工资不在1500至2850之间的所有雇员名及工资
select ename,sal from scott.emp where sal notin(select sal from scott.emp where sal between1500and2850);
--部门10和30中的工资超过1500的雇员名及工资
select deptno,ename,sal from scott.emp a where a.deptno in(10,30)anda.sal>1500orderby sal desc;
结果：
--在1981年2月1日至1981年5月1日之间雇佣的雇员名，岗位及雇佣日期，并以雇佣日期先后顺序排序
select ename as姓名,job as岗位,hiredate as雇佣日期 fromscott.emp a where a.hiredate between to_date('1981-02-01','yyyy-mm-dd')andto_date('1981-05-01','yyyy-mm-dd')orderbya.hiredate asc;
结果：
select*from scott.emp where hiredate >to_date('1981-02-01','yyyy-MM-dd');
--查询获得补助的所有雇佣名，工资及补助额，并以工资和补助的降序排序
select ename,sal,comm from scott.emp a where a.comm >all(0)orderby commdesc;
--工资低于1500的员工增加10%的工资，工资在1500及以上的增加5%的工资并按工资高低排序（降序）
select ename as员工姓名,sal as补发前的工资,casewhen sal<1500then(sal+sal*0.1)else(sal+sal*0.05)end补助后的工资 fromscott.emp orderby sal desc;
结果：
--查询公司每天，每月，每季度，每年的资金支出数额
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;
结果：
--每种岗位的雇员总数和平均工资
select job as岗位,count(job)as岗位雇员总数,avg(sal)as平均工资 fromscott.emp groupby job orderby平均工资 desc;
结果：
--雇员总数以及获得补助的雇员数
selectcount(*)as公司雇员总数,count(comm)as获得补助的雇员人数 fromscott.emp ;
--管理者的总人数
--雇员工资的最大差额
selectmax(sal),min(sal),(max(sal)-min(sal))as员工工资最大差额 fromscott.emp ;
--每个部门的平均工资
select deptno,avg(sal)from scott.emp a groupby a.deptno;
结果：
--查询每个岗位人数超过2人的所有职员信息
select*from scott.emp a,(selectc.job,count(c.job)as sl from scott.emp c groupby c.job ) b where b.sl>2anda.job=b.job;
结果：
select*from scott.emp a where a.empno in(select mgr from scott.emp )and(selectcount(mgr)from scott.emp)>2;
结果：
--处理重复行数据信息（删除，查找，修改）
select*from a1 a wherenotexists(select b.rd from(selectrowid rd,row_number()over(partitionby LOAN,BRANCH orderby BEGIN_DATE desc) rn from a1) b where b.rn=1and a.rowid = b.rd);
--查询emp表数据信息重复问题
select*from scott.emp a whereexists(select b.rd from(selectrowid rd,row_number()over(partitionby ename,job,mgr,hiredate,sal,comm,deptno orderby empno asc) rn fromscott.emp) b where b.rn=1anda.rowid=b.rd);
--initcap：返回字符串，字符串第一个字母大写
selectinitcap(ename) Upp fromscott.emp ;
结果：
--ascii:返回与指定的字符对应的十进制数
selectascii(a.empno)as编号,ascii(a.ename)as姓名,ascii(a.job)as岗位 from scott.emp a ;
结果：
--chr:给出整数，返回对应的字符
selectchr(ascii(ename))as姓名 from scott.emp ;
结果：
--concat:连接字符串
selectconcat(a.ename,a.job)||a.empno as字符连接 fromscott.emp a;
结果：
--instr:在一个字符串中搜索指定的字符,返回发现指定的字符的位置
selectinstr(a.empno,a.mgr,1,1)fromscott.emp a ;
--length:返回字符串的长度
select ename,length(a.ename)as长度,a.job,length(a.job)as长度 from scott.emp a ;
--lower：返回字符串，并将所返回的字符小写
select a.ename as大写,lower(a.ename)as小写 from scott.emp a ;
结果：
--upper：返回字符串，并将返回字符串都大写
selectlower(a.ename)as小写名字,upper(a.ename)as大写名字 fromscott.emp a ;
结果：
--rpad:在列的右边粘贴字符，lpad: 在列的左边粘贴字符(不够字符则用*来填满)
selectlpad(rpad(a.ename,10,'*'),16,'*')as粘贴 from scott.emp a ;
结果：
--like不同角度的使用
select*from scott.emp where ename like'%XXR%';
select*from scott.emp where ename like'%S';
select*from scott.emp where ename like'J%';
select*from scott.emp where ename like'S';
select*from scott.emp where ename like'%S_';
--每个部门的工资总和
select a.ename,sum(sal)from scott.emp a groupby ename;
--每个部门的平均工资
select a.deptno,avg(sal)from scott.emp a groupby deptno ;
--每个部门的最大工资
select a.deptno,max(sal)from scott.emp a groupby deptno ;
--每个部门的最小工资
select a.deptno,min(sal)from scott.emp a groupby deptno ;
--查询原工资占部门工资的比率
select deptno ,sal,ratio_to_report(sal)over(partitionby deptno) sal_ratio from scott.emp ;
--查询成绩不及格的所有学生信息（提示：没有对应的表，只是意思意思。不及格人数大于等于三才能查）
select*from scott.emp where empno in(selectdistinct empno from scott.emp where3<(selectcount(sal)fromscott.emp where sal<3000)and empno in(select empno from scott.emp where sal<3000));
结果：
--查询每个部门的平均工资
selectdistinct deptno,avg(sal)from scott.emp groupby deptno orderbydeptno desc;
--union组合查出的结果，但要求查出来的数据类型必须相同
select sal from scott.emp where sal >=all(select sal fromscott.emp )unionselect sal from scott.emp ;
select*from scott.emp a where a.empno between7227and7369;--只能从小到大
---------创建表空间 要用拥有create tablespace权限的用户，比如sys
createtablespace tbs_dat datafile'c:\oradata\tbs_dat.dbf'size2000M;
---------添加数据文件
altertablespace tbs_dat adddatafile'c:\oradata\tbs_dat2.dbf'size100M;
---------改变数据文件大小
alterdatabasedatafile'c:\oradata\tbs_dat.dbf'resize250M;
---------数据文件自动扩展大小
alterdatabasedatafile'c:\oradata\tbs_dat.dbf'autoextendonnext1mmaxsize20m;
---------修改表空间名称
altertablespace tbs_dat renameto tbs_dat1;
---------删除表空间 and datafiles 表示同时删除物理文件
droptablespace tbs_dat includingcontentsanddatafiles;
--substr(s1,s2,s3)：截取s1字符串,从s2开始,结束s3
selectsubstr(job,3,length(job))fromscott.emp ;
--replace：替换字符串
selectreplace(ename,'LL','aa')fromscott.emp;
select*from scott.test;
insertinto scott.test(ename,job)values('weather','好');
insertinto scott.test(ename,job)values('wether','差');
--soundex：返回一个与给定的字符串读音相同的字符串
select ename from scott.test wheresoundex(ename)=soundex('wether');
--floor：取整数
select sal,floor(sal)as整数 fromscott.emp ;
--log（n,s）：返回一个以n为低，s的对数
select empno,log(empno,2)as对数 fromscott.emp ;
--mod(n1,n2)：返回一个n1除以n2的余数
select empno,mod(empno,2)as余数 fromscott.emp ;
结果：
--power(n1,n2)：返回n1的n2次方根
select empno,power(empno,2)as方根 from scott.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*from scott.emp;
6922
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
