vcg05.htm

Visual C++与数据库的连接经典实例

*|select-sublist[{, select-sublist}...]

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

select-sublist

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

table-name.*|expression [AS column-alias]|column-name

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

...

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080><BR></FONT>



<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

table-expression

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

from-clause|[where-clause]|[group-by-clause]|[having-clause]|[order-by-clause]

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

...

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080><BR></FONT>



<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

from-clause

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

FROM table-reference-list

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

table-reference-list

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

table-reference [{, table-reference}...]

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

table-reference

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

table-name [AS correlation-name|joined-table]

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

...

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080><BR></FONT>



<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

table-name

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

base-table-name|querydef-name|attached-table-name|correlation-name</FONT>

</TABLE></CENTER><BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The DISTINCTROW qualifier and the <I>querydef-name</I> element are specific to Access SQL. DISTINCTROW is discussed in the section &quot;Theta Joins and the DISTINCTROW Keyword.&quot; Chapter 6 describes the Access QueryDef object.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>After you've looked up all the allowable forms of the elements in the table, you might have forgotten the key word whose syntax you set out to determine. The modified Backus Naur form used by Microsoft is unquestionably easier to use than full BNF.

<BR>

<BR>

<A NAME="E69E121"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>The Practical Grammar of a Simple SQL <I>SELECT</I> Statement</B></FONT></CENTER></H4>

<BR>

<P>Here is a more practical representation of the syntax of a typical ANSI SQL statement, substituting underscores for hyphens:

<BR>

<PRE>

<FONT COLOR="#000080">SELECT [ALL|DISTINCT] <I>select_list</I>

   FROM <I>table_names</I>

   [WHERE {<I>search_criteria</I>|<I>join_criteria</I>}

   [{AND|OR <I>search_criteria</I>}]

   [ORDER BY {<I>field_list</I>} [ASC|DESC]]</FONT></PRE>

<P>The following list explains the use of each SQL reserved word in the preceding statement:

<BR>

<UL>

<LI>SELECT specifies that the query is to return data from the database rather than modify the data in the database. The <I>select_list</I> element contains the names of the fields of the table that are to appear in the query. Multiple fields appear in a comma-separated list. The asterisk (*) specifies that data from all fields of a table is returned. If more than one table is involved (joined) in the query, you use the <I>table_name.field_name</I> syntax, in which the period (.) separates the name of the table from the name of the field.

<BR>

<BR>

<LI>The ALL qualifier specifies that you want the query to return all rows, regardless of duplicate values; DISTINCT returns only nonduplicate rows. These qualifiers have significance only in queries that involve joins. The penalty for using DISTINCT is that the query will take longer to process.

<BR>

<BR>

<LI>FROM begins a clause that specifies the names of the tables that contain the fields you include in your <I>select_list</I>. If more than one table is involved in <I>select_list</I><I>,</I> <I>table_list</I> consists of comma-separated table names.

<BR>

<BR>

<LI>WHERE begins a clause that serves two purposes in ANSI SQL: specifying the fields on which tables are joined, and limiting the records returned to records with field values that meet a particular criterion or set of criteria. The WHERE clause must include an operator and two operands, the first of which must be a field name. (The field name doesn't need to appear in the <I>select_list</I>, but the <I>table_name</I> that includes <I>field_name</I> must be included in the <I>table_names</I> list.)

<BR>

<BR>

<LI>SQL operators include LIKE, IS {NULL|NOT NULL}, and IN, as well as the arithmetic operators&#151;&lt;, &lt;=, =, =&gt;, &gt;, and &lt;&gt;. If you use the arithmetic equal operator (=) and specify <I>table_name.field_name</I> values for both operands, you create an <I>equi-join</I> (also called an <I>inner join</I>) between the two tables on the specified fields. You can create left and right joins by using the special operators *= and =*, respectively, if your DBM supports outer joins. (Both left and right joins are called <I>outer joins</I>.) Types of joins are discussed in the section &quot;Joining Tables.&quot;

<BR>

<BR>

</UL>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>If you use more than one table in your query, make sure that you create a join between the tables with a WHERE Table1.<I>field_name</I> = Table2.<I>field_name</I> clause. If you omit the statement that creates the join, your query will return the Cartesian product of the two tables. A Cartesian product is all the combinations of fields and rows in the two tables. This results in extremely large query-return set and, if the tables have a large number of records, it can cause your computer to run out of memory to hold the query result set. (The term <I>Cartesian</I> is derived from the name of a famous French mathematician, Ren&#233; D&#233;scartes.)</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<UL>

<LI>ORDER BY defines a clause that determines the sort order of the records returned by the SELECT statement. You specify the field(s) on which you want to sort the query result set by the <I>table_names</I> list. You can specify a descending sort with the DESC qualifier; ascending (ASC) is the default. As in other lists, if you have more than one <I>table_name</I>, you use a comma-separated list. You use the <I>table_name.field_name</I> specifier if you have joined tables.

<BR>

<BR>

</UL>

<P>Depending on the dialect of SQL your database uses and the method of transmitting the SQL statement to the DBM, you might need to terminate the SQL statement with a semicolon. (Access SQL no longer requires the semicolon; statements you send directly to the server through the ODBC driver don't use terminating semicolons.)

<BR>

<BR>

<A NAME="E69E122"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Using the MS Query Application to Explore Queries</B></FONT></CENTER></H4>

<BR>

<P>The MS Query application that accompanies Visual C++ version 1.5 (\MSVC15\MSQUERY) is an excellent application that can be used to create SQL statements. Visual C++ 2.x and 4 don't include MS Query; however, because Visual C++ 1.5 is included with later versions of Visual C++, you can install that version from Visual C++ 1.5. Also, when you purchase Microsoft Office, you will receive a 32-bit version of Microsoft Query. It can be found on the Microsoft Office Pro CD in the \OS\MSAPPS\MSQUERY folder.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>BIBLIO.MDB is included on the CD that comes with this book in the CHAPTR05 folder. Visual Basic users will have an Access database called BIBLIO, which is included with Visual Basic. Visual C++ users don't have this sample database. If you have Visual Basic, you can use the copy of BIBLIO included with Visual Basic or the copy included on the CD in the CHAPTR05 folder.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>Query as found on the Visual C++ 1.5x CD (a 16-bit application) works only with the Access 2 version of BIBLIO. It might not work correctly with the second version, called BIBLIO 95, which is an Access 7 version of the database. MSQRY32 (the 32-bit version of MS Query, which is on the Microsoft Office CD) will work with the Access 7 version of BIBLIO. The 32-bit version of MS Query is a bit more reliable and should be used if possible.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>MS Query falls into the category of <I>ad hoc</I> query generators. You can use MS Query to test some simple SQL statements by following these steps:

<BR>

<OL>

<LI>Start MS Query and choose File | New Query.

<BR>

<BR>

<LI>MS Query displays the Select Data Source dialog box. Select the BIBLIO datasource. If you haven't previously opened BIBLIO using MS Query, click the Other button to add BIBLIO to MS Query's list of datasources.

<BR>

<BR>