当前位置：首页 > news >正文

PostgreSQL 入门学习教程，从入门到精通，PostgreSQL 16 (Windows) 安装与核心语法实战指南(2）

news 2026/3/26 18:57:23

PostgreSQL 16 (Windows) 安装与核心语法实战指南

本指南基于PostgreSQL 16版本，专为 Windows 环境设计。内容涵盖从下载安装到核心 SQL 语法、高级特性及综合案例的全流程解析。

第一部分：Windows 环境下安装 PostgreSQL 16

1.1 下载准备

访问 PostgreSQL 官方下载页面：https://www.postgresql.org/download/windows/
点击“Download the installer”(通常由 EnterpriseDB 提供)。
选择PostgreSQL 16版本，下载对应的 Windows x86-64 安装包（例如postgresql-16.x-windows-x64.exe）。

1.2 安装步骤详解

双击运行下载的安装包，按以下步骤操作：

Welcome: 点击Next。
Installation Directory: 选择安装路径（建议默认C:\Program Files\PostgreSQL\16），点击Next。
Select Components:
- PostgreSQL Server: 数据库核心（必选）。
- pgAdmin 4: 图形化管理工具（必选，方便新手）。
- Command Line Tools: 命令行工具（必选，用于psql）。
- Stack Builder: 额外驱动和工具（可选，初学者可不选）。
- 点击Next。
Data Directory: 选择数据存放路径（默认即可），点击Next。
Password:关键步骤。设置超级用户postgres的密码。
- 注意：请务必牢记此密码，后续所有连接都需要用到。
- 点击Next。
Port: 默认端口为5432。如果本机已安装其他数据库占用此端口，可修改为5433等。点击Next。
Locale: 保持默认Default locale(通常对应系统语言)，点击Next。
Pre-installation Summary: 确认信息，点击Next开始安装。
Installation Complete: 安装完成后，取消勾选 “Stack Builder”（如果不需要），点击Finish。

1.3 环境变量配置 (重要)

为了在 CMD 或 PowerShell 的任何目录下使用psql命令，需配置环境变量：

右键“此电脑” -> “属性” -> “高级系统设置” -> “环境变量”。
在系统变量中找到Path，点击“编辑”。
点击“新建”，添加 PostgreSQL 的 bin 目录路径，例如：
C:\Program Files\PostgreSQL\16\bin
连续点击“确定”保存。
验证安装：打开新的 CMD 窗口，输入：
```
psql --version
```
若显示psql (PostgreSQL) 16.x，则安装成功。

1.4 首次连接测试

打开 CMD，输入以下命令连接数据库：

psql -U postgres

输入安装时设置的密码（输入时不会显示字符，直接回车即可）。
看到postgres=#提示符即表示连接成功。

第二部分：核心语法知识点与案例代码

PostgreSQL 遵循标准 SQL，但拥有许多强大的扩展特性。以下知识点均包含详细注释的代码案例。

2.1 数据库与模式管理 (DDL)

知识点：

CREATE DATABASE: 创建数据库。
SCHEMA: 逻辑命名空间，用于隔离对象。
DROP: 删除对象。

案例代码：

-- 1. 创建一个新的数据库名为 'shop_db'-- 注意：创建数据库必须在 postgres 库下执行，不能在自己连接的库中创建自己CREATEDATABASEshop_db;-- 连接到新数据库 (在 psql 命令行中使用 \c 命令，SQL 脚本中通常由客户端处理)-- \c shop_db-- 2. 在当前数据库中创建一个名为 'sales' 的模式 (Schema)-- 模式类似于文件夹，用于分类管理表CREATESCHEMAsales;-- 3. 创建一个名为 'dev' 的模式CREATESCHEMAdev;-- 4. 查看当前所有模式-- \dn (这是 psql 元命令，非标准 SQL)SELECTschema_nameFROMinformation_schema.schemata;-- 5. 删除模式 (如果存在且为空)DROPSCHEMAIFEXISTSdev;

2.2 数据类型与表创建 (Advanced DDL)

知识点：

丰富类型:SERIAL(自增),JSONB(高效 JSON),ARRAY(数组),TIMESTAMPTZ(带时区时间)。
约束:PRIMARY KEY,FOREIGN KEY,CHECK,NOT NULL,UNIQUE.
默认值:DEFAULT.

案例代码：

-- 切换到 sales 模式 (可选，为了方便演示)SETsearch_pathTOsales;-- 创建 'users' 表CREATETABLEusers(user_idSERIALPRIMARYKEY,-- 自增主键，PG 特有语法usernameVARCHAR(50)NOTNULLUNIQUE,-- 唯一且非空emailVARCHAR(100)NOTNULL,is_activeBOOLEANDEFAULTTRUE,-- 布尔类型，默认真created_at TIMESTAMPTZDEFAULTNOW(),-- 带时区的时间戳，默认为当前时间profile_data JSONB-- 存储灵活的 JSON 数据 (如偏好设置));-- 创建 'products' 表CREATETABLEproducts(product_idSERIALPRIMARYKEY,nameVARCHAR(100)NOTNULL,priceNUMERIC(10,2)NOTNULLCHECK(price>=0),-- 检查约束：价格必须>=0tagsTEXT[],-- 数组类型，存储标签stock_quantityINTDEFAULT0);-- 创建 'orders' 表 (包含外键)CREATETABLEorders(order_idSERIALPRIMARYKEY,user_idINTNOTNULL,order_date TIMESTAMPTZDEFAULTNOW(),total_amountNUMERIC(10,2),-- 外键约束：引用 users 表的 user_id，删除用户时级联删除订单CONSTRAINTfk_userFOREIGNKEY(user_id)REFERENCESusers(user_id)ONDELETECASCADE);-- 插入测试数据 (演示特殊类型用法)INSERTINTOusers(username,email,profile_data)VALUES('alice','alice@example.com','{"theme": "dark", "notifications": true}'::jsonb),('bob','bob@example.com','{"theme": "light", "lang": "zh-CN"}'::jsonb);INSERTINTOproducts(name,price,tags,stock_quantity)VALUES('机械键盘',599.00,ARRAY['电子','办公','热销'],50),('无线鼠标',129.50,ARRAY['电子','办公'],120);

2.3 数据查询与操作 (DML & DQL)

知识点：

CRUD:INSERT,SELECT,UPDATE,DELETE.
RETURNING 子句: PG 特色，插入/更新/删除后直接返回受影响的数据，无需二次查询。
JSONB 查询: 使用->,->>,@>操作符。
数组查询: 使用ANY,ALL,@>。

案例代码：

-- 1. 基础查询与排序SELECTusername,email,created_atFROMusersWHEREis_active=TRUEORDERBYcreated_atDESC;-- 2. 使用 RETURNING 子句 (插入并立即获取生成的 ID)-- 传统做法需要插入后查 LASTVAL()，PG 可以直接返回INSERTINTOorders(user_id,total_amount)VALUES(1,728.50)RETURNINGorder_id,order_date;-- 3. 更新操作 (带条件)UPDATEproductsSETstock_quantity=stock_quantity-1WHEREproduct_id=1RETURNINGname,stock_quantity;-- 返回更新后的库存-- 4. JSONB 高级查询-- 查询 profile_data 中 theme 为 'dark' 的用户-- ->> 获取文本值，-> 获取 JSON 对象SELECTusernameFROMusersWHEREprofile_data->>'theme'='dark';-- 5. 数组查询-- 查询 tags 包含 '热销' 的产品-- @> 表示左边数组包含右边数组SELECTname,tagsFROMproductsWHEREtags @>ARRAY['热销'];-- 6. 模糊查询与正则-- 查询邮箱以 example 开头的用户SELECT*FROMusersWHEREemailLIKE'%example%';-- 正则匹配 (PG 强大之处)SELECT*FROMusersWHEREemail~'^[a-z]+@example\.com$';

2.4 聚合函数与分组

知识点：

标准聚合:COUNT,SUM,AVG,MAX,MIN.
GROUP BY与HAVING.
STRING_AGG: PG 特有的字符串聚合函数。

案例代码：

-- 1. 统计每个用户的订单总数和总金额SELECTu.username,COUNT(o.order_id)asorder_count,SUM(o.total_amount)astotal_spentFROMusers uJOINorders oONu.user_id=o.user_idGROUPBYu.usernameHAVINGSUM(o.total_amount)>100;-- 只筛选总消费大于 100 的用户-- 2. 字符串聚合 (将产品的标签合并成一个字符串)SELECTname,STRING_AGG(tag,', ')asall_tags-- 假设 tags 已被展开，此处演示概念FROM(SELECTname,UNNEST(tags)astagFROMproducts)subGROUPBYname;

2.5 视图与索引

知识点：

VIEW: 虚拟表，简化复杂查询。
MATERIALIZED VIEW: 物化视图，物理存储结果，查询快但需刷新。
INDEX: 加速查询，PG 支持 B-Tree, Hash, GiST, GIN (针对 JSON/数组) 等。

案例代码：

-- 1. 创建普通视图 (简化多表连接)CREATEVIEWuser_order_summaryASSELECTu.username,u.email,COUNT(o.order_id)asorder_countFROMusers uLEFTJOINorders oONu.user_id=o.user_idGROUPBYu.user_id,u.username,u.email;-- 查询视图SELECT*FROMuser_order_summaryWHEREorder_count>0;-- 2. 创建索引-- 为经常查询的 email 字段创建 B-Tree 索引CREATEINDEXidx_users_emailONusers(email);-- 为 JSONB 字段创建 GIN 索引 (加速 JSON 内部键值查询)CREATEINDEXidx_users_profileONusersUSINGGIN(profile_data);-- 为数组字段创建 GIN 索引 (加速数组包含查询)CREATEINDEXidx_products_tagsONproductsUSINGGIN(tags);

2.6 事务控制 (Transaction)

知识点：

BEGIN,COMMIT,ROLLBACK.
ACID 特性保证数据一致性。

案例代码：

-- 模拟一个转账或库存扣减的事务BEGIN;-- 1. 扣减库存UPDATEproductsSETstock_quantity=stock_quantity-1WHEREproduct_id=1;-- 2. 记录订单INSERTINTOorders(user_id,total_amount)VALUES(1,599.00);-- 检查中间状态 (可选)-- 如果发现库存不足或其他错误，执行 ROLLBACK;-- 这里假设一切正常COMMIT;-- 如果发生错误，回滚所有操作：-- ROLLBACK;

第三部分：综合性实战案例

场景：电商实时库存与用户行为分析系统

需求：

设计包含用户、商品、订单、订单详情的完整 schema。
实现一个功能：当用户下单时，自动扣减库存，如果库存不足则失败（使用触发器或应用层逻辑，此处演示触发器自动处理）。
创建一个物化视图，每日统计各分类的销售排行，并提供刷新机制。
利用 JSONB 存储商品的动态属性（如颜色、尺寸），并进行灵活查询。

3.1 完整初始化脚本

-- 清理旧环境 (谨慎生产环境使用)DROPTABLEIFEXISTSorder_itemsCASCADE;DROPTABLEIFEXISTSordersCASCADE;DROPTABLEIFEXISTSproductsCASCADE;DROPTABLEIFEXISTSusersCASCADE;DROPMATERIALIZEDVIEWIFEXISTSdaily_sales_reportCASCADE;-- 1. 创建基础表CREATETABLEusers(user_idSERIALPRIMARYKEY,usernameVARCHAR(50)UNIQUENOTNULL,reg_date TIMESTAMPTZDEFAULTNOW());CREATETABLEproducts(product_idSERIALPRIMARYKEY,nameVARCHAR(100)NOTNULL,categoryVARCHAR(50),priceNUMERIC(10,2)NOTNULL,stockINTNOTNULLDEFAULT0,attributes JSONB-- 存储动态属性，如 {"color": "red", "size": "L"});CREATETABLEorders(order_idSERIALPRIMARYKEY,user_idINTREFERENCESusers(user_id),created_at TIMESTAMPTZDEFAULTNOW(),statusVARCHAR(20)DEFAULT'PENDING'-- PENDING, COMPLETED, CANCELLED);CREATETABLEorder_items(item_idSERIALPRIMARYKEY,order_idINTREFERENCESorders(order_id)ONDELETECASCADE,product_idINTREFERENCESproducts(product_id),quantityINTNOTNULL,price_at_purchaseNUMERIC(10,2)NOTNULL-- 记录购买时的单价);-- 2. 插入初始数据INSERTINTOusers(username)VALUES('Charlie'),('Diana');INSERTINTOproducts(name,category,price,stock,attributes)VALUES('T-Shirt Red','Clothing',29.99,100,'{"color": "Red", "size": "M"}'::jsonb),('T-Shirt Blue','Clothing',29.99,50,'{"color": "Blue", "size": "L"}'::jsonb),('Coffee Mug','Home',9.99,200,'{"material": "Ceramic"}'::jsonb);-- 3. 创建自动扣减库存的触发器函数CREATEORREPLACEFUNCTIONdecrease_stock()RETURNSTRIGGERAS$$BEGIN-- 检查库存是否充足IF(SELECTstockFROMproductsWHEREproduct_id=NEW.product_id)<NEW.quantityTHENRAISE EXCEPTION'库存不足！产品 ID: %, 请求数量：%, 当前库存：%',NEW.product_id,NEW.quantity,(SELECTstockFROMproductsWHEREproduct_id=NEW.product_id);ENDIF;-- 扣减库存UPDATEproductsSETstock=stock-NEW.quantityWHEREproduct_id=NEW.product_id;RETURNNEW;END;$$LANGUAGEplpgsql;-- 绑定触发器：在插入 order_items 之前执行CREATETRIGGERtrg_decrease_stock BEFOREINSERTONorder_itemsFOR EACH ROWEXECUTEFUNCTIONdecrease_stock();-- 4. 创建物化视图：每日销售统计CREATEMATERIALIZEDVIEWdaily_sales_reportASSELECTDATE(o.created_at)assale_date,p.category,COUNT(oi.item_id)asitems_sold,SUM(oi.quantity*oi.price_at_purchase)astotal_revenueFROMorders oJOINorder_items oiONo.order_id=oi.order_idJOINproducts pONoi.product_id=p.product_idWHEREo.status='COMPLETED'-- 仅统计已完成的订单GROUPBYDATE(o.created_at),p.categoryORDERBYsale_dateDESC,total_revenueDESC;-- 为物化视图创建唯一索引 (以便并发刷新)CREATEUNIQUEINDEXidx_daily_sales_date_catONdaily_sales_report(sale_date,category);

3.2 业务操作演示

-- === 场景 A: 正常下单 ===BEGIN;-- 1. 创建订单INSERTINTOorders(user_id,status)VALUES(1,'PENDING')RETURNINGorder_id;-- 假设返回 order_id = 1-- 2. 添加订单项 (触发器会自动扣减库存)INSERTINTOorder_items(order_id,product_id,quantity,price_at_purchase)VALUES(1,1,2,29.99);-- 买2件红色T恤-- 3. 更新订单状态为完成UPDATEordersSETstatus='COMPLETED'WHEREorder_id=1;COMMIT;-- 验证库存是否减少SELECTname,stockFROMproductsWHEREproduct_id=1;-- 预期：stock 从 100 变为 98-- === 场景 B: 库存不足测试 ===BEGIN;INSERTINTOorders(user_id,status)VALUES(2,'PENDING')RETURNINGorder_id;-- 假设返回 order_id = 2-- 尝试购买 1000 件 (远超库存)INSERTINTOorder_items(order_id,product_id,quantity,price_at_purchase)VALUES(2,1,1000,29.99);-- 预期报错：ERROR: 库存不足！...ROLLBACK;-- 事务回滚，数据不变-- === 场景 C: 复杂查询 (JSONB + 物化视图) ===-- 1. 查询所有 "Red" 颜色的商品 (利用 JSONB)SELECTname,priceFROMproductsWHEREattributes->>'color'='Red';-- 2. 刷新物化视图 (通常在每天凌晨定时任务执行)REFRESH MATERIALIZEDVIEWCONCURRENTLY daily_sales_report;-- 3. 查询昨天的销售冠军分类SELECTcategory,total_revenueFROMdaily_sales_reportWHEREsale_date=CURRENT_DATE-INTERVAL'1 day'ORDERBYtotal_revenueDESCLIMIT1;