一、数据库管理(创建、连接、删除等)
1. 创建数据库
-- 基础创建
CREATE DATABASE db_name;
-- 指定字符集、所有者、编码(推荐生产环境使用)
CREATE DATABASE db_name WITH OWNER = username
ENCODING = 'UTF8'
LC_COLLATE =
LC_CTYPE
TABLESPACE pg_default;
PostgreSQL 数据库的常用操作,包括数据库的创建、连接与删除,表的增删改查及索引优化,数据的插入更新删除查询,事务管理与视图创建,以及备份恢复策略。此外还涵盖了用户权限管理和 psql 命令行元命令的使用,提供了具体的 SQL 语句和 shell 命令示例,适用于数据库开发与管理场景。
-- 基础创建
CREATE DATABASE db_name;
-- 指定字符集、所有者、编码(推荐生产环境使用)
CREATE DATABASE db_name WITH OWNER = username
ENCODING = 'UTF8'
LC_COLLATE =
LC_CTYPE
TABLESPACE pg_default;
# 基础连接
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
\c db_name; -- 等同于 MySQL 的 use db_name;
-- 基础删除(数据库不存在会报错)
DROP DATABASE db_name;
-- 安全删除(数据库不存在不报错)
DROP DATABASE IF EXISTS db_name;
-- 强制删除(断开所有连接后删除,需超级权限)
DROP DATABASE db_name WITH (FORCE);
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, -- 其他字段同上
username VARCHAR(50) NOT NULL UNIQUE,
age INT DEFAULT 0,
email VARCHAR(100),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP
);
-- 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;
-- 基础删除
DROP TABLE user_info;
-- 安全删除
DROP TABLE IF EXISTS user_info;
-- 级联删除(删除表及依赖它的对象,如外键关联)
DROP TABLE IF EXISTS user_info CASCADE;
-- 方法 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';
-- 普通索引
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;
-- 基础插入
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;
-- 基础更新
UPDATE user_info SET age = 26, email = '[email protected]'
WHERE username = 'zhangsan';
-- 安全更新(避免无 WHERE 条件更新全表)
UPDATE user_info SET modify_time = CURRENT_TIMESTAMP
WHERE id = 1;
-- 基础删除
DELETE FROM user_info WHERE username = 'zhaoliu';
-- 清空全表(不可回滚,谨慎使用)
TRUNCATE TABLE user_info;
-- 清空全表并重置自增序列(SERIAL/IDENTITY 列)
TRUNCATE TABLE user_info RESTART IDENTITY;
-- 基础查询
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; -- 分组后过滤
-- 开启事务
BEGIN;
-- 执行操作(多个 SQL 语句)
INSERT INTO user_info (username, age) VALUES ('test1', 18);
UPDATE user_info SET age = 19 WHERE username = 'test1';
-- 提交事务(确认修改)
COMMIT;
-- 回滚事务(放弃修改,回到 BEGIN 前状态)
-- ROLLBACK;
-- 创建视图
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;
# 基础备份(导出 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
# 恢复 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
-- 基础创建
CREATE USER user1 WITH PASSWORD '123456';
-- 创建带权限的用户(允许创建数据库、角色)
CREATE USER user2 WITH PASSWORD '123456' CREATEDB CREATEROLE;
-- 授予数据库所有权限
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;
-- 基础删除
DROP USER IF EXISTS user1;
-- 级联删除(用户拥有的对象一并删除)
DROP USER IF EXISTS user2 CASCADE;
| 元命令 | 功能说明 |
|---|---|
| \c db_name | 切换数据库 |
| \l | 查看所有数据库 |
| \dt | 查看当前数据库所有表 |
| \d table_name | 查看指定表结构 |
| \di | 查看所有索引 |
| \du | 查看所有用户 |
| \q | 退出 psql 终端 |
| \x | 切换横向 / 纵向显示查询结果 |
| \timing | 开启 / 关闭查询耗时统计 |

微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL 转 CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML 转 Markdown 互为补充。 在线工具,Markdown 转 HTML在线工具,online