数据库子查询

数据库子查询

在数据库原理的学习中，子查询是实现复杂数据查询的核心技术之一。近期我完成了一次围绕子查询的专项实验，涵盖验证性实验与设计性实验，深度体验了子查询在多表关联、集合运算、条件筛选等场景的灵活应用。本文将结合实验内容，分享关键技术点与实战经验。

一、实验环境与核心目标

• 环境：Windows 11 + SQL Server 2022
• 目标：
  1. 掌握嵌套子查询、集合运算（UNION/INTERSECT/EXCEPT）、EXISTS 子查询的逻辑；
  2. 实现从简单数据筛选到复杂业务场景（如成绩分析、学生选课状态判断）的查询；
  3. 理解子查询与表连接的配合使用，优化查询效率。

二、验证性实验：子查询基础语法与集合运算

1. 嵌套子查询：数据逐层筛选

场景：查询与“王丽娜”同班的学生

方法 1：子查询获取班级后筛选

SELECT SNO, SNAME  
FROM STUDENT057  
WHERE CLASS = (SELECT CLASS FROM STUDENT057 WHERE SNAME = '王丽娜');

查询结果如图所示，方法2的查询结果也是这个

方法 2：表连接实现（笛卡尔积/JOIN）

-- 笛卡尔积连接  
SELECT S1.SNO, S1.SNAME  
FROM STUDENT057 S1, STUDENT057 S2  
WHERE S1.CLASS = S2.CLASS AND S2.SNAME = '王丽娜';  -- JOIN 连接  
SELECT S1.SNO, S1.SNAME  
FROM STUDENT057 S1 JOIN STUDENT057 S2  
ON S1.CLASS = S2.CLASS  
WHERE S2.SNAME = '王丽娜';

核心逻辑：通过子查询或表连接，将“同班”条件转化为具体的班级值或关联关系，实现数据过滤。

场景：显示学生非最高分/最高分成绩

-- 非最高分（子查询对比当前学生最高分）  
SELECT SNO, CNO, DEGREE  
FROM Score057 SC1  
WHERE DEGREE < (SELECT MAX(DEGREE) FROM Score057 SC2 WHERE SC2.SNO = SC1.SNO);  

-- 最高分（等于当前学生最高分）  
SELECT SNO, CNO, DEGREE  
FROM Score057 SC1  
WHERE DEGREE = (SELECT MAX(DEGREE) FROM Score057 SC2 WHERE SC2.SNO = SC1.SNO);

关键点：相关子查询（依赖主查询的学生学号），实现按学生分组后的条件筛选。

2. 集合运算：数据合并与交差

UNION 合并教师与学生信息

-- 合并姓名与性别（去重）  
SELECT TNAME, TSEX FROM Teacher057  
UNION  
SELECT SNAME, SSEX FROM STUDENT057;  

-- 保留重复行（UNION ALL）  
SELECT TNAME FROM Teacher057 WHERE TNAME LIKE '李%'  
UNION ALL  
SELECT SNAME FROM STUDENT057 WHERE SNAME LIKE '李%';

INTERSECT 与 EXCEPT 实现集合交差

-- 学号在 103~108 之间的学生（交集）  
SELECT * FROM STUDENT057 WHERE SNO < '108'  
INTERSECT  
SELECT * FROM STUDENT057 WHERE SNO > '103';  

-- 学号 <108 但不满足 >103 的学生（差集）  
SELECT * FROM STUDENT057 WHERE SNO < '108'  
EXCEPT  
SELECT * FROM STUDENT057 WHERE SNO > '103';

应用场景：UNION 用于合并同类数据，INTERSECT/EXCEPT 用于快速筛选数据子集。

3. EXISTS 子查询：存在性判断

场景：查询有/无选课记录的学生

-- 有选课记录  
SELECT SNO, SNAME  
FROM STUDENT057 S  
WHERE EXISTS (SELECT * FROM Score057 WHERE SNO = S.SNO);  

-- 无选课记录  
SELECT SNO, SNAME  
FROM STUDENT057 S  
WHERE NOT EXISTS (SELECT * FROM Score057 WHERE SNO = S.SNO);

核心：EXISTS 子查询返回逻辑值（TRUE/FALSE），高效判断子表中是否存在关联记录。

三、设计性实验：复杂业务场景的子查询应用

1. 成绩分析：平均分与排名计算

场景 5：成绩高于特定学生特定课程的记录

-- 查询成绩 > 学号 101 的课程 3-105 成绩  
SELECT SNO, CNO, DEGREE  
FROM Score057  
WHERE DEGREE > (SELECT DEGREE FROM Score057 WHERE SNO = '101' AND CNO = '3-105');

场景 11：操作系统成绩排名（不使用排序函数）

SELECT s.SNO, s.SNAME, sc.DEGREE,  (SELECT COUNT(*) + 1 FROM Score057 sc2  JOIN Course057 c2 ON sc2.CNO = c2.CNO  WHERE c2.CNAME = '操作系统' AND sc2.DEGREE > sc.DEGREE) AS 排名  
FROM student057 s  
JOIN Score057 sc ON s.SNO = sc.SNO  
JOIN Course057 c ON sc.CNO = c.CNO  
WHERE c.CNAME = '操作系统';

技巧：通过子查询统计比当前成绩高的记录数，COUNT(*) + 1 即为排名，避免使用窗口函数。

2. 学生状态筛选：选课与成绩判断

场景 9：有成绩不及格的学生

-- IN 子查询快速定位  
SELECT SNO, SNAME  
FROM STUDENT057  
WHERE SNO IN (SELECT SNO FROM Score057 WHERE DEGREE < 60);

没有不及格的

场景 12：无选课记录的学生

-- NOT EXISTS 高效判断  
SELECT SNO, SNAME  
FROM STUDENT057 S  
WHERE NOT EXISTS (SELECT * FROM Score057 WHERE SNO = S.SNO);

3. 多表关联与分组统计

场景 10：选修两门以上课程的学生信息保存

--将选中的数据保存到另一个表中 
SELECT S.SNO,C.CNAME,SC.DEGREE INTO SCB057
FROM student057 S 
JOIN Score057 SC ON S.SNO = SC.Sno
JOIN Course057 C ON C.Cno = SC.Cno
WHERE S.SNO IN (SELECT SNO FROM Score057 GROUP BY SNO HAVING COUNT(CNO )>2)

关键点：子查询通过 GROUP BY + HAVING 筛选多课程学生，主查询实现三表连接获取完整信息。

四、实验中的常见问题与解决

1. 多列 IN 子查询语法错误（SQL Server 不支持）

   • 问题：(CNO, DEGREE) IN (SELECT CNO, MAX(DEGREE) FROM Score057 GROUP BY CNO) 报错。
   • 解决：改用表连接，将子查询结果作为中间表关联：

     SELECT ...  
     JOIN (SELECT CNO, MAX(DEGREE) AS MaxDegree FROM Score057 GROUP BY CNO) AS max_sc  
     ON sc.CNO = max_sc.CNO AND sc.DEGREE = max_sc.MaxDegree;
     

2. 并列最高分漏选问题

   • 原因：直接使用 DEGREE = MAX(DEGREE) 仅返回单条记录。
   • 解决：通过关联子查询分组后的最高分，确保所有并列记录被筛选：

     JOIN (SELECT CNO, MAX(DEGREE) AS MaxDegree FROM Score057 GROUP BY CNO) AS max_sc  
     ON sc.CNO = max_sc.CNO AND sc.DEGREE = max_sc.MaxDegree;
     

五、实验收获：子查询的核心价值与实践建议

1. 子查询分类与适用场景

   • 相关子查询：依赖主查询字段（如 WHERE EXISTS (SELECT * FROM A WHERE A.SNO = B.SNO)），适合逐行匹配条件。
   • 不相关子查询：可独立执行（如 WHERE CNO IN (SELECT CNO FROM Course057)），适合预计算固定值。
   • 集合运算：UNION 用于结果合并，INTERSECT/EXCEPT 用于快速筛选数据交集与差集。

2. 性能优化建议

   • 避免在子查询中使用 SELECT *，仅获取必要字段以提升效率。
   • 复杂逻辑优先使用 JOIN 替代多层嵌套子查询，增强可读性与性能。

3. 业务场景迁移

   • 学生管理：通过子查询快速定位特定班级、成绩区间的学生；
   • 成绩分析：结合 AVG、MAX 等聚合函数，实现个性化数据统计；
   • 选课系统：利用 EXISTS 判断选课状态，优化用户界面显示逻辑。