📄 ch03.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Day 3 -- Expressions, Conditions, and Operators</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="sams.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="ch02.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch02.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="ch04.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch04.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 3 -<BR>
Expressions, Conditions, and Operators</FONT></H1>
</CENTER>
<H2><FONT COLOR="#000077">Objectives</FONT></H2>
<P>On Day 2, "Introduction to the Query: The <TT>SELECT</TT> Statement,"
you used <TT>SELECT</TT> and <TT>FROM</TT> to manipulate data in interesting (and
useful) ways. Today you learn more about <TT>SELECT</TT> and <TT>FROM</TT> and expand
the basic query with some new terms to go with query, table, and row, as well as
a new clause and a group of handy items called operators. When the sun sets on Day
3, you will
<UL>
<LI>Know what an expression is and how to use it
<P>
<LI>Know what a condition is and how to use it
<P>
<LI>Be familiar with the basic uses of the <TT>WHERE</TT> clause
<P>
<LI>Be able to use arithmetic, comparison, character, logical, and set operators
<P>
<LI>Have a working knowledge of some miscellaneous operators
</UL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>We used Oracle's Personal Oracle7
to generate today's examples. Other implementations of SQL may differ slightly in
the way in which commands are entered or output is displayed, but the results are
basically the same for all implementations that conform to the ANSI standard.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077">Expressions</FONT></H2>
<P>The definition of an expression is simple: An <I>expression</I> returns a value.
Expression types are very broad, covering different data types such as String, Numeric,
and Boolean. In fact, pretty much anything following a clause (<TT>SELECT</TT> or
<TT>FROM</TT>, for example) is an expression. In the following example <TT>amount</TT>
is an expression that returns the value contained in the <TT>amount</TT> column.</P>
<PRE><FONT COLOR="#0066FF">SELECT amount FROM checks;
</FONT></PRE>
<P>In the following statement <TT>NAME, ADDRESS, PHONE</TT> and <TT>ADDRESSBOOK</TT>
are expressions:</P>
<PRE><FONT COLOR="#0066FF">SELECT NAME, ADDRESS, PHONE
FROM ADDRESSBOOK;
</FONT></PRE>
<P>Now, examine the following expression:</P>
<PRE><FONT COLOR="#0066FF">WHERE NAME = 'BROWN'
</FONT></PRE>
<P>It contains a condition, <TT>NAME = 'BROWN'</TT>, which is an example of a Boolean
expression. <TT>NAME = 'BROWN'</TT> will be either <TT>TRUE</TT> or <TT>FALSE</TT>,
depending on the condition <TT>=</TT>.
<H2><FONT COLOR="#000077">Conditions</FONT></H2>
<P>If you ever want to find a particular item or group of items in your database,
you need one or more conditions. Conditions are contained in the <TT>WHERE</TT> clause.
In the preceding example, the condition is</P>
<PRE><FONT COLOR="#0066FF">NAME = 'BROWN'
</FONT></PRE>
<P>To find everyone in your organization who worked more than <TT>100</TT> hours
last month, your condition would be</P>
<PRE><FONT COLOR="#0066FF">NUMBEROFHOURS > 100
</FONT></PRE>
<P>Conditions enable you to make selective queries. In their most common form, conditions
comprise a variable, a constant, and a comparison operator. In the first example
the variable is <TT>NAME</TT>, the constant is <TT>'BROWN'</TT>, and the comparison
operator is <TT>=</TT>. In the second example the variable is <TT>NUMBEROFHOURS</TT>,
the constant is <TT>100</TT>, and the comparison operator is <TT>></TT>. You need
to know about two more elements before you can write conditional queries: the <TT>WHERE</TT>
clause and operators.
<H3><FONT COLOR="#000077">The WHERE Clause</FONT></H3>
<P>The syntax of the <TT>WHERE</TT> clause is</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">WHERE <SEARCH CONDITION>
</FONT></PRE>
<P><TT>SELECT</TT>, <TT>FROM</TT>, and <TT>WHERE</TT> are the three most frequently
used clauses in SQL. <TT>WHERE</TT> simply causes your queries to be more selective.
Without the <TT>WHERE</TT> clause, the most useful thing you could do with a query
is display all records in the selected table(s). For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM BIKES;</B>
</FONT></PRE>
<P>lists all rows of data in the table <TT>BIKES</TT>.</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">NAME FRAMESIZE COMPOSITION MILESRIDDEN TYPE
-------------- --------- ------------ ----------- -------
TREK 2300 22.5 CARBON FIBER 3500 RACING
BURLEY 22 STEEL 2000 TANDEM
GIANT 19 STEEL 1500 COMMUTER
FUJI 20 STEEL 500 TOURING
SPECIALIZED 16 STEEL 100 MOUNTAIN
CANNONDALE 22.5 ALUMINUM 3000 RACING
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>If you wanted a particular bike, you could type</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *
FROM BIKES
WHERE NAME = 'BURLEY';</B>
</FONT></PRE>
<P>which would yield only one record:</P>
<PRE><FONT COLOR="#0066FF">NAME FRAMESIZE COMPOSITION MILESRIDDEN TYPE
-------------- --------- -------------- ----------- -------
BURLEY 22 STEEL 2000 TANDEM
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This simple example shows how you can place a condition on the data that you want
to retrieve.
<H2><FONT COLOR="#000077">Operators</FONT></H2>
<P>Operators are the elements you use inside an expression to articulate how you
want specified conditions to retrieve data. Operators fall into six groups: arithmetic,
comparison, character, logical, set, and miscellaneous.
<H3><FONT COLOR="#000077">Arithmetic Operators</FONT></H3>
<P>The arithmetic operators are plus (<TT>+</TT>), minus (-), divide (<TT>/</TT>),
multiply (<TT>*</TT>), and modulo (<TT>%</TT>). The first four are self-explanatory.
Modulo returns the integer remainder of a division. Here are two examples:</P>
<PRE><FONT COLOR="#0066FF">5 % 2 = 1
6 % 2 = 0
</FONT></PRE>
<P>The modulo operator does not work with data types that have decimals, such as
Real or Number.</P>
<P>If you place several of these arithmetic operators in an expression without any
parentheses, the operators are resolved in this order: multiplication, division,
modulo, addition, and subtraction. For example, the expression</P>
<PRE><FONT COLOR="#0066FF">2*6+9/3
</FONT></PRE>
<P>equals</P>
<PRE><FONT COLOR="#0066FF">12 + 3 = 15
</FONT></PRE>
<P>However, the expression</P>
<PRE><FONT COLOR="#0066FF">2 * (6 + 9) / 3
</FONT></PRE>
<P>equals</P>
<PRE><FONT COLOR="#0066FF">2 * 15 / 3 = 10
</FONT></PRE>
<P>Watch where you put those parentheses! Sometimes the expression does exactly what
you tell it to do, rather than what you want it to do.</P>
<P>The following sections examine the arithmetic operators in some detail and give
you a chance to write some queries.
<H4><FONT COLOR="#000077">Plus (+)</FONT></H4>
<P>You can use the plus sign in several ways. Type the following statement to display
the <TT>PRICE</TT> table:</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>Now type:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT ITEM, WHOLESALE, WHOLESALE + 0.15
FROM PRICE;</B>
</FONT></PRE>
<P>Here the <TT>+</TT> adds 15 cents to each price to produce the following:</P>
<PRE><FONT COLOR="#0066FF">ITEM WHOLESALE WHOLESALE+0.15
-------------- --------- --------------
TOMATOES .34 .49
POTATOES .51 .66
BANANAS .67 .82
TURNIPS .45 .60
CHEESE .89 1.04
APPLES .23 .38
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>What is this last column with the unattractive column heading <TT>WHOLESALE+0.15</TT>?
It's not in the original table. (Remember, you used <TT>*</TT> in the <TT>SELECT</TT>
clause, which causes all the columns to be shown.) SQL allows you to create a virtual
or derived column by combining or modifying existing columns.</P>
<P>Retype the original entry:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM PRICE;</B>
</FONT></PRE>
<P>The following table results:</P>
<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>
<H5>ANALYSIS:</H5>
<P>The output confirms that the original data has not been changed and that the column
heading <TT>WHOLESALE+0.15</TT> is not a permanent part of it. In fact, the column
heading is so unattractive that you should do something about it.</P>
<P>Type the following:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT ITEM, WHOLESALE, (WHOLESALE + 0.15) RETAIL
FROM PRICE;</B>
</FONT></PRE>
<P>Here's the result:</P>
<PRE><FONT COLOR="#0066FF">ITEM WHOLESALE RETAIL
-------------- --------- ------
TOMATOES .34 .49
POTATOES .51 .66
BANANAS .67 .82
TURNIPS .45 .60
CHEESE .89 1.04
APPLES .23 .38
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This is wonderful! Not only can you create new columns, but you can also rename
them on the fly. You can rename any of the columns using the syntax <TT>column_name
alias</TT> (note the space between <TT>column_name</TT> and <TT>alias</TT>).</P>
<P>For example, the query</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT ITEM PRODUCE, WHOLESALE, WHOLESALE + 0.25 RETAIL
FROM PRICE;</B>
</FONT></PRE>
<P>renames the columns as follows:</P>
<PRE><FONT COLOR="#0066FF">PRODUCE WHOLESALE RETAIL
-------------- --------- ---------
TOMATOES .34 .59
POTATOES .51 .76
BANANAS .67 .92
TURNIPS .45 .70
CHEESE .89 1.14
APPLES .23 .48
</FONT></PRE>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Some implementations of SQL use
the syntax <TT><column name = alias></TT>. The preceding example would be written
as follows:</P>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT ITEM = PRODUCE,
WHOLESALE,
WHOLESALE + 0.25 = RETAIL,
FROM PRICE;</B></FONT></PRE>
<P>Check your implementation for the exact syntax.
<HR>
</BLOCKQUOTE>
<P>You might be wondering what use aliasing is if you are not using command-line
SQL. Fair enough. Have you ever wondered how report builders work? Someday, when
you are asked to write a report generator, you'll remember this and not spend weeks
reinventing what Dr. Codd and IBM have wrought.</P>
<P>So far, you have seen two uses of the plus sign. The first instance was the use
of the plus sign in the <TT>SELECT</TT> clause to perform a calculation on the data
and display the calculation. The second use of the plus sign is in the <TT>WHERE</TT>
clause. Using operators in the <TT>WHERE</TT> clause gives you more flexibility when
you specify conditions for retrieving data.</P>
<P>In some implementations of SQL, the plus sign does double duty as a character
operator. You'll see that side of the plus a little later today.
<H4><FONT COLOR="#000077">Minus (-)</FONT></H4>
<P>Minus also has two uses. First, it can change the sign of a number. You can use
the table <TT>HILOW</TT> to demonstrate this function.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL><B> SELECT * FROM HILOW;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">STATE HIGHTEMP LOWTEMP
---------- -------- ---------
CA -50 120
FL 20 110
LA 15 99
ND -70 101
NE -60 100
</FONT></PRE>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -