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  |
+------------+--------+-------+--------------+------+----------+