合肥工业大学—SQL Server数据库实验六：数据更新操作

---

1. 数据插入

依次将准备好的数据用Insert Into语句将数据插入相应的基本表：

1. 正确数据的插入，这些数据应该不会违反完整性约束
2. 注意基本表的先后插入顺序，体会参照完整性。特别要注意课程表的数据插入与其他基本表的不同。
3. 对每一个基本表，插入错误的数据，这些数据应该是事先准备好，且违反了基本表的某一个约束条件的。

1. 插入系表

1. -- 插入系表
2. insert into department values(1,'计算机与信息系','周波',18856245890,'安徽省宣城市');
3. insert into department values(2,'法律系','王立',18856247532,'安徽省合肥市');
4. insert into department values(3,'英语系','周扬',18856246429,'安徽省芜湖市');
5. insert into department values(4,'机器系','宋志诚',18856241353,'安徽省宣城市');

复制代码

2. 插入专业表

1. -- 插入专业表
2. insert into major values(21,'物联网工程',1);
3. insert into major values(22,'计算机科学',1);
4. insert into major values(23,'人工智能',1);
5. insert into major values(31,'国家政法',2);
6. insert into major values(32,'法律事务',2);
7. insert into major values(41,'世界英语',3);
8. insert into major values(51,'机器设计制造',4);
9. insert into major values(52,'自动化',4);

复制代码

3. 插入学生表：日期类型注意加单引号

1. -- 插入学生表
2. insert into student values(2018217876,'钟嗣儒','男','2000-02-04',21);
3. insert into student values(2018217879,'何飞','男','2000-02-04',21);
4. insert into student values(2018217912,'王珊','女','2000-06-23',41);
5. insert into student values(2018217923,'李子木','女','2001-06-23',41);
6. insert into student values(2018217863,'宋承羽','男','2000-01-14',22);
7. insert into student values(2018217845,'张波','男','2001-10-14',22);
8. insert into student values(2018217989,'王骁','男','2000-06-29',23);
9. insert into student values(2018217765,'张云轩','男','2000-09-29',23);
10. insert into student values(2018217821,'袁姗','女','2000-11-04',32);
11. insert into student values(2018217823,'章怡','女','2000-9-04',32);
12. insert into student values(2018217978,'王琼','女','2000-5-23',31);
13. insert into student values(2018217965,'宋宇','男','2000-5-11',31);
14. insert into student values(2018217654,'高原','男','1999-09-14',51);
15. insert into student values(2018217647,'王志鹏','男','1999-11-04',51);
16. insert into student values(2018217552,'庞博','男','1998-06-27',52);
17. insert into student values(2018217593,'宋诚','男','1998-10-27',52);

复制代码

4. 插入课程表：注意顺序问题，先修课的课程要先创建！

1. -- 插入课程表
2. insert into course values(94,'计算机基础',5,null);
3. insert into course values(100,'JAVA技术',4,94);
4. insert into course values(90,'高等数学',6,null);
5. insert into course values(95,'概率论与数理统计',5,90);
6. insert into course values(101,'机器学习',3,95);
7. insert into course values(80,'英语',3,null);
8. insert into course values(81,'国际英语交流',5,80);
9. insert into course values(97,'汇编语言',3,94);
10. insert into course values(96,'微机原理与接口技术',4,97);
11. insert into course values(112,'政法基础',3,null);
12. insert into course values(111,'法律素养',3,112);
13. insert into course values(73,'自动化入门',6,null);
14. insert into course values(71,'机器制造',6,73);

复制代码

5. 插入学生选课表

1. -- 插入学生选课表
2. insert into select_course values(2018217876,100,090.5)
3. insert into select_course values(2018217876,96,079.0)
4. insert into select_course values(2018217876,97,081.0)
5. insert into select_course values(2018217879,94,091.5);
6. insert into select_course values(2018217879,101,095.0);
7. insert into select_course values(2018217912,80,099.0);
8. insert into select_course values(2018217912,81,089.5);
9. insert into select_course values(2018217923,80,096.5);
10. insert into select_course values(2018217923,81,094.5);
11. insert into select_course values(2018217863,96,091.5);
12. insert into select_course values(2018217863,73,088.0);
13. insert into select_course values(2018217845,100,091.0);
14. insert into select_course values(2018217845,96,088.0);
15. insert into select_course values(2018217989,101,076.0);
16. insert into select_course values(2018217989,95,081.0);
17. insert into select_course values(2018217765,71,097.0);
18. insert into select_course values(2018217765,73,096.0);
19. insert into select_course values(2018217821,111,081.0);
20. insert into select_course values(2018217821,112,087.0);
21. insert into select_course values(2018217823,111,091.0);
22. insert into select_course values(2018217823,112,098.0);
23. insert into select_course values(2018217978,111,071.5);
24. insert into select_course values(2018217978,112,087.5);
25. insert into select_course values(2018217965,111,067.5);
26. insert into select_course values(2018217965,112,091.0);
27. insert into select_course values(2018217654,71,086.5);
28. insert into select_course values(2018217647,71,084.5);
29. insert into select_course values(2018217552,73,067.5);
30. insert into select_course values(2018217593,73,077.5);

复制代码

---

错误数据的插入：

1. insert into department values(5,null,'王青',188562459080,'安徽省宣城市');

复制代码

---

2. 数据修改

用Update 语句完成下列任务：

1. 修改某位学生的专业号，要求包括：修改语句成功执行和修改操作被拒绝两种情形。
2. 修改某门课学生的成绩，学生、课程及成绩由学生自行确定。
3. 将选课表中50~59之间的成绩整体增加10分。
4. 将“Java语句”课程的先修课改为“c209”。（可根据表中数据调整条件数据）

1. 修改某位学生的专业号，要求包括：修改语句成功执行和修改操作被拒绝两种情形。

1. -- 更改2018217876学生的专业号为22
2. update student set tc_mj = 22 where st_id = '2018217876';

复制代码

1. -- 更改2018217876学生的专业号为55(不存在的专业号)
2. update student set tc_mj = 55 where st_id = '2018217876';

复制代码

2. 修改某门课学生的成绩，学生、课程及成绩由学生自行确定。

1. -- 更改2018217876号学生的96号课成绩为91分
2. update select_course set sc_grade = 091.0 where sc_id = 2018217876 and sc_num = 96

复制代码

3. 将选课表中70~79之间的成绩整体增加10分

1. -- 将选课表中70~79之间的成绩整体增加10分
2. update select_course set sc_grade = sc_grade + 10 where sc_grade >= 70 and sc_grade <= 79

复制代码

4. 将“JAVA技术”课程的先修课改为“90”

1. -- 将“JAVA技术”课程的先修课改为“90”
2. update course set cs_prerequisite = 90 where cs_name = 'JAVA技术'

复制代码

---

3. 数据删除

用Delete from语句完成下列任务：

1. 先在学生表中插入一个专业号为空值的元组。
2. 删除学生表中所有未设定专业号的学生。

1. -- 先在学生表中插入一个专业号为空值的元组
2. insert into student values(2018217971,'zsr','男','2000-09-07',null);
4. -- 删除学生表中所有未设定专业号的学生
5. delete from student where tc_mj is null

复制代码