📄 ch03.htm
字号:
<P>For example, here's a way to manipulate the data:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT STATE, -HIGHTEMP LOWS, -LOWTEMP HIGHS
FROM HILOW;</B>
STATE LOWS HIGHS
---------- -------- ---------
CA 50 -120
FL -20 -110
LA -15 -99
ND 70 -101
NE 60 -100
</FONT></PRE>
<P>The second (and obvious) use of the minus sign is to subtract one column from
another. For example:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT STATE,
</B> 2<B> HIGHTEMP LOWS,
</B> 3 <B> LOWTEMP HIGHS,
</B> 4<B> (LOWTEMP - HIGHTEMP) DIFFERENCE
</B> 5 <B> FROM HILOW;</B>
STATE LOWS HIGHS DIFFERENCE
---------- -------- -------- ----------
CA -50 120 170
FL 20 110 90
LA 15 99 84
ND -70 101 171
NE -60 100 160
</FONT></PRE>
<P>Notice the use of aliases to fix the data that was entered incorrectly. This remedy
is merely a temporary patch, though, and not a permanent fix. You should see to it
that the data is corrected and entered correctly in the future. On Day 21, "Common
SQL Mistakes/Errors and Resolutions," you'll learn how to correct bad data.</P>
<P>This query not only fixed (at least visually) the incorrect data but also created
a new column containing the difference between the highs and lows of each state.</P>
<P>If you accidentally use the minus sign on a character field, you get something
like this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL><B> SELECT -STATE FROM HILOW;</B>
ERROR:
ORA-01722: invalid number
no rows selected
</FONT></PRE>
<P>The exact error message varies with implementation, but the result is the same.
<H4><FONT COLOR="#000077">Divide (/)</FONT></H4>
<P>The division operator has only the one obvious meaning. Using the table <TT>PRICE</TT>,
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 .34
POTATOES .51
BANANAS .67
TURNIPS .45
CHEESE .89
APPLES .23
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>You can show the effects of a two-for-one sale by typing the next statement:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT ITEM, WHOLESALE, (WHOLESALE/2) SALEPRICE
2 FROM PRICE;</B>
ITEM WHOLESALE SALEPRICE
-------------- --------- ---------
TOMATOES .34 .17
POTATOES .51 .255
BANANAS .67 .335
TURNIPS .45 .225
CHEESE .89 .445
APPLES .23 .115
</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 .34
POTATOES .51
BANANAS .67
TURNIPS .45
CHEESE .89
APPLES .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 .306
POTATOES .51 .459
BANANAS .67 .603
TURNIPS .45 .405
CHEESE .89 .801
APPLES .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. Using
the 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 following
statement 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. Using
the 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 current
first and last columns.
<H3><FONT COLOR="#000077">Comparison Operators</FONT></H3>
<P>True to their name, comparison operators compare expressions and return one of
three 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 what
is <TT>Unknown</TT>?</P>
<P>To understand how you could get an <TT>Unknown</TT>, you need to know a little
about the concept of <TT>NULL</TT>. In database terms <TT>NULL</TT> is the absence
of data in a field. It does not mean a column has a zero or a blank in it. A zero
or a blank is a value. <TT>NULL</TT> means nothing is in that field. If you make
a 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 uncomfortable
condition, most flavors of SQL change <TT>Unknown</TT> to <TT>FALSE</TT> and provide
a 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> table
does not contain a value for <TT>WHOLESALE</TT>. The results of a query might look
like 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 .34
POTATOES .51
BANANAS .67
TURNIPS .45
CHEESE .89
APPLES .23
ORANGES
</FONT></PRE>
<P>Notice that nothing is printed out in the <TT>WHOLESALE</TT> field position for
oranges. 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 impossible
to 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 use
the 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> returned
a <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's
a 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 equal
sign is the most commonly used comparison operator. Used alone, the equal sign is
a 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 22333
MEZA AL 200 555-2222 UK
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER 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 have
more 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 22333
MEZA 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.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -