vcg08.htm

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

<A NAME="E69E157"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Creating a Classification Crosstab Query</B></FONT></CENTER></H4>

<BR>

<P>With a few simple changes and additions to the preceding query, you can create a crosstab query in classification format to compare sales of product categories by employee during a specific period of time. The following SQL statement adds the Employees table to the query and displays the last name of each employee of Northwind Traders as column headings, with sales by category for 1991 as the data cell values:

<BR>

<PRE>

<FONT COLOR="#000080">TRANSFORM Format(Sum([Order Details].[Unit Price] *

    [Order Details].[Quantity]), &quot;$#,##0&quot;) AS Sales

    SELECT Categories.[Category Name]

    FROM Categories, [Order Details], Orders, Products, Employees

    WHERE Products.[Category ID] = Categories.[Category ID]

    AND [Order Details].[Order ID] = Orders.[Order ID]

    AND [Order Details].[Product ID] = Products.[Product ID]

    AND Orders.[Employee ID] = Employees.[Employee ID]

    AND Orders.[Order Date] Between #01/1/91# And #12/31/91#

    GROUP BY Categories.[Category Name]

    ORDER BY Categories.[Category Name]

    PIVOT Employees.[Last Name];</FONT></PRE>

<P>Figure 8.8 shows the crosstab query result set returned by the preceding SQL statement that is executed in Microsoft Access.

<BR>

<P><B><A HREF="08vcg08.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/08vcg08.gif">Figure 8.8. A classification crosstab query executed in Access.</A></B>

<BR>

<BR>

<A NAME="E68E51"></A>

<H3 ALIGN=CENTER>

<CENTER>

<FONT SIZE=5 COLOR="#FF0000"><B>Defining Action Queries</B></FONT></CENTER></H3>

<BR>

<P>Action queries provide a method of creating or modifying persistent database objects without the necessity of writing low-level Visual C++ code to manipulate table record pointers. Action queries are especially effective when you want to add, delete, or modify many records in a database with a single operation. (An operation that makes changes to multiple sets of records is called a <I>bulk update</I>.) The following list briefly describes the four types of action queries:

<BR>

<UL>

<LI>Append queries use the INSERT INTO <I>table_name</I> predicate followed by a conventional SELECT statement to specify the fields and criteria used to determine the data to be appended to <I>table name</I>.

<BR>

<BR>

<LI>Delete queries use the DELETE FROM <I>table_name</I> WHERE <I>criteria</I> syntax to delete records whose data meets the <I>criteria</I>.

<BR>

<BR>

<LI>Update queries use the UPDATE <I>table_name</I> SET <I>field_name</I> = <I>expression</I> statement with a WHERE clause to establish which records are updated.

<BR>

<BR>

<LI>Make-table queries use SELECT <I>field_names</I> INTO <I>dest_table</I> FROM <I>source_table</I> statements to create a new table, <I>dest_table</I>, with fields specified by the <I>field_names</I> list.

<BR>

<BR>

</UL>

<BR>

<A NAME="E68E52"></A>

<H3 ALIGN=CENTER>

<CENTER>

<FONT SIZE=5 COLOR="#FF0000"><B>Using the SQL Action Queries</B></FONT></CENTER></H3>

<BR>

<P>The following sections give examples of the use of each of the four types of action queries that Access SQL supports. All of the following examples use the CROSSTAB 95.MDB database opened in Microsoft Access.

<BR>

<BR>

<A NAME="E69E158"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Creating New Tables with Make-Table Queries</B></FONT></CENTER></H4>

<BR>

<P>To avoid modifying the existing tables in the CROSSTAB 95.MDB database, the first action query example creates a new table from the Orders table, tblOrders, that includes all orders that have order dates earlier than July 1, 1991. To test make-table action query syntax, enter the following in Access's Query SQL Statement window:

<BR>

<PRE>

<FONT COLOR="#000080">SELECT *, [Order Date]

    INTO tblOrders

    FROM Orders

    WHERE [Order Date]&lt;#07/1/91#</FONT></PRE>

<P>You need to include the [Order Date] entry in the SELECT list because Access SQL doesn't let you specify a WHERE criterion on a field that isn't explicitly declared in the SELECT list.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The NewOrders table doesn't appear in the Tables tab of Microsoft Access until you execute the query after saving it. After the query is executed (Access will warn you that a new table will be created), you can view the new table.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>Use a make-table query to create a backup of a table before you test other SQL action statements on the table. If your append, update, or delete query gives an unexpected result, you can start fresh by deleting all the records in the original table and appending all the records from the backup table.

<BR>

<P>You can add an IN clause to your make-table query to create a table in another database. Here's the full syntax of an SQL make-table clause:

<BR>

<PRE>

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

    INTO dest_table [IN <I>database_name</I>[ <I>connect_string</I>]]

    FROM <I>source_table</I>

    WHERE <I>criteria</I></FONT></PRE>

<P>Thus, you can create a tblOrders table in the C:\ACCESS\SAMPAPPS\NorthWind.MDB database (if you have the Northwind Traders database installed) with the following statement:

<BR>

<PRE>

<FONT COLOR="#000080">SELECT *, [Order Date]

    INTO tblOrders IN &quot;c:\access\nwind.mdb&quot;

    FROM Orders

    WHERE [Order Date]&lt;#07/1/91#</FONT></PRE>

<BR>

<A NAME="E69E159"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Appending Records to Existing Tables</B></FONT></CENTER></H4>

<BR>

<P>You can append the balance of the records for the year 1991 to the tblOrders table with the following append query statement:

<BR>

<PRE>

<FONT COLOR="#000080">INSERT INTO tblOrders ([Order ID], [Customer ID], [Employee ID],

    [Order Date], [Shipped Date], [Order Amount])

    SELECT [Order ID], [Customer ID], [Employee ID], [Order Date],

    [Shipped Date], [Order Amount]

    FROM Orders

    WHERE [Order Date] Between #07/1/91# And #12/31/91#</FONT></PRE>

<P>You need to explicitly declare the field list of both the destination table (tblOrders) and the source table (Orders) if you use a WHERE criterion. The field list of the destination table must be enclosed in parentheses. If you attempt to use the asterisk (*) to add all fields and then add the [Order Date] field to the SELECT statement, as in the make-table example, you receive a syntax error message.

<BR>

<BR>

<A NAME="E69E160"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Updating Data Values in Tables</B></FONT></CENTER></H4>

<BR>

<P>To change values in data fields, you use the UPDATE predicate. The SET clause specifies the expression used to update one or more fields. The following update action query SQL statement reduces the dollar amount of each order received in April 1991 to 90 percent of its current value:

<BR>

<PRE>

<FONT COLOR="#000080">UPDATE tblOrders

    SET [Order Amount] = 0.9*[Order Amount]

    WHERE [Order Date] BETWEEN #4/1/91# AND #4/30/91#;</FONT></PRE>

<P>If you want to update the values of more than one field, add the field name, the equals sign (=), and an expression separated from the preceding SET expression by a comma (,).

<BR>

<BR>

<A NAME="E69E161"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Deleting Records from Tables</B></FONT></CENTER></H4>

<BR>

<P>The simplest and most potentially destructive of the action queries is the delete query. If you execute a delete query and forget to add a WHERE criterion, all the records in your table can disappear in an instant. To delete the records for the last half of 1991 from the tblOrders table, use the following statement:

<BR>

<PRE>

<FONT COLOR="#000080">DELETE FROM tblOrders

    WHERE [Order Date] BETWEEN #7/1/91# AND #12/31/91#;</FONT></PRE>

<P>You now can safely verify that the DELETE FROM tblOrders statement without the WHERE clause does indeed delete all the records in the table.

<BR>

<BR>

<A NAME="E68E53"></A>

<H3 ALIGN=CENTER>

<CENTER>

<FONT SIZE=5 COLOR="#FF0000"><B>Summary</B></FONT></CENTER></H3>

<BR>

<P>This chapter demonstrated how to use the Access SQL TRANSFORM and PIVOT keywords to create crosstab queries and showed the syntax necessary to implement action queries with SQL statements. Crosstab queries are one of the most commonly used forms of SELECT queries in Visual C++ decision-support applications. The next chapter makes more extensive use of crosstab queries. Using action queries to create and update tables often can save you from writing a substantial amount of Visual C++ code.

<BR>

<P>This chapter ends Part II of this book. At this point, you have the background you need in database design and the use of both ANSI and Access SQL to develop meaningful database applications. Part III shows you how to design applications to display and print the information that you extract from databases with SQL SELECT queries.

<P ALIGN=CENTER>

<A HREF="vcg07.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg07.htm" TARGET="_self"><IMG SRC="blanprev.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blanprev.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Previous Page"></A>

<A HREF="#I0" TARGET="_self"><IMG SRC="blantop.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blantop.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Page Top"></A>

<A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/index.htm" TARGET="_self"><IMG SRC="blantoc.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blantoc.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="TOC"></A>

<A HREF="vcgp3.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcgp3.htm" TARGET="_self"><IMG SRC="blannext.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blannext.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Next Page"></A>


</BODY></HTML>