MySQL作业分享2.0

付出总会跟收获成正比的，加油加油拉

1、查询1833班信息的2到4行的数据  

答：select * from student where class=1833 limit 1,3;

2、显示班级为空的id和姓名、和数学分数

答：select id,name,math from student where class is null;

3、统计每个班级人数

答：select class,count(*)qty from student group by class;

4、1833班数学成绩最大的ID年龄和姓名

答： 1，select max(math),id,age,name from student where class=1833;

2， select id,age,name from student where class=1833 order by math desc limit 1;

5、求数学分最小的班级 ID年龄和姓名

答：1，select id,age,name,class from student where math=(select min(math)from student);

2，select id,age,name,class from student order by math asc limit 1;

6、求1833班数学分总和

答：select sum(math) from student where class=1833;

7、求所有班级分数总和

答：select sum(math) from student;

8、求年纪最大的班级并显示班级年龄和姓名分数

答：1，select age,name,class,math from student where age=(select max(age)from student);

2，select class,age,name,math from student order by age desc limit 1;

9、统计sex1和0个总数

答：select count(*)qty from student group by sex;

10、求出所有班级年纪平均数

答：select avg(age)from student;

11、求出1835班年纪的平均数

答：select avg(age)from student where class=1835;

12、求出1833班年纪的平均数

答：select avg(age)from student where class=1833;

13、将所有数据按照年纪进行降序后显示年纪姓名和班级

答：select age,name,class from student order by age desc;

14、将所有数据按照年纪升序显示年纪姓名班级和数学分数

答：select age,name,class,math from student order by age asc;

15、按照班级将进行分组

答：select class,count(*)from student group by class;

16、根据age字段进行降序排序；

答：select * from student order by age desc;

17、根据math字段进行升序排序，并显示前5行所有数据；

答：select * from student order by math asc limit 5;

18、把lisi的数学成绩改为69分

答：update student set math=69 where id=2;

19、查找性别不为1的所有数据

答：select * from student where sex!=1;

20、只显示表中姓名，且将相同的姓名名称去重

答：select name from student group by name;

21、统计表中行数

答： select count(*) from student;

22、统计年纪在27岁的有多少

答：select count(*)qty from student where age=27;

23、统计年纪大于25小于35的有多少

答：select count(*)qty from student where age>25 and age<35;

24、求数学分总和

答：select sum(math)from student;

25、求分数最小

答：select min(math)from student;

26、求平均分

答：select avg(math)from student;

27、只显示3-8行的数据

答：select * from student limit 2,6;

28、查找姓名尾号为qi的所有数据

答：select * from student where name like '%qi';

29、查询姓名开头为xiao的所有数据

答：select * from student where name like 'xiao%';

30、查询中间值为ao开头的所有数据

答：select * from student where name like '%ao%';