班级学生成绩表ab:
数学:math 英语:eng 语文:chine
班级:1832,1833
性别:1,2
biological :物理 chemical :化学 physical:生物
1、查询1832班的成绩信息
mysql> select name,class,math,eng,chine from ab where class=1832;
或:select * from ab where class=1832;
2、查询1833班,语文成绩大于80小于90的成绩信息
mysql> select name,class,chine from ab where class=1833 and 80<chine<90;
或:select * from ab where class=1833 and 80<chine<90;
3、查询学生表中5到10行的数据
mysql> select * from ab limit 4,6;
4、显示1832班英语成绩为98,数学成绩为77的姓名与学号
mysql> select id,name,class,math,eng from ab where class=1832 and math=77 and eng=98;
select id,name from ab where class=1832 and math=77 and eng=98;
5,查询出1832班成绩并且按语文成绩排序(降序)
mysql> select class,name,chine,math,eng from ab where class=1832 order by chine desc;
select * from ab where class=1832 order by chine desc;
6、查询1833班与1832班,语文成绩与数学成绩都小于80的姓名
mysql> select name,class,math,chine from ab where math<80 and chine<80;
select name from ab where math<80 and chine<80;
7、查询出没有参加语文考试的学生姓名和班级名称
mysql> select * from ab where chine is null;
select name,class from ab where chine is null;
8、求出班上语文成绩不及格的学生姓名
mysql> select name from ab where chine<60 or chin is null;
9、求出每个班的数学平均成绩;
mysql> select class,avg(math) from ab group by class;
10、求出每个班级语文成绩总分 --涉及到每个的时候都需要分组
mysql> select class,sum(chine) from ab group by class;
11、将语文成绩不及格的学生成绩改为60分
mysql> update ab set chine="60" where chine=55;
mysql> update ab set chine="60" where chine is null;
mysql> update ab set chine="60" where chine is null;
改:update ab set chine="60" where chine<60;
12、三科分数都大于70分的人名和年纪
mysql> select name,age,math,eng,chine from ab where math and eng and chine>70;
改:select name,age from ab where math and eng and chine>70;
13、求出英语分数高于70且其它任何一科目大于60分的人和班级
mysql> select name,class from ab where eng>70 and math>60 or chine>60;
select * from ab where eng>70 and math>60 or chine>60;
14、统计每个班的人数
mysql> select count(*),class from ab group by class;
15、求每个班数学成绩大于80的人数
mysql> select count(math) from ab where math>80 group by class;
select count(class) from student group by class;
16、求出每个班英语成绩最高的那个人的姓名和班级名称 --每个班英语成绩 最高
mysql>select class,name,max(eng) from ab group by class;
mysql> select class,name,max(eng) from ab group by class;
17、给表增加3个字段(数据类型及长度自定义,建议要合理)
mysql> alter table ab add(biological int(4),chemical int(4),physical int);