|
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO OUTFILE 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]
SELECT is used to retrieve rows selected from one or more tables.
select_expression indicates the columns you want to retrieve.
SELECT may also be used to retrieve rows computed without reference to
any table. For example:
mysql> SELECT 1 + 1;
-> 2
All keywords used must be given in exactly the order shown above. For example,
a HAVING clause must come after any GROUP BY clause and before
any ORDER BY clause.
-
A
SELECT expression may be given an alias using AS . The alias
is used as the expression's column name and can be used with
ORDER BY or HAVING clauses. For example:
mysql> select concat(last_name,', ',first_name) AS full_name
from mytable ORDER BY full_name;
-
The
FROM table_references clause indicates the tables from which to
retrieve rows. If you name more than one table, you are performing a
join. For information on join syntax, see JOIN .
-
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 in a SELECT
statement unless the reference would be ambiguous. See 7.1.5 Database, table, index, column and alias names,
for examples of ambiguity that require the more explicit column reference
forms.
-
A table reference may be aliased using
tbl_name [AS] alias_name .
mysql> select t1.name, t2.salary from employee AS t1, info AS t2
where t1.name = t2.name;
mysql> select t1.name, t2.salary from employee t1, info t2
where t1.name = t2.name;
-
Columns selected for output may be referred to in
ORDER BY and
GROUP BY clauses using column names, column aliases or column
positions. Column positions begin with 1.
mysql> select college, region, seed from tournament
ORDER BY region, seed;
mysql> select college, region AS r, seed AS s from tournament
ORDER BY r, s;
mysql> 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 may be specified explicitly using
the ASC keyword.
-
The
HAVING clause can refer to any column or alias named in the
select_expression . It is applied last, just before items are sent to
the client, with no optimization. Don't use HAVING for items that
should be in the WHERE clause. For example, do not write this:
mysql> select col_name from tbl_name HAVING col_name > 0;
Write this instead:
mysql> select col_name from tbl_name WHERE col_name > 0;
In MySQL 3.22.5 or later, you can also write queries like this:
mysql> select user,max(salary) from users
group by user HAVING max(salary)>10;
In older MySQL versions, you can write this instead:
mysql> select user,max(salary) AS sum from users
group by user HAVING sum>10;
-
SQL_SMALL_RESULT , SQL_BIG_RESULT , STRAIGHT_JOIN and
HIGH_PRIORITY are MySQL extensions to ANSI SQL92.
-
STRAIGHT_JOIN forces the optimizer to join the tables in the order in
which they are listed in the FROM clause. You can use this to speed up
a query if the optimizer joins the tables in non-optimal order.
EXPLAIN .
-
SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT
to tell the optimizer that the result set will be small. In this case,
MySQL will use fast temporary tables to store the resulting table
instead of using sorting. SQL_SMALL_RESULT is a MySQL
-
SQL_BIG_RESULT can be used with GROUP BY or DISTINCT
to tell the optimizer that the result set will have many rows. In this case,
MySQL will directly use disk based temporary tables if needed.
MySQL in this case will prefer to do a sort instead doing a
temporary table with a key on the GROUP BY elements.
-
HIGH_PRIORITY will give the SELECT higher priority than
a statement that updates a table. You should only use this for queries
that are very fast and must be done at once. A SELECT HIGH_PRIORITY
query will run if the table is locked for read even if there is an update
statement that is waiting for the table to be free.
-
The
LIMIT clause can be used to constrain the number of rows returned
by the SELECT statement. LIMIT takes one or two numeric
arguments.
If two arguments are given, the first specifies the offset of the first row to
return, the second specifies the maximum number of rows to return.
The offset of the initial row is 0 (not 1).
mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15
If one argument is given, it indicates the maximum number of rows to return.
mysql> select * from table LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT n is equivalent to LIMIT 0,n .
-
The
SELECT ... INTO OUTFILE 'file_name' form of SELECT writes
the selected rows to a file. The file is created on the server host, and
cannot already exist (among other things, this prevents database tables and
files such as `/etc/passwd' from being destroyed). You must have the
file privilege on the server host to use this form of SELECT .
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.
LOAD DATA .
In the resulting text file, only the following characters are escaped by
the ESCAPED BY character:
- The
ESCAPED BY character
- The first character in
FIELDS TERMINATED BY
- The first character in
LINES TERMINATED BY
Additionally, ASCII 0 is converted to ESCAPED BY followed by 0
(ASCII 48 ).
The reason for the above is that you MUST escape any FIELDS
TERMINATED BY , ESCAPED BY or LINES TERMINATED BY
characters to reliably be able to read the file
back. ASCII 0 is escaped to make it easier to view with some
pagers.
As the resulting file doesn't have to conform to the SQL syntax, nothing
else need be escaped.
|