MySQL 数据库 高级子查询的细节问题

1、查询多个符合条件的结果：

in

示例：查询所有《MySQL 数据库》 考试成绩刚好等于90分的学生名单

select studentName from studentInfo where studentID in (select studentID from exam where exam =90 and subjectID =(select subjectID from subject where subjectName ='MySQL数据库'）);

not in

示例：查询所有《MySQL数据库》考试成绩刚好不等于90分的学生名单

select studentName from studentInfo where studentID not in ((select studentID from exam where exam=90 and subjectID =(select subjectID from subject where subjectName ='MySQL暑数据库')));

2、对子查询结果进行甄别，有结果返回true，没结果返回false 并且外层语句将不再进行剩余的查询操作

exists

示例：查询成绩表中科目编号为2的考试成绩中是否存在大于50的学生，如果存在就显示所有参加科目编号为2的考试学生信息

select studentID,exam from exam where subjectID=2 and exists(select studentID from exam where exam>50);

not exists

示例：查询成绩表中科目编号为4的考试成绩中是否存在不及格的学生，如果不存在就显示所有参加科目编号为4的考试学生信息

select studentID ,exam from exam where subjectID=4 and not exists(select studentID from exam where exam<60 and subjectID=4);

3.all、any/some的查询：

示例：查询成绩比科目编号为“1"的这门课程的所有成绩都大的学生考试成绩

select * from exam where exam>all (select exam from exam where subjectID=1);

查询成绩比科目编号为”1“ 的这门课程的任意一个成绩都大的学生考试成绩

select * from exam where exam>any (select exam from exam where subjectID=1);