关系模型精讲：从投影、选择到连接与除运算的SQL实战

在上一部分介绍了关系模型的基础概念后，我们今天继续深入，详解关系代数中的几项核心运算：投影、选择、连接以及相对复杂的除运算。理解这些运算是掌握数据库查询精髓的关键，它们不仅是SQL语言的数学基础，也是我们进行高效数据库查询的逻辑核心。

1. 投影 (π_A(R))

专业性描述

投影运算（Projection）是从关系R中选择指定的属性列，并去除重复的元组，形成一个新的关系。数学符号：π_A(R)，其中A是希望保留的属性集合。
投影操作相当于垂直切割表格，只保留我们关心的列。

大白话理解

投影就像拍照时的特写镜头：

• 你拍全班集体照（整个表的所有列）
• 但你现在只需要每个学生的姓名和学号（特写）
• 投影就是从这个大照片中截取姓名和学号部分
• 如果多个学生有相同信息，自动去重

智慧学堂中的投影运算示例

原始学生表 (students)

student_id	student_name	gender	birth_date	major	enroll_year
1001	张三	男	2000-01-15	计算机科学	2023
1002	李四	女	2001-03-22	软件工程	2023
1003	王五	男	2002-05-10	计算机科学	2023
1004	赵六	女	2001-08-30	软件工程	2023

投影运算：π_{major, enroll_year}(students)

只保留专业和入学年份两列，并去除重复行：

major	enroll_year
计算机科学	2023
软件工程	2023

注意：原始表有4行，但投影后只有2行，因为去除了重复的组合。

SQL实现

-- 创建学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    gender CHAR(1),
    birth_date DATE,
    major VARCHAR(50),
    enroll_year INT
);
-- 插入测试数据
INSERT INTO students VALUES
(1001, '张三', '男', '2000-01-15', '计算机科学', 2023),
(1002, '李四', '女', '2001-03-22', '软件工程', 2023),
(1003, '王五', '男', '2002-05-10', '计算机科学', 2023),
(1004, '赵六', '女', '2001-08-30', '软件工程', 2023);
-- 投影运算：选择专业和入学年份
SELECT DISTINCT major, enroll_year
FROM students;
-- 结果：
-- 计算机科学, 2023
-- 软件工程, 2023
-- 选择学生姓名和专业
SELECT student_name, major
FROM students;
-- 结果：4行数据（不去重）
-- 张三, 计算机科学
-- 李四, 软件工程
-- 王五, 计算机科学
-- 赵六, 软件工程

2. 选择 (σ_F(R))

专业性描述

选择运算（Selection）是从关系R中选择满足给定条件F的元组，形成一个新的关系。数学符号：σ_F(R)，其中F是选择条件。
选择操作相当于水平切割表格，只保留我们关心的行。

大白话理解

选择就像用筛子筛沙子：

• 你有一堆混合的沙子（整个表的所有行）
• 你用一个特定大小的筛子（条件）过滤
• 筛子上留下符合要求的沙子（符合条件的行）
• 筛子下是不符合条件的沙子（被过滤掉的行）

智慧学堂中的选择运算示例

选择运算：σ_{major='计算机科学' AND gender='男'}(students)

选择专业是“计算机科学”且性别是“男”的学生：

student_id	student_name	gender	birth_date	major	enroll_year
1001	张三	男	2000-01-15	计算机科学	2023
1003	王五	男	2002-05-10	计算机科学	2023

SQL实现

-- 选择运算：专业是计算机科学且性别是男的学生
SELECT *
FROM students
WHERE major = '计算机科学' AND gender = '男';
-- 结果：张三和王五
-- 选择2001年出生的学生
SELECT *
FROM students
WHERE YEAR(birth_date) = 2001;
-- 结果：李四和赵六
-- 选择姓“张”的学生
SELECT *
FROM students
WHERE student_name LIKE '张%';
-- 结果：张三
-- 选择多个条件：2001年出生或专业是软件工程
SELECT *
FROM students
WHERE YEAR(birth_date) = 2001 OR major = '软件工程';
-- 结果：李四、王五、赵六
-- 注意：王五虽然专业是计算机科学，但满足2001年出生条件

3. 连接 (join)

3.1. θ连接 (基础形式)

专业性描述

θ连接（Theta Join）是从两个关系R和S的笛卡儿积中选择满足给定条件θ的元组。数学符号：R ⋈_{θ} S，其中θ是比较运算符，如=、<、>、≤、≥、≠等。
θ连接是连接运算的一般形式，其他连接都是θ连接的特例。

大白话理解

θ连接就像相亲配对：

• 有男生名单和女生名单（两个表）
• 先让所有可能的男女配对（笛卡儿积）
• 然后按照一定条件筛选（θ条件）
• 比如：年龄相差不超过3岁（年龄差≤3）
• 符合条件的就连接在一起

智慧学堂中的θ连接示例

学生表 (students)

student_id	student_name	birth_year
1001	张三	2000
1002	李四	2001

教师表 (teachers)

teacher_id	teacher_name	birth_year
2001	王老师	1985
2002	李老师	1990

θ连接：students ⋈_{students.birth_year > teachers.birth_year - 30} teachers

连接条件：学生出生年份 > 教师出生年份 - 30（即年龄差小于30岁）

SQL实现

-- 创建测试表
CREATE TABLE students_theta (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    birth_year INT
);
CREATE TABLE teachers_theta (
    teacher_id INT PRIMARY KEY,
    teacher_name VARCHAR(50),
    birth_year INT
);
-- 插入测试数据
INSERT INTO students_theta VALUES
(1001, '张三', 2000),
(1002, '李四', 2001);
INSERT INTO teachers_theta VALUES
(2001, '王老师', 1985),
(2002, '李老师', 1990);
-- θ连接：年龄差小于30岁的师生组合
SELECT s.student_id, s.student_name, s.birth_year as student_birth,
       t.teacher_id, t.teacher_name, t.birth_year as teacher_birth
FROM students_theta s
CROSS JOIN teachers_theta t
WHERE s.birth_year > t.birth_year - 30;
-- 结果：
-- 1001, 张三, 2000, 2001, 王老师, 1985
-- 1001, 张三, 2000, 2002, 李老师, 1990
-- 1002, 李四, 2001, 2001, 王老师, 1985
-- 1002, 李四, 2001, 2002, 李老师, 1990
-- 所有师生年龄差都小于30岁，所以是所有组合

3.2. 等值连接 (θ为“=”的特殊θ连接)

专业性描述

等值连接（Equijoin）是θ连接的特殊情况，其中θ是等号“=”。等值连接从两个关系的笛卡儿积中选择指定属性值相等的元组。

大白话理解

等值连接就像配钥匙：

• 有一批锁和一批钥匙（两个表）
• 只有锁的编号和钥匙的编号相同时（等值条件）
• 才能把锁和钥匙配在一起
• 编号就是连接的依据

智慧学堂中的等值连接示例

学生表 (students)

student_id	student_name	class_id
1001	张三	301
1002	李四	302
1003	王五	301

班级表 (classes)

class_id	class_name	head_teacher
301	计算机一班	王老师
302	计算机二班	李老师
303	软件工程班	张老师

等值连接：students ⋈_{students.class_id = classes.class_id} classes

student_id	student_name	class_id	class_name	head_teacher
1001	张三	301	计算机一班	王老师
1002	李四	302	计算机二班	李老师
1003	王五	301	计算机一班	王老师

注意：班级303没有学生，所以没有出现在结果中。

SQL实现

-- 创建测试表
CREATE TABLE students_eq (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    class_id INT
);
CREATE TABLE classes_eq (
    class_id INT PRIMARY KEY,
    class_name VARCHAR(50),
    head_teacher VARCHAR(50)
);
-- 插入测试数据
INSERT INTO students_eq VALUES
(1001, '张三', 301),
(1002, '李四', 302),
(1003, '王五', 301);
INSERT INTO classes_eq VALUES
(301, '计算机一班', '王老师'),
(302, '计算机二班', '李老师'),
(303, '软件工程班', '张老师');
-- 等值连接：学生和班级通过class_id连接
SELECT s.student_id, s.student_name, s.class_id,
       c.class_name, c.head_teacher
FROM students_eq s
INNER JOIN classes_eq c ON s.class_id = c.class_id;
-- 或者用旧的语法
SELECT s.student_id, s.student_name, s.class_id,
       c.class_name, c.head_teacher
FROM students_eq s, classes_eq c
WHERE s.class_id = c.class_id;
-- 结果：
-- 1001, 张三, 301, 计算机一班, 王老师
-- 1002, 李四, 302, 计算机二班, 李老师
-- 1003, 王五, 301, 计算机一班, 王老师

3.3. 自然连接 (特殊的等值连接)

专业性描述

自然连接（Natural Join）是一种特殊的等值连接，它会自动比较两个关系中所有同名的属性，并在结果中消除重复的属性列。
自然连接是关系代数中最常用的连接操作之一。

大白话理解

自然连接就像自动配对：

• 有两份名单，都有“学号”这个字段
• 自然连接会自动找到这个共同字段
• 然后把两份名单中相同学号的行合并
• 结果中“学号”只出现一次（去重）

智慧学堂中的自然连接示例

学生表 (students_nat) - 假设有同名属性

student_id	student_name	class_id
1001	张三	301
1002	李四	302

成绩表 (scores_nat)

student_id	course_id	score
1001	2001	85.5
1001	2002	92.0
1002	2001	78.0

自然连接：students_nat ⋈ scores_nat

自动按student_id连接，结果中student_id只出现一次：

student_id	student_name	class_id	course_id	score
1001	张三	301	2001	85.5
1001	张三	301	2002	92.0
1002	李四	302	2001	78.0

SQL实现

-- 创建测试表
CREATE TABLE students_nat (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    class_id INT
);
CREATE TABLE scores_nat (
    student_id INT,
    course_id INT,
    score DECIMAL(5,2),
    PRIMARY KEY (student_id, course_id)
);
-- 插入测试数据
INSERT INTO students_nat VALUES
(1001, '张三', 301),
(1002, '李四', 302);
INSERT INTO scores_nat VALUES
(1001, 2001, 85.5),
(1001, 2002, 92.0),
(1002, 2001, 78.0);
-- 自然连接（在SQL中需要显式指定连接条件）
SELECT s.student_id, s.student_name, s.class_id,
       sc.course_id, sc.score
FROM students_nat s
NATURAL JOIN scores_nat sc;
-- 或者用INNER JOIN
SELECT s.student_id, s.student_name, s.class_id,
       sc.course_id, sc.score
FROM students_nat s
INNER JOIN scores_nat sc ON s.student_id = sc.student_id;
-- 结果：
-- 1001, 张三, 301, 2001, 85.5
-- 1001, 张三, 301, 2002, 92.0
-- 1002, 李四, 302, 2001, 78.0

4. 除运算 (R÷S)

专业性描述

除运算（Division）是关系代数中最复杂的运算之一。给定关系R(X,Y)和S(Y)，其中X和Y是属性集合，R÷S的结果是一个新关系，包含所有满足以下条件的X值：对于S中的每个Y值，在R中都有对应的(X,Y)组合。
简单说：R÷S找出在R中包含了S中所有Y值的X。

大白话理解

除运算就像找全能选手：

• 有一个学生选课表R（学生，课程）
• 有一个课程列表S（核心课程）
• R÷S的结果是：选了所有核心课程的学生
• 也就是找出“全能”学生，选了S中的所有课程

智慧学堂中的除运算示例

学生选课表 (student_courses_div)

student_id	student_name	course_id	course_name
1001	张三	2001	数据库原理
1001	张三	2002	数据结构
1001	张三	2003	操作系统
1002	李四	2001	数据库原理
1002	李四	2002	数据结构
1003	王五	2001	数据库原理

核心课程表 (core_courses)

course_id	course_name
2001	数据库原理
2002	数据结构

除运算：student_courses_div ÷ core_courses

找出选了所有核心课程的学生：

student_id	student_name
1001	张三
1002	李四

结果解释：张三和李四都选了数据库原理和数据结构两门核心课程，王五只选了数据库原理，所以不在结果中。

SQL实现（使用NOT EXISTS实现除运算）

-- 创建测试表
CREATE TABLE student_courses_div (
    student_id INT,
    student_name VARCHAR(50),
    course_id INT,
    course_name VARCHAR(100)
);
CREATE TABLE core_courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);
-- 插入测试数据
INSERT INTO student_courses_div VALUES
(1001, '张三', 2001, '数据库原理'),
(1001, '张三', 2002, '数据结构'),
(1001, '张三', 2003, '操作系统'),
(1002, '李四', 2001, '数据库原理'),
(1002, '李四', 2002, '数据结构'),
(1003, '王五', 2001, '数据库原理');
INSERT INTO core_courses VALUES
(2001, '数据库原理'),
(2002, '数据结构');
-- 除运算：找出选了所有核心课程的学生
-- 方法：不存在某门核心课程是这个学生没选的
SELECT DISTINCT sc1.student_id, sc1.student_name
FROM student_courses_div sc1
WHERE NOT EXISTS (
    -- 找出核心课程
    SELECT course_id
    FROM core_courses cc
    WHERE NOT EXISTS (
        -- 检查这个学生是否选了这门课
        SELECT 1
        FROM student_courses_div sc2
        WHERE sc2.student_id = sc1.student_id
        AND sc2.course_id = cc.course_id
    )
);
-- 另一种写法
SELECT student_id, student_name
FROM student_courses_div
WHERE course_id IN (SELECT course_id FROM core_courses)
GROUP BY student_id, student_name
HAVING COUNT(DISTINCT course_id) = (SELECT COUNT(*) FROM core_courses);
-- 结果：
-- 1001, 张三
-- 1002, 李四

5. 关于元组

专业性描述

元组（Tuple）是关系模型中的基本概念，表示关系（表）中的一行数据。在关系模型中：

1. 每个元组代表一个实体或实体之间的联系
2. 元组由一组属性值组成，每个属性值对应一个属性（列）
3. 在同一个关系中，所有元组具有相同的属性结构
4. 关系中不允许有完全相同的元组（实体完整性约束）

元组的特性示例

学生表中的元组示例

student_id	student_name	gender	birth_date	状态说明
1001	张三	男	2000-01-15	一个有效的元组
1002	李四	女	2001-03-22	另一个有效的元组
1001	张三	男	2000-01-15	无效的元组（与第一个重复）
NULL	王五	男	2002-05-10	无效的元组（主键为NULL）

元组运算的重要性

关系代数中的所有运算最终都是对元组的操作：

• 投影： 从每个元组中选择部分属性
• 选择： 根据条件过滤元组
• 连接： 将不同关系的元组合并
• 除运算： 基于元组集合进行操作

元组的数学表示

假设有学生关系S，属性为(student_id, name, age)
那么一个元组可以表示为：
t = (1001, “张三”, 20)
在关系代数中，我们通常用t[A]表示元组t在属性A上的值：
t[student_id] = 1001
t[name] = “张三”
t[age] = 20

关系代数运算总结

1. 基本运算

运算	符号	说明	SQL对应
投影	π_A(R)	选择列	SELECT column1, column2
选择	σ_F(R)	选择行	WHERE condition
并	R ∪ S	合并行（去重）	UNION
差	R - S	在R不在S的行	EXCEPT 或 LEFT JOIN + IS NULL
笛卡儿积	R × S	所有组合	CROSS JOIN

2. 导出运算（可由基本运算表示）

运算	符号	基本运算表示	SQL对应
交	R ∩ S	R - (R - S)	INTERSECT 或 INNER JOIN
连接	R ⋈_{θ} S	σ_{θ}(R × S)	INNER JOIN ON condition
自然连接	R ⋈ S	π(去重属性)(σ{R.A=S.A}(R × S))	NATURAL JOIN 或 INNER JOIN ON R.A=S.A
除	R ÷ S	π_X(R) - π_X((π_X(R) × S) - R)	NOT EXISTS 子查询

在智慧学堂中的实际应用

运算	智慧学堂应用场景	示例查询
投影	生成学生名单、课程列表	只显示学生姓名和学号
选择	查询特定条件的学生	查询计算机专业的所有男生
θ连接	复杂的关联查询	查询年龄相差5岁以内的师生组合
等值连接	关联查询	查询学生及其所在班级信息
自然连接	通过共同字段自动连接	通过学号连接学生和成绩表
除运算	查询完成所有要求的学生	查询修完所有核心课程的学生

理解元组的重要性

元组是关系模型的基本单位，所有运算最终都作用于元组：

• 每个元组代表一个完整的实体信息
• 元组的唯一性保证了数据的一致性
• 对元组的操作是关系数据库的底层逻辑
• 理解元组有助于理解数据库的存储和检索机制

关系代数不仅是数据库的理论基础，也是我们理解和优化SQL查询的重要工具。通过掌握这些运算，我们能够更有效地从数据中提取有价值的信息，为智慧学堂这样的教育平台提供强大的数据支持。扎实的理论基础是进行高效系统设计的基石，更多深入的计算机理论基础和实践讨论，欢迎在云栈社区继续探索。