oracle常用sql查询语句部分集合
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<=3order by deptno asc,sal desc;
结果：
--rank()分析函数(运行结果与上语句相同)
select*from(select deptno,ename,sal,rank()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<=3order by deptno asc,sal desc;
--rows 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 rows3 preceding)每4汇总值from scott.emp ;
结果：
--rows between 1 preceding and 1 following（统计3条记录的汇总值【当前记录居中】）
select deptno,ename,sal,sum(sal)over(order by deptno rows between1preceding and1following)汇总值from scott.emp ; 结果：
--ratio_to_report(显示员工工资及占该部门总工资的比例) select deptno,sal,ratio_to_report(sal)over(partition by deptno)比例from scott.emp ;
结果：
--查看所有用户
select*from dba_users ;
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*from dba_role_privs ;
select*from user_role_privs ;
-- rownum:查询10至12信息
select*from scott.emp a where rownum<=3and 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.empno=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 order 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<10and rk>=1;
结果：
--rownum分页(一万条数据，查询10000至9980时间大概在0.01秒左右)
select*from(select t.*,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.deptno ;
--right outer join:右连接
select a.*,b.*from scott.emp a right outer join scott.dept b on a.deptno=b.deptno ;
--inner join
select 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.*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 when10then'会计部'when20 then'研究部'when30then'销售部'else'其他部门'end部门from scott.emp ;
结果：
select ename,sal,case when sal>0and sal<1500then'一级工资'when sal>=1500and sal<3000then'二级工资'when
sal>=3000and sal<4500then'三级工资'else'四级工资'end 工资等级from scott.emp order by sal desc;
结果：
--交叉报表是使用分组函数与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学生group 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 lpad(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.*from 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=10or sal>2500);
结果：
--非等值连接
select a.ename,a.sal,b.grade from scott.emp
a,scott.salgrade b where a.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.deptno and scott.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.sal>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');
结果：
--单行子查询在 from 后
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 from 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.emp 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)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 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,job)=(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 ename,sal,deptno from scott.emp where deptno>10union 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>10union all select ename,sal,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 dname 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)/3from 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.deptno=scott.dept.deptno group by dname)select dname as部门,sumsal as工资总和from scott.test where sumsal>(select sum(sumsal)/3from 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 sal rows5preceding) 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 deptno 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 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 sal)上一个,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_value()
select deptno,sal,first_value(sal)over(partition by deptno)最前,last_value(sal)over(partition by deptno )最后from scott.emp ;
结果：
--分组补充 group by grouping sets
select 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 all select deptno,null,sum(sal)from scott.emp group by deptno ;
结果：
--rollup
select 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 null,null,avg(sal)from scott.emp ;
结果：
select deptno,job,avg(sal) a from scott.emp group by
cube(deptno,job);
--理解CUBE
select 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之间的所有雇员名及工资
select ename,sal from scott.emp where sal not in(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)and a.sal>1500order by sal desc;
结果：
--在1981年2月1日至1981年5月1日之间雇佣的雇员名，岗位及雇佣日期，并以雇佣日期先后顺序排序
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','yyyy-MM-dd');
--查询获得补助的所有雇佣名，工资及补助额，并以工资和补助的降序排序
select ename,sal,comm from scott.emp a where https://www.wendangku.net/doc/d118722938.html,m >all(0) order by comm desc;
--工资低于1500的员工增加10%的工资，工资在1500及以上的增加5%的工资并按工资高低排序（降序）
select ename as员工姓名,sal as补发前的工资,case when
sal<1500then(sal+sal*0.1)else(sal+sal*0.05)end补助后的工资from scott.emp order by sal desc;
结果：
--查询公司每天，每月，每季度，每年的资金支出数额
select sum(sal/30)as每天发的工资,sum(sal)as每月发的工资,sum(sal)*3as每季度发的工资,sum(sal)*12as每年发的工资from scott.emp;
结果：
--查询所有员工的平均工资，总计工资，最高工资和最低工资select avg(sal)as平均工资,sum(sal)as总计工资,max(sal) as最高工资,min(sal)as最低工资from scott.emp;
--每种岗位的雇员总数和平均工资
select job 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(sal)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>2and
a.job=
b.job;