Java39: Database Three (Oracle)

Oracle Advanced Queries

1 set operation

Union combining two tables to form a single table

Intersect the intersection of the same leave, not the same don't

Minus minus the previous result minus the result of the following

CREATE TABLE Emp3 as SELECT * from EMP2 where deptno=20;create table Emp4 as SELECT * from EMP2 where Deptno=30;alter tabl E 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 * to EMP2 minus select * from Emp30;

2 connect by and start with

Depending on the syntax, we can list a table-shaped structure in tree order

Provides a pseudo-column level

Level

Find the head 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 are null) connect by prior EM Pno=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 functions

Subtotal, Rollup function

Rollup function multiple Line subtotal

For grouped columns, NULL

For subtotals for aggregate functions

Select Job,sum (SAL) from the EMP GROUP by Rollup (Job), select Job,sum (SAL), round (avg (SAL)), Max (SAL), Count (empno) from EMP GROUP by rollup (job);

Statistics Rollup (x, Y) statistics the first x does not count y

Select Dname,job,sum (SAL) from the EMP INNER JOIN dept using (DEPTNO) GROUP by Rollup (Dname,job);

Cube counts all the columns

Select Dname,job,sum (SAL) from the EMP INNER JOIN dept using (DEPTNO) Froup by Cube (dname,job) order by Dname;

Rollup and cube are aggregated results.

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) 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) O Rder by Dname;

Query only the totals of the Total row totals also do not

Select Dname,job,sum (SAL) from the EMP INNER JOIN dept using (DEPTNO) Group by grouping sets (Dname,job);

If you need to query results only subtotals can be used with cube and grouping sets with grouping sets efficiency is higher than cube and rollup

Ranking function

Can be sorted and ranked

Rank () Over (Orader by XX)

Repeat, the next number will be trimmed.

Select Rank () over (order by Sal Desc), ename,sal from EMP;

Dense_rank () Over (Orader by XX)

Repeat without clipping number

Select Dense_rank () over (order by Sal Desc), ename,sal from EMP;

Sort first in rownum than rownum simple

Select Row_number () over (order by Sal Desc), ename from EMP;

This article is from the "Romantic Laugh" blog, make sure to keep this source http://lmdtx.blog.51cto.com/6942028/1836807

Java39: Database Three (Oracle)