1 一、查询每个专业的学生人数 2 SELECT COUNT(*) 3 FROM student 4 GROUP BY majorid; 5 6 二、查询参加考试的学生中，每个学生的平均分、最高分 7 SELECT avg(score),MAX(score),studentno 8 FROM result 9 GROUP BY stduentno; 10 11 三、查询姓张的每个学生的最低分大于60的学号、姓名 12 SELECT s.studentid,s.studentname,MIN(score) 13 FROM student s 14 JOIN result r 15 on s.studentno=r.studentno 16 where s.studentname LIKE '张%' 17 GROUP BY studentno 18 HAVING min(score)>60; 19 20 四、查询专业生日在“1988-1-1”后的学生姓名、专业名称 21 SELECT studentname,majorname,borndate 22 FROM student s 23 join major m 24 on s.majorid=m.majorid 25 where DATEDIFF(borndate,'1988-1-1')>0; 26 27 #五、查询每个专业的男生人数和女生人数分别是多少 28 #方式一： 29 SELECT COUNT(*) 个数,majorid,sex 30 FROM student 31 GROUP BY sex,majorid; 32 33 方式二： 34 SELECT majorid, 35 (SELECT COUNT(*) FROM student where sex='男' and majorid=s.majorid) 男, 36 (SELECT COUNT(*) FROM student where sex='女' and majorid=s.majorid) 女 37 FROM student s 38 GROUP BY majorid 39 40 41 六、查询专业和张翠山一样的学生的最低分 42 SELECT MIN(score) 43 FROM result 44 where studentno in ( 45 SELECT studentno 46 FROM student 47 WHERE mojorid=(SELECT majorid 48 FROM student 49 where studentname='张翠山')); 50 51 七、查询大于60分的学生的姓名、密码、专业名 52 SELECT studentname,loginpwd,majorname 53 FROM student s 54 join result r 55 on s.studentno=r.studentno 56 JOIN major m 57 on s.majorid=m.majorid 58 where r.score>60; 59 60 八、按邮箱位数分组，查询每组的学生个数 61 SELECT COUNT(*) 62 FROM student 63 GROUP BY LENGTH(email); 64 65 九、查询学生名、专业名、分数 66 SELECT studentname,majorname,score 67 FROM student s 68 JOIN major m 69 on s.majorid=m.majorid 70 JOIN s.studentno=r.studentno; 71 72 十、查询哪个专业没有学生，分别用左连接和右连接实现 73 SELECT majorid,majorname 74 from major m 75 LEFT JOIN student s 76 on m.majorid=s.majorid 77 where s.studentno is null 78 79 十一、查询没有成绩的学生人数 80 SELECT COUNT(*) 81 FROM student s 82 LEFT JOIN result r on s.studentno=r.studentno 83 where r.id is null