PostgreSQL数据库常用命令有哪些？

PostgreSQL 是一款功能强大的开源关系型数据库管理系统，以其丰富的功能、高可靠性和良好的扩展性著称，掌握其常用命令是高效管理和操作数据库的基础，以下从数据库连接、用户与权限管理、数据库与对象操作、数据操作与查询、备份与恢复等方面详细介绍 PostgreSQL 的核心命令。

数据库连接与退出

连接 PostgreSQL 数据库需使用 psql 命令行工具，基本语法为：

psql -h 主机名 -p 端口号 -U 用户名 -d 数据库名

连接本地默认数据库（端口为 5432）可简化为 psql -U postgres，首次连接可能需输入密码，可通过 PGPASSWORD 环境变量或 ~/.pgpass 文件配置免密登录，退出当前会话使用 \q 命令，或直接按 Ctrl+D。

用户与权限管理

用户（角色）是 PostgreSQL 权限控制的核心，创建用户需使用 CREATE ROLE 或 CREATE USER（后者默认包含登录权限），

CREATE USER user1 WITH PASSWORD 'password123' CREATEDB;

CREATEDB 表示用户具备创建数据库的权限，授予用户权限使用 GRANT，如授予表 employees 的查询权限：

GRANT SELECT ON employees TO user1;

撤销权限则用 REVOKE，如 REVOKE INSERT ON employees FROM user1;，删除用户需先处理其对象依赖，或使用 DROP USER user1 CASCADE 强制删除。

数据库与对象操作

创建数据库使用 CREATE DATABASE，

CREATE DATABASE mydb WITH ENCODING 'UTF8' TEMPLATE template0;

ENCODING 指定字符集，TEMPLATE 选择模板数据库（默认为 template1），切换当前数据库使用 \c mydb（在 psql 中），创建表需定义字段及类型，如：

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age > 0)
);

SERIAL 为自增主键类型，CHECK 约束确保数据有效性，修改表结构使用 ALTER TABLE，如添加列：ALTER TABLE students ADD COLUMN email VARCHAR(100);，删除表使用 DROP TABLE students;，若需级联删除关联数据则加 CASCADE。

数据操作与查询

数据插入使用 INSERT INTO，

INSERT INTO students (name, age, email) VALUES ('Alice', 20, 'alice@example.com');

批量插入可多组 VALUES 并用逗号分隔，更新数据通过 UPDATE，需搭配 WHERE 条件避免全表更新：

UPDATE students SET age = 21 WHERE name = 'Alice';

删除数据使用 DELETE FROM，同样需 WHERE 限制范围，否则清空表数据。

查询数据是核心操作，基本语法为：

SELECT column1, column2 FROM table_name WHERE condition GROUP BY column1 HAVING group_condition ORDER BY column1 DESC LIMIT 10;

SELECT 指定列， 表示所有列；WHERE 过滤行，GROUP BY 分组聚合，HAVING 筛选分组结果，ORDER BY 排序，LIMIT 限制返回行数，常用函数包括聚合函数（COUNT(), SUM(), AVG()）和字符串函数（CONCAT(), SUBSTR()）。

索引与性能优化

创建索引可加速查询，例如为 students 表的 name 列创建索引：

CREATE INDEX idx_students_name ON students(name);

索引适用于高频查询列，但会降低写入速度，查看索引使用情况可通过 \di（在 psql 中）或查询系统视图 pg_indexes。

备份与恢复

备份工具为 pg_dump，可导出整个数据库或特定表：

pg_dump -U postgres -f backup.sql mydb  # 备份数据库到文件
pg_dump -U postgres -t students -f students.sql mydb  # 备份单表

恢复数据使用 psql 或 pg_restore（针对自定义格式备份）：

psql -U postgres -d mydb -f backup.sql

对于大型数据库，建议使用 pg_dump 的自定义格式（Fc）并配合 pg_restore 实现增量恢复。

相关问答 FAQs

Q1: 如何查看 PostgreSQL 数据库中所有表的大小？
A1: 在 psql 中执行以下查询，可按表大小降序排列：

SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

pg_size_pretty() 函数将字节大小转换为易读格式（如 KB、MB）。

Q2: 忘记 PostgreSQL 用户密码如何重置？
A2: 若拥有系统管理员权限，可停止 PostgreSQL 服务，编辑 pg_hba.conf 文件，将目标用户的认证方法临时改为 trust，然后重启服务，使用 psql -U postgres 连接后执行 ALTER USER user1 WITH PASSWORD 'newpassword';，最后恢复 pg_hba.conf 的原始配置并重启服务，若无法停止服务，可通过 pg_shadow 表查看密码哈希（需超级用户权限）,但直接修改密码更高效。