📄 ch03.htm
字号:
<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
--------------
BUD
1 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 to
store data in uppercase to provide data consistency. You should always store data
either in all uppercase or in all lowercase. Mixing case creates difficulties when
you 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 23456
BULHER 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 80212
MAST JD 381 555-6767 LA 23456
BULHER 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. You
could 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 22333
MERRICK BUD 300 555-6666 CO 80212
BULHER 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 course
you did. You can use any of these operators on any data type. The result varies by
data 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 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>
<H5>ANALYSIS:</H5>
<P>Uppercase is usually sorted before lowercase; therefore, the uppercase codes returned
are 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 22333
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER 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
</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 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> <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><></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 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><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> <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> <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 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> <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> <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> <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> <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> <B>SELECT *
</B>2<B> FROM FRIENDS
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -