ch08.htm

来自「Learn SQL in an easy way...」· HTM 代码 · 共 1,082 行 · 第 1/3 页

<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE TABLE INVENTORY</B>
  2  <B>(ITEM CHAR(20),</B>
  3 <B> COST NUMBER,</B>
  4  <B>ROOM CHAR(20),</B>
  5  <B>REMARKS CHAR(40));</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">Table created.
</FONT></PRE>
<P>The following <TT>INSERT</TT> fills the new <TT>INVENTORY</TT> table with data
from <TT>COLLECTION</TT>.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>INSERT INTO INVENTORY (ITEM, COST, REMARKS)</B>
  2  <B>SELECT ITEM, WORTH, REMARKS</B>
  3 <B> FROM COLLECTION;</B>

6 rows created.
</FONT></PRE>
<P>You can verify that the <TT>INSERT</TT> works with this <TT>SELECT</TT> statement:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM INVENTORY;</B>

ITEM                      COST ROOM     REMARKS
-------------------- --------- -------- ----------------------------
NBA ALL STAR CARDS         300          SOME STILL IN BIKE SPOKES
MALIBU BARBIE              150          TAN NEEDS WORK
STAR WARS GLASS            5.5          HANDLE CHIPPED
LOCK OF SPOUSES HAIR         1          HASN'T NOTICED BALD SPOT YET
SUPERMANS CAPE             250          TUGGED ON IT
STRING                    1000          SOME DAY IT WILL BE VALUABLE

6 rows selected.
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The data appears to be in the table;
	however, the transaction is not finalized until a <TT>COMMIT</TT> is issued. The
	transaction can be committed either by issuing the <TT>COMMIT</TT> command or by
	simply exiting. See Day 11 for more on the <TT>COMMIT</TT> command. 
<HR>


</BLOCKQUOTE>

<H5>ANALYSIS:</H5>
<P>You have successfully, and somewhat painlessly, moved the data from the <TT>COLLECTION</TT>
table to the new <TT>INVENTORY</TT> table!</P>
<P>The <TT>INSERT...SELECT</TT> statement requires you to follow several new rules:

<UL>
	<LI>The <TT>SELECT</TT> statement cannot select rows from the table that is being
	inserted into.
	<P>
	<LI>The number of columns in the <TT>INSERT INTO</TT> statement must equal the number
	of columns returned from the <TT>SELECT</TT> statement.
	<P>
	<LI>The data types of the columns in the <TT>INSERT INTO</TT> statement must be the
	same as the data types of the columns returned from the <TT>SELECT</TT> statement.
</UL>

<P>Another use of the <TT>INSERT...SELECT</TT> statement is to back up a table that
you are going to drop, truncate for repopulation, or rebuild. The process requires
you to create a temporary table and insert data that is contained in your original
table into the temporary table by selecting everything from the original table. For
example:</P>
<PRE><FONT COLOR="#0066FF">   SQL&gt; <B>insert into copy_table</B>
     2  <B>select * from original_table;</B>
</FONT></PRE>
<P>Now you can make changes to the original table with a clear conscience.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Later today you learn how to input
	data into a table using data from another database format. Nearly all businesses
	use a variety of database formats to store data for their organizations. The applications
	programmer is often expected to convert these formats, and you will learn some common
	methods for doing just that. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">The UPDATE Statement</FONT></H2>
<P>The purpose of the <TT>UPDATE</TT> statement is to change the values of existing
records. The syntax is</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">UPDATE table_name
SET columnname1 = value1
[, columname2 = value2]...
WHERE search_condition
</FONT></PRE>
<P>This statement checks the <TT>WHERE</TT> clause first. For all records in the
given table in which the <TT>WHERE</TT> clause evaluates to <TT>TRUE</TT>, the corresponding
value is updated.
<H4><FONT COLOR="#000077">Example 8.4</FONT></H4>
<P>This example illustrates the use of the <TT>UPDATE</TT> statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> UPDATE COLLECTION</B>
  2  <B>SET WORTH = 900</B>
  3  <B>WHERE ITEM = 'STRING';</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">1 row updated.
</FONT></PRE>
<P>To confirm the change, the query</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM COLLECTION</B>
  2  <B>WHERE ITEM = 'STRING';</B>
</FONT></PRE>
<P>yields</P>
<PRE><FONT COLOR="#0066FF">ITEM                     WORTH REMARKS
-------------------- --------- ------------------------------

STRING                     900 SOME DAY IT WILL BE VALUABLE
</FONT></PRE>
<P>Here is a multiple-column update:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>update collection</B>
  2  <B>set worth = 900, item = ball</B>
  3  <B>where item = 'STRING';</B>

1 row updated.
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Your implementation might use a
	different syntax for multiple-row updates. 
<HR>
</P>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice in the set that <TT>900</TT>
	does not have quotes, because it is a numeric data type. On the other hand, <TT>String</TT>
	is a character data type, which requires the quotes. 
<HR>


</BLOCKQUOTE>

<H4><FONT COLOR="#000077">Example 8.5</FONT></H4>
<P>If the <TT>WHERE</TT> clause is omitted, every record in the <TT>COLLECTION</TT>
table is updated with the value given.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE COLLECTION
</B>  2 <B> SET WORTH = 555;</B>

6 rows updated.
</FONT></PRE>
<P>Performing a <TT>SELECT</TT> query shows that every record in the database was
updated with that value:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM COLLECTION;</B>

ITEM                     WORTH REMARKS
-------------------- --------- ------------------------------
NBA ALL STAR CARDS         555 SOME STILL IN BIKE SPOKES
MALIBU BARBIE              555 TAN NEEDS WORK
STAR WARS GLASS            555 HANDLE CHIPPED
LOCK OF SPOUSES HAIR       555 HASN'T NOTICED BALD SPOT YET
SUPERMANS CAPE             555 TUGGED ON IT
STRING                     555 SOME DAY IT WILL BE VALUABLE

6 rows selected.
</FONT></PRE>
<P>You, of course, should check whether the column you are updating allows unique
values only.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>If you omit the <TT>WHERE</TT>
	clause from the <TT>UPDATE</TT> statement, all records in the given table are updated.
	
<HR>


</BLOCKQUOTE>

<P>Some database systems provide an extension to the standard <TT>UPDATE</TT> syntax.
SQL Server's Transact-SQL language, for instance, enables programmers to update the
contents of a table based on the contents of several other tables by using a <TT>FROM</TT>
clause. The extended syntax looks like this:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">UPDATE table_name
SET columnname1 = value1
[, columname2 = value2]...
FROM table_list
WHERE search_condition
</FONT></PRE>
<H4><FONT COLOR="#000077">Example 8.6</FONT></H4>
<P>Here's an example of the extension:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE COLLECTION</B>
  2  <B>SET WORTH = WORTH * 0.005;</B>
</FONT></PRE>
<P>that changes the table to this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> SELECT * FROM COLLECTION;</B>

ITEM                     WORTH REMARKS
-------------------- -------- ----------------------------
NBA ALL STAR CARDS       2.775 SOME STILL IN BIKE SPOKES
MALIBU BARBIE            2.775 TAN NEEDS WORK
STAR WARS GLASS          2.775 HANDLE CHIPPED
LOCK OF SPOUSES HAIR     2.775 HASN'T NOTICED BALD SPOT YET
SUPERMANS CAPE           2.775 TUGGED ON IT
STRING                   2.775 SOME DAY IT WILL BE VALUABLE

6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This syntax is useful when the contents of one table need to be updated following
the manipulation of the contents of several other tables. Keep in mind that this
syntax is nonstandard and that you need to consult the documentation for your particular
database management system before you use it.</P>
<P>The <TT>UPDATE</TT> statement can also update columns based on the result of an
arithmetic expression. When using this technique, remember the requirement that the
data type of the result of the expression must be the same as the data type of the
field that is being modified. Also, the size of the value must fit within the size
of the field that is being modified.</P>
<P>Two problems can result from the use of calculated values: truncation and overflow.<I>
Truncation </I>results when the database system converts a fractional number to an
integer, for instance. <I>Overflow </I>results when the resulting value is larger
than the capacity of the modified column, which will cause an error to be returned
by your database system.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Some database systems handle the
	overflow problem for you. Oracle7 converts the number to exponential notation and
	presents the number that way. You should keep this potential error in mind when using
	number data types. 
<HR>
</P>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>If you update a column(s) and notice
	an error after you run the update, issue the <TT>ROLLBACK</TT> command (as you would
	for an incorrect insert) to void the update. See Day 11 for more on the <TT>ROLLBACK</TT>
	command. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">The DELETE Statement</FONT></H2>
<P>In addition to adding data to a database, you will also need to delete data from
a database. The syntax for the <TT>DELETE</TT> statement is</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">DELETE FROM tablename
WHERE condition
</FONT></PRE>
<P>The first thing you will probably notice about the <TT>DELETE</TT> command is
that it doesn't have a prompt. Users are accustomed to being prompted for assurance
when, for instance, a directory or file is deleted at the operating system level.
<TT>Are you sure? (Y/N)</TT> is a common question asked before the operation is performed.
Using SQL, when you instruct the DBMS to delete a group of records from a table,
it obeys your command without asking. That is, when you tell SQL to delete a group
of records, it will really do it!</P>
<P>On Day 11 you will learn about transaction control. Transactions are database
operations that enable programmers to either <TT>COMMIT</TT> or <TT>ROLLBACK</TT>
changes to the database. These operations are very useful in online transaction-processing
applications in which you want to execute a batch of modifications to the database
in one logical execution. Data integrity problems will occur if operations are performed
while other users are modifying the data at the same time. For now, assume that no
transactions are being undertaken.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Some implementations, for example,
	Oracle, automatically issue a <TT>COMMIT</TT> command when you exit SQL. 
<HR>


</BLOCKQUOTE>

<P>Depending on the use of the <TT>DELETE</TT> statement's <TT>WHERE</TT> clause,
SQL can do the following:

<UL>
	<LI>Delete single rows
	<P>
	<LI>Delete multiple rows
	<P>
	<LI>Delete all rows
	<P>
	<LI>Delete no rows
</UL>

<P>Here are several points to remember when using the <TT>DELETE</TT> statement:

<UL>
	<LI>The <TT>DELETE</TT> statement cannot delete an individual field's values (use
	<TT>UPDATE</TT> instead). The <TT>DELETE</TT> statement deletes entire records from
	a single table.
	<P>
	<LI>Like <TT>INSERT</TT> and <TT>UPDATE</TT>, deleting records from one table can
	cause referential integrity problems within other tables. Keep this potential problem
	area in mind when modifying data within a database.
	<P>
	<LI>Using the <TT>DELETE</TT> statement deletes only records, not the table itself.
	Use the <TT>DROP TABLE</TT> statement (see Day 9) to remove an entire table.
</UL>

<H4><FONT COLOR="#000077">Example 8.7</FONT></H4>
<P>This example shows you how to delete all the records from <TT>COLLECTION</TT>
where <TT>WORTH</TT> is less than <TT>275</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>DELETE FROM COLLECTION</B>
  2  <B>WHERE WORTH &lt; 275;
</B>
4 rows deleted.
</FONT></PRE>
<P>The result is a table that looks like this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM COLLECTION;</B>

ITEM                     WORTH REMARKS
-------------------- --------- ------------------------------
NBA ALL STAR CARDS         300 SOME STILL IN BIKE SPOKES

STRING                    1000 SOME DAY IT WILL BE VALUABLE
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Like the <TT>UPDATE </TT>statement,
	if you omit a <TT>WHERE</TT> clause from the <TT>DELETE</TT> statement, all rows
	in that particular table will be deleted. 
<HR>


</BLOCKQUOTE>

<P>Example 8.8 uses all three data manipulation statements to perform a set of database
operations.
<H4><FONT COLOR="#000077">Example 8.8</FONT></H4>
<P>This example inserts some new rows into the <TT>COLLECTION</TT> table you used
earlier today.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>INSERT INTO COLLECTION</B>
  2  <B>VALUES('CHIA PET', 5,'WEDDING GIFT');</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">1 row created.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>INSERT INTO COLLECTION</B>
  2 <B> VALUES('TRS MODEL III', 50, 'FIRST COMPUTER');</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>