- 张芷铭的个人博客

SQL 完整语法手册，覆盖 95% 日常开发场景，从基础操作到高级查询、索引优化一应俱全。

数据库操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 创建数据库
CREATE DATABASE IF NOT EXISTS 库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看/选择数据库
SHOW DATABASES;
SELECT DATABASE();
USE 库名;
SHOW CREATE DATABASE 库名;

-- 修改/删除
ALTER DATABASE 库名 CHARACTER SET utf8mb4;
DROP DATABASE IF EXISTS 库名;

表操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 创建表（完整模板）
CREATE TABLE IF NOT EXISTS 用户表 (
    user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    phone CHAR(11) COMMENT '手机号',
    gender TINYINT DEFAULT 0 COMMENT '性别：0-未知 1-男 2-女',
    user_status TINYINT DEFAULT 1 COMMENT '状态：0-禁用 1-正常',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

-- 查看表
SHOW TABLES;
DESC 表名;
SHOW CREATE TABLE 表名;

-- 修改表
ALTER TABLE 表名 ADD COLUMN 新字段 类型 AFTER 已有字段;
ALTER TABLE 表名 MODIFY COLUMN 字段名 新类型;
ALTER TABLE 表名 CHANGE COLUMN 旧字段 新字段 类型;
ALTER TABLE 表名 DROP COLUMN 字段名;
ALTER TABLE 表名 ADD INDEX 索引名 (字段名);
RENAME TABLE 旧表名 TO 新表名;

-- 删除表
DROP TABLE IF EXISTS 表名;
TRUNCATE TABLE 表名;  -- 清空数据，保留结构

数据类型

数值类型

类型	大小	用途
TINYINT	1字节	状态、性别、年龄
INT	4字节	主键、ID、数量
BIGINT	8字节	超大ID、金额（分）
DECIMAL(M,D)	可变	金额、价格（精确）
FLOAT/DOUBLE	4/8字节	科学计算

字符串类型

类型	大小	用途
CHAR(M)	0-255字符	固定长度：手机号、身份证
VARCHAR(M)	0-65535字符	可变长度：用户名、地址
TEXT	0-65535字节	长文本
LONGTEXT	0-4GB	超长文本

时间类型

类型	格式	用途
DATE	YYYY-MM-DD	日期
TIME	HH:MM:SS	时间
DATETIME	YYYY-MM-DD HH:MM:SS	日期+时间
TIMESTAMP	时间戳	自动更新时间

数据操作（CRUD）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
-- 插入
INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2);
INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2), (值3, 值4);  -- 批量
INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2)
ON DUPLICATE KEY UPDATE 字段2 = VALUES(字段2);  -- 插入或更新

-- 查询
SELECT 字段1, 字段2 FROM 表名;
SELECT DISTINCT 字段 FROM 表名;  -- 去重
SELECT 字段 AS '别名' FROM 表名;

-- 更新（必须加 WHERE）
UPDATE 表名 SET 字段1 = 值1 WHERE 条件;
UPDATE 商品表 SET stock = stock - 1 WHERE product_id = 100;

-- 删除（必须加 WHERE）
DELETE FROM 表名 WHERE 条件;

条件查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 比较运算
WHERE age = 18 / > 18 / >= 18 / < 30 / <= 30 / != 18 / <> 18

-- 逻辑运算
WHERE age >= 18 AND age <= 30
WHERE gender = 1 OR gender = 2
WHERE NOT (user_status = 0)

-- 范围查询
WHERE age BETWEEN 18 AND 30
WHERE age NOT BETWEEN 18 AND 30
WHERE user_id IN (1, 3, 5)
WHERE user_id NOT IN (2, 4, 6)

-- NULL 查询
WHERE email IS NULL
WHERE email IS NOT NULL

-- 模糊查询
WHERE username LIKE '张%'   -- 以张开头
WHERE username LIKE '%三'   -- 以三结尾
WHERE username LIKE '%张%'  -- 包含张
WHERE username LIKE '张_'   -- 张+一个字符

排序与分页

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- 排序
ORDER BY age ASC   -- 升序（默认）
ORDER BY age DESC  -- 降序
ORDER BY user_status DESC, create_time ASC  -- 多字段

-- 分页
LIMIT 10              -- 前 10 条
LIMIT 0, 10           -- 第 1 页（跳过 0 条，取 10 条）
LIMIT 10, 10          -- 第 2 页
LIMIT (页码-1)*每页条数, 每页条数

聚合与分组

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 聚合函数
SELECT COUNT(*) FROM 表名;           -- 总数
SELECT COUNT(DISTINCT 字段) FROM 表名; -- 去重计数
SELECT SUM(price) FROM 订单表;
SELECT AVG(age) FROM 用户表;
SELECT MAX(age), MIN(create_time) FROM 用户表;

-- 分组统计
SELECT gender, COUNT(*) AS '人数' FROM 用户表 GROUP BY gender;
SELECT category_id, AVG(price) AS '均价' FROM 商品表
GROUP BY category_id HAVING AVG(price) > 100;

WHERE vs HAVING：WHERE 在分组前筛选，HAVING 在分组后筛选。

多表关联

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 内连接：只返回匹配记录
SELECT u.username, o.order_id FROM 用户表 u
INNER JOIN 订单表 o ON u.user_id = o.user_id;

-- 左连接：左表全部，右表无匹配则为 NULL
SELECT u.username, o.order_id FROM 用户表 u
LEFT JOIN 订单表 o ON u.user_id = o.user_id;

-- 右连接：右表全部，左表无匹配则为 NULL
SELECT u.username, o.order_id FROM 用户表 u
RIGHT JOIN 订单表 o ON u.user_id = o.user_id;

-- 自连接：查询员工及上级
SELECT e.employee_name AS '员工', m.employee_name AS '上级'
FROM 员工表 e LEFT JOIN 员工表 m ON e.manager_id = m.employee_id;

子查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- WHERE 子查询
SELECT * FROM 商品表 WHERE price > (SELECT AVG(price) FROM 商品表);

-- IN 子查询
SELECT * FROM 用户表 WHERE user_id IN (SELECT DISTINCT user_id FROM 订单表 WHERE amount > 1000);

-- EXISTS 子查询（性能更优）
SELECT * FROM 用户表 u WHERE EXISTS (SELECT 1 FROM 订单表 o WHERE o.user_id = u.user_id AND o.amount > 1000);

-- FROM 子查询（派生表）
SELECT t.category_id, t.avg_price FROM (
    SELECT category_id, AVG(price) AS avg_price FROM 商品表 GROUP BY category_id
) t WHERE t.avg_price > 100;

索引操作

1
2
3
4
5
6
7
8
9
-- 创建索引
CREATE INDEX idx_username ON 用户表(username);
CREATE UNIQUE INDEX idx_email ON 用户表(email);
CREATE INDEX idx_status_time ON 用户表(user_status, create_time);  -- 复合索引
CREATE FULLTEXT INDEX idx_content ON 文章表(content);  -- 全文索引

-- 查看与删除
SHOW INDEX FROM 表名;
DROP INDEX 索引名 ON 表名;

视图操作

1
2
3
4
5
6
7
8
-- 创建视图
CREATE VIEW 用户订单视图 AS
SELECT u.user_id, u.username, o.order_id, o.amount
FROM 用户表 u INNER JOIN 订单表 o ON u.user_id = o.user_id;

-- 使用/删除视图
SELECT * FROM 用户订单视图 WHERE amount > 100;
DROP VIEW IF EXISTS 视图名;

用户与权限

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 用户管理
CREATE USER 'dev_user'@'%' IDENTIFIED BY 'Password123!';
ALTER USER '用户名'@'主机' IDENTIFIED BY '新密码';
DROP USER '用户名'@'主机';

-- 权限管理
GRANT SELECT, INSERT, UPDATE, DELETE ON 库名.* TO 'dev_user'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%';
REVOKE 权限 ON 库名.表 FROM '用户名'@'主机';
FLUSH PRIVILEGES;
SHOW GRANTS FOR '用户名'@'主机';

事务处理

1
2
3
4
5
START TRANSACTION;
UPDATE 账户表 SET balance = balance - 100 WHERE user_id = 1;
UPDATE 账户表 SET balance = balance + 100 WHERE user_id = 2;
COMMIT;   -- 成功提交
ROLLBACK; -- 失败回滚

ACID 特性：原子性（全成功或全失败）、一致性（数据完整）、隔离性（事务互不干扰）、持久性（提交后永久生效）。

最佳实践

命名规范

库名、表名、字段名：小写 + 下划线
主键：id 或 表名_id
时间字段：create_time, update_time

设计规范

每表必有主键、create_time、update_time
字符集统一 utf8mb4
金额用 DECIMAL 或 BIGINT（存分）

查询优化

避免 SELECT *
WHERE 条件用索引字段
大偏移量分页用 WHERE id > last_id
复杂查询先 EXPLAIN

安全规范

生产环境禁用 root
UPDATE/DELETE 必须加 WHERE
定期备份

常用速查

操作	语句
当前时间	`SELECT NOW();`
当前用户	`SELECT USER();`
当前数据库	`SELECT DATABASE();`
版本	`SELECT VERSION();`
连接数	`SHOW PROCESSLIST;`
杀死连接	`KILL 连接ID;`

数据库操作

表操作

数据类型

数值类型

字符串类型

时间类型

数据操作（CRUD）

条件查询

排序与分页

聚合与分组

多表关联

子查询

索引操作

视图操作

用户与权限

事务处理

最佳实践

命名规范

设计规范

查询优化

安全规范

常用速查

Comments