ch03.htm
来自「Learn SQL in an easy way...」· HTM 代码 · 共 1,522 行 · 第 1/4 页
HTM
1,522 行
</FONT></PRE><PRE><FONT COLOR="#0066FF">6 rows selected.</FONT></PRE><P>The use of division in the preceding <TT>SELECT</TT> statement is straightforward(except that coming up with half pennies can be tough).<H4><FONT COLOR="#000077">Multiply (*)</FONT></H4><P>The multiplication operator is also straightforward. Again, using the <TT>PRICE</TT>table, type the following:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM PRICE;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">ITEM WHOLESALE-------------- ---------TOMATOES .34POTATOES .51BANANAS .67TURNIPS .45CHEESE .89APPLES .23</FONT></PRE><PRE><FONT COLOR="#0066FF">6 rows selected.</FONT></PRE><P>This query changes the table to reflect an across-the-board 10 percent discount:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT ITEM, WHOLESALE, WHOLESALE * 0.9 NEWPRICE FROM PRICE;</B>ITEM WHOLESALE NEWPRICE-------------- --------- --------TOMATOES .34 .306POTATOES .51 .459BANANAS .67 .603TURNIPS .45 .405CHEESE .89 .801APPLES .23 .207</FONT></PRE><PRE><FONT COLOR="#0066FF">6 rows selected.</FONT></PRE><P>These operators enable you to perform powerful calculations in a <TT>SELECT</TT>statement.<H4><FONT COLOR="#000077">Modulo (%)</FONT></H4><P>The modulo operator returns the integer remainder of the division operation. Usingthe table <TT>REMAINS</TT>, type the following:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM REMAINS;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">NUMERATOR DENOMINATOR--------- ------------ 10 5 8 3 23 9 40 17 1024 16 85 34</FONT></PRE><PRE><FONT COLOR="#0066FF">6 rows selected.</FONT></PRE><P>You can also create a new column, <TT>REMAINDER</TT>, to hold the values of <TT>NUMERATOR% DENOMINATOR</TT>:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT NUMERATOR, DENOMINATOR, NUMERATOR%DENOMINATOR REMAINDER FROM REMAINS;</B>NUMERATOR DENOMINATOR REMAINDER--------- ----------- --------- 10 5 0 8 3 2 23 9 5 40 17 6 1024 16 0 85 34 17</FONT></PRE><PRE><FONT COLOR="#0066FF">6 rows selected.</FONT></PRE><P>Some implementations of SQL implement modulo as a function called <TT>MOD</TT>(see Day 4, "Functions: Molding the Data You Retrieve"). The followingstatement produces results that are identical to the results in the preceding statement:</P><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT NUMERATOR, DENOMINATOR, MOD(NUMERATOR,DENOMINATOR) REMAINDER FROM REMAINS;</B></FONT></PRE><H4><FONT COLOR="#000077">Precedence</FONT></H4><P>This section examines the use of precedence in a <TT>SELECT</TT> statement. Usingthe database <TT>PRECEDENCE</TT>, type the following:</P><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM PRECEDENCE;</B> N1 N2 N3 N4--------- --------- --------- --------- 1 2 3 4 13 24 35 46 9 3 23 5 63 2 45 3 7 2 1 4</FONT></PRE><P>Use the following code segment to test precedence:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL><B> SELECT 2 N1+N2*N3/N4, 3 (N1+N2)*N3/N4, 4 N1+(N2*N3)/N4 5 FROM PRECEDENCE;</B>N1+N2*N3/N4 (N1+N2)*N3/N4 N1+(N2*N3)/N4----------- ------------- ------------- 2.5 2.25 2.5 31.26087 28.152174 31.26087 22.8 55.2 22.8 93 975 93 7.5 2.25 7.5</FONT></PRE><P>Notice that the first and last columns are identical. If you added a fourth column<TT>N1+N2* (N3/N4)</TT>, its values would also be identical to those of the currentfirst and last columns.<H3><FONT COLOR="#000077">Comparison Operators</FONT></H3><P>True to their name, comparison operators compare expressions and return one ofthree values: <TT>TRUE</TT>, <TT>FALSE</TT>, or <TT>Unknown</TT>. Wait a minute!<TT>Unknown</TT>? <TT>TRUE</TT> and <TT>FALSE</TT> are self-explanatory, but whatis <TT>Unknown</TT>?</P><P>To understand how you could get an <TT>Unknown</TT>, you need to know a littleabout the concept of <TT>NULL</TT>. In database terms <TT>NULL</TT> is the absenceof data in a field. It does not mean a column has a zero or a blank in it. A zeroor a blank is a value. <TT>NULL</TT> means nothing is in that field. If you makea comparison like <TT>Field = 9</TT> and the only value for <TT>Field</TT> is <TT>NULL</TT>,the comparison will come back <TT>Unknown</TT>. Because <TT>Unknown</TT> is an uncomfortablecondition, most flavors of SQL change <TT>Unknown</TT> to <TT>FALSE</TT> and providea special operator, <TT>IS NULL</TT>, to test for a <TT>NULL</TT> condition.</P><P>Here's an example of <TT>NULL</TT>: Suppose an entry in the <TT>PRICE</TT> tabledoes not contain a value for <TT>WHOLESALE</TT>. The results of a query might looklike this:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM PRICE;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">ITEM WHOLESALE-------------- ----------TOMATOES .34POTATOES .51BANANAS .67TURNIPS .45CHEESE .89APPLES .23ORANGES</FONT></PRE><P>Notice that nothing is printed out in the <TT>WHOLESALE</TT> field position fororanges. The value for the field <TT>WHOLESALE</TT> for oranges is <TT>NULL</TT>.The <TT>NULL</TT> is noticeable in this case because it is in a numeric column. However,if the <TT>NULL</TT> appeared in the <TT>ITEM</TT> column, it would be impossibleto tell the difference between <TT>NULL</TT> and a blank.</P><P>Try to find the <TT>NULL</TT>:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B> 2 <B> FROM PRICE </B> 3<B> WHERE WHOLESALE IS NULL;</B>ITEM WHOLESALE-------------- ----------ORANGES</FONT></PRE><H5>ANALYSIS:</H5><P>As you can see by the output, <TT>ORANGES</TT> is the only item whose value for<TT>WHOLESALE</TT> is <TT>NULL</TT> or does not contain a value. What if you usethe equal sign (<TT>=</TT>) instead?</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM PRICE WHERE WHOLESALE = NULL;</B></FONT></PRE><PRE><FONT COLOR="#0066FF">no rows selected</FONT></PRE><H5>ANALYSIS:</H5><P>You didn't find anything because the comparison <TT>WHOLESALE = NULL</TT> returneda <TT>FALSE</TT>--the result was unknown. It would be more appropriate to use an<TT>IS NULL </TT>instead of <TT>=</TT>, changing the <TT>WHERE</TT> statement to<TT>WHERE WHOLESALE IS NULL</TT>. In this case you would get all the rows where a<TT>NULL</TT> existed.</P><P>This example also illustrates both the use of the most common comparison operator,the equal sign (<TT>=</TT>), and the playground of all comparison operators, the<TT>WHERE</TT> clause. You already know about the <TT>WHERE</TT> clause, so here'sa brief look at the equal sign.<H4><FONT COLOR="#000077">Equal (=)</FONT></H4><P>Earlier today you saw how some implementations of SQL use the equal sign in the<TT>SELECT</TT> clause to assign an alias. In the <TT>WHERE</TT> clause, the equalsign is the most commonly used comparison operator. Used alone, the equal sign isa very convenient way of selecting one value out of many. Try this:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM FRIENDS;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">LASTNAME FIRSTNAME AREACODE PHONE ST ZIP-------------- -------------- --------- -------- -- -----BUNDY AL 100 555-1111 IL 22333MEZA AL 200 555-2222 UKMERRICK BUD 300 555-6666 CO 80212MAST JD 381 555-6767 LA 23456BULHER FERRIS 345 555-3223 IL 23332</FONT></PRE><P>Let's find JD's row. (On a short list this task appears trivial, but you may havemore friends than we do--or you may have a list with thousands of records.)</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM FRIENDS WHERE FIRSTNAME = 'JD';</B>LASTNAME FIRSTNAME AREACODE PHONE ST ZIP-------------- -------------- --------- -------- -- -----MAST JD 381 555-6767 LA 23456</FONT></PRE><P>We got the result that we expected. Try this:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM FRIENDS WHERE FIRSTNAME = 'AL';</B>LASTNAME FIRSTNAME AREACODE PHONE ST ZIP-------------- -------------- --------- -------- -- -----BUNDY AL 100 555-1111 IL 22333MEZA AL 200 555-2222 UK</FONT></PRE><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Here you see that <TT>=</TT> can pull in multiple records. Notice that <TT>ZIP</TT> is blank on the second record. <TT>ZIP</TT> is a character field (you learn how to create and populate tables on Day 8, "Manipulating Data"), and in this particular record the <TT>NULL</TT> demonstrates that a <TT>NULL</TT> in a character field is impossible to differentiate from a blank field. <HR></BLOCKQUOTE><P>Here's another very important lesson concerning case sensitivity:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM FRIENDS WHERE FIRSTNAME = 'BUD';</B>FIRSTNAME--------------BUD1 row selected.</FONT></PRE><P>Now try this:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>select * from friends where firstname = 'Bud';</B>no rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>Even though SQL syntax is not case sensitive, data is. Most companies prefer tostore data in uppercase to provide data consistency. You should always store dataeither in all uppercase or in all lowercase. Mixing case creates difficulties whenyou try to retrieve accurate data.<H4><FONT COLOR="#000077">Greater Than (>) and Greater Than or Equal To (>=)</FONT></H4><P>The greater than operator (<TT>></TT>) works like this:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM FRIENDS WHERE AREACODE > 300;</B></FONT></PRE><H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5><PRE><FONT COLOR="#0066FF">LASTNAME FIRSTNAME AREACODE PHONE ST ZIP-------------- -------------- --------- -------- -- -----MAST JD 381 555-6767 LA 23456BULHER FERRIS 345 555-3223 IL 23332</FONT></PRE><H5>ANALYSIS:</H5><P>This example found all the area codes greater than (but not including) <TT>300</TT>.To include <TT>300</TT>, type this:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B> 2<B> FROM FRIENDS</B> 3<B> WHERE AREACODE >= 300;</B>LASTNAME FIRSTNAME AREACODE PHONE ST ZIP-------------- -------------- --------- -------- -- -----MERRICK BUD 300 555-6666 CO 80212MAST JD 381 555-6767 LA 23456BULHER FERRIS 345 555-3223 IL 23332</FONT></PRE><H5>ANALYSIS:</H5><P>With this change you get area codes starting at <TT>300</TT> and going up. Youcould achieve the same results with the statement <TT>AREACODE > 299</TT>.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice that no quotes surround <TT>300</TT> in this SQL statement. Number-defined fieldsnumber-defined fields do not require quotes. <HR></BLOCKQUOTE><H4><FONT COLOR="#000077">Less Than (<) and Less Than or Equal To (<=)</FONT></H4><P>As you might expect, these comparison operators work the same way as <TT>></TT>and <TT>>=</TT> work, only in reverse:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B> 2 <B> FROM FRIENDS</B> 3<B> WHERE STATE < 'LA';</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#000000"></FONT></PRE><PRE><FONT COLOR="#0066FF">LASTNAME FIRSTNAME AREACODE PHONE ST ZIP-------------- -------------- --------- -------- -- ------BUNDY AL 100 555-1111 IL 22333MERRICK BUD 300 555-6666 CO 80212BULHER FERRIS 345 555-3223 IL 23332</FONT></PRE><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>How did <TT>STATE</TT> get changed to <TT>ST</TT>? Because the column has only two characters, the column name is shortened to two characters in the returned rows. If the column name had been <TT>COWS</TT>, it would come out <TT>CO</TT>. The widths of <TT>AREACODE</TT> and <TT>PHONE</TT> are wider than their column names, so they are not truncated. <HR></BLOCKQUOTE><H5>ANALYSIS:</H5><P>Wait a minute. Did you just use <TT><</TT> on a character field? Of courseyou did. You can use any of these operators on any data type. The result varies bydata type. For example, use lowercase in the following state search:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B> 2<B> FROM FRIENDS</B> 3<B> WHERE STATE < 'la';</B>LASTNAME FIRSTNAME AREACODE PHONE ST ZIP-------------- -------------- --------- -------- -- -----BUNDY AL 100 555-1111 IL 22333MEZA AL 200 555-2222 UKMERRICK BUD 300 555-6666 CO 80212MAST JD 381 555-6767 LA 23456BULHER FERRIS 345 555-3223 IL 23332</FONT></PRE><H5>ANALYSIS:</H5><P>Uppercase is usually sorted before lowercase; therefore, the uppercase codes returnedare less than <TT>'la'</TT>. Again, to be safe, check your implementation.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>To be sure of how these operators will behave, check your language tables. Most PC implementations use the ASCII tables. Some other platforms use EBCDIC. <HR></BLOCKQUOTE><P>To include the state of Louisiana in the original search, type</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * </B> 2<B> FROM FRIENDS</B> 3<B> WHERE STATE <= 'LA';</B>LASTNAME FIRSTNAME AREACODE PHONE ST ZIP-------------- -------------- --------- -------- -- -----BUNDY AL 100 555-1111 IL 22333MERRICK BUD 300 555-6666 CO 80212MAST JD 381 555-6767 LA 23456BULHER FERRIS 345 555-3223 IL 23332</FONT></PRE><H4><FONT COLOR="#000077">Inequalities (< > or !=)</FONT></H4><P>When you need to find everything except for certain data, use the inequality symbol,which can be either <TT>< ></TT> or <TT>!=</TT>, depending on your SQL implementation.For example, to find everyone who is not <TT>AL</TT>, type this:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B> 2 <B> FROM FRIENDS⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?