index.html

《Big C++ 》Third Edition电子书和代码全集-Part1

<ul>
	<li>E.g., find all cities that received a hair dryer:
		<ul>
			<li>Use Product table to match 'Hair Dryer' to items</li>
			<li>Use Item table to find invoices</li>
			<li>Use Invoice table to find customers</li>
			<li>Pull info from Customer table</li>
		</ul>
		<blockquote>
<pre>SELECT Customer.City, Customer.State, Customer.Zip
   FROM Product, Item, Invoice, Customer
   WHERE Product.Description = 'Hair dryer'
      AND Product.Product_Code = Item.Product_Code
      AND Item.Invoice_Number = Invoice.Invoice_Number
      AND Invoice.Customer_Number
         = Customer.Customer_Number</pre>
		</blockquote>
	</li>
	<table border='1' cellpadding='4'>
		<tr><td align='center'>Anytown</td>
			<td align='center'>CA</td>
		</tr>
	</table>
</ul>

</font>

<hr><h2><font color="#009999">28.2.5 Joins (cont.)</font></h2>
<font size="+1">

<ul>
	<li>Remember:
		<ul>
			<li>List appropriate tables in the <tt>FROM</tt> clause</li>
			<li>Use the TableName.ColumnName syntax</li>
			<li>List all join conditions (TableName1.ColumnName1 =
				TableName2.ColumnName2) in the <tt>WHERE</tt> clause</li>
		</ul>
	</li>
	<li>Queries can be complex</li>
	<li>SQL doesn't specify <i>how</i> to search</li>
	<li>DBMSs are very good at queries</li>
</ul>

</font>

<hr><h2><font color="#009999">28.2.6 Updating and Deleting Data</font></h2>
<font size="+1">

<ul>
	<li><tt>SELECT</tt>s produce a <i>result set</i>
		<ul>
			<li>Do <b>not</b> modify the DB</li>
		</ul>
	</li>
	<li><tt>DELETE</tt> and <tt>UPDATE</tt> modify DB
		<ul>
			<li>Return # of rows affected</li>
		</ul>
	<li><tt>DELETE</tt> removes specified row(s):
		<blockquote><tt>
			DELETE FROM Customer WHERE State = 'CA'
		</tt></blockquote>
	</li>
</ul>

</font>

<hr><h2><font color="#009999">28.2.6 Updating and Deleting Data</font></h2>
<font size="+1">

<ul>
	<li><tt>UPDATE</tt> modifies fields in record(s)
		<blockquote>
<pre>UPDATE Item
   SET Quantity = Quantity + 1
   WHERE Invoice_Number = '11731'</pre>
		</blockquote>
	</li>
	<li>Update multiple fields by putting multiple expressions separated by
		commas in the <tt>SET</tt> clause</li>
</ul>

</font>

<hr><h2><font color="#009999">28.3 Installing a Database</font></h2>
<font size="+1">

<ul>
	<li>MySQL will be used for the installation and programming examples
		<ul>
			<li>Open-source</li>
			<li>Available on many platforms</li>
			<li>Widely-used</li>
		</ul>
	</li>
	<li>SQL fairly standard</li>
	<li>DB APIs are not</li>
</ul>

</font>

<hr><h2><font color="#009999">28.3 Installing a Database (cont.)</font></h2>
<font size="+1">

<p>General sequence to install and test installation:<p>

<script><!-- 
	iframeWrapCode( "dbInstall.txt", "95%", "75%" )
//--></script>

</font>

<hr><h2><font color="#009999">28.3 Installing a Database (cont.)</font></h2>
<font size="+1">

<p>To connect from C++ (specific to MySQL):</p>

<script><!-- 
	iframeWrapCode( "dbConnect.txt", "95%", "75%" )
//--></script>

</font>

<hr><h2><font color="#009999">28.4 Database Programming in C++</font></h2>
<h2><font color="#009999">28.4.1 Connecting to the MySQL Database</font></h2>
<font size="+1">

<ul>
	<li>MySQL API
		<ul>
			<li>C/C++ compatible</li>
			<li>No classes</li>
			<li>Convert between <tt>string</tt> and <tt>char[]</tt> as needed</li>
			<li>Allows for connections, queries, updates, etc.</li>
		</ul>
	</li>
	<li>DB programs run continuously</li>
	<li>Wait for a client to connect</li>
</ul>

</font>

<hr><h2><font color="#009999">28.4.1 Connecting to the MySQL Database
	</font></h2>
<font size="+1">

<ul>
	<li>Get a connection <i>handle</i>:
		<blockquote><tt>
			MYSQL* connection = mysql_init();
		</tt></blockquote>
	</li>
	<li>Make connection using handle:
		<blockquote><tt>
			mysql_real_connect(connection, NULL, NULL, NULL,<br>
			&nbsp;&nbsp;&nbsp;"bigcpp", 0, NULL, 0)
		</tt></blockquote>

		<p>Arguments are:
		<ul>
			<li>A ptr to an initialised connection</li>
			<li>Three strings: hostname, username, password.  <tt>NULL</tt> to
				use defaults</li>
			<li>DB name (<tt>bigcpp</tt>)</li>
			<li>Last three should be the defaults <tt>(0, NULL, 0)</tt></li>
		</ul>
	</li>
</ul>

</font>

<hr><h2><font color="#009999">28.4.1 Connecting to the MySQL Database
	</font></h2>
<font size="+1">

<ul>
	<li><tt>mysql_real_connect</tt> returns <tt>NULL</tt> if it fails</li>
	<li><b>Always</b> check:
		<blockquote>
<pre>if (mysql_real_connect(...) == NULL)
{
   string error_message = mysql_error(connection);
   . . .
}</pre>
		</blockquote>
	</li>
	<li>When done, free resources in your program <b>and in the DB</b>:
		<blockquote><tt>
			mysql_close(connection);
		</tt></blockquote>
	</li>
</ul>

</font>

<hr><h2><font color="#009999">28.4.2 Executing SQL Statements</font></h2>
<font size="+1">

<ul>
	<li>Use <tt>mysql_query</tt> to issue commands (even updates)</li>
	<li>Assemble command in a <tt>string</tt> object</li>
	<li>Then use <tt>c_str</tt>:
		<blockquote><tt>
			string new_value = ...;<br>
			string query = "INSERT INTO Test VALUES ('" + new_value + "')";<br>
			mysql_query(query.c_str());
		</tt></blockquote>
	</li>
</ul>

</font>

<hr><h2><font color="#009999">28.4.2 Executing SQL Statements (cont.)
	</font></h2>
<font size="+1">

<ul>
	<li>Use <tt>int_to_string</tt> and <tt>double_to_string</tt> (chptr 12)
		to place numbers:
		<blockquote><tt>
			int qty = ...;<br>
			string command = "SELECT * FROM Item WHERE Quantity &gt;= "<br>
			&nbsp;&nbsp;&nbsp;+ int_to_string(qty);<br>
			mysql_query(connection, command.c_str());
		</tt></blockquote>
	</li>
	<li><tt>mysql_query</tt> returns non-zero error code for invalid command</li>
</ul>

</font>

<hr><h2><font color="#009999">28.4.2 Executing SQL Statements (cont.)
	</font></h2>
<font size="+1">

<ul>
	<li>Always check:
		<blockquote>
<pre>if (mysql_query(connection, command.c_str()) != 0)
{
   cout &lt;&lt; "Error: " &lt;&lt; mysql_error(connection) &lt;&lt; "\n";
   ...
}</pre>
		</blockquote>
	</li>
	<li>Do not use results if query failed</li>
</ul>

</font>

<hr><h2><font color="#009999">28.4.3 Analyzing Query Results</font></h2>
<font size="+1">

<ul>
	<li>Result of a <tt>SELECT</tt> command stored in a <tt>MYSQL_RES</tt>
 		object</li>
	<li>Call <tt>mysql_store_result</tt> to allocate and fill result object:
		<blockquote><tt>
			MYSQL_RES* result = mysql_store_result(connection);
		</tt></blockquote>
	</li>
	<li>Returns <tt>NULL</tt> if preceding command was <b>not</b>
		<tt>SELECT</tt></li>
</ul>

</font>

<hr><h2><font color="#009999">28.4.3 Analyzing Query Results (cont.)</font></h2>
<font size="+1">

<ul>
	<li>To get the # of rows and columns:
		<blockquote><tt>
			int rows = mysql_num_rows(result);<br>
			int fields = mysql_num_fields(result);
		</tt></blockquote>
	</li>
	<li><tt>rows == 0</tt> means query yielded no records</li>
	<li>To inspect, fetch one row at a time:
		<blockquote>
<pre>for (int r = 1; r &lt;= rows; r++)
{
   MYSQL_ROW row = mysql_fetch_row(result);
   <font color="#0000cc">// inspect field data from the current row</font>
}</pre>
		</blockquote>
	</li>
</ul>

</font>

<hr><h2><font color="#009999">28.4.3 Analyzing Query Results (cont.)</font></h2>
<font size="+1">

<ul>
	<li><tt>MYSQL_ROW</tt> is an array of C-style strings</li>
	<li>Convert to <tt>strings</tt> immediately</li>
	<li>To obtain the <tt>i</tt><sup>th</sup> field of a row:
		<blockquote><tt>
			string field = row[i];
		</tt></blockquote>
	</li>
</ul>

</font>

<hr><h2><font color="#009999">28.4.3 Analyzing Query Results (cont.)</font></h2>
<font size="+1">

<ul>
	<li>Even numeric fields returned in a string</li>
	<li>Use <tt>string_to_int</tt> or <tt>string_to_double</tt></li>
	<li>E.g., given
		<blockquote><tt>
			SELECT Unit_Price, ... FROM Product WHERE ...
		</tt></blockquote>

		<p>To retrieve a record's price:
		<blockquote><tt>
			double price = string_to_double(row[0]);
		</tt></blockquote>
	</li>
</ul>

</font>

<hr><h2><font color="#009999">28.4.3 Analyzing Query Results (cont.)</font></h2>
<font size="+1">

<ul>
	<li>Close each row (free resources) when done:
		<blockquote><tt>
			mysql_free_result(result);
		</tt></blockquote>
	</li>
	<li>Avoid loading very large result sets into memory at once</li>
</ul>

</font>

<hr><h2><font color="#009999">28.4.3 Analyzing Query Results (cont.)</font></h2>
<font size="+1">

<ul>
	<li><tt>execsql</tt> program demonstrates these concepts</li>
	<li>Executes SQL commands stored in a text file (e.g., <tt>product.sql</tt>)
		<blockquote><tt>
			execsql &lt; product.sql
		</tt></blockquote>
	</li>
	<li>Prints out results of a <tt>SELECT</tt> query</li>
	<li>To run interactively:
		<blockquote><tt>
			execsql
		</tt></blockquote>
		<ul>
			<li>Enter key executes a query</li>
			<li>End-of-file (ctrl-D or ctrl-Z to quit)</li>
		</ul>
	</li>
</ul>

</font>

<hr><h2><font color="#009999">28.4.3 Analyzing Query Results
	(<tt>execsql.cpp</tt>)</font></h2>
<font size="+1">

<script><!-- 
	iframeWrapCode( "execsql.cpp", "95%", "75%" )
//--></script>

</font>

<hr><h2><font color="#009999">28.4.3 Analyzing Query Results
	(<tt>product.sql</tt>)</font></h2>
<font size="+1">

<script><!-- 
	iframeWrapCode( "product.sql", "95%", "65%" )
//--></script>

</font>

<hr><h2><font color="#009999">28.5 Case Study: Accessing an Invoice Database
	</font></h2>
<font size="+1">

<ul>
	<li>2 programs:
		<ul>
			<li><tt>printinv.cpp</tt> gets an invoice # from the user, prints
				the invoice</li>
			<li><tt>addinv.cpp</tt> adds new invoices to DB</li>
		</ul>
	</li>
	<li><a target='bigc' href='Code/sutil.h'>sutil.h</a>,
	<a target='bigc' href='Code/sutil.cpp'>sutil.cpp</a> - for conversions</li>
</ul>

</font>

<hr><h2><font color="#009999">28.5 Case Study: Accessing an Invoice Database
	(cont.)</font></h2>
<font size="+1">

<p><b><tt>printinv.cpp</tt></b>

<ul>
	<li>Construct query for cust# and payment:
		<blockquote><tt>
			string query = "SELECT Customer_Number, Payment FROM Invoice "<br>
			&nbsp;&nbsp;&nbsp;"WHERE Invoice_Number = '" + invnum + "'";
		</tt></blockquote>
		<p>(C++ concatenates adj. string literals)</p>
	</li>
</ul>

</font>

<hr><h2><font color="#009999">28.5 Case Study: Accessing an Invoice Database
	(cont.)</font></h2>
<font size="+1">

<ul>
	<li>If found, fetch first row, extract fields:
		<blockquote><tt>
			MYSQL_ROW row = mysql_fetch_row(result);<br>
			string custnum = row[0];<br>
			double payment = string_to_double(row[1]);
		</tt></blockquote>
	</li>
	<li>Avoid <tt>SELECT *</tt>, in case table changes later</li>
</ul>

</font>

<hr><h2><font color="#009999">28.5 Case Study: Accessing an Invoice Database
	(cont.)</font></h2>
<font size="+1">

<p><b><tt>printinv.cpp</tt></b>

<ul>
	<li>Query and print customer data:
		<blockquote><tt>
			string query = "SELECT Item.Quantity, Product.Description, "<br>
			&nbsp;&nbsp;&nbsp;"Product.Unit_Price FROM Item, Product WHERE "<br>
			&nbsp;&nbsp;&nbsp;"Item.Invoice_Number = ' "+ invnum<br>
			&nbsp;&nbsp;&nbsp;+ "'AND Item.Product_Code = Product.Product_Code";
		</tt></blockquote>
	</li>
</ul>

</font>

<hr><h2><font color="#009999">28.5 Case Study: Accessing an Invoice Database
	(cont.)</font></h2>
<font size="+1">

<ul>
	<li>Compute amt due:
		<blockquote><tt>
			string query = "SELECT SUM(Item.Quantity * Product.Unit_Price) "<br>
			&nbsp;&nbsp;&nbsp;"FROM Item, Product WHERE Item.Invoice_Number = '"<br>
			&nbsp;&nbsp;&nbsp;+ invnum + "' AND Item.Product_Code
				= Product.Product_Code";
		</tt></blockquote>
		<p>Yields a single row w/a single field</p>
	</li>
	<li>Extract value, convert:
		<blockquote><tt>
			MYSQL_ROW row = mysql_fetch_row(result);
			double amount_due = string_to_double(row[0]);
		</tt></blockquote>
	</li>
</ul>

</font>

<hr><h2><font color="#009999">28.5 Case Study: Accessing an Invoice Database
	(cont.)</font></h2>
<font size="+1">

<p><b><tt>addinv.cpp</tt></b>

<ul>
	<li>Prompt for cust# and payment</li>
	<li>Assign unique invoice #:
		<ul>
			<li>Find max of existing invoice #s:
				<blockquote><tt>
					SELECT MAX(Invoice_Number) FROM Invoice
				</tt></blockquote>
			</li>
			<li>Add 1</li>
		</ul>
	</li>
	<li>Insert new row into DB:
		<blockquote><tt>
			string command = "INSERT INTO Item VALUES ('" + invnum +<br>
			&nbsp;&nbsp;&nbsp;"', '" + prodcode + "', "
				+ int_to_string(quantity) + ")";
		</tt></blockquote>
	</li>
</ul>

</font>

<hr><h2><font color="#009999">28.5 Case Study (printinv.cpp)</font></h2>
<font size="+1">

<script><!-- 
	iframeWrapCode( "printinv.cpp", "95%", "75%" )
//--></script>

</font>

<hr><h2><font color="#009999">28.5 Case Study (addinv.cpp)</font></h2>
<font size="+1">

<script><!-- 
	iframeWrapCode( "addinv.cpp", "95%", "75%" )
//--></script>

</font>

<hr><h2><font color="#009999">Advanced Topic 23.1</font></h2>
<font size="+1">
<hr color="#00ffff" size="6">

<p><font color="#009999">Transactions</font></p>

<ul>
	<li><i>Transaction handling</i> important DB task</li>
	<li>Transaction - set of operations that should all succeed, or fail
		entirely</li>
	<li>Use SQL <tt>COMMIT</tt> and <tt>ROLLBACK</tt> commands</li>
	<li><tt>COMMIT</tt> makes a set of updates permanent</li>
	<li><tt>ROLLBACK</tt> undoes all changes from last <tt>COMMIT</tt></li>
	<li>Make creation of unique values (e.g., invoice #) part of the
		transaction</li>
</ul>

</font>

<hr><h2><font color="#009999">Chapter Summary</font></h2>
<font size="+1">
<hr color="#00ffff" size="6">

<ol>
	<li>RDB stores information in tables.  Each column has a datatype and unique
		name</li>
	<li>SQL - command language for interacting w/the DB</li>
	<li><tt>CREATE TABLE</tt> and <tt>INSERT</tt> commands to add to a DB.
		<tt>SELECT</tt> to query.  <tt>UPDATE</tt> and <tt>DELETE</tt> to modify
		the data</li>
	<li>Do <b>not</b> replicate data among rows.  Distribute over multiple
		tables</li>
	<li>Primary key - column(s) that uniquely identify a record<br>
		Foreign key - reference to primary key in linked table</li>
	<li>A <i>join</i> is a query that involves multiple tables</li>
	<li>Use an API to access a DB from a C++ program</li>
	<li>Transaction - set of updates that should succeed entirely, or not at
		all</li>
</ol>

</font>

</body>
</html>