لطفا منتظر باشید ...
SELECT
SELECT [select_options]
select_expression, ...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | col_pos } [ASC | DESC], ...]
[HAVING where_definition]
[ORDER BY {col_name | col_pos } [ASC | DESC], ...]
[LIMIT [offset,] rows]]
SELECT is the most complex query in SQL, and is used
to perform all data retrieval operations. This query supports the following select_options,
which may be specified in any sensible combination by simply listing them
separated by spaces:
STRAIGHT_JOIN
Forces MySQL to join multiple tables specified in the table_references argument
in the order specified there. If you think MySQL’s query optimizer is
doing it the ‘slow way’, this argument lets you override it. For
more information on joins, see "Joins" below.
SQL_SMALL_RESULT
This option shouldn’t be needed in MySQL 3.23 or later; however,
it remains available for compatibility. This option informs MySQL that you
are expecting a relatively small result set from a query that uses the DISTINCT option
or the GROUP BY clause, so it uses the faster, but more
memory-intensive, method of generating a temporary table in memory to hold
the result set as it is created.
SQL_BIG_RESULT
Along the same lines as SQL_SMALL_RESULT, this option
informs MySQL that you are expecting a large number of results from a query
that makes use of DISTINCT or GROUP BY.
When it creates the result set, MySQL will create on disk, as needed, a temporary
table in which the results are sorted. This is a quicker solution than generating
an index on the temporary table, which would take longer to update for each
result row in a large result set.
SQL_BUFFER_RESULT
This option forces MySQL to store the result set in a temporary table.
This frees up the tables that were used in the query for use by other processes,
while the result set is transmitted to the client.
HIGH_PRIORITY
This option does exactly what it says—it assigns a high priority
to the SELECT query. Normally, if a query is waiting to
update a table, all read-only queries (such as SELECT)
must yield to it. A SELECT HIGH_PRIORITY, however, will
go first.
DISTINCT | DISCTINCTROW | ALL
Any one of these three options may be used to specify the treatment
of duplicate rows in the result set. ALL (the default)
specifies that all duplicate rows appear in the result set, while DISTINCT and DISTINCTROW (they
have the same effect) specify that duplicate rows should be eliminated from
the result set.
select_expression defines
a column of the result set to be returned by the query. Typically, this is
a table column name, and may be specified as col_name, tbl_name.col_name,
or db_name.tbl_name.col_name, depending on how specific
you need to be for MySQL to know to which particular column you are referring. select_expressions
need not refer to a database column—a simple mathematical formula such
as 1 + 1 or a complex expression calculated with MySQL
functions may also be used. Here’s an example of the latter, which will
give the date one month from now in the form “January 1st, 2002”:
SELECT DATE_FORMAT(
DATE_ADD(CURDATE(),INTERVAL 1 MONTH),'%M %D, %Y')
select_expressions may
also contain an alias, or assigned name for the result column, if the expression
is followed with [AS] alias (the AS is
entirely optional). This expression must be used when referring to that column
elsewhere in the query (e.g. in WHERE and ORDER
BY clauses), as follows:
SELECT JokeDate AS JD FROM Jokes ORDER BY JD ASC
MySQL lets you use an INTO clause to output
the results of a query into a file instead of returning them to the client.
The most typical use of this is to export the contents of a table into a text
file containing comma-separated values (CSV). Here’s an example:
SELECT * INTO OUTFILE '/home/user/myTable.txt'
FIELDS TERMINATED BY ',' OPTIONALY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM myTable
The file to which the results are dumped must not exist beforehand,
or this query will fail. This restriction prevents an SQL query from being
used to overwrite critical operating system files. The created file will also
be world-readable on systems that support file security, so consider this
before you export sensitive data to a text file that anyone on the system
can read.DUMPFILE may be used instead of OUTFILE to
write to the file only a single row, without row or column delimiters. It
can be used, for example, to dump a BLOB stored in the table to a file (SELECT
blobCol INTO DUMPFILE ...). For complete information on the INTO clause,
see the MySQL Reference
Manual. For information on reading data back from a text file, see "LOAD DATA INFILE".The FROM clause contains a list of tables from which
the rows composing the result set should be formed. At its most basic, table_references is
a comma-separated list of one or more tables, which may be assigned aliases
with or without using AS as described above for select_expression.
If you specify more than one table name, you are performing a join.
These are discussed in "Joins" below.The where_definition in
the WHERE clause sets the condition for a row to be included
in the table of results sent in response to the SELECT query.
This may be a simple condition (e.g. id=5) or a complex
expression that makes use of MySQL functions and combines multiple conditions
using Boolean operators (AND, OR, NOT).The GROUP BY clause
lets you specify one or more columns (by name, alias, or column position,
where 1 is the first column in the result set) for which rows with equal values
should be collapsed into single rows in the result set. This clause should
usually be used in combination with the MySQL grouping functions such as COUNT, MAX,
and AVG, described in "MySQL Functions",
to produce result rows that give summary information about the groups produced.
By default, the grouped results are sorted in ascending order of the grouped
column(s); however, the ASC or DESC argument
may be added following each column reference to explicitly sort the results
in ascending or descending order for that column, respectively. Results are
sorted by the first column listed, and then tying sets of rows are sorted
by the second, and so on.Note that the WHERE clause is processed before GROUP BY grouping
occurs, so conditions in the WHERE clause may not refer
to columns produced by the grouping operation. To impose conditions on the
post-grouping result set, you should use the HAVING clause instead. This clause’s syntax is identical to that
of the WHERE clause, except the conditions specified here
are processed just prior to returning the set of results, and are not optimized
in any way. For this reason, you should use the WHERE clause
whenever possible. For more information on GROUP BY and
the HAVING clause, see "Advanced SQL".The ORDER BY clause
lets you sort results according the values in one or more rows before they
are returned. As for the GROUP BY clause, each column may
be identified by a column name, alias, or position (where 1 is the first column
in the result set), and each column may have an ASC or DESC argument
to specify that sorting occurs in ascending or descending order, respectively
(ascending is the default). Rows are initially sorted by the first column
listed, and then tying sets of rows are sorted by the second, and so on.The LIMIT clause instructs the query to return only
a portion of the results it would normally generate. In the simple case, LIMIT n returns
only the first n rows of the
complete result set. You can also specify an offset by using the form LIMIT x, n. In this case, up to n rows
will be returned, beginning from the xth
row of the complete result set. The first row corresponds to x =
0, the second to x = 1 and so on.
Joins
As described above, the FROM clause
of a SELECT query lets you specify the tables that are
combined to create the result set. When multiple tables are combined in this
way, it is called a join. MySQL supports several types
of joins, as defined by the following supported syntaxes for the table_references component
of the FROM clause above:
table_ref
table_references, table_ref
table_references [CROSS] JOIN table_ref
table_references INNER JOIN table_ref join_condition
table_references STRAIGHT_JOIN table_ref
table_references LEFT [OUTER] JOIN table_ref join_condition
{ oj table_ref LEFT OUTER JOIN table_ref ON cond_expr }
table_references NATURAL [LEFT [OUTER]] JOIN table_ref
table_references RIGHT [OUTER] JOIN table_ref join_condition
table_references NATURAL [RIGHT [OUTER]] JOIN table_ref
Where table_ref is defined
as:
table_name [[AS] alias] [USE INDEX (key_list)]
[IGNORE INDEX (key_list)]
and join_condition is
defined as one of the following:
ON cond_expr
USING (column_list)
Don't be disheartened by the sheer variety of join types; I'll explain
how each of them works below.The most basic type of join, an inner join, produces
rows made up of all possible pairings of the rows from the first table with
the second. You can perform an inner join in MySQL either by separating the
table names with a comma (,) or with the words JOIN, CROSS
JOIN, or INNER JOIN (these are all equivalent).Normally, the WHERE clause of the SELECT query
is used to specify a condition to narrow down which of the combined rows are
actually returned (e.g. to match up a primary key in the first table with
a column in the second); however, when the INNER JOIN syntax
is used, the ON form of the join_condition can
play this role as well. As a final alternative, the USING (column_list) form
of join_condition lets you specify
columns that must match between the two tables. For example,
SELECT * FROM t1 INNER JOIN t2 USING (aid)
is equivalent to
SELECT * FROM t1 INNER JOIN t2 ON t1.aid = t2.aid
and also
SELECT * FROM t1, t2 WHERE t1.aid = t2.aid
STRAIGHT_JOIN works the same as an inner join, except
that the tables are processed in the order listed (left first, then right).
Normally MySQL selects the order that will produce the shortest processing
time, but if you think you know better, you can use a STRAIGHT_JOIN.The second type of join is an outer join, which is accomplished in MySQL with LEFT/RIGHT [OUTER]
JOIN (OUTER is completely optional, and has no
effect). In a LEFT outer join, any row in the left-hand
table that has no matching rows in the right-hand table (as defined by the join_condition),
will be listed as a single row in the result set. NULL values
will appear in all the columns that come from the right-hand table.The { oj ... } syntax is equivalent to a standard
left outer join; it is included for compatibility with other ODBC databases.RIGHT outer joins work in the same way as LEFT outer
joins, except in this case, it is the table on the right whose entries are
always included, even if they do not have matching entries in the left-hand
table. Since RIGHT outer joins are nonstandard, it is usually
best to stick to LEFT outer joins for cross-database compatibility.For some practical examples of outer joins and when they are useful,
see "Advanced SQL".Natural joins are kind of 'automatic',
in that they will automatically match up rows based on column names that are
found to match between the two tables. Thus, if a table called Jokes has
an AID column that refers to entries in
an Authors table whose primary key is another AID column, you can perform a join of these two tables
on that column very simply (assuming there are no other columns with identical
names in the two tables):
SELECT * FROM Jokes NATURAL JOIN Authors
