?? ch03.htm
字號:
</B> 3<B> WHERE FIRSTNAME <> '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 80212MAST JD 381 555-6767 LA 23456BULHER 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> <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 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><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice that both symbols, <TT><></TT> and <TT>!=</TT>, can express "not equals." <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 beretrieved. This section describes two character operators: the <TT>LIKE</TT> operatorand 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'tquite exact matches? You could use the equal sign and run through all the possiblecases, 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><B> SELECT * FROM PARTS;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">NAME LOCATION PARTNUMBER-------------- -------------- ----------APPENDIX MID-STOMACH 1ADAMS APPLE THROAT 2HEART CHEST 3SPINE BACK 4ANVIL EAR 5KIDNEY MID-BACK 6</FONT></PRE><P>How can you find all the parts located in the back? A quick visual inspectionof this simple table shows that it has two parts, but unfortunately the locationshave slightly different names. Try this:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B> 2 <B> FROM PARTS </B> 3 <B> WHERE LOCATION LIKE '%BACK%';</B>NAME LOCATION PARTNUMBER-------------- -------------- ----------SPINE BACK 4KIDNEY 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 forwas any occurrence of <TT>BACK</TT> in the column location. If you queried</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <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> <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 1ADAMS APPLE THROAT 2ANVIL 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> <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 certainpattern? 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 thetable <TT>FRIENDS</TT>, type 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 UD 300 555-6666 CO 80212MAST JD 381 555-6767 LA 23456BULHER FERRIS 345 555-3223 IL 23332PERKINS ALTON 911 555-3116 CA 95633BOSS SIR 204 555-2345 CT 95633</FONT></PRE><P>To find all the records where <TT>STATE</TT> starts with <TT>C</TT>, type thefollowing:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <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 80212PERKINS ALTON 911 555-3116 CA 95633BOSS 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> <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 80212MAST 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> <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 80212MAST JD 381 555-6767 LA 23456</FONT></PRE><P>Notice that the results are identical. These two wildcards can be combined. Thenext 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 22333MEZA AL 200 555-2222 UKPERKINS 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 BUNDYAL MEZABUD MERRICKJD MASTFERRIS BULHERALTON PERKINSSIR 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> totry to concatenate the strings, the SQL interpreter used for this example (PersonalOracle7) 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 , ALMEZA , ALMERRICK , BUDMAST , JDBULHER , FERRISPERKINS , ALTONBOSS , 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 forsome problems, but what if you need to find all the people at work with last namesstarting 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 tablecalled <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 4BAKER 104 5 23BLEDSOE 107 8 45BOLIVAR 233 4 80BOLD 210 15 100COSTALES 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 whatyou have learned and a logical operator, find all the employees whose names startwith <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 51BOLD 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 eachemployee 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 ensurethat 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 lessand 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--------BAKERBOLIVAR</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 couldwrite:</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---------------BAKERBLEDSOE</FONT></PRE><P>Check these people for burnout. Also check out how we used the <TT>AND</TT> tocombine 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 comparisonsis true, <TT>OR</TT> returns <TT>TRUE</TT>. To illustrate the difference, conditionsrunthe 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---------------ABLEBAKERBLEDSOEBOLDCOSTALES</FONT></PRE><H5>ANALYSIS:</H5><P>The original names are still in the list, but you have three new entries (whowould probably resent being called workaholics). These three new names made the listbecause they satisfied one of the conditions. <TT>OR</TT> requires that only oneof 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 theonly 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 4COSTALES 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 .34POTATOES .51BANANAS .67?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -