Select Statement of MySQL

Select
Name
Select-extract several rows from a table or view.

Select [All | distinct [ON (expression [,...])]
Expression [as name] [,...]
[Into [temporary | temp] [Table] new_table]
[From table [alias] [,...]
[Where condition]
[Group by column [,...]
[Having condition [,...]
[{Union [all] | intersect | except t} select]
[Order by column [ASC | DESC | using operator] [,...]
[For update [of class_name [,...]
Limit {count | all} [{offset |,} start]

Input
Expression
The column/field name or expression of the table.
Name
Use the as clause to declare another name for a column/field or expression. This name is mainly used to mark the output column. Name cannot be used in the where, group by, or having clause. However, it can be referenced in the order by clause.
Temporary, temp
This table is unique in this session and will be automatically deleted after the session ends.
New_table
If the into Table clause is declared, the query results will be stored in another table with the specified name. The target table (new_table) will be automatically created and should not exist before the command is executed. For more information, see select.
Note: the create table as statement also creates a new table from the selected query.

Table
The name of an existing table referenced by the form clause.
Alias
The alias of the table being processed. It is used to abbreviation or remove the ambiguity when a table is joined.
Condition
Returns a true or false Boolean expression. For more information, see the WHERE clause.
Column
The name of the column/field in the table.
Select
A selection statement that can have all the features except the order by clause.
Output
Rows
Query the rows of all returned result sets.
Count
Query the records of the returned rows.
Description
Select returns record rows from one or more tables. The selected candidate row is all rows that meet the where condition. Or if the where statement is omitted, all rows in the table are selected. (See where clause)

Distinct deletes all duplicate rows from the selected result set. All (default) returns all the selected rows, including duplicate rows.

Distinct on deletes the rows that match all expressions. Only the first row of each repeated set is retained. Note that the "first line" of each duplicate set is unpredictable unless we use order by to ensure that the row we want appears first. For example,

Select distinct on (location) location, time, report
From weatherreports
Order by location, time DESC;

Retrieve the latest weather forecast for each region. However, if we do not use order by to force the time-based descending order of each region, we will get an unpredictable time report for each region.

The group by clause allows you to divide a table into conceptual groups. (See the group by clause .)

The having clause declares a Grouped table. The table is generated after some groups are removed from the result set of the previously declared clause. (See having clause .)

The order by clause allows the user to specify the Order Mode of the row he/she wants Based on the pattern operator (ascending or descending) (see the order by clause ).

The Union operator allows the result set to be the set of rows returned by the involved query. (See union clause .)

Intersect provides two common rows for query. (See the Intersect clause .)

Distinct t indicates the row that exists in the first query but does not exist in the second query. (See the limit t clause .)

The for update clause allows the SELECT statement to execute an exclusive lock on the selected rows.

The limit clause allows you to return a subset of the results generated by a query. (See the limit clause .)

You must have the select permission to read values from the table (see Grant/Revoke statements ).

Where clause
The optional where condition has the following common forms:

Where boolean_expr

Boolean_expr can contain any Boolean expression. Normally, the expression is

Expr cond_op expr

Or

Log_op expr

Here cond_op can be one of the following: =, <, <=,>, >=, <> or the conditional operators such as all, any, in, and like, or user-defined operators, log_op can be: And, or, not. compare the returned results either true or falset. If the expression value is false, all records will be discarded.

Group by clause
Group by declares a table that is divided into groups. The table is derived from the following clause used by the application:

Group by column [,...]

Group by compresses all rows that share the same value in the combined columns into one row. If clustering functions exist, these Aggregate functions calculate all rows in each group and calculate an independent value for each group (if no group, the aggregate function calculates a value for all the rows selected ). When group by exists, except in clustering functions, reference to any non-composite column is invalid because there will be more than one possible return value for a non-composite column.

Having clause
The following table lists the optional having conditions:

Having cond_expr

Here, cond_expr is the same as that declared in the WHERE clause.

The having clause declares that a table that does not conform to the cond_expr group is removed from the result set of the preceding clause.

Each column/field referenced in cond_expr should clearly specify the column/Field of a group unless referenced in a clustering function.

Order by clause
Order by column [ASC | DESC] [,...]

Column can be either a column/field name or an ordinal number.

Ordinal refers to the position of columns/fields in order (from left to right. this feature makes it possible to sort columns/fields without a proper name. this may never be used because the as clause can always be used to assign a name to a column/field to be calculated. For example:

Select title, date_prod + 1 as newlen from films order by newlen;

From PostgreSQL version 6.4, you can also use any order by expression, including the fields that are not displayed in the select result list. Therefore, the following statement is valid:

Select name from distributors order by code;

We can add a keyword DESC (descending) or ASC (ascending) to each column/field in the order by clause ). if not declared, ASC is the default value. we can also declare a sorting operator to implement sorting. ASC is equivalent to '<' while DESC is equivalent to '> '.

Union clause
Table_query Union [all] table_query
[Order by column [ASC | DESC] [,...]

Table_query indicates any selection expression without the order by clause.

The Union operator allows the result set to be the set of results returned by the involved query. Two select statements that act as the direct operand of union must generate fields of the same number and the corresponding fields must have compatible data types.

By default, Union results do not contain any duplicate rows unless the all clause is declared.

Multiple Union operators in the same SELECT statement are calculated from left to right. Note that the All keyword is not necessarily global, but only applied to the results of the current pair of tables.

Intersect clause
Table_query intersect table_query
[Order by column [ASC | DESC] [,...]

Here, table_query declares any selection expressions without the order by clause.

Intersect provides two common rows for query. The two select results, which act as the direct operand of intersect, must have the same number of fields and the corresponding fields must have compatible data types.

Unless the sequence is specified in parentheses, multiple intersect operators in the same SELECT statement are calculated from left to right.

Limit t clause
Table_query partition t table_query
[Order by column [ASC | DESC] [,...]

Here, table_query declares any selection expressions without the order by clause.

Distinct t indicates the row that exists in the first query but does not exist in the second query. (See the limit t clause ). The two select results used as the distinct T direct operand must have the same number of fields and the corresponding fields must have compatible data types.

Unless the sequence is specified in parentheses, multiple distinct T operators in the same SELECT statement are calculated from left to right.

Limit clause
Limit {count | all} [{offset |,} start]
Offset start

Here, the Count statement specifies the maximum number of rows returned, while the start statement specifies the number of rows ignored before the return.

Limit allows you to retrieve a part of a row generated by querying other parts. If a limit is given, the number of returned rows does not exceed the limit. If an offset is given, the number of rows will be ignored before the row is returned.

When using limit, a good habit is to use an order by clause to limit the result row to a unique order. Otherwise, you will get an unexpected subset returned by the query-you may want rows 10 to 20th, but in what order? You do not know the order unless you declare order.

In ipvs 7.0, the query optimizer takes the limit into account when generating the query plan, therefore, it is very likely that you will get different plans for different limit and offset values (generating different rows ). Therefore, selecting a subset of different query results with different limit/Offset values will not produce consistent results unless you use order by to generate an predictable order of results. This is not a problem; this is a feature of SQL, because unless order bye is used to constrain the order, SQL does not guarantee that the query results are in any specific order.

Usage
Combine table films with table Distributors:

Select F. Title, F. Did, D. Name, F. date_prod, F. Kind
From distributors D, films F
Where F. Did = D. Did

Title | did | Name | date_prod | kind
------------------------- + --- + ---------------- + ----------
The third man | 101 | briish lion | 1949-12-23 | Drama
The African Queen | 101 | briish lion | romantic
Une femme est une femme | 102 | Jean Luc Godard | 1961-03-12 | romantic
Vertigo | 103 | paramount | 1958-11-14 | action
Becket | 103 | paramount | Drama
48 hrs | 103 | paramount | 1982-10-22 | action
War and Peace | 104 | Mosfilm | 1967-02-12 | Drama
West Side Story | 105 | United Artists | 1961-01-03 | musical
Bananas | 105 | United Artists | 1971-07-13 | comedy
Yojimbo | 106 | Toho | 1961-06-16 | Drama
There's a girl in my soup | 107 | Columbia | 1970-06-11 | comedy
Taxi Driver | 107 | Columbia | 1975-05-15 | action
Absence of malice | 107 | Columbia | 1981-11-15 | action
Storia di una donna | 108 | westward | romantic
The King and I | 109 | 20th Century Fox | 1956-08-11 | musical
Das Boot | 110 | Bavaria atelier | 1981-11-11 | Drama
Bed knobs and broomsticks | 111 | Walt Disney | musical

Count the sum of Len (length) of all movie and group columns/fields grouped by kind:

Select kind, sum (LEN) as total from films group by kind;

Kind | Total
---------- + ------
Action | 07:34
Comedy | 0: 58
Drama | 14: 28
Musical | 06:42
Romantic |

Count the sum of all movies (films), group columns/fields Len (length), group with kind and show the group sum less than 5 hours:

Select kind, sum (LEN) as total
From films
Group by kind
Having sum (LEN) <interval '5 hour ';

Kind | Total
---------- + ------
Comedy | 0: 58
Romantic |

The following two examples are typical methods for sorting individual results based on the content of the second column (name.

Select * from distributors order by name;
Select * from distributors order by 2;

Did | Name
--- + ----------------
109 | 20th Century Fox
110 | Bavaria Atelier
101 | British lion
107 | Columbia
102 | Jean Luc Godard
113 | Luso Films
104 | Mosfilm
103 | paramount
106 | Toho
105 | United Artists
111 | Walt Disney
112 | Warner Bros.
108 | westward

This example shows how to obtain the Union of distributors and actors. Only the table starting with a letter W is obtained. Because only irrelevant rows are obtained, the keyword "all" is omitted:

-- Distributors: actors:
-- Did | Name ID | Name
-- --- + ------------ -- + --------------
-- 108 | westward 1 | Woody Allen
-- 111 | Walt Disney 2 | Warren Beatty
-- 112 | Warner Bros. 3 | Walter Matthau
--......

Select distributors. Name
From distributors
Where distributors. name like 'W %'
Union
Select actors. Name
From actors
Where actors. name like 'W %'

Name
--------------
Walt Disney
Walter Matthau
Warner Bros.
Warren Beatty
Westward
Woody Allen

Compatibility
Extension
S allows us to omit the from clause in a query. This feature is retained from the original postquel query language:

Select distributors. * Where name = 'westwood ';

Did | Name
--- + ----------------
108 | westward

Sql92
Select clause
In the sql92 specification, the optional keyword "as" is redundant and can be ignored without any impact on the statement. postgres analyzer requires this keyword when renaming columns/fields, because the type Extension Feature will cause the context to be ambiguous.

The distinct on syntax is not the standard of sql92. Neither limit nor offset.

Union clause
The Union Syntax of sql92 allows an additional corresponding by clause:

 
Table_query Union [all]
[Corresponding [by (column [,...])]
Table_query

Corresponding by is not currently supported by ipvs.