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

SQL与关系代数实战从3个经典学生选课表到5类查询的完整转换练习在数据库系统学习中掌握SQL与关系代数的相互转换是理解查询本质的关键技能。本文将以学生(S)、课程(C)、选课(SC)三个经典表为基础通过5类典型查询场景系统讲解两种查询语言的对应关系帮助读者建立从理论到实践的完整知识框架。1. 基础表结构与关系代数运算回顾1.1 表结构定义我们使用以下三个表作为练习基础学生表Student(S)S(Sno, Sname, Age, Sex, Dept) 主键SnoSno-学号Sname-姓名Age-年龄Sex-性别Dept-所在系课程表Course(C)C(Cno, Cname, Credit, Teacher) 主键CnoCno-课程号Cname-课程名Credit-学分Teacher-授课教师选课表SCSC(Sno, Cno, Grade) 主键(Sno, Cno) 外键Sno参照Student(Sno)Cno参照Course(Cno)Grade-成绩1.2 关系代数基本运算关系代数包含以下核心运算运算类型符号SQL对应功能描述选择σWHERE按条件筛选行投影πSELECT选择特定列连接⋈JOIN表间关联查询并∪UNION结果集合并差-EXCEPT结果集差集提示关系代数表达式默认去除重复元组而SQL需要使用DISTINCT显式去重2. 选择运算单表条件筛选2.1 基础选择查询例题1查询计算机系年龄小于20的学生信息关系代数σ(Dept计算机 ∧ Age20)(Student)SQL实现SELECT * FROM Student WHERE Dept 计算机 AND Age 20;2.2 复杂条件选择例题2查询选修了课程号为C001且成绩在80-90之间的学生学号关系代数π(Sno)(σ(CnoC001 ∧ 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 ⋈ σ(CnoC001)(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 ⟕ SCSQL实现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)(σ(CnoC001)(SC)) ⋈ Student)SQL实现使用NOT EXISTSSELECT 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 EXISTSSELECT 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 查询优化原则选择下推尽早执行选择运算减少中间结果投影下推尽早去除不需要的列连接顺序小表优先连接避免笛卡尔积确保连接条件正确注意实际优化还需考虑索引、统计信息等因素通过这5类查询的完整练习我们系统建立了SQL与关系代数的对应关系。在实际数据库开发中理解这种对应关系有助于准确分析复杂查询语义优化查询执行效率设计更合理的数据库模式