mysql数据库实验报告四

实验目的：

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. 更新这类实验报告为啥让软件运行这么慢，就算不报错也不会直接显示更改后的结果