基本比较与范围
- BETWEEN a AND b 等价于 >= a AND <= b，包含两个边界
- IN 列表里可以有 NULL，但 NULL 永远不会匹配成功
- LIKE 中 % 匹配任意多个字符，_ 匹配恰好一个字符
- 字符串比较区分大小写，除非用 UPPER/LOWER 或设置排序规则
SELECT employee_id, last_name, salary, hire_date
FROM employees
WHERE salary BETWEEN 5000 AND 10000 -- 含边界
AND hire_date >= DATE '2005-01-01'
AND department_id IN (50, 60, 80)
AND job_id LIKE 'SA\_%' ESCAPE '\' -- 下划线是通配符，需转义
ORDER BY salary DESC;在你的专用沙箱里执行打开演练场
AND / OR 优先级
NOT 优先级最高，其次 AND，最后 OR。这意味着不加括号时，A OR B AND C 会被理解成 A OR (B AND C)，往往不是你想要的。规则很简单：只要混用 AND 和 OR，就加括号。
-- 未加括号：实际是 dept=50 OR (dept=60 AND salary>10000)
SELECT employee_id, department_id, salary
FROM employees
WHERE department_id = 50 OR department_id = 60 AND salary > 10000;
-- 加上括号，语义才清晰
SELECT employee_id, department_id, salary
FROM employees
WHERE (department_id = 50 OR department_id = 60) AND salary > 10000
ORDER BY department_id, salary DESC;在你的专用沙箱里执行打开演练场
排序细节
SELECT employee_id, department_id, commission_pct
FROM employees
WHERE department_id IN (80, 90)
ORDER BY department_id ASC,
commission_pct DESC NULLS LAST;
-- 把空值放最后在你的专用沙箱里执行打开演练场
-- 按 SELECT 中的别名排序（ORDER BY 在 SELECT 之后执行，允许）
SELECT last_name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;
-- 也可以用列序号，但不推荐：改 SELECT 时容易出错
SELECT last_name, salary FROM employees ORDER BY 2 DESC;在你的专用沙箱里执行打开演练场