第17章 组合查询

一、组合查询基础

1.1 组合查询的定义与用途：组合查询也叫并（union）或复合查询，指在MySQL中执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。主要用于两种情况：一是在单个查询中从不同的表返回类似结构的数据；二是对单个表执行多个查询，按单个查询返回数据 。在实际应用场景中，比如需要从不同表获取相关数据进行统一展示，或者对同一表不同条件的数据进行合并处理时，组合查询就能发挥重要作用。

1.2 与多个WHERE条件的对比：多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询类似，但两者在性能上可能有所不同。因此，在具体使用时，建议对两种技术进行测试，以确定哪种更适合特定的查询需求 。

二、创建组合查询

2.1 使用UNION操作符：使用UNION操作符可组合数条SELECT语句，将它们的结果组合成单个结果集。使用时，只需在各条SELECT语句之间放上关键字UNION 。例如，要获取价格小于等于5的所有物品列表，以及供应商1001和1002生产的所有物品（不考虑价格），可以这样写：

-- 选择价格小于等于5的物品信息
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
-- 选择供应商1001和1002生产的物品信息
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

在这个例子中，两条SELECT语句分别查询满足不同条件的数据，通过UNION将它们的结果合并为一个结果集。需要注意的是，作为参考，使用多个WHERE子句也能实现类似功能：

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5 OR vend_id IN (1001, 1002);

在简单场景下，使用UNION可能比使用WHERE子句更复杂，但在更复杂的过滤条件或从多个表检索数据的情况下，UNION可能使处理更简单。
2.2 UNION规则：在使用UNION进行组合查询时，需要遵循以下规则：
- UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。例如，如果组合4条SELECT语句，将要使用3个UNION关键字。
- UNION中的每个查询必须包含相同的列、表达式或聚集函数，不过各个列不需要以相同的次序列出。
- 列数据类型必须兼容，即类型不必完全相同，但必须是DBMS可以隐含地转换的类型，例如不同的数值类型或不同的日期类型。
2.3 包含或取消重复的行：UNION默认会从查询结果集中自动去除重复的行，其行为类似于单条SELECT语句中使用多个WHERE子句条件。如果想返回所有匹配行，包括重复行，可使用UNION ALL而不是UNION 。例如：

-- 使用UNION ALL保留重复行
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

在这个例子中，使用UNION ALL后，结果集中会包含所有匹配的行，包括重复的行。这在某些需要保留所有数据的场景中非常有用。
2.4 对组合查询结果排序：在用UNION组合查询时，只能使用一条ORDER BY子句，且它必须出现在最后一条SELECT语句之后。这是因为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;

在这条语句中，ORDER BY子句对整个组合查询结果按vend_id和prod_price进行排序。

三、小结

本章重点介绍了如何使用UNION操作符来组合SELECT语句，通过组合查询，可以将多条查询的结果合并为一个结果集，无论是包含还是不包含重复行。UNION操作符在简化复杂的WHERE子句以及从多个表中检索数据方面具有显著优势，合理运用它能够提高数据查询的效率和灵活性。

实战案例

1. 实战数据准备

• 建表语句

-- 创建电子产品表
CREATE TABLE electronics (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(50),
    price DECIMAL(10, 2),
    category VARCHAR(20)
);
-- 创建家具产品表
CREATE TABLE furniture (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(50),
    price DECIMAL(10, 2),
    category VARCHAR(20)
);

- **插入数据语句**

-- 向电子产品表插入数据
INSERT INTO electronics (product_name, price, category) 
VALUES ('Smartphone', 599.99, 'Electronics'), ('Tablet', 399.99, 'Electronics'), ('Laptop', 999.99, 'Electronics');
-- 向家具产品表插入数据
INSERT INTO furniture (product_name, price, category) 
VALUES ('Chair', 149.99, 'Furniture'), ('Table', 299.99, 'Furniture'), ('Sofa', 799.99, 'Furniture');

2. 题目
   检索价格低于500的所有产品（包括电子产品和家具产品）的名称和价格，并按价格升序排序。
3. 解析与脚本答案

• 解析：本题需要从两个不同的表（electronics和furniture）中获取数据，且满足价格低于500的条件，最后按价格升序排序。可以使用UNION操作符将两个表中符合条件的数据合并，并使用ORDER BY子句进行排序。
• 脚本答案

-- 从电子产品表中选择价格低于500的产品名称和价格
SELECT product_name, price
FROM electronics
WHERE price < 500
UNION
-- 从家具产品表中选择价格低于500的产品名称和价格
SELECT product_name, price
FROM furniture
WHERE price < 500
-- 按价格升序排序
ORDER BY price ASC;

在这个脚本中，首先分别从electronics表和furniture表中筛选出价格低于500的产品名称和价格，然后使用UNION将两个结果集合并，最后通过ORDER BY price ASC对合并后的结果按价格进行升序排序，从而满足题目要求。

第18章 全文本搜索

一、全文本搜索基础

1. 1.1 全文本搜索的必要性：在数据查询中，通配符和正则表达式匹配虽有用，但存在性能、明确控制和智能化结果等方面的限制。例如，通配符和正则表达式匹配通常需尝试匹配表中所有行，且极少使用表索引，导致搜索耗时；难以精确控制匹配规则；无法根据匹配程度对结果进行智能化排序 。而全文本搜索能有效解决这些问题，它通过创建指定列中各词的索引，可快速确定哪些词匹配、哪些行包含它们以及匹配频率等 。
2. 1.2 引擎支持情况：并非所有MySQL的数据库引擎都支持全文本搜索。常见的MyISAM引擎支持全文本搜索，而InnoDB引擎不支持。这就是本书部分样例表（如productnotes表）使用MyISAM引擎的原因。在实际应用中，若需要全文本搜索功能，需选择正确的引擎类型 。

二、使用全文本搜索

1. 2.1 启用全文本搜索支持：一般在创建表时启用全文本搜索，使用CREATE TABLE语句的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,
    PRIMARY KEY (note_id),
    FULLTEXT (note_text)
) ENGINE=MyISAM;

在上述代码中，FULLTEXT (note_text)指示MySQL对note_text列进行索引。定义后，MySQL会自动维护该索引，在数据增加、更新或删除时，索引也会相应更新 。也可在创建表后指定FULLTEXT索引，但需注意在导入数据时，为提高效率，应先导入数据，再定义FULLTEXT索引 。
2. 2.2 进行全文本搜索：索引建立后，使用Match()和Against()函数执行全文本搜索。Match()指定被搜索的列，Against()指定搜索表达式 。例如：

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

上述语句检索note_text列中包含rabbit的行。全文本搜索不区分大小写（除非使用BINARY方式），且会对结果按文本匹配的良好程度排序，匹配词在文本中靠前的行等级更高，会先返回 。例如，通过以下查询可查看匹配等级：

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

3. 2.3 使用查询扩展：查询扩展用于放宽全文本搜索结果的范围。MySQL会对数据和索引进行两遍扫描：第一遍进行基本搜索，找出匹配行；第二遍检查匹配行并选择有用的词；第三遍使用原来的条件和有用的词再次搜索 。例如：

-- 基本全文本搜索，未使用查询扩展
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils');
-- 使用查询扩展的全文本搜索
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

使用查询扩展能找出可能相关的结果，即使这些结果不精确包含所查找的词。但它也会增加返回的行数，可能包含一些实际不需要的行，且表中的行越多，使用查询扩展返回的结果越好 。该功能仅在MySQL 4.1.1或更高级的版本中可用。
4. 2.4 布尔文本搜索：MySQL支持的布尔文本搜索方式，可提供更详细的搜索控制，如指定要匹配的词、要排斥的词、排列提示、表达式分组等 。即使没有定义FULLTEXT索引也可使用，但性能会随数据量增加而降低 。例如：

-- 检索包含heavy但不包含任意以rope开始的词的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

在布尔文本搜索中，支持多种操作符，如+表示包含，-表示排除，*是截断操作符等 。详细操作符及说明如下表：

- 索引全文本数据时，短词（3个或3个以下字符的词）会被忽略且从索引中排除，该数目可根据需要更改。
- MySQL带有内建的非用词列表，在索引全文本数据时会被忽略，可根据需要覆盖该列表。
- 若一个词出现在50%以上的行中，MySQL会将其作为非用词忽略（`IN BOOLEAN MODE`方式除外）。
- 表中的行数少于3行时，全文本搜索不返回结果。
- 忽略词中的单引号，如`don't`索引为`dont`。
- 不具有词分隔符（如日语和汉语）的语言不能恰当地返回全文本搜索结果。
- MySQL全文本搜索目前不支持邻近操作符，但未来版本有支持计划。

三、小结

本章详细介绍了MySQL的全文本搜索功能，包括其必要性、不同的搜索方式（基本搜索、查询扩展、布尔文本搜索）以及使用时的注意事项。通过合理运用全文本搜索，能够更高效地进行复杂的数据查询和选择，满足多样化的业务需求。

实战案例

1. 实战数据准备

• 建表语句

CREATE TABLE articles (
    article_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT (content)
) ENGINE=MyISAM;

- **插入数据语句**

INSERT INTO articles (title, content) VALUES
('Article 1', 'MySQL is a popular open - source database. It offers various features like full - text search.'),
('Article 2', 'Full - text search in MySQL can be used to find relevant information quickly. It is useful for applications with large text - based data.'),
('Article 3', 'Learn how to implement full - text search in your MySQL - based projects. This includes using Match and Against functions.');

2. 题目
   检索内容中包含“MySQL”和“full - text search”的文章标题，使用布尔文本搜索，并按相关性降序排序（假设相关性由匹配词的数量和位置决定）。
3. 解析与脚本答案

• 解析：本题需使用布尔文本搜索，在articles表的content列中查找同时包含“MySQL”和“full - text search”的文章，并按相关性降序排序。可使用Match()和Against()函数结合布尔操作符实现。
• 脚本答案

SELECT title
FROM articles
WHERE Match(content) Against('+MySQL +"full - text search"' IN BOOLEAN MODE)
ORDER BY Match(content) Against('+MySQL +"full - text search"' IN BOOLEAN MODE) DESC;

在上述脚本中，Match(content) Against('+MySQL +"full - text search"' IN BOOLEAN MODE)用于指定搜索条件，要求content列中必须同时包含“MySQL”和“full - text search”。ORDER BY Match(content) Against('+MySQL +"full - text search"' IN BOOLEAN MODE) DESC则按相关性降序对搜索结果进行排序，确保相关性高的文章标题排在前面。