?? 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="../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="../ch18/ch18.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch20/ch20.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 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 asupplement 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 exampleson Day 13 depicted the use of embedded SQL in third-generation programming languagessuch as C. With this method of programming, the embedded SQL code does not changeand is, therefore, limited. On the other hand, you can write dynamic SQL to performthe same functions as a procedural programming language and allow conditions to bechanged 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-SQLis 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 havediscussed so far. In addition, each product contains many extensions to the ANSISQL 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 containsmost of the constructs found in third-generation languages, as well as some SQL Server-specificfeatures that turn out to be very handy tools for the database programmer. (Othermanufacturers' 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 programmerswho occasionally write queries as well as developers who write applications and createobjects 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-SQLenables you to maintain tight control over your database transactions and to writeprocedural database programs that practically render the programmer exempt from exhaustingprogramming 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 atable. The value stored within a column whose data type is <TT>bit</TT> is eithera <TT>1</TT> or <TT>0</TT>. For example, the value <TT>1</TT> may signify the conditiontrue, 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 failis false.</P><P>Throughout the rest of the day, pay attention to the data types used when creatingtables 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 buildthe 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 typicalbaseball information: the <TT>BATTERS</TT> table, the <TT>PITCHERS</TT> table, andthe <TT>TEAMS</TT> table. This database will be used in examples throughout the restof today.<H4><FONT COLOR="#000077">The BATTERS TABLE</FONT></H4><PRE><FONT COLOR="#0066FF">NAME char(30)TEAM intAVERAGE floatHOMERUNS intRBIS 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 thencreate 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 + -