oracle经典SQL查询语句

1、Oracle查询语句select * from scott.emp ;1.-dense_rank()分析函数（查找每个部门工资最高前三名员工信息）select * from (select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) a from scott.emp) where a<=3 order by deptno asc,sal desc ;结果：-rank()分析函数(运行结果与上语句相同)select * from (select deptno,ename,sal,rank

2、() over(partition by deptno order by sal desc) a from scott.emp ) where a<=3 order by deptno asc,sal desc ;结果：-row_number()分析函数（运行结果与上相同）select * from(select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) a from scott.emp) where a<=3 order by deptno asc,sal desc ;-ro

3、ws unbounded preceding 分析函数（显示各部门的积累工资总和）select deptno,sal,sum(sal) over(order by deptno asc rows unbounded preceding) 积累工资总和 from scott.emp ;结果：-rows 整数值 preceding(显示每最后4条记录的汇总值)select deptno,sal,sum(sal) over(order by deptno rows 3 preceding) 每4汇总值 from scott.emp ;结果：-rows between 1 preceding and

4、1 following（统计3条记录的汇总值【当前记录居中】）select deptno,ename,sal,sum(sal) over(order by deptno rows between 1 preceding and 1 following) 汇总值 from scott.emp ;结果：-ratio_to_report(显示员工工资及占该部门总工资的比例)select deptno,sal,ratio_to_report(sal) over(partition by deptno) 比例 from scott.emp ;结果：-查看所有用户select * from dba_use

5、rs ;select count(*) from dba_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 * f

6、rom dba_role_privs ;select * from user_role_privs ;- rownum:查询10至12信息select * from scott.emp a where rownum<=3 and a.empno not in(select b.empno from scott.emp b where rownum<=9);结果：-not exists；查询emp表在dept表中没有的数据select * from scott.emp a where not exists(select * from scott.dept b where a.empn

7、o=b.deptno) ; 结果：-rowid;查询重复数据信息select * from scott.emp a where a.rowid>(select min(x.rowid) from scott.emp x where x.empno=a.empno);-根据rowid来分页(一万条数据，查询10000至9980时间大概在0.03秒左右)select * from scott.emp where rowid in(select rid from(select rownum rn,rid from(select rowid rid,empno from scott.emp or

8、der by empno desc) where rownum<10)where rn>=1)order by empno desc ;结果：-根据分析函数分页(一万条数据，查询10000至9980时间大概在1.01秒左右)select * from(select a.*,row_number() over(order by empno desc) rk from scott.emp a ) where rk<10 and rk>=1;结果：-rownum分页(一万条数据，查询10000至9980时间大概在0.01秒左右)select * from(select t.*

9、,rownum rn from(select * from scott.emp order by empno desc)t where rownum<10) where rn>=1;select * from(select a.*,rownum rn from (select * from scott.emp) a where rownum<=10) where rn>=5 ;-left outer join:左连接select a.*,b.* from scott.emp a left outer join scott.dept b on a.deptno=b.dep

10、tno ;-right outer join:右连接select a.*,b.* from scott.emp a right outer join scott.dept b on a.deptno=b.deptno ;-inner joinselect a.*,b.* from scott.emp a inner join scott.dept b on a.deptno=b.deptno ;-full join select a.*,b.* from scott.emp a full join scott.dept b on a.deptno=b.deptno ;select a.*,b.

11、* from scott.emp a,scott.dept b where a.deptno(+)=b.deptno ;select distinct ename,sal from scott.emp a group by sal having ;select * from scott.dept ;select * from scott.emp ;-case when then end (交叉报表)select ename,sal,case deptno when 10 then '会计部' when 20 then '研究部' when 30 then 

12、9;销售部' else '其他部门' end 部门 from scott.emp ;结果：select ename,sal,case when sal>0 and sal<1500 then '一级工资' when sal>=1500 and sal<3000 then '二级工资' when sal>=3000 and sal<4500 then '三级工资' else '四级工资' end 工资等级 from scott.emp order by sal desc ;

13、结果：-交叉报表是使用分组函数与case结构一起实现select 姓名,sum(case 课程 when '数学' then 分数 end)数学,sum(case 课程 when '历史' then 分数 end)历史 from 学生 group by 姓名 ;-decode 函数select 姓名,sum(decode(课程,'数学',分数,null)数学,sum(decode(课程,'语文',分数,null)语文,sum(decode(课程,'历史','分数',null)历史 from 学生 g

14、roup by 姓名 ;-level。connect by(层次查询)select level,emp.* from scott.emp connect by prior empno = mgr order by level ;结果：-sys_connect_by_path函数select ename,sys_connect_by_path(ename,'/') from scott.emp start with mgr is null connect by prior empno=mgr ;结果：-start with connect by prior 语法select lp

15、ad(ename,3*(level),'')姓名,lpad(ename,3*(level),'')姓名 from scott.emp where job<>'CLERK' start with mgr is null connect by prior mgr = empno ;-level与prior关键字select level,emp.* from scott.emp start with ename='SCOTT' connect by prior empno=mgr;select level,emp.* fro

16、m scott.emp start with ename='SCOTT' connect by empno = prior mgr ;结果：-等值连接select empno,ename,job,sal,dname from scott.emp a,scott.dept b where a.deptno=b.deptno and (a.deptno=10 or sal>2500);结果：-非等值连接select a.ename,a.sal,b.grade from scott.emp a,scott.salgrade b where a.sal between b.los

17、al 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.deptno and scott

18、.emp.sal between scott.salgrade.losal and scott.salgrade.hisal ;结果：select * from scott.emp a join scott.dept b on a.deptno=b.deptno join scott.salgrade s on a.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.sa

19、l>1000) c join scott.salgrade s on c.sal between s.losal and s.hisal ;-单行子查询select * from scott.emp a where a.deptno=(select deptno from scott.dept where loc='NEW YORK');select * from scott.emp a where a.deptno in (select deptno from scott.dept where loc='NEW YORK');结果：-单行子查询在 fro

20、m 后select scott.emp.*,(select deptno from scott.dept where loc='NEW YORK') a from scott.emp ;-使用 in ,all,any 多行子查询-in：表示等于查询出来的对应数据select ename,job,sal,deptno from scott.emp where job in(select distinct job from scott.emp where deptno=10);-all:表示大于所有括号中查询出来的对应的数据信息select ename,sal,deptno fro

21、m scott.emp where sal>all(select sal from scott.emp where deptno=30);-any:表示大于括号查询出来的其中任意一个即可（只随机一个）select ename,sal,deptno from scott.emp where sal>any(select sal from scott.emp where deptno=30);-多列子查询select ename,job,sal,deptno from scott.emp where(deptno,job)=(select deptno,job from scott.e

22、mp where ename='SCOTT');select ename,job,sal,deptno from scott.emp where(sal,nvl(comm,-1) in(select sal,nvl(comm,-1) from scott.emp where deptno=30);-非成对比较select ename,job,sal,deptno from scott.emp where sal in(select sal from scott.emp where deptno=30) and nvl(comm,-1) in(select nvl(comm,-1

23、) from scott.emp where deptno=30);-其他子查询select ename,job,sal,deptno from scott.emp where exists(select null from scott.dept where scott.dept.deptno=scott.emp.deptno and scott.dept.loc='NEW YORK');select ename,job,sal from scott.emp join(select deptno,avg(sal) avgsal,null from scott.emp group

24、 by deptno) dept on emp.deptno=dept.deptno where sal>dept.avgsal ;create table scott.test( ename varchar(20), job varchar(20);-drop table test ;select * from scott.test ;-Insert与子查询(表间数据的拷贝)insert into scott.test(ename,job) select ename,job from scott.emp ;-Update与子查询update scott.test set(ename,j

25、ob)=(select ename,job from scott.emp where ename='SCOTT' and deptno ='10');-创建表时，还可以指定列名create table scott.test_1(ename,job) as select ename,job from scott.emp ;select * from scott.test_1 ;-delete与子查询delete from scott.test where ename in('');-合并查询-union语法（合并且去除重复行，且排序）select

26、ename,sal,deptno from scott.emp where deptno>10 union select ename,sal,deptno from scott.emp where deptno<30 ;select a.deptno from scott.emp a union select b.deptno from scott.dept b ;-union all(直接将两个结果集合并，不排序)select ename,sal,deptno from scott.emp where deptno>10 union all select ename,sal

27、,deptno from scott.emp where deptno<30 ;select a.deptno from scott.emp a union all select b.deptno from scott.dept b ;-intersect:取交集select ename,sal,deptno from scott.emp where deptno>10 intersect select ename,sal,deptno from scott.emp where deptno<30;-显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和select dn

28、ame as 部门,sum(sal) as 工资总和 from scott.emp a,scott.dept b where a.deptno=b.deptno group by dname having sum(sal)>(select sum(sal)/3 from scott.emp c,scott.dept d where c.deptno=d.deptno);结果：-使用with得到以上同样的结果with test as (select dname ,sum(sal) sumsal from scott.emp ,scott.dept where scott.emp.deptn

29、o=scott.dept.deptno group by dname) select dname as 部门,sumsal as 工资总和 from scott.test where sumsal>(select sum(sumsal)/3 from scott.test);结果：-分析函数select ename,sal,sum(sal) over(partition by deptno order by sal desc) from scott.emp ;-rows n preceding(窗口子句一)select deptno,sal,sum(sal) over(order by

30、sal rows 5 preceding) from scott.emp ;结果：-rum(.) over(.).select sal,sum(1) over(order by sal) aa from scott.emp ;select deptno,ename,sal,sum(sal) over(order by ename) 连续求和,sum(sal) over() 总和,100*round(sal/sum(sal) over(),4) as 份额 from scott.emp;结果：select deptno,ename,sal,sum(sal) over(partition by d

31、eptno order by ename) 部门连续求和,sum(sal) over(partition by deptno) 部门总和,100*round(sal/sum(sal) over(),4) as 总份额 from scott.emp;结果：select deptno,sal,rank() over (partition by deptno order by sal),dense_rank() over(partition by deptno order by sal) from scott.emp order by deptno ;结果；select * from (select

32、 rank() over(partition by 课程 order by 分数 desc) rk,分析函数_rank.* from 分析函数_rank) where rk<=3 ;-dense_rank():有重复的数字不跳着排列-row_number()select deptno,sal,row_number() over(partition by deptno order by sal) rm from scott.emp ;结果：-lag()和lead()select deptno,sal,lag(sal) over(partition by deptno order by sa

33、l) 上一个,lead(sal) over(partition by deptno order by sal) from scott.emp ;结果：-max(),min(),avg()select deptno,sal,max(sal) over(partition by deptno order by sal)最大,min(sal) over(partition by deptno order by sal)最小,avg(sal) over(partition by deptno order by sal)平均 from scott.emp ;结果：-first_value(),last_

34、value()select deptno,sal,first_value(sal) over(partition by deptno)最前,last_value(sal) over(partition by deptno )最后 from scott.emp ;结果：-分组补充 group by grouping setsselect deptno ,sal,sum(sal) from scott.emp group by grouping sets(deptno,sal);select null,sal,sum(sal) from scott.emp group by sal union a

35、ll select deptno,null,sum(sal) from scott.emp group by deptno ;结果：-rollupselect deptno,job,avg(sal) from scott.emp group by rollup(deptno,job) ;-理解rollup等价于select deptno,job,avg(sal) from scott.emp group by deptno,job union select deptno ,null,avg(sal) from scott.emp group by deptno union select nul

36、l,null,avg(sal) from scott.emp ;结果：select deptno,job,avg(sal) a from scott.emp group by cube(deptno,job) ;-理解CUBEselect deptno,job,avg(sal) from scott.emp group by cube(deptno,job) ;-等价于select deptno,job,avg(sal) from scott.emp group by grouping sets(deptno,job),(deptno),(job),();结果：-查询工资不在1500至2850

37、之间的所有雇员名及工资select ename,sal from scott.emp where sal not in(select sal from scott.emp where sal between 1500 and 2850 );-部门10和30中的工资超过1500的雇员名及工资select deptno,ename,sal from scott.emp a where a.deptno in(10,30) and a.sal>1500 order by sal desc ;结果：-在1981年2月1日至1981年5月1日之间雇佣的雇员名，岗位及雇佣日期，并以雇佣日期先后顺序排

38、序select ename as 姓名,job as 岗位,hiredate as 雇佣日期 from scott.emp a where a.hiredate between to_date('1981-02-01','yyyy-mm-dd') and to_date('1981-05-01','yyyy-mm-dd') order by a.hiredate asc ;结果：select * from scott.emp where hiredate >to_date('1981-02-01','

39、yyyy-MM-dd');-查询获得补助的所有雇佣名，工资及补助额，并以工资和补助的降序排序select ename,sal,comm from scott.emp a where m > all(0) order by comm desc;-工资低于1500的员工增加10%的工资，工资在1500及以上的增加5%的工资并按工资高低排序（降序）select ename as 员工姓名,sal as 补发前的工资,case when sal<1500 then (sal+sal*0.1) else (sal+sal*0.05) end 补助后的工资 from scott.emp

40、 order by sal desc ;结果：-查询公司每天，每月，每季度，每年的资金支出数额select sum(sal/30) as 每天发的工资,sum(sal) as 每月发的工资,sum(sal)*3 as 每季度发的工资,sum(sal)*12 as 每年发的工资 from scott.emp;结果：-查询所有员工的平均工资，总计工资，最高工资和最低工资select avg(sal) as 平均工资,sum(sal) as 总计工资,max(sal) as 最高工资,min(sal) as 最低工资 from scott.emp;结果：-每种岗位的雇员总数和平均工资select j

41、ob as 岗位,count(job) as 岗位雇员总数,avg(sal) as 平均工资 from scott.emp group by job order by 平均工资 desc;结果：-雇员总数以及获得补助的雇员数select count(*) as 公司雇员总数,count(comm) as 获得补助的雇员人数 from scott.emp ;-管理者的总人数-雇员工资的最大差额select max(sal),min(sal),(max(sal) - min(sal) as 员工工资最大差额 from scott.emp ;-每个部门的平均工资select deptno,avg(s

42、al) from scott.emp a group by a.deptno;结果：-查询每个岗位人数超过2人的所有职员信息select * from scott.emp a,(select c.job,count(c.job) as sl from scott.emp c group by c.job ) b where b.sl>2 and a.job=b.job; 结果：select * from scott.emp a where a.empno in(select mgr from scott.emp ) and (select count(mgr) from scott.em

43、p)>2 ;结果：-处理重复行数据信息（删除，查找，修改）select * from a1 a where not exists (select b.rd from (select rowid rd,row_number() over(partition by LOAN, BRANCH order by BEGIN_DATE desc) rn from a1) b where b.rn = 1 and a.rowid = b.rd);-查询emp表数据信息重复问题select * from scott.emp a where exists(select b.rd from(select

44、rowid rd,row_number() over(partition by ename,job,mgr,hiredate,sal,comm,deptno order by empno asc) rn from scott.emp) b where b.rn=1 and a.rowid=b.rd);-initcap：返回字符串，字符串第一个字母大写select initcap(ename) Upp from scott.emp ;结果：-ascii:返回与指定的字符对应的十进制数select ascii(a.empno) as 编号,ascii(a.ename) as 姓名,ascii(a.

45、job) as 岗位 from scott.emp a ;结果：-chr:给出整数，返回对应的字符select chr(ascii(ename) as 姓名 from scott.emp ;结果：-concat:连接字符串select concat(a.ename,a.job)| a.empno as 字符连接 from scott.emp a;结果：-instr:在一个字符串中搜索指定的字符,返回发现指定的字符的位置select instr(a.empno,a.mgr,1,1) from scott.emp a ;-length:返回字符串的长度select ename,length(a.e

46、name) 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：返回字符串，并将返回字符串都大写select lower(a.ename) as 小写名字,upper(a.ename) as 大写名字 from scott.emp a ;结果：-rpad:在列的右边粘贴字符，lpad: 在列的左边粘贴字符(不够字符则用*来填满)select lpad(rpad(a

47、.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 *

48、 from scott.emp where ename like '%S_'-每个部门的工资总和select a.ename,sum(sal) from scott.emp a group by ename;-每个部门的平均工资select a.deptno,avg(sal) from scott.emp a group by deptno ;-每个部门的最大工资select a.deptno,max(sal) from scott.emp a group by deptno ;-每个部门的最小工资select a.deptno,min(sal) from scott.emp

49、a group by deptno ;-查询原工资占部门工资的比率select deptno ,sal,ratio_to_report(sal) over(partition by deptno) sal_ratio from scott.emp ;-查询成绩不及格的所有学生信息（提示：没有对应的表，只是意思意思。不及格人数大于等于三才能查）select * from scott.emp where empno in(select distinct empno from scott.emp where 3<(select count(sal) from scott.emp where s

50、al<3000) and empno in(select empno from scott.emp where sal<3000);结果：-查询每个部门的平均工资select distinct deptno,avg(sal) from scott.emp group by deptno order by deptno desc;-union组合查出的结果，但要求查出来的数据类型必须相同select sal from scott.emp where sal >=all(select sal from scott.emp ) union select sal from scott

51、.emp ;select * from scott.emp a where a.empno between 7227 and 7369 ;-只能从小到大-创建表空间 要用拥有create tablespace权限的用户，比如syscreate tablespace tbs_dat datafile 'c:oradatatbs_dat.dbf' size 2000M;-添加数据文件alter tablespace tbs_dat add datafile 'c:oradatatbs_dat2.dbf' size 100M;-改变数据文件大小alter databa

52、se datafile 'c:oradatatbs_dat.dbf' resize 250M;-数据文件自动扩展大小alter database datafile 'c:oradatatbs_dat.dbf' autoextend on next 1m maxsize 20m;-修改表空间名称alter tablespace tbs_dat rename to tbs_dat1;-删除表空间 and datafiles 表示同时删除物理文件drop tablespace tbs_dat including contents and datafiles;-substr(s1,s2,s3)：截取s1字符串,从s2开始,结束s3select substr(job,3,length(job)