Movatterモバイル変換

Eachselect_expr indicates a column that you want to retrieve. There must be at least oneselect_expr.

table_references indicates the table or tables from which to retrieve rows. Its syntax is described inSection 13.2.9.2, “JOIN Clause”.

SELECT supports explicit partition selection using thePARTITION clause with a list of partitions or subpartitions (or both) following the name of the table in atable_reference (seeSection 13.2.9.2, “JOIN Clause”). In this case, rows are selected only from the partitions listed, and any other partitions of the table are ignored. For more information and examples, seeSection 22.5, “Partition Selection”.

SELECT ... PARTITION from tables using storage engines such asMyISAM that perform table-level locks (and thus partition locks) lock only the partitions or subpartitions named by thePARTITION option.

For more information, seeSection 22.6.4, “Partitioning and Locking”.

TheWHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected.where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is noWHERE clause.

In theWHERE expression, you can use any of the functions and operators that MySQL supports, except for aggregate (group) functions. SeeSection 9.5, “Expressions”, andChapter 12,Functions and Operators.

A select list consisting only of a single unqualified* can be used as shorthand to select all columns from all tables:

SELECT * FROM t1 INNER JOIN t2 ...

tbl_name.* can be used as a qualified shorthand to select all columns from the named table:

SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...

Use of an unqualified* with other items in the select list may produce a parse error. For example:

SELECT id, * FROM t1

To avoid this problem, use a qualifiedtbl_name.* reference:

SELECT id, t1.* FROM t1

Use qualifiedtbl_name.* references for each table in the select list:

SELECT AVG(score), t1.* FROM t1 ...

Aselect_expr can be given an alias usingASalias_name. The alias is used as the expression's column name and can be used inGROUP BY,ORDER BY, orHAVING clauses. For example:

SELECT CONCAT(last_name,', ',first_name) AS full_name  FROM mytable ORDER BY full_name;

TheAS keyword is optional when aliasing aselect_expr with an identifier. The preceding example could have been written like this:

SELECT CONCAT(last_name,', ',first_name) full_name  FROM mytable ORDER BY full_name;

However, because theAS is optional, a subtle problem can occur if you forget the comma between twoselect_expr expressions: MySQL interprets the second as an alias name. For example, in the following statement,columnb is treated as an alias name:

SELECT columna columnb FROM mytable;

For this reason, it is good practice to be in the habit of usingAS explicitly when specifying column aliases.

It is not permissible to refer to a column alias in aWHERE clause, because the column value might not yet be determined when theWHERE clause is executed. SeeSection B.3.4.4, “Problems with Column Aliases”.

TheFROMtable_references clause indicates the table or tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, seeSection 13.2.9.2, “JOIN Clause”. For each table specified, you can optionally specify an alias.

tbl_name [[AS]alias] [index_hint]

The use of index hints provides the optimizer with information about how to choose indexes during query processing. For a description of the syntax for specifying these hints, seeSection 8.9.4, “Index Hints”.

You can useSET max_seeks_for_key=value as an alternative way to force MySQL to prefer key scans instead of table scans. SeeSection 5.1.7, “Server System Variables”.

You can refer to a table within the default database astbl_name, or asdb_name.tbl_name to specify a database explicitly. You can refer to a column ascol_name,tbl_name.col_name, ordb_name.tbl_name.col_name. You need not specify atbl_name ordb_name.tbl_name prefix for a column reference unless the reference would be ambiguous. SeeSection 9.2.2, “Identifier Qualifiers”, for examples of ambiguity that require the more explicit column reference forms.

A table reference can be aliased usingtbl_name ASalias_name ortbl_name alias_name. These statements are equivalent:

SELECT t1.name, t2.salary FROM employee AS t1, info AS t2  WHERE t1.name = t2.name;SELECT t1.name, t2.salary FROM employee t1, info t2  WHERE t1.name = t2.name;

Columns selected for output can be referred to inORDER BY andGROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1:

SELECT college, region, seed FROM tournament  ORDER BY region, seed;SELECT college, region AS r, seed AS s FROM tournament  ORDER BY r, s;SELECT college, region, seed FROM tournament  ORDER BY 2, 3;

To sort in reverse order, add theDESC (descending) keyword to the name of the column in theORDER BY clause that you are sorting by. The default is ascending order; this can be specified explicitly using theASC keyword.

IfORDER BY occurs within a parenthesized query expression and also is applied in the outer query, the results are undefined and may change in a future MySQL version.

Use of column positions is deprecated because the syntax has been removed from the SQL standard.

MySQL extends theGROUP BY clause so that you can also specifyASC andDESC after columns named in the clause. However, this syntax is deprecated. To produce a given sort order, provide anORDER BY clause.

If you useGROUP BY, output rows are sorted according to theGROUP BY columns as if you had anORDER BY for the same columns. To avoid the overhead of sorting thatGROUP BY produces, addORDER BY NULL:

SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

Relying on implicitGROUP BY sorting (that is, sorting in the absence ofASC orDESC designators) or explicit sorting forGROUP BY (that is, by using explicitASC orDESC designators forGROUP BY columns) is deprecated. To produce a given sort order, provide anORDER BY clause.

When you useORDER BY orGROUP BY to sort a column in aSELECT, the server sorts values using only the initial number of bytes indicated by themax_sort_length system variable.

MySQL extends the use ofGROUP BY to permit selecting fields that are not mentioned in theGROUP BY clause. If you are not getting the results that you expect from your query, please read the description ofGROUP BY found inSection 12.19, “Aggregate Functions”.

GROUP BY permits aWITH ROLLUP modifier. SeeSection 12.19.2, “GROUP BY Modifiers”.

TheHAVING clause, like theWHERE clause, specifies selection conditions. TheWHERE clause specifies conditions on columns in the select list, but cannot refer to aggregate functions. TheHAVING clause specifies conditions on groups, typically formed by theGROUP BY clause. The query result includes only groups satisfying theHAVING conditions. (If noGROUP BY is present, all rows implicitly form a single aggregate group.)

TheHAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied afterHAVING.)

The SQL standard requires thatHAVING must reference only columns in theGROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permitsHAVING to refer to columns in theSELECT list and columns in outer subqueries as well.

If theHAVING clause refers to a column that is ambiguous, a warning occurs. In the following statement,col2 is ambiguous because it is used as both an alias and a column name:

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

Preference is given to standard SQL behavior, so if aHAVING column name is used both inGROUP BY and as an aliased column in the select column list, preference is given to the column in theGROUP BY column.

Do not useHAVING for items that should be in theWHERE clause. For example, do not write the following:

SELECTcol_name FROMtbl_name HAVINGcol_name > 0;

Write this instead:

SELECTcol_name FROMtbl_name WHEREcol_name > 0;

TheHAVING clause can refer to aggregate functions, which theWHERE clause cannot:

SELECT user, MAX(salary) FROM users  GROUP BY user HAVING MAX(salary) > 10;

(This did not work in some older versions of MySQL.)

MySQL permits duplicate column names. That is, there can be more than oneselect_expr with the same name. This is an extension to standard SQL. Because MySQL also permitsGROUP BY andHAVING to refer toselect_expr values, this can result in an ambiguity:

SELECT 12 AS a, a FROM t GROUP BY a;

In that statement, both columns have the namea. To ensure that the correct column is used for grouping, use different names for eachselect_expr.

MySQL resolves unqualified column or alias references inORDER BY clauses by searching in theselect_expr values, then in the columns of the tables in theFROM clause. ForGROUP BY orHAVING clauses, it searches theFROM clause before searching in theselect_expr values. (ForGROUP BY andHAVING, this differs from the pre-MySQL 5.0 behavior that used the same rules as forORDER BY.)

TheLIMIT clause can be used to constrain the number of rows returned by theSELECT statement.LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

In other words,LIMITrow_count is equivalent toLIMIT 0,row_count.

For prepared statements, you can use placeholders. The following statements return one row from thetbl table:

SET @a=1;PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';EXECUTE STMT USING @a;

The following statements return the second to sixth row from thetbl table:

SET @skip=1; SET @numrows=5;PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';EXECUTE STMT USING @skip, @numrows;

For compatibility with PostgreSQL, MySQL also supports theLIMITrow_count OFFSEToffset syntax.

IfLIMIT occurs within a parenthesized query expression and also is applied in the outer query, the results are undefined and may change in a future MySQL version.

APROCEDURE clause names a procedure that should process the data in the result set. For an example, seeSection 8.4.2.4, “Using PROCEDURE ANALYSE”, which describesANALYSE, a procedure that can be used to obtain suggestions for optimal column data types that may help reduce table sizes.

APROCEDURE clause is not permitted in aUNION statement.

TheSELECT ... INTO form ofSELECT enables the query result to be written to a file or stored in variables. For more information, seeSection 13.2.9.1, “SELECT ... INTO Statement”.

If you useFOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction. UsingLOCK IN SHARE MODE sets a shared lock that permits other transactions to read the examined rows but not to update or delete them. SeeSection 14.7.2.4, “Locking Reads”.

In addition, you cannot useFOR UPDATE as part of theSELECT in a statement such asCREATE TABLEnew_table SELECT ... FROMold_table .... (If you attempt to do so, the statement is rejected with the errorCan't update table 'old_table' while 'new_table' is being created.) This is a change in behavior from MySQL 5.5 and earlier, which permittedCREATE TABLE ... SELECT statements to make changes in tables other than the table being created.

TheALL andDISTINCT modifiers specify whether duplicate rows should be returned.ALL (the default) specifies that all matching rows should be returned, including duplicates.DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both modifiers.DISTINCTROW is a synonym forDISTINCT.

HIGH_PRIORITY gives theSELECT higher priority than a statement that updates a table. You should use this only for queries that are very fast and must be done at once. ASELECT HIGH_PRIORITY query that is issued while the table is locked for reading runs even if there is an update statement waiting for the table to be free. This affects only storage engines that use only table-level locking (such asMyISAM,MEMORY, andMERGE).

HIGH_PRIORITY cannot be used withSELECT statements that are part of aUNION.

STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in theFROM clause. You can use this to speed up a query if the optimizer joins the tables in nonoptimal order.STRAIGHT_JOIN also can be used in thetable_references list. SeeSection 13.2.9.2, “JOIN Clause”.

STRAIGHT_JOIN does not apply to any table that the optimizer treats as aconst orsystem table. Such a table produces a single row, is read during the optimization phase of query execution, and references to its columns are replaced with the appropriate column values before query execution proceeds. These tables appear first in the query plan displayed byEXPLAIN. SeeSection 8.8.1, “Optimizing Queries with EXPLAIN”. This exception may not apply toconst orsystem tables that are used on theNULL-complemented side of an outer join (that is, the right-side table of aLEFT JOIN or the left-side table of aRIGHT JOIN.

SQL_BIG_RESULT orSQL_SMALL_RESULT can be used withGROUP BY orDISTINCT to tell the optimizer that the result set has many rows or is small, respectively. ForSQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if they are created, and prefers sorting to using a temporary table with a key on theGROUP BY elements. ForSQL_SMALL_RESULT, MySQL uses in-memory temporary tables to store the resulting table instead of using sorting. This should not normally be needed.

SQL_BUFFER_RESULT forces the result to be put into a temporary table. This helps MySQL free the table locks early and helps in cases where it takes a long time to send the result set to the client. This modifier can be used only for top-levelSELECT statements, not for subqueries or followingUNION.

SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in the result set, disregarding anyLIMIT clause. The number of rows can then be retrieved withSELECT FOUND_ROWS(). SeeSection 12.15, “Information Functions”.

TheSQL_CACHE andSQL_NO_CACHE modifiers affect caching of query results in the query cache (seeSection 8.10.3, “The MySQL Query Cache”).SQL_CACHE tells MySQL to store the result in the query cache if it is cacheable and the value of thequery_cache_type system variable is2 orDEMAND. WithSQL_NO_CACHE, the server does not use the query cache. It neither checks the query cache to see whether the result is already cached, nor does it cache the query result.

These two modifiers are mutually exclusive and an error occurs if they are both specified. Also, these modifiers are not permitted in subqueries (including subqueries in theFROM clause), andSELECT statements in unions other than the firstSELECT.

For views,SQL_NO_CACHE applies if it appears in anySELECT in the query. For a cacheable query,SQL_CACHE applies if it appears in the firstSELECT of a view referred to by the query.