组合查询

组合查询

MySQL允许执行多个查询并将结果作为单个查询结果返回。 两种情况：

• 在单个查询中从不同的表返回类似结构的数据
• 对单个表执行多个查询，按单个查询返回数据

创建组合查询

使用UNION

给出每条SELECT语句，在各条语句之间放上关键字UNION 例：需要价格小于等于5的所有物品的一个列表，并且包含供应商1001和1002生产的所有物品

#单条语句
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5;

SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);

#组合上述语句
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 vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
OR vend_id IN (1001, 1002);

UNION规则

• UNION 必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
• UNION中的每个查询必须包含先沟通的列、表达式或聚集函数
• 列数据类型必须兼容：类型不必完全向东，但必须是DBMS可以隐含地转换的类型

包含或取消重复的行

UNION从查询结果集中自动去除了重复的行，如果需要返回所有行，可以使用UNION ALL

对组合查询结果排序

使用ORDER BY子句排序，只能使用一条ORDER BY子句，必须在最后一条SELECT语句之后。

全文本搜索

并非所有引擎都支持全文本搜索 MyISAM支持，InnoDB不支持

LIKE、正则表达式的限制

• 性能：由于被搜索行不断增加，这些搜索可能非常耗时
• 明确控制：很难明确控制匹配什么和不匹配什么
• 智能化的结果：不能提供一种智能化的选择结果的方法，例如：一个特殊词的搜索将会返回包含该词的所有行而不区分包含单个匹配的行和多个匹配的行。

使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。 在索引之后，SELECT可与Match()和Against()一起使用以实际执行搜索。

启用全文本搜索支持

一般在创建表时启用全文本搜索 CREATE TABLE语句接收FULLTEXT子句，它给出被索引列的一个逗号分隔的列表 例：

CREATE TABLE productnotes
(
	note_id int NOT NULL AUTO_INCREMENT,
    pord_id char(10) NOT NULL,
    note_date datetime NOT NULL.
    note_text text NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
)ENGINE=MyISAM;

可以在创建表时指定FULLTEXT或在稍后指定（所有数据必须立即索引） 不要在导入数据时使用FULLTEXT 应线导入所有数据再修改表，定义FULLTEXT

进行全文本搜索

Match() 指定被搜索的列 Against() 指定要使用的搜索表达式

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

传递个Match()的值必须与FULLTEXT()定义中的相同。

除非使用BINARY方式，否则全文本搜索不区分大小写

全文本搜索对结果排序，具有较高等级的行先返回

SELECT note_text,
Match(note_text) Against('rabbit') AS rank
FROM productnotes;

显示所有行及他们的等级

使用扩展查询

（MySQL 4.1.1及更高版本） 例如找出所有提到anvils的注释，和与搜索有关的其他行，即使它们不包含这个词

#不使用扩展查询
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils');
+------------------------------------------------------------------------------+
| note_text                                                                    |
+------------------------------------------------------------------------------+
| Multiple custoer returns, anvils failing to drop fast enough or falling      |
| backwords on purchaser, Recomend that customer considers using havier        |
| anvils.                                                                      |
+------------------------------------------------------------------------------+
#使用扩展查询
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
+------------------------------------------------------------------------------+
| note_text                                                                    |
+------------------------------------------------------------------------------+
| Multiple custoer returns, anvils failing to drop fast enough or falling      |
| backwords on purchaser, Recomend that customer considers using havier        |
| anvils.                                                                      |
| Customer complaint: Sticks not individually wrapped, too easy to mistakenly  |
| detonate all at once. Recommend individual wrapping.                         |
| Customer compliant: Not heavy enouth to generate flying stars around heardof |
| victim. If veing purchased for dropping, recommend ANV02 or ANV03 instead.   |
| Please note that no returns will be accepted if safe opened using explosives.|
| Customer complaint: rabbit has been able to detect trap, food apparently     |
| less effective now.                                                          |
| Customer complaint: Circular hole in safe floor can apparently be easily cut |
| with handsaw.                                                                |
| Matches not include, recomend purchase of matches or detonator (item DTNTR)  |
+------------------------------------------------------------------------------+

返回7行，第一行包含anvils，因此等级最高，第二行与anvils无关，但包含第一行中的两个次，因此被检索出来。

布尔文本搜索

可以提供如下内容的细节：

• 要匹配的词
• 要排斥的次
• 排列提示
• 表达式分组
• 另外一些内容

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

例：匹配包含heavy但不包含任意以rope开始的词的行

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

*MySQL4.x版本中使用-ropes而不是-rope **

全文本布尔操作符

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不出现
>	包含，而且增加等级值
<	包含，且减少等级
()	把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~	取消一个词的排序值
*	取消一个词的排序值
""	定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

例：

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中的至少一个词的行

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和carrot，增加前者的等级，降低后者的等级

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
#匹配词safe和combination，降低后者的等级

全文本搜索的使用说明

• 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为 那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
• MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
• 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。 因此，MySQL规定了一条50%规则，如果一个词出现在50%以上 的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
• 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词 或者不出现，或者至少出现在50%的行中）。
• 忽略词中的单引号。例如，don't索引为dont。
• 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文 本搜索结果。
• 如前所述，仅在MyISAM数据库引擎中支持全文本搜索。