WHERE条件查询

点击勘误issues (opens new window)，哪吒感谢大家的阅读

WHERE子句概述

WHERE子句用于过滤记录，只返回满足指定条件的行。它是SQL查询中最重要的子句之一，可以大大提高查询的精确性和效率。

基本语法

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

比较运算符

1. 等于 (=)

-- 查询薪资等于8000的员工
SELECT * FROM employees WHERE salary = 8000;

-- 查询部门为IT的员工
SELECT name, position FROM employees WHERE department = 'IT';

2. 不等于 (!= 或 <>)

-- 查询薪资不等于8000的员工
SELECT * FROM employees WHERE salary != 8000;
SELECT * FROM employees WHERE salary <> 8000;

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

3. 大于 (>) 和小于 (<)

-- 查询薪资大于10000的员工
SELECT name, salary FROM employees WHERE salary > 10000;

-- 查询年龄小于30的员工
SELECT name, age FROM employees WHERE age < 30;

4. 大于等于 (>=) 和小于等于 (<=)

-- 查询薪资大于等于8000的员工
SELECT * FROM employees WHERE salary >= 8000;

-- 查询年龄小于等于25的员工
SELECT * FROM employees WHERE age <= 25;

逻辑运算符

1. AND运算符

-- 查询IT部门且薪资大于8000的员工
SELECT * FROM employees 
WHERE department = 'IT' AND salary > 8000;

-- 查询年龄在25-35之间的员工
SELECT * FROM employees 
WHERE age >= 25 AND age <= 35;

-- 多个AND条件
SELECT * FROM employees 
WHERE department = 'IT' 
  AND salary > 8000 
  AND hire_date > '2020-01-01';

2. OR运算符

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

-- 查询薪资小于5000或大于15000的员工
SELECT * FROM employees 
WHERE salary < 5000 OR salary > 15000;

3. NOT运算符

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

-- 查询薪资不在8000-12000之间的员工
SELECT * FROM employees 
WHERE NOT (salary >= 8000 AND salary <= 12000);

4. 运算符优先级

-- 使用括号明确优先级
SELECT * FROM employees 
WHERE (department = 'IT' OR department = 'HR') 
  AND salary > 8000;

-- 等价于上面的查询
SELECT * FROM employees 
WHERE department = 'IT' AND salary > 8000
   OR department = 'HR' AND salary > 8000;

范围查询

1. BETWEEN运算符

-- 查询薪资在8000-12000之间的员工
SELECT * FROM employees 
WHERE salary BETWEEN 8000 AND 12000;

-- 查询入职日期在2020年的员工
SELECT * FROM employees 
WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';

-- NOT BETWEEN
SELECT * FROM employees 
WHERE salary NOT BETWEEN 8000 AND 12000;

2. IN运算符

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

-- 查询特定ID的员工
SELECT * FROM employees 
WHERE id IN (1, 3, 5, 7);

-- NOT IN
SELECT * FROM employees 
WHERE department NOT IN ('IT', 'HR');

模式匹配

1. LIKE运算符

-- 查询姓名以'张'开头的员工
SELECT * FROM employees WHERE name LIKE '张%';

-- 查询姓名以'三'结尾的员工
SELECT * FROM employees WHERE name LIKE '%三';

-- 查询姓名包含'小'的员工
SELECT * FROM employees WHERE name LIKE '%小%';

-- 查询姓名为两个字的员工
SELECT * FROM employees WHERE name LIKE '__';

-- 查询邮箱是gmail的员工
SELECT * FROM employees WHERE email LIKE '%@gmail.com';

2. 通配符说明

• %：匹配零个或多个字符
• _：匹配单个字符
• []：匹配括号内的任意单个字符（SQL Server）
• [^]：匹配不在括号内的任意单个字符（SQL Server）

-- SQL Server示例
SELECT * FROM employees WHERE name LIKE '[张李王]%';
SELECT * FROM employees WHERE name LIKE '[^张李王]%';

3. REGEXP/RLIKE（MySQL）

-- 使用正则表达式查询
SELECT * FROM employees WHERE name REGEXP '^张.*三$';

-- 查询邮箱格式正确的员工
SELECT * FROM employees 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

NULL值处理

1. IS NULL和IS NOT NULL

-- 查询没有邮箱的员工
SELECT * FROM employees WHERE email IS NULL;

-- 查询有邮箱的员工
SELECT * FROM employees WHERE email IS NOT NULL;

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

2. NULL值的特殊性

-- 错误：NULL不能用=比较
SELECT * FROM employees WHERE email = NULL;  -- 返回空结果

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

-- NULL参与的运算结果都是NULL
SELECT * FROM employees WHERE salary + NULL > 10000;  -- 返回空结果

日期时间查询

1. 日期比较

-- 查询2020年后入职的员工
SELECT * FROM employees WHERE hire_date > '2020-01-01';

-- 查询今年入职的员工
SELECT * FROM employees WHERE YEAR(hire_date) = YEAR(CURDATE());

-- 查询本月入职的员工
SELECT * FROM employees 
WHERE YEAR(hire_date) = YEAR(CURDATE()) 
  AND MONTH(hire_date) = MONTH(CURDATE());

2. 日期函数在WHERE中的应用

-- 查询工作超过3年的员工
SELECT * FROM employees 
WHERE DATEDIFF(CURDATE(), hire_date) > 365 * 3;

-- 查询本周入职的员工
SELECT * FROM employees 
WHERE YEARWEEK(hire_date) = YEARWEEK(CURDATE());

-- 查询生日在本月的员工
SELECT * FROM employees 
WHERE MONTH(birthday) = MONTH(CURDATE());

子查询在WHERE中的应用

1. 单值子查询

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

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

2. 多值子查询

-- 查询薪资在前3名的员工
SELECT * FROM employees 
WHERE salary IN (
    SELECT DISTINCT salary FROM employees 
    ORDER BY salary DESC LIMIT 3
);

-- 查询有下属的管理者
SELECT * FROM employees 
WHERE id IN (SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL);

3. EXISTS子查询

-- 查询有订单的客户
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- 查询没有订单的客户
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

实战案例

假设我们有以下员工表：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE,
    manager_id INT,
    email VARCHAR(100)
);

复杂查询示例

-- 1. 查询IT部门中薪资在8000-15000之间，且入职超过2年的程序员
SELECT name, salary, hire_date
FROM employees 
WHERE department = 'IT' 
  AND salary BETWEEN 8000 AND 15000
  AND position LIKE '%程序员%'
  AND DATEDIFF(CURDATE(), hire_date) > 730;

-- 2. 查询薪资高于其所在部门平均薪资的员工
SELECT e1.name, e1.department, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary) 
    FROM employees e2 
    WHERE e2.department = e1.department
);

-- 3. 查询没有邮箱或邮箱不是公司邮箱的员工
SELECT name, email
FROM employees 
WHERE email IS NULL 
   OR email NOT LIKE '%@company.com';

-- 4. 查询同时满足以下条件的员工：
-- - 不是管理者
-- - 薪资在部门前50%
-- - 入职时间在工作日
SELECT name, department, salary, hire_date
FROM employees e1
WHERE e1.id NOT IN (
        SELECT DISTINCT manager_id 
        FROM employees 
        WHERE manager_id IS NOT NULL
    )
  AND e1.salary >= (
        SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)
        FROM employees e2
        WHERE e2.department = e1.department
    )
  AND DAYOFWEEK(e1.hire_date) BETWEEN 2 AND 6;

性能优化技巧

1. 索引优化

-- 在经常用于WHERE条件的列上创建索引
CREATE INDEX idx_department ON employees(department);
CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_hire_date ON employees(hire_date);

-- 复合索引
CREATE INDEX idx_dept_salary ON employees(department, salary);

2. 查询优化

-- 优化前：函数作用在列上
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

-- 优化后：避免在列上使用函数
SELECT * FROM employees 
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

-- 优化前：OR条件
SELECT * FROM employees WHERE department = 'IT' OR department = 'HR';

-- 优化后：使用IN
SELECT * FROM employees WHERE department IN ('IT', 'HR');

3. 避免全表扫描

-- 避免：前导通配符
SELECT * FROM employees WHERE name LIKE '%张%';

-- 推荐：后导通配符（可以使用索引）
SELECT * FROM employees WHERE name LIKE '张%';

-- 避免：NOT条件
SELECT * FROM employees WHERE department != 'IT';

-- 推荐：使用IN列出所有可能值
SELECT * FROM employees WHERE department IN ('HR', 'Finance', 'Marketing');

常见错误

1. NULL值比较错误

-- 错误
SELECT * FROM employees WHERE email = NULL;

-- 正确
SELECT * FROM employees WHERE email IS NULL;

2. 字符串比较错误

-- 错误：数字当字符串比较
SELECT * FROM employees WHERE salary = '8000';

-- 正确：数字比较
SELECT * FROM employees WHERE salary = 8000;

3. 日期格式错误

-- 错误：日期格式不标准
SELECT * FROM employees WHERE hire_date = '2020/1/1';

-- 正确：标准日期格式
SELECT * FROM employees WHERE hire_date = '2020-01-01';

4. 逻辑运算符优先级错误

-- 可能不是预期结果
SELECT * FROM employees 
WHERE department = 'IT' OR department = 'HR' AND salary > 8000;

-- 明确优先级
SELECT * FROM employees 
WHERE (department = 'IT' OR department = 'HR') AND salary > 8000;

总结

WHERE子句是SQL查询的核心，掌握各种条件表达式的使用方法对于编写高效的SQL查询至关重要。关键要点：

1. 熟练掌握各种比较和逻辑运算符
2. 正确处理NULL值
3. 合理使用通配符和模式匹配
4. 注意运算符优先级
5. 考虑查询性能优化
6. 避免常见的语法错误

下一步学习

• JOIN表连接
• 聚合函数
• 分组查询GROUP BY