SQL Foundation uses Select to retrieve data

SELECT
[distinct| ALL]
Select_list
From Table_list
[Where+clause]
[Group_by_clause]
[Having CONDITION]
[Order_by_clause]

Use aliases instead of field names in the database (as can be removed with spaces, but not for reading)

SELECT Colimn_name1 as alias 1,... column_name2 | | ' * ' | | 1.23 | | ' = ' | | Column_name2 * 1.23 as Alias 2
from table_name;

field of query using function action

SELECT Colimn_name1 as Alias 1,substr (colimn_name1,1,6) as Alias 2,... column_name2 | | ' * ' | | 1.23 | | ' = ' | | Column_name2 * 1.23 as Alias 2
from table_name;

Remove duplicate records from the retrieved data
SELECT DISTINCT (column_name) from table_name;

Sort the retrieved data
ORDER by
{EXPR | POSITION | C_alias}
[ASC | DESC]
[NULLS First | NULLS last]
[, {EXPR | POSITION | C_alias}
[ASC | DESC]
[NULLS First | NULLS last]
]...

SELECT column_name1,... Column_name2 fromtable_name ORDER by Column_name3;

SELECT column_name1,... Column_name2 fromtable_name ORDER BY column_name3 Desc; (Default is ascending ASC, descending must declare desc)

Handling of NULL values when sorting (the default is ascending when the null value is the lowest, and the first in descending order)

Place null values in the first place in ascending order

SELECT column_name1,... Column_name2 fromtable_name ORDER by Column_name3 NULLS first;

Place null values at the bottom of the row when descending
SELECT column_name1,... COLUMN_NAME2 from table_name ORDER by Column_name3 Nullslast;

Aliases can be used

SELECT column_name1,... Column_name2 Fromtable_name ORDER by alias NULLS last;

Use an expression as a sort field

SELECT column_name1,... Column_name2 fromtable_name ORDER by EXPR NULLS last;

Use the position of the field as the Sort field (convenient, and prevent errors when using union)

SELECT column_name1,... Column_name2 fromtable_name ORDER by 3 ASC;

Sorting using multiple fields

SELECT column_name1,... Column_name2 fromtable_name ORDER by column_name2 asc,3 DESC;

To set a search condition using the WHEHE clause

Relational operators include:<, <=, >, >=, =,! =, <>

Comparison operators include:

Is null if the operand is null returns True

Like blur comparison string value

Between ... And ... Verify that the value is within range

In verifies that the operand is in a set number of values

Logical comparators include:

and two conditions must be met

OR as long as one of the two conditions is met

Not is reversed from a logical value

Single condition limit used in queries

SELECT column_name1,... Column_name2 fromtable_name WHERE CONDITION ORDER by column_name;

Using functions in query conditions

SELECT column_name1,... Column_name2 fromtable_name WHERE SUBSTR (condition,1,2) = ' 123456 ';

Multiple criteria restrictions are used in a query

SELECT column_name1,... Column_name2 fromtable_name WHERE CONDITION1 and CONDITION2;

Query criteria using between ... And ...

SELECT column_name1,... Column_name2 fromtable_name WHERE column_name1 between ' 12344 ' and ' 12334 ';

Query criteria use or

SELECT column_name1,... Column_name2 fromtable_name WHERE CONDITION1 or CONDITION2;

Fuzzy query data

SELECT column_name1,... Column_name2 fromtable_name WHERE column_name1 like '%condition% ';

Query criteria are limited to a list range

SELECT column_name1,... Column_name2 fromtable_name WHERE column_name1 in (' CONDITION1 ', ' CONDITION2 ');

Queries that are specifically for null values

SELECT column_name1,... Column_name2 fromtable_name WHERE column_name1 is NULL;

Specifically for non-null value scrub

SELECT column_name1,... COLUMN_NAME2 from table_name wherecolumn_name1 are not NULL;

GROUP by clause syntax and usage

GROUP by
{EXPR
| {Rollop | CUBE} ({expr [, expr] ...})
}

Expr usually refers to the database column name

Rollop | CUBE GROUP By clause extension returns subtotals and grand totals records

Used with a grouping function to group by a column, or to group by a few columns

SELECTCOLUMN_NAME1...,COLUMN_NAME2 from table_name [WHERE CONDITION] GROUP bycolumn_name4...,column_name5;

Use of having clauses

The HAVING clause is usually used with the GROUP BY clause, which restricts the search condition, which is different from the WHERE clause. A HAVING clause is related to a group, not to a single group, and in a GROUP BY clause, he acts on the groups created by GroupBy

Selectcolumn_name1 from Table_namegroup to Column_name2 having CONDITION;

Working with sub-queries

Single conditional subquery

SELECTCOLUMN_NAME1...,COLUMN_NAME2 from table_name WHERE column_name codition (SELECT column_name from table_name WHERECON dition);

Multi-Conditional subquery

SELECTCOLUMN_NAME1...,COLUMN_NAME2 from table_name WHERE column_name codition (SELECT column_name from table_name WHERECON dition) and column_name codition (SELECT column_name from table_name wherecondition);

Subquery returns multiple rows

In (subquery statement);

Any (subquery statement), any one that satisfies the subquery result, and <,<= mate, which represents the maximum value in less than or equal to the list, and the >,>= mate represents the maximum value greater than or equal to the list

SOME (subquery statement); can be considered to be the same as any

All (sub-query statement); Represents all results that satisfy the subquery result, and the <,<= mate, which represents the maximum value in the less than equals list, and the >,>= mate represents the maximum value greater than or equal to the list L

Count function

SELECT COUNT (*) from table_name [WHERE CONDITION];

Connection Query

Internal connection query (multiple tables connected to query bar number I n*m ...)

Selecrcolumn_name1,... Column_name2 from Table_name1,... table_name2 [WHERE CONDITION];

Equivalent connection

SELECTA. Column_name1,... A.column_name2,b.column_name1,... B.column_name2 from Table_name1a,... Table2_name B WHERE A.column_name3=b.column_name3 and A.column_name4=b.column_name4;

SELECTA. Column_name1,... A.column_name2,b.column_name1,... B.column_name2 from Table_name1a INNER joins Table_name2 B on A.column_name3=b.column_name3 ANDA. Column_name4=b.column_name4;

INNER JOIN ... Equivalent to the above

No equivalent connection

A non-equivalent connection means using the ' > ' >= ' < ' <= ' = ' <> ' between in the join condition ... Connect to two conditions list, but this usually needs to be used in conjunction with other equivalent calculations, otherwise the retrieved data may have no practical meaning

SELECTA. Column_name1,... A.column_name2,b.column_name1,... B.column_name2 from Table_name1a INNER joins Table_name2 B on A.column_name3 CONDITION b.column_name3 ANDA. Column_name4 CONDITION b.column_name4;

INNER join can be written directly into the JOIN keyword on cannot save

Self-connect

SELECTA. Column_name1,... A.column_name2,b.column_name1,... B.column_name2 from Table_namea,... table_name B WHERE A.column_name3=b.column_name3 ANDA. Column_name4=b.column_name4;

External connection

Left outer connection

SELECTA. Column_name1,... A.column_name2,b.column_name1,... B.column_name2 from table_name1a left joins Table_name2 B on A.column_name3 CONDITION b.column_name3 ANDA. Column_name4 CONDITION b.column_name4;

Right outer connection

SELECTA. Column_name1,... A.column_name2,b.column_name1,... B.column_name2 from table_name1a right joins Table_name2 B on A.column_name3 CONDITION b.column_name3 ANDA. Column_name4 CONDITION b.column_name4;

Full outer connection

SELECTA. Column_name1,... A.column_name2,b.column_name1,... B.column_name2 from table_name1a full JOIN table_name2 B on A.column_name3 CONDITION b.column_name3 ANDA. Column_name4 CONDITION b.column_name4;

Use of outer joins (+)

Left connection

SELECTA. Column_name1,... A.column_name2,b.column_name1,... B.column_name2 from Table_name1a,... Table_name2 B WHERE A.column_name3=b.column_name3 (+);

Right connection

SELECTA. Column_name1,... A.column_name2,b.column_name1,... B.column_name2 from Table_name1a,... Table_name2 B WHERE A.column_name3 (+) =b.column_name3;

SQL Foundation uses Select to retrieve data