GNU Info
(mysql.info)SELECT
`SELECT' Syntax
---------------
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name]
[FOR UPDATE | LOCK IN SHARE MODE]]
`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 Note:
`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 Note:
Legal 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.
* You can in the `WHERE' clause use any of the functions that MySQL
support. Note: Functions.
* 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 Version 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', `SQL_BUFFER_RESULT',
`STRAIGHT_JOIN', and `HIGH_PRIORITY' are MySQL extensions to ANSI
SQL92.
* `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.
* `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 will also, in this case, prefer sorting to doing a
temporary table with a key on the `GROUP BY' elements.
* `SQL_BUFFER_RESULT' will force the result to be put into a
temporary table. This will help MySQL free the table locks early
and will help in cases where it takes a long time to send the
result set to the client.
* `SQL_SMALL_RESULT', a MySQL-specific option, 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. In MySQL
Version 3.23 this shouldn't normally be needed.
* If you use `GROUP BY', the output rows will be sorted according to
the `GROUP BY' as if you would have had an `ORDER BY' over all the
fields in the `GROUP BY'. MySQL has extended the `GROUP BY' so that
you can also specify `ASC' and `DESC' to `GROUP BY':
SELECT a,COUNT(b) FROM test_table GROUP BY a DESC
* MySQL has extended the use of `GROUP BY' to allow you to select
fields which are not mentioned in the `GROUP BY' clause. If you
are not getting the results you expect from your query, please
read the `GROUP BY' description. Note: Group by functions.
* `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. Note: `EXPLAIN'.
* 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 mainly intended to let you very
quickly dump a table on the server machine. If you want to create
the resulting file on some other host than the server host you
can't use `SELECT ... INTO OUTFILE'. In this case you should
instead use some client program like `mysqldump --tab' or `mysql
-e "SELECT ..." > outfile' to generate the file.
`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. Note: `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.
Here follows an example of getting a file in the format used by
many old programs.
SELECT a,b,a+b INTO OUTFILE "/tmp/result.text"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM test_table;
* If you use `INTO DUMPFILE' instead of `INTO OUTFILE', MySQL will
only write one row into the file, without any column or line
terminations and without any escaping. This is useful if you want
to store a blob in a file.
* Note that any file created by `INTO OUTFILE' and `INTO DUMPFILE'
is going to be readable for all users! The reason is that the
MySQL server can't create a file that is owned by anyone else than
the user it's running as (you should never run `mysqld' as root),
the file has to be word readable so that you can retrieve the rows.
* If you are using `FOR UPDATE' on a table handler with page/row
locks, the examined rows will be write locked.
JOIN- `JOIN' Syntax
automatically generated by info2www version 1.2.2.9