Oracle 5.0, Справочное руководство

Страница: 1104 / 3232

SELECT
Syntax
1084
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, see
Section 13.2.8.3,
“Index Hint 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.1.4, “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.1, “Identifier
Qualifiers” , for examples of ambiguity that require the more explicit column reference forms.
• A table reference can be aliased using
tbl_name AS alias_name
or
tbl_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.
If
ORDER BY
occurs within a subquery and also is applied in the outer query, the outermost
ORDER
BY
takes precedence. For example, results for the following statement are sorted in descending
order, not ascending order:
(SELECT ... ORDER BY a) ORDER BY a DESC;
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 permit 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.15, “Functions and Modifiers for Use with
GROUP BY
Clauses” .