如何在SQL中使用通配符
介绍
像许多计算机语言一样，SQL 允许使用各种 通配符 。 通配符 是特殊的占位符，可以表示一个或多个其他字符或值。 这是 SQL 中的一个方便功能，因为它允许您在数据库中搜索数据，而无需知道其中包含的确切值。
本指南将介绍如何使用 SQL 的指定通配符查询数据。
先决条件
为了遵循本指南，您需要一台运行某种类型的使用 SQL 的关系数据库管理系统 (RDBMS) 的计算机。 本指南中的说明和示例已使用以下环境进行验证：
- 运行 Ubuntu 20.04 的服务器，具有管理权限的非 root 用户和配置了 UFW 的防火墙，如我们的 Ubuntu 20.04 初始服务器设置指南中所述。
- 如 如何在 Ubuntu 20.04 上安装 MySQL 中所述，在服务器上安装并保护了 MySQL。 本指南已通过非 root MySQL 用户验证，使用 Step 3 中描述的过程创建。
注意：请注意，许多 RDBMS 使用自己独特的 SQL 实现。 尽管本教程中概述的命令适用于大多数 RDBMS，但如果您在 MySQL 以外的系统上测试它们，确切的语法或输出可能会有所不同。
您还需要一个加载了一些示例数据的数据库和表，您可以练习使用通配符。 如果您没有这个，您可以阅读以下 连接到 MySQL 并设置示例数据库 部分，了解如何创建本指南将在整个示例中使用的数据库和表的详细信息。
您还可以使用嵌入在此页面上的交互式终端来试验本教程中的示例查询。 单击下面的Launch an Interactive Terminal!按钮开始使用。
启动交互式终端！
连接到 MySQL 并设置示例数据库
如果您的 SQL 数据库系统在远程服务器上运行，请从本地计算机通过 SSH 连接到您的服务器：
ssh sammy@your_server_ip
然后打开 MySQL 服务器提示符，将 sammy 替换为您的 MySQL 用户帐户的名称。 如果您在此页面上使用嵌入式交互式终端，请注意提示时使用的密码是单词 secret：
mysql -u sammy -p
创建一个名为 wildcardsDB 的数据库：
CREATE DATABASE wildcardsDB;
如果数据库创建成功，您将收到如下输出：
OutputQuery OK, 1 row affected (0.01 sec)
要选择 wildcardsDB 数据库，请运行以下 USE 语句：
USE wildcardsDB;
OutputDatabase changed
选择数据库后，您可以使用以下命令在其中创建一个表。 例如，假设您要创建一个名为 user_profiles 的表来保存应用程序用户的个人资料信息。 该表将包含以下五列：
user_id：此列将保存int数据类型的值。 它还将用作表的主键，每个值都充当其各自行的唯一标识符name：每个用户的名字，用varchar数据类型表示，最多30个字符email：此列将保存用户的电子邮件地址，也使用varchar数据类型表示，但最多 40 个字符birthdate：使用date数据类型，此列将保存每个用户的出生日期quote：每个用户最喜欢的报价。 为提供足够数量的引号字符，此列还使用varchar数据类型，但最多 300 个字符
运行以下命令以创建此示例表：
CREATE TABLE user_profiles ( user_id int, name varchar(30), email varchar(40), birthdate date, quote varchar(300), PRIMARY KEY (user_id) );
OutputDatabase changed
然后将一些示例数据插入到空表中：
INSERT INTO user_profiles VALUES (1, 'Kim', 'bd_eyes@example.com', '1945-07-20', '"Never let the fear of striking out keep you from playing the game." -Babe Ruth'), (2, 'Ann', 'cantstandrain@example.com', '1947-04-27', '"The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt'), (3, 'Phoebe', 'poetry_man@example.com', '1950-07-17', '"100% of the people who give 110% do not understand math." -Demitri Martin'), (4, 'Jim', 'u_f_o@example.com', '1940-08-13', '"Whoever is happy will make others happy too." -Anne Frank'), (5, 'Timi', 'big_voice@example.com', '1940-08-04', '"It is better to fail in originality than to succeed in imitation." -Herman Melville'), (6, 'Taeko', 'sunshower@example.com', '1953-11-28', '"You miss 100% of the shots you don\'t take." -Wayne Gretzky'), (7, 'Irma', 'soulqueen_NOLA@example.com', '1941-02-18', '"You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss'), (8, 'Iris', 'our_town@example.com', '1961-01-05', '"You will face many defeats in life, but never let yourself be defeated." -Maya Angelou');
OutputQuery OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0
有了它，您就可以按照本指南的其余部分开始学习如何使用通配符查询 SQL 中的数据。
使用通配符查询数据
如简介中所述， 通配符 是特殊的占位符，可以表示一个或多个其他字符或值。
在 SQL 中，只有两个已定义的通配符：
_：用作通配符时，下划线表示单个字符。 例如，s_mmy将匹配sammy、sbmmy或sxmmy。%：百分号通配符代表零个或多个字符。 例如，s%mmy将匹配sammy、saaaaaammy或smmy。
这些通配符专门用于带有 LIKE 或 NOT LIKE 运算符的查询的 WHERE 子句。
为了说明使用先决条件部分中的示例数据，假设您知道 user_profiles 表中列出的至少一个用户的名称长度为三个字母并以“im”结尾，但您不是确定他们是谁。 因为您只是不确定这些用户名称中的第一个字母，您可以运行以下查询，该查询使用 _ 通配符来查找他们是谁：
SELECT * FROM user_profiles WHERE name LIKE '_im';
Output+---------+------+---------------------+------------+---------------------------------------------------------------------------------+ | user_id | name | email | birthdate | quote | +---------+------+---------------------+------------+---------------------------------------------------------------------------------+ | 1 | Kim | bd_eyes@example.com | 1945-07-20 | "Never let the fear of striking out keep you from playing the game." -Babe Ruth | | 4 | Jim | u_f_o@example.com | 1940-08-13 | "Whoever is happy will make others happy too." -Anne Frank | +---------+------+---------------------+------------+---------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
注：在本例中，星号 (*) 跟在 SELECT 后面。 这是 SQL 中的简写，代表“每一列”。
在某些应用程序和编程语言中，甚至 SQL 的某些实现中，星号用作表示零个或多个字符的通配符，就像本示例中使用的百分号一样。 但是，上面示例中的星号不是通配符，因为它代表特定的东西——即 user_profiles 表中的每一列——而不是一个或多个未知字符。
NOT LIKE 运算符与 LIKE 的效果相反。 它不会返回与通配符模式匹配的每条记录，而是返回与该模式不匹配的每一行。 为了说明，再次运行前面的查询，但将 LIKE 替换为 NOT LIKE：
SELECT * FROM user_profiles WHERE name NOT LIKE '_im';
这一次，结果集中省略了 name 列中的值与 _im 匹配的每一行：
Output+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+ | user_id | name | email | birthdate | quote | +---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+ | 2 | Ann | cantstandrain@example.com | 1947-04-27 | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt | | 3 | Phoebe | poetry_man@example.com | 1950-07-17 | "100% of the people who give 110% do not understand math." -Demitri Martin | | 5 | Timi | big_voice@example.com | 1940-08-04 | "It is better to fail in originality than to succeed in imitation." -Herman Melville | | 6 | Taeko | sunshower@example.com | 1953-11-28 | "You miss 100% of the shots you don't take." -Wayne Gretzky | | 7 | Irma | soulqueen_NOLA@example.com | 1941-02-18 | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss | | 8 | Iris | our_town@example.com | 1961-01-05 | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou | +---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec)
再举一个例子，假设您知道数据库中列出的几个用户的名称以“I”开头，但您无法记住所有这些用户。 您可以使用 % 通配符列出所有这些，如以下查询所示：
SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'I%';
Output+---------+------+----------------------------+ | user_id | name | email | +---------+------+----------------------------+ | 7 | Irma | soulqueen_NOLA@example.com | | 8 | Iris | our_town@example.com | +---------+------+----------------------------+ 2 rows in set (0.00 sec)
请注意，在 MySQL 中，默认情况下，LIKE 和 NOT LIKE 运算符不区分大小写。 这意味着即使您没有将通配符模式中的“I”大写，前面的查询也将返回相同的结果：
SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'i%';
Output+---------+------+----------------------------+ | user_id | name | email | +---------+------+----------------------------+ | 7 | Irma | soulqueen_NOLA@example.com | | 8 | Iris | our_town@example.com | +---------+------+----------------------------+ 2 rows in set (0.00 sec)
请注意，通配符与正则表达式不同。 通常，通配符是指 glob 样式模式匹配 中使用的字符，而正则表达式依赖 正则语言 来匹配字符串模式。
转义通配符
有时您可能想要搜索包含 SQL 通配符之一的数据条目。 在这种情况下，您可以使用 转义字符 来指示 SQL 忽略 % 或 _ 的通配符函数，而是将它们解释为纯文本。
例如，假设您知道数据库中列出的至少几个用户有一个包含百分号的最喜欢的报价，但您不确定他们是谁。
您可以尝试运行以下查询：
SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%';
但是，此查询不会很有帮助。 因为百分号作为任何长度的任何字符串的替代，它将返回表中的每一行：
Output+---------+--------+--------------------------------------------------------------------------------------------------------------------------+ | user_id | name | quote | +---------+--------+--------------------------------------------------------------------------------------------------------------------------+ | 1 | Kim | "Never let the fear of striking out keep you from playing the game." -Babe Ruth | | 2 | Ann | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt | | 3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin | | 4 | Jim | "Whoever is happy will make others happy too." -Anne Frank | | 5 | Timi | "It is better to fail in originality than to succeed in imitation." -Herman Melville | | 6 | Taeko | "You miss 100% of the shots you don't take." -Wayne Gretzky | | 7 | Irma | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss | | 8 | Iris | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou | +---------+--------+--------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec)
要转义百分号，可以在它前面加上反斜杠 (\)，这是 MySQL 的默认转义字符：
SELECT * FROM user_profiles WHERE quote LIKE '\%';
但是，此查询也无济于事，因为它指定 quote 列的内容应 only 由百分号组成。 因此，结果集将为空：
OutputEmpty set (0.00 sec)
要更正此问题，您需要在 LIKE 运算符之后的搜索模式的开头和结尾包含百分号通配符：
SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%\%%';
Output+---------+--------+----------------------------------------------------------------------------+ | user_id | name | quote | +---------+--------+----------------------------------------------------------------------------+ | 3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin | | 6 | Taeko | "You miss 100% of the shots you don't take." -Wayne Gretzky | +---------+--------+----------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
在这个查询中，反斜杠只转义了第二个百分号，而第一个和第三个仍然充当通配符。 因此，此查询将返回 quote 列至少包含一个百分号的每一行。
请注意，您还可以使用 ESCAPE 子句定义自定义转义字符，如下例所示：
SELECT user_id, name, email FROM user_profiles WHERE email LIKE '%@_%' ESCAPE '@';
Output+---------+--------+----------------------------+ | user_id | name | email | +---------+--------+----------------------------+ | 1 | Kim | bd_eyes@example.com | | 3 | Phoebe | poetry_man@example.com | | 4 | Jim | u_f_o@example.com | | 5 | Timi | big_voice@example.com | | 7 | Irma | soulqueen_NOLA@example.com | +---------+--------+----------------------------+ 5 rows in set (0.00 sec)
此查询将 @ 符号定义为转义字符，并返回 email 列至少包含一个下划线的每一行。 如果要删除 ESCAPE 子句，查询将返回表中的 every 行，因为它们中的每一个都至少包含一个 @ 符号。
结论
通过阅读本指南，您了解了如何使用和转义基于 SQL 的数据库的通配符。 此处列出的命令应该适用于任何使用 SQL 的数据库管理系统。 请记住，每个 SQL 数据库都使用自己独特的语言实现，因此您应该查阅 DBMS 的官方文档以获取对每个命令及其全套选项的更完整描述。
如果您想了解有关使用 SQL 的更多信息，我们鼓励您查看本系列中关于 如何使用 SQL 的其他教程。
