Mysql 简明教程
MySQL - LIKE Operator
MySQL LIKE Operator
MySQL 数据库中的 LIKE 运算符是一个逻辑运算符，用于根据指定模式从表中检索数据。
The LIKE Operator in MySQL database is a logical operator that is used to retrieve the data from a table, based on a specified pattern.
以非常基本的方式过滤和搜索表中的某些记录是使用 WHERE 子句。详细来说，只要我们想搜索精确匹配，带有“等于”号 (=) 的 WHERE 子句就能很好地工作。但可能需要筛选出表中值具有特定模式的所有结果。这可以通过在 WHERE 子句中使用 LIKE 运算符来处理。
To filter and search for some records in a table, in a very basic way is using a WHERE clause. To elaborate, a WHERE clause with the 'equal to' sign (=) works fine whenever we want search for an exact match. But there may be a requirement where we want to filter out all the results wherever the values in a table have a particular pattern. This can be handled by using a LIKE Operator in a WHERE clause.
LIKE 运算符通常与一个模式一起使用。但是，此模式（例如记录开头或结尾）的位置是通过一些称为通配符的字符决定的。如果没有通配符字符，则 LIKE 运算符与 WHERE 子句中的等于 (=) 符号完全相同。
The LIKE operator is usually used along with a pattern. However, the placement of this pattern (like at the beginning of the record, or at the ending) is decided using some characters known as wildcards. Without a wildcard character, the LIKE operator is very same as the equal to (=) sign in the WHERE clause.
Syntax
以下是 MySQL 中 LIKE 运算符的基本语法 -
Following is the basic syntax of the LIKE operator in MySQL −
SELECT column_name(s) FROM table_name
WHERE column_name LIKE [condition];-
You can specify any condition using the WHERE clause.
-
You can use the LIKE Operator along with the WHERE clause.
-
You can use the LIKE Operator in place of the equals to sign.
-
When LIKE is used along with % sign then it will work like a meta character search.
-
You can specify more than one condition using AND or OR operators.
-
A WHERE…LIKE clause can also be used in DELETE or UPDATE SQL commands to specify a condition.
Using LIKE Operator with Wildcards
通配符是在 SQL 查询中用于匹配数据中模式的特殊字符。以下是在 LIKE 运算符中结合使用的四个通配符 -
Wildcards are special characters used in SQL queries to match patterns in the data. Following are the four wildcards used in conjunction with the LIKE operator −
Note− 在 LIKE 运算符中，上述通配符字符可以单独使用，也可以相互组合。两个主要使用的通配符字符是 '%' 和 '_' 。
Note− In the 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
在下面的查询中，我们使用 CREATE 语句创建名为 CUSTOMERS 的表 -
In the following query, we are creating a table named CUSTOMERS using the CREATE statement −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);下面的查询将 7 条记录插入上述创建的表中 -
The below query inserts 7 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 following query to fetch all the records from the CUSTOMERS table −
Select * From CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
现在，我们使用带通配符的 LIKE 运算符检索以“esh”结尾的客户的名称 -
Now, we are retrieving the name of the customers ending with "esh" using the LIKE operator with wildcards −
SELECT * from CUSTOMERS WHERE NAME LIKE '%esh';Using LIKE Operator with AND/OR Operators
在 MySQL 中，我们还可以使用 LIKE 运算符与多个字符串模式，通过使用 AND 或 OR 运算符来选择行。
In MySQL, we can also use the LIKE operator with multiple string patterns for selecting rows by using the AND or OR operators.
Syntax
以下是使用 LIKE 运算符与 AND/OR 运算符的基本语法 -
Following is the basic syntax of using LIKE operator with AND/OR operator −
SELECT column_name(s)
FROM table_name
WHERE column1 LIKE pattern1 [AND|OR] column2
LIKE pattern2 [AND|OR] ...;Using NOT Operator with LIKE Operator
我们可以在 LIKE 运算符中结合使用 NOT 运算符，以提取不包含搜索模式中提供的特定字符串的行。
We can use the NOT operator in conjunction with LIKE operator to extract the rows which does not contain a particular string provided in the search pattern.
Syntax
以下是 SQL 中 NOT LIKE 运算符的基本语法 −
Following is the basic syntax of NOT LIKE operator in SQL −
SELECT column1, column2, ...
FROM table_name
WHERE column1 NOT LIKE pattern;Example
在下面的查询中，我们正在检索所有名称不以 K 开头的客户 -
In the following query, we are retrieving all the customers whose name does not start with K −
SELECT * FROM CUSTOMERS WHERE Name NOT LIKE 'K%';输出如下：
Following is the output −
Example
如果搜索模式与提供的字符串完全相同，则该运算符返回 1 -
If the search pattern is exactly as provided string, this operator returns 1 −
SELECT 'Tutorialspoint' LIKE 'Tutorialspoint';输出如下：
Following is the output −
如果搜索模式与字符串不完全相同，则返回 0 作为输出 -
If the search pattern is not exactly same as the string, it returns 0 as output −
SELECT 'Tutorialspoint' LIKE 'Tutorial';输出如下：
Following is the output −
Example
如果任何两个第一个操作数为 NULL，该操作符将返回 NULL。
If either of the first two operands is NULL, this operator returns NULL.
SELECT NULL LIKE 'value';以上程序的输出如下所示：
The output for the program above is produced as given below −
此处，我们向搜索模式操作数提供 NULL。
Here, we are providing NULL to the seach pattern operand.
SELECT 'Tutorialspoint' LIKE NULL;输出如下：
Following is the output −
Client Program
除了使用 MySQL LIKE 操作符基于指定模式过滤和搜索表中的某些记录外，我们也可以使用 Node.js、PHP、Java 和 Python 等客户端程序来获取相同的结果。
Besides using MySQL LIKE operator to filter and search for some records in a table, based on a specified pattern, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.