《sql必知必会》第5版 知识点摘要梳理（第6-10章）

六、用通配符进行过滤

这一章节据介绍什么时通配符、如何使用通配符，以及怎样使用LIKE操作符进行通配搜索，以便对数据进行复杂过滤。

6.1 LIKE操作符

之前介绍的所有操作符都是针对已知值进行过滤的。不管是匹配一个值还是多个值，检验大于还是小于已知值，或者检查某个范围的值，其共同点是过滤中使用的值都是已知的。但是过滤方法并不是任何时候都好用。例如怎么样搜索产品名中包含文本bean bag 的所有产品？用简单的比较操作符肯定不行，必须使用通配符。利用通配符，可以创建比较特定数据的搜索模式。在这个例子中，如果你想找出名称包含bean bag的所有产品，可以构造一个通配符搜索模式，找出在产品名的任何位置出现bean bag的产品。

通配符（wildcard）用来匹配值的一部分的特殊字符。搜索模式（search pattern）由字面值、通配符或者两者组合构成的搜索条件。

通配符本身实际上时SQL的WHERE子句中由特殊含义的字符，SQL支持几种通配符。为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示DBMS后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。

谓词（predicate），操作符何时不是操作符？答案是，它作为谓词时。从技术上说，LIKE是谓词而不是操作符。虽然最终的结果是相同的，但应该对此属于
有所了解，以免在SQL文献或手册中遇到此术语时不知所云。

通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。

6.1.1 百分号（%）通配符

最常使用的通配符是百分号（%）在搜索串中，%表示任何字符出现任意次数。例如，为了找出所有以词Fish起头的产品，可写以下的SELECT语句：

---

区分大小写：
根据DBMS的不同及其配置，搜索可以是区分大小写的。如果区分大小写则不同大小写就不匹配。

通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，他们位于模式的两端：

---

下面的例子使用一个通配符百分号，位于中间：

根据部分信息搜索电子邮箱地址时可以用到将通配符百分号至于中间位置，如 WHERE email LIKE 'b%forta.com'

---

通配符百分号除了能匹配一个或多个字符外，还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。

注意：1、有些DBMS用空格来填补字段的内容。例如，如果某列有50个字符，而存储的文本为Fish bean bag toy(17个字符)，则为填满该列需要在文本后
附加33个空格。这样做一般对数据及使用没有影响，但是可能对上述SQL语句有负面影响。子句WHERE prod_name LIKE 'F%y'只匹配以F开头、以y结尾的
prod_name。如果值后面跟空格则不是以y结尾，所以Fish bean bag toy 就不会检索出来。简单的解决办法是给搜索模式再增加一个%号：'F%y%'还匹配
y之后的字符（或空格）。更好的解决办法是用函数去掉空格。（操作参阅第八课）
2、注意NULL。 通配符%看起来像是可以匹配任何东西，但有个例外，这就是NULL。子句WHERE prod_name LIKE'%'不会匹配产品名称为NULL的行。

---

6.1.2 下划线（_）通配符

另一个有用的通配符是下划线（_）。下划线的用途与“%”一样，但是它只匹配单个字符，而不是多个字符。

注意：DB2不支持通配符

下面是一个例子，因数据有空格所以注意空格的输入，前面：

---

上图例子为两个下划线_通配符，所以返回值为12和18的产品名称，下图例子是使用百分号%通配符匹配的结果反馈：

上例可以看出百分比是匹配了多个字符儿下划线是一个下划线匹配一个字符。

---

6.1.3 方括号（[]）通配符

方括号（[]）通配符用来指定一个字符集。它必须匹配指定位置（通配符的位置）一个字符。

注意： 与前面描述的通配符不同，方括号并不是总是支持集合。不是所有的DBMS都支持用来创建集合的[]，微软的SQL Server支持集合，
但是MySQL，Oracle，DB2，SQLite都不支持。未确定你使用的DBMS是否支持集合，请参阅相应文档。

下例为找出所有名字以 **J ** 或 ** M ** 起头的联系人：

我们可以看到在MySQL中不支持方括号通配符限定集合。
在MySQL的标准LIKE操作符中，方括号 [ ] 不被视为通配符，它们会被当作普通字符来处理。

---

MySQL的 LIKE 操作符主要支持以下两个通配符：

1. % (百分号)：匹配任意数量的字符（包括零个字符）。
   · LIKE ‘a%’：匹配以 “a” 开头的任何字符串，如 “apple”, “a”, “abc123”。
2. _ (下划线)：匹配任意单个字符。
   · LIKE ‘a_’：匹配以 “a” 开头且长度为两个字符的字符串，如 “ab”, “a1”, “ax”。

---

如何实现类似方括号通配符的功能？

如果你想实现像其他数据库（如SQL Server）中 [abc] 匹配一个特定字符集的功能，在MySQL中有以下几种替代方案：

1. 使用 REGEXP 或 RLIKE (正则表达式)

这是最灵活和强大的方法。正则表达式可以完全覆盖方括号通配符的功能，甚至更强大。

· [abc] 的等效写法：REGEXP ‘[abc]’
· SELECT * FROM table_name WHERE column_name REGEXP ‘[abc]’;
· 这会匹配包含 ‘a’, ‘b’, 或 ‘c’ 中任意一个字符的记录。
· 更多正则表达式示例：
· [a-z]：匹配任何小写字母。
· [0-9]：匹配任何数字。
· ^ [abc]：匹配以 ‘a’, ‘b’, 或 ‘c’ 开头的字符串。
· [abc]$：匹配以 ‘a’, ‘b’, 或 ‘c’ 结尾的字符串。

注意：正则表达式默认是部分匹配。如果希望完全匹配，需要使用 ^ 和 $ 限定符，例如 REGEXP ‘^ [abc]$’ 只会匹配 exactly ‘a’, ‘b’, 或 ‘c’ 这三个单字符字符串。

上述例子我们可以看到在使用REGEXP时方括号集合内在MySQL中不区分大小写，所以会匹配出“Kim Howard”名字中带小写
字母m的结果，这个时候我们需要用到新的正则表达式语句“REGEXP BINAR”，但是这个案例会出现字符集冲突，BINAR关
键字要求进行原始的二进制比较，这意味着它必须是区分大小写和重音的。而我们的[]字符集（utf8mb4_0900_ai_ci）
中_ai 表示不区分重音，_ci表示不区分大小写。MYSQL无法同时执行不区分大小写和中印的规则

解决方案：
使用^匹配开头字母。

---

2. 使用多个 LIKE 语句配合 OR

对于非常简单的模式，你也可以用多个 LIKE 来模拟。

· 模拟 [abc]：

SELECT * FROM table_name
WHERE column_name LIKE '%a%'
   OR column_name LIKE '%b%'
   OR column_name LIKE '%c%';

但这种方法是匹配包含这些字符，要精确模拟一个字符的位置，需要结合 _ 通配符，会非常繁琐，不推荐。

---

3. 使用 FIND_IN_SET (适用于逗号分隔的特定值)

如果你的匹配目标是几个明确的、完整的字符串，而不是单个字符，可以考虑这个函数，但适用场景比较特殊。

SELECT * FROM table_name
WHERE FIND_IN_SET(column_name, 'apple,banana,cherry') > 0;

---

总结对比

需求 SQL Server / Access (使用 LIKE) MySQL 解决方案 (使用 REGEXP)
匹配单个字符，是 ‘a’, ‘b’ 或 ‘c’ WHERE col LIKE ‘[abc]’ WHERE col REGEXP ‘^ [abc]$’
匹配包含 ‘a’, ‘b’ 或 ‘c’ 的字符串 WHERE col LIKE ‘%[abc]%’ WHERE col REGEXP ‘[abc]’
匹配以 ‘a’, ‘b’ 或 ‘c’ 开头的字符串 WHERE col LIKE ‘[abc]%’ WHERE col REGEXP ‘^ [abc]’

结论：在MySQL中，如果你需要字符集匹配的功能，应该优先使用 REGEXP 正则表达式来代替其他数据库中的方括号通配符。

注意：在[]集合中可以使用^（脱字号）在集合内表示否定。例如WHERE cust_contact REGEXP '^[^JM]'，下例子为返
回结果

---

上述方法也可以使用NOT操作符得出类似的结果，^的唯一优点是在使用多个WHERE子句时可以简化语法。此处与书上所写NOT LIKE不同，由于MySQL不能使用集合[]所以依然要用NOT + REGEXP这种表达语句。

---

6.2 使用通配符的技巧

上述例子可以看出SQL的通配符很有用。但是这种功能是有代价的，即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。下面给出一些使用通配符要记住的技巧。

1. 不要过度使用通配符，如果其他操作符能达到相同的目的，应该使用其他操作符。
2. 在确实需要使用通配符时，也尽量不i要把他们用在搜索模式的开始处，把通配符置于开始处，搜索起来是最慢的。
3. 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

---

6.3 此章小结

1. 使用LIKE操作符进行模糊查询
2. 使用通配符%、_、[]通配符，注意各自通配符的区别
3. 使用通配符坚持少用不用于开始的原则避免搜索效率低。

---

6.4章节练习题

1.编写SQL语句，从Products表中检索产品名称（prod_name)和描述（prod_desc)，仅返回描述中包含toy一词的产品。

此处考察LIKE模糊查询，在不确定的字符的情况下加上百分号

---

2.编写SQL语句，从Products表中检索产品名称（prod_name)和描述（prod_desc)，仅返回描述中不包含toy一词的产品。按产品名称对结果进行排序。

---

3.编写SQL语句，从Products表中检索产品名称（prod_name)和描述（prod_desc)，仅返回描述中同时出现toy和carrots一词的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用AND和两个LIKE比较。

---

4.编写SQL语句，从Products表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现toy和carrots的产品。提示：只需要用带有三个%符号的LIKE即可。

---

七、创建计算字段

这一刻价绍什么是计算字段，如何创建计算字段，以及如何从应用程序中使用别名引用他们。

7.1 计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式，下面举几个例子。

1. 需要显示公司名，同时还需要显示公司的地址，但这两个信息存储在不同的表列中。
2. 城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签打印程序需要把它们作为一个有恰当格式的字段检索出来。
3. 列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。
4. 物品订单表存储物品的价格和数量，不存储每个物品的总价格（用价格乘以数量即可）。但为打印发票，需要物品的总价格。
5. 需要根据表数据进行诸如总数、平均数的计算。

在上述每个例子中，存储在表中的数据都不是应用程序所有需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化。这就是计算字段可以派上用场的地方了。计算字段并不实际存在于数据库表中，计算字段是运行时在SELECT语句内创建的。

字段：基本与列的意思相同，经常互换使用，不过数据库列一般称为列，而字段这个术语通常在计算字段这种场合下使用。

    需要特别注意，只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段。从客户端（如应用程序）来看，
计算字段的数据与其他列的数据返回方式相同。

客户端与服务器的格式：
	在SQL语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成。但一般来说，在数据库服务器上
完成这些操作比在客户端中完成要快得多。

---

7.2 拼接字段

• 为了说明如何使用计算字段，我们来举一
• 个简单的例子，创建由两列组成的标题。

Vendors表包含供应商名和地址信息。加入要生成一个供应商报表，需要在格式化的名称（位置）中列出供应商的位置。
此报表需要一个值，而表中数据存储在两个列vend_name和vend_country中。此外，需要用括号将vend_country括起来，这些东西都没有存储在数据表中。这个返回供应商名称和地址的SELECT 语句很简单，但我们是如何创建这个组合值的呢？

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

解决办法： 把两个列拼接起来。在SQL中的SELECT语句中，可以使用一个特殊的操作符来拼接两个列。根据你所使用的DBMS。此操作符可用加号（+）或两个竖杠（||）表示。在MySQL中和Maria DB中，必须使用特殊的函数。

是+还是||？
	SQL Server使用+号。DB2、Oracle、PostgreSQL和SQLite使用||。详细请参阅具体的DBMS文档

下面是使用+号的例子（多数DBMS使用这种语法，MySQL不使用）

SELECT vend_name + '(' + vend_country  +  ')'
FROM Vendors
ORDER BY vend_name;

---

下面是相同的语句，但使用||的语法：

SELECT vend_name || '(' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;

---

下面是使用MySQL或MariaDB时需要使用的语句：

SELECT
Concat(vend_name, '(',vend_country,')')
RROM Vendors
ORDER BY vend_name;

---

上面两个SELECT语句拼接一下元素：

• 存储在vend_name列中的名字；

• 包含一个空格和一个左圆括号的字符串；

• 存储在vend_country列中的国家；

• 包含一个右圆括号的字符串。

   从上述输出中可以看出，SELECT语句返回包含上述四个元素的一个列（计算字段）。
   再看看上述SELECT语句返回的输出。结合成一个字段的两个列用空格填充。许多数据库（不是所有）保存填充为
   列宽的文本值，而实际上你要的结果不需要这些空格。为正确返回格式化的数据，必须去掉这些空格。这可以使用
   SQL的RTRTM（）函数来完成，如下例子所示：
  

SELECT
RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM
Vendors
ORDER BY vend_name;



###使用+联结

SELECT
RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
FROM
Vendors
ORDER BY vend_name;

上述为不同于MySQL的数据库代码，下述为MySQL数据库代码与结果（使用CONCAT函数联结列）：

RTRIM()函数去掉值右边所有空格。通过使用RTRIM（），各个列值都进行了整理。

说明：TRIM函数
大多数DBMS都支持RTRIM（）函数，（正如刚才所见，它去掉字符串右边的空格）、LTRIM（）函数去掉左边的空格以及
TRIM（）函数去掉字符串左右两边的空格。

---

使用别名
从前面的输出可以看到，SELECT语句可以很好地拼接地址字段。但是，这个新计算列的名字是什么呢？实际上它没有名字，它只是一个值。如果仅在SQL查询工具中查看一下结果，这样没有什么不好。但是一个未命名的列不能用于客户端应用中，因为客户端没有办法引用它。
为了解决这个问题，SQL支持列别名。别名（alisa）是一个字段或者值的替换名。别名用AS关键字赋予。请看下面的SELECT语句：

此处仅展示MySQL中用AS关键字别名新建列的用法与输出结果。由上图可以看出新列计算字段有了新列名vend_title。其余DBMS同理。

说明：AS通常可选
	在很多DBMS中，AS关键字是可选的，不过最好使用它，这被视为一条最佳实践。

说明：别名的其他用途
	别名还有其他用途。常见的用途包括在实际的表列名包含不合法的字符（如空格）时重新命名它。在原来的名字含混
	或容易误解时扩充它。

注意：别名
	别名的名字既可以是一个单词，也可以是一个字符串。如果是后者，字符串应该括在引号中。虽然这种做法是合法的，
	但不建议这么去做。多单词的名字可读性高，不过会给客户端应用带来各种问题。因此，别名最常见的使用是将多个
	单词的列名重命名为一个单词的名字。
说明：导出列
	别名有时也称为导出列（derived column），不管怎么叫，它们所代表的是相同的东西。

---

7.3 执行算术计算

计算字段的另一种常见用途是对检索出的数据进行算术计算。举个例子，Orders表包含收到的所有订