51学通信

PostgreSQL数据库技术 第 1 篇：数据库入门与SQL基础

34分钟阅读

PostgreSQL数据库技术 第 1 篇：数据库入门与SQL基础

本篇属于「PostgreSQL数据库技术」学习系列，建议按顺序阅读以获得最佳学习效果。

全文摘要

本文将带你从零开始学习PostgreSQL数据库的基础知识，帮助你建立扎实的数据库技术基础。你将学到数据库系统的核心架构、PostgreSQL的安装与配置、SQL数据类型的选择与应用、完整的CRUD操作，以及条件查询、数据排序和分页等核心技能。通过本文学习，你将能够独立搭建数据库环境，设计并管理数据表，编写实用的SQL查询语句。

学习目标

阅读完本文后，你将能够：

  • 理解数据库系统架构：掌握客户端-服务器模型和PostgreSQL的进程结构
  • 独立安装配置：完成PostgreSQL的安装和基本配置
  • 管理数据库对象：创建数据库、表，理解Schema的概念
  • 选择合适的数据类型：根据业务需求选择数值、字符串、日期、JSON等数据类型
  • 执行CRUD操作：熟练掌握数据的增删改查操作
  • 编写实用查询：使用条件过滤、排序、分页等功能处理数据

一、数据库系统架构概述

在深入学习PostgreSQL之前，我们首先需要理解数据库系统的基本架构。这有助于我们理解数据库如何工作，以及为什么需要特定的配置和优化。

客户端-服务器模型

PostgreSQL采用经典的客户端-服务器（Client-Server）架构模型。这个模型将数据处理分为两个独立的进程：客户端进程和服务器进程。

客户端进程：运行应用程序代码，向服务器发送请求，接收并显示服务器返回的结果。客户端可以是命令行工具（如psql）、图形界面工具（如pgAdmin）、Web应用程序，或任何使用数据库驱动库的应用程序。

服务器进程：管理数据库文件，接受客户端的连接请求，处理客户端发送的SQL语句，并将结果返回给客户端。PostgreSQL服务器进程通常被称为”postgres”进程。

flowchart TB
    subgraph Client[客户端层]
        direction TB
        C1[命令行工具 psql]
        C2[图形界面 pgAdmin]
        C3[Web应用/移动应用]
    end

    subgraph Server[服务器层]
        direction TB
        S1[Postmaster 守护进程]
        S2[Postgres 服务器进程]
        S3[共享内存区]
    end

    subgraph Storage[存储层]
        direction TB
        D1[数据文件]
        D2[配置文件]
        D3[WAL日志]
    end

    C1 -->|网络连接| S2
    C2 -->|网络连接| S2
    C3 -->|网络连接| S2

    S1 -->|管理| S2
    S2 <-->|读写| S3
    S2 <-->|管理| D1
    S2 <-->|读取| D2
    S2 <-->|写入| D3

    style Client fill:#e3f2fd
    style Server fill:#fff9c4
    style Storage fill:#c8e6c9

图表讲解：这张图展示了PostgreSQL数据库系统的三层架构——这是理解数据库工作方式的基础框架。

客户端层包含各种类型的数据库客户端：psql是PostgreSQL自带的命令行工具，功能强大但需要熟悉命令；pgAdmin是图形化管理工具，适合初学者和日常管理；Web应用和移动应用通过数据库驱动库与数据库通信，这是最常见的使用场景。

服务器层是PostgreSQL的核心。Postmaster守护进程负责监听新的连接请求，为每个客户端连接创建一个独立的服务器进程。每个服务器进程独立处理客户端的SQL请求，访问共享内存区中的数据。共享内存区包含缓冲区缓存（存储从磁盘读取的数据页）和各种系统表。

存储层包含数据库的实际文件：数据文件存储表和索引的实际数据，配置文件（postgresql.conf、pg_hba.conf等）控制服务器行为，WAL（Write-Ahead Log）日志用于事务恢复和复制。

PostgreSQL进程结构

PostgreSQL采用多进程架构，每个客户端连接对应一个独立的服务器进程。这种设计的好处是进程隔离性好，一个进程崩溃不会影响其他进程；缺点是进程创建和上下文切换的开销较大。

Postmaster进程：PostgreSQL的主守护进程，负责以下任务：

  • 监听客户端连接请求（默认端口5432）
  • 为每个新连接创建一个postgres服务器进程
  • 管理共享内存和信号量
  • 执行崩溃后的自动恢复
  • 执行日常维护任务（如自动清理）

Postgres服务器进程：处理具体客户端请求的后端进程。每个连接对应一个postgres进程，该进程：

  • 解析客户端发送的SQL语句
  • 生成查询执行计划
  • 执行查询并返回结果
  • 管理客户端会话状态

辅助进程：除Postmaster和postgres进程外，PostgreSQL还运行多个辅助进程：

  • WAL写入进程（wal writer）：将WAL缓冲区的内容写入磁盘
  • 检查点进程（checkpointer）：定期执行检查点操作
  • 自动清理进程（autovacuum launcher）：清理死元组和回收空间
  • 统计收集进程（stats collector）：收集数据库统计信息

二、PostgreSQL的安装与配置

掌握PostgreSQL的安装和基本配置是使用数据库的第一步。不同的操作系统有不同的安装方法，但核心概念是相通的。

在Linux系统上安装PostgreSQL

使用包管理器安装（推荐）：

Debian/Ubuntu系统使用apt：

# 更新包列表
sudo apt update
 
# 安装PostgreSQL
sudo apt install postgresql postgresql-contrib

RedHat/CentOS系统使用yum或dnf：

# 安装PostgreSQL
sudo yum install postgresql-server
# 或
sudo dnf install postgresql-server

从源代码安装（高级用户）： 从源代码安装可以自定义编译选项，获得最新版本，但过程更复杂。基本步骤包括：安装依赖包、下载源代码、配置编译选项、编译安装、初始化数据库集群、配置自动启动。

在Windows系统上安装PostgreSQL

Windows系统提供图形化的安装程序：

  1. 从PostgreSQL官网下载安装程序（.exe文件）
  2. 运行安装程序，按照向导完成安装
  3. 选择安装路径（默认：C:\Program Files\PostgreSQL\16）
  4. 设置数据目录（默认：C:\Program Files\PostgreSQL\16\data）
  5. 设置超级用户密码（postgres用户的密码，请记住这个密码）
  6. 选择端口号（默认：5432）
  7. 选择要安装的组件（pgAdmin、Stack Builder等）

安装完成后，PostgreSQL作为Windows服务自动启动。

使用Docker运行PostgreSQL

Docker是现代开发和部署环境的首选方式：

# 拉取PostgreSQL镜像
docker pull postgres:16
 
# 运行PostgreSQL容器
docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -v /my/data:/var/lib/postgresql/data \
  -d postgres:16

Docker方式的优点是环境隔离、快速部署、便于迁移。

基本配置文件

PostgreSQL的主要配置文件位于数据目录中：

postgresql.conf：核心配置文件，包含服务器运行参数。重要配置项：

  • listen_addresses：监听的网络地址（默认：localhost，改为’*‘允许远程连接）
  • port：监听端口（默认：5432）
  • max_connections：最大并发连接数（默认：100）
  • shared_buffers：共享内存缓冲区大小
  • effective_cache_size：系统可用缓存大小估算
  • work_mem：排序和哈希操作使用的内存大小
  • maintenance_work_mem：维护操作的内存大小

pg_hba.conf：客户端认证配置文件，控制哪些客户端可以连接数据库，以及使用什么认证方法。认证方法包括：

  • trust：无条件信任（不推荐用于生产环境）
  • password：使用密码认证
  • md5：使用MD5加密的密码认证
  • scram-sha-256：使用更安全的SCRAM-SHA-256认证（推荐）
  • cert：使用客户端证书认证

pg_ident.conf：映射操作系统用户与数据库用户，用于ident认证方法。

连接到数据库

使用psql命令行工具连接数据库：

# 连接到本地默认数据库（postgres）
psql -U postgres
 
# 连接到特定数据库
psql -U postgres -d mydb
 
# 连接到远程数据库
psql -h 192.168.1.100 -p 5432 -U postgres -d mydb

psql是交互式命令行工具，常用命令：

  • \l：列出所有数据库
  • \c dbname：切换到指定数据库
  • \dt：列出当前数据库的所有表
  • \d tablename：显示表结构
  • \q：退出psql

三、数据库与Schema管理

在PostgreSQL中，数据是按层级组织的：数据库集群→数据库→Schema→数据库对象（表、视图等）。理解这个层级结构对于组织数据非常重要。

数据库集群的概念

数据库集群（Database Cluster）是PostgreSQL管理的最高层级单位，不是一个集群的多台服务器，而是一组数据库的集合。一个数据库集群包含：

  • 一个全局数据目录
  • 多个数据库
  • 共享的系统表（pg_catalog）
  • 配置文件
  • WAL日志和事务日志

一个PostgreSQL服务器实例管理一个数据库集群。

数据库的基本操作

创建数据库：

-- 使用CREATE DATABASE语句
CREATE DATABASE myapp;
 
-- 创建数据库时指定编码和所有者
CREATE DATABASE myapp
  ENCODING 'UTF8'
  OWNER appuser;
 
-- 从模板数据库创建（template0是纯净模板）
CREATE DATABASE myapp TEMPLATE template0;

查看数据库列表：

-- 在psql中使用\l命令
\l
 
-- 或使用SQL查询
SELECT datname, pg_encoding_to_char(encoding) as encoding,
       pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY datname;

切换当前数据库：

-- 在psql中使用\c命令
\c myapp
 
-- SQL方式（需要重新连接）

删除数据库：

-- 删除空数据库
DROP DATABASE myapp;
 
-- 强制删除数据库及其包含的所有对象
DROP DATABASE myapp WITH (FORCE);

Schema的概念与管理

Schema是数据库内部的命名空间，用于组织数据库对象。Schema类似于文件系统中的目录，可以避免对象名称冲突，并实现逻辑分组。

默认Schema：

  • public：默认Schema，创建对象时如果不指定Schema，则创建在public中
  • pg_catalog：系统Schema，包含系统表和内置函数
  • pg_toast：存储TOAST表（大对象的存储）

Schema的基本操作：

-- 创建Schema
CREATE SCHEMA myschema;
 
-- 在指定Schema中创建表
CREATE TABLE myschema.users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
 
-- 设置搜索路径（决定查找对象的顺序）
SET search_path TO myschema, public;
 
-- 查看当前搜索路径
SHOW search_path;
 
-- 查看所有Schema
SELECT schema_name FROM information_schema.schemata;

Schema权限管理：

-- 授予用户在Schema中创建对象的权限
GRANT CREATE ON SCHEMA myschema TO appuser;
 
-- 授予用户使用Schema的权限
GRANT USAGE ON SCHEMA myschema TO appuser;
 
-- 授予用户对Schema中所有对象的权限
GRANT ALL ON ALL TABLES IN SCHEMA myschema TO appuser;

四、SQL数据类型详解

选择合适的数据类型是数据库设计的基础。PostgreSQL提供了丰富的数据类型，选择正确的类型可以提高存储效率、查询性能和数据完整性。

数值类型

整数类型：

类型存储大小范围适用场景
smallint2字节-32768 到 +32767小范围计数、状态码
integer4字节-2147483648 到 +2147483647常规整数（推荐）
bigint8字节非常大的整数范围大ID、计数器
CREATE TABLE counters (
    level smallint,
    quantity integer,
    total bigint
);

精确数值类型：

-- NUMERIC(p, s)：精度p，小数位s
NUMERIC(10, 2)  -- 总共10位，其中2位小数
 
-- 示例
CREATE TABLE products (
    price NUMERIC(10, 2) NOT NULL,  -- 8位整数+2位小数
    quantity NUMERIC(8, 3)           -- 5位整数+3位小数
);

浮点类型：

-- real：单精度浮点，6位精度
-- double precision：双精度浮点，15位精度
 
CREATE TABLE measurements (
    temperature real,           -- 温度测量
    weight double precision     -- 重量测量
);

字符串类型

类型特点适用场景
char(n)定长字符串，不足填充空格固定长度代码（如ISBN）
varchar(n)变长字符串，最大长度n一般文本字段
text变长字符串，无长度限制长文本、文章内容
CREATE TABLE documents (
    isbn char(13),               -- ISBN标准13位
    title varchar(200) NOT NULL, -- 标题最多200字符
    content text                 -- 长文本内容
);

字符串函数：

-- 字符串拼接
SELECT 'Hello, ' || 'World!' AS greeting;
 
-- 字符串长度
SELECT length(name), char_length(name) FROM users;
 
-- 大小写转换
SELECT upper(email), lower(name) FROM users;
 
-- 字符串截取
SELECT substring(title FROM 1 FOR 10) FROM articles;
 
-- 去除空格
SELECT trim(name) FROM users;

日期时间类型

类型描述存储大小
timestamp日期和时间，不带时区8字节
timestamptz日期和时间，带时区8字节
date日期（年月日）4字节
time时间（时分秒）8字节
interval时间间隔16字节
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    event_date DATE NOT NULL,
    event_time TIME NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    duration INTERVAL
);
 
-- 插入数据
INSERT INTO events (name, event_date, event_time, duration)
VALUES (
    '产品发布会',
    '2024-03-15',
    '14:30:00',
    INTERVAL '2 hours 30 minutes'
);
 
-- 日期函数
SELECT
    event_date,
    event_date + INTERVAL '7 days' AS next_week,
    CURRENT_DATE,
    CURRENT_TIME,
    NOW()
FROM events;

布尔类型

PostgreSQL使用boolean类型，有三个取值：TRUE、FALSE、NULL。

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    completed BOOLEAN DEFAULT FALSE
);
 
-- 插入数据
INSERT INTO tasks (title, completed)
VALUES ('完成文档', TRUE);
 
-- 布尔查询
SELECT * FROM tasks WHERE completed = TRUE;
SELECT * FROM tasks WHERE NOT completed;

JSON类型

PostgreSQL提供两种JSON数据类型：

类型特点适用场景
json存储文本格式的JSON，输入时检查格式需要保留原始格式的场景
jsonb存储分解后的二进制格式，支持索引需要查询和操作JSON内容
CREATE TABLE products_json (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB  -- 推荐使用jsonb
);
 
-- 插入JSON数据
INSERT INTO products_json (name, attributes)
VALUES (
    '智能手机',
    '{
        "brand": "TechCorp",
        "price": 2999,
        "specs": {
            "screen": "6.5 inch",
            "ram": "8GB",
            "storage": "256GB"
        },
        "colors": ["black", "white", "blue"],
        "available": true
    }'::jsonb
);
 
-- JSON查询
SELECT
    name,
    attributes->'brand' AS brand,           -- 获取JSON对象的字段
    attributes->>'price' AS price,          -- 获取JSON对象的文本值
    attributes->'specs'->>'screen' AS screen,
    attributes->'colors' AS colors           -- 获取JSON数组
FROM products_json;
 
-- JSON条件查询
SELECT * FROM products_json
WHERE attributes->>'brand' = 'TechCorp';
 
SELECT * FROM products_json
WHERE attributes->'available' = 'true'::jsonb;
 
-- JSON更新操作
UPDATE products_json
SET attributes = jsonb_set(
    attributes,
    '{price}',
    '2799'::jsonb
)
WHERE id = 1;

五、表的创建与管理

表是数据库中最基本的数据存储单元。创建结构良好的表是数据库设计的关键。

创建表的基本语法

CREATE TABLE table_name (
    column_name data_type [column_constraint],
    ...
    [table_constraint]
);

创建用户表示例：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    is_active BOOLEAN DEFAULT TRUE
);

约束详解

PRIMARY KEY（主键）：唯一标识表中的每一行

-- 列级约束
CREATE TABLE users (
    id SERIAL PRIMARY KEY
);
 
-- 表级约束（多个字段组成复合主键）
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

FOREIGN KEY（外键）：建立表之间的引用关系

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount NUMERIC(10, 2)
);
 
-- 外键约束选项
-- ON DELETE CASCADE：删除父记录时自动删除子记录
-- ON DELETE SET NULL：删除父记录时子记录的外键设为NULL
-- ON DELETE RESTRICT：删除父记录时如果有子记录则阻止删除
-- ON DELETE SET DEFAULT：删除父记录时子记录的外键设为默认值

UNIQUE约束：确保字段值的唯一性

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,  -- 列级约束
    email VARCHAR(255),
    CONSTRAINT email_unique UNIQUE (email)  -- 表级约束
);

CHECK约束：自定义验证规则

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    quantity INTEGER DEFAULT 0,
    CHECK (price > 0),
    CHECK (quantity >= 0)
);
 
-- 多字段约束
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CHECK (end_date >= start_date)
);

NOT NULL约束：确保字段不为空

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,  -- 不允许NULL
    full_name TEXT  -- 允许NULL
);

修改表结构

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
 
-- 删除列
ALTER TABLE users DROP COLUMN phone;
 
-- 修改列的数据类型
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);
 
-- 添加约束
ALTER TABLE users ADD CONSTRAINT username_min_length
    CHECK (char_length(username) >= 3);
 
-- 重命名列
ALTER TABLE users RENAME COLUMN full_name TO name;
 
-- 重命名表
ALTER TABLE users RENAME TO app_users;

查看表结构

-- 在psql中使用\d命令
\d users
 
-- 或使用信息模式查询
SELECT
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'users';

六、CRUD操作详解

CRUD是Create（创建）、Read（读取）、Update（更新）、Delete（删除）的缩写，是数据库操作的四种基本操作。

INSERT语句：插入数据

基本语法：

-- 插入完整行（指定所有列）
INSERT INTO users (username, email, password_hash, full_name)
VALUES ('john_doe', '[email protected]', 'hash123', 'John Doe');
 
-- 插入部分列（其他列使用默认值）
INSERT INTO users (username, email, password_hash)
VALUES ('jane_doe', '[email protected]', 'hash456');
 
-- 插入多行
INSERT INTO users (username, email, password_hash) VALUES
    ('user1', '[email protected]', 'hash1'),
    ('user2', '[email protected]', 'hash2'),
    ('user3', '[email protected]', 'hash3');

从查询结果插入：

-- 从其他表复制数据
INSERT INTO archived_users (username, email, full_name)
SELECT username, email, full_name
FROM users
WHERE created_at < '2023-01-01';
 
-- 创建并插入（CTE方式）
WITH new_users AS (
    SELECT 'new_user' AS username, '[email protected]' AS email
)
INSERT INTO users (username, email)
SELECT * FROM new_users;

INSERT … ON CONFLICT（PostgreSQL特色）：

-- 如果冲突则更新（UPSERT）
INSERT INTO users (username, email, password_hash)
VALUES ('john_doe', '[email protected]', 'newhash')
ON CONFLICT (username)
DO UPDATE SET
    email = EXCLUDED.email,
    password_hash = EXCLUDED.password_hash,
    updated_at = NOW();
 
-- 如果冲突则什么都不做
INSERT INTO users (username, email)
VALUES ('john_doe', '[email protected]')
ON CONFLICT (username)
DO NOTHING;

SELECT语句：查询数据

基本查询：

-- 查询所有列
SELECT * FROM users;
 
-- 查询指定列
SELECT username, email, full_name FROM users;
 
-- 使用表达式和别名
SELECT
    username,
    upper(email) AS email_upper,
    char_length(username) AS name_length
FROM users;

WHERE子句：条件过滤：

-- 比较运算符
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE price BETWEEN 100 AND 500;
 
-- 逻辑运算符
SELECT * FROM users WHERE is_active = TRUE AND age >= 18;
SELECT * FROM users WHERE city = 'Beijing' OR city = 'Shanghai';
 
-- 模式匹配
SELECT * FROM users WHERE username LIKE 'john%';  -- 以john开头
SELECT * FROM users WHERE email LIKE '%@example.com';
 
-- IN操作符
SELECT * FROM users WHERE city IN ('Beijing', 'Shanghai', 'Guangzhou');
 
-- NULL判断
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

ORDER BY子句：排序结果：

-- 升序排序（默认）
SELECT * FROM users ORDER BY username;
 
-- 降序排序
SELECT * FROM users ORDER BY created_at DESC;
 
-- 多列排序
SELECT * FROM users
ORDER BY is_active DESC, created_at DESC;
 
-- 使用表达式排序
SELECT * FROM products
ORDER BY price * quantity DESC;

LIMIT和OFFSET：分页查询：

-- 返回前10行
SELECT * FROM users LIMIT 10;
 
-- 跳过前5行，返回接下来的10行（第二页）
SELECT * FROM users LIMIT 10 OFFSET 5;
 
-- 计算分页（每页20行，第3页）
SELECT * FROM users
LIMIT 20
OFFSET (3 - 1) * 20;

UPDATE语句：更新数据

-- 更新单行
UPDATE users
SET email = '[email protected]'
WHERE id = 1;
 
-- 更新多列
UPDATE users
SET
    email = '[email protected]',
    updated_at = NOW()
WHERE id = 1;
 
-- 基于条件更新多行
UPDATE users
SET is_active = FALSE
WHERE last_login < '2023-01-01';
 
-- 基于其他表更新
UPDATE orders o
SET total_amount = (
    SELECT SUM(quantity * price)
    FROM order_items oi
    WHERE oi.order_id = o.id
)
WHERE o.status = 'pending';

DELETE语句：删除数据

-- 删除指定行
DELETE FROM users WHERE id = 1;
 
-- 基于条件删除多行
DELETE FROM users WHERE is_active = FALSE;
 
-- 删除所有数据（保留表结构）
DELETE FROM users;
 
-- 使用TRUNCATE快速删除所有数据（不能回滚）
TRUNCATE TABLE users;

删除与外键约束：

当表之间存在外键关系时，删除操作可能受限制：

-- 如果有子记录，默认会阻止删除
DELETE FROM users WHERE id = 1;  -- 可能失败
 
-- 如果外键设置了ON DELETE CASCADE，删除父记录会自动删除子记录
DELETE FROM users WHERE id = 1;  -- 成功，相关orders也被删除

事务处理

事务是一组SQL操作的逻辑单元，要么全部执行成功，要么全部回滚。

-- 开始事务
BEGIN;
 
-- 执行多个操作
INSERT INTO orders (user_id, total_amount)
VALUES (123, 299.99);
 
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (LASTVAL(), 456, 2);
 
-- 如果一切正常，提交事务
COMMIT;
 
-- 如果出现错误，回滚事务
-- ROLLBACK;
sequenceDiagram
    participant Client as 客户端
    participant DB as 数据库

    Note over Client,DB: 事务处理过程

    Client->>DB: 1. BEGIN
    DB->>DB: 开启事务<br/>设置保存点

    Client->>DB: 2. INSERT INTO orders
    DB->>DB: 执行操作1<br/>锁定相关行

    Client->>DB: 3. INSERT INTO order_items
    DB->>DB: 执行操作2<br/>验证约束

    alt 操作成功
        Client->>DB: 4. COMMIT
        DB->>DB: 提交所有更改<br/>释放锁
        DB-->>Client: 事务成功
    else 操作失败
        Client->>DB: 4. ROLLBACK
        DB->>DB: 撤销所有更改<br/>释放锁
        DB-->>Client: 事务回滚
    end

    Note over Client,DB: 事务保证ACID特性

图表讲解：这张时序图展示了数据库事务的完整生命周期——这是理解数据一致性的核心机制。

事务从BEGIN命令开始，数据库建立事务上下文并设置保存点。保存点用于在需要时回滚到特定状态。

客户端依次发送多个SQL操作，数据库逐个执行这些操作，并在内部记录变更。在这个示例中，先插入订单，再插入订单项。数据库在执行过程中会检查约束（如外键约束、唯一性约束、CHECK约束）。

如果所有操作都成功执行，客户端发送COMMIT命令，数据库永久保存所有变更，释放锁定的资源。如果任何操作失败（如约束违反、唯一性冲突），客户端发送ROLLBACK命令，数据库撤销所有变更，恢复到事务开始前的状态。

事务的ACID特性保证了数据的完整性：原子性确保事务要么全部成功要么全部失败；一致性确保事务前后数据都处于有效状态；隔离性确保并发事务之间互不干扰；持久性确保提交的数据永久保存。

七、核心概念总结

概念说明应用场景
数据库集群一组数据库的集合，由一个PostgreSQL实例管理组织多个相关数据库
Schema数据库内的命名空间，用于组织对象避免对象名冲突，逻辑分组
表结构化数据的存储单元，由行和列组成存储实体数据
主键唯一标识表中的每一行建立表之间的关系，保证数据唯一性
外键建立表之间的引用关系维护引用完整性
事务一组操作的逻辑单元，要么全成功要么全失败保证数据一致性
索引加速数据查询的数据结构优化查询性能

八、本篇总结

本文介绍了PostgreSQL数据库的基础知识，包括数据库系统架构、安装配置、数据库对象管理、SQL数据类型、表设计、CRUD操作和事务处理。通过这些内容，你现在已经能够：

  • 理解PostgreSQL的客户端-服务器架构
  • 完成PostgreSQL的安装和基本配置
  • 创建和管理数据库、Schema、表
  • 选择合适的数据类型
  • 执行完整的数据增删改查操作
  • 使用事务保证数据一致性

这些技能是使用PostgreSQL的基础，下一篇我们将深入学习SQL高级查询，包括多表连接、子查询、窗口函数等更强大的数据处理技术。

下篇预告

第2篇将深入探讨SQL高级查询技术，带你学习多表连接、子查询、聚合函数、窗口函数等高级特性，帮助你编写更复杂、更高效的查询语句。

常见问题解答

Q1：PostgreSQL和MySQL有什么区别，为什么选择PostgreSQL？

答：PostgreSQL和MySQL都是优秀的开源关系数据库，但设计理念不同。

PostgreSQL更注重数据完整性和SQL标准的完全实现，支持复杂查询、事务、外键约束等。PostgreSQL的优势包括：更丰富的数据类型（JSON/JSONB、数组、范围类型）、更强大的查询优化器、更好的并发控制（MVCC）、完整的ACID支持、可扩展性（可以添加自定义函数和类型）。

MySQL的优势是简单易用、读密集场景性能好、社区庞大。如果需要复杂查询、数据完整性要求高、需要处理复杂数据结构，PostgreSQL是更好的选择。

Q2：什么是NULL，如何正确处理NULL值？

答：NULL表示”未知”或”无值”，与空字符串、0不同。

在查询中，NULL值参与任何运算结果都是NULL。比较NULL需要使用IS NULL或IS NOT NULL，不能使用=或<>。在聚合函数中，NULL值被忽略（COUNT(*)除外）。

创建表时，默认列允许NULL，使用NOT NULL约束可以禁止NULL。在应用中，NULL应该表示”值未知”而不是”空值”或”默认值”。例如，用户的中间名可以是NULL（没有提供），但不应该是空字符串（可能表示有意留空）。理解NULL的语义对于正确处理数据非常重要。

Q3：如何选择合适的数据类型，选择错误会有什么影响？

答：选择数据类型需要考虑存储空间、性能、数据范围和精度。

基本原则：选择能够满足需求的最小类型。例如，年龄可以用SMALLINT（-32768到32767），而不是BIGINT；价格应该使用NUMERIC而不是FLOAT以避免精度丢失；文本长度确定使用VARCHAR，不确定使用TEXT。

选择错误的影响：存储浪费（用BIGINT存储0-100的计数）、性能下降（索引更大的类型占用更多空间）、精度丢失（FLOAT存储金额）、查询复杂（需要类型转换）。在设计表时，应该仔细考虑每个字段的实际用途和数据范围。

Q4：事务是什么，为什么需要事务？

答：事务是一组SQL操作的逻辑单元，具有ACID特性：原子性（全部成功或全部失败）、一致性（数据始终处于有效状态）、隔离性（并发事务互不干扰）、持久性（提交的数据永久保存）。

事务用于保证数据完整性，特别是在涉及多个表的复杂操作中。

例如，银行转账需要从一个账户扣款、向另一个账户加款，这两个操作必须同时成功或同时失败，否则会导致数据不一致（钱凭空消失或增加）。如果没有事务，其中一个操作失败会导致数据不一致。使用事务，任何失败都会触发回滚，数据库恢复到操作前的状态，保证数据一致性。

Q5：SERIAL类型是什么，与直接使用INTEGER有什么区别？

答：SERIAL是PostgreSQL的伪类型，用于创建自增主键。

SERIAL实际上是INTEGER + SEQUENCE + DEFAULT + NOT NULL的组合。当定义id SERIAL PRIMARY KEY时，PostgreSQL会：创建一个SEQUENCE（序列对象），将id字段的默认值设置为序列的nextval()，设置NOT NULL约束。

使用SERIAL的好处是简单方便，自动处理自增逻辑。直接使用INTEGER时，需要手动创建序列并设置默认值。在现代PostgreSQL中，推荐使用GENERATED ALWAYS AS IDENTITY（身份列）替代SERIAL，因为它是SQL标准的一部分，更明确、更可靠。IDENTITY列提供更多控制选项，如起始值、增量、循环等。

更新时间：2026年3月2日 系列：PostgreSQL数据库技术 标签：#PostgreSQL 数据库入门 SQL基础 数据类型 CRUD 事务

关系图谱