mysql 上个月 汇总_MySQL-汇总分析

汇总分析相关知识点

汇总分析语法练习

/*查询课程编号为“002”的总成绩*/

select sum(成绩) from course where 课程号='0002';

/*查询选了课程的学生人数*/

select count(distinct 学号) FROM COURSE;

/*查询各科成绩最高和最低的分*/

select 课程号,MAX(成绩),MIN(成绩)from course group by 课程号;

/*查询每门课程被选修的学生人数*/

select count(学号),课程号 from course GROUP BY 课程号;

/*查询男生，女生人数*/

select count(学号),性别 from student GROUP BY 性别;

/*查询平均成绩大于80分学生的学号和平均成绩*/

select 学号,avg(成绩) from course GROUP BY 学号 HAVING AVG(成绩)>='70';

/*查询至少选修两门课程的学生学号*/

SELECT 学号,count(课程号) FROM COURSE GROUP BY 学号 HAVING COUNT(课程号)>1;

/*查询同名同姓学生名单并统计同名人数*/

SELECT 姓名,COUNT(学号) FROM STUDENT GROUP BY 姓名;

SELECT 课程号,AVG(成绩) FROM COURSE GROUP BY 课程号 HAVING AVG(成绩)>=80;

/*查询小于80分的课程并按照课程号从小到大排列*/

SELECT 课程号 FROM COURSE where 成绩

/*查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排序*/

SELECT 课程号, AVG(成绩) FROM COURSE GROUP BY 课程号 ORDER BY AVG(成绩) ASC;