Mysql 简明教程
MySQL - Wildcards
The MySQL wildcards
MySQL wildcards 是与 LIKE 运算符结合使用的特殊字符,用于搜索表列中的文本模式。MySQL 提供两种通配符:百分号(%)和下划线(_)。
The MySQL wildcards are special characters used in combination with the LIKE operator to search for text patterns in a table column. MySQL provides two wildcards: percent (%) and underscore (_).
下表说明了 MySQL 中两种通配符的用例:
The following table mentions the use case of the two wildcards in MySQL −
Syntax
以下是 MySQL 中 % 和 _ 通配符的语法:
Following is the syntax of % and _ wildcards in MySQL −
SELECT * FROM table_name
WHERE column_name LIKE wildcard;
通配符字符可以组合使用。下表演示了在 WHERE 子句中使用 % 和 _ 与 LIKE 运算符的不同方式:
The wildcard characters can be used in combination with each other. The following table demonstrates different ways of using '%' and '_' with the LIKE operator in a WHERE clause −
The MySQL Percent % Wildcard
MySQL % 通配符是 SQL 查询中用于模式匹配的符号。它表示字符串中的任何字符序列(包括零个字符)。
The MySQL % wildcard is a symbol used in SQL queries for pattern matching. It represents any sequence of characters (including zero characters) within a string.
与 WHERE 子句中的 LIKE 运算符一起使用时,% 允许您搜索与指定模式匹配的值。
When used with the LIKE operator in a WHERE clause, % allows you to search for values that match a specified pattern.
Example
首先,让我们使用以下查询创建一个名为 CUSTOMERS 的表 −
First, let us create a table with the name CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(25) NOT NULL,
SALARY DECIMAL(18, 2),
PRIMARY KEY(ID)
);
现在,让我们使用 INSERT 语句将值插入上述创建的表中,如下所示:
Now, let us insert values into the table created above using the INSERT statement as shown below −
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 );
获得的 CUSTOMERS 表如下:−
The CUSTOMERS table obtained is as follows −
在这里,我们正在检索 CUSTOMERS 表中 SALARY 以 2000 开头的所有记录:
Here, we are retrieving all the records from the CUSTOMERS table where SALARY starts with 2000 −
SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '2000%';
Example
在以下查询中,我们正在获取所有以“D”开头且以“i”结尾的记录:
In the following query, we are fetching all the records where ADDRESS starts with 'D' and ends with 'i' −
SELECT * FROM CUSTOMERS
WHERE ADDRESS LIKE 'D%i';
Example
在这里,我们正在查找所有以“d”结尾的记录:
Here, we are finding all the records where ADDRESS ends with 'd' −
SELECT * FROM CUSTOMERS
WHERE ADDRESS LIKE '%d';
The MySQL Underscore _ Wildcard
MySQL 下划线通配符表示其使用位置处的单个字符。当与 WHERE 子句中的 LIKE 运算符结合使用时,下划线通配符使您能够搜索与单个字符占位符匹配特定模式的值。
The MySQL underscore Wildcard represents a single character at the position where it is used. When combined with the LIKE operator in a WHERE clause, the underscore wildcard allows you to search for values that match a specific pattern with a single character placeholder.
Example
在本文示例中,我们检索以某个字符开头并后跟“ardik”的全部 CUSTOMERS 名称 −
Here, we are retrieving all the CUSTOMERS with NAME starting with a character, followed by 'ardik' −
SELECT * FROM CUSTOMERS
WHERE NAME LIKE '_ardik';
Example
现在,我们检索以“M”开头,后跟任意字符,后跟“f”,后跟任意字符,后跟“y”的全部 CUSTOMERS 名称 −
Now, we are retrieving all CUSTOMERS with NAME starting with 'M', followed by any character, followed by 'f', followed by any character, followed by 'y' −
SELECT * FROM CUSTOMERS
WHERE NAME LIKE 'M_f_y';
Example
在下面的查询中,我们检索 SALARY 的第二位、第三位和第四位包含“500”的所有记录 −
In the below query, we are retrieving all the records where SALARY have '500' in the second, third, and fourth positions −
SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '_500%';
Example
在下面的查询中,我们检索 ADDRESS 以“M”开头并且长度至少为 3 个字符的所有记录 −
In the following query, we are retrieving all the records where ADDRESS starts with 'M' and is at least 3 characters in length −
SELECT * FROM CUSTOMERS
WHERE ADDRESS LIKE 'M_%_%';