📄 ch03.htm
字号:
</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> <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> <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> <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> <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, "Creating and Maintaining Tables."
<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> <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> <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 > 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>></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> <B>SELECT LASTNAME
</B> 2 <B> FROM VACATION
</B> 3<B> WHERE YEARS <= 5
</B> 4 <B> AND
</B> 5 <B> LEAVETAKEN > 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> <B>SELECT LASTNAME WORKAHOLICS
</B> 2<B> FROM VACATION
</B> 3 <B> WHERE YEARS >= 5
</B> 4<B> AND
</B> 5<B> ((YEARS *12)-LEAVETAKEN)/(YEARS * 12) < 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> <B>SELECT LASTNAME WORKAHOLICS
</B>2 <B> FROM VACATION
</B>3<B> WHERE YEARS >= 5
</B> 4<B> OR
</B> 5<B> ((YEARS *12)-LEAVETAKEN)/(YEARS * 12) >= 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> <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> <B>SELECT * FROM PRICE;</B>
ITEM WHOLESALE
-------------- ---------
TOMATOES .34
POTATOES .51
BANANAS .67
TURNIPS .45
CHEESE .89
APPLES .23
ORANGES
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">7 rows selected.
</FONT></PRE>
<P>To find the non-<TT>NULL</TT> items, type this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *
</B>2<B> FROM PRICE
</B> 3<B> WHERE WHOLESALE IS NOT NULL;
</B>
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>
<H3><FONT COLOR="#000077">Set Operators</FONT></H3>
<P>On Day 1, "Introduction to SQL," you learned that SQL is based on the
theory of sets. The following sections examine set operators.
<H4><FONT COLOR="#000077">UNION and UNION ALL</FONT></H4>
<P><TT>UNION</TT> returns the results of two queries minus the duplicate rows. The
following two tables represent the rosters of teams:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM FOOTBALL;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME
--------------------
ABLE
BRAVO
CHARLIE
DECON
EXITOR
FUBAR
GOOBER
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">7 rows selected.
</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM SOFTBALL;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME
--------------------
ABLE
BAKER
CHARLIE
DEAN
EXITOR
FALCONER
GOOBER
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">7 rows selected.
</FONT></PRE>
<P>How many different people play on one team or another?</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT NAME FROM SOFTBALL
</B>2<B> UNION
</B> 3<B> SELECT NAME FROM FOOTBALL;
</B>
NAME
--------------------
ABLE
BAKER
BRAVO
CHARLIE
DEAN
DECON
EXITOR
FALCONER
FUBAR
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -