本质:在where语句中嵌套一个子查询语句
例:
查询数据库结构-1的所有考试结果(学号,科目名,成绩),降序排列
方式一:使用连接查询
SELECT res.`student_no`,res.`subject_no`,res.`student_result`
FROM `result` res
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='高等数学-2'
ORDER BY res.`student_result` DESC;
方式二:使用子查询(由里及外)
SELECT res.`student_no`,res.`subject_no`,res.`student_result`
FROM `result` res
WHERE res.`subject_no` = (
SELECT sub.`subject_no`
FROM `subject` sub
WHERE sub.`subject_name`='高等数学-2'
)
ORDER BY res.`student_result` DESC;
分数不小于80分的学生的学号和姓名
SELECT DISTINCT stu.`student_no`,stu.`student_name`
FROM student stu
INNER JOIN result res
ON stu.`student_no`=res.`student_no`
WHERE res.`student_result` > 80;
在这个基础上增加一个科目,查询课程为高等数学-2,且分数不小于80分的学生的学号和姓名
SELECT DISTINCT stu.`student_no`,stu.`student_name`
FROM student stu
INNER JOIN result res
ON stu.`student_no`=res.`student_no`
WHERE res.`student_result` > 80
AND res.`subject_no`=(
SELECT sub.`subject_no` FROM `subject` sub
WHERE sub.`subject_name`='高等数学-2'
);
SELECT DISTINCT stu.`student_no`,stu.`student_name`
FROM student stu
INNER JOIN result res
ON stu.`student_no`=res.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='高等数学-2'
AND res.`student_result` > 80;
再次改造(由里及外)
SELECT DISTINCT `student_no`,`student_name` FROM student WHERE student_no IN (
SELECT student_no FROM result WHERE `student_result` > 80 AND subject_no = (
SELECT subject_no FROM `subject` WHERE `subject_name`='高等数学-2'
)
);
运行结果: