mysql子查询应用案例_mysql子查询应用实例

例1：编写SQL语句，查看年龄比“李斯文”小的学生，要求显示这些学生的信息

1.查询李斯文的出生日期

SELECT borndate FROM student WHERE studentname='李斯文'

2.查询出生日期比李斯文大的学生

SELECT *

FROM student

WHERE borndate>(

SELECT borndate FROM student

WHERE studentname='李斯文'

)

例2：查询“Logic Java”课程至少一次考试刚好等于60分的学生

1)查询logic java课程并且分数是60分的学号

SELECT studentno

FROM result r

INNER JOIN SUBJECT j ON r.subjectno=j.`subjectNo`

WHERE j.subjectname='Java logic' AND r.studentresult=60;

2)查询logic java课程并且分数是60分的学生名字

SELECT studentno,studentname FROM student

WHERE studentno in(

SELECT studentno

FROM result r

INNER JOIN SUBJECT j ON r.subjectno=j.subjectno

WHERE j.subjectname='Java logic' AND r.studentresult=60

)

例3：查询参加最近一次Logic Java考试成绩的学生的最高分和最低分

1)查询java logic课程编号

SELECT subjectno FROM SUBJECT WHERE subjectname='java logic'

2)查询最近一次Logic Java考试

SELECT MAX(examdate) FROM result

WHERE subjectno IN(SELECT subjectno FROM SUBJECT WHERE subjectname='java logic')

3)查询最近一次Java Logic考试成绩的学生的最高分和最低分

SELECT MAX(studentresult) 最高分,MIN(studentresult) 最低分

FROM result

WHERE subjectno IN(SELECT subjectno FROM SUBJECT WHERE subjectname='java logic') AND examdate IN(

SELECT MAX(examdate) FROM result

WHERE subjectno IN(SELECT subjectno FROM SUBJECT WHERE subjectname='java logic')

)