?? ch19.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Ch 19 -- Transact-SQL: An Introduction</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="ch18.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch18.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="ch20.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch20.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 19 -<BR>
Transact-SQL: An Introduction</FONT></H1>
</CENTER>
<H2><FONT COLOR="#000077">Objectives</FONT></H2>
<P>Today's material supplements the previous presentations, as Transact-SQL is a
supplement to the accepted SQL standard. Today's goals are to
<UL>
<LI>Identify one of the popular extensions to SQL
<P>
<LI>Outline the major features of Transact-SQL
<P>
<LI>Provide practical examples to give you an understanding of how Transact-SQL is
used
</UL>
<H2><FONT COLOR="#000077">An Overview of Transact-SQL</FONT></H2>
<P>Day 13, "Advanced SQL Topics," briefly covered static SQL. The examples
on Day 13 depicted the use of embedded SQL in third-generation programming languages
such as C. With this method of programming, the embedded SQL code does not change
and is, therefore, limited. On the other hand, you can write dynamic SQL to perform
the same functions as a procedural programming language and allow conditions to be
changed within the SQL code.</P>
<P>As we have mentioned during the discussion of virtually every topic in this book,
almost every database vendor has added many extensions to the language. Transact-SQL
is the Sybase and Microsoft SQL Server database product. Oracle's product is PL/SQL.
Each of these languages contains the complete functionality of everything we have
discussed so far. In addition, each product contains many extensions to the ANSI
SQL standard.
<H2><FONT COLOR="#000077">Extensions to ANSI SQL</FONT></H2>
<P>To illustrate the use of these SQL extensions to create actual programming logic,
we are using Sybase and Microsoft SQL Server's Transact-SQL language. It contains
most of the constructs found in third-generation languages, as well as some SQL Server-specific
features that turn out to be very handy tools for the database programmer. (Other
manufacturers' extensions contain many of these features and more.)
<H3><FONT COLOR="#000077">Who Uses Transact-SQL?</FONT></H3>
<P>Everyone reading this book can use Transact-SQL--casual relational database programmers
who occasionally write queries as well as developers who write applications and create
objects such as triggers and stored procedures.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Users of Sybase and Microsoft SQL
Server who want to explore the true capabilities of relational database programming
must use the Transact-SQL features.
<HR>
</BLOCKQUOTE>
<H3><FONT COLOR="#000077">The Basic Components of Transact-SQL</FONT></H3>
<P>SQL extensions overcome SQL's limits as a procedural language. For example, Transact-SQL
enables you to maintain tight control over your database transactions and to write
procedural database programs that practically render the programmer exempt from exhausting
programming tasks.</P>
<P>Day 19 covers the following key features of Transact-SQL:
<UL>
<LI>A wide range of data types to optimize data storage
<P>
<LI>Program flow commands such as loops and <TT>IF-ELSE</TT> statements
<P>
<LI>Use of variables in SQL statements
<P>
<LI>Summarized reports using computations
<P>
<LI>Diagnostic features to analyze SQL statements
<P>
<LI>Many other options to enhance the standard language of SQL
</UL>
<H2><FONT COLOR="#000077">Data Types</FONT></H2>
<P>On Day 9, "Creating and Maintaining Tables," we discussed data types.
When creating tables in SQL, you must specify a specific data type for each column.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Data types vary between implementations
of SQL because of the way each database server stores data. For instance, Oracle
uses selected data types, whereas Sybase and Microsoft's SQL Server have their own
data types.
<HR>
</BLOCKQUOTE>
<P>Sybase and Microsoft's SQL Server support the following data types.
<H3><FONT COLOR="#000077">Character Strings</FONT></H3>
<UL>
<LI><TT>char</TT> stores fixed-length character strings, such as STATE abbreviations,
when you know that the column will always be two characters.
<P>
<LI><TT>varchar</TT> stores variable-length character strings, such as an individual's
name, where the exact length of a name is not specified, for example, AL RAY to WILLIAM
STEPHENSON.
<P>
<LI><TT>text</TT> stores strings with nearly unlimited size, such as a remarks column
or description of a type of service.
</UL>
<H3><FONT COLOR="#000077">Numeric Data Types</FONT></H3>
<UL>
<LI><TT>int</TT> stores integers from <TT>-2</TT>,<TT>147</TT>,<TT>483</TT>,<TT>647</TT>
to <TT>+2</TT>,<TT>147</TT>,<TT>483</TT>,<TT>647</TT>.
<P>
<LI><TT>smallint</TT> stores integers from <TT>-32,768</TT> to <TT>32,767</TT>.
<P>
<LI><TT>tinyint</TT> stores integers from <TT>0</TT> to <TT>255</TT>.
<P>
<LI><TT>float</TT> expresses numbers as real floating-point numbers with data precisions.
Decimals are allowed with these data types. The values range from <TT>+2.23E-308</TT>
to <TT>+1.79E308</TT>.
<P>
<LI><TT>real</TT> expresses real numbers with data precisions from <TT>+1.18E-38</TT>
to <TT>+3.40E38</TT>.
</UL>
<H3><FONT COLOR="#000077">DATE Data Types</FONT></H3>
<UL>
<LI><TT>datetime</TT> values range from <TT>Jan 1, 1753</TT> to <TT>Dec 31, 9999</TT>.
<P>
<LI><TT>smalldatetime</TT><B> </B>values range from <TT>Jan 1, 1900</TT> to <TT>Jun
6, 2079</TT>.
</UL>
<H3><FONT COLOR="#000077">Money Data Types</FONT></H3>
<UL>
<LI><TT>money</TT><B> </B>stores<B> </B>values up to <TT>+922,337,203,685,477.5808</TT>.
<P>
<LI><TT>smallmoney</TT><B> </B>stores<B> </B>values up to <TT>+214,748.3647</TT>.
</UL>
<P>Money values are inserted into a table using the dollar sign; for example:</P>
<PRE><FONT COLOR="#0066FF">insert payment_tbl (customer_id, paydate, pay_amt)
values (012845, "May 1, 1997", $2099.99)
</FONT></PRE>
<H3><FONT COLOR="#000077">Binary Strings</FONT></H3>
<UL>
<LI><TT>binary</TT> stores fixed-length binary strings.
<P>
<LI><TT>varbinary</TT> stores variable-length binary strings.
<P>
<LI><TT>image</TT> stores very large binary strings, for example, photographs and
other images.
</UL>
<H3><FONT COLOR="#000077">bit: A Logical Data Type</FONT></H3>
<P>The data type <TT>bit</TT> is often used to flag certain rows of data within a
table. The value stored within a column whose data type is <TT>bit</TT> is either
a <TT>1</TT> or <TT>0</TT>. For example, the value <TT>1</TT> may signify the condition
true, whereas <TT>0</TT> denotes a false condition. The following example uses the
<TT>bit</TT> data type to create a table containing individual test scores:</P>
<PRE><FONT COLOR="#0066FF">create table test_flag
( ind_id int not null,
test_results int not null,
result_flag bit not null)
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The column <TT>result_flag</TT> is defined as a <TT>bit</TT> column, where the
<TT>bit</TT> character represents either a pass or fail, where pass is true and fail
is false.</P>
<P>Throughout the rest of the day, pay attention to the data types used when creating
tables and writing Transact-SQL code.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The code in today's examples uses
both uppercase and lowercase. Although SQL keywords are not case sensitive in most
implementations of SQL, always check your implementation.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077">Accessing the Database with Transact-SQL</FONT></H2>
<P>All right, enough talk. To actually run the examples today, you will need to build
the following database tables in a database named <TT>BASEBALL</TT>.
<H3><FONT COLOR="#000077">The BASEBALL Database</FONT></H3>
<P>The <TT>BASEBALL</TT> database consists of three tables used to track typical
baseball information: the <TT>BATTERS</TT> table, the <TT>PITCHERS</TT> table, and
the <TT>TEAMS</TT> table. This database will be used in examples throughout the rest
of today.
<H4><FONT COLOR="#000077">The BATTERS TABLE</FONT></H4>
<PRE><FONT COLOR="#0066FF">NAME char(30)
TEAM int
AVERAGE float
HOMERUNS int
RBIS int
</FONT></PRE>
<P>The table above can be created using the following Transact-SQL statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1> <B>create database BASEBALL on default</B>
2> <B>go</B>
1> <B>use BASEBALL</B>
2> <B>go</B>
1> <B>create table BATTERS (</B>
2> <B>NAME char(30),</B>
3> <B>TEAM int,</B>
4> <B>AVERAGE float,</B>
5> <B>HOMERUNS int,</B>
6> <B>RBIS int)</B>
7> <B>go</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Line 1 creates the database. You specify the database <TT>BASEBALL</TT> and then
create the table <TT>BATTERS</TT> underneath <TT>BASEBALL</TT>.</P>
<P>Enter the data in Table 19.1 into the <TT>BATTERS</TT> table.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The command <TT>go</TT> that separates
each Transact-SQL statement in the preceding example is not part of Transact-SQL.
<TT>go</TT>'s purpose is to pass each statement from a front-end application to SQL
Server.
<HR>
</BLOCKQUOTE>
<H4><FONT COLOR="#000077">Table 19.1. Data for the BATTERS table.</FONT></H4>
<P>
<TABLE BORDER="1">
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><B>Name</B></TD>
<TD ALIGN="LEFT"><B>Team</B></TD>
<TD ALIGN="LEFT"><B>Average</B></TD>
<TD ALIGN="LEFT"><B>Homeruns</B></TD>
<TD ALIGN="LEFT"><B>RBIs</B></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Billy Brewster</TD>
<TD ALIGN="LEFT">1</TD>
<TD ALIGN="LEFT">.275</TD>
<TD ALIGN="LEFT">14</TD>
<TD ALIGN="LEFT">46</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">John Jackson</TD>
<TD ALIGN="LEFT">1</TD>
<TD ALIGN="LEFT">.293</TD>
<TD ALIGN="LEFT">2</TD>
<TD ALIGN="LEFT">29</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Phil Hartman</TD>
<TD ALIGN="LEFT">1</TD>
<TD ALIGN="LEFT">.221</TD>
<TD ALIGN="LEFT">13</TD>
<TD ALIGN="LEFT">21</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Jim Gehardy</TD>
<TD ALIGN="LEFT">2</TD>
<TD ALIGN="LEFT">.316</TD>
<TD ALIGN="LEFT">29</TD>
<TD ALIGN="LEFT">84</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Tom Trawick</TD>
<TD ALIGN="LEFT">2</TD>
<TD ALIGN="LEFT">.258</TD>
<TD ALIGN="LEFT">3</TD>
<TD ALIGN="LEFT">51</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Eric Redstone</TD>
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -