Oracle SQL Basics

1. nvl ('A', 'B') if A is null, B is displayed.

2. DECODE (value, if1, then1, if2, then2, if3, then3,... else)

3. grouping function usage, with a parameter. The parameter is the field name. If the result is obtained from this field, 1 is returned. Otherwise, 0 is returned.

4. Over

Over cannot be used independently. It must be used together with analysis functions such as rank (), dense_rank (), and row_number.
Parameter: over (partition by col1 order by col2)
Meaning: grouping by fields specified by col1 and sorting by col2

4. window opening function: rank () over (partition by [col1], [col2]... by [col3] desc)

Rank, dense_rank, row_number:
If two identical data entries appear in the result set, rank performs a skip ranking,
For example, if there are two second places, then there is no third and then there is the fourth place;
However, dense_rank does not skip the ranking, and the second and second are the third;
Row_number is the best. Even if the two data types are the same, the ranking is also different.

5. distinct deduplication

6. The Lag and Lead functions can retrieve the data of the first N rows and the last N rows of a field in a query (it can be the data of other fields, for example, if field a is used to query field B of the previous or next two rows, subquery is used when no function is analyzed. However, it is troublesome, some subqueries cannot be found.

Syntax:
Lag (value_expression [, offset] [, default]) over ([query_partition_clase] order_by_clause );
Lead (value_expression [, offset] [, default]) over ([query_partition_clase] order_by_clause );
Where:
Value_expression: it can be a field or a built-in function.
Offset is a positive integer. The default value is 1, indicating the first or later records. Because the first record in the group does not have the previous row, the last row does not have the subsequent row,
Default is used to process such information. It is null by default.

Find out whether or not each migrant worker made money in the previous month and next month)

 ),,,),,) (partition  sname  ),,,),,) (partition  sname   earnings;

Http://love-flying-snow.iteye.com/blog/573083 details:

7. Two cases

Simple case:

 expression1  expression2  expressionN  default_result

If search_expression is null, it will go through ELSE, and no other matching will be performed, but decode does not have this problem.

Search case:
Use case when + to compare the conditions to the end to return the results. After the end, you can perform operations.
Syntax:

  Boolean  Boolean  

Differences between simple case and searched case:
1. A simple case can only be the expression after the when exactly matches the expression after the case, which is equal to =, so no
Can match null.
2. searched case can be used as a comparison condition, so you can use like ,! =, Between... and, <, =,
Is null, is not null, and so on. It is more widely used than a simple case and can completely replace a simple case.

8. Basic Structure of the stored procedure

: Stored procedure name

9. Basic trigger Structure

        table_name   old  NEW  new PARENT SQL_BLOCK  CALL procedure_name;