数据库开发基础-Oracle-SQL基础-006

--Oracle开发中常用sql

--子查询
嵌套在其他sql中的一条查询语句，该查询语句就是子查询。
当我们执行的sql语句需要先从数据库中获取一些数据才能
运行时，那么先期执行的这条sql就是子查询，是为了给要
实际执行的sql提供数据的。

查看工资高于CLARK的员工？

select ename, sal
  from emp
 where sal > (select sal from emp where ename = 'CLARK');

查看与ALLEN同职位的员工？

select * from emp where job = (select job from emp where ename = 'ALLEN');


子查询常用于查询语句，但是亦可以在DDL,dml中使用。

DDL中使用，例如：

基于一个查询结果集快速创建一张表。
create table myemp 
as
select e.empno,e.ename,e.sal,e.job,
e.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno =d.deptno;


desc myemp;

select * from myemp;

dml中使用子查询
删除CLARK所在部门的所有员工
delete from myemp
 where deptno = (select deptno from myemp where ename = 'CLARK');

子查询根据查询结果分为：
单行单列子查询，多行单列子查询，
多行多列子查询

其中单列的子查询多用在 where 子句中做过滤条件。
而多行多列子查询通常用于 from 中当做表看待。

select ename,job from emp;


查看谁比公司平均工资高？

select ename, sal from emp where sal > (select avg(sal) from emp);

查看与职位是“SALESMAN”相同部门的
其他职位员工？

select ename, job, deptno
  from emp
 where deptno in (select deptno from emp where job = 'SALESMAN')
   and job <> 'SALESMAN';

查看比20与30号部门工资高的员工信息？

select *
  from emp
 where sal > all (select sal from emp where deptno in (20, 30));

--等价
select *
  from emp
 where sal > (select max(sal) from emp where deptno in (20, 30))



exists关键字
exists是用于where中作为判断条件使用的
其后需要紧跟一个子查询，只要该子查询能查询出
至少一条记录，那么exists表达式就返回真，
也可以使用 not exists 来表达相反的效果。

查看所以员工的部门信息

select deptno, dname
  from dept d
 where exists (select * from emp e where e.deptno = d.deptno);

查看每个部门的最低薪水，前提是
该部门的最低薪水要高于30号部门的最低薪水？

select deptno, min(sal)
  from emp
 group by deptno
having min(sal) > (select min(sal) from emp where deptno = 30);

查看谁比自己所在部门平均工资高？

select e.ename,e.sal,e.deptno
  from emp e, (select avg(sal) avg_sal, deptno from emp group by deptno) s
 where e.deptno = s.deptno
   and e.sal > s.avg_sal;


子查询也可以应用在select子句中
可以实现外连接的效果

查看每个员工以及所在部门名称信息：

select e.ename, (select d.dname from dept d where d.deptno = e.deptno)
  from emp e;

--分页查询
当查询的数据量过度时，会导致一些情况：
1.服务端响应慢
2.系统资源占用多
3.数据过剩

为了解决这些问题，通常我们会分批查询数据
这个过程就是分页查询。
由于分页查询没有标准的sql语法，所以不同
数据库对于分页的机制不一致（方言）

select rownum,ename, sal, job, deptno from emp;

Oracle支持一个关键字rownum
rownum是一个伪列，该列不存在于
任何一张表中，但是每张表都可以查询该列。
而该列在结果集的值是结果集中每条记录的“行号”
rownum给结果集编号是在查询的过程中进行的，只要可以
从表中查询出一条记录，该记录的行号就会作为这条记录
rownum字段的值，rownum从1开始递增。
由于rownum从1开始，所以在第一次查询表中数据进行编号时。
不要使用rownum做大于1以上的数字判断，否则查询不到数据：

select rownum,ename,sal from emp where rownum >1;

select *
  from (select rownum rn, ename, sal from emp)
 where rn between 5 and 10;


查看公司工资排名的第6-10名
select *
  from (select rownum rn, t.*
          from (select ename, sal from emp order by sal desc) t)
 where rn between 6 and 10;


下面的效率好，不需要的数据就不编号了

select * 
from( select rownum rn,t.*
      from (select ename,sal from emp order by sal desc) t
       where rownum <=10
      )    
where rn >= 6;

计算分页公式:
页数：page
每页显示的条数：pageSize

start = (page-1)*pageSize+1--[当前页之前条数+1]
end = pageSize*page


一页显示5条，显示第2页

decode 函数，可以实现类似java中的分支操作：

select ename,
       job,
       sal,
       decode(job,
              'MANAGER',
              sal * 1.2,
              'ANALYST',
              sal * 1.2,
              'SALESMAN',
              sal * 1.05,
              sal) bonus
  from emp;

select count(*), job from emp group by job;

在group by中使用decode可以做到
将字段值不同的记录看做一个大组，只要
将需要看做一组的记录中该字段的值替换为相同的值即可。

select decode(
        job,
        'MANAGER','VIP',
        'ANALYST','VIP',
        'OTHER'
),count(*)

from emp 
group by decode(
        job,
        'MANAGER','VIP',
        'ANALYST','VIP',
        'OTHER'
);

select deptno,dname,loc
from dept
order by decode(
         dname,
         'OPERATIONS',1,
         'ACCOUNTING',2,
         'SALES',3
         );
         
         
排序函数：
排序函数可以按照指定的字段分组，然后再按照
指定的字段排序，最后为记录生成组内的编号。
1：row_number函数，生成组内连续且唯一的数字。--[独立编号]

查看每个部门中工资的排名：

select ename,
       sal,
       deptno,
       row_number() over(partition by deptno order by sal desc) rank

  from emp;

2:rank函数:生成组内不连续不唯一的数字，--[跳跃并列]

select 
    ename,sal,deptno,
    rank() over(
      partition by deptno
      order by sal desc
    ) rank
from emp;

3:dense_rank()生成组内连续但不唯一的数字--[不跳跃并列]

select ename,
       sal,
       deptno,
       dense_rank() over(partition by deptno order by sal desc) rank

  from emp;


--集合运算
select ename,job,sal
from emp where job = 'MANAGER'
--union
--intersect 
minus
select ename,job,sal
 from emp where sal >2500;


--下面的是创建一千行数据的表

CREATE TABLE sales_tab (

  year_id   NUMBER NOT NULL,

  month_id   NUMBER NOT NULL,

  day_id   NUMBER NOT NULL,

  sales_value NUMBER(10,2) NOT NULL

);

INSERT INTO sales_tab

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_tab
ORDER BY year_id,month_id,day_id


 查看每天的营业额？
 
 select year_id, month_id, day_id, sum(sales_value)
   from sales_tab
  group by year_id, month_id, day_id
  order by year_id, month_id, day_id;

查看每月的营业额？

select year_id, month_id, sum(sales_value)

  from sales_tab
 group by year_id, month_id
 order by year_id, month_id;

查看每年的营业额？

select year_id, sum(sales_value)
  from sales_tab
 group by year_id
 order by year_id;

查看全部的营业额？


select sum(sales_value) from sales_tab;

--高级分组函数

group by rollup (a,b,c)
等价于：

group by a,b,c
union all
group by a,b
union all
group by a
union all
全表

查看每天，每月，每年，以及全部
营业额的统计？

select year_id, month_id, day_id, sum(sales_value)
  from sales_tab
 group by rollup(year_id, month_id, day_id)
 order by year_id, month_id, day_id;


cube函数：
cube会将每个参数的不同组合进行分组
然后将所有分组统计结果并在一起。
分组次数是2的参数个数次方。

group by cube(a,b,c)
abc,
ab,
ac,
bc,
a,
b,
c
全表

select year_id, month_id, day_id, sum(sales_value)

  from sales_tab
 group by cube(year_id, month_id, day_id)
 order by year_id, month_id, day_id;

grouping sets()
该函数可以按照指定的分组方式进行分组，
然后将结果集并在一起。
其中每一个参数就是一种组合方式。

查看每天以及每月的营业额？

select year_id, month_id, day_id, sum(sales_value)
  from sales_tab
 group by grouping sets((year_id, month_id, day_id),(year_id, month_id))
 order by year_id, month_id, day_id;