13.2.9. SELECT Syntax SELECT ... INTOSyntax JOIN Syntax Index Hint Syntax UNION 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 retrieve rows selected from one or more tables, and can include UNION statements and subqueries. See Section, "UNION Syntax", and Section 13.2.10, "Subquery Syntax".

The most commonly used clauses of SELECT statements are these:

SELECT can also be used to retrieve rows computed without reference to any table.

For example:

mysql> SELECT 1 +
        1;        -> 2

You are permitted to specify DUAL as a dummy table name in situations where no tables are referenced:

mysql> SELECT 1 + 1 FROM DUAL;        -> 2

DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.

In general, clauses used must be given in exactly the order shown in the syntax description. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. The exception is that the INTO clause can appear either as shown in the syntax description or immediately following the select_expr list. For more information about INTO, see Section, "SELECT ... INTO Syntax".

The list of select_expr terms comprises the select list that indicates which columns to retrieve. Terms specify a column or expression or can use *-shorthand:

The following list provides additional information about other SELECT clauses:

Following the SELECT keyword, you can use a number of options that affect the operation of the statement. HIGH_PRIORITY, STRAIGHT_JOIN, and options beginning with SQL_ are MySQL extensions to standard SQL.

In MySQL 5.7, a SELECT from a partitioned table using a storage engine such as MyISAM that employs table-level locks locks only those partitions containing rows that match the SELECT statement's WHERE clause. (This does not occur with storage engines such as InnoDB that employ row-level locking.) For more information, see Section 17.6.4, "Partitioning and Locking".

Spec-Zone.ru - all specs in one place