【PostgreSQL数据分析实战：从数据清洗到可视化全流程】1.4 数据库与表的基本操作（DDL/DML语句）

👉 点击关注不迷路 👉 点击关注不迷路 👉 点击关注不迷路

文章大纲

• 1.4 数据库与表的基本操作（DDL/DML语句）
• • 1.4.1 数据库生命周期管理（DDL核心）
  • • 1.4.1.1 创建数据库（CREATE DATABASE）
    • • 最佳实践
    • 1.4.1.2 查看数据库信息
    • 1.4.1.3 修改数据库（ALTER DATABASE）
    • 1.4.1.4 删除数据库（DROP DATABASE）
  • 1.4.2 表结构定义与约束（DDL核心）
  • • 1.4.2.1 数据类型速查表
    • 1.4.2.2 创建表（CREATE TABLE）
    • • 约束类型对比
    • 1.4.2.3 修改表结构（ALTER TABLE）
    • 1.4.2.4 删除表（DROP TABLE）
  • 1.4.3 数据操作语言（DML核心）
  • • 1.4.3.1 插入数据（INSERT）
    • 1.4.3.2 查询数据（SELECT）
    • • 基础语法结构
      • 常用函数
    • 1.4.3.3 更新数据（UPDATE）
    • 1.4.3.4 删除数据（DELETE）
  • 1.4.4 事务管理与锁定机制
  • • 1.4.4.1 事务控制语句
    • • ACID特性保障
    • 1.4.4.2 锁机制
  • 1.4.5 实战案例：电商订单表设计
  • • 1.4.5.1 表结构定义
    • 1.4.5.2 高频操作示例
    • • 订单查询（带索引优化）
      • 批量订单导入（COPY命令）
  • 1.4.6 最佳实践与规范
  • • 1.4.6.1 `命名规范 !!!`
    • 1.4.6.2 性能优化建议
    • 1.4.6.3 数据完整性保障
  • 1.4.7 常见错误与解决方案
  • • 1.4.7.1 约束冲突处理
    • 1.4.7.2 大表操作技巧
  • 1.4.8 总结：构建健壮的数据模型

1.4 数据库与表的基本操作（DDL/DML语句）

1.4.1 数据库生命周期管理（DDL核心）

1.4.1.1 创建数据库（CREATE DATABASE）

— 基础语法
— 基础语法
CREATE DATABASE analytics_db
WITH
OWNER = postgres — 所有者
ENCODING = 'UTF8' — 字符编码
— LC_COLLATE = 'en_US.utf8' — 排序规则
— LC_CTYPE = 'en_US.utf8' — 字符分类
TABLESPACE = pg_default — 表空间
CONNECTION LIMIT = –1 — 最大连接数（-1无限制）
TEMPLATE = template0; — 模板数据库

最佳实践

• 使用TEMPLATE template1创建支持中文的数据库
• 通过pg_tablespace查看可用表空间
• 生产环境建议单独创建业务表空间：CREATE TABLESPACE data_ts LOCATION '/data/pg_tablespace';

1.4.1.2 查看数据库信息

1.4.1.3 修改数据库（ALTER DATABASE）

— 修改所有者
ALTER DATABASE analytics_db OWNER TO data_team;

— 限制连接数
ALTER DATABASE analytics_db CONNECTION LIMIT 100;

— 更改参数配置（会话级生效）
ALTER DATABASE analytics_db SET work_mem = '32MB';

1.4.1.4 删除数据库（DROP DATABASE）

— 强制删除（终止所有连接）
DROP DATABASE IF EXISTS analytics_db WITH (FORCE);

⚠️ 注意：删除前请通过SELECT * FROM pg_stat_activity WHERE datname='analytics_db';确认无活动连接

1.4.2 表结构定义与约束（DDL核心）

1.4.2.1 数据类型速查表

1.4.2.2 创建表（CREATE TABLE）

CREATE TABLE departments (
dept_id INTEGER PRIMARY KEY,
dept_name VARCHAR(50)
);
—
员工信息表（含约束）
CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY, — 自增主键
emp_name VARCHAR(50) NOT NULL, — 非空约束
email VARCHAR(100) UNIQUE, — 唯一约束
hire_date DATE DEFAULT CURRENT_DATE, — 默认值约束
salary NUMERIC(10,2) CHECK (salary > 0), — 检查约束
department_id INTEGER REFERENCES departments(dept_id) — 外键约束
);

约束类型对比

1.4.2.3 修改表结构（ALTER TABLE）

— 添加字段（带默认值）
ALTER TABLE employees ADD COLUMN phone VARCHAR(20) DEFAULT '未提供';

— 修改数据类型（需重建表）
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12,2);

— 删除字段（生产环境慎用）
ALTER TABLE employees DROP COLUMN fax;

— 添加外键（延迟约束检查）
ALTER TABLE employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (department_id) REFERENCES departments(dept_id)
DEFERRABLE INITIALLY DEFERRED;

1.4.2.4 删除表（DROP TABLE）

— 级联删除依赖对象
DROP TABLE IF EXISTS employees CASCADE;

— 仅删除表结构，保留数据（PostgreSQL 12+）
TRUNCATE TABLE employees;

💡💡💡 提示：TRUNCATE比DELETE FROM 效率高10-100倍，适合清空大表 💡💡💡 提示：TRUNCATE比DELETE FROM 效率高10-100倍，适合清空大表 💡💡💡 提示：TRUNCATE比DELETE FROM 效率高10-100倍，适合清空大表

1.4.3 数据操作语言（DML核心）

1.4.3.1 插入数据（INSERT）

— 单条插入
INSERT INTO employees (emp_name, email, salary, department_id)
VALUES ('张三', 'zhangsan@example.com', 15000, 101);

— 批量插入（性能提升30%+）
INSERT INTO employees (emp_name, email, salary, department_id)
VALUES
('李四', 'lisi@example.com', 16000, 102),
('王五', 'wangwu@example.com', 17000, 101);

— 插入并返回自增ID
INSERT INTO employees (emp_name, email, salary, department_id)
VALUES ('赵六', 'zhaoliu@example.com', 18000, 103)
RETURNING emp_id; — 返回新生成的emp_id

1.4.3.2 查询数据（SELECT）

基础语法结构

SELECT
column1, column2,
CASE WHEN salary > 15000 THEN '高薪' ELSE '普通' END AS salary_level — 条件表达式
FROM employees
WHERE hire_date >= '2023-01-01' — 过滤条件
AND department_id IN (101, 102)
GROUP BY department_id — 分组聚合
HAVING AVG(salary) > 16000 — 分组后过滤
ORDER BY salary DESC — 排序
LIMIT 10 OFFSET 20; — 分页（第3页，每页10条）

常用函数

• 在 PostgreSQL 中，AGE(timestamp) 是一个用于计算时间间隔的函数，主要用于获取 某个时间点与当前时间的间隔，或 两个时间点之间的间隔。以下是关于该函数的详细解析：
  • 基础语法

  AGE(timestamp) — 计算 timestamp 到当前时间的间隔（等效于 AGE(now(), timestamp)）

  AGE(end_timestamp, start_timestamp) — 计算两个时间点之间的间隔（end – start）

  • 参数说明
    参数类型描述示例值

    timestamp	单个时间戳（可带时区 timestamptz）	'2020-01-01 08:00:00'
    end_timestamp	结束时间戳（必须晚于或等于开始时间）	order_completed_at
    start_timestamp	开始时间戳	order_created_at

  • 返回值类型 返回 interval 类型，格式为 年-月-日 时:分:秒，例如： 3 years 2 mons 10 days 03:05:15（表示 3 年 2 个月 10 天 3 小时 5 分 15 秒）。

  • 场景 1：计算单个时间点与当前时间的间隔（最常用）

  • 场景 2：计算两个时间点的间隔（如订单处理耗时）

  • 场景 3：带时区的时间戳处理（自动转换为 UTC 计算）

  • 注意事项与最佳实践

  • 参数顺序的重要性 AGE(end, start) 必须满足 end >= start，否则会返回负数间隔（如 -1 day 00:00:00）。
  • 时区处理 若时间戳带时区（timestamptz），AGE 会自动将其转换为 UTC 时间进行计算，避免时区偏移导致的误差。
  • 性能优化 对大表使用 AGE 时，若涉及索引列（如 hire_date），确保该列有索引以加速查询：

  CREATE INDEX idx_hire_date ON employees(hire_date);

1.4.3.3 更新数据（UPDATE）

— 单表更新（设置工资增长5%）
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 101;

— 关联更新（基于部门表）
UPDATE employees e
SET email
= CONCAT(e.emp_name, '@', d.dept_domain)
FROM departments d
WHERE e.department_id = d.dept_id;

1.4.3.4 删除数据（DELETE）

— 单表删除（离职员工）
— 单表删除（离职员工）
DELETE FROM employees
WHERE hire_date < '2020-01-01'
AND NOT EXISTS (SELECT 1 FROM payroll WHERE emp_id = employees.emp_id);

— 跨表级联删除（需外键设置级联）
DELETE FROM departments
WHERE dept_id = 100; — 自动删除关联的employees记录

1.4.4 事务管理与锁定机制

1.4.4.1 事务控制语句

— 显式事务示例（账户转账）
BEGIN TRANSACTION; — 开始事务
UPDATE accounts SET balance = balance – 1000 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE user_id = 2;
COMMIT; — 提交事务（永久生效）

— 带保存点的事务
BEGIN;
SAVEPOINT sp1; — 设置保存点
UPDATE orders SET status = 'processing' WHERE order_id = 101;
ROLLBACK TO sp1; — 回滚到保存点
COMMIT;

ACID特性保障

• 原子性：事务内操作要么全成功，要么全回滚
• 一致性：事务前后数据满足约束条件
• 隔离性：通过SET TRANSACTION ISOLATION LEVEL控制（默认READ COMMITTED）
• 持久性：提交后数据永久写入磁盘

1.4.4.2 锁机制

1.4.5 实战案例：电商订单表设计

1.4.5.1 表结构定义

CREATE TABLE orders (
order_id BIGINT PRIMARY KEY DEFAULT generate_ulid(), — 使用ULID作为主键（比UUID性能高50%）
user_id INTEGER NOT NULL REFERENCES users(user_id),
order_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
total_amount NUMERIC(12,2) CHECK (total_amount > 0),
status VARCHAR(20) CHECK (status IN ('待支付', '已支付', '已取消', '已完成')),
delivery_address JSONB, — 存储结构化地址数据
INDEX idx_user_id_status (user_id, status), — 组合索引优化查询
TABLESPACE order_ts — 使用独立表空间
);

1.4.5.2 高频操作示例

订单查询（带索引优化）

— 优化前：全表扫描（耗时120ms）
SELECT * FROM orders WHERE user_id = 1234 AND status = '已完成';

— 优化后：使用组合索引（耗时8ms）
CREATE INDEX idx_user_id_status ON orders(user_id, status);

批量订单导入（COPY命令）

— 比INSERT速度快10倍以上
COPY orders (user_id, order_time, total_amount, status, delivery_address)
FROM '/data/orders_202401.csv'
WITH (FORMAT CSV, HEADER, DELIMITER ',', QUOTE '"');

1.4.6 最佳实践与规范

1.4.6.1 命名规范 !!!

1.4.6.2 性能优化建议

• **避免使用SELECT ***：显式列出所需字段，减少数据传输
• 合理使用JOIN：优先在WHERE子句添加过滤条件，减少关联数据量
• 批量操作：使用INSERT … VALUES (…),(…)替代单条插入
• 事务控制：将高频小事务合并，减少日志写入次数

1.4.6.3 数据完整性保障

• 外键约束必须定义ON DELETE CASCADE或SET NULL行为
• 核心业务表启用TRIGGER进行数据校验
• 定期运行ANALYZE更新统计信息（建议每日凌晨执行）

1.4.7 常见错误与解决方案

1.4.7.1 约束冲突处理

1.4.7.2 大表操作技巧

— 在线重命名大表（PostgreSQL 11+）
ALTER TABLE large_table RENAME TO new_large_table;

— 分批次删除数据（避免锁表）
— 开始一个无限循环，用于分批次删除数据，避免一次性删除大量数据导致锁表和性能问题
WHILE TRUE LOOP
— 从 old_data 表中删除创建时间早于 '2023-01-01' 的数据，每次最多删除 10000 条记录
DELETE FROM old_data WHERE create_time < '2023-01-01' LIMIT 10000;
— 检查上一条 DELETE 语句是否删除了任何记录
— 如果没有删除任何记录（即 NOT FOUND 条件为真），则说明已经没有符合条件的数据需要删除
IF NOT FOUND THEN
— 当没有符合条件的数据可删除时，退出当前的循环
EXIT;
END IF;
— 提交当前事务，将已删除的数据永久保存到数据库中
— 分批次提交事务可以减少锁的持有时间，提高并发性能
COMMIT;
— 结束循环
END LOOP;

1.4.8 总结：构建健壮的数据模型

• 通过掌握DDL与DML的核心语法，我们能够：
  • 设计符合业务需求的数据表结构（合理使用约束与索引）
  • 高效操作数据（批量处理、事务控制、性能优化）
  • 保障数据完整性与一致性（ACID特性、外键约束）

该文章讲解了PostgreSQL的DDL/DML操作。 后续章节将深入讲解数据清洗技巧（使用CTE、窗口函数）、复杂查询优化（执行计划分析）以及与数据分析工具的集成方法。 建议在实践中遵循"先设计ER图，再编写建表语句"的流程，逐步积累数据库设计经验。

• CTE 即公共表表达式（Common Table Expressions），它是在 SQL 中定义的临时命名结果集，这些结果集只在当前查询的执行期间存在。
  • CTE 的基本语法如下：WITH cte_name (column1, column2, ...) AS (
    — CTE 的查询语句
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
    )
    — 主查询，使用 CTE
    SELECT *
    FROM cte_name;
  • 其中，WITH 关键字用于引入 CTE，cte_name 是 CTE 的名称，括号内可指定列名，AS 后面的括号中是 CTE 的查询语句，最后是使用该 CTE 的主查询。
  • 使用注意事项
    • CTE 的作用域： CTE 只在当前查询中有效，查询执行完毕后，CTE 占用的资源会被释放。
    • 性能问题：虽然 CTE 在某些情况下可以提高性能，但在处理大量数据或复杂递归时，可能会导致性能下降。需要根据具体情况进行性能测试和优化。
    • 递归终止条件：在使用递归 CTE 时，必须确保递归有终止条件，否则会导致无限递归，使数据库陷入死循环。