SQL server数据的插入修改和删除

13.数据的插入修改和删除

select *
from Student;

数据的插入

insert into Student(Sno,Sage,Sex,Sdept,Sname)
values(‘201216128’,212,‘男’,‘IS’,‘芜湖’);

insert into Student
values(‘201216118’,‘iu’,‘女’,‘IS’,24);

insert into Student
values(‘301216118’,‘a’,‘女’,‘IS’,24),
(‘401216118’,‘b’,‘女’,‘IS’,24),
(‘501216118’,‘c’,‘女’,‘IS’,24),
(‘601216118’,‘d’,‘女’,‘IS’,24),
(‘701216118’,‘e’,‘女’,‘IS’,24);

–对每一个系,求学生平均年龄 放入数据库
–1插入子查询的结果
–1.1创建一个表
create table depage(
sdept char(15),
avage smallint,
);
–1.2插入子查询结果(向表中插入一个查询到的表的结果,直接插入多行)
insert into depage
select sdept,AVG(Sage)
from Student
group by Sdept;

select *
from depage;

数据的修改

update Student
set Sage = 22,Sname = ‘pdd’
where Sno = ‘201215122’;

–修改多个元组
update depage
set avage = 18;

update depage
set avage += 1;

update depage
set avage ++; --error 不可以

–将计算机科学系全体学生成绩置零
update SC
set Grade = 0
where Sno in(select Sno
from Student
where Sdept = ‘CS’);

use master

数据的删除

select *
from student;

delete from student
where sno = 201215128;

–删除多个元组
delete from depage;–删除数据
drop table depage;–删除表

–删除信息系所有学生 选课记录
delete from SC
where sno in (select sno
from student
where sdept = ‘CS’ );