vcg05.htm

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

<BR>Where Access SQL uses syntax that isn't specified by ANSI SQL, such as the use of number signs (#) to indicate date-time field data types, or where examples of complete statements are given in Access SQL, the SQL reserved words that are also keywords or reserved words appear in the upper-and-lowercase convention.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E70E36"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Calculated Query Columns</B></FONT></CENTER></H5>

<BR>

<P>Using Access, you can create calculated columns in query return sets by defining fields that use SQL arithmetic operators and functions that are supported by the Access database engine or your client-server RDBMS. Ordinarily, calculated columns are derived from fields of numeric field data types. BIBLIO.MDB uses a numeric data type (the auto-incrementing long integer Counter field) for ID fields, so you can use the PubID field or Val(Zip) expression as the basis for the calculated field. Enter SELECT DISTINCTROW Publishers.Name, Val([Zip])*3 AS Zip_Times_3, Publishers.State FROM Publishers in Access's SQL query window. The query result set appears as shown in Figure 5.6.

<BR>

<P><B><A HREF="05vcg06.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/05vcg06.gif">Figure 5.6. A calculated column added to the query against the publisher's table.</A></B>

<BR>

<P>The AS qualifier designates an alias for the column name, <I>column_alias</I>. If you don't supply the AS <I>column_alias</I> qualifier, the column name is empty when you use the Access database engine. Access provides a default AS Expr1 column alias for calculated columns; the <I>column_alias</I> that appears when you use ODBC to connect to databases is implementation-specific. IBM's DB2 and DB2/2, for example, don't support aliasing of column names with the AS qualifier. ODBC drivers for DB2 and DB2/2 may assign the field name from which the calculated column value is derived, or apply an arbitrary name, such as Col_1.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>If you must include spaces in the <I>column_alias</I>, make sure that you enclose the <I>column_alias</I> in square brackets for the Access database engine and in single quotation marks for RDBMSs that support spaces in <I>column_alias</I> fields. (Although you might see column names such as Col 1 when you execute queries against DB2 or other mainframe databases in an emulated 3270 terminal session, these <I>column_alias</I> values are generated by the local query tool running on your PC, not by DB2.) If you use single or double quotation marks with the Access database engine, these quotation marks appear in the column headers.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E70E37"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Monadic Text Operators, Null Value Predicates, and Functions</B></FONT></CENTER></H5>

<BR>

<P>One of the most useful operators for the WHERE criterion of fields of the text field data type is ANSI SQL's LIKE predicate, called the Like operator in Access SQL. (The terms <I>predicate</I> and <I>operator</I> are used interchangeably in this context.) The LIKE predicate lets you search for one or more characters you specify at any location in the text. Table 5.2 shows the syntax of the ANSI SQL LIKE predicate and the Access SQL Like operator used in the WHERE clause of an SQL statement.

<BR>

<BR>

<P ALIGN=CENTER>

<CENTER>

<FONT COLOR="#000080"><B>Table 5.2. Forms of the ANSI SQL LIKE and Access SQL Like predicates.</B></FONT></CENTER>

<BR>



<CENTER><TABLE  BORDERCOLOR=#000040 BORDER=1 CELLSPACING=2 CELLPADDING=3 >

<TR>

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

<I>ANSI SQL</I>

</FONT>

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

<I>Access SQL</I>

</FONT>

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

<I>Description</I>

</FONT>

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

<I>What It Returns</I>

</FONT>

<TR>

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

LIKE '%am%'

</FONT>

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

Like &quot;*am*&quot;

</FONT>

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

Matches any text that contains the characters.

</FONT>

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

ram, rams, damsel, amnesty

</FONT>

<TR>

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

LIKE 'John%'

</FONT>

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

Like &quot;John*&quot;

</FONT>

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

beginning with the characters.

</FONT>

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

Johnson, Johnsson

</FONT>

<TR>

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

LIKE '%son'

</FONT>

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

Like &quot;*son&quot;

</FONT>

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

ending with the characters.

</FONT>

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

Johnson, Anderson

</FONT>

<TR>

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

LIKE 'Glen_'

</FONT>

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

Like &quot;Glen?&quot;

</FONT>

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

Matches the text and any single trailing character.

</FONT>

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

Glenn, Glens

</FONT>

<TR>

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

LIKE '_am'

</FONT>

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

Like &quot;?am&quot;

</FONT>

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

Matches the text and any single preceding character.

</FONT>

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

dam, Pam, ram

</FONT>

<TR>

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

LIKE '_am%'

</FONT>

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

Like &quot;_am*&quot;

</FONT>

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

with one preceding character and any trailing characters.

</FONT>

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

dams, Pam, Ramses</FONT>

</TABLE></CENTER><P>The IS NULL and IS NOT NULL predicates (Is Null and Is Not Null operators in Access SQL) test whether a value has been entered in a field. IS NULL returns False and IS NOT NULL returns True if a value, including an empty string &quot;&quot; or 0, is present in the field.

<BR>

<P>The SQL-92 POSITION() function returns the position of characters in a test field using the syntax POSITION(<I>characters</I> IN <I>field_name</I>). The equivalent Access SQL function is InStr(<I>field_name</I>, <I>characters</I>). If <I>characters</I> are not found in <I>field_name</I>, both functions return 0.

<BR>

<P>The SQL-92 SUBSTRING() function returns a set of characters with SUBSTRING(<I>field_name</I> FROM <I>start_position</I> FOR <I>number_of_characters</I>). This function is quite useful for selecting and parsing text fields.

<BR>

<BR>

<A NAME="E69E124"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Joining Tables</B></FONT></CENTER></H4>

<BR>

<P>As I mentioned earlier in this chapter, you can join two tables by using <I>table_name.field_name</I> operands with a comparison operator in the WHERE clause of an SQL statement. You can join additional tables by combining two sets of join statements with the AND operator. SQL-86 and SQL-89 supported only WHERE joins. You can create equi-joins, natural equi-joins, left and right equi-joins, not-equal joins, and self-joins with the WHERE clause. Joins that are created with the equals (=) operator use the prefix <I>equi</I>.

<BR>

<P>SQL-92 added the JOIN reserved words, plus the CROSS, NATURAL, INNER, OUTER, FULL, LEFT, and RIGHT qualifiers, to describe a variety of JOINs. At the time this book was written, few RDBMSs supported the JOIN statement. (Microsoft SQL Server 4.2, for example, doesn't include the JOIN statement in Transact-SQL.) Access SQL supports INNER, LEFT, and RIGHT JOINs with SQL-92 syntax using the ON predicate. Access SQL doesn't support the USING clause or the CROSS, NATURAL, or FULL qualifiers for JOINs.

<BR>

<P>A CROSS JOIN returns the Cartesian product of two tables. The term CROSS is derived from <I>cross-product,</I> a synonym for Cartesian product. You can emulate a CROSS JOIN by leaving out the join components of the WHERE clause of a SELECT statement that includes a table name from more than one table. Figure 5.7 shows Access 95 displaying the first few rows of the 29-row Cartesian product created when you enter SELECT Publishers.Name, Authors.Author FROM Publishers, Authors in the SQL Statement window. There are seven Publishers records and 42 Authors records; thus, the query returns 294 rows (7 * 42 = 294). It is highly unlikely that you would want to create a CROSS JOIN in a commercial database application.

<BR>

<P><B><A HREF="05vcg07.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/05vcg07.gif">Figure 5.7. The first few rows of the 29-row Cartesian product from the Publishers and Authors table.</A></B>

<BR>

<P>The common types of joins that you can create with SQL-89 and Access SQL are described in the following sections.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>All joins except the CROSS JOIN or Cartesian product require that the field data types of the two f