11.0 中出现的问题 现在需要列出订购物品TNT2的所有客户 即可以通过下面的方式处理
SELECT cust_name, cust_contact FROM customers WHERE cust_id IN ( SELECT cust_id FROM orders WHERE order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' )); SELECT cust_name, cust_contact FROM orderitems, orders, customers WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num AND prod_id = 'TNT2'; 12.4 使用别名这条语句和上面的查询结果一致，但使用了 别名
SELECT cust_name, cust_contact FROM orderitems AS oi, orders AS o, customers AS c WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'TNT2'; 12.5 自联结自联结: 同一张表的联结
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'; 12.6 外联结许多联结将一个表中的行与另外一个表中的行进行关联。但有时会需要包含没有关联的那些行，这就用到了外联结
LEFT OUTER JOIN : OUTER JOIN左边的表
RIGHT OUTER JOIN : OUTER JOIN右边的表
检索所有客户及客户所下的订单数
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; 13.0 组合查询 13.1 创建组合查询 union 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 中的每个查询必须包含相同的列、表达式或聚集函数（顺序可以不一致）
列数据类型必须兼容，及可以隐含转换的类型
union 可用于组合不同的表
13.2 包含或取消重复的行 union all13.1 的例子中，如果两条语句单独查询共计显示9条数据，而使用union却只显示了8行，
这是因为union默认会去除掉重复的行，不然不祥去重，可以使用 union all
order by只能放在最后的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 ) ORDER BY vend_id, prod_price;上面的查询看似只对第二条select语句进行排序，实则是对所有的select语句进行排序，
14.0全文本搜索并不是所有的引擎都支持全文索引。两个最常使用的引擎为MyISAM和InnoDB,前者支持全文本索引，而后者不支持
前面所提到的 like 和 regexp 都能实现这个功能，但是会有如下的的限制
性能 -- 通配符和正则表达式匹配通常要求mysql尝试匹配表中所有行(而且这些搜索极少使用表索引)。
因此，由于被搜索行数不断增加,这些搜索可能非常耗时
明确控制
智能化的结果 --
14.1启用全文本搜索 -- FULLTEXT() 可指定多个列 CREATE TABLE `productnotes` ( `note_id` int(11) NOT NULL AUTO_INCREMENT, `prod_id` char(10) NOT NULL, `note_date` datetime NOT NULL, `note_text` text, PRIMARY KEY (`note_id`), FULLTEXT (`note_text`) ) ENGINE=MyISAM;注意：不要在导入数据时使用 fulltext，可以先导入数据后在修改表
14.2进行全文本搜索
Match() 指定被搜索的列
传递给Match()的值必须与fulltext定义中的相同，如果指定多个列，则必须列出它们（且次序正确）
Against() 指定要使用的搜索表达式 - 不区分大小写
select note_text from productnotes where Match(note_text) Against('rabbit')\G; *************************** 1. row *************************** note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now. *************************** 2. row *************************** note_text: Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait. 2 rows in set (0.00 sec)查看全文搜索的排序如何工作的
select note_text, Match(note_text) Against('rabbit') as rank from productnotes \G; *************************** 1. row *************************** note_text: Customer complaint: Sticks not individually wrapped, too easy to mistakenly detonate all at once. Recommend individual wrapping. rank: 0 *************************** 2. row *************************** note_text: Can shipped full, refills not available. Need to order new can if refill needed. rank: 0 *************************** 3. row *************************** note_text: Safe is combination locked, combination not provided with safe. This is rarely a problem as safes are typically blown up or dropped by customers. rank: 0 *************************** 4. row *************************** note_text: Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait. rank: 1.5905543565750122 *************************** 5. row *************************** note_text: Included fuses are short and have been known to detonate too quickly for some customers. Longer fuses are available (item FU1) and should be recommended. rank: 0 *************************** 6. row *************************** note_text: Matches not included, recommend purchase of matches or detonator (item DTNTR). rank: 0 *************************** 7. row *************************** note_text: Please note that no returns will be accepted if safe opened using explosives. rank: 0 *************************** 8. row *************************** note_text: Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. rank: 0 *************************** 9. row *************************** note_text: Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes. rank: 0 *************************** 10. row *************************** note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now. rank: 1.6408053636550903 *************************** 11. row *************************** note_text: Shipped unassembled, requires common tools (including oversized hammer). rank: 0 *************************** 12. row *************************** note_text: Customer complaint: Circular hole in safe floor can apparently be easily cut with handsaw. rank: 0 *************************** 13. row *************************** note_text: Customer complaint: Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. rank: 0 *************************** 14. row *************************** note_text: Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added. Comment forwarded to vendor. rank: 0 14 rows in set (0.00 sec) 14.3使用查询扩展 with query expansion