PostgreSQL 的一些常用命令
以下是 PostgreSQL 的一些常用命令，分为连接数据库、用户和权限管理、表管理、查询数据等多个方面进行讲解，帮助你快速熟悉 PostgreSQL 的操作。
1. 数据库连接与退出
连接到数据库
psql -U username -d dbname -h host -p port
-U：指定用户名。-d：指定数据库名称。-h：指定主机地址（默认是localhost）。-p：指定端口号（默认是5432）。
退出数据库
在 psql 中输入以下命令退出：
\q
2. 数据库管理
列出所有数据库
\l
切换到某个数据库
\c database_name
或者：
psql -U username -d database_name
创建数据库
CREATE DATABASE database_name;
删除数据库
DROP DATABASE database_name;
显示当前连接的数据库信息
\conninfo
3. 用户和权限管理
创建用户
CREATE USER username WITH PASSWORD 'password';
删除用户
DROP USER username;
为用户分配权限
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
ALL PRIVILEGES：授予用户对数据库的所有权限。- 其他权限可使用：
SELECT、INSERT、UPDATE、DELETE等。
撤销用户权限
REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;
列出所有用户
\du
4. 表管理
列出当前数据库的所有表
\dt
创建表
CREATE TABLE table_name (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT
);
查看表结构
\d table_name
删除表
DROP TABLE table_name;
重命名表
ALTER TABLE table_name RENAME TO new_table_name;
向表中添加列
ALTER TABLE table_name ADD COLUMN column_name data_type;
删除表的列
ALTER TABLE table_name DROP COLUMN column_name;
5. 插入、查询和修改数据
插入数据
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
查询数据
SELECT * FROM table_name;
- 条件查询：
SELECT * FROM table_name WHERE column_name = 'value';
- 排序：
SELECT * FROM table_name ORDER BY column_name ASC; -- 升序
SELECT * FROM table_name ORDER BY column_name DESC; -- 降序
更新数据
UPDATE table_name SET column_name = new_value WHERE condition;
删除数据
DELETE FROM table_name WHERE condition;
删除表中所有数据（但保留表结构）：
TRUNCATE TABLE table_name;
6. 索引
创建索引
CREATE INDEX index_name ON table_name (column_name);
删除索引
DROP INDEX index_name;
7. 备份与恢复
备份数据库
在终端中使用 pg_dump 命令：
pg_dump -U username -d database_name > backup.sql
恢复数据库
psql -U username -d database_name < backup.sql
8. 常用 psql 元命令
| 命令 | 功能 |
|---|---|
\l | 列出所有数据库 |
\c database_name | 连接到指定数据库 |
\dt | 列出当前数据库的所有表 |
\d table_name | 查看表结构（字段、类型、约束） |
\du | 列出所有用户和权限 |
\di | 列出所有索引 |
\df | 列出所有函数 |
\dn | 列出所有 schema |
\q | 退出 psql |
\conninfo | 显示当前连接的数据库和用户信息 |
9. 高级操作
事务管理
PostgreSQL 支持事务，以下是基本事务操作：
- 开始事务：
BEGIN; - 执行操作：
INSERT INTO table_name (column1, column2) VALUES (value1, value2); - 提交事务：
COMMIT; - 回滚事务：
ROLLBACK;
外键约束
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id), -- 定义外键
order_date DATE NOT NULL
);
查看执行计划
使用 EXPLAIN 查看查询的执行计划：
EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';
10. 日常排错命令
检查服务状态
在 Linux 系统中：
sudo service postgresql status
查看日志
日志路径通常在 /var/log/postgresql/ 下：
sudo tail -f /var/log/postgresql/postgresql-14-main.log