python之mysql第三节课

#检查“Logic Java”课程最近一次考试成绩
#如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数
SELECT subjectNO FROM `subject` WHERE subjectName='Logic Java'
SELECT MAX(examDate) FROM `subject` 
			WHERE subjectNo=(SELECT subjectNo FROM `subject` 
				WHERE subjectName='Logic Java')
				
SELECT studentNO,studentResult
FROM result
WHERE EXISTS(
	SELECT * FROM result
	WHERE studentResult>80
	AND subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')	
	AND examDate=(SELECT MAX(examDate) FROM `subject` WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java'))
)
AND subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')	
AND examDate=(SELECT MAX(examDate) FROM `subject` WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java'))
ORDER BY studentResult DESC
LIMIT 5 

#检查“Logic Java”课程最近一次考试成绩
#如果全部未通过考试（60分及格），认为本次考试偏难，计算的该次考试平均分加5分

SELECT AVG(studentResult)+5
FROM result
WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')	
AND examDate=(SELECT MAX(examDate) FROM `subject` WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java'))
AND NOT EXISTS(
	SELECT * FROM result
	WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')	
	AND examDate=(SELECT MAX(examDate) FROM `subject` WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java'))
	AND studentResult>100
)
#--------------------------------------------------
/*
训练要点
任何允许使用表达式的地方都可以使用子查询
需求说明
为每个学生制作在校期间每门课程的成绩单，
要求每个学生参加每门课程的最后一次考试成绩作为该生本课程的最终成绩
成绩单的数据项
学生姓名
课程所属的年级名称
课程名称
考试日期
考试成绩
*/
SELECT studentName,r.subjectNo,subjectName,examDate,studentResult
FROM student AS s 
INNER JOIN result AS r ON r.studentNo=s.studentNO
INNER JOIN SUBJECT AS sub ON sub.subjectNo=r.subjectNo
WHERE examDate=(
	s.studentNO
	SELECT MAX(examDate) FROM result
	WHERE studentNo=s.studentNo AND subjectNO=sub.subjectNO
)




#------------------------------------
#分组查询
#统计每门课程平均分各是多少
SELECT subjectNo,AVG(studentResult) AS '平均分'
FROM result
GROUP BY subjectNo
HAVING 平均分>=60
ORDER BY 平均分 DESC
LIMIT 2


#查询每个年级的男女多少
SELECT gradeId,sex,COUNT(*) FROM student 
GROUP BY gradeId,sex

#哪年多少人
SELECT YEAR(bornDate),COUNT(*) FROM student
GROUP BY YEAR(bornDate)

UPDATE student SET sex='男' WHERE(studentNo%2)=0;

#----------------------------------------------------
#查询学生的考试成绩 显示学生姓名 科目id 成绩
#内联
SELECT studentName,s.`studentNo`,gradename,subjectName,studentResult
FROM student AS s
INNER JOIN result AS r ON r.`studentNo`=s.`studentNo`
INNER JOIN `subject` AS sub ON r.`subjectNo`=sub.`subjectNo`
INNER JOIN grade AS g ON g.`gradeId`=s.`gradeId`
#直联
SELECT studentName,sub.`subjectNo`,subjectName,studentResult
FROM student AS s,result AS r,`subject` AS sub,grade AS g
WHERE r. studentNo =s.studentNo
AND r.`subjectNo`=sub.`subjectNo`
AND g.`gradeId`=s.`gradeId`
#外联  左连接
SELECT studentName,s.`studentNo`,studentResult
FROM student AS s
LEFT JOIN result AS r ON r.`studentNo`=s.`studentNo`

#外联  右连接
#跟左连接一样,只不过是以右边为主
SELECT studentName,s.`studentNo`,studentResult
FROM student AS s
RIGHT JOIN result AS r ON r.`studentNo`=s.`studentNo`




SELECT sub.*,g.`gradename`
FROM `subject` AS sub 
LEFT JOIN grade AS g ON g.`gradeId`=sub.`gradeID`