select语句练习题（一）

========================================================


1、查询1833班信息的2,4行的数据ues
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(*) from student group by class;

4、1833班数学成绩最大的ID年龄和姓名
select id,age,name from student where class=1833 and math in(select max(math) from student where class=1833);

5、求数学分最小的班级 ID年龄和姓名
select  id,age,name from student where math=(select min(math) from student);

6、求1833班数学分总和
select sum(math) from student where class=1833;

7、求所有班级分数总和
select sum(math) from student;

8、求年纪最大的班级并显示班级年龄和姓名分数
select age,name,math from student where age=(select max(age) from student)；

9、统计sex1和0个总数
select sex,count(*) 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 * from student order by age desc;

14、将所有数据按照年纪升序显示年纪姓名班级和数学分数
select  class,math from student order by age asc;

15、按照班级将进行分组
select class,count(*) from student group by class;

16、根据age字段进行降序排序；
select * from student where age order by age desc;
select * from student order by age desc;

17、根据math字段进行升序排序，并显示前5行所有数据；
select * from student where math order by math asc limit 5;

18、把lisi的数学成绩改为69分
update student set math=69 where name='lisi';

19、查找性别不为1的所有数据
select * from student where sex !=1;
select * from student where sex<>1;

20、只显示表中姓名，且将相同的姓名名称去重
select distinct(name) from student;
select name from student group by name; --该语句会把name重新排序

21、统计表中行数
select count(*) from student;

22、统计年纪在27岁的有多少
select count(*) from student where age=27;

23、统计年纪大于25小于35的有多少
select count(*) 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%';