CREATETABLEstudent_score(idINTPRIMARYKEYAUTO_INCREMENT,student_nameVARCHAR(20)NOTNULL,subjectVARCHAR(20)NOTNULL,scoreINTNOTNULL,exam_dateDATENOTNULL);-- 插入测试数据INSERTINTOstudent_score(student_name,subject,score,exam_date)VALUES(张三,语文,85,2026-01-15),(张三,数学,92,2026-01-15),(张三,英语,78,2026-01-15),(李四,语文,90,2026-01-15),(李四,数学,88,2026-01-15),(李四,英语,95,2026-01-15),(王五,语文,70,2026-01-15),(王五,数学,65,2026-01-15),(王五,英语,80,2026-01-15),(赵六,语文,88,2026-01-15),(赵六,数学,76,2026-01-15),(赵六,英语,82,2026-01-15),(张三,语文,90,2026-06-15),-- 张三第二次考试(张三,数学,95,2026-06-15),(张三,英语,88,2026-06-15);一、DISTINCT去重 查询所有出现过考试记录的学生姓名去重。SELECTDISTINCTstudent_nameFROMstudent_score;二、ORDERBY排序 查询所有学生的成绩按分数从高到低排序。SELECT*FROMstudent_scoreORDERBYscoreDESC;查询所有成绩先按学生姓名升序再按科目升序最后按分数降序排列。SELECT*FROMstudent_scoreORDERBYstudent_nameASC,subjectASC,scoreDESC;查询数学成绩按分数从高到低排序取前三名。SELECT*FROMstudent_scoreWHEREsubject数学ORDERBYscoreDESCLIMIT3;三、GROUPBY分组聚合函数 统计每个学生的总成绩和平均分。---如果加 GROUP BY, 就是对分组内的所有score求和如果不加 GROUP BY就是对表里的所有score字段求和SELECTstudent_name,SUM(score)AStotal_score,AVG(score)ASavg_scoreFROMstudent_scoreGROUPBYstudent_name;统计每门科目的平均分、最高分、最低分。SELECTsubject,AVG(score)ASavg_score,MAX(score)ASmax_score,MIN(score)ASmin_scoreFROMstudent_scoreGROUPBYsubject;统计每个学生考试的科目数量。SELECTstudent_name,COUNT(*)ASsubject_countFROMstudent_scoreGROUPBYstudent_name;四、GROUPBYHAVING分组后筛选 查询总成绩大于250分的学生。SELECTstudent_name,SUM(score)AStotal_scoreFROMstudent_scoreGROUPBYstudent_nameHAVINGtotal_score250;查询平均分大于85分的学生。SELECTstudent_name,AVG(score)ASavg_scoreFROMstudent_scoreGROUPBYstudent_nameHAVINGavg_score85;查询有3门及以上科目成绩的学生。SELECTstudent_name,COUNT(*)ASsubject_countFROMstudent_scoreGROUPBYstudent_nameHAVINGCOUNT(*)3;五、综合题包含多个关键字5.1查询每个学生的总成绩按总成绩从高到低排序只显示总成绩大于200分的学生。SELECTstudent_name,SUM(score)AStotal_scoreFROMstudent_scoreGROUPBYstudent_nameHAVINGtotal_score200ORDERBYtotal_scoreDESC;5.2查询每门科目的平均分按平均分从高到低排序只显示平均分大于80分的科目。SELECTsubject,AVG(score)ASavg_scoreFROMstudent_scoreGROUPBYsubjectHAVINGavg_score80ORDERBYavg_scoreDESC;5.3查询至少考了3门科目的学生按总成绩从高到低排序显示学生姓名、科目数、总成绩、平均分。SELECTstudent_name,COUNT(*)ASsubject_count,SUM(score)AStotal_score,AVG(score)ASavg_scoreFROMstudent_scoreGROUPBYstudent_nameHAVINGsubject_count3ORDERBYtotal_scoreDESC;5.4查询所有学生的最新一次考试按日期中各科成绩都大于80分的学生姓名去重。SELECTstudent_nameFROMstudent_scoreWHEREexam_date2026-06-15-- 已知最新日期GROUPBYstudent_nameHAVINGMIN(score)80;-- 所有科目都 80最小值也 80
