《数据库实验》实验二：数据库定义与操作语言

​

前言：历时几天，总算把这个实验做出来了。此文章实验的题目、答案、解析3部分组成，包含实现这个实验的所有代码和问题的解析教程等。 说明：实验用的软件：SQL Server 2014版。

一、题目

导入ScoreDB数据库,按照“实验2数据”，修改四张表内各个字段的类型以及宽度。完成后，实现以下操作：

ScoreDB的数据库源码可以到公众号【三桥君】回复【数据库实验二】领取。

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

2.1数据查询

1. 查询选修了“数据结构”课程的同学姓名。
2. 查询选修了课程号为’001’或’002’课程的学生学号、课程号和分数。
3. 查询学过课程号为’001’但是没有学过课程号为’002’的课程的学生学号、课程号和分数。
4. 查询至少有一门课与学号为’0700001’的学生所学相同的学生的学号和姓名。
5. 查询至少选修了学号为“0700001”学生所选修的所有课程的学生姓名。
6. 查询本月过生日的学生信息。
7. 查询没有成绩的学生信息。（使用IN语句）
8. 查询（没有）选修过课程的学生姓名。（使用IN语句）
9. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。
10. 查找选修过课程名中包含“系统”的课程的同学学号、姓名和所在班级。
11. 查找同时选修过“高等数学”和“离散数学”两门课程的同学学号、姓名以及该同学所选修的所有课程的课程名和相应成绩，按学号(升序)、成绩(降序)排序输出。
12. 查询所有学生的选修信息，显示信息包括学号，选修门数，平均分和总分。
13. 查询先修课是“计算机基础”的课程。（分别用自连接和IN语句实现）
14. 查询至少有一门课程成绩超过85分的所有学生的信息，显示信息包括学号、课程号和分数。
15. 查询至少有两门课程成绩超过85分的所有学生的学号。
16. 查询平均成绩超过80分且选修3门及以上的所有学生的信息，显示信息包括学号、课程数目和分数。
17. 查询所选修课程的成绩大于所有“002”号课程成绩的同学学号及相应课程的课程号和成绩。
18. 查询选修了所有课程的学生姓名。

2.2数据操纵

1. 将所有选修001课程的同学的成绩增加5分。
2. 将李小勇同学选修课程的成绩增加30%。
3. 将每个班级的学生人数填入到班级表的ClassNum列中。
4. 在Student表中添加一条学生记录(学号：0800016,姓名：张三，籍贯：福建)。
5. 求出每门课程的选修人数，把结果存放在新表CountC中。(用SELECT INTO语句实现)
6. 求出每个学生的选修门数和平均分，把结果存放在新表CountSC中。(用INSERT SELECT语句实现)
7. 删除刘方晨同学所有的选修记录。
8. 删除选修了“操作系统”课程的选课记录。

2.3视图

1. 创建视图vw1，该视图包含福建省的女学生信息。
2. 创建视图vw2，该视图包含学生的学号、姓名、所选修的课程的课程号、课程名和成绩。
3. 创建视图vw3，该视图每门课程的课程号、课程名、选修人数和平均分。
4. 建立一个新的视图vw4，该视图包含学生姓名和年龄字段。将vw4中的年龄增加1岁。观察其运行结果并分析原因。
5. 删除视图vw1中学号为 ‘0800007’ 的数据。

二、答案

2.1数据查询

在这里插入图片描述

2.2数据操纵

在这里插入图片描述

2.3视图

在这里插入图片描述

以上相关的数据库源码可以到公众号【三桥君】回复【数据库实验二】领取。

三、解析

说明：只挑部分较难的题目来分析 相关理论知识参考：数据库系统概论（基础篇）-笔记

2.1数据查询

4.查询至少有一门课与学号为’0700001’的学生所学相同的学生的学号和姓名。

在这里插入图片描述

第一步：先在Score表中查询学号为“’0700001”选修的课程号；

第二步：再Score表中查询至少有一门课程号与第一步课程号相同的学生学号；

第三步：再Student表中查询与第二步学号相同的学生学号与姓名。

5.查询至少选修了学号为“0700001”学生所选修的所有课程的学生姓名。

暂时忘记怎么做了

6.查询本月过生日的学生信息。

数据库怎么查出本月过生日的同学

MONTH(birthday)=MONTH(GETDATE())

表示生日=当月的信息

MONTH(birthday)=MONTH(GETDATE())+1

表示生日=当前下一月的信息

9.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。

GROUP BY 需要与SELECT 的一致

在这里插入图片描述

平均成绩是原表中没有的属性，可以通过这个方式来创建。

在这里插入图片描述

17.查询所选修课程的成绩大于所有“002”号课程成绩的同学学号及相应课程的课程号和成绩。

如果成绩有空值的话，不能用ALL,要用MAX。

SELECT studentNo,courseNo,score
FROM Score
WHERE score >(SELECT MAX(score)
			FROM Score
			WHERE courseNo='002')

2.3视图

4.建立一个新的视图vw4，该视图包含学生姓名和年龄字段。将vw4中的年龄增加1岁。观察其运行结果并分析原因。

CREATE VIEW vw4(studentName,age)
AS
SELECT studentName,YEAR(GETDATE())-YEAR(birthday) age 
FROM Student

UPDATE vw4
SET age=age+1

因为所创建的视图对其属性值进行了计算的其他形式上的改变，

而对视图的更改最终表现为对表的更改而表中不存在视图的某一属性，或属性的性质不相同，

则无法更改，这是一种视图机制。

而且在我们数据库中存在的vw4的数据有两个，只不过一个是基本表，一个是视图。

视图里的数据是从该Student基本表中得出的数据，如果要修改视图，就转化为对Student表的修改，

但是在Student表中并没有age这列属性，所以无法更改。

解决办法是重新创建一个基本表vw5，结构内容都一样的，然后把vw4视图的数据添加到vw5基本表中，就可以修改年龄了。

CREATE TABLE vw5(
studentName VARCHAR(20) NOT NULL,
age VARCHAR(20)
)

INSERT INTO vw5
SELECT *
FROM vw4


UPDATE vw5
SET age=age+1

​