1969 字
10 分钟
SQL 基础速成:建表、增删改查与常用语法
2026-11-15

这一篇练什么#

不管你最后用 PostgreSQL 还是 MySQL,日常打交道的都是同一套 SQL 语言——先选哪个库的问题已经在 《数据库怎么选》 里解决了,这一篇只管把手练熟。我们用上一篇 《用 Docker 快速搭建三大数据库》 里建好的环境,围绕一张最典型的用户表,把建表、增、删、改、查、排序、聚合、关联全部走一遍。

文中 SQL 以 PostgreSQL 为主演示,MySQL 差异会随文标注;两边 95% 的基础语法是通用的。先连进 PostgreSQL:

Terminal window
docker exec -it lab-postgres psql -U appuser -d appdb

记住一条贯穿全文的核心原则:先用 SELECT 把要动的数据查出来确认,再执行 UPDATE 或 DELETE。这一个习惯能挡掉绝大多数线上事故。

建表:先把结构定清楚#

CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
age SMALLINT,
balance NUMERIC(10, 2) NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

这段定义里包含了建表最常用的元素:

元素作用说明
BIGSERIAL PRIMARY KEY主键,自增PostgreSQL 写法;MySQL 写 BIGINT AUTO_INCREMENT PRIMARY KEY
VARCHAR(n)变长字符串用户名、邮箱这类长度可控的文本
NOT NULL不允许为空关键字段务必加,空值是 bug 温床
UNIQUE唯一约束用户名、邮箱防重复靠它,不要只靠代码判断
NUMERIC(10, 2)精确小数钱必须用它,绝不能用 FLOAT/DOUBLE 存金额,浮点误差迟早对不上账
DEFAULT默认值余额默认 0、创建时间默认当前时间
TIMESTAMPTZ带时区时间PostgreSQL 推荐;MySQL 对应 DATETIME / TIMESTAMP

常用数据类型速记:整数用 INT / BIGINT,短文本用 VARCHAR,长文本用 TEXT,布尔用 BOOLEAN,日期用 DATE,时间用带时区的 TIMESTAMPTZ,半结构化数据 PostgreSQL 用 JSONB、MySQL 用 JSON。

删表和改表:

ALTER TABLE users ADD COLUMN phone VARCHAR(20); -- 加字段
ALTER TABLE users ALTER COLUMN age TYPE INT; -- 改类型(MySQL 用 MODIFY)
ALTER TABLE users DROP COLUMN phone; -- 删字段
DROP TABLE users; -- 删整张表,数据全没,慎用

增:INSERT 插入数据#

-- 插入一行
INSERT INTO users (username, email, age, balance)
VALUES ('zhangsan', 'zhangsan@example.com', 28, 100.50);
-- 一次插入多行,效率比逐条插高得多
INSERT INTO users (username, email, age, balance) VALUES
('lisi', 'lisi@example.com', 35, 200.00),
('wangwu', 'wangwu@example.com', 22, 50.25),
('zhaoliu', 'zhaoliu@example.com', 41, 0);

插入后想拿到自增 id:PostgreSQL 用 INSERT ... RETURNING id;,MySQL 用 SELECT LAST_INSERT_ID();。

查:SELECT 是 SQL 的半壁江山#

-- 查全部列(练习可以,生产代码里别用 *,列名写全)
SELECT * FROM users;
-- 只查需要的列
SELECT id, username, balance FROM users;
-- WHERE 过滤
SELECT * FROM users WHERE age >= 30;
SELECT * FROM users WHERE username = 'lisi';
SELECT * FROM users WHERE age BETWEEN 20 AND 40;
SELECT * FROM users WHERE username IN ('lisi', 'wangwu');
SELECT * FROM users WHERE email LIKE '%@example.com';
-- 多条件组合:AND 比 OR 优先,复杂条件加括号
SELECT * FROM users WHERE age >= 30 AND balance > 0;
-- 排序与分页
SELECT * FROM users ORDER BY balance DESC LIMIT 10;
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10; -- 第二页
-- 去重
SELECT DISTINCT age FROM users;

几个高频细节:

  • 字符串用单引号,双引号在 PostgreSQL 里是标识符(表名列名)的引号,别混。
  • 判断空值只能用 age IS NULL / age IS NOT NULL,写 age = NULL 永远查不到任何行。
  • LIKE 的 % 匹配任意长度字符,_ 匹配单个字符;前缀模糊(LIKE 'zhang%')能走索引,LIKE '%zhang' 这种前后都带 % 的写法走不了普通索引,数据量大时就是全表扫。
  • 分页深翻(比如 OFFSET 100000)性能很差,数据库要先数出前十万行再丢掉。数据量大时改用「记住上一页最后一个 id,WHERE id > ? ORDER BY id LIMIT 10」的游标分页。

改:UPDATE 必须带 WHERE#

UPDATE users SET balance = balance + 50 WHERE username = 'lisi';
UPDATE users SET age = 29, email = 'new@example.com' WHERE id = 1;
WARNING

UPDATE 和 DELETE 不带 WHERE 就是全表生效,没有回收站、没有撤销键。手滑一次,全表用户的余额都会被改掉。养成两个习惯:第一,先把同样的条件用 SELECT 查一遍确认影响行数;第二,重要操作先开事务(见下文),确认无误再提交。

事务的基本用法(PostgreSQL 与 MySQL InnoDB 都支持):

BEGIN;
UPDATE users SET balance = balance - 100 WHERE username = 'zhangsan';
UPDATE users SET balance = balance + 100 WHERE username = 'lisi';
-- 两条都成功才提交;任何一步不对,用 ROLLBACK 全部撤销
COMMIT;

转账这类「要么全成、要么全不成」的操作,必须放进同一个事务,这正是关系型数据库 ACID 的核心价值。

删:DELETE 与 TRUNCATE 的区别#

DELETE FROM users WHERE username = 'zhaoliu'; -- 删符合条件的行
DELETE FROM users; -- 删全部行(逐行删,可回滚)
TRUNCATE TABLE users; -- 清空整表,更快,但多数情况下不能回滚
方式速度能否回滚备注
DELETE ... WHERE慢事务内可回滚日常删数据用它
DELETE 无条件慢事务内可回滚逐行删除,产生大量日志
TRUNCATE快PostgreSQL 事务内可回滚,MySQL 不可清空测试表用
DROP TABLE快不可连表结构一起删

软删除也是常见做法:给表加 deleted_at 字段,删除时只更新这个字段,查询时过滤掉。数据可恢复、能审计,代价是所有查询都要记得带条件。

聚合统计:GROUP BY 入门#

SELECT COUNT(*) FROM users; -- 总行数
SELECT COUNT(*) FROM users WHERE age >= 30; -- 条件计数
SELECT AVG(balance), MAX(balance), MIN(balance) FROM users; -- 平均/最大/最小
SELECT age, COUNT(*) AS cnt FROM users GROUP BY age ORDER BY cnt DESC; -- 按年龄分组数人
SELECT age, COUNT(*) AS cnt FROM users GROUP BY age HAVING COUNT(*) >= 2; -- 对分组结果再过滤

记住分工:WHERE 在分组前过滤行,HAVING 在分组后过滤组,两者别用反。

关联查询:JOIN 入门#

再建一张订单表练关联:

CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
amount NUMERIC(10, 2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 查每个订单对应的用户名
SELECT o.id, u.username, o.amount
FROM orders o
JOIN users u ON o.user_id = u.id
ORDER BY o.id;
-- 查每个用户的订单总额,没下过单的用户也要出现(LEFT JOIN,结果为 NULL)
SELECT u.username, COALESCE(SUM(o.amount), 0) AS total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.username;

JOIN(内连接)只返回两边都匹配的行,LEFT JOIN 保左表全部行、右表没匹配的补 NULL——报表统计里后者用得更多。索引和 JOIN 的性能关系,在 《PostgreSQL 索引与查询优化入门》 和 《MySQL 索引与慢查询优化》 里展开。

常见问题#

Q:执行 SQL 报语法错误,不知道错在哪? A:先把语句拆开逐段看:关键字拼写、逗号、引号配对是最常见三处。psql 里可以用 \e 把上一条语句调回编辑器改,MySQL 客户端里按上箭头调出历史命令修改。

Q:SELECT * FROM users WHERE age = '28' 年龄是字符串也能查? A:多数数据库会做隐式类型转换,能查出结果,但会让索引失效。比较时类型保持一致,数字就写数字。

Q:中文存进去变问号或乱码? A:基本是字符集问题。MySQL 确保库、表、连接都是 utf8mb4(上一篇的 Compose 已钉死);PostgreSQL 建库时用 UTF8 编码,一般不出这问题。

Q:自增 id 删除一行后会复用吗? A:不会。PostgreSQL 的序列和 MySQL 的自增值都是只进不退,id 出现空洞完全正常,不要拿 id 连续性做业务逻辑。

小结#

这一篇的骨架就五件事:CREATE TABLE 定结构、INSERT 进数据、SELECT ... WHERE ... ORDER BY ... LIMIT 取数据、UPDATE / DELETE 永远带 WHERE、聚合靠 GROUP BY、多表靠 JOIN。把这套在练功房里敲熟,后面学哪个数据库的专属功能都有底。下一篇进入 PostgreSQL 正题,从安装与初始配置开始。

SQL 基础速成:建表、增删改查与常用语法
https://www.anexns.cloudns.org/posts/sql-basics-crud/
作者
Anexns
发布于
2026-11-15
许可协议
CC BY-NC-SA 4.0