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

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

一、数据库基础

1. 什么是数据库？

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

2. 表（Table）

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

✅ 语法：创建表

CREATETABLE table_name ( column1 datatype constraints, column2 datatype constraints,...);

📌 案例：创建学生表

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

3. 数据类型

PostgreSQL 支持丰富的数据类型：

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

CREATETABLE products ( product_id SERIALPRIMARYKEY, product_name VARCHAR(100)NOTNULL, price NUMERIC(10,2)CHECK(price >0),-- 价格，保留2位小数，必须大于0 is_available BOOLEANDEFAULTTRUE,-- 是否上架，默认为真 description TEXT,-- 商品描述，长文本 release_date DATE,-- 发布日期 last_updated TIMESTAMPDEFAULTNOW()-- 最后更新时间);

4. 主键（Primary Key）

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

✅ 语法：定义主键

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

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

CREATETABLE order_items ( order_id INTNOTNULL, product_id INTNOTNULL, quantity INTCHECK(quantity >0),PRIMARYKEY(order_id, product_id)-- 复合主键：一个订单中同一商品只能出现一次);

二、SQL语言基础

1. 插入数据（INSERT）

INSERTINTO table_name (col1, col2,...)VALUES(val1, val2,...);

📌 案例：

-- 插入学生数据INSERTINTO students (name, age, email)VALUES('张三',20,'[email protected]');-- 插入多条数据INSERTINTO students (name, age, email)VALUES('李四',22,'[email protected]'),('王五',19,'[email protected]');

2. 查询数据（SELECT）

SELECT column1, column2 FROM table_name WHERE condition;

📌 案例：

-- 查询所有学生SELECT*FROM students;-- 查询特定列SELECT name, age FROM students;-- 带条件查询SELECT name, email FROM students WHERE age >=20;-- 排序SELECT*FROM students ORDERBY age DESC;-- 限制结果数量SELECT*FROM students LIMIT3;

3. 更新数据（UPDATE）

UPDATE table_name SET column1 = value1 WHERE condition;

📌 案例：

-- 更新张三的邮箱UPDATE students SET email ='[email protected]'WHERE name ='张三';-- 更新多个字段UPDATE students SET age = age +1,-- 年龄+1 last_updated =NOW()-- 更新时间WHERE id =1;

4. 删除数据（DELETE）

DELETEFROM table_name WHERE condition;

📌 案例：

-- 删除年龄小于18的学生DELETEFROM students WHERE age <18;-- ⚠️ 删除所有数据（慎用！）DELETEFROM students;-- 更安全的方式是使用 TRUNCATE（重置自增ID）TRUNCATETABLE students RESTART IDENTITY;

三、PostgreSQL 特性与优势

1. 客户机-服务器架构

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

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

2. 开源优势

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

3. PostgreSQL 特色语法

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

-- 插入后返回自动生成的IDINSERTINTO students (name, age, email)VALUES('赵六',25,'[email protected]')RETURNING id, name;-- 更新后返回旧值或新值UPDATE students SET age =30WHERE name ='赵六'RETURNING*;-- 返回更新后的整行

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

-- 查询年龄大于平均年龄的学生WITH avg_age AS(SELECTAVG(age)as avg_val FROM students )SELECT name, age FROM students, avg_age WHERE students.age > avg_age.avg_val;

✅ JSONB 数据类型（PostgreSQL 特色）

CREATETABLE user_profiles ( id SERIALPRIMARYKEY, user_id INTUNIQUE, profile JSONB -- 存储灵活的JSON结构);-- 插入JSON数据INSERTINTO user_profiles (user_id, profile)VALUES(1,'{"name": "Alice", "hobbies": ["reading", "swimming"], "active": true}');-- 查询JSON字段SELECT profile->>'name'AS username FROM user_profiles WHERE profile->>'name'='Alice';-- 查询数组元素SELECT*FROM user_profiles WHERE profile->'hobbies' ? 'reading';-- 是否包含 "reading"

四、数据库访问技术

1. 使用 psql 命令行工具

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

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

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

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

import psycopg2 # 连接数据库 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()for row in rows:print(f"姓名: {row[0]}, 年龄: {row[1]}") cur.close() conn.close()

五、综合性实战案例

🎯 案例：图书管理系统

步骤1：创建数据库和表

-- 创建数据库（在 psql 中执行）CREATEDATABASE library;-- 切换数据库 \c library -- 创建作者表CREATETABLE authors ( author_id SERIALPRIMARYKEY, name VARCHAR(100)NOTNULL, birth_year INT, country VARCHAR(50));-- 创建图书表CREATETABLE books ( book_id SERIALPRIMARYKEY, title VARCHAR(200)NOTNULL, author_id INTREFERENCES authors(author_id)ONDELETECASCADE,-- 外键 publish_year INT, isbn VARCHAR(13)UNIQUE, price NUMERIC(8,2), in_stock BOOLEANDEFAULTTRUE);-- 创建借阅记录表CREATETABLE borrows ( borrow_id SERIALPRIMARYKEY, book_id INTREFERENCES books(book_id), borrower_name VARCHAR(100), borrow_date DATEDEFAULTCURRENT_DATE, return_date DATE, is_returned BOOLEANDEFAULTFALSE);

步骤2：插入测试数据

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

步骤3：综合查询

-- 查询所有在借书籍（未归还）SELECT b.title AS 书名, a.name AS 作者, br.borrower_name AS 借阅人, br.borrow_date AS 借出日期 FROM borrows br JOIN books b ON br.book_id = b.book_id JOIN authors a ON b.author_id = a.author_id WHERE br.is_returned =FALSEOR br.return_date ISNULL;-- 查询每本书的借阅次数SELECT b.title,COUNT(br.borrow_id)AS 借阅次数 FROM books b LEFTJOIN borrows br ON b.book_id = br.book_id GROUPBY b.book_id, b.title ORDERBY 借阅次数 DESC;-- 查询最贵的3本书SELECT title, author_id, price FROM books ORDERBY price DESCLIMIT3;

步骤4：更新与维护

-- 标记某本书已归还UPDATE borrows SET is_returned =TRUE, return_date =CURRENT_DATEWHERE borrow_id =1;-- 删除不再出版的书籍（级联删除借阅记录）DELETEFROM books WHERE book_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 所需的核心语法和实战技能，建议保存为学习手册，反复练习！