📄 index.html
字号:
<td align='center'><tt>DECIMAL(10,2)</tt></td>
</tr>
<tr>
<td align='center'>11731</td>
<td align='center'>3175</td>
<td align='center'>116-064</td>
<td align='center'>3</td>
<td align='center'>257-535</td>
<td align='center'>1</td>
<td align='center'>643-119</td>
<td align='center'>2</td>
<td align='center'>0</td>
</tr>
</table>
</font>
<hr><h2><font color="#009999">28.1.3 Implementing One-to-Many Relationships
(cont.)</font></h2>
<font size="+1">
<table cellpadding='12'>
<tr>
<td><font size='+1'>
<ul>
<li>Break into 2 tables</li>
<li>Link items to an invoice w/<tt>Invoice_Number</tt></li>
</ul>
</td>
<td rowspan='2'>
<table border='1' cellpadding='4'>
<tr><th colspan='3'>Item</th></tr>
<tr bgcolor='#00ffff'>
<th>Invoice_<br>Number</th>
<th>Product_<br>Code</th>
<th>Quantity</th>
</tr>
<tr>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>CHAR(10)</tt></td>
<td align='center'><tt>INTEGER</tt></td>
</tr>
<tr>
<td align='center'>11731</td>
<td align='center'>116-064</td>
<td align='center'>3</td>
</tr>
<tr>
<td align='center'>11731</td>
<td align='center'>257-535</td>
<td align='center'>1</td>
</tr>
<tr>
<td align='center'>11731</td>
<td align='center'>643-119</td>
<td align='center'>2</td>
</tr>
<tr>
<td align='center'>11732</td>
<td align='center'>116-064</td>
<td align='center'>10</td>
</tr>
<tr>
<td align='center'>11733</td>
<td align='center'>116-064</td>
<td align='center'>2</td>
</tr>
<tr>
<td align='center'>11733</td>
<td align='center'>643-119</td>
<td align='center'>1</td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<table border='1' cellpadding='4'>
<tr><th colspan='3'>Invoice</th></tr>
<tr bgcolor='#00ffff'>
<th>Invoice_<br>Number</th>
<th>Customer_<br>Number</th>
<th>Payment</th>
</tr>
<tr>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>DECIMAL(10,2)</tt></td>
</tr>
<tr>
<td align='center'>11731</td>
<td align='center'>3175</td>
<td align='center'>0</td>
</tr>
<tr>
<td align='center'>11732</td>
<td align='center'>3176</td>
<td align='center'>249.5</td>
</tr>
<tr>
<td align='center'>11733</td>
<td align='center'>3175</td>
<td align='center'>0</td>
</tr>
</table>
</td>
</tr>
</table>
</font>
<hr><h2><font color="#009999">28.1.3 Implementing One-to-Many Relationships
(cont.)</font></h2>
<font size="+1">
<table cellpadding='12'>
<tr>
<td valign='top'><font size='+1'>
<ul>
<li>Our DB now has 4 tables:
<ul>
<li>Invoice</li>
<li>Customer</li>
<li>Item</li>
<li>Product</li>
</ul>
</li>
</ul>
</td>
<td>
<script><!--
image( "fig01.png" )
//--></script>
</td>
</tr>
</table>
</font>
<hr><h2><font color="#009999">28.2 Queries</font></h2>
<font size="+1">
<ul>
<li>Given a populated database (next slides)</li>
<li>Want to query it:
<ul>
<li>Names and addresses of all customers</li>
<li>In CA</li>
<li>Who bought toasters</li>
<li>With unpaid invoices</li>
</ul>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.2 Queries (cont.)</font></h2>
<font size="+1">
<table>
<tr>
<td valign='top'>
<table border='1' cellpadding='4'>
<tr><th colspan='6'>Product</th></tr>
<tr bgcolor='#00ffff'>
<th>Product_Code</th>
<th>Description</th>
<th>Unit_Price</th>
</tr>
<tr>
<td align='center'>116-064</td>
<td align='center'>Toaster</td>
<td align='center'>24.95</td>
</tr>
<tr>
<td align='center'>257-535</td>
<td align='center'>Hair dryer</td>
<td align='center'>29.95</td>
</tr>
<tr>
<td align='center'>643-119</td>
<td align='center'>Car vacuum</td>
<td align='center'>19.99</td>
</tr>
</table>
</td>
<td valign='top'>
<table border='1' cellpadding='4'>
<tr><th colspan='3'>Item</th></tr>
<tr bgcolor='#00ffff'>
<th>Invoice_Number</th>
<th>Product_Code</th>
<th>Quantity</th>
</tr>
<tr>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>CHAR(10)</tt></td>
<td align='center'><tt>INTEGER</tt></td>
</tr>
<tr>
<td align='center'>11731</td>
<td align='center'>116-064</td>
<td align='center'>3</td>
</tr>
<tr>
<td align='center'>11731</td>
<td align='center'>257-535</td>
<td align='center'>1</td>
</tr>
<tr>
<td align='center'>11731</td>
<td align='center'>643-119</td>
<td align='center'>2</td>
</tr>
<tr>
<td align='center'>11732</td>
<td align='center'>116-064</td>
<td align='center'>10</td>
</tr>
<tr>
<td align='center'>11733</td>
<td align='center'>116-064</td>
<td align='center'>2</td>
</tr>
<tr>
<td align='center'>11733</td>
<td align='center'>643-119</td>
<td align='center'>1</td>
</tr>
</table>
</td>
</tr>
</table>
</font>
<hr><h2><font color="#009999">28.2 Queries (cont.)</font></h2>
<font size="+1">
<table border='1' cellpadding='4'>
<tr><th colspan='3'>Invoice</th></tr>
<tr bgcolor='#00ffff'>
<th>Invoice_Number</th>
<th>Customer_Number</th>
<th>Payment</th>
</tr>
<tr>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>DECIMAL(10,2)</tt></td>
</tr>
<tr>
<td align='center'>11731</td>
<td align='center'>3175</td>
<td align='center'>0</td>
</tr>
<tr>
<td align='center'>11732</td>
<td align='center'>3176</td>
<td align='center'>249.5</td>
</tr>
<tr>
<td align='center'>11733</td>
<td align='center'>3175</td>
<td align='center'>0</td>
</tr>
</table>
<table border='1' cellpadding='4'>
<tr><th colspan='6'>Customer</th></tr>
<tr bgcolor='#00ffff'>
<th>Customer_Number</th>
<th>Name</th>
<th>Address</th>
<th>City</th>
<th>State</th>
<th>Zip</th>
</tr>
<tr>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>CHAR(40)</tt></td>
<td align='center'><tt>CHAR(40)</tt></td>
<td align='center'><tt>CHAR(30)</tt></td>
<td align='center'><tt>CHAR(2)</tt></td>
<td align='center'><tt>CHAR(10)</tt></td>
</tr>
<tr>
<td align='center'>3175</td>
<td align='center'>Sam's Small Appliances</td>
<td align='center'>100 Main St.</td>
<td align='center'>Anytown</td>
<td align='center'>CA</td>
<td align='center'>98765</td>
</tr>
<tr>
<td align='center'>3176</td>
<td align='center'>Electronics Unlimited</td>
<td align='center'>1175 Liberty Ave.</td>
<td align='center'>Pleasantville</td>
<td align='center'>MI</td>
<td align='center'>45066</td>
</tr>
</table>
</font>
<hr><h2><font color="#009999">28.2.1 Simple Queries</font></h2>
<font size="+1">
<ul>
<li>Use <tt>SELECT</tt></li>
<li>E.g., to get all data from the Customer table:
<blockquote><tt>
SELECT * FROM Customer
</tt></blockquote>
</li>
<li>Returns <i>view</i>:<br><br>
<table border='1' cellpadding='4'>
<tr>
<td align='center'>3175</td>
<td align='center'>Sam's Small Appliances</td>
<td align='center'>100 Main St.</td>
<td align='center'>Anytown</td>
<td align='center'>CA</td>
<td align='center'>98765</td>
</tr>
<tr>
<td align='center'>3176</td>
<td align='center'>Electronics Unlimited</td>
<td align='center'>1175 Liberty Ave.</td>
<td align='center'>Pleasantville</td>
<td align='center'>MI</td>
<td align='center'>45066</td>
</tr>
</table>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.2.2 Selecting Columns</font></h2>
<font size="+1">
<ul>
<li>To select certain fields from all rows in table:
<blockquote><tt>
SELECT City, State FROM Customer
</tt></blockquote>
</li>
<li>Returns view:<br><br>
<table border='1' cellpadding='4'>
<tr>
<td align='center'>Anytown</td>
<td align='center'>CA</td>
</tr>
<tr>
<td align='center'>Pleasantville</td>
<td align='center'>MI</td>
</tr>
</table>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.2.3 Selecting Subsets</font></h2>
<font size="+1">
<ul>
<li>To select rows (records) based on certain criteria:
<blockquote><tt>
SELECT * FROM Customer WHERE State = 'CA'
</tt></blockquote>
</li>
<li>Returns:<br><br>
<table border='1' cellpadding='4'>
<tr>
<td align='center'>3175</td>
<td align='center'>Sam's Small Appliances</td>
<td align='center'>100 Main St.</td>
<td align='center'>Anytown</td>
<td align='center'>CA</td>
<td align='center'>98765</td>
</tr>
</table>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.2.3 Selecting Subsets (cont.)</font></h2>
<font size="+1">
<ul>
<li>Relational operators:
<blockquote><tt>
= <> < <=
> >= LIKE
</tt></blockquote>
</li>
<li>(Case-sensitive matching is DB dependent)
<ul>
<li>E.g.
<blockquote><tt>
SELECT * FROM Customer WHERE State <> 'CA'
</tt></blockquote>
</li>
</ul>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.2.3 Selecting Subsets (cont.)</font></h2>
<font size="+1">
<ul>
<li><tt>LIKE</tt> allows pattern matching
<table>
<tr>
<td width='20%'><tt>_</tt></td>
<td>Any single character</td>
</tr>
<tr>
<td><tt>%</tt></td>
<td>Any sequence of characters</td>
</tr>
</table>
<ul>
<li>E.g.
<blockquote><tt>
Name LIKE '_o%'
</tt></blockquote>
matches "Toaster", but not "Crowbar"
</li>
</ul>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.2.3 Selecting Subsets</font></h2>
<font size="+1">
<ul>
<li>Logical Operators:
<blockquote><tt>
AND OR NOT
</tt></blockquote>
<ul>
<li>E.g.
<blockquote>
<pre>SELECT *
FROM Product
WHERE Unit_Price < 100
AND Description <> 'Toaster'</pre>
</blockquote>
</li>
</ul>
</li>
<li>Select row and column subsets:
<blockquote><tt>
SELECT Name, City FROM Customer WHERE State = 'CA'
</tt></blockquote>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.2.4 Calculations</font></h2>
<font size="+1">
<ul>
<li>Built-in functions:
<blockquote><tt>
COUNT SUM AVG
MAX MIN
</tt></blockquote>
</li>
<li>E.g.,
<blockquote><tt>
SELECT COUNT(*) FROM Customer WHERE State = 'CA'
</tt></blockquote>
</li>
<li>The <tt>*</tt> argument applies to entire record; only valid in
<tt>COUNT</tt></li>
<li>Specify column for other functions:
<blockquote><tt>
SELECT AVG(Unit_Price) FROM Product
</tt></blockquote>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.2.5 Joins</font></h2>
<font size="+1">
<ul>
<li>Retrieve information distributed over several tables</li>
<li>E.g., find all invoices that order a car vacuum:
<ul>
<blockquote>
<pre>SELECT Product_Code
FROM Product
WHERE Description = 'Car vacuum'</pre>
</blockquote>
<li>To discover vacuum has code 643-119. Search invoices:</li>
<blockquote>
<pre>SELECT Invoice_Number
FROM Item
WHERE Product_Code = '643-119'</pre>
</blockquote>
</ul>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.2.5 Joins (cont.)</font></h2>
<font size="+1">
<ul>
<li>Can be combined into a single query</li>
<li>Tables linked by column(s) (e.g., <tt>Product_Code</tt>)</li>
<li>Syntax to qualify column names:
<blockquote><i>
TableName.ColumnName
</i></blockquote>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.2.5 Joins (cont.)</font></h2>
<font size="+1">
<ul>
<li>So,
<blockquote>
<pre>SELECT Item.Invoice_Number
FROM Product, Item
WHERE Product.Description = 'Car vacuum'
AND Product.Product_Code = Item.Product_Code</pre>
</blockquote>
</li>
<li>Yields<br><br>
<table border='1' cellpadding='4'>
<tr><td align='center'>11731</td></tr>
<tr><td align='center'>11733</td></tr>
</table><br><br>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.2.5 Joins (cont.)</font></h2>
<font size="+1">
<ul>
<li>Multiple tables after <tt>FROM</tt>
<ul>
<li>Comma-separated</li>
<li>Order irrelevant</li>
</ul>
</li>
<li>Called a <i>join</i></li>
</ul>
</font>
<hr><h2><font color="#009999">28.2.5 Joins (cont.)</font></h2>
<font size="+1">
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -