数据更新操作

1. 数据插入

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

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

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,'安徽省宣城市');

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);

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

-- 插入学生表insert into student values(217876,'钟嗣儒','男','2000-02-04',21);insert into student values(217879,'何飞','男','2000-02-04',21);insert into student values(217912,'王珊','女','2000-06-23',41);insert into student values(217923,'李子木','女','2001-06-23',41);insert into student values(217863,'宋承羽','男','2000-01-14',22);insert into student values(217845,'张波','男','2001-10-14',22);insert into student values(217989,'王骁','男','2000-06-29',23);insert into student values(217765,'张云轩','男','2000-09-29',23);insert into student values(217821,'袁姗','女','2000-11-04',32);insert into student values(217823,'章怡','女','2000-9-04',32);insert into student values(217978,'王琼','女','2000-5-23',31);insert into student values(217965,'宋宇','男','2000-5-11',31);insert into student values(217654,'高原','男','1999-09-14',51);insert into student values(217647,'王志鹏','男','1999-11-04',51);insert into student values(217552,'庞博','男','1998-06-27',52);insert into student values(217593,'宋诚','男','1998-10-27',52);

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);

5. 插入学生选课表

-- 插入学生选课表insert into select_course values(217876,100,090.5)insert into select_course values(217876,96,079.0)insert into select_course values(217876,97,081.0)insert into select_course values(217879,94,091.5);insert into select_course values(217879,101,095.0);insert into select_course values(217912,80,099.0);insert into select_course values(217912,81,089.5);insert into select_course values(217923,80,096.5);insert into select_course values(217923,81,094.5);insert into select_course values(217863,96,091.5);insert into select_course values(217863,73,088.0);insert into select_course values(217845,100,091.0);insert into select_course values(217845,96,088.0);insert into select_course values(217989,101,076.0);insert into select_course values(217989,95,081.0);insert into select_course values(217765,71,097.0);insert into select_course values(217765,73,096.0);insert into select_course values(217821,111,081.0);insert into select_course values(217821,112,087.0);insert into select_course values(217823,111,091.0);insert into select_course values(217823,112,098.0);insert into select_course values(217978,111,071.5);insert into select_course values(217978,112,087.5);insert into select_course values(217965,111,067.5);insert into select_course values(217965,112,091.0);insert into select_course values(217654,71,086.5);insert into select_course values(217647,71,084.5);insert into select_course values(217552,73,067.5);insert into select_course values(217593,73,077.5);

错误数据的插入：

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

2. 数据修改

用Update 语句完成下列任务：

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

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

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

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

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

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

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技术'

3. 数据删除

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

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

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