数据库原理实验五：SQL的常用数据更新操作

实验目的

掌握SQL的常用数据更新操作，熟练应用INSERT,UPDATE,DELETE语句。

实验内容

1. 插入如下学生记录（学号：95030，姓名：李莉，年龄：18）

insert into student(sno,sname,sage)
values ('95030','李莉',18)

2. 插入如下选课记录（95030，1）

insert into sc(sno,cno)
values('95030',1)

3. 计算机系学生年龄改成20

update student
set sage=20
where sdept='CS'

4. 把数学系所有学生成绩改成0

update sc
set grade=0
where 'MA'=
(select sdept
from student
where student.sno=sc.sno)

5. 把低于总平均成绩的女同学成绩提高5分

update sc 
set grade+=5
where grade<
(select avg(grade) 
from sc inner join student
on student.sno=sc.sno
where ssex='女')

6. 删除95030学生信息

delete
from student
where sno='95030'

7. 删除SC表中无成绩的记录

delete 
from sc
where grade is null;

8. 删除张娜的选课记录

delete
from sc
where sno=
(select sno from student where sname='张娜')

9. 删除不及格的学生选课记录

delete
from sc
where grade<60

10.删除数学系所有学生选课记录

delete
from sc
where sno in 
(select sno from student where sdept='MA')

11. 删除所有未被选修的课程

delete
from course
where cno not in (select cno from sc)

12. 查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中

create table STU 
(sno char(8), 
sname char(8), 
ssex char(2) 
)

insert into STU(sno,sname,ssex);

select distinct student.sno,sname,ssex
from student,sc 
where student.sno not in
(select sno from sc where grade<80) and student.sno=sc.sno

13. 建立一个sdeptgrade 表，包含（sdept,avggrade）字段，对每一个系，求学生的成绩，并把结果存入sdeptgrade

create table sdeptgrade 
(sdept char(8) primary key, 
avggrade int
) 

insert into sdeptgrade;

select student.sdept, avg(sc.grade) 
from student inner join sc
on (student.sno = sc.sno) 
group by student.sdept;

实验总结

删除主键表数据如果有外键约束就会报错