SQL query operations and clause precedence

The table can be automatically built with the source. sql file.

Simple data query:

Select *  from table_name;

Avoid duplicate queries:

Select distinct  from table_name;

To achieve a simple mathematical arithmetic:

Select 1 * 2 ; Select Ten 3 ; Select field_name*2 from table_name;

To implement a string connection:

Select concat ("Hello", "World"); Select concat (concat (Filed_name, " is from table_name;

With conditional query:

Select *  from table_name where <  +  and Field_name='123' )or is not NULL;

Set range query (between and)

Select *  from table_name where between  +  and ;(=<x<=);

Null and "" are different:

Query with in keyword: used to specify a list

Select *  fromtable_namewhereField_nameinch(Ten, -, -, +);Select *  fromtable_namewhereField_name not inch(Ten, -, -);

Fuzzy query:

Fuzzy query (like syntax)

<*/field<tablename<field'%value%';    

A wildcard Description:

_: Represents any single character. Matches a single arbitrary character, which is commonly used to limit the expression's character-length statements.

%: represents any 0 or more characters. Can match any type and length of the character, in some cases, if Chinese, please use two percent sign (%).

[]: Represents one of the characters listed in parentheses (similar to a regular expression). Specifies a character, string, or range that requires matching objects to be any of them.

[user' old [1-9]'   

Will find "Old 1", "Old 2" 、......、 "Old 9";

[^] : Represents a single character that is not listed in parentheses. The value is the same as [], but it requires that the matched object be any character other than the specified character.

Will exclude "old 1" to "Old 4", Looking for "old 5", "Old 6" 、......

[user' old [^1-4]';   

How do I match the percent sign? Use a backslash to move.

Select *  from where  like "\%";

Sort:

Select *  from Order  by field_name; (the default is ascending, ASC , descending to DESC), and multiple columns: Select *  from Order  by field_name1,field_name_2, sort by field1 first, then sort by field2. 

Limit the number of records with the Limit keyword: limit limits the amount of records that you want to display.

Select *  from table_name limit; the first M-line; Select *  from table_name limit m,n; restricts the display of n rows starting with M rows. The limit must be followed by an integral type, and variables and expressions are not supported. 

Statistical functions and grouped record queries:

COUNT (): Number of records

Select Count  from Table_name;count does not record field_name as null; Select Count (1 from table_name;

AVG (): Average

SUM (): Sum

Max (): Maximum value

Min (): Minimum value

Select Max (field_name),min from table_name;

Group query:

Select field_name,Count(1 from table_nameGroup by field_name; Multiple condition groups: Select field_name1,field_name2,Count(1 from table_name  Group field_name1,filed_name2;

Functional group Query:

Select  from Group  by Field_name Displays a list of Field2 fields that are grouped in field1. 

Multiple fields Feature Group query:

Use having to filter on the result set of GROUP by:

Select *  from table_name Group  by Field_name  having > ten;

SQL statement Execution Order:

Processing order:

from: Performs a Cartesian product (Cartesian product) (cross join) on the first two tables in the FROM clause, generating a virtual table VT1

on: Apply an on filter to VT1. Only those rows that make it true are inserted into the VT2.

OUTER (join): If OUTER join is specified (relative to cross join or (INNER join), the reserved table (preserved table: Left outer join marks the left table as a reserved table, right outer join marks the right table as a reserved table, A full outer join that marks two tables as a reserved table) does not find a matching row to be added to VT2 as an outer row, generating VT3. If the FROM clause contains more than two tables, repeat steps 1 through 3 for the result table and the next table that were generated for the previous join until all the tables have been processed.

where: Apply a where filter to VT3. Only the line that is true is inserted into the VT4.

groupby: Groups The rows in VT4 by the list of columns in the GROUP BY clause, generating VT5.

cube| ROLLUP: Inserts a Hyper-group (suppergroups) into VT5, generating VT6.

Having: Apply a having filter to VT6. Only groups that are true are inserted into the VT7.

Select: Processes the select list, producing VT8.

DISTINCT: Removes duplicate rows from VT8, resulting in VT9.

Orderby: Generates a cursor (VC10) by sorting the rows in VT9 by the list of columns in the ORDER by clause.

TOP: Selects the specified number or scale of rows from the beginning of the VC10, generates the table VT11, and returns the caller.

Note: Step 10, sort the rows returned by the column list in the ORDER BY clause, and return the cursor VC10. This step is the first and only step you can use the column aliases in the select list. This step differs from the other step in that it does not return a valid table, but instead returns a cursor. SQL is based on the set theory. The collection does not pre-order its rows, it is only a logical collection of members, and the order of the members is irrelevant. A query that sorts a table can return an object that contains rows organized in a specific physical order. ANSI calls this object a cursor. Understanding this step is the basis for a proper understanding of SQL.

SQL query operations and clause precedence