SQL (advanced query)

1. Sub-query in the WHERE clause
In a select query, the constraints in the where query condition are not a deterministic value, but are the result of another query
The query statement executed first to provide data to the query is called a subquery
A subquery is embedded in a SELECT statement in another SQL statement, most of which appears in the WHERE clause
A subquery-embedded statement is called a primary query or a parent query
The main query can be a SELECT statement or another type of statement such as a DML or DDL statement
Sub-query can be divided into single-row subquery, multiline subquery and Dolez query according to the result of the return.
2. Sub-query in the WHERE clause (cont. 1)
If the subquery returns multiple rows, the multiline comparison operator is used in the main query
Multi-line comparison operators include in, all, any.
3. Sub-query in the WHERE clause (cont. 4)
You need to reference the field data in the subquery to the main query, using the EXISTS keyword
Exiists the subquery returns at least one row of data, the entire condition returns true
4. Sub-query in the HAVING clause
5. Sub-query in the FROM section
The FROM clause is used to specify the table to query
If you want to continue the query in the results of a subquery, the subquery appears in the FROM clause, which is also called an inline view or an anonymous view.
The handle query is treated as a view, but the view has no name and is valid only in the current SQL statement
6. Sub-query in the Select section
The handle query is placed in the SELECT clause section, which can be considered another form of monetization for outer joins, using a more flexible
7.ROWNUM
Called a pseudo-column that returns a number that identifies the order of row data
Can only be counted from 1 and cannot be intercepted directly from the result set
Using rownum to intercept part of the data in the result set, you need to use the inline view
8. Paging using subqueries
Paging policy: Only one page of data is taken at a time. Each time a page is changed, one page of data is removed.
The ability to use RowNum in Oracle for paging
Assume a total of 105 results, each 20 is one page:
Page1:1 to 20
Page2:21 to 40
...
Pagen: (n-1) *pagesize+1 to N*pagesize
Total 6 page
9. Paging and ORDER BY
10.DECODE function Basic Syntax
DECODE (Expr,search1,result1[,search2,result2 ...] [, Default]
The decode is used to compare the value of the parameter expr, and if it matches which search condition, it returns the result of the corresponding result
There can be multiple sets of search and result correspondence, and if any one of the search conditions does not match, the value of the last default is returned
The default parameter is optional, and if no default parameter value is supplied, NULL is returned when no match is reached.
Similar to the Decode function, there is a case statement that implements a if-else-like operation.
When the case job is ' MANAGER ' then sal*1.2
When ' ANALYST ' then sal*1.1
ELSE Sal END
Example 1:
SELECT DECODE (Job,
' ANALYST ', ' VIP ',
' MANAGER ', ' VIP ',
' Operation ') job,
COUNT (1) job_cnt
From EMP
GROUP by DECODE (Job,
' ANALYST ', ' VIP ',
' MANAGER ', ' VIP ',
' Operation ');
Example 2:
SELECT Deptno,dname,loc
From Dept
Order by DECODE (dname, ' OPERATIONS ', 1, ' ACCOUNTING ', 2, ' SALES ', 3); (Free definition collation)
11.row_number (sort function)
Row_number () over (PARTITION by col1 ORDER by col2)
Indicates the sort according to col2 within the grouping according to the col1 grouping
The value computed by this function represents the sequential numbering of each set of internally ordered, continuous and unique within the group
RowNum is a pseudo-column, Row_number is more powerful and can take a subset directly from the result set
For example:
Grouped by department code, sorted by employee code within each group, and assigned to group encoding
SELECT Deptno,ename,empno,row_number ()
Over (PARTITION by Deptno ORDER by Empno) as emp_id
From EMP
12.RANK
RANK () over (PARTITION by col1 ORDER by col2)
Indicates the level identification according to col2 within the grouping according to the col1 group
Rank identification is ranked, the same data returns the same rank
Jump sort, if there is the same data, that is ranked the same, such as the second side, two rows of data are marked as 2, but the next one will be the fourth place
The difference with row_number is that there are duplicate values, and Row_number is not
13.dense_rank
Dense_rank () over (PARTITION by col1 ORDER by col2)
Indicates the level identification according to col2 within the grouping according to the col1 group
That is ranking, the same data returns the same rank
Sequential sort, if there is a juxtaposition second, the next sort will be three, which is different from rank, rank is jumping sort
14.UNION, UNION All
To combine the results of multiple SELECT statements, you can use the set operator to implement the Union, intersection, and difference
Set operators include union, union all, intersect, and minus
The number of columns and data type must match for multiple SELECT statements that act as collections
The ORDER by clause can only be placed in the last query statement
The syntax for a collection operation is as follows:
SELECT Statement1 [union| UNION all| Intersect| Minus] SELECT Statement2;
Used to obtain a set of two or more two result sets
The union operator automatically removes duplicate records after merging
UNION all returns all rows in two result sets, including duplicate rows.
The union operator sorts the results of the query, and the union all is not sorted
15.INTERSECT
To get the intersection of two result sets, only the data that exists in the two result set will be displayed output
Result sets using the Intersect operator are sorted in ascending order with the first column of data
16.MINUS
Get the difference set of two result sets
Data that does not exist in the second result set can be displayed only if there is one in the first result set. That is, result set minus result set two results
17.ROLLUP
The ROLLUP, cube, and grouping sets operators are extensions of the GROUP BY clause that can generate the same result set as using union ALL to combine a single group query to simplify and efficiently implement statistical queries
GROUP by ROLLUP (a,b,c)
GROUP by CUBE (a,b,c)
GROUP by GROUPING sets ((a), (b))
18.ROLLUP (cont.)
Suppose there is a table test with a, B, C, d four columns
SELECT A,b,c,sum (d) from Test GROUP by ROLLUP (a,b,c)
Equivalent to
SELECT A,b,c,sum (d) from Test GROUP by A,b,c
UNION All
SELECT A,b,null,sum (d) from Test GROUP by a, b
UNION All
SELECT A,null,null,sum (d) from Test GROUP by a
UNION All
SELECT Null,null,null,sum (d) from test
Rollup columns are grouped from right to left in one column at a time until all columns are stripped (that is, the whole table is grouped)
For rollup of n parameters, there are n+1 sub-groups
19.GROUP by CUBE (a,b,c)
For each parameter of the cube, it can be understood that the value is a dimension that participates in grouping and does not participate in grouping two values, and that the collection of all dimension values combinations is the grouped collection
For a cube of n parameters, there are 2^n sub-party groupings
Group by CUBE (A,b,c), first group by of (A,b,c), then once is (a, b), (A,C), (a), (B,c), (b), (c), and finally to the whole table group by operation, is 2^3=8 sub-group
20.GROUPING Sets
The GROUPING sets operator can generate the same result set as the result set generated by using a single group BY, rollup, or cube operator
If you do not need to get all the groupings generated by the complete rollup or cube operator, you can use grouping sets to specify only the groupings that you want
GROUPING Sets list can contain duplicate groupings
Grouping examples:
Using GROUP by GROUPING sets (A,b,c), then (a), (b), (c) GROUP by
Using GROUP by GROUPING sets ((b), (c)), then (A, B), (c) GROUP by
GROUP BY GROUPING Sets (A,a), the parameters of group By,grouping sets (a) are allowed to repeat 2 times

SQL (advanced query)