Sql 简明教程
SQL - WHERE Clause
The SQL Where Clause
SQL WHERE 子句用于筛选 DML 语句（如 SELECT、UPDATE 和 DELETE 等）获得的结果。我们可以在连接操作后使用 WHERE 子句从单个表或多个表中检索数据。
The SQL WHERE clause is used to filter the results obtained by the DML statements such as SELECT, UPDATE and DELETE etc. We can retrieve the data from a single table or multiple tables(after join operation) using the WHERE clause.
例如，你可以使用 WHERE 子句来检索组织中某部门的员工详情，或薪水高于/低于特定金额的员工，或有资格获得奖学金的学生详情等。此子句基本上提供了要检索哪些记录以及哪些要忽略的记录的说明。
For instance, you can use the WHERE clause to retrieve details of employees of a department in an organization, or employees earning salary above/below certain amount, or details of students eligible for scholarships etc. This clause basically provides the specification of which records to be retrieved and which are to be to be neglected.
Syntax
SQL WHERE 子句的基本语法如下所示：
The basic syntax of the SQL WHERE clause is as shown below −
DML_Statement column1, column2,... columnN
FROM table_name
WHERE [condition];这里，DML_Statement 可以是任何语句，例如 SELECT、UPDATE、DELETE 等。
Here, the DML_Statement can be any statement, such as SELECT, UPDATE, DELETE etc.
您可以使用 comparison or logical operators 来指定条件，例如 >、<、=、 LIKE, NOT 等。
You can specify a condition using the comparison or logical operators such as, >, <, =, LIKE, NOT, etc.
WHERE Clause with SELECT Statement
通常情况下，SELECT 语句用于从表中检索数据。如果我们对 SELECT 语句使用 WHERE 子句，则可以基于特定条件（或表达式）来过滤要检索的行。以下为此提供语法：
Typically, the SELECT statement is used to retrieve data from a table. If we use the WHERE clause with the SELECT statement, we can filter the rows to be retrieved based on a specific condition (or expression). Following is the syntax for it −
SELECT column1, column2, ...
FROM table_name
WHERE condition;Example
假设我们使用 CREATE TABLE 语句在 MySQL 数据库中创建名为 CUSTOMERS 的表，如下所示：
Assume we have created a table named CUSTOMERS in MySQL database using CREATE TABLE statement 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 查询可将 7 条记录插入此表中：
Following INSERT query inserts 7 records into this 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 created is as shown below −
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 大于 2000 的记录的 ID、NAME 和 SALARY 字段：
In the following query, we are fetching the ID, NAME and SALARY fields from the CUSTOMERS table for the records where the SALARY is greater than 2000 −
SELECT ID, NAME, SALARY FROM CUSTOMERS
WHERE SALARY > 2000;WHERE Clause with UPDATE Statement
UPDATE 语句用于修改表中现有记录。使用 SQL WHERE 子句与 UPDATE 语句，我们可以更新特定记录。如果未使用 WHERE 子句，则 UPDATE 语句将影响表的所有记录。以下是语法：
The UPDATE statement is used to modify the existing records in a table. Using the SQL WHERE clause with the UPDATE statement, we can update particular records. If the WHERE clause is not used, the UPDATE statement would affect all the records of a table. Following is the syntax −
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;Example
在以下查询中，我们通过在 UPDATE 语句中使用 WHERE 子句，将 Ramesh 命名的客户的工资提高了 10000：
In the following query, we are incrementing the salary of the customer named Ramesh by 10000 by using the WHERE clause along with the UPDATE statement −
UPDATE CUSTOMERS set SALARY = SALARY+10000
where NAME = 'Ramesh';Output
我们得到以下结果。我们可以观察到，已修改了 2 位客户的年龄：
We get the following result. We can observe that the age of 2 customers have been modified −
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0Verification
为了验证更改是否反映在表中，我们可以使用 SELECT 语句，如下一个查询中所示：
To verify if the changes are reflected in the table, we can use SELECT statement as shown in the following query −
SELECT * FROM CUSTOMERS WHERE NAME = 'Ramesh';该表显示如下：
The table is displayed as follows −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
12000.00 |
WHERE Clause with IN Operator
使用 IN 运算符，您可以在 where 子句中指定值列表或子查询。如果您对 SELECT 语句使用 WHERE 和 IN，它允许我们检索表中与指定列表中的任何值匹配的行。以下是语法：
Using the IN operator you can specify the list of values or sub query in the where clause. If you use WHERE and IN with the SELECT statement, it allows us to retrieve the rows in a table that match any of the values in the specified list. Following is the syntax for it −
WHERE column_name IN (value1, value2, ...);其中， column_name 是表的列， value1, value2, 等是我们想要与 column_name 进行对比的值的列表。
Where, the column_name is the column of a table and value1, value2, etc. are the list of values that we want to compare with the column_name.
WHERE Clause with NOT IN Operator
带 NOT IN 运算符的 WHERE 子句是对带 IN 运算符的 WHERE 子句的否定。
The WHERE clause with NOT IN operator is the negation of WHERE clause with the IN operator.
-
If you use WHERE with the IN operator, the DML statement will act on the the list of values (of a column) specified
-
Whereas, if you use WHERE with the NOT IN operator, the DML operation is performed on the values (of a column) that are not there in the specified list.
WHERE column_name NOT IN (value1, value2, ...);WHERE Clause with LIKE Operator
带 LIKE 运算符的 WHERE 子句允许我们过滤匹配特定模式的行。此特定模式由通配符（例如 %、_、[] 等）表示。以下是语法：
The WHERE clause with LIKE operator allows us to filter rows that matches a specific pattern. This specific pattern is represented by wildcards (such as %, _, [] etc). Following is the syntax −
WHERE column_name LIKE pattern;其中， column_name 是我们要针对其比较模式的列， pattern 是可以包含通配符（例如 %、_、[] 等）的字符串。
Where, column_name is the column that we want to compare the pattern against and pattern is a string that can contain wildcards (such as %, _, [] etc).
WHERE Clause with AND, OR Operators
我们在 SQL 中可以使用 AND 和 OR 运算符来组合 WHERE 子句中的多个条件，以筛选满足特定条件的行。AND 运算符将确保仅筛选满足所有条件的行，而 OR 运算符将筛选满足指定条件中的任何一个条件的记录。但是，这仅在指定一个条件不足以筛选所有必需的行时才使用。
We can use AND and OR operators together in SQL to combine multiple conditions in a WHERE clause to filter rows that meets the specified criteria. The AND operator will make sure only those rows are filtered that satisfy all the conditions and the OR operator will filter records that satisfy any one of the specified conditions. However, this is only used when specifying one condition is not enough to filter all the required rows.
以下是 WHERE 子句中使用 AND 和 OR 运算符的语法 -
Following is the syntax for using the AND and OR operators in a WHERE clause −
WHERE (condition1 OR condition2) AND condition3;Example
在以下查询中，我们基于一些条件从 CUSTOMERS 表中检索所有行。括号控制求值顺序，以便先应用 OR 运算符，然后应用 AND 运算符 -
In the following query, we are retrieving all rows from the CUSTOMERS table based on some conditions. The parentheses control the order of evaluation so that the OR operator is applied first, followed by the AND operator −
SELECT * FROM CUSTOMERS
WHERE (AGE = 25 OR salary < 4500)
AND (name = 'Komal' OR name = 'Kaushik');