Data query of SQL language

In an Oracle database, the basic syntax for a SELECT statement is:

SELECT [all| DISTINCT]column_name[, expression ...] fromTable1_name[, Table2_name, view_name, ...][WHERE Condition][GROUP by Column1_name [, Column2_name, ...] ][Having group_condition][ORDER by Column1_name [ASC | DESC] [, Column2_name, ...]]

First, the basic query

1. Unconditional Inquiry

[1] Query all columns: You can use "*" to indicate the target column;

[2] Query the specified column;

[3] used as an expression: such as Column1 * 8;

[4] Using character constants: such as ' name ';

[5] Use of functions: such as UPPER (Column1);

[6] using the column name alias: Add "Space" or "as" after the column name, plus alias;

[7] Using the connection string: using the | | "to concatenate strings;

[8] Eliminate duplicate rows: Use "DISTINCT" to remove duplicate Row records.

2. Conditional query

2.1 Relational Operations: =, >, <, >=, <=, <>,! =

2.1 Determining range: Between ... And ... And not between ... And ...

2.3 Determining the set: in and not in

2.4 Character matches: like and not like, with "%" and "_" two wildcard characters.

[1]%: represents 0 or more arbitrary characters;

[2] _: Represents a single arbitrary character;

2.5 Control judgment: Is null and is not NULL;

2.6 Logical operations: not, and, or, priority not > and > or

3. Query sort

3.1 Single row ascending, descending sort: ASC or DESC;

3.2 Multi-column sorting:

When sorting by more than one column or multiple expressions, sort by first column or expression, and when the data for the first column or expression is sorted, the same data is sorted with the second column or expression, and so on.

3.3 Sort By expression: Column1 * 8;

3.4 Use the list to sort aliases;

3.5 Sort By Column position number: The position number is the column name or the position number of the expression in the SELECT clause (starting with 1).

If you use collection queries such as UNION, INTERSECT, minus, and target column names, you must use column position sorting.

4, Query statistics

It is usually implemented using built-in aggregation functions (also called grouping functions, aggregate functions).

Common aggregation functions:

COUNT (* [| num]): Returns the number of records in the result set (including null);

COUNT ([DISTINCT | All] Column: Returns the number of non-empty records in the result set (excluding null);

AVG ([DISTINCT | All] Column: Returns the average of a column or expression (ignoring null);

MAX ([DISTINCT | All] Column: Returns the maximum value of the column or expression (ignoring null);

MIN ([DISTINCT | All] Column: Returns the minimum value of the column or expression (ignoring null);

SUM ([DISTINCT | All] Column: Returns the sum of the columns or expressions (ignoring null);

STDDEV (column): Returns the standard deviation of a column or an expression;

VARIANCE (column): Returns the variance of a column or an expression;

Use the aggregation function to note:

[1] In addition to the count (* [| num]) function, other statistical functions do not consider the return value or the expression is null;

[2] Aggregation functions can only appear in the SELECT clause as a target column expression, an ORDER by clause, a HAVING clause, cannot appear in the WHERE clause and the GROUP BY clause;

Second, group query

Basic syntax:

SELECT column , Group_function, ...  from Table [] [ GROUP by [ROLLUP | CUBE | GROUPING sets]  group_by_expression][][ ] ]

Attention:

[1] The GROUP BY clause is used to specify grouping columns or grouping expressions.

[2] The HAVING clause is used to restrict the return result of a grouping.

[3] The WHERE clause filters the records in the table, while the HAVING clause filters the groups formed after grouping.

[4] In a grouped query, all target columns or target expressions after the SELECT clause can only be: grouped columns, grouping expressions, aggregate functions.

1, single-row group query;

2, multi-row group query;

3, having clauses restricting the return group;

4, ROLLUP and CUBE options

[1] ROLLUP: Horizontal and non-grouped statistics can be generated;

Such as: The average wage for each job in each department, the average wage per department and the average wage of all employees.

[2] CUBE: Horizontal statistics, vertical statistics, and non-grouped statistics can be generated.

Such as: The average wage for each job in each department, the average wage per department, the average wage per job and the average wage of all employees.

5, Merge Group query: Say a few separate group query merged into a group query. Use GROUPING sets in the GROUP BY clause.

　　

Data query of SQL language