?? 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="../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="../ch03/ch03.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch05/ch05.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></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 suchas determining the sum of a column or converting all the characters of a string touppercase. By the end of the day, you will understand and be able to use all thefollowing:<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 youretrieved 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 haveextensions to these aggregate functions, some of which are covered today. Some implementationsmay 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 basedon the values in a column. (After all, you wouldn't ask for the average of a singlefield.) 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 10DONKNOW 3B 175 65 23 50 10 1 4 15WORLEY LF 157 49 15 35 8 3 3 16DAVID OF 187 70 24 48 4 0 17 42HAMHOCKER 3B 50 12 10 10 2 0 0 13CASEY DH 1 0 0 0 0 0 0 16 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 conditionin the <TT>WHERE</TT> clause. Say you wanted to know how many ball players were hittingunder 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 involvedin 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. Tofind 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, youget</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 numberno 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 averagenumber 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 theexample 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 gets0 hits in 1 at bat for a 0.0 average. The average of 0.0 and 0.5 is .250. If youcompute 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 andexplains 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 returnsthe 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. Forexample:</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 vitalto 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 numberno rows selected</FONT></PRE><P>you find that <TT>VARIANCE</TT> is another function that works exclusively withnumbers.<H3><FONT COLOR="#000077">STDDEV</FONT></H3><P>The final group function, <TT>STDDEV</TT>, finds the standard deviation of a columnof numbers, as demonstrated by this example:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT STDDEV(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">STDDEV(HITS)------------ 28.336666</FONT></PRE><P>It also returns an error when confronted by a string:</P>?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -