MySQL常用语句

SQL 使用技巧汇总

持续更新，收录各类实用 SQL 技巧、实例及应用场景。

1. 基础查询

1.1 SELECT 获取指定列

示例：

SELECT username, email, created_at FROM users;

应用场景： 只获取需要的字段，减少数据传输量，适合字段较多的大表查询。

1.2 DISTINCT 去重

示例：

SELECT DISTINCT department FROM employees;

应用场景： 快速获取不重复的分类列表，如查看所有有订单记录的客户 ID。

1.3 LIMIT 分页

示例（MySQL/PostgreSQL）：

SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20;

示例（SQL Server）：

SELECT * FROM products ORDER BY price DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

应用场景： 分页展示数据，如商品列表第 3 页。

1.4 SELECT INTO 复制表数据

示例（MySQL）：

CREATE TABLE orders_backup AS
SELECT * FROM orders WHERE order_date >= '2024-01-01';

应用场景： 备份数据、创建临时分析表。

2. 条件过滤

2.1 IF / CASE 条件表达式

示例：

SELECT
    username,
    IF(age >= 18, '成年', '未成年') AS age_group
FROM users;

-- CASE 写法（更通用）
SELECT
    username,
    CASE
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 60 THEN '成年'
        ELSE '老年'
    END AS age_group
FROM users;

应用场景： 字段值转换、等级判定、状态标记。

2.2 IN 多值匹配

示例：

SELECT * FROM products
WHERE category IN ('电子产品', '服装', '图书');

应用场景： 替代多个 OR 条件，代码更简洁。

2.3 LIKE 模糊匹配

示例：

-- 以指定字符开头
SELECT * FROM users WHERE email LIKE 'admin%';

-- 包含指定字符
SELECT * FROM users WHERE username LIKE '%张%';

-- 任意单字符
SELECT * FROM products WHERE product_code LIKE 'A_001';

应用场景： 搜索功能、按前缀查分类。

注意： LIKE 前缀通配（%xxx）会导致索引失效，大数据量场景慎用。

2.4 REGEXP 正则匹配

示例（MySQL）：

SELECT * FROM users WHERE phone REGEXP '^1[3-9][0-9]{9}$';

应用场景： 手机号格式校验、复杂模式匹配。

2.5 NULL 处理

示例：

-- IFNULL 填充默认值（MySQL）
SELECT IFNULL(nickname, '匿名用户') FROM users;

-- COALESCE 返回第一个非 NULL 值（通用）
SELECT COALESCE(phone, email, '无联系方式') FROM contacts;

-- NULLIF 比较两个值，相等返回 NULL
SELECT NULLIF(a, b) FROM ...

应用场景： 显示默认值、避免除零错误。

3. 聚合与分组

3.1 COUNT 统计行数

示例：

-- 统计总行数
SELECT COUNT(*) FROM orders;

-- 统计非 NULL 数量
SELECT COUNT(shipped_date) FROM orders;

-- 去重统计
SELECT COUNT(DISTINCT customer_id) FROM orders;

应用场景： 统计数据量、去重计算。

3.2 GROUP BY 分组聚合

示例：

SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    MAX(price) AS max_price,
    MIN(price) AS min_price
FROM products
GROUP BY category;

应用场景： 按分类统计商品数量和价格区间。

3.3 HAVING 过滤分组结果

示例：

SELECT
    customer_id,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 10000;

应用场景： 筛选消费总额超过某值的客户（相当于 WHERE 但作用于聚合后）。

区别： WHERE 在分组前过滤原始数据，HAVING 在分组后过滤聚合结果。

3.4 GROUP_CONCAT 行转列（MySQL）

示例：

SELECT
    department,
    GROUP_CONCAT(employee_name ORDER BY salary DESC SEPARATOR ', ') AS employees
FROM employees
GROUP BY department;

应用场景： 将同一组的多个值合并为一个字符串输出，如每个部门的人员列表。

4. 多表连接

4.1 INNER JOIN 内连接

示例：

SELECT
    o.order_id,
    o.amount,
    c.customer_name,
    c.phone
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

应用场景： 只保留两边都匹配到的记录，最常用的连接类型。

4.2 LEFT JOIN 左连接

示例：

SELECT
    c.customer_name,
    COUNT(o.id) AS order_count,
    IFNULL(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.customer_name;

应用场景： 获取所有客户及他们的订单（含无订单客户）。

4.3 RIGHT JOIN 右连接

示例：

SELECT
    p.product_name,
    o.order_id
FROM orders o
RIGHT JOIN products p ON o.product_id = p.id;

应用场景： 以右侧表为基准，保留所有产品即使没有订单记录。

4.4 FULL OUTER JOIN 全连接

示例（MySQL 不直接支持，用 UNION 模拟）：

SELECT * FROM A LEFT JOIN B ON A.id = B.id
UNION
SELECT * FROM A RIGHT JOIN B ON A.id = B.id;

应用场景： 两表记录全部保留，匹配不上则对方字段为 NULL。

4.5 多表连接

示例：

SELECT
    o.order_id,
    c.customer_name,
    p.product_name,
    oi.quantity
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.order_date >= '2024-01-01';

应用场景： 订单详情查询，串联多张关联表。

4.6 自连接

示例：

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

应用场景： 查员工及其上级、查分类及其父分类。

5. 子查询

5.1 WHERE 中的子查询

示例：

SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

应用场景： 筛选高于平均值的产品。

5.2 IN 中的子查询

示例：

SELECT * FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
    WHERE order_date >= '2024-01-01'
);

应用场景： 找出近一年有订单的所有客户。

5.3 FROM 中的子查询（派生表）

示例：

SELECT
    category,
    avg_price,
    total_products
FROM (
    SELECT
        category,
        AVG(price) AS avg_price,
        COUNT(*) AS total_products
    FROM products
    GROUP BY category
) AS stats
WHERE avg_price > 100;

应用场景： 先聚合再筛选，两阶段过滤。

5.4 EXISTS 判断存在性

示例：

SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.amount > 5000
);

应用场景： 检查客户是否有符合条件的订单，EXISTS 在找到第一条记录后即停止，效率通常优于 IN。

6. 窗口函数

窗口函数在不 GROUP BY 压缩行数的情况下，对每一行计算其相关的聚合值。

6.1 OVER + PARTITION BY 分区计算

示例：

SELECT
    employee_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total_salary
FROM employees;

应用场景： 计算每个员工工资在其部门的排名、占比。

6.2 ROW_NUMBER / RANK / DENSE_RANK 排名

示例：

SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- 取每个部门工资前三名
SELECT * FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 3;

应用场景：

ROW_NUMBER：严格排序（无并列）
RANK：有并列跳号（1, 2, 2, 4）
DENSE_RANK：有并列不跳号（1, 2, 2, 3）

6.3 LAG / LEAD 前后行数据

示例：

SELECT
    order_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY order_date) AS prev_amount,
    LEAD(amount, 1) OVER (ORDER BY order_date) AS next_amount,
    amount - LAG(amount, 1) OVER (ORDER BY order_date) AS amount_change
FROM orders;

应用场景： 计算环比增长/下降、找出前后相邻的记录。

6.4 FIRST_VALUE / LAST_VALUE

示例：

SELECT
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (ORDER BY order_date) AS first_amount,
    LAST_VALUE(amount) OVER (ORDER BY order_date) AS last_amount
FROM orders;

应用场景： 获取分区内的首尾值。

6.5 SUM / AVG / COUNT 窗口累计

示例：

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS cumulative_amount,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg
FROM orders;

应用场景： 计算累计销售额、滚动平均。

7. 常见函数

7.1 字符串函数

示例：

-- CONCAT 拼接
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- SUBSTRING 截取
SELECT SUBSTRING(phone, 1, 3) AS phone_prefix FROM contacts;

-- LENGTH / CHAR_LENGTH 长度
SELECT LENGTH(username), CHAR_LENGTH(username) FROM users;

-- TRIM 去空格
SELECT TRIM('  hello  '); -- 'hello'

-- UPPER / LOWER 大小写
SELECT UPPER(username), LOWER(email) FROM users;

-- REPLACE 替换
SELECT REPLACE(description, '旧地址', '新地址') FROM orders;

-- LPAD / RPAD 填充
SELECT LPAD(product_code, 6, '0') FROM products; -- 123 → 000123

7.2 日期时间函数

示例：

-- NOW / CURRENT_TIMESTAMP 当前时间
SELECT NOW();

-- DATE_ADD / DATE_SUB 日期增减
SELECT DATE_ADD(order_date, INTERVAL 7 DAY) AS delivery_date FROM orders;
SELECT DATE_SUB(order_date, INTERVAL 1 MONTH) AS last_month FROM orders;

-- DATEDIFF 计算日期差
SELECT DATEDIFF(NOW(), created_at) AS days_since_register FROM users;

-- DATE_FORMAT 日期格式化
SELECT DATE_FORMAT(order_date, '%Y年%m月%d日') AS formatted_date FROM orders;

-- YEAR / MONTH / DAY 提取部分
SELECT YEAR(order_date), MONTH(order_date), DAY(order_date) FROM orders;

-- TIMESTAMPDIFF 精确时间差
SELECT TIMESTAMPDIFF(HOUR, created_at, NOW()) AS hours_elapsed FROM sessions;

-- DATE 函数取日期部分
SELECT DATE(NOW()), DATE(created_at) FROM orders;

7.3 数学函数

示例：

-- ROUND 四舍五入
SELECT ROUND(amount, 2) FROM orders;

-- FLOOR / CEIL 向下/向上取整
SELECT FLOOR(price), CEIL(price) FROM products;

-- ABS 绝对值
SELECT ABS(delta) FROM changes;

-- MOD 取模
SELECT * FROM products WHERE id MOD 2 = 1; -- 奇数 ID

-- POW / POWER 幂
SELECT POW(radius, 2) FROM circles;

7.4 条件函数

示例：

-- IF 条件返回
SELECT IF(price > 1000, '高价', '普通') AS price_level FROM products;

-- NULLIF 相等返回 NULL
SELECT NULLIF(quantity, 0) AS safe_quantity FROM stock; -- 避免除零

-- GREATEST / LEAST 取最大/最小
SELECT GREATEST(a, b, c), LEAST(a, b, c) FROM ...;

8. 性能优化

8.1 EXPLAIN 分析执行计划

示例：

EXPLAIN SELECT * FROM orders WHERE customer_id = 100;
EXPLAIN ANALYZE SELECT ...; -- PostgreSQL

应用场景： 检查查询是否走索引、是否有全表扫描。

8.2 创建索引

示例：

-- 单列索引
CREATE INDEX idx_customer_id ON orders(customer_id);

-- 复合索引（注意顺序）
CREATE INDEX idx_order_date_customer ON orders(order_date, customer_id);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 前缀索引（MySQL）
CREATE INDEX idx_product_name ON products(name(10));

应用场景： 加速 WHERE 条件、JOIN、ORDER BY 查询。

复合索引原则： 最左前缀匹配，查询条件顺序应与索引字段顺序一致。

8.3 避免 SELECT *

示例：

-- ❌ 低效
SELECT * FROM orders;

-- ✅ 只查需要的字段
SELECT order_id, customer_id, amount, order_date FROM orders;

应用场景： 减少 IO、减少网络传输、避免覆盖索引失效。

8.4 批量插入

示例（MySQL）：

INSERT INTO products(name, price) VALUES
('产品A', 100),
('产品B', 200),
('产品C', 300);

应用场景： 大数据量导入，减少事务提交次数。

8.5 批量更新

示例：

-- case when 批量更新
UPDATE products
SET price = CASE id
    WHEN 1 THEN 150
    WHEN 2 THEN 250
    WHEN 3 THEN 350
END
WHERE id IN (1, 2, 3);

应用场景： 减少 UPDATE 语句数量，一条语句批量处理多条记录。

8.6 分页优化

示例（深度分页优化）：

-- ❌ 偏移量大时慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- ✅ 基于 ID 范围定位
SELECT * FROM orders
WHERE id > 1000000
ORDER BY id
LIMIT 20;

-- ✅ 记录上次查询最大 ID
SELECT * FROM orders
WHERE id > #{lastId}
ORDER BY id
LIMIT 20;

应用场景： 百万级以上数据分页查询优化。

9. 数据更新与删除

9.1 INSERT ... ON DUPLICATE KEY UPDATE 冲突更新

示例（MySQL）：

INSERT INTO user_stats(user_id, login_count, last_login)
VALUES (1, 1, NOW())
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login = NOW();

应用场景： 统计用户登录次数，upsert 操作。

9.2 REPLACE INTO 替换插入

示例（MySQL）：

REPLACE INTO products(id, name, price) VALUES (1, '新名称', 199);

应用场景： 存在则删除旧记录再插入，不存在则直接插入。

9.3 DELETE 加锁注意事项

示例：

-- 分批删除大表数据
DELETE FROM logs
WHERE created_at < '2023-01-01'
LIMIT 1000;
-- 循环执行直至删除完毕

应用场景： 大表历史数据清理，避免长时间锁表。

9.4 UPDATE 加锁注意事项

示例：

-- 带条件的批量更新
UPDATE orders
SET status = '已完成', updated_at = NOW()
WHERE status = '处理中' AND updated_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

应用场景： 批量处理超时订单。

10. 其他高级技巧

10.1 WITH (CTE) 公共表表达式

示例：

WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
),
vip_users AS (
    SELECT * FROM users WHERE level >= 3
)
SELECT
    u.username,
    u.level
FROM active_users u
INNER JOIN vip_users v ON u.id = v.id;

应用场景： 简化复杂查询、提高可读性、支持递归查询。

10.2 递归 CTE

示例（查组织架构）：

WITH RECURSIVE org_tree AS (
    -- 起始：根节点
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归：下级
    SELECT e.id, e.name, e.manager_id, ot.depth + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;

应用场景： 无限级分类、菜单树、组织架构。

10.3 UNION / UNION ALL 合并结果

示例：

SELECT username FROM users WHERE status = 'active'
UNION
SELECT username FROM admins WHERE active = 1;

-- UNION ALL 不过滤重复
SELECT category FROM products
UNION ALL
SELECT category FROM archived_products;

应用场景： 合并多个查询结果、合并历史表和当前表。

UNION 自动去重（有性能开销），不需要去重时用 UNION ALL。

10.4 随机抽样

示例（MySQL）：

-- 随机取 10 条
SELECT * FROM products ORDER BY RAND() LIMIT 10;

-- 随机抽样（数据量大时 RAND() 性能差，用 ID 区间随机）
SELECT * FROM products
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM products)))
LIMIT 10;

应用场景： 随机推荐、测试数据抽样。

10.5 合并区间（Gaps and Islands）

示例（找出连续登录天数）：

SELECT
    user_id,
    COUNT(*) AS consecutive_days
FROM (
    SELECT
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
    FROM user_logins
) t
GROUP BY user_id, grp;

应用场景： 分析用户连续行为、股票连续上涨天数。

10.6 JSON 字段查询

示例（MySQL 5.7+）：

-- 插入 JSON
INSERT INTO orders(data) VALUES ('{"product": "A", "qty": 3}');

-- 查询 JSON 字段
SELECT * FROM orders WHERE JSON_EXTRACT(data, '$.product') = 'A';

-- 简写
SELECT * FROM orders WHERE data->>'$.product' = 'A';

-- JSON 包含
SELECT * FROM orders WHERE JSON_CONTAINS(data, '"A"', '$.product');

应用场景： 存储灵活结构数据如订单扩展属性、用户标签。

10.7 IFNULL + GROUP BY 统计转换

示例：

SELECT
    DATE(order_date) AS order_date,
    IFNULL(SUM(amount), 0) AS total_amount
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY DATE(order_date)
ORDER BY order_date;

应用场景： 补零统计——即使某天无订单也显示 0 而不是空行。

10.8 动态条件拼接

示例（以应用程序语言伪代码示意）：

-- 根据条件动态拼接 AND
SELECT * FROM products
WHERE 1=1
  [AND category = ?]
  [AND min_price <= ?]
  [AND max_price >= ?]
  [AND name LIKE '%' + ? + '%']

应用场景： 后端动态查询构建，防止 SQL 注入。

附录：数据库差异说明

特性	MySQL	PostgreSQL	SQL Server
分页	`LIMIT offset, count`	`OFFSET x ROWS FETCH NEXT y ROWS ONLY`	`OFFSET x ROWS FETCH NEXT y ROWS ONLY`
递归 CTE	✅	✅	✅
窗口函数	✅（8.0+）	✅	✅
JSON 支持	✅	✅（JSONB 更高效）	✅
正则	`REGEXP`	`~`	`LIKE` + CLR
字符串拼接	`CONCAT()`	`\|\|`	`+` 或 `CONCAT()`

具体语法请参考对应数据库文档。

持续补充中，欢迎提供案例。

posted on 2026-04-29 11:30 code0101 阅读(23) 评论(0) 收藏举报

刷新页面返回顶部

gao

导航

公告

MySQL常用语句

SQL 使用技巧汇总

目录

1. 基础查询

1.1 SELECT 获取指定列

1.2 DISTINCT 去重

1.3 LIMIT 分页

1.4 SELECT INTO 复制表数据

2. 条件过滤

2.1 IF / CASE 条件表达式

2.2 IN 多值匹配

2.3 LIKE 模糊匹配

2.4 REGEXP 正则匹配

2.5 NULL 处理

3. 聚合与分组

3.1 COUNT 统计行数

3.2 GROUP BY 分组聚合

3.3 HAVING 过滤分组结果

3.4 GROUP_CONCAT 行转列（MySQL）

4. 多表连接

4.1 INNER JOIN 内连接

4.2 LEFT JOIN 左连接

4.3 RIGHT JOIN 右连接

4.4 FULL OUTER JOIN 全连接

4.5 多表连接

4.6 自连接

5. 子查询

5.1 WHERE 中的子查询

5.2 IN 中的子查询

5.3 FROM 中的子查询（派生表）

5.4 EXISTS 判断存在性

6. 窗口函数

6.1 OVER + PARTITION BY 分区计算

6.2 ROW_NUMBER / RANK / DENSE_RANK 排名

6.3 LAG / LEAD 前后行数据

6.4 FIRST_VALUE / LAST_VALUE

6.5 SUM / AVG / COUNT 窗口累计

7. 常见函数

7.1 字符串函数

7.2 日期时间函数

7.3 数学函数

7.4 条件函数

8. 性能优化

8.1 EXPLAIN 分析执行计划

8.2 创建索引

8.3 避免 SELECT *

8.4 批量插入

8.5 批量更新

8.6 分页优化

9. 数据更新与删除

9.1 INSERT ... ON DUPLICATE KEY UPDATE 冲突更新

9.2 REPLACE INTO 替换插入

9.3 DELETE 加锁注意事项

9.4 UPDATE 加锁注意事项

10. 其他高级技巧

10.1 WITH (CTE) 公共表表达式

10.2 递归 CTE

10.3 UNION / UNION ALL 合并结果

10.4 随机抽样

10.5 合并区间（Gaps and Islands）

10.6 JSON 字段查询

10.7 IFNULL + GROUP BY 统计转换

10.8 动态条件拼接

附录：数据库差异说明