数据库实验三——DML

本次实验需要插入数据，点击这里下载

实验要求

• 将补充资料中提供的数据插入到相应的表中；
• 删除请假单号为“6”的请假单；
• 删除教师表中教工号（teacherNum）为“2”的教师记录，如果不能请分析原因；
• 删除数据库中指定教工号（teacherNum）的教师的所有相关记录（以teacherNum为“2”为例）
• 删除数据库中指定学号（studengNum）的学生的个人信息，但保留其选课信息及请假信息，只是将选课信息和请假信息中的学号改为“000000”（以studengNum为“200202” 为例）；
• 将课程号（lessonnum）为3的课程的学分更新为6分；
• 将所有学生的课程名称（lessonname）为“C++编程”的成绩（score）增加2分；
• 对于每张“审批未通过”的请假条，将该学生该课程的成绩减少1分。

实验过程

Step1: 将上面提供的数据插入到相应的表中

–-将数据插入到student表中
INSERT INTO student VALUES   
(200101,'王小虎', '男', 1, 429001199212140000),  
(200102,'李云',   '女', 1,  429001199305110000),  
(200103,'郭兴',   '男', 1, 429003199201170000),  
(200201,'高玲',   '女', 2, 421003199212140000),  
(200202,'王锐', '男', 2, 439001199105060000),  
(200203,'马龙',   '男', 2,  429001199203110000),  
(200301,'元宝',   '男', 3, 424551199104010000)   
 –将数据插入到teacher表中
INSERT INTO teacher (teacherNum, teacherName, sex, title) VALUES  
(1,'刘波',  '男', '教授'),  
(2,'许明',  '男', '副教授'),  
(3,'方燕燕','女',' 讲师') 

当我们插入lesson表时，提示数据被截断，原因是我们定义的时候，将lessonNum设置为8个字节，但是在实际插入的值‘建筑与音乐’包含10个字节，所以我们需要对lessonNum的数据类型进行调整再进行插入。

ALTER TABLE lesson ALTER COLUMN lessonName char(10)  
  
INSERT INTO lesson (lessonNum, lessonName, year, term, credit, teacherNum,beforeLessonNum) VALUES  
(1,  'C语言',  2013,  '上',  4,  1, null),  
(2,'C++编程',  2013,   '上', 3,  2,  1),  
(3, '算法设计', 2013,  '上',  5,  3,  2),  
(4,'建筑与音乐', 2012, '下',  2,  3, null) 

再将数据插入到stu_choose_lesson表中

INSERT INTO stu_choose_lesson (studentNum, lessonNum, score) VALUES  
(200101, 1, 89),  
(200102, 1, 100),  
(200103, 1, 78),  
(200201, 2, 89),  
(200202, 2, 60),  
(200203, 2, 61),  
(200301, 2, 77),  
(200101, 3, 82),  
(200102, 3, 56),  
(200103, 3, 86),  
(200201, 3, 89),  
(200202, 3, 78),  
(200101, 4, 62),  
(200102, 4, 68),  
(200201, 4, 82),  
(200202, 4, 89)    
SELECT * FROM stu_choose_lesson

接下来将数据插入到leave_apply中，但当插入的时候，系统提示不满足参照完整性，这是因为我们在实验二中定义了外键：

ALTER TABLE leave_apply ADD CONSTRAINT FK_st_le FOREIGN KEY(studentNum,lessonNum) REFERENCES stu_choose_lesson(studentNum,lessonNum)  

而在资料提供的数据集中：

INSERT INTO leave_apply (applyNum, applyTime, reason, state, studentNum, lessonNum, teacherNum) VALUES  
(1, '2013-8-29', '事假',      '审批未通过',   200101,  1, 1),  
(2, '2013-8-30', '病假',      '审批未通过',   200102,  1, 1),  
(3, '2013-8-31', '看病',      '审批已通过',   200103,  1, 1),  
(4, '2013-9-1',  '家人来访',  '审批已通过',   200201,  2, 2),  
(5, '2013-9-2',  '临时有急事', '审批已通过',  200202,  2, 2),  
(6, '2013-9-3',  '学生会活动', '未审核',      200203,  3, 3),-- ?  
(7, '2013-9-4',  '病假',       '未审核',      200301,  3, 3),-- ?  
(8, '2013-9-5',  '感冒发烧',   '审批已通过',  200201,  3, 3),  
(9, '2013-9-6',  '风寒',       '审批未通过',  200202,  4, 3),  
(10,'2013-9-7',  '活动',      '未审核',      200203,  4, 3) -- ? 

编号为6，7，11的(studentNum,lessonNum)没有出现在stu_choose_lesson中，不满足参照完整性约束，所以将其删除后再进行插入（我看到有同学插入成功了，这是因为这位同学在上次实验中并没有创建我上述指出的外键，如果追求完美的同学，可以把那个外键给删了，然后再添加数据，这里的数据应该是老师给的有问题）

INSERT INTO leave_apply (applyNum, applyTime, reason, state, studentNum, lessonNum, teacherNum) VALUES  
(1, '2013-8-29', '事假',      '审批未通过',   200101,  1, 1),  
(2, '2013-8-30', '病假',      '审批未通过',   200102,  1, 1),  
(3, '2013-8-31', '看病',      '审批已通过',   200103,  1, 1),  
(4, '2013-9-1',  '家人来访',   '审批已通过',   200201,  2, 2),  
(5, '2013-9-2',  '临时有急事', '审批已通过',  200202,  2, 2),  
(8, '2013-9-5',  '感冒发烧',   '审批已通过',  200201,  3, 3),  
(9, '2013-9-6',  '风寒',       '审批未通过',  200202,  4, 3)  

Step2: 删除请假单号为“6”的请假单

DELETE FROM leave_apply
WHERE applyNum = 6

Step3：删除教师表中教工号（teacherNum）为“2”的教师记录，如果不能请分析原因

尝试删除：

DELETE FROM teacher  
WHERE teacherNum = 2  

但删除失败，分析原因：因为我们在lesson表和leave_apply表中，都将teacherNum作为外键，如果删除了teacherNum，两张表的teacher会丢失参照，就不满足一致性约束了，所以会出错。

Step4：删除数据库中指定教工号（teacherNum）的教师的所有相关记录（以teacherNum为“2”为例）

这道题方法很多，可以先删除其他表中teacherNum为2的数据，再删除teacherNum的数据，也可以将外键先关闭后，再打开，我这里使用的是另一种方法，就是外键扩充定义，可以了解一下on delete cascade

在这里，我们先删除原来的外键，再进行外键的定义，但由于我们之前定义外键的时候图方便没有给他命名，所以只能用系统生成的，所以以后定义约束最好还是命个名比较好。。

ALTER TABLE lesson drop CONSTRAINT FK__lesson__teacherN__29572725  
ALTER TABLE lesson ADD CONSTRAINT Fk_le_te FOREIGN KEY(teacherNum) REFERENCES teacher(teacherNum) ON DELETE CASCADE  
	  
ALTER TABLE stu_choose_lesson DROP CONSTRAINT  FK__stu_choos__lesso__2D27B809  
ALTER TABLE stu_choose_lesson ADD CONSTRAINT Fk_choose_le FOREIGN KEY(lessonNum) REFERENCES lesson(lessonNum) ON DELETE CASCADE  
	  
ALTER TABLE leave_apply DROP CONSTRAINT FK_st_le  
ALTER TABLE leave_apply ADD CONSTRAINT FK_st_le FOREIGN KEY(studentNum, lessonNum) REFERENCES stu_choose_lesson(studentNum,lessonNum) ON DELETE CASCADE

再进行删除

DELETE FROM teacher  
WHERE teacherNum = 2  
SELECT * FROM teacher  

Step 5:删除数据库中指定学号（studengNum）的学生的个人信息，但保留其选课信息及请假信息，只是将选课信息和请假信息中的学号改为“000000”（以studengNum为“200202” 为例）

首先将leave_apply中连接student的外键进行删除后，将学号’200202’设置为’000000’

ALTER TABLE leave_apply DROP CONSTRAINT FK__leave_app__stude__300424B4  
ALTER TABLE leave_apply DROP CONSTRAINT FK_st_le  
update leave_apply SET studentNum = '000000'  
WHERE studentNum = '200202'  

然后将stu_choose_lesson中连接student的外键删除，将学号’200202’设置为’000000’

ALTER TABLE stu_choose_lesson DROP CONSTRAINT FK__stu_choos__stude__2C3393D0
update stu_choose_lesson SET studentNum = '000000'  
WHERE studentNum = '200202'  

最后将student表中学号为’200202’的元组删除

DELETE FROM student  
WHERE studentNum = '200202'  

Step 6: 课程号（lessonnum）为3的课程的学分更新为6分；

UPDATE lesson SET credit = 6  
    WHERE lessonNum = 3  
SELECT * FROM lesson  

Step 7: 将所有学生的课程名称（lessonname）为“C++编程”的成绩（score）增加2分

UPDATE stu_choose_lesson SET score = score + 2  
    FROM stu_choose_lesson, lesson  
        WHERE lesson.lessonNum = stu_choose_lesson.lessonNum and lesson.lessonName = 'C语言'  
SELECT * FROM stu_choose_lesson  
SELECT * FROM lesson  

Step 8: 对于每张“审批未通过”的请假条，将该学生该课程的成绩减少1分

UPDATE stu_choose_lesson SET score = score - 1
	FROM stu_choose_lesson AS sc, leave_apply AS la
		WHERE sc.lessonNum = la.lessonNum and sc.studentNum = la.studentNum 
			and la.state = '审批未通过' and la.studentNum<> '000000'

实验三Over