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

红太狼 2023-01-03 04:24 205阅读 0赞

### 数据更新操作 ###

*  1. 数据插入
 *  2. 数据修改
 *  3. 数据删除

--------------------

# 1. 数据插入 #

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

**1. 插入系表**

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

![image-20201123153427655][]

**2. 插入专业表**

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

![image-20201123153444623][]

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

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

![image-20201123154207179][]

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

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

![image-20201123154251340][]

**5. 插入学生选课表**

-- 插入学生选课表
    insert into select_course values(2018217876,100,090.5)
    insert into select_course values(2018217876,96,079.0)
    insert into select_course values(2018217876,97,081.0)
    insert into select_course values(2018217879,94,091.5);
    insert into select_course values(2018217879,101,095.0);
    insert into select_course values(2018217912,80,099.0);
    insert into select_course values(2018217912,81,089.5);
    insert into select_course values(2018217923,80,096.5);
    insert into select_course values(2018217923,81,094.5);
    insert into select_course values(2018217863,96,091.5);
    insert into select_course values(2018217863,73,088.0);
    insert into select_course values(2018217845,100,091.0);
    insert into select_course values(2018217845,96,088.0);
    insert into select_course values(2018217989,101,076.0);
    insert into select_course values(2018217989,95,081.0);
    insert into select_course values(2018217765,71,097.0);
    insert into select_course values(2018217765,73,096.0);
    insert into select_course values(2018217821,111,081.0);
    insert into select_course values(2018217821,112,087.0);
    insert into select_course values(2018217823,111,091.0);
    insert into select_course values(2018217823,112,098.0);
    insert into select_course values(2018217978,111,071.5);
    insert into select_course values(2018217978,112,087.5);
    insert into select_course values(2018217965,111,067.5);
    insert into select_course values(2018217965,112,091.0);
    insert into select_course values(2018217654,71,086.5);
    insert into select_course values(2018217647,71,084.5);
    insert into select_course values(2018217552,73,067.5);
    insert into select_course values(2018217593,73,077.5);

![image-20201123154609310][]

--------------------

错误数据的插入：

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

![image-20201123155042913][]

--------------------

# 2. 数据修改 #

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

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

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

![image-20201123155532052][]

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

![image-20201123155603084][]

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

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

![image-20201123155902302][]

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

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

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

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

![image-20201123160749891][]

--------------------

# 3. 数据删除 #

> 用Delete from语句完成下列任务：
> 
> 1.  先在学生表中插入一个专业号为空值的元组。
> 2.  删除学生表中所有未设定专业号的学生。

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

[image-20201123153427655]: /images/20221119/d6392d6e660d4766822dd331bf85537f.png
[image-20201123153444623]: /images/20221119/f4e0d7e7b88243d4bbb75c246f038638.png
[image-20201123154207179]: /images/20221119/8c04081641f9487ab36eb72337a7b433.png
[image-20201123154251340]: /images/20221119/b3548f6feff94129b757d2953f52e7cd.png
[image-20201123154609310]: /images/20221119/58f643524d9445a1996ec6fdf44f4a84.png
[image-20201123155042913]: /images/20221119/e5bbaffa58224ef5bee430c9d5ffcc8e.png
[image-20201123155532052]: https://gitee.com/zhong_siru/images/raw/master//img/image-20201123155532052.png
[image-20201123155603084]: /images/20221119/282b31099c6540c58a3bbff177d8db61.png
[image-20201123155902302]: https://gitee.com/zhong_siru/images/raw/master//img/image-20201123155902302.png
[image-20201123160749891]: https://gitee.com/zhong_siru/images/raw/master//img/image-20201123160749891.png