?? ch08.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Ch 8 -- Manipulating Data</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="sams.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="ch07.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch07.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="ch09.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch09.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 8 -<BR>
Manipulating Data</FONT></H1>
</CENTER>
<H2><FONT COLOR="#000077">Objectives</FONT></H2>
<P>Today we discuss data manipulation. By the end of the day, you should understand:
<UL>
<LI>How to manipulate data using the <TT>INSERT</TT>, <TT>UPDATE</TT>, and <TT>DELETE</TT>
commands
<P>
<LI>The importance of using the <TT>WHERE</TT> clause when you are manipulating data
<P>
<LI>The basics of importing and exporting data from foreign data sources
</UL>
<H2><FONT COLOR="#000077">Introduction to Data Manipulation Statements</FONT></H2>
<P>Up to this point you have learned how to retrieve data from a database using every
selection criterion imaginable. After this data is retrieved, you can use it in an
application program or edit it. Week 1 focused on retrieving data. However, you may
have wondered how to enter data into the database in the first place. You may also
be wondering what to do with data that has been edited. Today we discuss three SQL
statements that enable you to manipulate the data within a database's table. The
three statements are as follows:
<UL>
<LI>The <TT>INSERT</TT> statement
<P>
<LI>The <TT>UPDATE</TT> statement
<P>
<LI>The <TT>DELETE</TT> statement
</UL>
<P>You may have used a PC-based product such as Access, dBASE IV, or FoxPro to enter
your data in the past. These products come packaged with excellent tools to enter,
edit, and delete records from databases. One reason that SQL provides data manipulation
statements is that it is primarily used within application programs that enable the
user to edit the data using the application's own tools. The SQL programmer needs
to be able to return the data to the database using SQL. In addition, most large-scale
database systems are not designed with the database designer or programmer in mind.
Because these systems are designed to be used in high-volume, multiuser environments,
the primary design emphasis is placed on the query optimizer and data retrieval engines.</P>
<P>Most commercial relational database systems also provide tools for importing and
exporting data. This data is traditionally stored in a delimited text file format.
Often a format file is stored that contains information about the table being imported.
Tools such as Oracle's SQL*Loader, SQL Server's bcp (bulk copy), and Microsoft Access
Import/Export are covered at the end of the day.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Today's examples were generated
with Personal Oracle7. Please note the minor differences in the appearance of commands
and the way data is displayed in the various implementations.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077">The INSERT Statement</FONT></H2>
<P>The <TT>INSERT</TT> statement enables you to enter data into the database. It
can be broken down into two statements:</P>
<PRE><FONT COLOR="#0066FF">INSERT...VALUES
</FONT></PRE>
<P>and</P>
<PRE><FONT COLOR="#0066FF">INSERT...SELECT
</FONT></PRE>
<H3><FONT COLOR="#000077">The INSERT...VALUES Statement</FONT></H3>
<P>The <TT>INSERT...VALUES</TT> statement enters data into a table one record at
a time. It is useful for small operations that deal with just a few records. The
syntax of this statement is as follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">INSERT INTO table_name
(col1, col2...)
VALUES(value1, value2...)
</FONT></PRE>
<P>The basic format of the <TT>INSERT...VALUES</TT> statement adds a record to a
table using the columns you give it and the corresponding values you instruct it
to add. You must follow three rules when inserting data into a table with the <TT>INSERT...VALUES</TT>
statement:
<UL>
<LI>The values used must be the same data type as the fields they are being added
to.
<P>
<LI>The data's size must be within the column's size. For instance, you cannot add
an 80-character string to a 40-character column.
<P>
<LI>The data's location in the <TT>VALUES</TT> list must correspond to the location
in the column list of the column it is being added to. (That is, the first value
must be entered into the first column, the second value into the second column, and
so on.)
</UL>
<H4><FONT COLOR="#000077">Example 8.1</FONT></H4>
<P>Assume you have a <TT>COLLECTION</TT> table that lists all the important stuff
you have collected. You can display the table's contents by writing</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM COLLECTION;
</B></FONT></PRE>
<P>which would yield this:</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">ITEM WORTH 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
</FONT></PRE>
<P>If you wanted to add a new record to this table, you would write</P>
<H5><FONT COLOR="#000000">INPUTOUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL><B> INSERT INTO COLLECTION
</B> 2 <B> (ITEM, WORTH, REMARKS)
</B> 3 <B> VALUES('SUPERMANS CAPE', 250.00, 'TUGGED ON IT');
</B>
1 row created.
</FONT></PRE>
<P>You can execute a simple <TT>SELECT</TT> statement to verify the insertion:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM COLLECTION;
</B>
ITEM WORTH 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
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The <TT>INSERT</TT> statement does not require column names. If the column names
are not entered, SQL lines up the values with their corresponding column numbers.
In other words, SQL inserts the first value into the first column, the second value
into the second column, and so on.
<H4><FONT COLOR="#000077">Example 8.2</FONT></H4>
<P>The following statement inserts the values from Example 8.1 into the table:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>INSERT INTO COLLECTION VALUES
</B> 2 <B> ('STRING',1000.00,'SOME DAY IT WILL BE VALUABLE');
</B></FONT></PRE>
<PRE><FONT COLOR="#0066FF"><B> </B>1 row created.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>By issuing the same <TT>SELECT</TT> statement as you did in Example 8.1, you can
verify that the insertion worked as expected:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM COLLECTION;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">ITEM WORTH 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>
<H3><FONT COLOR="#000077">Inserting NULL Values</FONT></H3>
<P>On Day 9, "Creating and Maintaining Tables," you learn how to create
tables using the <TT>SQL CREATE TABLE</TT> statement. For now, all you need to know
is that when a column is created, it can have several different limitations placed
upon it. One of these limitations is that the column should (or should not) be allowed
to contain <TT>NULL</TT> values. A <TT>NULL</TT> value means that the value is empty.
It is neither a zero, in the case of an integer, nor a space, in the case of a string.
Instead, no data at all exists for that record's column. If a column is defined as
<TT>NOT NULL</TT> (that column is not allowed to contain a <TT>NULL</TT> value),
you must<B> </B>insert a value for that column when using the <TT>INSERT</TT> statement.
The <TT>INSERT</TT> is canceled if this rule is broken, and you should receive a
descriptive error message concerning your error.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>You could insert spaces for a
null column, but these spaces will be treated as a value. <TT>NULL</TT> simply means
nothing is there.
<HR>
</P>
</BLOCKQUOTE>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>insert into collection values</B>
2 <B>('SPORES MILDEW FUNGUS', 50.00, ' ');</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> 1 row inserted.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Using <TT>''</TT> instead of <TT>NULL</TT> inserted a space in the <TT>collection</TT>
table. You then can select the space.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>select * from collection</B>
2 <B>where remarks = ' ';
</B>
ITEM WORTH REMARKS
--------------------------- -------- ---------
SPORES MILDEW FUNGUS 50.00
1 row selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The resulting answer comes back as if a <TT>NULL</TT> is there. With the output
of character fields, it is impossible to tell the difference between a null value
and a mere space.</P>
<P>Assume the column <TT>REMARKS</TT> in the preceding table has been defined as
<TT>NOT NULL</TT>. Typing</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>INSERT INTO COLLECTION</B>
2 <B> VALUES('SPORES MILDEW FUNGUS',50.00,NULL);</B>
</FONT></PRE>
<P>produces the following error:</P>
<PRE><FONT COLOR="#0066FF">INSERT INTO COLLECTION
*
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
</FONT></PRE>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice the syntax. Number data types
do not require quotes; <TT>NULL</TT> does not require quotes; character data types
do require quotes.
<HR>
</BLOCKQUOTE>
<H3><FONT COLOR="#000077">Inserting Unique Values</FONT></H3>
<P>Many database management systems also allow you to create a <TT>UNIQUE</TT> column
attribute. This attribute means that within the current table, the values within
this column must be completely unique and cannot appear more than once. This limitation
can cause problems when inserting or updating values into an existing table, as the
following exchange demonstrates:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING');</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING')
*
ERROR at line 1:
ORA-00001: unique constraint (PERKINS.UNQ_COLLECTION_ITEM) violated
</FONT></PRE>
<H5>ANALYSIS:</H5>
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -