目录
(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);
(2)子查询(不能用order by)
①不相关子查询
子查询不依赖于父查询,执行顺序从最内层开始,由内到外。
--成绩在70分以上的学生信息
select sno, sname from studen
where sno in
(select sno from garde where score=70)
②相关子查询
子查询依赖父查询,从外层先拿出第一个元组(拿出来的是元组),根据它与内层查询相关的属性值处理内层查询,如果where为真则区该元组放入结果集中,依次取下一个。
--查询选修了c1号课程的学生的学号和姓名。
select sno,sname from s
where exists
(select * from sc
where cno = 'c1'
and sno = s.sno)
二者区别在于元祖变量的作用域不同。
(3)集合之间比较(符号写在子句前面)
①.比较运算符位置
子查询一定跟在比较运算符后面,且内层查询返回的是单值。
SELECT sno,sname,dno FROM S
WHERE dno =
(SELECT dno
FROM S
WHERE sname= ‘ 刘晨’);
②.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' ;
some,all有时可用聚集函数代替(4)存在谓词
找出平均成绩最高的学生学号
select sno from SC
group by sno having avg(score) >= all(select avg(score)
from SC
group by sno)
(4)exists(存在)
①exists基本用法
in后的子查询与外层查询无关只执行一次,exists后的子查询需要执行多次,是相关子查询。
由上可以看出exists是判断子查询的结果集中是否有元组存在,而并不关心存在什么元组。传统意义认为其目标列表达式通常都用* ,因为带EXISTS的子查询只关注是否有元组,给出列名无实际意义。但是*会带来查询性能问题,建议使用常数。
通过此题看一下元组变量的作用范围,子查询中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
(5)重复元祖unique
找出至少选修了两门课程的学生姓名
select sname from s
where not unique
(select sno from sc where sc.sno = s.sno)
这里也是相关子查询。
(6)复杂查询派生关系(定义临时视图)
①as
这里group by后面必须加上sname因为你要select sname。
select语句写完之后后面加上as 虚表名(列名【,列名】),result为临时关系。
②with子句(类似于倒装的as)
格式 with 临时关系名(属性列)as select子句。
示例:查询成绩最高的学生学号
with max_score(mscore) as
select max(score) from sc
select sno from sc,max_score where sc.score = max_score.mscore