52. MySQL WHERE 条件查询

实际开发中很少查询全表数据，通常需要按条件筛选。WHERE 子句就是实现这一需求的核心语法。

---

1. 基础条件查询

SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age <= 30;
SELECT * FROM users WHERE username <> 'admin'; -- <> 或 != 都表示不等于

---

2. AND 与 OR

多个条件组合使用：

-- 同时满足（年龄大于18且状态为1）
SELECT * FROM users WHERE age > 18 AND status = 1;

-- 满足其一（年龄小于18或年龄大于60）
SELECT * FROM users WHERE age < 18 OR age > 60;

---

3. IN 与 NOT IN

判断字段值是否在指定集合中：

-- 等同于 age = 18 OR age = 25 OR age = 30
SELECT * FROM users WHERE age IN (18, 25, 30);

-- 排除指定值
SELECT * FROM users WHERE status NOT IN (0, 2);

---

4. BETWEEN 范围查询

查询某个范围内的值，包含边界：

-- 年龄在 18 到 30 之间（包含18和30）
SELECT * FROM users WHERE age BETWEEN 18 AND 30;

-- 日期范围
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- 不在范围内
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 100;

---

5. LIKE 模糊查询

配合通配符进行模糊匹配：

通配符	含义
%	匹配任意长度字符（包括空）
_	匹配单个字符

-- 用户名以 "张" 开头
SELECT * FROM users WHERE username LIKE '张%';

-- 邮箱包含 "gmail"
SELECT * FROM users WHERE email LIKE '%gmail%';

-- 手机号第二位是 3（如 138xxxx）
SELECT * FROM users WHERE phone LIKE '_3%';

---

6. PHP 条件查询示例

<?php
$pdo = new PDO('mysql:host=localhost;dbname=my_shop;charset=utf8mb4', 'root', '123456');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 动态条件查询
$minAge = 18;
$maxAge = 30;
$keyword = '%张%';

$stmt = $pdo->prepare(
    "SELECT * FROM users 
     WHERE age BETWEEN :min AND :max 
     AND username LIKE :kw"
);
$stmt->execute([
    ':min' => $minAge,
    ':max' => $maxAge,
    ':kw'  => $keyword
]);

$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

---

7. NULL 值判断

-- 查询邮箱为空的用户
SELECT * FROM users WHERE email IS NULL;

-- 查询邮箱已填写的用户
SELECT * FROM users WHERE email IS NOT NULL;

---

小结

WHERE 子句让查询更精准。记住优先级：括号 > NOT > AND > OR。模糊查询很实用，但要注意性能。

---