介绍
与许多计算机语言一样,SQL允许使用各种 _wildcard 字符。 _wildcards 是特殊的位置符号,可以代表一个或多个其他字符或值。
本指南将介绍如何使用 SQL 的指定 wildcards 查询数据。
前提条件
要遵循本指南,您需要运行某种类型的关系数据库管理系统(RDBMS)的计算机,该系统使用SQL。
- 运行 Ubuntu 20.04 的服务器,具有非 root 用户管理权限和与 UFW 配置的防火墙,如我们在 [Ubuntu 20.04 的初始服务器设置指南] 中所描述的。
<$>[注] 注:请注意,许多RDBMS使用自己的独特的SQL实现程序,虽然本教程中描述的命令将在大多数RDBMS上工作,但如果您在MySQL以外的系统上测试它们,精确的语法或输出可能会有所不同。
你还需要一个数据库和表加载一些样本数据,你可以练习使用与野卡. 如果你没有这个,你可以阅读以下 连接到MySQL和设置样本数据库的部分有关如何创建一个数据库和表,这本指南将使用在整个示例。
连接到MySQL并设置样本数据库
如果您的 SQL 数据库系统在远程服务器上运行,则从本地计算机输入 SSH 到服务器:
1[environment local]
2ssh sammy@your_server_ip
然后打开MySQL服务器提示,用您的MySQL用户帐户的名称代替sammy:
1mysql -u sammy -p
创建一个名为wildcardsDB的数据库:
1CREATE DATABASE wildcardsDB;
如果数据库创建成功,您将收到这样的输出:
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
要选择wildcardsDB数据库,请运行以下USE语句:
1USE wildcardsDB;
1[secondary_label Output]
2Database changed
选择数据库后,您可以使用以下命令创建一个表,例如,假设您想要创建一个名为user_profiles的表,以存储应用程序用户的个人资料信息。
它还将作为表的主要密钥,每个值都作为其相应行
- 名称的独特标识符功能:每个用户的名称,使用
varchar数据类型表示最多30个字符 - 电子邮件:这个列将包含用户的电子邮件地址,也用
varchar数据类型表示,但最多40个字符 - 出生日期:使用
日期数据类型,这个列将包含每个用户的出生日期 - 评论:每个用户的喜好评论。
运行以下命令来创建此样本表:
1CREATE TABLE user_profiles (
2user_id int,
3name varchar(30),
4email varchar(40),
5birthdate date,
6quote varchar(300),
7PRIMARY KEY (user_id)
8);
1[secondary_label Output]
2Database changed
然后将一些样本数据插入空表中:
1INSERT INTO user_profiles
2VALUES
3(1, 'Kim', '[email protected]', '1945-07-20', '"Never let the fear of striking out keep you from playing the game." -Babe Ruth'),
4(2, 'Ann', '[email protected]', '1947-04-27', '"The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt'),
5(3, 'Phoebe', '[email protected]', '1950-07-17', '"100% of the people who give 110% do not understand math." -Demitri Martin'),
6(4, 'Jim', '[email protected]', '1940-08-13', '"Whoever is happy will make others happy too." -Anne Frank'),
7(5, 'Timi', '[email protected]', '1940-08-04', '"It is better to fail in originality than to succeed in imitation." -Herman Melville'),
8(6, 'Taeko', '[email protected]', '1953-11-28', '"You miss 100% of the shots you don\'t take." -Wayne Gretzky'),
9(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'),
10(8, 'Iris', '[email protected]', '1961-01-05', '"You will face many defeats in life, but never let yourself be defeated." -Maya Angelou');
1[secondary_label Output]
2Query OK, 8 rows affected (0.00 sec)
3Records: 8 Duplicates: 0 Warnings: 0
有了这一点,您已经准备好跟随本指南的其余部分,并开始学习如何使用野生卡来查询 SQL 中的数据。
使用Wildcards查询数据
正如介绍中提到的, _wildcards 是特殊的位置符号,可以代表一个或多个其他字符或值。
在 SQL 中,只有两个定义的 wildcard 字符:
_:当被用作 wildcard 时,一个 underscore 代表一个单个字符. 例如,s_mmy会匹配sammy,sbmmy或sxmmy.%:百分比 wildcard 代表零或更多字符. 例如,s%mmy会匹配sammy,saaaaaammy或smmy。
这些 wildcards 仅用于查询的WHERE条款,使用LIKE或NOT LIKE运算符。
为了说明使用前提部分的样本数据,假设您知道至少在user_profiles表中列出的用户中有一个名称,长三个字母,结尾为im,但您不确定他们是谁。
1SELECT * FROM user_profiles WHERE name LIKE '_im';
1[secondary_label Output]
2+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
3| user_id | name | email | birthdate | quote |
4+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
5| 1 | Kim | [email protected] | 1945-07-20 | "Never let the fear of striking out keep you from playing the game." -Babe Ruth |
6| 4 | Jim | [email protected] | 1940-08-13 | "Whoever is happy will make others happy too." -Anne Frank |
7+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
82 rows in set (0.00 sec)
<$>[注]
注:在本示例中,一个星座(*)跟随SELECT。
在某些应用程序和编程语言中,甚至在一些 SQL 实现中,星座被用作代表零或多个字符的野卡字符,就像本示例中使用的百分比字符一样。
NOT LIKE运算器具有LIKE的相反效果,而不是返回与Wildcard模式匹配的每个记录,它会返回不匹配该模式的每个行。
1SELECT * FROM user_profiles WHERE name NOT LIKE '_im';
这次,在名称列中的值匹配_im的每个行都被排除在结果集中:
1[secondary_label Output]
2+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+
3| user_id | name | email | birthdate | quote |
4+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+
5| 2 | Ann | [email protected] | 1947-04-27 | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt |
6| 3 | Phoebe | [email protected] | 1950-07-17 | "100% of the people who give 110% do not understand math." -Demitri Martin |
7| 5 | Timi | [email protected] | 1940-08-04 | "It is better to fail in originality than to succeed in imitation." -Herman Melville |
8| 6 | Taeko | [email protected] | 1953-11-28 | "You miss 100% of the shots you don't take." -Wayne Gretzky |
9| 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 |
10| 8 | Iris | [email protected] | 1961-01-05 | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou |
11+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+
126 rows in set (0.00 sec)
作为另一个例子,假设您知道数据库中列出的几个用户有以我开头的名称,但您无法记住所有用户。
1SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'I%';
1[secondary_label Output]
2+---------+------+----------------------------+
3| user_id | name | email |
4+---------+------+----------------------------+
5| 7 | Irma | [email protected] |
6| 8 | Iris | [email protected] |
7+---------+------+----------------------------+
82 rows in set (0.00 sec)
请注意,在MySQL中,默认情况下,LIKE和NOT LIKE操作员不是案例敏感的,这意味着之前的查询将返回相同的结果,即使您在野卡模式中没有利用I:
1SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'i%';
1[secondary_label Output]
2+---------+------+----------------------------+
3| user_id | name | email |
4+---------+------+----------------------------+
5| 7 | Irma | [email protected] |
6| 8 | Iris | [email protected] |
7+---------+------+----------------------------+
82 rows in set (0.00 sec)
请注意,野卡与常规表达式不同,一般来说,野卡是指在 [glob-style pattern matching](https://en.wikipedia.org/wiki/Glob_(programming)中使用的字符,而常规表达式则依赖于 常规语言来匹配字符串模式。
逃避野生卡片角色
在这种情况下,您可以使用 escape character来指示 SQL 忽略 % 或_ 的 wildcard 函数,并将其解释为简单文本。
例如,假设您知道数据库中列出的至少几个用户有包含百分比标志的喜爱报价,但您不确定他们是谁。
您可以尝试运行以下查询:
1SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%';
由于百分比符号为任何长度的字符串的站点,它会返回表中的每个行:
1[secondary_label Output]
2+---------+--------+--------------------------------------------------------------------------------------------------------------------------+
3| user_id | name | quote |
4+---------+--------+--------------------------------------------------------------------------------------------------------------------------+
5| 1 | Kim | "Never let the fear of striking out keep you from playing the game." -Babe Ruth |
6| 2 | Ann | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt |
7| 3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin |
8| 4 | Jim | "Whoever is happy will make others happy too." -Anne Frank |
9| 5 | Timi | "It is better to fail in originality than to succeed in imitation." -Herman Melville |
10| 6 | Taeko | "You miss 100% of the shots you don't take." -Wayne Gretzky |
11| 7 | Irma | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss |
12| 8 | Iris | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou |
13+---------+--------+--------------------------------------------------------------------------------------------------------------------------+
148 rows in set (0.00 sec)
若要逃避百分比符号,您可以先用 backslash (\),MySQL 的默认逃避字符:
1SELECT * FROM user_profiles WHERE quote LIKE '\%';
但是,此查询也不会有用,因为它指定引用列的内容只应包含一个百分比符号,因此结果集将是空的:
1[secondary_label Output]
2Empty set (0.00 sec)
要纠正此问题,您需要在搜索模式的开始和结束中包含百分比符号 wildcards,然后使用LIKE运算符:
1SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%\%%';
1[secondary_label Output]
2+---------+--------+----------------------------------------------------------------------------+
3| user_id | name | quote |
4+---------+--------+----------------------------------------------------------------------------+
5| 3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin |
6| 6 | Taeko | "You miss 100% of the shots you don't take." -Wayne Gretzky |
7+---------+--------+----------------------------------------------------------------------------+
82 rows in set (0.00 sec)
在此查询中,反转只会逃离第二个百分比符号,而第一个和第三个仍然作为野卡符号,因此,此查询将返回每个包含至少一个百分比符号的行。
请注意,您还可以使用ESCAPE条款定义自定义逃避字符,如下示例所示:
1SELECT user_id, name, email FROM user_profiles WHERE email LIKE '%@_%' ESCAPE '@';
1[secondary_label Output]
2+---------+--------+----------------------------+
3| user_id | name | email |
4+---------+--------+----------------------------+
5| 1 | Kim | [email protected] |
6| 3 | Phoebe | [email protected] |
7| 4 | Jim | [email protected] |
8| 5 | Timi | [email protected] |
9| 7 | Irma | [email protected] |
10+---------+--------+----------------------------+
115 rows in set (0.00 sec)
此查询将@符号定义为逃避字符,并返回每个电子邮件列中包含至少一个标签的行,如果要删除ESCAPE条款,查询将返回表中的每行,因为每行都包含至少一个@符号。
结论
通过阅读本指南,您学会了如何使用和逃避基于SQL的数据库。 这里描述的命令应该在使用SQL的任何数据库管理系统上工作。 请记住,每个SQL数据库都使用其独特的语言实现,因此您应该咨询您的DBMS的官方文档,以获得对每个命令和其完整的选项集的更完整的描述。
如果您想了解有关使用 SQL 的更多信息,我们鼓励您查看本系列中的其他教程在 如何使用 SQL。