Java39: 数据库三(Oracle)

wstlwl

　　Oracle 高级查询
　　1 集合运算
　　union 并集 把两张表合成一张表
　　intersect 交集 一样的留下，不一样的不要
　　minus 减去 前面的结果减去后面的结果
create table emp3 as　　
select * from emp2 where deptno=20;
　　
create table emp4 as
　　
select * from emp2 where deptno=30;
　　

　　
alter table emp3 rename to emp20;
　　
alter table emp4 rename to emp30;
　　

　　
select * from emp20;
　　
select * from emp30;
　　

　　
select * from emp20 union select * from emp30;
　　

　　
select * from emp30 intersect select * from emp2;
　　

　　
select * from emp2 minus select * from emp30;
　　2 connect by 和start with
　　依托于该语法，我们可以将一个表形结构的以树的顺序列出来
　　提供一个伪列 level
　　level
找到头　　
select ename from emp2 where mgr is null;
　　
select empno,ename,mgr from emp start with ename='KING' connect by prior empno=mgr;
　　

　　
select level,empno,ename,mgr from emp start with ename=(select ename from emp2 where mgr is null) connect by prior empno=mgr;
　　

　　

　　
select * from (select level lv,empno,ename,mgr from emp start with ename=(select ename from emp2 where mgr is null) connect by prior empno=mgr) where lv=2;
　　3高级分组函数
　　rollup 函数 小计
　　rollup 函数多一行 小计
　　对于分组的列为null
　　对于聚集函数为求 小计
select job,sum(sal) from emp GROUP BY rollup(job);　　

　　
select job,sum(sal),round(avg(sal)),max(sal),count(empno)from emp group by rollup(job);
　　统计rollup(x,y) 统计第一个x 不统计y
select dname,job,sum(sal) from emp inner join dept using(deptno) group by rollup(dname,job);　　cube 统计所有的列
select dname,job,sum(sal) from emp inner join dept using(deptno) froup by cube(dname,job) order by dname;　　rollup 和cube 就是汇总的结果
　　grouping 和grouping sets
select grouping(dname),dname ,grouping(job),job,sum(sal) from emp inner join dept using(deptno) group by rollup(dname,job) order by dname;select * from (select grouping(dname),dname ,grouping(job) N,job,sum(sal) from emp inner join dept using(deptno) group by rollup(dname,job) order by dname) where dname='SALES' and N=1 ;select grouping(dname),dname,grouping(job),job,sum(sal) from emp inner join dept using(deptno) group by cube(dname,job) order by dname;　　只查询汇总的行 总的汇总也不要
select dname,job,sum(sal) from emp inner join dept using(deptno) group by grouping sets(dname,job);　　如果需要查询的结果只有小计 可以用cube 和 grouping sets 用grouping sets 的效率高于cube 和rollup
　　排名函数
　　既能排序又能排名
　　rank() over( orader by xx)
　　重复就会削去下一个编号
select rank() over(order by sal desc),ename,sal from emp;　　dense_rank() over(orader by xx)
　　重复不削去编号
select dense_rank() over(order by sal desc),ename,sal from emp;　　先排序在rownum  比rownum 简单
select row_number() over(order by sal desc),ename from emp;