Docker PostgreSQL 操作指南与 SQL 语法速查

技术随笔

Docker PostgreSQL 操作指南与 SQL 语法速查

一、Docker 中操作 PostgreSQL

1.1 容器管理

# 查看所有运行中的容器
docker ps

# 查看指定容器
docker ps --format "{{.Names}}" | findstr postgres

# 查看容器日志
docker logs blog-postgres

# 进入容器内部
docker exec -it blog-postgres bash

# 启动/停止容器
docker start blog-postgres
docker stop blog-postgres

# 删除容器（数据卷保留，数据不丢）
docker rm blog-postgres

1.2 连接数据库

# 方式一：直接执行 SQL（推荐）
docker exec blog-postgres psql -U postgres -d blog -c "SQL 语句"

# 方式二：进入交互式命令行
docker exec -it blog-postgres psql -U postgres -d blog

# 连接后看到如下提示符
blog=#

1.3 常用 psql 命令

在交互式命令行（blog=#）中输入：

-- 查看所有数据库
\l

-- 切换到指定数据库
\c blog

-- 查看所有表
\dt

-- 查看表结构（字段名、类型、约束）
\d user_models

-- 查看表的完整信息（包括索引、触发器）
\d+ user_models

-- 查看所有索引
\di

-- 查看当前用户
\du

-- 查看帮助
\h

-- 查看某个命令的帮助
\h SELECT

-- 设置输出格式（对齐/无对齐）
\x on    -- 扩展显示（字段竖排）
\x off   -- 标准显示（表格横排）

-- 退出
\q

1.4 实用示例

# 查看用户表所有数据
docker exec blog-postgres psql -U postgres -d blog -c "SELECT * FROM user_models;"

# 查看用户表（竖排显示，字段多时更方便）
docker exec blog-postgres psql -U postgres -d blog -c "\x on" -c "SELECT * FROM user_models;"

# 统计表数据量
docker exec blog-postgres psql -U postgres -d blog -c "
SELECT
    relname AS table_name,
    n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
"

二、数据库与表管理

2.1 数据库操作

-- 创建数据库
CREATE DATABASE blog;

-- 删除数据库（不可恢复！）
DROP DATABASE blog;

-- 重命名数据库
ALTER DATABASE blog RENAME TO blog_v2;

-- 查看当前数据库
SELECT current_database();

-- 查看数据库大小
SELECT pg_database_size('blog');
SELECT pg_size_pretty(pg_database_size('blog'));

2.2 表操作

-- 创建表
CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    body TEXT,
    author_id BIGINT REFERENCES user_models(id),
    category VARCHAR(100),
    tags TEXT[],
    published BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 删除表
DROP TABLE articles;

-- 清空表数据（保留表结构）
TRUNCATE TABLE articles;

-- 重命名表
ALTER TABLE articles RENAME TO posts;

-- 查看表结构
\d articles

2.3 字段操作

-- 添加字段
ALTER TABLE user_models ADD COLUMN phone VARCHAR(20);

-- 删除字段
ALTER TABLE user_models DROP COLUMN phone;

-- 修改字段类型
ALTER TABLE user_models ALTER COLUMN phone TYPE VARCHAR(30);

-- 修改字段名
ALTER TABLE user_models RENAME COLUMN phone TO mobile;

-- 设置默认值
ALTER TABLE user_models ALTER COLUMN role SET DEFAULT 1;

-- 删除默认值
ALTER TABLE user_models ALTER COLUMN role DROP DEFAULT;

-- 设置非空约束
ALTER TABLE user_models ALTER COLUMN username SET NOT NULL;

-- 取消非空约束
ALTER TABLE user_models ALTER COLUMN username DROP NOT NULL;

2.4 索引操作

-- 创建索引
CREATE INDEX idx_articles_author ON articles(author_id);
CREATE INDEX idx_articles_published ON articles(published);
CREATE UNIQUE INDEX idx_articles_slug ON articles(slug);

-- 复合索引（多字段）
CREATE INDEX idx_articles_category_published ON articles(category, published);

-- 删除索引
DROP INDEX idx_articles_author;

-- 查看索引
\d articles
-- 或
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'articles';

三、数据操作（CRUD）

3.1 插入数据

-- 插入单条
INSERT INTO user_models (username, password, nickname, role, created_at)
VALUES ('admin', '哈希值', '管理员', 1, NOW());

-- 插入多条
INSERT INTO user_models (username, password, nickname, role, created_at)
VALUES
    ('user1', 'hash1', '用户一', 2, NOW()),
    ('user2', 'hash2', '用户二', 2, NOW());

-- 插入并返回整行
INSERT INTO user_models (username, password, nickname, role)
VALUES ('test', 'hash', '测试', 2)
RETURNING *;

-- 插入并只返回 ID
INSERT INTO user_models (username, password, nickname)
VALUES ('test', 'hash', '测试')
RETURNING id;

-- 从查询结果插入
INSERT INTO user_models (username, password, nickname, role)
SELECT username, password, nickname, 2 FROM old_users WHERE active = true;

3.2 查询数据

-- 查询所有列
SELECT * FROM user_models;

-- 查询指定列
SELECT id, username, nickname, role FROM user_models;

-- 条件查询
SELECT * FROM user_models WHERE role = 1;
SELECT * FROM articles WHERE published = true;

-- 多条件
SELECT * FROM articles
WHERE category = '系列教程'
  AND published = true
  AND created_at >= '2024-01-01';

-- 模糊查询
SELECT * FROM articles WHERE title LIKE '%Dioxus%';
SELECT * FROM articles WHERE title ILIKE '%dioxus%';  -- 不区分大小写

-- 排序
SELECT * FROM articles ORDER BY created_at DESC;       -- 最新在前
SELECT * FROM articles ORDER BY created_at ASC;        -- 最早在前
SELECT * FROM articles ORDER BY category, created_at DESC;

-- 分页
SELECT * FROM articles
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;     -- 第1页，每页10条

SELECT * FROM articles
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;    -- 第2页

-- 聚合统计
SELECT COUNT(*) FROM articles;
SELECT category, COUNT(*) FROM articles GROUP BY category;
SELECT category, COUNT(*) as cnt FROM articles
GROUP BY category ORDER BY cnt DESC;

-- 去重
SELECT DISTINCT category FROM articles;
SELECT COUNT(DISTINCT category) FROM articles;

-- 限制返回条数
SELECT * FROM articles LIMIT 5;

3.3 更新数据

-- 更新单个字段
UPDATE user_models SET nickname = '新昵称' WHERE id = 1;

-- 更新多个字段
UPDATE user_models
SET nickname = '新昵称',
    email = 'new@email.com',
    updated_at = NOW()
WHERE id = 1;

-- 更新并返回更新后的数据
UPDATE user_models
SET nickname = '新昵称'
WHERE id = 1
RETURNING *;

-- 条件更新
UPDATE articles
SET published = true
WHERE category = '系列教程'
  AND created_at < '2024-06-01';

-- 使用表达式更新
UPDATE articles
SET view_count = view_count + 1
WHERE id = 100;

3.4 删除数据

-- 删除指定行
DELETE FROM user_models WHERE id = 1;

-- 条件删除
DELETE FROM articles WHERE published = false AND created_at < '2023-01-01';

-- 删除并返回删除的数据
DELETE FROM user_models WHERE id = 1 RETURNING *;

-- 清空表（比 DELETE 快，但无法回滚）
TRUNCATE TABLE articles;

-- 清空表并重置自增 ID
TRUNCATE TABLE articles RESTART IDENTITY;

四、用户管理

4.1 创建管理员账号

# 先确认表结构
docker exec blog-postgres psql -U postgres -d blog -c "\d user_models"

-- role 字段说明（根据项目 entity.rs 中的定义）
-- 0 = 普通用户
-- 1 = 管理员
-- 其他值根据项目定义

-- 插入管理员账号
-- 注意：password 字段需要 bcrypt 哈希值
INSERT INTO user_models (username, password, nickname, role, created_at)
VALUES ('admin', '这里放bcrypt哈希', '管理员', 1, NOW());

-- 查看已创建的用户
SELECT id, username, nickname, role, created_at FROM user_models;

4.2 生成 bcrypt 密码哈希

方法一：使用在线工具（推荐，方便快捷）

访问 https://bcrypt.online/
输入密码，生成哈希

方法二：使用 Python（如果安装了 bcrypt 库）

import bcrypt
password = b"你的密码"
hashed = bcrypt.hashpw(password, bcrypt.gensalt(rounds=12))
print(hashed.decode())

方法三：使用 Node.js

const bcrypt = require('bcryptjs');
const hash = bcrypt.hashSync('你的密码', 12);
console.log(hash);

4.3 修改密码

-- 直接更新密码字段（需要 bcrypt 哈希值）
UPDATE user_models
SET password = '新的bcrypt哈希'
WHERE username = 'admin';

-- 修改用户角色
UPDATE user_models
SET role = 1
WHERE username = 'admin';

五、实用查询

5.1 项目相关查询

-- 查看所有文章
SELECT id, title, slug, category, published, created_at FROM articles
ORDER BY created_at DESC;

-- 查看某个分类的文章数
SELECT category, COUNT(*) as article_count
FROM articles GROUP BY category;

-- 查看文章标签统计
SELECT unnest(tags) as tag, COUNT(*) as cnt
FROM articles
GROUP BY tag
ORDER BY cnt DESC
LIMIT 20;

-- 查看最近评论
SELECT c.*, a.title
FROM comments c
JOIN articles a ON c.article_id = a.id
ORDER BY c.created_at DESC
LIMIT 10;

-- 查看用户登录记录
SELECT * FROM user_models
ORDER BY updated_at DESC
LIMIT 10;

5.2 数据库维护

-- 查看数据库大小
SELECT pg_size_pretty(pg_database_size('blog'));

-- 查看表大小
SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- 查看当前连接数
SELECT COUNT(*) FROM pg_stat_activity WHERE datname = 'blog';

-- 查看慢查询（运行超过 5 秒的查询）
SELECT
    pid, now() - pg_stat_activity.query_start AS duration,
    query, state
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - pg_stat_activity.query_start > interval '5 seconds'
ORDER BY duration DESC;

-- 清理和分析（更新统计信息）
ANALYZE;

-- 清理死元组（类似碎片整理）
VACUUM;
VACUUM FULL;  -- 更彻底，但会锁表

六、常见错误与解决

| 错误信息 | 原因 | 解决 | |---------|------|------| | database "xxx" does not exist | 数据库不存在 | CREATE DATABASE xxx; | | relation "xxx" does not exist | 表不存在 | 检查表名，先运行数据库迁移 | | column "xxx" does not exist | 字段不存在 | 检查字段名，大小写敏感 | | violates foreign key constraint | 外键冲突 | 先插入关联表的数据 | | duplicate key value violates unique constraint | 唯一键冲突 | 检查是否已存在相同数据 | | invalid input syntax for type smallint | 类型不匹配 | 检查字段类型，'admin' 不能存入 smallint | | password authentication failed | 密码错误 | 检查连接字符串的用户名密码 | | Connection refused | 数据库未启动 | docker start blog-postgres |

七、快速参考

# 最常用命令速查

# 1. 查看所有用户
docker exec blog-postgres psql -U postgres -d blog -c "SELECT * FROM user_models;"

# 2. 创建管理员（先获取 bcrypt 哈希）
docker exec blog-postgres psql -U postgres -d blog -c "
INSERT INTO user_models (username, password, nickname, role, created_at)
VALUES ('admin', '你的bcrypt哈希', '管理员', 1, NOW());"

# 3. 查看所有文章
docker exec blog-postgres psql -U postgres -d blog -c "SELECT id, title, slug, published FROM articles;"

# 4. 进入交互式命令行
docker exec -it blog-postgres psql -U postgres -d blog

# 5. 退出交互式
\q

PostgreSQLDockerSQL数据库运维

目录

Docker PostgreSQL 操作指南与 SQL 语法速查

Docker PostgreSQL 操作指南与 SQL 语法速查

一、Docker 中操作 PostgreSQL

1.1 容器管理

1.2 连接数据库

1.3 常用 psql 命令

1.4 实用示例

二、数据库与表管理

2.1 数据库操作

2.2 表操作

2.3 字段操作

2.4 索引操作

三、数据操作（CRUD）

3.1 插入数据

3.2 查询数据

3.3 更新数据

3.4 删除数据

四、用户管理

4.1 创建管理员账号

4.2 生成 bcrypt 密码哈希

4.3 修改密码

五、实用查询

5.1 项目相关查询

5.2 数据库维护

六、常见错误与解决

七、快速参考