"Sailing the full mastery of SQL skills"

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/05/AA/wKiom1mpE-vyZV5-AABK9SPvNKE351.png-wh_500x0-wm_ 3-wmp_4-s_1366597960.png "title=" Qihang.png "alt=" Wkiom1mpe-vyzv5-aabk9spvnke351.png-wh_50 "/>

Directory

The first part: SELECT query article

I. SELECT BASE Query

1.1. SELECT Introduction

1.2. Basic syntax for SELECT in SQL99

1.2.1. Introduction to Grammar

1.2.1.1. Full SQL statements

1.2.1.2. All, DISTINCT, UNIQUE differences

1.2.1.3. Aliases for tables and columns

1.2.1.4. Table and column names for double quotation marks

1.2.1.5. How to take Top-n

1.2.1.6. ORDER by sentence

1.2.1.6.1. You can use column names, column aliases, column position representations when you ORDER by

1.2.1.6.2. Wrong ORDER by statement

1.2.1.6.3. Special ORDER by statement (Dbms_random random function)

1.2.1.6.4. ORDER by when NULLS first | NULLS Last

1.2.1.7. GROUP by words

All columns of non-multiline functions appearing in 1.2.1.7.1. SELECT must appear in the GROUP by clause

1.2.1.7.2. All columns appearing in the GROUP by clause can appear in the SELECT sentence

1.2.1.7.3. Column aliases cannot appear in GROUP by

1.2.1.7.4. The difference between WHERE and having

1.2.1.8. Execution order of SQL

1.2.1.9. ORDER by, GROUP by, and DISTINCT

1.2.1.10. * (asterisk) wildcard characters

1.2.1.11. For UPDATE clause

1.2.2. Complete syntax for SELECT in ORACLE

1.2.2.1. With clause

1.2.2.2. FLASHBACK QUERIES (Flash back query)

1.2.2.3. system table DUAL

1.2.2.4. HINT Tips

1.2.2.5. CONNECT by (recursive query)

1.2.2.6. Layered queries

1.2.2.7. PIVOT and UNPIVOT

Two. Sub-query

2.1. Query and sub-query

2.2. Sub-query classification

2.2.1. correlated sub-query (related sub-query)

2.2.2. uncorrelated sub-query (non-correlated subquery)

2.2.3. Subqueries in the SELECT clause

2.2.4. Subqueries in the FROM clause (also known as inline views)

2.2.5. Subqueries in the WHERE clause (also known as nested subqueries)

2.2.5.1. Single-line subquery

2.2.5.2. Multi-row subqueries

2.2.5.3. Multi-column subqueries

2.2.5.4. Child queries in the having

2.2.6. Considerations for using >, <, <>, =, or combination operators in subqueries

2.2.7. When using >, <, <>, = with all, SOME, and any in a subquery, or in a subquery, use all, SOME, and any without restriction

2.2.8. In and EXISTS

2.2.8.1. In any one, quite with the role of JOIN

2.2.8.2. Use of EXISTS

2.2.8.3. In does not process NULLS

2.2.9. DDL statements Using subqueries

2.2.10. DML statements Using subqueries

2.2.10.1. INSERT statements Using subqueries

2.2.10.2. UPDATE statement using subqueries

2.2.10.3. DELETE statements Using subqueries

2.2.11. The problem of referencing each other between the main query and the subquery

2.2.11.1. Subqueries can reference fields of the main query

2.2.11.2. The primary query cannot reference the fields of a subquery

2.2.12. Sub-queries in top-n need to be sorted

2.3. Considerations for sub-queries

2.4. Supplementary cases

2.4.1. Sub-query that's something.

2.4.2. Subqueries referencing the main query field error cases

Three. Set (SET) statement

3.1. Introduction to Collection statements

3.2. Priority of the collection statement and return result

3.2.1. The return type of select using character data in a resurrection query

3.2.2. The return type for select using numeric data in a resurrection query

3.3. Limitations of Set operators

3.4. Collection of SQL instances

3.4.1. return type of character data

3.4.2. Return type of numeric data

3.4.3. UNION all returns duplicate values

3.4.4. UNION returns non-repeating values

3.4.5. Collections cannot be used on large data types (except UNION all)

3.4.6. Sorting expressions in a collection

3.4.7. Set operations for NULL values

3.4.8. A collection of NULL and NOT NULL

3.5. Supplementary cases

3.5.1. UNION All optimization case

3.5.2. Combined optimization method for optimization of Dafa

3.5.3. Using subtraction to optimize cases

Four. Join (Connect)

4.1. JOIN Introduction

4.2. JOIN classification

4.2.1. JOIN Graphical Description

4.2.2. Innon join (inner connection)

4.2.2.1. Innon JOIN Basic syntax

4.2.2.2. Innon JOIN Instance

4.2.3. OUTER join (outer join)

4.2.3.1. Left OUTER JOIN syntax

4.2.3.1. Left OUTER JOIN Instance

4.2.3.3. Right OUTER JOIN syntax

4.2.3.4. Right OUTER JOIN Instance

4.2.3.5. Full OUTER JOIN syntax

4.2.3.6. Full OUTER JOIN Instance

4.2.4. The difference between on and WHERE in the OUTER JOIN

4.2.4.1. The difference between on and WHERE in the left JOIN

4.2.4.2. The difference between on and WHERE in right JOIN

4.2.5. Limitations of ORACLE syntax when OUTER JOIN

4.2.6. The left and right tables in the OUTER JOIN

4.2.7. Problems with left multiple tables in OUTER JOIN

4.2.8. Antjoins (anti-connection)

4.2.8.1. Association predicate is NULL problem

4.2.9. Semijoins (semi-connected)

4.2.9.1. Semijoins or antjoins with OR operator

4.2.10. Selfjoins Self-connect

4.2.11. Partitioned outer joins (off-zone connection)

4.2.12. Cartesian joins (Cartesian product connection)

4.2.13. Natural joins (natural connection)

4.2.14 Cross (Outer) apply joins

4.2.14.1 Cross Apply joins

4.2.14.2. Outer Apply Joins

4.2.15. On, USING, WHERE relationship

4.3. Supplementary cases

4.3.1. Case of optimization using an off-partition connection

4.3.2. An interesting SQL case

Five. Hierarchical query

5.1. Hierarchical principles and syntax

5.1.1. Introduction to Hierarchies

5.1.2. About PRIOR

5.1.3. START with

5.1.4. Level Pseudo-column

5.1.5. Clipping of nodes and branches

5.1.6. Siblings sort

5.1.7. Connect_by_root Pseudo-column

5.1.8. Processing flow for layered queries

5.1.9. Steps for the formation of hierarchies

5.2. Examples of hierarchies

5.2.1. Connect by recursive clauses

5.2.2. Connect by complex statements

5.2.3. The loop caused by the Connect by

5.2.4. Level pseudo-column instances

5.2.5. Start with clause instance

5.2.6. Connect_by_isleaf Pseudo-column instance

5.2.7. Connect_by_root Pseudo-column instance

5.2.8. Sys_connect_by_path Pseudo-column instance

5.2.9. Siblings hierarchical sorting

5.2.10. The position of Prior determines the level of relationship

5.3. Supplementary cases

5.3.1. Small words recursion

5.3.2. TREE's Principle and example description

5.3.3. Travel trails for travellers

Six. With clause

6.1. The principle and syntax of the WITH clause

6.1.1. With clause introduction

6.1.2. With clause syntax

6.2. An instance of the WITH clause

6.2.1. Defining a WITH clause for an alias

6.2.2. With clauses that define multiple aliases

6.2.3. Complex with clauses

6.2.3.1. With clause specifies the alias of a column

6.2.3.2. DEPTH first by depth priority

6.2.3.3. Breadth first by breadth priority

6.2.3.4. Cycle SET Loop

6.2.4. Using a with clause to implement a recursive query

6.2.5. With aliases referenced in the WITH clause

6.2.6. Materialize force generation of temporary tables

Summary of the 6.2.7. With clause

6.3. Supplementary cases

6.3.1. An interesting SQL case

6.3.2. SQL Optimization Sewer capsized

6.3.3. Case for simplifying SQL with with

Seven. Analysis functions

7.1. Introduction to Analytic functions and syntax

7.1.1. Introduction to Analytic functions

7.1.2. Parsing function syntax

7.1.3. Classification of analytic functions

7.2. Examples of analytic functions

7.2.1. Hierarchical functions (Ranking), rank, Dense_rank, row_number, and other functions

7.2.1.1. Rank as an aggregation function

7.2.1.2. Rank as an analytic function

7.2.1.3. Dense_rank as an aggregation function

7.2.1.4. Dense_rank as an analytic function

7.2.1.5. Row_number as an analytic function

7.2.2. open Window function (windowing), SUM, max, Min, Avg, count, and other functions

7.2.2.1. Sum function

7.2.2.2. Max | Min function

7.2.2.3. Avg | Count function

7.2.3. Tabulation function (Reporting), Sum, max, Min, Avg, COUNT, etc.

7.2.3.1. Tabulation function syntax

7.2.3.2. Tabulation function Case

7.2.3.3. The difference between window-opening and tabulation functions

7.2.4. Top-n, Bottom-n, First, last, NTILE functions

7.2.4.1. First | Last function

7.2.4.2. first_value| Last_value function

7.2.4.3. Top-n | Bottom-n function

7.2.5. LAG | Lead function

7.2.6. Listagg function

7.2.6.1. Listagg as an aggregation function

7.2.6.2. Listagg as an analytic function

7.3. Supplementary cases

7.3.1. Analysis function Small case

7.3.2. An example, mastering the use of analytic functions

7.3.3. Analysis functions for Interval

This article is from the "srsunbing" blog, make sure to keep this source http://srsunbing.blog.51cto.com/3221858/1961973

"Sailing the full mastery of SQL skills"