Mysql 简明教程
MySQL - NOT LIKE Operator
MySQL NOT LIKE Operator
我们之前了解到 MySQL 数据库中的 LIKE 运算符是一种用于对数据库表执行模式匹配操作的逻辑运算符。而 NOT LIKE 运算符的定义与此 LIKE 运算符相反。
We have previously learned that the LIKE Operator in MySQL database is a logical operator used to perform pattern matching operation on a database table. And NOT LIKE Operator is defined as opposite of this LIKE operator.
LIKE 和 NOT LIKE 运算符都在数据库表中执行模式匹配。因此，它们都需要通配符和模式才能正常工作。然而，如果 LIKE 运算符用于查找使用通配符提到的相似模式，NOT LIKE 运算符则用于查找不包含指定模式的所有记录。
Both LIKE and NOT LIKE operators perform pattern matching in a database table. Thus, they both need wildcards and patterns to function. However, if the LIKE operator is used to find the similar patterns mentioned using the wildcards, NOT LIKE operator is used to find all the records that do not contain the specified pattern.
-
The NOT LIKE operator is nothing but the amalgamation of two SQL operators, NOT and LIKE operators. Thus, having the combination of their functionalities.
-
It is used to match a particular pattern in the given string and returns 0 in case of a match and returns 1 otherwise. If either of the two operands of this function is NULL, it returns NULL as result.
-
This operator is useful for finding strings that do not match a specific pattern or do not have certain characteristics.
Using NOT LIKE Operator with Wildcards
通配符是用于 SQL 查询中匹配数据模式的特殊字符。以下通配符可以与 NOT LIKE 运算符结合使用：
Wildcards are special characters used in SQL queries to match patterns in the data. Following wildcards can be used in conjunction with the NOT LIKE operator −
Note: 在 NOT LIKE 运算符中，上述通配符字符可以单独使用，也可以相互组合使用。两个主要使用的通配符字符是 '%' 和 '_' 。
Note: In the NOT LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other. The two mainly used wildcard characters are '%' and '_'.
Example
让我们通过使用以下查询创建一个名为 CUSTOMERS 的表：
Let us begin by creating a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS (
ID INT AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);使用以下 INSERT 语句，我们将记录插入到上述创建的表中：
Using the below INSERT statements, we are inserting records into the above created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );执行以下查询以显示 CUSTOMERS 表中存在的所有记录：
Execute the below query to display all the records present in the CUSTOMERS table −
Select * from CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
现在，让我们使用 MySQL NOTLIKE 运算符显示 CUSTOMERS 表中名称不以 'k' 开头的所有记录。
Now, let us use the MySQL NOTLIKE operator to displays the all the records in CUSTOMERS table whose name doesn’t starts with 'k'.
SELECT * FROM CUSTOMERS where NAME NOT LIKE 'k%';以下是名称不以“k”开头的记录：
Following are the records whose name doesn’t starts with 'k' −
以下查询显示了 NAME 不以 'ik' 结尾的客户记录。
The following query displays the records of customers whose NAME doesn’t end with 'ik'.
SELECT * FROM CUSTOMERS where NAME NOT LIKE '%ik';以下是名称不以“ik”结尾的记录：
Following are the records whose name doesn’t ends with 'ik' −
这里，我们显示了名称中不包含子字符串 'al' 的所有记录。
Here, we are displaying all the records whose name does not contains the substring 'al'.
SELECT * FROM CUSTOMERS where NAME NOT LIKE '%al%';以下记录的名称不包含子字符串“al” -
Following are the records whose name doesn’t contains the substring 'al' −
以下查询显示所有名称不以 'm' 开头且不以 'y' 结尾的记录。
The following query displays all the records whose name does not starts with 'm' and ends with 'y'.
SELECT * FROM CUSTOMERS WHERE NAME NOT LIKE 'm___y';正如我们在输出表中看到的，第七条记录被剔除了，因为该名称以“m”开头，以“y”结尾。
As we can see in the output table, the seventh record is eliminated because the name starts with 'm' and ends with 'y'.
以下查询显示所有名称不以“k”开头且正好有 6 个字符的客户名称。
The below query displays all customer names that does not start with 'k' and have exactly 6 characters.
SELECT * FROM CUSTOMERS WHERE name NOT LIKE 'k_____';输出如下：
Following is the output −
在此，我们显示 CUSTOMERS 表中的记录，其中 ADDRESS 列中记录的第二个字符不是“h”。
Here, we are displaying the records of CUSTOMERS table, where the second character of the records in ADDRESS column is not "h".
SELECT * FROM CUSTOMERS where ADDRESS NOT LIKE '_h%';输出如下：
Following is the output −
Using NOT LIKE Operator with AND/OR Operators
我们可以将 MySQL NOT LIKE 运算符与不同的字符串模式结合起来，使用 AND 或 OR 运算符对此模式进行选择。
We can use the MySQL NOT LIKE operator with different string patterns to choose rows, combining them with the AND or OR operators.
Syntax
以下是使用 NOT LIKE 运算符与 AND/OR 运算符的语法：
Following is the syntax of using NOT LIKE operator with AND/OR operator −
SELECT column_name(s)
FROM table_name
WHERE column1 NOT LIKE pattern1 [AND|OR] column2 NOT LIKE pattern2 [AND|OR] ...;Example
在以下示例中，我们显示 CUSTOMERS 表中所有名称不以 'k' 开头且地址不应以 'm' 开头的记录，并使用 AND 运算符：
In the following example, we are displaying all records from the CUSTOMERS table where name does not start with 'k' and the address should not start with 'm' using AND operator −
SELECT * FROM CUSTOMERS
WHERE name NOT LIKE 'k%' AND address NOT LIKE 'm%';NOT LIKE Operator on Strings
MySQL NOT LIKE 运算符不仅可以在数据库表上执行模式匹配，还可以对单个字符串执行模式匹配。在此，如果模式存在于给定的字符串中，结果将为 0；如果不存在，结果将为 1。结果使用 SQL SELECT 语句作为结果集进行检索。
The MySQL NOT LIKE operator can perform pattern matching not only on database tables but also on individual strings. Here, the result will obtain as 0 if the pattern exists in the given string, or 1 if it doesn’t. The result is retrieved as a result-set using the SQL SELECT statement.
Syntax
以下是 NOT LIKE 运算符在 MySQL 中的语法：
Following is the syntax of NOT LIKE operator in MySQL −
SELECT expression NOT LIKE pattern;Example
在以下查询中，模式“Tutorix”不存在于指定的字符串中。所以，该运算符将返回 1。
In the following query, the pattern 'Tutorix' is not present in the specified string. So, this operator will return 1.
SELECT 'Tutorialspoint' NOT LIKE 'Tutorix';执行上述查询将产生以下输出：
Executing the query above will produce the following output −
在此，模式“Tutorialspoint”存在于指定的字符串中。因此，它返回 0 作为输出。
Here, the pattern 'Tutorialspoint' is present in the specified string. Thus, it returns 0 as output.
SELECT 'Tutorialspoint' NOT LIKE 'Tutorialspoint';输出如下：
Following is the output −
Example
如果（字符串或模式操作数）之一为 NULL，则此运算符返回 NULL。在以下查询中，字符串为 NULL，所以输出将返回为 NULL。
If either (string or pattern operands) is NULL, this operator returns NULL. In the following query, the string is NULL, so that the output will be returned as NULL.
SELECT NULL NOT LIKE 'value';执行上述查询将产生以下输出：
Executing the query above will produce the following output −
在此，搜索模式为 NULL。所以，输出将返回为 NULL。
Here, the search pattern is NULL. So, the output will be returned as NULL.
SELECT 'Tutorialspoint' NOT LIKE NULL;输出如下：
Following is the output −
NOT LIKE Operator Using a Client Program
除了使用 MySQL 查询来执行 Not Like 运算符外，我们还可以使用像 Node.js、PHP、Java 和 Python 这样的客户端程序来达到相同的结果。
Besides using MySQL queries to perform the Not Like operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.