子查询

子查询

-- ===================== where =============================
-- 1、查询数据库结构-1的所有考试结果(学号，科目编号，成绩)，降序排列
-- 方式一：使用连接查询
SELECT r.`studentno`,r.`subjectno`,`studentresult`
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname`='数据库结构-1' 
ORDER BY studentResult DESC


-- 方式二：使用子查询(由里及外)
-- 查询所有数据库结构-1的学生学号
SELECT r.`studentno`,r.`subjectno`,`studentresult`
FROM result r
WHERE subjectno =(
SELECT subjectno FROM `subject` 
WHERE `subjectname`='数据库结构-1'
)

 -- 查询课程为高等数学-2  且 分数不小于80分学生的学号和姓名
 SELECT s.`studentno`,`studentname`
 FROM `student` s
 INNER JOIN `result` r
 ON s.`studentno` = r.`studentno`
 INNER JOIN `subject` sub
 ON r.`subjectno` = sub.`subjectno`
 WHERE sub.`subjectname` = '高等数学-2' AND `studentresult`>=80
 
 
-- 分数不小于80分的学生的学号和姓名
 SELECT DISTINCT s.`studentno`,`studentname`
 FROM student s
 INNER JOIN result r
 ON s.`studentno`=r.`studentno`
 WHERE `studentresult`>=80
 
 -- 在这个基础上增加一个科目，高等数学-2 
 -- 查询高等数学-2 的编号
  SELECT DISTINCT s.`studentno`,`studentname`
 FROM student s
 INNER JOIN result r
 ON s.`studentno`=r.`studentno`
 WHERE `studentresult`>=80 AND `subjectno` = (SELECT `subjectno` FROM `subject`
 WHERE `subjectname`='高等数学-2')
 
 
-- 再改造(由里及外)
SELECT `studentno`,`studentname` FROM `student` WHERE `studentno`=(
SELECT `studentno` FROM `result` WHERE `studentresult`>=80 AND `subjectno`=(
SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-2'
)
)