sql serve2008的增删改查操作

sql serve2008的增删改查

use test
create table student
( 
id int not null primary key,
name char(16) not null,
sex bit default 1,
major char(16) not null,
score int not null
)
--在创建的表中添加数据
insert into student values
(6,'xiaoming',1,'pe',100),
(1, 'liming', 1,'conputer',45),
(2,'xiaotong',0,'computer',67),
(3,'wangsi',1,'pe',89),
(4,'qianjin',0,'computer',78),
(5,'mengmeng',0,'pe',98)
--修改id=1的专业
update student set major='computer' where id=1
select score,name from student 
--查询学生的姓名，性别，以及成绩并对成绩分等级
select name, sex,score=
case when score<60 then 'not'
when score>80 then 'best'
else 'yes'
end
from student
--求平均成绩
select avg(score) as 'pingjunchengji'
from student
where major='computer'
--求最大值
select max(score) as 'pingjunchengji'
from student
where major='computer'
--统计满足条件的总行数或满足条件的行数
select COUNT(*) as 'studentsum' from student
--查询名字中有i的所有人
select * from student where name like '_i%'
--修改id为4的成绩
update student set score='92' where id =4
--插入一列
alter table student add note1 char(4) null
--新插入的列中添加数据
update student set note1='good' where id =3
--删除一列
alter table student drop column note
--修改列名
exec sp_rename '[student].[note1]','note'
--查询某一专业的学生人数
select major,COUNT(*) as 'student_count' from student group by major

下边的图片是上面一部分代码完成后的结果，希望对大家有用