15.聯結表
SQL最強大的功能之一就是能在數據檢索查詢的執行中聯結表。聯結是利用SQL的SELECT能執行的最重要的操作。
關系表的設計是要保證把信息分解成多個表，一類數據一個表，各表通過一定的關系互相關聯。
外鍵：外鍵為某個表的一列，它包含另一個表的主鍵值，定義了兩個表之間的關系。
可伸縮性：能夠適應不斷增加的工作量而不失敗。
15.1 聯結
聯結是一種機制，用來在一條select語句中關聯表，因此稱之為聯結。使用特殊的語法，可以聯結多個表返回一組輸出，聯結在運行時關聯表中正確的行。
15.2 創建聯結
SELECT vend_name,prod_name,prod_price FROMvendors,products WHERE vendo.vend_id = products.vend_id ORDER BYvend_name,prod_name;
+-------------+----------------+------------+
| vend_name | prod_name | prod_price |
+-------------+----------------+------------+
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Detonator | 13.00 |
| ACME | Safe | 50.00 |
| ACME | Sling | 4.49 |
| ACME | TNT (1 stick) | 2.50 |
| ACME | TNT (5 sticks) | 10.00 |
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
| Anvils R Us | 2 ton anvil | 14.99 |
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
+-------------+----------------+------------+
14 rows in set (0.05 sec)
應該保證所有聯結都有WHERE子句，否則MySQL將返回比想要的數據多得多的數據。在引用的列可能出現二義性時，必須使用完全限定列名（用一個點分隔的表名和列名）。
15.3 內部聯結
基於兩個表之間相等測試的聯結稱為等值聯結，也叫內部聯結。可以使用稍微不同的語法來實現這種類型的聯結。
SELECT vend_name,prod_name,prod_price FROMvendors INNER JOIN products ON vendors.vend_id = products.vend_id;
這裡兩個表之間的關系式FROM子句的組成部分，以INNER JOIN指定。在使用這種語法時，聯結條件用特定的ON子句而不是WHERE子句給出。傳遞給ON的實際條件與傳遞給WHERE的相同。
15.4聯結多個表
SELECT cust_name,cust_contact FROMcustomers,orders,orderitems WHERE customers.cust_id = orders.cust_id ANDorderitems.order_num = orders.order_num AND prod_id = 'TNT2';
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
2 rows in set (0.02 sec)
這裡實現了14章中子查詢的功能。
16.創建高級聯結
16.1使用表別名
SELECT cust_name,cust_contact FROM customersAS c,orders AS o,orderitemsAS oi WHERE c.cust_id = o.cust_id AND oi.order_num=o.order_numAND prod_id = 'TNT2';
表別名布局能用於WHERE子句還可以用於SELECT的列表，ORDER BY子句以及語句的其他部分。
16.2使用不同類型的聯結
自聯結，自燃聯結和外部聯結
16.2.1 自聯結
SELECT p1.prod_id,p1.prod_name FROMproducts AS p1,products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id ='DTNTR';
16.2.2 自然聯結
自然聯結排除多次出現，使每個列值返回一次。事實上，我們建立的每個內部聯結都是自然聯結。
16.2.3外部聯結
mysql> SELECTcustomers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN
orders ON customers.cust_id =orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10002 | NULL |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
6 rows in set (0.00 sec)
與內部聯結關聯兩個表中的行不同的是外部聯結還包括沒有關聯的行。在使用OUTER JOIN時，必須使用RIGHT或LEFT關鍵字指定包括其所有行的表（RIGHT指出OUTER JOIN右邊的表，LEFT指出OUTER JOIN左邊的表）。
16.3使用帶聚集函數的聯結
mysql> SELECTcustomers.cust_name,customers.cust_id,COUNT(orders.order_num) AS N
num_ord FROM customers INNER JOIN orders ONcustomers.cust_id = orders.cust_id G
ROUP BY customers.cust_id;
+----------------+---------+----------+
| cust_name | cust_id | Nnum_ord |
+----------------+---------+----------+
| Coyote Inc. | 10001 | 2 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+----------+
4 rows in set (0.00 sec)
17.組合查詢
組合查詢也通常稱為並（union）或復合查詢
有兩種基本情況，其中需要使用組合查詢：
1）在單個查詢中從不同的表返回類似結構的數據
2）對單個表執行多個查詢，按單個查詢返回數據
17.1創建組合查詢
SELECT vend_id,prod_id,prod_price FROM productsWHERE prod_price <=5 UNION SELECT vend_id,prod_id,prod_price FROM productsWHERE vend_id IN(1001,1002);
將UNION換位UNION ALL可以可以包含不同SELECT子句選中的重復的列
17.2 UNION規則
1）UNION必須由兩條或兩條以上的SELECT語句組成，語句之間用關鍵字UNION分隔。
2）UNION中的 每個查詢必須包含相同的列、表達式或聚集函數
3）列數據類型可以不完全相同，但必須兼容
17.3對組合查詢結果排序
ORDER BY必須出現在最後一條SELECT語句之後
18.全文本搜索
在進行全文本搜索之前，首先應將表中的一列或多列設為FULLTEXT。然後使用Match()和Against()執行全文搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表達式。
18.1進行全文本搜索
SELECT note_text FROM productnotes WHERE Match(note_text)Against('rabbit');
+-------------------------------------------------------------------------------
----------------------------------------+
| note_text
|
+-------------------------------------------------------------------------------
----------------------------------------+
| Customer complaint: rabbit has been ableto detect trap, food apparently less
effective now. |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, andsuitable for use as rabbit bait. |
+-------------------------------------------------------------------------------
----------------------------------------+
2 rows in set (0.05 sec)
上面輸出的兩行結果的輸出順序是進過排序後得到的，也就是包含詞rabbit作為第3個詞的等級比作為第20個詞的行高。下面演示全文本搜索如何排序工作。
mysql> SELECT note_text,Match(note_text)Against('rabbit') AS rank FROM productn
otes;
+-------------------------------------------------------------------------------
-----------------------------------------------------------------------------+--
----------------+
| note_text
| r
ank |
+-------------------------------------------------------------------------------
-----------------------------------------------------------------------------+--
----------------+
| Customer complaint:
Sticks not individually wrapped, too easyto mistakenly detonate all at once.
Recommend individual wrapping. | 0 |
| Can shipped full, refills not available.
Need to order new can if refill needed.
| 0 |
| Safe is combination locked, combinationnot provided with safe.
This is rarely a problem as safes aretypically blown up or dropped by customers
. | 0 |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, andsuitable for use as rabbit bait.
| 1.59055435657501 |
| Included fuses are short and have beenknown to detonate too quickly for some
customers.
Longer fuses are available (item FU1) andshould be recommended. |
0 |
| Matches not included, recommend purchaseof matches or detonator (item DTNTR).
|
0 |
| Please note that no returns will beaccepted if safe opened using explosives.
|
0 |
| Multiple customer returns, anvils failingto drop fast enough or falling backw
ards on purchaser. Recommend that customerconsiders using heavier anvils. |
0 |
| Item is extremely heavy. Designed for dropping,not recommended for use with s
lings, ropes, pulleys, or tightropes. |
0 |
| Customer complaint: rabbit has been ableto detect trap, food apparently less
effective now. | 1
.64080536365509 |
| Shipped unassembled, requires commontools (including oversized hammer).
|
0 |
| Customer complaint:
Circular hole in safe floor can apparentlybe easily cut with handsaw.
| 0 |
| Customer complaint:
Not heavy enough to generate flying starsaround head of victim. If being purcha
sed for dropping, recommend ANV02 or ANV03instead. | 0 |
| Call from individual trapped in safeplummeting to the ground, suggests an esc
ape hatch be added.
Comment forwarded to vendor. | 0 |
+-------------------------------------------------------------------------------
-----------------------------------------------------------------------------+--
----------------+
14 rows in set (0.03 sec)
這裡，在SELECT而不是WHERE子句中使用Match()和Against()。這使所有行都被返回。Match()和Against()用來建立一個計算列，此列包含全文本搜索計算出的等級值。等級有MySQL根據行中詞的數目、唯一詞的數目、整個索引中詞的總數以及包含該詞的行的數目計算出來。
18.2使用查詢擴展
查詢擴展用來設法房款所返回的全文本搜素結果的范圍。在使用查詢擴展時，MySQL對數據和索引進行兩邊掃描來完成搜索：
1） 首先，進行一個基本的全文本搜索，找粗與搜索條件匹配的所有行
2） 其次，MySQL檢查這些匹配行並進行選擇所有有用的詞（我們將會簡要地解釋MySQL如何判定什麼有用，什麼無用）
3） 再其次，MySQL再次進行全文本搜索，這次不僅使用原來的條件，而且還使用所有有用的詞
mysql> select note_text FROMproductnotes WHERE Match(note_text) Against('anvils
' WITH QUERY EXPANSION);
+-------------------------------------------------------------------------------
---------------------------------------------------------------------------+
| note_text
|
+-------------------------------------------------------------------------------
---------------------------------------------------------------------------+
| Multiple customer returns, anvils failingto drop fast enough or falling backw
ards on purchaser. Recommend that customerconsiders using heavier anvils. |
| Customer complaint:
Sticks not individually wrapped, too easyto mistakenly detonate all at once.
Recommend individual wrapping. |
| Customer complaint:
Not heavy enough to generate flying starsaround head of victim. If being purcha
sed for dropping, recommend ANV02 or ANV03instead. |
| Please note that no returns will beaccepted if safe opened using explosives.
|
| Customer complaint: rabbit has been ableto detect trap, food apparently less
effective now. |
| Customer complaint:
Circular hole in safe floor can apparentlybe easily cut with handsaw.
|
| Matches not included, recommend purchaseof matches or detonator (item DTNTR).
|
+-------------------------------------------------------------------------------
---------------------------------------------------------------------------+
7 rows in set (0.00 sec)
這裡第一行包含詞anvils，因此等級最高。第二行與anvils無關，但因為它包含第一行中的兩個詞（customer和recommend）,所以也被檢索出來。接下去的幾行也會如此。
18.3布爾文本搜索
MySQL支持全文本搜索的另外一種形式，布爾搜索，可以提供如下內容的細節：
1） 要匹配的詞
2） 要排斥的詞（如果某行包含這種詞，則不返回改行，即使它包含要匹配的詞）
3） 排列提示（指定某些詞比其他次更重要，更重的詞等級更高）
4） 表達式分組
5） 另外一些內容
布爾方式不同於一般的全文本搜索的地方在於：即使沒有定義FULLTEXT索引，也可以使用它。
布爾操作符與說明如下：
+ 包含，詞必須存在
- 排除，詞必須不出現
> 包含，而且增加等級值
< 包含，且減少等級值
() 把詞組成子表達式
~ 取消一個詞的排序值
* 詞尾的通配符
“ ” 定義一個短語
SELECT note_text FROM productnotes WHERE Match(note_text)Against('heavy' IN BOOLEAN MODE);//輸出結果如下
+-------------------------------------------------------------------------------
---------------------------------------------------------------------------+
| note_text
|
+-------------------------------------------------------------------------------
---------------------------------------------------------------------------+
| Item is extremely heavy. Designed fordropping, not recommended for use with s
lings, ropes, pulleys, or tightropes. |
| Customer complaint:
Not heavy enough to generate flying starsaround head of victim. If being purcha
sed for dropping, recommend ANV02 or ANV03instead. |
+-------------------------------------------------------------------------------
---------------------------------------------------------------------------+
2 rows in set (0.00 sec)
這裡使用了關鍵字IN BOOLEAN MODEN
mysql> SELECT note_text FROMproductnotes WHERE Match(note_text) Against('heavy
-rope*' IN BOOLEAN MODE);
+-------------------------------------------------------------------------------
---------------------------------------------------------------------------+
| note_text
|
+-------------------------------------------------------------------------------
---------------------------------------------------------------------------+
| Customer complaint:
Not heavy enough to generate flying starsaround head of victim. If being purcha
sed for dropping, recommend ANV02 or ANV03instead. |
+-------------------------------------------------------------------------------
---------------------------------------------------------------------------+
1 row in set (0.00 sec)
這裡-rope*明確地指示MySQL排除包含rope*。
下面舉幾個例子
SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘+rabbit +bait’ IN BOOLEAN MODE);//這個搜索匹配包含詞rabbit和bait的行
SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘rabbit bait’ IN BOOLEAN MODE);//沒有指定操作符，這個搜索匹配rabbit和bait中的至少一個詞的行
SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘”rabbit +bait”’ IN BOOLEAN MODE);//這個詞匹配短語rabbitbait而不是匹配兩個詞rabbit和bait
SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘>rabbit <bait’ IN BOOLEAN MODE);//增加rabbit的等級，降低bait的等級
SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘+safe +(<cobination)’ IN BOOLEAN MODE);//這個搜索匹配詞safe和combination，降低後者的等級
19.插入數據
19.1插入完整的行
mysql> INSERT INTOcustomers(cust_name,cust_address,cust_city,cust_state,cust_zi
p,cust_country,cust_contact,cust_email)VALUES('Pep E. Lapew','100 main Street',
'LosAngeles','CA','90046','USA','NULL','NULL');
Query OK, 1 row affected (0.06 sec)
19.2插入多個行
mysql> INSERT INTOcustomers(cust_name,cust_address,cust_city,cust_state,cust_zi
p,cust_country) VALUES('Pep E. Lapew','100main Street',
'Los Angeles','CA','90046','USA'),(‘M.Martian’,’42 Galaxy Way’,’New York’,’NY’,’11213’,’USA’);
19.3插入檢索出的數據
INSERT INTOcustomers(cust_name,cust_address,cust_city,cust_state,cust_zi
p,cust_country)SELECTcust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_countryFROM custnew;
20.更新和刪除數據
20.1跟新數據
兩種方式
1）更新表中所有行,不加WHERE
2）更新表中特定行，加WHERE
UPDATE語句由3個部分組成，分別是：
1） 要更新的表
2） 列名和他們的新值
3） 確定要更新的過濾條件
mysql> UPDATE customers SET cust_name ='The Fudds',cust_email = '[email protected]
' WHERE cust_id = 10005;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
20.2刪除數據
