开发中经常用到的 MySQL SQL 语句大全

执行 UPDATE、DELETE、ALTER TABLE 或 DROP 前，请先确认环境、备份策略和影响行数。应用代码应使用参数化查询，避免拼接 SQL。

一、基础查询 SELECT

查询全部或指定列

SELECT * FROM users;

SELECT id, name, age
FROM users;

别名与计算列

SELECT
    id,
    name AS user_name,
    price * quantity AS amount
FROM order_items;

二、条件过滤

WHERE、AND 与 OR

SELECT id, name
FROM users
WHERE age >= 18
  AND status = 1
  AND (city = 'SH' OR city = 'BJ');

LIKE、IN、BETWEEN 与 NULL

SELECT * FROM users WHERE name LIKE '%Tom%';
SELECT * FROM users WHERE city IN ('BJ', 'SH');
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE deleted_at IS NULL;

NULL 不能使用 = NULL 或 != NULL 判断，应使用 IS NULL 或 IS NOT NULL。LIKE 以 % 开头时通常难以利用普通 B-Tree 索引。

三、排序、去重与分页

SELECT DISTINCT city
FROM users
ORDER BY city ASC;

SELECT id, name, created_at
FROM users
ORDER BY created_at DESC, id DESC
LIMIT 10 OFFSET 20;

SELECT id, name, created_at
FROM users
WHERE id < :last_id
ORDER BY id DESC
LIMIT 20;

四、聚合、分组与 HAVING

SELECT
    city,
    COUNT(*) AS user_count,
    AVG(age) AS avg_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM users
WHERE status = 1
GROUP BY city
HAVING COUNT(*) > 10
ORDER BY user_count DESC;

• WHERE 在分组前过滤明细行。
• HAVING 在分组后过滤聚合结果。
• 开启 ONLY_FULL_GROUP_BY 时，非聚合列应满足分组规则。

五、多表 JOIN

INNER JOIN：只保留匹配行

SELECT u.id, u.name, o.order_no
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id;

LEFT JOIN：保留左表全部行

SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name;

查找没有订单的用户

SELECT u.id, u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL;

关联字段通常应建立合适索引。把右表过滤条件放在 WHERE 还是 ON 中，可能改变 LEFT JOIN 的结果语义。

六、子查询与 CTE

查询订单数最多的用户

SELECT u.*
FROM users AS u
WHERE u.id = (
    SELECT o.user_id
    FROM orders AS o
    GROUP BY o.user_id
    ORDER BY COUNT(*) DESC
    LIMIT 1
);

使用 CTE 拆分复杂查询（MySQL 8+）

WITH order_stats AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT u.id, u.name, s.order_count
FROM users AS u
JOIN order_stats AS s ON s.user_id = u.id
WHERE s.order_count >= 10;

七、新增、修改与删除

INSERT

INSERT INTO users (name, age, email)
VALUES ('Tom', 20, 'tom@example.com');

INSERT INTO users (name, age, email)
VALUES
    ('Tom', 20, 'tom@example.com'),
    ('Lucy', 22, 'lucy@example.com');

UPDATE

UPDATE users
SET age = 25,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

DELETE

DELETE FROM users
WHERE id = 1;

写操作前可以先用相同 WHERE 执行 SELECT，确认目标行。生产环境中应限制账号权限，并关注受影响行数。

八、事务与行锁

START TRANSACTION;

SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
-- 出错时执行：ROLLBACK;

• 事务中的表应使用支持事务的存储引擎，例如 InnoDB。
• 保持事务短小，避免在持锁期间执行网络请求或等待用户输入。
• 按固定顺序访问资源，有助于降低死锁概率。

九、创建与修改表

常用建表模板

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT UNSIGNED DEFAULT NULL,
    email VARCHAR(100) NOT NULL,
    status TINYINT NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_users_email (email),
    KEY idx_users_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE

ALTER TABLE users ADD COLUMN avatar VARCHAR(255) NULL;
ALTER TABLE users MODIFY COLUMN age INT UNSIGNED DEFAULT NULL;
ALTER TABLE users DROP COLUMN avatar;

DDL 可能锁表、重建表或消耗大量 I/O。对大表执行前，应结合 MySQL 版本、存储引擎和 Online DDL 支持情况评估。

十、索引与执行计划

创建、查看与删除索引

CREATE INDEX idx_users_age ON users (age);
CREATE UNIQUE INDEX uk_users_email ON users (email);
SHOW INDEX FROM users;
DROP INDEX idx_users_age ON users;

查看执行计划

EXPLAIN
SELECT id, name
FROM users
WHERE status = 1
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1001;

• 重点关注访问类型、候选索引、实际使用索引、预估行数与额外操作。
• 联合索引的列顺序应结合常用过滤、排序和分组条件设计。
• 索引会增加写入与存储成本，不是越多越好。

十一、日期与常用函数

SELECT NOW(), CURRENT_DATE, CURRENT_TIMESTAMP;

SELECT *
FROM orders
WHERE created_at >= '2026-01-01 00:00:00'
  AND created_at <  '2026-02-01 00:00:00';

SELECT CONCAT(name, ' <', email, '>') AS display_name
FROM users;

SELECT COALESCE(nickname, name) AS display_name
FROM users;

按时间范围查询时，推荐使用左闭右开区间，避免月底天数和微秒精度问题。尽量不要对索引列包裹函数，否则可能影响索引使用。

十二、开发安全清单

• 使用预编译参数，绝不直接拼接用户输入。
• 业务账号遵循最小权限原则，避免使用 root 连接应用。
• UPDATE 与 DELETE 必须确认 WHERE 条件和影响行数。
• 分页查询必须使用稳定排序，避免结果漂移。
• 上线索引或 DDL 前先在接近生产数据量的环境验证。
• 慢查询先看 EXPLAIN，再结合慢日志和实际负载判断。