数据库实验作业及详解

本站仅限个人学习和分享笔记使用，转载请注明出处。

本次数据库实验围绕学生信息与课程选修等数据展开，旨在考察 SQL 语句的使用与数据查询逻辑。下面对每个实验任务及其对应的 SQL 代码进行详细介绍。

---

1. 查询所有学生的信息

任务描述

输出数据库中存储的全部学生记录，以获取学生表的完整信息。

SQL 代码

 

SELECT \* FROM students;

 

代码解释

SELECT * 表示选择表中的所有列，FROM students 指定从 students 表中进行查询。

---

2. 查询学号为 “20100306” 的学生姓名和学院

任务描述

从学生表中筛选出学号为 “20100306” 的学生，并获取其姓名和所属学院信息。

SQL 代码

 

SELECT name, college FROM students WHERE student\_id = ‘20100306’;

代码解释

SELECT name, college 选择 name 和 college 列，FROM students 指定查询表，WHERE student_id = '20100306' 用于筛选出学号为 “20100306” 的记录。

---

3. 查询每个学院的学生数

任务描述

统计每个学院的学生数量，了解各学院的学生分布情况。

SQL 代码

SELECT college, COUNT(\*) AS student\_count FROM students GROUP BY college;

代码解释

SELECT college, COUNT(*) 选择 college 列并统计记录数，AS student_count 为统计结果命名。FROM students 指定查询表，GROUP BY college 按学院对数据进行分组，确保每个学院的学生数被正确统计。

---

4. 查询 “计算机学院” 和 “软件学院” 的学生信息

任务描述

获取 “计算机学院” 和 “软件学院” 的学生详细信息，以便分析特定学院的学生情况。

SQL 代码

SELECT \* FROM students WHERE college IN (‘计算机学院’, ‘软件学院’);

代码解释

SELECT * 选择所有列，FROM students 指定查询表，WHERE college IN ('计算机学院', '软件学院') 筛选出学院为 “计算机学院” 或 “软件学院” 的记录。

---

5. 查询姓名中含有 “李” 的学生

任务描述

在学生表中查找姓名包含 “李” 字的学生，实现模糊查询功能。

SQL 代码

SELECT \* FROM students WHERE name LIKE ‘%李%’;

代码解释

SELECT * 选择所有列，FROM students 指定查询表，WHERE name LIKE '%李%' 使用 LIKE 操作符和通配符 %，匹配姓名中任意位置包含 “李” 字的记录。

---

6. 查询学生表中年龄是 18 和 20 的男生的信息

任务描述

筛选出年龄为 18 岁和 20 岁的男生记录，用于特定人群的数据提取。

SQL 代码

SQL 代码

SELECT \* FROM students WHERE age IN (18, 20) AND gender = ‘男’;

代码解释

SELECT * 选择所有列，FROM students 指定查询表，WHERE age IN (18, 20) AND gender = '男' 使用 IN 操作符筛选年龄为 18 或 20 的记录，并通过 AND 连接性别为 “男” 的条件。

---

7. 查询每一门课程间接先行课的信息（除了自连接，还有其他方法吗？）

任务描述

获取每一门课程的间接先行课程信息，探索除自连接外的实现方法，深入理解数据之间的层级关系。

SQL 代码

WITH RECURSIVE CoursePrecedence AS (  SELECT course\_id, preceding\_course\_id  FROM courses  WHERE preceding\_course\_id IS NOT NULL  UNION ALL  SELECT cp.course\_id, c.preceding\_course\_id  FROM CoursePrecedence cp  JOIN courses c ON cp.preceding\_course\_id = c.course\_id  WHERE c.preceding\_course\_id IS NOT NULL ) SELECT \* FROM CoursePrecedence;

代码解释

使用递归 CTE（Common Table Expression）实现。首先，初始查询选择有先行课程的记录。然后，通过递归部分不断匹配先行课程，获取间接先行课程信息。

---

8. 查询和 “王军” 在同一个学院学习的学生信息

任务描述

找出与 “王军” 在同一学院的其他学生信息，实现关联数据的查询。

SQL 代码

SELECT s.\* FROM students s JOIN (SELECT college FROM students WHERE name = ‘王军’) t ON s.college = t.college WHERE s.name != ‘王军’;

代码解释

子查询 SELECT college FROM students WHERE name = '王军' 获取 “王军” 的学院。主查询通过 JOIN 将学生表与子查询结果连接，筛选出同学院且姓名不为 “王军” 的学生记录。

---

9. 查询每个学生选修的课程数

任务描述

统计每个学生选修的课程数量，了解学生的选课情况。

SQL 代码

SELECT student\_id, COUNT(course\_id) AS course\_count FROM enrollments GROUP BY student\_id;

代码解释

SELECT student_id, COUNT(course_id) 选择学生 ID 并统计课程数量，AS course_count 为统计结果命名。FROM enrollments 指定查询表，GROUP BY student_id 按学生 ID 进行分组，确保每个学生的选课数被正确统计。

---

10. 查询学生的成绩，按照学号的升序，成绩的降序的方式显示

任务描述

以学号升序、成绩降序的顺序展示学生成绩，方便对成绩数据进行排序查看。

SQL 代码

SELECT student\_id, course\_id, grade FROM enrollments ORDER BY student\_id ASC, grade DESC;

代码解释

SELECT student_id, course_id, grade 选择相关列，FROM enrollments 指定查询表，ORDER BY student_id ASC, grade DESC 使用 ORDER BY 对结果进行排序，ASC 表示升序，DESC 表示降序。

---

11. 查询学生大于其选修课程平均成绩的选课信息

任务描述

找出学生成绩高于其选修课程平均成绩的选课记录，用于分析学生的相对成绩表现。

SQL 代码

 

SELECT e.\* FROM enrollments e JOIN ( SELECT student\_id, AVG(grade) AS avg\_grade FROM enrollments GROUP BY student\_id ) t ON e.student\_id = t.student\_id WHERE e.grade > t.avg\_grade;

 

代码解释

子查询 SELECT student_id, AVG(grade) AS avg_grade FROM enrollments GROUP BY student_id 计算每个学生的平均成绩。主查询通过 JOIN 将选课表与子查询结果连接，筛选出成绩高于平均成绩的记录。

---

12. 查询成绩在 80~89（包含 80 和 89）的学生的姓名，课程名称和成绩

任务描述

获取成绩在 80 到 89 分之间的学生的相关信息，用于特定分数段学生数据的分析。

SQL 代码

SELECT s.name, c.course\_name, e.grade FROM students s JOIN enrollments e ON s.student\_id = e.student\_id JOIN courses c ON e.course\_id = c.course\_id WHERE e.grade BETWEEN 80 AND 89;

代码解释

通过 JOIN 操作将学生表、选课表和课程表连接起来，获取学生姓名、课程名称和成绩。WHERE e.grade BETWEEN 80 AND 89 筛选出成绩在 80 到 89 分之间的记录。

---

13. 查询选修了 “0001” 课程的学生姓名

任务描述

找出选修了 “0001” 课程的学生姓名，用于特定课程选课学生的信息获取。

SQL 代码

SELECT s.name FROM students s JOIN enrollments e ON s.student\_id = e.student\_id WHERE e.course\_id = ‘0001’;

代码解释

通过 JOIN 将学生表和选课表连接，WHERE e.course_id = '0001' 筛选出选修 “0001” 课程的记录，获取对应的学生姓名。

---

14. 查询至少选修了 “20100101” 学生选修的全部课程的学生学号和姓名

任务描述

找出至少选修了 “20100101” 学生所选全部课程的学生信息，用于比较学生选课的覆盖情况。

SQL 代码

SELECT s.student\_id, s.name FROM students s JOIN enrollments e ON s.student\_id = e.student\_id GROUP BY s.student\_id, s.name HAVING COUNT(DISTINCT e.course\_id) >= ( SELECT COUNT(DISTINCT course\_id) FROM enrollments WHERE student\_id = ‘20100101’ ) AND NOT EXISTS ( SELECT course\_id FROM enrollments WHERE student\_id = ‘20100101’ AND course\_id NOT IN ( SELECT course\_id FROM enrollments WHERE student\_id = s.student\_id ; ) );

代码解释

子查询计算 “20100101” 学生选修的课程数，并检查是否存在该学生选修而其他学生未选修的课程。主查询通过 GROUP BY 和 HAVING 筛选出满足条件的学生。

---

15. 查询学号中倒数第三个字符是 “1” 的学生信息

任务描述

筛选出学号中倒数第三个字符为 “1” 的学生记录，实现特定格式学号的查询。

SQL 代码

SELECT \* FROM students WHERE student\_id LIKE ‘%\_1\_’;

代码解释

SELECT * 选择所有列，FROM students 指定查询表，WHERE student_id LIKE '%_1_' 使用 LIKE 操作符和通配符 % 和 _，匹配学号中倒数第三个字符为 “1” 的记录。

下载md格式原文

 

资源下载

免费资源

文件1地址点击下载

作者：信息安全23-1王智杰 客服QQ：3209697935