ch03.htm

来自「Learn SQL in an easy way...」· HTM 代码 · 共 1,522 行 · 第 1/4 页

</B>  3<B>  WHERE FIRSTNAME &lt;&gt; 'AL';</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME       FIRSTNAME       AREACODE PHONE    ST ZIP
-------------- -------------- --------- -------- -- -----
MERRICK        BUD                  300 555-6666 CO 80212
MAST           JD                   381 555-6767 LA 23456
BULHER         FERRIS               345 555-3223 IL 23332
</FONT></PRE>
<P>To find everyone not living in California, type this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
</B>  2 <B> FROM FRIENDS
</B>  3 <B> WHERE STATE != 'CA';</B>

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>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice that both symbols, <TT>&lt;&gt;</TT>
	and <TT>!=</TT>, can express &quot;not equals.&quot; 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077">Character Operators</FONT></H3>
<P>You can use character operators to manipulate the way character strings are represented,
both in the output of data and in the process of placing conditions on data to be
retrieved. This section describes two character operators: the <TT>LIKE</TT> operator
and the || operator, which conveys the concept of character concatenation.
<H4><FONT COLOR="#000077">I Want to Be Like </FONT><FONT SIZE="5" COLOR="#000077"><TT>LIKE</TT></FONT></H4>
<P>What if you wanted to select parts of a database that fit a pattern but weren't
quite exact matches? You could use the equal sign and run through all the possible
cases, but that process would be boring and time-consuming. Instead, you could use
<TT>LIKE</TT>. Consider the following:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> SELECT * FROM PARTS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME           LOCATION       PARTNUMBER
-------------- -------------- ----------
APPENDIX       MID-STOMACH             1
ADAMS APPLE    THROAT                  2
HEART          CHEST                   3
SPINE          BACK                    4
ANVIL          EAR                     5
KIDNEY         MID-BACK                6
</FONT></PRE>
<P>How can you find all the parts located in the back? A quick visual inspection
of this simple table shows that it has two parts, but unfortunately the locations
have slightly different names. Try this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
</B>  2 <B> FROM PARTS
 </B> 3 <B> WHERE LOCATION LIKE '%BACK%';</B>

NAME           LOCATION       PARTNUMBER
-------------- -------------- ----------
SPINE          BACK                    4
KIDNEY         MID-BACK                6
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You can see the use of the percent sign (<TT>%</TT>) in the statement after <TT>LIKE</TT>.
When used inside a <TT>LIKE</TT> expression, % is a wildcard. What you asked for
was any occurrence of <TT>BACK</TT> in the column location. If you queried</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
     FROM PARTS</B>
     <B>WHERE LOCATION LIKE 'BACK%';</B>
</FONT></PRE>
<P>you would get any occurrence that started with <TT>BACK</TT>:</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">NAME           LOCATION       PARTNUMBER
-------------- -------------- ----------
SPINE          BACK                    4
</FONT></PRE>
<P>If you queried</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
     FROM PARTS</B>
    <B> WHERE NAME LIKE 'A%';</B>
</FONT></PRE>
<P>you would get any name that starts with <TT>A</TT>:</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">NAME           LOCATION       PARTNUMBER
-------------- -------------- ----------
APPENDIX       MID-STOMACH             1
ADAMS APPLE    THROAT                  2
ANVIL          EAR                     5
</FONT></PRE>
<P>Is <TT>LIKE</TT> case sensitive? Try the next query to find out.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
     FROM PARTS
     WHERE NAME LIKE 'a%';</B>

no rows selected
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The answer is yes. References to data are always case sensitive.</P>
<P>What if you want to find data that matches all but one character in a certain
pattern? In this case you could use a different type of wildcard: the underscore.
<H4><FONT COLOR="#000077">Underscore (_)</FONT></H4>
<P>The underscore is the single-character wildcard. Using a modified version of the
table <TT>FRIENDS</TT>, type this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <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        UD                   300 555-6666 CO 80212
MAST           JD                   381 555-6767 LA 23456
BULHER         FERRIS               345 555-3223 IL 23332
PERKINS        ALTON                911 555-3116 CA 95633
BOSS           SIR                  204 555-2345 CT 95633
</FONT></PRE>
<P>To find all the records where <TT>STATE</TT> starts with <TT>C</TT>, type the
following:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
  </B>2 <B> FROM FRIENDS
</B>  3 <B> WHERE STATE LIKE 'C_';</B></FONT></PRE>
<PRE><FONT COLOR="#0066FF">LASTNAME       FIRSTNAME       AREACODE PHONE    ST ZIP
-------------- -------------- --------- -------- -- -----
MERRICK        BUD                  300 555-6666 CO 80212
PERKINS        ALTON                911 555-3116 CA 95633
BOSS           SIR                  204 555-2345 CT 95633
</FONT></PRE>
<P>You can use several underscores in a statement:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
 </B> 2 <B> FROM FRIENDS
 </B> 3<B>  WHERE PHONE LIKE'555-6_6_';</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">LASTNAME       FIRSTNAME       AREACODE PHONE    ST ZIP
-------------- -------------- --------- -------- -- -----
MERRICK        BUD                  300 555-6666 CO 80212
MAST           JD                   381 555-6767 LA 23456
</FONT></PRE>
<P>The previous statement could also be written as follows:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
  </B>2<B>  FROM FRIENDS
 </B> 3<B>  WHERE PHONE LIKE '555-6%';</B>

LASTNAME       FIRSTNAME       AREACODE PHONE    ST ZIP
-------------- -------------- --------- -------- -- -----
MERRICK        BUD                  300 555-6666 CO 80212
MAST           JD                   381 555-6767 LA 23456
</FONT></PRE>
<P>Notice that the results are identical. These two wildcards can be combined. The
next example finds all records with <TT>L</TT> as the second character:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
</B>  2<B>  FROM FRIENDS
 </B> 3<B>  WHERE FIRSTNAME LIKE '_L%';</B>

LASTNAME       FIRSTNAME       AREACODE PHONE    ST ZIP
-------------- -------------- --------- -------- -- -----
BUNDY          AL                   100 555-1111 IL 22333
MEZA           AL                   200 555-2222 UK
PERKINS        ALTON                911 555-3116 CA 95633
</FONT></PRE>
<H4><FONT COLOR="#000077">Concatenation (||)</FONT></H4>
<P>The <TT>||</TT> (double pipe) symbol concatenates two strings. Try this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT FIRSTNAME || LASTNAME ENTIRENAME
  </B>2<B>  FROM FRIENDS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">ENTIRENAME
----------------------
AL             BUNDY
AL             MEZA
BUD            MERRICK
JD             MAST
FERRIS         BULHER
ALTON          PERKINS
SIR            BOSS
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice that <TT>||</TT> is used instead of <TT>+</TT>. If you use <TT>+</TT> to
try to concatenate the strings, the SQL interpreter used for this example (Personal
Oracle7) returns the following error:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT FIRSTNAME + LASTNAME ENTIRENAME
     FROM FRIENDS;</B>

ERROR:
ORA-01722: invalid number
</FONT></PRE>
<P>It is looking for two numbers to add and throws the error <TT>invalid number</TT>
when it doesn't find any.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Some implementations of SQL use
	the plus sign to concatenate strings. Check your implementation. 
<HR>


</BLOCKQUOTE>

<P>Here's a more practical example using concatenation:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT LASTNAME || ',' || FIRSTNAME NAME
     FROM FRIENDS;
</B>
NAME
------------------------------------------------------
BUNDY    , AL
MEZA     , AL
MERRICK  , BUD
MAST     , JD
BULHER   , FERRIS
PERKINS  , ALTON
BOSS     , SIR
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This statement inserted a comma between the last name and the first name.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice the extra spaces between
	the first name and the last name in these examples. These spaces are actually part
	of the data. With certain data types, spaces are right-padded to values less than
	the total length allocated for a field. See your implementation. Data types will
	be discussed on Day 9, &quot;Creating and Maintaining Tables.&quot; 
<HR>


</BLOCKQUOTE>

<P>So far you have performed the comparisons one at a time. That method is fine for
some problems, but what if you need to find all the people at work with last names
starting with P who have less than three days of vacation time?
<H3><FONT COLOR="#000077">Logical Operators</FONT></H3>
<P>logical operatorsLogical operators separate two or more conditions in the <TT>WHERE</TT>
clause of an SQL statement.</P>
<P>Vacation time is always a hot topic around the workplace. Say you designed a table
called <TT>VACATION</TT> for the accounting department:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM VACATION;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME       EMPLOYEENUM    YEARS  LEAVETAKEN
-------------- ----------- --------- ----------
ABLE                   101        2           4
BAKER                  104        5          23
BLEDSOE                107        8          45
BOLIVAR                233        4          80
BOLD                   210       15         100
COSTALES               211       10          78
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>Suppose your company gives each employee 12 days of leave each year. Using what
you have learned and a logical operator, find all the employees whose names start
with <TT>B</TT> and who have more than <TT>50</TT> days of leave coming.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT LASTNAME,
  </B>2<B>  YEARS * 12 - LEAVETAKEN REMAINING
  </B>3<B>  FROM VACATION
  </B>4<B>  WHERE LASTNAME LIKE 'B%'
</B>  5<B>  AND
</B>  6 <B> YEARS * 12 - LEAVETAKEN &gt; 50;</B>

LASTNAME       REMAINING
-------------- ---------
BLEDSOE               51
BOLD                  80
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This query is the most complicated you have done so far. The <TT>SELECT</TT> clause
(lines 1 and 2) uses arithmetic operators to determine how many days of leave each
employee has remaining. The normal precedence computes <TT>YEARS * 12 - LEAVETAKEN</TT>.
(A clearer approach would be to write <TT>(YEARS * 12) - LEAVETAKEN</TT>.)</P>
<P><TT>LIKE</TT> is used in line 4 with the wildcard <TT>%</TT> to find all the <TT>B</TT>
names. Line 6 uses the <TT>&gt;</TT> to find all occurrences greater than <TT>50</TT>.</P>
<P>The new element is on line 5. You used the logical operator <TT>AND</TT> to ensure
that you found records that met the criteria in lines 4 and 6.
<H4><FONT COLOR="#000077">AND</FONT></H4>
<P><TT>AND</TT> means that the expressions on both sides must be true to return <TT>TRUE</TT>.
If either expression is false, <TT>AND</TT> returns <TT>FALSE</TT>. For example,
to find out which employees have been with the company for <TT>5</TT> years or less
and have taken more than <TT>20</TT> days leave, try this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT LASTNAME
</B>  2 <B> FROM VACATION
</B>  3<B>  WHERE YEARS &lt;= 5 
</B>  4 <B> AND
 </B> 5 <B> LEAVETAKEN &gt; 20 ;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME
--------
BAKER
BOLIVAR
</FONT></PRE>
<P>If you want to know which employees have been with the company for <TT>5</TT>
years or more and have taken less than <TT>50</TT> percent of their leave, you could
write:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT LASTNAME WORKAHOLICS
</B>  2<B>  FROM VACATION
 </B> 3 <B> WHERE YEARS &gt;= 5
 </B> 4<B>  AND
 </B> 5<B>  ((YEARS *12)-LEAVETAKEN)/(YEARS * 12) &lt; 0.50;</B>

WORKAHOLICS
---------------
BAKER
BLEDSOE
</FONT></PRE>
<P>Check these people for burnout. Also check out how we used the <TT>AND</TT> to
combine these two conditions.
<H4><FONT COLOR="#000077">OR</FONT></H4>
<P>You can also use <TT>OR</TT> to sum up a series of conditions. If any of the comparisons
is true, <TT>OR</TT> returns <TT>TRUE</TT>. To illustrate the difference, conditionsrun
the last query with <TT>OR</TT> instead of with <TT>AND</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT LASTNAME WORKAHOLICS
  </B>2 <B> FROM VACATION
  </B>3<B>  WHERE YEARS &gt;= 5
 </B> 4<B>  OR
</B>  5<B>   ((YEARS *12)-LEAVETAKEN)/(YEARS * 12) &gt;= 0.50;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">WORKAHOLICS
---------------
ABLE
BAKER
BLEDSOE
BOLD
COSTALES
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The original names are still in the list, but you have three new entries (who
would probably resent being called workaholics). These three new names made the list
because they satisfied one of the conditions. <TT>OR</TT> requires that only one
of the conditions be true in order for data to be returned.
<H4><FONT COLOR="#000077">NOT</FONT></H4>
<P><TT>NOT</TT> means just that. If the condition it applies to evaluates to <TT>TRUE</TT>,
<TT>NOT</TT> make it <TT>FALSE</TT>. If the condition after the <TT>NOT</TT> is <TT>FALSE</TT>,
it becomes <TT>TRUE</TT>. For example, the following <TT>SELECT</TT> returns the
only two names not beginning with <TT>B</TT> in the table:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
</B>  2 <B> FROM VACATION
  </B>3<B>  WHERE LASTNAME NOT LIKE 'B%';</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME       EMPLOYEENUM    YEARS LEAVETAKEN
-------------- ----------- -------- ----------
ABLE                   101        2          4
COSTALES               211       10         78
</FONT></PRE>
<P><TT>NOT</TT> can also be used with the operator <TT>IS</TT> when applied to <TT>NULL</TT>.
Recall the <TT>PRICES</TT> table where we put a <TT>NULL</TT> value in the <TT>WHOLESALE</TT>
column opposite the item <TT>ORANGES</TT>.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM PRICE;</B>

ITEM           WHOLESALE
-------------- ---------
TOMATOES             .34
POTATOES             .51
BANANAS              .67