數(shù)據(jù)庫-子查詢例子與詳解

1、不相關(guān)子查詢返回一個(gè)值（帶有比較運(yùn)算符的子查詢）例：查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。-子查詢實(shí)現(xiàn)（用=的前提是子查詢返回一個(gè)值，即單行單列，學(xué)生劉晨姓名在學(xué)生表中沒有重復(fù)值）select * from student where sdept =(select sdept from student where sname=劉晨 ); 例：查詢選修成績大于08001 號(hào)學(xué)生的平均成績的學(xué)生選課信息select *from sc where grade(select avg(grade) from sc where sno=08001); 例：查詢年齡大于所有學(xué)生平均年齡的學(xué)生信息select

2、* from student where sage(select avg(sage) from student); 例：查詢學(xué)校最小年齡的學(xué)生信息select * from student where sage=(select min(sage) from student); 例：查詢年齡處于李莉和趙海間的學(xué)生信息select * from student where sage between(select sage from student where sname=李莉 ) and (select sage from student where sname= 趙海 ); 或select *

3、from student where sage =(select sage from student where sname=李莉 ) and sage =(select sage from student where sname= 趙海 ); 返回一組值（帶有in 謂詞的子查詢、帶有any（some ）或 all 謂詞的子查詢）帶有 in 謂詞的子查詢例：選修 002 號(hào)課程的學(xué)生姓名-子查詢select sname from student where sno in(select sno from sc where cno=002); -連接查詢select sname from stud

4、ent,sc where student.sno=sc.sno and cno=002; 例：查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。-子查詢實(shí)現(xiàn)select * from student where sdept in(select sdept from student where sname=劉晨 ); -自身連接實(shí)現(xiàn)select b.sno,b.sname,b.sdept from student a,student b where a.sdept=b.sdept and a.sname= 劉晨 ; 例：查詢被學(xué)生選修過的課程信息select * from course where cno i

5、n(select distinct cno from sc); 例：查詢未被學(xué)生選修過的課程信息select * from course where cno not in(select distinct cno from sc); 例：查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號(hào)和姓名-子查詢實(shí)現(xiàn)select sno,sname from student where sno in( select sno from sc where cno in( select cno from course where cname= 信息系統(tǒng) ); -連接查詢實(shí)現(xiàn)select student.sno,sname

6、from student,sc,course where student.sno=sc.sno and o=o and cname=信息系統(tǒng) ; (執(zhí)行效率：一般dbms內(nèi)部按照連接查詢執(zhí)行。子查詢?cè)趦?nèi)部執(zhí)行時(shí)會(huì)先轉(zhuǎn)換為連接查詢，再執(zhí)行 ) 帶有 any（some ）或 all 謂詞的子查詢例：查詢其他系中比cs系某一學(xué)生年齡小的學(xué)生姓名和年齡select sname,sage from student where sageany(select sage from student where sdept=cs) and sdeptcs; 或select sname,sage from stud

7、ent where sage(select max(sage) from student where sdept=cs) and sdeptcs; 例：查詢其他系中比cs系所有學(xué)生年齡都大的學(xué)生姓名及年齡。select sname,sage from student where sageall(select sage from student where sdept=cs) and sdeptcs; 或select sname,sage from student where sage(select max(sage) from student where sdept=cs) and sdept

8、cs; =any(與 in 等價(jià) ) any (min() any(all(max() all(select avg(grade) from sc where sno=a.sno); 例：查詢比本系平均年齡大的學(xué)生信息。select * from student a where sage(select avg(sage) from student where sdept=a.sdept); 出現(xiàn)在 having 后面的子查詢例：查詢選修了全部課程的學(xué)生姓名。select sno,sname from student where sno in( select sno from sc group

9、by sno having count(*)=(select count(*) from course); 例：查詢至少選修了學(xué)生08002 選修的全部課程的學(xué)生號(hào)碼。select sno from sc where cno in(select cno from sc where sno=08002) group by sno having count(*)=(select count(*) from sc where sno=08002); 例：查詢最小年齡大于cs系最小年齡的系別和其最小年齡。select sdept,min(sage) from student group by sdep

10、t having min(sage) (select min(sage) from student where sdept=cs); 出現(xiàn)在 from 后面的子查詢（派生表）例：查詢比本人平均成績高的學(xué)生學(xué)號(hào)和課程號(hào)。select sc.sno,cno,grade,avggrade from sc,(select sno,avg(grade) avggrade from sc group by sno) awhere sc.sno=a.sno and gradea.avggrade;并操作 union/union all 例：查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19 歲的學(xué)生。select sname,sdept,sagefrom s