MySQL 的 DDL / DML / DQL / DCL 做一次系统梳理：概念区别→常用语法→特点与注意点→实战小例子→常见面试/坑点速记
一、四大类的定位（先看这张速查表）
| 类别 | 全称 | 主要作用 | 典型语句 | 是否改动数据 | 是否改动结构 | 事务性 | 权限相关 |
|---|---|---|---|---|---|---|---|
| DDL | Data Definition Language | 定义/变更数据库对象结构 | CREATE / ALTER / DROP / RENAME / TRUNCATE | 否（但会影响数据存在形态，TRUNCATE 会清空） | 是 | 有些操作可原子化（8.0起部分原子DDL），通常自动提交 | 否 |
| DML | Data Manipulation Language | 新增/修改/删除数据 | INSERT / UPDATE / DELETE / REPLACE | 是 | 否 | 支持事务 | 否 |
| DQL | Data Query Language | 查询数据 | SELECT（含 JOIN/GROUP BY/HAVING/WINDOW 等） | 否 | 否 | 参与事务一致性（快照读/锁定读） | 否 |
| DCL | Data Control Language | 账户、权限与角色控制 | CREATE USER / GRANT / REVOKE / DROP USER / CREATE ROLE | 否 | 否 | 不涉及业务事务 | 是 |
另外常见还有 TCL（Transaction Control Language）：
START TRANSACTION / COMMIT / ROLLBACK / SAVEPOINT / SET TRANSACTION，配合 DML 使用。
二、DDL（定义结构）
1）创建库/表
-- 创建数据库
CREATE DATABASE IF NOT EXISTS shopDEFAULT CHARACTER SET utf8mb4DEFAULT COLLATE utf8mb4_0900_ai_ci;-- 创建表（InnoDB、主键、自增、唯一索引、外键）
CREATE TABLE IF NOT EXISTS shop.user (id BIGINT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL UNIQUE,email VARCHAR(100),created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;-- 订单表，外键参照 user(id)
CREATE TABLE shop.orders (id BIGINT PRIMARY KEY AUTO_INCREMENT,user_id BIGINT NOT NULL,amount DECIMAL(10,2) NOT NULL,status ENUM('PENDING','PAID','CANCELLED') NOT NULL DEFAULT 'PENDING',created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,CONSTRAINT fk_orders_userFOREIGN KEY (user_id) REFERENCES shop.user(id)ON UPDATE CASCADEON DELETE RESTRICT
) ENGINE=InnoDB;
2）修改、删除结构
-- 增加列 / 修改列类型 / 重命名列
ALTER TABLE shop.user ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE shop.user MODIFY COLUMN phone VARCHAR(30);
ALTER TABLE shop.user RENAME COLUMN phone TO mobile;-- 索引：创建/删除（也可写在 ALTER TABLE 中）
CREATE INDEX idx_orders_userid ON shop.orders(user_id);
DROP INDEX idx_orders_userid ON shop.orders;-- 重命名表 / 删除表 / 清空表
RENAME TABLE shop.orders TO shop.order_main;
DROP TABLE IF EXISTS shop.order_main;
TRUNCATE TABLE shop.user; -- 清空数据、重置自增，速度快，DDL 语句
DDL 特点与注意
自动提交：DDL 通常隐式提交事务，不会被后续
ROLLBACK回滚。元数据锁（MDL）：
ALTER/CREATE/DROP会对表加 MDL，可能阻塞业务。TRUNCATE vs DELETE：
TRUNCATE是 DDL，清空全表、重置自增、不可逐行触发触发器；DELETE是 DML，可带WHERE、可回滚（在事务中）。字符集：MySQL 8.x 推荐
utf8mb4；避免历史utf8（不完整）。
三、DML（操作数据）
1）INSERT（含批量、插入或更新）
-- 单行/多行插入
INSERT INTO shop.user (username, email) VALUES('alice', 'alice@x.com'),('bob', 'bob@x.com');-- 基于查询插入
INSERT INTO shop.orders (user_id, amount, status)
SELECT id, 99.99, 'PENDING' FROM shop.user WHERE username='alice';-- Upsert：主键/唯一键冲突时更新（MySQL 常用）
INSERT INTO shop.user (id, username, email)
VALUES (1, 'alice', 'a@x.com')
ON DUPLICATE KEY UPDATE email = VALUES(email), updated_at = CURRENT_TIMESTAMP;-- 插入忽略错误（如唯一键冲突直接跳过）
INSERT IGNORE INTO shop.user (username, email) VALUES ('alice', 'dup@x.com');
2）UPDATE / DELETE
-- 更新
UPDATE shop.user
SET email='alice_new@x.com'
WHERE username='alice';-- 带 JOIN 的更新（MySQL 支持）
UPDATE shop.orders o
JOIN shop.user u ON o.user_id = u.id
SET o.status='CANCELLED'
WHERE u.username='bob';-- 删除：务必加 WHERE，结合事务
DELETE FROM shop.orders WHERE status='CANCELLED' AND created_at < NOW() - INTERVAL 30 DAY;
3）REPLACE（MySQL 特有）
-- 如果存在相同主键/唯一键记录：先删后插；否则直接插
REPLACE INTO shop.user (id, username, email) VALUES (1, 'alice', 're@x.com');
DML 特点与注意
事务支持（InnoDB）：用
START TRANSACTION ... COMMIT/ROLLBACK保证原子性。批量写入：合并多行 INSERT 明显快于逐行。
Upsert：
INSERT ... ON DUPLICATE KEY UPDATE优于先查后更；REPLACE会删除重建（触发删除副作用）。安全更新模式：某些客户端默认不允许无条件
UPDATE/DELETE；建议总是加WHERE或LIMIT（如果语义允许）。
四、DQL（查询数据）
1）基础查询
-- 投影/过滤/排序/分页
SELECT id, username, email
FROM shop.user
WHERE email LIKE '%@x.com'
ORDER BY id DESC
LIMIT 10 OFFSET 0;
2）聚合与分组
SELECT u.id, u.username, COUNT(o.id) AS order_cnt, SUM(o.amount) AS total_amount
FROM shop.user u
LEFT JOIN shop.orders o ON o.user_id = u.id AND o.status='PAID'
GROUP BY u.id, u.username
HAVING COUNT(o.id) > 0
ORDER BY total_amount DESC;
3）子查询 / CTE（8.0+）
-- 子查询
SELECT * FROM shop.user
WHERE id IN (SELECT user_id FROM shop.orders WHERE amount > 100);-- CTE（WITH）可读性更好
WITH big_order AS (SELECT user_id, amount FROM shop.orders WHERE amount > 100
)
SELECT u.username, b.amount
FROM shop.user u
JOIN big_order b ON b.user_id = u.id;
4）窗口函数（8.0+）
-- 计算每个用户订单累积金额与序号
SELECTo.id, o.user_id, o.amount,SUM(o.amount) OVER (PARTITION BY o.user_id ORDER BY o.created_at) AS running_total,ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.amount DESC) AS rn
FROM shop.orders o;
5）一致性与锁
快照读：普通
SELECT读取一致性视图，不加锁。锁定读：
SELECT ... FOR UPDATE/LOCK IN SHARE MODE会加行锁，常用于事务内检查后再修改。EXPLAIN：优化查询前先
EXPLAIN看执行计划；必要时建索引或调整 SQL。
五、DCL（账户、权限、角色）
1）账户与密码
-- 创建用户（限制来源主机）
CREATE USER 'reporter'@'%' IDENTIFIED BY 'StrongP@ssw0rd';-- 修改密码 / 失效
ALTER USER 'reporter'@'%' IDENTIFIED BY 'StrongerP@ss1';
DROP USER 'reporter'@'%';
2）授权与回收
-- 授权（最小权限原则）
GRANT SELECT ON shop.* TO 'reporter'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.orders TO 'reporter'@'%';-- 查看与回收
SHOW GRANTS FOR 'reporter'@'%';
REVOKE INSERT, UPDATE, DELETE ON shop.orders FROM 'reporter'@'%';
3）角色（8.0+）
CREATE ROLE 'read_only';
GRANT SELECT ON shop.* TO 'read_only';
GRANT 'read_only' TO 'reporter'@'%';
SET DEFAULT ROLE 'read_only' TO 'reporter'@'%';
说明：MySQL 8 起支持动态权限（如
REPLICATION SLAVE ADMIN等），用于管理运维功能；常见业务中以对象级（库/表/列/过程）权限为主。
六、TCL（事务控制，配合 DML）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- MySQL 默认START TRANSACTION;UPDATE shop.orders SET status='PAID' WHERE id=1001;SAVEPOINT s1;UPDATE shop.user SET email='oops@x.com' WHERE id=1;ROLLBACK TO s1; -- 撤销误操作
COMMIT;
要点
尽量短事务，避免长时间持锁。
明确隔离级别对“幻读/不可重复读”的影响；需要强一致时用
SELECT ... FOR UPDATE。
七、典型对比与坑点速记
DROP vs TRUNCATE vs DELETE
DROP TABLE：删表定义+数据，结构直接没了。TRUNCATE TABLE：清空数据、重置自增，快，不可逐行回滚/触发器。DELETE FROM t：逐行删除，可加WHERE，在事务内可回滚。
INSERT 冲突处理
INSERT ... ON DUPLICATE KEY UPDATE（推荐）REPLACE INTO：实际是删后重插，可能触发“删除副作用”。
索引
where/join/order by/group by 的列要评估索引；避免函数包裹导致索引失效（如
WHERE DATE(created_at)=...）。组合索引遵循最左前缀。
外键
需要事务型引擎
InnoDB；明确ON DELETE/UPDATE CASCADE|RESTRICT|SET NULL语义。大吞吐写场景要评估外键带来的约束检查成本。
字符集与排序规则
统一
utf8mb4，跨库/表/连接保持一致，避免“问号/乱码”。
原子 DDL（8.0+）
部分 DDL 具备原子性（失败自动回滚元数据），但依旧会隐式提交，不能当普通事务使用。
八、从零到一：小实战脚本（可直接粘贴）
-- 1. 结构（DDL）
CREATE DATABASE IF NOT EXISTS demo DEFAULT CHARACTER SET utf8mb4;
USE demo;CREATE TABLE user (id BIGINT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL UNIQUE,email VARCHAR(100),created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;CREATE TABLE orders (id BIGINT PRIMARY KEY AUTO_INCREMENT,user_id BIGINT NOT NULL,amount DECIMAL(10,2) NOT NULL,status ENUM('PENDING','PAID','CANCELLED') NOT NULL DEFAULT 'PENDING',created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES user(id)
) ENGINE=InnoDB;CREATE INDEX idx_orders_userid ON orders(user_id);-- 2. 数据（DML）
INSERT INTO user (username, email) VALUES('alice', 'alice@x.com'),('bob', 'bob@x.com');INSERT INTO orders (user_id, amount, status)
SELECT id, 128.88, 'PAID' FROM user WHERE username='alice';-- 3. 查询（DQL）
SELECT u.username, COUNT(o.id) AS cnt, SUM(o.amount) AS total
FROM user u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username
HAVING total IS NOT NULL
ORDER BY total DESC;-- 4. 权限（DCL）
CREATE USER 'reporter'@'%' IDENTIFIED BY 'StrongP@ss';
GRANT SELECT ON demo.* TO 'reporter'@'%';
九、记忆
改结构用 DDL；
改数据用 DML；
查数据是 DQL；
管权限用 DCL；
管事务用 TCL。
安全与性能两条线：权限最小化（DCL）、索引+EXPLAIN（DQL）、短事务（TCL）、避免无 WHERE 的批量 DML。
面试常问：
DELETE vs TRUNCATE vs DROP、ON DUPLICATE KEY UPDATE、事务隔离级别、最左前缀原则、SELECT ... FOR UPDATE场景。