(1)集合
①集合成员资格
注意用括号表示集合:
枚举集合
select * from s where sname in ('张军','王红');
子查询的查询结果,注意(sno,cno)这种用法,子查询中不用这么写
查询哪些同学没有选择哪些课程
select sno,cno
from s,c
where (sno,cno) not in (select sno,cno from sc);
注意:①查询的结果不是单指时就是元组集合,这里需要用in而不是==进行比较。
②枚举集合用(),如(sno,cno),子查询结果也是集合。
②重复元祖unique
找出至少选修了两门课程的学生姓名
select sname from s
where not unique
(select sno from sc where sc.sno = s.sno)
这里也是相关子查询。
(2)子查询
①不相关子查询
子查询不依赖于父查询,执行顺序从最内层开始,由内到外。
--成绩在70分以上的学生信息
select sno, sname from studen
where sno in
(select sno from garde where score=70)
![wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==](https://i-blog.csdnimg.cn/blog_migrate/a57fd336c1ca6278de7d7443917ba70f.png)
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/750a96f1213fde619ab85ab1c8d2e3e5.gif)
![wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==](https://i-blog.csdnimg.cn/blog_migrate/a57fd336c1ca6278de7d7443917ba70f.png)
②相关子查询
子查询依赖父查询,从外层先拿出第一个元组(拿出来的是元组),根据它与内层查询相关的属性值处理内层查询,如果where为真则区该元组放入结果集中,依次取下一个。
--查询选修了c1号课程的学生的学号和姓名。
select sno,sname from s
where exists
(select * from sc
where cno = 'c1'
and sno = s.sno)
![wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==](https://i-blog.csdnimg.cn/blog_migrate/a57fd336c1ca6278de7d7443917ba70f.png)
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/750a96f1213fde619ab85ab1c8d2e3e5.gif)
![wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==](https://i-blog.csdnimg.cn/blog_migrate/a57fd336c1ca6278de7d7443917ba70f.png)
二者区别在于元祖变量的作用域不同。
(3)集合之间比较(符号写在子句前面)
当确切知道子查询结果是单值的时候,子查询一定跟在比较运算符后面。
(4)some(子查询)与all(子查询)
查询比d1系某个学生年龄小的学生信息
SELECT sname,age FROM S
WHERE age < some (SELECT age FROM S WHERE dno= ' d1’)
AND dno <> ' d1' ;
查询比d1系任意学生年龄小的学生信息
SELECT sname,age FROM S
WHERE age < all(SELECT age FROM S WHERE dno= ' d1’)
AND dno <> ' d1' ;
或者
SELECT sname,age FROM S
WHERE age < (SELECT max(age) FROM S WHERE dno= ' d1’)
AND dno <> ' d1' ;
![wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==](https://i-blog.csdnimg.cn/blog_migrate/a57fd336c1ca6278de7d7443917ba70f.png)
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/750a96f1213fde619ab85ab1c8d2e3e5.gif)
![wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==](https://i-blog.csdnimg.cn/blog_migrate/a57fd336c1ca6278de7d7443917ba70f.png)
some,all有时可用聚集函数代替(4)存在谓词
找出平均成绩最高的学生学号
select sno from SC
group by sno having avg(score) >= all(select avg(score)
from SC
group by sno)
![wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==](https://i-blog.csdnimg.cn/blog_migrate/a57fd336c1ca6278de7d7443917ba70f.png)
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/750a96f1213fde619ab85ab1c8d2e3e5.gif)
![wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==](https://i-blog.csdnimg.cn/blog_migrate/a57fd336c1ca6278de7d7443917ba70f.png)
(5)exists(存在)
①exists基本用法
in后是不相关子查询,exist是相关子查询。
由上可以看出exists是判断子查询的结果集中是否有元组存在,而并不关心存在什么元组。传统意义认为其目标列表达式通常都用* ,因为带EXISTS的子查询只关注是否有元组,给出列名无实际意义。但是*会带来查询性能问题,建议使用常数(select 1 列的值为1,有多少行就有多少个1)exists后无脑跟1。
通过此题看一下元组变量的作用范围,子查询中sno指的是sc中的sno。
②用exists表示全部
找出选了所有课的学生,所有的课程该学生都选了,等价于不存在一门课程该学生没有选。
select sname from s
where not exists
(select cno from c where not exists
(select 1 from sc where sc.sno = s.sno and sc.cno = c.cno))
从所有的课程表c中选出一门课,这门课没有出现在sname的选课表上,以上情况不会发生即sname把课都选了。
查询至少选了学号为002的学生所选的全部课程的学生姓名。
p表示谓词:学生002选修了课程y,q谓词学生X选修了课程y。
经过以上推演,找到选修了002学生选修的所有课程的学生就等于找到一个学生不存在一门课002选修了但是他没选修。
select scx.sno from sc scx where not exists(
选出一名学生X
select 1 from sc scy where scy.sno = '002' and not exists(
选出002选的课程scy.cno
select 1 from sc scz where scz.sno = scx.sno and scz.cno = scy.cno))
如果学生X没有选择课程K的话返回 true