sql.sgml

关系型数据库 Postgresql 6.5.2

    </para>
   </sect2>
  </sect1>

  <sect1 id="sql-language">
   <title>The <acronym>SQL</acronym> Language</title>

   <para>
    As is the case with most modern relational languages,
    <acronym>SQL</acronym> is based on the tuple
    relational calculus. As a result every query that can be formulated
    using the tuple relational calculus (or equivalently, relational
    algebra) can also be formulated using <acronym>SQL</acronym>. There are, however,
    capabilities beyond the scope of relational algebra or calculus. Here
    is a list of some additional features provided by <acronym>SQL</acronym> that are not
    part of relational algebra or calculus:

    <itemizedlist>
     <listitem>
      <para>
       Commands for insertion, deletion or modification of data.
      </para>
     </listitem>

     <listitem>
      <para>
       Arithmetic capability: In <acronym>SQL</acronym> it is possible to involve
       arithmetic operations as well as comparisons, e.g.

       A &lt; B + 3.

       Note
       that + or other arithmetic operators appear neither in relational
       algebra nor in relational calculus.
      </para>
     </listitem>

     <listitem>
      <para>
       Assignment and Print Commands: It is possible to print a
       relation constructed by a query and to assign a computed relation to a
       relation name.
      </para>
     </listitem>

     <listitem>
      <para>
       Aggregate Functions: Operations such as
       <firstterm>average</firstterm>, <firstterm>sum</firstterm>,
       <firstterm>max</firstterm>, etc. can be applied to columns of a relation to
       obtain a single quantity.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <sect2 id="select">
    <title id="select-title">Select</title>

    <para>
     The most often used command in <acronym>SQL</acronym> is the
     SELECT statement,
     used to retrieve data. The syntax is:

     <synopsis>
   SELECT [ALL|DISTINCT] 
          { * | <replaceable class="parameter">expr_1</replaceable> [AS <replaceable class="parameter">c_alias_1</replaceable>] [, ... 
                [, <replaceable class="parameter">expr_k</replaceable> [AS <replaceable class="parameter">c_alias_k</replaceable>]]]}
   FROM <replaceable class="parameter">table_name_1</replaceable> [<replaceable class="parameter">t_alias_1</replaceable>] 
        [, ... [, <replaceable class="parameter">table_name_n</replaceable> [<replaceable class="parameter">t_alias_n</replaceable>]]]
   [WHERE <replaceable class="parameter">condition</replaceable>]
   [GROUP BY <replaceable class="parameter">name_of_attr_i</replaceable> 
             [,... [, <replaceable class="parameter">name_of_attr_j</replaceable>]] [HAVING <replaceable class="parameter">condition</replaceable>]]
   [{UNION [ALL] | INTERSECT | EXCEPT} SELECT ...]
   [ORDER BY <replaceable class="parameter">name_of_attr_i</replaceable> [ASC|DESC] 
             [, ... [, <replaceable class="parameter">name_of_attr_j</replaceable> [ASC|DESC]]]];
     </synopsis>
    </para>

    <para>
     Now we will illustrate the complex syntax of the SELECT statement
     with various examples. The tables used for the examples are defined in
     <xref linkend="supplier-fig" endterm="supplier-fig">.
    </para>

    <sect3>
     <title>Simple Selects</title>

     <para>
      Here are some simple examples using a SELECT statement:

      <example>
       <title id="simple-query">Simple Query with Qualification</title>
       <para>
	To retrieve all tuples from table PART where the attribute PRICE is
	greater than 10 we formulate the following query:

	<programlisting>
   SELECT * FROM PART
     WHERE PRICE > 10;
	</programlisting>

	and get the table:

	<programlisting>
                   PNO |  PNAME  |  PRICE
                  -----+---------+--------
                    3  |  Bolt   |   15
                    4  |  Cam    |   25
	</programlisting>
       </para>

       <para>
	Using "*" in the SELECT statement will deliver all attributes from
	the table. If we want to retrieve only the attributes PNAME and PRICE
	from table PART we use the statement:

	<programlisting>
   SELECT PNAME, PRICE 
   FROM PART
   WHERE PRICE > 10;
	</programlisting>

	In this case the result is:

	<programlisting>
                      PNAME  |  PRICE
                     --------+--------
                      Bolt   |   15
                      Cam    |   25
	</programlisting>

	Note that the <acronym>SQL</acronym> SELECT corresponds to the 
	"projection" in relational algebra not to the "selection"
	(see <xref linkend="rel-alg" endterm="rel-alg"> for more details).
       </para>

       <para>
	The qualifications in the WHERE clause can also be logically connected
	using the keywords OR, AND, and NOT:

	<programlisting>
   SELECT PNAME, PRICE 
   FROM PART
   WHERE PNAME = 'Bolt' AND
         (PRICE = 0 OR PRICE < 15);
	</programlisting>

	will lead to the result:

	<programlisting>
                      PNAME  |  PRICE
                     --------+--------
                      Bolt   |   15
	</programlisting>
       </para>

       <para>
	Arithmetic operations may be used in the target list and in the WHERE
	clause. For example if we want to know how much it would cost if we
	take two pieces of a part we could use the following query:

	<programlisting>
   SELECT PNAME, PRICE * 2 AS DOUBLE
   FROM PART
   WHERE PRICE * 2 < 50;
	</programlisting>

	and we get:

	<programlisting>
                      PNAME  |  DOUBLE
                     --------+---------
                      Screw  |    20
                      Nut    |    16
                      Bolt   |    30
	</programlisting>

	Note that the word DOUBLE after the keyword AS is the new title of the
	second column. This technique can be used for every element of the
	target list to assign a new title to the resulting column. This new title
	is often referred to as alias. The alias cannot be used throughout the
	rest of the query.
       </para>
      </example>
     </para>
    </sect3>

    <sect3>
     <title>Joins</title>

     <para id="simple-join">
      The following example shows how <firstterm>joins</firstterm> are
      realized in <acronym>SQL</acronym>.
     </para>

     <para>
      To join the three tables SUPPLIER, PART and SELLS over their common
      attributes we formulate the following statement:

      <programlisting>
   SELECT S.SNAME, P.PNAME
   FROM SUPPLIER S, PART P, SELLS SE
   WHERE S.SNO = SE.SNO AND
         P.PNO = SE.PNO;
      </programlisting>

      and get the following table as a result:

      <programlisting>
                       SNAME | PNAME
                      -------+-------
                       Smith | Screw
                       Smith | Nut
                       Jones | Cam
                       Adams | Screw
                       Adams | Bolt
                       Blake | Nut
                       Blake | Bolt
                       Blake | Cam
      </programlisting>
     </para>

     <para>
      In the FROM clause we introduced an alias name for every relation
      because there are common named attributes (SNO and PNO) among the
      relations. Now we can distinguish between the common named attributes
      by simply prefixing the attribute name with the alias name followed by
      a dot. The join is calculated in the same way as shown in 
      <xref linkend="join-example" endterm="join-example">.
      First the Cartesian product

      SUPPLIER &times; PART &times; SELLS

      is derived. Now only those tuples satisfying the
      conditions given in the WHERE clause are selected (i.e. the common
      named attributes have to be equal). Finally we project out all
      columns but S.SNAME and P.PNAME. 
     </para>
    </sect3>

    <sect3>
     <title>Aggregate Operators</title>

     <para>
      <acronym>SQL</acronym> provides aggregate operators
      (e.g. AVG, COUNT, SUM, MIN, MAX) that
      take the name of an attribute as an argument. The value of the
      aggregate operator is calculated over all values of the specified
      attribute (column) of the whole table. If groups are specified in the
      query the calculation is done only over the values of a group (see next
      section).

      <example>
       <title id="aggregates-example">Aggregates</title>

       <para>
	If we want to know the average cost of all parts in table PART we use
	the following query:

	<programlisting>
   SELECT AVG(PRICE) AS AVG_PRICE
   FROM PART;
	</programlisting>
       </para>

       <para>
	The result is:

	<programlisting>
                         AVG_PRICE
                        -----------
                           14.5
	</programlisting>
       </para>

       <para>
	If we want to know how many parts are stored in table PART we use
	the statement:

	<programlisting>
   SELECT COUNT(PNO)
   FROM PART;
	</programlisting>

	and get:

	<programlisting>
                           COUNT
                          -------
                             4
	</programlisting>

       </para>
      </example>
     </para>
    </sect3>

    <sect3>
     <title>Aggregation by Groups</title>

     <para>
      <acronym>SQL</acronym> allows one to partition the tuples of a table
      into groups. Then the
      aggregate operators described above can be applied to the groups
      (i.e. the value of the aggregate operator is no longer calculated over
      all the values of the specified column but over all values of a
      group. Thus the aggregate operator is evaluated individually for every
      group.) 
     </para>

     <para>
      The partitioning of the tuples into groups is done by using the
      keywords <command>GROUP BY</command> followed by a list of
      attributes that define the
      groups. If we have 
      <command>GROUP BY A<subscript>1</subscript>, &tdot;, A<subscript>k</subscript></command>
      we partition
      the relation into groups, such that two tuples are in the same group
      if and only if they agree on all the attributes 
      A<subscript>1</subscript>, &tdot;, A<subscript>k</subscript>.

      <example>
       <title id="aggregates-groupby">Aggregates</title>
       <para>
	If we want to know how many parts are sold by every supplier we
	formulate the query:

	<programlisting>
   SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
   FROM SUPPLIER S, SELLS SE
   WHERE S.SNO = SE.SNO
   GROUP BY S.SNO, S.SNAME;
	</programlisting>

	and get:

	<programlisting>
                     SNO | SNAME | COUNT
                    -----+-------+-------
                      1  | Smith |   2
                      2  | Jones |   1
                      3  | Adams |   2
                      4  | Blake |   3
	</programlisting>
       </para>

       <para>
	Now let's have a look of what is happening here.
	First the join of the
	tables SUPPLIER and SELLS is derived:

	<programlisting>
                  S.SNO | S.SNAME | SE.PNO
                 -------+---------+--------
                    1   |  Smith  |   1
                    1   |  Smith  |   2
                    2   |  Jones  |   4
                    3   |  Adams  |   1
                    3   |  Adams  |   3
                    4   |  Blake  |   2
                    4   |  Blake  |   3
                    4   |  Blake  |   4
	</programlisting>
       </para>

       <para>
	Next we partition the tuples into groups by putting all tuples