订单表mysql_MySQL必知必会 笔记(六)

笔记对应书本的十六至十八章

「written by Talaxy on 3/23/20」

---

使用表别名

除了列名和计算字段外，SQL还允许给表名起别名，比如：

select 

表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机

使用不同类型的联结

自联结

如果你想找出生产过"商品一"的所有厂商，然后列出这些厂商所生产的产品信息：

select 

而使用自联结可以这么写：

select 

虽然两个方法结果是相同的，但有时候处理联结远比处理子查询快得多。

自然联结

其实自然联结和内部联结一样，只是不允许出现相同的列。系统不会帮你自然联结(过去的MySQL有此功能)，只能通过自己手动联结这些互不相同的列。事实上，可能我们建立的每个内部联结都是自然联结。

外部联结

在对两个表(比如 表A 和 表B)进行联结时，我们可能会遇到 在表B中找不到与表A(的某一行)相关联的一行 的情况。使用内部联结或者自然联结时，会直接抛弃表A中的这类行，而在外部联结，会保留这类行，并对行中B列的值标记为NULL。

比如，我们想检索所有客户及其下的订单：

select 

如果存在 客户ID 为 10002，但是他没有下单(也就是订单表里没有 10002 的订单记录)。此时检索表里会有10002这一行，但他的订单ID为NULL。

在使用OUTER JOIN语法时，必须使用LEFT/RIGHT关键字。如果是LEFT，则以表A作为基准，反之则为表B。

使用带聚集函数的联结

比如，我们想检索所有客户及其下的订单数量(不包括没下单的用户)：

select 

比如，我们想检索所有客户及其下的订单数量(包括没下单的用户)：

select 

这个时候没有下单的用户的订单数量记为0，因为count函数会忽略NULL值。

使用联结和联结条件

汇总一下关于联结的某些要点：

• 注意所使用的联结类型，一般使用内部联结，但外部联结也是有效的
• 保证正确的联结条件
• 总是提供联结条件，不然会返回笛卡尔积
• 一个联结可以包含多个表，但为了保证有效，可以先测试两两之间的联结

---

组合查询

多数SQL语句只包含单条(不包括子查询)SELECT语句。

MySQL也允许执行多个查询，并组合这些查询结果。这些组合查询通常称为并(union)或者复合查询(compound query)

• 在单个查询中从不同的表返回类似结构的数据
• 对单个表多次查询，并组合这些查询

多数情况下，组合相同表的两个查询与具有多个WHERE子句条件的单条查询的工作相同。但在不同情况下，性能可能不同。

创建组合查询

可以用UNION操作符来组合多个SQL查询

使用UNION

UNION的使用非常简单，只要在各条查询语句间加上关键集UNION即可：

select 

对比多条WHERE条件语句：

select 

UNION规则

• UNION必须由两条及以上的SELECT语句组成，每两句之间用关键字UNION间隔
• UNION的每个查询必须包含相同的列、表达式、聚集函数。不过各个列不必以相同的次序列出
• 列数据必须兼容(指数据类型间支持转换)，但不需要完全相同

包含或取消重复的行

在UNION操作中，UNION会自动去除重复的行，如果不想去除，可以使用UNION ALL代替UNION

对组合查询结果排序

UNION的所有查询语句中，只能有一个ORDER BY子句，且在整个组合语句的最后一行，用来为服务UNION。

---

理解全文本搜索

笔者注：本章节可能已经过时，现代的全文本搜索可以移步官方文档

提前说明一下，不是所有的(数据库)引擎都支持全文本搜索。两个最常用的引擎为 MyISAM 和 InnoDB，前者支持全文本搜索，而后者不支持。

注：MySQL8 文档指明，MyISAM和InnoDB均支持全文本搜索。本章笔者则使用InnoDB引擎。

全文本搜索的优势：

• 性能快。全文本搜索如同字典一样，只要给定要搜索的表达式，就能返回匹配的目标行。而 LIKE和正则表达式匹配 则会将整个表从头到尾搜索，在行数较多的情况下，会相当耗时。
• 明确控制。全文本搜索能做比 LIKE和正则表达式匹配 更复杂的匹配逻辑
• 更智能化的搜索。全文本搜索如同主流的搜索网站一样，会匹配相关的结果。而 LIKE和正则表达式匹配 则不够灵活，只能匹配固定格式

使用全文本搜索

启用全文本搜索支持

在CREATE语句中，使用FULLTEXT语句

以下为样例：

create 

要注意的是，FULLTEXT里参数可以是多个列，但这些列必须是char，varchar，text类型。并且可以有多个FULLTEXT。

定义之后，MySQL就会根据FULLTEXT来创建维护索引

注：不要在导入数据的时候使用FULLTEXT，更新索引要花时间。如果正在往一个表里导入数据，此时不应该启用FULLTEXT索引，应当先导入所有数据后，再定义FULLTEXT。这样会花费更少的时间。

进行全文本搜索

MySQL在索引之后，可以用 Match() 和 Against() 进行全文本搜索。其中 Match() 指定被搜索的列(列需要和FULLTEXT中定义的完全一致)，Against() 指定要使用的搜索表达式。

以上面创建的表举个例子：

// 

实际上Match()Against()返回一个匹配值(书上称为等级值)，匹配值会针对表中每一行计算，并且根据行中词的数目、整个索引中词的总数、包含该词的行的数目等计算出来。比如，某一行中如果没有'hello'这个词，Match()Against()则返回 0。

搜索时注意，一些非常简单的词，比如"the"、"her"、"of"可能没有作为全文本的索引，也就是说查询这些词(即便行中存在)，返回值可能为0。

使用查询扩展

查询扩展不仅列出匹配行，而且能列出与匹配行相关的行(MySQL会优先列出匹配行)

select 

布尔文本搜索

MySQL支持全文本搜索的另一种形式：布尔方式(boolean mode)

功能有：

• 要匹配的值
• 要排斥的值
• 词的优先级匹配
• 分组

全文本布尔操作符

举例：

// 

全文本搜索的使用说明

• 在索引全文数据时，一些词可能会被忽略，这些词一般小于等于3个字符(笔者注：该特性可能只在旧版MyISAM出现)
• MySQL有一个stopword列表(可以用"SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;"来查看)，列表中的词在全文本索引时总是被忽略。如果需要，可以覆盖这个表
• 有些词出现的频率很高，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略(笔者注：通过实践发现，InnoDB并无该特性，可能仅出现在MyISAM中)
• 忽略词中的单引号，比如"don't"(笔者注：通过实践发现，InnoDB并无该特性)
• 汉语和日语等不具有词分隔符的语言，不能恰当的返回全文本搜索结果