目录
一、四大类的定位（先看这张速查表）
| 类别 | 全称 | 主要作用 | 典型语句 | 是否改动数据 | 是否改动结构 | 事务性 | 权限相关 |
|---|---|---|---|---|---|---|---|
| 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 shop
DEFAULT CHARACTER SET utf8mb4
DEFAULT 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_user
FOREIGN KEY (user_id) REFERENCES shop.user(id)
ON UPDATE CASCADE
ON 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+）
-- 计算每个用户订单累积金额与序号
SELECT
o.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场景。
扫一扫
1087
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
