PostgreSQL 入门到进阶完全指南

数据库

PostgreSQL 入门到进阶

一、PostgreSQL 简介

PostgreSQL 是一款功能强大且开源的关系型数据库管理系统，最早由加州大学伯克利分校的Ingres项目演化而来。它具有以下特点：

开源且跨平台：支持 Linux、Windows、macOS 等多个操作系统
功能强大：支持 SQL 标准及丰富的扩展功能
高度可扩展：支持自定义数据类型、函数和操作符
数据类型丰富：支持数值、字符串、时间、布尔、JSON 等多种数据类型
可靠性高：支持事务、外键约束、MVCC 等高级特性

📚 基础篇 - PostgreSQL 基本用法

1️⃣ 安装与连接

安装 PostgreSQL

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# CentOS/RHEL
sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup initdb

# macOS (使用 Homebrew)
brew install postgresql
brew services start postgresql

连接数据库

# 命令行连接本地数据库
psql -U postgres -d mydb

# 连接远程数据库
psql -h 192.168.1.100 -U username -d dbname -p 5432

# 连接后需要输入密码
Password for user username:

2️⃣ 数据库操作

创建数据库

-- 基本创建
CREATE DATABASE mydb;

-- 指定字符集和排序规则
CREATE DATABASE mydb 
WITH ENCODING 'UTF8' 
LC_COLLATE = 'zh_CN.UTF-8' 
LC_CTYPE = 'zh_CN.UTF-8';

删除数据库

-- 删除数据库（谨慎使用！）
DROP DATABASE mydb;

查看所有数据库

-- 在 psql 命令行中
\l
或
\list

3️⃣ 表操作

创建表

-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,      -- 自增主键
    username VARCHAR(50) NOT NULL,  -- 非空约束
    password VARCHAR(100) NOT NULL, -- 非空约束
    email VARCHAR(100) UNIQUE,      -- 唯一约束
    age INT CHECK (age >= 0),      -- 检查约束
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now()
);

-- 创建订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(20) UNIQUE,
    user_id INT REFERENCES users(id),  -- 外键约束
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT now()
);

修改表结构

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 修改列数据类型
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

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

-- 重命名列
ALTER TABLE users RENAME COLUMN username TO user_name;

-- 重命名表
ALTER TABLE users RENAME TO customers;

删除表

-- 删除表
DROP TABLE orders;

-- 删除表及其关联对象
DROP TABLE customers CASCADE;

4️⃣ 数据操作语言 (DML)

插入数据

-- 插入单条记录
INSERT INTO users (username, password, email, age) 
VALUES ('张三', 'password123', 'zhangsan@example.com', 25);

-- 插入多条记录
INSERT INTO users (username, password, email, age) VALUES 
('李四', 'password456', 'lisi@example.com', 30),
('王五', 'password789', 'wangwu@example.com', 28);

-- 插入时忽略部分列（使用默认值）
INSERT INTO users (username, password, email) 
VALUES ('赵六', 'password000', 'zhaoliu@example.com');

查询数据

-- 查询所有数据
SELECT * FROM users;

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

-- 条件查询
SELECT * FROM users WHERE age > 25;

-- 多条件查询
SELECT * FROM users WHERE age > 25 AND email LIKE '%@example.com';

-- 模糊查询
SELECT * FROM users WHERE username LIKE '张%';

-- 排序查询
SELECT * FROM users ORDER BY age DESC;

-- 分页查询
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 统计查询
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;

-- 分组统计
SELECT COUNT(*) as user_count, AVG(age) as avg_age 
FROM users GROUP BY age > 30;

-- 去重查询
SELECT DISTINCT age FROM users;

更新数据

-- 更新单条记录
UPDATE users SET age = 26 WHERE username = '张三';

-- 更新多条记录
UPDATE users SET age = age + 1 WHERE age < 30;

-- 基于查询结果更新
UPDATE users SET status = 'active' 
WHERE id IN (SELECT id FROM users WHERE age > 25);

删除数据

-- 删除单条记录
DELETE FROM users WHERE username = '赵六';

-- 删除多条记录
DELETE FROM users WHERE age < 20;

-- 清空表
TRUNCATE TABLE users;

5️⃣ 数据约束

主键约束

-- 创建表时指定主键
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 修改表添加主键
ALTER TABLE products ADD CONSTRAINT pk_product_id PRIMARY KEY (id);

外键约束

-- 创建表时指定外键
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(id),
    quantity INT
);

-- 修改表添加外键
ALTER TABLE orders 
ADD CONSTRAINT fk_order_product 
FOREIGN KEY (product_id) REFERENCES products(id);

唯一约束

-- 创建表时指定唯一约束
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

-- 修改表添加唯一约束
ALTER TABLE users ADD CONSTRAINT uk_username UNIQUE (username);

检查约束

-- 创建表时指定检查约束
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    salary DECIMAL(10,2) CHECK (salary > 0),
    age INT CHECK (age >= 18 AND age <= 65)
);

-- 修改表添加检查约束
ALTER TABLE employees 
ADD CONSTRAINT chk_salary CHECK (salary >= 1000);

6️⃣ 索引

创建索引

-- 创建普通索引
CREATE INDEX idx_users_username ON users(username);

-- 创建复合索引
CREATE INDEX idx_users_age_status ON users(age, status);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 创建部分索引
CREATE INDEX idx_active_users ON users(id) WHERE status = 'active';

删除索引

DROP INDEX idx_users_username;

🚀 进阶篇 - PostgreSQL 高级用法

1️⃣ 视图与物化视图

创建视图

-- 创建基本视图
CREATE VIEW v_user_info AS
SELECT 
    id, 
    username, 
    email, 
    age,
    created_at
FROM users 
WHERE status = 'active';

-- 使用视图
SELECT * FROM v_user_info WHERE age > 25;

-- 创建复杂视图
CREATE VIEW v_user_order_summary AS
SELECT 
    u.id,
    u.username,
    u.email,
    COUNT(o.id) as order_count,
    COALESCE(SUM(o.amount), 0) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;

创建物化视图

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_user_order_stats AS
SELECT 
    user_id,
    COUNT(*) as order_count,
    AVG(amount) as avg_amount,
    MAX(amount) as max_amount,
    MIN(amount) as min_amount
FROM orders
GROUP BY user_id;

-- 查询物化视图
SELECT * FROM mv_user_order_stats ORDER BY order_count DESC;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW mv_user_order_stats;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_order_stats;

2️⃣ 事务处理

基本事务

-- 开始事务
BEGIN;

-- 执行多个操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 如果出现错误，回滚事务
-- ROLLBACK;

保存点

BEGIN;

-- 第一步操作
INSERT INTO users (username, email) VALUES ('test1', 'test1@example.com');

-- 创建保存点
SAVEPOINT before_second_insert;

-- 第二步操作
INSERT INTO users (username, email) VALUES ('test2', 'test2@example.com');

-- 如果出错了，回滚到保存点
-- ROLLBACK TO before_second_insert;

-- 继续其他操作
INSERT INTO users (username, email) VALUES ('test3', 'test3@example.com');

COMMIT;

事务隔离级别

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 开始指定隔离级别的事务
BEGIN ISOLATION LEVEL READ COMMITTED;

3️⃣ 窗口函数

基本窗口函数

-- 计算部门平均工资
SELECT 
    department,
    employee_name,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM employees;

-- 计算排名
SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees;

-- 计算行号
SELECT 
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as overall_rank
FROM employees;

-- 计算累计总和
SELECT 
    department,
    employee_name,
    salary,
    SUM(salary) OVER (ORDER BY salary DESC) as cumulative_sum
FROM employees;

复杂窗口函数

-- 分组排名
SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank,
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as percent_rank
FROM employees;

-- 移动平均
SELECT 
    date,
    sales,
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7
FROM sales_data;

-- 同比环比
SELECT 
    date,
    sales,
    sales - LAG(sales, 12) OVER (ORDER BY date) as year_over_year,
    sales - LAG(sales, 1) OVER (ORDER BY date) as month_over_month
FROM sales_data;

4️⃣ 表继承

创建继承表结构

-- 创建父表
CREATE TABLE cities (
    name VARCHAR(80),
    population INT,
    elevation INT
);

-- 创建子表继承父表
CREATE TABLE capitals (
    state VARCHAR(2) UNIQUE NOT NULL
) INHERITS (cities);

-- 插入数据
INSERT INTO cities (name, population, elevation) 
VALUES ('Chicago', 2700000, 181);

INSERT INTO capitals (name, population, elevation, state) 
VALUES ('Springfield', 116250, 189, 'IL');

查询继承数据

-- 查询所有城市（包括首都）
SELECT * FROM cities;

-- 只查询非首都城市
SELECT * FROM ONLY cities;

-- 更新继承表
UPDATE cities SET population = population + 1000 WHERE name = 'Chicago';
UPDATE capitals SET population = population + 500 WHERE state = 'IL';

5️⃣ 用户与权限管理

创建用户

-- 创建普通用户
CREATE USER readonly_user WITH PASSWORD 'password123';

-- 创建具有创建权限的用户
CREATE USER developer WITH PASSWORD 'devpass' 
CREATEDB NOCREATEROLE;

-- 创建超级用户
CREATE USER admin WITH PASSWORD 'adminpass' SUPERUSER;

权限管理

-- 授予权限
GRANT CONNECT ON DATABASE mydb TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- 授予特定表权限
GRANT SELECT, INSERT, UPDATE ON users TO developer;

-- 授予序列权限
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO developer;

-- 撤销权限
REVOKE SELECT ON users FROM readonly_user;

角色管理

-- 创建角色
CREATE ROLE readwrite_role;

-- 给角色授权
GRANT SELECT, INSERT, UPDATE ON users TO readwrite_role;
GRANT SELECT ON orders TO readwrite_role;

-- 给用户分配角色
GRANT readwrite_role TO developer;

-- 移除角色
REVOKE readwrite_role FROM developer;

6️⃣ 复杂查询技巧

连接查询

-- 内连接
SELECT u.username, o.order_number, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 右连接
SELECT u.username, COUNT(o.id) as order_count
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id
GROUP BY o.user_id, u.username;

-- 全连接
SELECT u.username, COUNT(o.id) as order_count
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

子查询

-- 标量子查询
SELECT username, email, 
       (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
FROM users;

-- 相关子查询
SELECT username, email
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders WHERE user_id = u.id AND amount > 1000
);

-- IN 子查询
SELECT username, email
FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders WHERE amount > 500
);

-- ANY/SOME 子查询
SELECT username, email
FROM users
WHERE id = ANY (
    SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > 5
);

公用表表达式 (CTE)

-- 基本CTE
WITH active_users AS (
    SELECT id, username, email
    FROM users
    WHERE status = 'active' AND created_at >= '2023-01-01'
),
user_stats AS (
    SELECT 
        u.id,
        u.username,
        COUNT(o.id) as order_count,
        COALESCE(SUM(o.amount), 0) as total_amount
    FROM active_users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.username
)
SELECT * FROM user_stats
WHERE order_count > 0
ORDER BY total_amount DESC;

7️⃣ 性能优化

查询优化

-- 使用 EXPLAIN 分析查询计划
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 创建函数索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- 使用部分索引
CREATE INDEX idx_users_active ON users(id) WHERE status = 'active';

-- 使用表达式索引
CREATE INDEX idx_users_name_upper ON users(UPPER(username));

配置优化

-- 查看 PostgreSQL 配置
SHOW shared_buffers;
SHOW effective_cache_size;
SHOW work_mem;

-- 设置参数
SET work_mem = '16MB';
SET maintenance_work_mem = '256MB';
SET max_connections = 200;

8️⃣ 备份与恢复

数据备份

# 创建完整备份
pg_dump -U postgres -f mydb_backup.sql mydb

# 创建自定义格式备份
pg_dump -U postgres -F c -f mydb_backup.custom mydb

# 创建压缩备份
pg_dump -U postgres -F t -z -f mydb_backup.tar mydb

# 只备份特定表
pg_dump -U postgres -t users -t orders mydb > tables_backup.sql

数据恢复

# 从 SQL 文件恢复
psql -U postgres -d newdb -f mydb_backup.sql

# 从自定义格式恢复
pg_restore -U postgres -d newdb mydb_backup.custom

# 从压缩 tar 文件恢复
pg_restore -U postgres -d newdb mydb_backup.tar

🎯 实战案例

案例一：用户注册与登录系统

数据库设计

-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    phone VARCHAR(20),
    avatar_url VARCHAR(255),
    status VARCHAR(20) DEFAULT 'active',
    email_verified BOOLEAN DEFAULT false,
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now()
);

-- 用户登录历史表
CREATE TABLE user_login_history (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    ip_address INET,
    user_agent TEXT,
    login_status VARCHAR(20),
    login_time TIMESTAMP DEFAULT now()
);

业务逻辑实现

-- 用户注册
CREATE OR REPLACE FUNCTION register_user(
    p_username VARCHAR(50),
    p_email VARCHAR(100),
    p_password VARCHAR(255),
    p_full_name VARCHAR(100),
    p_ip_address INET
) RETURNS INT AS $$
DECLARE
    v_user_id INT;
BEGIN
    -- 检查用户名是否已存在
    IF EXISTS (SELECT 1 FROM users WHERE username = p_username) THEN
        RAISE EXCEPTION 'Username already exists';
    END IF;
    
    -- 检查邮箱是否已存在
    IF EXISTS (SELECT 1 FROM users WHERE email = p_email) THEN
        RAISE EXCEPTION 'Email already exists';
    END IF;
    
    -- 插入用户
    INSERT INTO users (username, email, password_hash, full_name)
    VALUES (p_username, p_email, crypt(p_password, gen_salt('bf')), p_full_name)
    RETURNING id INTO v_user_id;
    
    -- 记录登录历史
    INSERT INTO user_login_history (user_id, ip_address, user_agent, login_status)
    VALUES (v_user_id, p_ip_address, 'API Registration', 'success');
    
    RETURN v_user_id;
END;
$$ LANGUAGE plpgsql;

-- 用户登录
CREATE OR REPLACE FUNCTION user_login(
    p_username VARCHAR(50),
    p_password VARCHAR(255),
    p_ip_address INET,
    p_user_agent TEXT
) RETURNS TABLE(user_id INT, username VARCHAR, full_name VARCHAR, status VARCHAR) AS $$
BEGIN
    -- 验证用户名和密码
    RETURN QUERY
    SELECT u.id, u.username, u.full_name, u.status
    FROM users u
    WHERE u.username = p_username 
    AND u.password_hash = crypt(p_password, u.password_hash)
    AND u.status = 'active';
    
    -- 如果登录成功，记录历史并更新最后登录时间
    IF FOUND THEN
        UPDATE users SET last_login = now() WHERE username = p_username;
        
        INSERT INTO user_login_history (user_id, ip_address, user_agent, login_status)
        SELECT id, p_ip_address, p_user_agent, 'success'
        FROM users WHERE username = p_username;
    ELSE
        -- 记录登录失败
        INSERT INTO user_login_history (ip_address, user_agent, login_status)
        VALUES (p_ip_address, p_user_agent, 'failed');
    END IF;
END;
$$ LANGUAGE plpgsql;

案例二：电商订单管理系统

数据库设计

-- 商品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    category VARCHAR(50),
    brand VARCHAR(100),
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now()
);

-- 订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    user_id INT REFERENCES users(id),
    customer_name VARCHAR(100),
    customer_phone VARCHAR(20),
    customer_email VARCHAR(100),
    shipping_address TEXT,
    total_amount DECIMAL(10,2) NOT NULL,
    shipping_fee DECIMAL(10,2) DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    final_amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50),
    payment_status VARCHAR(20) DEFAULT 'pending',
    order_status VARCHAR(20) DEFAULT 'pending',
    shipping_status VARCHAR(20) DEFAULT 'pending',
    notes TEXT,
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now()
);

-- 订单详情表
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    discount_type VARCHAR(20),
    discount_amount DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT now()
);

业务逻辑实现

-- 创建订单
CREATE OR REPLACE FUNCTION create_order(
    p_user_id INT,
    p_customer_name VARCHAR(100),
    p_customer_phone VARCHAR(20),
    p_customer_email VARCHAR(100),
    p_shipping_address TEXT,
    p_item_ids INT[],
    p_quantities INT[],
    p_shipping_fee DECIMAL(10,2)
) RETURNS TABLE(order_id INT, order_number VARCHAR, total_amount DECIMAL) AS $$
DECLARE
    v_order_id INT;
    v_order_number VARCHAR(50);
    v_total_amount DECIMAL(10,2) := 0;
    v_final_amount DECIMAL(10,2);
    i INT;
    v_product_id INT;
    v_quantity INT;
    v_product RECORD;
    v_order_item_id INT;
BEGIN
    -- 检查库存
    FOR i IN 1..array_length(p_item_ids, 1) LOOP
        v_product_id := p_item_ids[i];
        v_quantity := p_quantities[i];
        
        SELECT stock_quantity INTO v_product
        FROM products 
        WHERE id = v_product_id AND status = 'active';
        
        IF v_product.stock_quantity < v_quantity THEN
            RAISE EXCEPTION 'Insufficient stock for product ID: %', v_product_id;
        END IF;
    END LOOP;
    
    -- 开始事务
    BEGIN
        -- 创建订单
        INSERT INTO orders (
            order_number, user_id, customer_name, customer_phone, 
            customer_email, shipping_address, total_amount, shipping_fee,
            final_amount
        ) VALUES (
            'ORD' || TO_CHAR(NOW(), 'YYYYMMDDHH24MISS'),
            p_user_id, p_customer_name, p_customer_phone,
            p_customer_email, p_shipping_address, 0, p_shipping_fee,
            p_shipping_fee
        ) RETURNING id, order_number INTO v_order_id, v_order_number;
        
        -- 创建订单明细
        v_total_amount := 0;
        FOR i IN 1..array_length(p_item_ids, 1) LOOP
            v_product_id := p_item_ids[i];
            v_quantity := p_quantities[i];
            
            SELECT price INTO v_product
            FROM products 
            WHERE id = v_product_id AND status = 'active';
            
            INSERT INTO order_items (
                order_id, product_id, quantity, unit_price,
                subtotal, discount_amount
            ) VALUES (
                v_order_id, v_product_id, v_quantity, v_product.price,
                v_product.price * v_quantity, 0
            );
            
            v_total_amount := v_total_amount + (v_product.price * v_quantity);
        END LOOP;
        
        -- 更新订单总金额
        v_final_amount := v_total_amount + p_shipping_fee;
        UPDATE orders 
        SET total_amount = v_total_amount,
            final_amount = v_final_amount
        WHERE id = v_order_id;
        
        -- 扣减库存
        FOR i IN 1..array_length(p_item_ids, 1) LOOP
            v_product_id := p_item_ids[i];
            v_quantity := p_quantities[i];
            
            UPDATE products 
            SET stock_quantity = stock_quantity - v_quantity,
                updated_at = now()
            WHERE id = v_product_id;
        END LOOP;
        
        -- 提交事务
        COMMIT;
        
        RETURN QUERY SELECT v_order_id, v_order_number, v_final_amount;
        
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
    END;
END;
$$ LANGUAGE plpgsql;

🔧 常用管理命令

psql 命令行工具

# 查看所有数据库
\l

# 切换数据库
\c mydb

# 查看当前数据库的所有表
\d

# 查看指定表的详细信息
\d+ users

# 查看表结构
\d users

# 查看索引
\di

# 查看视图
\dv

# 查看序列
\ds

# 查看用户
\du

# 查看权限
\dp

# 执行 SQL 文件
\i /path/to/file.sql

# 切换输出格式
\a          # 对齐输出
\pset null NULL        # 设置空值显示
\pset format aligned    # 对齐格式
\pset format html      # HTML 格式
\pset format latex     # LaTeX 格式

# 切换扩展插件
\dx        # 列出已安装扩展
\dx+ name  # 显示扩展详细信息

# 帮助命令
\?        # psql 命令帮助
\h        # SQL 命令帮助
\h CREATE TABLE  # 特定命令帮助

# 退出
\q 或 \quit

数据库管理

# 启动 PostgreSQL 服务
sudo systemctl start postgresql

# 停止 PostgreSQL 服务
sudo systemctl stop postgresql

# 重启 PostgreSQL 服务
sudo systemctl restart postgresql

# 查看 PostgreSQL 服务状态
sudo systemctl status postgresql

# 启用开机自启
sudo systemctl enable postgresql

# 禁用开机自启
sudo systemctl disable postgresql

# 查看日志
tail -f /var/log/postgresql/postgresql-XX-main.log

# 连接测试
psql -U postgres -c "SELECT version();"

📖 学习资源推荐

官方文档

在线资源

实践平台

SQLFiddle - 在线 SQL 测试
DB-Fiddle - 现代 SQL 测试平台
PostgreSQL 在线演练环境

图形化管理工具

pgAdmin - 官方图形化管理工具
DBeaver - 多数据库管理工具
DataGrip - JetBrains 数据库 IDE
Navicat - 商业数据库管理工具

🎯 总结

通过这篇从基础到进阶的 PostgreSQL 教程，您已经掌握了：

基础知识：数据库、表、数据的创建、查询、更新、删除
进阶技能：视图、事务、窗口函数、表继承、用户权限管理
实战应用：完整的项目案例和业务逻辑实现
性能优化：索引、查询优化、配置调优
运维管理：备份恢复、日常维护、常用管理命令

PostgreSQL 是一款功能强大的数据库系统，掌握了它将为您的开发工作带来巨大帮助。建议在学习过程中多实践，结合实际项目加深理解，逐步达到熟练运用的高级水平。

如果觉得文章对你有用，请随意赞赏

PostgreSQL

PostgreSQL 入门到进阶完全指南

https://terrabit.cn/archives/wei-ming-ming-wen-zhang-CEXndfmg

作者

YoLo

发布于

2025-10-14

更新于

2025-10-14

许可协议

CC BY 4.0

PostgreSQL 入门到进阶完全指南

PostgreSQL 入门到进阶

一、PostgreSQL 简介

📚 基础篇 - PostgreSQL 基本用法

1️⃣ 安装与连接

安装 PostgreSQL

连接数据库

2️⃣ 数据库操作

创建数据库

删除数据库

查看所有数据库

3️⃣ 表操作

创建表

修改表结构

删除表

4️⃣ 数据操作语言 (DML)

插入数据

查询数据

更新数据

删除数据

5️⃣ 数据约束

主键约束

外键约束

唯一约束

检查约束

6️⃣ 索引

创建索引

删除索引

🚀 进阶篇 - PostgreSQL 高级用法

1️⃣ 视图与物化视图

创建视图

创建物化视图

2️⃣ 事务处理

基本事务

保存点

事务隔离级别

3️⃣ 窗口函数

基本窗口函数

复杂窗口函数

4️⃣ 表继承

创建继承表结构

查询继承数据

5️⃣ 用户与权限管理

创建用户

权限管理

角色管理

6️⃣ 复杂查询技巧

连接查询

子查询

公用表表达式 (CTE)

7️⃣ 性能优化

查询优化

配置优化

8️⃣ 备份与恢复

数据备份

数据恢复

🎯 实战案例

案例一：用户注册与登录系统

数据库设计

业务逻辑实现

案例二：电商订单管理系统

数据库设计

业务逻辑实现

🔧 常用管理命令

psql 命令行工具

数据库管理

📖 学习资源推荐

官方文档

在线资源

实践平台

图形化管理工具

🎯 总结

作者

发布于

更新于

许可协议

评论