PostgreSQL 入门学习教程，从入门到精通，PostgreSQL 16 语法知识点与案例详解(1）

PostgreSQL 16 语法知识点与案例详解

一、数据库基础

1. 什么是数据库？

数据库（Database）是结构化数据的集合，用于高效存储、检索和管理数据。PostgreSQL 是一个功能强大的开源关系型数据库管理系统（RDBMS）。

2. 表（Table）

表是数据库中存储数据的基本结构，由行（记录）和列（字段）组成。

✅ 语法：创建表

CREATETABLEtable_name(column1 datatype constraints,column2 datatype constraints,...);

📌 案例：创建学生表

-- 创建一个名为 students 的表CREATETABLEstudents(idSERIALPRIMARYKEY,-- SERIAL 自动递增，PRIMARY KEY 主键约束nameVARCHAR(50)NOTNULL,-- 姓名，最大50字符，不允许为空ageINTCHECK(age>=0),-- 年龄，整数，必须大于等于0emailVARCHAR(100)UNIQUE,-- 邮箱，唯一值created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP-- 创建时间，默认当前时间);-- 注释：-- SERIAL 是 PostgreSQL 特有的自增整数类型-- PRIMARY KEY 唯一标识每一行-- NOT NULL 不允许为空-- CHECK 约束数据范围-- UNIQUE 不允许重复-- DEFAULT 设置默认值

3. 数据类型

PostgreSQL 支持丰富的数据类型：

📌 案例：创建商品表（包含多种数据类型）

CREATETABLEproducts(product_idSERIALPRIMARYKEY,product_nameVARCHAR(100)NOTNULL,priceNUMERIC(10,2)CHECK(price>0),-- 价格，保留2位小数，必须大于0is_availableBOOLEANDEFAULTTRUE,-- 是否上架，默认为真descriptionTEXT,-- 商品描述，长文本release_dateDATE,-- 发布日期last_updatedTIMESTAMPDEFAULTNOW()-- 最后更新时间);

4. 主键（Primary Key）

主键用于唯一标识表中的每一行，不能为 NULL，且必须唯一。

✅ 语法：定义主键

-- 在列定义时指定column_name datatypePRIMARYKEY-- 或在表级指定（支持复合主键）PRIMARYKEY(col1,col2)

📌 案例：订单明细表（复合主键）

CREATETABLEorder_items(order_idINTNOTNULL,product_idINTNOTNULL,quantityINTCHECK(quantity>0),PRIMARYKEY(order_id,product_id)-- 复合主键：一个订单中同一商品只能出现一次);

二、SQL语言基础

1. 插入数据（INSERT）

INSERTINTOtable_name(col1,col2,...)VALUES(val1,val2,...);

📌 案例：

-- 插入学生数据INSERTINTOstudents(name,age,email)VALUES('张三',20,'zhangsan@example.com');-- 插入多条数据INSERTINTOstudents(name,age,email)VALUES('李四',22,'lisi@example.com'),('王五',19,'wangwu@example.com');

2. 查询数据（SELECT）

SELECTcolumn1,column2FROMtable_nameWHEREcondition;

📌 案例：

-- 查询所有学生SELECT*FROMstudents;-- 查询特定列SELECTname,ageFROMstudents;-- 带条件查询SELECTname,emailFROMstudentsWHEREage>=20;-- 排序SELECT*FROMstudentsORDERBYageDESC;-- 限制结果数量SELECT*FROMstudentsLIMIT3;

3. 更新数据（UPDATE）

UPDATEtable_nameSETcolumn1=value1WHEREcondition;

📌 案例：

-- 更新张三的邮箱UPDATEstudentsSETemail='zhangsan_new@example.com'WHEREname='张三';-- 更新多个字段UPDATEstudentsSETage=age+1,-- 年龄+1last_updated=NOW()-- 更新时间WHEREid=1;

4. 删除数据（DELETE）

DELETEFROMtable_nameWHEREcondition;

📌 案例：

-- 删除年龄小于18的学生DELETEFROMstudentsWHEREage<18;-- ⚠️ 删除所有数据（慎用！）DELETEFROMstudents;-- 更安全的方式是使用 TRUNCATE（重置自增ID）TRUNCATETABLEstudents RESTARTIDENTITY;

三、PostgreSQL 特性与优势

1. 客户机-服务器架构

PostgreSQL 采用 C/S 架构，客户端通过网络连接服务器进行数据操作。

# 命令行连接示例（需安装 psql）psql -h localhost -U postgres -d mydb

2. 开源优势

• 免费使用，无商业限制
• 社区活跃，持续更新
• 支持扩展（如 PostGIS、JSONB、全文搜索等）

3. PostgreSQL 特色语法

✅ 返回插入/更新的值（RETURNING）

-- 插入后返回自动生成的IDINSERTINTOstudents(name,age,email)VALUES('赵六',25,'zhaoliu@example.com')RETURNINGid,name;-- 更新后返回旧值或新值UPDATEstudentsSETage=30WHEREname='赵六'RETURNING*;-- 返回更新后的整行

✅ 使用 WITH（CTE，公共表表达式）

-- 查询年龄大于平均年龄的学生WITHavg_ageAS(SELECTAVG(age)asavg_valFROMstudents)SELECTname,ageFROMstudents,avg_ageWHEREstudents.age>avg_age.avg_val;

✅ JSONB 数据类型（PostgreSQL 特色）

CREATETABLEuser_profiles(idSERIALPRIMARYKEY,user_idINTUNIQUE,profile JSONB-- 存储灵活的JSON结构);-- 插入JSON数据INSERTINTOuser_profiles(user_id,profile)VALUES(1,'{"name": "Alice", "hobbies": ["reading", "swimming"], "active": true}');-- 查询JSON字段SELECTprofile->>'name'ASusernameFROMuser_profilesWHEREprofile->>'name'='Alice';-- 查询数组元素SELECT*FROMuser_profilesWHEREprofile->'hobbies'?'reading';-- 是否包含 "reading"

四、数据库访问技术

1. 使用 psql 命令行工具

# 登录psql -U postgres -d mydb# 常用命令\l -- 列出所有数据库\c mydb -- 切换数据库\dt -- 列出当前数据库所有表\d students -- 查看表结构\q -- 退出

2. 使用图形化工具（如 pgAdmin、DBeaver）

略 —— 可视化操作，适合初学者。

3. 编程语言连接（Python + psycopg2 示例）

importpsycopg2# 连接数据库conn=psycopg2.connect(host="localhost",database="mydb",user="postgres",password="yourpassword")cur=conn.cursor()# 执行查询cur.execute("SELECT name, age FROM students WHERE age > %s",(20,))rows=cur.fetchall()forrowinrows:print(f"姓名:{row[0]}, 年龄:{row[1]}")cur.close()conn.close()

五、综合性实战案例

🎯 案例：图书管理系统

步骤1：创建数据库和表

-- 创建数据库（在 psql 中执行）CREATEDATABASElibrary;-- 切换数据库\c library-- 创建作者表CREATETABLEauthors(author_idSERIALPRIMARYKEY,nameVARCHAR(100)NOTNULL,birth_yearINT,countryVARCHAR(50));-- 创建图书表CREATETABLEbooks(book_idSERIALPRIMARYKEY,titleVARCHAR(200)NOTNULL,author_idINTREFERENCESauthors(author_id)ONDELETECASCADE,-- 外键publish_yearINT,isbnVARCHAR(13)UNIQUE,priceNUMERIC(8,2),in_stockBOOLEANDEFAULTTRUE);-- 创建借阅记录表CREATETABLEborrows(borrow_idSERIALPRIMARYKEY,book_idINTREFERENCESbooks(book_id),borrower_nameVARCHAR(100),borrow_dateDATEDEFAULTCURRENT_DATE,return_dateDATE,is_returnedBOOLEANDEFAULTFALSE);

步骤2：插入测试数据

-- 插入作者INSERTINTOauthors(name,birth_year,country)VALUES('鲁迅',1881,'中国'),('村上春树',1949,'日本'),('J.K.罗琳',1965,'英国')RETURNINGauthor_id;-- 插入图书（假设作者ID为1,2,3）INSERTINTObooks(title,author_id,publish_year,isbn,price)VALUES('呐喊',1,1923,'9787020000011',35.00),('挪威的森林',2,1987,'9784000000022',45.50),('哈利波特与魔法石',3,1997,'9780747532699',55.00);-- 插入借阅记录INSERTINTOborrows(book_id,borrower_name,return_date)VALUES(1,'张三','2025-09-20'),(2,'李四',NULL);-- 未归还

步骤3：综合查询

-- 查询所有在借书籍（未归还）SELECTb.titleAS书名,a.nameAS作者,br.borrower_nameAS借阅人,br.borrow_dateAS借出日期FROMborrows brJOINbooks bONbr.book_id=b.book_idJOINauthors aONb.author_id=a.author_idWHEREbr.is_returned=FALSEORbr.return_dateISNULL;-- 查询每本书的借阅次数SELECTb.title,COUNT(br.borrow_id)AS借阅次数FROMbooks bLEFTJOINborrows brONb.book_id=br.book_idGROUPBYb.book_id,b.titleORDERBY借阅次数DESC;-- 查询最贵的3本书SELECTtitle,author_id,priceFROMbooksORDERBYpriceDESCLIMIT3;

步骤4：更新与维护

-- 标记某本书已归还UPDATEborrowsSETis_returned=TRUE,return_date=CURRENT_DATEWHEREborrow_id=1;-- 删除不再出版的书籍（级联删除借阅记录）DELETEFROMbooksWHEREbook_id=3;-- 由于外键 ON DELETE CASCADE，相关借阅记录也会被自动删除

✅ 学习建议

1. 动手实践：每个语法点都要亲自敲代码，观察结果。
2. 善用\d和\dt：在 psql 中查看表结构。
3. 阅读官方文档：https://www.postgresql.org/docs/16/
4. 尝试复杂查询：多表 JOIN、子查询、窗口函数等。
5. 学习事务与索引：后续章节重点。

📚 附：常用命令速查表

📌提示：PostgreSQL 16 新增了并行化改进、逻辑复制增强、SQL/JSON 标准支持等，建议后续学习时关注官方 Release Notes。

✅ 本章内容覆盖了从零开始学习 PostgreSQL 所需的核心语法和实战技能，建议保存为学习手册，反复练习！