SQLite - WHERE 子句

SQLite WHERE 子句是筛选记录的主要工具。它用于只提取满足特定条件的记录，让你可以选择性地检索、更新或删除数据。

如果 WHERE 子句中指定的条件对于给定行求值为真（true），则该行将包含在操作中。这个子句并非 SELECT 语句独有；它也是 UPDATE 和 DELETE 语句的关键部分，确保修改的精确性和目的性。

语法

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

SELECT column_list
FROM table_name
WHERE [condition];

[condition] 是一个表达式，通常包含比较运算符（=, >, <, >=, <=, <>）、逻辑运算符（AND, OR, NOT）以及其他专用运算符，如 LIKE, IN, BETWEEN 和 IS NULL。

设置：创建示例数据

为了演示 WHERE 子句，我们来创建并填充一个 EMPLOYEES 表。该表将作为我们示例的基础。

CREATE TABLE EMPLOYEES(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   DEPARTMENT     CHAR(50),
   SALARY         REAL
);

INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (1, 'David', 27, 'IT', 85000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (2, 'Allen', 25, 'HR', 15000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (3, 'Martin', 23, 'Finance', 20000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (4, 'Mark', 25, 'IT', 65000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (5, 'Paul', 32, 'Finance', 21000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (6, 'Kim', 22, 'HR', 45000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (7, 'James', 24, 'Marketing', 10000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (8, 'Chris', 45, 'IT', NULL);

示例

让我们使用 WHERE 子句来探索各种筛选条件。

查找所有在 ‘IT’ 部门的员工：

sqlite> SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 'IT';

ID  NAME   AGE  DEPARTMENT  SALARY
--  -----  ---  ----------  -------
1   David  27   IT          85000.0
4   Mark   25   IT          65000.0
8   Chris  45   IT

查找所有年龄大于 30 岁或薪水超过 50,000 的员工：

sqlite> SELECT * FROM EMPLOYEES WHERE AGE > 30 OR SALARY > 50000;

ID  NAME   AGE  DEPARTMENT  SALARY
--  -----  ---  ----------  -------
1   David  27   IT          85000.0
4   Mark   25   IT          65000.0
5   Paul   32   Finance     21000.0
8   Chris  45   IT

查找所有姓名以 ‘M’ 开头的员工： LIKE 运算符用于字符串的模式匹配。百分号 (%) 是一个通配符（wildcard），匹配任意字符序列。

sqlite> SELECT * FROM EMPLOYEES WHERE NAME LIKE 'M%';

ID  NAME    AGE  DEPARTMENT  SALARY
--  ------  ---  ----------  -------
3   Martin  23   Finance     20000.0
4   Mark    25   IT          65000.0

查找所有在 ‘HR’ 或 ‘Finance’ 部门工作的员工： IN 运算符提供了一种简洁的方式来检查某个值是否与列表中的任何值匹配。

sqlite> SELECT * FROM EMPLOYEES WHERE DEPARTMENT IN ('HR', 'Finance');

ID  NAME    AGE  DEPARTMENT  SALARY
--  ------  ---  ----------  -------
2   Allen   25   HR          15000.0
3   Martin  23   Finance     20000.0
5   Paul    32   Finance     21000.0
6   Kim     22   HR          45000.0

查找所有薪水在 20,000 到 50,000 之间的员工： BETWEEN 运算符包含起始值和结束值。

sqlite> SELECT * FROM EMPLOYEES WHERE SALARY BETWEEN 20000 AND 50000;

ID  NAME    AGE  DEPARTMENT  SALARY
--  ------  ---  ----------  -------
3   Martin  23   Finance     20000.0
5   Paul    32   Finance     21000.0
6   Kim     22   HR          45000.0

查找所有薪水未记录的员工： IS NULL 运算符用于检查 NULL 值。你不能使用 = NULL。

sqlite> SELECT * FROM EMPLOYEES WHERE SALARY IS NULL;

ID  NAME   AGE  DEPARTMENT  SALARY
--  -----  ---  ----------  -------
8   Chris  45   IT

常见陷阱和最佳实践

NULL 处理： 一个常见错误是使用 column = NULL 或 column <> NULL。NULL 代表一个未知值，因此不能与标准运算符进行比较。始终使用 IS NULL 或 IS NOT NULL。
运算符优先级： 组合 AND 和 OR 时，请注意 AND 具有更高的优先级。为确保逻辑正确，请使用括号明确地对条件进行分组。例如：WHERE (DEPARTMENT = 'IT' OR DEPARTMENT = 'HR') AND SALARY > 50000。
性能： 对于大型表，查询可能会很慢。经常在 WHERE 子句中使用的列是数据库索引（database indexes）的绝佳候选，索引可以显著加快数据检索速度。我们将在后续章节中介绍索引。
数据类型： 确保你比较的数据与列的数据类型匹配。由于 SQLite 灵活的类型系统，将字符串与数字列进行比较（例如 WHERE AGE = '25'）可能有效，但这是一种不良实践，并可能导致意想不到的结果或性能不佳。