4.多表查询

多表查询

广义笛卡尔积运算

内连接运算

θ连接 --join on

–求学生以及其选修课程的情况。
----92

select *   
from xs,cj
where xs.sno=cj.sno;

----SQL99标准：(推荐)

select *
from xs join cj on xs.sno=cj.sno ;

–上面例子加一个条件dept=计算机

select *
from xs join cj on xs.sno=cj.sno
where dept='计算机';

–查找选修了“计算机基础”课程
–且成绩在80分以上的学生学号、姓名、课程名及成绩。

select xs.sno,sname,cname,grade
from xs,kc,cj
where xs.sno=cj.sno and kc.sno=cj.sno 
and cname='计算机基础' and grade>80;

select xs.sno,sname,cname,grade
from xs join CJ on xs.sno=cj.sno join kc on kc.sno=cj.sno 
where cname='计算机基础' and grade>80;

自连接

–求年龄大于王燕的所有学生的姓名、专业名和出生日期。

select b.sname,b.dept,b.birthday
from xs a join xs b on a.sno<>b.sno
where a.sname='王燕' and a.birthday>b.birthday;

---- 先筛选 a学生表里只剩下一条王燕的记录，要求b学生表的学生生日小于a表
----后连接 b表a表出去重复的王燕就是答案

外连接

–左连接 查找未选修任何课程的学生。

select *
from xs a left join cj b on a.sno=b.sno
where b.sno is null;

自然连接

–在FROM子句中采用如下表现形式：NATURAL JOIN，结果会把重复字段去掉；
----natural 不能用限定词sql

查找选修计算机基础的学生

select  *
from xs natural join cj
where cname='计算机基础';

----等值连接解决也可以，using不能有限定词

select  *
from xs join cj using(sno) join kc using(cno)
where cname='计算机基础';

–一些改造 运用

–查找选修了“计算机基础”课程
–且成绩在80分以上的学生学号、姓名、课程名及成绩。

select sno,sname,cname,grade
from xs NATURAL join CJ NATURAL join kc 
where cname='计算机基础' and grade>80;

select sno,sname,cname,grade
from xs join CJ using(sno) join kc using(cno)
where cname='计算机基础' and grade>80;

查找未选修任何课程的学生。

select *
from xs natural join cj 
where b.sno is null;

求计算机专业的学生以及其选修课程的情况

select *
from xs natural join cj 
where dept='计算机';

子查询

----查找选修离散数学的学生的学号。

select sno
from kc natural join cj 
where cname='离散数学';

select sno from cj
where con = (select cno from kc where cname='离散数学');

select cno from kc where cname=‘离散数学’; --在课程表里找到课程名是离散数学的课程号

----子查询求年龄大于王燕的所有学生的姓名、专业名和出生日期。

select sname,dept,birthday from xs
where birthday <
(select birthday from XS 
where sname='王燕' );

----查找选修离散数学的学生的姓名。（返回的是集合不是单值）
------先解决 查找 课程名为离散数学的课程号

select cno
from kc where cname='离散数学';

------再解决 查找 选修离散数学的学生的学号。

select sno from cj where cno =
                  (select cno
                   from kc where cname='离散数学');

------最后

select sname from xs 
where sno in
          (select sno 
          from cj  
          where cno =
                     (select cno 
                     from kc where cname='离散数学'));

----查找比所有计算机系的学生年龄都大的学生(< all)

select sno,sname,birthday
from xs
where dept<>'计算机'
and birthday<all(select birthday from xs where dept='计算机');

------等价于-----

select sno,sname,birthday
from xs
where dept<>'计算机'
and birthday<(select main(birthday) from xs where dept='计算机');

----查找课程号206的成绩不低于课程号101的最低成绩的学生的学号
---------课程号206的成绩

select grade from cj where cno='206';

---------课程号101的成绩

select grade from cj where cno='101';

---------课程号101的最低成绩

select min(grade) from cj where cno='101';

--------成绩满足条件的成绩

select grade from cj where cno='206' 
and grade>=
          any(select grade from cj where cno='101');

–min

select grade from cj where cno='206' 
and grade > 
          (select min(grade)from cj where cno='101');----找到101课程所有成绩

-----------最终结果

select sno from cj
where grade in (select grade from cj where cno='206' 
                and grade>=
                           any(select grade from cj where cno='101'));

子查询的位置

子查询放在WHERE子句中

----求与程明同系，或年龄大于王燕的学生的信息

select * from xs
where dept=(select dept from xs where sname='程明') 
or birthday<(select birthday from xs where sname='王燕');

子查询放在SELECT中

----求每个学生的学号、姓名、学分及最高学分。

select sno,sname,totalcredit,(select max(totalcredit) from xs)
from xs;

子查询放在FROM中

–求学分最高的前5名学生。
-----学分从高到低排列的学生表,nvl(totalcredit,0）把总学分为空的转换成0

select * from xs order by nvl(totalcredit,0) desc;

----排好的学生表放在from中

select *
from (select * from xs order by nvl(totalcredit,0) desc)
where rownum<6;  ---- 伪列rownum只能用小于号不能用大于号

相关子查询

–查询所有选修了102号课程的学生姓名。
-----子查询

select sname
from xs
where sno in (select sno from kc where cno='102');

----相关子查询用到了外查询exists

select sname 
from xs
where  exists (select * from cj where xs.sno=cj.sno and cno='102');

-------先执行外查询select * from cj where xs.sno=cj.sno and cno=‘102’
-------当此外查询语句为真时 exits 返回真
-------执行select sname from xs where ture; 返回sname

–求出选修全部课程学生姓名

-----这个成绩表里的这条信息，学号对应学生表里的学号，课程号对应课程表里的课程号
-----这个学生选修过这门课

SELECT * FROM cj WHERE (sno= xs.sno AND cno=kc.cno);

-----不存在这样的课程 （这个学生选修过）

SELECT cno FROM kc 
WHERE NOT EXISTS
               (SELECT * FROM cj
                 WHERE sno= xs.sno AND cno=kc.cno);

-----不存在这样的课程，这个学生没有选修过
-----说明这个学生选修了全部课程

SELECT sname FROM  xs
WHERE NOT EXISTS  (SELECT cno 
                   FROM kc 
                   WHERE NOT EXISTS
                                  (SELECT *
                                    FROM cj
                                    WHERE sno= xs.sno AND cno=kc.cno));

----查询选修程明同学选修的所有课程的学生名
–只需要替换kc 为程明同学选修的kc

--select cno from xs natural join kc where sname='程明'
--xs natural join kc 自然连接学生表和课程表
SELECT sname FROM  xs
WHERE NOT EXISTS  (SELECT cno 
                   FROM (select cno from xs natural join kc where sname='程明' ) 
                   WHERE NOT EXISTS
                                  (SELECT *
                                    FROM cj
                                    WHERE sno= xs.sno AND cno=kc.cno));

传统集合运算的实现

–求总学分在第五名到第八名的学生

select sno from (select * from xs order by nvl(totalcredit,0) desc )
where rownum<=8
minus
select sno from (select * from xs order by nvl(totalcredit,0) desc )
where rownum<5;