MySQL NOT LIKE用法及代码示例

MySQL 的 NOT LIKE 运算符可用于搜索与特定字符串模式不匹配的值。我们可以将两个通配符与NOT LIKE 运算符一起使用：%(百分比)和_(下划线)。

用法

SELECT column_name(s)
FROM table_name
WHERE column_name NOT LIKE value;

例子

考虑下表有关一些学生的信息：

可以使用此处的代码创建上述示例表。

百分比通配符

要返回 lname 不以 'Dav' 开头的所有行：

SELECT * FROM students WHERE lname NOT LIKE 'Dav%';



+------------+----------+--------+--------------+------+----------+
| student_id | fname    | lname  | day_enrolled | age  | username |
+------------+----------+--------+--------------+------+----------+
|          1 | Sky      | Towner | 2015-12-03   |   17 | stowner1 |
|          3 | Travis   | Apple  | 2018-08-14   |   18 | tapple3  |
|          5 | Benjamin | Town   | 2014-01-01   |   17 | btown5   |
+------------+----------+--------+--------------+------+----------+

请注意， % 表示 'Dav' 之后的任意数量的字符。

要返回 username 不包含 'dav' 的所有行：

SELECT * FROM students WHERE username NOT LIKE '%dav%';



+------------+----------+--------+--------------+------+----------+
| student_id | fname    | lname  | day_enrolled | age  | username |
+------------+----------+--------+--------------+------+----------+
|          1 | Sky      | Towner | 2015-12-03   |   17 | stowner1 |
|          3 | Travis   | Apple  | 2018-08-14   |   18 | tapple3  |
|          5 | Benjamin | Town   | 2014-01-01   |   17 | btown5   |
+------------+----------+--------+--------------+------+----------+

将 % 放在开头和结尾允许双端搜索。

下划线通配符

要返回名字不以 'S' 开头并以 'y' 结尾的三个字母的学生：

SELECT * FROM students WHERE fname NOT LIKE 'S_y';



+------------+----------+-------+--------------+------+----------+
| student_id | fname    | lname | day_enrolled | age  | username |
+------------+----------+-------+--------------+------+----------+
|          2 | Ben      | Davis | 2016-04-20   |   19 | bdavis2  |
|          3 | Travis   | Apple | 2018-08-14   |   18 | tapple3  |
|          4 | Arthur   | David | 2016-04-01   |   16 | adavid4  |
|          5 | Benjamin | Town  | 2014-01-01   |   17 | btown5   |
+------------+----------+-------+--------------+------+----------+

此查询返回名字不以 'S' 开头、以 'y' 结尾且包含 3 个字符的学生。我们返回除 'Sky Towner' 之外的所有学生。

组合通配符

要返回 username 没有 't' 作为第二个字符的学生：

SELECT * FROM students WHERE username NOT LIKE '_t%';



+------------+--------+-------+--------------+------+----------+
| student_id | fname  | lname | day_enrolled | age  | username |
+------------+--------+-------+--------------+------+----------+
|          2 | Ben    | Davis | 2016-04-20   |   19 | bdavis2  |
|          3 | Travis | Apple | 2018-08-14   |   18 | tapple3  |
|          4 | Arthur | David | 2016-04-01   |   16 | adavid4  |
+------------+--------+-------+--------------+------+----------+