Sql 简明教程
SQL - Like Operator
The SQL LIKE Operator
SQL LIKE 运算符用于在表中检索基于指定模式的列中的数据。
The SQL LIKE operator is used to retrieve the data in a column of a table, based on a specified pattern.
与 UPDATE、DELETE 和 SELECT 语句的 WHERE 子句一起使用，以根据给定的模式过滤行。使用 Wildcards 指定这些模式。
It is used along with the WHERE clause of the UPDATE, DELETE and SELECT statements, to filter the rows based on the given pattern. These patterns are specified using Wildcards.
假设我们需要提交所有以“K”开头的学生名单。我们可以在 LIKE 运算符的帮助下获取此列表，如下所示：
Suppose we need to submit the list of all the students whose name starts with 'K'. We can obtain this with the help of the LIKE operator as follows −
WHERE student_name LIKE 'K%';此处， % 是通配符，表示零个、一个或多个字符。表达式 K% 指定它将显示所有名称 starts with “k”的学生的列表。
Here, the % is a wild card which represents zero, one or multiple characters. And the expression K% specifies that it will display the list of all the students whose name starts with 'k'.
What are wild cards?
SQL 通配符是用于 SQL 查询中匹配数据模式的特殊字符。以下是 MySQL 数据库中与 LIKE 运算符结合使用的通配符：
SQL wildcards are special characters used in SQL queries to match patterns in the data. Following are the wildcards used in conjunction with the LIKE operator in MySQL database −
S.No |
WildCard & Definition |
1 |
% The percent sign represents zero, one or multiple characters. |
2 |
_ The underscore represents a single number or character. |
下表给出了一些示例，展示了包含“%”和“_”的不同 LIKE 运算符的 WHERE 子句：
The table given below has a few examples showing the WHERE clause having different LIKE operators with '%' and '_' −
S.No |
Statement & Description |
1 |
WHERE SALARY LIKE '200%' Finds any values that start with 200. |
2 |
WHERE SALARY LIKE '%200%' Finds any values that have 200 in any position. |
3 |
WHERE SALARY LIKE '_00%' Finds any values that have 00 in the second and third positions. |
4 |
WHERE SALARY LIKE '2_%_%' Finds any values that start with 2 and are at least 3 characters in length. |
5 |
WHERE SALARY LIKE '%2' Finds any values that end with 2. |
6 |
WHERE SALARY LIKE '_2%3' Finds any values that have a 2 in the second position and end with a 3. |
7 |
WHERE SALARY LIKE '2___3' Finds any values in a five-digit number that start with 2 and end with 3. |
The '%' Wildcard character
% 符号表示零个或多个字符。“%”通配符匹配任何长度的字符串，甚至包括长度为零。
The % sign represents zero or multiple characters. The '%' wildcard matches any length of a string which even includes the zero length.
Example
为了更好地理解它，让我们考虑一下 CUSTOMERS 表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和工资等，如下所示：
To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −
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)
);现在，使用如下所示的 INSERT 语句向此表中插入值：
Now, insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS 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 );该表将按如下方式创建：
The table will be created as follows −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
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 表中所有 SALARY 以 200 开头的记录：
Now, let us display all the records from the CUSTOMERS table, where the SALARY starts with 200 −
SELECT * FROM CUSTOMERS WHERE SALARY LIKE '200%';Output
这将产生以下结果 -
This would produce the following result −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
Example
以下是显示先前创建的 CUSTOMERS 表中所有 NAME 在任何位置都有“al”的记录的查询。此处，我们在 LIKE 条件中使用了多个“%”通配符：
Below is the query that displays all the records from the CUSTOMERS table previously created with the NAME that has 'al' in any position. Here, we are using multiple '%' wildcards in the LIKE condition −
SELECT * FROM CUSTOMERS WHERE NAME LIKE '%al%';The '_' wildcard character
underscore 通配符表示单个数字或字符。单个“_”表示与“%”通配符类似的正好一个字符。
The underscore wild card represents a single number or character. A single '_' looks for exactly one character similar to the '%' wildcard.
Example
以下是显示先前创建的 CUSTOMERS 表中所有 NAME 以 K 开头且长度至少为 4 个字符的记录的查询：
Following is the query which would display all the records from the CUSTOMERS table previously created, where the Name starts with K and is at least 4 characters in length −
SELECT * FROM CUSTOMERS WHERE NAME LIKE 'K___%';Output
获得的结果如下：
The result obtained is given below −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
LIKE operator with OR
我们还可以使用 AND 或 OR 运算符将 LIKE 运算符与多个字符串模式一起用于选择行。
We can also use the LIKE operator with multiple string patterns for selecting rows by using the AND or OR operators.
Syntax
以下是使用 LIKE 运算符和 OR 运算符的基本语法 −
Following is the basic syntax of using LIKE operator with OR operator −
SELECT column1, column2, ...
FROM table_name
WHERE column1 LIKE pattern1 OR column2 LIKE pattern2 OR ...;NOT operator with the LIKE condition
我们将 NOT 运算符与 LIKE 一起使用，以提取不包含搜索模式中提供的特定字符串的行。
We use the NOT operator with LIKE 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;Escape characters with LIKE operator
SQL 中的转义字符用于从 LIKE 运算符的表达式中排除某些通配符。通过这样做，我们可以在其常规意义上使用这些字符。
The escape character in SQL is used to exclude certain wildcard characters from the expression of the LIKE operator. By doing so, we can use these characters in their general sense.
使用转义字符，我们还可以避免使用 SQL 语法中保留的、表示特定命令的字符，例如单引号 ' 、 % 和 _ 。
Using the escape character, we can also avoid using the characters that are reserved in SQL syntax to denote specific commands, such as the single quote ', % and _.
例如，如果你需要在 LIKE 条件中将 % 搜索为常量，那么可以使用转义字符来实现。
For example, if you need to search for % as a literal in the LIKE condition, then it is done using Escape character.
Syntax
将 LIKE 运算符与转义字符一起使用的语法如下 −
The syntax for using the LIKE operator with escape characters is as follows −
SELECT column1, column2, ...
FROM table_name
WHERE column1 LIKE 'pattern ESCAPE escape_character';其中，
Where,
-
pattern is the pattern you want to match.
-
ESCAPE is the keyword that indicates the escape character
-
escape_character is the character that you want to use as the escape character.
Example
让我们使用下面的查询创建一个新表 EMPLOYEE −
Let us create a new table EMPLOYEE using the query below −
CREATE TABLE EMPLOYEE (
SALARY DECIMAL (18,2) NOT NULL,
BONUS_PERCENT VARCHAR (20)
);现在，我们可以使用 INSERT 语句将值插入到此空表中，如下所示：
Now, we can insert values into this empty tables using the INSERT statement as follows −
INSERT INTO EMPLOYEE VALUES
(67000.00, '45.00'),
(54000.00, '20.34%'),
(75000.00, '51.00'),
(84000.00, '56.82%');Employee 表由組織中員工的薪資和薪資中獎金百分比組成，如下所示 −
The Employee table consists of the salary of employees in an organization and the bonus percentage in their salary as shown below −
SALARY |
BONUS_PERCENT |
67000.00 |
45.00 |
54000.00 |
20.34% |
75000.00 |
51.00 |
84000.00 |
56.82% |
现在，我们正在显示 EMPLOYEE 表中的所有记录，其中 BONUS_PERCENT 包含 % 常量 −
Now, we are displaying all the records from the EMPLOYEE table, where the BONUS_PERCENT contains the % literal −
SELECT * FROM EMPLOYEE
WHERE BONUS_PERCENT LIKE'%!%%' ESCAPE '!';Output
这会产生以下结果 −
This will produce the following result −
SALARY |
BONUS_PERCENT |
54000.00 |
20.34% |
84000.00 |
56.82% |