sql.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

    </sect3>
   </sect2>

   <sect2 id="datadef">
    <title>Data Definition</title>

    <para>
     There is a set of commands used for data definition included in the
     <acronym>SQL</acronym> language. 
    </para>

    <sect3 id="create">
     <title id="create-title">Create Table</title>

     <para>
      The most fundamental command for data definition is the
      one that creates a new relation (a new table). The syntax of the
      <command>CREATE TABLE</command> command is:

      <synopsis>
CREATE TABLE <replaceable class="parameter">table_name</replaceable>
    (<replaceable class="parameter">name_of_attr_1</replaceable> <replaceable class="parameter">type_of_attr_1</replaceable>
     [, <replaceable class="parameter">name_of_attr_2</replaceable> <replaceable class="parameter">type_of_attr_2</replaceable> 
     [, ...]]);
      </synopsis>

      <example>
       <title id="table-create">Table Creation</title>

       <para>
	To create the tables defined in
	<xref linkend="supplier-fig" endterm="supplier-fig"> the
	following <acronym>SQL</acronym> statements are used:

	<programlisting>
CREATE TABLE SUPPLIER
    (SNO   INTEGER,
     SNAME VARCHAR(20),
     CITY  VARCHAR(20));
     </programlisting>

     <programlisting>
CREATE TABLE PART
    (PNO   INTEGER,
     PNAME VARCHAR(20),
     PRICE DECIMAL(4 , 2));
     </programlisting>

     <programlisting>
CREATE TABLE SELLS
    (SNO INTEGER,
     PNO INTEGER);
	</programlisting>
       </para>
      </example>
     </para>
    </sect3>

    <sect3>
     <title>Data Types in <acronym>SQL</acronym></title>

     <para>
      The following is a list of some data types that are supported by 
      <acronym>SQL</acronym>:

      <itemizedlist>
       <listitem>
	<para>
	 INTEGER: signed fullword binary integer (31 bits precision).
	</para>
       </listitem>

       <listitem>
	<para>
	 SMALLINT: signed halfword binary integer (15 bits precision).
	</para>
       </listitem>

       <listitem>
	<para>
	 DECIMAL (<replaceable class="parameter">p</replaceable>[,<replaceable class="parameter">q</replaceable>]):
	 signed packed decimal number of up to
	 <replaceable class="parameter">p</replaceable>
	 digits, with
	 <replaceable class="parameter">q</replaceable>
	 digits to the right of the decimal point.
	 If <replaceable class="parameter">q</replaceable>
	 is omitted it is assumed to be 0.
	</para>
       </listitem>

       <listitem>
	<para>
	 FLOAT: signed doubleword floating point number.
	</para>
       </listitem>

       <listitem>
	<para>
	 VARCHAR(<replaceable class="parameter">n</replaceable>):
	 varying length character string of maximum length
	 <replaceable class="parameter">n</replaceable>.
	</para>
       </listitem>

       <listitem>
	<para>
	 CHAR(<replaceable class="parameter">n</replaceable>):
	 fixed length character string of length
	 <replaceable class="parameter">n</replaceable>.
	</para>
       </listitem>

      </itemizedlist>
     </para>
    </sect3>

    <sect3>
     <title>Create Index</title>

     <para>
      Indexes are used to speed up access to a relation. If a relation <classname>R</classname>
      has an index on attribute <classname>A</classname> then we can
      retrieve all tuples <replaceable>t</replaceable>
      having
      <replaceable>t</replaceable>(<classname>A</classname>) = <replaceable>a</replaceable>
      in time roughly proportional to the number of such
      tuples <replaceable>t</replaceable>
      rather than in time proportional to the size of <classname>R</classname>.
     </para>

     <para>
      To create an index in <acronym>SQL</acronym>
      the <command>CREATE INDEX</command> command is used. The syntax is:

      <programlisting>
CREATE INDEX <replaceable class="parameter">index_name</replaceable> 
    ON <replaceable class="parameter">table_name</replaceable> ( <replaceable class="parameter">name_of_attribute</replaceable> );
      </programlisting>
     </para>

     <para>
      <example>
       <title id="index-create">Create Index</title>

       <para>
	To create an index named I on attribute SNAME of relation SUPPLIER
	we use the following statement:

      <programlisting>
CREATE INDEX I ON SUPPLIER (SNAME);
      </programlisting>
     </para>

       <para>
	The created index is maintained automatically, i.e. whenever a new
	tuple is inserted into the relation SUPPLIER the index I is
	adapted. Note that the only changes a user can perceive when an
	index is present are increased speed for <command>SELECT</command>
	and decreases in speed of updates.
       </para>
      </example>
     </para>
    </sect3>

    <sect3>
     <title>Create View</title>

     <para>
      A view may be regarded as a <firstterm>virtual table</firstterm>,
      i.e. a table that
      does not <emphasis>physically</emphasis> exist in the database
      but looks to the user
      as if it does. By contrast, when we talk of a
      <firstterm>base table</firstterm> there is
      really a physically stored counterpart of each row of the table
      somewhere in the physical storage.
     </para>

     <para>
      Views do not have their own, physically separate, distinguishable
      stored data. Instead, the system stores the definition of the
      view (i.e. the rules about how to access physically stored base
      tables in order to materialize the view) somewhere in the system
      catalogs (see
      <xref linkend="tutorial-catalogs-title" endterm="tutorial-catalogs-title">). For a
      discussion on different techniques to implement views refer to
<!--
      section
      <xref linkend="view-impl" endterm="view-impl">.
-->
      <citetitle>SIM98</citetitle>.
     </para>

     <para>
      In <acronym>SQL</acronym> the <command>CREATE VIEW</command>
      command is used to define a view. The syntax
      is:

      <programlisting>
CREATE VIEW <replaceable class="parameter">view_name</replaceable>
    AS <replaceable class="parameter">select_stmt</replaceable>
      </programlisting>

      where <replaceable class="parameter">select_stmt</replaceable> 
      is a valid select statement as defined
      in <xref linkend="select-title" endterm="select-title">.
      Note that <replaceable class="parameter">select_stmt</replaceable> is
      not executed when the view is created. It is just stored in the 
      <firstterm>system catalogs</firstterm>
      and is executed whenever a query against the view is made.
     </para>

     <para>
      Let the following view definition be given (we use
      the tables from
      <xref linkend="supplier-fig" endterm="supplier-fig"> again):

      <programlisting>
CREATE VIEW London_Suppliers
    AS SELECT S.SNAME, P.PNAME
        FROM SUPPLIER S, PART P, SELLS SE
        WHERE S.SNO = SE.SNO AND
              P.PNO = SE.PNO AND
              S.CITY = 'London';
      </programlisting>
     </para>

     <para>
      Now we can use this <firstterm>virtual relation</firstterm>
      <classname>London_Suppliers</classname> as
      if it were another base table:

      <programlisting>
SELECT * FROM London_Suppliers
    WHERE PNAME = 'Screw';
      </programlisting>

      which will return the following table:

      <programlisting>
 SNAME | PNAME
-------+-------
 Smith | Screw                 
      </programlisting>
     </para>

     <para>
      To calculate this result the database system has to do a
      <emphasis>hidden</emphasis>
      access to the base tables SUPPLIER, SELLS and PART first. It
      does so by executing the query given in the view definition against
      those base tables. After that the additional qualifications
      (given in the
      query against the view) can be applied to obtain the resulting
      table.
     </para>
    </sect3>

    <sect3>
     <title>Drop Table, Drop Index, Drop View</title>

     <para>
      To destroy a table (including all tuples stored in that table) the
      <command>DROP TABLE</command> command is used:

      <programlisting>
DROP TABLE <replaceable class="parameter">table_name</replaceable>;
       </programlisting>
      </para>

     <para>
      To destroy the SUPPLIER table use the following statement:

      <programlisting>
DROP TABLE SUPPLIER;
      </programlisting>
     </para>

     <para>
      The <command>DROP INDEX</command> command is used to destroy an index:

      <programlisting>
DROP INDEX <replaceable class="parameter">index_name</replaceable>;
      </programlisting>
     </para>

     <para>
      Finally to destroy a given view use the command <command>DROP
      VIEW</command>:

      <programlisting>
DROP VIEW <replaceable class="parameter">view_name</replaceable>;
      </programlisting>
     </para>
    </sect3>
   </sect2>

   <sect2>
    <title>Data Manipulation</title>

    <sect3>
     <title>Insert Into</title>

     <para>
      Once a table is created (see
      <xref linkend="create-title" endterm="create-title">), it can be filled
      with tuples using the command <command>INSERT INTO</command>.
      The syntax is:

      <programlisting>
INSERT INTO <replaceable class="parameter">table_name</replaceable> (<replaceable class="parameter">name_of_attr_1</replaceable> 
    [, <replaceable class="parameter">name_of_attr_2</replaceable> [,...]])
    VALUES (<replaceable class="parameter">val_attr_1</replaceable> [, <replaceable class="parameter">val_attr_2</replaceable> [, ...]]);
      </programlisting>
     </para>

     <para>
      To insert the first tuple into the relation SUPPLIER (from
      <xref linkend="supplier-fig" endterm="supplier-fig">) we use the
      following statement:

      <programlisting>
INSERT INTO SUPPLIER (SNO, SNAME, CITY)
    VALUES (1, 'Smith', 'London');
      </programlisting>
     </para>

     <para>
      To insert the first tuple into the relation SELLS we use:

      <programlisting>
INSERT INTO SELLS (SNO, PNO)
    VALUES (1, 1);
      </programlisting>
     </para>
    </sect3>

    <sect3>
     <title>Update</title>

     <para>
      To change one or more attribute values of tuples in a relation the
      <command>UPDATE</command> command is used. The syntax is:

      <programlisting>
UPDATE <replaceable class="parameter">table_name</replaceable>
    SET <replaceable class="parameter">name_of_attr_1</replaceable> = <replaceable class="parameter">value_1</replaceable> 
        [, ... [, <replaceable class="parameter">name_of_attr_k</replaceable> = <replaceable class="parameter">value_k</replaceable>]]
    WHERE <replaceable class="parameter">condition</replaceable>;
      </programlisting>
     </para>

     <para>
      To change the value of attribute PRICE of the part 'Screw' in the
      relation PART we use:

      <programlisting>
UPDATE PART
    SET PRICE = 15
    WHERE PNAME = 'Screw';
      </programlisting>
     </para>

     <para>
      The new value of attribute PRICE of the tuple whose name is 'Screw' is
      now 15.
     </para>
    </sect3>

    <sect3>
     <title>Delete</title>

     <para>
      To delete a tuple from a particular table use the command DELETE
      FROM. The syntax is:

      <programlisting>
DELETE FROM <replaceable class="parameter">table_name</replaceable>
    WHERE <replaceable class="parameter">condition</replaceable>;
      </programlisting>
     </para>

     <para>
      To delete the supplier called 'Smith' of the table SUPPLIER the
      following statement is used:

      <programlisting>
DELETE FROM SUPPLIER
    WHERE SNAME = 'Smith';
      </programlisting>
     </para>
    </sect3>
   </sect2>

   <sect2 id="tutorial-catalogs">
    <title id="tutorial-catalogs-title">System Catalogs</title>

    <para>
     In every <acronym>SQL</acronym> database system
     <firstterm>system catalogs</firstterm> are used to keep
     track of which tables, views indexes etc. are defined in the
     database. These system catalogs can be queried as if they were normal
     relations. For example there is one catalog used for the definition of
     views. This catalog stores the query from the view definition. Whenever
     a query against a view is made, the system first gets the
     <firstterm>view definition query</firstterm> out of the catalog
     and materializes the view
     before proceeding with the user query (see
<!--
      section
      <xref lin