[学会MySql系列] 第五篇：复杂检索

接上篇

8. 嵌套检索

SQL中的嵌套检索称之为子查询。基本结构为

#本例摘自《MySQL必知必会》
SELECT cust_id,...
FROM orders
WHERE order_num IN(
	   SELECT order_num
	   FROM orderitems
	   WHERE prod_id='TNT2' );

外层检索嵌套内层检索，内层检索用括号括起来。在上面例子中子查询是放在WHERE后面，即先进行内层检索，将返回的结果中作为外层检索的条件项目再检索。

子查询可以放在SELECT子句内，来看以下例子

#本例摘自《MySQL必知必会》
SELECT cust_name,
		cust_state,
		(SELECT COUNT(*)
		FROM orders
		WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

例子中子查询放在了SELECT子句内。
这样的嵌套可以多个。

• 注：在子查询中涉及不同表中的同名列，必须用完全限定列名。

9. 组合检索

将多条SELECT语句用UNION组合起来称之为组合查询。
实际上对于多条件检索数据都可以通过组合检索代替多个WHERE子句，比如

#本例摘自《MySQL必知必会》
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price<=5
  		OR vend_id IN (1001,1002)

可以写成

#本例摘自《MySQL必知必会》
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)

看起来这样更加复杂，但是组合查询支持从不同表中检索的SELECT组合起来，这是多个WHERE无法实现的。

在组合查询中需要注意的两点
1.组合查询返回的结果是无重复的结果，默认删除了重复的行，如果要返回原始的结果，要使用UNION ALL
2.组合查询的排序只有最有一个SELECT后面跟着的ORDER BY有用。

10. 全文本搜索

在文本搜索中可以用第三篇中的通配符或者正则表达式，但是这两个有个缺点就是1速度慢，2搜索的结果没有按照符合程度来排序。全文本搜索能够解决这两点。
不过全文本搜索也有局限性，最大的问题就是全文本搜索的内容必须是先全文本索引的，必须在创建表的时候选择全文本索引，例如

#本例摘自《MySQL必知必会》
CREATE TABLE productnotes
(
	note_id      int       NOT NULL AUTO_INCREMENT,
	prod_id      char(20)  NOT NULL,
	note_date   datetime   NOT NULL,
	note_text    text      NOT NULL,
	...
	FULLTEXT(note_text)
	#开启全文本索引
)ENGINE = MyISAM;

FULLTEXT就是全文本索引。

全文本索引需要注意的是一定要选择支持全文本索引的引擎。

在索引之后，使用Match()和Against()执行全文本搜索，例如

#本例摘自《MySQL必知必会》
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit')
#WHERE Match(column) Against(words)

全文本搜索的结果是有排序的，排序的原则就是对搜索词的匹配程度，匹配词越靠前等级越高。

全文本搜索对比通配符或正则表达式优越的另一点是，全文本搜索能够对搜索进行扩展。

10.1 搜索扩展

搜索扩展可以应用在这样的场景：当我记不清所要搜索的全部匹配词，只记得一个rabbit词，搜索扩展能够对这个搜索进行扩展，以确保最大程度的搜索到想要的内容。

搜索扩展的实际步骤：
1.首先，进行一个rabbit的全文本搜索，找到匹配行
2.检查选择匹配行里面的有用的词
3.用上步选择的有用的词再一次进行全文本搜索，找到匹配行

返回所有的匹配行。

进行搜索扩展的语句格式如下

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION)

10.2 布尔文本搜索

布尔文本搜索是全文本搜索的另一种方式，优点是没有全文索引的要求，能够支持各种骚操作，比如

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit -heavy' IN BOOLEAN MODE)
# 全文搜索note_text列中有rabbit没有heavy的行

只要或用布尔操作符就能够实现很多复杂的搜索要求，布尔操作符见下表

布尔操作符	意义
+	必须包含
-	必须排除
>	包含增加优先级
<	包含减小优先级
*	词尾通配符
" "	定义匹配短语
()	定义组合的匹配短语

MySQL必知必会