SQL 语法速查手册：前端开发者的学习笔记

最近在写 Next.js + Supabase 的一个练手项目，突然又接触到了 SQL（虽然 Supabase 是通过类似 ORM 来操作数据库的），突然感觉 SQL 语法又陌生了。

真是学了不用，等于白学。

之前写过一篇一个前端小白，学习 SQL 语句，现在回头看，自己都看懵了，真的尬住了。

算了，重新整理一遍吧，并通过一个实战案例来巩固基础。

邂逅

SQL： Structured Query Language，称为结构化查询语句，简称 SQL。

SQL 编写规范

1. 关键词建议使用大写，比如 CREATE、TABLE（小写也可以，但大写更规范）
2. 语句末尾加分号
3. 如果表名或字段名是 SQL 关键词，用反引号 ``` 包裹
4. 表名用单数，字段名用蛇形命名（下划线分隔，如 user_id）

SQL 分类

• DDL（Data Definition Language）：数据定义语言，-----> 管"结构"
• DML（Data Manipulation Language）：数据操作语言，-----> 管"改数据"
• DQL（Data Query Language）：数据查询语言，-----> 管"查数据"
• DCL（Data Control Language）：数据控制语言，-----> 管"权限"

为了阅读方便，下面的 SQL 语句都用小写，但实际开发中推荐关键词用大写

SQL 类型

实际开发中常用的类型：

• int：存储整数
• varchar(100): 存储变长字符串，可以指定长度
• char：定长字符串，不够的自动在末尾填充空格
• double：存储浮点数
• date：存储日期 2023-05-27
• time：存储时间 10:13
• datetime：存储日期和时间 2023-05-27 10:13
• timestamp：存储日期时间的，但是范围小一点，而且会转为中央时区 UTC 的时间来存储
• text：存储长文本

SQL 设计了这么多数据类型，一是为了存储更丰富的信息，二是为了节省存储空间。不常用的类型，用到时再查就行。

表约束

• primary key: 主键
  • 主键是表中唯一的索引
  • 必须是 not null; 如果没有设置，mysql 也会自动设置；
  • 联合主键，多个字段合成的主键
  • 尽量不要使用业务主键
• unique: 唯一；除了主键以外，针对某些字段，也是唯一的。
• not null: 字段不能为空
• default: 默认值
• auto_increment: 自动增长；
• foreign key: 外键，与其他表的字段关联

1create table if not exists yyy(
2  id int primary key,
3  `name` varchar(50) unique not null,
4  age int default 18,
5  num int auto_increment,
6  homeId int, -- 外键
7  foreign key (homeId) references home(id) -- 关联 home 表的 id
8)
9

DDL（管结构）

库操作

1-- 查看所有数据库
2show databases;
3​
4-- 使用数据库 co_blog
5use co_blog;
6​
7-- 查看选择的数据库
8select database();
9​
10-- 创建数据库
11create database if not exists co_blog; -- 先判断 co_blog 库是否存在，不存在则创建
12create database co_blog; -- 若存在，会报错；
13​
14-- 删除数据库
15drop database if exists co_blog;
16drop database co_blog; -- 数据库不存在，会报错
17use co_blog; -- 使用 co_blog 库，后续表操作都在这个库下
18

表操作

1-- 查看库中所有的表
2show tables;
3​
4-- 创建一张表
5create table if not exists user(
6  id int primary key auto_increment,
7  name varchar(10),
8  age int
9);
10​
11-- 查看表结构
12desc user; -- 显示表的字段定义信息
13​
14-- 删除 user 表
15drop table if exists user;
16​
17-- 修改 user 表
18alter table user rename to users;             -- 修改表名 user 变成 users
19alter table user add height int;              -- 添加 height 字段，为 int 类型；
20alter table user change height newHeight int; -- height 改为 newHeight, 类型也可以重新定义
21alter table user modify height bigint;        -- 类型 int 变为 bigint
22alter table user drop height;                 -- 删除 height 字段
23

DML（管改数据）

增删改

插入数据

1-- 插入单条数据
2insert into user(username, emails) values('admin', '[email protected]');
3​
4-- 插入多条数据
5insert into user(username, emails)
6  values
7    ('admin', '[email protected]'),
8    ('admin2', '[email protected]'),
9    ('admin3', '[email protected]');
10

更新数据

1update `user` set name = '张三1', age = 19 where id = 1;
2

删除数据

1delete from `user` where id = 1;
2

DQL（管查询）

查询语句的执行顺序：先筛选数据，再分组过滤，然后排序，最后分页。

1SELECT *
2  FROM table_name
3  WHERE    xxx
4  GROUP BY xxx
5  HAVING   xxx
6  ORDER BY xxx
7  LIMIT    xxx;
8

取别名 as

1-- 查询所有字段
2select * from user;
3​
4-- 查询指定字段并取别名
5select name, age as c_age from user;
6​
7-- 别名的作用：多表查询时避免字段名冲突
8

比较运算符

1-- 比较运算符：>、<、=、!=、>=、<=
2
3select * from users where age > 20;  -- 年龄大于 20
4select * from users where age != 20; -- 年龄不等于 20
5

逻辑运算符

1-- name 为 张三，且 age 大于 10
2select * from users where name = '张三' and age > 10;
3select * from users where name = '张三' && age > 10;
4
5-- name 为 张三，或者 age 大于 10
6select * from users where name = '张三' or age > 10;
7select * from users where name = '张三' || age > 10;
8
9-- age 在 10 到 20 岁间的
10select * from users where age between 10 and 20;
11select * from users where age >=10 && age <= 20;
12
13-- age 为 10 或 20 的（in 的相反是 not in）
14select * from users where age in (10, 20);
15select * from users where age = 10 or age = 20;
16

模糊搜索 like

• %: 任意多个字符
• _: 任意一个字符

1select * from users where name like 't%';   -- 以 t 开头
2select * from users where name like '%t%';   -- 包含 t
3select * from users where name like '_t%';   -- 第二个字符是 t
4

排序 order by

• asc：升序（默认，可省略）
• desc：降序

1select * from users order by age asc;  -- 年龄升序
2select * from users order by age desc; -- 年龄降序
3

限制分页 limit

• limit 数据条数 offset 偏移量 (推荐)
• limit 偏移量，数据条数 （不推荐）

1-- limit 数据条数 offset 偏移量 (推荐)
2select * from users limit 30 offset 10;
3
4-- limit 偏移量，数据条数 (不推荐)
5select * from users limit 10, 30;
6
7-- 前端格式
8const pages = {
9  current: 3,
10  pageSize: 10,
11};
12
13-- 后端查询
14const offsetNum = (pages.current - 1) * pages.pageSize;
15const limitNum = pages.pageSize;
16
17-- sql
18const sql = [`select * from users limit ${limitNum} offset ${offsetNum}`](https://xplanc.org/primers/document/zh/10.Bash/90.%E5%B8%AE%E5%8A%A9%E6%89%8B%E5%86%8C/EX.users.md);
19

聚合函数

聚合函数： 先收集到一起，然后对收集的结果进行操作。（看成一组）

• avg：平均值
• max：最大值
• min：最小值
• sum：求和
• count：计数（统计行数）

1-- 平均值
2select avg(age) as avg_age from users;
3
4-- 计算人数
5select count(*) as count from users;
6
7-- 最大值
8select max(age) as maxAge from users;
9
10-- 求和
11select sum(age) as sumAge from users;
12

SQL 还有很多内置函数：

• 聚合函数：avg、count、sum、min、max
• 字符串函数：concat、substr、length、upper、lower
• 数值函数：round、ceil、floor、abs、mod
• 日期函数：year、month、day、date、time
• 条件函数：if、case
• 系统函数：version、database、user
• 类型转换函数：convert、cast、date_format、str_to_date
• 其他函数：nullif、coalesce、greatest、least

实际开发中，聚合函数最常用的是统计和分组，其他函数用到时再查就行。

分组 group by

聚合函数看成一组；有时需要进行分组，然后进行操作。

使用建议：group by 一定要配合聚合函数使用，不然分组没有意义（除非整张表都看成一个聚合函数，就不需要使用 group by）。

1-- 统计男女个数
2select sex, count(*) as num from users group by sex;
3

分组筛选条件 having

在进行分组的时候，有时也需要过滤条件。

但是 group by 与 where 不能一起使用，语法报错。取而代之的是 having。

1-- 根据 sex 性别进行分组，然后筛选出 count 大于 2
2select count(*) as count, sex from users group by sex having count > 2;
3

去重 distinct

1select distinct age from users;
2

多表

外键添加

创建表时添加外键：

1create table if not exists users(
2  id int primary key,
3  name varchar(255) not null,
4  role_id varchar(255) not null,
5  foreign key (role_id) references role(id)
6);
7

已有表添加外键：

1alter table users add role_id int;
2alter table users add foreign key (role_id) references role(id);
3

外键删除（更新）

表之间有关联后，不能直接删除或更新，否则会影响关联表。需要设置级联操作：要么一起更新，要么一起删除。

1-- 一起更新一起删除
2alter table user add foreign key (role_id) references role(id) ON DELETE CASCADE ON UPDATE CASCADE;
3

删除外键

1show create table users; -- 查看外键名称
2alter table users drop foreign key users_ibfk_1;
3

如果存在多个外键：

• users_ibfk_1
• users_ibfk_2
• ...

重新绑定外键

1alter table users add foreign key (role_id) references role(id)
2                                           on delete cascade
3                                           on update cascade;
4

多表查询

多表查询时，表之间必须有联系（通常是外键）。

1select * from users, team;
2

这样查询会产生大量无用数据，因为这是笛卡尔积（每行数据都会组合）。

表连接

• 左连接（left join）: 常用
• 右连接（right join）: 不常用
• 内连接（[cross/inner] join）: 常用
• 全连接（mysql 不支持全连接，需要 union）： 不常用

左右指的是以哪张表为主，展示该表的所有数据。

1from 左表 [left/right] join 右表
2

其实 left join 和 right join 可以等价，换一下表的顺序就行

左连接

1-- 左连接 LEFT JOIN. ON 连接条件
2select * from user left join role
3                    on user.role_id = role.id;
4
5-- 筛选出 role_id 不为 null 的（左连接以左表为主，如果右表没有匹配数据，右表字段为 null）
6select * from user left join role
7                           ON user.role_id = role.id
8                           WHERE role_id IS NOT NULL;
9

内连接

内连接只返回两表都匹配的数据，不以哪张表为主。

1select * from user join role on user.role_id = role.id;
2

针对多对多的查询出现时，就是多次采用连接即可。

子查询

SQL 支持嵌套查询，也就是在查询中嵌套另一个查询（子查询）。

1-- 查询年龄最大的人
2-- 方式一：分两步
3select max(age) from user;  -- 先查出最大年龄
4select name, age from user where age = xxx; -- 再根据年龄查数据
5
6-- 方式二：用子查询（推荐）
7select name, age from user where age = (select max(age) from user);
8

子查询还有个特有的语法 EXISTS、NOT EXISTS。

1-- 查询所有有角色的用户
2select name from user where EXISTS (select * from role where role.id = user.role_id);
3
4-- 查询所有没有角色的用户
5select name from user where NOT EXISTS (select * from role where role.id = user.role_id);
6

子查询可以在 select、insert、update、delete 中使用。

事务

修改多个关联表时，必须使用事务，保证要么全部成功，要么全部失败（回滚）。

1start transaction;    -- 开启事务
2insert into role(id, name) VALUES (4, '群众');
3insert into user(name, age, sex) VALUES ('james', 18, 4);
4
5-- 提交事务，执行之后就不能 rollback 了
6commit;
7-- 回滚事务
8rollback;
9

如果想回滚到某个中间点，可以使用 savepoint 设置保存点。

savepoint a1;：回滚到 a1 时，a1 之前的操作保留，a1 之后的操作被撤销。

1start transaction;  -- 开启事务
2savepoint a1;  -- savepoint 这一刻前面的依旧活着，这一刻后面的都被抹除
3insert into role(id, name) VALUES (4, '群众');
4savepoint a2;
5insert into user(name, age, sex) VALUES ('james', 18, 4);
6savepoint a3;
7
8rollback to a2; -- 回滚到 a2 的那一刻
9

事务隔离级别（有四种级别，比较复杂，自己也有点懵，一般用默认的就行）

1select @@transaction_isolation; -- 查看隔离级别
2

DCL（管权限）

权限管理在实际开发中较少用到，等遇到时再补充（其实自己也不知道）。

案例演示（Blog 的 CRUD）

用一个博客系统的增删改查来演示 SQL 的实际应用。

先设计表结构

1CREATE TABLE `user`(
2  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
3  username VARCHAR(100) UNIQUE NOT NULL COMMENT '用户名',
4  password VARCHAR(100) NOT NULL COMMENT '密码',
5  email VARCHAR(100) UNIQUE NOT NULL COMMENT '邮箱',
6  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
7);
8
9-- 标签表
10CREATE TABLE `tag`(
11  id int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
12  name varchar(100) UNIQUE NOT NULL COMMENT '标签名',
13  article_count int DEFAULT 0 COMMENT '文章数量',
14  created_at datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
15);
16
17-- 文章表
18CREATE TABLE [`article`](https://xplanc.org/primers/document/zh/03.HTML/EX.HTML%20%E5%85%83%E7%B4%A0/EX.article.md)(
19  id int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
20  title varchar(100) NOT NULL COMMENT '标题',
21  content text NOT NULL COMMENT '内容',
22  user_id int NOT NULL COMMENT '作者id',
23  like_count int NOT NULL DEFAULT '0' COMMENT '点赞数',
24  comment_count int NOT NULL DEFAULT '0' COMMENT '评论数',
25  created_at datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
26  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE
27);
28
29-- 文章标签表(中间表的级联方式要设置为 CASCADE，这个是固定的)
30CREATE TABLE `article_tag`(
31  id int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
32  article_id int NOT NULL COMMENT '文章id',
33  tag_id int NOT NULL COMMENT '标签id',
34  FOREIGN KEY (article_id) REFERENCES article(id) ON DELETE CASCADE ON UPDATE CASCADE,
35  FOREIGN KEY (tag_id) REFERENCES tag(id) ON DELETE CASCADE ON UPDATE CASCADE
36);
37
38-- 点赞表
39CREATE TABLE `like`(
40  id int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
41  article_id int NOT NULL COMMENT '文章id',
42  user_id int NOT NULL COMMENT '用户id',
43  created_at datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
44  FOREIGN KEY (article_id) REFERENCES article(id) ON DELETE CASCADE ON UPDATE CASCADE,
45  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE
46)
47
48-- 评论表
49CREATE TABLE `comment`(
50  id int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
51  article_id int NOT NULL COMMENT '文章id',
52  user_id int NOT NULL COMMENT '用户id',
53  target_user_id int NOT NULL COMMENT '被回复人id',
54  content varchar(255) NOT NULL COMMENT '内容',
55  parent_id int DEFAULT 0 COMMENT '父级id',
56  created_at datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
57  FOREIGN KEY (article_id) REFERENCES article(id) ON DELETE CASCADE ON UPDATE CASCADE,
58  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE
59)
60

ER 图为：

用户和标签的 CRUD

以用户表（user）为例，标签表（tag）同理

1-- 创建用户
2insert into user(username, password, email) VALUES ('admin', '123456', '[email protected]');
3
4-- 查询用户
5select * from user;
6
7-- 更新用户
8update user set password = '123456' where id = 1;
9
10-- 删除用户
11delete from user where id = 1;
12

文章的 CRUD

创建文章

1start transaction;
2
3-- 创建文章，获取文章 id（代码中获取 id 后执行下一步）
4insert into article(title, content, user_id, like_count, comment_count) VALUES ('标题', '内容', 1, 0, 0);
5
6-- 遍历 tagIds，与文章 id 一起插入中间表（代码中遍历 tagIds，组装 SQL）
7insert into article_tag(article_id, tag_id)
8  VALUES
9    ('文章id', '标签id1'),
10    ('文章id', '标签id2');
11
12-- 更新 tag 表的文章数量
13update tag set article_count = article_count + 1 where id in ('标签id1', '标签id2');
14
15commit;
16

删除文章

1delete from article where id = '文章id';
2-- 因为采用的是 cascade，所以 article_tag 等会自动删除
3

查询文章列表

1SELECT id,
2       title,
3       user_id,
4       like_count,
5       comment_count,
6       created_at
7FROM   article
8ORDER  BY created_at DESC
9LIMIT  10 OFFSET 0;
10
11-- 分页查询，按创建时间倒序
12-- like_count 和 comment_count 采用计数器冗余方案，简单性能好，但可能数据不一致
13

更新文章

1-- 1. 开启事务
2START TRANSACTION;
3
4-- 2. 更新文章表
5UPDATE article
6  SET
7    title = '新标题',
8    content = '新内容'
9WHERE id = 123 AND article.user_id = '用户id';
10
11-- 3. 根据文章 id，在中间表 article_tag 中拿到旧标签（a,b）, 然后根据用户传递的新标签（a,c）, 找出删除的标签（b），然后再 tag 表中更新文章数量（-1）
12UPDATE tag
13SET    article_count = article_count - 1
14WHERE  id IN (
15        SELECT tag_id
16        FROM   article_tag
17        WHERE  article_id = '文章id'
18          AND  tag_id NOT IN ('新标签id1', '新标签id2')   -- 新标签列表
19      );
20
21-- 4. 查找旧标签，然后使用 in 和 not in 交集，找出新增的标签，然后在 tag 表中更新文章数量（+1）
22UPDATE tag
23SET    article_count = article_count + 1
24WHERE  id IN ('新标签id1', '新标签id2')
25  AND  id NOT IN (SELECT tag_id FROM article_tag WHERE article_id = '文章id');
26
27-- 5. 更新中间表：先删除文章的所有标签，再插入新标签
28DELETE FROM article_tag WHERE article_id = '文章id';
29INSERT INTO article_tag(article_id, tag_id) VALUES
30('文章id', '新标签id1'),
31('文章id', '新标签id2');
32
33COMMIT;
34

点赞

无论是点赞还是取消点赞, 第一步是先判断是否已经点过赞

1SELECT * FROM `like` WHERE article_id = '文章id' AND user_id = '用户id' LIMIT 1;
2

如果查询到记录，说明已点赞，执行取消点赞；否则执行点赞。

1-- 点赞
2
3START TRANSACTION;
4--  1. 更新 like 表数据
5INSERT INTO `like`(article_id, user_id) VALUES ('文章id', '用户id');
6--  2. 更新 article 表数据（+1）
7UPDATE article SET like_count = like_count + 1 WHERE id = '文章id';
8COMMIT;
9-- 取消点赞
10
11START TRANSACTION;
12--  1. 删除 like 表数据
13DELETE FROM `like` WHERE  article_id = '文章id' AND user_id = '用户id';
14--  2. 更新 article 表数据（-1）
15UPDATE article SET like_count = like_count - 1 WHERE id = '文章id';
16COMMIT;
17

评论

写到这里发现，最初表设计有问题。本想用一个 parent_id 实现无限层级，但看了掘金和 B 站的评论设计，发现评论最多 2 层，其他都是平铺回复。

B 站的评论：

掘金的评论：

都是两层模型：

• 第 1 层：顶级评论（parent_id = NULL）
• 第 2 层：对顶级评论的回复（parent_id = 顶级.id）
• 第 3 层及以后：不再嵌套，把目标人 @nick 写进内容，parent_id 仍等于顶级评论 id，按时间平铺

新增评论

1START TRANSACTION;
2-- 情况一：顶级评论
3INSERT INTO comment(article_id, user_id, parent_id, target_user_id, content)
4  VALUES ('文章id', '用户id', NULL, NULL, '一级评论');
5
6-- 情况二：回复顶级评论或回复某人
7INSERT INTO comment(article_id, user_id, parent_id, target_user_id, content)
8  VALUES ('文章id', '用户id', '顶级评论id', '被回复人id', '二级评论');
9
10-- 3. 更新 article 表评论数（+1）
11UPDATE article SET comment_count = comment_count + 1 WHERE id = '文章id';
12
13COMMIT;
14