关系代数 8 大核心运算从并/差/交到连接/除法的 5 个实战 SQL 案例在数据库系统的学习过程中关系代数作为理论基础常常让学生感到抽象难懂。本文将通过经典的学生-课程-选课数据库模型用5个可直接运行的SQL案例展示如何将关系代数中的8种核心运算转化为实际查询语句。这些案例不仅能帮助理解《数据库系统概论》中的关键概念更能解决学了理论却不知如何编码的痛点。1. 传统集合运算的SQL实现关系代数中的传统集合运算包括并、差、交和笛卡尔积它们对应SQL中的UNION、EXCEPT/MINUS、INTERSECT和CROSS JOIN操作。让我们通过学生选课系统的实例来理解这些运算。假设我们有两个表计算机系学生表CS_Students(StudentID, Name)数学系学生表Math_Students(StudentID, Name)1.1 并集运算UNION找出所有在计算机系或数学系就读的学生去除重复SELECT StudentID, Name FROM CS_Students UNION SELECT StudentID, Name FROM Math_Students;注意UNION会自动去除重复行。如果需要保留所有行包括重复的应使用UNION ALL。1.2 差集运算EXCEPT找出在计算机系但不在数学系的学生SELECT StudentID, Name FROM CS_Students EXCEPT SELECT StudentID, Name FROM Math_Students;注在MySQL中需使用LEFT JOIN配合IS NULL条件实现相同功能1.3 交集运算INTERSECT找出同时在计算机系和数学系就读的学生SELECT StudentID, Name FROM CS_Students INTERSECT SELECT StudentID, Name FROM Math_Students;MySQL替代方案使用INNER JOIN或EXISTS子查询1.4 笛卡尔积CROSS JOIN生成计算机系学生与数学系学生的所有可能组合SELECT cs.StudentID AS CS_ID, cs.Name AS CS_Name, ms.StudentID AS Math_ID, ms.Name AS Math_Name FROM CS_Students cs CROSS JOIN Math_Students ms;传统集合运算的SQL对应表关系代数运算SQL操作符保留重复行备注并(∪)UNION否需两表列数相同差(-)EXCEPT-MySQL需用LEFT JOIN替代交(∩)INTERSECT-MySQL需用INNER JOIN替代笛卡尔积(×)CROSS JOIN是结果行数为两表行数乘积2. 专门关系运算选择与投影专门的关系运算包括选择、投影、连接和除法它们是关系数据库查询的核心。我们先看最基本的选择和投影运算。2.1 选择运算σ选择运算是从关系中选取满足条件的元组对应SQL中的WHERE子句。案例从学生表中选择年龄大于20岁的学生-- 关系代数σ_age20(Students) SELECT * FROM Students WHERE age 20;2.2 投影运算π投影运算是从关系中选择特定的属性列对应SQL中的SELECT子句指定列。案例查询所有学生的姓名和邮箱-- 关系代数π_name,email(Students) SELECT name, email FROM Students;选择与投影的组合使用-- 关系代数π_name,email(σ_age20(Students)) SELECT name, email FROM Students WHERE age 20;提示在实际查询中选择运算应尽可能先执行这样可以减少后续处理的数据量提高查询效率。3. 连接运算的多种形式连接运算是关系代数中最重要且最复杂的运算之一SQL提供了多种连接方式来实现不同类型的连接操作。3.1 自然连接⋈自然连接自动基于相同名称的属性进行等值连接并去除重复列。案例学生选课信息与学生基本信息的自然连接-- 关系代数Students ⋈ SC SELECT s.*, sc.CourseID, sc.Score FROM Students s NATURAL JOIN SC;注实际SQL中更常用INNER JOIN显式指定连接条件3.2 θ-连接带条件的连接θ-连接允许指定任意的连接条件不限于等值连接。案例查找选了比自己平均分高的课程的学生SELECT s1.StudentID, s1.CourseID, s1.Score FROM SC s1 JOIN SC s2 ON s1.StudentID s2.StudentID AND s1.Score ( SELECT AVG(Score) FROM SC s3 WHERE s3.StudentID s1.StudentID );3.3 外连接运算外连接保留了不满足连接条件的元组用NULL填充缺失值。左外连接案例查询所有学生及其选课信息包括未选课的学生-- 关系代数Students ⟕ SC SELECT s.StudentID, s.Name, sc.CourseID, sc.Score FROM Students s LEFT JOIN SC ON s.StudentID sc.StudentID;连接类型对比表连接类型SQL语法保留左表未匹配行保留右表未匹配行备注内连接INNER JOIN否否默认连接类型左外连接LEFT JOIN是否保留左表全部右外连接RIGHT JOIN否是保留右表全部全外连接FULL JOIN是是MySQL不支持交叉连接CROSS JOIN--笛卡尔积4. 除法运算的实现技巧除法运算是关系代数中最难理解的运算之一它用于查询满足所有条件的情况。SQL没有直接的除法运算符需要通过组合其他操作实现。4.1 除法运算的概念给定关系R(X,Y)和S(Y)R÷S的结果是X上满足对于S中所有的Y值在R中都存在XY组合的元组。案例找出选修了所有数学系课程的学生。4.2 SQL实现方案-- 1. 找出数学系的所有课程 WITH MathCourses AS ( SELECT CourseID FROM Courses WHERE Department Math ) -- 2. 找出选修了所有数学系课程的学生 SELECT s.StudentID, s.Name FROM Students s WHERE NOT EXISTS ( SELECT CourseID FROM MathCourses WHERE NOT EXISTS ( SELECT * FROM SC WHERE SC.StudentID s.StudentID AND SC.CourseID MathCourses.CourseID ) );替代方案使用GROUP BY和HAVINGSELECT sc.StudentID, s.Name FROM SC sc JOIN Students s ON sc.StudentID s.StudentID WHERE sc.CourseID IN ( SELECT CourseID FROM Courses WHERE Department Math ) GROUP BY sc.StudentID, s.Name HAVING COUNT(DISTINCT sc.CourseID) ( SELECT COUNT(*) FROM Courses WHERE Department Math );注意第二种方法仅在确定数学系课程没有重复时有效且要求Courses表中每个数学系课程确实有学生选修。5. 综合案例复杂查询的关系代数与SQL对照让我们通过一个综合案例展示如何将复杂的关系代数表达式转化为SQL查询。业务需求找出选修了数据库系统课程且成绩高于该课程平均分的学生姓名和成绩。5.1 关系代数表达式π_Student.Name,SC.Score( σ_Course.Cname数据库系统 ∧ SC.Score AvgScore( SC ⋈ (σ_Course.Cname数据库系统(Course)) ⋈ Student ) × ρ_AvgScore(γ_AVG(SC.Score)(σ_Course.Cname数据库系统(SC ⋈ Course))) )5.2 SQL实现-- 计算数据库系统课程的平均分 WITH DB_Avg AS ( SELECT AVG(sc.Score) AS avg_score FROM SC sc JOIN Courses c ON sc.CourseID c.CourseID WHERE c.Cname 数据库系统 ) -- 查询结果 SELECT s.Name, sc.Score FROM Students s JOIN SC sc ON s.StudentID sc.StudentID JOIN Courses c ON sc.CourseID c.CourseID CROSS JOIN DB_Avg WHERE c.Cname 数据库系统 AND sc.Score DB_Avg.avg_score;5.3 关键步骤解析首先计算数据库系统课程的平均分存储在临时结果DB_Avg中然后连接学生表、选课表和课程表通过CROSS JOIN将平均分结果与每一行关联最后筛选课程名为数据库系统且成绩高于平均分的学生记录性能优化提示对于大型数据库可以在Course.Cname和SC.CourseID上建立索引并考虑将CROSS JOIN改为子查询SELECT s.Name, sc.Score FROM Students s JOIN SC sc ON s.StudentID sc.StudentID JOIN Courses c ON sc.CourseID c.CourseID WHERE c.Cname 数据库系统 AND sc.Score ( SELECT AVG(sc2.Score) FROM SC sc2 JOIN Courses c2 ON sc2.CourseID c2.CourseID WHERE c2.Cname 数据库系统 );