Java39: database 3 (Oracle)

Oracle Advanced query

1 set operation

Union union combines two tables into one table

Intersect intersects the same to stay, different do not

Minus minus the previous result minus the later result

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 and start with

Depending on this syntax, we can list a tabular structure in tree order.

Provide a pseudo column level

Level

Find the header 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 advanced grouping function

Rollup function subtotal

Rollup function multiple line subtotal

For grouped columns null

For aggregate functions, it is a subtotal.

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)

Statistics rollup (xQuery y) Statistics the first x does not count y

Select dname,job,sum (sal) from emp inner join dept using (deptno) group by rollup (dname,job)

Cube counts all columns

Select dname,job,sum (sal) from emp inner join dept using (deptno) froup by cube (dname,job) order by dname

Rollup and cube are the results of the summary.

Grouping and 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) Select grouping (dname), dname,grouping (job), job,sum (sal) from emp inner join dept using (deptno) group by cube (dname,job) order by dname

Query only the summary of the rows and not the total summary

Select dname,job,sum (sal) from emp inner join dept using (deptno) group by grouping sets (dname,job)

If you need query results, only subtotals can be used. Cube and grouping sets are more efficient than cube and rollup with grouping sets.

Ranking function

Can not only sort but also rank

Rank () over (orader by xx)

Repetition will cut off the next number.

Select rank () over (order by sal desc), ename,sal from emp

Dense_rank () over (orader by xx)

Repeat without pruning the number

Select dense_rank () over (order by sal desc), ename,sal from emp

Sorting first is easier in rownum than in rownum

Select row_number () over (order by sal desc), ename from emp