筛选使用 WHERE
Use WHERE clause 到筛选行 使用运算符, AND, OR, IN, BETWEEN, and LIKE.
你将学到
- 如何 WHERE clause 筛选行 之前它们是 returned
- Comparison 运算符: =, !=, <, >, <=, >=
- 组合条件使用 AND, OR, and NOT
- Using IN, BETWEEN, and LIKE for 设置 and 模式 matching
- 筛选 on NULL 使用 IS NULL and IS NOT NULL
概念讲解
WHERE 子句
WHERE goes after FROM and before ORDER BY / LIMIT. It evaluates a condition for every row and returns only the rows where the condition is TRUE. This filtering happens on the server before data is sent to your application, so it is far more efficient than fetching all rows and filtering in application code.
Comparison Operators
| Operator | Meaning | Example | |----------|---------|---------| | = | Equal | price = 19.99 | | != or <> | Not equal | 状态 != '删除' | | <, > | Less / greater | price < 50 | | <=, >= | Less or equal / greater or equal | stock >= 10 |
Combining Conditions
Use AND (both must be true), OR (at least one must be true), and NOT (negate). Use parentheses to control precedence — AND binds tighter than OR, so OR b AND c means OR (b AND c). Always use parentheses when mixing AND and OR to make intent explicit.
IN, BETWEEN, LIKE, IS NULL
IN (v1, v2, ...)— column matches any value in the listBETWEEN x AND y— inclusive range checkLIKE 'pat%'— pattern matching;%matches any sequence,_matches one charIS NULL/IS NOT NULL— you cannot use= NULL; NULL is not equal to anything, even itself
示例代码
-- Products in specific categories, priced between 10 and 100
SELECT name, price, category
FROM products
WHERE category IN ('electronics', 'books', 'toys')
AND price BETWEEN 10 AND 100
AND in_stock = TRUE
ORDER BY price ASC;
-- Find users whose name starts with 'A' and have no email set
SELECT id, name
FROM users
WHERE name LIKE 'A%'
AND email IS NULL;
-- Complex OR with parentheses
SELECT * FROM orders
WHERE (status = 'shipped' AND total > 100)
OR (status = 'pending' AND total > 500);
Three 模式: IN + BETWEEN for 范围 and 设置筛选, LIKE + IS NULL for 模式 and 空检查, and parenthesized OR for 复杂 logic.
动手试一试
- SQL Formatter
格式复杂 WHERE clauses 使用多个条件.
- Regex Tester
LIKE 模式是 simpler than regex — compare two.
常见错误
错误写法
Using = NULL 相反的 IS NULL
正确做法
NULL = NULL evaluates 到 NULL (不 TRUE) in SQL. 总是 use IS NULL or IS NOT NULL 到测试 for 空值.
错误写法
Forgetting parentheses 当 mixing AND and OR
正确做法
AND binds tighter than OR. 总是 use parentheses: (OR b) AND c, 不 OR b AND c, 除非你 是确定的 precedence.
练习 筛选使用 WHERE
14 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。