1.17 mysql

建表
create table grade(sid int(2) primary key auto_increment,name varchar(20),age int(3),class int(5),chinese int(3),english int(3),math int(3));
增加表数据
insert into grade (sid,name,age,class,chinese,english,math)values(1,"zhangsan",21,1833,86,90,40),(2,"lisi",22,1832,55,86,66),(3,"wangwu",23,1832,84,90,88),(4,"zhaoliu",24,1833,93,57,98),(5,"liqi",25,1833,93,57,22),(6,"niuqi",26,1832,84,98,77),(7,"liuli",27,1832,56,57,77),(8,"wangbo",28,1833,48,58,88),(9,"wangsan",29,1832,78,57,88),(10,"wangan",30,1833,87,60,65),(11,"wangping",31,1832,80,76,88),(12,"wanghui",32,1833,null,79,88);

1、查询1832班的成绩信息
select chinese,english,math from grade where class=1833;

2，查询1833班，语文成绩大于80小于90的成绩信息
select chinese,english,math from grade where class=1833 and chinese>80 and chinese<90;


3，查询学生表中5到10行的数据
select * from grade limit 4,6;


4，显示1832班英语成绩为98，数学成绩为77的姓名与学号,
select name from grade where class=1832 and english=98 and math=77;


5，查询出1832班成绩并且按语文成绩排序（降序）
select chinese,english,math from grade where class=1832 order by chinese desc;


6，查询1833班与1832班，语文成绩与数学成绩都小于80的姓名。
select name from grade where (class=1833 or class=1832) and chinese<80 and math<80;


7，查询出没有参加语文考试的学生姓名和班级名称。
select name,class from grade where chinese is null;



8，求出班上语文成绩不及格的学生姓名
select name from grade where chinese<60;


9，求出每个班的数学平均成绩
select class,avg(math) from grade group by class;



10、求出每个班级语文成绩总分 --涉及到每个的时候都需要分组
select sum(chinese) from grade group by class;


11、将语文成绩不及格的学生成绩改为60分
update grade set chinese=60 where chinese<60;


12、三科分数都大于70分的人名和年纪
select name,age from grade where chinese>70 and english>70 and math>70;


13、求出英语分数高于70且其它任何一科目大于60分的人和班级
select name,class from grade where english>70 and chinese>60 and math>60;


14、统计每个班的人数
select class,count(*) from grade group by class;


15、求每个班数学成绩大于80的人数
select class,count(*) from grade where math>80 group by class ;


16、求出每个班英语成绩最高的那个人的姓名和班级名称 --每个班英语成绩 最高
select name,class,english from grade where (english,class) in (select max(english),class from grade group by class) ;