?? ch04.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Day 4 -- Functions: Molding the Data You Retrieve</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="ch03.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch03.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="ch05.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch05.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>
</P>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 4 -<BR>
Functions: Molding the Data You Retrieve</FONT></H1>
</CENTER>
<H2><FONT COLOR="#000077">Objectives</FONT></H2>
<P>Today we talk about functions. Functions in SQL enable you to perform feats such
as determining the sum of a column or converting all the characters of a string to
uppercase. By the end of the day, you will understand and be able to use all the
following:
<UL>
<LI>Aggregate functions
<P>
<LI>Date and time functions
<P>
<LI>Arithmetic functions
<P>
<LI>Character functions
<P>
<LI>Conversion functions
<P>
<LI>Miscellaneous functions
</UL>
<P>These functions greatly increase your ability to manipulate the information you
retrieved using the basic functions of SQL that were described earlier this week.
The first five aggregate functions, <TT>COUNT</TT>, <TT>SUM</TT>, <TT>AVG</TT>, <TT>MAX</TT>,
and <TT>MIN</TT>, are defined in the ANSI standard. Most implementations of SQL have
extensions to these aggregate functions, some of which are covered today. Some implementations
may use different names for these functions.
<H2><FONT COLOR="#000077">Aggregate Functions</FONT></H2>
<P>These functions are also referred to as group functions. They return a value based
on the values in a column. (After all, you wouldn't ask for the average of a single
field.) The examples in this section use the table <TT>TEAMSTATS</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM TEAMSTATS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">NAME POS AB HITS WALKS SINGLES DOUBLES TRIPLES HR SO
--------- --- --- ---- ----- ------- ------- ------- -- --
JONES 1B 145 45 34 31 8 1 5 10
DONKNOW 3B 175 65 23 50 10 1 4 15
WORLEY LF 157 49 15 35 8 3 3 16
DAVID OF 187 70 24 48 4 0 17 42
HAMHOCKER 3B 50 12 10 10 2 0 0 13
CASEY DH 1 0 0 0 0 0 0 1
6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">COUNT</FONT></H3>
<P>The function <TT>COUNT</TT> returns the number of rows that satisfy the condition
in the <TT>WHERE</TT> clause. Say you wanted to know how many ball players were hitting
under 350. You would type</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT COUNT(*)
</B> 2 <B>FROM TEAMSTATS
</B> 3 <B> WHERE HITS/AB < .35;
</B>
COUNT(*)
--------
4
</FONT></PRE>
<P>To make the code more readable, try an alias:</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT COUNT(*) NUM_BELOW_350
</B> 2 <B> FROM TEAMSTATS
</B> 3 <B> WHERE HITS/AB < .35;</B>
NUM_BELOW_350
-------------
4
</FONT></PRE>
<P>Would it make any difference if you tried a column name instead of the asterisk?
(Notice the use of parentheses around the column names.) Try this:</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT COUNT(NAME) NUM_BELOW_350
</B> 2<B> FROM TEAMSTATS
</B> 3 <B> WHERE HITS/AB < .35;
</B>
NUM_BELOW_350
-------------
4
</FONT></PRE>
<P>The answer is no. The <TT>NAME</TT> column that you selected was not involved
in the <TT>WHERE</TT> statement. If you use <TT>COUNT</TT> without a <TT>WHERE</TT>
clause, it returns the number of records in the table.</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT COUNT(*)
</B> 2<B> FROM TEAMSTATS;
</B>
COUNT(*)
---------
6
</FONT></PRE>
<H3><FONT COLOR="#000077">SUM</FONT></H3>
<P><TT>SUM</TT> does just that. It returns the sum of all values in a column. To
find out how many singles have been hit, type</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT SUM(SINGLES) TOTAL_SINGLES
</B>2<B> FROM TEAMSTATS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">TOTAL_SINGLES
-------------
174
</FONT></PRE>
<P>To get several sums, use</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT SUM(SINGLES) TOTAL_SINGLES, SUM(DOUBLES) TOTAL_DOUBLES,
SUM(TRIPLES) TOTAL_TRIPLES, SUM(HR) TOTAL_HR
</B> 2 <B> FROM TEAMSTATS;</B>
TOTAL_SINGLES TOTAL_DOUBLES TOTAL_TRIPLES TOTAL_HR
------------- ------------- ------------- --------
174 32 5 29
</FONT></PRE>
<P>To collect similar information on all 300 or better players, type</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT SUM(SINGLES) TOTAL_SINGLES, SUM(DOUBLES) TOTAL_DOUBLES,
SUM(TRIPLES) TOTAL_TRIPLES, SUM(HR) TOTAL_HR
</B>2<B> FROM TEAMSTATS
</B>3<B> WHERE HITS/AB >= .300;</B>
TOTAL_SINGLES TOTAL_DOUBLES TOTAL_TRIPLES TOTAL_HR
------------- ------------- ------------- --------
164 30 5 29
</FONT></PRE>
<P>To compute a team batting average, type</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT SUM(HITS)/SUM(AB) TEAM_AVERAGE
</B>2<B> FROM TEAMSTATS;
</B>
TEAM_AVERAGE
------------
.33706294
</FONT></PRE>
<P><TT>SUM</TT> works only with numbers. If you try it on a nonnumerical field, you
get</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT SUM(NAME)
</B> 2<B> FROM TEAMSTATS;
</B>
ERROR:
ORA-01722: invalid number
no rows selected
</FONT></PRE>
<P>This error message is logical because you cannot sum a group of names.
<H3><FONT COLOR="#000077">AVG</FONT></H3>
<P>The <TT>AVG</TT> function computes the average of a column. To find the average
number of strike outs, use this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT AVG(SO) AVE_STRIKE_OUTS
</B> 2<B> FROM TEAMSTATS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">AVE_STRIKE_OUTS
---------------
16.166667
</FONT></PRE>
<P>The following example illustrates the difference between <TT>SUM</TT> and <TT>AVG</TT>:</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT AVG(HITS/AB) TEAM_AVERAGE
</B>2 <B> FROM TEAMSTATS;
</B>
TEAM_AVERAGE
------------
.26803448
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The team was batting over 300 in the previous example! What happened? <TT>AVG</TT>
computed the average of the combined column hits divided by at bats, whereas the
example with <TT>SUM</TT> divided the total number of hits by the number of at bats.
For example, player A gets 50 hits in 100 at bats for a .500 average. Player B gets
0 hits in 1 at bat for a 0.0 average. The average of 0.0 and 0.5 is .250. If you
compute the combined average of 50 hits in 101 at bats, the answer is a respectable
.495. The following statement returns the correct batting average:</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT AVG(HITS)/AVG(AB) TEAM_AVERAGE
</B> 2 <B> FROM TEAMSTATS;
</B></FONT></PRE>
<PRE><FONT COLOR="#0066FF">TEAM_AVERAGE
------------
.33706294
</FONT></PRE>
<P>Like the <TT>SUM</TT> function, <TT>AVG</TT> works only with numbers.
<H3><FONT COLOR="#000077">MAX</FONT></H3>
<P>If you want to find the largest value in a column, use <TT>MAX</TT>. For example,
what is the highest number of hits?</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT MAX(HITS)
</B> 2 <B> FROM TEAMSTATS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">MAX(HITS)
---------
70
</FONT></PRE>
<P>Can you find out who has the most hits?</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT NAME
</B> 2<B> FROM TEAMSTATS
</B> 3<B> WHERE HITS = MAX(HITS);
</B>
ERROR at line 3:
ORA-00934: group function is not allowed here
</FONT></PRE>
<P>Unfortunately, you can't. The error message is a reminder that this group function
(remember that <I>aggregate functions</I> are also called <I>group functions</I>)
does not work in the <TT>WHERE</TT> clause. Don't despair, Day 7, "Subqueries:
The Embedded <TT>SELECT</TT> Statement," covers the concept of subqueries and
explains a way to find who has the <TT>MAX</TT> hits.</P>
<P>What happens if you try a nonnumerical column?</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT MAX(NAME)
</B> 2 <B> FROM TEAMSTATS;</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">MAX(NAME)
---------------
WORLEY
</FONT></PRE>
<P>Here's something new. <TT>MAX</TT> returns the highest (closest to Z) string.
Finally, a function that works with both characters and numbers.
<H3><FONT COLOR="#000077">MIN</FONT></H3>
<P><TT>MIN</TT> does the expected thing and works like <TT>MAX</TT> except it returns
the lowest member of a column. To find out the fewest at bats, type</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT MIN(AB)
</B>2 <B> FROM TEAMSTATS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">MIN(AB)
---------
1
</FONT></PRE>
<P>The following statement returns the name closest to the beginning of the alphabet:</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT MIN(NAME)
</B> 2<B> FROM TEAMSTATS;
</B>
MIN(NAME)
---------------
CASEY
</FONT></PRE>
<P>You can combine <TT>MIN</TT> with <TT>MAX</TT> to give a range of values. For
example:</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT MIN(AB), MAX(AB)
</B> 2<B> FROM TEAMSTATS;
</B>
MIN(AB) MAX(AB)
-------- --------
1 187
</FONT></PRE>
<P>This sort of information can be useful when using statistical functions.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>As we mentioned in the introduction,
the first five aggregate functions are described in the ANSI standard. The remaining
aggregate functions have become de facto standards, present in all important implementations
of SQL. We use the Oracle7 names for these functions. Other implementations may use
different names.
<HR>
</BLOCKQUOTE>
<H3><FONT COLOR="#000077">VARIANCE</FONT></H3>
<P><TT>VARIANCE</TT> produces the square of the standard deviation, a number vital
to many statistical calculations. It works like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT VARIANCE(HITS)
</B> 2 <B> FROM TEAMSTATS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#000000"><B></B></FONT></PRE>
<PRE><FONT COLOR="#0066FF">VARIANCE(HITS)
--------------
802.96667
</FONT></PRE>
<P>If you try a string</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT VARIANCE(NAME)
</B> 2<B> FROM TEAMSTATS;
</B>
ERROR:
ORA-01722: invalid number
no rows selected
</FONT></PRE>
<P>you find that <TT>VARIANCE</TT> is another function that works exclusively with
numbers.
<H3><FONT COLOR="#000077">STDDEV</FONT></H3>
<P>The final group function, <TT>STDDEV</TT>, finds the standard deviation of a column
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -