ORDER BY var [DESC]
LIMIT [start,] num
说明:
子句 |
说明 |
是否必须使用 |
SELECT |
要返回的列或表达式 |
是 |
FROM |
从中检索数据的表 |
仅从表中选择数据时使用 |
WHERE |
行级过滤 |
否 |
GROUP BY |
分组说明 |
仅在按组计算聚集时使用 |
HAVING |
组级过滤 |
否 |
ORDER BY |
输出排序顺序 |
否 |
LIMIT |
要检索的行数 |
否 |
使用子查询
子查询,即嵌套在其他查询中的查询。
例如:现在需要列出订购物品TNT2的所有客户:
格式化SQL 包含子查询的SELECT语句难以阅读和调试,特别是它们较为复杂时更是如此。如上所示把子查询分解为多行并且适当地进行缩进,能极大地简化子查询的使用。
尤其要注意这个格式化的过程,因为外部查询实际上需要的是内部查询返回一个,
分隔的tuple。
可见,在WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。
虽然子查询一般与IN操作符结合使用,但也可以用于测试等于(=)、不等于(<>)等。
子查询和性能 这里给出的代码有效并获得所需的结果。但是,使用子查询并不总是执行这种类型的数据检索的最有效的方法。
作为计算字段使用子查询
计算字段,即跟着select语句的一些字段,是使用子查询的另外一种方式。
例:假如需要显示customers表中每个客户的订单总数,为此,我们必须执行两个步骤:
- 从customers表中检索客户列表。
- 对于检索出的每个客户,统计其在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;
注意这里被嵌套的查询where
子句需要使用完全限定的列名。
相关子查询(correlated subquery) 涉及外部查询的子查询。
此外,如上给出的sql
语句并不是最高效的办法。后续会进行改进。
逐渐增加子查询来建立查询 用子查询测试和调试查询很有技巧性,特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立(和测试)查询的最可靠的方法是逐渐进行,这与MySQL处理它们的方法非常相同。首先,建立和测试最内层的查询。然后,用硬编码数据建立和测试外层查询,并且仅在确认它正常后才嵌入子查询。这时,再次测试它。对于要增加的每个查询,重复这些步骤。这样做仅给构造查询增加了一点点时间,但节省了以后(找出查询为什么不正常)的大量时间,并且极大地提高了查询一开始就正常工作的可能性。
如上提供了SQL
的调试办法。
联结表
将不同表中的数据合并就是联结。
维护引用完整性 重要的是,要理解联结不是物理实体。换句话说,它在实际的数据库表中不存在。联结由MySQL根据需要建立,它存在于查询的执行当中。在使用关系表时,仅在关系列中插入合法的数据非常重要。回到这里的例子,如果在products表中插入拥有非法供应商ID(即没有在vendors表中出现)的供应商生产的产品,则这些产品是不可访问的,因为它们没有关联到某个供应商。为防止这种情况发生,可指示MySQL只允许在products表的供应商ID列中出现合法值(即出现在vendors表中的供应商)。这就是维护引用完整性,它是通过在表的定义中指定主键和外键来实现的。
内连接(自然连接)的两种使用方法:
如果我们要查询供应商所有的商品及其价格,我们可以:
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
还可以:
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;
其中前者是通常的where子句写法,后者明确的表示这是个内连接。通过ON
子句传递条件。
特别要注意是:where子句和on子句传递的条件必须不能忘记,否则直接返回一个笛卡尔积。
不要忘了WHERE子句 应该保证所有联结都有WHERE子句,否则MySQL将返回比想要的数据多得多的数据。同理,应该保证WHERE子句的正确性。不正确的过滤条件将导致MySQL返回
不正确的数据。
叉联结 有时我们会听到返回称为叉联结(cross join)的笛卡儿积(cartesian product)的联结类型。
反过来看一下子查询中查询订购产品TNT2的客户那个例子:
# 子查询
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";
多做实验 正如所见,为执行任一给定的SQL操作,一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此,有必要对不同的选择机制进行实验,以找出最适合具体情况的方法。
创建高级联结
MySQL不仅支持给计算字段取别名,同时也支持给表取别名。这样做主要有以下两个理由:
- 缩短SQL语句;
- 允许单条SELECT语句中多次使用相同的表。
很容易想到的一点是:表别名只在查询执行中使用,与列别名不一样,表别名不返回到客户机。
不同类型的连接:
之前谈到过内连接,这里介绍其他的三种连接: 自连接,自然连接,外连接。其中外连接包括左外连接,右外连接,但是MySQL并没有提供全连接的语法支持。不过是可以通过某些方法模拟出来全连接的。
自连接:
自连接通过给同一张表取别名的方式,多次连接同一张表。并且进行条件运算。如:
注意: MySQL中进行连接时,如果要选择的计算字段语义不明,必须给定表名限定的字段名。如上所示。同时,既可以使用from JOIN on...
也可以使用from ... where ...
。
用自联结而不用子查询 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的,但有时候处理联结远比处理子查询快得多。应该试一下两种方法,以确定哪一种的性能更好。
自然连接:
无论何时对表进行联结,应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结(笛卡尔积)返回所有数据,甚至相同的列多次出现。自然联结排除多次出现,使每个列只返回一次。
怎样完成这项工作呢?答案是,系统不完成这项工作,由你自己完成它。自然联结是这样一种联结,其中你只能选择那些唯一的列。这一般是通过对表使用通配符(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 customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;
MySQL左外连接,右外连接的语法支持分别是 LEFT JOIN
和RIGHT JOIN
。
没有=操作符 MySQL不支持简化字符=和=*的使用,这两种操作符在其他DBMS中是很流行的。
外部联结的类型 存在两种基本的外部联结形式:左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说,左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。因此,两种类型的外部联结可互换使用,而究竟使用哪一种纯粹是根据方便而定
此外,聚集函数也可以在各种连接中使用。
使用连接和链接条件:
- 注意所使用的联结类型。一般我们使用内部联结,但使用外部联结也是有效的。
- 保证使用正确的联结条件,否则将返回不正确的数据。
- 应该总是提供联结条件,否则会得出笛卡儿积。
- 在一个联结中可以包含多个表,甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试它们前,分别测试每个联结。这将使故障排除更为简单。
组合查询
组合查询/复合查询:或者说,不同查询结果的并(UNION)。
两种情况:
- 在单个查询中从不同的表返回类似结构的数据;
- 对单个表执行多个查询,按单个查询返回数据。
组合查询和多个WHERE条件 多数情况下,组合相同表的两个查询完成的工作与具有多WHERE子句条件的单条查询完成的工作相同。换句话说,任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出,在以下段落中可以看到这一点。这两种技术在不同的查询中性能也不同。因此,应该试一下这两种技术,以确定对特定的查询哪一种性能更好。
可用UNION操作符来组合数条SQL查询。利用UNION,可给出多条SELECT语句,将它们的结果组合成单个结果集。例如:筛选价格小于等于5的所有物品的一个列表,而且还想包括供应商1001和1002生产的所有物品(不考虑价格)。虽然可以很简单的用where子句完成,但这里也可以选用UNION操作符:
使用UNION操作符的一些规则:
- UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔(因此,如果组合4条SELECT语句,将要使用3个UNION关键字)。
- UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)。
- 列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含地转换的类型(例如,不同的数值类型或不同的日期类型)。
此外,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)
使用UNION ALL
,MySQL不取消重复的行。
UNION与WHERE UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL为UNION的一种形式,它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现(包括重复行),则必须使用UNION ALL而不是WHERE。
最后,如果想要对UNION的结果排序,只能在最后一行加一行ORDER BY
对整个表进行排序。不能以一种方式对一部分排序,以另一种方式对另一部分排序。并且,前面提到过的,可以对不同的表应用组合查询,而不仅限于例子中的单表。
之前在创建高级连接的时候提到过,mysql并没有语法层面的对full join的支持,学完UNION后,我们看看怎样实现一个FULL JOIN
:
可能第一次会是这样:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
然后这就没问题了吗?不对,一个比较通用的FULL JOIN
是不会自动去重的,所以第二次你可能会写出这样的code:
(The query above works for special cases where a FULL OUTER JOIN operation would not produce any duplicate rows. The query above depends on the UNION
set operator to remove duplicate rows introduced by the query pattern. We can avoid introducing duplicate rows by using an anti-join pattern for the second query, and then use a UNION ALL set operator to combine the two sets. In the more general case, where a FULL OUTER JOIN would return duplicate rows, we can do this:)
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL
但是这样就完了吗?不对,可以看到最后一行做了判断t1.id IS NULL
,对问题来了,怎么保证t1.id
是允许为空的,上面那个查询一定会是语法正确的吗?
参考:
Stack Overflow How to do a FULL OUTER JOIN in MySQL?
Stack Overflow Why does MySQL report a syntax error on FULL OUTER JOIN?
MySQL Reference Manual
全文本搜索
MySQL是一个多引擎架构的关系数据库。但是并非所有的引擎都支持全文本搜索。
并非所有引擎都支持全文本搜索 MySQL支持几种基本的数据库引擎。并非所有的引擎都支持全文本搜索。两个最常使用的引擎为MyISAM和InnoDB,前者支持全文本搜索,而后者不支持。这就是为什么例子中绝大多数表格创建时使用的都是InnoDB , 而有一个样例表(productnotes表)却使用MyISAM的原因。如果你的应用中需要全文本搜索功能,应该记住这一点。
注意,在新版MySQL5.6.24中也允许在InnoDB上建全文本索引了。
那为什么有了通配操作符和正则表达式之后还需要全文本搜索呢?这是因为虽然这些机制非常有用,但有几个重要的限制:
- 性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行(而且这些搜索极少使用表索引)。因此,由于被搜索行数不断增加,这些搜索可能非常耗时。
- 明确控制——使用通配符和正则表达式匹配,很难(而且并不总是能)明确地控制匹配什么和不匹配什么。例如,指定一个词必须匹配,一个词必须不匹配,而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。(我表示怀疑,正则表达式是万能的)
- 智能化的结果——虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索,但它们都不能提供一种智能化的选择结果的方法。例如,一个特殊词的搜索将会返回包含该词的所有行,而不区分包含单个匹配的行和包含多个匹配的行(按照可能是更好的匹配来排列它们)。类似,一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。
接下来就是重头戏了:所有这些限制以及更多的限制都可以用全文本搜索来解决。在使用全文本搜索时,MySQL不需要分别查看每个行,不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引,搜索可以针对这些词进行。这样,MySQL可以快速有效地决定哪些词匹配(哪些行包含它们),哪些词不匹配,它们匹配的频率,等等。
为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断地重新索引。在对表列进行适当设计后,MySQL会自动进行所有的索引和重新索引。在索引之后,SELECT可与Match()和Against()一起使用以实际执行搜索。
一般在创建表时启用全文本搜索。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 NULL ,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;
MySQL 根据子句FULLTEXT(note_text)对该列进行索引。如果需要,也可以索引多个列。
在定义之后,MySQL将会自动维护该索引。在增删改的时候,索引会自动更新。可以在创建表时指定FULLTEXT,也可以稍后指定。
显然,这解决了开头提出的第一个问题--性能。
不要在导入数据时使用FULLTEXT 更新索引要花时间,虽然不是很多,但毕竟要花时间。如果正在导入数据到一个新表,此时不应该启用FULLTEXT索引。应该首先导入所有数据,然后再修改表,定义FULLTEXT。这样有助于更快地导入数据(而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间)。
如果可以,尽量先导入数据,然后立即建立索引。
全文本索引的三种用途:
基础功能:
利用Match()和Against()两个函数执行全文本搜索,其中Match指定被搜索的列,Against()指定要使用的搜索表达式。注意这里是可以使用对多列使用全文本搜索的。比如对一篇文章进行全文本搜索的时候,我们不光关心它的内容,对标题也会进行全文本搜索。
使用完整的Match() 说明 传递给Match() 的值必须与FULLTEXT()定义中的相同。如果指定多个列,则必须列出它们(而且次序正确)。(好像在现在版本里面只需要传递个Match()的值与定义相同即可,顺序倒没有那么重要了。)
搜索不区分大小写 除非使用BINARY方式,否则全文本搜索不区分大小写(没有找到如何区分大小写的办法)。后续,找到了,建表的时候将字符编码方式从utf8改成utf8bin即可。其余的字符编码方式同理。
那怎么实现开头提出的第三个问题呢?
我们看全文索引怎么对搜索的结果进行排序的。
实例:
SELECT note_text,
Match(note_text) Against("rabbit") AS sequence
FROM productnotes;
结果:
分析:
这里,在SELECT而不是WHERE子句中使用Match()和Against()。这使所有行都被返回(因为没有WHERE子句)。Match()和Against()用来建立一个计算列(别名为rank),此列包含全文本搜索计算出的等级值。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。正如所见,不包含词rabbit的行等级为0(因此不被前一例子中的WHERE子句选择)。确实包含词rabbit的两个行每行都有一个等级值,文本中词靠前的行的等级值比词靠后的行的等级值高。
这个例子说明了全文搜索怎么排除行(排除那些相关等级为0的行),怎么排序结果(按等级降序排序)。
排序多个搜索项 如果指定多个搜索项,则包含多数匹配词的那些行将具有比包含较少词(或仅有一个匹配)的那些行高的等级值。
使用查询扩展:
查询扩展用来设法放宽所返回的全文本搜索结果的范围。
敲字员消极怠工。。。
布尔文本搜索:
MySQL全文搜索支持两种模式:默认的称为自然语言模式(IN NATURAL LAGUAGE MODE
),再者就是布尔文本搜索(IN BOOLEAN MODE
)。
布尔文本搜索提供以下的内容细节:
- 要匹配的词;
- 要排斥的词(如果某行包含这个词,则不返回该行,即使它包含其他指定的词也是如此);
- 排列提示(指定某些词比其他词更重要,更重要的词等级更高);
- 表达式分组;
- 另外一些内容。
即使没有FULLTEXT索引也可以使用 布尔方式不同于迄今为止使用的全文本搜索语法的地方在于, 即使没有定义FULLTEXT索引,也可以使用它。但这是一种非常缓慢的操作(其性能将随着数据量的增加而降低)。(虽然说是这么说,但是还没找到怎么使用的方法)
全文本搜索布尔操作符:
操作符 |
描述 |
+ |
包括,这个词必须存在。 |
- |
排除,这个词不能存在。 |
> |
包括并增加排名值。 |
< |
包括并降低排名值。 |
() |
将单词分组成子表达式(允许将其包括,排除,排序等作为一个组)。 |
~ |
否定一个词的排名值。 |
* |
通配符,在结尾的单词,感觉有点像正则里的位置匹配$符号 |
“” |
定义一个短语(与单个单词列表相反,整个短语匹配包含或排除)。 |
表格参考:[易百教程](Https://www.yiibai.com/mysql/boolean-text-searches.html)
举例:
使用要注意的一些地方:
- 在索引全文本数据时,短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词(如果需要,这个数目可以更改)。
- MySQL带有一个内建的非用词(stopWord)列表,这些词在索引全文本数据时总是被忽略。如果需要,可以覆盖这个列表(请参阅MySQL文档以了解如何完成此工作)。
- 许多词出现的频率很高,搜索它们没有用处(返回太多的结果)。因此,MySQL规定了一条50%规则,如果一个词出现在50%以上的行中,则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
- 如果表中的行数少于3行,则全文本搜索不返回结果(因为每个词或者不出现,或者至少出现在50%的行中)。
- 忽略词中的单引号。例如,don"t索引为dont。
- 不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果。这个地方又要注意一下,在后续版本中,加入了分词器,使得中文和日文等也能很好的建立全文索引。
没有邻近操作符 邻近搜索是许多全文本搜索支持的一个特性,它能搜索相邻的词(在相同的句子中、相同的段落中或者在特定数目的词的部分中,等等)。MySQL全文本搜索现在还不支持邻近操作符,不过未来的版本有支持这种操作符的计划。
我才说的应该就是分词器了....找了很久也没看到邻近搜索的消息。
接下来看一些其他最近版本的全文本搜索:
首先看一下一些与全文本搜索有关的变量:
MySQL对全文索引的支持:
- 在 MYSQL 中全文索引是类型为
FULLTEXT
的索引.
- 全文索引只能用在
InnoDB
或 MyISAM
表上, 且只能在 CHAR
, VARCHAR
, TEXT
类型的列上创建
- MYSQL 提供了内置的基于 ngram 的解析器, 用于支持中韩日文; 还有一个可安装的 MeCAb 解析器插件来支持日文.
FULLTEXT
索引可以在创建表时通过 CREATE TABLE
中定义, 或者在之后 ALTER TABLE
和 CREATE INDEX
.
- 对于大数据集, 应该将数据插入没有
FULLTEXT
索引的表中, 再创建全文索引, 这比将数据插入已创建全文索引的表中更快.
实现细节以及中文搜索:
默认情况下, 搜索是以 不区分大小写 的方式执行的. 要区分大小写, 必须对索引列使用区分大小写或二进制的排序规则. 比如, 使用 utf8mb4
字符集的列可以使用 utf8mb4_0900_as_cs
或 utf8mb4_bin
排序规则.
包含在双引号中的短语, 将按字面值进行匹配. 全文索引会将短语分解为单词, 并在 FULLTEXT 索引中搜索单词. 非单词字符不要求完全匹配. 搜索仅要求匹配项包含与短语完全相同的单词, 并且顺序相同. 例如, 短语 "test phrase" 与文本 "test, phrase" 就匹配.
MYSQL FULLTEXT 实现会将任何 字符序列(字母, 数字, 下划线) 视作一个单词. 这个字符序列中可以包含单引号(), 但在一行中不能多于一个. 所以
aaa"bbb是一个单词, 但
aaa""bbb` 就不是一个单词. 同时 FULLTEXT 会将单词开头或结尾的单引号删除.
内置的解析器通过查找某些定界符(delimiter characters)来确定单词的开头或结果. 常见的定界符有空格, 逗号, 句号. 对于单词之间没有定界符分隔的语言, 例如中文, 内置的解析器就无法确定单词的开始或结束位置.
对于这种情况, 为了将这些语言中的单词添加到全文索引中, 有两种方式处理.
- 对文本进行预处理, 使得单词之间存在任意的定界符. 通常是分词.
- 创建 FULLTEXT 时使用 ngram 解析器(中日韩文适用)或 MeCab 解析器(日文适用).
在全文索引中, 某些单词会被忽略掉:
- 任何太短的单词. 全文索引的默认最小单词长度在 InnoDB 是三个字符, 在 MyISAM 中是四个字符. 这个特性对 ngram 解析器不适用, ngram 解析器中单词长度由
ngram_token_size
选项确定.
- 在 stopwords 中的单词将被忽略.(最开始的截图中有介绍)
集合和查询中的每个正确的单词都被根据其在集合或查询中的重要性进行加权. 类似于 TF-IDF. 在多数文档中出现的单词权重比较低. 在所有文档中较少出现的单词权重较高.
布尔搜索:
布尔型的全文搜索支持以下运算符:
+
前导或尾随的加号表示单词必须出现在返回的每一行中. InnoDB 仅支持前导的加号.
-
前导或尾随的减号表示单词不能再返回的任何行中出现. InnoDB 仅支持前导的减号. -
仅能排除其他条件搜索到的行. 仅有 -
会返回空的结果.
- (没有操作符) 默认情况, 该单词为可选, 但包含该单词的行评分较高.
@distance
仅用于 InnoDB 表. 测试两个或多个单词出现的距离是否在 distance 的值之内. distance 的单位是单词的个数. 使用双引号指定要比较的单词. MATCH(col1) AGAINST(""word1 word2 word3" @8" IN BOOLEAN MODE)
word1, word2, word3 之间的距离(单词数)在 8 之内.
>``<
用于修改单词对所在行的相关性的贡献度.
()
括号用于将单词分组为子表达式. 括号可以嵌套.
~
前导的 ~ 表示否定运算符, 使得单词对行的相关性的贡献度为负数. 这对于标记噪音字符很有用. 包含这类单词的行的相关性低于其他行, 但不完全排除.
*
星号用作截断(通配符)运算符. 当一个单词使用截断符指定时, 即使它太短或者在 stopwords 列表中, 也不会被忽略.
"
包含在引号中的短语, 将按字面值进行匹配.
下面是几个例子:
"apple banana"
返回至少包含其中一个单词的行.
"+apple ~Macintosh"
返回包含单词 apple 的行, 如果行中有 macintosh, 它的评级会更低.
- `"+apple +(>turnover 返回行中同时有 apple 和 turnover, 或者同时有 apple 和 strudel 的行. apple 和 turnover 的行, 比 apple 和 strudel 的行优先级高.
apple*
返回单词以 apple 开头的行, 例如 “apple”, “apples”, “applesauce”, “applet”.
""some words""
返回行中明确包含短语 "some words" 的行, 可以是 “some words of wisdom” 但不能是 “some noise words”.
InnoDB 上的全文搜索是建立在 Sphinx 全文搜索引擎上的, 算法是基于 BM25 和 TF-IDF 排名算法.
相关性排名的计算方式
# 计算 IDF 逆文档频率
${IDF} = log10( ${total_records} / ${matching_records} )
# 文档多次包含一个单词时
${TF} * ${IDF}
# 单个单词的相关性, 不知道为什么要多乘次 ${IDF}
${rank} = ${TF} * ${IDF} * ${IDF}
# 多个单词的相关性
${rank} = ${TF} * ${IDF} * ${IDF} + ${TF} * ${IDF} * ${IDF}
ngram 解析器
一个 ngram 是 n 个字符的连续序列. ngram 解析器将文本序列标记为连续的 n 字符序列. 对于文本序列 "abcd" 可以使用 ngram 解析器标记为不同的 n 版本:
n=1: "a", "b", "c", "d"
n=2: "ab", "bc", "cd"
n=3: "abc", "bcd"
n=4: "abcd"
ngram 全文解析器是内置的服务器插件, 启动服务器时会自动加载该插件.
配置 token size
ngram 解析器默认的 ngram token size 是 2(bigram).
使用 ngram_token_size
配置选项设置 token size, 可配置的最小值是 1, 最大值是 10. 通常将 ngram_token_size
配置为你想要搜索的最大 token 的长度.
可以在启动的时候设置:
mysqld --ngram_token_size=2
或者在配置文件中设置:
[mysqld]
ngram_token_size=2
查看当前配置:
show variables like "%ngram%";
使用 ngram 解析器创建 FULLTEXT 索引
CREATE TABLE articles2 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body) WITH PARSER ngram
) ENGINE=InnoDB CHARACTER SET utf8mb4;
插入些测试数据:
SET NAMES utf8mb4;
INSERT INTO articles2 (title,body) VALUES
("数据库管理","在本教程中我将向你展示如何管理数据库"),
("数据库应用开发","学习开发数据库应用程序");
在表 INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE 中查看 tokenized data:
SET GLOBAL innodb_ft_aux_table="test/articles2";
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;
搜索查询:
select * from articles2 where match(title,body) against ("开发");
修改已存在的表:
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB CHARACTER SET utf8;
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;
# Or:
CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;
其他细节
ngram 解析器会在解析时消除空格.
对于 stopwords, ngram 解析器会排除包含 stopwords 的 token. 比如 stopwords 包含逗号, "a,b" 会在 n=2 时解析为 "a," 和 ",b", 这两个 token 都会被排除. stopwords 中长度大于 ngram_token_size
的单词, 将会被忽略.
对于自然语言搜索, 搜索字符串将会被转换为 ngram token 的并集, 比如 "abc" 会被转换为 "ab" 和 "bc".
对于布尔搜索, 搜索字符串将会被转换为 ngram phrase search. 比如 "abc" 会被转换为 ""ab bc"". 给定两个文档, 一个包含 "ab", 另一个包含 "abc", 搜索短语 ""ab bc"" 只会匹配包含 "abc" 的文档.
通配符搜索可能返回意外的结果. 以下行为适用:
- 如果一个通配符搜索的前缀项小于 ngram_token_size, 查询会返回所有 ngram token 以前缀开始的行. 比如 n=2 时, 搜索 "a*" 会返回所有 ngram token 中以 "a" 开头的行.
- 如果一个通配符搜索的前缀项大于 ngram_token_size, 前缀项会被转换为 ngram phrase, 并且忽略通配符. 比如 n=2 时, "abc*" 通配符搜索会被转换为 "ab bc".
phrase search 会被转换为 ngram phrase search. 比如搜索短语 "abc" 会被转换为 "ab bc", 返回包含 "abc" 或 "ab bc" 的文档. 搜索短语 "abc def" 会被转换为 "ab bc de ef", 返回包含 "abc def" 或 "ab bc de ef" 的文档. 文档包含 "abcdef" 的却不会返回.
最后这个真是迷惑行为大赏, 还是看个例子好了.
INSERT INTO articles2 (title,body) VALUES ("abcdef","在本教程中我将向你展示如何管理数据库");
普通搜索是有数据的:
select * from articles2 where match(title,body) against ("abc def" IN BOOLEAN MODE);
短语搜索没有数据, 注意用 双引号 引起来的表示短语:
select * from articles2 where match(title,body) against (""abc def"" IN BOOLEAN MODE);
然后切换到自然语言搜索, 两种方式都是有结果的:
select * from articles2 where match(title,body) against ("abc def");
select * from articles2 where match(title,body) against (""abc def"");
参考:
官方文档
知乎
插入数据
插入数据通常有以下几种形式:
- 插入完整的行;
- 插入行的一部分;
- 插入多行;
- 插入某些查询的结果
插入及系统安全 可针对每个表或每个用户,利用MySQL的安全机制禁止使用INSERT语句
插入完整的行:
INSERT INTO VALUES(NULL/num/string/...)
比如:
分析:
此例子插入一个新客户到customers表。存储到每个表列中的数据在VALUES子句中给出,对每个列必须提供一个值。如果某个列没有值(如上面的cust_contact和cust_email列),应该使用NULL
值(假定表允许对该列指定空值)。各个列必须以它们在表定义中出现的次序填充。第一列cust_id也为NULL。这是因为每次插入一个新行时,该列由MySQL自动增量。你不想给出一个值(这是MySQL的工作),又不能省略此列(如前所述,必须给出每个列),所以指定一个NULL值(它被
MySQL忽略,MySQL在这里插入下一个可用的cust_id值)。
可以看到,上面的SQL语句极其的不健壮,如果列中的次序发生改变,难免会出现安全问题,所以可以考虑用如下的方式插入记录。
总是使用列的列表 一般不要使用没有明确给出列的列表的INSERT语句。使用列的列表能使SQL代码继续发挥作用,即使表结构发生了变化。
仔细地给出值 不管使用哪种INSERT语法,都必须给出VALUES的正确数目。如果不提供列名,则必须给每个表列提供一个值。如果提供列名,则必须对每个列出的列给出一个值。如果不这样,将产生一条错误消息,相应的行插入不成功。
省略列 如果表的定义允许,则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件。
该列定义为允许NULL值(无值或空值)。
在表定义中给出默认值。这表示如果不给出值,将使用默认值。
如果对表中不允许NULL值且没有默认值的列不给出值,则
MySQL将产生一条错误消息,并且相应的行插入不成功。
提高整体性能 数据库经常被多个客户访问,对处理什么请求以及用什么次序处理进行管理是MySQL的任务。INSERT操作可能很耗时(特别是有很多索引需要更新时),而且它可能降低等待处理的SELECT语句的性能。如果数据检索是最重要的(通常是这样),则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY,指示MySQL降低INSERT语句的优先级,如下所示:顺便说一下,这也适用于马上要介绍的UPDATE和DELETE语句。
插入多个行:
显然,第一种方法是:使用多条插入语句,甚至一次提交他们,每条语句间以一个分号结束:
或者,在列名和次序相同的情况下,组合各语句:
注意:VALUES之间以逗号分隔。
提高INSERT的性能 此技术可以提高数据库处理的性能,因为MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。
插入检索出的数据:
注意:这里导入了cust_id,所以插入的时候需要保证cust_id的值不会重复。当然,也可以根本不关心这一列。
INSERT SELECT中的列名 为简单起见,这个例子在INSERT和SELECT语句中使用了相同的列名。但是,不一定要求列名匹配。事实上,MySQL甚至不关心SELECT返回的列名。它使用的是列的位置,因此SELECT中的第一列(不管其列名)将用来填充表列中指定的第一个列,第二列将用来填充表列中指定的第二个列,如此等等。这对于从使用不同列名的表中导入数据是非
常有用的。
更新和删除数据
更新和删除数据都有两种方式:
不要省略WHERE子句 因为UPDATE和DELETE都涉及到更改存储的数据,并且MySQL中是不存在undo操作的。因此一定要显式的给出WHERE子句,除非的确打算对整个表进行操作。
同样,两者因为都是对数据更改的不可逆操作,因此可以通过给不同的用户赋予不同的权限,保证操作的安全性。
UPDATE语句的组成:
- 要更新的表
- 列名和它们的新值
- 确定要更新的行和过滤条件
例如:现在需要更新10005客户的邮件信息和名字:
UPDATE customers
SET cust_name = "The Fudds",
cust_email = "elmer@fudd.com"
WHERE cust_id = 10005;
可以看到,需要更新多个记录时只需要将key-value对用逗号分开。
此外,UPDATE子句中给定的条件当然可以指定为单个值,多个值,范围甚至是子查询等。
IGNORE关键字 如果用UPDATE语句更新多行,并且在更新这些行中的一行或多行时出一个现错误,则整个UPDATE操作被取消(错误发生前更新的所有行被恢复到它们原来的值)。为了 即使是发生错误,也继续进行更新,可使用IGNORE关键字,如下所示:
UPDATE IGNORE customers…
而删除指定行某几列的值的时候,只需要将指定列设置为NULL即可。
DELETE语句的组成:
例如:现在需要从customers表中删除cust_id为10006的行:
DELETE FROM customers
WHERE cust_id = 10006;
注意DELETE语句不需要列名或通配符。DELETE删除整行而不是删除列。
删除表的内容而不是表 DELETE语句从表中删除行,甚至是删除表中所有行。但是,DELETE不删除表本身。
更快的删除 如果想从表中删除所有行,不要使用DELETE。可使用TRUNCATE TABLE语句,它完成相同的工作,但速度更快(TRUNCATE实际是删除原来的表并重新创建一个表,而不
是逐行删除表中的数据)。
注意:如果UPDATE和DELETE语句不带WHERE子句,则都是对全表进行操作,这是很危险的。
一些推荐的原则:
- 除非确实打算更新和删除每一行,否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
- 保证每个表都有主键,尽可能像WHERE子句那样使用它(可以指定各主键、多个值或值的范围)。
- 在对UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT进行测试,保证它过滤的是正确的记录,以防编写的WHERE子句不正确。
- 使用强制实施引用完整性的数据库,这样MySQL将不允许删除具有与其他表相关联的数据的行。
对于最后一条是怎么理解的呢?不妨我们删除一行试一哈:
没错,根本删除不了。
小心使用 MySQL没有撤销(undo)按钮。应该非常小心地使用UPDATE和DELETE,否则你会发现自己更新或删除了错误的数据。
结语:
2020.05.01-2020.05.03,花了三天终于看完了MySQL的增删改查和写完了前20章总结的笔记,挑战极限!是一本很好的MySQL入门书籍,后续应该会看《高性能MySQL》吧。不打算继续写读书笔记了,效率实在比较低。希望能晚上看完最后10章的内容。Code is Power!
参考书目:
《MySQL必知必会》
--结束END--
本文标题: MySQL必知必会1-20章读书笔记
本文链接: https://www.lsjlt.com/news/5868.html(转载时请注明来源链接)
有问题或投稿请发送至:
邮箱/279061341@qq.com QQ/279061341
本篇文章演示代码以及资料文档资料下载
下载Word文档到电脑,方便收藏和打印~
下载Word文档
-
否,year 不是 sql 中的聚合函数。year 函数是一个日期函数,用于从给定的日期值中提取年份。它是一个标量函数,返回单个值,而不是值的集合。相反,聚合函数对一组值进行操作并生成一...
-
sql 中 between 运算符用于检查值是否在指定范围之内,其语法为:select column_name from table_name where colum...
-
sql 中的 update 语句用于更新表中的现有数据,通过指定要更新的表、列、值和可选的更新条件来实现,可更新特定行或组行的特定列值。
SQL 中的 UPDATE 语句
什么是 UP...
-
sql 中 for 循环可用于遍历结果集,逐行执行操作。语法:for var_name in (select_statement) [loop_statement] end f...
-
sql 中 any 和 all 运算符的区别在于:any 检查子查询中是否存在满足条件的行,返回 true 或 false。all 检查子查询中所有行是否都满足条件,返回 true 或 ...
-
exists 子查询用于检查外层查询中的行是否存在匹配记录,用法如下:包含在 select 语句的 where 子句中。返回布尔值 true (存在匹配) 或 fal...
-
union 运算符在 sql 中用来合并相同结构的表或子查询的结果集,排除重复行。它具有以下用法:合并具有相同列名和数据类型的多个表或子查询的结果集合并为一个。排除结果集中重复...
-
sql 中索引是一种通过创建数据指针来提高查询性能的技术,主要用于where、order by、join和group by子句。索引类型包括聚集索引、非聚集索引、主键索引、唯一...
-
sql 中的 nullif() 函数,用于比较两个表达式并返回较小的值,若均为 null 则返回 null,语法为 nullif(expression1, expression2)。可用...
-
decode 函数根据输入表达式值将值转换为另一个值,语法为 decode(expression, value1, result1, value2, result2, ..., defa...
0