?? 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="../buttonart/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/ch02.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch04/ch04.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/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 (anduseful) ways. Today you learn more about <TT>SELECT</TT> and <TT>FROM</TT> and expandthe basic query with some new terms to go with query, table, and row, as well asa new clause and a group of handy items called operators. When the sun sets on Day3, 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, PHONEFROM 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 Booleanexpression. <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> hourslast 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, conditionscomprise a variable, a constant, and a comparison operator. In the first examplethe variable is <TT>NAME</TT>, the constant is <TT>'BROWN'</TT>, and the comparisonoperator 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 needto 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 frequentlyused 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 queryis 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 RACINGBURLEY 22 STEEL 2000 TANDEMGIANT 19 STEEL 1500 COMMUTERFUJI 20 STEEL 500 TOURINGSPECIALIZED 16 STEEL 100 MOUNTAINCANNONDALE 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 wantto retrieve.<H2><FONT COLOR="#000077">Operators</FONT></H2><P>Operators are the elements you use inside an expression to articulate how youwant 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 = 16 % 2 = 0</FONT></PRE><P>The modulo operator does not work with data types that have decimals, such asReal or Number.</P><P>If you place several of these arithmetic operators in an expression without anyparentheses, 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 whatyou 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 giveyou 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 displaythe <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 .34POTATOES .51BANANAS .67TURNIPS .45CHEESE .89APPLES .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 .49POTATOES .51 .66BANANAS .67 .82TURNIPS .45 .60CHEESE .89 1.04APPLES .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 virtualor 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 .34POTATOES .51BANANAS .67TURNIPS .45CHEESE .89APPLES .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 columnheading <TT>WHOLESALE+0.15</TT> is not a permanent part of it. In fact, the columnheading 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 .49POTATOES .51 .66BANANAS .67 .82TURNIPS .45 .60CHEESE .89 1.04APPLES .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 renamethem on the fly. You can rename any of the columns using the syntax <TT>column_namealias</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 .59POTATOES .51 .76BANANAS .67 .92TURNIPS .45 .70CHEESE .89 1.14APPLES .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-lineSQL. Fair enough. Have you ever wondered how report builders work? Someday, whenyou are asked to write a report generator, you'll remember this and not spend weeksreinventing 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 useof the plus sign in the <TT>SELECT</TT> clause to perform a calculation on the dataand 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 whenyou specify conditions for retrieving data.</P><P>In some implementations of SQL, the plus sign does double duty as a characteroperator. 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 usethe 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 120FL 20 110LA 15 99ND -70 101NE -60 100</FONT></PRE><P>For example, here's a way to manipulate the data:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT STATE, -HIGHTEMP LOWS, -LOWTEMP HIGHS FROM HILOW;</B>STATE LOWS HIGHS---------- -------- ---------CA 50 -120FL -20 -110LA -15 -99ND 70 -101NE 60 -100</FONT></PRE><P>The second (and obvious) use of the minus sign is to subtract one column fromanother. For example:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT STATE,</B> 2<B> HIGHTEMP LOWS,</B> 3 <B> LOWTEMP HIGHS,</B> 4<B> (LOWTEMP - HIGHTEMP) DIFFERENCE</B> 5 <B> FROM HILOW;</B>STATE LOWS HIGHS DIFFERENCE---------- -------- -------- ----------CA -50 120 170FL 20 110 90LA 15 99 84ND -70 101 171NE -60 100 160</FONT></PRE><P>Notice the use of aliases to fix the data that was entered incorrectly. This remedyis merely a temporary patch, though, and not a permanent fix. You should see to itthat the data is corrected and entered correctly in the future. On Day 21, "CommonSQL Mistakes/Errors and Resolutions," you'll learn how to correct bad data.</P><P>This query not only fixed (at least visually) the incorrect data but also createda new column containing the difference between the highs and lows of each state.</P><P>If you accidentally use the minus sign on a character field, you get somethinglike this:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL><B> SELECT -STATE FROM HILOW;</B>ERROR:ORA-01722: invalid numberno rows selected</FONT></PRE><P>The exact error message varies with implementation, but the result is the same.<H4><FONT COLOR="#000077">Divide (/)</FONT></H4><P>The division operator has only the one obvious meaning. Using the table <TT>PRICE</TT>,type the following:</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 .34POTATOES .51BANANAS .67TURNIPS .45CHEESE .89APPLES .23</FONT></PRE><PRE><FONT COLOR="#0066FF">6 rows selected.</FONT></PRE><P>You can show the effects of a two-for-one sale by typing the next statement:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT ITEM, WHOLESALE, (WHOLESALE/2) SALEPRICE 2 FROM PRICE;</B>ITEM WHOLESALE SALEPRICE-------------- --------- ---------TOMATOES .34 .17POTATOES .51 .255BANANAS .67 .335TURNIPS .45 .225CHEESE .89 .445APPLES .23 .115?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -