?? ch13.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Day 13 -- Advanced SQL Topic</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="../ch12/ch12.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch14/ch14.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 13 -<BR>Advanced SQL Topics</FONT></H1></CENTER><H2><FONT COLOR="#000077">Objectives</FONT></H2><P>Over the course of the past 12 days, you have examined every major topic usedto write powerful queries to retrieve data from a database. You have also brieflyexplored aspects of database design and database security. Today's purpose is tocover advanced SQL topics, which include the following:<UL> <LI>Temporary tables <P> <LI>Cursors <P> <LI>Stored procedures <P> <LI>Triggers <P> <LI>Embedded SQL</UL><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Today's examples use Oracle7's PL/SQL and Microsoft/Sybase SQL Server's Transact-SQL implementations. We made an effort to give examples using both flavors of SQL wherever possible. You do not need to own a copy of either the Oracle7 or the SQL Server database product. Feel free to choose your database product based on your requirements. (If you are reading this to gain enough knowledge to begin a project for your job, chances are you won't have a choice.) <HR></P> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Although you can apply most of the examples within this book to any popular database management system, this statement does not hold for all the material covered today. Many vendors still do not support temporary tables, stored procedures, and triggers. Check your documentation to determine which of these features are included with your favorite database system. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077">Temporary Tables</FONT></H2><P>The first advanced topic we discuss is the use of temporary tables, which aresimply tables that exist temporarily within a database and are automatically droppedwhen the user logs out or their database connection ends. Transact-SQL creates thesetemporary tables in the <TT>tempdb</TT> database. This database is created when youinstall SQL Server. Two types of syntax are used to create a temporary table.</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">SYNTAX 1:create table #table_name (field1 datatype,...fieldn datatype)</FONT></PRE><P>Syntax 1 creates a table in the <TT>tempdb</TT> database. This table is createdwith a unique name consisting of a combination of the table name used in the <TT>CREATETABLE</TT> command and a date-time stamp. A temporary table is available only toits creator. Fifty users could simultaneously issue the following commands:</P><PRE><FONT COLOR="#0066FF">1> <B>create table #albums (</B>2> <B>artist char(30),</B>3> <B>album_name char(50),</B>4> <B>media_type int)</B>5> <B>go</B></FONT></PRE><P>The pound sign (<TT>#</TT>) before the table's name is the identifier that SQLServer uses to flag a temporary table. Each of the 50 users would essentially receivea private table for his or her own use. Each user could update, insert, and deleterecords from this table without worrying about other users invalidating the table'sdata. This table could be dropped as usual by issuing the following command:</P><PRE><FONT COLOR="#0066FF">1> <B>drop table #albums</B>2> <B>go</B></FONT></PRE><P>The table could also be dropped automatically when the user who created it logsout of the SQL Server. If you created this statement using some type of dynamic SQLconnection (such as SQL Server's DB-Library), the table will be deleted when thatdynamic SQL connection is closed.</P><P>Syntax 2 shows another way to create a temporary table on an SQL Server. Thissyntax produces a different result than the syntax used in syntax 1, so pay carefulattention to the syntactical differences.</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">SYNTAX 2:create table tempdb..tablename (field1 datatype,...fieldn datatype)</FONT></PRE><P>Creating a temporary table using the format of syntax 2 still results in a tablebeing created in the <TT>tempdb</TT> database. This table's name has the same formatas the name for the table created using syntax 1. The difference is that this tableis not dropped when the user's connection to the database ends. Instead, the usermust actually issue a <TT>DROP TABLE</TT> command to remove this table from the <TT>tempdb</TT>database.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Another way to get rid of a table that was created using the <TT>create table tempdb..tablename</TT> syntax is to shut down and restart the SQL Server. This method removes all temporary tables from the <TT>tempdb</TT> database. <HR></BLOCKQUOTE><P>Examples 13.1 and 13.2 illustrate the fact that temporary tables are indeed temporary,using the two different forms of syntax. Following these two examples, Example 13.3illustrates a common usage of temporary tables: to temporarily store data returnedfrom a query. This data can then be used with other queries.</P><P>You need to create a database to use these examples. The database <TT>MUSIC</TT>is created with the following tables:<UL> <LI><TT>ARTISTS</TT> <P> <LI><TT>MEDIA</TT> <P> <LI><TT>RECORDINGS</TT></UL><P>Use the following SQL statements to create these tables:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1> <B>create table ARTISTS (</B>2> <B>name char(30),</B>3> <B>homebase char(40),</B>4> <B>style char(20),</B>5> <B>artist_id int)</B>6> <B>go</B>1> <B>create table MEDIA (</B>2> <B>media_type int,</B>3><B> description char(30),</B>4> <B>price float)</B>5> <B>go</B>1> <B>create table RECORDINGS (</B>2> <B>artist_id int,</B>3> <B>media_type int,</B>4><B> title char(50),</B>5> <B>year int)</B>6><B> go</B></FONT></PRE><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Tables 13.1, 13.2, and 13.3 show some sample data for these tables. <HR></BLOCKQUOTE><H4><FONT COLOR="#000077">Table 13.1. The ARTISTS table.</FONT></H4><P><TABLE BORDER="1"> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT"><B>Name</B></TD> <TD ALIGN="LEFT"><B>Homebase</B></TD> <TD ALIGN="LEFT"><B>Style</B></TD> <TD ALIGN="LEFT"><B>Artist_ID</B></TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Soul Asylum</TD> <TD ALIGN="LEFT">Minneapolis</TD> <TD ALIGN="LEFT">Rock</TD> <TD ALIGN="LEFT">1</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Maurice Ravel</TD> <TD ALIGN="LEFT">France</TD> <TD ALIGN="LEFT">Classical</TD> <TD ALIGN="LEFT">2</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Dave Matthews Band</TD> <TD ALIGN="LEFT">Charlottesville</TD> <TD ALIGN="LEFT">Rock</TD> <TD ALIGN="LEFT">3</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Vince Gill</TD> <TD ALIGN="LEFT">Nashville</TD> <TD ALIGN="LEFT">Country</TD> <TD ALIGN="LEFT">4</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Oingo Boingo</TD> <TD ALIGN="LEFT">Los Angeles</TD> <TD ALIGN="LEFT">Pop</TD> <TD ALIGN="LEFT">5</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Crowded House</TD> <TD ALIGN="LEFT">New Zealand</TD> <TD ALIGN="LEFT">Pop</TD> <TD ALIGN="LEFT">6</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Mary Chapin-Carpenter</TD> <TD ALIGN="LEFT">Nashville</TD> <TD ALIGN="LEFT">Country</TD> <TD ALIGN="LEFT">7</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Edward MacDowell</TD> <TD ALIGN="LEFT">U.S.A.</TD> <TD ALIGN="LEFT">Classical</TD> <TD ALIGN="LEFT">8</TD> </TR></TABLE><H4><FONT COLOR="#000077">Table 13.2. The MEDIA table.</FONT></H4><P><TABLE BORDER="1"> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT"><B>Media_Type</B></TD> <TD ALIGN="LEFT"><B>Description</B></TD> <TD ALIGN="LEFT"><B>Price</B></TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">1</TD> <TD ALIGN="LEFT">Record</TD> <TD ALIGN="LEFT">4.99</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">2</TD> <TD ALIGN="LEFT">Tape</TD> <TD ALIGN="LEFT">9.99</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">3</TD> <TD ALIGN="LEFT">CD</TD> <TD ALIGN="LEFT">13.99</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">4</TD> <TD ALIGN="LEFT">CD-ROM</TD> <TD ALIGN="LEFT">29.99</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">5</TD> <TD ALIGN="LEFT">DAT</TD> <TD ALIGN="LEFT">19.99</TD> </TR></TABLE><H4><FONT COLOR="#000077">Table 13.3. The RECORDINGS table.</FONT></H4><P><TABLE BORDER="1"> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT"><B>Artist_Id</B></TD> <TD ALIGN="LEFT"><B>Media_Type</B></TD> <TD ALIGN="LEFT"><B>Title</B></TD> <TD ALIGN="LEFT"><B>Year</B></TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">1</TD> <TD ALIGN="LEFT">2</TD> <TD ALIGN="LEFT">Hang Time</TD> <TD ALIGN="LEFT">1988</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">1</TD> <TD ALIGN="LEFT">3</TD> <TD ALIGN="LEFT">Made to Be Broken</TD> <TD ALIGN="LEFT">1986</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">2</TD> <TD ALIGN="LEFT">3</TD> <TD ALIGN="LEFT">Bolero</TD> <TD ALIGN="LEFT">1990</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">3</TD> <TD ALIGN="LEFT">5</TD> <TD ALIGN="LEFT">Under the Table and Dreaming</TD> <TD ALIGN="LEFT">1994</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">4</TD> <TD ALIGN="LEFT">3</TD> <TD ALIGN="LEFT">When Love Finds You</TD> <TD ALIGN="LEFT">1994</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">5</TD> <TD ALIGN="LEFT">2</TD> <TD ALIGN="LEFT">Boingo</TD> <TD ALIGN="LEFT">1987</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">5</TD> <TD ALIGN="LEFT">1</TD> <TD ALIGN="LEFT">Dead Man's Party</TD> <TD ALIGN="LEFT">1984</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">6</TD> <TD ALIGN="LEFT">2</TD> <TD ALIGN="LEFT">Woodface</TD> <TD ALIGN="LEFT">1990</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">6</TD> <TD ALIGN="LEFT">3</TD> <TD ALIGN="LEFT">Together Alone</TD> <TD ALIGN="LEFT">1993</TD> </TR>?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -