SQL语言-更新操作命令

一、 实验目的

利用INSERT,UPDATE,和DELETE命令或语句实现对表或视图数据的添加，修改与删除等更新操作。
二、实验内容与要求
请实践以下命令式更新操作,
①在学生表和学生选课表SC中分别添加表5-1和表52中的记录
②备份表到TS中,并清空TS表x
①给S系的学生开设7号课程,建立所在相应的选课记录,成绩置定为60分
①把年龄小于等于16岁的女生记录保存到表TS中。
⑤在表中检索每门课均不及格的学生学号,姓名、年龄性别及所在系等信息,
把检索到的信息存入TS表中,
⑥将学号为“98011”的学生姓名改为刘华,年龄增加1岁
②把选修了“数据库系统”课程而成绩不及格的学生的成绩全改为空值(NULL)
③将的前4位学生的年龄均增加1岁,
②学生王林在3号课程考试中作弊,该课成绩改为空值(NULL),
①把成绩低于总平均成绩的女同学的成绩提高5%
①在基本表SC中修改课程号为“2”号课程的成绩,若成绩小于等于80分时降低2%,
成绩大于80分时降低1%(用两个UPDATE语句实现)
⑩利用“SELECT INTO…”命令来备份、SC、三表,备份表名自定。
在基本表SC中删除尚无成绩的选课元组
把“钱横”同学的选课情况全部删去
①能删除学号为“9800°的学生记录吗?如果一定要删除该记录,该如何操作?给出提
作命令。
⑩删除姓“张”的学生的记录。
清空student与course两表.
如何又从备份表中恢复所有的三表?

三、实验结果
以下分别为实验代码和图片
--------T1插入
insert into student1 values(‘99010’,‘赵青江’,18,‘男’,‘CS’);
insert into student1 values(‘99011’,‘张丽萍’,19,‘女’,‘CH’);
insert into student1 values(‘99012’,‘陈景欢’,20,‘男’,‘IS’);
insert into student1 values(‘99013’,‘陈婷婷’,16,‘女’,‘PH’);
insert into student1 values(‘99014’,‘李军’,16,‘女’,‘EH’);

insert into sc values(‘99010’,‘1’,87);
insert into sc values(‘99010’,‘2’,null);
insert into sc values(‘99010’,‘3’,80);
insert into sc values(‘99010’,‘4’,87);
insert into sc values(‘99010’,‘6’,85);
insert into sc values(‘99011’,‘1’,52);
insert into sc values(‘99011’,‘2’,47);
insert into sc values(‘99011’,‘3’,53);
insert into sc values(‘99011’,‘5’,45);
insert into sc values(‘99012’,‘1’,84);
insert into sc values(‘99012’,‘3’,null);
insert into sc values(‘99012’,‘4’,67);
insert into sc values(‘99012’,‘5’,81);
-------T2 插入清空
select * into ts from student1;
delete from ts;
---------T3
insert into sc
select sno,cno,60
from student1,course2
where sdept='IS’and cno=‘7’;

-------T4
insert into ts
select *
from student1
where sage<=16 and ssex=‘女’;
--------T5
insert into ts
select distinct x.sno,sname,sage,ssex,sdept
from student1 x ,sc
where x.sno=sc.sno and x.sno not in (select sno from sc where grade>=60);

---------------T6
update student1 
set sname=‘李华’,sage=sage+1       
where sno=‘99011’;
select * from course2;
-----------------T7
update sc
set grade=null
where sno in
(
select sno
from course2,sc
where course2.cno=sc.cno and grade<60
);
-----------T8
update student1–一不小心执行了两次
set sage=sage+1
where sno in
( select top 4 sno
from student1
);
--------T9
update sc
set grade=null
where sno in
(
select sno
from student1
where sname=‘王林’ and cno=‘3’
);
------------T10
update sc
set grade=grade+grade0.5
where sno in
(
select sc.sno
from sc,student1
where sc.cno=student1.sno and ssex=‘女’ and grade<(select avg(grade) from sc)
);
select * from sc;
------------T111
update sc
set grade=grade-grade0.2
where cno=‘2’ and grade<=80;
update sc
set grade=grade-grade0.1
where cno=‘2’ and grade>80;
------------T12
select into bf1 from student1;
select * into bf2 from sc;
select * into bf3 from course2;
-----------T13
(select sno,grade from sc  where grade is NULL)
 delete from sc  
where grade is null;
----------T14
delete from sc where sno in(select sno from student1 where sname=‘钱横’);
select * from sc;
-------T15可以删除，但是要把SC其中记录删除
delete from sc where sno=‘98003’ ;
delete from student1 where sno=‘98003’;
-----------T16
delete from sc where sno in(select sno from student1 where sname like ‘张%’);
delete from student1 where sname like ‘张%’;
------------T17
delete from sc;
delete from student1;
drop table sc;
select * into sc from bf2;
delete from course2;
select from student1;
select * from course2;
-----------T18
drop table student1;
drop table course2;
 select into student from bf1;
 select* into course from bf3;

     ![在这里插入图片描述](https://img-blog.csdnimg.cn/20210302092337490.png)

四．实验心得
本次难度不是很大，主要是最后两题删除两个表，因为SC之中引用了这两个表，所以不能删除，所以一开始要删除SC表，然后利用备份将SC还原，再删除，就可以了，还有再备份的过程中也建表了。