PostgreSQL 入门教程

PostgreSQL WHERE 子句

WHERE 子句是 SQL 中数据过滤的基础。它允许你指定条件，从而决定哪些行将被包含在 SELECT、UPDATE 或 DELETE 语句的结果集中。如果没有 WHERE 子句，这些语句将会影响表中的所有行，这通常不是我们想要的结果。

掌握 WHERE 子句是在 PostgreSQL 数据库中提取有价值的信息和操作特定数据的关键。本章将深入探讨高效使用 WHERE 子句的各种细节，让你具备精准定位和管理数据的能力。

1. 了解 WHERE 子句

SQL 中的 WHERE 子句用于过滤记录。你可以使用它来指定一条记录必须满足什么条件才能被包含在结果集中。它是 SELECT、UPDATE 和 DELETE 语句中不可或缺的一部分，让你能够处理数据的特定子集。

1.1 基础语法

带有 WHERE 子句的 SELECT 语句的基础语法如下：

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

这里的 condition（条件）是一个布尔表达式，它的计算结果为 true（真）、false（假）或 unknown（未知/NULL）。只有当条件计算结果为 true 时，对应的行才会被包含在结果集中。

2. 比较运算符

比较运算符用于在 WHERE 子句中比较不同的值。以下是常用运算符的汇总：

  • = : 等于
  • <> 或 != : 不等于
  • > : 大于
  • < : 小于
  • >= : 大于或等于
  • <= : 小于或等于

示例：

假设我们有一个名为 customers（客户）的表，包含 customer_id（客户ID）、name（姓名）、city（城市）和 order_total（订单总额）等列：

查找来自纽约的客户：

SELECT customer_id, name
FROM customers
WHERE city = 'New York';

这个查询会提取所有居住在纽约（New York）的客户的 customer_id 和 name。

查找订单总额大于 100 的客户：

SELECT customer_id, name, order_total
FROM customers
WHERE order_total > 100;

这个查询会提取所有 order_total 大于 100 的客户的 customer_id、name 和 order_total。

查找订单总额不等于 50 的客户：

SELECT customer_id, name, order_total
FROM customers
WHERE order_total <> 50;

这个查询会提取所有 order_total 不等于 50 的客户的详细信息。

3. 逻辑运算符

逻辑运算符用于在 WHERE 子句中组合多个条件。主要的逻辑运算符包括：

  • AND : 如果两个条件都为真，则返回真。
  • OR : 如果任意一个条件为真，则返回真。
  • NOT : 对条件取反。

示例：

查找来自纽约且订单总额大于 100 的客户：

SELECT customer_id, name, city, order_total
FROM customers
WHERE city = 'New York' AND order_total > 100;

这个查询返回既来自纽约又有大于 100 的订单总额的客户。

查找来自纽约或洛杉矶的客户：

SELECT customer_id, name, city
FROM customers
WHERE city = 'New York' OR city = 'Los Angeles';

这个查询返回来自纽约或者洛杉矶的客户。

查找不是来自芝加哥的客户：

SELECT customer_id, name, city
FROM customers
WHERE NOT city = 'Chicago';

这个查询返回不来自芝加哥的客户。这等同于使用 WHERE city <> 'Chicago'。

4. 在 UPDATE 和 DELETE 中使用 WHERE

WHERE 子句在 UPDATE 和 DELETE 语句中同样至关重要：

  • UPDATE : 根据条件修改特定的行。
  • DELETE : 根据条件删除特定的行。

示例：

更新 ID 为 123 的客户的订单总额：

UPDATE customers
SET order_total = 150
WHERE customer_id = 123;

这条语句将 customer_id 等于 123 的客户的 order_total 更新为 150。

删除 ID 为 456 的客户：

DELETE FROM customers
WHERE customer_id = 456;

这条语句删除了 customer_id 等于 456 的客户。使用 DELETE 语句时请务必极其小心。（如果不加 WHERE 条件，会清空整个表！）

5. 进阶的 WHERE 子句条件

除了基础的比较和逻辑运算符，WHERE 子句还支持更复杂的条件，利用额外的运算符和函数来实现强大的过滤功能。

5.1 BETWEEN 运算符

BETWEEN 运算符用于选择给定范围内的值。它是包含边界的（inclusive），意味着边界值也会被包含在选择结果中。

语法：

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

示例：

假设我们想从 orders（订单）表中找出所有在 2023 年 1 月 1 日到 2023 年 1 月 31 日之间下的订单（使用 order_date 列）：

SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

这个查询将返回 2023 年 1 月份的所有订单。

5.2 LIKE 运算符

LIKE 运算符用于模式匹配。它通常与通配符一起使用：

  • % : 代表零个、一个或多个字符。
  • _ : 代表单个字符。

语法：

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

示例：

名字以 'A' 开头的客户：

SELECT customer_id, name
FROM customers
WHERE name LIKE 'A%';

这个查询返回所有名字以字母 'A' 开头的客户。

名字中包含 'an' 的客户：

SELECT customer_id, name
FROM customers
WHERE name LIKE '%an%';

这个查询返回所有名字中包含子字符串 'an' 的客户。

名字的第二个字符是 'a' 的客户：

SELECT customer_id, name
FROM customers
WHERE name LIKE '_a%';

这个查询返回所有名字的第二个字母为 'a' 的客户。

5.3 IN 运算符

IN 运算符允许你在 WHERE 子句中指定多个值。它会检查一个值是否与列表中的任意一个值匹配。

语法：

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

示例：

查找来自纽约、洛杉矶或芝加哥的客户：

SELECT customer_id, name, city
FROM customers
WHERE city IN ('New York', 'Los Angeles', 'Chicago');`

这等同于以下写法：

SELECT customer_id, name, city
FROM customers
WHERE city = 'New York' OR city = 'Los Angeles' OR city = 'Chicago';

然而，使用 IN 更加简洁，并且通常效率更高，尤其是在值列表很长的时候。

5.4 IS NULL 和 IS NOT NULL 运算符

这两个运算符用于检查 NULL（空值）。你不能使用 = 或 <> 来与 NULL 进行比较。

语法：

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

示例：

假设 customers 表有一个可能为 NULL 的 phone（电话）列。

没有电话号码的客户：

SELECT customer_id, name
FROM customers
WHERE phone IS NULL;

这个查询返回所有 phone 列为 NULL 的客户。

有电话号码的客户：

SELECT customer_id, name
FROM customers
WHERE phone IS NOT NULL;

这个查询返回所有 phone 列不为 NULL 的客户。

6. 组合条件

你可以使用逻辑运算符（AND、OR、NOT）组合多个条件，以创建复杂的过滤标准。请务必注意运算符的优先级，并使用括号来明确定义计算的顺序。

示例：

查找要么（来自纽约且订单总额大于 100），要么（来自洛杉矶）的客户：

SELECT customer_id, name, city, order_total
FROM customers
WHERE (city = 'New York' AND order_total > 100) OR city = 'Los Angeles';

括号确保了 AND 条件在 OR 条件之前被计算。如果没有括号，查询会被解释为：

SELECT customer_id, name, city, order_total
FROM customers
WHERE city = 'New York' AND (order_total > 100 OR city = 'Los Angeles');

这将会返回完全不同的结果。所有来自纽约的客户都会被无条件包含进来（无论订单总额多少），而任何来自洛杉矶的客户也会被包含进来。

7. 大小写敏感性

默认情况下，PostgreSQL 中的字符串比较是区分大小写的。这意味着 'New York' 与 'new york' 是不同的。

示例：

SELECT customer_id, name, city
FROM customers
WHERE city = 'new york'; 
-- 如果数据库中存储的是 'New York'，这很可能返回空结果

要执行不区分大小写的比较，你可以使用 LOWER() 或 UPPER() 函数将列值和比较值都转换为相同的大小写：

SELECT customer_id, name, city
FROM customers
WHERE LOWER(city) = 'new york';

这个查询将会返回城市名为 'New York'、'new york'、'NEW YORK' 以及任何其他大小写变体的客户。

8. 性能注意事项

高效使用 WHERE 子句对于查询性能至关重要。以下是一些需要注意的事项：

  • 索引 (Indexes)：确保在 WHERE 子句中使用的列已经建立了索引。索引允许数据库快速定位匹配的行，从而加快数据检索速度。我们将在后面的模块中详细介绍索引。
  • 避免在索引列上使用函数：在 WHERE 子句中使用像 LOWER() 这样的函数，可能会导致数据库无法有效利用索引。可以考虑使用函数索引（functional index）。
  • 条件顺序：将最具选择性的条件（即能过滤掉最多行的条件）放在 WHERE 子句的最前面，特别是在使用 AND 时。这可以帮助数据库优化查询执行计划。
  • 重写复杂查询：有时，为了获得更好的性能，可以简化或重写复杂的 WHERE 子句。像 EXPLAIN ANALYZE 这样的工具（稍后会讲到）可以帮助你了解 PostgreSQL 是如何执行你的查询的，并找出性能瓶颈。