PostgreSQL 16 插入、更新与删除数据 —语法详解与实战案例

一、插入数据（INSERT）

1.1 为表的所有字段插入数据

语法格式：

INSERT INTO table_name 
VALUES (value1, value2, ..., valueN);

✅ 说明：必须按表结构定义的字段顺序提供所有字段的值，且数量必须完全匹配。

案例代码：

-- 创建示例表 students
CREATE TABLE students (
    id SERIAL PRIMARY KEY,         -- 自增主键
    name VARCHAR(50) NOT NULL,     -- 姓名，非空
    age INT CHECK (age >= 0),      -- 年龄，需 >= 0
    grade VARCHAR(10),             -- 年级
    enrollment_date DATE DEFAULT CURRENT_DATE  -- 入学日期，默认当前日期
);

-- 插入一条完整记录（按字段顺序：id, name, age, grade, enrollment_date）
-- 注意：id 是 SERIAL 类型，可传 NULL 或 DEFAULT，系统自动分配
INSERT INTO students 
VALUES (DEFAULT, '张三', 18, '高一', '2025-09-01');

-- 插入另一条记录，enrollment_date 使用默认值
INSERT INTO students 
VALUES (DEFAULT, '李四', 17, '高二', DEFAULT);

-- 查询验证
SELECT * FROM students;

📌 注释：

  • SERIAL 是 PostgreSQL 的自增整型，等价于 INTEGER + SEQUENCE。
  • DEFAULT 关键字用于使用字段默认值。
  • 若字段有默认值或允许 NULL，可省略值，但此处为“所有字段”，必须显式提供（可用 DEFAULT 或 NULL）。

1.2 为表的指定字段插入数据

语法格式：

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

✅ 说明：只插入指定字段，未指定字段若允许 NULL 或有 DEFAULT，则自动填充；否则报错。

案例代码：

-- 只插入 name 和 age，其他字段使用默认值或 NULL（若允许）
INSERT INTO students (name, age) 
VALUES ('王五', 16);

-- 插入 name, age, grade，enrollment_date 使用默认值
INSERT INTO students (name, age, grade) 
VALUES ('赵六', 19, '高三');

-- 插入时指定字段顺序可与表结构不同，但值顺序必须对应
INSERT INTO students (grade, name, age) 
VALUES ('高一', '孙七', 15);

-- 查询验证
SELECT * FROM students;

📌 注释：

  • 字段列表顺序可任意，但 VALUES 中的值顺序必须与之对应。
  • 未指定字段：
    • 若有 DEFAULT → 使用默认值
    • 若允许 NULL → 填充 NULL
    • 否则 → 报错（如 NOT NULL 且无默认值）

1.3 同时插入多条记录

语法格式：

INSERT INTO table_name [(column_list)]
VALUES 
    (value_set1),
    (value_set2),
    ...,
    (value_setN);

✅ 说明：一次 INSERT 可插入多行，提高效率，减少网络往返。

案例代码：

-- 一次性插入三条记录，指定字段
INSERT INTO students (name, age, grade) 
VALUES 
    ('周八', 16, '高一'),
    ('吴九', 17, '高二'),
    ('郑十', 18, '高三');

-- 不指定字段，插入完整记录（注意顺序和数量）
INSERT INTO students 
VALUES 
    (DEFAULT, '钱十一', 15, '高一', '2025-09-05'),
    (DEFAULT, '冯十二', 16, '高一', DEFAULT);

-- 查询验证
SELECT * FROM students;

📌 注释：

  • 多行 VALUES 用逗号分隔，每行用括号包裹。
  • 性能优于多次单条 INSERT。
  • 若某行违反约束（如唯一、检查），整条语句失败（除非使用 ON CONFLICT）。

1.4 将查询结果插入到表中（INSERT INTO … SELECT）

语法格式：

INSERT INTO target_table [(column_list)]
SELECT column1, column2, ..., columnN
FROM source_table
[WHERE condition];

✅ 说明：从其他表或查询结果中选择数据插入到目标表，字段类型和数量需兼容。

案例代码：

-- 创建另一个表：graduated_students（已毕业学生）
CREATE TABLE graduated_students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    graduation_year INT,
    final_grade VARCHAR(10)
);

-- 将 students 表中 grade 为 '高三' 的学生插入到 graduated_students
INSERT INTO graduated_students (name, graduation_year, final_grade)
SELECT name, 2025, grade
FROM students
WHERE grade = '高三';

-- 查询验证
SELECT * FROM graduated_students;

-- 更复杂示例：插入时进行数据转换
INSERT INTO graduated_students (name, graduation_year, final_grade)
SELECT 
    UPPER(name) || '_GRAD',  -- 转大写并加后缀
    EXTRACT(YEAR FROM enrollment_date) + 3,  -- 入学年份+3作为毕业年份
    grade
FROM students
WHERE age >= 18;

-- 查询验证
SELECT * FROM graduated_students;

📌 注释：

  • SELECT 的列数、顺序、类型必须与 INSERT 的目标列匹配。
  • 可使用表达式、函数、常量进行数据转换。
  • 支持 JOIN、GROUP BY、子查询等复杂查询结构。

二、更新数据（UPDATE）

语法格式：

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
[WHERE condition];

⚠️ 警告：若省略 WHERE，将更新表中所有记录！

案例代码：

-- 将姓名为 '张三' 的学生年龄改为 19
UPDATE students
SET age = 19
WHERE name = '张三';

-- 更新多个字段：将 '李四' 的年级改为 '高三'，年龄改为 18
UPDATE students
SET grade = '高三', age = 18
WHERE name = '李四';

-- 使用表达式更新：所有学生年龄 +1
UPDATE students
SET age = age + 1
WHERE grade = '高一';

-- 使用子查询更新：将入学日期早于平均日期的学生年级设为 '留级'
UPDATE students
SET grade = '留级'
WHERE enrollment_date < (
    SELECT AVG(enrollment_date) FROM students
);

-- 查询验证
SELECT * FROM students;

📌 注释：

  • SET 后可跟多个字段，用逗号分隔。
  • 可使用当前字段值进行计算（如 age = age + 1）。
  • 支持子查询、函数、表达式。
  • 返回受影响的行数（可通过 RETURNING * 返回更新后数据）。

安全更新技巧（RETURNING）：

-- 更新并返回被修改的记录
UPDATE students
SET age = 20
WHERE name = '王五'
RETURNING *;  -- 返回更新后的整行

-- 只返回特定字段
UPDATE students
SET grade = '毕业生'
WHERE age >= 19
RETURNING id, name, grade;

三、删除数据（DELETE）

语法格式：

DELETE FROM table_name
[WHERE condition];

⚠️ 警告：若省略 WHERE，将删除表中所有记录！

案例代码：

-- 删除姓名为 '孙七' 的学生
DELETE FROM students
WHERE name = '孙七';

-- 删除年龄小于 16 的学生
DELETE FROM students
WHERE age < 16;

-- 删除年级为 '留级' 且入学日期早于 2025-01-01 的学生
DELETE FROM students
WHERE grade = '留级' AND enrollment_date < '2025-01-01';

-- 查询验证
SELECT * FROM students;

-- 删除并返回被删除的记录（PostgreSQL 特有）
DELETE FROM students
WHERE id = 5
RETURNING *;

-- 删除所有记录（慎用！）
-- DELETE FROM students;  -- 将清空整个表

📌 注释：

  • DELETE 不删除表结构，仅删除数据行。
  • 支持复杂 WHERE 条件（AND/OR/IN/子查询等）。
  • RETURNING 子句可返回被删除行，便于审计或日志记录。
  • 大量删除建议分批或使用 TRUNCATE（更快，但不可回滚，不触发触发器）。

四、综合案例 —— 学生管理系统数据操作

场景描述：

管理学生表，包括新生入学、信息修正、毕业转档、退学处理等。

-- 1. 批量插入新生
INSERT INTO students (name, age, grade) 
VALUES 
    ('新生A', 15, '高一'),
    ('新生B', 16, '高一'),
    ('新生C', 17, '高二');

-- 2. 修正录入错误：将 '新生A' 改名为 '张新生'
UPDATE students
SET name = '张新生'
WHERE name = '新生A';

-- 3. 年级升级：所有 '高二' 学生升为 '高三'
UPDATE students
SET grade = '高三'
WHERE grade = '高二';

-- 4. 毕业处理：将 '高三' 学生转移到 graduated_students 表
INSERT INTO graduated_students (name, graduation_year, final_grade)
SELECT name, 2025, grade
FROM students
WHERE grade = '高三';

-- 5. 从原表删除已毕业学生
DELETE FROM students
WHERE grade = '高三'
RETURNING id, name AS "已毕业学生";

-- 6. 清理无效数据：删除年龄超过 30 的异常记录（假设是录入错误）
DELETE FROM students
WHERE age > 30;

-- 7. 最终状态查询
SELECT '在校生' AS status, name, age, grade FROM students
UNION ALL
SELECT '毕业生' AS status, name, graduation_year AS age, final_grade AS grade 
FROM graduated_students
ORDER BY status, name;

五、常见问题及解答

疑问1：插入记录时可以不指定字段名称吗？

✅ 可以，但有条件：

  • 必须为所有字段提供值，且顺序必须与表结构定义完全一致。
  • 若某字段有默认值或允许 NULL，仍需显式提供 DEFAULT 或 NULL。
  • 若表结构变更（如新增字段），不指定字段名的 INSERT 可能失败。

推荐做法：

始终显式指定字段名，提高代码可读性、可维护性，避免表结构变更导致的错误。

-- ❌ 不推荐（脆弱，易出错）
INSERT INTO students VALUES (DEFAULT, '小明', 16, '高一', DEFAULT);

-- ✅ 推荐（健壮，清晰）
INSERT INTO students (name, age, grade) VALUES ('小明', 16, '高一');

疑问2：更新或者删除表时必须指定WHERE子句吗？

✅ 语法上不是必须，但强烈建议指定！

  • 不指定 WHERE：UPDATE 会更新全表，DELETE 会删除全表。
  • 生产环境中，误操作可能导致灾难性数据丢失。
  • PostgreSQL 默认不阻止无 WHERE 的操作（除非配置触发器或策略）。

安全建议：

  1. 开发/测试环境：可先用 SELECT 验证 WHERE 条件是否正确。
  2. 生产环境：务必使用事务 + 备份。
  3. 使用 RETURNING 预览影响。
  4. 配置权限：限制普通用户执行无 WHERE 的 DML。
-- 危险操作示例（请勿在生产环境直接运行）
-- UPDATE students SET age = 0;  -- 所有学生年龄归零！
-- DELETE FROM students;         -- 清空整个表！

-- 安全做法：
BEGIN;  -- 开启事务

UPDATE students SET age = 20 WHERE id = 100 RETURNING *;

-- 检查结果无误后再提交
-- COMMIT;

-- 若有误，回滚
ROLLBACK;

✅ 总结要点

操作语法要点安全建议
INSERT可指定或不指定字段；支持多行、SELECT 插入显式指定字段；检查约束
UPDATESET 多字段；支持表达式/子查询；WHERE 必选（逻辑上）先 SELECT 验证条件；用 RETURNING；事务包裹
DELETEWHERE 控制范围；RETURNING 可审计绝不裸奔无 WHERE；事务 + 备份；分批删除大数据

📘 最佳实践口诀：
“插数要明列，更删必带条；
事务保平安，返回好审计；
生产如战场，备份是王道。”

本章内容覆盖 PostgreSQL 16 中数据操作的核心语法，结合实战案例与安全建议，助你高效、安全地管理数据。

# postgresql # 学习 # 数据库
Logo
开源鸿蒙跨平台开发者社区

开源鸿蒙跨平台开发社区汇聚开发者与厂商，共建“一次开发，多端部署”的开源生态，致力于降低跨端开发门槛，推动万物智联创新。

更多推荐

cover

鸿蒙平台 Subversion 客户端适配实战：基于 HTTP_WebDAV 协议的轻量级 SVN 图形界面

avatar 开源鸿蒙跨平台开发者社区
cover

鸿蒙平台 InterModeller 概念建模工具适配实战：基于 Electron 壳方案的思维导图风格分类树开发

avatar 开源鸿蒙跨平台开发者社区

Flutter 鸿蒙化实战：flutter_vap_plus 适配 OpenHarmony，VAP 动效视频

随着鸿蒙生态的快速发展，越来越多的 Flutter 应用需要适配 OpenHarmony 平台。但生态早期，大量常用三方库只有 Android / iOS 实现，鸿蒙侧只能自己造轮子。为了解决这个问题，对一批高频使用的 Flutter 三方库做了 OpenHarmony 平台适配，统一托管在 GitCode 的下，全部通过 TAG 版本隔离、配套门禁编译验证，开箱即用。本篇是系列文章之一，主角是—

avatar 开源鸿蒙跨平台开发者社区