Oracle Learning Note Two

One, the Oracle General function VNL (A, A, a, b) for any type, if the value of a is NOT NULL returns the value of a, otherwise returns the value of

Conditional judgment can be used in Oracle

Case Field

When condition 1 then expression 1

When condition 2 then expression 2

else Expression n

End

Another, decode () function, can be calculated when the case is enhanced (field, condition 1, expression 1, condition 2, Expression 2, ...). Expression N)

Second, the quotation marks in the database

Where single quotes appear: 1 strings, where the date, double quotes appear: the alias of the column select Ename "Name" from EMP

Three multi-line functions and grouping operations

Common multiline rows, avg () cout (), Max (), sum (), min (), Statistical function,

Group, goupy by a column in a table

The Trun () function, which intercepts numbers after the decimal point, does not take rounding operations

Iv. details of the GROUP BY clause:

1) All columns of non-multiline functions that appear in the SELECT clause, "must" appear in the GROUP BY clause
2) All the columns that appear in the GROUP BY clause, "can appear non-present" in the SELECT clause

Having

1, Group Filter

2, for the record after the group

3. Following GROUP by

4. Have the province

5. Execute after where

Five, multi-table query

1 Cartesian product (result = sum of number of columns, product of number of rows)

Six, sub-query

Subqueries can be used to query for unknown results,

RowNum special columns, table storage exists

Oracle Learning Note Two