3.13作业

CREATE TABLE GRADE(
ID int primary key auto_increment,
name varchar(20),
age int(3),
class int(10),
chinese int(5),
english int(5),
math int(5)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
insert into GRADE values(1,'zhangsan',18,1832,80,90,77),
(2,'lisi',18,1832,90,90,90),
(3,'wangwu',19,1833,99,98,100),
(4,'zhaoliu',17,1833,85,78,85),
(5,'xiaoqi',17,1832,60,80,88),
(6,'xiaoba',18,1833,70,82,90);
select * from GRADE where class=1832;
select * from GRADE where chinese>80 and chinese<90 and class=1833;
select * from GRADE where ID LIMIT 4,6;
SELECT ID,name from GRADE where class=1832 and english=98 and math=77;
SELECT * from GRADE where class=1832 ORDER BY chinese desc;
select name from GRADE where math<80 and chinese<80 and class in(1832,1833);
select name,class from GRADE where chinese is null;
select name from GRADE where chinese<60;
SELECT class,AVG(math) from GRADE group by class;
select class,sum(chinese) from GRADE group by class;
update GRADE set chinese=60 where chinese<60;
select name,age from GRADE where chinese>70 and english>70 and math>70;
select name,class from GRADE where (math>60 or chinese>60) and english>70;
select class,count(id) from GRADE group by class;
select class,max(english) from GRADE group by class;
alter table GRADE add(history int(5),brith date,address varchar(20));