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

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

---

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 查看数据库信息

命令	说明
\l或SELECT * FROM pg_database;	列出所有数据库
SELECT datname, datsize FROM pg_database;	查看数据库名称及大小
\conninfo	当前连接的数据库信息

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 数据类型速查表

类型分类	常用数据类型	存储范围/特性	示例值
数值型	INT/BIGINT	4/8字节整数	100, -500
	DECIMAL(p,s)	高精度十进制数（p总位数，s小数位）	DECIMAL(10,2) → 1234.56
字符型	VARCHAR(n)	可变长字符串（n为最大长度）	‘数据分析’
	TEXT	无长度限制字符串	长文本内容
日期时间	DATE	年月日（YYYY-MM-DD）	‘2023-12-31’
	TIMESTAMP WITH TIME ZONE	带时区的时间戳	‘2024-01-01 10:00:00+08’
布尔型	BOOLEAN	真/假	TRUE/FALSE
二进制	BYTEA	二进制数据（图片/文件）	\xDEADBEEF
几何类型	POINT	二维坐标（x,y）	POINT(10, 20)

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) -- 外键约束
);

约束类型对比

约束类型	关键字	作用	性能影响
主键	PRIMARY KEY	唯一标识记录，自动创建索引	读优化，写轻微影响
唯一	UNIQUE	确保字段值唯一	类似主键，允许NULL值
非空	NOT NULL	禁止字段为空	无索引性能影响
检查	CHECK	自定义逻辑约束	每次写入时触发检查
外键	REFERENCES	建立表间关联	级联操作需额外开销

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条）

常用函数

函数分类	函数名	说明	示例
聚合函数	COUNT(*) / COUNT(col)	计数	COUNT(DISTINCT department_id)
	SUM/AVG/MAX/MIN	数值聚合	AVG(salary)
字符串函数	CONCAT(str1, str2)	字符串拼接	CONCAT(emp_name, ’ (', email, ‘)’)
	SUBSTRING(str FROM start FOR length)	子串提取	SUBSTRING(email FROM 1 FOR 5)
日期函数	AGE(timestamp)	计算时间间隔	AGE(hire_date)
	DATE_TRUNC(‘month’, date)	日期截断到月	DATE_TRUNC(‘quarter’, now())

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

    AGE(timestamp)                  -- 计算 timestamp 到当前时间的间隔（等效于 AGE(now(), timestamp)）
    
    AGE(end_timestamp, start_timestamp)  -- 计算两个时间点之间的间隔（end - start）
    

  • 2. 参数说明

       参数类型	描述	示例值
       timestamp	单个时间戳（可带时区 timestamptz）	'2020-01-01 08:00:00'
       end_timestamp	结束时间戳（必须晚于或等于开始时间）	order_completed_at
       start_timestamp	开始时间戳	order_created_at

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

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

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

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

  • 注意事项与最佳实践

  • 1. 参数顺序的重要性 AGE(end, start) 必须满足 end >= start，否则会返回负数间隔（如 -1 day 00:00:00）。
  • 2. 时区处理 若时间戳带时区（timestamptz），AGE 会自动将其转换为 UTC 时间进行计算，避免时区偏移导致的误差。
  • 3. 性能优化 对大表使用 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 锁机制

锁类型	模式	作用	示例场景
共享锁（S）	SELECT … FOR SHARE	阻止写操作，允许读并发	报表查询时防止数据修改
排他锁（X）	SELECT … FOR UPDATE	阻止所有并发操作	库存扣减时防止超卖
表级锁	LOCK TABLE …	锁定整张表（性能影响大）	批量数据初始化

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 命名规范 !!!

对象类型	命名规则	示例
数据库	业务模块_环境（小写）	analytics_dev
表	复数名词（小写下划线分隔）	employee_records
字段	小写下划线命名	hire_date
约束	表名_约束类型_字段名	employees_pk_emp_id

1.4.6.2 性能优化建议

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

1.4.6.3 数据完整性保障

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

1.4.7 常见错误与解决方案

1.4.7.1 约束冲突处理

错误信息	原因分析	解决方案
唯一约束冲突	插入重复数据	使用INSERT ... ON CONFLICT UPDATE
外键约束失败	父表无对应记录	先插入父表数据，或设置DEFERRABLE约束
检查约束违反	数据不符合自定义规则	添加数据清洗步骤，或修改CHECK条件

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的核心语法，我们能够：
  • 1. 设计符合业务需求的数据表结构（合理使用约束与索引）
  • 2. 高效操作数据（批量处理、事务控制、性能优化）
  • 3. 保障数据完整性与一致性（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 时，必须确保递归有终止条件，否则会导致无限递归，使数据库陷入死循环。