WHERE 子句用于从数据库表中筛选数据，仅返回满足指定条件的行。它通常与 SELECT、UPDATE、DELETE 等 SQL 语句一起使用，以限制查询范围。

---

1. WHERE 语法

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• condition 是一个表达式，它返回 TRUE、FALSE 或 NULL。
• 只有 TRUE 的行会被返回或受影响。

---

2. WHERE 子句示例

假设有一个 employees 表：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    department VARCHAR(50),
    salary DECIMAL(10,2)
);
INSERT INTO employees (name, age, department, salary) VALUES
('Alice', 30, 'HR', 6000),
('Bob', 35, 'IT', 8000),
('Charlie', 28, 'Finance', 5500),
('David', 40, 'IT', 9500),
('Eve', 25, 'HR', 5000);

2.1 使用 WHERE 进行条件筛选

SELECT * FROM employees WHERE age > 30;

结果：

id | name  | age | department | salary
---+-------+-----+-----------+--------
2  | Bob   |  35 | IT        | 8000.00
4  | David |  40 | IT        | 9500.00

---

3. WHERE 子句中的常见运算符

3.1 比较运算符

运算符	说明
=	等于
<> 或 !=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于

示例：

SELECT * FROM employees WHERE salary >= 8000;

id | name  | age | department | salary
---+-------+-----+-----------+--------
2  | Bob   |  35 | IT        | 8000.00
4  | David |  40 | IT        | 9500.00

---

3.2 逻辑运算符

运算符	说明
AND	逻辑与（必须同时满足多个条件）
OR	逻辑或（满足任意条件即可）
NOT	逻辑非（排除某些条件）

示例：

SELECT * FROM employees WHERE department = 'IT' AND salary > 8000;

id | name  | age | department | salary
---+-------+-----+-----------+--------
4  | David |  40 | IT        | 9500.00

SELECT * FROM employees WHERE department = 'HR' OR age < 30;

id | name    | age | department | salary
---+---------+-----+-----------+--------
1  | Alice   | 30  | HR        | 6000.00
3  | Charlie | 28  | Finance   | 5500.00
5  | Eve     | 25  | HR        | 5000.00

---

3.3 BETWEEN：范围查询

SELECT * FROM employees WHERE salary BETWEEN 5000 AND 8000;

id | name    | age | department | salary
---+---------+-----+-----------+--------
1  | Alice   | 30  | HR        | 6000.00
3  | Charlie | 28  | Finance   | 5500.00
5  | Eve     | 25  | HR        | 5000.00

---

3.4 IN：多选匹配

SELECT * FROM employees WHERE department IN ('HR', 'Finance');

id | name    | age | department | salary
---+---------+-----+-----------+--------
1  | Alice   | 30  | HR        | 6000.00
3  | Charlie | 28  | Finance   | 5500.00
5  | Eve     | 25  | HR        | 5000.00

---

3.5 LIKE：模式匹配

通配符	说明
%	代表任意数量的字符
_	代表单个字符

示例：查找以 A 开头的员工

SELECT * FROM employees WHERE name LIKE 'A%';

id | name  | age | department | salary
---+-------+-----+-----------+--------
1  | Alice |  30 | HR        | 6000.00

示例：查找名字长度为 5 的员工

SELECT * FROM employees WHERE name LIKE '_____';

id | name  | age | department | salary
---+-------+-----+-----------+--------
2  | Bob   |  35 | IT        | 8000.00
3  | David |  40 | IT        | 9500.00

---

3.6 IS NULL / IS NOT NULL

SELECT * FROM employees WHERE salary IS NULL;

如果某个员工的 salary 为空，该查询将返回结果。

---

3.7 EXISTS 子查询

查询 IT 部门的所有员工

SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM employees WHERE department = 'IT' AND e.id = id
);

id | name  | age | department | salary
---+-------+-----+-----------+--------
2  | Bob   |  35 | IT        | 8000.00
4  | David |  40 | IT        | 9500.00

---

4. WHERE 子句应用在不同 SQL 语句

4.1 在 UPDATE 语句中使用 WHERE

UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT';

将 IT 部门的员工薪资增加 10%。

---

4.2 在 DELETE 语句中使用 WHERE

DELETE FROM employees WHERE age < 30;

删除所有小于 30 岁的员工。

---

5. 结论

• WHERE 子句用于筛选数据，仅返回满足条件的行。
• 支持 比较运算符 (=, <>, >, <, BETWEEN, IN, LIKE 等)。
• 支持 逻辑运算符 (AND, OR, NOT) 组合条件。
• 可用于 SELECT、UPDATE、DELETE 等 SQL 语句。

通过 WHERE 子句，我们可以更精准地查询和操作数据，提高数据库操作的效率！更多详细内容请关注其他相关文章！