SQL LIKE运算符


SQL LIKE运算符

LIKE运算符定义

LIKE运算符用于WHERE或HAVING （作为GROUP BY一部分），以便在列中包含特定字符模式时将所选行限制为项。

本指南将演示：

  • 确定字符串是否以给定的字符串模式开始或结束
  • 确定字符串中间是否存在模式
  • 确定字符串中是否包含字符串

列以给定的字符串模式开始或结束

此SQL将选择FullName以“Monique”开头或以“Greene”结尾的学生。

SELECT studentID, FullName, sat_score, rcd_updated
FROM student
WHERE
FullName LIKE 'Monique%' OR -- note the % at the end but not the beginning
FullName LIKE '%Greene'; -- note the % at the beginning but not the end

+-----------+---------------+-----------+---------------------+
 | studentID | FullName      | sat_score | rcd_updated         |
 +-----------+---------------+-----------+---------------------+
 |         1 | Monique Davis |       400 | 2017-08-16 15:34:50 |
 |         5 | Alvin Greene  |      1200 | 2017-08-16 15:34:50 |
 +-----------+---------------+-----------+---------------------+
 2 rows in set (0.00 sec)

字符串模式位于列的中间

此SQL将选择名称中包含“ree”的学生。

SELECT studentID, FullName, sat_score, rcd_updated
FROM student
WHERE FullName LIKE '%ree%'; -- note the % at the beginning AND at the end

+-----------+----------------+-----------+---------------------+
 | studentID | FullName       | sat_score | rcd_updated         |
 +-----------+----------------+-----------+---------------------+
 |         5 | Alvin Greene   |      1200 | 2017-08-16 15:34:50 |
 |         6 | Sophie Freeman |      1200 | 2017-08-16 15:34:50 |
 +-----------+----------------+-----------+---------------------+
 2 rows in set (0.00 sec)

字符串不在列中

您可以在LIKE之前放置“NOT”以使用字符串模式排除行而不是选择它们。 此SQL排除FullName列中包含“cer Pau”和“Ted”的记录。

SELECT studentID, FullName, sat_score, rcd_updated
FROM student
WHERE FullName NOT LIKE '%cer Pau%' AND FullName NOT LIKE '%"Ted"%';

+-----------+----------------------+-----------+---------------------+
 | studentID | FullName             | sat_score | rcd_updated         |
 +-----------+----------------------+-----------+---------------------+
 |         1 | Monique Davis        |       400 | 2017-08-16 15:34:50 |
 |         2 | Teri Gutierrez       |       800 | 2017-08-16 15:34:50 |
 |         4 | Louis Ramsey         |      1200 | 2017-08-16 15:34:50 |
 |         5 | Alvin Greene         |      1200 | 2017-08-16 15:34:50 |
 |         6 | Sophie Freeman       |      1200 | 2017-08-16 15:34:50 |
 |         8 | Donald D. Chamberlin |      2400 | 2017-08-16 15:35:33 |
 |         9 | Raymond F. Boyce     |      2400 | 2017-08-16 15:35:33 |
 +-----------+----------------------+-----------+---------------------+
 7 rows in set (0.00 sec)

以下是与上面的where子句结果集进行比较的当前完整学生列表。

SELECT studentID, FullName, sat_score, rcd_updated FROM student;

+-----------+------------------------+-----------+---------------------+
| studentID | FullName               | sat_score | rcd_updated         |
+-----------+------------------------+-----------+---------------------+
|         1 | Monique Davis          |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez         |       800 | 2017-08-16 15:34:50 |
|         3 | Spencer Pautier        |      1000 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey           |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene           |      1200 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman         |      1200 | 2017-08-16 15:34:50 |
|         7 | Edgar Frank "Ted" Codd |      2400 | 2017-08-16 15:35:33 |
|         8 | Donald D. Chamberlin   |      2400 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce       |      2400 | 2017-08-16 15:35:33 |
+-----------+------------------------+-----------+---------------------+
9 rows in set (0.00 sec)

更多SQL教程

学习更多SQL教程