PostgreSQL（pgSQL）常用操作
目录
一、数据库管理（创建、连接、删除等）
1. 创建数据库
-- 基础创建 CREATE DATABASE db_name; -- 指定字符集、所有者、编码（推荐生产环境使用） CREATE DATABASE db_name WITH OWNER = username -- 数据库所有者 ENCODING = 'UTF8' -- 字符编码 LC_COLLATE = 'zh_CN.UTF-8' -- 排序规则 LC_CTYPE = 'zh_CN.UTF-8' -- 字符分类规则 TABLESPACE = pg_default; -- 表空间2. 连接数据库
（1）命令行方式（psql 工具）
# 基础连接 psql -U username -d db_name -h host_ip -p port # 示例：连接本地默认端口的test_db数据库 psql -U postgres -d test_db -h 127.0.0.1 -p 5432 # 本地默认配置（用户名postgres，端口5432）可简化 psql -d db_name（2）SQL 内部切换数据库
\c db_name; -- 等同于MySQL的use db_name;3. 删除数据库
-- 基础删除（数据库不存在会报错） DROP DATABASE db_name; -- 安全删除（数据库不存在不报错） DROP DATABASE IF EXISTS db_name; -- 强制删除（断开所有连接后删除，需超级权限） DROP DATABASE db_name WITH (FORCE);二、表操作（创建、修改、删除、索引等）
1. 创建表
CREATE TABLE user_info ( id SERIAL PRIMARY KEY, -- 自增主键（SERIAL等价于int4+自增序列） username VARCHAR(50) NOT NULL UNIQUE, -- 非空唯一 age INT DEFAULT 0, -- 默认值0 email VARCHAR(100), create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 默认当前时间 update_time TIMESTAMP ); -- 说明：PostgreSQL 10+推荐使用IDENTITY列替代SERIAL（更标准） CREATE TABLE user_info ( id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 其他字段同上 );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 常用元命令（便捷操作）
| 元命令 | 功能说明 |
| \c db_name | 切换数据库 |
| \l | 查看所有数据库 |
| \dt | 查看当前数据库所有表 |
| \d table_name | 查看指定表结构 |
| \di | 查看所有索引 |
| \du | 查看所有用户 |
| \q | 退出 psql 终端 |
| \x | 切换横向 / 纵向显示查询结果 |
| \timing | 开启 / 关闭查询耗时统计 |
