数据库系统概论（十二）SQL 基于派生表的查询 超详细讲解（附带例题表格对比带你一步步掌握）

---

前言

• 在前几期博客中，我们探讨了 SQL 连接查询，单表查询，嵌套查询，集合查询技术等知识点。
• 从本节开始，我们将深入讲解 SQL 中基于派生表的查询的知识点。

我的个人主页，欢迎来阅读我的其他文章
https://blog.csdn.net/2402_83322742?spm=1011.2415.3001.5343
我的数据库系统概论专栏
https://blog.csdn.net/2402_83322742/category_12911520.html?spm=1001.2014.3001.5482

---

一、什么是派生表？

一句话理解：

• 派生表是写在 FROM 子句中的子查询，它会先执行并生成一个临时表，供外层查询直接使用。
• 这个临时表只在当前查询中有效，查询结束后自动删除。

核心特点：

1. 必须起别名（用 AS 或直接跟别名），否则语法错误。
   例：(SELECT Sno FROM SC) AS SC1 或 (SELECT Sno FROM SC) SC1。
2. 用途：简化复杂查询，先计算中间结果（如分组统计），再与其他表连接或筛选。

二、派生表的使用示例

场景1：分组统计后过滤数据

需求：找出每个学生超过自己选修课程平均成绩的课程。
思路：

1. 先用子查询计算每个学生的平均成绩（派生表 Avg_sc）。
2. 将原表 SC 与派生表通过学号 Sno 连接，筛选成绩高于平均分的记录。

SELECT SC.Sno, SC.Cno, SC.Grade
FROM SC,
(SELECT Sno, AVG(Grade) AS avg_grade FROM SC GROUP BY Sno) AS Avg_sc
WHERE SC.Sno = Avg_sc.Sno AND SC.Grade >= Avg_sc.avg_grade;

场景2：替代临时表查询

需求：查询选修了“81001”号课程的学生姓名。
思路：

1. 先用子查询找出选了该课程的学号（派生表 SC1）。
2. 将学生表 Student 与派生表通过学号连接，获取姓名。

SELECT Student.Sname
FROM Student,
(SELECT Sno FROM SC WHERE Cno = '81001') AS SC1
WHERE Student.Sno = SC1.Sno;

三、SELECT语句核心结构

SELECT [列名/表达式] -- 要查询的结果
FROM 表1 [别名], 表2 [别名], ... -- 数据来源（可包含派生表）
WHERE 条件 -- 筛选行（如成绩>80）
GROUP BY 列名 -- 按某列分组（如按学生分组统计平均分）
HAVING 条件 -- 筛选分组结果（如只保留平均分>85的组）
ORDER BY 列名 [ASC/DESC] -- 排序（ASC升序，DESC降序）

关键说明：

• FROM 子句：可以是真实表或派生表（子查询），多个表用逗号分隔。
• WHERE 与 HAVING 区别：
  • WHERE：过滤原始数据行（分组前生效）。
  • HAVING：过滤分组后的结果（分组后生效，需配合 GROUP BY）。

四、常用聚集函数

函数	作用	示例
COUNT()	统计行数	COUNT(Sno)：统计学号数量
SUM()	求和	SUM(Grade)：总分
AVG()	求平均值	AVG(Grade)：平均分
MAX()	最大值	MAX(Grade)：最高成绩
MIN()	最小值	MIN(Grade)：最低成绩

用法注意：

• 常与 GROUP BY 配合使用（如按学生分组算平均分）。
• 示例：

  SELECT Sno, AVG(Grade) AS avg_grade 
  FROM SC 
  GROUP BY Sno; -- 按学号分组，计算每人平均分
  

五、WHERE子句

1. 比较运算符（θ运算）

• 符号：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、!=（不等于）。
• 示例：

  SELECT * FROM SC WHERE Grade >= 80; -- 成绩≥80分的记录
  

2. 区间匹配

• 作用：筛选在某个区间内的数据（包含边界）。
• 示例：

  SELECT * FROM SC 
  WHERE Grade BETWEEN 80 AND 90; -- 成绩在80-90分之间的记录
  

3. 集合匹配

• 作用：判断值是否在指定集合中。
• 示例：

  SELECT * FROM Student 
  WHERE Sno IN ('20180001', '20180002'); -- 学号为指定值的学生
  

4. 模糊查询

• 通配符：
  • %：匹配任意多个字符（如 '张%' 匹配姓张的人）。
  • _：匹配单个字符（如 '_立' 匹配第二个字是“立”的人）。
• 示例：

  SELECT * FROM Student 
  WHERE Sname LIKE '李%'; -- 姓名以“李”开头的学生
  

5. 空值判断

• 示例：

  SELECT * FROM SC WHERE Grade IS NULL; -- 成绩为空的记录
  

六、多表连接与派生表结合

需求：查询选修“81001”课程的学生学号、姓名和等级（等级由 mark 表定义）。
数据关系：

• SC 表存储成绩，Student 表存储学生信息，mark 表存储等级对应的分数区间。

SQL语句：

SELECT SC.Sno, Student.Sname, mark.score -- 选择学号、姓名、等级
FROM SC, Student, mark -- 连接三个表（SC、Student、mark）
WHERE SC.Sno = Student.Sno -- 学生表与SC表通过学号连接AND SC.Cno = '81001' -- 筛选课程号为81001AND SC.Grade BETWEEN mark.low AND mark.high -- 成绩匹配等级区间
ORDER BY mark.score DESC, SC.Sno ASC; -- 按等级降序、学号升序排序

执行逻辑：

1. 先通过 BETWEEN 匹配 SC 表成绩与 mark 表的等级区间，得到每个成绩对应的等级。
2. 再通过学号连接 Student 表，获取学生姓名。
3. 最后按要求排序结果。

七、总结

• 简化复杂查询：将中间计算结果（如分组统计）封装成临时表，避免重复书写子查询。
• 逻辑清晰：通过别名让代码更易读（如 AS Avg_sc 表示平均成绩表）。
• 注意事项：
  • 派生表必须有别名。
  • 连接时需确保列名匹配（如学号 Sno 必须在两表中存在）。

---

以上就是这篇博客的全部内容，下一篇我们将继续探索更多精彩内容。

我的个人主页，欢迎来阅读我的其他文章
https://blog.csdn.net/2402_83322742?spm=1011.2415.3001.5343
我的数据库系统概论专栏
https://blog.csdn.net/2402_83322742/category_12911520.html?spm=1001.2014.3001.5482

非常感谢您的阅读，喜欢的话记得三连哦