SQL LIKE 操作符 #
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
SQL LIKE 语法 #
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
SQL LIKE 操作符实例 #
下面的 SQL 语句选取 domainname 以字母 “p” 开始的所有记录：
MariaDB [learnsql]> select * from domains where domainname like 'p%';
+----+-----------+------------+------------+------------+
| ID | ICP | DomainName | RegDate | EndDate |
+----+-----------+------------+------------+------------+
| 14 | namesilo | pornie.in | 2016/3/10 | 2023/3/10 |
| 25 | namesilo | pornie.top | 2016/3/10 | 2027/3/10 |
| 48 | dnspod.cn | pushto.cn | 2022-01-28 | 2023-03-12 |
+----+-----------+------------+------------+------------+
3 rows in set (0.000 sec)
提示："%" 符号用于在模式的前后定义通配符（默认字母）。您将在下一章中学习更多有关通配符的知识。
下面的 SQL 语句选取 domainname 以字母 “n” 结尾的所有记录：
MariaDB [learnsql]> select * from domains where domainname like '%n';
+----+-----------+--------------+------------+------------+
| ID | ICP | DomainName | RegDate | EndDate |
+----+-----------+--------------+------------+------------+
| 9 | namesilo | regex.in | 2020/1/3 | 2023/1/3 |
| 13 | namesilo | raspi.in | 2014/2/27 | 2023/2/27 |
| 14 | namesilo | pornie.in | 2016/3/10 | 2023/3/10 |
| 44 | dnspod.cn | alair.cn | 2021-08-06 | 2022-12-10 |
| 46 | dnspod.cn | nasplus.cn | 2022-01-28 | 2023-03-08 |
| 47 | dnspod.cn | manjushri.cn | 2022-01-28 | 2023-03-11 |
| 48 | dnspod.cn | pushto.cn | 2022-01-28 | 2023-03-12 |
| 49 | dnspod.cn | gfwlist.cn | 2022-01-28 | 2023-03-23 |
| 50 | dnspod.cn | byhzg.cn | 2022-01-28 | 2023-04-14 |
+----+-----------+--------------+------------+------------+
9 rows in set (0.000 sec)
下面的 SQL 语句选取 domainname 包含模式 “o” 的所有记录：
MariaDB [learnsql]> select * from domains where domainname like '%o%';
+----+-----------+---------------+------------+------------+
| ID | ICP | DomainName | RegDate | EndDate |
+----+-----------+---------------+------------+------------+
| 4 | namesilo | getos.org | 2016/10/9 | 2022/10/9 |
| 6 | wanwang | dotbbq.com | 2017/10/28 | 2022/10/28 |
| 8 | namesilo | les.moe | 2015/12/31 | 2022/12/31 |
| 14 | namesilo | pornie.in | 2016/3/10 | 2023/3/10 |
| 15 | namesilo | aosp.me | 2016/3/17 | 2023/3/17 |
| 16 | namesilo | bailuyuan.org | 2017/3/23 | 2023/3/23 |
| 17 | wanwang | linuxuc.com | 2016/3/24 | 2023/3/24 |
| 19 | wanwang | unixetc.com | 2017/3/30 | 2023/3/30 |
| 20 | namesilo | ciux.org | 2010/4/26 | 2023/4/26 |
| 21 | namesilo | avlist.top | 2021/6/5 | 2023/6/5 |
| 22 | google | suopo.net | 2020/6/17 | 2023/6/17 |
| 24 | namesilo | ltecn.com | 2014/7/26 | 2025/7/26 |
| 25 | namesilo | pornie.top | 2016/3/10 | 2027/3/10 |
| 48 | dnspod.cn | pushto.cn | 2022-01-28 | 2023-03-12 |
+----+-----------+---------------+------------+------------+
14 rows in set (0.001 sec)
通过使用 NOT 关键字，您可以选取不匹配模式的记录。
下面的 SQL 语句选取 domainname 不包含模式 “s” 的所有记录：
MariaDB [learnsql]> select * from domains where domainname not like '%s%';
+----+-----------+---------------+------------+------------+
| ID | ICP | DomainName | RegDate | EndDate |
+----+-----------+---------------+------------+------------+
| 6 | wanwang | dotbbq.com | 2017/10/28 | 2022/10/28 |
| 7 | wanwang | lyq.wiki | 2018/12/27 | 2022/12/28 |
| 9 | namesilo | regex.in | 2020/1/3 | 2023/1/3 |
| 12 | namesilo | blbl.dev | 2020/1/17 | 2023/1/17 |
| 14 | namesilo | pornie.in | 2016/3/10 | 2023/3/10 |
| 16 | namesilo | bailuyuan.org | 2017/3/23 | 2023/3/23 |
| 17 | wanwang | linuxuc.com | 2016/3/24 | 2023/3/24 |
| 19 | wanwang | unixetc.com | 2017/3/30 | 2023/3/30 |
| 20 | namesilo | ciux.org | 2010/4/26 | 2023/4/26 |
| 24 | namesilo | ltecn.com | 2014/7/26 | 2025/7/26 |
| 25 | namesilo | pornie.top | 2016/3/10 | 2027/3/10 |
| 43 | dnspod.cn | zjq.xyz | 2021-08-06 | 2022-10-24 |
| 44 | dnspod.cn | alair.cn | 2021-08-06 | 2022-12-10 |
| 45 | dnspod.cn | ymqd.net | 2022-01-28 | 2023-03-03 |
| 50 | dnspod.cn | byhzg.cn | 2022-01-28 | 2023-04-14 |
| 52 | gandi.net | mua.wiki | 2022/7/19 | 2023/7/19 |
+----+-----------+---------------+------------+------------+
16 rows in set (0.001 sec)