ch08.htm

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

<PRE><FONT COLOR="#0066FF">1 row created.
</FONT></PRE>
<P>Now create a new table and copy this data to it:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> CREATE TABLE TEMP</B>
  2 <B> (NAME CHAR(20),</B>
  3  <B>VALUE NUMBER,</B>
  4  <B>REMARKS CHAR(40));</B>

Table created.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>INSERT INTO TEMP(NAME, VALUE, REMARKS)</B>
  2  <B>SELECT ITEM, WORTH, REMARKS</B>
  3 <B> FROM COLLECTION;</B>

4 rows created.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM TEMP;</B>

NAME                     VALUE REMARKS
-------------------- --------- ------------------------------
NBA ALL STAR CARDS         300 SOME STILL IN BIKE SPOKES
STRING                    1000 SOME DAY IT WILL BE VALUABLE
CHIA PET                     5 WEDDING GIFT
TRS MODEL III               50 FIRST COMPUTER
</FONT></PRE>
<P>Now change some values:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE TEMP</B>
  2  <B>SET VALUE = 100</B>
  3  <B>WHERE NAME = 'TRS MODEL III';</B>

1 row updated.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE TEMP</B>
  2  <B>SET VALUE = 8</B>
  3  <B>WHERE NAME = 'CHIA PET';</B>

1 row updated.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM TEMP;</B>

NAME                     VALUE REMARKS
-------------------- --------- ----------------------------
NBA ALL STAR CARDS         300 SOME STILL IN BIKE SPOKES
STRING                    1000 SOME DAY IT WILL BE VALUABLE
CHIA PET                     8 WEDDING GIFT
TRS MODEL III              100 FIRST COMPUTER
</FONT></PRE>
<P>And update these values back to the original table:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">INSERT COLLECTION
SELECT * FROM TEMP;
DROP TABLE TEMP;
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The <TT>DROP TABLE</TT> and <TT>CREATE TABLE</TT> statements are discussed in
greater detail on Day 9. For now, these statements basically do what their names
suggest. <TT>CREATE TABLE</TT> builds a new table with the format you give it, and
<TT>DROP TABLE</TT> deletes the table. Keep in mind that <TT>DROP TABLE</TT> permanently
removes a table, whereas <TT>DELETE FROM &lt;TableName&gt;</TT> removes only the
records from a table.</P>
<P>To check what you have done, select out the records from the <TT>COLLECTION</TT>
table. You will see that the changes you made now exist in the <TT>COLLECTION</TT>
table.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM COLLECTION;</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">NAME                     VALUE REMARKS
-------------------- -------- ----------------------------
NBA ALL STAR CARDS         300 SOME STILL IN BIKE SPOKES
STRING                    1000 SOME DAY IT WILL BE VALUABLE
CHIA PET                     8 WEDDING GIFT
TRS MODEL III              100 FIRST COMPUTER
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The previous example used all three data manipulation commands--<TT>INSERT</TT>,
<TT>UPDATE</TT>, and <TT>DELETE</TT>--to perform a set of operations on a table.
The <TT>DELETE</TT> statement is the easiest of the three to use.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Always keep in mind that any
	modifications can affect the referential integrity of your database. Think through
	all your database editing steps to make sure that you have updated all tables correctly.
	
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">Importing and Exporting Data from Foreign Sources</FONT></H2>
<P>The <TT>INSERT</TT>, <TT>UPDATE</TT>, and <TT>DELETE</TT> statements are extremely
useful from within a database program. They are used with the <TT>SELECT</TT> statement
to provide the foundation for all other database operations you will perform. However,
SQL as a language does not have a way to import or export of data from foreign data
sources. For instance, your office may have been using a dBASE application for several
years now that has outgrown itself. Now your manager wants to convert this application
to a client/server application using the Oracle RDBMS. Unfortunately for you, these
dBASE files contain thousands of records that must be converted to an Oracle database.
Obviously, the <TT>INSERT</TT>, <TT>UPDATE</TT>, and <TT>DELETE</TT> commands will
help you after your Oracle database has been populated, but you would rather quit
than retype 300,000 records. Fortunately, Oracle and other manufacturers provide
tools that will assist you in this task.</P>
<P>Nearly all database systems allow you to import and export data using ASCII text
file formats. Although the SQL language does not include this feature, SQL will not
do you (or your boss) much good when you have an empty database. We will examine
the import/export tools available in the following products: Microsoft Access, Microsoft
and Sybase SQL Server, and Personal Oracle7.
<H3><FONT COLOR="#000077">Microsoft Access</FONT></H3>
<P>Microsoft Access is a PC-only database product that contains many of the features
of a relational database management system. Access also includes powerful reporting
tools, a macro language similar to Visual Basic, and the capability to import and
export data from various database and text file formats. This section examines this
last feature, particularly the capability to export to delimited text files. Delimited
means that each field is separated, or delimited, by some special character. This
character is often a comma, a quotation mark, or a space.</P>
<P>Access allows you to import and export various database formats, including dBASE,
FoxPro, and SQL Database. The SQL Database option is actually an ODBC data source
connection. (Microsoft ODBC is covered on Day 13, &quot;Advanced SQL Topics.&quot;)
For this discussion, you want to select the Export option and then choose the Text
(Fixed Width) option.</P>
<P>After opening an Access database (with the File | Open), select Export. A Destination
dialog box (for Exporting) is displayed. Select the Text (Fixed Width) option. This
option allows you to output your Access tables to text files in which each data type
is a fixed width. For example, a character data field of length 30 will be output
to the file as a field 30 characters long. If the field's data takes up less space
than 30 characters, it will be padded with spaces. Eventually, you will be asked
to set up the export file format. Figure 8.1 shows the Import/Export Setup dialog
box.</P>
<P><A NAME="01"></A><A HREF="01.htm"><B>Figure 8.1.<BR>
</B></A><I>The Import/Export Setup dialog box.</I></P>
<P>Notice that in this dialog box you can select the Text Delimiter and the Field
Separator for your export file. As a final step, save the specification for use later.
This specification is stored internally within the database.
<H3><FONT COLOR="#000077">Microsoft and Sybase SQL Server</FONT></H3>
<P>Microsoft and Sybase have jointly developed a powerful database system that is
very popular in client/server application development. The name of this system is
SQL Server. Microsoft has agreed to develop versions of the RDBMS for some platforms,
and Sybase has developed its version for all the other platforms (usually the larger
ones). Although the arrangement has changed somewhat in recent years, we mention
this agreement here to help you avoid confusion when you begin examining the various
database systems available on the market today.</P>
<P>SQL Server provides file import/export capabilities with the bcp tool. bcp is
short for &quot;bulk copy.&quot; The basic concept behind bcp is the same as that
behind Microsoft Access. Unfortunately, the bcp tool requires you to issue commands
from the operating system command prompt, instead of through dialog boxes or windows.</P>
<P>Bcp imports and exports fixed-width text files. It is possible to export a file
using the Microsoft Access method described earlier and then import that same file
directly into an SQL Server table using bcp. bcp uses format files (usually with
an <TT>.FMT</TT> extension) to store the import specification. This specification
tells bcp the column names, field widths, and field delimiters. You can run bcp from
within an SQL database build script to completely import data after the database
has been built.
<H3><FONT COLOR="#000077">Personal Oracle7</FONT></H3>
<P>Personal Oracle7 allows you to import and export data from ASCII text files containing
delimited or fixed-length records. The tool you use is SQL*Loader. This graphical
tool uses a control file (with the <TT>.CTL</TT> extension). This file is similar
to SQL Server's format (FMT) file. The information contained in this file tells SQL*Loader
what it needs to know to load the data from the file.</P>
<P>The SQL*Loader dialog box appears in Figure 8.2.</P>
<P><A NAME="02"></A><A HREF="02.htm"><B>Figure 8.2.<BR>
</B></A><I>The SQL*Loader dialog box.</I></P>
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>SQL provides three statements that you can use to manipulate data within a database.</P>
<P>The <TT>INSERT</TT> statement has two variations. The <TT>INSERT...VALUES</TT>
statement inserts a set of values into one record. The <TT>INSERT...SELECT</TT> statement
is used in combination with a <TT>SELECT</TT> statement to insert multiple records
into a table based on the contents of one or more tables. The <TT>SELECT</TT> statement
can join multiple tables, and the results of this join can be added to another table.</P>
<P>The <TT>UPDATE</TT> statement changes the values of one or more columns based
on some condition. This updated value can also be the result of an expression or
calculation.</P>
<P>The <TT>DELETE</TT> statement is the simplest of the three statements. It deletes
all rows from a table based on the result of an optional <TT>WHERE</TT> clause. If
the <TT>WHERE</TT> clause is omitted, all records from the table are deleted.</P>
<P>Modern database systems supply various tools for data manipulation. Some of these
tools enable developers to import or export data from foreign sources. This feature
is particularly useful when a database is upsized or downsized to a different system.
Microsoft Access, Microsoft and Sybase SQL Server, and Personal Oracle7 include many
options that support the migration of data between systems.
<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>

<DL>
	<DD><B>Q Does SQL have a statement for file import/export operations?</B>
	<P><B>A</B> No. Import and export are implementation-specific operations. In other
	words, the ANSI committee allows individual manufacturers to create whatever features
	or enhancements they feel are necessary.</P>
	<P><B>Q Can I copy data from a table into itself using the </B>INSERT<B> command?
	I would like to make duplicate copies of all the existing records and change the
	value of one field.</B></P>
	<P><B>A</B> No, you cannot insert data into the same table that you selected from.
	However, you can select the original data into a temporary table. (True temporary
	tables are discussed on Day 14.) Then modify the data in this temporary table and
	select back into the original table. Make sure that you watch out for unique fields
	you may have already created. A unique field means that the particular field must
	contain a unique value for each row of data that exists in its table.</P>
	<P><B>Q You have stressed using caution when issuing </B>INSERT<B>, </B>UPDATE<B>,
	and </B>DELETE <B>commands, but simple fixes seem to be available to correct whatever
	I did wrong. Is that a fair statement?</B></P>
	<P><B>A </B>Yes. For example, a simple way to fix a misspelled name is to issue a
	<TT>ROLLBACK</TT> command and redo the insert. Another fix would be to do an update
	to fix the name. Or you could delete the row and redo the insert with the corrected
	spelling of the name.</P>
	<P>But suppose you inserted a million rows into a table and didn't notice that you
	had misspelled a name when you issued the <TT>COMMIT</TT> command. A few weeks later,
	someone notices some bad data. You have had two weeks' worth of database activity.
	You would more than likely have to issue individual updates to make individual corrections,
	instead of making any type of global change. In most cases you probably will not
	know what to change. You may have to restore the database.
</DL>

<H2><FONT COLOR="#000077">Workshop</FONT></H2>
<P>The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;
<H3><FONT COLOR="#000077">Quiz</FONT></H3>

<DL>
	<DD><B>1. </B>What is wrong with the following statement?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">DELETE COLLECTION;</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>2. </B>What is wrong with the following statement?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">INSERT INTO COLLECTION 
SELECT * FROM TABLE_2</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>3. </B>What is wrong with the following statement?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">UPDATE COLLECTION (&quot;HONUS WAGNER CARD&quot;, 
25000, &quot;FOUND IT&quot;);</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>4.</B> What would happen if you issued the following statement?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>DELETE * FROM COLLECTION;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<P><B>5.</B> What would happen if you issued the following statement?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>DELETE FROM COLLECTION;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>6. </B>What would happen if you issued the following statement?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE COLLECTION
     SET WORTH = 555
     SET REMARKS = 'UP FROM 525';</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>7. </B>Will the following SQL statement work?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; I<B>NSERT INTO COLLECTION
     SET VALUES = 900
     WHERE ITEM = 'STRING';</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>8. </B>Will the following SQL statement work?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE COLLECTION
     SET VALUES = 900
     WHERE ITEM = 'STRING';</B></FONT></PRE>

</BLOCKQUOTE>

<H3><FONT COLOR="#000077">Exercises</FONT></H3>

<DL>
	<DD><B>1.</B> Try inserting values with incorrect data types into a table. Note the
	errors and then insert values with correct data types into the same table.
	<P><B>2. </B>Using your database system, try exporting a table (or an entire database)
	to some other format. Then import the data back into your database. Familiarize yourself
	with this capability. Also, export the tables to another database format if your
	DBMS supports this feature. Then use the other system to open these files and examine
	them.
</DL>

<H1></H1>
<CENTER>
<P>
<HR>
<A HREF="../ch07/ch07.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch09/ch09.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>