查询拓展

摘要

Mysql Version：5.7.36
官方文档：https://dev.mysql.com/doc/refman/ (opens new window)
样例表：下载

一：子查询

练习使用的库表关系ER图

例子：列出订购物品TNT2的所有客户

步骤1：检索包含物品TNT2的所有订单的编号

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';

步骤2：检索具有前一步骤列出的订单编号的所有客户的ID

SELECT cust_id FROM orders WHERE order_num IN 
	(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

步骤3：检索前一步骤返回的所有客户ID的客户信息

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'));

实际使用时由于性能的限制，不能嵌套太多的子查询

例子：显示用户信息以及对应订单总量

步骤1：从customers表中检索客户列表 步骤2：对于检索出的每个客户，统计其在orders表中的订单数目

SELECT cust_name, cust_state, 
	(SELECT COUNT(*) FROM orders 
		WHERE orders.cust_id = customers.cust_id) AS orders 
FROM customers ORDER BY cust_name;

分析：该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索出了5个客户。

用子查询测试和调试查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此；
用子查询建立（和测试）查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同；

1. 首先，建立和测试最内层的查询。
   
2. 然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。
   
3. 这时，再次测试它。对于要增加的每个查询，重复这些步骤。
   

二：连接表

2.1 连接

连接能够分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性。连接由MySQL根据需要建立，它存在于查询的执行当中。

SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;

WHERE子句的重要性

利用WHERE子句建立连接关系似乎有点奇怪，但实际上，有一个很充分的理由。一条SELECT语句中连接几个表时，相应的关系是在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进行连接的东西。没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。

笛卡尔积（cartesian product）

由没有连接条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT vend_name, prod_name, prod_price FROM vendors, products ORDER BY vend_name, prod_name;

运行结果

+----------------+----------------+------------+
| vend_name      | prod_name      | prod_price |
+----------------+----------------+------------+
| ACME           | .5 ton anvil   |       5.99 |
| ACME           | 1 ton anvil    |       9.99 |
| ACME           | 2 ton anvil    |      14.99 |
| ACME           | Bird seed      |      10.00 |
| ACME           | Carrots        |       2.50 |
| ACME           | Detonator      |      13.00 |
| ACME           | Fuses          |       3.42 |
| ACME           | JetPack 1000   |      35.00 |
| ACME           | JetPack 2000   |      55.00 |
| ACME           | Oil can        |       8.99 |
| ACME           | Safe           |      50.00 |
| ACME           | Sling          |       4.49 |
| ACME           | TNT (1 stick)  |       2.50 |
| ACME           | TNT (5 sticks) |      10.00 |
| Anvils R Us    | .5 ton anvil   |       5.99 |
| Anvils R Us    | 1 ton anvil    |       9.99 |
| Anvils R Us    | 2 ton anvil    |      14.99 |
| Anvils R Us    | Bird seed      |      10.00 |
| Anvils R Us    | Carrots        |       2.50 |
| Anvils R Us    | Detonator      |      13.00 |
| Anvils R Us    | Fuses          |       3.42 |
| Anvils R Us    | JetPack 1000   |      35.00 |
| Anvils R Us    | JetPack 2000   |      55.00 |
| Anvils R Us    | Oil can        |       8.99 |
| Anvils R Us    | Safe           |      50.00 |
| Anvils R Us    | Sling          |       4.49 |
| Anvils R Us    | TNT (1 stick)  |       2.50 |
| Anvils R Us    | TNT (5 sticks) |      10.00 |
| Furball Inc.   | .5 ton anvil   |       5.99 |
| Furball Inc.   | 1 ton anvil    |       9.99 |
| Furball Inc.   | 2 ton anvil    |      14.99 |
| Furball Inc.   | Bird seed      |      10.00 |
| Furball Inc.   | Carrots        |       2.50 |
| Furball Inc.   | Detonator      |      13.00 |
| Furball Inc.   | Fuses          |       3.42 |
| Furball Inc.   | JetPack 1000   |      35.00 |
| Furball Inc.   | JetPack 2000   |      55.00 |
| Furball Inc.   | Oil can        |       8.99 |
| Furball Inc.   | Safe           |      50.00 |
| Furball Inc.   | Sling          |       4.49 |
| Furball Inc.   | TNT (1 stick)  |       2.50 |
| Furball Inc.   | TNT (5 sticks) |      10.00 |
| Jet Set        | .5 ton anvil   |       5.99 |
| Jet Set        | 1 ton anvil    |       9.99 |
| Jet Set        | 2 ton anvil    |      14.99 |
| Jet Set        | Bird seed      |      10.00 |
| Jet Set        | Carrots        |       2.50 |
| Jet Set        | Detonator      |      13.00 |
| Jet Set        | Fuses          |       3.42 |
| Jet Set        | JetPack 1000   |      35.00 |
| Jet Set        | JetPack 2000   |      55.00 |
| Jet Set        | Oil can        |       8.99 |
| Jet Set        | Safe           |      50.00 |
| Jet Set        | Sling          |       4.49 |
| Jet Set        | TNT (1 stick)  |       2.50 |
| Jet Set        | TNT (5 sticks) |      10.00 |
| Jouets Et Ours | .5 ton anvil   |       5.99 |
| Jouets Et Ours | 1 ton anvil    |       9.99 |
| Jouets Et Ours | 2 ton anvil    |      14.99 |
| Jouets Et Ours | Bird seed      |      10.00 |
| Jouets Et Ours | Carrots        |       2.50 |
| Jouets Et Ours | Detonator      |      13.00 |
| Jouets Et Ours | Fuses          |       3.42 |
| Jouets Et Ours | JetPack 1000   |      35.00 |
| Jouets Et Ours | JetPack 2000   |      55.00 |
| Jouets Et Ours | Oil can        |       8.99 |
| Jouets Et Ours | Safe           |      50.00 |
| Jouets Et Ours | Sling          |       4.49 |
| Jouets Et Ours | TNT (1 stick)  |       2.50 |
| Jouets Et Ours | TNT (5 sticks) |      10.00 |
| LT Supplies    | .5 ton anvil   |       5.99 |
| LT Supplies    | 1 ton anvil    |       9.99 |
| LT Supplies    | 2 ton anvil    |      14.99 |
| LT Supplies    | Bird seed      |      10.00 |
| LT Supplies    | Carrots        |       2.50 |
| LT Supplies    | Detonator      |      13.00 |
| LT Supplies    | Fuses          |       3.42 |
| LT Supplies    | JetPack 1000   |      35.00 |
| LT Supplies    | JetPack 2000   |      55.00 |
| LT Supplies    | Oil can        |       8.99 |
| LT Supplies    | Safe           |      50.00 |
| LT Supplies    | Sling          |       4.49 |
| LT Supplies    | TNT (1 stick)  |       2.50 |
| LT Supplies    | TNT (5 sticks) |      10.00 |
+----------------+----------------+------------+
84 rows in set (0.00 sec)

2.2 内连接

上面例子中的连接也叫等值连接，或内部连接。对于这种连接可以使用稍微不同的语法来明确指定连接的类型。

下面sql与上面sql效果等同。

SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products 
ON vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;

ANSI SQL规范首选 INNER JOIN 语法

连接多表

例子：展示订单编号为2005的所有物品供应商信息

SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors 
WHERE orderitems.prod_id = products.prod_id AND products.vend_id = vendors.vend_id AND order_num = 20005;

2.3 自连接

例子：发现某物品（其ID为DTNTR）存在问题，因此想知道产生该物品的供应商生产的其他物品是否也存在这些问题。

-- 子查询
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';

用自连接而不用子查询
自连接通常作为外部语句用来替代从相同表中检索数据时使用的查询语句。虽然最终结果相同，但有时候处理连接远比处理子查询快的多

2.4 外连接

许多连接将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。

例子：检索所有客户及其订单，包括未下单

-- 内连接
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;

结果显示，外部连接还包括没有关联的行 —— 10002。在使用OUTER JOIN语法时，必须使用RIGHT 或 LEET 关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用 LEFT OUTER JOIN 从 FROM 子句的左边表（customers表）中选择所有行。为了从右边的表中选择所有行，应该使用 RIGHT OUTER JOIN ，如下例所示：

SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

2.5 带聚集函数

例子，检索所有客户所下的订单数

-- 内连接
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 JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;

这个例子使用左外部连接来包含所有客户，甚至包含那些没有任何下订单的客户。结果显示也包含了客户Mouse House，它有0个订单。

三：组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条 SELECT 语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

有两种基本情况，其中需要使用组合查询：

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

3.1 UNION

例子：

1）价格小于等于5的所有物品

2）供应商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);

1. UNION必须由两条或两条以上的SELECT语句组成，语句之间用关 键字UNION分隔
   
2. UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
   
3. 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）

3.2 UNION ALL

UNION 会从查询结果集中自动去除了重复的行（换句话说，它的行为与单条 SELECT 语句中使用多个 WHERE 子句条件一样）。

这是UNION的默认行为，但是如果需要，可以改变它。事实上，如果想返回所有匹配行，可使用 UNION ALL 而不是 UNION。

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);

3.3 组合查询排序

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条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 语句返回的所有结果。

四：全文搜索

常用的两个引擎 MyISAM 和 InnoDB，仅前者支持全文本检索。样例表中的 productnotes 表使用的就是 MyISAM 。

LIKE和正则的限制：

1. 性能 — 通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
2. 明确控制 — 使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。
3. 智能化的结果 — 虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配来排列它们）。类似，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。

所有这些限制以及更多的限制都可以用全文本搜索来解决。在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等。

4.1 启用全文本搜索

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

创建表时启用全文本搜索。CREATE TABLE 语句接受 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 KEY (`note_text`)
) ENGINE=MyISAM;

不要在导入数据时使用FULLTEXT
更新索引要花时间，虽然不是很多。如果正在导入数据到一个新表，此时不应该启用 FULLTEXT 索引。应该首先导入所有数据，然后再修改表，定义 FULLTEXT。这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）

4.2 进行全文搜索

在索引之后，使用两个函数 Match() 和 Against() 执行全文本搜索，其中 Match() 指定被搜索的列，Against() 指定要使用的搜索表达式。

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

全文本搜索的一个重要部分就是对结果排序。

使用完整的 Match() 说明
传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。
搜索不区分大小写
除非使用BINARY方式，否则全文搜索不区分大小写。

4.3 计算列

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

这里，在SELECT而不是WHERE子句中使用Match()和Against()。这使所有行都被返回（因为没有WHERE子句）。Match()和Against()用来建立一个计算列（别名为rank），此列包含全文本搜索计算出的等级值。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。正如所见，不包含词rabbit的行等级为0（因此不被前一例子中的WHERE子句选择）。确实包含词rabbit的两个行每行都有一个等级值，文本中词靠前的行的等级值比词靠后的行的等级值高。

这个例子有助于说明全文本搜索如何排除行（排除那些等级为0的行），如何排序结果（按等级以降序排序）。

排序多个搜索项
如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值。

全文本搜索提供了简单LIKE搜索不能提供的功能。而且，由于数据是索引的，全文本搜索相对来说比较快

4.4 查询拓展

查询扩展用来设法放宽所返回的全文本搜索结果的范围。

例如：想找出所有提到anvils的注释。只有一个注释包含词anvils，但还想找出与搜索结果相关的所有其他行，即使不包含词anvils

在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索：

1. 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
2. 其次，MySQL检查这些匹配行并选择所有有用的词；
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);

第二个DML语句比第一个的结果明显多，第二个DML的第一行包含词anvils，因此等级最高。第二行与anvils无关，但因为他包含第一行中的两个词（Customer和recommend），所以也被检索出来。第三行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此等级为第三。

表中的行越多，使用查询拓展返回的结果越好

4.5 布尔文本搜索

MySQL支持全文搜索的另外一种形式，称为布尔方式（boolean mode）。以布尔方式，可以提供关于如下内容的细节：

• 要匹配的词
• 要排斥的词
• 排列提示（指示某些词比其他词更重要，更重要的词等级更高）
• 表达式分组
• 另外一些内容

即使没有FULLTEXT索引也可以使用，布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它。慢操作，性能随着数据量增加而降低

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

此全文搜索检索包含词heavy的所有行（有两行）。其中使用了关键字 IN BOOLEAN MODE，但实际上没有指定布尔操作符，因此，其结果与没有指定布尔方式的结果相同。

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

SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' 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而非匹配两个词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);

排列而不排序：在布尔方式中，不按等级值降级排序返回的行

重要说明

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

五：参考文献

• 《MySQL 是怎样运行的 - 小孩子4919》
• 《MySQL 必知必会 - Ben Forta著（刘晓霞、钟鸣）译》
• 官方文档 (opens new window)