这一篇练什么
不管你最后用 PostgreSQL 还是 MySQL,日常打交道的都是同一套 SQL 语言——先选哪个库的问题已经在 《数据库怎么选》 里解决了,这一篇只管把手练熟。我们用上一篇 《用 Docker 快速搭建三大数据库》 里建好的环境,围绕一张最典型的用户表,把建表、增、删、改、查、排序、聚合、关联全部走一遍。
文中 SQL 以 PostgreSQL 为主演示,MySQL 差异会随文标注;两边 95% 的基础语法是通用的。先连进 PostgreSQL:
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.amountFROM orders oJOIN users u ON o.user_id = u.idORDER BY o.id;
-- 查每个用户的订单总额,没下过单的用户也要出现(LEFT JOIN,结果为 NULL)SELECT u.username, COALESCE(SUM(o.amount), 0) AS totalFROM users uLEFT JOIN orders o ON o.user_id = u.idGROUP 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 正题,从安装与初始配置开始。