?? ch02.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Day 2 -- Introduction to the Query: The SELECT Statement</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="../ch01/ch01.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch03/ch03.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 2 -<BR>Introduction to the Query: The <TT>SELECT</TT> Statement</FONT></H1></CENTER><H2><FONT COLOR="#000077">Objectives</FONT></H2><P>Welcome to Day 2! By the end of the day you will be able to do the following:<UL> <LI>Write an SQL query <P> <LI>Select and list all rows and columns from a table <P> <LI>Select and list selected columns from a table <P> <LI>Select and list columns from multiple tables</UL><H2><FONT COLOR="#000077">Background</FONT></H2><P>To fully use the power of a relational database as described briefly on Day 1,"Introduction to SQL," you need to communicate with it. The ultimate communicationwould be to turn to your computer and say, in a clear, distinct voice, "Showme all the left-handed, brown-eyed bean counters who have worked for this companyfor at least 10 years." A few of you may already be doing so (talking to yourcomputer, not listing bean counters). Everyone else needs a more conventional wayof retrieving information from the database. You can make this vital link throughSQL's middle name, "Query."</P><P>As mentioned on Day 1, the name Query is really a misnomer in this context. AnSQL query is not necessarily a question to the database. It can be a command to doone of the following:<UL> <LI>Build or delete a table <P> <LI>Insert, modify, or delete rows or fields <P> <LI>Search several tables for specific information and return the results in a specific order <P> <LI>Modify security information</UL><P>A query can also be a simple question to the database. To use this powerful tool,you need to learn how to write an SQL query.<H2><FONT COLOR="#000077">General Rules of Syntax</FONT></H2><P>As you will find, syntax in SQL is quite flexible, although there are rules tofollow as in any programming language. A simple query illustrates the basic syntaxof an SQL select statement. Pay close attention to the case, spacing, and logicalseparation of the components of each query by SQL keywords.</P><PRE><FONT COLOR="#0066FF"><B>SELECT NAME, STARTTERM, ENDTERMFROM PRESIDENTSWHERE NAME = 'LINCOLN';</B></FONT></PRE><P>In this example everything is capitalized, but it doesn't have to be. The precedingquery would work just as well if it were written like this:</P><PRE><FONT COLOR="#0066FF"><B>select name, startterm, endtermfrom presidentswhere name = 'LINCOLN';</B></FONT></PRE><P>Notice that <TT>LINCOLN</TT> appears in capital letters in both examples. Althoughactual SQL statements are not case sensitive, references to data in a database are.For instance, many companies store their data in uppercase. In the preceding example,assume that the column <TT>name</TT> stores its contents in uppercase. Therefore,a query searching for 'Lincoln' in the <TT>name</TT> column would not find any datato return. Check your implementation and/or company policies for any case requirements.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Commands in SQL are not case sensitive. <HR></BLOCKQUOTE><P>Take another look at the sample query. Is there something magical in the spacing?Again the answer is no. The following code would work as well:</P><PRE><FONT COLOR="#0066FF"><B>select name, startterm, endterm from presidents where name = 'LINCOLN';</B></FONT></PRE><P>However, some regard for spacing and capitalization makes your statements mucheasier to read. It also makes your statements much easier to maintain when they becomea part of your project.</P><P>Another important feature of ; (semicolon)semicolon (;)the sample query is thesemicolon at the end of the expression. This punctuation mark tells the command-lineSQL program that your query is complete.</P><P>If the magic isn't in the capitalization or the format, then just which elementsare important? The answer is keywords, or the words in SQL that are reserved as apart of syntax. (Depending on the SQL statement, a keyword can be either a mandatoryelement of the statement or optional.) The keywords in the current example are<UL> <LI><TT>SELECT</TT> <P> <LI><TT>FROM</TT> <P> <LI><TT>WHERE</TT></UL><P>Check the table of contents to see some of the SQL keywords you will learn andon what days.<H2><FONT COLOR="#000077">The Building Blocks of Data Retrieval: SELECT and FROM</FONT></H2><P>As your experience with SQL grows, you will notice that you are typing the words<TT>SELECT</TT> and <TT>FROM</TT> more than any other words in the SQL vocabulary.They aren't as glamorous as <TT>CREATE</TT> or as ruthless as <TT>DROP</TT>, butthey are indispensable to any conversation you hope to have with the computer concerningdata retrieval. And isn't data retrieval the reason that you entered mountains ofinformation into your very expensive database in the first place?</P><P>This discussion starts with <TT>SELECT</TT> because most of your statements willalso start with <TT>SELECT</TT>:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">SELECT <COLUMN NAMES></FONT></PRE><P>The commands, see also statementsbasic <TT>SELECT</TT> statement couldn't be simpler.However, <TT>SELECT</TT> does not work alone. If you typed just <TT>SELECT</TT> intoyour system, you might get the following response:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#000000"></FONT></PRE><PRE><FONT COLOR="#0066FF">SELECT *ERROR at line 1:ORA-00936: missing expression</FONT></PRE><P>The asterisk under the offending line indicates where Oracle7 thinks the offenseoccurred. The error message tells you that something is missing. That something isthe <TT>FROM</TT> clause:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">FROM <TABLE></FONT></PRE><P>Together, the statements <TT>SELECT</TT> and <TT>FROM</TT> begin to unlock thepower behind your database.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>keywordsclausesAt this point you may be wondering what the difference is between a keyword, a statement, and a clause. SQL keywords refer to individual SQL elements, such as <TT>SELECT</TT> and <TT>FROM</TT>. A clause is a part of an SQL statement; for example, <TT>SELECT </TT>column1, column2, ... is a clause. SQL clauses combine to form a complete SQL statement. For example, you can combine a <TT>SELECT</TT> clause and a <TT>FROM</TT> clause to write an SQL statement. <HR></P> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Each implementation of SQL has a unique way of indicating errors. Microsoft Query, for example, says it can't show the query, leaving you to find the problem. Borland's Interbase pops up a dialog box with the error. Personal Oracle7, the engine used in the preceding example, gives you an error number (so you can look up the detailed explanation in your manuals) and a short explanation of the problem. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077">Examples</FONT></H2><P>Before going any further, look at the sample database that is the basis for thefollowing examples. This database illustrates the basic functions of <TT>SELECT</TT>and <TT>FROM</TT>. In the real world you would use the techniques described on Day8, "Manipulating Data," to build this database, but for the purpose ofdescribing how to use <TT>SELECT</TT> and <TT>FROM</TT>, assume it already exists.This example uses the <TT>CHECKS</TT> table to retrieve information about checksthat an individual has written.</P><P>The <TT>CHECKS</TT> table:</P><PRE><FONT COLOR="#0066FF"> CHECK# PAYEE AMOUNT REMARKS--------- -------------------- ------ --------------------- 1 Ma Bell 150 Have sons next time 2 Reading R.R. 245.34 Train to Chicago 3 Ma Bell 200.32 Cellular Phone 4 Local Utilities 98 Gas 5 Joes Stale $ Dent 150 Groceries 6 Cash 25 Wild Night Out 7 Joans Gas 25.1 Gas</FONT></PRE><H2><FONT COLOR="#000077">Your First Query</FONT></H2><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL> <B>select * from checks;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">queriesCHECK# PAYEE AMOUNT REMARKS ------ -------------------- ------- ---------------------</FONT></PRE><PRE><FONT COLOR="#0066FF"> 1 Ma Bell 150 Have sons next time 2 Reading R.R. 245.34 Train to Chicago 3 Ma Bell 200.32 Cellular Phone 4 Local Utilities 98 Gas 5 Joes Stale $ Dent 150 Groceries 6 Cash 25 Wild Night Out 7 Joans Gas 25.1 Gas7 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>This output looks just like the code in the example. Notice that columns 1 and3 in the output statement are right-justified and that columns 2 and 4 are left-justified.This format follows the alignment convention in which numeric data types are right-justifiedand character data types are left-justified. Data types are discussed on Day 9, "Creatingand Maintaining Tables."</P><P>The asterisk (<TT>*</TT>) in <TT>select *</TT> tells the database to return allthe columns associated with the given table described in the <TT>FROM</TT> clause.The database determines the order in which to return the columns.<H3><FONT COLOR="#000077">Terminating an SQL Statement</FONT></H3><P>In some implementations of SQL, the semicolon at the end of the statement tellsthe interpreter that you are finished writing the query. For example, Oracle's SQL*PLUSwon't execute the query until it finds a semicolon (or a slash). On the other hand,some implementations of SQL do not use the semicolon as a terminator. For example,Microsoft Query and Borland's ISQL don't require a terminator, because your queryis typed in an edit box and executed when you push a button.<H3><FONT COLOR="#000077">Changing the Order of the Columns</FONT></H3><P>The preceding example of an SQL statement used the <TT>*</TT> to select all columnsfrom a table, the order of their appearance in the output being determined by thedatabase. To specify the order of the columns, you could type something like:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT payee, remarks, amount, check# from checks;</B></FONT></PRE><P>Notice that each column name is listed in the <TT>SELECT</TT> clause. The orderin which the columns are listed is the order in which they will appear in the output.Notice both the commas that separate the column names and the space between the finalcolumn name and the subsequent clause (in this case <TT>FROM</TT>). The output would?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -