查询数据库结构-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