Oracle高级查询学习笔记

Oracle的高级查询虽然工作中也经常用，但是总感觉对高级查询这块知识的基础不是很好，于是今天就抽了一个周末将慕课网的高级查询课程又刷了一遍，收获颇丰。于是将学习过程记录下来，方便自己以后查阅同时也将学习到的知识和大家分享一下,以下案例中的表结构和数据来自Oracle11G默认的Scott用户。
一、Oracle高级查询之分组函数

--案例 1 查询员工的平均工资和工资总额
select  avg(sal), sum(SAL) from  EMP;
--案例2 求出员工工资的最大值和最小值
select  max(sal), min(SAL) from  EMP;
--案例3 求出员工总人数
select  count(*) from EMP;
--案例4 求出部门数
select count( distinct  deptno ) from EMP;
--案例5  查询每个部门中员工
select deptno 部门号,wm_concat(ename) from emp group by deptno;
-- Oracle高级查询之分组函数3的使用
--案例6 统计员工的平均工资 分组函数会忽略空值 可以使用nvl(num1,num2)解决
--第一种方式
select sum(sal)/count(*) from EMP;
--第二种方式
select sum(sal)/count(sal) from EMP;
--第三种方式
select avg(sal) from EMP;
--案例 7 求每个部门的平均工资,要求显示部门号,部门的平均工资
--(注意:在Select语句中没有包含在分组函数中列都应该包含在group by 子句中不然oracle就会报错,在group by 字句中出现的列不一定要出现在select 子句中)
select deptno,avg(sal) from EMP group by deptno;
--案例8 求出部门的平均工资 要求显示部门的平均工资
select avg(sal) from EMP group by deptno;
--案例9 按部门,不同的职位,统计员工工资的总额
select DEPTNO,JOB,sum(sal) from EMP group by deptno,JOB;
--having 子句的使用 
--案例10 查询平均工资大于2000的部门
select  DEPTNO,avg(SAL) avgsal from  EMP group by  DEPTNO  having  avg(SAL)>2000;
--where 与having的区别
--where 不能用于分组函数 having 可以
--案例 11查询部门号为10的平均工资(where 和having 通用的情况)
-- 使用having 实现
select DEPTNO, avg(sal)
from EMP group by  DEPTNO having  DEPTNO=10;‘
--使用where 实现
select avg(SAL) from  EMP where  DEPTNO=10;
--案例12 求出每个部门的平均工资 ,要求显示部门号，部门的平均工资，并且按照工资升序排列。
select DEPTNO,avg(SAL)
from EMP group by  DEPTNO order by  avg(SAL) asc ;
--案例13 求出部门平均工资的最大值
select max(avg(SAL))
from EMP group by  DEPTNO;
--查询员工表中所有员工,每个部门,以及每个部门下每个职位的总工资(group by 字句的增强)
SELECT  DEPTNO,JOB ,SUM(SAL) FROM  EMP GROUP BY  DEPTNO,JOB+;
select DEPTNO,sum(sal)  from emp group by deptno+;
select sum(sal) from emp=
select deptno,JOB, sum(sal) from emp group by rollup(deptno,JOB);

Oracle高级查询之 多表查询

--案例 1 要求显示员工的员工号,姓名,月薪,部门名称(等值连接)。
select e.EmpNO, e.ENAME,e.SAL,DNAME  from EMP e,DEPT   d where  e.DEPTNO=d.DEPTNO;
--案例 2 要求显示员工的员工号,姓名,月薪,工资级别(不等值连接)。
select e.EmpNO, e.ENAME,e.SAL,s.GRADE  from EMP e,SALGRADE   s where  e.SAL between  s.LOSAL and s.HISAL;
 or 
select e.EmpNO, e.ENAME,e.SAL,s.GRADE  from EMP e,SALGRADE   s where  e.SAL >=  s.LOSAL and  e.SAL <=s.HISAL;
--案例 3 按部门统计员工人数 要求显示 部门号 部门名称 人数(外连接)
--外连接的概念：通过外连接,把对于连接条件不成立的记录仍然包含在结果中。
--左外连接：当连接条件不成立的时候,等于号左边的表中记录仍然被包含
--右外连接：当连接条件不成立的时候,等于号右边的表中记录仍然被包含
select  d.DEPTNO,d.DNAME,count(empno) from EMP e,DEPT d where e.DEPTNO(+)=d.deptno  group by  d.DEPTNO,d.DNAME;
--案例 4 查询员工的姓名以及员工上级的姓名(自连接)
select  e1.ENAME, e2.ename  from emp e1,EMP e2 where e1.MGR=e2.empno(+);
--oracle 自连接存在的问题以及解决办法(层次查询)
select level,empno,ename,sal,mgr from emp connect by prior empno=mgr start with  empno=7839 order by 1;

Oracle高级查询之 子查询

--案例1 查询工资比scott高的员工信息
SELECT *  from emp  where  SAL>(select  SAL from EMP where ENAME='SCOTT');
--案例 2 查询工资高于本部门平均工资的员工信息
select  * from EMP ,(select DEPTNO, avg(SAL)  avgsal from  EMP group by DEPTNO) temp  where EMP.DEPTNO=temp.DEPTNO and EMP.SAL>temp.avgsal;
--案例 3 查询职位与员工号为7566相同并且薪水大于7782的员工信息
select * from emp where job=(select JOB from emp where empno='7566') and sal>(select sal from emp where empno='7782');
--案例 4 查询工资最低的员工信息
select * from emp where sal=(select min(sal) from emp );
--案例 4 查询最低工资大于20号部门的最低工资的部门号和最低工资
select DEPTNO,min(SAL) from  EMP group by  DEPTNO having  min(sal)>(select  min(sal) from  EMP where DEPTNO='20');
--案例 5 查询 部门名称是SALES和ACCOUNTING 的员工信息
SELECT * FROM EMP WHERE DEPTNO IN (select deptno from DEPT where dname='SALES' or dname='ACCOUNTING');
--案例 6 查询 工资比30号部门任意一个员工高的员工信息
select * from emp where sal>any(select sal from emp where deptno='30');
--案例 7 查询工资比30号部门所有员工高的员工信息
select * from emp where sal>all (select sal from emp where deptno='30');
--案例 8 查询不是老板的员工
select * from emp where empno not in(select mgr from emp where mgr is not null);

综合案例

综合案例1 
分页查询 显示员工信息：显示员工号,姓名和月薪(每页显示四条记录,显示第二页员工信息,按照月薪降序排列)
select r,empno,ename ,sal
from (select rownum r,empno,ename ,sal
         from (select rownum,empno,ename,sal from emp order by sal desc)e1
          where rownum<=8) e2
where r>=5;
--综合案例2 找到员工表中薪水大于本部门平均薪水的员工
select empno,ename ,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);
--sql语句的执行计划
explain plan for sql
--查看SQL的执行计划
select * from table(dbms_xplan.display);
--综合案例3  按部门统计员工人数,按照 以下格式方式输入(显示每一个年份入职员工人数和员工总数)
Total       1980       1981        1982        1987
 14           1          10          1           2
select count(*)  total,sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"
from emp;