介绍
像许多计算机语言一样，SQL 允许使用各种通配符。通配符是特殊的占位符，可以表示一个或多个其他字符或值。这是 SQL 中的一个方便功能，因为它允许您在不知道其中保存的确切值的情况下搜索数据库中的数据。
本指南将介绍如何使用 SQL 的指定通配符查询数据。
先决条件
为了遵循本指南，您需要一台运行某种类型的使用 SQL 的关系数据库管理系统 (RDBMS) 的计算机。本指南中的说明和示例已使用以下环境进行验证：
- 运行 Ubuntu 20.04 的服务器，具有管理权限的非 root 用户和使用 UFW 配置的防火墙，如我们的 Ubuntu 20.04 初始服务器设置指南中所述。
- 在服务器上安装并保护 MySQL，如如何在 Ubuntu 20.04 上安装 MySQL 中所述。本指南已使用非 root MySQL 用户进行验证，该用户使用步骤 3中描述的过程创建。
注意：请注意，许多 RDBMS 使用自己独特的 SQL 实现。尽管本教程中概述的命令适用于大多数 RDBMS，但如果您在 MySQL 以外的系统上测试它们，则确切的语法或输出可能会有所不同。
您还需要一个加载了一些示例数据的数据库和表，您可以练习使用通配符。如果您没有这个，您可以阅读以下连接到 MySQL 和设置示例数据库部分，以了解有关如何创建本指南将在整个示例中使用的数据库和表的详细信息。
连接到 MySQL 并设置示例数据库
如果您的 SQL 数据库系统在远程服务器上运行，请从本地计算机通过 SSH 连接到您的服务器：
- ssh sammy@your_server_ip
然后打开 MySQL 服务器提示，替换sammy为您的 MySQL 用户帐户的名称：
- 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', '[email protected]', '1945-07-20', '"Never let the fear of striking out keep you from playing the game." -Babe Ruth'),
- (2, 'Ann', '[email protected]', '1947-04-27', '"The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt'),
- (3, 'Phoebe', '[email protected]', '1950-07-17', '"100% of the people who give 110% do not understand math." -Demitri Martin'),
- (4, 'Jim', '[email protected]', '1940-08-13', '"Whoever is happy will make others happy too." -Anne Frank'),
- (5, 'Timi', '[email protected]', '1940-08-04', '"It is better to fail in originality than to succeed in imitation." -Herman Melville'),
- (6, 'Taeko', '[email protected]', '1953-11-28', '"You miss 100% of the shots you don\'t take." -Wayne Gretzky'),
- (7, 'Irma', '[email protected]', '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', '[email protected]', '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。
这些通配符专门用于WHERE带有LIKEorNOT LIKE运算符的查询子句中。
为了说明如何使用先决条件部分中的示例数据，假设您知道user_profiles表中列出的至少一个用户的姓名长度为三个字母并以“im”结尾，但您不确定他们是谁。因为您只是不确定这些用户名中的第一个字母，所以您可以运行以下使用_通配符的查询来查找他们是谁：
- SELECT * FROM user_profiles WHERE name LIKE '_im';
Output+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
| user_id | name | email | birthdate | quote |
+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
| 1 | Kim | [email protected] | 1945-07-20 | "Never let the fear of striking out keep you from playing the game." -Babe Ruth |
| 4 | Jim | [email protected] | 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 | [email protected] | 1947-04-27 | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt |
| 3 | Phoebe | [email protected] | 1950-07-17 | "100% of the people who give 110% do not understand math." -Demitri Martin |
| 5 | Timi | [email protected] | 1940-08-04 | "It is better to fail in originality than to succeed in imitation." -Herman Melville |
| 6 | Taeko | [email protected] | 1953-11-28 | "You miss 100% of the shots you don't take." -Wayne Gretzky |
| 7 | Irma | [email protected] | 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 | [email protected] | 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 | [email protected] |
| 8 | Iris | [email protected] |
+---------+------+----------------------------+
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 | [email protected] |
| 8 | Iris | [email protected] |
+---------+------+----------------------------+
2 rows in set (0.00 sec)
请注意，通配符不同于正则表达式。通常，通配符是指在glob-style 模式匹配中使用的字符，而正则表达式依赖于正则语言来匹配字符串模式。
转义通配符
有时您可能想要搜索包含 SQL 通配符之一的数据条目。在这种情况下，您可以使用转义字符，它会指示 SQL 忽略%or的通配符函数，_而是将它们解释为纯文本。
例如，假设您知道数据库中列出的至少几个用户有一个最喜欢的报价，其中包含一个百分号，但您不确定他们是谁。
您可以尝试运行以下查询：
- 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列的内容应仅包含百分比符号。因此，结果集将为空：
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 | [email protected] |
| 3 | Phoebe | [email protected] |
| 4 | Jim | [email protected] |
| 5 | Timi | [email protected] |
| 7 | Irma | [email protected] |
+---------+--------+----------------------------+
5 rows in set (0.00 sec)
此查询将@符号定义为转义字符，并返回其email列至少包含一个下划线的每一行。如果您要删除该ESCAPE子句，查询将返回表中的每一行，因为它们中的每一行都包含至少一个@符号。
结论
通过阅读本指南，您了解了如何使用和转义通配符基于 SQL 的数据库。此处概述的命令应该适用于任何使用 SQL 的数据库管理系统。请记住，每个 SQL 数据库都使用自己独特的语言实现，因此您应该查阅 DBMS 的官方文档，以获得对每个命令及其全套选项的更完整描述。
如果您想了解有关使用 SQL 的更多信息，我们鼓励您查看本系列中关于如何使用 SQL的其他教程。
