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' , 'admin@example.com' ) ;
INSERT INTO users ( username, email)
VALUES ( 'user1' , 'user1@example.com' ) , ( 'user2' , 'user2@example.com' ) ;
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 ;
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 = 'newemail@example.com'
WHERE username = 'admin' ;
UPDATE users
SET email = 'newemail@example.com' , 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' , 'user1@example.com' ) ;
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
性能优化
使用 EXPLAIN 分析查询
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'admin@example.com' ;
创建合适的索引
CREATE INDEX idx_email ON users( email) ;
定期维护
VACUUM ANALYZE users;
常见问题解决
连接问题
SELECT * FROM pg_stat_activity;
SELECT pg_terminate_backend( pid) ;
权限问题
GRANT ALL PRIVILEGES ON DATABASE xianxia TO username;
REVOKE ALL PRIVILEGES ON DATABASE xianxia FROM username;
最佳实践
总是使用事务来保证数据一致性 为频繁查询的字段创建索引 定期进行数据库维护和备份 使用参数化查询防止SQL注入 适当使用视图简化复杂查询 遵循命名规范 为表添加适当的约束