PostgreSQL 16 查询数据 —语法详解与实战案例

---

PostgreSQL 16 查询数据（含示例数据）

为配合《PostgreSQL 16 查询数据 —— 语法详解与实战案例》一文，现提供完整的数据库、数据表结构及初始示例数据，便于读者逐条运行书中所有案例代码，获得真实执行效果。

---

🗃️创建数据库

-- 创建教学用数据库（若不存在）
CREATE DATABASE school_db 
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'zh_CN.UTF-8'   -- 中文排序支持
    LC_CTYPE = 'zh_CN.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

-- 连接数据库（在 psql 中执行：\c school_db）
-- 或在图形化工具中切换数据库

✅ 建议使用 school_db 作为本章所有操作的数据库。

---

📊 创建数据表结构

1. 学生表 students

-- 删除已存在表（避免重复执行报错）
DROP TABLE IF EXISTS students;

-- 创建 students 表
CREATE TABLE students (
    id SERIAL PRIMARY KEY,                    -- 学生ID，自增主键
    name VARCHAR(50) NOT NULL,               -- 姓名
    age INT CHECK (age >= 10 AND age <= 25), -- 年龄，限制范围
    grade VARCHAR(20),                       -- 年级（如：高一、高二、高三）
    enrollment_date DATE,                    -- 入学日期
    email VARCHAR(100),                      -- 邮箱（用于正则演示）
    phone VARCHAR(20)                        -- 电话（用于正则演示）
);

-- 添加注释
COMMENT ON TABLE students IS '学生基本信息表';
COMMENT ON COLUMN students.id IS '学生唯一ID';
COMMENT ON COLUMN students.name IS '学生姓名';
COMMENT ON COLUMN students.age IS '学生年龄';
COMMENT ON COLUMN students.grade IS '所在年级';
COMMENT ON COLUMN students.enrollment_date IS '入学日期';
COMMENT ON COLUMN students.email IS '电子邮箱';
COMMENT ON COLUMN students.phone IS '联系电话';

---

2. 成绩表 scores

-- 删除已存在表
DROP TABLE IF EXISTS scores;

-- 创建 scores 表
CREATE TABLE scores (
    id SERIAL PRIMARY KEY,              -- 成绩记录ID
    student_id INT NOT NULL,           -- 关联学生ID
    subject VARCHAR(30) NOT NULL,      -- 科目（如：数学、语文、英语）
    score NUMERIC(5,2) CHECK (score >= 0 AND score <= 150), -- 分数（支持小数）
    exam_date DATE,                    -- 考试日期
    teacher_name VARCHAR(50),          -- 任课教师（用于演示连接）
    CONSTRAINT fk_student 
        FOREIGN KEY (student_id) 
        REFERENCES students(id) 
        ON DELETE CASCADE              -- 学生删除时，成绩级联删除
);

-- 添加注释
COMMENT ON TABLE scores IS '学生成绩表';
COMMENT ON COLUMN scores.student_id IS '学生ID，外键';
COMMENT ON COLUMN scores.subject IS '考试科目';
COMMENT ON COLUMN scores.score IS '考试分数';
COMMENT ON COLUMN scores.exam_date IS '考试日期';
COMMENT ON COLUMN scores.teacher_name IS '授课教师姓名';

---

3. 教师表 teachers（用于多表连接演示）

-- 删除已存在表
DROP TABLE IF EXISTS teachers;

-- 创建 teachers 表
CREATE TABLE teachers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    subject VARCHAR(30) UNIQUE,       -- 教授科目（假设每人教一科）
    hire_date DATE,
    phone VARCHAR(20)
);

COMMENT ON TABLE teachers IS '教师信息表';

---

📥 插入示例数据

1. 插入学生数据（20条，覆盖各种查询场景）

-- 清空表（避免重复插入）
TRUNCATE TABLE students RESTART IDENTITY CASCADE;

-- 插入学生数据
INSERT INTO students (name, age, grade, enrollment_date, email, phone) VALUES
('张三', 17, '高二', '2024-09-01', 'zhangsan@email.com', '13800138001'),
('李四', 16, '高一', '2025-09-01', 'lisi@email.com', '13800138002'),
('王五', 18, '高三', '2023-09-01', 'wangwu@email.com', '13800138003'),
('赵六', 17, '高二', '2024-09-01', 'zhaoliu@email.com', '13800138004'),
('孙七', 16, '高一', '2025-09-01', 'sunqi@email.com', '13800138005'),
('周八', 18, '高三', '2023-09-01', 'zhouba@email.com', '13800138006'),
('吴九', 17, '高二', '2024-09-01', 'wujiu@email.com', '13800138007'),
('郑十', 15, '高一', '2025-09-01', 'zhengshi@email.com', '13800138008'),
('钱十一', 19, '高三', '2023-09-01', 'qianshiyi@email.com', '13800138009'),
('冯十二', 17, '高二', '2024-09-01', 'fengshier@email.com', '13800138010'),
('陈十三', 16, '高一', '2025-09-01', NULL, NULL),  -- 邮箱和电话为空
('褚十四', 18, '高三', '2023-09-01', 'chushisi@email.com', '13800138012'),
('卫十五', 17, '高二', '2024-09-01', 'weishiwu@email.com', '13800138013'),
('蒋十六', 16, '高一', '2025-09-01', 'jiangshiliu@email.com', '13800138014'),
('沈十七', 18, '高三', '2023-09-01', 'shenshiqi@email.com', '13800138015'),
('韩十八', 17, '高二', NULL, 'hanshiba@email.com', '13800138016'), -- 入学日期为空
('杨十九', 16, '高一', '2025-09-01', 'yangshijiu@email.com', '13800138017'),
('朱二十', 18, '高三', '2023-09-01', 'zhuershi@email.com', '13800138018'),
('秦二十一', 17, '高二', '2024-09-01', 'qiner shiyi@email.com', '13800138019'), -- 姓名含空格
('许二十二', 15, '高一', '2025-09-01', 'xuer shi er@school.edu.cn', '13800138020'); -- 邮箱含.edu.cn

---

2. 插入成绩数据（覆盖多科目、多学生、高低分）

-- 清空成绩表
TRUNCATE TABLE scores RESTART IDENTITY CASCADE;

-- 插入成绩数据（每位学生3科成绩）
INSERT INTO scores (student_id, subject, score, exam_date, teacher_name) VALUES
-- 张三
(1, '数学', 95.5, '2025-06-15', '张老师'),
(1, '语文', 88.0, '2025-06-16', '李老师'),
(1, '英语', 92.0, '2025-06-17', '王老师'),
-- 李四
(2, '数学', 85.0, '2025-06-15', '张老师'),
(2, '语文', 90.5, '2025-06-16', '李老师'),
(2, '英语', 87.0, '2025-06-17', '王老师'),
-- 王五
(3, '数学', 135.0, '2025-06-15', '张老师'),
(3, '语文', 120.0, '2025-06-16', '李老师'),
(3, '英语', 128.0, '2025-06-17', '王老师'),
-- 赵六
(4, '数学', 78.0, '2025-06-15', '张老师'),
(4, '语文', 82.5, '2025-06-16', '李老师'),
(4, '英语', 80.0, '2025-06-17', '王老师'),
-- 孙七
(5, '数学', 92.0, '2025-06-15', '张老师'),
(5, '语文', 89.0, '2025-06-16', '李老师'),
(5, '英语', 94.5, '2025-06-17', '王老师'),
-- 周八
(6, '数学', 110.0, '2025-06-15', '张老师'),
(6, '语文', 105.0, '2025-06-16', '李老师'),
(6, '英语', 112.0, '2025-06-17', '王老师'),
-- 吴九
(7, '数学', 88.0, '2025-06-15', '张老师'),
(7, '语文', 91.5, '2025-06-16', '李老师'),
(7, '英语', 89.0, '2025-06-17', '王老师'),
-- 郑十
(8, '数学', 75.0, '2025-06-15', '张老师'),
(8, '语文', 80.0, '2025-06-16', '李老师'),
(8, '英语', 77.5, '2025-06-17', '王老师'),
-- 钱十一
(9, '数学', 125.0, '2025-06-15', '张老师'),
(9, '语文', 118.0, '2025-06-16', '李老师'),
(9, '英语', 122.0, '2025-06-17', '王老师'),
-- 冯十二
(10, '数学', 90.0, '2025-06-15', '张老师'),
(10, '语文', 87.0, '2025-06-16', '李老师'),
(10, '英语', 93.0, '2025-06-17', '王老师'),
-- 陈十三（部分成绩缺失，用于演示外连接）
(11, '数学', 82.0, '2025-06-15', '张老师'),
(11, '语文', 79.0, '2025-06-16', '李老师'),
-- 褚十四
(12, '数学', 130.0, '2025-06-15', '张老师'),
(12, '语文', 125.0, '2025-06-16', '李老师'),
(12, '英语', 127.0, '2025-06-17', '王老师'),
-- 卫十五
(13, '数学', 86.0, '2025-06-15', '张老师'),
(13, '语文', 84.0, '2025-06-16', '李老师'),
(13, '英语', 88.0, '2025-06-17', '王老师'),
-- 蒋十六
(14, '数学', 79.0, '2025-06-15', '张老师'),
(14, '语文', 83.0, '2025-06-16', '李老师'),
(14, '英语', 81.0, '2025-06-17', '王老师'),
-- 沈十七
(15, '数学', 118.0, '2025-06-15', '张老师'),
(15, '语文', 112.0, '2025-06-16', '李老师'),
(15, '英语', 115.0, '2025-06-17', '王老师'),
-- 韩十八（入学日期为空，用于空值查询）
(16, '数学', 94.0, '2025-06-15', '张老师'),
(16, '语文', 90.0, '2025-06-16', '李老师'),
(16, '英语', 96.0, '2025-06-17', '王老师'),
-- 杨十九
(17, '数学', 83.0, '2025-06-15', '张老师'),
(17, '语文', 86.0, '2025-06-16', '李老师'),
(17, '英语', 84.0, '2025-06-17', '王老师'),
-- 朱二十
(18, '数学', 122.0, '2025-06-15', '张老师'),
(18, '语文', 119.0, '2025-06-16', '李老师'),
(18, '英语', 124.0, '2025-06-17', '王老师'),
-- 秦二十一（姓名含空格）
(19, '数学', 89.0, '2025-06-15', '张老师'),
(19, '语文', 92.0, '2025-06-16', '李老师'),
(19, '英语', 91.0, '2025-06-17', '王老师'),
-- 许二十二（邮箱含.edu.cn）
(20, '数学', 81.0, '2025-06-15', '张老师'),
(20, '语文', 78.0, '2025-06-16', '李老师'),
(20, '英语', 80.0, '2025-06-17', '王老师');

---

3. 插入教师数据（用于三表连接）

-- 清空教师表
TRUNCATE TABLE teachers RESTART IDENTITY CASCADE;

-- 插入教师数据
INSERT INTO teachers (name, subject, hire_date, phone) VALUES
('张老师', '数学', '2010-08-01', '13900000001'),
('李老师', '语文', '2012-09-01', '13900000002'),
('王老师', '英语', '2015-03-01', '13900000003'),
('赵老师', '物理', '2018-07-01', '13900000004'), -- 暂无学生成绩
('孙老师', '化学', '2020-01-01', '13900000005'); -- 暂无学生成绩

---

✅ 验证数据完整性

-- 查看学生总数
SELECT COUNT(*) AS total_students FROM students;

-- 查看成绩总数
SELECT COUNT(*) AS total_scores FROM scores;

-- 查看教师总数
SELECT COUNT(*) AS total_teachers FROM teachers;

-- 查看各年级学生数
SELECT grade, COUNT(*) FROM students GROUP BY grade ORDER BY grade;

-- 查看各科目最高分
SELECT subject, MAX(score) FROM scores GROUP BY subject ORDER BY subject;

---

🧪 配套查询验证示例

以下查询可直接运行，验证数据是否正确导入：

-- 1. 查询所有高二学生
SELECT name, age FROM students WHERE grade = '高二';

-- 2. 查询数学成绩大于90分的学生姓名
SELECT s.name, sc.score 
FROM students s 
JOIN scores sc ON s.id = sc.student_id 
WHERE sc.subject = '数学' AND sc.score > 90;

-- 3. 查询邮箱以 .com 结尾的学生（正则）
SELECT name, email FROM students WHERE email ~ '\.com$';

-- 4. 查询平均分最高的学生
SELECT s.name, AVG(sc.score) as avg_score
FROM students s
JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id, s.name
ORDER BY avg_score DESC
LIMIT 1;

---

📝 数据特点说明（便于理解案例）

特性	说明
NULL值	陈十三（id=11）邮箱和电话为NULL；韩十八（id=16）入学日期为NULL
特殊字符	秦二十一姓名含空格；许二十二邮箱含 .edu.cn
成绩分布	高三学生成绩普遍较高（模拟备考状态），高一较低
缺失成绩	陈十三只有数学和语文成绩，无英语成绩（用于外连接演示）
教师覆盖	仅数学、语文、英语有成绩记录，物理、化学教师无关联成绩
年龄范围	15~19岁，覆盖高一到高三

---

✅ 使用说明：

1. 复制上述所有 SQL 代码，在 PostgreSQL 客户端（如 psql、pgAdmin、DBeaver）中按顺序执行。
2. 执行后即可在 school_db 数据库中看到 students, scores, teachers 三张表及完整数据。
3. 本书所有案例代码均可直接运行，结果与文中描述一致。
4. 可根据需要自行增删改数据，进行更多探索性查询。

---

此配套数据结构完整覆盖书中所有查询语法点，包括：
✅ 基础查询、条件过滤、聚合函数、分组、排序、LIMIT
✅ 内连接、外连接、多表连接
✅ 子查询（IN/EXISTS/ANY/ALL）
✅ 正则表达式匹配
✅ 空值处理、别名使用、集合操作等

一、基本查询语句

1.1 查询所有字段（SELECT *）

语法：

SELECT * FROM table_name;

✅ 说明：* 表示选择所有列。适用于快速查看全表数据，但生产环境建议显式指定字段。

案例代码：

-- 查询 students 表中所有学生记录
SELECT * FROM students;

-- 查询时可加注释说明
SELECT * FROM students; -- 查看所有在校学生信息

📌 注释：

• * 是通配符，代表“所有列”。
• 不推荐在生产代码中使用，因表结构变更可能导致程序异常。
• 无 WHERE 时返回全表，大数据表慎用。

---

1.2 查询指定字段

语法：

SELECT column1, column2, ..., columnN 
FROM table_name;

案例代码：

-- 只查询学生姓名和年龄
SELECT name, age FROM students;

-- 查询姓名、年级，并为字段加别名（后续讲解）
SELECT name AS "学生姓名", grade AS "所在年级" 
FROM students;

-- 查询时进行简单计算
SELECT name, age, age + 3 AS "预计毕业年龄" 
FROM students;

📌 注释：

• 字段顺序可自定义，与表结构无关。
• 可使用表达式、函数、常量。
• 推荐方式：明确指定所需字段，减少网络传输和内存开销。

---

1.3 查询指定记录（WHERE 子句）

语法：

SELECT column_list
FROM table_name
WHERE condition;

案例代码：

-- 查询年龄为 18 的学生
SELECT name, age, grade FROM students WHERE age = 18;

-- 查询姓名为 '张三' 的学生
SELECT * FROM students WHERE name = '张三';

-- 查询入学日期为 2025-09-01 的学生
SELECT name, enrollment_date FROM students 
WHERE enrollment_date = '2025-09-01';

-- 使用不等于
SELECT name, grade FROM students WHERE grade != '高三';
-- 或使用 <>
SELECT name, grade FROM students WHERE grade <> '高三';

📌 注释：

• WHERE 后跟布尔表达式，结果为 TRUE 的行被返回。
• 支持比较运算符：=, !=/<>, >, <, >=, <=。
• 字符串用单引号包围（疑问3解答）。

---

二、条件查询进阶

2.1 带 IN 关键字的查询

语法：

SELECT ... FROM ... WHERE column IN (value1, value2, ...);

✅ 等价于多个 OR 条件。

案例代码：

-- 查询年级为 '高一' 或 '高二' 的学生
SELECT name, grade FROM students 
WHERE grade IN ('高一', '高二');

-- 查询特定ID的学生
SELECT id, name FROM students 
WHERE id IN (1, 3, 5, 7);

-- NOT IN 查询不在列表中的记录
SELECT name, grade FROM students 
WHERE grade NOT IN ('高三', '毕业生');

📌 注释：

• IN 后括号内是值列表，支持子查询（见子查询章节）。
• 若列表中有 NULL，NOT IN 可能返回空结果（三值逻辑）。

---

2.2 带 BETWEEN AND 的范围查询

语法：

SELECT ... FROM ... WHERE column BETWEEN value1 AND value2;

✅ 包含边界值，等价于 >= value1 AND <= value2。

案例代码：

-- 查询年龄在 16 到 18 岁之间的学生（包含16和18）
SELECT name, age FROM students 
WHERE age BETWEEN 16 AND 18;

-- 查询入学日期在 2025-09-01 到 2025-09-10 之间的学生
SELECT name, enrollment_date FROM students 
WHERE enrollment_date BETWEEN '2025-09-01' AND '2025-09-10';

-- NOT BETWEEN
SELECT name, age FROM students 
WHERE age NOT BETWEEN 15 AND 17; -- 年龄 <15 或 >17

📌 注释：

• 适用于数值、日期、字符串（按字典序）等可比较类型。
• 边界值顺序：小值在前，大值在后。

---

2.3 带 LIKE 的字符匹配查询（模糊查询）

语法：

SELECT ... FROM ... WHERE column LIKE 'pattern';

✅ 通配符：

• %：匹配任意长度字符串（包括空）
• _：匹配单个任意字符

案例代码：

-- 查询姓名以 '张' 开头的学生
SELECT name FROM students WHERE name LIKE '张%';

-- 查询姓名第二个字是 '小' 的学生（如：王小明、李小红）
SELECT name FROM students WHERE name LIKE '_小%';

-- 查询姓名包含 '三' 的学生
SELECT name FROM students WHERE name LIKE '%三%';

-- NOT LIKE
SELECT name FROM students WHERE name NOT LIKE '张%';

-- 大小写敏感（默认）
-- 若需不敏感，使用 ILIKE（PostgreSQL 扩展）
SELECT name FROM students WHERE name ILIKE 'zhang%'; -- 匹配 Zhang, ZHANG, zhang 等

📌 注释：

• LIKE 默认区分大小写。
• 使用 ILIKE 可实现不区分大小写的匹配（PostgreSQL 特有）。
• 性能：LIKE 以 % 开头时无法使用普通索引，考虑使用 pg_trgm 模块或全文检索。

---

2.4 查询空值（IS NULL / IS NOT NULL）

语法：

SELECT ... FROM ... WHERE column IS NULL;
SELECT ... FROM ... WHERE column IS NOT NULL;

⚠️ 注意：不能用 = NULL，必须用 IS NULL！

案例代码：

-- 假设 students 表中有些学生没有填写年级（grade 为 NULL）
-- 查询年级为空的学生
SELECT name, grade FROM students WHERE grade IS NULL;

-- 查询年级不为空的学生
SELECT name, grade FROM students WHERE grade IS NOT NULL;

-- 错误写法（不会报错，但永远返回空！）
-- SELECT name FROM students WHERE grade = NULL; -- ❌ 错误！

📌 注释：

• NULL 表示“未知”或“不存在”，任何与 NULL 的比较结果都是 UNKNOWN（非 TRUE/FALSE）。
• 必须使用 IS NULL 或 IS NOT NULL 判断。

---

2.5 带 AND 的多条件查询

语法：

SELECT ... FROM ... WHERE condition1 AND condition2 [AND condition3 ...];

✅ 所有条件必须同时为 TRUE。

案例代码：

-- 查询年龄大于等于17岁且年级为'高二'的学生
SELECT name, age, grade FROM students 
WHERE age >= 17 AND grade = '高二';

-- 三个条件：年龄在16-18之间，年级不是'高三'，姓名不以'张'开头
SELECT name, age, grade FROM students 
WHERE age BETWEEN 16 AND 18 
  AND grade != '高三' 
  AND name NOT LIKE '张%';

📌 注释：

• AND 优先级高于 OR，但建议用括号明确逻辑（疑问4解答）。
• 条件顺序不影响结果，但可能影响性能（索引选择）。

---

2.6 带 OR 的多条件查询

语法：

SELECT ... FROM ... WHERE condition1 OR condition2 [OR condition3 ...];

✅ 任一条件为 TRUE 即可。

案例代码：

-- 查询年级为'高一'或年龄小于16岁的学生
SELECT name, age, grade FROM students 
WHERE grade = '高一' OR age < 16;

-- 组合 AND 和 OR（注意括号！）
-- 查询：年级是'高一'，或者（年龄>=18且年级是'高二'）
SELECT name, age, grade FROM students 
WHERE grade = '高一' 
   OR (age >= 18 AND grade = '高二'); -- 括号确保逻辑正确

📌 注释：

• AND 优先级高于 OR，不用括号可能导致逻辑错误。
• 示例：A OR B AND C 等价于 A OR (B AND C)，而非 (A OR B) AND C。

---

三、查询结果处理

3.1 查询结果不重复（DISTINCT）

语法：

SELECT DISTINCT column1, column2, ... FROM table_name;

✅ 去除结果集中完全重复的行。

案例代码：

-- 查询所有不重复的年级
SELECT DISTINCT grade FROM students;

-- 查询不重复的（年级, 年龄）组合
SELECT DISTINCT grade, age FROM students;

-- 错误理解：DISTINCT 作用于所有列，不是单列
-- 下面语句中 DISTINCT 作用于 name 和 grade 两列的组合
SELECT DISTINCT name, grade FROM students;

📌 注释：

• DISTINCT 作用于SELECT 后所有列的组合（疑问1解答）。
• 不能只对某一列去重而保留其他列原值（需用窗口函数或 GROUP BY）。
• 性能：DISTINCT 需要排序或哈希去重，大数据集可能较慢。

---

3.2 对查询结果排序（ORDER BY）

语法：

SELECT ... FROM ... 
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

✅ ASC（升序，默认），DESC（降序）

案例代码：

-- 按年龄升序排列（默认ASC）
SELECT name, age FROM students ORDER BY age;

-- 按年龄降序排列
SELECT name, age FROM students ORDER BY age DESC;

-- 多列排序：先按年级升序，同年级内按年龄降序
SELECT name, grade, age FROM students 
ORDER BY grade ASC, age DESC;

-- 按表达式排序：按预计毕业年龄（age+3）排序
SELECT name, age, age+3 AS graduate_age 
FROM students 
ORDER BY graduate_age DESC;

-- 按列位置排序（不推荐，可读性差）
SELECT name, age FROM students ORDER BY 2 DESC; -- 按第2列（age）降序

📌 注释：

• 可对 SELECT 中的字段、表达式、别名、列位置排序。
• NULL 值默认排在最后（升序）或最前（降序），可用 NULLS FIRST/LAST 调整。
• 可与 LIMIT 混合使用（疑问2解答）。

---

3.3 用 LIMIT 限制查询结果的数量

语法：

SELECT ... FROM ... 
LIMIT count [OFFSET offset];

✅ LIMIT：返回前 count 行
✅ OFFSET：跳过前 offset 行（常用于分页）

案例代码：

-- 只返回前3条记录
SELECT name, age FROM students LIMIT 3;

-- 跳过前2条，返回接下来的3条（分页：第2页，每页3条）
SELECT name, age FROM students LIMIT 3 OFFSET 2;

-- 与 ORDER BY 结合：获取年龄最大的前3名学生
SELECT name, age FROM students 
ORDER BY age DESC 
LIMIT 3;

-- LIMIT 0 返回空集
SELECT * FROM students LIMIT 0;

📌 注释：

• LIMIT 必须在 ORDER BY 之后（如果同时存在）。
• OFFSET 0 等同于省略 OFFSET。
• 分页公式：第 N 页，每页 M 条 → LIMIT M OFFSET (N-1)*M

---

四、使用集合函数查询（聚合函数）

4.1 COUNT() 函数

语法：

COUNT(*)       -- 统计行数（包括NULL）
COUNT(column)  -- 统计该列非NULL值的行数
COUNT(1)       -- 等同于 COUNT(*)，常用于优化

案例代码：

-- 统计学生总人数
SELECT COUNT(*) AS total_students FROM students;

-- 统计有年级信息的学生数（grade非NULL）
SELECT COUNT(grade) AS students_with_grade FROM students;

-- 统计不同年级的数量
SELECT COUNT(DISTINCT grade) AS grade_varieties FROM students;

---

4.2 SUM() 函数

语法：

SUM(column)  -- 对数值列求和

案例代码：

-- 计算所有学生年龄总和
SELECT SUM(age) AS total_age FROM students;

-- 计算高一学生年龄总和
SELECT SUM(age) AS total_age_grade10 
FROM students 
WHERE grade = '高一';

---

4.3 AVG() 函数

语法：

AVG(column)  -- 计算平均值（忽略NULL）

案例代码：

-- 计算学生平均年龄
SELECT AVG(age) AS average_age FROM students;

-- 计算平均年龄（保留2位小数）
SELECT ROUND(AVG(age), 2) AS average_age FROM students;

-- 计算高二学生的平均年龄
SELECT AVG(age) AS avg_age_grade11 
FROM students 
WHERE grade = '高二';

---

4.4 MAX() / MIN() 函数

语法：

MAX(column)  -- 最大值
MIN(column)  -- 最小值

案例代码：

-- 查询最大年龄和最小年龄
SELECT 
    MAX(age) AS max_age,
    MIN(age) AS min_age 
FROM students;

-- 查询最早和最晚的入学日期
SELECT 
    MIN(enrollment_date) AS earliest_enrollment,
    MAX(enrollment_date) AS latest_enrollment 
FROM students;

-- 查询字典序最大的姓名
SELECT MAX(name) AS last_name_alphabetically FROM students;

📌 注释：

• 聚合函数忽略 NULL 值。
• 通常与 GROUP BY 配合使用（见下节）。
• 可对数值、日期、字符串等可比较类型使用。

---

五、分组查询（GROUP BY）

语法：

SELECT column, aggregate_function(...)
FROM table_name
[WHERE condition]
GROUP BY column
[HAVING condition];

✅ GROUP BY：按列分组，每组返回一行
✅ HAVING：对分组后的结果进行过滤（WHERE 不能用于聚合函数）

案例代码：

-- 按年级分组，统计每级人数和平均年龄
SELECT 
    grade,
    COUNT(*) AS student_count,
    AVG(age) AS avg_age
FROM students
GROUP BY grade;

-- 按年级分组，只显示人数大于2的年级（HAVING 过滤分组）
SELECT 
    grade,
    COUNT(*) AS student_count
FROM students
GROUP BY grade
HAVING COUNT(*) > 2;

-- 错误示例：SELECT 中非聚合字段必须出现在 GROUP BY 中
-- SELECT name, grade, COUNT(*) FROM students GROUP BY grade; -- ❌ 错误！name 未分组

-- 正确：要么聚合，要么分组
SELECT grade, COUNT(*) FROM students GROUP BY grade; -- ✅
SELECT grade, name, COUNT(*) FROM students GROUP BY grade, name; -- ✅（但意义不大）

📌 注释：

• SELECT 中的非聚合字段必须出现在 GROUP BY 子句中。
• WHERE 在分组前过滤，HAVING 在分组后过滤。
• 执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

---

六、连接查询（JOIN）

6.1 内连接查询（INNER JOIN）

语法：

SELECT ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
-- INNER 可省略

✅ 只返回两表中满足连接条件的记录。

案例代码：

-- 假设有 scores 表：id, student_id, subject, score
-- 查询学生姓名及其数学成绩
SELECT 
    s.name,
    sc.subject,
    sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
WHERE sc.subject = '数学';

---

6.2 外连接查询

左外连接（LEFT JOIN）：

SELECT ... FROM table1 LEFT JOIN table2 ON condition;

✅ 返回左表所有记录，右表无匹配则填充 NULL。

右外连接（RIGHT JOIN）：

SELECT ... FROM table1 RIGHT JOIN table2 ON condition;

✅ 返回右表所有记录，左表无匹配则填充 NULL。

全外连接（FULL JOIN）：

SELECT ... FROM table1 FULL JOIN table2 ON condition;

✅ 返回两表所有记录，无匹配则填充 NULL。

案例代码：

-- LEFT JOIN：列出所有学生，包括没有成绩的学生
SELECT 
    s.name,
    sc.subject,
    sc.score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id;

-- RIGHT JOIN：列出所有成绩记录，包括找不到学生的信息（理论上不应发生）
SELECT 
    s.name,
    sc.subject,
    sc.score
FROM students s
RIGHT JOIN scores sc ON s.id = sc.student_id;

-- FULL JOIN：列出所有学生和所有成绩，匹配不到的用NULL
SELECT 
    s.name,
    sc.subject,
    sc.score
FROM students s
FULL JOIN scores sc ON s.id = sc.student_id;

---

6.3 复合条件连接查询

语法：

SELECT ...
FROM table1
JOIN table2 ON condition1 AND condition2;

案例代码：

-- 连接条件：学生ID匹配 且 科目是数学或英语
SELECT 
    s.name,
    sc.subject,
    sc.score
FROM students s
INNER JOIN scores sc 
    ON s.id = sc.student_id 
    AND sc.subject IN ('数学', '英语');

-- 多表连接：学生 + 成绩 + 教师表（假设）
SELECT 
    s.name AS student_name,
    sc.subject,
    sc.score,
    t.name AS teacher_name
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN teachers t ON sc.teacher_id = t.id; -- 假设有 teacher_id 字段

---

七、子查询

7.1 带 IN 关键字的子查询

语法：

SELECT ... FROM ... WHERE column IN (SELECT column FROM ...);

案例代码：

-- 查询有成绩记录的学生姓名（子查询返回 student_id 列表）
SELECT name FROM students 
WHERE id IN (SELECT DISTINCT student_id FROM scores);

-- 查询没有成绩的学生
SELECT name FROM students 
WHERE id NOT IN (SELECT student_id FROM scores WHERE student_id IS NOT NULL);
-- 注意：子查询结果含 NULL 时 NOT IN 返回空，故加 IS NOT NULL

---

7.2 带比较运算符的子查询（=, >, < 等）

语法：

SELECT ... FROM ... WHERE column > (SELECT value FROM ...);

✅ 子查询必须返回单行单列（标量子查询）。

案例代码：

-- 查询年龄大于平均年龄的学生
SELECT name, age FROM students 
WHERE age > (SELECT AVG(age) FROM students);

-- 查询入学日期早于最早成绩记录日期的学生（假设 scores 有 exam_date）
SELECT name, enrollment_date FROM students 
WHERE enrollment_date < (SELECT MIN(exam_date) FROM scores);

---

7.3 带 ANY/SOME 关键字的子查询

语法：

SELECT ... FROM ... WHERE column > ANY (SELECT column FROM ...);

✅ ANY/SOME：满足子查询中任意一个值即可（等价于 OR 逻辑）
✅ SOME 是 ANY 的同义词

案例代码：

-- 查询年龄大于任何一个'高一'学生年龄的学生
SELECT name, age FROM students 
WHERE age > ANY (SELECT age FROM students WHERE grade = '高一');

-- 等价于：
-- SELECT name, age FROM students 
-- WHERE age > (SELECT MIN(age) FROM students WHERE grade = '高一');

---

7.4 带 ALL 关键字的子查询

语法：

SELECT ... FROM ... WHERE column > ALL (SELECT column FROM ...);

✅ ALL：必须大于子查询中所有值（等价于 AND 逻辑）

案例代码：

-- 查询年龄大于所有'高一'学生年龄的学生（即比最大小的还大）
SELECT name, age FROM students 
WHERE age > ALL (SELECT age FROM students WHERE grade = '高一');

-- 等价于：
-- SELECT name, age FROM students 
-- WHERE age > (SELECT MAX(age) FROM students WHERE grade = '高一');

---

7.5 带 EXISTS 关键字的子查询

语法：

SELECT ... FROM table1 
WHERE EXISTS (SELECT 1 FROM table2 WHERE condition);

✅ EXISTS：子查询返回至少一行则为 TRUE，不关心具体值，只关心是否存在。
✅ 性能通常优于 IN，尤其当子查询结果集大时。

案例代码：

-- 查询有成绩记录的学生（EXISTS 版本）
SELECT name FROM students s
WHERE EXISTS (
    SELECT 1 FROM scores sc 
    WHERE sc.student_id = s.id
);

-- 查询没有成绩的学生（NOT EXISTS）
SELECT name FROM students s
WHERE NOT EXISTS (
    SELECT 1 FROM scores sc 
    WHERE sc.student_id = s.id
);

📌 注释：

• EXISTS 子查询常用 SELECT 1 或 SELECT *，因不返回实际数据。
• 相关子查询：子查询中引用了外层查询的表（如 s.id）。

---

八、合并查询结果（UNION, INTERSECT, EXCEPT）

语法：

SELECT ... UNION [ALL] SELECT ...;      -- 并集（去重/不去重）
SELECT ... INTERSECT SELECT ...;        -- 交集
SELECT ... EXCEPT SELECT ...;           -- 差集（第一个结果减第二个）

✅ 列数和类型必须兼容。

案例代码：

-- UNION：高一和高三学生名单合并（自动去重）
SELECT name, grade FROM students WHERE grade = '高一'
UNION
SELECT name, grade FROM students WHERE grade = '高三';

-- UNION ALL：不去重（若有人同时满足两个条件会重复）
SELECT name, '高一或高三' AS source FROM students WHERE grade = '高一'
UNION ALL
SELECT name, '高一或高三' FROM students WHERE grade = '高三';

-- INTERSECT：既是高一又年龄>16的学生（理论上无意义，仅为演示）
SELECT id FROM students WHERE grade = '高一'
INTERSECT
SELECT id FROM students WHERE age > 16;

-- EXCEPT：高一学生中排除年龄>16的学生
SELECT name FROM students WHERE grade = '高一'
EXCEPT
SELECT name FROM students WHERE age > 16;

---

九、为表和字段取别名

9.1 为表取别名

语法：

SELECT ... FROM table_name [AS] alias_name;

✅ AS 可省略

案例代码：

-- 为 students 表取别名 s
SELECT s.name, s.age FROM students AS s;

-- 省略 AS
SELECT s.name, s.age FROM students s;

-- 多表连接时别名尤其重要
SELECT 
    s.name AS student_name,
    sc.score
FROM students s
JOIN scores sc ON s.id = sc.student_id;

---

9.2 为字段取别名

语法：

SELECT column [AS] alias_name FROM ...;
SELECT expression AS alias_name FROM ...;

案例代码：

-- 为字段取别名
SELECT name AS "学生姓名", age AS "年龄" FROM students;

-- 为表达式取别名
SELECT name, age, age + 3 AS "预计毕业年龄" FROM students;

-- 别名用于 ORDER BY
SELECT name, age * 12 AS months_old FROM students 
ORDER BY months_old DESC;

-- 别名用于 GROUP BY（部分数据库支持，PostgreSQL 支持）
SELECT grade AS g, COUNT(*) FROM students GROUP BY g; -- ✅ PostgreSQL 允许

📌 注释：

• 别名含空格或特殊字符时需用双引号包围。
• 别名在 SELECT 之后的子句（ORDER BY, GROUP BY, HAVING）中可用。

---

十、使用正则表达式查询（~ 操作符）

PostgreSQL 使用 ~（区分大小写）、~*（不区分大小写）进行正则匹配。

10.1 查询以特定字符或字符串开头的记录（^）

案例代码：

-- 查询姓名以 '张' 开头的学生（正则）
SELECT name FROM students WHERE name ~ '^张';

-- 不区分大小写：查询以 'zhang' 或 'Zhang' 开头（假设英文名）
SELECT name FROM students WHERE name ~* '^zhang';

---

10.2 查询以特定字符或字符串结尾的记录（$）

案例代码：

-- 查询姓名以 '三' 结尾的学生
SELECT name FROM students WHERE name ~ '三$';

-- 查询邮箱以 .com 结尾（假设 students 有 email 字段）
-- SELECT email FROM students WHERE email ~ '\.com$'; -- 注意点号需转义

---

10.3 用符号 “.” 来替代字符串中的任意一个字符

案例代码：

-- 查询姓名为三个字且中间是 '小' 的学生（如：王小明、李小刚）
SELECT name FROM students WHERE name ~ '^.{1}小.{1}$';
-- 或更简单：^_小_$（但正则中 . 更通用）
-- 实际中：WHERE name LIKE '_小_' 更高效

---

10.4 使用 “*” 和 “+” 来匹配多个字符

• *：前面字符出现0次或多次
• +：前面字符出现1次或多次

案例代码：

-- 查询姓名中包含连续多个 'a' 的学生（英文名场景）
-- SELECT name FROM students WHERE name ~ 'a+'; -- 至少一个a
-- SELECT name FROM students WHERE name ~ 'a*'; -- 0个或多个a（几乎匹配所有）

-- 查询手机号（假设格式：1开头，11位数字）
-- SELECT phone FROM contacts WHERE phone ~ '^1\d{10}$';

---

10.5 匹配指定字符串

案例代码：

-- 查询姓名包含 '小明' 或 '小红' 的学生
SELECT name FROM students WHERE name ~ '小明|小红';

-- 等价于：
-- SELECT name FROM students WHERE name LIKE '%小明%' OR name LIKE '%小红%';

---

10.6 匹配指定字符中的任意一个（[…]）

案例代码：

-- 查询姓名第二个字是 '小' 或 '大' 的学生
SELECT name FROM students WHERE name ~ '^.[小大]';

-- 查询姓名包含数字的学生（假设混有数字）
-- SELECT name FROM students WHERE name ~ '[0-9]';

---

10.7 匹配指定字符以外的字符（[^…]）

案例代码：

-- 查询姓名不以 '张'、'李'、'王' 开头的学生
SELECT name FROM students WHERE name ~ '^[^张李王]';

-- 查询姓名中不含数字的学生
-- SELECT name FROM students WHERE name ~ '^[^0-9]*$';

---

10.8 使用 {M} 或 {M,N} 指定连续出现次数

案例代码：

-- 查询姓名正好是3个汉字的学生（假设一个汉字占3字节，但正则按字符）
-- PostgreSQL 中一个汉字是一个字符
SELECT name FROM students WHERE name ~ '^.{3}$' AND LENGTH(name) = 3;

-- 查询身份证号（15位或18位数字，简化版）
-- SELECT id_card FROM users WHERE id_card ~ '^\d{15}(\d{3})?$';

📌 注释：

• 正则功能强大但性能较低，简单匹配优先用 LIKE。
• 常用正则元字符：^, $, ., *, +, ?, |, [], [^], {}, (), \d, \w 等。
• 使用 ~* 忽略大小写，!~ 表示不匹配。

---

十一、PostgreSQL 16 新特性 —— psql 新增 \gdesc 选项

✅ \gdesc：在 psql 命令行中，显示查询结果的列名和数据类型，而不执行查询。

使用方法（在 psql 中）：

-- 在 psql 终端中执行：
SELECT name, age, enrollment_date FROM students \gdesc

-- 输出示例：
--     Column     |          Type
-- ---------------+------------------------
--  name          | character varying(50)
--  age           | integer
--  enrollment_date | date

📌 注释：

• 用于快速查看复杂查询的输出结构。
• 不实际执行查询，无性能开销。
• 仅适用于 psql 客户端，非 SQL 语法。

---

十二、综合案例 —— 学生成绩分析系统

-- 假设表结构：
-- students(id, name, age, grade, enrollment_date)
-- scores(id, student_id, subject, score, exam_date)

-- 1. 查询每个学生的最高分、最低分、平均分（多科目）
SELECT 
    s.name AS "学生姓名",
    s.grade AS "年级",
    MAX(sc.score) AS "最高分",
    MIN(sc.score) AS "最低分",
    AVG(sc.score) AS "平均分",
    COUNT(sc.subject) AS "参考科目数"
FROM students s
JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id, s.name, s.grade  -- 按学生分组
ORDER BY "平均分" DESC;

-- 2. 查询各科平均分及及格率（>=60分）
SELECT 
    subject AS "科目",
    ROUND(AVG(score), 2) AS "平均分",
    ROUND(
        COUNT(CASE WHEN score >= 60 THEN 1 END) * 100.0 / COUNT(*), 
        2
    ) AS "及格率%"
FROM scores
GROUP BY subject
ORDER BY "平均分" DESC;

-- 3. 查询偏科学生（某科成绩与平均分差超过20分）
SELECT 
    s.name,
    sc.subject,
    sc.score,
    class_avg.avg_score AS "班级平均分",
    ABS(sc.score - class_avg.avg_score) AS "分差"
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN (
    SELECT subject, AVG(score) AS avg_score 
    FROM scores 
    GROUP BY subject
) class_avg ON sc.subject = class_avg.subject
WHERE ABS(sc.score - class_avg.avg_score) > 20
ORDER BY "分差" DESC;

-- 4. 查询进步最大的学生（假设有多次考试，按最近两次）
-- （需 scores 表有 exam_date 或 exam_round 字段，此处简化）
-- 使用窗口函数（后续章节），此处略。

-- 5. 综合排名：按总分降序，显示名次
SELECT 
    s.name,
    SUM(sc.score) AS total_score,
    RANK() OVER (ORDER BY SUM(sc.score) DESC) AS rank
FROM students s
JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id, s.name
ORDER BY total_score DESC;

---

十三、常见问题及解答

疑问1：DISTINCT可以应用于所有的列吗？

✅ 是的，但作用于列的组合。
SELECT DISTINCT col1, col2 FROM ... 会去除 (col1, col2) 组合完全相同的行。
不能单独对某一列去重而保留其他列原始值（需用 DISTINCT ON 或窗口函数）。

---

疑问2：ORDER BY可以和LIMIT混合使用吗？

✅ 可以，且推荐！
语法顺序：ORDER BY 在 LIMIT 之前。
LIMIT 限制的是排序后的结果。
示例：SELECT ... ORDER BY age DESC LIMIT 5; — 获取年龄最大的5人。

---

疑问3：什么时候使用单引号？

✅ 字符串和日期常量用单引号。

• '张三', '2025-09-12', '高一'
• 别名或标识符含特殊字符时用双引号：SELECT name AS "学生姓名"
• 数字、布尔值不用引号：123, true, false

---

疑问4：在WHERE子句中必须使用圆括号吗？

✅ 非必须，但强烈建议在混合 AND/OR 时使用！
AND 优先级高于 OR，不用括号可能导致逻辑错误。
示例：
WHERE A OR B AND C → WHERE A OR (B AND C)
WHERE (A OR B) AND C → 明确意图

---

疑问5：为什么使用通配符格式正确，却没有查找出符合条件的记录？

✅ 常见原因：

1. 大小写敏感：LIKE 区分大小写，尝试 ILIKE。
2. 尾部空格：数据含空格，用 TRIM(column) LIKE '张%'。
3. 特殊字符：% 或 _ 是通配符，查找字面值需转义：LIKE '100\%' ESCAPE '\'。
4. NULL 值：NULL 不匹配任何 LIKE 模式，需单独处理 OR column IS NULL。
5. 字符集/排序规则：多字节字符（如中文）处理可能有差异。

---

📘 查询优化口诀：
“条件写准WHERE，排序LIMIT要配对；
连接别名不可少，聚合分组HAVING筛；
子查EXISTS效率高，正则虽强慎滥用；
生产查询先EXPLAIN，索引命中是王道。”

---

本章全面覆盖 PostgreSQL 16 数据查询核心语法，从基础到高级，结合实战案例与避坑指南，助你成为查询高手！