MySQL基础训练50题之41~48

统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列， 若人数相同，按课程号升序排列

SELECT c_id, COUNT( distinct s_id) num FROM score GROUP BY c_id HAVING num>5 ORDER BY num DESC,c_id ASC

检索至少选修两门课程的学生学号

SELECT s_id ,COUNT(*) 选课数量 FROM score GROUP BY s_id HAVING 选课数量 >=2

查询选修了全部课程的学生信息

SELECT s.* FROM student s, (SELECT s_id,COUNT(*) AS a FROM score GROUP BY s_id HAVING a = ( SELECT COUNT(*) FROM course))r WHERE s.s_id = r.s_id

查询各学生的年龄

select s_id,s_name,TIMESTAMPDIFF(YEAR,s.s_birth,CURDATE()) "年龄" from student s

查询本周过生日的学生

select s_id,s_name from student where week(concat(substring(now() from 1 for 4),substring(s_birth,5)))=week(now())

查询下周过生日的学生

select s_id,s_name from student where week(concat(substring(now() from 1 for 4),substring(s_birth,5)))=week(now())+1 select s_id,s_name from student where week(concat(substring(now() from 1 for 4),substring(s_birth,5)))=week(now())+1

查询本月过生日的学生

select s_id,s_name from student where month(s_birth)=month('2020-12-1')SELECT c_id,COUNT(c_id) FROM score GROUP BY c_id

查询下月过生日的学生

select s_id,s_name from student where month(s_birth)=month('2020-7-1')+1