跳到主要内容

SQL WHERE 条件过滤

WHERE 子句用于过滤数据，只返回满足条件的记录。

基本语法​

SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件;

演示表​

假设有 employees 表：

idnamedepartmentsalaryagehire_date
1张三Sales5000252023-01-15
2李四IT8000302022-03-20
3王五Sales6000282023-06-10
4赵六HR5500352021-11-05
5孙七IT7500272023-09-01

比较运算符​

等于​

-- 查询 IT 部门员工
SELECT * FROM employees WHERE department = 'IT';

不等于​

-- 查询非 IT 部门员工
SELECT * FROM employees WHERE department != 'IT';
-- 或
SELECT * FROM employees WHERE department <> 'IT';

大于/小于​

-- 薪资大于6000
SELECT * FROM employees WHERE salary > 6000;

-- 年龄小于30
SELECT * FROM employees WHERE age < 30;

大于等于/小于等于​

-- 薪资大于等于6000
SELECT * FROM employees WHERE salary >= 6000;

-- 年龄小于等于30
SELECT * FROM employees WHERE age <= 30;

逻辑运算符​

AND（并且）​

-- IT 部门且薪资大于7000
SELECT * FROM employees 
WHERE department = 'IT' AND salary > 7000;

OR（或者）​

-- Sales 或 IT 部门
SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'IT';

NOT（取反）​

-- 非 IT 部门
SELECT * FROM employees 
WHERE NOT department = 'IT';

组合使用​

-- AND 和 OR 组合，注意括号优先级
SELECT * FROM employees 
WHERE (department = 'Sales' OR department = 'IT') 
AND salary > 6000;

BETWEEN 范围​

-- 薪资在5000到7000之间
SELECT * FROM employees 
WHERE salary BETWEEN 5000 AND 7000;

-- 等价于
SELECT * FROM employees 
WHERE salary >= 5000 AND salary <= 7000;

IN 列表​

-- 查询特定部门
SELECT * FROM employees 
WHERE department IN ('Sales', 'IT', 'HR');

-- 等价于
SELECT * FROM employees 
WHERE department = 'Sales' 
OR department = 'IT' 
OR department = 'HR';

LIKE 模糊匹配​

% 通配符​

-- 名字以"张"开头
SELECT * FROM employees WHERE name LIKE '张%';

-- 名字包含"四"
SELECT * FROM employees WHERE name LIKE '%四%';

-- 以"六"结尾
SELECT * FROM employees WHERE name LIKE '%六';

_ 通配符​

-- 名字是2个字
SELECT * FROM employees WHERE name LIKE '__';

-- 第一个字是"张"，共2个字
SELECT * FROM employees WHERE name LIKE '张_';

区分大小写​

不同数据库的区分方式不同：

-- MySQL 默认不区分大小写
-- 可使用 BINARY 区分
SELECT * FROM employees WHERE BINARY name LIKE '张%';

NULL 值处理​

IS NULL​

-- 查询没有部门的员工
SELECT * FROM employees WHERE department IS NULL;

IS NOT NULL​

-- 查询有部门的员工
SELECT * FROM employees WHERE department IS NOT NULL;

日期比较​

-- 2023年入职的员工
SELECT * FROM employees 
WHERE hire_date >= '2023-01-01' AND hire_date <= '2023-12-31';

-- 特定日期之后
SELECT * FROM employees 
WHERE hire_date > '2023-06-01';

子查询​

-- 查询薪资高于平均值的员工
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 查询与张三同部门的员工
SELECT * FROM employees 
WHERE department = (
    SELECT department FROM employees WHERE name = '张三'
);

EXISTS​

-- 查询有员工的部门
SELECT * FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.department = d.name
);

常见错误​

1. 字符串缺少引号​

-- 错误
SELECT * FROM employees WHERE department = IT;

-- 正确
SELECT * FROM employees WHERE department = 'IT';

2. NULL 比较​

-- 错误：NULL 不能用 = 比较
SELECT * FROM employees WHERE department = NULL;

-- 正确：使用 IS NULL
SELECT * FROM employees WHERE department IS NULL;

3. 模糊匹配位置​

-- 错误：% 在开头会降低性能
SELECT * FROM employees WHERE name LIKE '%四';

-- 正确：尽量在末尾
SELECT * FROM employees WHERE name LIKE '张%';

小结​

本章我们学习了：

  1. WHERE 基本用法
  2. 比较运算符（=, !=, >, <, >=, <=）
  3. 逻辑运算符（AND, OR, NOT）
  4. BETWEEN 范围
  5. IN 列表
  6. LIKE 模糊匹配
  7. NULL 处理
  8. 子查询

练习​

  1. 查询薪资在5000到7000之间的员工
  2. 查询名字包含"四"的员工
  3. 查询IT部门且薪资大于7000的员工
  4. 查询2023年入职的员工