PostgreSQL 常用操作指南

2. 修改表（ALTER TABLE）

-- 1. 添加字段
ALTER TABLE user_info ADD COLUMN phone VARCHAR(20);

-- 2. 修改字段类型
ALTER TABLE user_info ALTER COLUMN email TYPE VARCHAR(150);

-- 3. 修改字段默认值
ALTER TABLE user_info ALTER COLUMN age SET DEFAULT 18;

-- 4. 删除字段
ALTER TABLE user_info DROP COLUMN IF EXISTS phone;

-- 5. 重命名字段
ALTER TABLE user_info RENAME COLUMN update_time TO modify_time;

-- 6. 重命名表
ALTER TABLE user_info RENAME TO user_info_new;

3. 删除表

-- 基础删除
DROP TABLE user_info;

-- 安全删除
DROP TABLE IF EXISTS user_info;

-- 级联删除（删除表及依赖它的对象，如外键关联）
DROP TABLE IF EXISTS user_info CASCADE;

4. 查看表信息

-- 方法 1：查看当前数据库所有表
\dt

-- 方法 2：查看指定表结构（详细字段信息）
\d user_info

-- 方法 3：SQL 查询表结构
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'user_info';

5. 创建索引（优化查询速度）

-- 普通索引
CREATE INDEX idx_user_username ON user_info(username);

-- 唯一索引（确保字段值唯一）
CREATE UNIQUE INDEX idx_user_email ON user_info(email);

-- 复合索引（多字段组合）
CREATE INDEX idx_user_age_create_time ON user_info(age, create_time);

-- 删除索引
DROP INDEX IF EXISTS idx_user_username;

三、数据操作（增、删、改、查）

1. 插入数据（INSERT）

-- 基础插入
INSERT INTO user_info (username, age, email)
VALUES ('zhangsan', 25, '[email protected]');

-- 批量插入
INSERT INTO user_info (username, age, email)
VALUES ('lisi', 28, '[email protected]'), ('wangwu', 30, '[email protected]');

-- 插入并返回结果（获取自增 ID 等）
INSERT INTO user_info (username, age)
VALUES ('zhaoliu', 22)
RETURNING id, username, create_time;

2. 更新数据（UPDATE）

-- 基础更新
UPDATE user_info SET age = 26, email = '[email protected]'
WHERE username = 'zhangsan';

-- 安全更新（避免无 WHERE 条件更新全表）
UPDATE user_info SET modify_time = CURRENT_TIMESTAMP
WHERE id = 1;

3. 删除数据（DELETE）

-- 基础删除
DELETE FROM user_info WHERE username = 'zhaoliu';

-- 清空全表（不可回滚，谨慎使用）
TRUNCATE TABLE user_info;

-- 清空全表并重置自增序列（SERIAL/IDENTITY 列）
TRUNCATE TABLE user_info RESTART IDENTITY;

4. 查询数据（SELECT）

-- 基础查询
SELECT id, username, age FROM user_info;

-- 条件查询
SELECT * FROM user_info WHERE age > 25 AND email IS NOT NULL;

-- 排序查询
SELECT * FROM user_info ORDER BY age DESC, create_time ASC;

-- 分页查询（PostgreSQL 特有，比 LIMIT+OFFSET 更高效）
-- 第 1 页（每页 10 条）
SELECT * FROM user_info ORDER BY id LIMIT 10 OFFSET 0;

-- 第 2 页
SELECT * FROM user_info ORDER BY id LIMIT 10 OFFSET 10;

-- 聚合查询
SELECT COUNT(*) AS total_count,      -- 总条数
       AVG(age) AS avg_age,          -- 平均年龄
       MAX(age) AS max_age           -- 最大年龄
FROM user_info;

-- 分组查询
SELECT age, COUNT(*) AS user_count
FROM user_info
GROUP BY age
HAVING COUNT(*) > 1; -- 分组后过滤

四、常用进阶操作

1. 事务管理（保证数据一致性）

-- 开启事务
BEGIN;

-- 执行操作（多个 SQL 语句）
INSERT INTO user_info (username, age) VALUES ('test1', 18);
UPDATE user_info SET age = 19 WHERE username = 'test1';

-- 提交事务（确认修改）
COMMIT;

-- 回滚事务（放弃修改，回到 BEGIN 前状态）
-- ROLLBACK;

2. 视图创建（简化复杂查询）

-- 创建视图
CREATE VIEW v_user_adult AS
SELECT id, username, email, create_time
FROM user_info
WHERE age >= 18;

-- 查询视图
SELECT * FROM v_user_adult;

-- 删除视图
DROP VIEW IF EXISTS v_user_adult;

3. 备份与恢复

（1）备份数据库（pg_dump 工具，命令行执行）

# 基础备份（导出 SQL 文件）
pg_dump -U username -d db_name -h host -p port > backup.sql

# 压缩备份（推荐，节省空间）
pg_dump -U username -d db_name | gzip > backup.sql.gz

# 仅备份表结构（不包含数据）
pg_dump -U username -d db_name -s > backup_schema.sql

（2）恢复数据库（psql/pg_restore 工具）

# 恢复 SQL 格式备份
psql -U username -d db_name -h host -p port < backup.sql

# 恢复压缩备份
gzip -d -c backup.sql.gz | psql -U username -d db_name

# 恢复自定义格式备份（pg_dump -F c 导出的文件）
pg_restore -U username -d db_name -h host -p port backup.dump

五、用户与权限管理

1. 创建用户

-- 基础创建
CREATE USER user1 WITH PASSWORD '123456';

-- 创建带权限的用户（允许创建数据库、角色）
CREATE USER user2 WITH PASSWORD '123456' CREATEDB CREATEROLE;

2. 授权权限

-- 授予数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE db_name TO user1;

-- 授予表的查询/插入权限
GRANT SELECT, INSERT ON TABLE user_info TO user1;

-- 授予序列权限（自增列需要）
GRANT USAGE, SELECT ON SEQUENCE user_info_id_seq TO user1;

-- 收回权限
REVOKE INSERT ON TABLE user_info FROM user1;

3. 删除用户

-- 基础删除
DROP USER IF EXISTS user1;

-- 级联删除（用户拥有的对象一并删除）
DROP USER IF EXISTS user2 CASCADE;

六、psql 常用元命令（便捷操作）