exists用法

• exists 子查询找到的提交

• not exists 子查询中找不到的提交

• exists 是一个动态的查询过程，如for循环

• exists 的执行流程exists首先执行外层查询，再执行内层查询，与in相反。流程为首先取出外层中的第一元组，在执行内层查询，将外层表的第一元组带入，若内层查询为真，既有结果。返回外层表中的第一元组，接着取出第二元组，执行相同的算法，一直到扫描完外层表。

for(int i=0;i<>EOFout;i++){
    for(int j=0;j<>EOFout;j++)
}

• 学生表student(sno,sname),课程表course(cno,cname),选课表sc(sno,cno)
• 要求查询出：选择了全部课程的学生姓名

select s.sname from student where not exists(select * from course c where not exists(select * from sc where sc.sno=s.sno and sc.cno=c.cno)) select sname from student where sno in(select sno from sc group by sno having count()=(select count(*)from course)

• 查找学号为00003没有选修的科目

• select cname from course where not exists (selec * from sc where sc.cno=course.cno and sno=00003)

• 查找一门课也没有选的学生(不存在，它至少选择了一门课)

select s.sname from student where not exists (select * from course where exists (select * from sc where sc.sno=s.sno and sc.cno=course.cno))

• 查询没有选择所有课程的学生(存在，至少有一门课程没选)

select sname from student where exists (select * from course where not exists (select * from sc where sc.sno=student.sno and course.cno=sc.cno));

• 查询至少选修了一门课程的学生

select s.sname from student s where exists (selct * from course where exists (select * from sc where sc.sno=s.sno andr sc.course =course.cno))