今天繼續(xù)來學 SQL，主要是《MySQL必知必會》15-18章的內容。
Ch 15.聯結表
1.聯結
SQL 最強大的功能之一就是能在數據檢索查詢的執(zhí)行中聯結（join）表。聯結是利用 SQL 的 SELECT 能執(zhí)行的最重要的操作。關系數據庫設計的基礎是將信息分解為多個表，相同類型的數據存放在同一個表中，各個表通過某些常用的值（即關系設計中的關系（relational））互相關聯。關系數據可以有效地存儲和方便地處理。因此，關系數據庫的可伸縮性遠比非關系數據庫要好。
可伸縮性指的是能夠適應不斷增加的工作量而不失敗。設計良好的數據庫或應用程序稱之為可伸縮性好（scale well）。
SQL 中聯結的實現依賴于主鍵和外鍵。主鍵為某個表的一列，其值為某條記錄的唯一標識符。外鍵同樣是某個表的一列，它包含另一個表的主鍵值，定義了兩個表之間的關系。以下是聯結的一個例子：
SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;
其中 where 子句規(guī)定了聯結條件。如果沒有聯結條件，返回的結果為笛卡爾積（Cartesian product），檢索出的行的數目將是第一個表中的行數乘以第二個表中的行數。
上述聯結稱為等值聯結，基于兩個表之間的相等測試，也稱為內部鏈接。以下的 SQL 語句會返回跟上述語句相同的結果：
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
在使用這種語法時，聯結條件用特定的ON子句而不是WHERE子句給出。傳遞給ON的實際條件與傳遞給WHERE的相同。
注意：ANSI SQL規(guī)范首選INNER JOIN語法。此外，盡管使用WHERE子句定義聯結的確比較簡單，但是使用明確的聯結語法能夠確保不會忘記聯結條件，有時候這樣做也能影響性能。
2.聯結多個表
SQL對一條SELECT語句中可以聯結的表的數目沒有限制。、
SELECT vend_name,prod_name,prod_price,quantity
FROM vendors,products,ordertimes
WHERE vendors.vend_id = products.vend_id
AND ordertimes.prod_id = products.vend_id
AND order_num = 20005;
注意：MySQL 在運行時關聯指定的每個表以處理聯結。這種處理可能是非常耗費資源的，因此應該仔細，不要聯結不必要的表。聯結的表越多，性能下降越嚴重。
上一章使用嵌套子查詢的例子同樣能夠使用聯結實現：
SELECT cust_name,cust_contact
FROM customers,orders,ordertimes
WHERE customers.cust_id = orders.cust_id
AND ordertimes.order_num = orders.order_num
AND prod_id = 'TNT2';
為執(zhí)行任一給定的SQL操作，一般存在不止一種方法。很少有絕對正確或絕對錯誤的方法。性能可能會受操作類型、表中數據量、是否存在索引或鍵以及其他一些條件的影響。
Ch 16.高級聯結
1.使用表別名
前文介紹了如何使用別名引用被檢索的表列，SQL 還支持給表起別名，這樣一來可以縮減 SQL 語句的長度，二來可以在同一條 SQL 語句中多次使用同一張表。
SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNT2';
應該注意，表別名只在查詢執(zhí)行中使用。與列別名不一樣，表別名不返回到客戶機。
2.使用不同類型的聯結
自聯結
舉一個簡單的例子，如果要查詢名為“DTNTR”產品的廠商所生產的其他產品，首先需要根據產品名在products表中獲取廠商名，然后再利用廠商名在products表中獲得其他產品。利用此前講過的子查詢可以實現：
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
FROM products
WHERE prod_id = 'DTNTR');
使用聯結可以實現相同的功能：
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';
上述聯結稱為自聯結。需要注意的是使用自聯結時必須使用完全限定列名，否則會產生歧義。
自聯結通常作為外部語句用來替代從相同表中檢索數據時使用的子查詢語句。雖然最終的結果是相同的，但有時候處理聯結遠比處理子查詢快得多。
自然聯結
通常情況下使用聯結時所設計的幾張表里面應該至少有一個列出現在不止一個表中，標準的聯結返回所有的數據，因此會出現多次相同的列。而自然聯結排除多次出現，使每個列之返回一次。
自然聯結無法由系統自動實現，只能自己編寫，需要在SQL語句中明確制定要返回的列。以下是一個例子：
SELECT c.*, o.order_num, o.order_date,
oi.prod_id, oi.quantity, OI.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'FB';
“事實上，迄今為止我們建立的每個內部聯結都是自然聯結，很可能我們永遠都不會用到不是自然聯結的內部聯結?！?/em>
外部聯結
聯結的主要意義在于將不同表的行所關聯起來，然而，有時候會需要包含那些沒有關聯行的那些行，例如以下的需求：
- 對每個客戶下了多少訂單進行計數，包括那些至今尚未下訂單的客戶；
- 列出所有產品以及訂購數量，包括沒有人訂購的產品；
- 計算平均銷售規(guī)模，包括那些至今尚未下訂單的客戶。
這樣的聯結稱為外部聯結。
試看以下兩個 SQL 語句的異同：
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;
#檢索所有的客戶及其訂單
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
#檢索所有客戶，包括那些沒有訂單的客戶
以上這條SELECT語句使用了關鍵字 OUTER JOIN 來指定聯結的類型（而不是在 WHERE 子句中指定）。但是，與內部聯結關聯兩個表中的行不同的是，外部聯結還包括沒有關聯行的行。在使用 OUTER JOIN 語法時，必須使用 RIGHT 或 LEFT 關鍵字指定包括其所有行的表（RIGHT 指出的是 OUTER JOIN 右邊的表，而 LEFT 指出的是OUTER JOIN 左邊的表）。上面的例子使用 LEFT OUTER JOIN 從 FROM 子句的左邊表（customers表）中選擇所有行。
注意：存在兩種基本的外部聯結形式：左外部聯結和右外部聯結。它們之間的唯一差別是所關聯的表的順序不同。換句話說，左外部聯結可通過顛倒FROM或WHERE子句中表的順序轉換為右外部聯結。因此，兩種類型的外部聯結可互換使用，而究竟使用哪一種純粹是根據方便而定。
3.使用帶聚集函數的聯結
可以在內部聯結、外部聯結中使用聚集函數：
#內部聯結中使用聚集函數
SELECT customers.cust_name,
customers.cust_id,
COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
#外部聯結中使用聚集函數
SELECT customers.cust_name,
customers.cust_id,
COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
4.使用聯結和聯結條件的注意事項
- 注意所使用的聯結類型。一般我們使用內部聯結，但使用外部聯結也是有效的。
- 保證使用正確的聯結條件，否則將返回不正確的數據。
- 應該總是提供聯結條件，否則會得出笛卡兒積。
- 在一個聯結中可以包含多個表，甚至對于每個聯結可以采用不同的聯結類型。雖然這樣做是合法的，一般也很有用，但應該在一起測試它們前，分別測試每個聯結。這將使故障排除更為簡單。
Ch 17.組合查詢
MySQL允許執(zhí)行多個查詢（多條SELECT語句），并將結果作為單個查詢結果集返回。這些組合查詢通常稱為并（union）或復合查詢（compoundquery）。
有兩種基本情況，其中需要使用組合查詢：
- 在單個查詢中從不同的表返回類似結構的數據；
- 對單個表執(zhí)行多個查詢，按單個查詢返回數據。
多數情況下，組合相同表的兩個查詢完成的工作與具有多個WHERE子句條件的單條查詢完成的工作相同。換句話說，任何具有多個WHERE子句的SELECT語句都可以作為一個組合查詢給出，兩者的性能同時也存在差異。
組合查詢的實現使用的是 UNION 關鍵字，其使用也非常簡單，只需要在若干個 SELECT 語句中間添加 UNION 關鍵字即可，查詢輸出的結果為各個SELECT語句結果的去重并集。
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);
使用UNION關鍵字的注意事項：
- UNION必須由兩條或兩條以上的SELECT語句組成，語句之間用關鍵字UNION分隔（因此，如果組合4條SELECT語句，將要使用3個UNION關鍵字）。
- UNION中的每個查詢必須包含相同的列、表達式或聚集函數（不過各個列不需要以相同的次序列出）。
- 列數據類型必須兼容：類型不必完全相同，但必須是DBMS可以隱含地轉換的類型（例如，不同的數值類型或不同的日期類型）。
非去重結果：在上述查詢中，返回的結果是會被進行去重處理的。這是 MySQL 默認的行為，如果要取消去重處理，可是顯示地使用 UNION ALL 關鍵字。前文說過，UNION和WHERE關鍵字執(zhí)行的是相同的操作，而WHERE子句無法完成UNION ALL 的操作。
對組合查詢結果進行排序：如果要對組合查詢的結果進行排序，可以在最后一條SELECT語句后使用ORDER BY子句：
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id, prod_price;
雖然ORDER BY子句似乎只是最后一條SELECT語句的組成部分，但實際上MySQL將用它來排序所有SELECT語句返回的所有結果。
Ch 18.全文本搜索
注意，并非所有的引擎都支持全文本搜索。MySQL最常使用的兩個數據庫引擎 MyISAM和InnoDB中前者支持而后者不支持。
1.理解全文本搜索
前面所介紹的LIKE關鍵字匹配和正則表達式匹配兩種搜索機制存在以下幾個重大的局限：
- 性能：通配符和正則表達式匹配往往需要嘗試匹配表中所有的記錄中所有的詞，如果表比較大就會非常耗時。
- 明確控制：使用通配符和正則表達式匹配，很難（而且并不總是能）明確地控制匹配什么和不匹配什么。
- 智能化的結果：雖然基于通配符和正則表達式的搜索提供了非常靈活的搜索，但它們都不能提供一種智能化的選擇結果的方法。例如，一個特殊詞的搜索將會返回包含該詞的所有行，而不區(qū)分包含單個匹配的行和包含多個匹配的行（按照可能是更好的匹配來排列它們）。
以上的這些限制都可以使用全文本搜索解決。在使用全文本搜索時，MySQL不需要分別查看每個行，不需要分別分析和處理每個詞。MySQL創(chuàng)建指定列中各詞的一個索引，搜索可以針對這些詞進行。這樣，MySQL可以快速有效地決定哪些詞匹配（哪些行包含它們），哪些詞不匹配，它們匹配的頻率等。
2.使用全文本搜索
要啟用全文本搜索必須要定義FULLTEXT進行索引，可以在定義表時制定，也可以在導入數據完成后通過修改標的定義實現：
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL ,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;
#在定義表時指定FULLTEXT
在定義之后，MySQL自動維護該索引。在增加、更新或刪除行時，索引隨之自動更新。MySQL會動態(tài)更新索引，因此導入數據時會更新索引降低效率，因此最好是先導入數據然后修改表、定義FULLTEXT。
3.進行全文本搜索
在索引之后，使用兩個函數Match()和Against()執(zhí)行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表達式。
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
注意：
- 傳遞給Match()的值必須與FULLTEXT()定義中的相同。如果指定多個列，則必須列出它們（而且次序正確）。
- 除非使用BINARY方式（本章中沒有介紹），否則全文本搜索不區(qū)分大小寫。
- 與通配符和正則表達式匹配不同，全文本搜索返回以文本匹配的良好程度排序的數據。兩個行都包含詞rabbit，但包含詞rabbit作為第3個詞的行的等級比作為第20個詞的行高。這很重要。全文本搜索的一個重要部分就是對結果排序。具有較高等級的行先返回（因為這些行很可能是你真正想要的行）。如果指定多個搜索項，則包含多數匹配詞的那些行將具有比包含較少詞（或僅有一個匹配）的那些行高的等級值。
4.使用查詢擴展
考慮下面的情況：你想找出所有提到anvils的注釋。只有一個注釋包含詞anvils，但你還想找出可能與你的搜索有關的所有其他行，即使它們不包含詞anvils。
上述情況需要使用查詢擴展來實現。在使用查詢擴展時，MySQL對數據和索引進行兩遍掃描來完成搜索：
- 首先，進行一個基本的全文本搜索，找出與搜索條件匹配的所有行；
- 其次，MySQL檢查這些匹配行并選擇所有有用的詞（我們將會簡要地解釋MySQL如何斷定什么有用，什么無用）。
- 最后，MySQL再次進行全文本搜索，這次不僅使用原來的條件，而且還使用所有有用的詞。
利用查詢擴展，能找出可能相關的結果，即使它們并不精確包含所查找的詞。
比較一下兩個SQL語句：
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils');
#返回一個包含 anvils 的記錄
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
#返回一個包含 anvils 的記錄和六個與之相關的記錄
查詢擴展極大地增加了返回的行數，但這樣做也增加了你實際上并不想要的行的數目，需要斟酌使用。
5.布爾文本搜索
利用布爾文本搜索，可以實現如下的元素匹配：
- 包含某詞匹配；
- 包含某詞不匹配；
- 排列提示（指定某些詞比其他詞更重要，更重要的詞等級更高）；
- 表達式分組等。
即使不定義 FULLTEXT，同樣可以使用布爾文本搜索，只不過其性能很低。
#匹配包含heavy但不包含任意以rope開始的詞的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
IN BOOLEAN MODE 表示使用布爾文本搜索。以下是MySQL所支持的布爾操作：
#布爾操作符 說 明
# + 包含，詞必須存在
# - 排除，詞必須不出現
# > 包含，而且增加等級值
# < 包含，且減少等級值
# () 把詞組成子表達式（允許這些子表達式作為一個組被包含、排除、排列等）
# ~ 取消一個詞的排序值
# * 詞尾的通配符
# "" 定義一個短語（與單個詞的列表不一樣，它匹配整個短語以便包含或排除這個短語）
#例：匹配包含詞rabbit和bait的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait"' IN BOOLEAN MODE);
#例：沒有指定操作符，這個搜索匹配包含rabbit和bait中的至少一個詞的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
#例：這個搜索匹配短語rabbit bait而不是匹配兩個詞rabbit和bait。
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);
#例：匹配rabbit和carrot，增加前者的等級，降低后者的等級。
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);
#例：這個搜索匹配詞safe和combination，降低后者的等級。
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
#注意：布爾文本搜索只排列而不排序，返回的結果不按等級值降序排序。
6.全文本搜索的使用說明
- 在索引全文本數據時，短詞被忽略且從索引中排除。短詞定義為那些具有3個或3個以下字符的詞（如果需要，這個數目可以更改）。
- MySQL帶有一個內建的非用詞（stopword）列表，這些詞在索引全文本數據時總是被忽略。如果需要，可以覆蓋這個列表（請參閱MySQL文檔）。
- 許多詞出現的頻率很高，搜索它們沒有用處（返回太多的結果）。因此，MySQL規(guī)定了一條50%規(guī)則，如果一個詞出現在50%以上的行中，則將它作為一個非用詞忽略。50%規(guī)則不用于IN BOOLEAN MODE。
- 如果表中的行數少于3行，則全文本搜索不返回結果（因為每個詞或者不出現，或者至少出現在50%的行中）。
- 忽略詞中的單引號。例如，don't索引為dont。
- 不具有詞分隔符（包括日語和漢語）的語言不能恰當地返回全文本搜索結果。
- 如前所述，僅在MyISAM數據庫引擎中支持全文本搜索。
- MySQL全文本搜索現在還不支持鄰近操作符，不過未來的版本有支持這種操作符的計劃。
未完待續(xù)···
