📄 ch05.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Day 5 -- Clauses in SQL</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="../ch04/ch04.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch06/ch06.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 5 -<BR>Clauses in SQL</FONT></H1></CENTER><H2><FONT COLOR="#000077">Objectives</FONT></H2><P>Today's topic is clauses--not the kind that distribute presents during the holidays,but the ones you use with a <TT>SELECT</TT> statement. By the end of the day youwill understand and be able to use the following clauses:<UL> <LI><TT>WHERE</TT> <P> <LI><TT>STARTING WITH</TT> <P> <LI><TT>ORDER BY</TT> <P> <LI><TT>GROUP BY</TT> <P> <LI><TT>HAVING</TT></UL><P>To get a feel for where these functions fit in, examine the general syntax fora <TT>SELECT</TT> statement:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">SELECT [DISTINCT | ALL] { * | { [schema.]{table | view | snapshot}.* | expr } [ [AS] c_alias ] [, { [schema.]{table | view | snapshot}.* | expr } [ [AS] c_alias ] ] ... }FROM [schema.]{table | view | snapshot}[@dblink] [t_alias][, [schema.]{table | view | snapshot}[@dblink] [t_alias] ] ... [WHERE condition ] [GROUP BY expr [, expr] ... [HAVING condition] ] [{UNION | UNION ALL | INTERSECT | MINUS} SELECT command ] [ORDER BY {expr|position} [ASC | DESC] [, {expr|position} [ASC | DESC]] ...]</FONT></PRE><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>In my experience with SQL, the ANSI standard is really more of an ANSI "suggestion." The preceding syntax will generally work with any SQL engine, but you may find some slight variations. <HR></P> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>You haven't yet had to deal with a complicated syntax diagram. Because many people find syntax diagrams more puzzling than illuminating when learning something new, this book has used simple examples to illustrate particular points. However, we are now at the point where a syntax diagram can help tie the familiar concepts to today's new material. <HR></BLOCKQUOTE><P>Don't worry about the exact syntax--it varies slightly from implementation toimplementation anyway. Instead, focus on the relationships. At the top of this statementis <TT>SELECT</TT>, which you have used many times in the last few days. <TT>SELECT</TT>is followed by <TT>FROM</TT>, which should appear with every <TT>SELECT</TT> statementyou typed. (You learn a new use for <TT>FROM</TT> tomorrow.) <TT>WHERE</TT>, <TT>GROUPBY</TT>, <TT>HAVING</TT>, and <TT>ORDER BY</TT> all follow. (The other clauses inthe diagram--<TT>UNION</TT>, <TT>UNION ALL</TT>, <TT>INTERSECT</TT>, and <TT>MINUS</TT>--werecovered in Day 3, "Expressions, Conditions, and Operators.") Each clauseplays an important part in selecting and manipulating data.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>We have used two implementations of SQL to prepare today's examples. One implementation has an SQL> prompt and line numbers (Personal Oracle7), and the other (Borland's ISQL) does not. You will also notice that the output displays vary slightly, depending on the implementation. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077">The WHERE Clause</FONT></H2><P>Using just <TT>SELECT</TT> and <TT>FROM</TT>, you are limited to returning everyrow in a table. For example, using these two key words on the <TT>CHECKS</TT> table,you get all seven rows:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL><B> SELECT * </B> 2<B> FROM CHECKS;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#000000"></FONT></PRE><PRE><FONT COLOR="#0066FF"> CHECK# PAYEE AMOUNT REMARKS-------- -------------------- -------- ------------------ 1 Ma Bell 150 Have sons next time 2 Reading R.R. 245.34 Train to Chicago 3 Ma Bell 200.32 Cellular Phone 4 Local Utilities 98 Gas 5 Joes Stale $ Dent 150 Groceries 16 Cash 25 Wild Night Out 17 Joans Gas 25.1 Gas</FONT></PRE><PRE><FONT COLOR="#0066FF">7 rows selected.</FONT></PRE><P>With <TT>WHERE</TT> in your vocabulary, you can be more selective. To find allthe checks you wrote with a value of more than <TT>100</TT> dollars, write this:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * </B>2<B> FROM CHECKS </B>3<B> WHERE AMOUNT > 100;</B></FONT></PRE><P>The <TT>WHERE</TT> clause returns the four instances in the table that meet therequired condition:</P><H5>OUTPUT:</H5><PRE><FONT COLOR="#0066FF"> CHECK# PAYEE AMOUNT REMARKS-------- -------------------- -------- ------------------ 1 Ma Bell 150 Have sons next time 2 Reading R.R. 245.34 Train to Chicago 3 Ma Bell 200.32 Cellular Phone 5 Joes Stale $ Dent 150 Groceries</FONT></PRE><P><TT>WHERE</TT> can also solve other popular puzzles. Given the following tableof names and locations, you can ask that popular question, Where's Waldo?</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * </B> 2 <B> FROM PUZZLE;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">NAME LOCATION-------------- --------------TYLER BACKYARDMAJOR KITCHENSPEEDY LIVING ROOMWALDO GARAGELADDIE UTILITY CLOSETARNOLD TV ROOM</FONT></PRE><PRE><FONT COLOR="#0066FF">6 rows selected.</FONT></PRE><PRE><FONT COLOR="#0066FF"></FONT></PRE><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT LOCATION AS "WHERE'S WALDO?" </B>2<B> FROM PUZZLE </B> 3<B> WHERE NAME = 'WALDO';</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">WHERE'S WALDO?--------------GARAGE</FONT></PRE><P>Sorry, we couldn't resist. We promise no more corny queries. (We're saving thosefor that SQL bathroom humor book everyone's been wanting.) Nevertheless, this queryshows that the column used in the condition of the <TT>WHERE</TT> statement doesnot have to be mentioned in the <TT>SELECT</TT> clause. In this example you selectedthe location column but used <TT>WHERE</TT> on the name, which is perfectly legal.Also notice the <TT>AS</TT> on the <TT>SELECT</TT> line. <TT>AS</TT> is an optionalassignment operator, assigning the alias <TT>WHERE'S WALDO?</TT> to <TT>LOCATION</TT>.You might never see the <TT>AS</TT> again, because it involves extra typing. In mostimplementations of SQL you can type</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT LOCATION "WHERE'S WALDO?" </B> 2<B> FROM PUZZLE </B> 3<B> WHERE NAME ='WALDO';</B></FONT></PRE><P>and get the same result as the previous query without using <TT>AS</TT>:</P><H5>OUTPUT:</H5><PRE><FONT COLOR="#0066FF">WHERE'S WALDO?--------------GARAGE</FONT></PRE><P>After <TT>SELECT</TT> and <TT>FROM</TT>, <TT>WHERE</TT> is the third most frequentlyused SQL term.<H2><FONT COLOR="#000077">The STARTING WITH Clause</FONT></H2><P><TT>STARTING WITH</TT> is an addition to the <TT>WHERE</TT> clause that worksexactly like <TT>LIKE(<exp>%)</TT>. Compare the results of the following query:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, AMOUNT, REMARKSFROM CHECKSWHERE PAYEE LIKE('Ca%');</B></FONT></PRE><H5><FONT COLOR="#0066FF">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">PAYEE AMOUNT REMARKS==================== =============== ==============Cash 25 Wild Night OutCash 60 Trip to BostonCash 34 Trip to Dayton</FONT></PRE><P>with the results from this query:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, AMOUNT, REMARKSFROM CHECKSWHERE PAYEE STARTING WITH('Ca');</B></FONT></PRE><H5><FONT COLOR="#0066FF">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">PAYEE AMOUNT REMARKS==================== =============== ==============Cash 25 Wild Night OutCash 60 Trip to BostonCash 34 Trip to Dayton</FONT></PRE><P>The results are identical. You can even use them together, as shown here:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, AMOUNT, REMARKSFROM CHECKSWHERE PAYEE STARTING WITH('Ca')ORREMARKS LIKE 'G%';</B></FONT></PRE><H5><FONT COLOR="#0066FF">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">PAYEE AMOUNT REMARKS==================== =============== ===============Local Utilities 98 GasJoes Stale $ Dent 150 GroceriesCash 25 Wild Night OutJoans Gas 25.1 GasCash 60 Trip to BostonCash 34 Trip to DaytonJoans Gas 15.75 Gas</FONT></PRE><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>WARNING:</B></FONT><TT><B> </B>STARTING WITH</TT> is a common feature of many implementations of SQL. Check your implementation before you grow fond of it. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077">Order from Chaos: The ORDER BY Clause</FONT></H2><P>From time to time you will want to present the results of your query in some kindof order. As you know, however, <TT>SELECT FROM</TT> gives you a listing, and unlessyou have defined a primary key (see Day 10, "Creating Views and Indexes"),your query comes out in the order the rows were entered. Consider a beefed-up <TT>CHECKS</TT>table:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM CHECKS;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#000000"></FONT></PRE><PRE><FONT COLOR="#0066FF"> CHECK# PAYEE AMOUNT REMARKS-------- -------------------- -------- ------------------ 1 Ma Bell 150 Have sons next time 2 Reading R.R. 245.34 Train to Chicago 3 Ma Bell 200.32 Cellular Phone 4 Local Utilities 98 Gas 5 Joes Stale $ Dent 150 Groceries 16 Cash 25 Wild Night Out 17 Joans Gas 25.1 Gas 9 Abes Cleaners 24.35 X-Tra Starch 20 Abes Cleaners 10.5 All Dry Clean 8 Cash 60 Trip to Boston 21 Cash 34 Trip to Dayton11 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>You're going to have to trust me on this one, but the order of the output is exactlythe same order as the order in which the data was entered. After you read Day 8,"Manipulating Data," and know how to use <TT>INSERT</TT> to create tables,you can test how data is ordered by default on your own.</P><P>The <TT>ORDER BY </TT>clause gives you a way of ordering your results. For example,to order the preceding listing by check number, you would use the following <TT>ORDERBY</TT> clause:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * </B>2<B> FROM CHECKS </B>3<B> ORDER BY CHECK#;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF"> CHECK# PAYEE AMOUNT REMARKS-------- -------------------- -------- ------------------ 1 Ma Bell 150 Have sons next time 2 Reading R.R. 245.34 Train to Chicago 3 Ma Bell 200.32 Cellular Phone 4 Local Utilities 98 Gas 5 Joes Stale $ Dent 150 Groceries 8 Cash 60 Trip to Boston 9 Abes Cleaners 24.35 X-Tra Starch 16 Cash 25 Wild Night Out 17 Joans Gas 25.1 Gas 20 Abes Cleaners 10.5 All Dry Clean 21 Cash 34 Trip to Dayton</FONT></PRE><PRE><FONT COLOR="#0066FF">11 rows selected.</FONT></PRE><P>Now the data is ordered the way you want it, not the way in which it was entered.As the following example shows, <TT>ORDER</TT> requires <TT>BY</TT>; <TT>BY</TT>is not optional.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM CHECKS ORDER CHECK#;</B>SELECT * FROM CHECKS ORDER CHECK# *ERROR at line 1:ORA-00924: missing BY keyword</FONT></PRE><P>What if you want to list the data in reverse order, with the highest number or⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -