PostgreSQL（PgSQL）入门指南

PostgreSQL（常简称为PgSQL）是一款功能强大、开源免费的关系型数据库管理系统（RDBMS），以稳定性、安全性和丰富的特性（如支持复杂数据类型、自定义函数、事务、并发控制等）著称，广泛应用于个人项目、企业级应用甚至大型互联网系统。本指南将从环境搭建、基础概念到核心操作，带您快速入门PgSQL。

一、环境搭建：安装与连接

首先需要在本地或服务器上安装PgSQL，并通过工具连接数据库。

1. 安装PgSQL

根据操作系统选择对应的安装方式，以下是主流系统的安装步骤：

（1）Windows系统

访问PgSQL官方下载页：PostgreSQL Downloads
下载对应版本的安装包（建议选择LTS长期支持版，如16.x）
双击安装，按向导提示操作：
- 端口号：默认5432（若端口被占用可修改，需记住自定义端口）
- 超级用户密码：设置postgres用户（PgSQL默认超级用户）的密码（重要，后续连接需使用）
- 安装组件：默认勾选PostgreSQL Server（核心服务）、pgAdmin 4（可视化管理工具）即可
安装完成后，通过开始菜单启动PostgreSQL xx服务（默认自动启动）。

（2）Linux系统（以Ubuntu为例）

通过APT包管理器安装，命令如下：

# 1. 更新软件源
sudo apt update

# 2. 安装PgSQL服务器和客户端
sudo apt install postgresql postgresql-client

# 3. 验证服务状态（若显示"active (running)"则成功）
sudo systemctl status postgresql

安装后，Linux默认会创建postgres系统用户和数据库用户，需先切换到该用户再操作：

# 切换到postgres系统用户
sudo su - postgres

# 进入PgSQL命令行（此时已自动以postgres用户登录）
psql

（3）macOS系统

推荐使用Homebrew安装（需先安装Homebrew：brew.sh）

执行命令：

# 安装PgSQL
brew install postgresql@16

# 启动服务（并设置开机自启）
brew services start postgresql@16

2. 连接PgSQL：两种常用方式

连接PgSQL需知道4个核心信息：主机（Host）、端口（Port）、用户名（Username）、密码（Password）。

（1）可视化工具：pgAdmin 4（推荐新手）

安装PgSQL时默认自带，若未安装可单独下载：pgAdmin Downloads
连接步骤：
1. 打开pgAdmin 4，首次启动可能需要设置主密码（保护pgAdmin配置）
2. 点击左侧菜单栏「Object」→「Register」→「Server...」
3. 在「General」标签填写服务器名称（自定义，如“Local PgSQL”）
4. 在「Connection」标签填写：
  - Host name/address：本地连接填localhost或127.0.0.1
  - Port：默认5432（安装时自定义的需对应）
  - Username：默认postgres
  - Password：安装时设置的postgres用户密码（勾选“Save password”可保存）
5. 点击「Save」，若连接成功，左侧会显示该服务器及默认数据库。

（2）命令行工具：psql（适合进阶用户）

Windows：打开「PostgreSQL xx」→「SQL Shell (psql)」
Linux/macOS：直接在终端输入psql（需先确保PgSQL服务已启动）

命令行连接格式：

# 完整格式（适用于远程或自定义参数）
psql -h localhost -p 5432 -U postgres -d postgres

# 本地默认参数可简化（仅需输入密码）
psql -U postgres

常用psql命令（需加;结尾，除特殊命令）：
- \l：查看所有数据库
- \c 数据库名：切换到指定数据库
- \d：查看当前数据库的所有表
- \d 表名：查看指定表的结构
- \q：退出psql

二、PgSQL核心概念

在开始操作前，先理解PgSQL的核心对象关系：

服务器（Server） → 数据库（Database） → 模式（Schema） → 表（Table）/视图（View）/函数（Function）等

服务器：一个PgSQL服务实例（对应一个进程），可管理多个数据库。
数据库：一个独立的数据集，不同数据库之间数据隔离（如“电商数据库”和“博客数据库”分开）。
模式（Schema）：数据库内的“命名空间”，用于区分同名表（如user表在public模式和admin模式下可共存），默认模式是public。
表（Table）：存储数据的基本单元，由行（记录）和列（字段）组成。

三、基础SQL操作（CRUD）

SQL（结构化查询语言）是操作数据库的标准语言，PgSQL完全兼容标准SQL，同时扩展了部分特性。以下操作以pgAdmin或psql的“查询工具”为例（输入SQL后执行，快捷键F5）。

1. 数据库操作：创建、查看、删除

（1）创建数据库（CREATE DATABASE）

-- 创建名为"my_first_db"的数据库
CREATE DATABASE my_first_db;

注意：超级用户（如postgres）才能创建数据库；数据库名不能包含空格（若需包含，需用双引号包裹，如"my db"）。

（2）查看数据库（\l 或 SELECT）

命令行：\l

SQL查询：

SELECT datname FROM pg_database; -- 查看所有数据库名称

（3）删除数据库（DROP DATABASE）

-- 删除"my_first_db"数据库（谨慎操作，数据不可恢复！）
DROP DATABASE IF EXISTS my_first_db;

IF EXISTS：避免数据库不存在时报错。

2. 表操作：创建、查看、修改、删除

首先切换到目标数据库（如my_first_db）：

命令行：\c my_first_db
pgAdmin：右键点击数据库→「Query Tool」

（1）创建表（CREATE TABLE）

创建表需定义表名和列结构（列名、数据类型、约束）。

示例：创建一个“用户表”users，包含ID、姓名、年龄、邮箱、创建时间：

CREATE TABLE users (
    -- 主键（唯一标识每行数据，自增）
    id SERIAL PRIMARY KEY,
    -- 姓名（非空，字符串长度不超过50）
    name VARCHAR(50) NOT NULL,
    -- 年龄（整数，范围1-120）
    age INT CHECK (age BETWEEN 1 AND 120),
    -- 邮箱（唯一，不允许重复）
    email VARCHAR(100) UNIQUE,
    -- 创建时间（默认值为当前时间）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

关键说明：

数据类型：
- SERIAL：自增整数（PgSQL特有，对应MySQL的AUTO_INCREMENT），适合作为主键。
- VARCHAR(n)：可变长度字符串（n为最大长度），如VARCHAR(50)。
- INT：整数（4字节，范围-2147483648~2147483647）。
- TIMESTAMP：时间戳（包含日期和时间，如2024-05-20 14:30:00）。
约束：
- PRIMARY KEY：主键（唯一且非空），确保每行数据唯一。
- NOT NULL：列值不允许为空。
- CHECK：自定义条件（如年龄范围）。
- UNIQUE：列值不允许重复（如邮箱）。
- DEFAULT：列的默认值（如创建时间默认当前时间）。

（2）查看表（\d 或 SELECT）

命令行：\d（查看当前库所有表）、\d users（查看users表结构）

SQL查询表结构：

SELECT column_name, data_type, is_nullable, column_default 
FROM information_schema.columns 
WHERE table_name = 'users';

（3）修改表（ALTER TABLE）

常见需求：添加列、修改列类型、删除列、添加约束。

示例：

-- 1. 给users表添加"性别"列（允许为空，默认值为'male'）
ALTER TABLE users ADD COLUMN gender VARCHAR(10) DEFAULT 'male';

-- 2. 修改"年龄"列的类型（从INT改为SMALLINT，节省空间）
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

-- 3. 删除"性别"列
ALTER TABLE users DROP COLUMN IF EXISTS gender;

-- 4. 给"姓名"列添加唯一约束（若已有重复数据会报错）
ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (name);

（4）删除表（DROP TABLE）

-- 删除users表（谨慎操作，数据不可恢复！）
DROP TABLE IF EXISTS users;

3. 数据操作：增（INSERT）、查（SELECT）、改（UPDATE）、删（DELETE）

以users表为例，演示基础数据操作。

（1）插入数据（INSERT INTO）

-- 1. 插入完整数据（按列顺序，需包含所有非空且无默认值的列）
INSERT INTO users (name, age, email) 
VALUES ('张三', 25, 'zhangsan@example.com');

-- 2. 插入多条数据（用逗号分隔）
INSERT INTO users (name, age, email) 
VALUES 
    ('李四', 30, 'lisi@example.com'),
    ('王五', 28, 'wangwu@example.com');

-- 3. 插入部分数据（未指定的列需有默认值或允许为空）
INSERT INTO users (name, age) 
VALUES ('赵六', 22); -- email为空，created_at用默认当前时间

（2）查询数据（SELECT）

查询是最常用的操作，支持筛选、排序、分页、关联等复杂逻辑。

基础查询：

-- 1. 查询所有列的所有数据（*表示所有列，不推荐大量数据场景）
SELECT * FROM users;

-- 2. 查询指定列（name、age、email）
SELECT name, age, email FROM users;

-- 3. 筛选数据（WHERE条件，如年龄>25）
SELECT name, age FROM users WHERE age > 25;

-- 4. 排序（ORDER BY，ASC升序，DESC降序，默认ASC）
SELECT name, age FROM users ORDER BY age DESC;

-- 5. 分页（LIMIT限制条数，OFFSET偏移量，常用于列表页）
SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 0; -- 第1页，2条/页
SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 2; -- 第2页，2条/页

进阶查询（示例）：

-- 1. 去重（DISTINCT，如查询所有不重复的年龄）
SELECT DISTINCT age FROM users;

-- 2. 聚合函数（COUNT计数、SUM求和、AVG平均等）
SELECT 
    COUNT(*) AS total_users, -- 总用户数
    AVG(age) AS avg_age      -- 平均年龄
FROM users;

-- 3. 分组（GROUP BY，如按年龄分组统计人数）
SELECT age, COUNT(*) AS user_count 
FROM users 
GROUP BY age;

（3）修改数据（UPDATE）

-- 修改"张三"的邮箱（必须加WHERE条件，否则会修改所有数据！）
UPDATE users 
SET email = 'zhangsan_new@example.com' 
WHERE name = '张三';

-- 同时修改多个列（用逗号分隔）
UPDATE users 
SET age = 26, email = 'zhangsan_26@example.com' 
WHERE id = 1; -- 推荐用主键ID筛选，避免误改

（4）删除数据（DELETE）

-- 删除"赵六"的数据（必须加WHERE条件，否则会删除所有数据！）
DELETE FROM users WHERE name = '赵六';

-- 按主键删除（最安全，唯一确定一行）
DELETE FROM users WHERE id = 4;

四、进阶特性（入门后可探索）

PgSQL的强大之处在于丰富的进阶特性，适合复杂业务场景：

复杂数据类型：支持数组（如INT[]）、JSON/JSONB（存储JSON数据，支持索引）、枚举（ENUM）、地理信息类型（GEOMETRY）等。

示例：创建带数组列的表

CREATE TABLE student (
 id SERIAL PRIMARY KEY,
 name VARCHAR(50) NOT NULL,
 scores INT[] -- 存储多门课程成绩，如'{80, 90, 85}'
);
INSERT INTO student (name, scores) VALUES ('小明', '{80, 90, 85}');

事务（Transaction）：确保一组操作要么全部成功，要么全部失败（ACID特性），用BEGIN、COMMIT、ROLLBACK控制。

示例：

BEGIN; -- 开始事务
INSERT INTO users (name, age) VALUES ('钱七', 35);
UPDATE users SET age = 31 WHERE name = '李四';
COMMIT; -- 提交事务（所有操作生效）
-- ROLLBACK; -- 若出错，回滚事务（所有操作撤销）

自定义函数/存储过程：用PL/pgSQL（PgSQL自带的过程语言）编写复杂逻辑，复用代码。
索引：优化查询性能，支持B-tree、Hash、GIN（适合数组/JSON）等多种索引类型。
- 示例：给users表的email列加B-tree索引
```
CREATE INDEX idx_users_email ON users (email);
```

五、学习资源推荐

官方文档：最权威的资料，包含所有特性细节
PostgreSQL 16 Official Documentation
中文教程：
- PostgreSQL 中文文档（非官方，适合入门）
- 菜鸟教程 - PostgreSQL（基础语法快速查阅）
实战练习：
- LeetCode数据库题目（用PgSQL提交，练习SQL逻辑）
- 搭建个人项目（如博客、TodoList），用PgSQL存储数据

通过以上步骤，您已掌握PgSQL的基础操作。建议从创建一个简单表、插入测试数据开始，逐步尝试查询、修改等操作，再探索进阶特性，逐步熟悉这款强大的数据库。