数据库实验四（SQL 数据库更新操作与完整性约束实践）

server/2024/12/17 21:53:19/

一、实验目的

本次实验主要目的包括：

  1. 熟练掌握使用 SQL 语句实现更新操作的方法，能够对数据库中的数据进行准确修改。
  2. 深刻认识完整性约束对数据库的重要性，理解其在维护数据准确性、一致性和可靠性方面的关键作用。
  3. 精通在 MySQL 中对完整性约束创建和修改的方法，以便根据实际业务需求灵活设置数据库约束条件。

二、各类数据库操作 SQL 语句及分析

（一）插入学院信息

  1. 问题描述：在学院表中增加一个新的学院，外国语学院，学院编号为 15，院长暂时不设置。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（2 分）
INSERT INTO Department(Dno,Dname)
VALUES ('15','外国语学院');

（二）更新学生课程成绩

  1. 问题描述：学号为 2014112103 的学生的数据结构课程的补考成绩为 63 分，请用补考成绩更新原有的考试成绩。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（2 分）
UPDATE Reports
SET Grade=63
WHERE Sno='2014112103'
AND Cno=
(SELECT Cno FROM Courses
WHERE Cname='数据结构'
);

（三）为表添加列并设置属性

  1. 问题描述：为表 Tutors 增加新列 Tutid，将该列设置为第一列，并将 Tutid 列设置为自动增长的整数列，同时设置该列为主键。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（2 分）
ALTER TABLE tutors
ADD Tutid INT AUTO_INCREMENT PRIMARY KEY FIRST;

（四）多表更新插入数据

  1. 问题描述：2018 学年第 1 学期，为 20171151 班的所有学生开设了计算机网络课程，并由 “许永军” 教授进行授课。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（4 分）
-- ① 先插入授课信息
INSERT INTO Tutors
(Tno,Cno,Tacademicyear,Tterm,Sclass)
VALUES
((SELECT Tno FROM Teachers WHERE Tname='许永军'),
(SELECT Cno FROM Courses WHERE Cname='计算机网络'),
2018,1,'20171151');
-- ② 再插入选课信息
INSERT INTO Reports
(Sno,Cno,Racademicyear,Rterm)
(SELECT Sno,Cno,2018,1FROM Students, CoursesWHERE Sclass='20171151' AND Cname='计算机网络');

（五）更新授课教师信息

  1. 问题描述：李桂清教授光荣退休了，需要将他在 2018 学年第 2 学期给 20161121 班的学生所上的数字图像制作课程将改由李力授课。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（3 分）
UPDATE Tutors
SET Tno=(SELECT Tno FROM Teachers WHERE Tname='李力')
WHERE Tno=(SELECT Tno FROM Teachers WHERE Tname='李桂清')
AND Cno=(SELECT Cno FROM Courses WHERE Cname='数字图像制作')
AND Sclass ='20161121' AND Tacademicyear=2018 AND Tterm=2;

（六）删除教师信息及相关问题

  1. 问题描述：李桂清教授光荣退休了，需要将他的信息从教师表中删除，并回答相关问题。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（2 分）
DELETE FROM Teachers
WHERE Tname='李桂清';

  1. 回答问题：
    • ① 删除失败
    • ② 因为授课表中有李桂清教授的授课信息，违反参照完整性约束

（七）解决教师退休信息处理问题

  1. 问题描述：李桂清教授虽然退休了，但是很明显不能直接将他从教师信息表中删除，因为在授课表中还有他往年的授课信息，那么这个问题该如何解决呢？既能分辨教师是否退休，又不会影响到他之前的授课信息。
  2. 我的答案 SQL 语句：无
  3. 正确答案：
    • 参考问题：（3 分）（注意，此处仅为参考答案，可根据学生答题分析可行性予以评分）
    • 可以在教师表中增加一个新的属性列，标识是否在岗 TInWork，数据类型设置为 VARCHAR (4)，默认可设置为 “在岗”，若教师退休，可将该属性列的值修改为 “退休”。还可以标识为其他状态，如 “进修”、“读博”、“病假” 等。

（八）删除学生信息及相关问题

  1. 问题描述：学号为 2017120202 的学生因病退学了，请从学生表中将他的信息删除，并回答相关问题。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（2 分）
DELETE FROM Students
WHERE Sno='2017120202';

  1. 回答问题：
    • ① 删除失败
    • ② 因为选课表中有该位学生的选课信息，违反参照完整性（外键）约束

（九）设置级联删除后删除学生信息

  1. 问题描述：将选课表中对 Sno 的外键声明重新设置可为级联删除；再从学生表中将学号 2017120202 的学生信息删除，并回答相关问题。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（6 分）
① 重新设置外键
ALTER TABLE reportsDROP CONSTRAINT FK_Sno;
ALTER TABLE reports
ADD CONSTRAINT FK_Sno FOREIGN KEY(Sno)
REFERENCES students(Sno)
ON DELETE CASCADE;
② 删除学生信息
DELETE FROM Students
WHERE Sno='2017120202';

  1. 回答问题：
    • ① 删除成功
    • ② 因为设置了级联删除，所以当删除学生表中的学生信息时，系统会自动将选课表中该位学生的选课信息一起删除

（十）更新专业编号及相关问题

  1. 问题描述：由于人才培养方案的变更，数学与统计学院的数据科学与大数据技术（非师范）专业的编号将变更为编号 1203，请直接将专业表中的专业编号 1202 更改为 1203 的信息，并回答相关问题。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（2 分）
UPDATE major
SET Mno='1203'
WHERE Mno='1202';

  1. 回答问题：
    • ① 更新失败
    • ② 因为学生表中有与该专业相关联的学生信息，违反参照完整性（外键）约束

（十一）设置级联更新后更新专业编号

  1. 问题描述：将学生表中对 Mno 的外键声明重新设置可为级联更新；再重新更新专业表中数据科学与大数据技术（非师范）专业的专业编号，并回答相关问题。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（6 分）
① 重新设置外键
ALTER TABLE studentsDROP CONSTRAINT FK_Mno;
ALTER TABLE students
ADD CONSTRAINT FK_Mno FOREIGN KEY(Mno)
REFERENCES major(Mno)
ON UPDATE CASCADE;
② 更新专业信息
UPDATE major
SET Mno='1203'
WHERE Mname='数据科学与大数据技术（非师范）';

  1. 回答问题：
    • ① 更新成功
    • ② 因为设置了级联更新，所以当更新专业表中的专业编号时，系统会自动将学生表中该专业编号更新为新的专业编号

（十二）批量更新学院院长信息

  1. 问题描述：分别任命李力、张兰和张雪为本学院的院长，要求使用一条更新语句完成次操作。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（3 分）
UPDATE Department D,( SELECT Dno, TnoFROM TeachersWHERE Tname IN ('李力', '张兰', '张雪')) DH
SET Dheader=DH.Tno
WHERE D.Dno=DH.Dno;

（十三）创建教师工作量统计表

  1. 问题描述：创建一个教师工作量统计表 TeaQuantity，其中包含统计编号 Qid、教师编号 Tno、学年 Tacademicyear 和工作量 Tquantity，其中 Tno 和 Tacademicyear 的数据类型与 Tutors 表中的设置相同，Qid 的数据类型定义为 Binary (16)，默认输入 UNIQUEINDETIFIER (UUID) 的二进制数，且设置为该表的主键，Tquantity 的数据类型设置为整数，并且在属性列 Tno 和 Tacademicyear 上设置唯一性约束，即约束该表中每个教师编号，每个学年只统计一个工作量。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（3 分）
CREATE TABLE TeaQuantity
(Qid Binary(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
Tno CHAR(4),Tacademicyear YEAR,Tquantity INT,CONSTRAINT UK_Tno UNIQUE KEY(Tno,Tacademicyear)
);

（十四）统计教师授课工作量并插入表

  1. 问题描述：对所有教师的授课工作量按学年进行统计，即统计出每个学年该位教师所上课程的总课时量，并将统计结果添加到 TeaQuantity 表中。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（3 分）
INSERT INTO TeaQuantity(Tno, Tacademicyear, Tquantity)
(SELECT Tno,Tacademicyear,SUM(Chours)
FROM Tutors Tut,Courses C
WHERE Tut.Cno=C.Cno
GROUP BY Tno,Tacademicyear);

（十五）多步骤操作及统计插入

  1. 问题描述：① 2019 学年第 1 学期，为 20171202 班的所有学生开设了数据库系统原理，并由 “王平” 老师进行授课；② 重新对 “王平” 老师的授课工作量按学年进行统计，再次将统计结果添加到 TeaQuantity 表中。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（6 分）
① 添加授课记录
INSERT INTO Tutors(Tno,Cno,Tacademicyear,Tterm,Sclass)
VALUES
((SELECT Tno FROM Teachers WHERE Tname='王平'),
(SELECT Cno FROM Courses WHERE Cname='数据库系统原理'),
2019,1,'20171202');
-- ② 再插入选课信息
INSERT INTO Reports(Sno,Cno,Racademicyear,Rterm)
(SELECT Sno,Cno,2019,1FROM Students, CoursesWHERE Sclass='20171202' AND Cname='数据库系统原理');
③ 插入授课工作量统计表
INSERT INTO TeaQuantity(Tno,Tacademicyear,Tquantity)
(SELECT Tut.Tno,Tacademicyear,SUM(Chours)
FROM Tutors Tut,Courses C, Teachers T
WHERE Tut.Cno=C.Cno AND T.Tno=Tut.Tno AND Tname='王平'
GROUP BY Tno,Tacademicyear);

（十六）更新教师授课工作量统计

  1. 问题描述：重新对 “王平” 老师的授课工作量按学年进行统计，并将统计结果更新到 TeaQuantity 表中。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（3 分）
UPDATE TeaQuantity, 
(SELECT Tut.Tno,Tacademicyear,SUM(Chours) SumofQuantity
FROM Tutors Tut,Courses C, Teachers T
WHERE Tut.Cno=C.Cno AND T.Tno=Tut.Tno AND Tname='王平'
GROUP BY Tno,Tacademicyear) TQ
SET TeaQuantity.Tquantity= TQ.SumofQuantity
WHERE TeaQuantity.Tno=TQ.Tno AND TeaQuantity.Tacademicyeart=TQ.Tacademicyear;

（十七）添加学生表 CHECK 约束及验证

  1. 问题描述：在学生表中增加 CHECK 约束：Sno 的长度必须为 10 个字符长度；分别向学生表中添加一条正确的学生信息和一条错误的学生信息，验证 CHECK 约束是否能正确执行。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（6 分）
① CHECK 约束
ALTER TABLE StudentsADD CONSTRAINT CHK_LenSno CHECK (CHAR_LENGTH(Sno)=10);
② 错误信息验证
INSERT INTO Students(Sno,Sname)
VALUES ('202311','李四');
③ 正确信息验证
INSERT INTO Students(Sno,Sname)
VALUES ('2023115101','李四');

（十八）添加教师表 CHECK 约束

  1. 问题描述：在教师表中增加 CHECK 约束：若教师的职称是教授，则其工资 Tsal 要在 3500 元以上；若教师的职称是副教授，则其工资要在 2600 元到 3500 元之间；若教师的职称是讲师，则其工资要在 1600 元到 2600 元之间；若教师的职称是助教，则其工资要在 800 元到 1600 元之间。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（4 分）
ALTER TABLE TeachersADD CONSTRAINT CHK_Tprof_TsalCHECK (Tprof = '教授' and Tsal > 3500OR Tprof = '副教授' and Tsal BETWEEN 2600 AND 3500OR Tprof = '讲师' and Tsal BETWEEN 1600 AND 2600OR Tprof = '助教' and Tsal BETWEEN 800 AND 1600);

（十九）更新教师信息及约束验证

  1. 问题描述：张兰老师因评上教授，需将其职称 (Tprof) 由副教授改为教授，并将工资 (Tsal) 改为 3300，岗位津贴 (Tcomm) 改为 2400，并回答相关问题。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（2 分）
UPDATE Teachers 
SET Tprof='教授', Tsal=3300, Tcomm=2400
WHERE Tname='张兰';

  1. 回答问题：
    • ① 更新失败
    • ② 因为输入的工资不满足 CHECK 约束

（二十）更新教师信息成功案例

  1. 问题描述：许红霞老师评上了副教授，需将其职称 (Tprof) 由讲师改为副教授，并将工资 (Tsal) 改为 2700，岗位津贴 (Tcomm) 改为 2000，并回答相关问题。
  2. 我的答案 SQL 语句：无
  3. 正确答案 SQL 语句：

收起

sql

-- MySQL 语句（2 分）
UPDATE Teachers 
SET Tprof='副教授', Tsal=2700, Tcomm=2000
WHERE Tname='许红霞';

  1. 回答问题：
    • ① 更新成功
    • ② SELECT * FROM Teachers WHERE Tname=' 许红霞 ';

通过本次实验，我们深入学习了 SQL 数据库的更新操作以及完整性约束的设置与应用。在实际数据库管理中，正确运用这些知识可以确保数据的准确性、一致性和完整性，避免因数据错误或不一致而导致的各种问题。无论是数据的插入、更新还是删除操作，都需要充分考虑完整性约束的影响，以构建稳健可靠的数据库系统。同时，根据业务需求合理设置约束条件，能够有效规范数据的录入和修改，提高数据库的质量和可用性。


http://www.ppmy.cn/server/151007.html

相关文章

【Python】paddleocr快速使用及参数详解

【Python】paddleocr快速使用及参数详解

文章目录 1. paddleocr快速使用1.1 使用默认模型路径1.2 设定模型路径 2. PaddleOCR其他参数介绍PaddleOCR模型推理参数解释 其它相关推荐： PaddleOCR模型训练及使用详细教程 官方网址：https://github.com/PaddlePaddle/PaddleOCR PaddleOCR是基于Paddle…
阅读更多...
Java从入门到工作2 - IDEA

Java从入门到工作2 - IDEA

2.1、项目启动 从git获取到项目代码后，用idea打开。 安装依赖完成Marven/JDK等配置检查数据库配置启动相关服务 安装依赖 如果个别依赖从私服下载不了，可以去maven官网下载补充。 如果run时提示程序包xx不存在，在项目目录右键Marven->Re…
阅读更多...
从模型到视图：如何用 .NET Core MVC 构建完整 Web 应用

从模型到视图：如何用 .NET Core MVC 构建完整 Web 应用

MVC模式自出现以来便成为了 Web 开发的基石，它通过将数据、业务逻辑与用户界面分离，使得应用更加清晰易于维护，然而随着前端技术的飞速发展和框架如 React、Vue、Angular 等的崛起，许多开发者开始倾向于前后端分离的方式&#xff…
阅读更多...
elk部署与实战案例

elk部署与实战案例

**ELK Stack** 是一个非常强大的日志处理和分析平台，由 **Elasticsearch**、**Logstash** 和 **Kibana** 三个组件组成。它被广泛应用于日志收集、搜索、分析和可视化。ELK 可以处理大量数据，并帮助用户从中提取有价值的信息。以下是一个从部署到实际应用…
阅读更多...
AirSim 无人机利用姿态文件获取图片

AirSim 无人机利用姿态文件获取图片

之前我们得到了随机姿态下无人机获得的不同场景图像，我们输出了无人机随机的姿态信息到poses.csv文件中，现在我们想要复现我们的结果，就要利用我们之前输出的姿态文件来获取图像。 无人机利用姿态文件获取图片的代码如下： impor…
阅读更多...
后端向前端传值

后端向前端传值

如果在后端使用Spring MVC（或类似的框架）并通过model.addAttribute(“serverIp”, serverConfig.getIp())将服务器IP地址传递给前端视图，那么在前端视图中（通常是HTML模板，如Thymeleaf、JSP等），…
阅读更多...
分享两个爬虫练习网站+一个python游戏网站

分享两个爬虫练习网站+一个python游戏网站

目录 第一个网站第二个Python游戏网站 第一个网站 网站一 第二个 网站二 Python游戏网站 网站三
阅读更多...
005 Qt常用控件Qwidget_下

005 Qt常用控件Qwidget_下

文章目录 前言windowOpacity属性cursor属性font属性toolTip属性focuspolicy属性styleSheet属性 小结 前言 本文将会向你分享Qwidget的常见属性 windowOpacity属性 API说明windowOpacity()获取到控件的不透明数值. 返回 float, 取值为 0.0 -> 1.0 其中 0.0 表⽰全透明, 1.…
阅读更多...
最新文章

Copyright @ 2022~2023