Mysql第三章(高级查询)

/*
检查“Logic Java”课程最近一次考试成绩
如果有80分以上的成绩，显示分数排在前5名的学员学号和分数
*/
SELECT studentNo,studentresult
FROM result
WHERE EXISTS( 
	SELECT * FROM result
	WHERE examDate=(
	SELECT MAX(examDate)
	FROM result 
	WHERE subjectNo=(
	SELECT subjectNo
	FROM `subject`
	WHERE subjectName='Logic Java'
	) )
	
AND studentResult>80
)
AND subjectNo=(
	SELECT subjectNo
	FROM `subject`
	WHERE subjectName='Logic Java')
AND examDate=(
	SELECT MAX(examDate)
	FROM result 
	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 result 
	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 result 
	WHERE subjectNo=(
	SELECT subjectNo
	FROM `subject`
	WHERE subjectName='Logic Java'))
	AND studentResult>100
	)

常用的多表链接查询

# 查询学生的考试成绩 显示学生姓名，科目ID 成绩
#内联

SELECT studentName,subjectNo,studentResult
FROM student AS s
INNER JOIN result AS r ON s.studentNo=r.studentNo

#直联
SELECT studentName,subjectNo,studentResult
FROM student AS s, result AS r
WHERE s.stud