SQL LIKE 操作符
SQL LIKE 操作符
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
LIKE操作符是 SQL 中用于在WHERE子句中进行模糊查询的关键字，它允许我们根据模式匹配来选择数据，通常与%和_通配符一起使用。
SQL LIKE 语法
SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;
参数说明：
- column1, column2, ...：要选择的字段名称，可以为多个字段。如果不指定字段名称，则会选择所有字段。
- table_name：要查询的表名称。
- column：要搜索的字段名称。
- pattern：搜索模式。
通配符
%：匹配任意字符（包括零个字符）。_：匹配单个字符。
实例
假设我们有一个名为 Products 的表，包含以下数据：
| ProductID | ProductName | Category |
|---|---|---|
| 1 | iPhone 12 | Electronics |
| 2 | Samsung Galaxy S21 | Electronics |
| 3 | Dell XPS 13 | Electronics |
| 4 | Nike Air Zoom | Footwear |
| 5 | Adidas Ultraboost | Footwear |
| 6 | Sony PlayStation 5 | Electronics |
使用 % 通配符找出所有以 "iPhone" 开头的产品：
SELECT ProductName, Category FROM Products WHERE ProductName LIKE 'iPhone%';
返回以下数据：
| ProductName | Category |
|---|---|
| iPhone 12 | Electronics |
使用 _ 通配符找出所有产品名称第二个字符为 "e" 的产品：
SELECT ProductName, Category FROM Products WHERE ProductName LIKE '_e%';
返回以下数据：
| ProductName | Category |
|---|---|
| Dell XPS 13 | Electronics |
结合 % 和 _ 通配符找出所有产品名称包含 "Zoom" 的产品：
SELECT ProductName, Category FROM Products WHERE ProductName LIKE '%Zoom%';
返回以下数据：
| ProductName | Category |
|---|---|
| Nike Air Zoom | Footwear |
演示数据库
在本教程中，我们将使用 RUNOOB 样本数据库。
下面是选自 "Websites" 表的数据：
vk.com/topic-234777584_54241449
vk.com/topic-234777907_55566500
vk.com/topic-234777876_54602217
vk.com/topic-234777897_55566501
vk.com/topic-234777925_54124685
vk.com/topic-234777883_54531825
vk.com/topic-234777957_55566499
vk.com/topic-234777971_55830467
vk.com/topic-234777908_54732679
vk.com/topic-234777875_54124684
vk.com/topic-234777787_55566498
vk.com/topic-234777886_54602216
vk.com/topic-234777887_55566497
vk.com/topic-234777846_54602215
vk.com/topic-234466423_54531824
vk.com/topic-234252226_54602214
vk.com/topic-234777891_55830466
vk.com/topic-234777903_54531823
vk.com/topic-234766022_54902518
vk.com/topic-234777923_54531821
vk.com/topic-234762422_54902517
vk.com/topic-234777871_55830465
vk.com/topic-234756933_54531820
vk.com/topic-234777928_54732678
vk.com/topic-234762548_54732677
vk.com/topic-234777584_54241448
vk.com/topic-234466429_54979486
vk.com/topic-234756700_55335748
vk.com/topic-234777876_54602213
vk.com/topic-234777897_55566496
vk.com/topic-234777957_55566495
vk.com/topic-234777875_54124683
vk.com/topic-234777908_54732676
vk.com/topic-234777888_54732675
vk.com/topic-234777787_55566494
vk.com/topic-234777901_55830464
vk.com/topic-234777886_54602212
vk.com/topic-234777887_55566493
vk.com/topic-234777846_54602211
vk.com/topic-234757176_54602210
vk.com/topic-234466423_54531819
vk.com/topic-234466708_54732674
vk.com/topic-234252226_54602209
vk.com/topic-234777903_54531818
vk.com/topic-234777923_54531817
vk.com/topic-234761470_55335747
vk.com/topic-234762422_54902516
vk.com/topic-234777871_55830463
vk.com/topic-234756933_54531816
vk.com/topic-234777928_54732673
vk.com/topic-234777584_54241447
vk.com/topic-234466429_54979485
vk.com/topic-234756700_55335746
vk.com/topic-234777957_55566491
vk.com/topic-234777875_54124682
vk.com/topic-234777886_54602208
vk.com/topic-234777901_55830462
vk.com/topic-234777887_55566490
vk.com/topic-234757176_54602207
vk.com/topic-234253030_55335745
vk.com/topic-234466423_54531814
vk.com/topic-234466708_54732672
vk.com/topic-234252226_54602206
vk.com/topic-234777881_55830461
vk.com/topic-234777903_54531812
vk.com/topic-234777923_54531811
vk.com/topic-234761470_55335743
vk.com/topic-234762422_54902515
vk.com/topic-234756933_54531810
vk.com/topic-234777905_54124681
vk.com/topic-234777584_54241446
vk.com/topic-234777876_54602205
vk.com/topic-234756700_55335742
vk.com/topic-234762356_54602204
vk.com/topic-234250764_54241445
vk.com/topic-234777843_54531809
vk.com/topic-234777901_55830460
vk.com/topic-234757176_54602202
vk.com/topic-234253030_55335741
vk.com/topic-234466423_54531808
vk.com/topic-234466708_54732671
vk.com/topic-234777888_54732670
vk.com/topic-234777912_54902513
vk.com/topic-234777881_55830459
vk.com/topic-234777913_54531807
vk.com/topic-234761470_55335740
vk.com/topic-234762422_54902511
vk.com/topic-234761493_54531806
vk.com/topic-234777905_54124680
vk.com/topic-234777584_54241444
vk.com/topic-234777876_54602201
vk.com/topic-234777883_54531805
vk.com/topic-234762356_54602200
vk.com/topic-234250764_54241443
vk.com/topic-234777843_54531804
vk.com/topic-234253030_55335739
vk.com/topic-234777937_55566489
vk.com/topic-234777952_54902510
vk.com/topic-234466708_54732669
vk.com/topic-234466423_54531803
vk.com/topic-234777912_54902509
vk.com/topic-234777881_55830458
vk.com/topic-234777888_54732668
vk.com/topic-234777857_55566488
vk.com/topic-234777913_54531802
vk.com/topic-234761470_55335738
vk.com/topic-234762422_54902508
vk.com/topic-234761493_54531801
vk.com/topic-234777905_54124679
vk.com/topic-234777907_55566487
vk.com/topic-234762356_54602199
vk.com/topic-234250764_54241441
vk.com/topic-234777843_54531800
vk.com/topic-234777901_55830457
vk.com/topic-234777937_55566486
vk.com/topic-234777952_54902506
vk.com/topic-234466708_54732665
vk.com/topic-234777912_54902504
vk.com/topic-234777888_54732666
vk.com/topic-234766022_54902505
vk.com/topic-234777857_55566485
vk.com/topic-234777913_54531799
vk.com/topic-234761470_55335737
vk.com/topic-234777877_55566483
vk.com/topic-234766256_54602197
vk.com/topic-234761493_54531798
vk.com/topic-234777905_54124678
vk.com/topic-234777883_54531797
vk.com/topic-234777907_55566482
vk.com/topic-234777846_54602196
vk.com/topic-234762356_54602195
vk.com/topic-234250764_54241439
vk.com/topic-234777843_54531796
vk.com/topic-234777901_55830456
vk.com/topic-234777937_55566481
vk.com/topic-234777952_54902503
vk.com/topic-234762405_54124677
vk.com/topic-234777888_54732664
vk.com/topic-234766022_54902501
vk.com/topic-234777912_54902502
vk.com/topic-234466708_54732663
vk.com/topic-234777891_55830455
vk.com/topic-234777857_55566480
vk.com/topic-234777913_54531795
vk.com/topic-234777877_55566478
vk.com/topic-234766256_54602194
vk.com/topic-234777871_55830454
vk.com/topic-234761493_54531793
vk.com/topic-234777872_54902500
vk.com/topic-234777897_55566477
vk.com/topic-234777908_54732662
vk.com/topic-234777905_54124674
vk.com/topic-234777875_54124675
vk.com/topic-234777883_54531791
vk.com/topic-234777846_54602193
vk.com/topic-234250764_54241438
vk.com/topic-234777901_55830453
vk.com/topic-234762356_54602191
vk.com/topic-234777843_54531789
vk.com/topic-234777907_55566474
vk.com/topic-234762405_54124673
vk.com/topic-234777952_54902499
vk.com/topic-234777886_54602192
vk.com/topic-234777837_55566475
vk.com/topic-234777937_55566476
vk.com/topic-234777888_54732661
vk.com/topic-234777923_54531786
vk.com/topic-234777912_54902496
vk.com/topic-234766022_54902495
vk.com/topic-234777891_55830452
vk.com/topic-234777857_55566473
vk.com/topic-234777913_54531788
vk.com/topic-234777877_55566472
vk.com/topic-234777928_54732660
vk.com/topic-234766256_54602190
vk.com/topic-234466429_54979481
vk.com/topic-234756933_54531787
vk.com/topic-234252226_54602189
vk.com/topic-234761493_54531785
vk.com/topic-234777871_55830451
vk.com/topic-234777872_54902494
vk.com/topic-234777897_55566470
vk.com/topic-234777876_54602188
vk.com/topic-234777908_54732658
vk.com/topic-234777852_54902493
vk.com/topic-234777847_55566469
vk.com/topic-234777875_54124672
vk.com/topic-234777883_54531784
vk.com/topic-234777887_55566468
vk.com/topic-234777846_54602186
vk.com/topic-234777901_55830450
vk.com/topic-234762405_54124671
vk.com/topic-234777952_54902492
vk.com/topic-234777886_54602185
vk.com/topic-234777937_55566467
vk.com/topic-234466423_54531783
vk.com/topic-234777837_55566466
vk.com/topic-234757176_54602184
vk.com/topic-234777903_54531782
vk.com/topic-234777907_55566465
vk.com/topic-234766022_54902491
vk.com/topic-234777891_55830449
vk.com/topic-234777857_55566464
vk.com/topic-234777877_55566463
vk.com/topic-234777928_54732657
vk.com/topic-234766256_54602183
vk.com/topic-234761470_55335735
vk.com/topic-234466429_54979480
vk.com/topic-234756933_54531781
vk.com/topic-234252226_54602182
vk.com/topic-234777923_54531780
mysql> SELECT * FROM Websites; +----+---------------+---------------------------+-------+---------+ | id | name | url | alexa | country | +----+---------------+---------------------------+-------+---------+ | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | | 3 | 菜鸟教程 | http://www.runoob.com/ | 5000 | USA | | 4 | 微博 | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | | 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND | +----+---------------+---------------------------+-------+---------+
SQL LIKE 操作符实例
下面的 SQL 语句选取 name 以字母 "G" 开始的所有客户：
实例
SELECT * FROM Websites
WHERE name LIKE 'G%';
执行输出结果：
提示："%" 符号用于在模式的前后定义通配符（默认字母）。您将在下一章中学习更多有关通配符的知识。
下面的 SQL 语句选取 name 以字母 "k" 结尾的所有客户：
实例
SELECT * FROM Websites
WHERE name LIKE '%k';
执行输出结果：
下面的 SQL 语句选取 name 包含模式 "oo" 的所有客户：
实例
SELECT * FROM Websites
WHERE name LIKE '%oo%';
执行输出结果：
通过使用 NOT 关键字，您可以选取不匹配模式的记录。
下面的 SQL 语句选取 name 不包含模式 "oo" 的所有客户：
实例
SELECT * FROM Websites
WHERE name NOT LIKE '%oo%';