MySQL单表查询语句、常用函数、WHERE条件筛选、查询结果处理

一、基础单表查询语法

SELECT 
    [DISTINCT] column1, column2, ..., expression
FROM table_name
[WHERE condition]
[GROUP BY column]
[HAVING condition]
[ORDER BY column [ASC|DESC]]
[LIMIT offset, row_count];

💡 单表查询：只涉及一张表，不包含 JOIN。

二、SELECT 子句详解

✅ 1. 选择字段

-- 查询所有字段
SELECT * FROM users;

-- 查询指定字段
SELECT id, username, email FROM users;

-- 查询表达式（计算字段）
SELECT id, username, salary * 12 AS annual_salary FROM employees;

✅ 2. 别名（AS）

SELECT 
    u.id AS user_id,
    u.username AS name,
    YEAR(NOW()) - YEAR(birth_date) AS age
FROM users u;  -- 表别名（可选但推荐）

✅ 3. 去重（DISTINCT）

-- 去除重复邮箱
SELECT DISTINCT email FROM users;

-- 多字段去重（组合唯一）
SELECT DISTINCT city, country FROM addresses;

三、WHERE 条件筛选（核心！）

用于过滤行数据，支持多种条件组合。

🔧 1. 比较运算符

⚠️ NULL 不能用 = 判断！必须用 IS NULL

🔧 2. 逻辑运算符

✅ 优先级：NOT > AND > OR → 建议用 () 明确逻辑：

WHERE (city = 'Beijing' OR city = 'Shanghai') AND age > 20

🔧 3. 模糊匹配（LIKE）

• %：匹配任意多个字符
• _：匹配单个字符

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

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

-- 手机号第二位是 3（如 13x...）
SELECT * FROM users WHERE phone LIKE '13_';

⚠️ LIKE '%xxx%' 无法使用普通索引，大数据量慎用！

🔧 4. 集合匹配（IN / NOT IN）

-- 查询特定状态
SELECT * FROM orders WHERE status IN ('paid', 'shipped', 'completed');

-- 排除某些用户
SELECT * FROM users WHERE role NOT IN ('admin', 'moderator');

✅ IN 可替代多个 OR，更简洁高效。

🔧 5. 正则匹配（REGEXP / RLIKE）

-- 邮箱以数字开头
SELECT * FROM users WHERE email REGEXP '^[0-9]';

-- 手机号为11位纯数字
SELECT * FROM users WHERE phone REGEXP '^[0-9]{11}$';

⚠️ 性能较差，仅在复杂模式匹配时使用。

四、常用 MySQL 函数

📌 1. 字符串函数

📌 2. 数值函数

📌 3. 日期时间函数

✅ 常用格式符：%Y=四位年, %m=月, %d=日, %H=小时, %i=分钟, %s=秒

📌 4. 聚合函数（配合 GROUP BY）

⚠️ 聚合函数不能与普通字段混用（除非用 GROUP BY）。

五、查询结果处理

✅ 1. 排序（ORDER BY）

-- 按注册时间倒序
SELECT * FROM users ORDER BY created_at DESC;

-- 多字段排序：先按城市升序，再按年龄降序
SELECT * FROM users ORDER BY city ASC, age DESC;

✅ 2. 分页（LIMIT）

-- 第1页（每页10条）
SELECT * FROM products LIMIT 0, 10;

-- 第2页
SELECT * FROM products LIMIT 10, 10;

-- 简写（仅限 MySQL）：LIMIT 10 OFFSET 10

💡 公式：LIMIT (page_no - 1) * page_size, page_size

⚠️ 深度分页性能差（如 LIMIT 100000, 10），建议用“游标分页”（基于 ID > last_id）。

✅ 3. 条件表达式（CASE WHEN）

SELECT 
    username,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 60 THEN '成年人'
        ELSE '老年人'
    END AS age_group
FROM users;

✅ 4. 空值处理（IFNULL / COALESCE）

-- 将 NULL 电话显示为 '未填写'
SELECT username, IFNULL(phone, '未填写') AS phone FROM users;

-- 多个备选值
SELECT COALESCE(phone, email, '无联系方式') FROM users;

六、实战示例

🎯 场景：用户分析

-- 查询活跃成年用户（近30天登录过），按注册时间倒序，分页第1页
SELECT 
    id,
    username,
    email,
    DATE_FORMAT(last_login, '%Y-%m-%d') AS last_login_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM users
WHERE 
    is_active = 1
    AND last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND birth_date <= DATE_SUB(CURDATE(), INTERVAL 18 YEAR)
ORDER BY created_at DESC
LIMIT 0, 20;

🎯 场景：订单统计

-- 查询每个用户的订单总数和总金额（仅完成订单）
SELECT 
    user_id,
    COUNT(*) AS order_count,
    ROUND(SUM(total_amount), 2) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING total_spent > 1000  -- 筛选高价值用户
ORDER BY total_spent DESC;

✅ 最佳实践总结