数据库系统实验5 SQL语言-数据更新操作

程序员文章站 2022-05-08 19:42:19

...

SQL语言-数据更新操作

1.在教学管理数据库jxgl中，使用MySQL Workbench在表中导入表5-1和表5-2的数据

将所给数据导入student表：

insert into student
values ('2005010','赵春江',18,'男','Cs'),
       ('2005011','赵丽萍',19,'女','Ch'),
       ('2005012','陈景欢',20,'男','Is'),
       ('2005013','陈婷婷',16,'女','Ph'),
       ('2005014','李军',16,'女','Ph');

导入后查看结果：

数据库系统实验5 SQL语言-数据更新操作

将所给数据导入sc表：

insert into sc 
values ('2005010','1',87),
	   ('2005010','2',NULL),
       ('2005010','3',80),
       ('2005010','4',87),
       ('2005010','6',85),
       ('2005011','1',52),
       ('2005011','2',47),
       ('2005011','3',53),
       ('2005011','5',45),
       ('2005012','1',84),
       ('2005012','3',NULL),
       ('2005012','4',67),
       ('2005012','5',81);

导入数据后查看结果：

数据库系统实验5 SQL语言-数据更新操作

2.使用SQL语句进行以下更新操作

给IS系的学生开设7号课程，建立相应的选课记录，成绩为空

insert into sc  #插入数据
select sno,'7',null    #令sno为Is系的学生的学号，cno为'7',成绩为空
from student
where sdept='Is';

查看结果：

数据库系统实验5 SQL语言-数据更新操作
从运行结果可以看出Is系的两门学生的选课记录中增加了7号课程且课程成绩为NULL。

在表student中检索每门课均不及格的学生学号、姓名、年龄、性别及所在系信息，并把检索到的信息存入信标ts中。

首先创建表ts，由于其属性和student相同，故直接用create table name1 like name2的方式建表，然后再用insert语句筛选出数据插入其中
```
create table ts like student;  #通过create table  name  like name2 可创建属性相同的表
insert into ts
select *
from student natural join sc  #用student和sc的自然连接表示
group by sno #用学号分组
having max(grade)<60;  #max(grade)<60说明所有课均不及格
```
执行结果：

数据库系统实验5 SQL语言-数据更新操作

将学号为2005001的学生姓名修改为"李华",年龄增加1岁
```
update student set sname="李华" ,sage=sage+1 #将年龄加1,名字改为'李华'
where sno='2005001';
```
运行结果：

起初2005001对应的是‘钱横’且age=18，故可知修改成功。
把选修了"数据库系统"课程而成绩不及格的学生成绩全部改为空值
```
update sc set grade=NULL 
where cno=(select distinct cno from course where cname='数据库系统') and grade<60; 
```
运行结果：

之前2005011对应的数据库成绩为52，现在改为null，故可知正确。
学生王林在3号课程考试作弊，将该课成绩改为空值

由于原本的数据库中王林没用选修3号课程，故现增加一组数据：
```
insert into sc values('2005002','3',100);
```
然后再进行操作
```
update sc set grade=null
where sno=(select distinct sno from student where sname='王林') and cno='3';
```
运行结果：

数据库系统实验5 SQL语言-数据更新操作

把成绩低于总平均成绩的女同学成绩提高5%
```
SET SQL_SAFE_UPDATES = 0; #因为要修改非主键元素，故必须关闭safe_update
with avg_grade(the_avg_grade) as
(select avg(grade)
 from sc)
update sc set grade=grade*1.05
where sno in (select sno from student where ssex='女') and grade<(select distinct * from avg_grade);
```
运行结果：

从之前的结果中可以看出赵丽萍的成绩为47-53-45，故完成了更新。共两个女性，而另一位女性的成绩是较高的，高于平均成绩故不需要更新，因此更新仅对赵丽萍操作。

在基本表sc中修改课程号为"2"号课程的成绩，成绩小于等于80分的降低2%,成绩大于80分时降低1%

update sc set grade=grade*(1-0.02)
where grade<=80 and cno='2';
update sc set grade=grade*(1-0.01)
where grade>80 and cno='2';
#注意这里的顺序不可以反，否则可能导致重复更新

另一种写法:

update sc
set grade=case
when grade<=80 then grade*(1-0.02)
else grade*(1-0.01)
end
where cno='2';
#用case when then else语句

结果：

数据库系统实验5 SQL语言-数据更新操作

与之前对比可知减分操作完成。

把"钱横"的选课记录全部删去
```
delete from sc
where sno=(select sno from student where sname='钱横');
```
由于之前我们已经将钱横改为李华，故在此处我将删除李华的选课记录。
```
delete from sc
where sno=(select sno from student where sname='李华');
```
运行结果：

数据库系统实验5 SQL语言-数据更新操作

故可知删除记录成功。

能删除2005001的学生记录吗？一定要删除应该如何操作？给出操作命令

可以根据学号删除学生记录。

删除该学生的记录意味着删除该学生对应的student和sc记录，故需要删除两个表中的记录。
```
delete from sc
where sc.sno in(
    select sno 
    from student 
    where sno = '2005001');
delete from student where sno = '2005001'
```
删除后查询可知均为空：

数据库系统实验5 SQL语言-数据更新操作