学生成绩表mysql_mysql 学生成绩表例题

#查询姓张的每个学生的最低分大于60的学号和姓名

SELECT r.studentno,studentname

FROM result r

INNER JOIN student s

ON r.`studentno`=s.`studentno`

WHERE s.`studentname` LIKE '张%'

GROUP BY r.`studentno`

HAVING MIN(r.`score`)>60;

#查询生日在'1988-1-1'后的学生的姓名和专业名称

SELECT studentname,m.`majorname`

FROM student s

INNER JOIN major m

ON s.`majorid`=m.`majorid`;

WHERE DATEDIFF(borndate,'1988-1-1')>0;

#查询每个专业的男生人数和女生人数分别是多少

SELECT COUNT(*),sex,majorid

FROM student

GROUP BY sex,majorid;

或

SELECT (SELECT COUNT(*) FROM student WHERE sex='男' AND s.majorid=majorid ),

(SELECT COUNT(*) FROM student WHERE sex='女' AND s.majorid=majorid),

majorid

FROM student s

GROUP BY majorid;

#查询专业和张翠山一样的学生的最低分

SELECT MIN(score)

FROM result r

WHERE r.`studentno`IN

(SELECT studentno

FROM student s

WHERE s.`majorid` =(

SELECT majorid

FROM student

WHERE studentname='张翠山'

)

)

#查询大于60分的学生的姓名、密码、专业名

SELECT studentname,loginpwd,majorname

FROM student s

INNER JOIN major m

ON s.`majorid`=m.`majorid`

INNER JOIN result r

ON r.`studentno`=s.`studentno`

WHERE r.`score`>60;

#按邮箱位数分组，查询每组的学生个数

SELECT COUNT(*)

FROM student

GROUP BY LENGTH(email);

#查询学生名、专业名、分数

SELECT studentname,majorname,score

FROM student s

INNER JOIN major m

ON s.`majorid`=m.`majorid`

INNER JOIN result r

ON r.`studentno`=s.`studentno`;

#查询哪个专业没有学生，分别用左连接和右连接实现

SELECT majorname

FROM major m

LEFT OUTER JOIN student s

ON m.`majorid`=s.`majorid`

WHERE s.`majorid` IS NULL;

#查询没有成绩的学生人数

SELECT COUNT(*)

FROM student s

LEFT JOIN result r ON s.studentno=r.studentno

WHERE r.id IS NULL;