Sql 简明教程
SQL - Wildcards
SQL Wildcards
SQL Wildcards 是用作字符串中一个或多个字符的替代品的特殊字符。它们与 SQL 中的 LIKE 运算符一起使用，用于在字符串中搜索特定的模式或比较不同的字符串。
SQL Wildcards are special characters used as substitutes for one or more characters in a string. They are used with the LIKE operator in SQL, to search for specific patterns in character strings or compare various strings.
SQL 中的 LIKE 运算符区分大小写，因此它只匹配与指定模式完全相同大小写的字符串。
The LIKE operator in SQL is case-sensitive, so it will only match strings that have the exact same case as the specified pattern.
以下是 SQL 中最常用的通配符 −
Following are the most commonly used wildcards in SQL −
S.No. |
Wildcard & Description |
1 |
The percent sign (%) Matches one or more characters. Note − MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character. |
2 |
The underscore () Matches one character. Note − MS Access uses a question mark (?) instead of the underscore () to match any one character. |
百分号 (%) 表示字符串内零个、一个或多个字符。下划线 (_) 表示一个字符或数字。这些符号还可以组合使用，以在 SQL 查询中执行复杂的模式搜索和匹配。
The percent sign (%) represents zero, one, or multiple characters within a string. The underscore (_) represents a single character or number. These symbols can also be used in combination to perform complex pattern searching and matching in SQL queries.
Syntax
以下是使用通配符的基本语法 -
Following is the basic syntax to use wildcard characters −
SELECT * FROM table_name
WHERE column_name LIKE [wildcard_pattern];我们可以使用 AND 或 OR 运算符组合 N 个条件。此处，[wildcard_pattern] 可以表示任何数字或字符串值。
We can combine N number of conditions using the AND or the OR operators. Here, the [wildcard_pattern] can represent any numeric or string value.
下表演示了在 WHERE 子句中结合 LIKE 运算符使用通配符的各种方式：
The following table demonstrates various ways of using wildcards in conjunction with the LIKE operator within a WHERE clause:
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. |
Example
首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表 -
Firstly, let us create a table named CUSTOMERS using the following query −
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 查询向 CUSTOMERS 表中添加记录 -
The following INSERT query adds records into the CUSTOMERS table −
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 −
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 开头的所有记录。
Here, we are displaying all the records from the CUSTOMERS table where the SALARY starts with 200.
SELECT * FROM CUSTOMERS WHERE SALARY LIKE '200%';