📄 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="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="ch04.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch04.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="ch06.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch06.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 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 you
will 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 for
a <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 to
implementation anyway. Instead, focus on the relationships. At the top of this statement
is <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> statement
you typed. (You learn a new use for <TT>FROM</TT> tomorrow.) <TT>WHERE</TT>, <TT>GROUP
BY</TT>, <TT>HAVING</TT>, and <TT>ORDER BY</TT> all follow. (The other clauses in
the diagram--<TT>UNION</TT>, <TT>UNION ALL</TT>, <TT>INTERSECT</TT>, and <TT>MINUS</TT>--were
covered in Day 3, "Expressions, Conditions, and Operators.") Each clause
plays 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 every
row 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 all
the 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 the
required 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 table
of 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 BACKYARD
MAJOR KITCHEN
SPEEDY LIVING ROOM
WALDO GARAGE
LADDIE UTILITY CLOSET
ARNOLD 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 those
for that SQL bathroom humor book everyone's been wanting.) Nevertheless, this query
shows that the column used in the condition of the <TT>WHERE</TT> statement does
not have to be mentioned in the <TT>SELECT</TT> clause. In this example you selected
the 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 optional
assignment 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 most
implementations 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 frequently
used 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 works
exactly like <TT>LIKE(<exp>%)</TT>. Compare the results of the following query:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, AMOUNT, REMARKS
FROM CHECKS
WHERE PAYEE LIKE('Ca%');</B></FONT></PRE>
<H5><FONT COLOR="#0066FF">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PAYEE AMOUNT REMARKS
==================== =============== ==============
Cash 25 Wild Night Out
Cash 60 Trip to Boston
Cash 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, REMARKS
FROM CHECKS
WHERE 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 Out
Cash 60 Trip to Boston
Cash 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, REMARKS
FROM CHECKS
WHERE PAYEE STARTING WITH('Ca')
OR
REMARKS LIKE 'G%';</B></FONT></PRE>
<H5><FONT COLOR="#0066FF">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PAYEE AMOUNT REMARKS
==================== =============== ===============
Local Utilities 98 Gas
Joes Stale $ Dent 150 Groceries
Cash 25 Wild Night Out
Joans Gas 25.1 Gas
Cash 60 Trip to Boston
Cash 34 Trip to Dayton
Joans 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 kind
of order. As you know, however, <TT>SELECT FROM</TT> gives you a listing, and unless
you 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 Dayton
11 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 exactly
the 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>ORDER
BY</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
letter first? You're in luck! The following query generates a list of <TT>PAYEE</TT>s
that stars at the end of the alphabet:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *
</B> 2<B> FROM CHECKS
</B> 3<B> ORDER BY PAYEE DESC;</B>
CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- ------------------
2 Reading R.R. 245.34 Train to Chicago
1 Ma Bell 150 Have sons next time
3 Ma Bell 200.32 Cellular Phone
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
17 Joans Gas 25.1 Gas
16 Cash 25 Wild Night Out
8 Cash 60 Trip to Boston
21 Cash 34 Trip to Dayton
9 Abes Cleaners 24.35 X-Tra Starch
20 Abes Cleaners 10.5 All Dry Clean
11 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The <TT>DESC</TT> at the end of the <TT>ORDER BY</TT> clause orders the list in
descending order instead of the default (ascending) order. The rarely used, optional
keyword <TT>ASC</TT> appears in the following statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT PAYEE, AMOUNT
</B>2<B> FROM CHECKS
</B> 3 <B> ORDER BY CHECK# ASC;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PAYEE AMOUNT
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -