Oracle Learning Note Five SQL command (c): Group by, sort, connection query, subquery, paging

GROUP BY and HAVING clause GROUP BY clause

Used to divide information into smaller groups
Each group of rows returns a single result for that group

--count the number of people in each department:Select Count(*) fromEmpGroup  byDeptno--according to departmental groupings, and statistics SelectDeptnoCount(*) Form EMPGroup  byDeptno; SelectDeptnoavg(SAL) fromEmpGroup  byDeptno--average salary for each department

HAVING clause

Criteria for specifying the GROUP BY clause to retrieve rows

Select avg  from Group  by  having avg >  - -- identify departments with average wages greater than 2000;

Sorting Data

Order  byColumn Name 1ASC | desc, column 2ASC | desc;--The default is ASC ascendingSelect *  fromEmpOrder  bySal--Sort by salary ascendingSelect *  fromEmpOrder  bySaldesc;--Payroll DescendingSelect *  fromEmpOrder  bySaldesc, HireDateASC;--in descending order of wages, if wages are the same, ascending by date;

of the query statementExecution Order

Where, group BY, have

Filter the data that meets the requirements according to where conditions first,

and GROUP by groups.

Finally, the data after grouping is constrained with having , and The result of select is obtained.

Multi-Table Query

Select  from emp e, Test T;  -- Cartesian set, cross-query

Connection QueryINNER JOIN : INNER JOIN returns common data between multiple tables

Select  from where = Dept.deptno;
Select Inner Join  on =

External connection : Left join: The result of the left join data is the main table, and the right table is empty if it doesn't exist in the left table!

Select S.*from leftjoinon= C.sid

Right Join

Fully connected : Full join

See: Indexman article Oracle Connection query in detail

Sub-query

In one query can contain another query, it can appear anywhere, external queries get results, internal query return conditions

--get the information of the person with the highest salarySelect *  fromEmpwhereSal=(Select Max(SAL) fromEMP);
--Find all the people in the same department as SmithSelectEname fromEmpwhereDeptno=(SelectDeptno fromEmpwhereEname= 'SMITH');
--List employee information in the same department as SmithSelect *  fromEmpwhereDeptno=(SelectDeptno form EMP were ename= 'SMITH');
--and Martin is the same leader of the staffSelect *Form EMPwhereMrg=(SelectMgr fromEmpwhereEname= 'MARTIN');
--List all employees who work in New York--Connection Query method:Select *  fromEmpInner JoinDept onEmp.deptno=Dept.deptnowhereLOC= 'NEW YORK';--Sub-query mode:Select *  fromEmpwhereDeptno=(SelectDeptno fromDeptwhereLOC= 'NEW YORK');
--List all employees who work in New York and Dallas Select *  fromEmpwhereDeptnoinch(SelectDeptno fromDeptwhereLoc= 'NEW YORK' orLoc= 'DALLAS'); Select *  fromEmpwhereDeptno=  any(SelectDeptno fromDeptwhereLoc= 'NEW YORK' orLoc= 'DALLAS');

Paging of data

--Query the first 5 articles    SelectRowNum, EMP.*  fromEmpwhereRowNum<=5;--Query the data after article 6th    Select *  fromEmpwhereEmpno not inch(SelectEmpno fromEmpwhereRowNum<=5);--Querying 第6-10条 Data0Select *  from(Select *  fromEmpwhereEmpno not inch(SelectEmpno fromEmpwhereRowNum<=5))whereRowNum<=5; 0Select *  fromEmpwhereEmpno not inch(SelectEmpno fromEmpwhereRowNum<=5) andRowNum<= 5;--Summary0Select *  from(Select *  fromEmpwhereEmpno not inch(SelectEmpno fromEmpwhereRowNum<=Size*(page- 1)))whereRowNum<=size; 0Select *  fromEmpwhereEmpno not inch(SelectEmpno fromEmpwhereRowNum<=Size*(page- 1)) andRowNum<=size; --sorting and then paging query    --number of data after sorting (Efficiency row 2nd)    Select *  from(SelectRowNum RN, D.*  from(Select *  fromEmpOrder  bySaldesc) d)whereRn> 5  andRn<  One; --Paging function: row_number function (1th efficiency row)    SelectRow_number () Over(Order  bySaldesc) RN, EMP.*  fromEMP; --page Out    Select *  from(SelectRow_number () Over(Order  bySaldesc) RN, EMP.*  fromEmpwhereRn> 5  andRn<  One;

Oracle Learning Note Five SQL command (c): Group by, sort, connection query, subquery, paging