一、基础概述

1.1 什么是PostgreSQL

PostgreSQL（简称Postgres）是一款开源的关系型数据库管理系统（RDBMS），支持SQL标准，同时提供丰富的扩展功能（如JSON存储、全文检索、地理信息处理等），稳定性强、兼容性好，适用于从个人项目到企业级应用的各类场景。

核心特点：开源免费、支持ACID事务、可扩展性强、多数据类型支持（数值、字符串、日期、JSON、数组等）、强大的索引机制。

1.2 环境部署与连接

1.2.1 常见部署方式

• 本地部署：Windows/Linux/MacOS，通过官方安装包或包管理器（如yum、apt、brew）安装。

• 容器部署：使用Docker快速启动，命令示例：docker run --name postgres -e POSTGRES_PASSWORD=123456 -p 5432:5432 -d postgres

• 云服务：阿里云RDS、腾讯云CDB等，无需手动配置环境，直接获取连接信息。

1.2.2 连接方式

核心连接参数：主机（host）、端口（默认5432）、数据库名（dbname）、用户名（user）、密码（password）。

• 命令行连接（psql）：psql -h 127.0.0.1 -p 5432 -U 用户名 -d 数据库名，输入密码后进入交互模式。

• 图形化工具连接：Navicat、DBeaver、PgAdmin（PostgreSQL官方工具），填写连接参数即可。

• 代码连接：通过Python（psycopg2）、Java（JDBC）、Go（pgx）等语言的驱动包连接，示例见下文实操模块。

二、核心操作（SQL基础）

2.1 数据库操作

2.1.1 创建数据库

语法：CREATE DATABASE 数据库名 [WITH OWNER = 用户名 ENCODING = 'UTF8'];

示例：CREATE DATABASE test_db WITH OWNER = postgres ENCODING = 'UTF8';（指定所有者为postgres，编码为UTF-8，避免中文乱码）。

2.1.2 查看数据库

查看所有数据库：\l（psql交互模式下）或 SELECT datname FROM pg_database;（SQL语句）。

查看当前连接的数据库：SELECT current_database();。

2.1.3 删除数据库

语法：DROP DATABASE [IF EXISTS] 数据库名;

注意：删除数据库会彻底删除所有数据，无法恢复，建议先备份；若有活跃连接，需先断开连接再删除。

2.2 表操作

2.2.1 创建表

语法：

• CREATE TABLE 表名 ( 字段名1 数据类型 [约束条件], 字段名2 数据类型 [约束条件], ... [主键约束、外键约束等] );

常用数据类型：

• 数值型：int（整数）、bigint（长整数）、numeric(10,2)（小数，10位总长度，2位小数）。

• 字符串型：varchar(n)（可变长度字符串，n为最大长度）、text（不限长度字符串）。

• 日期时间型：date（日期，如2026-04-07）、time（时间，如14:30:00）、timestamp（日期时间，如2026-04-07 14:30:00）。

• 其他：boolean（布尔值，true/false）、json（JSON格式数据）、array（数组）。

常用约束：

• 主键（PRIMARY KEY）：唯一标识表中记录，非空且唯一，示例：id int PRIMARY KEY。

• 非空（NOT NULL）：字段值不能为null，示例：name varchar(50) NOT NULL。

• 唯一（UNIQUE）：字段值唯一，可为空，示例：phone varchar(11) UNIQUE。

• 外键（FOREIGN KEY）：关联另一张表的主键，示例：user_id int REFERENCES user(id)。

• 默认值（DEFAULT）：字段未赋值时使用默认值，示例：create_time timestamp DEFAULT CURRENT_TIMESTAMP（默认取当前时间）。

示例：创建用户表user_info

• CREATE TABLE user_info ( id int PRIMARY KEY, name varchar(50) NOT NULL, age int CHECK (age > 0 AND age < 150), -- 检查约束，年龄范围0-150 phone varchar(11) UNIQUE, create_time timestamp DEFAULT CURRENT_TIMESTAMP );

2.2.2 查看表结构

psql交互模式：\d 表名（查看表的字段、类型、约束）。

SQL语句：SELECT * FROM information_schema.columns WHERE table_name = '表名';（查看详细字段信息）。

2.2.3 修改表结构（ALTER TABLE）

• 添加字段：ALTER TABLE 表名 ADD COLUMN 字段名 数据类型 [约束];，示例：ALTER TABLE user_info ADD COLUMN email varchar(100) UNIQUE;。

• 修改字段类型：ALTER TABLE 表名 ALTER COLUMN 字段名 TYPE 新数据类型;，示例：ALTER TABLE user_info ALTER COLUMN age TYPE bigint;。

• 修改字段名：ALTER TABLE 表名 RENAME COLUMN 旧字段名 TO 新字段名;，示例：ALTER TABLE user_info RENAME COLUMN phone TO mobile;。

• 删除字段：ALTER TABLE 表名 DROP COLUMN [IF EXISTS] 字段名;，示例：ALTER TABLE user_info DROP COLUMN email;。

• 修改表名：ALTER TABLE 旧表名 RENAME TO 新表名;。

2.2.4 删除表

语法：DROP TABLE [IF EXISTS] 表名 [CASCADE];

说明：CASCADE表示级联删除，若该表被其他表作为外键关联，添加CASCADE会同时删除关联的约束或表（谨慎使用）。

2.3 数据操作（CRUD）

2.3.1 插入数据（INSERT）

语法1（指定所有字段）：INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);

语法2（不指定字段，按表字段顺序插入）：INSERT INTO 表名 VALUES (值1, 值2, ...);

批量插入：INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2), (值3, 值4), ...;

示例：向user_info插入数据

• INSERT INTO user_info (id, name, age, mobile) VALUES (1, '张三', 25, '13800138000'), (2, '李四', 30, '13900139000');

2.3.2 查询数据（SELECT）

基础语法：SELECT 字段1, 字段2, ... FROM 表名 [WHERE 条件] [ORDER BY 字段] [LIMIT 数量];

• 查询所有字段：SELECT * FROM 表名;

• 条件查询：SELECT * FROM user_info WHERE age > 25;（查询年龄大于25的用户）。

• 排序：SELECT * FROM user_info ORDER BY age DESC;（按年龄降序排列，ASC为升序，默认升序）。

• 限制数量：SELECT * FROM user_info LIMIT 10;（查询前10条数据）。

• 去重：SELECT DISTINCT age FROM user_info;（查询不重复的年龄）。

• 模糊查询：SELECT * FROM user_info WHERE name LIKE '%张%';（查询名字包含“张”的用户，%表示任意字符）。

2.3.3 更新数据（UPDATE）

语法：UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2, ... [WHERE 条件];

注意：必须加WHERE条件，否则会更新表中所有数据（极其危险）！

示例：将id=1的用户年龄改为26

UPDATE user_info SET age = 26 WHERE id = 1;

2.3.4 删除数据（DELETE）

语法：DELETE FROM 表名 [WHERE 条件];

注意：同样必须加WHERE条件，否则会删除表中所有数据！

示例：删除id=2的用户

DELETE FROM user_info WHERE id = 2;

三、进阶操作

3.1 索引

3.1.1 索引的作用

提高查询效率，减少数据库扫描的数据量；适用于查询频繁、数据量大的表；但插入、更新、删除数据时会增加开销（需维护索引），不宜过度创建。

3.1.2 常见索引类型

• B树索引（默认）：适用于等值查询、范围查询，如WHERE id = 1、WHERE age BETWEEN 20 AND 30。

• 哈希索引：适用于等值查询，查询速度快，但不支持范围查询。

• 全文索引：适用于字符串的模糊查询、全文检索，如WHERE name @@ to_tsquery('张')。

3.1.3 索引操作

创建索引：CREATE [UNIQUE] INDEX 索引名 ON 表名 (字段名);

示例：为user_info的name字段创建索引

CREATE INDEX idx_user_name ON user_info (name);

查看索引：\d 表名（psql交互模式）或 SELECT indexname FROM pg_indexes WHERE tablename = '表名';。

删除索引：DROP INDEX [IF EXISTS] 索引名;

3.2 事务

3.2.1 事务的ACID特性

• 原子性（Atomicity）：事务要么全部执行，要么全部回滚，不会出现部分执行的情况。

• 一致性（Consistency）：事务执行前后，数据库的完整性约束不变。

• 隔离性（Isolation）：多个事务并发执行时，相互不干扰，每个事务都感觉不到其他事务的存在。

• 持久性（Durability）：事务执行完成后，数据的修改会永久保存，即使数据库崩溃也不会丢失。

3.2.2 事务操作

PostgreSQL默认自动提交事务（每条SQL语句都是一个独立事务），手动事务需手动控制提交和回滚。

• -- 开启事务 BEGIN; -- 执行SQL操作（可多个） INSERT INTO user_info (id, name, age, mobile) VALUES (3, '王五', 28, '13700137000'); UPDATE user_info SET age = 29 WHERE id = 3; -- 提交事务（数据永久生效） COMMIT; -- 若出现错误，回滚事务（撤销所有操作） -- ROLLBACK;

3.3 视图

3.3.1 视图的作用

视图是虚拟表，基于查询结果创建，不存储实际数据，仅存储查询逻辑；用于简化复杂查询、控制数据访问权限（隐藏敏感字段）。

3.3.2 视图操作

创建视图：CREATE VIEW 视图名 AS SELECT 字段1, 字段2 FROM 表名 [WHERE 条件];

示例：创建用户年龄大于25的视图

CREATE VIEW v_user_age_25 AS SELECT id, name, age FROM user_info WHERE age > 25;

查询视图：SELECT * FROM 视图名;（与查询普通表一致）。

修改视图：CREATE OR REPLACE VIEW 视图名 AS 新的查询语句;。

删除视图：DROP VIEW [IF EXISTS] 视图名;。

3.4 常用函数

3.4.1 聚合函数（用于统计）

• COUNT()：统计记录数，示例：SELECT COUNT(*) FROM user_info;（统计所有用户数）。

• SUM()：求和，示例：SELECT SUM(age) FROM user_info;（求所有用户年龄总和）。

• AVG()：求平均值，示例：SELECT AVG(age) FROM user_info;（求用户平均年龄）。

• MAX()/MIN()：求最大值/最小值，示例：SELECT MAX(age) FROM user_info;。

3.4.2 字符串函数

• CONCAT()：拼接字符串，示例：SELECT CONCAT(name, '-', mobile) FROM user_info;。

• LENGTH()：获取字符串长度，示例：SELECT LENGTH(name) FROM user_info;。

• UPPER()/LOWER()：转换大小写，示例：SELECT UPPER(name) FROM user_info;（转为大写）。

3.4.3 日期函数

• CURRENT_TIMESTAMP：获取当前日期时间，示例：SELECT CURRENT_TIMESTAMP;。

• TO_CHAR()：日期格式转换，示例：SELECT TO_CHAR(create_time, 'YYYY-MM-DD HH24:MI:SS') FROM user_info;。

• AGE()：计算时间差，示例：SELECT AGE(CURRENT_TIMESTAMP, create_time) FROM user_info;（计算用户创建时间到现在的时长）。

四、实用技巧与注意事项

4.1 常用psql命令

• \l：查看所有数据库。

• \c 数据库名：切换到指定数据库。

• \d 表名：查看表结构。

• \d+ 表名：查看表的详细信息（包括索引、注释等）。

• \q：退出psql交互模式。

• \help：查看SQL命令帮助。

4.2 数据备份与恢复

4.2.1 备份（pg_dump）

命令行备份整个数据库：pg_dump -U 用户名 -d 数据库名 -f 备份文件路径.sql

示例：pg_dump -U postgres -d test_db -f D:\backup\test_db_backup.sql

4.2.2 恢复（psql）

先创建空数据库，再执行备份文件：psql -U 用户名 -d 数据库名 -f 备份文件路径.sql

4.3 注意事项

• 编码问题：创建数据库时务必指定ENCODING = 'UTF8'，避免中文乱码。

• 权限控制：创建用户时分配对应权限（如SELECT、INSERT、UPDATE等），避免使用超级用户直接操作业务数据库。

• 性能优化：避免频繁查询大表的所有字段（尽量指定需要的字段），合理创建索引，避免在WHERE条件中使用函数（会导致索引失效）。

• 安全问题：数据库密码不要明文存储，定期更换密码；禁止将数据库端口暴露在公网，限制访问IP。

五、代码连接示例（Python）

使用psycopg2驱动连接PostgreSQL，需先安装：pip install psycopg2-binary

• import psycopg2 from psycopg2 import OperationalError def connect_postgres(): # 连接参数 conn_params = { "host": "127.0.0.1", "port": 5432, "user": "postgres", "password": "123456", "dbname": "test_db" } conn = None try: # 建立连接 conn = psycopg2.connect(**conn_params) print("连接PostgreSQL成功！") # 执行查询 cursor = conn.cursor() cursor.execute("SELECT * FROM user_info;") results = cursor.fetchall() # 获取所有查询结果 for row in results: print(row) except OperationalError as e: print(f"连接失败：{e}") finally: # 关闭连接 if conn: conn.close() print("连接已关闭") if __name__ == "__main__": connect_postgres()