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(age0),-- 年龄整数必须大于等于0emailVARCHAR(100)UNIQUE,-- 邮箱唯一值created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP-- 创建时间默认当前时间);-- 注释-- SERIAL 是 PostgreSQL 特有的自增整数类型-- PRIMARY KEY 唯一标识每一行-- NOT NULL 不允许为空-- CHECK 约束数据范围-- UNIQUE 不允许重复-- DEFAULT 设置默认值3. 数据类型PostgreSQL 支持丰富的数据类型类型说明示例SERIAL自增整数id SERIALVARCHAR(n)可变长度字符串name VARCHAR(50)INT整数age INTTEXT长文本description TEXTBOOLEAN布尔值is_active BOOLEANDATE日期birth_date DATETIMESTAMP日期时间created_at TIMESTAMPNUMERIC(p,s)精确小数price NUMERIC(10,2) 案例创建商品表包含多种数据类型CREATETABLEproducts(product_idSERIALPRIMARYKEY,product_nameVARCHAR(100)NOTNULL,priceNUMERIC(10,2)CHECK(price0),-- 价格保留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(quantity0),PRIMARYKEY(order_id,product_id)-- 复合主键一个订单中同一商品只能出现一次);二、SQL语言基础1. 插入数据INSERTINSERTINTOtable_name(col1,col2,...)VALUES(val1,val2,...); 案例-- 插入学生数据INSERTINTOstudents(name,age,email)VALUES(张三,20,zhangsanexample.com);-- 插入多条数据INSERTINTOstudents(name,age,email)VALUES(李四,22,lisiexample.com),(王五,19,wangwuexample.com);2. 查询数据SELECTSELECTcolumn1,column2FROMtable_nameWHEREcondition; 案例-- 查询所有学生SELECT*FROMstudents;-- 查询特定列SELECTname,ageFROMstudents;-- 带条件查询SELECTname,emailFROMstudentsWHEREage20;-- 排序SELECT*FROMstudentsORDERBYageDESC;-- 限制结果数量SELECT*FROMstudentsLIMIT3;3. 更新数据UPDATEUPDATEtable_nameSETcolumn1value1WHEREcondition; 案例-- 更新张三的邮箱UPDATEstudentsSETemailzhangsan_newexample.comWHEREname张三;-- 更新多个字段UPDATEstudentsSETageage1,-- 年龄1last_updatedNOW()-- 更新时间WHEREid1;4. 删除数据DELETEDELETEFROMtable_nameWHEREcondition; 案例-- 删除年龄小于18的学生DELETEFROMstudentsWHEREage18;-- ⚠️ 删除所有数据慎用DELETEFROMstudents;-- 更安全的方式是使用 TRUNCATE重置自增IDTRUNCATETABLEstudents RESTARTIDENTITY;三、PostgreSQL 特性与优势1. 客户机-服务器架构PostgreSQL 采用 C/S 架构客户端通过网络连接服务器进行数据操作。# 命令行连接示例需安装 psqlpsql -h localhost -U postgres -d mydb2. 开源优势免费使用无商业限制社区活跃持续更新支持扩展如 PostGIS、JSONB、全文搜索等3. PostgreSQL 特色语法✅ 返回插入/更新的值RETURNING-- 插入后返回自动生成的IDINSERTINTOstudents(name,age,email)VALUES(赵六,25,zhaoliuexample.com)RETURNINGid,name;-- 更新后返回旧值或新值UPDATEstudentsSETage30WHEREname赵六RETURNING*;-- 返回更新后的整行✅ 使用 WITHCTE公共表表达式-- 查询年龄大于平均年龄的学生WITHavg_ageAS(SELECTAVG(age)asavg_valFROMstudents)SELECTname,ageFROMstudents,avg_ageWHEREstudents.ageavg_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-nameASusernameFROMuser_profilesWHEREprofile-nameAlice;-- 查询数组元素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# 连接数据库connpsycopg2.connect(hostlocalhost,databasemydb,userpostgres,passwordyourpassword)curconn.cursor()# 执行查询cur.execute(SELECT name, age FROM students WHERE age %s,(20,))rowscur.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,3INSERTINTObooks(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_idb.book_idJOINauthors aONb.author_ida.author_idWHEREbr.is_returnedFALSEORbr.return_dateISNULL;-- 查询每本书的借阅次数SELECTb.title,COUNT(br.borrow_id)AS借阅次数FROMbooks bLEFTJOINborrows brONb.book_idbr.book_idGROUPBYb.book_id,b.titleORDERBY借阅次数DESC;-- 查询最贵的3本书SELECTtitle,author_id,priceFROMbooksORDERBYpriceDESCLIMIT3;步骤4更新与维护-- 标记某本书已归还UPDATEborrowsSETis_returnedTRUE,return_dateCURRENT_DATEWHEREborrow_id1;-- 删除不再出版的书籍级联删除借阅记录DELETEFROMbooksWHEREbook_id3;-- 由于外键 ON DELETE CASCADE相关借阅记录也会被自动删除✅ 学习建议动手实践每个语法点都要亲自敲代码观察结果。善用\d和\dt在 psql 中查看表结构。阅读官方文档https://www.postgresql.org/docs/16/尝试复杂查询多表 JOIN、子查询、窗口函数等。学习事务与索引后续章节重点。 附常用命令速查表功能SQL 语句创建表CREATE TABLE ...插入数据INSERT INTO ... VALUES ...查询数据SELECT ... FROM ... WHERE ...更新数据UPDATE ... SET ... WHERE ...删除数据DELETE FROM ... WHERE ...添加列ALTER TABLE ... ADD COLUMN ...删除表DROP TABLE ...查看表结构\d table_namepsql中返回插入IDINSERT ... RETURNING id事务BEGIN; ... COMMIT; / ROLLBACK;提示PostgreSQL 16 新增了并行化改进、逻辑复制增强、SQL/JSON 标准支持等建议后续学习时关注官方 Release Notes。✅ 本章内容覆盖了从零开始学习 PostgreSQL 所需的核心语法和实战技能建议保存为学习手册反复练习