PostgreSQL CRUD 操作指南

PostgreSQL CRUD 操作指南

连接数据库

-- 连接到特定数据库
psql -U postgres -d xianxia-- 列出所有数据库
\l-- 切换数据库
\c xianxia-- 列出所有表
\dt-- 查看表结构
\d table_name

基本 CRUD 操作

CREATE（创建）

-- 创建新表
CREATE TABLE users (id SERIAL PRIMARY KEY,username VARCHAR(50) UNIQUE NOT NULL,email VARCHAR(100) UNIQUE NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);-- 插入单条数据
INSERT INTO users (username, email) 
VALUES ('admin', '[e-mail]');-- 插入多条数据
INSERT INTO users (username, email) 
VALUES ('user1', '[e-mail]'),('user2', '[e-mail]');

READ（查询）

-- 查询所有列
SELECT * FROM users;-- 查询特定列
SELECT username, email FROM users;-- 条件查询
SELECT * FROM users WHERE username = 'admin';-- 排序
SELECT * FROM users ORDER BY created_at DESC;-- 分页查询
SELECT * FROM users 
LIMIT 10 OFFSET 0;  -- 第一页，每页10条-- 模糊查询
SELECT * FROM users 
WHERE username LIKE '%admin%';-- 联表查询
SELECT u.username, p.title 
FROM users u 
JOIN posts p ON u.id = p.user_id;

UPDATE（更新）

-- 更新单个字段
UPDATE users 
SET email = '[e-mail]' 
WHERE username = 'admin';-- 更新多个字段
UPDATE users 
SET email = '[e-mail]',username = 'newadmin'
WHERE id = 1;-- 批量更新
UPDATE users 
SET status = 'inactive' 
WHERE last_login < NOW() - INTERVAL '30 days';

DELETE（删除）

-- 删除特定记录
DELETE FROM users 
WHERE username = 'admin';-- 删除所有记录
DELETE FROM users;-- 截断表（更快的清空表方式）
TRUNCATE TABLE users;-- 删除表
DROP TABLE users;

高级操作

事务处理

-- 开始事务
BEGIN;-- 执行操作
INSERT INTO users (username, email) VALUES ('user1', '[e-mail]');
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;-- 提交事务
COMMIT;-- 回滚事务
ROLLBACK;

索引操作

-- 创建索引
CREATE INDEX idx_username ON users(username);-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);-- 删除索引
DROP INDEX idx_username;

常用函数

-- 计数
SELECT COUNT(*) FROM users;-- 分组统计
SELECT status, COUNT(*) 
FROM users 
GROUP BY status;-- 聚合函数
SELECT AVG(age) as average_age,MAX(age) as max_age,MIN(age) as min_age
FROM users;

实用技巧

备份和恢复

# 备份数据库
pg_dump -U postgres xianxia > backup.sql# 恢复数据库
psql -U postgres xianxia < backup.sql

性能优化

1. 使用 EXPLAIN 分析查询

EXPLAIN ANALYZE 
SELECT * FROM users WHERE email = '[e-mail]';

2. 创建合适的索引

-- 为经常查询的字段创建索引
CREATE INDEX idx_email ON users(email);

3. 定期维护

-- 清理和分析数据库
VACUUM ANALYZE users;

常见问题解决

1. 连接问题

-- 查看当前连接
SELECT * FROM pg_stat_activity;-- 终止特定连接
SELECT pg_terminate_backend(pid);

2. 权限问题

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE xianxia TO username;-- 撤销权限
REVOKE ALL PRIVILEGES ON DATABASE xianxia FROM username;

最佳实践

1. 总是使用事务来保证数据一致性
2. 为频繁查询的字段创建索引
3. 定期进行数据库维护和备份
4. 使用参数化查询防止SQL注入
5. 适当使用视图简化复杂查询
6. 遵循命名规范
7. 为表添加适当的约束