数据库基础的增删改查

#数据库基础的增删改查

##创建一个表名为TestScores，数据为姓名，科目，成绩

create table TestScores(name varchar(100),course varchar(100),score varchar(100))

##往表里增加数据

 insert into testscores(name,course,score) values('张三','语文',81);
 insert into testscores(name,course,score) values('张三','数学',75);
 insert into testscores(name,course,score) values('李四','语文',76);
 insert into testscores(name,course,score) values('李四','数学',90);
 insert into testscores(name,course,score) values('王五','语文',81);
 insert into testscores(name,course,score) values('王五','数学',100);
 insert into testscores(name,course,score) values('王五','英语',90)

##增
增加一组数据王六英语32

insert into testscores values('王六','英语',32)

##删
删除姓名是王六的所有数据

delete from testscores where name="王六"

##改
**把姓名是张三的语文成绩改成80

update testscores set score=80 where name="张三" and course="语文"

##查
查询所有成绩都在80分以上的学生

select name from testscores group by name having   MIN(score)>=80;

查询语文数学成绩总和第一的学生

select name from testscores GROUP BY name ORDER BY sum(score) desc limit 0, 1

group byXXX:以XXX分组
order by XXXdesc :按xxx倒序排列
limit 0,1:取第一个
limit2：取前两个
limit1,1:取第二个