-
A select_expr can be given an
alias using AS
alias_name. The alias
is used as the expression's column name and can be used in
GROUP BY, ORDER BY, or
HAVING clauses. For example:
SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable ORDER BY full_name;
The AS keyword is optional when aliasing
a select_expr. 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 the AS is optional, a
subtle problem can occur if you forget the comma between two
select_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
using AS explicitly when specifying
column aliases.
It is not allowable to use a column alias in a
WHERE clause, because the column value
might not yet be determined when the
WHERE clause is executed. See
Section A.5.4, “Problems with Column Aliases”.
-
The FROM
table_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, see Section 13.2.7.1, “JOIN Syntax”.
For each table specified, you can optionally specify an
alias.
tbl_name [[AS] alias]
[{USE|IGNORE|FORCE} INDEX (key_list)]
The use of USE INDEX, IGNORE
INDEX, FORCE INDEX to give the
optimizer hints about how to choose indexes is described in
Section 13.2.7.1, “JOIN Syntax”.
You can use SET
max_seeks_for_key=value
as an alternative way to force MySQL to prefer key scans
instead of table scans. See
Section 5.2.2, “Server System Variables”.
You can refer to a table within the default database as
tbl_name, or as
db_name.tbl_name
to specify a database explicitly. You can refer to a column
as col_name,
tbl_name.col_name,
or
db_name.tbl_name.col_name.
You need not specify a tbl_name
or
db_name.tbl_name
prefix for a column reference unless the reference would be
ambiguous. See Section 9.2, “Database, Table, Index, Column, and Alias Names”, for examples
of ambiguity that require the more explicit column reference
forms.
-
A table reference can be aliased using
tbl_name AS
alias_nametbl_name alias_name:
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 in
ORDER BY and GROUP 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 the DESC
(descending) keyword to the name of the column in the
ORDER BY clause that you are sorting by.
The default is ascending order; this can be specified
explicitly using the ASC keyword.
Use of column positions is deprecated because the syntax has
been removed from the SQL standard.
-
If you use GROUP BY, output rows are
sorted according to the GROUP BY columns
as if you had an ORDER BY for the same
columns. To avoid the overhead of sorting that
GROUP BY produces, add ORDER BY
NULL:
SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
-
MySQL extends the GROUP BY clause so that
you can also specify ASC and
DESC after columns named in the clause:
SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;
MySQL extends the use of GROUP BY to
allow selecting fields that are not mentioned in the
GROUP BY clause. If you are not getting
the results that you expect from your query, please read the
description of GROUP BY found in
Section 12.11, “Functions and Modifiers for Use with GROUP BY Clauses”.
GROUP BY allows a WITH
ROLLUP modifier. See
Section 12.11.2, “GROUP BY Modifiers”.
-
The HAVING clause is applied nearly last,
just before items are sent to the client, with no
optimization. (LIMIT is applied after
HAVING.)
The SQL standard requires that HAVING
must reference only columns in the GROUP
BY clause or columns used in aggregate functions.
However, MySQL supports an extension to this behavior, and
allows HAVING to refer to columns in the
SELECT list and columns in outer
subqueries as well.
If the HAVING 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 a
HAVING column name is used both in
GROUP BY and as an aliased column in the
output column list, preference is given to the column in the
GROUP BY column.
-
Do not use HAVING for items that should
be in the WHERE clause. For example, do
not write the following:
SELECT col_name FROM tbl_name HAVING col_name > 0;
Write this instead:
SELECT col_name FROM tbl_name WHERE col_name > 0;
-
The HAVING clause can refer to aggregate
functions, which the WHERE 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.)
-
The LIMIT clause can be used to constrain
the number of rows returned by the SELECT
statement. LIMIT takes one or two numeric
arguments, which must both be non-negative integer constants
(except when using prepared statements).
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, LIMIT
row_count is equivalent
to LIMIT 0,
row_count.
For prepared statements, you can use placeholders. The
following statements will return one row from the
tbl table:
SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;
The following statements will return the second to sixth row
from the tbl 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 the
LIMIT row_count OFFSET
offset syntax.
-
The SELECT ... INTO OUTFILE
'file_name' form of
SELECT writes the selected rows to a
file. The file is created on the server host, so you must
have the FILE privilege to use this
syntax. file_name cannot be an
existing file, which among other things prevents files such
as /etc/passwd and database tables from
being destroyed. As of MySQL 5.1.6, the
character_set_filesystem system variable
controls the interpretation of the filename.
The SELECT ... INTO OUTFILE statement is
intended primarily to let you very quickly dump a table to a
text file on the server machine. If you want to create the
resulting file on some client host other than the server
host, you cannot use SELECT ... INTO
OUTFILE. In that case, you should instead use a
command such as mysql -e "SELECT ..." >
file_name to generate
the file on the client host.
SELECT ... INTO OUTFILE is the complement
of LOAD DATA INFILE; the syntax for the
export_options part of the
statement consists of the same FIELDS and
LINES clauses that are used with the
LOAD DATA INFILE statement. See
Section 13.2.5, “LOAD DATA INFILE Syntax”.
FIELDS ESCAPED BY controls how to write
special characters. If the FIELDS ESCAPED
BY character is not empty, it is used as a prefix
that precedes following characters on output:
The FIELDS ESCAPED BY character
The FIELDS [OPTIONALLY] ENCLOSED BY
character
The first character of the FIELDS TERMINATED
BY and LINES TERMINATED BY
values
ASCII NUL (the zero-valued byte; what
is actually written following the escape character is
ASCII ‘0’, not a
zero-valued byte)
The FIELDS TERMINATED BY,
ENCLOSED BY, ESCAPED
BY, or LINES TERMINATED BY
characters must be escaped so that you
can read the file back in reliably. ASCII
NUL is escaped to make it easier to view
with some pagers.
The resulting file does not have to conform to SQL syntax,
so nothing else need be escaped.
If the FIELDS ESCAPED BY character is
empty, no characters are escaped and NULL
is output as NULL, not
\N. It is probably not a good idea to
specify an empty escape character, particularly if field
values in your data contain any of the characters in the
list just given.
Here is an example that produces a file in the
comma-separated values (CSV) format used by many programs:
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
If you use INTO DUMPFILE instead of
INTO OUTFILE, MySQL writes only one row
into the file, without any column or line termination and
without performing any escape processing. This is useful if
you want to store a BLOB value in a file.
Note: Any file created by
INTO OUTFILE or INTO
DUMPFILE is writable by all users on the server
host. The reason for this is that the MySQL server cannot
create a file that is owned by anyone other than the user
under whose account it is running. (You should
never run mysqld as
root for this and other reasons.) The
file thus must be world-writable so that you can manipulate
its contents.
The SELECT syntax description at the
beginning this section shows the INTO
clause near the end of the statement. It is also possible to
use INTO OUTFILE or INTO
DUMPFILE immediately preceding the
FROM clause.
A PROCEDURE clause names a procedure that
should process the data in the result set. For an example,
see Section 27.4.1, “Procedure Analyse”.
If you use FOR 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. Using LOCK IN SHARE MODE
sets a shared lock that allows other transactions to read
the examined rows but not to update or delete them. See
Section 14.2.10.5, “SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads”.
