MENU


SELECT

SELECT
 [ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[MAX_STATEMENT_TIME]
[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_expr can be a single * to retrieve all columns.
You may specify an alias for select_expr, as in the following:
SELECT CONCAT(a,'-',b) AS c FROM myTable ORDER BY c;
or
SELECT CONCAT(a,'-',b) c FROM myTable ORDER BY c;
You may specify an alias for each table using the syntax:
tbl_name [[AS] new_name] [index_int]
ORDER BY and GROUP BY can use column names, column aliases, or column positions. Column positions are integers beginning with 1.
The HAVING clause can use aggregate functions and merge rows with the same values for the specified columns, but the WHERE clause cannot:
SELECT a, MAX(b) FROM users
GROUP BY a HAVING MAX(b) > 10;
LIMIT constrains the number of rows fetched:
SELECT * FROM tbl LIMIT 3,10;  # Fetch rows 4-13
SELECT * FROM tbl LIMIT 35,9999999; # Fetch rows 36-last
PROCEDURE processes data in the result set.
If FOR UPDATE is used with a storage engine that uses page or row locks, before the end of the transaction, examined rows cannot be written, cannot be doing SELECT…LOCK IN SHARE MODE, and cannot be read in certain transaction isolation levels. LOCK IN SHARE MODE prevents affected rows from being modified until your transaction commits. Other sessions can still read the rows.
ALL returns all rows including duplicates. DISTINCT removes duplicate rows. DISTINCTROW is the same as DISTINCT.
For storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE), HIGH_PRIORITY runs the query even if the table is locked for reading.
MAX_STATEMENT_TIME sets the execution timeout in milliseconds.
STRAIGHT_JOIN joins the tables in the order in which they appear in the FROM clause.
SQL_BIG_RESULT tells the optimizer that the result set has many rows. SQL_SMALL_RESULT tells the optimizer that the result set has few rows.
SQL_BUFFER_RESULT puts the result into a temporary table, thus freeing the table locks early. This helps when it takes a long time to send the result set to the client.
SQL_CALC_FOUND_ROWS calculates the number of rows in the result set without regard to any LIMIT clause. The total can then be retrieved with SELECT_FOUND_ROWS().
SQL_CACHE stores the result in the query cache if it is cacheable. With SQL_NO_CACHE, the server neither checks the query cache to see if the result is already cached, nor does it cache the query result.
The INTO clause will be discussed in 5.9.2.