SQL子查询案例

-- 子查询中：查询比“李斯文”年龄大的学生的姓名和出生日期
SELECT `studentName` AS "姓名", `bornDate` AS "出生日期"
FROM student -- 外层查询
WHERE bornDate<(-- 子查询的结果必须 <=1
-- 内层查询
    SELECT bornDate FROM student 
    WHERE studentName ="李斯文"
);

-- 查询最近一次考试 html 考试学生的最高分和最低分
SELECT MAX(`studentResult`) AS 最高分,MIN(`studentResult`) AS 最低分
FROM `result`

WHERE `examDate`=(

SELECT MAX(`examDate`) FROM `result` 
WHERE `subjectNo`=(
    SELECT `subjectNo` FROM `subject`
    WHERE `subjectName` = "语文"
)
); 
 
-- 子查询in(值1,值2....) not in()
-- 查询学号为10000，10001，10002的姓名和性别
SELECT `studentName` AS 姓名 ,sex AS 性别
FROM student
WHERE studentNo IN(10000,10001,10002);