目录
- 一、验证性实验:夯实基础
- (一)查询同班学生信息
- (二)查询成绩相关信息
- (三)查询课程选课人数
- (四)相关子查询
- (五)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执行顺序的理解也更加深入。