Oracle高级查询

笔记时间：2016年11月1日

Oracle内事问百度外事问谷歌

查询

分组查询概述:
    分组函数作用于一组数据,并对一组数据返回一个值.

    常用的分组函数:
        avg         平均值
        sum         求和
        min         最小值
        max         最大值
        count       求个数
        distinct    去重
        wm_count    行专列
            select deptno 部门号,wm_concat(ename) 部门中员工的姓名 from emp group by deptno

分组函数的使用:
    分组函数会自动忽略空值:
        统计没有空值字段
        select avg(asl) avg平均数,sum(asl)/count(*) 除以总统计数,sum(asl)/count(asl) 除以部门统计数 from emp;

        统计有空值字段
        select avg(comm) AVG平均数,sum(comm)/count(*) 除以总统计数,sum(comm)/count(comm) 除以部门统计数 from emp;

        等值不同
        select count(*),count(comm) from emp;

    NVL函数使分组函数无法忽略空值: nvl(xx,xxx)函数当第一个参数为空时会替换第二个参数.
        select count(*),count(nvl(comm,0)) from emp; 

使用group by 子句数据分组:

    select deptno,avg(sal) from emp group by deptno;

    select avg(sal) from emp group by deptno;

    注意除主函数外其他字段必须放到group by后面:
    //错误
    select column1,column2,column3,avg(column4) from table_name group by column1;

    //正确
    select column1,column2,column3,avg(column4) from table_name group by column1,column2,column3;

    使用多个列分组:
        select deptno,job,sum(sal) from emp group by deptno,job;

    分组函数的嵌套:
        select max(avg(sal)) from emp group by deptno;


使用having子句过滤分组结果集:

    select deptno,avg(sal) from emp group by deptno baving avg(sal) >2000;

    where与having的区别:
    where后不可使用函数,having后可使用函数.

在分组查询中使用order by子句:
    排序默认升序  desc降序

group by子句的增强:
    select deptno,job,sum(sal) from emp group by rollup (deptno,job);
    效果等同于:
    select deptno,obj,sum(sal) from emp group by deptno,job;
    +
    select deptno,sum(sal) from emp group by deptno;
    +
    select sum(sal) from emp group by deptno;

多表查询:

等值连接:(两表数值相等)
    select e.empno,e.ename,e.sal,d.dname from emp e,deptno d where e.deptno = d.deptno;

不等值连接:(非等值连接就是范围连接)
    select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal betwenn s.losal and s.hisal;

外链接:
    方法一: left(左连接)|right(右连接) join table_name on
    select d.deptno,d.dname,count(e.empno) from emp e left(左连接)|right(右连接) join dept d on e.deptno = d.deptno ;

    方法二: (+)
    select d.deptno,d.dname,count(e.empno) from emp e,dept d where e.deptno(+左连接) = d.deptno(+右连接) group by d.deptno,d.dname;

自连接:
    方法一:
    select e.ename 员工名,em.ename 老板名 from emp e left join emp em on e.empno = em.mgr;

    方法二:
    select e.ename 员工名,em.ename 老板名 from emp e,emp em where e.empno = em.mgr(+);


层次查询:
    层次查询说明:connect by 
    子查询是操作多表,会产生笛卡尔乘积不适合操作数据较多的表,展示效果强可以双重复字段。
    层次查询是操作单表不会产生笛卡尔积,不可有重复字段。


    老板号是空的树查询:
    select level,empno,ename,sal,mgr from emp connect by prior empno = mgr start with mgr is null order by 1;

    老板号是某一个数值查询:
    select level,empno,ename,sal,mgr from emp connect by prior empno = mgr start with empno = 7839 order by 1;

多表查询:

子查询的使用:查询工资比scott用户高的用户
select * from emp where sal > (
    select sal from emp where ename = 'SCOTT'
);

子查询可以使用的位置:where、select、having、from
子查询不可以使用的位置:group by 后面

子查询的类型:

单行子查询: > >= < <= = <> 
    //两个条件都是单行查询
    //查询员工号为7566同部门员工
    //同部门员工薪资大于员工号为7782的员工 
    select * from emp where job = (
        select job from emp where empno = 7566
    ) and sal > (
        select sal from emp where empno = 7782
    );

单行子查询null值问题不会报错,肯定没有数据.

多行子查询:
    in    等于列表中的任何一个
    select * from emp where deptno in (
        select deptno from dept where dname = 'SALES' or dname = 'ACCOUNTING'
    );

    //多表关联结果同上
    select * from emp e left join dept d on e.deptno = d.deptno where d.dname = 'SALES' or d.dname = 'ACCOUNTING';

    any 和子查询返回的任意 等同于min
    select * from emp where sal > any (
        select sal from emp where deptno = 30
    );

    //min方式
    select * from emp where sal > (
        select min(sal) from emp where deptno =30
    );

    all 和子查询返回的所有值比较 等同于max
    select * from emp where sal > all (
        select sal from emp where deptno = 30
    );

    //max方式
    select * from emp where sal > (
        select max(sal) from emp where deptno = 30 
    );

分页查询:
    select * from (
        select A.*,rownum ro from (
            select * from emp
        ) A where rownum <= 10
    ) where ro >=0;

sql语句执行计划:
explain plan for
sql语句
select * from table(dbms_xplan.display);

select count(*) dateall,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

select 
    (select count(*) from emp) dateall,
    (select count(*) from emp where  '1980' = to_char(hiredate,'yyyy')) "1980年",
    (select count(*) from emp where to_char(hiredate,'yyyy') = '1981') "1981年",
    (select count(*) from emp where to_char(hiredate,'yyyy') = '1982') "1982年",
    (select count(*) from emp where to_char(hiredate,'yyyy') = '1987') "1987年" 
from dual ;