?? 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="../buttonart/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/ch07.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch09/ch09.htm"><IMGSRC="../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> <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 everyselection criterion imaginable. After this data is retrieved, you can use it in anapplication program or edit it. Week 1 focused on retrieving data. However, you mayhave wondered how to enter data into the database in the first place. You may alsobe wondering what to do with data that has been edited. Today we discuss three SQLstatements that enable you to manipulate the data within a database's table. Thethree 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 enteryour 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 manipulationstatements is that it is primarily used within application programs that enable theuser to edit the data using the application's own tools. The SQL programmer needsto be able to return the data to the database using SQL. In addition, most large-scaledatabase 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 andexporting 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 AccessImport/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. Itcan 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 ata time. It is useful for small operations that deal with just a few records. Thesyntax 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 atable using the columns you give it and the corresponding values you instruct itto 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 stuffyou 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 SPOKESMALIBU BARBIE 150 TAN NEEDS WORKSTAR WARS GLASS 5.5 HANDLE CHIPPEDLOCK 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 SPOKESMALIBU BARBIE 150 TAN NEEDS WORKSTAR WARS GLASS 5.5 HANDLE CHIPPEDLOCK OF SPOUSES HAIR 1 HASN'T NOTICED BALD SPOT YETSUPERMANS CAPE 250 TUGGED ON IT</FONT></PRE><H5>ANALYSIS:</H5><P>The <TT>INSERT</TT> statement does not require column names. If the column namesare 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 valueinto 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 canverify 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 SPOKESMALIBU BARBIE 150 TAN NEEDS WORKSTAR WARS GLASS 5.5 HANDLE CHIPPEDLOCK OF SPOUSES HAIR 1 HASN'T NOTICED BALD SPOT YETSUPERMANS CAPE 250 TUGGED ON ITSTRING 1000 SOME DAY IT WILL BE VALUABLE6 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 createtables using the <TT>SQL CREATE TABLE</TT> statement. For now, all you need to knowis that when a column is created, it can have several different limitations placedupon it. One of these limitations is that the column should (or should not) be allowedto 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 adescriptive 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.001 row selected.</FONT></PRE><H5>ANALYSIS:</H5><P>The resulting answer comes back as if a <TT>NULL</TT> is there. With the outputof character fields, it is impossible to tell the difference between a null valueand 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> columnattribute. This attribute means that within the current table, the values withinthis column must be completely unique and cannot appear more than once. This limitationcan cause problems when inserting or updating values into an existing table, as thefollowing 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><P>In this example you tried to insert another <TT>ITEM</TT> called <TT>STRING</TT>into the <TT>COLLECTION</TT> table. Because this table was created with <TT>ITEM</TT>as a unique value, it returned the appropriate error. ANSI SQL does not offer a solutionto this problem, but several commercial implementations include extensions that wouldallow you to use something like the following:</P><PRE><FONT COLOR="#0066FF">IF NOT EXISTS (SELECT * FROM COLLECTION WHERE NAME = 'STRING'INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING')</FONT></PRE><P>This particular example is supported in the Sybase system.</P><P>A properly normalized table should have a unique, or key, field. This field isuseful for joining data between tables, and it often improves the speed of your querieswhen using indexes. (See Day 10, "Creating Views and Indexes.")<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Here's an <TT>INSERT</TT> statement that inserts a new employee into a table:</P> <PRE><FONT COLOR="#0066FF">SQL> insert into employee_tbl values ('300500177', 'SMITHH', 'JOHN');1 row inserted.</FONT></PRE> <P>After hitting Enter, you noticed that you misspelled <TT>SMITH</TT>. Not to fret! All you have to do is issue the <TT>ROLLBACK</TT> command, and the row will not be inserted. See Day 11, "Controlling Transactions," for more on the <TT>ROLLBACK</TT> command. <HR></BLOCKQUOTE><H3><FONT COLOR="#000077">The INSERT...SELECT Statement</FONT></H3><P>The <TT>INSERT...VALUES</TT> statement is useful when adding single records toa database table, but it obviously has limitations. Would you like to use it to add25,000 records to a table? In situations like this, the <TT>INSERT...SELECT</TT>statement is much more beneficial. It enables the programmer to copy informationfrom a table or group of tables into another table. You will want to use this statementin several situations. Lookup tables are often created for performance gains. Lookuptables can contain data that is spread out across multiple tables in multiple databases.Because multiple-table joins are slower to process than simple queries, it is muchquicker to execute a <TT>SELECT</TT> query against a lookup table than to executea long, complicated joined query. Lookup tables are often stored on the client machinesin client/server environments to reduce network traffic.</P><P>Many database systems also support temporary tables. (See Day 14, "DynamicUses of SQL.") Temporary tables exist for the life of your database connectionand are deleted when your connection is terminated. The <TT>INSERT...SELECT</TT>statement can take the output of a <TT>SELECT</TT> statement and insert these valuesinto a temporary table.</P><P>Here is an example:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL><B> insert into tmp_tbl</B> 2 <B>select * from table;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">19,999 rows inserted.</FONT></PRE><H5>ANALYSIS:</H5><P>You are selecting all the rows that are in <TT>table</TT> and inserting them into<TT>tmp_tbl</TT>.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Not all database management systems support temporary tables. Check the documentation for the specific system you are using to determine if this feature is supported. Also, see Day 14 for a more detailed treatment of this topic. <HR></BLOCKQUOTE><P>The syntax of the <TT>INSERT...SELECT</TT> statement is as follows:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">INSERT INTO table_name(col1, col2...)SELECT col1, col2...FROM tablenameWHERE search_condition</FONT></PRE><P>Essentially, the output of a standard <TT>SELECT</TT> query is then input intoa database table. The same rules that applied to the <TT>INSERT...VALUES</TT> statementapply to the <TT>INSERT...SELECT</TT> statement. To copy the contents of the <TT>COLLECTION</TT>table into a new table called <TT>INVENTORY</TT>, execute the set of statements inExample 8.3.<H4><FONT COLOR="#000077">Example 8.3</FONT></H4><P>This example creates the new table <TT>INVENTORY</TT>.</P><H5>INPUT:</H5>?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -