《MYSQL必知必会》阅读随记（十四 - 十八章）

最新推荐文章于 2021-10-23 15:10:34 发布

Artintel

最新推荐文章于 2021-10-23 15:10:34 发布

阅读量188

点赞数

分类专栏：学习文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_39274501/article/details/114828625

版权

学习专栏收录该内容

76 篇文章 4 订阅

订阅专栏

工具准备

MySQL Workbench 8.0

create schema crashcourse;

use crashcourse;

分别运行 create.sql 和 populate.sql

这两个脚本下载地址：

脚本地址
在这里插入图片描述

第十四章 - 使用子查询

子查询

SELECT 语句是 SQL 的查询。学到现在都是简单查询，即从单个数据库表中检索数据的单挑语句

查询（ query ） 任何 SQL 语句都是查询。但此属于一般指 SELECT 语句
SQL 还允许创建子查询（ subquery ），即嵌套在其他查询中的查询。

利用子查询进行过滤

目前这本数据使用的数据库表都是关系表。定单存储在两个表中。对于包含订单号、客户ID 、订单日期的每个订单, orders 表存储一行。各订单的物品存储在相关的 orderitems 表中。orders 表不存储客户信息。它只存储客户的 ID。实际的客户信息存储在 customers 表中。
如何列出订购物品 TNT2 的所有客户，如何检索

检索包含物品 TNT2 的所有订单的编号
检索具有前一步骤列出的订单编号的所有客户的 ID
检索前一步骤返回的所有客户 ID 的客户信息

上面每个步骤都可以单独作为一个查询来执行。可以把一条 SELECT 语句返回的结果用于另一条 SELECT 语句的 WHERE 字句

SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';

在这里插入图片描述
下一步，查询具有订单 20005 和 20007 的客户 ID。利用第 7 章介绍的 IN 字句，编写如下语句

SELECT cust_id
FROM orders
WHERE order_num IN (20005, 20007);

在这里插入图片描述

现在把第一个查询 (返回订单号的哪一个) 编程子查询组合两个查询 (原来如此!)

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

在 SELECT 语句中，子查询总时从内向外处理。在处理 SELECT 语句时，MySQL 实际上执行勒两个操作

首先，它执行下面的查询
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'
此查询返回两个订单号：20005 和 20009。然后，这两个值以 IN 操作符要求的逗号分隔的格式传递给外部查询的 WHERE 子句。外部查询编程：
SELECT cust_id FROM orders WHERE order_num IN (20005, 20007)

现在得到勒订购物品 TNT2 的所有客户的 ID。下一步是检索这些客户 ID 的客户信息。检索两列的 SQL 语句为：

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (10001, 10004);

在这里插入图片描述
可以把其中的 WHERE 字句转换为子查询而不是硬编码这些客户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'));

为了执行上述的 SELECT 语句，MySQL 实际上必须执行 3 条 SELECT 语句。最里边的子查询返回订单号列表，此列表用于其外面的子查询 WHERE 字句。最外面的子查询返回客户 ID 列表，此客户 ID 列表用于最外层查询的 WHERE 字句。最外层查询确实返回所需的数据。

列必须匹配 在 WHERE 字句中使用子查询，应该保证 SELECT 语句具有于 WHERE 字句中相同数目的列。通常，子查询将返回单个列并于单个列匹配，但如果需要也可以使用多个列

子查询和性能 使用子查询并不总是执行这种类型的数据检索的最有效的方法

作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示 customers 表中每个客户的订单总数。订单与相应的客户 ID 存储在 orders 表中.

为了执行这个操作，遵循下面的步骤

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

SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001;

为了对每个客户执行 COUNT(*) 计算，应该将 COUNT(*) 作为一个子查询。

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

在这里插入图片描述

orders 是一个计算字段，它是由圆括号中的子查询简历的。该子查询对检索出的每个客户执行一次。在此例中，该子查询执行了 5 次，因为检索出了 5 个客户。

相关子查询（ correlated subquery ） 涉及外部查询的子查询

这种类型的子查询称为相关子查询。任何时候只要列明可能有多义性，就必须使用这种语法（表明和列明由一个据点分隔）

错误示范
```
SELECT cust_name,
	   cust_state,
	(SELECT COUNT(*)
     FROM orders
     WHERE cust_id = cust_id) AS orders
FROM customers
ORDER BY cust_name;
```
返回结果不对，和之前的比较。因为有两个 cust_id 列，一个在 customers 中，另一个在 orders 中。如果不完全限定列名，MySQL 将假定你是对 orders 表中的 cust_id 进行自身比较。而 SELECT COUNT(*) FROM orders WHERE cust_id = cust_id ; 总时返回 orders 表中的订总数( 因为 MySQL 查看每个订单的 cust_id 是否与本身匹配，当然匹配。 )

第十五章 - 联结表

联结

SQL 最强大的功能之一就是能在数据检索查询的执行中**联结( join )**表。联结是利用 SQL 的 SELECT 能执行的最重要的操作，很好地理解联结及其语法是学习 SQL 的一个极为重要的组成部分

关系表

假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息
现在，假如有由同一供应商生产的多种物品，那么在何处存储供应商信息呢（供应商名、地址、联系方法等）？将这些数据与产品信息分开存储的理由如下：

因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费空间
如果供应商信息改变，比如电话号码变动，只需改动一次即可
如果有重复数据（即每种产品都存储供应商信息），很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用

关键在于，相同数据出现多次绝不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（ relational ））互相关联

在这个例子中，可以建立两个表，一个存储供应商信息，另一个存储产品信息。vendors 表包含所有供应商信息，每个供应商占一行，每个供应商应该有唯一标识。此标识称为 主键(primary key)，可以是供应商 ID 或其他

products 表只存储产品信息，它除了存储供应商 ID (vendors 表的主键) 外不存储其他供应商信息。vendors 表的主键又叫做 products 的外键，他将 vendors 表和 products 表关联，利用供应商 ID 能从 vendors 表中找出相应供应商的详细信息

外键( foreign key ) 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系
这样做的好处

供应商信息不重复，从而不浪费时间和空间
如果供应商信息变动，可以只更新 vendors 表中的单个记录，相关表中的数据不用改动
由于数据无重复，显然数据是一致的，这使得处理数据更简单

总之，关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好

可伸缩性（ scale ） 能够适应不断增加的工作量而不是白。设计良好的数据库或应用程序称之为可伸缩性好(scale well)

为什么使用联结

分解数据为多个表能更有效地存储，更方便地处理。
如果数据存储在多个表中，如何用单挑 SELECT 语句检索出数据？ — 联结

联结是一种机制，用来在一条 SELECT 语句中关联表，因此称之为联结。使用特殊地语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行

联结不是物理实体，在实际的数据库表中不存在，联结由 MySQL 根据需要建立，它存在于查询的执行当中

创建联结

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

在这里插入图片描述

这一段代码和之前的差别在于指定的两个列 ( prod_name 和 prod_price ) 在一个表中，而另一个列 (vend_name) 在另一个表中。

FROM 字句，和以往不同，列出了两个表，分别时 vendors 和 products 。它们就是这条 SELECT 语句联结的两个表的名字。这两个表用 WHERE 字句正确联结，WHERE 字句指示 MySQL 匹配 vendors 表中的 vend_id 和 products 中的 vend_id

WHERE 字句重要性

在一条 SELECT 语句中连接几个表时，相应的关系时在运行中构造的。在数据库表的定义中不存在能指示 MySQL 如何对表进行连接的东西。在联结两个表时，实际上做的时将第一个表中的每一行与第二个表中的每一行配对。WHERE 字句作为过滤条件，它只包含那些匹配给定条件（这里是连接条件）的行。没有 WHERE 子句，第一个表中的每个行将于第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起

笛卡尔积(catesian product) 没有连接条件的表返回的结果为笛卡尔积

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

在这里插入图片描述

不要忘了 WHERE 子句 应该保证所有联结都有 WHERE 子句。

内部联结

目前所用的联结称为等值联结( equijoin )，它基于两个表之间的相等测试。这种联结也成为内部联结。对于这种联结也可以使用稍微不同的语法来明确指定连接的类型。

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

此这里的 FROM 和之前不同，这里，两个表之间的关系是 FROM 子句的组成部分，以 INNER JOIN 指定。在使用这种语法时，连接条件用特定的 ON 子句而不是 WHERE 子句。

联结多个表

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

在这里插入图片描述

这里显示编号为 20005 的订单中的物品。订单物品存储在 orderitems 表中。每个产品按其产品 ID 存储，它引用 products 表中的产品。这些产品通过供应商 ID 联结到 vendors 表中相应的供应商，供应商 ID 存储在每个产品的记录中。这里的 FROM 子句列出了 3 个表，而 WHERE 子句中定义了这两个联结条件，而第三个联结条件用来过滤出订单 20005 中的产品

对于之前 14 章中的例子：

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 customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';

第十六章 - 创建高级联结

使用表别名

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

别名除了用于列明和计算字段外， SQL 还允许给表名起别名。这样做有两个主要理由：

缩短 SQL 语句
允许在单挑 SELECT 语句中多次使用相同的表

SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND o.order_num = o.order_num
  AND prod_id = 'TNT2';

使用不同类型的联结

目前，所学到的都指示被称为内部联结或者**等值联结(equijoin)**的简单联结。还有 3 种其他的联结，分别是自联结、自然连接和外部连接

自联结

如前所述，使用表别名的主要原因之一是能在单条 SELECT 语句中不止一次引用相同的表
例子:
假如发现某物品( 其 ID 为 DTNTR ) 存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产 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';

此查询种需要的两个表实际上是相同的表，因此 products 表在 FROM 子句种出现了两次。虽然这是完全合法的，但对 products 具有二义性，因为 MySQL 不知道你引用的是 products 表中的哪个实例

为了解决此问题，使用了表别名。products 的第一次出现为别名 p1, 第二次出现为别名 p2。现在可以将这些别名用作表明。例如，SELECT语句使用 p1 前缀明确地给出所需列地全名。如果不这这样，MySQL 将返回错误，因为分别存在两个别名为 prod_id、prod_name 的列。MySQL 不知道想要的是哪一个列（即使它们事实上是同一个列）。WHERE （通过匹配 p1 中的 vend_id 和 p2 中的 vend_id ）首先联结两个表，然后按第二个表中的 prod_id 过滤数据，返回所需的数据。

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

自然联结

无论何时对表进行连接，应该至少有一个列出现在不知一个表中（被联结的列）。标准的联结（内部联结）返回所有数据，甚至相同的列多次出现。自然连接排除多次出现，使每个列只返回一次
自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符( SELECT * ), 对所有其他表的列使用明确的子集来完成。

SELECT c.*, o.order_num, o.order_date,
	   oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'FB';

在这里插入图片描述

外部联结

许多联结将一个表中的行与两一个表中的行相关联。但有时后会需要包含没有关联行的那些行。例如，可能需要使用联结来完成一下工作：

对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户
列出所有产品以及订购数量，包括没有人订购的产品
计算平均销售规模，包括那些至今尚未下订单的客户
在上述例子中，联结包含了那些在相关表中没有关联的行。这种类型的联结成为外部联结

下面的 SELECT 语句给出了一个简单的内部联结。检索所有客户及其订单：

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;

在这里插入图片描述

这条 SELECT 语句使用了关键字 OUTER JOIN 来指定连接的类型（而不是在 WHERE 子句中指定）。但是，与内部连接关联的两个表中的行不同的是，外部连接还包括没有关联行的行。在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（ RIGHT 指出的是 OUTER JOIN 右边的表。。。）上面的例子使用 LEFT OUTER JOIN 从 FROM 子句的左边表（ customers 表）中选择所有的行。

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

在这里插入图片描述

使用带聚集函数的连接

聚集函数用来汇总数据。也可以与连接一起使用

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 语句使用 INNER JOIN 将 customers 和 orders 表互相关联。GROUP BY 子句按客户分组，因此，函数调用 COUNT(orders.order_num) 对每个客户的订单计数，将它作为 num_ord 返回

聚集函数也可以方便地与其他联结一起使用。

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;

在这里插入图片描述
使用左外部联结来包含所有客户，甚至包含哪些没有任何下订单地客户。结果显示也包含了客户 Mouse House, 它有 0 个订单

使用联结和联结条件

注意所使用的联结类型。一般使用内部联结，但使用外部联结也是有效的
保证使用正确的联结条件，否则将返回不正确的数据
应该总是提供联结条件，否则会得出笛卡尔积
在一个联结中可以包含多个表，甚至对于每个连接可以采用不同的连接类型。虽然这样做使合法的，一般也很有用，但应该在一起测试它们前，分别测试每个连接。这将使故障排除更为简单

第十七章组合查询

多数 SQL 查询都只包含从一个或多个表中返回数据的单条 SELECT 语句。MySQL 也允许置新过多个查询（多条 SELECT ）语句，并将结果作为单个查询结果集返回。这些组合插叙通常称为并（ union ）或符合查询( compound query )
有两种基本情况，其中需要使用组合查询：

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

组合查询和多个 WHERE 条件 多数情况下，组合相同表的两个查询完成的工作与具有多个 WHERE 子句条件的单条查询完成的工作相同。换句话说，任何具有多个 WHERE 子句的 SELECT 语句都可以作为一个组合查询给出。

创建组合查询

可用 UNION 操作符来组合数条 SQL 查询。利用 UNION，可给出多条 SELECT 语句，将它们的结果组合成单个结果集。

使用 UNION

单条 SELECT 语句

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 语句组成，语句中用 UNION 关键字分隔。UNION 指示 MySQL 执行两条 SELECT 语句，并把输出组合成单个查询结果集。
作为参考，这里有使用多条 WHERE 子句的相同查询：

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

在这里插入图片描述

UNION 规则

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

包含或取消重复的行

UNION 从查询结果集中自动去除勒重复的行。这是 UNION 的默认行为，但是如果需要，可以改变它。如果想返回所有匹配行可以使用 UNION ALL

对组合查询结果排序

SELECT 语句的输出用 ORDER BY 子句排序。在用 UNION 组合查询时，只能使用一条 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;

在这里插入图片描述

第十八章全文本搜索

理解全文本搜索

之前介绍了比如 LIKE 关键字，和基于文本的搜索作为正则表达式匹配列值。虽然这些搜索机制有用，但存在几个重要的限制

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

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

使用全文本搜索

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

启用全文本搜索支持

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

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

CREATE TABLE 语句定义表 productnotes 并列出它所包含的列即可。这些列中有一个名为 note_text 的咧，为了进行全文本搜索，MySQL 根据子句 FULLTEXT(note_text) 的指示对它进行索引。这里的 FULLTEXT 索引单个列，如果需要也可以指定多个列

在定义之后，MySQL 自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

可以在创建表时指定 FULLTEXT，或者在稍后指定（在这种情况下所有已有数据必须立即索引）

不要再导入数据时时候 FULLTEXT 根新索引要花事件，虽然不多，但毕竟要花。如果正在导入数据到一个信标，此时不应该启用 FULLTEXT 索引。应该首先导入所有数据，然后再修改表，定义 FULLTEXT 。这样有助于更快地导入数据

进行全文本搜索

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

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

在这里插入图片描述
此 SELECT 语句检索单个列 note_text。由于 WHERE 子句，一个全文本搜索被执行。Match(note_text) 指示 MySQL 针对指定的列进行搜索，Against('rabbit') 指定词 rabbit 作为搜索文本。由于有两行包含词 rabbit ，这两个行被返回

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

用 LIKE 子句也可以完成：

SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

和 LIKE 不同的是，全文本搜索返回以文本匹配的良好程度排序的数据。两个行都包含词 rabbit, 但包含词 rabbit 作为第 3 个词的行的等级比作为第 20 个词的行高。
演示：

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

在这里插入图片描述

排序多个搜索项 如果指定多个搜索项，则包含多数匹配此的那些行将具有比包含较少词的那些行搞得等级值

使用查询扩展

查询扩展用来设法放款所返回得全文本搜索结果得范围。如果想找出所有提到 anvils 的柱思华。只有一个注释包含词 anvils，但还想找出可能与搜索有关的所有其他行，即使它们不包含词 anvils。

这也是查询扩展的一项任务。在使用查询扩展时，MySQL 对数据和索引进行两边扫描来完成搜索

首先，进行一个基本的全文搜索，找出与搜索条件匹配的所有行
其次，MySQL 检查这些匹配行并选择所有有用的词
再其次，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);
```
这次返回了 7 行。第一行包含词 anvils，因此等级最高。第二行与 anvils ，但因为它包含第一行中的两个词（ customer 和 recommend ），所以也被检索出来。第 3 行也包含这两个相同的词，但它们再文本中的位置更靠后且分的更远，因此也包含这一行，但等级为第三。第三行确实没有设计 anvils

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

布尔文本搜索

MySQL 支持全文本搜索的另外一种形式，称为布尔方式(boolean mode)。

要匹配的词；
要排斥的词（如果某行包含这个词，则不反悔改行，即使它包含其他指定的词也是如此）
排列提示（指定某些词比其他词更重要，更重要的词等级更重要）；
表达式分组
另外一些内容

即使没有 FULLTEXT 索引也可以使用 布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义 FULLTEXT 索引，也可以使用它。但非常缓慢

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

在这里插入图片描述
此文本搜索检索包含 heavy 的所有行。但这一个例子因为实际上没有指定布尔操作符，结果和没有指定布尔方式的结果相同g

为了匹配包含 heavy 但不包含任何以 rope 开始的词的行，可以使用以下查询：

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

在这里插入图片描述
这次只返回一行。也以此仍然匹配词 heavy，但 -rope* 明确地指示 MySQL 排除包含 rope* （任何以 rope 开始地词，包括 ropes ）的行。所以排除了上一个例子种的第一行。

下标为所有布尔操作符

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

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 而不是匹配两个词 rabbit 和 bait

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <carrot' 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 指定了一条 50% 的规则，如果一个词出现了 50% 以上的行中，则将它作为一个非用词忽略。50% 规则步用于 IN BOOLEAN MODE
如果表中的行少于 3 行，则全文本搜索不反悔结果
忽略词中的单引号。例如，don`t 索引为 dont
不具有词分隔符的语言不能恰当地返回全文本搜索结果

Artintel

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
《MYSQL必知必会》阅读随记（十四 - 十八章）

文章目录工具准备第十四章 - 使用子查询子查询利用子查询进行过滤工具准备MySQL Workbench 8.0create schema crashcourse;use crashcourse;分别运行 create.sql 和 populate.sql这两个脚本下载地址：脚本地址第十四章 - 使用子查询子查询SELECT 语句是 SQL 的查询。学到现在都是简单查询，即从单个数据库表中检索数据的单挑语句查询（ query ）任何 SQL 语句都是查询。但此属于一般指 SELECT
复制链接

扫一扫