PostgreSQL (pgsql) 教程：新手快速入门指南

2025-3-22

PostgreSQL (pgsql) 教程：新手快速入门指南

PostgreSQL，通常简称为 pgsql，是一个强大、开源、对象关系型数据库系统 (ORDBMS)。它以其可靠性、数据完整性和对 SQL 标准的广泛支持而闻名。PostgreSQL 在各种规模的项目中都有应用，从小型个人项目到大型企业级应用。本教程旨在为初学者提供一个全面的 PostgreSQL 入门指南，涵盖安装、基本配置、SQL 基础、数据类型、常用操作以及一些进阶概念。

一、PostgreSQL 的优势与特点

在深入学习之前，了解 PostgreSQL 的优势和特点有助于你更好地理解为什么选择它：

开源和免费： PostgreSQL 采用高度自由的 PostgreSQL 许可证，你可以免费使用、修改和分发它，无需担心许可费用。
强大的功能： PostgreSQL 支持高级数据类型（如数组、JSON、hstore）、全文搜索、地理空间数据 (PostGIS 扩展)、事务、并发控制等。
数据完整性： PostgreSQL 强调数据完整性，提供了各种约束（主键、外键、唯一约束、检查约束）来确保数据的准确性和一致性。
可扩展性： PostgreSQL 可以处理大量数据和高并发请求，并支持多种扩展方式，如复制、分区和并行查询。
活跃的社区： PostgreSQL 拥有一个庞大且活跃的社区，提供丰富的文档、教程和支持。
跨平台： PostgreSQL 可以在各种操作系统上运行，包括 Linux、Windows、macOS、BSD 等。
ACID特性: PostgreSQL完全支持事务的ACID属性（原子性、一致性、隔离性、持久性），保证数据操作的可靠性。

二、安装与配置

PostgreSQL 的安装过程因操作系统而异。以下是在几种常见操作系统上的安装步骤：

1. Linux (Ubuntu/Debian)

```bash

更新软件包列表

sudo apt update

安装 PostgreSQL

sudo apt install postgresql postgresql-contrib

启动 PostgreSQL 服务

sudo systemctl start postgresql

检查服务状态

sudo systemctl status postgresql

设置 PostgreSQL 开机自启

sudo systemctl enable postgresql
```

2. Linux (CentOS/RHEL/Fedora)

```bash

安装 PostgreSQL (版本号可能需要根据实际情况修改)

sudo yum install postgresql-server postgresql-contrib

初始化数据库

sudo /usr/bin/postgresql-setup initdb

启动 PostgreSQL 服务

sudo systemctl start postgresql

检查服务状态

sudo systemctl status postgresql

设置 PostgreSQL 开机自启

sudo systemctl enable postgresql
```

3. Windows

从 PostgreSQL 官方网站下载 Windows 安装程序：https://www.postgresql.org/download/windows/
运行安装程序，按照提示进行操作。
在安装过程中，你需要设置超级用户 (postgres) 的密码。请务必记住这个密码！
安装程序会自动将 PostgreSQL 安装为 Windows 服务。

4. macOS

使用 Homebrew (推荐)：

```bash
# 安装 Homebrew (如果尚未安装)
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# 安装 PostgreSQL
brew install postgresql

# 启动 PostgreSQL 服务
brew services start postgresql

# (可选) 设置 PostgreSQL 开机自启
brew services run postgresql
```
* 使用Postgres.app：
* 从Postgres.app官网下载并安装：https://postgresapp.com/
* Postgres.app是一个独立的PostgreSQL安装包，包含图形化管理工具。

配置 (所有平台通用)

安装完成后，通常需要进行一些基本的配置：

postgres 用户： PostgreSQL 安装后会创建一个名为 postgres 的操作系统用户和一个同名的数据库超级用户。
psql 命令行工具： psql 是 PostgreSQL 的命令行客户端，用于连接数据库、执行 SQL 命令等。

```bash

以 postgres 用户身份连接到默认数据库 (postgres)

sudo -u postgres psql
```

或者，在 Windows 上，你可以从开始菜单中找到 "SQL Shell (psql)"。
修改 postgres 用户密码 (强烈建议)：

sql -- 在 psql 中执行 ALTER USER postgres WITH PASSWORD 'your_new_password';
pg_hba.conf 文件： 这个文件控制客户端的身份验证。它位于 PostgreSQL 数据目录中（通常是 /var/lib/postgresql/<version>/main 或 C:\Program Files\PostgreSQL\<version>\data）。你可能需要修改这个文件来允许远程连接或更改身份验证方法。 谨慎修改此文件，错误的配置可能导致安全问题或无法连接数据库！
postgresql.conf 文件： 这是 PostgreSQL 的主要配置文件，包含各种服务器设置，如监听地址、端口、内存分配等。

三、SQL 基础

PostgreSQL 使用 SQL (结构化查询语言) 作为其主要的交互语言。以下是一些基本的 SQL 命令：

1. 数据库操作

创建数据库：

sql CREATE DATABASE mydatabase;
删除数据库：

sql DROP DATABASE mydatabase;
注意：删除数据库是不可逆的操作，会永久删除数据库及其所有数据！
连接到数据库：

sql \c mydatabase; -- 在 psql 中使用

或者使用连接字符串（在程序中或 psql 命令行中使用）：

bash psql -h localhost -p 5432 -U postgres -d mydatabase
- -h: 主机名 (默认 localhost)
- -p: 端口 (默认 5432)
- -U: 用户名
- -d: 数据库名

2. 表操作

创建表：

sql CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
* SERIAL: 自动递增的整数类型，通常用作主键。
* PRIMARY KEY: 主键约束，确保每行数据的唯一性。
* VARCHAR(n): 可变长度字符串，最多 n 个字符。
* UNIQUE: 唯一约束，确保该列的值不重复。
* NOT NULL: 非空约束，确保该列的值不为空。
* TIMESTAMP: 时间戳类型。
* DEFAULT: 默认值。
查看表结构:
sql \d users
删除表：

sql DROP TABLE users;
修改表结构 (ALTER TABLE)：

```sql
-- 添加列
ALTER TABLE users ADD COLUMN age INT;

-- 删除列
ALTER TABLE users DROP COLUMN age;

-- 修改列的数据类型
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

-- 添加约束
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
```

3. 数据操作 (CRUD)

插入数据 (INSERT)：

```sql
INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');

-- 插入多行
INSERT INTO users (username, email) VALUES
('jane_doe', '[email protected]'),
('peter_pan', '[email protected]');
```
查询数据 (SELECT)：

```sql
-- 查询所有列
SELECT * FROM users;

-- 查询特定列
SELECT username, email FROM users;

-- WHERE 子句 (过滤条件)
SELECT * FROM users WHERE username = 'john_doe';

-- ORDER BY 子句 (排序)
SELECT * FROM users ORDER BY created_at DESC; -- 按创建时间降序

-- LIMIT 子句 (限制结果数量)
SELECT * FROM users LIMIT 10;

-- LIKE 子句 (模糊匹配)
SELECT * FROM users WHERE username LIKE 'john%'; -- 以 john 开头的用户名

-- IN 子句
SELECT * FROM users WHERE id IN (1, 2, 3);
-- BETWEEN ... AND 子句
SELECT * from users WHERE id BETWEEN 1 AND 5;

-- 使用聚合函数
SELECT COUNT(*) FROM users; -- 计算总行数
SELECT AVG(age) FROM users; -- 计算平均年龄
SELECT MAX(age),MIN(age) FROM users;

-- GROUP BY 子句 (分组)
SELECT age, COUNT(*) FROM users GROUP BY age;
```
更新数据 (UPDATE)：

sql UPDATE users SET email = '[email protected]' WHERE id = 1;
删除数据 (DELETE)：

sql DELETE FROM users WHERE id = 1;

四、常用数据类型

PostgreSQL 支持多种数据类型，以下是一些常用的：

数值类型：
- SMALLINT: 小范围整数 (-32768 到 +32767)
- INTEGER (或 INT): 常用整数类型 (-2147483648 到 +2147483647)
- BIGINT: 大范围整数 (-9223372036854775808 到 +9223372036854775807)
- REAL (或 FLOAT4): 单精度浮点数
- DOUBLE PRECISION (或 FLOAT8): 双精度浮点数
- NUMERIC(precision, scale) (或 DECIMAL(precision, scale)): 精确数值类型，precision 是总位数，scale 是小数位数
字符串类型：
- VARCHAR(n): 可变长度字符串，最多 n 个字符
- CHAR(n): 固定长度字符串，n 个字符（不足部分用空格填充）
- TEXT: 不限长度字符串
日期和时间类型：
- DATE: 日期 (年、月、日)
- TIME: 时间 (时、分、秒)
- TIMESTAMP: 时间戳 (日期和时间)
- INTERVAL: 时间间隔
布尔类型：
- BOOLEAN: true 或 false
JSON类型
JSON: 存储JSON数据
JSONB: 以二进制格式存储的JSON数据，支持索引，查询更快
数组类型
任何基本数据类型都可以用来定义数组。
sql CREATE TABLE my_table ( id SERIAL PRIMARY KEY, name VARCHAR(100), scores INTEGER[] --整数数组 tags TEXT[] --文本数组 )
枚举类型
```sql
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (
name text,
current_mood mood
);
```

五、常用操作与技巧

连接 (JOIN)： 用于将多个表的数据组合在一起。

```sql
-- 创建 orders 表
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id), -- 外键
product_name VARCHAR(100),
order_date DATE
);
--插入数据
INSERT INTO orders (user_id,product_name,order_date) VALUES(1,'apple','2024-1-1');
INSERT INTO orders (user_id,product_name,order_date) VALUES(2,'banana','2024-1-5');

-- 内连接 (INNER JOIN)
SELECT
users.username,
orders.product_name,
orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- 左外连接 (LEFT JOIN)
SELECT
users.username,
orders.product_name
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- 右外连接 (RIGHT JOIN)
SELECT
users.username,
orders.product_name
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- 全外连接 (FULL OUTER JOIN),PostgreSQL特有
SELECT
users.username,
orders.product_name
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;
```
子查询 (Subquery)： 在一个查询中嵌套另一个查询。

sql -- 查询年龄大于平均年龄的用户 SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);
事务 (Transaction)： 用于将多个 SQL 操作作为一个原子单元执行，要么全部成功，要么全部失败。

```sql
BEGIN; -- 开始事务

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

COMMIT; -- 提交事务 (如果一切正常)
-- 或者
ROLLBACK; -- 回滚事务 (如果发生错误)
```
索引 (Index)： 用于加速查询。

```sql
-- 在 username 列上创建索引
CREATE INDEX idx_username ON users (username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);
```
视图 (View)： 一个虚拟的表，其内容由查询定义。

```sql
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

-- 查询视图
SELECT * FROM active_users;
```
函数 (Function)： PostgreSQL 允许你创建自定义函数。

```sql
CREATE FUNCTION add(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT add(5, 3);
```
触发器 (Trigger)： 在特定事件（如 INSERT、UPDATE、DELETE）发生时自动执行的函数。

```sql
-- 创建一个触发器函数
CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
-- 在这里记录更改，例如插入到另一个表
INSERT INTO user_audit_log(user_id, old_data, new_data, changed_at)
VALUES (OLD.id, OLD., NEW., NOW());
RETURN NEW; -- 对于 AFTER 触发器，通常返回 NEW
END;
$$ LANGUAGE plpgsql;

-- 创建一个触发器
CREATE TRIGGER user_update_trigger
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_changes();

```

六、进阶概念

存储过程 (Stored Procedure)： 类似于函数，但可以执行更复杂的操作，并且没有返回值（或者返回多个结果集）。 PostgreSQL 11 及更高版本支持存储过程。
分区 (Partitioning)： 将大表分成多个较小的、更易于管理的片段。
复制 (Replication)： 将数据从一个 PostgreSQL 服务器复制到另一个服务器，以实现高可用性、负载均衡或灾难恢复。
全文搜索 (Full-Text Search)： 使用 tsvector 和 tsquery 数据类型进行高效的文本搜索。
扩展 (Extension)： PostgreSQL 支持各种扩展，可以添加额外的功能，如 PostGIS (地理空间数据)、hstore (键值对存储) 等。

七、常用工具

pgAdmin： 一个流行的图形化 PostgreSQL 管理工具，提供了一个直观的界面来管理数据库、执行查询、查看对象等。
DBeaver： 一个通用的数据库客户端，支持多种数据库系统，包括 PostgreSQL。
DataGrip： JetBrains 出品的数据库 IDE，功能强大，但需要付费。

八、学习资源

PostgreSQL 官方文档： https://www.postgresql.org/docs/ (权威、全面)
PostgreSQL Tutorial： https://www.postgresqltutorial.com/ (适合初学者)
Learn PostgreSQL Tutorial - Full Course for Beginners (freeCodeCamp.org on YouTube)： 一个很好的视频教程。

总结

本教程提供了 PostgreSQL 的全面入门指南，涵盖了安装、配置、SQL 基础、常用操作以及一些进阶概念。通过学习本教程，你应该能够：

安装和配置 PostgreSQL。
使用 psql 命令行工具连接和管理数据库。
编写基本的 SQL 查询来创建、读取、更新和删除数据。
理解 PostgreSQL 的常用数据类型。
使用连接、子查询、事务等高级 SQL 特性。
了解索引、视图、函数和触发器等概念。

PostgreSQL 是一个功能强大且灵活的数据库系统，值得深入学习和掌握。希望本教程能帮助你快速入门！记住，实践是最好的学习方式，多多动手操作，你会更快地掌握 PostgreSQL。

作者：admin

链接：https://hostlocvps.com/2025/03/22/postgresql-pgsql-%e6%95%99%e7%a8%8b%ef%bc%9a%e6%96%b0%e6%89%8b%e5%bf%ab%e9%80%9f%e5%85%a5%e9%97%a8%e6%8c%87%e5%8d%97/

文章版权归作者所有，未经允许请勿转载。

THE END

提升 MySQL 查询效率：CASE WHEN 的妙用

<<上一篇

UDP协议是什么？一文读懂UDP工作原理

下一篇>>