SQL与关系代数实战：从3个经典学生选课表到5类查询的完整转换练习

SQL与关系代数实战：从3个经典学生选课表到5类查询的完整转换练习

在数据库系统学习中，掌握SQL与关系代数的相互转换是理解查询本质的关键技能。本文将以学生(S)、课程(C)、选课(SC)三个经典表为基础，通过5类典型查询场景，系统讲解两种查询语言的对应关系，帮助读者建立从理论到实践的完整知识框架。

1. 基础表结构与关系代数运算回顾

1.1 表结构定义

我们使用以下三个表作为练习基础：

学生表Student(S)

S(Sno, Sname, Age, Sex, Dept) 主键：Sno

Sno-学号，Sname-姓名，Age-年龄，Sex-性别，Dept-所在系

课程表Course(C)

C(Cno, Cname, Credit, Teacher) 主键：Cno

Cno-课程号，Cname-课程名，Credit-学分，Teacher-授课教师

选课表SC

SC(Sno, Cno, Grade) 主键：(Sno, Cno) 外键：Sno参照Student(Sno)，Cno参照Course(Cno)

Grade-成绩

1.2 关系代数基本运算

关系代数包含以下核心运算：

提示：关系代数表达式默认去除重复元组，而SQL需要使用DISTINCT显式去重

2. 选择运算：单表条件筛选

2.1 基础选择查询

例题1：查询计算机系年龄小于20的学生信息

关系代数：

σ(Dept='计算机' ∧ Age<20)(Student)

SQL实现：

SELECT * FROM Student WHERE Dept = '计算机' AND Age < 20;

2.2 复杂条件选择

例题2：查询选修了课程号为'C001'且成绩在80-90之间的学生学号

关系代数：

π(Sno)(σ(Cno='C001' ∧ Grade≥80 ∧ Grade≤90)(SC))

SQL实现：

SELECT DISTINCT Sno FROM SC WHERE Cno = 'C001' AND Grade BETWEEN 80 AND 90;

3. 投影运算：属性选择与列裁剪

3.1 基本投影操作

例题3：查询所有学生的姓名和所在系

关系代数：

π(Sname, Dept)(Student)

SQL实现：

SELECT Sname, Dept FROM Student;

3.2 投影与选择的组合

例题4：查询选修了'C001'课程的学生姓名

关系代数：

π(Sname)(Student ⋈ σ(Cno='C001')(SC))

SQL实现：

SELECT DISTINCT S.Sname FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE SC.Cno = 'C001';

4. 连接运算：多表关联查询

4.1 自然连接

例题5：查询选修了'数据库原理'课程的学生学号和成绩

关系代数：

π(Sno, Grade)(SC ⋈ σ(Cname='数据库原理')(Course))

SQL实现：

SELECT SC.Sno, SC.Grade FROM SC JOIN Course C ON SC.Cno = C.Cno WHERE C.Cname = '数据库原理';

4.2 外连接应用

例题6：查询所有学生选课情况（包括未选课学生）

关系代数（左外连接）：

Student ⟕ SC

SQL实现：

SELECT S.Sno, S.Sname, SC.Cno, SC.Grade FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno;

5. 分组聚合与嵌套查询

5.1 分组统计

例题7：统计每门课程的平均成绩，只显示平均分≥75的课程

关系代数：

γ(Cno; AVG(Grade)→AvgGrade)(SC) ⋈ σ(AvgGrade≥75)(γ(Cno; AVG(Grade)→AvgGrade)(SC))

SQL实现：

SELECT Cno, AVG(Grade) AS AvgGrade FROM SC GROUP BY Cno HAVING AVG(Grade) >= 75;

5.2 存在性测试

例题8：查询没有选修'C001'课程的学生姓名

关系代数：

π(Sname)(Student - π(Sno)(σ(Cno='C001')(SC)) ⋈ Student)

SQL实现（使用NOT EXISTS）：

SELECT Sname FROM Student S WHERE NOT EXISTS ( SELECT * FROM SC WHERE SC.Sno = S.Sno AND SC.Cno = 'C001' );

6. 综合应用与优化技巧

6.1 复杂查询分解

例题9：查询选修了全部课程的学生姓名

关系代数（除法运算）：

π(Sno, Cno)(SC) ÷ π(Cno)(Course) ⋈ π(Sno, Sname)(Student)

SQL实现（使用双重NOT EXISTS）：

SELECT S.Sname FROM Student S WHERE NOT EXISTS ( SELECT * FROM Course C WHERE NOT EXISTS ( SELECT * FROM SC WHERE SC.Sno = S.Sno AND SC.Cno = C.Cno ) );

6.2 查询优化原则

1. 选择下推：尽早执行选择运算减少中间结果
2. 投影下推：尽早去除不需要的列
3. 连接顺序：小表优先连接
4. 避免笛卡尔积：确保连接条件正确

注意：实际优化还需考虑索引、统计信息等因素

通过这5类查询的完整练习，我们系统建立了SQL与关系代数的对应关系。在实际数据库开发中，理解这种对应关系有助于：

• 准确分析复杂查询语义
• 优化查询执行效率
• 设计更合理的数据库模式