当前位置：首页 > news >正文

数据库子查询实验全解析

news 来源：原创 2025/8/17 20:32:16

- 一、验证性实验：夯实基础
- - （一）查询同班学生信息
  - （二）查询成绩相关信息
  - （三）查询课程选课人数
  - （四）相关子查询
  - （五）EXISTS嵌套子查询
  - （六）集合运算
  - （七）子查询作为表
  - （八）其他操作
- 二、设计性实验：进阶挑战
- - （一）查询各类平均成绩
  - （二）其他复杂查询
- 三、实验总结：经验与反思

本次实验的核心目标是熟练掌握子查询的嵌套查询、集合操作，同时了解EXISTS嵌套查询方法。

一、验证性实验：夯实基础

（一）查询同班学生信息

查询与“王丽娜”同班的学生学号和姓名，有多种实现方式。可以使用传统的连接查询，通过FROM STUDENT054 AS S1, STUDENT054 AS S2这种笛卡尔积的方式，再利用WHERE S1.CLASS=S2.CLASS AND S2.SNAME='王丽娜'筛选出符合条件的记录；也可以使用更简洁的JOIN语句，即FROM STUDENT054 AS S1 JOIN STUDENT054 AS S2 ON S1.CLASS=S2.CLASS WHERE S2.SNAME='王丽娜'，两种方法都能准确获取结果。

（二）查询成绩相关信息

非最高分与最高分成绩：查询每个学生的非最高分成绩时，通过WHERE DEGREE<(SELECT MAX(DEGREE) FROM SCORE054 AS SC2 WHERE SC2.SNO=SC1.SNO)语句，利用子查询找出每个学生的最高分，再筛选出低于最高分的成绩记录。而查询最高分成绩则将条件改为DEGREE =(SELECT MAX(DEGREE) FROM SCORE054 AS SC2 WHERE SC2.SNO=SC1.SNO) 。
各科最高分信息：查询各科的最高分的学号，姓名及课程名和成绩，需要多表联合查询。通过JOIN student054 S ON SC1.SNO=S.SNO和JOIN Course054 C ON C.Cno=SC1.Cno连接学生表和课程表，再利用WHERE DEGREE =(SELECT MAX(DEGREE) FROM SCORE054 AS SC2 WHERE SC2.CNO=SC1.CNO)筛选出各科最高分记录。

（三）查询课程选课人数

查询“操作系统”课程的选课人数，使用SELECT COUNT(*) FROM SCORE054 WHERE CNO IN (SELECT CNO FROM COURSE054 WHERE CNAME='操作系统') 语句，通过子查询找到“操作系统”课程的课程号，再统计选这门课的学生数量。

（四）相关子查询

相关子查询的特点是子查询中要用到父查询表的信息，不能独立执行。例如查询选修课程号为“3-105”课程且成绩至少高于选修课程号为“3-245”的同学的相关信息，通过SELECT CNO,SNO,DEGREE FROM SCORE054AS SC1 WHERE CNO='3-105'AND DEGREE >(SELECT DEGREE FROM SCORE054AS SC2 WHERE SC2.SNO=SC1.SNO AND SC2.CNO='3-245')实现，子查询根据父查询的学号获取对应学生“3-245”课程的成绩进行比较。

（五）EXISTS嵌套子查询

查询有/无成绩记录的学生信息：SELECT SNO,SNAME FROM STUDENT054 S WHERE EXISTS (SELECT * FROM SCORE054 WHERE SNO=S.SNO)用于查询所有有成绩记录的学生信息；而SELECT SNO,SNAME FROM STUDENT054 S WHERE not EXISTS (SELECT * FROM SCORE054 WHERE SNO=S.SNO)则查询所有没有成绩记录的学生信息。
特定条件下的学生信息查询：通过修改EXISTS子查询中的条件，如SELECT SNO,SNAME FROM STUDENT054 S WHERE EXISTS (SELECT * FROM SCORE054 WHERE SNO='108')，可以查询成绩表中存在指定学号（如108）成绩记录的学生信息。

（六）集合运算

UNION：Select tName,tSex from teacher054 union select sName,sSex from STUDENT054用于合并教师和学生的姓名与性别信息，并去除重复记录；select tName from teacher054 WHERE TNAME LIKE '李%' union select sName from STUDENT054 WHERE sNAME LIKE '李%'查询所有姓“李”的教师和学生姓名并去重；select tName from teacher054 WHERE TNAME LIKE '李%' union ALL select sName from STUDENT054 WHERE sNAME LIKE '李%'则保留所有姓“李”的教师和学生姓名，包括重复记录。
INTERSECT：select * from STUDENT054 WHERE sno<'108' intersect select * from STUDENT054 WHERE sno>'103'查询学号大于“103”且小于“108”的所有学生信息，取两个查询结果的交集。
EXCEPT：select * from STUDENT054 WHERE sno<'108' except select * from STUDENT054 WHERE sno>'103'查询学号小于等于“103”的所有学生信息，即第一个查询结果减去第二个查询结果。

（七）子查询作为表

Select s.*,av from STUDENT054 s join(select sno,avg(degree)av from SCORE054 group by sno)t on s.sno=t.sno将子查询(select sno,avg(degree)av from SCORE054 group by sno)作为一个临时表，与学生表STUDENT054进行连接，从而查询每个学生的所有信息及其平均成绩。

（八）其他操作

窗口函数ROW_NUMBER()：Select row_number()over(order by sno) as rowNum,* from SCORE054为每一行分配一个唯一的序号，按sno列排序；Select row_number()over(partition by sno order by sno)as rowNum,* from SCORE054先按sno分组，再在每个分组内按sno排序并编号，适用于分析每个学生的多条成绩记录。
CASE WHEN条件判断：select sno,cno,case when degree>=90 then 'A' WHEN DEGREE>=80 THEN 'B' WHEN DEGREE>=70 THEN 'C' WHEN DEGREE>=60 THEN 'D' ELSE 'E' END FROM SCORE054根据成绩划分等级；select sno,sname,case ssex when '男' then 'M' when '女' then 'F' END XB from STUDENT054将中文性别转换为单字母代码。
表操作：SELECT * into STU_054 FROM STUDENT054 WHERE SSEX='男'创建一个包含所有男生的新表，INSERT STU_054 SELECT * FROM STUDENT054 WHERE SSEX='女'再将女生信息插入该表，从而创建包含所有学生的新表。

二、设计性实验：进阶挑战

（一）查询各类平均成绩

个人成绩与总平均成绩：SELECT s.Sno, sc.Cno, sc.Degree,(SELECT AVG(Degree) FROM Score054) AS 总平均 FROM student054 s JOIN Score054 sc ON s.Sno = sc.Sno查询每人的成绩和所有成绩平均分。
个人成绩与本课程平均成绩：SELECT s.Sno, sc.Cno, sc.Degree,(SELECT AVG(Degree) FROM Score054 WHERE Cno = sc.Cno) AS 科平均 FROM student054 s JOIN Score054 sc ON s.Sno = sc.Sno查询每人的成绩和本课程平均分。
个人成绩与本班总平均成绩：SELECT s.Sno,s.Sname,c.Cname, sc.Degree,(SELECT AVG(Degree) FROM Score054 sc1 JOIN student054 s1 ON s1.Sno = sc1.Sno WHERE s.Class = s1.Class) AS 班平均 FROM student054 s JOIN Score054 sc ON s.Sno = sc.Sno JOIN Course054 c ON sc.Cno = c.Cno查询每人的成绩和本班总平均分。
个人成绩与本班本科平均成绩：SELECT s.Sno, s.Sname, c.Cname, sc.Degree,(SELECT AVG(Degree) FROM Score054 sc2 JOIN student054 s2 ON sc2.Sno = s2.Sno WHERE s2.Class = s.Class AND sc2.Cno = sc.Cno) AS 班科平均 FROM student054 s JOIN Score054 sc ON s.Sno = sc.Sno JOIN Course054 c ON sc.Cno = c.Cno查询每人的成绩和本班本科平均分。

（二）其他复杂查询

特定成绩比较查询：SELECT s.Sno, c.Cno, sc.Degree FROM student054 s JOIN Score054 sc ON s.Sno = sc.Sno JOIN Course054 c ON sc.Cno = c.Cno WHERE sc.Degree > (SELECT Degree FROM Score054 WHERE Sno = '101' AND Cno = '3-105')查询成绩高于学号为“101”的课程号为“3-105”的成绩的所有记录。
同月份出生学生查询：SELECT Sno, Sname, Sbirthday FROM student054 WHERE MONTH(Sbirthday) = (SELECT MONTH(Sbirthday) FROM student054 WHERE Sno = '101')查询和学号为101的同学同月出生的所有学生信息。
特定教师任课成绩查询：SELECT s.Sno, s.Sname, c.Cname, sc.Degree FROM student054 s JOIN Score054 sc ON st.Sno = sc.Sno JOIN Course054 c ON sc.Cno = c.Cno WHERE EXISTS (SELECT 1 FROM teacher054 t WHERE c.Tno = t.Tno AND t.Tname = '张旭')查询“张旭”教师任课的学生成绩。
每科最高分信息查询：SELECT st.Sno, st.Sname, c.Cname, sc.Degree FROM student054 st JOIN Score054 sc ON st.Sno = sc.Sno JOIN Course054 c ON sc.Cno = c.Cno WHERE sc.Degree IS NOT NULL AND NOT EXISTS (SELECT 1 FROM Score054 sc2 WHERE sc2.Cno = sc.Cno AND sc2.Degree IS NOT NULL AND sc2.Degree > sc.Degree)查询每科的最高分信息。
不及格学生信息查询：SELECT DISTINCT st.Sno, st.Sname FROM student054 st WHERE EXISTS (SELECT 1 FROM Score054 sc WHERE sc.Sno = st.Sno AND sc.Degree < 60)查询有成绩不及格的同学的学号和姓名。
多课程选修学生信息保存：SELECT st.Sno, st.Sname, c.Cname, sc.Degree INTO SCB054 FROM student054 st JOIN Score054 sc ON st.Sno = sc.Sno JOIN Course054 c ON sc.Cno = c.Cno WHERE EXISTS (SELECT 1 FROM (SELECT Sno, COUNT(*) AS cnt FROM Score054 GROUP BY Sno) t WHERE t.Sno = st.Sno AND t.cnt >= 2)查询选修两门及两门以上课程的学生学号及姓名、课程名、成绩，并保存到“SCBXXX”表中。
课程成绩排名查询（不使用排序函数）：SELECT s.Sno, s.Sname, sc.Degree,(SELECT COUNT(*) FROM Score054 sc2 JOIN Course054 c2 ON sc2.Cno = c2.Cno WHERE c2.Cname = '操作系统' AND sc2.Degree >= sc.Degree) AS Ranking FROM student054 s JOIN Score054 sc ON s.Sno = sc.Sno JOIN Course054 c ON sc.Cno = c.Cno WHERE c.Cname = '操作系统'查询所有学生的操作系统成绩及排名。
选修课程情况查询：通过EXISTS和NOT EXISTS分别查询有选修课程和没有选修任何课程的学生信息。
成绩及格与否学生信息查询：利用EXISTS和NOT EXISTS查询所有成绩都及格和成绩有不及格的学生的详细信息，包括学号、姓名、课程号、课程名、成绩和最低成绩。

三、实验总结：经验与反思

在实验过程中，我遇到了不少问题。比如在查询本班总平均分时，子查询关联条件缺失，导致结果错误，后来通过正确建立子查询与主查询的表关联解决了问题；计算排名时条件写错，修正后才得到正确结果。同时也出现了一些错误，像查询“张旭”教师任课成绩时遗漏关联条件，查询不及格学生时未去重导致数据重复。

通过这次实验，我在SQL关联与子查询方面的能力有了显著提升，对JOIN、IN、EXISTS等关键词的应用更加熟练。也深刻意识到数据处理细节的重要性，学会了处理重复数据等问题。在不断调试错误的过程中，逻辑思维和问题排查能力得到了锻炼，对SQL执行顺序的理解也更加深入。

目录