查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名SELECT student.SId, student.SnameFROM student, scWHERE student.SId = sc.SIdAND sc.CId = '01'AND sc.score > 80求每门课程的学生人数SELECT sc.CId, count(sc.SId)FROM scGROUP BY sc.CId成绩不重复，查询选修「」老师所授课程的学生中，成绩最高的学生信息及其成绩SELECT student.*, sc.score, sc.CIdFROM student, scWHERE sc.CId = (SELECT course.CIdFROM course, teacherWHERE course.TId = teacher.TIdAND teacher.Tname = '')AND student.SId = sc.SIdORDER BY sc.score DESCLIMIT 1成绩有重复的情况下，查询选修「」老师所授课程的学生中，成绩最高的学生信息及其成绩SELECT * FROM(SELECT student.*, sc.score, sc.CId, DENSE_RANK() OVER (ORDER BY sc.score DESC)排名FROM student, scWHERE sc.CId = (SELECT course.CIdFROM course, teacherWHERE course.TId = teacher.TIdAND teacher.Tname = '')AND student.SId = sc.SId) as tWHERE t.排名='1'查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩SELECT sc.SId, sc.CId, sc.scoreFROM scWHERE sc.score in (SELECT score FROM(SELECT sc.score, count(sc.score) as count_peopleFROM scGROUP BY sc.score) AWHERE A.count_people > 1)SELECT s,c,scoreFROM(SELECT max(score),avg(score),COUNT(score),student.s,c,scoreFROM studentLEFT JOIN scON student.s=sc.sGROUP BY student.sHAVING max(score)=avg(score) AND COUNT(score)>=2) A;查询每成绩最好的前两名SELECT *FROM (SELECT sc.SId, sc.CId, sc.score, row_number() over (PARTITION BY sc.CId ORDER BY sc.score DESC)排名FROM scORDER BY sc.score DESC) AWHERE 排名 <= 2ORDER BY A.CId, A.排名统计每门课程的学生选修人数（超过 5 人的课程才统计)SELECT *FROM (SELECT sc.CId, count(sc.SId) as count_peopleFROM scGROUP BY sc.CId) AWHERE A.count_people > 5SELECT sc.CId,COUNT(sc.CId)FROM scGROUP BY sc.CIdHAVING COUNT(sc.CId)>5 ;检索至少选修两门课程的学生学号SELECT sc.SId, COUNT(sc.SId)FROM scGROUP BY sc.SIdHAVING COUNT(sc.SId) >= 2查询各学生的年龄，只按年份来算SELECT SId, Sname, (year(curdate())-year(Sage)) as ageFROM studentORDER BY age按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一SELECT SId, Sname,timestampdiff(year,Sage,curdate())年龄FROM studentORDER BY 年龄;查询本过生日的学生SET @day =8-dayofweek(curdate());SELECT *FROM studentWHERE date_format(Sage, '%m%d')BETWEEN date_format(curdate(),'%m%d')AND date_format (date_add(curdate(),interval @day day), '%m%d');查询下过生日的学生set @day =9-dayofweek(curdate());SELECT *FROM studentWHERE date_format(sage, '%m%d')BETWEEN date_format (date_add(curdate(),interval @day day), '%m%d')AND date_format (date_add(curdate(),interval @day+6 day), '%m%d');查询本月过生日的学生SELECT *FROM studentWHERE date_format(Sage,'%m')=date_format(curdate(),'%m');查询下月过生日的学生SELECT *FROM studentWHERE date_format(Sage,'%m')=date_format(date_add(curdate(),interval 1 month),'%m');