实验课速通SQLServer期末考点三：数据查询

一、实验内容

SQL Server 2017 环境下教学信息管理系统核心表的基础查询与嵌套查询（DQL）实现与验证。

二、实验目的

1. 掌握 SQL Server 中 DQL 核心语句的基础用法：SELECT字段指定、WHERE条件筛选、ORDER BY排序、TOP限制结果集、DISTINCT去重；

2. 理解并掌握聚合函数（COUNT、SUM、AVG、MAX、MIN）的使用，能结合GROUP BY进行分组统计；

3. 学会使用嵌套查询（子查询）解决复杂业务需求，包括 “子查询作为条件”“子查询作为结果集”，明确子查询的执行顺序；

4. 结合教学信息管理系统场景，仅通过单表查询与嵌套查询（不含表连接）获取目标数据，确保查询逻辑正确且结果精准。

三、实验任务

任务 1：基础查询（单表，含筛选、排序、去重）

1. 查询Student表中 “计算机科学与技术学院” 所有学生的学号、姓名、性别，按年龄降序排序；

2. 查询Teacher表中职称不为 “讲师” 的教师姓名、职称、所在院系，仅显示前 3 条结果；

3. 查询Course表中所有课程的学分，要求去除重复学分，按学分升序排序；

4. 查询SC表中学号为 “2023010101” 的学生所有选课记录的课程编号、成绩，按成绩降序排序，且仅显示成绩大于 80 分的记录。

任务 2：聚合函数查询（单表统计）

1. 统计Student表中各院系的学生人数，显示院系名称与对应人数（使用COUNT与GROUP BY）；

2. 计算SC表中 “CS101”（数据库原理）课程的平均成绩、最高成绩、最低成绩（使用AVG、MAX、MIN）；

3. 统计Teacher表中各职称的教师数量，按教师数量降序排序（使用COUNT与GROUP BY）；

4. 计算Course表中所有课程的总学分（使用SUM），并显示 “总学分” 别名。

任务 3：嵌套查询（子查询作为条件 / 结果集）

1. 子查询作为条件（IN/NOT IN）：查询选修了 “数据库原理”（通过子查询获取Cno）的学生学号、成绩（基于SC表）；

2. 子查询作为条件（EXISTS/NOT EXISTS）：查询存在 “成绩大于 90 分” 选课记录的学生姓名、所在院系（基于Student与SC表，子查询判断存在性）；

3. 子查询作为结果集：查询Teacher表中 “计算机科学与技术学院” 的教师信息，要求显示教师编号、姓名、职称，且教师编号需在 “授课学分大于 3 分的教师编号” 列表中（子查询从Course表获取符合条件的Tno）；

4. 多层嵌套查询：查询 “数学与统计学院” 学生中，所选课程平均成绩大于 80 分的学生学号、姓名（先通过子查询统计SC表中平均成绩 > 80 的Sno，再关联Student表筛选院系）。

任务 4：查询结果验证

1. 对比基础查询结果与表中原始数据，确保筛选、排序逻辑正确；

2. 手动计算聚合函数结果（如各院系学生人数），验证COUNT/SUM等函数的计算准确性；

3. 拆解嵌套查询的执行顺序（先执行子查询，再执行主查询），验证最终结果是否符合业务需求。

四、实验步骤与参考代码

步骤 1：基础查询实现

-- 切换到TeachingSysDB数据库
USE TeachingSysDB;
GO
-- 1. 任务1-1：查询计算机学院学生，按年龄降序
SELECT Sno AS '学号', Sname AS '姓名', Ssex AS '性别'
FROM Student
WHERE Sdept = '计算机科学与技术学院'
ORDER BY Sage DESC;
GO
-- 1. 任务1-2：查询非讲师教师，显示前3条
SELECT TOP 3 Tname AS '教师姓名', Ttitle AS '职称', Tdept AS '所在院系'
FROM Teacher
WHERE Ttitle != '讲师';  -- 等价于 Ttitle NOT IN ('讲师')
GO
-- 1. 任务1-3：查询课程学分，去重并升序
SELECT DISTINCT Ccredit AS '课程学分'
FROM Course
ORDER BY Ccredit ASC;
GO
-- 1. 任务1-4：查询学生2023010101的选课记录（成绩>80，按成绩降序）
SELECT Cno AS '课程编号', Grade AS '成绩'
FROM SC
WHERE Sno = '2023010101' AND Grade > 80
ORDER BY Grade DESC;
GO 

步骤 2：聚合函数查询实现

-- 2. 任务2-1：统计各院系学生人数
SELECT Sdept AS '院系名称', COUNT(Sno) AS '学生人数'
FROM Student
GROUP BY Sdept;  -- 按院系分组
GO
-- 2. 任务2-2：计算CS101课程的成绩统计
SELECT
   'CS101（数据库原理）' AS '课程标识',
   AVG(Grade) AS '平均成绩',
   MAX(Grade) AS '最高成绩',
   MIN(Grade) AS '最低成绩'
FROM SC
WHERE Cno = 'CS101';  -- 筛选目标课程
GO
-- 2. 任务2-3：统计各职称教师数量，按数量降序
SELECT Ttitle AS '职称', COUNT(Tno) AS '教师数量'
FROM Teacher
GROUP BY Ttitle
ORDER BY 教师数量 DESC;  -- 按别名排序，等价于 ORDER BY COUNT(Tno) DESC
GO
-- 2. 任务2-4：计算所有课程总学分
SELECT SUM(Ccredit) AS '所有课程总学分'
FROM Course;
GO

步骤 3：嵌套查询实现

-- 3. 任务3-1：子查询作为条件（IN）：查询选修“数据库原理”的学生学号、成绩
-- 子查询：获取“数据库原理”的课程编号Cno
SELECT Sno AS '学生学号', Grade AS '成绩'
FROM SC
WHERE Cno IN (SELECT Cno FROM Course WHERE Cname = '数据库原理');
GO
-- 3. 任务3-2：子查询作为条件（EXISTS）：查询存在90分以上成绩的学生姓名、院系
SELECT Sname AS '学生姓名', Sdept AS '所在院系'
FROM Student s
WHERE EXISTS (
   -- 子查询：判断该学生是否有成绩>90的选课记录
   SELECT 1
   FROM SC
   WHERE SC.Sno = s.Sno AND Grade > 90
);
GO
-- 3. 任务3-3：子查询作为结果集：查询计算机学院且授课学分>3的教师
SELECT Tno AS '教师编号', Tname AS '教师姓名', Ttitle AS '职称'
FROM Teacher
WHERE
   Tdept = '计算机科学与技术学院'
   -- 子查询：获取授课学分>3分的教师编号
   AND Tno IN (SELECT DISTINCT Tno FROM Course WHERE Ccredit > 3);
GO
-- 3. 任务3-4：多层嵌套：查询数学学院平均成绩>80的学生学号、姓名
SELECT s.Sno AS '学生学号', s.Sname AS '学生姓名'
FROM Student s
WHERE
   s.Sdept = '数学与统计学院'
   -- 第一层子查询：获取平均成绩>80的学生学号
   AND s.Sno IN (
       -- 第二层子查询：统计每个学生的平均成绩
       SELECT Sno
       FROM SC
       GROUP BY Sno
       HAVING AVG(Grade) > 80
   );
GO

步骤 4：查询结果验证

-- 验证任务3-1：先查询“数据库原理”的Cno，再对比SC表结果
SELECT Cno FROM Course WHERE Cname = '数据库原理';  -- 确认Cno为CS101
SELECT Sno, Grade FROM SC WHERE Cno = 'CS101';  -- 与任务3-1结果对比，应一致
GO
-- 验证任务2-1：手动统计计算机学院学生人数，与查询结果对比
SELECT COUNT(*) FROM Student WHERE Sdept = '计算机科学与技术学院';  -- 单独统计，验证分组结果
GO
-- 验证任务3-4：先统计每个学生的平均成绩，再筛选数学学院学生
SELECT Sno, AVG(Grade) AS '平均成绩' FROM SC GROUP BY Sno HAVING AVG(Grade) > 80;  -- 第一层子结果
SELECT Sno, Sname FROM Student WHERE Sdept = '数学与统计学院' AND Sno IN ('CS101');  -- 与任务3-4结果对比
GO

五、实验结果与分析

1. 基础查询结果：
   ◦ 任务 1-1 查询出计算机学院学生（如张伟、李娜），按年龄降序后 older 学生排在前；
   ◦ 任务 1-3 通过DISTINCT去除了Course表中的重复学分（如 3 分、4 分各出现 1 次，无重复）；
   ◦ 任务 1-4 精准筛选出学生 “2023010101” 成绩 > 80 的选课记录（如 CS101-88 分、CS102-85 分），排序逻辑正确。

2. 聚合函数查询结果：
   ◦ 任务 2-1 统计出各院系人数（如计算机学院 4 人、数学学院 4 人，与实验二插入数据一致）；
   ◦ 任务 2-2 计算出 CS101 课程的平均成绩（如 (88+92+83)/3=87.67）、最高成绩 92 分、最低成绩 83 分，手动计算验证无误；
   ◦ 任务 2-4 通过SUM得出所有课程总学分（如 3+4+5+3+4+4=23 分），与课程表学分总和一致。

3. 嵌套查询结果：
   ◦ 任务 3-1 通过子查询获取 “数据库原理” 的Cno（CS101），最终查询出选修该课程的学生学号及成绩（如 2023010101-88 分），与SC表数据匹配；
   ◦ 任务 3-2 通过EXISTS子查询，筛选出有 90 分以上成绩的学生（如李娜、刘芳），无遗漏或误判；
   ◦ 任务 3-4 通过两层嵌套，先统计平均成绩 > 80 的学生，再筛选数学学院学生，最终结果（如刘芳）符合业务需求，无逻辑错误。

六、实验总结

1. 本次实验掌握了 SQL Server DQL 的核心用法：基础查询通过WHERE筛选、ORDER BY排序实现数据精准提取；聚合函数结合GROUP BY能高效完成统计需求；嵌套查询通过 “子查询优先执行” 的逻辑，解决了复杂业务场景下的条件关联问题；

2. 理解了关键语法的注意事项：DISTINCT作用于所有查询字段（非单个字段），GROUP BY需包含非聚合函数的查询字段，EXISTS子查询仅判断存在性（无需返回具体数据，用SELECT提升效率）；

3. 遇到的问题：
   ◦ 聚合查询时忘记加GROUP BY，导致 “统计各院系人数” 报错，通过明确 “分组字段与查询字段一致” 解决；
   ◦ 嵌套查询中主查询与子查询的Sno未关联（如任务 3-2），导致筛选出所有学生，通过添加SC.Sno = s.Sno的关联条件解决；
   ◦ 多层嵌套时逻辑混乱，通过 “先执行内层子查询，再逐步向外验证” 的方式拆解问题；

4. 后续应用：本次实验的基础查询与嵌套查询是表连接查询的基础，后续可结合表连接进一步优化复杂数据关联场景，但单表查询与嵌套查询在简单业务统计中更简洁高效，需根据场景灵活选择。