SQL嵌套ppt課件

1、Student表,1,SC表,2,Course表,3,嵌 套 查 詢,問(wèn)題的引入 試查詢與”劉晨“同學(xué)在同一個(gè)系的學(xué)生學(xué)號(hào)、姓名。 分步完成：1) 確定”劉晨“所在的系 select sdept from student where sname=“劉晨” 查詢結(jié)果為：計(jì)算機(jī) 2）查詢轉(zhuǎn)換為 查詢計(jì)算機(jī)系學(xué)生的學(xué)號(hào)、姓名,4,2） select sno,sname from student where sdept=計(jì)算機(jī) 可以用一次SQL查詢完成嗎？ select sno,sname from student where sdept in ( select sdept from student

2、where sname=劉晨),5,一、帶in謂詞的嵌套查詢 謂詞in是連接父子查詢最常用的謂詞。某些 情況下，in可以用=代替。 select sno,sname from student where sdept in ( select sdept from student where sname= 劉晨 ) 定義：一個(gè)select-from-where語(yǔ)句稱為一個(gè)查詢塊。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的where或having子句中的查詢稱為嵌套查詢。,內(nèi)查詢塊、子查詢,外層查詢塊、父查詢,謂詞in進(jìn)行父、子查詢間的連接,6,思考：系統(tǒng)對(duì)嵌套語(yǔ)句執(zhí)行先后順序是怎樣的？ 由里向外進(jìn)行處理，將

3、子查詢的查詢結(jié)果作為父 查詢的查詢條件。 select sno,sname select sno,sname from student from student where sdept in where sdept in (計(jì)算機(jī)) ( select sdept from student where sname= 劉晨),7,例1、 查詢選修了課程名為”信息系統(tǒng)“的學(xué)生學(xué)號(hào)。 第一步：找到”信息系統(tǒng)“的課程號(hào) select cno from course where cname=信息系統(tǒng) 結(jié)果為： cno 3 第二步：找到選修了cno=3 的學(xué)生學(xué)號(hào) select sno from sc wh

4、ere cno=3,8,構(gòu)建嵌套查詢： select sno from sc where cno in ( select cno from course where cname=信息系統(tǒng)) 注意 ：該查詢只有一層嵌套。SQL嵌套查詢?cè)试S多層嵌套格式。,找到”信息系統(tǒng)“的課程號(hào)，結(jié)果為cno=3,1,2,找到選修了cno=3的學(xué)生學(xué)號(hào),9,例2、 查詢選修了課程名為”信息系統(tǒng)“的學(xué)生姓名。 1）找到”信息系統(tǒng)“的cno select cno from course where cname=信息系統(tǒng) 結(jié)果cno=3 2）找到選修了cno=3的學(xué)生學(xué)號(hào) select sno from sc wher

5、e cno=3 結(jié)果為： sno : 200215121 200215122。,10,3）找到學(xué)號(hào)為200215122、200215121的姓名。 select sname from student where sno=200215122 or sno=200215121 思考：該查詢會(huì)構(gòu)建成幾層嵌套？,11,select sname from student where sno in (select sno from sc where cno in (select cno from course where cname=信息系統(tǒng)),1,2,3,12,二、帶比較運(yùn)算符的嵌套查詢 當(dāng)子查詢返回的

6、是單值時(shí)，父查詢和子查詢之 間需要進(jìn)行值的比較時(shí)，用比較運(yùn)算符進(jìn)行連接。 = (select sage from student where sname=張立),子查詢的范返回值是一個(gè)確定的值,13,例2、查詢成績(jī)小于2號(hào)課程的平均成績(jī)的 課程號(hào)。 select distinct cno from sc where grade (select avg(grade) from sc where cno=2),14,例3、查詢成績(jī)小于2號(hào)課程的平均成績(jī)的 課程名。 select cname from course where cno in ( select distinct cno from sc

7、 where grade (select avg(grade) from sc where cno=2) ),15,student,16,teacher,17,Course,18,score,19,案例： 1、查詢與108號(hào)同學(xué)在（不在）同一個(gè)班級(jí)的學(xué)生學(xué)號(hào)、姓名、班級(jí)。 2、查詢”張旭“老師所授課程的學(xué)生成績(jī)。 3、查詢計(jì)算機(jī)系教師所授課程的成績(jī)。 4、查詢選修了3105課程且成績(jī)大于3245課程最高成績(jī)的學(xué)生的學(xué)號(hào)，成績(jī)。且結(jié)果按成績(jī)的降序進(jìn)行排序。 5、查詢計(jì)算機(jī)系且職稱與電子工程系教師職稱不同的教師姓名和職稱。,20,6、查詢有超過(guò)5人選修的課程的任課教師名。 7、課程平均成績(jī)大于80

8、分的任課教師名。 8、查詢95031班同學(xué)所選修課程中每一門課程的 任課教師名。,21,-1、查詢與108號(hào)同學(xué)在（不在）同一個(gè)班級(jí)的學(xué)生學(xué)號(hào)、姓名、班級(jí) - select sno,sname,class - from student - where class in - (select class - from student - where sno=108 ) 2、查詢”張旭“老師任課的學(xué)生成績(jī)。 - select grade - from score - where cno in - (select cno - from course - where tno in - (select t

9、no - from teacher - where tname=張旭),22,3、查詢計(jì)算機(jī)系教師所教課程的成績(jī)。 - select grade - from score - where cno in - ( select cno - from course - where tno in - (select tno - from teacher - where depart=計(jì)算機(jī)) 4、查詢選修了3105課程且成績(jī)3245最高成績(jī)的學(xué)生的學(xué)號(hào)，成績(jī)。且結(jié)果按成績(jī)的降序進(jìn)行排序。 - select sno,grade - from score - where cno=3105 and grad

10、e - (select max(grade) - from score - where cno=3245) - order by grade desc,23,5、查詢計(jì)算機(jī)系與電子工程系不同職稱教師的姓名和職稱。 - select tname,prof - from teacher - where prof not in - (select prof - from teacher - where depart=電子工程) 6、查詢有超過(guò)5人選修的課程的任課教師名。 - select tname - from teacher - where tno in - (select tno - from

11、 course - where cno in - (select cno - from score - group by cno - having count(sno)5),24,7、課程平均成績(jī)大于80分的任課教師名。 - select tname - from teacher - where tno in - (select tno - from course - where cno in - (select cno - from score - group by cno - having avg(grade)80) 8、查詢95031班同學(xué)所選各門課程的任課教師名。 select tna

12、me from teacher where tno in (select tno from course where cno in (select distinct cno from student,score where student.sno=score.sno and class=95031),25,三、帶有any或all謂詞的子查詢 當(dāng)子查詢返回多值時(shí)，需用any或all謂詞修飾比較運(yùn)算符 = any 僅大于子查詢結(jié)果集中的某一個(gè)值 min =any 僅大于等于子查詢結(jié)果集中的某一個(gè)值 any 僅小于子查詢結(jié)果集中的某一個(gè)值 max =any 僅小于子查詢結(jié)果集中的某一個(gè)值,26,al

13、l 大于子查詢結(jié)果集中的所有值 max =all 大于等于子查詢結(jié)果集中的所有值 all 小于子查詢結(jié)果集中的所有值 min =any 小于等于子查詢結(jié)果集中的所有值 !=any 不等于子查詢結(jié)果中的某個(gè)值 !=all 不等于子查詢結(jié)果集中的任何值,27,例1：查詢其他系中僅比計(jì)算機(jī)某一學(xué)生年 齡小的學(xué)生姓名和年齡。 僅比計(jì)算機(jī)某一學(xué)生年齡小 any max select sname,sage from student where sageany (select sage from student where sdept=計(jì)算機(jī)) and sdept!=計(jì)算機(jī),先找到計(jì)算機(jī)專業(yè)中的年齡集合(2

14、2,20,17),28,以上嵌套查詢可以轉(zhuǎn)換為： select sname,sage from student where sage (select max(sage) from student where sdept=計(jì)算機(jī)) and sdept!=計(jì)算機(jī),29,例2：查詢其他系中比計(jì)算機(jī)系所有學(xué)生年齡 都小的學(xué)生姓名和年齡。 select sname,sage from student where sageall (select sage from student where sdept=計(jì)算機(jī)) and sdept!=計(jì)算機(jī),先找到計(jì)算機(jī)專業(yè)中的年齡集合(22,20,17),30,以上嵌

15、套查詢可以轉(zhuǎn)換為： select sname,sage from student where sage (select min(sage) from student where sdept=計(jì)算機(jī)) and sdept!=計(jì)算機(jī),31,案例 1、查詢選修了3105課程且成績(jī)高于3245課程所有學(xué)生成績(jī)的學(xué)生學(xué)號(hào)、成績(jī)、課程號(hào)。 2、查詢選修了3105課程且成績(jī)僅高于3245課程某一位學(xué)生成績(jī)的學(xué)生學(xué)號(hào)、成績(jī)、課程號(hào)，且查詢結(jié)果按成績(jī)的降序進(jìn)行排序。 3、查詢成績(jī)高于3245課程最高分課程的任課教師名。,32,9、select cno,sno,grade from score where cno

16、=3105and grade any (select grade from score where cno=3245) order by grade desc 或者 select cno,sno,grade from score where cno=3105and grade (select min(grade) from score where cno=3245) order by grade desc,33,10、select student.sno,student.sname,grade from score,student where score.sno=student.sno and cno=3105 an