Author: paul
Date: 2006-01-27 21:30:35 +0100 (Fri, 27 Jan 2006)
New Revision: 1068
Log:
r6763@frost: paul | 2006-01-27 14:30:20 -0600
General revisions.
Modified:
trunk/
trunk/refman-4.1/sql-syntax.xml
trunk/refman-5.0/sql-syntax.xml
trunk/refman-5.1/sql-syntax.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6761
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2564
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6763
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2564
Modified: trunk/refman-4.1/sql-syntax.xml
===================================================================
--- trunk/refman-4.1/sql-syntax.xml 2006-01-27 18:33:37 UTC (rev 1067)
+++ trunk/refman-4.1/sql-syntax.xml 2006-01-27 20:30:35 UTC (rev 1068)
@@ -5865,8 +5865,8 @@
standard, and that either inserts or
<emphasis>updates</emphasis>, look for the <literal>INSERT ...
ON DUPLICATE KEY UPDATE</literal> statement; see
- <xref linkend="insert"/>. <literal>INSERT ... ON DUPLICATE KEY
- UPDATE</literal> is available as of MySQL 4.1.0.
+ <xref linkend="insert-on-duplicate"/>. <literal>INSERT ... ON
+ DUPLICATE KEY UPDATE</literal> is available as of MySQL 4.1.0.
</para>
<para>
@@ -5933,8 +5933,9 @@
</para>
<para>
- Here follows in more detail the algorithm that is used (it is
- also used with <literal>LOAD DATA ... REPLACE</literal>):
+ MySQL uses the following algorithm for
+ <literal>REPLACE</literal> (and <literal>LOAD DATA ...
+ REPLACE</literal>):
</para>
<orderedlist>
@@ -6008,7 +6009,7 @@
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING <replaceable>where_condition</replaceable>]
[ORDER BY {<replaceable>col_name</replaceable> | <replaceable>expr</replaceable> | <replaceable>position</replaceable>}
- [ASC | DESC] , ...]
+ [ASC | DESC], ...]
[LIMIT {[<replaceable>offset</replaceable>,] <replaceable>row_count</replaceable> | <replaceable>row_count</replaceable> OFFSET <replaceable>offset</replaceable>}]
[PROCEDURE <replaceable>procedure_name</replaceable>(<replaceable>argument_list</replaceable>)]
[INTO OUTFILE '<replaceable>file_name</replaceable>' <replaceable>export_options</replaceable>
@@ -6026,12 +6027,18 @@
<xref linkend="subqueries"/>.
</para>
+ <para>
+ The most commonly used clauses of <literal>SELECT</literal>
+ statements are these:
+ </para>
+
<itemizedlist>
<listitem>
<para>
Each <replaceable>select_expr</replaceable> indicates a
- column you want to retrieve.
+ column that you want to retrieve. There must be at least one
+ <replaceable>select_expr</replaceable>.
</para>
</listitem>
@@ -6045,11 +6052,20 @@
<listitem>
<para>
- <replaceable>where_condition</replaceable> consists of the
- keyword <literal>WHERE</literal> followed by an expression
- that indicates the condition or conditions that rows must
- satisfy to be selected.
+ The <literal>WHERE</literal> clause, if given, indicates the
+ condition or conditions that rows must satisfy to be
+ selected. <replaceable>where_condition</replaceable> is an
+ expression that evaluates to true for each row to be
+ selected. The statement selects all rows if there is no
+ <literal>WHERE</literal> clause.
</para>
+
+ <para>
+ In the <literal>WHERE</literal> clause, you can use any of
+ the functions and operators that MySQL supports, except for
+ aggregate (summary) functions. See
+ <xref linkend="functions"/>.
+ </para>
</listitem>
</itemizedlist>
@@ -6070,13 +6086,45 @@
-> 2
</programlisting>
+ <remark role="help-topic" condition="DUAL"/>
+
+ <remark role="help-description-begin"/>
+
<para>
- All clauses used must be given in exactly the order shown in the
- syntax description. For example, a <literal>HAVING</literal>
- clause must come after any <literal>GROUP BY</literal> clause
- and before any <literal>ORDER BY</literal> clause.
+ <indexterm>
+ <primary><literal>DUAL</literal></primary>
+ </indexterm>
+
+ From MySQL 4.1.0 on, you are allowed to specify
+ <literal>DUAL</literal> as a dummy table name in situations
+ where no tables are referenced:
</para>
+<programlisting>
+mysql> <userinput>SELECT 1 + 1 FROM DUAL;</userinput>
+ -> 2
+</programlisting>
+
+ <para>
+ <literal>DUAL</literal> is purely for compatibility with some
+ other database servers that require a <literal>FROM</literal>
+ clause. MySQL does not require the clause if no tables are
+ referenced.
+ </para>
+
+ <remark role="help-description-end"/>
+
+ <para>
+ In general, clauses used must be given in exactly the order
+ shown in the syntax description. For example, a
+ <literal>HAVING</literal> clause must come after any
+ <literal>GROUP BY</literal> clause and before any <literal>ORDER
+ BY</literal> clause. The exception is that the
+ <literal>INTO</literal> clause can appear either as shown in the
+ syntax description or immediately preceding the
+ <literal>FROM</literal> clause.
+ </para>
+
<itemizedlist>
<listitem>
@@ -6099,8 +6147,8 @@
</para>
<programlisting>
-mysql> <userinput>SELECT CONCAT(last_name,', ',first_name) AS full_name</userinput>
- -> <userinput>FROM mytable ORDER BY full_name;</userinput>
+SELECT CONCAT(last_name,', ',first_name) AS full_name
+ FROM mytable ORDER BY full_name;
</programlisting>
<para>
@@ -6110,13 +6158,13 @@
</para>
<programlisting>
-mysql> <userinput>SELECT CONCAT(last_name,', ',first_name) full_name</userinput>
- -> <userinput>FROM mytable ORDER BY full_name;</userinput>
+SELECT CONCAT(last_name,', ',first_name) full_name
+ FROM mytable ORDER BY full_name;
</programlisting>
<para>
- Because the <literal>AS</literal> is optional, a subtle
- problem can occur if you forget the comma between two
+ However, because the <literal>AS</literal> is optional, a
+ subtle problem can occur if you forget the comma between two
<replaceable>select_expr</replaceable> expressions: MySQL
interprets the second as an alias name. For example, in the
following statement, <literal>columnb</literal> is treated
@@ -6124,13 +6172,13 @@
</para>
<programlisting>
-mysql> <userinput>SELECT columna columnb FROM mytable;</userinput>
+SELECT columna columnb FROM mytable;
</programlisting>
<para>
For this reason, it is good practice to be in the habit of
- specifying column aliases explicitly using
- <literal>AS</literal>.
+ using <literal>AS</literal> explicitly when specifying
+ column aliases.
</para>
</listitem>
@@ -6180,10 +6228,11 @@
<para>
The <literal>FROM
<replaceable>table_references</replaceable></literal> 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 <xref linkend="join"/>. For
- each table specified, you can optionally specify an alias.
+ 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 <xref linkend="join"/>.
+ For each table specified, you can optionally specify an
+ alias.
</para>
<programlisting>
@@ -6211,15 +6260,15 @@
In MySQL 4.0.14, you can use <literal>SET
max_seeks_for_key=<replaceable>value</replaceable></literal>
as an alternative way to force MySQL to prefer key scans
- instead of table scans.
+ instead of table scans. See
+ <xref linkend="server-system-variables"/>.
</para>
</listitem>
<listitem>
<para>
You can refer to a table within the default database as
- <replaceable>tbl_name</replaceable> (within the current
- database), or as
+ <replaceable>tbl_name</replaceable>, or as
<replaceable>db_name</replaceable>.<replaceable>tbl_name</replaceable>
to specify a database explicitly. You can refer to a column
as <replaceable>col_name</replaceable>,
@@ -6237,10 +6286,6 @@
</listitem>
<listitem>
- <remark role="help-topic" condition="DUAL"/>
-
- <remark role="help-description-begin"/>
-
<para>
<indexterm>
<primary>aliases</primary>
@@ -6251,38 +6296,6 @@
<primary>table aliases</primary>
</indexterm>
- <indexterm>
- <primary><literal>DUAL</literal></primary>
- </indexterm>
-
- From MySQL 4.1.0 on, you are allowed to specify
- <literal>DUAL</literal> as a dummy table name in situations
- where no tables are referenced:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT 1 + 1 FROM DUAL;</userinput>
- -> 2
-</programlisting>
-
- <para>
- <literal>DUAL</literal> is purely for compatibility with
- some other servers that require a <literal>FROM</literal>
- clause. MySQL does not require the clause if no tables are
- referenced, and the preceding statement can be written this
- way:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT 1 + 1;</userinput>
- -> 2
-</programlisting>
-
- <remark role="help-description-end"/>
- </listitem>
-
- <listitem>
- <para>
A table reference can be aliased using
<literal><replaceable>tbl_name</replaceable> AS
<replaceable>alias_name</replaceable></literal> or
@@ -6290,27 +6303,20 @@
</para>
<programlisting>
-mysql> <userinput>SELECT t1.name, t2.salary FROM employee AS t1, info AS t2</userinput>
- -> <userinput>WHERE t1.name = t2.name;</userinput>
-mysql> <userinput>SELECT t1.name, t2.salary FROM employee t1, info t2</userinput>
- -> <userinput>WHERE t1.name = t2.name;</userinput>
+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;
</programlisting>
</listitem>
<listitem>
<para>
- In the <literal>WHERE</literal> clause, you can use any of
- the functions that MySQL supports, except for aggregate
- (summary) functions. See <xref linkend="functions"/>.
- </para>
+ <indexterm type="function">
+ <primary>ORDER BY</primary>
+ </indexterm>
- <indexterm type="function">
- <primary>ORDER BY</primary>
- </indexterm>
- </listitem>
-
- <listitem>
- <para>
Columns selected for output can be referred to in
<literal>ORDER BY</literal> and <literal>GROUP BY</literal>
clauses using column names, column aliases, or column
@@ -6318,12 +6324,14 @@
</para>
<programlisting>
-mysql> <userinput>SELECT college, region, seed FROM tournament</userinput>
- -> <userinput>ORDER BY region, seed;</userinput>
-mysql> <userinput>SELECT college, region AS r, seed AS s FROM tournament</userinput>
- -> <userinput>ORDER BY r, s;</userinput>
-mysql> <userinput>SELECT college, region, seed FROM tournament</userinput>
- -> <userinput>ORDER BY 2, 3;</userinput>
+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;
</programlisting>
<para>
@@ -6338,20 +6346,42 @@
Use of column positions is deprecated because the syntax has
been removed from the SQL standard.
</para>
-
- <indexterm>
- <primary><literal>GROUP BY</literal></primary>
- <secondary>extensions to standard SQL</secondary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm>
+ <primary>ORDER BY</primary>
+ <secondary>NULL</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>NULL</primary>
+ <secondary>ORDER BY</secondary>
+ </indexterm>
+
If you use <literal>GROUP BY</literal>, output rows are
sorted according to the <literal>GROUP BY</literal> columns
as if you had an <literal>ORDER BY</literal> for the same
- columns. MySQL has extended the <literal>GROUP BY</literal>
- clause as of version 3.23.34 so that you can also specify
+ columns. To avoid the overhead of sorting that
+ <literal>GROUP BY</literal> produces, add <literal>ORDER BY
+ NULL</literal>:
+ </para>
+
+<programlisting>
+SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary><literal>GROUP BY</literal></primary>
+ <secondary>extensions to standard SQL</secondary>
+ </indexterm>
+
+ MySQL extends the <literal>GROUP BY</literal> clause as of
+ version 3.23.34 so that you can also specify
<literal>ASC</literal> and <literal>DESC</literal> after
columns named in the clause:
</para>
@@ -6366,7 +6396,7 @@
MySQL extends the use of <literal>GROUP BY</literal> to
allow selecting fields that are not mentioned in the
<literal>GROUP BY</literal> clause. If you are not getting
- the results you expect from your query, please read the
+ the results that you expect from your query, please read the
description of <literal>GROUP BY</literal> found in
<xref linkend="group-by-functions-and-modifiers"/>.
</para>
@@ -6378,14 +6408,14 @@
<literal>WITH ROLLUP</literal> modifier. See
<xref linkend="group-by-modifiers"/>.
</para>
-
- <indexterm type="function">
- <primary>HAVING</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>HAVING</primary>
+ </indexterm>
+
The <literal>HAVING</literal> clause is applied nearly last,
just before items are sent to the client, with no
optimization. (<literal>LIMIT</literal> is applied after
@@ -6411,7 +6441,7 @@
</para>
<programlisting>
-mysql> <userinput>SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> HAVING <replaceable>col_name</replaceable> > 0;</userinput>
+SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> HAVING <replaceable>col_name</replaceable> > 0;
</programlisting>
<para>
@@ -6419,7 +6449,7 @@
</para>
<programlisting>
-mysql> <userinput>SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>col_name</replaceable> > 0;</userinput>
+SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>col_name</replaceable> > 0;
</programlisting>
</listitem>
@@ -6430,8 +6460,8 @@
</para>
<programlisting>
-mysql> <userinput>SELECT user, MAX(salary) FROM users</userinput>
- -> <userinput>GROUP BY user HAVING MAX(salary)>10;</userinput>
+SELECT user, MAX(salary) FROM users
+ GROUP BY user HAVING MAX(salary) > 10;
</programlisting>
<para>
@@ -6442,17 +6472,17 @@
</para>
<programlisting>
-mysql> <userinput>SELECT user, MAX(salary) AS max_salary FROM users</userinput>
- -> <userinput>GROUP BY user HAVING max_salary>10;</userinput>
+SELECT user, MAX(salary) AS max_salary FROM users
+ GROUP BY user HAVING max_salary>10;
</programlisting>
-
- <indexterm type="function">
- <primary>LIMIT</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>LIMIT</primary>
+ </indexterm>
+
The <literal>LIMIT</literal> clause can be used to constrain
the number of rows returned by the <literal>SELECT</literal>
statement. <literal>LIMIT</literal> takes one or two numeric
@@ -6468,16 +6498,10 @@
</para>
<programlisting>
-mysql> <userinput>SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15</userinput>
+SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
</programlisting>
<para>
- For compatibility with PostgreSQL, MySQL also supports the
- <literal>LIMIT <replaceable>row_count</replaceable> OFFSET
- <replaceable>offset</replaceable></literal> syntax.
- </para>
-
- <para>
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
@@ -6485,7 +6509,7 @@
</para>
<programlisting>
-mysql> <userinput>SELECT * FROM tbl LIMIT 95,18446744073709551615;</userinput>
+SELECT * FROM tbl LIMIT 95,18446744073709551615;
</programlisting>
<para>
@@ -6494,12 +6518,14 @@
</para>
<programlisting>
-mysql> <userinput>SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows</userinput>
+SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows
</programlisting>
<para>
- In other words, <literal>LIMIT n</literal> is equivalent to
- <literal>LIMIT 0,n</literal>.
+ In other words, <literal>LIMIT
+ <replaceable>row_count</replaceable></literal> is equivalent
+ to <literal>LIMIT 0,
+ <replaceable>row_count</replaceable></literal>.
</para>
<para>
@@ -6509,9 +6535,9 @@
</para>
<programlisting>
-mysql> <userinput>SET @a=1;</userinput>
-mysql> <userinput>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";</userinput>
-mysql> <userinput>EXECUTE STMT USING @a;</userinput>
+SET @a=1;
+PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
+EXECUTE STMT USING @a;
</programlisting>
<para>
@@ -6520,10 +6546,16 @@
</para>
<programlisting>
-mysql> <userinput>SET @skip=1; SET @numrows=5;</userinput>
-mysql> <userinput>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";</userinput>
-mysql> <userinput>EXECUTE STMT USING @skip, @numrows;</userinput>
+SET @skip=1; SET @numrows=5;
+PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
+EXECUTE STMT USING @skip, @numrows;
</programlisting>
+
+ <para>
+ For compatibility with PostgreSQL, MySQL also supports the
+ <literal>LIMIT <replaceable>row_count</replaceable> OFFSET
+ <replaceable>offset</replaceable></literal> syntax.
+ </para>
</listitem>
<listitem>
@@ -6549,20 +6581,21 @@
<para>
The <literal>SELECT ... INTO OUTFILE</literal> statement is
- intended primarily to let you very quickly dump a table on
- the server machine. If you want to create the resulting file
- on some client host other than the server host, you cannot
- use <literal>SELECT ... INTO OUTFILE</literal>. In that
- case, you should instead use some command like
- <literal>mysql -e "SELECT ..." > file_name</literal> on
- the client host to generate the file.
+ 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 <literal>SELECT ... INTO
+ OUTFILE</literal>. In that case, you should instead use a
+ command such as <literal>mysql -e "SELECT ..." >
+ <replaceable>file_name</replaceable></literal> to generate
+ the file on the client host.
</para>
<para>
<literal>SELECT ... INTO OUTFILE</literal> is the complement
of <literal>LOAD DATA INFILE</literal>; the syntax for the
- <literal>export_options</literal> part of the statement
- consists of the same <literal>FIELDS</literal> and
+ <replaceable>export_options</replaceable> part of the
+ statement consists of the same <literal>FIELDS</literal> and
<literal>LINES</literal> clauses that are used with the
<literal>LOAD DATA INFILE</literal> statement. See
<xref linkend="load-data"/>.
@@ -6571,8 +6604,8 @@
<para>
<literal>FIELDS ESCAPED BY</literal> controls how to write
special characters. If the <literal>FIELDS ESCAPED
- BY</literal> character is not empty, it is used to prefix
- the following characters on output:
+ BY</literal> character is not empty, it is used as a prefix
+ that precedes following characters on output:
</para>
<itemizedlist>
@@ -6600,16 +6633,31 @@
<listitem>
<para>
- ASCII <literal>0</literal> (what is actually written
- following the escape character is ASCII
- ‘<literal>0</literal>’, not a zero-valued
- byte)
+ ASCII <literal>NUL</literal> (the zero-valued byte; what
+ is actually written following the escape character is
+ ASCII ‘<literal>0</literal>’, not a
+ zero-valued byte)
</para>
</listitem>
</itemizedlist>
<para>
+ The <literal>FIELDS TERMINATED BY</literal>,
+ <literal>ENCLOSED BY</literal>, <literal>ESCAPED
+ BY</literal>, or <literal>LINES TERMINATED BY</literal>
+ characters <emphasis>must</emphasis> be escaped so that you
+ can read the file back in reliably. ASCII
+ <literal>NUL</literal> is escaped to make it easier to view
+ with some pagers.
+ </para>
+
+ <para>
+ The resulting file does not have to conform to SQL syntax,
+ so nothing else need be escaped.
+ </para>
+
+ <para>
If the <literal>FIELDS ESCAPED BY</literal> character is
empty, no characters are escaped and <literal>NULL</literal>
is output as <literal>NULL</literal>, not
@@ -6619,40 +6667,33 @@
list just given.
</para>
- <para>
- The reason for the above is that you
- <emphasis>must</emphasis> escape any <literal>FIELDS
- TERMINATED BY</literal>, <literal>ENCLOSED BY</literal>,
- <literal>ESCAPED BY</literal>, or <literal>LINES TERMINATED
- BY</literal> characters to read the file back reliably.
- ASCII <literal>NUL</literal> is escaped to make it easier to
- view with some pagers.
- </para>
+ <indexterm>
+ <primary>CSV data, reading</primary>
+ </indexterm>
- <para>
- The resulting file does not have to conform to SQL syntax,
- so nothing else need be escaped.
- </para>
+ <indexterm>
+ <primary>comma-separate values data, reading</primary>
+ </indexterm>
<para>
Here is an example that produces a file in the
- comma-separated values format used by many programs:
+ comma-separated values (CSV) format used by many programs:
</para>
<programlisting>
-SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
-FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-LINES TERMINATED BY '\n'
-FROM test_table;
+SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
+ FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
+ LINES TERMINATED BY '\n'
+ FROM test_table;
</programlisting>
-
- <indexterm type="function">
- <primary>DUMPFILE</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>DUMPFILE</primary>
+ </indexterm>
+
If you use <literal>INTO DUMPFILE</literal> instead of
<literal>INTO OUTFILE</literal>, MySQL writes only one row
into the file, without any column or line termination and
@@ -6668,9 +6709,9 @@
DUMPFILE</literal> 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
+ under whose account it is running. (You should
<emphasis>never</emphasis> run <command>mysqld</command> as
- <literal>root</literal> for this and other reasons). The
+ <literal>root</literal> for this and other reasons.) The
file thus must be world-writable so that you can manipulate
its contents.
</para>
@@ -6678,7 +6719,7 @@
<listitem>
<para>
- The <literal>SELECT</literal> syntax diagram at the
+ The <literal>SELECT</literal> syntax description at the
beginning this section shows the <literal>INTO</literal>
clause near the end of the statement. It is also possible to
use <literal>INTO OUTFILE</literal> or <literal>INTO
@@ -6709,12 +6750,12 @@
<primary>LOCK IN SHARE MODE</primary>
</indexterm>
- If you use <literal>FOR UPDATE</literal> on 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
- <literal>LOCK IN SHARE MODE</literal> sets a shared lock
- that prevents other transactions from updating or deleting
- the examined rows. See
+ If you use <literal>FOR UPDATE</literal> 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 <literal>LOCK IN SHARE MODE</literal>
+ sets a shared lock that allows other transactions to read
+ the examined rows but not to update or delete them. See
<xref linkend="innodb-locking-reads"/>.
</para>
</listitem>
@@ -6744,8 +6785,8 @@
rows should be returned. If none of these options are given, the
default is <literal>ALL</literal> (all matching rows are
returned). <literal>DISTINCT</literal> and
- <literal>DISTINCTROW</literal> are synonyms and specify that
- duplicate rows in the result set should be removed.
+ <literal>DISTINCTROW</literal> are synonyms and specify removal
+ of duplicate rows from the result set.
</para>
<para>
@@ -6776,18 +6817,18 @@
<literal>SELECT</literal> statements that are part of a
<literal>UNION</literal>.
</para>
-
- <indexterm>
- <primary>hints</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>STRAIGHT_JOIN</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm>
+ <primary>hints</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>STRAIGHT_JOIN</primary>
+ </indexterm>
+
<literal>STRAIGHT_JOIN</literal> forces the optimizer to
join the tables in the order in which they are listed in the
<literal>FROM</literal> clause. You can use this to speed up
@@ -6797,43 +6838,42 @@
<replaceable>table_references</replaceable> list. See
<xref linkend="join"/>.
</para>
-
- <indexterm type="function">
- <primary>SQL_BIG_RESULT</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_BIG_RESULT</primary>
+ </indexterm>
+
<literal>SQL_BIG_RESULT</literal> can be used with
<literal>GROUP BY</literal> or <literal>DISTINCT</literal>
to tell the optimizer that the result set has many rows. In
this case, MySQL directly uses disk-based temporary tables
- if needed. MySQL also, in this case, prefers sorting to
- using a temporary table with a key on the <literal>GROUP
- BY</literal> elements.
+ if needed, and prefers sorting to using a temporary table
+ with a key on the <literal>GROUP BY</literal> elements.
</para>
-
- <indexterm type="function">
- <primary>SQL_BUFFER_RESULT</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_BUFFER_RESULT</primary>
+ </indexterm>
+
<literal>SQL_BUFFER_RESULT</literal> forces the result to be
put into a temporary table. This helps MySQL free the table
locks early and helps in cases where it takes a long time to
send the result set to the client.
</para>
-
- <indexterm type="function">
- <primary>SQL_SMALL_RESULT</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_SMALL_RESULT</primary>
+ </indexterm>
+
<literal>SQL_SMALL_RESULT</literal> can be used with
<literal>GROUP BY</literal> or <literal>DISTINCT</literal>
to tell the optimizer that the result set is small. In this
@@ -6841,14 +6881,14 @@
resulting table instead of using sorting. In MySQL 3.23 and
up, this should not normally be needed.
</para>
-
- <indexterm type="function">
- <primary>SQL_CALC_FOUND_ROWS</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_CALC_FOUND_ROWS</primary>
+ </indexterm>
+
<literal>SQL_CALC_FOUND_ROWS</literal> (available in MySQL
4.0.0 and up) tells MySQL to calculate how many rows there
would be in the result set, disregarding any
@@ -6863,14 +6903,14 @@
instantly (resulting in a row count of 0). See
<xref linkend="limit-optimization"/>.
</para>
-
- <indexterm type="function">
- <primary>SQL_CACHE</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_CACHE</primary>
+ </indexterm>
+
<literal>SQL_CACHE</literal> tells MySQL to store the query
result in the query cache if you are using a
<literal>query_cache_type</literal> value of
@@ -6879,14 +6919,14 @@
option effects any <literal>SELECT</literal> in the query.
See <xref linkend="query-cache"/>.
</para>
-
- <indexterm type="function">
- <primary>SQL_NO_CACHE</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_NO_CACHE</primary>
+ </indexterm>
+
<literal>SQL_NO_CACHE</literal> tells MySQL not to store the
query result in the query cache. See
<xref linkend="query-cache"/>. For a query that uses
Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml 2006-01-27 18:33:37 UTC (rev 1067)
+++ trunk/refman-5.0/sql-syntax.xml 2006-01-27 20:30:35 UTC (rev 1068)
@@ -5853,7 +5853,7 @@
standard, and that either inserts or
<emphasis>updates</emphasis>, look for the <literal>INSERT ...
ON DUPLICATE KEY UPDATE</literal> statement; see
- <xref linkend="insert"/>.
+ <xref linkend="insert-on-duplicate"/>.
</para>
<para>
@@ -5920,8 +5920,9 @@
</para>
<para>
- Here follows in more detail the algorithm that is used (it is
- also used with <literal>LOAD DATA ... REPLACE</literal>):
+ MySQL uses the following algorithm for
+ <literal>REPLACE</literal> (and <literal>LOAD DATA ...
+ REPLACE</literal>):
</para>
<orderedlist>
@@ -5995,7 +5996,7 @@
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING <replaceable>where_condition</replaceable>]
[ORDER BY {<replaceable>col_name</replaceable> | <replaceable>expr</replaceable> | <replaceable>position</replaceable>}
- [ASC | DESC] , ...]
+ [ASC | DESC], ...]
[LIMIT {[<replaceable>offset</replaceable>,] <replaceable>row_count</replaceable> | <replaceable>row_count</replaceable> OFFSET <replaceable>offset</replaceable>}]
[PROCEDURE <replaceable>procedure_name</replaceable>(<replaceable>argument_list</replaceable>)]
[INTO OUTFILE '<replaceable>file_name</replaceable>' <replaceable>export_options</replaceable>
@@ -6012,12 +6013,18 @@
<xref linkend="subqueries"/>.
</para>
+ <para>
+ The most commonly used clauses of <literal>SELECT</literal>
+ statements are these:
+ </para>
+
<itemizedlist>
<listitem>
<para>
Each <replaceable>select_expr</replaceable> indicates a
- column you want to retrieve.
+ column that you want to retrieve. There must be at least one
+ <replaceable>select_expr</replaceable>.
</para>
</listitem>
@@ -6031,11 +6038,20 @@
<listitem>
<para>
- <replaceable>where_condition</replaceable> consists of the
- keyword <literal>WHERE</literal> followed by an expression
- that indicates the condition or conditions that rows must
- satisfy to be selected.
+ The <literal>WHERE</literal> clause, if given, indicates the
+ condition or conditions that rows must satisfy to be
+ selected. <replaceable>where_condition</replaceable> is an
+ expression that evaluates to true for each row to be
+ selected. The statement selects all rows if there is no
+ <literal>WHERE</literal> clause.
</para>
+
+ <para>
+ In the <literal>WHERE</literal> clause, you can use any of
+ the functions and operators that MySQL supports, except for
+ aggregate (summary) functions. See
+ <xref linkend="functions"/>.
+ </para>
</listitem>
</itemizedlist>
@@ -6056,13 +6072,44 @@
-> 2
</programlisting>
+ <remark role="help-topic" condition="DUAL"/>
+
+ <remark role="help-description-begin"/>
+
<para>
- All clauses used must be given in exactly the order shown in the
- syntax description. For example, a <literal>HAVING</literal>
- clause must come after any <literal>GROUP BY</literal> clause
- and before any <literal>ORDER BY</literal> clause.
+ <indexterm>
+ <primary><literal>DUAL</literal></primary>
+ </indexterm>
+
+ You are allowed to specify <literal>DUAL</literal> as a dummy
+ table name in situations where no tables are referenced:
</para>
+<programlisting>
+mysql> <userinput>SELECT 1 + 1 FROM DUAL;</userinput>
+ -> 2
+</programlisting>
+
+ <para>
+ <literal>DUAL</literal> is purely for compatibility with some
+ other database servers that require a <literal>FROM</literal>
+ clause. MySQL does not require the clause if no tables are
+ referenced.
+ </para>
+
+ <remark role="help-description-end"/>
+
+ <para>
+ In general, clauses used must be given in exactly the order
+ shown in the syntax description. For example, a
+ <literal>HAVING</literal> clause must come after any
+ <literal>GROUP BY</literal> clause and before any <literal>ORDER
+ BY</literal> clause. The exception is that the
+ <literal>INTO</literal> clause can appear either as shown in the
+ syntax description or immediately preceding the
+ <literal>FROM</literal> clause.
+ </para>
+
<itemizedlist>
<listitem>
@@ -6085,8 +6132,8 @@
</para>
<programlisting>
-mysql> <userinput>SELECT CONCAT(last_name,', ',first_name) AS full_name</userinput>
- -> <userinput>FROM mytable ORDER BY full_name;</userinput>
+SELECT CONCAT(last_name,', ',first_name) AS full_name
+ FROM mytable ORDER BY full_name;
</programlisting>
<para>
@@ -6096,13 +6143,13 @@
</para>
<programlisting>
-mysql> <userinput>SELECT CONCAT(last_name,', ',first_name) full_name</userinput>
- -> <userinput>FROM mytable ORDER BY full_name;</userinput>
+SELECT CONCAT(last_name,', ',first_name) full_name
+ FROM mytable ORDER BY full_name;
</programlisting>
<para>
- Because the <literal>AS</literal> is optional, a subtle
- problem can occur if you forget the comma between two
+ However, because the <literal>AS</literal> is optional, a
+ subtle problem can occur if you forget the comma between two
<replaceable>select_expr</replaceable> expressions: MySQL
interprets the second as an alias name. For example, in the
following statement, <literal>columnb</literal> is treated
@@ -6110,13 +6157,13 @@
</para>
<programlisting>
-mysql> <userinput>SELECT columna columnb FROM mytable;</userinput>
+SELECT columna columnb FROM mytable;
</programlisting>
<para>
For this reason, it is good practice to be in the habit of
- specifying column aliases explicitly using
- <literal>AS</literal>.
+ using <literal>AS</literal> explicitly when specifying
+ column aliases.
</para>
</listitem>
@@ -6166,10 +6213,11 @@
<para>
The <literal>FROM
<replaceable>table_references</replaceable></literal> 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 <xref linkend="join"/>. For
- each table specified, you can optionally specify an alias.
+ 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 <xref linkend="join"/>.
+ For each table specified, you can optionally specify an
+ alias.
</para>
<programlisting>
@@ -6197,15 +6245,15 @@
You can use <literal>SET
max_seeks_for_key=<replaceable>value</replaceable></literal>
as an alternative way to force MySQL to prefer key scans
- instead of table scans.
+ instead of table scans. See
+ <xref linkend="server-system-variables"/>.
</para>
</listitem>
<listitem>
<para>
You can refer to a table within the default database as
- <replaceable>tbl_name</replaceable> (within the current
- database), or as
+ <replaceable>tbl_name</replaceable>, or as
<replaceable>db_name</replaceable>.<replaceable>tbl_name</replaceable>
to specify a database explicitly. You can refer to a column
as <replaceable>col_name</replaceable>,
@@ -6223,10 +6271,6 @@
</listitem>
<listitem>
- <remark role="help-topic" condition="DUAL"/>
-
- <remark role="help-description-begin"/>
-
<para>
<indexterm>
<primary>aliases</primary>
@@ -6237,38 +6281,6 @@
<primary>table aliases</primary>
</indexterm>
- <indexterm>
- <primary><literal>DUAL</literal></primary>
- </indexterm>
-
- You are allowed to specify <literal>DUAL</literal> as a
- dummy table name in situations where no tables are
- referenced:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT 1 + 1 FROM DUAL;</userinput>
- -> 2
-</programlisting>
-
- <para>
- <literal>DUAL</literal> is purely for compatibility with
- some other servers that require a <literal>FROM</literal>
- clause. MySQL does not require the clause if no tables are
- referenced, and the preceding statement can be written this
- way:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT 1 + 1;</userinput>
- -> 2
-</programlisting>
-
- <remark role="help-description-end"/>
- </listitem>
-
- <listitem>
- <para>
A table reference can be aliased using
<literal><replaceable>tbl_name</replaceable> AS
<replaceable>alias_name</replaceable></literal> or
@@ -6276,27 +6288,20 @@
</para>
<programlisting>
-mysql> <userinput>SELECT t1.name, t2.salary FROM employee AS t1, info AS t2</userinput>
- -> <userinput>WHERE t1.name = t2.name;</userinput>
-mysql> <userinput>SELECT t1.name, t2.salary FROM employee t1, info t2</userinput>
- -> <userinput>WHERE t1.name = t2.name;</userinput>
+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;
</programlisting>
</listitem>
<listitem>
<para>
- In the <literal>WHERE</literal> clause, you can use any of
- the functions that MySQL supports, except for aggregate
- (summary) functions. See <xref linkend="functions"/>.
- </para>
+ <indexterm type="function">
+ <primary>ORDER BY</primary>
+ </indexterm>
- <indexterm type="function">
- <primary>ORDER BY</primary>
- </indexterm>
- </listitem>
-
- <listitem>
- <para>
Columns selected for output can be referred to in
<literal>ORDER BY</literal> and <literal>GROUP BY</literal>
clauses using column names, column aliases, or column
@@ -6304,12 +6309,14 @@
</para>
<programlisting>
-mysql> <userinput>SELECT college, region, seed FROM tournament</userinput>
- -> <userinput>ORDER BY region, seed;</userinput>
-mysql> <userinput>SELECT college, region AS r, seed AS s FROM tournament</userinput>
- -> <userinput>ORDER BY r, s;</userinput>
-mysql> <userinput>SELECT college, region, seed FROM tournament</userinput>
- -> <userinput>ORDER BY 2, 3;</userinput>
+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;
</programlisting>
<para>
@@ -6324,35 +6331,56 @@
Use of column positions is deprecated because the syntax has
been removed from the SQL standard.
</para>
-
- <indexterm>
- <primary><literal>GROUP BY</literal></primary>
- <secondary>extensions to standard SQL</secondary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm>
+ <primary>ORDER BY</primary>
+ <secondary>NULL</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>NULL</primary>
+ <secondary>ORDER BY</secondary>
+ </indexterm>
+
If you use <literal>GROUP BY</literal>, output rows are
sorted according to the <literal>GROUP BY</literal> columns
as if you had an <literal>ORDER BY</literal> for the same
- columns. MySQL extends the <literal>GROUP BY</literal>
- clause so that you can also specify <literal>ASC</literal>
- and <literal>DESC</literal> after columns named in the
- clause:
+ columns. To avoid the overhead of sorting that
+ <literal>GROUP BY</literal> produces, add <literal>ORDER BY
+ NULL</literal>:
</para>
<programlisting>
-SELECT a, COUNT(b) FROM test_table GROUP BY a DESC
+SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
</programlisting>
</listitem>
<listitem>
<para>
+ <indexterm>
+ <primary><literal>GROUP BY</literal></primary>
+ <secondary>extensions to standard SQL</secondary>
+ </indexterm>
+
+ MySQL extends the <literal>GROUP BY</literal> clause so that
+ you can also specify <literal>ASC</literal> and
+ <literal>DESC</literal> after columns named in the clause:
+ </para>
+
+<programlisting>
+SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
MySQL extends the use of <literal>GROUP BY</literal> to
allow selecting fields that are not mentioned in the
<literal>GROUP BY</literal> clause. If you are not getting
- the results you expect from your query, please read the
+ the results that you expect from your query, please read the
description of <literal>GROUP BY</literal> found in
<xref linkend="group-by-functions-and-modifiers"/>.
</para>
@@ -6364,14 +6392,14 @@
ROLLUP</literal> modifier. See
<xref linkend="group-by-modifiers"/>.
</para>
-
- <indexterm type="function">
- <primary>HAVING</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>HAVING</primary>
+ </indexterm>
+
The <literal>HAVING</literal> clause is applied nearly last,
just before items are sent to the client, with no
optimization. (<literal>LIMIT</literal> is applied after
@@ -6412,15 +6440,15 @@
</para>
<programlisting>
-mysql> <userinput>SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;</userinput>
+SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
</programlisting>
<para>
- Preference is given to standard SQL behavior, so that if a
+ Preference is given to standard SQL behavior, so if a
<literal>HAVING</literal> column name is used both in
<literal>GROUP BY</literal> and as an aliased column in the
- output column list, preferences is given to the column in
- the <literal>GROUP BY</literal> column.
+ output column list, preference is given to the column in the
+ <literal>GROUP BY</literal> column.
</para>
</listitem>
@@ -6432,7 +6460,7 @@
</para>
<programlisting>
-mysql> <userinput>SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> HAVING <replaceable>col_name</replaceable> > 0;</userinput>
+SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> HAVING <replaceable>col_name</replaceable> > 0;
</programlisting>
<para>
@@ -6440,7 +6468,7 @@
</para>
<programlisting>
-mysql> <userinput>SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>col_name</replaceable> > 0;</userinput>
+SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>col_name</replaceable> > 0;
</programlisting>
</listitem>
@@ -6451,21 +6479,21 @@
</para>
<programlisting>
-mysql> <userinput>SELECT user, MAX(salary) FROM users</userinput>
- -> <userinput>GROUP BY user HAVING MAX(salary)>10;</userinput>
+SELECT user, MAX(salary) FROM users
+ GROUP BY user HAVING MAX(salary) > 10;
</programlisting>
<para>
(This did not work in some older versions of MySQL.)
</para>
-
- <indexterm type="function">
- <primary>LIMIT</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>LIMIT</primary>
+ </indexterm>
+
The <literal>LIMIT</literal> clause can be used to constrain
the number of rows returned by the <literal>SELECT</literal>
statement. <literal>LIMIT</literal> takes one or two numeric
@@ -6481,16 +6509,10 @@
</para>
<programlisting>
-mysql> <userinput>SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15</userinput>
+SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
</programlisting>
<para>
- For compatibility with PostgreSQL, MySQL also supports the
- <literal>LIMIT <replaceable>row_count</replaceable> OFFSET
- <replaceable>offset</replaceable></literal> syntax.
- </para>
-
- <para>
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
@@ -6498,7 +6520,7 @@
</para>
<programlisting>
-mysql> <userinput>SELECT * FROM tbl LIMIT 95,18446744073709551615;</userinput>
+SELECT * FROM tbl LIMIT 95,18446744073709551615;
</programlisting>
<para>
@@ -6507,12 +6529,14 @@
</para>
<programlisting>
-mysql> <userinput>SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows</userinput>
+SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows
</programlisting>
<para>
- In other words, <literal>LIMIT n</literal> is equivalent to
- <literal>LIMIT 0,n</literal>.
+ In other words, <literal>LIMIT
+ <replaceable>row_count</replaceable></literal> is equivalent
+ to <literal>LIMIT 0,
+ <replaceable>row_count</replaceable></literal>.
</para>
<para>
@@ -6522,9 +6546,9 @@
</para>
<programlisting>
-mysql> <userinput>SET @a=1;</userinput>
-mysql> <userinput>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";</userinput>
-mysql> <userinput>EXECUTE STMT USING @a;</userinput>
+SET @a=1;
+PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
+EXECUTE STMT USING @a;
</programlisting>
<para>
@@ -6533,10 +6557,16 @@
</para>
<programlisting>
-mysql> <userinput>SET @skip=1; SET @numrows=5;</userinput>
-mysql> <userinput>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";</userinput>
-mysql> <userinput>EXECUTE STMT USING @skip, @numrows;</userinput>
+SET @skip=1; SET @numrows=5;
+PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
+EXECUTE STMT USING @skip, @numrows;
</programlisting>
+
+ <para>
+ For compatibility with PostgreSQL, MySQL also supports the
+ <literal>LIMIT <replaceable>row_count</replaceable> OFFSET
+ <replaceable>offset</replaceable></literal> syntax.
+ </para>
</listitem>
<listitem>
@@ -6562,20 +6592,21 @@
<para>
The <literal>SELECT ... INTO OUTFILE</literal> statement is
- intended primarily to let you very quickly dump a table on
- the server machine. If you want to create the resulting file
- on some client host other than the server host, you cannot
- use <literal>SELECT ... INTO OUTFILE</literal>. In that
- case, you should instead use some command like
- <literal>mysql -e "SELECT ..." > file_name</literal> on
- the client host to generate the file.
+ 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 <literal>SELECT ... INTO
+ OUTFILE</literal>. In that case, you should instead use a
+ command such as <literal>mysql -e "SELECT ..." >
+ <replaceable>file_name</replaceable></literal> to generate
+ the file on the client host.
</para>
<para>
<literal>SELECT ... INTO OUTFILE</literal> is the complement
of <literal>LOAD DATA INFILE</literal>; the syntax for the
- <literal>export_options</literal> part of the statement
- consists of the same <literal>FIELDS</literal> and
+ <replaceable>export_options</replaceable> part of the
+ statement consists of the same <literal>FIELDS</literal> and
<literal>LINES</literal> clauses that are used with the
<literal>LOAD DATA INFILE</literal> statement. See
<xref linkend="load-data"/>.
@@ -6584,8 +6615,8 @@
<para>
<literal>FIELDS ESCAPED BY</literal> controls how to write
special characters. If the <literal>FIELDS ESCAPED
- BY</literal> character is not empty, it is used to prefix
- the following characters on output:
+ BY</literal> character is not empty, it is used as a prefix
+ that precedes following characters on output:
</para>
<itemizedlist>
@@ -6613,16 +6644,31 @@
<listitem>
<para>
- ASCII <literal>0</literal> (what is actually written
- following the escape character is ASCII
- ‘<literal>0</literal>’, not a zero-valued
- byte)
+ ASCII <literal>NUL</literal> (the zero-valued byte; what
+ is actually written following the escape character is
+ ASCII ‘<literal>0</literal>’, not a
+ zero-valued byte)
</para>
</listitem>
</itemizedlist>
<para>
+ The <literal>FIELDS TERMINATED BY</literal>,
+ <literal>ENCLOSED BY</literal>, <literal>ESCAPED
+ BY</literal>, or <literal>LINES TERMINATED BY</literal>
+ characters <emphasis>must</emphasis> be escaped so that you
+ can read the file back in reliably. ASCII
+ <literal>NUL</literal> is escaped to make it easier to view
+ with some pagers.
+ </para>
+
+ <para>
+ The resulting file does not have to conform to SQL syntax,
+ so nothing else need be escaped.
+ </para>
+
+ <para>
If the <literal>FIELDS ESCAPED BY</literal> character is
empty, no characters are escaped and <literal>NULL</literal>
is output as <literal>NULL</literal>, not
@@ -6632,40 +6678,33 @@
list just given.
</para>
- <para>
- The reason for the above is that you
- <emphasis>must</emphasis> escape any <literal>FIELDS
- TERMINATED BY</literal>, <literal>ENCLOSED BY</literal>,
- <literal>ESCAPED BY</literal>, or <literal>LINES TERMINATED
- BY</literal> characters to read the file back reliably.
- ASCII <literal>NUL</literal> is escaped to make it easier to
- view with some pagers.
- </para>
+ <indexterm>
+ <primary>CSV data, reading</primary>
+ </indexterm>
- <para>
- The resulting file does not have to conform to SQL syntax,
- so nothing else need be escaped.
- </para>
+ <indexterm>
+ <primary>comma-separate values data, reading</primary>
+ </indexterm>
<para>
Here is an example that produces a file in the
- comma-separated values format used by many programs:
+ comma-separated values (CSV) format used by many programs:
</para>
<programlisting>
-SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
-FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-LINES TERMINATED BY '\n'
-FROM test_table;
+SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
+ FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
+ LINES TERMINATED BY '\n'
+ FROM test_table;
</programlisting>
-
- <indexterm type="function">
- <primary>DUMPFILE</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>DUMPFILE</primary>
+ </indexterm>
+
If you use <literal>INTO DUMPFILE</literal> instead of
<literal>INTO OUTFILE</literal>, MySQL writes only one row
into the file, without any column or line termination and
@@ -6681,9 +6720,9 @@
DUMPFILE</literal> 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
+ under whose account it is running. (You should
<emphasis>never</emphasis> run <command>mysqld</command> as
- <literal>root</literal> for this and other reasons). The
+ <literal>root</literal> for this and other reasons.) The
file thus must be world-writable so that you can manipulate
its contents.
</para>
@@ -6691,7 +6730,7 @@
<listitem>
<para>
- The <literal>SELECT</literal> syntax diagram at the
+ The <literal>SELECT</literal> syntax description at the
beginning this section shows the <literal>INTO</literal>
clause near the end of the statement. It is also possible to
use <literal>INTO OUTFILE</literal> or <literal>INTO
@@ -6722,12 +6761,12 @@
<primary>LOCK IN SHARE MODE</primary>
</indexterm>
- If you use <literal>FOR UPDATE</literal> on 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
- <literal>LOCK IN SHARE MODE</literal> sets a shared lock
- that prevents other transactions from updating or deleting
- the examined rows. See
+ If you use <literal>FOR UPDATE</literal> 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 <literal>LOCK IN SHARE MODE</literal>
+ sets a shared lock that allows other transactions to read
+ the examined rows but not to update or delete them. See
<xref linkend="innodb-locking-reads"/>.
</para>
</listitem>
@@ -6757,8 +6796,8 @@
rows should be returned. If none of these options are given, the
default is <literal>ALL</literal> (all matching rows are
returned). <literal>DISTINCT</literal> and
- <literal>DISTINCTROW</literal> are synonyms and specify that
- duplicate rows in the result set should be removed.
+ <literal>DISTINCTROW</literal> are synonyms and specify removal
+ of duplicate rows from the result set.
</para>
<para>
@@ -6789,18 +6828,18 @@
<literal>SELECT</literal> statements that are part of a
<literal>UNION</literal>.
</para>
-
- <indexterm>
- <primary>hints</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>STRAIGHT_JOIN</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm>
+ <primary>hints</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>STRAIGHT_JOIN</primary>
+ </indexterm>
+
<literal>STRAIGHT_JOIN</literal> forces the optimizer to
join the tables in the order in which they are listed in the
<literal>FROM</literal> clause. You can use this to speed up
@@ -6810,58 +6849,57 @@
<replaceable>table_references</replaceable> list. See
<xref linkend="join"/>.
</para>
-
- <indexterm type="function">
- <primary>SQL_BIG_RESULT</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_BIG_RESULT</primary>
+ </indexterm>
+
<literal>SQL_BIG_RESULT</literal> can be used with
<literal>GROUP BY</literal> or <literal>DISTINCT</literal>
to tell the optimizer that the result set has many rows. In
this case, MySQL directly uses disk-based temporary tables
- if needed. MySQL also, in this case, prefers sorting to
- using a temporary table with a key on the <literal>GROUP
- BY</literal> elements.
+ if needed, and prefers sorting to using a temporary table
+ with a key on the <literal>GROUP BY</literal> elements.
</para>
-
- <indexterm type="function">
- <primary>SQL_BUFFER_RESULT</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_BUFFER_RESULT</primary>
+ </indexterm>
+
<literal>SQL_BUFFER_RESULT</literal> forces the result to be
put into a temporary table. This helps MySQL free the table
locks early and helps in cases where it takes a long time to
send the result set to the client.
</para>
-
- <indexterm type="function">
- <primary>SQL_SMALL_RESULT</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_SMALL_RESULT</primary>
+ </indexterm>
+
<literal>SQL_SMALL_RESULT</literal> can be used with
<literal>GROUP BY</literal> or <literal>DISTINCT</literal>
to tell the optimizer that the result set is small. In this
case, MySQL uses fast temporary tables to store the
- resulting table instead of using sorting. In MySQL
- ¤t-series;, this should not normally be needed.
+ resulting table instead of using sorting. This should not
+ normally be needed.
</para>
-
- <indexterm type="function">
- <primary>SQL_CALC_FOUND_ROWS</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_CALC_FOUND_ROWS</primary>
+ </indexterm>
+
<literal>SQL_CALC_FOUND_ROWS</literal> tells MySQL to
calculate how many rows there would be in the result set,
disregarding any <literal>LIMIT</literal> clause. The number
@@ -6869,14 +6907,14 @@
FOUND_ROWS()</literal>. See
<xref linkend="information-functions"/>.
</para>
-
- <indexterm type="function">
- <primary>SQL_CACHE</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_CACHE</primary>
+ </indexterm>
+
<literal>SQL_CACHE</literal> tells MySQL to store the query
result in the query cache if you are using a
<literal>query_cache_type</literal> value of
@@ -6885,14 +6923,14 @@
option effects any <literal>SELECT</literal> in the query.
See <xref linkend="query-cache"/>.
</para>
-
- <indexterm type="function">
- <primary>SQL_NO_CACHE</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_NO_CACHE</primary>
+ </indexterm>
+
<literal>SQL_NO_CACHE</literal> tells MySQL not to store the
query result in the query cache. See
<xref linkend="query-cache"/>. For a query that uses
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2006-01-27 18:33:37 UTC (rev 1067)
+++ trunk/refman-5.1/sql-syntax.xml 2006-01-27 20:30:35 UTC (rev 1068)
@@ -6458,7 +6458,7 @@
standard, and that either inserts or
<emphasis>updates</emphasis>, look for the <literal>INSERT ...
ON DUPLICATE KEY UPDATE</literal> statement; see
- <xref linkend="insert"/>.
+ <xref linkend="insert-on-duplicate"/>.
</para>
<para>
@@ -6525,8 +6525,9 @@
</para>
<para>
- Here follows in more detail the algorithm that is used (it is
- also used with <literal>LOAD DATA ... REPLACE</literal>):
+ MySQL uses the following algorithm for
+ <literal>REPLACE</literal> (and <literal>LOAD DATA ...
+ REPLACE</literal>):
</para>
<orderedlist>
@@ -6600,7 +6601,7 @@
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING <replaceable>where_condition</replaceable>]
[ORDER BY {<replaceable>col_name</replaceable> | <replaceable>expr</replaceable> | <replaceable>position</replaceable>}
- [ASC | DESC] , ...]
+ [ASC | DESC], ...]
[LIMIT {[<replaceable>offset</replaceable>,] <replaceable>row_count</replaceable> | <replaceable>row_count</replaceable> OFFSET <replaceable>offset</replaceable>}]
[PROCEDURE <replaceable>procedure_name</replaceable>(<replaceable>argument_list</replaceable>)]
[INTO OUTFILE '<replaceable>file_name</replaceable>' <replaceable>export_options</replaceable>
@@ -6617,12 +6618,18 @@
<xref linkend="subqueries"/>.
</para>
+ <para>
+ The most commonly used clauses of <literal>SELECT</literal>
+ statements are these:
+ </para>
+
<itemizedlist>
<listitem>
<para>
Each <replaceable>select_expr</replaceable> indicates a
- column you want to retrieve.
+ column that you want to retrieve. There must be at least one
+ <replaceable>select_expr</replaceable>.
</para>
</listitem>
@@ -6636,11 +6643,20 @@
<listitem>
<para>
- <replaceable>where_condition</replaceable> consists of the
- keyword <literal>WHERE</literal> followed by an expression
- that indicates the condition or conditions that rows must
- satisfy to be selected.
+ The <literal>WHERE</literal> clause, if given, indicates the
+ condition or conditions that rows must satisfy to be
+ selected. <replaceable>where_condition</replaceable> is an
+ expression that evaluates to true for each row to be
+ selected. The statement selects all rows if there is no
+ <literal>WHERE</literal> clause.
</para>
+
+ <para>
+ In the <literal>WHERE</literal> clause, you can use any of
+ the functions and operators that MySQL supports, except for
+ aggregate (summary) functions. See
+ <xref linkend="functions"/>.
+ </para>
</listitem>
</itemizedlist>
@@ -6661,13 +6677,44 @@
-> 2
</programlisting>
+ <remark role="help-topic" condition="DUAL"/>
+
+ <remark role="help-description-begin"/>
+
<para>
- All clauses used must be given in exactly the order shown in the
- syntax description. For example, a <literal>HAVING</literal>
- clause must come after any <literal>GROUP BY</literal> clause
- and before any <literal>ORDER BY</literal> clause.
+ <indexterm>
+ <primary><literal>DUAL</literal></primary>
+ </indexterm>
+
+ You are allowed to specify <literal>DUAL</literal> as a dummy
+ table name in situations where no tables are referenced:
</para>
+<programlisting>
+mysql> <userinput>SELECT 1 + 1 FROM DUAL;</userinput>
+ -> 2
+</programlisting>
+
+ <para>
+ <literal>DUAL</literal> is purely for compatibility with some
+ other database servers that require a <literal>FROM</literal>
+ clause. MySQL does not require the clause if no tables are
+ referenced.
+ </para>
+
+ <remark role="help-description-end"/>
+
+ <para>
+ In general, clauses used must be given in exactly the order
+ shown in the syntax description. For example, a
+ <literal>HAVING</literal> clause must come after any
+ <literal>GROUP BY</literal> clause and before any <literal>ORDER
+ BY</literal> clause. The exception is that the
+ <literal>INTO</literal> clause can appear either as shown in the
+ syntax description or immediately preceding the
+ <literal>FROM</literal> clause.
+ </para>
+
<itemizedlist>
<listitem>
@@ -6690,8 +6737,8 @@
</para>
<programlisting>
-mysql> <userinput>SELECT CONCAT(last_name,', ',first_name) AS full_name</userinput>
- -> <userinput>FROM mytable ORDER BY full_name;</userinput>
+SELECT CONCAT(last_name,', ',first_name) AS full_name
+ FROM mytable ORDER BY full_name;
</programlisting>
<para>
@@ -6701,13 +6748,13 @@
</para>
<programlisting>
-mysql> <userinput>SELECT CONCAT(last_name,', ',first_name) full_name</userinput>
- -> <userinput>FROM mytable ORDER BY full_name;</userinput>
+SELECT CONCAT(last_name,', ',first_name) full_name
+ FROM mytable ORDER BY full_name;
</programlisting>
<para>
- Because the <literal>AS</literal> is optional, a subtle
- problem can occur if you forget the comma between two
+ However, because the <literal>AS</literal> is optional, a
+ subtle problem can occur if you forget the comma between two
<replaceable>select_expr</replaceable> expressions: MySQL
interprets the second as an alias name. For example, in the
following statement, <literal>columnb</literal> is treated
@@ -6715,13 +6762,13 @@
</para>
<programlisting>
-mysql> <userinput>SELECT columna columnb FROM mytable;</userinput>
+SELECT columna columnb FROM mytable;
</programlisting>
<para>
For this reason, it is good practice to be in the habit of
- specifying column aliases explicitly using
- <literal>AS</literal>.
+ using <literal>AS</literal> explicitly when specifying
+ column aliases.
</para>
</listitem>
@@ -6771,10 +6818,11 @@
<para>
The <literal>FROM
<replaceable>table_references</replaceable></literal> 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 <xref linkend="join"/>. For
- each table specified, you can optionally specify an alias.
+ 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 <xref linkend="join"/>.
+ For each table specified, you can optionally specify an
+ alias.
</para>
<programlisting>
@@ -6802,15 +6850,15 @@
You can use <literal>SET
max_seeks_for_key=<replaceable>value</replaceable></literal>
as an alternative way to force MySQL to prefer key scans
- instead of table scans.
+ instead of table scans. See
+ <xref linkend="server-system-variables"/>.
</para>
</listitem>
<listitem>
<para>
You can refer to a table within the default database as
- <replaceable>tbl_name</replaceable> (within the current
- database), or as
+ <replaceable>tbl_name</replaceable>, or as
<replaceable>db_name</replaceable>.<replaceable>tbl_name</replaceable>
to specify a database explicitly. You can refer to a column
as <replaceable>col_name</replaceable>,
@@ -6828,10 +6876,6 @@
</listitem>
<listitem>
- <remark role="help-topic" condition="DUAL"/>
-
- <remark role="help-description-begin"/>
-
<para>
<indexterm>
<primary>aliases</primary>
@@ -6842,38 +6886,6 @@
<primary>table aliases</primary>
</indexterm>
- <indexterm>
- <primary><literal>DUAL</literal></primary>
- </indexterm>
-
- You are allowed to specify <literal>DUAL</literal> as a
- dummy table name in situations where no tables are
- referenced:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT 1 + 1 FROM DUAL;</userinput>
- -> 2
-</programlisting>
-
- <para>
- <literal>DUAL</literal> is purely for compatibility with
- some other servers that require a <literal>FROM</literal>
- clause. MySQL does not require the clause if no tables are
- referenced, and the preceding statement can be written this
- way:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT 1 + 1;</userinput>
- -> 2
-</programlisting>
-
- <remark role="help-description-end"/>
- </listitem>
-
- <listitem>
- <para>
A table reference can be aliased using
<literal><replaceable>tbl_name</replaceable> AS
<replaceable>alias_name</replaceable></literal> or
@@ -6881,27 +6893,20 @@
</para>
<programlisting>
-mysql> <userinput>SELECT t1.name, t2.salary FROM employee AS t1, info AS t2</userinput>
- -> <userinput>WHERE t1.name = t2.name;</userinput>
-mysql> <userinput>SELECT t1.name, t2.salary FROM employee t1, info t2</userinput>
- -> <userinput>WHERE t1.name = t2.name;</userinput>
+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;
</programlisting>
</listitem>
<listitem>
<para>
- In the <literal>WHERE</literal> clause, you can use any of
- the functions that MySQL supports, except for aggregate
- (summary) functions. See <xref linkend="functions"/>.
- </para>
+ <indexterm type="function">
+ <primary>ORDER BY</primary>
+ </indexterm>
- <indexterm type="function">
- <primary>ORDER BY</primary>
- </indexterm>
- </listitem>
-
- <listitem>
- <para>
Columns selected for output can be referred to in
<literal>ORDER BY</literal> and <literal>GROUP BY</literal>
clauses using column names, column aliases, or column
@@ -6909,12 +6914,14 @@
</para>
<programlisting>
-mysql> <userinput>SELECT college, region, seed FROM tournament</userinput>
- -> <userinput>ORDER BY region, seed;</userinput>
-mysql> <userinput>SELECT college, region AS r, seed AS s FROM tournament</userinput>
- -> <userinput>ORDER BY r, s;</userinput>
-mysql> <userinput>SELECT college, region, seed FROM tournament</userinput>
- -> <userinput>ORDER BY 2, 3;</userinput>
+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;
</programlisting>
<para>
@@ -6929,35 +6936,56 @@
Use of column positions is deprecated because the syntax has
been removed from the SQL standard.
</para>
-
- <indexterm>
- <primary><literal>GROUP BY</literal></primary>
- <secondary>extensions to standard SQL</secondary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm>
+ <primary>ORDER BY</primary>
+ <secondary>NULL</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>NULL</primary>
+ <secondary>ORDER BY</secondary>
+ </indexterm>
+
If you use <literal>GROUP BY</literal>, output rows are
sorted according to the <literal>GROUP BY</literal> columns
as if you had an <literal>ORDER BY</literal> for the same
- columns. MySQL extends the <literal>GROUP BY</literal>
- clause so that you can also specify <literal>ASC</literal>
- and <literal>DESC</literal> after columns named in the
- clause:
+ columns. To avoid the overhead of sorting that
+ <literal>GROUP BY</literal> produces, add <literal>ORDER BY
+ NULL</literal>:
</para>
<programlisting>
-SELECT a, COUNT(b) FROM test_table GROUP BY a DESC
+SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
</programlisting>
</listitem>
<listitem>
<para>
+ <indexterm>
+ <primary><literal>GROUP BY</literal></primary>
+ <secondary>extensions to standard SQL</secondary>
+ </indexterm>
+
+ MySQL extends the <literal>GROUP BY</literal> clause so that
+ you can also specify <literal>ASC</literal> and
+ <literal>DESC</literal> after columns named in the clause:
+ </para>
+
+<programlisting>
+SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
MySQL extends the use of <literal>GROUP BY</literal> to
allow selecting fields that are not mentioned in the
<literal>GROUP BY</literal> clause. If you are not getting
- the results you expect from your query, please read the
+ the results that you expect from your query, please read the
description of <literal>GROUP BY</literal> found in
<xref linkend="group-by-functions-and-modifiers"/>.
</para>
@@ -6969,14 +6997,14 @@
ROLLUP</literal> modifier. See
<xref linkend="group-by-modifiers"/>.
</para>
-
- <indexterm type="function">
- <primary>HAVING</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>HAVING</primary>
+ </indexterm>
+
The <literal>HAVING</literal> clause is applied nearly last,
just before items are sent to the client, with no
optimization. (<literal>LIMIT</literal> is applied after
@@ -7001,15 +7029,15 @@
</para>
<programlisting>
-mysql> <userinput>SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;</userinput>
+SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
</programlisting>
<para>
- Preference is given to standard SQL behavior, so that if a
+ Preference is given to standard SQL behavior, so if a
<literal>HAVING</literal> column name is used both in
<literal>GROUP BY</literal> and as an aliased column in the
- output column list, preferences is given to the column in
- the <literal>GROUP BY</literal> column.
+ output column list, preference is given to the column in the
+ <literal>GROUP BY</literal> column.
</para>
</listitem>
@@ -7021,7 +7049,7 @@
</para>
<programlisting>
-mysql> <userinput>SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> HAVING <replaceable>col_name</replaceable> > 0;</userinput>
+SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> HAVING <replaceable>col_name</replaceable> > 0;
</programlisting>
<para>
@@ -7029,7 +7057,7 @@
</para>
<programlisting>
-mysql> <userinput>SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>col_name</replaceable> > 0;</userinput>
+SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>col_name</replaceable> > 0;
</programlisting>
</listitem>
@@ -7040,21 +7068,21 @@
</para>
<programlisting>
-mysql> <userinput>SELECT user, MAX(salary) FROM users</userinput>
- -> <userinput>GROUP BY user HAVING MAX(salary)>10;</userinput>
+SELECT user, MAX(salary) FROM users
+ GROUP BY user HAVING MAX(salary) > 10;
</programlisting>
<para>
(This did not work in some older versions of MySQL.)
</para>
-
- <indexterm type="function">
- <primary>LIMIT</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>LIMIT</primary>
+ </indexterm>
+
The <literal>LIMIT</literal> clause can be used to constrain
the number of rows returned by the <literal>SELECT</literal>
statement. <literal>LIMIT</literal> takes one or two numeric
@@ -7070,16 +7098,10 @@
</para>
<programlisting>
-mysql> <userinput>SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15</userinput>
+SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
</programlisting>
<para>
- For compatibility with PostgreSQL, MySQL also supports the
- <literal>LIMIT <replaceable>row_count</replaceable> OFFSET
- <replaceable>offset</replaceable></literal> syntax.
- </para>
-
- <para>
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
@@ -7087,7 +7109,7 @@
</para>
<programlisting>
-mysql> <userinput>SELECT * FROM tbl LIMIT 95,18446744073709551615;</userinput>
+SELECT * FROM tbl LIMIT 95,18446744073709551615;
</programlisting>
<para>
@@ -7096,12 +7118,14 @@
</para>
<programlisting>
-mysql> <userinput>SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows</userinput>
+SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows
</programlisting>
<para>
- In other words, <literal>LIMIT n</literal> is equivalent to
- <literal>LIMIT 0,n</literal>.
+ In other words, <literal>LIMIT
+ <replaceable>row_count</replaceable></literal> is equivalent
+ to <literal>LIMIT 0,
+ <replaceable>row_count</replaceable></literal>.
</para>
<para>
@@ -7111,9 +7135,9 @@
</para>
<programlisting>
-mysql> <userinput>SET @a=1;</userinput>
-mysql> <userinput>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";</userinput>
-mysql> <userinput>EXECUTE STMT USING @a;</userinput>
+SET @a=1;
+PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
+EXECUTE STMT USING @a;
</programlisting>
<para>
@@ -7122,10 +7146,16 @@
</para>
<programlisting>
-mysql> <userinput>SET @skip=1; SET @numrows=5;</userinput>
-mysql> <userinput>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";</userinput>
-mysql> <userinput>EXECUTE STMT USING @skip, @numrows;</userinput>
+SET @skip=1; SET @numrows=5;
+PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
+EXECUTE STMT USING @skip, @numrows;
</programlisting>
+
+ <para>
+ For compatibility with PostgreSQL, MySQL also supports the
+ <literal>LIMIT <replaceable>row_count</replaceable> OFFSET
+ <replaceable>offset</replaceable></literal> syntax.
+ </para>
</listitem>
<listitem>
@@ -7151,20 +7181,21 @@
<para>
The <literal>SELECT ... INTO OUTFILE</literal> statement is
- intended primarily to let you very quickly dump a table on
- the server machine. If you want to create the resulting file
- on some client host other than the server host, you cannot
- use <literal>SELECT ... INTO OUTFILE</literal>. In that
- case, you should instead use some command like
- <literal>mysql -e "SELECT ..." > file_name</literal> on
- the client host to generate the file.
+ 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 <literal>SELECT ... INTO
+ OUTFILE</literal>. In that case, you should instead use a
+ command such as <literal>mysql -e "SELECT ..." >
+ <replaceable>file_name</replaceable></literal> to generate
+ the file on the client host.
</para>
<para>
<literal>SELECT ... INTO OUTFILE</literal> is the complement
of <literal>LOAD DATA INFILE</literal>; the syntax for the
- <literal>export_options</literal> part of the statement
- consists of the same <literal>FIELDS</literal> and
+ <replaceable>export_options</replaceable> part of the
+ statement consists of the same <literal>FIELDS</literal> and
<literal>LINES</literal> clauses that are used with the
<literal>LOAD DATA INFILE</literal> statement. See
<xref linkend="load-data"/>.
@@ -7173,8 +7204,8 @@
<para>
<literal>FIELDS ESCAPED BY</literal> controls how to write
special characters. If the <literal>FIELDS ESCAPED
- BY</literal> character is not empty, it is used to prefix
- the following characters on output:
+ BY</literal> character is not empty, it is used as a prefix
+ that precedes following characters on output:
</para>
<itemizedlist>
@@ -7202,16 +7233,31 @@
<listitem>
<para>
- ASCII <literal>0</literal> (what is actually written
- following the escape character is ASCII
- ‘<literal>0</literal>’, not a zero-valued
- byte)
+ ASCII <literal>NUL</literal> (the zero-valued byte; what
+ is actually written following the escape character is
+ ASCII ‘<literal>0</literal>’, not a
+ zero-valued byte)
</para>
</listitem>
</itemizedlist>
<para>
+ The <literal>FIELDS TERMINATED BY</literal>,
+ <literal>ENCLOSED BY</literal>, <literal>ESCAPED
+ BY</literal>, or <literal>LINES TERMINATED BY</literal>
+ characters <emphasis>must</emphasis> be escaped so that you
+ can read the file back in reliably. ASCII
+ <literal>NUL</literal> is escaped to make it easier to view
+ with some pagers.
+ </para>
+
+ <para>
+ The resulting file does not have to conform to SQL syntax,
+ so nothing else need be escaped.
+ </para>
+
+ <para>
If the <literal>FIELDS ESCAPED BY</literal> character is
empty, no characters are escaped and <literal>NULL</literal>
is output as <literal>NULL</literal>, not
@@ -7221,40 +7267,33 @@
list just given.
</para>
- <para>
- The reason for the above is that you
- <emphasis>must</emphasis> escape any <literal>FIELDS
- TERMINATED BY</literal>, <literal>ENCLOSED BY</literal>,
- <literal>ESCAPED BY</literal>, or <literal>LINES TERMINATED
- BY</literal> characters to read the file back reliably.
- ASCII <literal>NUL</literal> is escaped to make it easier to
- view with some pagers.
- </para>
+ <indexterm>
+ <primary>CSV data, reading</primary>
+ </indexterm>
- <para>
- The resulting file does not have to conform to SQL syntax,
- so nothing else need be escaped.
- </para>
+ <indexterm>
+ <primary>comma-separate values data, reading</primary>
+ </indexterm>
<para>
Here is an example that produces a file in the
- comma-separated values format used by many programs:
+ comma-separated values (CSV) format used by many programs:
</para>
<programlisting>
-SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
-FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-LINES TERMINATED BY '\n'
-FROM test_table;
+SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
+ FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
+ LINES TERMINATED BY '\n'
+ FROM test_table;
</programlisting>
-
- <indexterm type="function">
- <primary>DUMPFILE</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>DUMPFILE</primary>
+ </indexterm>
+
If you use <literal>INTO DUMPFILE</literal> instead of
<literal>INTO OUTFILE</literal>, MySQL writes only one row
into the file, without any column or line termination and
@@ -7270,9 +7309,9 @@
DUMPFILE</literal> 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
+ under whose account it is running. (You should
<emphasis>never</emphasis> run <command>mysqld</command> as
- <literal>root</literal> for this and other reasons). The
+ <literal>root</literal> for this and other reasons.) The
file thus must be world-writable so that you can manipulate
its contents.
</para>
@@ -7280,7 +7319,7 @@
<listitem>
<para>
- The <literal>SELECT</literal> syntax diagram at the
+ The <literal>SELECT</literal> syntax description at the
beginning this section shows the <literal>INTO</literal>
clause near the end of the statement. It is also possible to
use <literal>INTO OUTFILE</literal> or <literal>INTO
@@ -7311,12 +7350,12 @@
<primary>LOCK IN SHARE MODE</primary>
</indexterm>
- If you use <literal>FOR UPDATE</literal> on 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
- <literal>LOCK IN SHARE MODE</literal> sets a shared lock
- that prevents other transactions from updating or deleting
- the examined rows. See
+ If you use <literal>FOR UPDATE</literal> 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 <literal>LOCK IN SHARE MODE</literal>
+ sets a shared lock that allows other transactions to read
+ the examined rows but not to update or delete them. See
<xref linkend="innodb-locking-reads"/>.
</para>
</listitem>
@@ -7346,8 +7385,8 @@
rows should be returned. If none of these options are given, the
default is <literal>ALL</literal> (all matching rows are
returned). <literal>DISTINCT</literal> and
- <literal>DISTINCTROW</literal> are synonyms and specify that
- duplicate rows in the result set should be removed.
+ <literal>DISTINCTROW</literal> are synonyms and specify removal
+ of duplicate rows from the result set.
</para>
<para>
@@ -7378,18 +7417,18 @@
<literal>SELECT</literal> statements that are part of a
<literal>UNION</literal>.
</para>
-
- <indexterm>
- <primary>hints</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>STRAIGHT_JOIN</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm>
+ <primary>hints</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>STRAIGHT_JOIN</primary>
+ </indexterm>
+
<literal>STRAIGHT_JOIN</literal> forces the optimizer to
join the tables in the order in which they are listed in the
<literal>FROM</literal> clause. You can use this to speed up
@@ -7399,58 +7438,57 @@
<replaceable>table_references</replaceable> list. See
<xref linkend="join"/>.
</para>
-
- <indexterm type="function">
- <primary>SQL_BIG_RESULT</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_BIG_RESULT</primary>
+ </indexterm>
+
<literal>SQL_BIG_RESULT</literal> can be used with
<literal>GROUP BY</literal> or <literal>DISTINCT</literal>
to tell the optimizer that the result set has many rows. In
this case, MySQL directly uses disk-based temporary tables
- if needed. MySQL also, in this case, prefers sorting to
- using a temporary table with a key on the <literal>GROUP
- BY</literal> elements.
+ if needed, and prefers sorting to using a temporary table
+ with a key on the <literal>GROUP BY</literal> elements.
</para>
-
- <indexterm type="function">
- <primary>SQL_BUFFER_RESULT</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_BUFFER_RESULT</primary>
+ </indexterm>
+
<literal>SQL_BUFFER_RESULT</literal> forces the result to be
put into a temporary table. This helps MySQL free the table
locks early and helps in cases where it takes a long time to
send the result set to the client.
</para>
-
- <indexterm type="function">
- <primary>SQL_SMALL_RESULT</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_SMALL_RESULT</primary>
+ </indexterm>
+
<literal>SQL_SMALL_RESULT</literal> can be used with
<literal>GROUP BY</literal> or <literal>DISTINCT</literal>
to tell the optimizer that the result set is small. In this
case, MySQL uses fast temporary tables to store the
- resulting table instead of using sorting. In MySQL
- ¤t-series;, this should not normally be needed.
+ resulting table instead of using sorting. This should not
+ normally be needed.
</para>
-
- <indexterm type="function">
- <primary>SQL_CALC_FOUND_ROWS</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_CALC_FOUND_ROWS</primary>
+ </indexterm>
+
<literal>SQL_CALC_FOUND_ROWS</literal> tells MySQL to
calculate how many rows there would be in the result set,
disregarding any <literal>LIMIT</literal> clause. The number
@@ -7458,14 +7496,14 @@
FOUND_ROWS()</literal>. See
<xref linkend="information-functions"/>.
</para>
-
- <indexterm type="function">
- <primary>SQL_CACHE</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_CACHE</primary>
+ </indexterm>
+
<literal>SQL_CACHE</literal> tells MySQL to store the query
result in the query cache if you are using a
<literal>query_cache_type</literal> value of
@@ -7474,14 +7512,14 @@
option effects any <literal>SELECT</literal> in the query.
See <xref linkend="query-cache"/>.
</para>
-
- <indexterm type="function">
- <primary>SQL_NO_CACHE</primary>
- </indexterm>
</listitem>
<listitem>
<para>
+ <indexterm type="function">
+ <primary>SQL_NO_CACHE</primary>
+ </indexterm>
+
<literal>SQL_NO_CACHE</literal> tells MySQL not to store the
query result in the query cache. See
<xref linkend="query-cache"/>. For a query that uses
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1068 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 27 Jan |
