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%';

Output

上述查询的输出如下所示:

The output of the above query is as given below −

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';

Output

执行给定的查询后,输出如下:

On executing the given query, the output is displayed as follows −

Example

在这里,我们正在查找所有以“d”结尾的记录:

Here, we are finding all the records where ADDRESS ends with 'd' −

SELECT * FROM CUSTOMERS
WHERE ADDRESS LIKE '%d';

Output

当我们执行上述查询时,输出将获得如下:

When we execute the above query, the output is obtained as follows −

Example

在以下查询中,我们尝试获取所有 SALARY 在任何位置都带有“1”的记录:

In the following query, we are trying to fetch all the records where SALARY has '1' in any position −

SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '%1%';

Output

从上述查询中产生的输出如下:

The output produced from the above query is as follows −

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';

Output

让我们编译并运行查询,以生成以下结果 −

Let us compile and run the query, to produce the following result −

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';

Output

当我们执行上述查询时,输出将获得如下:

When we execute the above query, the output is obtained as follows −

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%';

Output

执行给定的查询后,输出如下:

On executing the given query, the output is displayed as follows −

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_%_%';

Output

上述查询的输出如下所示 −

The output of the above query is produced as given below −

Example

下面的查询检索 NAME 的第二位包含“h”并且以“i”结尾的所有记录 −

The following query retrieves all records where NAME has 'h' in the second position and ends with 'i' −

SELECT * FROM CUSTOMERS
WHERE NAME LIKE '_h%i';

Output

如果我们编译并运行查询,则结果会生成如下 −

If we compile and run the query, the result is produced as follows −