子查询和嵌套查询

        查询数据库结构-1的所有考试结果（学号，科目编号，成绩），降序排列

方式一： 使用连接查询

SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '数据库结构-1'
ORDER BY `studentresult` DESC

 

方法二：使用子查询（由里及外）

•  查询所有数据库结构-1 的学生学号

SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE subjectno = (
SELECT subjectno FROM 'subject'
WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC

• 分数不小于80分的学生的学号和姓名 

SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON r.`studentno`=s.`studentno`
WHERE `studentresult`>=80

• 在这个基础上增加一个科目，高等数学-2
• 查询高等数学-2的编号

SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON r.`studentno`=s.`studentno`
WHERE `studentresult`>=80 AND `subjectno` =(
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '高等数学-2'
)

• 查询课程为高等数学-2且分数不小于80的同学的学号和姓名
• 查询 高等数学-2的编号

SELECT s.`studentno`,`studentname`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-2' AND `studentresult`>=80

• 在改造（由里及外）
• 子查询

SELECT `studentno`,`studentname` FROM `student` WHERE `studentno` IN(
  SELECT `studentno` FROM `result` WHERE `studentresult`>80 AND `subjectno` =(
     SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-2'
  
	)

)

---

分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同的课程分组）
SELECT SubjectName,AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result r
INNER JOIN `subject` AS sub
ON r.`subjectNo` = sub.`subjectNo`
GROUP BY r.subjectNo -- 通过什么字段来分组
HAVING 平均分 > 80