sql3.4数据查询

嵌套查询

带有in谓词的子查询

• 子查询不能使用order by子句

--例子：查询选修2号课程的所有学生的姓名
--连接查询
select sname
from student s,sc
where s.sno=sc.sno and cno=2;
--嵌套查询,先查选修了2号课程的学生学号，再查学号对应的姓名
select sname
from student
where sno in(
	select sno
	from sc
	where cno=2
);

--例子：找出每个学生超过他自己选修课程平均成绩的课程号
select sno,cno
from sc x
where grade>(
	select avg(grade)
	from sc y
	where y.sno=x.sno
);

--例1:查询与“刘晨”在同一个系学习的学生学号、姓名和所在系
--(用自身连接实现)
select s1.sno,s1.sname,s1.sdept
from student s1,student s2
where s1.sdept=s2.sdept and s2.sname='刘晨';

--带in谓词的子查询   
--1、查询刘晨所在的系
--2、查询在刘晨所在系的学生
select sname,sno,sdept
from student
where sdept in(
	select sdept
	from student
	where sname='刘晨');
	
--查询选修课程名为IS的学生的学号和姓名
--先在course中查IS的课程号，再在sc中查选修IS课程号的学生的sno，再在student中找到姓名
select sno,sname
from student
where sno in(
	select sno
	from sc
	where cno in(
		select cno
		from course
		where cname='IS'));

连接查询
当确切知道内层查询返回的是单值时，可用比较运算符，与any或all配合使用

带有比较运算符的子查询

--假设一个学生只可能在一-个系学习，并且必须属于一个系，则在例1 可以用=代替IN :
select sno,sname
from student
where sdept=(
	select sdept
	from student
	where sname='刘晨');
	
--找出每个学生超过他选修课程平均成绩的课程号
select  sno,cno
from sc s1
where grade > (
	select avg(grade)
	from sc s2
	where s1.sno=s2.sno
)

带有Any或All谓词的子查询

不相关

--查询其他系中比信息系某一个学生年龄小的学生姓名及年龄。
select sname,sage
from student
where sage < any(
	select sage
	from student
	where sdept = 'IS'
) and sdept!= 'IS'

--查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。
select sname,sage
from student
where sage < all(
	select sage
	from student
	where sdept = 'IS'
) and sdept!= 'IS'

--使用聚集函数，效率会高
--查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。
select sname,sage
from student
where sage < (
	select min(sage)
	from student
	where sdept = 'IS'
) and sdept!= 'IS'

Exists谓词

• 存在变量，带有Exists谓词的子查询不返回任何数据，只产生逻辑真值
  true或逻辑假值false！内层查询结果非空，返回真值，为空，返回假值
• 由Exists引出的子查询，其目标列表达式通常都用*，因为带Exists的子查询只返回真值或假值，给出列名无实际意义
• not Exists，与Exists相反

--成员关系，判断外层每一个元组是否是集合的成员
--查询选修2号课程的学生的姓名
select s.sname
from student s
where s.sno in(
	select sno
	from sc
	where cno=2
);
select s.sname
from student s
where exists(
	select *
	from sc
	where cno=2 and s.sno=sc.sno
)

--包含关秀，判断一个集合是否是另一个集合的子类
--例：查询至少选修了200215122这位同学所选的全部课程的同学
--R：200215122这位学生选修的全部课程
--S:某一个同学选修的全部课程
--判断S是否包含R,不存在一门课，200215122选修了，y没选修
select sname
from student y
where not Exists(
	select *
	from sc s1
	where s1.sno='200215122' and not Exists(
		select *
		from sc s2
		where s2.sno=y.sno and s2.cno=s1.cno
	)
);

--相等关系：R是否与S相等
--例：查询选修全部选修课程的同学
--R：course表里面有的可选修的全部课程
--S：某一个学生选修的课程
--判断R是否与S相等，R包含S永真,不存在一门课程，course里有，y没有
select sname
from student y
where not Exists(
	select *
	from course c
	where  not Exists(
		select *
		from sc s
		where s.sno=y.sno and s.cno=c.cno
	)
);

--交集是否为空
--例：查询与学生200215122选修的所有课程中有相同课程的学生
--R：学生200215122选修的课程
--S：要查询的学生选修的课程
--R∩S是否为空，是否存在某些集合既属于R也属于S
select sname
from student y
where Exists(
	select *
	from sc s1
	where s1.sno='200215122' and Exists(
		select *
		from sc s2
		where s2.cno=s1.cno and s2.sno=y.sno
	)
);

--查询两个交集为空
--查询与学生200215122没有相同选修课程的学生的姓名
--R和S的交集为空，不存在一门课程，200215122选修了，y也选修了
select sname
from student y
where not Exists(
	select *
	from sc s1
	where s1.sno='200215122' and Exists(
		select *
		from sc s2
		where s2.sno=y.sno and s2.cno=s1.cno
	)
);

集合查询

并操作

--例1：查询计算机系的学生和年龄不大于19岁的学生，all显示重复元组
select *
from student
where sdept='CS'
union all
select *
from student
where sage<=19;
	--或
select *
from student
where sdept='CS' or sage<=19;

--例2：查询选修了1号或选修了2号课程的学生，并按学号升序排列
select sno
from sc
where cno='1'
union
select sno
from sc
where cno='2'
order by sno;
--或（or）
select distinct sno
from sc
where cno='1' or cno='2'
--order by 1 根据属性列的次序排序
order by sno;

--例3：查询学校中所有师生的姓名
select sname
from student
union
select tname
from teacher

交操作

select *
from student
where sdept='CS'
intersect
select *
from student
where sage<=19;
--或
select *
from student
where sdept='CS' and sage<=19

--例5：查询选修1号课的学生学号与选修2号课的学生学号的交集。
--自身连接
select s1.sno
from sc s1,sc s2
where s1.sno=s2.sno and s1.cno='1' and s2.cno='2';
--内连接
select s1.sno
from sc s1 inner join sc s2 on s2.sno=s1.sno and s1.cno='1' and s2.cno='2';
--嵌套查询
select *
from sc s1
where s1.cno='2' and Exists(
	select *
	from sc s2
	where s2.cno='2' and s2.sno=s1.sno
);
--交操作
select *
from sc
where cno='1'
intersect
select *
from sc
where cno='2';

差操作

--例7：查询计算机系的学生与年龄不大于19岁的学生的差集
select *
from student
where sdept='cs'
except
select *
from student
where sage>19;
--或
select *
from student
where sdept='cs' and sage<=19;

--查询没有选修1号课程的学生姓名
--嵌套
select sname
from student
where not Exists(
	select *
	from sc
	where cno=1 and sc.sno=student.sno
);
--差操作
select sname
from student
except
select sname
from student,sc
where cno=1 and student.sno=sc.sno;

基于派生表的查询

--找出每个学生超过他自己选修课程平均成绩的课程号
select x.sno,x.cno
from sc x
where grade>(
	select avg(grade)
	from sc y
	where y.sno=x.sno
);
--基于派生表的查询
select sno,cno
from sc,(
	select sno,avg(grade)
	from sc
	group by sno) as avg_sc(avgsno,avggrade)
where sno=avgsno and grade>avggrade;

--查询所有选修了1号课程的学生姓名
select sname
from student,(
	select sno
	from sc
	where cno=1) as onesc
where student.sno=onesc.sno;