![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/e0880bd8b96166844ae63e7d6cdb748a.png)
实验目的:
1.熟练掌握SQL的常用数据更新操作。
2.熟练应用INSERT,UPDATE,DELETE语句。
3.掌握更新操作的各种格式。
实验内容和主要步骤:
第1题:
1>
Insert into student(sno,sname,sage) Values('95030','李莉',18);
2>
insert into sc(sno,cno)
values('95030','1');
3>
update student
set sage=20
where sdept=‘cs’;
4>
update sc
set grade=0
where
sno in (select sno from a.student where sdept=‘ma’);
5>
update sc
set grade=grade+5
where sno in
(
select sno
from student
where ssex=‘女’ and grade<(select avg(grade) from sc)
) ;
6>
update sc
set grade=grade*(1+0.05)
where cno=‘002’ and grade<75;
update sc
set grade=grade*(1+0.04)
where cno=‘002’ and grade>75
7>
delete
from student
where sno=‘95030’;
8>
delete
from sc
where grade is null;
9>
delete
from sc
where sno in
(
select sno
from student
where sname=‘张那’
);
10>
delete
from sc
where sno in
(
select sno
from student
where sdept=‘ma’
);
11>
delete
from sc
where grade<60;
12>
create table stu
(sno char(8),
sname char(8) ,
ssex char(2)
);
insert into stu(Sno,Sname,Ssex)
select sno,sname,ssex
from student
where sno in
(select sno
from sc
group by sno
having min(grade)>80);
13>
create table stu1
(
sno char(8),
cno char(3)
);
insert into stu1(sno,cno)
select sno,cno
from sc;
n) 所有学生年龄增1
update student
set sage=sage+1;
14>
create table stu2
(
sname char(8),
sdept char(20)
);
insert into stu2(sname,sdept)
select sname,sdept
from student
where sno in
(
select distinct sno
from sc
where grade<60
group by sno
having count(grade)>=3
);
实验中遇到的问题和心得体会:
1.No database selected Select the default DB to be used by double clicking its name报错:原因没有选中数据库
2. Error Code: 1054. Unknown column ‘‘95030’’ in ‘field list’ 0.000 sec报错:因为有多余列
3. mysql1062错误: Duplicate entry ‘…’ for key 'PRIMARY报错:插入数据时,要插入数据的主键数据(…)已经存在,不能再重复添加了
4. 建表的时候设置了非空,所以更新数据也不得有空
5. 更新这类实验报告为啥让软件运行这么慢,就算不报错也不会直接显示更改后的结果