📄 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="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="ch12.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch12.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="ch14.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch14.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 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 used
to write powerful queries to retrieve data from a database. You have also briefly
explored aspects of database design and database security. Today's purpose is to
cover 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 are
simply tables that exist temporarily within a database and are automatically dropped
when the user logs out or their database connection ends. Transact-SQL creates these
temporary tables in the <TT>tempdb</TT> database. This database is created when you
install 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 created
with a unique name consisting of a combination of the table name used in the <TT>CREATE
TABLE</TT> command and a date-time stamp. A temporary table is available only to
its 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 SQL
Server uses to flag a temporary table. Each of the 50 users would essentially receive
a private table for his or her own use. Each user could update, insert, and delete
records from this table without worrying about other users invalidating the table's
data. 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 logs
out of the SQL Server. If you created this statement using some type of dynamic SQL
connection (such as SQL Server's DB-Library), the table will be deleted when that
dynamic SQL connection is closed.</P>
<P>Syntax 2 shows another way to create a temporary table on an SQL Server. This
syntax produces a different result than the syntax used in syntax 1, so pay careful
attention 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 table
being created in the <TT>tempdb</TT> database. This table's name has the same format
as the name for the table created using syntax 1. The difference is that this table
is not dropped when the user's connection to the database ends. Instead, the user
must 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.3
illustrates a common usage of temporary tables: to temporarily store data returned
from 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 + -