SQL语句中exists/not exists的用法分析

系统管理员 2021-01-22 17:38 2620阅读 0赞

1、Student：

| Sno       | Sname | Ssex | Sage | Sdept |
| --------- | ----- | ---- | ---- | ----- |
| 200215121 | 李勇  | 男   | 20   | CS    |
| 200215122 | 刘晨  | 女   | 19   | CS    |
| 200215123 | 王敏  | 女   | 18   | MA    |
| 200215124 | 张立  | 男   | 19   | IS    |

2、Course：

| Cno  | Cname      | Cpno | Ccredit |
| ---- | ---------- | ---- | ------- |
| 1    | 数据库     | 5    | 4       |
| 2    | 数学       |      | 2       |
| 3    | 信息系统   | 1    | 4       |
| 4    | 操作系统   | 6    | 3       |
| 5    | 数据结构   | 7    | 4       |
| 6    | 数据处理   |      | 2       |
| 7    | PASCAL语言 | 6    | 4       |

3、SC：

| Sno       | Cno  | Credit |
| --------- | ---- | ------ |
| 200215121 | 1    | 92     |
| 200215121 | 2    | 85     |
| 200215121 | 3    | 88     |
| 200215122 | 2    | 90     |
| 200215122 | 3    | 80     |

例1：查询所有选修了1号课程的学生的姓名。

解法1：利用exists

首先取Student表中的一个元组，然后在SC表中依次找SC.Sno=该元组的Sno，并且对应的Cno='1'，如果存在，则外层查询的where子句返回为真，则Student表中的该元组可以输出。然后依次遍历Student表中的其他元组。

举个例子：对于在学生表中学号等于2002151121这个元组，在SC表中第一条记录即符合条件，然后where 子句返回 true，所以该条元组可以输出。然后依次遍历。
````
select Sname
from  Student
where exists
(
      select *
      from SC
      where Sno = Student.Sno AND Cno='1'
);
````
解法2：利用连接查询
````
select Sname
from Student,SC
where Student.Sno=SC.Sno AND SC.Cno='1';
````
例2：查询没有选修1号课程的学生的姓名。

解：
````
select Sname
from Student
where not exists
(
     select *
     from SC
     where Sno=Student.Sno  AND   Cno='1'
);
````
例3：查询选修了全部课程的学生姓名。
```
select Sname
from Student
where not exists
          (
                 select *
                 from Course
                 where not exists
                           (
                                 select *
                                 from  SC
                                 where Sno=Student.Sno AND
                                            Cno=Course.Cno
                           ) );
````
查找语义：查询这样的学生，没有一门课程是他不选修的。

查找过程：

首先，选取Student表中的一个元组，然后在依次判断Course表中的每个元组是否可以输出，只要有一个课程可以输出，则最外层查询的where子句返回为false；而在判断某个课程是否可以输出时，则要利用第三层查询，利用当前的学号和当前的课程号，在SC表中查询，如果存在，则第二层查询where子句返回false。至此，每一门课程都不可以输出时，这个学号对应的元组才可以输出。表示这个学生选修了全部的课程。

例4：至少选修了学生200215122选修的全部课程的学生号码。
````
select distinct Sno
from SC SCX
where not exists
          (
              select * 
              from SC SCY
              where SCY.Sno='2002151122'  AND
                        not exists
                        (
                              select *
                              from SC SCZ
                              where SCZ.Sno=SCX.Sno  AND
                                        SCZ.Cno=SCY.Cno));
````
查询语义：不存在这样的课程y，学生200215122选修了y，而学生x没选。

查询过程：先在SCX表中选一条记录，比方说第一条，然后再看SCY表中，只有SCY表中全部不能输出，第一层查询的where子句才返回true，第一条记录就可以输出；所以就要一次查看SCY表中的每一个元组，前三个，因为学号首先不满足=200215122所以必然不能输出，第四个的话，就要看其AND后面的not exists返回什么值，而这又取决于第三层查询中是否存在满足学号等于SCX.Sno且课程号=SCY.Cno的元组，经查看，有 ，则返回false，所以第四个也不能输出，第五个类似，所以，第一层查询的not exists返回true。所以第一条记录可以输出。