数据库应用（MySQL）---实验1--单表统计

1.根据表xsxxb统计全校王姓学生的学生数

select count(*)
from xsxxb
where substr(xm,1,1)='王'

2.根据表xsxxb统计每种姓氏的学生数，并按学生数降序排列

select substr(xm,1,1),count(*)
from xsxxb
group by substr(xm,1,1)
order by count(*) desc

3.根据表xsxxb按姓名长度统计学生数

select length(xm),count(*)
from xsxxb
group by length(xm)

4.根据表xsxxb统计每个班的学生数

select bjmc,count(*)
from xsxxb
group by bjmc

5.根据表xsxxb按性别统计学生数

select substr(sfzh,17,1)%2,count(*)
from xsxxb
group by substr(sfzh,17,1)%2

6.根据表xsxxb统计每个班的男生的学生数

第一种
select bjmc,count(*)
from xsxxb
where substr(sfzh,17,1)%2=1
group by bjmc

第二种
select bjmc,sum(substr(sfzh,17,1)%2)
from xsxxb
group by bjmc

7.根据表xsxxb统计每个班的入学成绩的平均值

select bjmc,avg(rxcj)
from xsxxb
group by bjmc

8.根据表xsxxb按出生年份统计学生数

select substr(sfzh,7,4),count(*)
from xsxxb
group by substr(sfzh,7,4)

9.根据表xsxxb按年龄统计学生数

select substr(now(),1,4)-substr(sfzh,7,4),count(*)
from xsxxb
group by substr(now(),1,4)-substr(sfzh,7,4)

10.根据表cjb统计每个学生的平均成绩

select xh,avg(cj)
from cjb
group by xh

11.根据表cjb统计每门课程的平均成绩、最高成绩、最低成绩

select kcmc,avg(cj),max(cj),min(cj)
from cjb
group by kcmc

12.根据表cjb统计每学期的平均成绩、最高成绩、最低成绩

select xnxq,avg(cj),max(cj),min(cj)
from cjb
group by xnxq

13.根据表cjb统计每学期每门课程的平均成绩、最高成绩、最低成绩，并按学期、平均成绩排序

select xnxq,kcmc,avg(cj),max(cj),min(cj)
from cjb
group by xnxq,kcmc
order by xnxq,avg(cj)

14.根据表cjb统计每门课程的平均成绩、最高成绩、最低成绩，并按平均成绩降序排列

select kcmc,avg(cj),max(cj),min(cj)
from cjb
group by kcmc
order by avg(cj) desc

15.根据表bjxxb统计每个学院的班级数

select xymc,count(*)
from bjxxb
group by xymc

16.根据表bjxxb统计每个专业的班级数

select zymc,count(*)
from bjxxb
group by zymc

17.根据表bjxxb统计每个年级的班级数

select nj,count(*)
from bjxxb
group by nj

18.根据表bjxxb统计每个学院每个年级的班级数

select xymc,nj,count(*)
from bjxxb
group by xymc,nj

19.根据表bjxxb统计每个学院每个专业的班级数

select xymc,zymc,count(*)
from bjxxb
group by xymc,zymc

20.根据表bjxxb统计每个年级每个专业的班级数

select nj,zymc,count(*)
from bjxxb
group by nj,zymc

21.根据表bjxxb统计每个学院每个年级每个专业的班级数

select xymc,nj,zymc,count(*)
from bjxxb
group by xymc,nj,zymc

22.根据表bjxxb统计除2012级以外每个学院每个专业的班级数

select xymc,zymc,count(*)
from bjxxb
where nj!=2012
group by xymc,zymc

23.根据表bjxxb统计除2012级以外每个学院每个年级每个专业的班级数

select xymc,nj,zymc,count(*)
from bjxxb
where nj!=2012
group by xymc,nj,zymc

24.根据表bjxxb统计每个年级每个专业的班级数，同时剔除掉班级数少于10的记录

select nj,zymc,count(*)
from bjxxb
group by nj,zymc
having count(*)>=10

25.根据表xsxxb统计每个班的学生数、男生学生数、女生学生数、入学平均成绩、
select bjmc,count(*),
sum(substr(sfzh,17,1)%2),
sum((substr(sfzh,17,1)+1)%2),
avg(rxcj)
from xsxxb
group by bjmc