【数据库笔记】MySQL必知必会：chapter 10-18 创建计算字段 | 数据处理函数 | 汇总数据 | 分组数据 | 子查询 | 联结表 | 组合查询

文章目录

chapter 10 创建计算字段
chapter 11使用数据处理函数
chapter 12 汇总数据
chapter 13 分组数据
chapter 14 使用子查询
chapter 15&16 联结表
chapter 17 组合查询
chapter 18 全文本搜索

chapter 10 创建计算字段

10.1 计算字段

计算字段并不实际存在于数据库表中，计算字段是运行时在select语句内创建的。

字段（field）：基本与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

10.2 拼接字段

拼接（concatenate）：将值联结到一起构成单个值。

MySQL可以使用concat()函数来拼接两个列。而多数DBMS使用+或||来实现拼接。

select concat(vend_name,'(',vend_country,')')--各个串之间用逗号分隔
from vendors
order by vend_name;
--返回结果
--ACME(USA)

RTrim() 函数：删除右侧多余空格
LTrim() 函数：删除左侧多余空格
Trim() 函数：删除两侧多余空格

select concat(RTrim(vend_name),'(',RTrim(vend_country),')') as vend_title--使用别名（alias）
from vendors
order by vend_name;

别名（alias），有时也成为导出列（derived column）

10.3 执行算数计算

加减乘除（+ - * / ）

select  prod_id,
		quantity,
		item_price,
		quantity*item_price as expanded_price
from orderitems
where order_num=20005

如何测试计算

select提供了测试和试验函数与计算的一个很好的办法，虽然select通常用来从表中检索数据，但可以省略from子句以便简单地访问和处理表达式。例如，select 2*3 将返回6，select Trim('abc ') 将返回abc，select now()将返回当前日期和时间。

chapter 11使用数据处理函数

11.1 函数

函数没有SQL的可移植性强。能运行在多个系统上的代码称可移植的（portable）。

多数的SQL语句是可移植的，在SQL实现之间有差异时，这些差异不那么难处理。

而函数的可移植性却不强，几乎每种主要的DBMS的实现都支持其他实现不支持的函数，有时差异很大。

11.2 使用函数

大多数SQL实现支持以下类型的函数。

文本处理函数：用于处理文本串（如删除或填充值，转换值为大写/小写）的函数；
日期-时间处理函数：用于处理日期和时间值，并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性等）的函数；
数值处理函数：用于在数值数据上进行算数操作（如返回绝对值，进行代数运算）的函数；
系统函数：返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本的细节）的函数。

文本处理函数

RTrim() 函数：删除右侧多余空格
LTrim() 函数：删除左侧多余空格
Trim() 函数：删除两侧多余空格

select concat(RTrim(vend_name),'(',RTrim(vend_country),')') as vend_title--使用别名（alias）
from vendors
order by vend_name;

Upper() 函数：将文本转换成大写

select  vend_name,
		upper(vend_name) as vend_name_upcase
from vendors
order by vend_name;

Locate(substr, str) 函数：找出串的一个子串，返回substr在str中，第一次出现的位置

select locate('叶青大厦','叶青大厦'); -- 1 
select locate('叶青大厦','朝阳区叶青大厦');-- 4 
select locate('叶青大厦','朝阳区叶青'); -- 0 
select locate('叶青大厦','海淀区');--0

Substring() 函数：返回子串的字符

SUBSTRING(string,position);
SUBSTRING(string FROM position);

string参数：要提取子字符串的字符串
position参数：一个整数，用于指定子串的起始字符，position可以是正或负整数。

如果position为正，则SUBSTRING函数从字符串的开始处提取子字符串。例如，要从“MySQL SUBSTRING”字符串中获取子字符串：“SUBSTRING”，子串的位置必须从7开始。在这里插入图片描述

mysql> SELECT SUBSTRING('MYSQL SUBSTRING', 7);
+---------------------------------+
| SUBSTRING('MYSQL SUBSTRING', 7) |
+---------------------------------+
| SUBSTRING                       |
+---------------------------------+
1 row in set

请注意，如果position参数为零，则SUBSTRING函数返回一个空字符串。

mysql> SELECT SUBSTRING('MYSQL SUBSTRING', 0);
+---------------------------------+
| SUBSTRING('MYSQL SUBSTRING', 0) |
+---------------------------------+
|                                 |
+---------------------------------+
1 row in set

除了特定于MySQL的语法之外，可以使用SQL标准语法与FROM关键字一起调用SUBSTRING函数。

例如，以下语句使用SQL标准语法从"MySQL SUBSTRING"字符串中获取"SUBSTRING"：

mysql> SELECT SUBSTRING('MySQL SUBSTRING' FROM -10);
+---------------------------------------+
| SUBSTRING('MySQL SUBSTRING' FROM -10) |
+---------------------------------------+
|  SUBSTRING                            |
+---------------------------------------+
1 row in set

如果要指定要从字符串中提取的子字符串的长度，可以使用以下形式的SUBSTRING函数：

SUBSTRING(string,position,length);
SUBSTRING(string FROM position FOR length);

除了string和position参数之外，SUBSTRING函数还有一个额外的length参数。length是一个正整数，用于指定子字符串的字符数。

如果position和length的总和大于字符串的字符数，则SUBSTRING函数将返回一个从位置开始到字符串末尾的子串。

例如，要从"MySQL SUBSTRING"获取"MySQL"，请使用以下语句：

mysql> SELECT SUBSTRING('MySQL SUBSTRING',1,5);
+----------------------------------+
| SUBSTRING('MySQL SUBSTRING',1,5) |
+----------------------------------+
| MySQL                            |
+----------------------------------+
1 row in set

mysql> SELECT SUBSTRING('MySQL SUBSTRING' FROM 1 FOR 5);
+-------------------------------------------+
| SUBSTRING('MySQL SUBSTRING' FROM 1 FOR 5) |
+-------------------------------------------+
| MySQL                                     |
+-------------------------------------------+
1 row in set

mysql> SELECT SUBSTRING('MySQL SUBSTRING' FROM -15 FOR 5);
+---------------------------------------------+
| SUBSTRING('MySQL SUBSTRING' FROM -15 FOR 5) |
+---------------------------------------------+
| MySQL                                       |
+---------------------------------------------+
1 row in set

Soundex() 函数：返回串的soundex值
customers表中有一个顾客C，其联系名为Y.Lee，但是这是输入错的，正确的联系名为Y.Lie。此时如下检索不会出结果。

select  cust_name,
		cust_contact
from customers
where cust_contact='Y.Lie';
--返回结果
--无

如果使用Soundex()，它匹配所有发音类似于Y.Lie的联系名。

select  cust_name,
		cust_contact
from customers
where Soundex(cust_contact)='Y.Lie';
--返回结果
--C Y.Lee

表：常用文本处理函数

函数	说明
RTrim()	删除右侧多余空格
LTrim()	删除左侧多余空格
Trim()	删除两侧多余空格
Upper()	将文本转换成大写
Lower()	将文本转换成小写
Left()	返回串左边的字符
Right()	返回串右边的字符
Locate()	找出串的一个子串
Substring()	返回子串的字符
Length()	返回串的长度
Soundex()	返回串的soundex值

soundex
是一个将任何文本串转化为描述其语音表示的字母数字模式的算法。考虑了类似发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然soundex不是SQL概念，但是MySQL和多数DBMS一样，都提供对soundex的支持。

日期-时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效的排序或过滤，并节省物理存储空间。

一般应用程序不使用用来存储日期和时间的格式，因此日期和时间函数总是被用来读取统计和处理这些值。

情形1：基本的日期筛选

select cust_id,order_num
from orders
where order_date='2005-09-01';

存在问题：where order_date='2005-09-01' 可靠么？
order_date的数据类型为datetime，这种类型存储日期及时间值。存储的order_date=2005-09-01 11:30:05 ，此时order_date='2005-09-01'不能检索出实际行。
解决方法：使用Date()函数，令MySQL仅提取列的日期部分。如果要的只是日期，请使用Date()函数，这是一个良好的习惯！也有一个Time()函数，与之对应。

因此，更可靠的select语句为：

select cust_id,order_num
from orders
where Date(order_date)='2005-09-01';

情形2：检索2005年9月下的所有订单

select cust_id,order_num
from orders
where Date(order_date) between '2005-09-01' and '2005-09-30';

--另一种办法：不需要记住每个月中有多少天 或 不需要操心闰年2月的办法
select cust_id,order_num
from orders
where Year(order_date)=2005 and Month(order_date)=9;

情形3：日期时间运算

DATEDIFF

SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate;
SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate;
--返回结果
--   1
--  -1

DATE_ADD

DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
type 参数可以是下列值：

Type 值

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

YEAR_MONTH

假设我们有如下的表：

OrderId	ProductName	OrderDate
1	'Computer'	2008-12-29 16:25:46.635

现在，我们希望向OrderDate添加 2 天，这样就可以找到付款日期。

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders

OrderId	OrderPayDate
1	2008-12-31 16:25:46.635

Date_Format

DATE_FORMAT(date,format)

date 参数是合法的日期。format 规定日期/时间的输出格式。
可以使用的格式有：

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天（0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

Dec 29 2008 11:45 PM
12-29-2008
29 Dec 08
29 Dec 2008 16:25:46.635

表：常用日期-时间处理函数

函数	说明
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Now()	返回当前日期和时间
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
DayOfWeek()	对于一个日期，返回对应的是星期几
Time()	返回一个日期时间的时间部分
Date()	返回一个日期时间的日期部分
Day()	返回一个日期的天数部分
Month()	返回一个日期的月份部分
Year()	返回一个日期的年份部分
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Second()	返回一个时间的秒部分

对于MySQL使用的日期格式，无论你什么时候指定一个日期，不管是插入或更新值或用where子句进行过滤，日期格式必须为yyyy-mm-dd，这是首选的日期格式，排除了多义性。

年份应该总是用4位数字，这会更可靠。就算MySQL会把00-69处理为2000-2069，将70-99处理为1970-1999。

数值处理函数

数值处理函数仅处理数值数据，一般主要用于代数、三角或几何运算，因此没有串或日期-时间处理函数使用那么频繁。

mysql> SELECT MOD(63,8),MOD(120,10),MOD(15.5,3);
+-----------+-------------+-------------+
| MOD(63,8) | MOD(120,10) | MOD(15.5,3) |
+-----------+-------------+-------------+
|         7 |           0 |         0.5 |
+-----------+-------------+-------------+
1 row in set (0.03 sec)

表：常用数值处理函数

函数	说明
Abs()	返回绝对值
Exp()	返回指数值
Mod()	返回除操作的余数
Sqrt()	返回平方根
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Cos()	返回一个角度的余弦
Tan()	返回一个角度的正切
Pi()	返回圆周率

Reference：https://blog.csdn.net/qq_34579060/article/details/80283575

chapter 12 汇总数据

12.1 聚集函数

我们需要经常汇总数据，而不用把它们实际检索出来，以便于分析和报表的产生。
此类检索的例子有如下几种：

确定表中行数
确定表中行组的和
找出表列的最大值、最小值和均值

聚集函数（aggregate function）：运行在行组上，计算和返回单个值的函数。

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

select avg(prod_price) as avg_price
from products;
--avg()忽略列值为NULL的行

select count(*) as num_cust
from products;
--count(*)对表中行的数目进行计数,会把NULL行计入
--count(colunmn)会忽略NULL行

select max(prod_price) as max_price
from products;
--对非数值数据使用max()：虽然max()一般用来找出最大的数值或日期值,但MySQL允许用它来返回任意列中的最大值,包括返回文本列的最大值. 在用于文本数据时,如果数据按相应的列排序,则max()返回最后一行.
--max() 会忽略NULL行

select min(prod_price) as max_price
from products;
--对非数值数据使用min()：虽然min()一般用来找出最小的数值或日期值,但MySQL允许用它来返回任意列中的最小值,包括返回文本列的最小值. 在用于文本数据时,如果数据按相应的列排序,则min()返回最前面的行.
--min() 会忽略NULL行

select sum(item_price*quantity) as total_price
from oderitems
where order_num=20005;
--可在多列上进行计算
--sum() 会忽略NULL行

12.2 聚集不同值

以上5个聚集函数都可以如下使用：

对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；
只包含不同的值，指定Distinct参数；
Distinct必须+列名
Distinct技术上可以用于min() 和max() ，实际这样做没有价值~

ALL参数为默认，ALL参数不需要指定，因为它是默认行为。如果不指定Distinct参数，则假定为ALL参数。

select avg(distinct prod_price) as avg_price
from products
where vend_id=1003;

12.3 组合聚集函数

实际上，select语句可根据需要包含多个聚集函数。

select  count(*) as  num_items,
		min(prod_price) as price_min,
		max(prod_price) as price_max,
		avg(prod_price) as price_avg
from products;

chapter 13 分组数据

13.1 数据分组

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

13.2 创建分组

select 语句的group by实现分组。

select vend_id,count(*) as num_prods
from products
group by vend_id;

在具体使用group by子句之前，需要知道一些重要的规定：

group by子句可以包含任意数目的列；
如果group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总；
group by子句中列出的每个列都必须是 检索列 或 有效的表达式（但不能是聚集函数！）。如果在select 语句中使用表达式，则必须在group by子句中指定相同的表达式，不能使用别名；
除聚集计算语句外（AVG/MAX/MIN/SUM/COUNT），select 语句中每个列都必须在group by子句中给出；
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分成一组；
出现的顺序：where子句+group by子句+order by子句

使用 WITH ROLLUP

WITH ROLLUP：在group分组字段的基础上再进行统计数据。

例子：首先在name字段上进行分组，然后在分组的基础上进行某些字段统计，表结构如下：

CREATE TABLE `test` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(25) DEFAULT NULL COMMENT '标题',
  `uid` int(11) DEFAULT NULL COMMENT 'uid',
  `money` decimal(2,0) DEFAULT '0',
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('2', '国庆节', '2', '12', '周伯通');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('3', '这次是8天假哦', '3', '33', '老顽童');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('4', '这是Uid=1的第一条数据哦', '1', '70', '欧阳锋');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('5', '灵白山少主', '4', '99', '欧阳克');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('7', '九阴真经创始人', '3', '12', '小顽童');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('8', '双手互博', '2', '56', '周伯通');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('9', '销魂掌', '2', '19', '周伯通');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('10', '蛤蟆功', '1', '57', '欧阳锋');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('11', '绝杀掌', '3', '800', '小顽童');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('12', '九阴真经', '3', '84', '老顽童');

Id	title	uid	money	name
2	国庆节	2	12	周伯通
3	这次是8天假哦	3	33	老顽童
4	这是Uid=1的第一条数据哦	1	70	欧阳锋
5	灵白山少主	4	99	欧阳克
7	九阴真经创始人	3	12	小顽童
8	双手互博	2	56	周伯通
9	销魂掌	2	19	周伯通
10	蛤蟆功	1	57	欧阳锋
11	绝杀掌	3	800	小顽童
12	九阴真经	3	84	老顽童

分组统计：

SELECT name, SUM(money) as money FROM  test GROUP BY name WITH ROLLUP;

在这里插入图片描述
可以看到按照name分组后对money求和统计了。上面看到 null 1242, 如何搞个别名字段? 比如，总金额？？

coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

SELECT coalesce(name, '总金额'),name, SUM(money) as money FROM  test GROUP BY name WITH ROLLUP;

在这里插入图片描述

Reference：https://www.cnblogs.com/phpper/p/9384614.html

13.3 过滤分组

having子句非常类似where子句，实际上目前所学过所有的where子句都可以用having替代。

唯一的差别：where过滤行（分组前过滤），having过滤分组（分组后过滤）

select vend_id,count(*) as num_prods
from products
where prod_price>=10
group by vend_id
having count(*)>=2;

13.4 分组和排序

一般使用group by子句时，应该也给出order by子句，这是保证数据正确排序的唯一方法。千万不要依赖group by排序数据。

select vend_id,count(*) as num_prods
from products
where prod_price>=10
group by vend_id
having count(*)>=2;
order by vend_id

13.5 select子句顺序

select
from
where
group by
having
order by
limit

chapter 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');
--子查询总是由内向外
--执行顺序：
--(1) select order_num from orderitems where prod_id='TNT2';
--(2) select cust_id from orders where order_num in (20005,20007);
--(3) select cust_name,cust_contact from customers where cust_id in (10001,10004);

（1）对于嵌套的子查询数目没有限制，但由于性能限制，实际中不能嵌套过多的子查询；
（2）通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以多个列，select和where的列必须匹配；
（3）子查询一般与in操作符结合使用，也可以用=,<>

--子查询作为计算字段使用
select  cust_name,
		cust_state,
		(select count(*)
		from orders
		where orders.cust_id=customers.cust_id) as orders
from customers
order by cust_name

此处使用了完全限定列名where orders.cust_id=customers.cust_id。不这样做无法返回期望结果，如果不限定列名，MySQL将假定你是对orders表中的cust_id进行自身比较，那么总是返回订单总数。

chapter 15&16 联结表

外键（foreign key）：外键为某个表的一列，它包含另一个表的主键值，定义了两个表的关系。

可伸缩性（scale）：能适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好。

笛卡尔积（cartesian product）：由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表的行数×第二个表的行数。

叉联结（cross join）

在这里插入图片描述

【数据库笔记】MySQL&OracleJOIN方法图码总结

在这里插入图片描述

chapter 17 组合查询

17.1 组合查询

并（union）或复合查询（compound query）：组合查询，允许执行多个查询（多个select语句），并将结果作为单个查询结果集返回。

组合查询和多个where条件
多数情况下，任何具有多个where子句的select语句都可以作为一个组合查询给出。因此，应该试一下这两种技术，以确定对特定的查询哪一种性能更好。

17.2 创建组合查询（union）

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

`union`使用规则

union必须由两条以上的select语句组成，语句之间用关键词union分隔；
union中的每个查询必须包含相同的列、表达式或聚集函数
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的额日期类型）；
union自动去重，是默认行为，如果不想去重，可使用union all.union all 可以完成 where 无法完成的工作；
select语句的输出用order by子句排序。在用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`子句

chapter 18 全文本搜索

MySQL的全文本搜索功能进行高级的数据查询和选择。

18.1 理解全文本搜索

并非所有的引擎都支持全文本搜索
正如 chapter 21所述，MySQL支持几种基本的数据库引擎，并非所有的引擎都支持本书所描述的全文本搜索。两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，后者不支持。

这就是为什么本书创建的多数样例表使用InnoDB，而有一个杨丽表（productnotes表）却使用MyISAM的原因，如果你的应用中需要全文本搜索功能，应该记住这一点！

chapter 8 介绍了 like 关键字，它利用通配操作符匹配文本。能够查找包含特殊值或部分值的行（不论这些值位于列内的什么位置）。

chapter 9 介绍了基于文本的搜索作为正则表达式匹配列值。能够查找所续行的非常复杂的匹配模式。

虽然这些搜索机制非常有用，但是存在几个重要的限制。

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

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

18.2 使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。

在索引之后，select可match()和against()一起使用以实际执行搜索。

启用全文本搜索支持

一般在创建表时启用全文本搜索。create table语句（chapter 21）接受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 null,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;

在这里插入图片描述

进行全文本搜索

在索引之后，select可match()和against()一起使用以实际执行搜索。

match()指定被搜索的列，against()指定要使用的搜索表达式。

SELECT note_text 
FROM productnotes 
WHERE Match(note_text) Against ('rabbit');
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
| Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

在这里插入图片描述
上述搜索，可以用like子句完成：

select note_text
from productnotes
where note_text like '%rabbit%';

同样检索出两行，但是次序不同（虽然不总是出现这种情况）

上述两种select语句都不包含order by子句。后者（like子句）以不特别有用的顺序返回数据。前者（全文本搜索）返回以文本匹配良好程度的排序数据。

两个行都包含rabbit，但包含词rabbit作为第3个词的行（第一行）的等级比作为第20个词的行（第二行）高。这很重要。全文本搜索一个很重要的部分就是对结果排序。

select  note_text,
		Match(note_text) Against ('rabbit')
from productnotes

在这里插入图片描述

使用查询扩展

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

用例：你想找出所有提到anvils的注释，只有一个注释包含词anvils，但你还想找出可能与你的搜索有关的其他所有行，即使它们不包含词anvils。

首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
其次，MySQL检查这些匹配行并选择所有有用的词；
再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。

利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。

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

+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

只有一行包含词anvils，因此只返回一行。
下面是相同的搜索但是使用查询拓展。

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

+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. 
| Customer complaint: Sticks not individually wrapped, too easy to mistakenly detonate all at once. Recommendindividual wrapping.                         |
| Customer complaint:Not heavy enough to generate flying stars around head of victim. If being 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 included, recommend purchase of matches or detonator (item DTNTR).                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.01 sec)

在这里插入图片描述

布尔文本搜索

MySQL支持全文本搜索的另外一种形式，称为布尔方式。布尔方式使用细节如下：

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

即使没有FULLTEXT索引也可以使用
布尔表达式不同于之前使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它，但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）

1.检索包含heavy词的所有行

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

+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.                                     |
| Customer complaint:Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

在这里插入图片描述
2.匹配heavy但不包含任意以rope开始的词的行

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

+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

在这里插入图片描述

下面举几个例子说明这些操作符如何使用：
3.检索包含词rabbit和词bait的行

SELECT note_text 
FROM productnotes 
WHERE Match(note_text) Against ('+rabbit +bait' IN BOOLEAN MODE);

+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.检索至少包含rabbit和bait一个词的行

没有指定操作符，这个搜索匹配至少包含rabbit和bait一个词的行。

SELECT note_text 
FROM productnotes 
WHERE Match(note_text) Against ('rabbit bait' IN BOOLEAN MODE);
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

5.检索包含短语rabbit bait的行

匹配短语“rabbit bait”，而不是两个词rabbit 和bait。

SELECT note_text 
FROM productnotes 
WHERE Match(note_text) Against ('"rabbit bait"' IN BOOLEAN MODE);

+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

6.匹配rabbit和carrot，提高前者的等级降低后者的等级

匹配rabbit 和bait，增加前者的等级，降低后者的等级。

SELECT note_text 
FROM productnotes 
WHERE Match(note_text) Against (">rabbit <carrot" IN BOOLEAN MODE);

+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

7.匹配safe和combination，降低后者的等级

SELECT note_text 
FROM productnotes 
WHERE Match(note_text) Against ("+safe +(<combination)" IN BOOLEAN MODE);

+---------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Safe is combination locked, combination not provided with safe. This is rarely a problem as safes are typically blown up or dropped by customers. |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

全文本搜索使用说明

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