day05-用通配符进行过滤

LIKE操作符

前面介绍的所有操作符都是针对已知值进行过滤的。不管是匹配一个值还是多个值，检验大于还是小于已知值，或者检查某个范围的值，其共同点是过滤中使用的值都是已知的。

但是，这种过滤方法并不是任何时候都好用。例如，怎样搜索产品名中包含文本 bean bag 的所有产品？用简单的比较操作符肯定不行，必须使用通配符。利用通配符，可以创建比较特定数据的搜索模式。在这个例子中，如果你想找出名称包含 bean bag 的所有产品，可以构造一个通配符搜索模式，找出在产品名的任何位置出现 bean bag 的产品。


通配符（wildcard） 
用来匹配值的一部分的特殊字符。

搜索模式（search pattern） 
由字面值、通配符或两者组合构成的搜索条件。


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


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

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

百分号（%）通配符

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE 'Fish%';


输出：
mysql> SELECT prod_id, prod_name 
    -> FROM Products 
    -> WHERE prod_name LIKE 'Fish%';
+---------+-------------------+
| prod_id | prod_name         |
+---------+-------------------+
| BNBG01  | Fish bean bag toy |
+---------+-------------------+
1 row in set (0.00 sec)

mysql> 




此例子使用了搜索模式'Fish%'。在执行这条子句时，将检索任意以Fish 起头的词。%告诉 DBMS 接受 Fish 之后的任意字符，不管它有多少字符。

说明：区分大小写 
根据 DBMS 的不同及其配置，搜索可以是区分大小写的。如果区分大小写，则'fish%'与 Fish bean bag toy 就不匹配。

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



SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE '%bean bag%';

输出：
mysql> SELECT prod_id, prod_name 
    -> FROM Products 
    -> WHERE prod_name LIKE '%bean bag%';
+---------+---------------------+
| prod_id | prod_name           |
+---------+---------------------+
| BNBG01  | Fish bean bag toy   |
| BNBG02  | Bird bean bag toy   |
| BNBG03  | Rabbit bean bag toy |
+---------+---------------------+
3 rows in set (0.00 sec)

mysql> 




搜索模式'%bean bag%'表示匹配任何位置上包含文本 bean bag 的值，不论它之前或之后出现什么字符。

通配符也可以出现在搜索模式的中间，虽然这样做不太有用。下面的例子找出以 F 起头、以 y 结尾的所有产品：



SELECT prod_name FROM Products
WHERE prod_name LIKE 'F%y';



mysql> SELECT prod_name FROM Products
    -> WHERE prod_name LIKE 'F%y';
+-------------------+
| prod_name         |
+-------------------+
| Fish bean bag toy |
+-------------------+
1 row in set (0.00 sec)




提示：根据部分信息搜索电子邮件地址 
有一种情况下把通配符放在搜索模式中间是很有用的，就是根据邮件地址的一部分来查找电子邮件，例如 WHERE email LIKE 'b%@forta.com'。

需要特别注意，除了能匹配一个或多个字符外，%还能匹配 0 个字符。%代表搜索模式中给定位置的 0 个、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 之后的字符（或空格）。更好的解决办法是用函数去掉空格。


注意：请注意 NULL 
通配符%看起来像是可以匹配任何东西，但有个例外，这就是 NULL。
子句 WHERE prod_name LIKE '%'不会匹配产品名称为 NULL 的行。

下划线（_）通配符

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

说明：DB2 通配符 
DB2 不支持通配符_。

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';


输出：
mysql> SELECT prod_id, prod_name
    -> FROM Products
    -> WHERE prod_name LIKE '__ inch teddy bear';
+---------+--------------------+
| prod_id | prod_name          |
+---------+--------------------+
| BR02    | 12 inch teddy bear |
| BR03    | 18 inch teddy bear |
+---------+--------------------+
2 rows in set (0.00 sec)






说明：请注意后面所跟的空格 
与上例一样，可能需要给这个模式添加一个通配符。

这个 WHERE 子句中的搜索模式给出了后面跟有文本的两个通配符。结果只显示匹配搜索模式的行：第一行中下划线匹配 12，第二行中匹配18。8 inch teddy bear 产品没有匹配，因为搜索模式要求匹配两个通配符而不是一个。对照一下，下面的 SELECT 语句使用%通配符，返
回三行产品：

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '% inch teddy bear';


输出：
mysql> SELECT prod_id, prod_name
    -> FROM Products
    -> WHERE prod_name LIKE '% inch teddy bear';
+---------+--------------------+
| prod_id | prod_name          |
+---------+--------------------+
| BR01    | 8 inch teddy bear  |
| BR02    | 12 inch teddy bear |
| BR03    | 18 inch teddy bear |
+---------+--------------------+
3 rows in set (0.00 sec)



注意：
1.与%能匹配多个字符不同，_总是刚好匹配一个字符，不能多也不能少。

方括号（[ ]）通配符

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


说明：并不总是支持集合与前面描述的通配符不一样，并不是所有 DBMS 都支持用来创建集合
的[]。微软的 SQL Server 支持集合，但是 MySQL，Oracle，DB2，SQLite都不支持。为确定你使用的 DBMS 是否支持集合，请参考对应的文档





例如，找出所有名字以 J 或 M 起头的联系人，可进行如下查询：

SELECT cust_contact 
FROM Customers 
WHERE cust_contact LIKE '[JM]%' 
ORDER BY cust_contact;


输出：
mysql> SELECT cust_contact 
    -> FROM Customers 
    -> WHERE cust_contact LIKE '[JM]%' 
    -> ORDER BY cust_contact;
Empty set (0.00 sec)

mysql> 




此语句的 WHERE 子句中的模式为'[JM]%'。这一搜索模式使用了两个不同的通配符。[JM]匹配方括号中任意一个字符，它也只能匹配单个字符。
因此，任何多于一个字符的名字都不匹配。[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。
此通配符可以用前缀字符^（脱字号）来否定。例如，下面的查询匹配以J 和 M 之外的任意字符起头的任意联系人名（与前一个例子相反）：


SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^
JM]%'
ORDER BY cust_contact;


输出：
mysql> SELECT cust_contact
    -> FROM Customers
    -> WHERE cust_contact LIKE '[^
    '> JM]%'
    -> ORDER BY cust_contact;
Empty set (0.00 sec)

mysql> 




当然，也可以使用 NOT 操作符得出类似的结果。^ 的唯一优点是在使用多个 WHERE 子句时可以简化语法：


SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%'
ORDER BY cust_contact;



输出：
mysql> SELECT cust_contact
    -> FROM Customers
    -> WHERE NOT cust_contact LIKE '[JM]%'
    -> ORDER BY cust_contact;
+--------------------+
| cust_contact       |
+--------------------+
| Denise L. Stephens |
| Jim Jones          |
| John Smith         |
| Kim Howard         |
| Michelle Green     |
+--------------------+
5 rows in set (0.00 sec)

mysql>

使用通配符的技巧

正如所见，SQL 的通配符很有用。但这种功能是有代价的，即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。这里给出一些使用通配符时要记住的技巧。


不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。
总之，通配符是一种极其重要和有用的搜索工具，以后我们经常会用到它。

小练习

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

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%';


mysql> SELECT prod_name, prod_desc
    -> FROM Products
    -> WHERE prod_desc LIKE '%toy%';
+---------------------+-----------------------------------------------------------------------+
| prod_name           | prod_desc                                                             |
+---------------------+-----------------------------------------------------------------------+
| Fish bean bag toy   | Fish bean bag toy, complete with bean bag worms with which to feed it |
| Bird bean bag toy   | Bird bean bag toy, eggs are not included                              |
| Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots                      |
+---------------------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

2.反过来再来一次。编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品。这次，按产品名称对结果进行排序。


SELECT prod_name, prod_desc
FROM Products
WHERE NOT prod_desc LIKE '%toy%'
ORDER BY prod_name;



mysql> SELECT prod_name, prod_desc
    -> FROM Products
    -> WHERE NOT prod_desc LIKE '%toy%'
    -> ORDER BY prod_name;
+--------------------+--------------------------------------------------+
| prod_name          | prod_desc                                        |
+--------------------+--------------------------------------------------+
| 12 inch teddy bear | 12 inch teddy bear, comes with cap and jacket    |
| 18 inch teddy bear | 18 inch teddy bear, comes with cap and jacket    |
| 8 inch teddy bear  | 8 inch teddy bear, comes with cap and jacket     |
| King doll          | 12 inch king doll with royal garments and crown  |
| Queen doll         | 12 inch queen doll with royal garments and crown |
| Raggedy Ann        | 18 inch Raggedy Ann doll                         |
+--------------------+--------------------------------------------------+
6 rows in set (0.00 sec)

mysql>

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


SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';



mysql> SELECT prod_name, prod_desc
    -> FROM Products
    -> WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';
+---------------------+--------------------------------------------------+
| prod_name           | prod_desc                                        |
+---------------------+--------------------------------------------------+
| Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots |
+---------------------+--------------------------------------------------+
1 row in set (0.00 sec)

mysql>

4.来个比较棘手的。我没有特别向你展示这个语法，而是想看看你根据目前已学的知识是否可以找到答案。编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可。

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%carrots%';




mysql> SELECT prod_name, prod_desc
    -> FROM Products
    -> WHERE prod_desc LIKE '%toy%carrots%';
+---------------------+--------------------------------------------------+
| prod_name           | prod_desc                                        |
+---------------------+--------------------------------------------------+
| Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots |
+---------------------+--------------------------------------------------+
1 row in set (0.00 sec)

mysql>

posted @ 2025-06-20 20:41 三思博客阅读(13) 评论(0) 收藏举报

刷新页面返回顶部

三思博客

纸上得来终觉浅绝知此事要躬行敢想敢干最重要 Slow is fast No one has a chance of victory, but I am willing to exchange my life for this chance of victory. What about you?

day05-用通配符进行过滤

day05-用通配符进行过滤

LIKE操作符

百分号（%）通配符

下划线（_）通配符

方括号（[ ]）通配符

使用通配符的技巧

小练习

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

2.反过来再来一次。编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品。这次，按产品名称对结果进行排序。

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

公告

三思博客

纸上得来终觉浅 绝知此事要躬行 敢想敢干最重要 Slow is fast No one has a chance of victory, but I am willing to exchange my life for this chance of victory. What about you?

day05-用通配符进行过滤

day05-用通配符进行过滤

LIKE操作符

百分号（%）通配符

下划线（_）通配符

方括号（[ ]）通配符

使用通配符的技巧

小练习

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

2.反过来再来一次。编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品。这次，按产品名称对结果进行排序。

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

公告

纸上得来终觉浅绝知此事要躬行敢想敢干最重要 Slow is fast No one has a chance of victory, but I am willing to exchange my life for this chance of victory. What about you?