SQL 从零基础到实战 – 教学笔记

本课程专为0基础学习者设计，每次学习5个核心知识点，循序渐进。

---

第一课：数据库与表的创建

知识点

1. SQL Intro – SQL（Structured Query Language，结构化查询语言）是用于与数据库沟通的标准语言。无论使用MySQL、PostgreSQL还是Oracle，基本语法都相同。
2. SQL Database – 数据库是存储数据的容器，一个数据库可以包含多张表。
3. SQL Create DB – 创建数据库
4. SQL Create Table – 创建数据表
5. SQL Data Types – 数据类型（文本、数字、日期等）

什么是数据库

数据库（Database）就像一个文件柜，专门用来存放表格形式的数据。一个数据库管理系统（DBMS）可以创建多个数据库，每个数据库相互独立。

PostgreSQL 常用命令

命令	作用
\l	列出所有数据库
\c 数据库名	连接到指定数据库
\dt	列出当前数据库中的所有表
\d 表名	查看表结构（列名、类型、约束）
\d+ 表名	查看表结构详细信息

# 在psql命令行中的使用示例
\l              -- 查看有哪些数据库
\c 学生管理系统 -- 切换到"学生管理系统"数据库
\dt             -- 查看当前数据库有哪些表
\d 学生         -- 查看学生表的结构

什么是表（Table）

表是数据库中最基本的数据存储单位。一张表由行和列组成：

• 列（Column/Field）：每列是一种属性，代表数据的”种类”，如姓名、年龄、班级
• 行（Row/Record）：每行是一条具体记录，代表一个完整的”个体”

下面用表格直观展示一张”学生表”的结构：

	字段1	字段2	字段3	字段4
字段名	学号	姓名	年龄	班级
数据类型	INTEGER	VARCHAR(50)	INTEGER	VARCHAR(50)
约束	PRIMARY KEY	NOT NULL
记录1	1	张三	18	一班
记录2	2	李四	19	一班
记录3	3	王五	18	二班

图解说明：

• 垂直方向是”字段”（Field），即每一列的列名和属性
• 横向是”记录”（Record），即每一行具体的数据
• 学号字段标注了 PRIMARY KEY，表示它是主键

主键（Primary Key）

每张表必须有一个主键，它是用来唯一标识每一行记录的字段。

比如学生表中的”学号”：

• 每个学生的学号都是唯一的，不可能重复
• 通过学号，可以精确定位到某一个学生

主键的两个特性：

1. 唯一性 – 不能有两行记录的主键值相同
2. 非空性 – 主键字段不能为空

PostgreSQL 常用数据类型

类型	含义	适用场景
INTEGER 或 INT	整数	年龄、数量、学号
BIGINT	大整数	很大的数字（如全国人口）
DECIMAL(p,s) 或 NUMERIC(p,s)	精确小数	金额、精确计算
REAL	浮点数	近似值（但不适合金钱）
VARCHAR(n)	可变长度字符串	姓名、地址（指定最大长度）
CHAR(n)	固定长度字符串	性别代码、状态码
TEXT	长文本	文章、备注（无长度限制）
DATE	日期	生日、入职日期
TIMESTAMP	日期+时间	订单时间、登录时间
BOOLEAN 或 BOOL	布尔值	是否毕业、是否启用

代码案例

-- 创建数据库
CREATE DATABASE 学生管理系统;

-- 连接到数据库（在psql中）
\c 学生管理系统

-- 创建学生表
CREATE TABLE 学生 (
    学号 INTEGER PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,
    年龄 INTEGER,
    班级 VARCHAR(50)
);

案例结果

执行后，数据库中会创建一张名为”学生”的表，表结构如下：

erDiagram
    学生 {
        int 学号 PK "主键，唯一标识每行"
        string 姓名 "非空"
        int 年龄
        string 班级
    }

列名	数据类型	说明
学号	INTEGER	主键，唯一标识每名学生
姓名	VARCHAR(50)	最长50个字符，不能为空
年龄	INTEGER	整数
班级	VARCHAR(50)	最长50个字符

插入数据后的样子：

学号 (PK)	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班

---

第二课：基础查询

知识点

1. SQL Syntax – SQL基本语法规则
2. SQL Select – 从表中查询数据
3. SQL Select Distinct – 查询不重复的数据
4. SQL Where – 设置查询条件
5. SQL And/Or/Not – 组合多个条件

SQL基本语法规则

SQL语句以分号（;）结束，不区分大小写。建议：

• 关键字大写（SELECT、FROM、WHERE）
• 表名和列名小写
• 关键字之间用空格或换行分隔

-- 正确的写法
SELECT 姓名, 年龄
FROM 学生
WHERE 班级 = '一班';

-- 也能运行，但不推荐（全小写）
select 姓名, 年龄 from 学生 where 班级 = '一班';

---

查询数据（SELECT）

-- 查询表中所有列（* 代表所有列）
SELECT * FROM 学生;

-- 只查询指定的几列
SELECT 姓名, 年龄, 班级 FROM 学生;

执行结果：

假设学生表数据如下：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班
4	赵六	20	二班

• SELECT * FROM 学生 – 返回全部4条记录
• SELECT 姓名, 年龄 FROM 学生：

姓名	年龄
张三	18
李四	19
王五	18
赵六	20

---

查询不重复的数据（DISTINCT）

如果某一列有重复值，可以用 DISTINCT 去重：

-- 查询有哪些班级（去掉重复）
SELECT DISTINCT 班级 FROM 学生;

执行结果：

假设学生表数据如下：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班
4	赵六	20	二班

• SELECT DISTINCT 班级 FROM 学生：

班级
一班
二班

---

条件查询（WHERE）

WHERE 用来筛选符合条件的记录：

-- 查询一班的所有学生
SELECT * FROM 学生 WHERE 班级 = '一班';

-- 查询年龄大于18的学生
SELECT * FROM 学生 WHERE 年龄 > 18;

常用比较运算符：

符号	含义
=	等于
<> 或 !=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于

执行结果：

假设学生表数据如下：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班
4	赵六	20	二班

• WHERE 班级 = '一班' – 返回张三、李四
• WHERE 年龄 > 18 – 返回李四(19岁)、赵六(20岁)

---

组合条件（AND / OR / NOT）

• AND：两个条件都满足
• OR：满足任意一个即可
• NOT：取反

-- AND：同时满足多个条件
SELECT * FROM 学生 WHERE 班级 = '一班' AND 年龄 > 18;

-- OR：满足任意一个条件
SELECT * FROM 学生 WHERE 班级 = '一班' OR 班级 = '二班';

-- NOT：取反
SELECT * FROM 学生 WHERE NOT 班级 = '一班';

-- 混合：AND优先级高于OR，需要时加括号
SELECT * FROM 学生 WHERE 年龄 > 18 AND (班级 = '一班' OR 班级 = '二班');

执行结果：

假设学生表数据如下：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班
4	赵六	20	二班

• 班级 = '一班' AND 年龄 > 18 – 返回李四（19岁，一班）
• 班级 = '一班' OR 班级 = '二班' – 返回全部4条
• NOT 班级 = '一班' – 返回王五、赵六

---

综合代码案例

-- 1. 查询所有学生
SELECT * FROM 学生;

-- 2. 只查询姓名和班级
SELECT 姓名, 班级 FROM 学生;

-- 3. 查询不重复的班级
SELECT DISTINCT 班级 FROM 学生;

-- 4. 查询一班的学生
SELECT * FROM 学生 WHERE 班级 = '一班';

-- 5. 查询一班且年龄大于18的学生
SELECT * FROM 学生 WHERE 班级 = '一班' AND 年龄 > 18;

-- 6. 查询一班或二班的学生
SELECT * FROM 学生 WHERE 班级 = '一班' OR 班级 = '二班';

综合案例结果

假设学生表数据如下：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班
4	赵六	20	二班

1. SELECT * FROM 学生 – 返回全部4条记录
2. SELECT 姓名, 班级 FROM 学生 – 返回4行（姓名+班级）
3. SELECT DISTINCT 班级 FROM 学生 – 返回：一班、二班
4. WHERE 班级 = '一班' – 返回张三、李四
5. 班级 = '一班' AND 年龄 > 18 – 返回李四
6. 班级 = '一班' OR 班级 = '二班' – 返回全部4条

---

第三课：排序与条件查询

知识点

1. SQL Order By – 对查询结果排序
2. SQL Like – 模糊匹配查找
3. SQL In – 在指定列表中查找
4. SQL Between – 在范围中查找
5. SQL Null Values – 空值处理

排序（ORDER BY）

数据库中的数据通常按插入顺序存储，但往往需要按某种顺序显示。ORDER BY 用来对结果排序。

ORDER BY 关键字默认按升序对记录进行排序。若要按降序对记录进行排序，请使用 DESC 关键字。

对于字符串类型，ORDER BY 会按字母顺序排序。

-- 按年龄升序（从小到大）
SELECT * FROM 学生 ORDER BY 年龄;

-- 按年龄降序（从大到小）
SELECT * FROM 学生 ORDER BY 年龄 DESC;

-- 按姓名字母顺序排序（默认升序）
SELECT * FROM 学生 ORDER BY 姓名;

-- 按班级字母降序排列
SELECT * FROM 学生 ORDER BY 班级 DESC;

-- 按班级排序，班级相同的按年龄降序
SELECT * FROM 学生 ORDER BY 班级, 年龄 DESC;

执行结果：

假设学生表数据如下：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班
4	赵六	20	二班

• ORDER BY 年龄 – 按年龄升序：18、18、19、20
• ORDER BY 年龄 DESC – 按年龄降序：20、19、18、18
• ORDER BY 姓名 – 按姓名字母升序：赵六、李四、王五、张三
• ORDER BY 班级 DESC – 按班级字母降序：二班、一班
• ORDER BY 班级, 年龄 DESC – 先按班级，班级内按年龄降序

---

模糊匹配（LIKE）

LIKE 用来查找符合某种模式的记录，常用于搜索。

-- 姓名以"张"开头的学生（% 代表任意多个字符）
SELECT * FROM 学生 WHERE 姓名 LIKE '张%';

-- 姓名包含"小"的学生
SELECT * FROM 学生 WHERE 姓名 LIKE '%小%';

-- 姓名以"张"开头且只有2个字的学生（_ 代表单个字符）
SELECT * FROM 学生 WHERE 姓名 LIKE '张_';

-- 姓名以"张"开头且是3个字
SELECT * FROM 学生 WHERE 姓名 LIKE '张__';

通配符说明：

通配符	含义	示例
%	任意多个字符	张% 表示以张开头
_	单个字符	张_ 表示张开头共2个字

执行结果：

假设学生表数据如下：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王小五	18	二班
4	赵六	20	二班
5	张七	22	三班

• 姓名 LIKE '张%' – 返回：张三、张七（以张开头）
• 姓名 LIKE '%小%' – 返回：王小五（包含”小”）
• 姓名 LIKE '张_' – 返回：张三（张开头，2个字）
• 姓名 LIKE '张__' – 返回：张七（张开头，3个字）

---

在列表中查找（IN）

IN 相当于多个 OR 的简写，用来判断列的值是否在指定列表中。

-- 班级是一班、二班、三班的学生
SELECT * FROM 学生 WHERE 班级 IN ('一班', '二班', '三班');

-- 也可以用于数字
SELECT * FROM 学生 WHERE 学号 IN (1, 3, 5);

执行结果：

假设学生表数据如下：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王小五	18	二班
4	赵六	20
5	张七	22	三班

• 班级 IN ('一班', '二班', '三班') – 返回：张三、李四、王小五、张七
• 学号 IN (1, 3, 5) – 返回：张三、王小五、张七

---

范围查找（BETWEEN）

BETWEEN 用于查找在某个范围内的记录。

-- 年龄在18到20岁之间的学生（BETWEEN包含两端)
SELECT * FROM 学生 WHERE 年龄 BETWEEN 18 AND 20;

-- 也可以用于日期
SELECT * FROM 学生 WHERE 出生日期 BETWEEN '2020-01-01' AND '2020-12-31';

注意： BETWEEN a AND b 包含 a 和 b 两端。

执行结果：

假设学生表数据如下：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王小五	18	二班
4	赵六	20
5	张七	22	三班

• 年龄 BETWEEN 18 AND 20 – 返回：张三(18岁)、李四(19岁)、王小五(18岁)、赵六(20岁)，共4人
• 注意：22岁的张七不在范围内

---

空值处理（NULL）

在数据库中，”没有值”用 NULL 表示。注意：判断 NULL 不能用 = 号，必须用 IS。

-- 错误的写法（永远得不到结果）
SELECT * FROM 学生 WHERE 班级 = NULL;

-- 正确的写法
SELECT * FROM 学生 WHERE 班级 IS NULL;        -- 找出没有班级的学生
SELECT * FROM 学生 WHERE 班级 IS NOT NULL;    -- 找出有班级的学生

执行结果：

假设学生表数据如下：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王小五	18	二班
4	赵六	20	（空）
5	张七	22	三班

• 班级 IS NULL – 返回：赵六（班级为空）
• 班级 IS NOT NULL – 返回：张三、李四、王小五、张七（共4人）

---

综合代码案例

-- 1. 按年龄从高到低排序
SELECT * FROM 学生 ORDER BY 年龄 DESC;

-- 2. 查找姓张的学生
SELECT * FROM 学生 WHERE 姓名 LIKE '张%';

-- 3. 查找名字包含"小"的学生
SELECT * FROM 学生 WHERE 姓名 LIKE '%小%';

-- 4. 查找一班、二班、三班的学生
SELECT * FROM 学生 WHERE 班级 IN ('一班', '二班', '三班');

-- 5. 查找年龄在18到20岁的学生
SELECT * FROM 学生 WHERE 年龄 BETWEEN 18 AND 20;

-- 6. 找出没有填写班级的学生
SELECT * FROM 学生 WHERE 班级 IS NULL;

-- 7. 找出已填写班级的学生
SELECT * FROM 学生 WHERE 班级 IS NOT NULL;

综合案例结果

假设学生表有以下数据：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王小五	18	二班
4	赵六	20
5	张七	22	三班

执行结果：

1. ORDER BY 年龄 DESC – 按年龄从高到低：22、20、19、18、18
2. 姓名 LIKE '张%' – 返回张三、张七
3. 姓名 LIKE '%小%' – 返回王小五
4. 班级 IN ('一班', '二班', '三班') – 返回张三、李四、王小五、张七
5. 年龄 BETWEEN 18 AND 20 – 返回张三(18)、王小五(18)、赵六(20)
6. 班级 IS NULL – 返回赵六
7. 班级 IS NOT NULL – 返回其他4人

---

第四课：数据增删改

知识点

1. SQL Insert Into – 插入新数据
2. SQL Update – 修改已有数据
3. SQL Delete – 删除数据
4. SQL Select Top – 限制返回数量
5. SQL Aliases – 给列或表起别名

插入数据（INSERT INTO）

INSERT INTO 用来向表中添加新记录。

基础语法：

INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);

-- 插入一条完整记录
INSERT INTO 学生 (学号, 姓名, 年龄, 班级)
VALUES (1, '张三', 18, '一班');

-- 只插入部分列（其他列允许为空或默认值）
INSERT INTO 学生 (学号, 姓名)
VALUES (2, '李四');

-- 同时插入多条记录（PostgreSQL 语法）
INSERT INTO 学生 (学号, 姓名, 年龄, 班级) VALUES 
    (3, '王五', 19, '二班'),
    (4, '赵六', 20, '二班'),
    (5, '钱七', 18, '三班');

执行结果：

插入后学生表：

SQL Shell 应用程序将会返回以下内容：

INSERT 0 1
INSERT 0 1
INSERT 0 3

学号	姓名	年龄	班级
1	张三	18	一班
2	李四
3	王五	19	二班
4	赵六	20	二班
5	钱七	18	三班

---

更新数据（UPDATE）

UPDATE 用来修改表中已存在的记录。

基础语法：

UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2 WHERE 条件;

-- 修改单个字段
UPDATE 学生 SET 班级 = '三班' WHERE 学号 = 1;

-- 修改多个字段
UPDATE 学生 SET 姓名 = '张三丰', 年龄 = 19 WHERE 学号 = 1;

-- 不加 WHERE 会修改所有记录（危险！）
UPDATE 学生 SET 班级 = '未分配';

重要： UPDATE 和 DELETE 语句一定要加 WHERE 条件！

执行结果：

假设当前学生表：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班

• UPDATE 学生 SET 班级 = '三班' WHERE 学号 = 1 – 学号1的班级改为三班

修改后：

学号	姓名	年龄	班级
1	张三	18	三班
2	李四	19	一班

---

删除数据（DELETE）

DELETE 用来删除表中的记录。

基础语法：

DELETE FROM 表名 WHERE 条件;

-- 删除指定记录
DELETE FROM 学生 WHERE 学号 = 1;

-- 删除符合条件的多条记录
DELETE FROM 学生 WHERE 班级 = '三班';

-- 删除表中所有记录（危险！）
DELETE FROM 学生;

执行结果：

假设学生表：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班
4	赵六	20	三班

• DELETE FROM 学生 WHERE 学号 = 1 – 删除张三，剩余3条

删除后：

学号	姓名	年龄	班级
2	李四	19	一班
3	王五	18	二班
4	赵六	20	三班

---

限制返回数量（SELECT TOP / LIMIT）

PostgreSQL 使用 LIMIT，SQL Server 使用 TOP。

基础语法：

-- PostgreSQL/MySQL
SELECT * FROM 表名 LIMIT 数量;

-- SQL Server
SELECT TOP 数量 * FROM 表名;

-- PostgreSQL / MySQL 语法
SELECT * FROM 学生 LIMIT 5;         -- 只返回前5条
SELECT * FROM 学生 LIMIT 5 OFFSET 10; -- 跳过前10条，返回5条

-- SQL Server 语法
SELECT TOP 5 * FROM 学生;          -- 只返回前5条
SELECT TOP 50 PERCENT * FROM 学生;  -- 返回前50%的记录

执行结果：

假设学生表有10条记录：

学号	姓名
1	张三
2	李四
3	王五
4	赵六
5	钱七
…	…

• LIMIT 5 – 返回前5条：张三、李四、王五、赵六、钱七

---

给列和表起别名（AS）

AS 用来给列名或表名起一个临时的别名，让结果更易读。

基础语法：

-- 给列起别名
SELECT 字段 AS 别名 FROM 表名;

-- 给表起别名
SELECT 列名 FROM 表名 AS 别名;

-- 给列起别名
SELECT 姓名 AS 学生姓名, 年龄 AS 学生年龄 FROM 学生;

-- 给表起别名（常用于多表查询）
SELECT s.姓名, s.年龄 FROM 学生 AS s;

-- 列名可以是表达式
SELECT 姓名, 年龄 + 1 AS 明年年龄 FROM 学生;

执行结果：

假设学生表：

学号	姓名	年龄
1	张三	18
2	李四	19

• SELECT 姓名 AS 学生姓名, 年龄 AS 学生年龄 FROM 学生：

学生姓名	学生年龄
张三	18
李四	19

• SELECT 姓名, 年龄 + 1 AS 明年年龄 FROM 学生：

姓名	明年年龄
张三	19
李四	20

---

综合代码案例

-- 1. 插入一条学生记录
INSERT INTO 学生 (学号, 姓名, 年龄, 班级)
VALUES (6, '孙八', 21, '一班');

-- 2. 修改学号为6的学生班级
UPDATE 学生 SET 班级 = '二班' WHERE 学号 = 6;

-- 3. 删除学号为6的学生
DELETE FROM 学生 WHERE 学号 = 6;

-- 4. 查询前3名学生
SELECT * FROM 学生 LIMIT 3;

-- 5. 给列和表起别名
SELECT 姓名 AS 学生名, 班级 AS 所属班级 FROM 学生 AS s;

综合案例结果

假设学生表原始数据：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班
4	赵六	20	二班
5	钱七	18	三班

按顺序执行：

1. 插入后增加第6条：孙八，21岁，一班
2. 修改后：孙八的班级变为二班
3. 删除后：学号6的记录被删除
4. LIMIT 3 – 返回：张三、李四、王五
5. AS 别名结果：

学生名	所属班级
张三	一班
李四	一班
王五	二班
赵六	二班
钱七	三班

---

第五课：聚合函数

知识点

1. SQL Aggregate Functions – 聚合函数概述
2. SQL Count() – 计数
3. SQL Sum() – 求和
4. SQL Avg() – 求平均值
5. SQL Min() / Max() – 求最大最小值

聚合函数概述（Aggregate Functions）

聚合函数对一组值进行计算，把多行数据压缩成一行结果。这是聚合函数最核心的概念。

flowchart LR
    subgraph 输入 ["多行数据"]
        A1[张三 - 18岁]
        A2[李四 - 19岁]
        A3[王五 - 18岁]
        A4[赵六 - 20岁]
    end

    subgraph 聚合 ["聚合函数 (如 AVG)"]
        B[计算中...]
    end

    subgraph 输出 ["单行结果"]
        C[平均年龄: 18.75]
    end

    A1 --> B
    A2 --> B
    A3 --> B
    A4 --> B
    B --> C

聚合函数的特点：

• 输入：多行数据
• 输出：单个值
• 除了 COUNT(*)，其他聚合函数会自动忽略 NULL 值

常见聚合函数：

函数	作用
COUNT()	计数
SUM()	求和
AVG()	求平均值
MIN()	求最小值
MAX()	求最大值

---

计数（COUNT）

COUNT 用来统计行数。

基础语法：

SELECT COUNT(字段) FROM 表名;

-- 统计所有行数（包括NULL）
SELECT COUNT(*) FROM 学生;

-- 统计非空值的数量
SELECT COUNT(班级) FROM 学生;

-- 统计班级不重复的数量
SELECT COUNT(DISTINCT 班级) FROM 学生;

执行结果：

假设学生表：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18
4	赵六	20	二班

• COUNT(*) – 返回 4（总行数）
• COUNT(班级) – 返回 3（班级非空的有3人）
• COUNT(DISTINCT 班级) – 返回 2（一班、二班）

---

求和（SUM）

SUM 用来计算数值列的总和。SUM 会自动忽略 NULL 值。

基础语法：

SELECT SUM(字段) FROM 表名;

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

执行结果：

假设学生表：

学号	姓名	年龄
1	张三	18
2	李四	19
3	王五	NULL
4	赵六	20

• SUM(年龄) – 返回 57（18+19+20，忽略了NULL）

---

求平均值（AVG）

AVG 用来计算数值列的平均值。AVG 也会自动忽略 NULL 值。

基础语法：

SELECT AVG(字段) FROM 表名;

-- 计算所有学生的平均年龄（原始值）
SELECT AVG(年龄) AS 平均年龄 FROM 学生;

-- 使用 ROUND 保留一位小数（PostgreSQL 常用）
SELECT ROUND(AVG(年龄), 1) AS 平均年龄 FROM 学生;

关于 NULL： 如果有4名学生，其中1名年龄为 NULL，AVG 会用总和除以 3（不是4），因为 NULL 被忽略了。

执行结果：

假设学生表：

学号	姓名	年龄
1	张三	18
2	李四	19
3	王五	18
4	赵六	20

• AVG(年龄) – 返回 18.75（75/4）
• ROUND(AVG(年龄), 1) – 返回 18.8（四舍五入保留1位）

---

最大值/最小值（MIN / MAX）

MIN 返回最小值，MAX 返回最大值。也会忽略 NULL 值。

基础语法：

SELECT MIN(字段), MAX(字段) FROM 表名;

-- 找出最大年龄和最小年龄
SELECT MIN(年龄) AS 最小年龄, MAX(年龄) AS 最大年龄 FROM 学生;

执行结果：

假设学生表：

学号	姓名	年龄
1	张三	18
2	李四	19
3	王五	18
4	赵六	20

• MIN(年龄) – 返回 18
• MAX(年龄) – 返回 20

---

PostgreSQL 特有语法：FILTER

PostgreSQL 支持一种很酷的写法，可以用 FILTER 条件过滤后再聚合。

-- 统计年龄大于18岁的学生数量
SELECT COUNT(*) FILTER (WHERE 年龄 > 18) AS 成年学生数 FROM 学生;

-- 统计一班学生的平均年龄
SELECT AVG(年龄) FILTER (WHERE 班级 = '一班') AS 一班平均年龄 FROM 学生;

这比写子查询更简洁。

---

预告：GROUP BY 分组

聚合函数经常配合 GROUP BY 使用，可以按类别分别统计。

语法预览：

SELECT 班级, AVG(年龄) AS 平均年龄
FROM 学生
GROUP BY 班级;

执行结果：

假设学生表：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班
4	赵六	20	二班

• GROUP BY 班级 后：

班级	平均年龄
一班	18.5
二班	19

（GROUP BY 的详细用法在下一课）

---

综合代码案例

-- 1. 统计学生总数
SELECT COUNT(*) AS 学生总数 FROM 学生;

-- 2. 统计有班级的学生数量
SELECT COUNT(班级) AS 有班级人数 FROM 学生;

-- 3. 计算平均年龄（保留1位小数）
SELECT ROUND(AVG(年龄), 1) AS 平均年龄 FROM 学生;

-- 4. 计算年龄总和
SELECT SUM(年龄) AS 年龄总和 FROM 学生;

-- 5. 找出最大和最小年龄
SELECT MIN(年龄) AS 最小年龄, MAX(年龄) AS 最大年龄 FROM 学生;

-- 6. PostgreSQL 特有写法：统计成年学生数
SELECT COUNT(*) FILTER (WHERE 年龄 >= 18) AS 成年学生数 FROM 学生;

综合案例结果

假设学生表：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班
4	赵六	20	二班

执行结果：

1. COUNT(*) – 返回 4
2. COUNT(班级) – 返回 4
3. ROUND(AVG(年龄), 1) – 返回 18.8
4. SUM(年龄) – 返回 75
5. MIN(年龄), MAX(年龄) – 返回 最小18，最大20

---

第六课：分组与条件表达式

知识点

1. SQL Group By – 分组统计
2. SQL Having – 分组后筛选
3. SQL Exists – 检查是否存在
4. SQL Case – 条件判断
5. SQL Wildcards – 通配符（续）

分组统计（GROUP BY）

GROUP BY 按一个或多个列对数据进行分组，然后对每个组应用聚合函数。

基础语法：

SELECT 字段1, 聚合函数(字段2) AS 别名
FROM 表名
GROUP BY 字段1;

重要规则： PostgreSQL 对 GROUP BY 非常严格。SELECT 后面的列，要么出现在 GROUP BY 中，要么必须被包含在聚合函数里。

-- 正确：班级在 GROUP BY 中
SELECT 班级, COUNT(*) AS 人数 FROM 学生 GROUP BY 班级;

-- 正确：姓名被聚合函数包裹
SELECT COUNT(*) AS 人数, MAX(姓名) AS 最新姓名 FROM 学生;

-- 错误：姓名既不在 GROUP BY 中，也不在聚合函数里（PostgreSQL 会报错）
-- SELECT 姓名, 班级, COUNT(*) FROM 学生 GROUP BY 班级;

-- 按班级统计学生人数
SELECT 班级, COUNT(*) AS 人数 FROM 学生 GROUP BY 班级;

-- 按班级统计平均年龄
SELECT 班级, ROUND(AVG(年龄), 1) AS 平均年龄 FROM 学生 GROUP BY 班级;

-- 按多个条件分组：班级和性别
SELECT 班级, 性别, COUNT(*) AS 人数 FROM 学生 GROUP BY 班级, 性别;

执行结果：

假设学生表：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班
4	赵六	20	二班

• GROUP BY 班级：

班级	人数
一班	2
二班	2

---

分组后筛选（HAVING）

WHERE 用于分组前筛选原始数据，HAVING 用于分组后筛选组。

flowchart TD
    A[FROM 表名] --> B[WHERE 条件]
    B --> C[GROUP BY 分组]
    C --> D[HAVING 条件]
    D --> E[SELECT 选择]
    E --> F[ORDER BY 排序]

    style B fill:#e1f5fe
    style C fill:#fff3e0
    style D fill:#e8f5e9

执行顺序说明：

1. FROM – 读取表
2. WHERE – 过滤原始数据（聚合前）
3. GROUP BY – 分组
4. HAVING – 过滤分组后的结果（聚合后）
5. SELECT – 选择列
6. ORDER BY – 排序

基础语法：

SELECT 字段1, 聚合函数(字段2) AS 别名
FROM 表名
GROUP BY 字段1
HAVING 条件;

-- 简单示例：找出学生数量大于1的班级
SELECT 班级, COUNT(*) AS 人数 
FROM 学生 
GROUP BY 班级 
HAVING COUNT(*) > 1;

-- 复杂示例：平均年龄大于18岁且人数不少于2人的班级
SELECT 班级, COUNT(*) AS 人数, ROUND(AVG(年龄), 1) AS 平均年龄
FROM 学生
GROUP BY 班级
HAVING AVG(年龄) > 18 AND COUNT(*) >= 2;

为什么 WHERE 无法替代 HAVING？

-- 错误写法：WHERE 无法引用聚合函数的结果
-- SELECT 班级, AVG(年龄) FROM 学生 WHERE AVG(年龄) > 18 GROUP BY 班级;

-- 正确写法：必须用 HAVING 筛选聚合结果
SELECT 班级, ROUND(AVG(年龄), 1) AS 平均年龄
FROM 学生
GROUP BY 班级
HAVING AVG(年龄) > 18;

真实业务场景示例：

假设有一张”订单表”，查询哪些客户消费总额超过1000元：

-- 按客户统计消费总额
SELECT 客户名, SUM(金额) AS 总消费额, COUNT(*) AS 订单数
FROM 订单
GROUP BY 客户名
HAVING SUM(金额) > 1000
ORDER BY 总消费额 DESC;

执行结果：

假设订单表：

订单ID	客户名	金额
1	张三	500
2	张三	600
3	李四	300
4	李四	400
5	王五	2000

• HAVING SUM(金额) > 1000：

客户名	总消费额	订单数
张三	1100	2
王五	2000	1

李四(700元) 不满足条件，被过滤掉。

执行结果：

假设学生表：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	18	二班
4	赵六	20	二班

• HAVING COUNT(*) > 1 – 返回一班(2人)、二班(2人)，都满足
• HAVING AVG(年龄) > 18 – 返回二班(平均19岁)，一班(18.5岁)不满足

---

检查是否存在（EXISTS）

EXISTS 检查子查询是否返回行。返回 TRUE 或 FALSE。

EXISTS 通常用于关联子查询，检查一个表中的记录是否在另一个表中存在关联。

基础语法：

SELECT * FROM 表名 WHERE EXISTS (子查询);

真实案例： 假设有两张表——学生表和欠费表，查询哪些学生有欠费记录。

-- 先创建欠费表
CREATE TABLE 欠费记录 (
    欠费ID SERIAL PRIMARY KEY,
    学号 INT,
    欠费金额 DECIMAL(10,2)
);

INSERT INTO 欠费记录 (学号, 欠费金额) VALUES 
(1, 500.00),
(3, 300.00);

-- 查询有欠费记录的学生
SELECT * FROM 学生 WHERE EXISTS (
    SELECT 1 FROM 欠费记录 WHERE 欠费记录.学号 = 学生.学号
);

执行结果：

学生表：

学号	姓名	班级
1	张三	一班
2	李四	一班
3	王五	二班
4	赵六	二班

欠费记录表：

欠费ID	学号	欠费金额
1	1	500
2	3	300

• EXISTS 结果：返回张三(学号1)、王五(学号3)——只有他们有欠费记录

---

条件判断（CASE）

CASE 就像编程中的 if-else，可以创建新的”虚拟列”。

flowchart LR
    subgraph 输入 ["原始表"]
        A[张三 - 17岁]
        B[李四 - 19岁]
        C[王五 - 21岁]
    end

    subgraph CASE ["CASE 表达式"]
        D{年龄<18?}
    end

    subgraph 输出 ["添加虚拟列"]
        E[张三 - 未成年]
        F[李四 - 青年]
        G[王五 - 成年]
    end

    A --> D
    B --> D
    C --> D
    D --> E
    D --> F
    D --> G

基础语法：

SELECT 
    列名,
    CASE 
        WHEN 条件1 THEN 结果1
        WHEN 条件2 THEN 结果2
        ELSE 默认结果
    END AS 别名
FROM 表名;

-- 根据年龄划分年龄段
SELECT 姓名, 年龄,
    CASE 
        WHEN 年龄 < 18 THEN '未成年'
        WHEN 年龄 < 20 THEN '青年'
        ELSE '成年'
    END AS 年龄段
FROM 学生;

-- 根据成绩分类
SELECT 姓名, 成绩,
    CASE 
        WHEN 成绩 >= 90 THEN '优秀'
        WHEN 成绩 >= 60 THEN '及格'
        ELSE '不及格'
    END AS 等级
FROM 学生;

执行结果：

假设学生表：

学号	姓名	年龄
1	张三	17
2	李四	19
3	王五	21

• CASE 结果：

姓名	年龄	年龄段
张三	17	未成年
李四	19	青年
王五	21	成年

---

通配符（Wildcards）续

PostgreSQL 的 LIKE 只支持 % 和 _ 两个通配符。

通配符	含义	示例
%	任意多个字符	张% = 以张开头
_	单个字符	张_ = 张开头共2字

PostgreSQL 特有：SIMILAR TO 或正则表达式

如果需要更复杂的匹配，使用 SIMILAR TO 或 POSIX 正则表达式：

-- SIMILAR TO：匹配张、李、王开头
SELECT * FROM 学生 WHERE 姓名 SIMILAR TO '(张|李|王)%';

-- 正则表达式：匹配张、李、王开头（更常用）
SELECT * FROM 学生 WHERE 姓名 ~ '^[张李王]';

---

综合代码案例

-- 1. 按班级统计人数
SELECT 班级, COUNT(*) AS 人数 FROM 学生 GROUP BY 班级;

-- 2. 找出人数大于1的班级
SELECT 班级, COUNT(*) AS 人数 
FROM 学生 
GROUP BY 班级 
HAVING COUNT(*) > 1;

-- 3. 检查是否存在欠费（关联子查询）
SELECT * FROM 学生 WHERE EXISTS (
    SELECT 1 FROM 欠费记录 WHERE 欠费记录.学号 = 学生.学号
);

-- 4. 给学生年龄分组
SELECT 姓名, 年龄,
    CASE 
        WHEN 年龄 < 18 THEN '未成年'
        ELSE '成年'
    END AS 身份
FROM 学生;

-- 5. 查找姓张的学生（PostgreSQL 语法）
SELECT * FROM 学生 WHERE 姓名 LIKE '张%';

综合案例结果

假设学生表：

学号	姓名	年龄	班级
1	张三	18	一班
2	李四	19	一班
3	王五	17	二班
4	赵六	20	二班

假设欠费记录表有学号1和3的记录。

1. GROUP BY 班级 – 一班2人，二班2人
2. HAVING COUNT(*) > 1 – 返回一班、二班
3. EXISTS – 返回张三、王五（有欠费记录）
4. CASE – 张三成年，李四成年，王五未成年，赵六成年
5. LIKE '张%' – 返回张三

---

---

第七课：表连接

知识点

1. SQL Joins – 表连接概述
2. SQL Inner Join – 内连接
3. SQL Left Join – 左连接
4. SQL Right Join – 右连接
5. SQL Self Join – 自连接

表连接概述（Joins）

在实际数据库中，数据分散在多张表中。JOIN 用来把多张表的字段合并成一张表。

erDiagram
    学生 ||--o 成绩 : "拥有"

    学生 {
        int 学号 PK
        string 姓名
        int 班级编号 FK
    }

    成绩 {
        int 学号 PK FK
        string 课程
        int 分数
    }

---

内连接（INNER JOIN）

内连接返回两张表都有的匹配记录。

flowchart LR
    subgraph 学生表
        S1[张三]
        S2[李四]
        S3[王五]
        S4[赵六-NULL]
    end

    subgraph 班级表
        C1[一班]
        C2[二班]
    end

    subgraph 结果
        R1[张三-一班]
        R2[李四-一班]
        R3[王五-二班]
    end

    S1 --> C1 --> R1
    S2 --> C1 --> R2
    S3 --> C2 --> R3
    S4 -->|无匹配| X[不包含]

基础语法：

SELECT 列1, 列2, ...
FROM 表1
INNER JOIN 表2 ON 表1.关联列 = 表2.关联列;

PostgreSQL 简化语法：如果列名相同，用 USING

-- 以下两种写法等价
SELECT s.姓名, c.班级名称
FROM 学生 s
INNER JOIN 班级 c ON s.班级编号 = c.班级编号;

-- 使用 USING 更简洁
SELECT s.姓名, c.班级名称
FROM 学生 s
INNER JOIN 班级 c USING (班级编号);

为什么必须用表别名？

当两张表有同名列时，不加别名会导致”列引用歧义”错误：

-- 错误：id 在两张表都有，数据库不知道用哪个
-- SELECT id, 姓名 FROM 学生 INNER JOIN 班级 ON ...;

-- 正确：加上表别名前缀
SELECT s.学号, c.班级编号
FROM 学生 s
INNER JOIN 班级 c ON s.班级编号 = c.班级编号;

执行结果：

学生表

学号	姓名	班级编号
1	张三	1
2	李四	1
3	王五	2
4	赵六	NULL

班级表

班级编号	班级名称
1	一班
2	二班

• INNER JOIN 结果：返回张三( 一班)、李四( 一班)、王五( 二班)
• 注意：赵六没有班级，NULL匹配不上，不包含

---

左连接（LEFT JOIN）

左连接返回左表的全部记录，右表没有匹配的显示 NULL。

flowchart LR
    subgraph 左表[学生表-全部]
        L1[张三]
        L2[李四]
        L3[王五]
        L4[赵六-NULL]
    end

    subgraph 结果
        R1[张三-一班]
        R2[李四-一班]
        R3[王五-二班]
        R4[赵六-NULL]
    end

    L1 --> R1
    L2 --> R2
    L3 --> R3
    L4 -->|无匹配|NULL[赵六-NULL]

SELECT s.姓名, c.班级名称
FROM 学生 s
LEFT JOIN 班级 c ON s.班级编号 = c.班级编号;

• LEFT JOIN 结果：返回全部4条，赵六班级显示为 NULL

---

右连接（RIGHT JOIN）

右连接返回右表的全部记录。

SELECT s.姓名, c.班级名称
FROM 学生 s
RIGHT JOIN 班级 c ON s.班级编号 = c.班级编号;

实际工作中右连接很少用，调换表顺序可用左连接实现。

---

全外连接（FULL OUTER JOIN）

PostgreSQL 支持全外连接，返回两张表的全部记录。

flowchart LR
    subgraph 学生表
        S1[张三-班级1]
        S2[李四-班级1]
        S3[王五-班级2]
        S4[赵六-NULL]
    end

    subgraph 班级表
        C1[班级1-一班]
        C2[班级2-二班]
        C3[班级3-三班]
    end

    subgraph 结果
        R1[张三-一班]
        R2[李四-一班]
        R3[王五-二班]
        R4[赵六-NULL]
        R5[空-三班]
    end

    S1 --> C1 --> R1
    S2 --> C1 --> R2
    S3 --> C2 --> R3
    S4 -->|左无匹配|NULL[赵六-NULL]
    C3 -->|右无匹配|EMPTY[空-三班]

执行结果：

学生表

学号	姓名	班级编号
1	张三	1
2	李四	1
3	王五	2
4	赵六	NULL

班级表

班级编号	班级名称
1	一班
2	二班
3	三班

• FULL OUTER JOIN 结果：返回5条

姓名	班级名称
张三	一班
李四	一班
王五	二班
赵六	NULL
空	三班

---

LEFT JOIN + IS NULL：找”差集”

LEFT JOIN 经常用于找”没有匹配”的记录。

-- 找出没有班级的学生（左表独有）
SELECT s.姓名, c.班级名称
FROM 学生 s
LEFT JOIN 班级 c ON s.班级编号 = c.班级编号
WHERE c.班级编号 IS NULL;

-- 找出没有学生的班级（右表独有）
SELECT s.姓名, c.班级名称
FROM 学生 s
RIGHT JOIN 班级 c ON s.班级编号 = c.班级编号
WHERE s.学号 IS NULL;

执行结果：

• 第一个查询：返回赵六（没有班级）
• 第二个查询：返回三班（没有学生）

---

笛卡尔积警告

重要：如果你忘记写 ON 条件，会产生笛卡尔积！

-- 危险！学生表4行 x 班级表2行 = 8行
SELECT * FROM 学生, 班级;

-- 危险！等同于 CROSS JOIN
SELECT * FROM 学生 CROSS JOIN 班级;

笛卡尔积演示：

flowchart LR
    subgraph 学生表[学生表 - 4行]
        S1[张三]
        S2[李四]
        S3[王五]
        S4[赵六]
    end

    subgraph 班级表[班级表 - 2行]
        C1[一班]
        C2[二班]
    end

    subgraph 结果[笛卡尔积 - 4x2=8行]
        R1[张三-一班]
        R2[张三-二班]
        R3[李四-一班]
        R4[李四-二班]
        R5[王五-一班]
        R6[王五-二班]
        R7[赵六-一班]
        R8[赵六-二班]
    end

    S1 --> R1
    S1 --> R2
    S2 --> R3
    S2 --> R4
    S3 --> R5
    S3 --> R6
    S4 --> R7
    S4 --> R8

笛卡尔积会把所有行两两匹配，4行 x 2行 = 8行。在生产环境可能导致数据库崩溃！

---

自连接（Self Join）

同一张表当两张表用，用于查找表内记录之间的关系。

为什么要加 WHERE a.学号 < b.学号？

如果不加，会出现：

1. 张三和张三同班（自己匹配自己）
2. 张三和李四、李四和张三（重复）

-- 加了条件后：排除自己+去除重复
SELECT a.姓名 AS 学生1, b.姓名 AS 学生2
FROM 学生 a
INNER JOIN 学生 b ON a.班级 = b.班级
WHERE a.学号 < b.学号;

执行结果：

学生表

学号	姓名	班级
1	张三	一班
2	李四	一班
3	王五	一班
4	赵六	二班

• 自连接结果（WHERE a.学号 < b.学号）：

学生1	学生2	班级
张三	李四	一班
张三	王五	一班
李四	王五	一班

---

综合代码案例

-- 1. 内连接（推荐用 USING）
SELECT s.姓名, c.班级名称
FROM 学生 s
INNER JOIN 班级 c USING (班级编号);

-- 2. 左连接：查询所有学生
SELECT s.姓名, c.班级名称
FROM 学生 s
LEFT JOIN 班级 c USING (班级编号);

-- 3. 右连接
SELECT s.姓名, c.班级名称
FROM 学生 s
RIGHT JOIN 班级 c USING (班级编号);

-- 4. 全外连接
SELECT s.姓名, c.班级名称
FROM 学生 s
FULL OUTER JOIN 班级 c USING (班级编号);

-- 5. 自连接：找同班同学（去重）
SELECT a.姓名 AS 学生1, b.姓名 AS 学生2
FROM 学生 a
INNER JOIN 学生 b ON a.班级 = b.班级
WHERE a.学号 < b.学号;

综合案例结果

学生表

学号	姓名	班级编号
1	张三	1
2	李四	1
3	王五	2
4	赵六	NULL

班级表

班级编号	班级名称
1	一班
2	二班

1. INNER JOIN – 返回3条
2. LEFT JOIN – 返回全部4条，赵六为NULL
3. RIGHT JOIN – 返回2个班级
4. FULL OUTER JOIN – 返回5条（含NULL学生和三班）
5. 自连接 – 返回同班级不同人组合（去重）

---

第八课：高级查询

知识点

1. SQL Union – 合并查询结果（去重）
2. SQL Union All – 合并结果（不去重）
3. SQL Select Into – 查询结果存入新表
4. SQL Insert Into Select – 从其他表复制数据
5. SQL Null Functions – 空值处理函数

Union 合并结果（去重）

UNION 用来合并多个 SELECT 的结果，自动去除重复行。

重要限制：两个 SELECT 的列数量必须相同，列的数据类型必须兼容。

-- 正确：两个 SELECT 都只查 1 列
SELECT 姓名 FROM 学生
UNION
SELECT 姓名 FROM 教师;

-- 错误：列数不同（会报错）
-- SELECT 姓名, 年龄 FROM 学生
-- UNION
-- SELECT 姓名 FROM 教师;

UNION vs UNION ALL 对比图：

flowchart LR
    subgraph 查询1[一班学生]
        Q1[张三]
        Q2[李四]
        Q3[王五]
    end

    subgraph 查询2[二班学生]
        Q4[张三]
        Q5[赵六]
    end

    subgraph UNION去重[UNION - 去重]
        U1[张三]
        U2[李四]
        U3[王五]
        U4[赵六]
    end

    subgraph UNION_ALL不去重[UNION ALL - 保留]
        UA1[张三]
        UA2[李四]
        UA3[王五]
        UA4[张三]
        UA5[赵六]
    end

    Q1 --> U1
    Q2 --> U2
    Q3 --> U3
    Q4 --> U1
    Q5 --> U4

    Q1 --> UA1
    Q2 --> UA2
    Q3 --> UA3
    Q4 --> UA4
    Q5 --> UA5

-- 合并一班和二班的学生姓名
SELECT 姓名 FROM 学生 WHERE 班级 = '一班'
UNION
SELECT 姓名 FROM 学生 WHERE 班级 = '二班';

-- UNION ALL：不去除重复，速度更快
SELECT 姓名 FROM 学生 WHERE 班级 = '一班'
UNION ALL
SELECT 姓名 FROM 学生 WHERE 班级 = '二班';

执行结果：

学生表：

学号	姓名	班级
1	张三	一班
2	李四	一班
3	王五	二班
4	赵六	二班

• UNION 结果（去重）：

姓名
张三
李四
王五
赵六

• UNION ALL 结果（保留重复）：

姓名
张三
李四
王五
赵六

（如果两班有同名学生，UNION 只显示一次，UNION ALL 显示两次）

---

Select Into：查询结果存入新表

SELECT INTO 用来将查询结果保存为一张新表。

基础语法：

SELECT 列1, 列2, ...
INTO 新表名
FROM 表名
WHERE 条件;

-- 将一班学生复制到新表
SELECT *
INTO 一班学生表
FROM 学生
WHERE 班级 = '一班';

-- PostgreSQL 语法（使用 CREATE TABLE）
CREATE TABLE 一班学生表 AS
SELECT * FROM 学生 WHERE 班级 = '一班';

执行结果：

执行后，数据库会创建一张名为”一班学生表”的新表，包含所有一班学生的数据。

---

Insert Into Select：从其他表复制数据

INSERT INTO SELECT 用来从一张表复制数据到另一张表。

基础语法：

INSERT INTO 目标表 (列1, 列2, ...)
SELECT 列1, 列2, ...
FROM 源表
WHERE 条件;

-- 从学生表复制到备份表
INSERT INTO 学生备份 (学号, 姓名, 班级)
SELECT 学号, 姓名, 班级
FROM 学生;

-- 复制特定班级的学生
INSERT INTO 一班备份
SELECT * FROM 学生 WHERE 班级 = '一班';

执行结果：

假设有”学生备份表”，结构与学生表相同：

操作	结果
复制全部	备份表包含学生表的所有数据
条件复制	备份表只包含符合条件的记录

---

Null Functions：空值处理函数

PostgreSQL 处理 NULL 的常用函数：

函数	作用
COALESCE(a, b, c…)	返回第一个非 NULL 值
NULLIF(a, b)	如果 a=b，返回 NULL；否则返回 a

COALESCE 最常用：

-- 如果班级为 NULL，显示"未分配"
SELECT 姓名, COALESCE(班级, '未分配') AS 班级 FROM 学生;

-- 如果电话为 NULL，显示邮箱；如果邮箱也为 NULL，显示"无联系方式"
SELECT 姓名, COALESCE(电话, 邮箱, '无联系方式') AS 联系方式 FROM 学生;

NULLIF 用途 1：避免除零错误

SELECT 金额 / NULLIF(数量, 0) AS 单价 FROM 订单;
-- 如果数量=0，NULLIF返回NULL，结果变成NULL而不是报错

NULLIF 用途 2：空字符串转换为 NULL（数据清理神器）

-- 处理爬虫或Excel导入的空字符串
SELECT NULLIF(地址, '') AS 地址 FROM 用户;
-- 如果地址是空字符串 ''，会转换成 NULL
-- 后续可以用 COALESCE 处理

小技巧：数据清洗时，先用 NULLIF 把空字符串变 NULL，再用 COALESCE 填默认值。

执行结果：

学生表：

学号	姓名	班级	电话
1	张三	一班	13800138000
2	李四	一班	NULL
3	王五	二班	NULL

• COALESCE(班级, '未分配')：

姓名	班级
张三	一班
李四	一班
王五	二班

• COALESCE(电话, '无')：

姓名	电话
张三	13800138000
李四	无
王五	无

---

综合代码案例

-- 1. Union 合并两个班的学生
SELECT 姓名 FROM 学生 WHERE 班级 = '一班'
UNION
SELECT 姓名 FROM 学生 WHERE 班级 = '二班';

-- 2. Union All 保留重复
SELECT 姓名 FROM 学生 WHERE 班级 = '一班'
UNION ALL
SELECT 姓名 FROM 学生 WHERE 班级 = '二班';

-- 3. 创建备份表（PostgreSQL 语法）
CREATE TABLE 学生备份 AS
SELECT * FROM 学生;

-- 4. 复制数据到备份表
INSERT INTO 学生备份
SELECT * FROM 学生 WHERE 班级 = '一班';

-- 5. 处理 NULL 值
SELECT 
    姓名,
    COALESCE(班级, '未分配') AS 班级,
    COALESCE(电话, '无') AS 电话
FROM 学生;

综合案例结果

假设学生表：

学号	姓名	班级	电话
1	张三	一班	13800138000
2	李四	一班	NULL
3	王五	二班	NULL
4	赵六	二班	13900139000

1. UNION – 返回4个不重复的姓名
2. UNION ALL – 返回4个姓名（可能有重复）
3. CREATE TABLE AS – 创建”学生备份”表
4. INSERT INTO SELECT – 复制数据
5. COALESCE – 处理后的结果

---

第九课：约束

知识点

1. SQL Constraints – 约束概述
2. SQL Not Null – 非空约束
3. SQL Unique – 唯一约束
4. SQL Primary Key – 主键
5. SQL Foreign Key – 外键

约束概述（Constraints）

约束是数据库用来限制表中数据的规则。约束在数据进入数据库时就会检查，不符合规则的数据会被拒绝。

常见约束类型：

约束	作用
NOT NULL	该列不能为空
UNIQUE	该列值不能重复
PRIMARY KEY	主键（唯一+非空）
FOREIGN KEY	外键（关联另一表）
CHECK	自定义规则
DEFAULT	默认值

---

非空约束（NOT NULL）

NOT NULL 规定列不能为空。

CREATE TABLE 学生 (
    学号 INTEGER PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,  -- 姓名不能为空
    年龄 INTEGER,
    班级 VARCHAR(50)
);

执行结果：

尝试插入空姓名：

INSERT INTO 学生 (学号, 姓名) VALUES (1, NULL);
-- 报错：ERROR: null value in column "姓名" violates not-null constraint

---

唯一约束（UNIQUE）

UNIQUE 规定列值不能重复。

注意：UNIQUE 允许出现多个 NULL 值！

CREATE TABLE 学生 (
    学号 SERIAL PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,
    邮箱 VARCHAR(100) UNIQUE  -- 邮箱不能重复
);

执行结果：

-- 正确：邮箱不重复
INSERT INTO 学生 (姓名, 邮箱) VALUES ('张三', '[email protected]');
INSERT INTO 学生 (姓名, 邮箱) VALUES ('李四', '[email protected]');

-- 正确：多个 NULL（允许）
INSERT INTO 学生 (姓名, 邮箱) VALUES ('王五', NULL);
INSERT INTO 学生 (姓名, 邮箱) VALUES ('赵六', NULL);

-- 错误：邮箱重复
INSERT INTO 学生 (姓名, 邮箱) VALUES ('孙七', '[email protected]');
-- 报错：ERROR: duplicate key value violates unique constraint

如果需要”不允许NULL”，需要额外加 NOT NULL：

邮箱 VARCHAR(100) NOT NULL UNIQUE

---

主键（PRIMARY KEY）

主键 = 唯一 + 非空。每张表只能有一个主键。

CREATE TABLE 学生 (
    学号 INTEGER PRIMARY KEY,  -- 主键
    姓名 VARCHAR(50) NOT NULL,
    年龄 INTEGER
);

主键特点：

• 值唯一，不能重复
• 不能为空
• 每表只能有一个

---

外键（FOREIGN KEY）

外键用来建立两张表的关联，保证引用完整性。

-- 班级表
CREATE TABLE 班级 (
    班级编号 INTEGER PRIMARY KEY,
    班级名称 VARCHAR(50) NOT NULL
);

-- 学生表（外键引用班级表）
CREATE TABLE 学生 (
    学号 INTEGER PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,
    班级编号 INTEGER REFERENCES 班级(班级编号)  -- 外键
);

外键约束的作用：

1. 学生表中的”班级编号”必须是班级表中存在的值
2. 不能插入”班级编号”为不存在编号的学生
3. 删除班级表中的记录时，如果学生表有关联，会被阻止（或级联删除）

-- 正确：班级编号 1 存在于班级表中
INSERT INTO 学生 (学号, 姓名, 班级编号) VALUES (1, '张三', 1);

-- 错误：班级编号 99 不存在
INSERT INTO 学生 (学号, 姓名, 班级编号) VALUES (2, '李四', 99);
-- 报错：ERROR: insert or update on table "学生" violates foreign key constraint

erDiagram
    Class ||--o{ Student : contains

    Class {
        int class_id
        varchar class_name
    }

    Student {
        int student_id
        varchar name
        int class_id
    }

---

CHECK 约束

CHECK 用来定义自定义规则。

单列 CHECK：

CREATE TABLE 学生 (
    学号 SERIAL PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,
    年龄 INTEGER CHECK (年龄 >= 0 AND 年龄 <= 150),
    成绩 DECIMAL(5,2) CHECK (成绩 >= 0 AND 成绩 <= 100)
);

多列 CHECK（表级约束）：

-- 结束日期必须晚于开始日期
CREATE TABLE 合同 (
    合同编号 SERIAL PRIMARY KEY,
    员工姓名 VARCHAR(50) NOT NULL,
    开始日期 DATE NOT NULL,
    结束日期 DATE NOT NULL,
    CONSTRAINT chk_date CHECK (结束日期 > 开始日期)
);

-- 正确：开始日期早于结束日期
INSERT INTO 合同 (员工姓名, 开始日期, 结束日期) 
VALUES ('张三', '2024-01-01', '2024-12-31');

-- 错误：结束日期早于开始日期
INSERT INTO 合同 (员工姓名, 开始日期, 结束日期) 
VALUES ('李四', '2024-12-31', '2024-01-01');
-- 报错：ERROR: new row for relation "合同" violates check constraint "chk_date"

执行结果：

-- 正确
INSERT INTO 学生 (学号, 姓名, 年龄, 成绩) VALUES (1, '张三', 18, 85.5);

-- 错误：年龄超出范围
INSERT INTO 学生 (学号, 姓名, 年龄) VALUES (2, '李四', 200);
-- 报错：ERROR: new row for relation "学生" violates check constraint "学生_年龄_check"

---

DEFAULT 默认值

DEFAULT 为列提供默认值。

CREATE TABLE 学生 (
    学号 INTEGER PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,
    班级 VARCHAR(50) DEFAULT '未分配',  -- 默认值
    状态 CHAR(1) DEFAULT 'A'            -- 默认值 A
);

执行结果：

-- 不指定班级时，使用默认值"未分配"
INSERT INTO 学生 (学号, 姓名) VALUES (1, '张三');
-- 结果：班级 = '未分配', 状态 = 'A'

-- 指定班级时，使用指定值
INSERT INTO 学生 (学号, 姓名, 班级) VALUES (2, '李四', '一班');
-- 结果：班级 = '一班', 状态 = 'A'

---

修改表添加约束

已存在的表可以通过 ALTER TABLE 添加约束。

-- 添加唯一约束
ALTER TABLE 学生 ADD CONSTRAINT 邮箱唯一 UNIQUE (邮箱);

-- 添加外键约束
ALTER TABLE 学生 ADD CONSTRAINT 班级外键 
    FOREIGN KEY (班级编号) REFERENCES 班级(班级编号);

-- 添加 CHECK 约束
ALTER TABLE 学生 ADD CONSTRAINT 年龄检查 
    CHECK (年龄 >= 0 AND 年龄 <= 150);

---

综合代码案例

-- 1. 创建带完整约束的表
CREATE TABLE 员工 (
    工号 INTEGER PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,
    邮箱 VARCHAR(100) UNIQUE,
    部门编号 INTEGER REFERENCES 部门(部门编号),
    工资 DECIMAL(10,2) CHECK (工资 >= 0),
    状态 CHAR(1) DEFAULT '在职'
);

-- 2. 插入数据（符合约束）
INSERT INTO 员工 (工号, 姓名, 邮箱, 工资) 
VALUES (1, '张三', 'zhangsan@公司.com', 5000);

-- 3. 尝试插入违反约束的数据
-- INSERT INTO 员工 (工号, 姓名, 工资) VALUES (2, '李四', -100);
-- 会报错：工资不能为负

综合案例结果

假设创建员工表并执行插入：

工号	姓名	邮箱	工资	状态
1	张三	zhangsan@公司.com	5000	在职

尝试插入重复邮箱：

INSERT INTO 员工 (工号, 姓名, 邮箱) VALUES (2, '李四', 'zhangsan@公司.com');
-- 报错：邮箱重复

尝试插入负工资：

INSERT INTO 员工 (工号, 姓名, 工资) VALUES (2, '李四', -100);
-- 报错：CHECK 约束违反

---

第十课：索引与视图

知识点

1. SQL Index – 索引（提高查询速度）
2. SQL Auto Increment – 自增主键（续）
3. SQL Views – 视图（虚拟表）
4. SQL Drop – 删除表/视图/数据库
5. SQL Alter Table – 修改表结构

索引（Index）

索引就像书的目录，可以加快查询速度，但会让写入变慢。

索引的”双刃剑”特性：

操作	有索引	无索引
SELECT（查询）	快	慢
INSERT（插入）	慢	快
UPDATE（更新）	慢	快
DELETE（删除）	慢	快

类比：如果你给书的每一页、每一个字都做目录，那写书的速度会慢得多。

B-Tree 索引结构（二分查找）：

flowchart TD
    root[根节点: 50]

    left[左子树 < 50] --> l1[25]
    right[右子树 > 50] --> r1[75]

    l1 --> ll[10, 30]
    l1 --> lr[35, 40]
    r1 --> rl[60, 70]
    r1 --> rr[80, 90]

    target[查找50]
    root --> target

    style root fill:#e3f2fd
    style target fill:#4caf50

基础语法：

-- 创建索引
CREATE INDEX 索引名 ON 表名 (列名);

-- 创建复合索引（按多列查询时有效）
CREATE INDEX idx_班级姓名 ON 学生 (班级, 姓名);

-- 创建唯一索引
CREATE UNIQUE INDEX uq_邮箱 ON 学生 (邮箱);

-- 删除索引
DROP INDEX 索引名;

使用场景：

-- 创建姓名的索引（加快按姓名搜索）
CREATE INDEX idx_姓名 ON 学生 (姓名);

-- 查询时会使用索引
SELECT * FROM 学生 WHERE 姓名 = '张三';

PostgreSQL 查看索引是否生效：EXPLAIN

-- 查看查询计划
EXPLAIN SELECT * FROM 学生 WHERE 姓名 = '张三';

EXPLAIN 输出解读：

• Seq Scan（全表扫描）= 没有使用索引，逐一读取
• Index Scan（索引扫描）= 使用了索引，快速定位
• Bitmap Scan = 使用了复合索引

-- 查看更详细的分析
EXPLAIN ANALYZE SELECT * FROM 学生 WHERE 姓名 = '张三';

重要：EXPLAIN ANALYZE 会真正执行查询并显示实际耗时，是调优的必备工具。

执行结果：

假设学生表有10000条记录：

• 无索引：需要扫描10000行（Seq Scan）
• 有索引：直接定位（Index Scan），约1-2行

注意：索引不是越多越好！

• 频繁更新的列不适合建索引
• 小表不需要索引
• 常用查询的列才需要建索引
• 索引占用额外磁盘空间

---

视图（Views）

视图是一个虚拟表，本质是一个保存的 SELECT 查询。

视图的实用场景：安全隔离

-- 创建视图：隐藏敏感信息（手机号）
CREATE VIEW 学生基本信息 AS
SELECT 学号, 姓名, 班级
FROM 学生;

-- 普通员工只能看这个视图，看不到手机号
SELECT * FROM 学生基本信息;

为什么用视图：

1. 简化复杂查询：把常用的 JOIN 查询保存为视图
2. 安全隔离：只暴露必要的列，隐藏敏感信息
3. 权限控制：不同用户看不同的视图

-- 创建视图
CREATE VIEW 一班学生视图 AS
SELECT 学号, 姓名, 年龄
FROM 学生
WHERE 班级 = '一班';

-- 查询视图（像查表一样）
SELECT * FROM 一班学生视图;

-- 查询视图的统计
SELECT COUNT(*) FROM 一班学生视图;

视图的特点：

• 不存储实际数据，只存储查询定义
• 每次查询视图时，查询会重新执行
• 可以简化复杂查询

视图 vs 表：

特点	表	视图
存储数据	是	否
实际占用空间	是	否
更新数据	直接更新	取决于视图类型

-- 删除视图
DROP VIEW 一班学生视图;

---

删除操作（Drop）

-- 删除表（数据+结构全部删除）
DROP TABLE 学生;

-- 删除视图
DROP VIEW 一班学生视图;

-- 删除数据库
DROP DATABASE 学生管理系统;

-- 清空表数据（保留结构）
TRUNCATE TABLE 学生;

TRUNCATE vs DELETE：

特性	DELETE	TRUNCATE
速度	慢（逐行删除）	快（整表清空）
事务日志	记录每行	少量日志
自增重置	否	是
可回滚	是（事务内）	视设置而定

-- TRUNCATE：速度极快，会重置 SERIAL/IDENTITY
TRUNCATE TABLE 学生;

-- DELETE：可以加条件，可回滚
DELETE FROM 学生 WHERE 班级 = '一班';

危险提示：

• DROP TABLE 会彻底删除表，无法恢复
• TRUNCATE 删除后通常无法轻易回滚，比 DELETE 更危险！

---

修改表结构（Alter Table）

-- 添加新列
ALTER TABLE 学生 ADD COLUMN 电话 VARCHAR(20);

-- 删除列
ALTER TABLE 学生 DROP COLUMN 电话;

-- 重命名表
ALTER TABLE 学生 RENAME TO 学生信息;

PostgreSQL 修改列类型（可能需要 USING）：

-- 直接修改类型可能报错
ALTER TABLE 学生 ALTER COLUMN 年龄 TYPE INTEGER;
-- 报错：ERROR: column "年龄" cannot be cast to type integer

-- 正确写法：使用 USING 子句
ALTER TABLE 学生 ALTER COLUMN 年龄 TYPE INTEGER USING 年龄::integer;

-- 字符串转日期
ALTER TABLE 合同 ALTER COLUMN 开始日期 TYPE DATE USING 开始日期::date;

---

综合代码案例

-- 1. 创建索引
CREATE INDEX idx_姓名 ON 学生 (姓名);
CREATE INDEX idx_班级 ON 学生 (班级);

-- 2. 创建视图（简化查询）
CREATE VIEW 学生统计 AS
SELECT 班级, COUNT(*) AS 人数, AVG(年龄) AS 平均年龄
FROM 学生
GROUP BY 班级;

-- 3. 查询视图
SELECT * FROM 学生统计;

-- 4. 修改表结构
ALTER TABLE 学生 ADD COLUMN 邮箱 VARCHAR(100);

-- 5. 删除操作
DROP INDEX idx_姓名;
DROP VIEW 学生统计;

综合案例结果

假设学生表：

学号	姓名	班级	年龄
1	张三	一班	18
2	李四	一班	19
3	王五	二班	20

1. 创建索引后，查询速度提升
2. 创建视图后，查询”学生统计”：

班级	人数	平均年龄
一班	2	18.5
二班	1	20

1. 添加新列后，表结构更新

---

学习建议

1. 常用命令：SELECT、FROM、WHERE、ORDER BY、GROUP BY、HAVING、JOIN
2. 字符串用单引号，数值直接写，NULL 用 IS 判断
3. UPDATE/DELETE 一定要加 WHERE 条件
4. 索引用于加速查询，不要在频繁更新的列上建索引
5. 视图用于简化复杂查询，不存储实际数据

---

SQL 课程结束！