MYSQL SELECT Syntax

SELECT
[All | DISTINCT | Distinctrow]
[High_priority]
[Straight_join]
[Sql_small_result] [Sql_big_result] [Sql_buffer_result]
[Sql_cache | Sql_no_cache] [sql_calc_found_rows]
select_expr [, select_expr ...]
[From Table_references
[PARTITION Partition_list]
[WHERE Where_condition]
[GROUP by {col_name | expr | position}
[ASC | DESC], ... [With ROLLUP]]
[Having where_condition]
[ORDER by {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count offset Offset}]
[PROCEDURE procedure_name (Argument_list)]
[Into OUTFILE ' file_name '
[CHARACTER SET Charset_name]
Export_options
| Into DumpFile ' file_name '
| into Var_name [, Var_name]]
[For UPDATE | LOCK in SHARE MODE]]

Select is used to get rows selected from one or more tables and can contain union statements and subqueries.

The most common clauses of select are as follows:

• Each select_expr indicates a column that you want to get. Must have at least one select_expr.
• Table_references indicates which table or table to fetch rows from. The syntax is described in the join syntax.
• Starting with MySQL 5.6.2, select supports explicit partition queries, followed by a table name in a table_reference, using the partition keyword with a list of partitions or sub-partitions. In this case, only the rows in the partition list are queried, and the other partitions of the table are ignored.

After MySQL 5.6.6 and, select ... partition from table uses the storage engine that performs table-level locks (and therefore partition locks), such as MyISAM, to lock only the partitions or sub-partitions specified in the partition option.

• The WHERE clause, if given, indicates that the behavior has been chosen to satisfy the condition. Where_condition is an expression in which each selected guild gets true. If there is no WHERE clause, the statement will query out all rows.

In the where expression, you can use all of the functions and operators provided by MySQL except the aggregate function.

Select can also be used to get row calculations, and no tables are used.

Example: Select 1 + 1;2

It is also allowed to use dual as a false table name when no tables are used.

Example: Select 1 + 1 from dual;2

Dual is purely for the convenience of requiring all query statements to contain the FROM and other possible clauses. MySQL may ignore this clause. MySQL does not require the from dual if no tables are used.

In general, clauses must be used strictly in the order shown in the syntax description. For example, the HAVING clause must be after any GROUP BY clause, and after any order by. The exception is the INTO clause, which can be displayed as shown in the syntax description, or immediately following the select_expr list.

The select_expr term list contains a list of queries that indicate which columns to get. The term specifies a column or an expression or uses the * abbreviation.

• There is only one unrestricted * query list as an abbreviation for querying all columns of all tables.

SELECT * from t1 INNER JOIN T2 ...

• Tbl_name.* can be used as a restricted abbreviation to query all columns from this defined table.

Select t1.*, t2.* from t1 inner JOIN T2 ...

• Using Unrestricted * and other items in the query list can produce syntax errors. To avoid this problem, you can use tbl_name.* to refer to it.

Select AVG (Score), t1.* from T1 ...

MYSQL SELECT Syntax