List:Commits« Previous MessageNext Message »
From:paul Date:January 27 2006 8:30pm
Subject:svn commit - mysqldoc@docsrva: r1068 - in trunk: . refman-4.1 refman-5.0 refman-5.1
View as plain text  
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 @@
         -&gt; 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&gt; <userinput>SELECT 1 + 1 FROM DUAL;</userinput>
+        -&gt; 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&gt; <userinput>SELECT CONCAT(last_name,', ',first_name) AS full_name</userinput>
-    -&gt; <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&gt; <userinput>SELECT CONCAT(last_name,', ',first_name) full_name</userinput>
-    -&gt; <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&gt; <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&gt; <userinput>SELECT 1 + 1 FROM DUAL;</userinput>
-        -&gt; 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&gt; <userinput>SELECT 1 + 1;</userinput>
-        -&gt; 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&gt; <userinput>SELECT t1.name, t2.salary FROM employee AS t1, info AS t2</userinput>
-    -&gt;     <userinput>WHERE t1.name = t2.name;</userinput>
-mysql&gt; <userinput>SELECT t1.name, t2.salary FROM employee t1, info t2</userinput>
-    -&gt;     <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&gt; <userinput>SELECT college, region, seed FROM tournament</userinput>
-    -&gt;     <userinput>ORDER BY region, seed;</userinput>
-mysql&gt; <userinput>SELECT college, region AS r, seed AS s FROM tournament</userinput>
-    -&gt;     <userinput>ORDER BY r, s;</userinput>
-mysql&gt; <userinput>SELECT college, region, seed FROM tournament</userinput>
-    -&gt;     <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&gt; <userinput>SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> HAVING <replaceable>col_name</replaceable> &gt; 0;</userinput>
+SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> HAVING <replaceable>col_name</replaceable> &gt; 0;
 </programlisting>
 
           <para>
@@ -6419,7 +6449,7 @@
           </para>
 
 <programlisting>
-mysql&gt; <userinput>SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>col_name</replaceable> &gt; 0;</userinput>
+SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>col_name</replaceable> &gt; 0;
 </programlisting>
         </listitem>
 
@@ -6430,8 +6460,8 @@
           </para>
 
 <programlisting>
-mysql&gt; <userinput>SELECT user, MAX(salary) FROM users</userinput>
-    -&gt;     <userinput>GROUP BY user HAVING MAX(salary)&gt;10;</userinput>
+SELECT user, MAX(salary) FROM users
+  GROUP BY user HAVING MAX(salary) &gt; 10;
 </programlisting>
 
           <para>
@@ -6442,17 +6472,17 @@
           </para>
 
 <programlisting>
-mysql&gt; <userinput>SELECT user, MAX(salary) AS max_salary FROM users</userinput>
-    -&gt;     <userinput>GROUP BY user HAVING max_salary&gt;10;</userinput>
+SELECT user, MAX(salary) AS max_salary FROM users
+  GROUP BY user HAVING max_salary&gt;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&gt; <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&gt; <userinput>SELECT * FROM tbl LIMIT 95,18446744073709551615;</userinput>
+SELECT * FROM tbl LIMIT 95,18446744073709551615;
 </programlisting>
 
           <para>
@@ -6494,12 +6518,14 @@
           </para>
 
 <programlisting>
-mysql&gt; <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&gt; <userinput>SET @a=1;</userinput>
-mysql&gt; <userinput>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";</userinput>
-mysql&gt; <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&gt; <userinput>SET @skip=1; SET @numrows=5;</userinput>
-mysql&gt; <userinput>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";</userinput>
-mysql&gt; <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 ..." &gt; 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 ..." &gt;
+            <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
-                &lsquo;<literal>0</literal>&rsquo;, not a zero-valued
-                byte)
+                ASCII <literal>NUL</literal> (the zero-valued byte; what
+                is actually written following the escape character is
+                ASCII &lsquo;<literal>0</literal>&rsquo;, 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 @@
         -&gt; 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&gt; <userinput>SELECT 1 + 1 FROM DUAL;</userinput>
+        -&gt; 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&gt; <userinput>SELECT CONCAT(last_name,', ',first_name) AS full_name</userinput>
-    -&gt; <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&gt; <userinput>SELECT CONCAT(last_name,', ',first_name) full_name</userinput>
-    -&gt; <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&gt; <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&gt; <userinput>SELECT 1 + 1 FROM DUAL;</userinput>
-        -&gt; 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&gt; <userinput>SELECT 1 + 1;</userinput>
-        -&gt; 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&gt; <userinput>SELECT t1.name, t2.salary FROM employee AS t1, info AS t2</userinput>
-    -&gt;     <userinput>WHERE t1.name = t2.name;</userinput>
-mysql&gt; <userinput>SELECT t1.name, t2.salary FROM employee t1, info t2</userinput>
-    -&gt;     <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&gt; <userinput>SELECT college, region, seed FROM tournament</userinput>
-    -&gt;     <userinput>ORDER BY region, seed;</userinput>
-mysql&gt; <userinput>SELECT college, region AS r, seed AS s FROM tournament</userinput>
-    -&gt;     <userinput>ORDER BY r, s;</userinput>
-mysql&gt; <userinput>SELECT college, region, seed FROM tournament</userinput>
-    -&gt;     <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&gt; <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&gt; <userinput>SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> HAVING <replaceable>col_name</replaceable> &gt; 0;</userinput>
+SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> HAVING <replaceable>col_name</replaceable> &gt; 0;
 </programlisting>
 
           <para>
@@ -6440,7 +6468,7 @@
           </para>
 
 <programlisting>
-mysql&gt; <userinput>SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>col_name</replaceable> &gt; 0;</userinput>
+SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>col_name</replaceable> &gt; 0;
 </programlisting>
         </listitem>
 
@@ -6451,21 +6479,21 @@
           </para>
 
 <programlisting>
-mysql&gt; <userinput>SELECT user, MAX(salary) FROM users</userinput>
-    -&gt;     <userinput>GROUP BY user HAVING MAX(salary)&gt;10;</userinput>
+SELECT user, MAX(salary) FROM users
+  GROUP BY user HAVING MAX(salary) &gt; 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&gt; <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&gt; <userinput>SELECT * FROM tbl LIMIT 95,18446744073709551615;</userinput>
+SELECT * FROM tbl LIMIT 95,18446744073709551615;
 </programlisting>
 
           <para>
@@ -6507,12 +6529,14 @@
           </para>
 
 <programlisting>
-mysql&gt; <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&gt; <userinput>SET @a=1;</userinput>
-mysql&gt; <userinput>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";</userinput>
-mysql&gt; <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&gt; <userinput>SET @skip=1; SET @numrows=5;</userinput>
-mysql&gt; <userinput>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";</userinput>
-mysql&gt; <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 ..." &gt; 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 ..." &gt;
+            <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
-                &lsquo;<literal>0</literal>&rsquo;, not a zero-valued
-                byte)
+                ASCII <literal>NUL</literal> (the zero-valued byte; what
+                is actually written following the escape character is
+                ASCII &lsquo;<literal>0</literal>&rsquo;, 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
-            &current-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 @@
         -&gt; 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&gt; <userinput>SELECT 1 + 1 FROM DUAL;</userinput>
+        -&gt; 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&gt; <userinput>SELECT CONCAT(last_name,', ',first_name) AS full_name</userinput>
-    -&gt; <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&gt; <userinput>SELECT CONCAT(last_name,', ',first_name) full_name</userinput>
-    -&gt; <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&gt; <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&gt; <userinput>SELECT 1 + 1 FROM DUAL;</userinput>
-        -&gt; 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&gt; <userinput>SELECT 1 + 1;</userinput>
-        -&gt; 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&gt; <userinput>SELECT t1.name, t2.salary FROM employee AS t1, info AS t2</userinput>
-    -&gt;     <userinput>WHERE t1.name = t2.name;</userinput>
-mysql&gt; <userinput>SELECT t1.name, t2.salary FROM employee t1, info t2</userinput>
-    -&gt;     <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&gt; <userinput>SELECT college, region, seed FROM tournament</userinput>
-    -&gt;     <userinput>ORDER BY region, seed;</userinput>
-mysql&gt; <userinput>SELECT college, region AS r, seed AS s FROM tournament</userinput>
-    -&gt;     <userinput>ORDER BY r, s;</userinput>
-mysql&gt; <userinput>SELECT college, region, seed FROM tournament</userinput>
-    -&gt;     <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&gt; <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&gt; <userinput>SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> HAVING <replaceable>col_name</replaceable> &gt; 0;</userinput>
+SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> HAVING <replaceable>col_name</replaceable> &gt; 0;
 </programlisting>
 
           <para>
@@ -7029,7 +7057,7 @@
           </para>
 
 <programlisting>
-mysql&gt; <userinput>SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>col_name</replaceable> &gt; 0;</userinput>
+SELECT <replaceable>col_name</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>col_name</replaceable> &gt; 0;
 </programlisting>
         </listitem>
 
@@ -7040,21 +7068,21 @@
           </para>
 
 <programlisting>
-mysql&gt; <userinput>SELECT user, MAX(salary) FROM users</userinput>
-    -&gt;     <userinput>GROUP BY user HAVING MAX(salary)&gt;10;</userinput>
+SELECT user, MAX(salary) FROM users
+  GROUP BY user HAVING MAX(salary) &gt; 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&gt; <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&gt; <userinput>SELECT * FROM tbl LIMIT 95,18446744073709551615;</userinput>
+SELECT * FROM tbl LIMIT 95,18446744073709551615;
 </programlisting>
 
           <para>
@@ -7096,12 +7118,14 @@
           </para>
 
 <programlisting>
-mysql&gt; <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&gt; <userinput>SET @a=1;</userinput>
-mysql&gt; <userinput>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";</userinput>
-mysql&gt; <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&gt; <userinput>SET @skip=1; SET @numrows=5;</userinput>
-mysql&gt; <userinput>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";</userinput>
-mysql&gt; <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 ..." &gt; 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 ..." &gt;
+            <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
-                &lsquo;<literal>0</literal>&rsquo;, not a zero-valued
-                byte)
+                ASCII <literal>NUL</literal> (the zero-valued byte; what
+                is actually written following the escape character is
+                ASCII &lsquo;<literal>0</literal>&rsquo;, 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
-            &current-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.1paul27 Jan