sql.sgml

关系型数据库 Postgresql 6.5.2

   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 percept when an index is present
	are an increased speed.
       </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="catalogs-title" endterm="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 P.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
      DROP TABLE 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 DROP INDEX 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 DROP VIEW:

      <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
      UPDATE 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="catalogs">
    <title id="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 linkend="view-impl" endterm="view-impl">.
-->
     <citetitle>SIM98</citetitle>
     for a more detailed
     description). For more information about system catalogs refer to
     <citetitle>DATE</citetitle>.
    </para>
   </sect2>

   <sect2>
    <title>Embedded <acronym>SQL</acronym></title>

    <para>
     In this section we will sketch how <acronym>SQL</acronym> can be
     embedded into a host language (e.g. <literal>C</literal>).
     There are two main reasons why we want to use <acronym>SQL</acronym>
     from a host language:

     <itemizedlist>
      <listitem>
       <para>
	There are queries that cannot be formulated using pure <acronym>SQL</acronym>
	(i.e. recursive queries). To be able to perform such queries we need a
	host language with a greater expressive power than
	<acronym>SQL</acronym>.
       </para>
      </listitem>

      <listitem>
       <para>
	We simply want to access a database from some application that
	is written in the host language (e.g. a ticket reservation system
	with a graphical user interface is written in C and the information
	about which tickets are still left is stored in a database that can be
	accessed using embedded <acronym>SQL</acronym>).
       </para>
      </listitem>
     </itemizedlist>
    </para>

    <para>
     A program using embedded <acronym>SQL</acronym>
     in a host language consists of statements
     of the host language and of
     <firstterm>embedded <acronym>SQL</acronym></firstterm>
     (<acronym>ESQL</acronym>) statements. Every <acronym>ESQL</acronym>
     statement begins with the keywords <command>EXEC SQL</command>.
     The <acronym>ESQL</acronym> statements are
     transformed to statements of the host language
     by a <firstterm>precompiler</firstterm>
     (which usually inserts
     calls to library routines that perform the various <acronym>SQL</acronym>
     commands). 
    </para>

    <para>
     When we look at the examples throughout
     <xref linkend="select-title" endterm="select-title"> we
     realize that the result of the queries is very often a set of
     tuples. Most host languages are not designed to operate on sets so we
     need a mechanism to access every single tuple of the set of tuples
     returned by a SELECT statement. This mechanism can be provided by
     declaring a <firstterm>cursor</firstterm>.
     After that we can use the FETCH command to
     retrieve a tuple and set the cursor to the next tuple.
    </para>

    <para>
     For a detailed discussion on embedded <acronym>SQL</acronym>
     refer to
     [<xref linkend="DATE97" endterm="DATE97">],
     [<xref linkend="DATE94" endterm="DATE94">],
     or
     [<xref linkend="ULL88" endterm="ULL88">].
    </para>
   </sect2>
  </sect1>
 </chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->