004

--子查询
--子查询也是一条查询语句，只是它嵌套在其他sql语句中
--作用是为外层的sql提供数据
--查看比CLARK工资高的员工
select ename sal from emp_coco
where sal>(select sal from emp_coco where ename='SMITH')
SELECT * FROM EMP_COCO
--和SMITH部门相同的员工                    
select ename,JOB from emp_coco
where job=(select job from emp_coco where ename='SMITH')
--查看比公司平均工资高的员工
select ename,sal from emp_coco
where sal>(select avg(sal) from emp_coco)
--DDL中使用子查询
--基于一个子查询的结果集快速创建一张表
create table emp_cococo
as
select e.empno,e.ename,e.job,e.deptno,d.dname,d.loc
from emp_coco e,dept_coco d
where e.deptno=d.deptno(+)
DROP TABLE EMP_COCOCO
select * from emp_coco

--DML使用子查询
--将SMITH 部门所有员工工资提高%10：
update emp_coco
set sal=sal*1.1
where deptno=(select deptno from emp_coco
              where ename='SMITH')
--查看与职位是SALESMAN相同部门的.其他.职位员工？
select ename,job,deptno from emp_coco
where deptno in (select deptno from emp_coco
                where job='SALESMAN')AND job<>'SALESMAN'
--查看比职位时CLERK 和 SALESMAN工资都高的员工，可用all或 max
select ename,sal from emp_coco
where sal>ALL((select sal from emp_coco
          where job IN('CLERK','SALESMAN')))
--EXISTS关键字
--EXISTS后面要跟一个子查询，当该子查询可以至少查询出一条记录时
--EXISTS返回真，NOT EXISTS这是相反的操作
select deptno,dname from dept_coco d
where exists (
              select * from emp_coco e
              where d.deptno=e.deptno)
--HAVING中使用子查询
--查看最低薪水高于30号部门最低薪水的部门
select deptno,min(sal) from emp_coco
group by deptno
having min(sal)>(select min(sal) from emp_coco
                 where deptno=30)
--单列作为条件
--多列子查询常被当作一张表看待而出现在FROM子句中
--查看高于自己部门平均工资的员工
--select avg(sal),deptno --不指名 报错
--from  emp_coco
--group by deptno （当作一张表看待）
--子查询中的字段若是函数或者表达式那么必须给别名
select ename,sal,e.deptno
from emp_coco e,(select avg(sal) avg_sal,deptno --指定别名
                 from  emp_coco
                 group by deptno) t
where e.deptno=t.deptno and e.sal>t.avg_sal

--SELECT 子句中也可以使用子查询
select e.ename,e.sal,
       (select d.dname from dept_coco d
       where d.deptno=e.deptno) dname
from emp_coco e  --显示null

select e.ename,d.dname from dept_coco d,emp_coco e
where d.deptno=e.deptno--不显示null

select e.ename,d.dname
from emp_coco e
left outer join 
dept_coco d
on e.deptno=d.deptno
--分页查询
--分页查询是将查询的数据分段显示，这样做的目的可以减少资源占用，提高响应速度
--分页再不同的数据库中的 SQL语句是不一样的（方言）
--ORACLE 中的分页是依靠伪列：ROWNUM实现的
--ROWNUM不存在与任何表中，但是任何表都可以查询该列，该列的值是随着查询数据的过程中生成的
--只要可以从查询出一条记录，那么 ROWNUM字段的值就是该记录的行号，从1开始 divlebo

--在使用ROWNUM 对结果集进行编号的过程中不要使用rownum做>1以上的过滤判断，
--否则结果集将得不到任何结果

select * from(
             select rownum r,ename,sal,deptno from emp_coco)
where r between 6and 10          
--由于排序在查询语句中的执行顺序是最低的，所以，在分页中若有排序需求时
--应当先排序，然后再根据排序的结果进行分页查询

select * 
from( select rownum r,t.* 
from(
      select ename,sal from emp_coco
      order by sal desc) t
    where rownum<=10)--加上次句效率高，不写r，因为where 比select先执行
where r between 6 and 10--若不写r，会默认为最外层
--根据页数以及每页显示的条目数来求范围的公式：
--pageSize:5 每页显示的条目数
--page：1    页数
start=(page-1)*pageSize+1
end=pageSize*page

--DECODE函数
--decode函数可以实现分支的效果
select ename,sal,job,
       decode(job,
              'MANAGER',sal*1.2,
              'ANALYST',SAL*1.1,
              'SALESMAN',SAL*1.05,
              SAL)bonus
from emp_coco             
--和decode 功能相似的有case语句 类似与if else的操作
select ename,job,sal,
        case job when 'MANAGER' THEN SAL*1.2
                 WHEN 'ANALYST' THEN SAL*1.1
                 WHEN 'SALESMAN' THEN SAL*1.05
                 ELSE SAL END
                 bonus
from emp_coco                 
--可以利用DECODE将字段值不一样的记录看做同一组，
--只需要将看做一组的记录该字段值替换为一个相同值即可
select count(*),decode(job,
                'MANAGER','VIP',
                'ANALYST','VIP',
                'OTHER') D
from emp_coco
group by decode(job,
                'MANAGER','VIP',
                'ANALYST','VIP',
                'OTHER')
select deptno,dname,loc from dept_coco
order by
        decode(dname,
               'OPERATIONS',1,
               'ACCOUNTING',2,
               'ASLES',3)--没写的为null 最大
--排序函数               
--排序函数允许对结果集按照给定的字段分组，然后在组内按照指定的字段排序
--然后生成一个组内编号

select ename,sal,deptno,
       row_number() over(
       partition by deptno
       order by sal desc
       )rank
from emp_coco
--RANK:生成组内不连续且不唯一的数字
select ename,sal,deptno,
      rank() over(
       partition by deptno
       order by sal desc
       )rank
from emp_coco
--DENSE_RANK:生成组内连续但不唯一的数字
select ename,sal,deptno,
      dense_rank() over(
        partition by deptno
        order by sal desc
        )rank
from emp_coco        

--UNION
select ename,job,sal from emp_coco
where job='MANAGER'
union all
select ename,job,sal from emp_coco
where sal>=2500
--INTERSECT 交集
select ename,job,sal from emp_coco
where job='MANAGER'
intersect
select ename,job,sal from emp_coco
where sal>=2500
--MINUS 差集 上面有，下没有，也就是结果集1减去结果集2的结果
--职位是MANAGER 但薪水低于2500的员工记录
select ename,job,sal from emp_coco
where job='MANAGER'
minus
select ename,job,sal from emp_coco
where sal>=2500

--高级分组函数
--高级分组函数可以按照该函数要求的分组方式对数据进行分组统计，
--然后将这些方式统计的结果并到一个结果集中显示
--相比我们按照不同分组方式分别统计结果后再使用UNION ALL并在一起，书写起来要简便的多
create table sales_coco(
year_id number not null,
month_id number not null,
day_id number not null,
sales_value number(10,2) not null          
)
--插入1000条数据
insert into sales_coco
select trunc(dbms_random.value(2010,2012))as year_id,
       trunc(dbms_random.value(1,13))as month_id,
       trunc(dbms_random.value(1,32))as day_id,
       round(dbms_random.value(1,100),2)as sales_value
       from dual
       connect by level<=1000
select * from sales_coco
--ROLLUP
select year_id,month_id,day_id,sum(sales_value) from sales_coco
group by rollup(year_id,month_id,day_id)
order by year_id,month_id,day_id
--CUBE（a,b,c) 全方位统计
select year_id,month_id,day_id,sum(sales_value) from sales_coco
group by cube(year_id,month_id,day_id)
order by year_id,month_id,day_id
--GROUPING SETS()
--每个参数是一种分组方式，然后将这些分组统计的结果并在一个结果集显示
--由于分组方式可以通过参数传入，所以相比ROLLUP，CUBE的内定分组方式要灵活
select year_id,month_id,day_id,sum(sales_value) from sales_coco
group by grouping sets(
            (year_id,month_id),
            (year_id,month_id,day_id))
order by year_id,month_id,day_id