數(shù)據(jù)庫的查詢課件

1、sql數(shù)據(jù)查詢數(shù)據(jù)查詢 基本結(jié)構(gòu)：基本結(jié)構(gòu)：select a1 , a2 , , anfrom r1 , r2 , , rmwhere p語句格式語句格式select all|distinct *|,from ， where group by having order by asc|desc ；學(xué)生學(xué)生-課程數(shù)據(jù)庫課程數(shù)據(jù)庫 學(xué)生表：學(xué)生表：student(sno，sname，ssex，sage， sdept) 課程表：課程表：course(cno，cname，semester，ccredit) 學(xué)生選課表：學(xué)生選課表：sc(sno，cno，grade) select語句的含義語句的含義 結(jié)果

2、集結(jié)果集 查詢操作的對(duì)象是關(guān)系，結(jié)果還是一個(gè)關(guān)系，查詢操作的對(duì)象是關(guān)系，結(jié)果還是一個(gè)關(guān)系，是一個(gè)結(jié)果集，是一個(gè)動(dòng)態(tài)數(shù)據(jù)集是一個(gè)結(jié)果集，是一個(gè)動(dòng)態(tài)數(shù)據(jù)集select子句 用以列出查詢結(jié)果集中的期望屬性用以列出查詢結(jié)果集中的期望屬性. select sno, sname from studentselect子句子句 *與屬性列表與屬性列表 星號(hào)星號(hào) * 表示所有屬性表示所有屬性 星號(hào)星號(hào) * ：按關(guān)系模式中屬性的順序排列，：按關(guān)系模式中屬性的順序排列，并具有一定的邏輯數(shù)據(jù)獨(dú)立性并具有一定的邏輯數(shù)據(jù)獨(dú)立性 顯式列出屬性名：按用戶順序排列顯式列出屬性名：按用戶順序排列select * from stu

3、dent 等價(jià)于：等價(jià)于：select sno, sname，ssex,sage，sdept from studentselect子句子句更名更名 為結(jié)果集中的某個(gè)屬性改名為結(jié)果集中的某個(gè)屬性改名 使結(jié)果集更具可讀性使結(jié)果集更具可讀性 select sno as stu_no，cno as course_no， grade from sc select sno，sname，2009 - sage as birthday from studentselect子句子句重復(fù)元組重復(fù)元組 select 子句的缺省情況是保留重復(fù)元組子句的缺省情況是保留重復(fù)元組（ all ），可用），可用 distinc

4、t 去除重復(fù)元去除重復(fù)元組組 select distinct sdept from student select all sdept from student 去除重復(fù)元組需要臨時(shí)表的支持去除重復(fù)元組需要臨時(shí)表的支持where 子句子句 查詢滿足指定條件的元組可以通過查詢滿足指定條件的元組可以通過where子句來實(shí)現(xiàn)子句來實(shí)現(xiàn) 使使where子句中的邏輯表達(dá)式返回子句中的邏輯表達(dá)式返回true值的元組，是符合要求的元組，將被選值的元組，是符合要求的元組，將被選擇出來擇出來where 子句子句運(yùn)算符運(yùn)算符 比較：比較：、=、=、not 確定范圍：確定范圍： between a and b、not

5、 between a and b 確定集合：確定集合：in、not in 字符匹配：字符匹配：like，not like 空值：空值：is null、is not null 多重條件：多重條件：and、or、notwhere 子句子句比較比較 例，查詢所有年齡在例，查詢所有年齡在20歲以下的學(xué)生姓歲以下的學(xué)生姓名及其年齡。名及其年齡。 select sname,sage from student where sage =70 and grade80 練習(xí)，檢索選修練習(xí)，檢索選修1號(hào)或號(hào)或2號(hào)課程號(hào)課程,且成績高且成績高于于80分的學(xué)生號(hào)分的學(xué)生號(hào),課程號(hào)和分?jǐn)?shù)課程號(hào)和分?jǐn)?shù) from 子句子句

6、列出將被掃描的關(guān)系（表）列出將被掃描的關(guān)系（表） 例：列出所有學(xué)生的學(xué)號(hào)、姓名、課號(hào)、例：列出所有學(xué)生的學(xué)號(hào)、姓名、課號(hào)、成績。成績。select student.sno，sname，sc.cno，gradefrom student，scwhere student.sno = sc.snofrom 子句子句元組變量元組變量 為為 from 子句中的關(guān)系定義元組變量子句中的關(guān)系定義元組變量 方便關(guān)系名的引用方便關(guān)系名的引用 例：列出與例：列出與s1同歲的同學(xué)的學(xué)號(hào)同歲的同學(xué)的學(xué)號(hào),姓名姓名,年齡年齡select t.sno,t.sname,t.sagefromstudent as t,stude

7、nt as swhere s.sno = s1 andt.sage = s.sageorder by子句子句 指定結(jié)果集中元組的排列次序指定結(jié)果集中元組的排列次序 asc（缺?。ⅲㄈ笔。esc、未選中的屬性、未選中的屬性 例：列出例：列出cs系中的男生的學(xué)號(hào)、姓名、系中的男生的學(xué)號(hào)、姓名、年齡，并按年齡進(jìn)行排列（升序）年齡，并按年齡進(jìn)行排列（升序） select sno, sname,sage from student where sdept = cs order by sage asc 集函數(shù)集函數(shù) 計(jì)數(shù)計(jì)數(shù) count（distinct|all *） count（distinct|a

8、ll ） 計(jì)算總和計(jì)算總和 sum（distinct|all ） 計(jì)算平均值計(jì)算平均值 avg（distinct|all ） 求最大值求最大值 max（distinct|all ） 求最小值求最小值 min（distinct|all ） 例例, 查詢學(xué)生總?cè)藬?shù)。查詢學(xué)生總?cè)藬?shù)。 select count(*) from student; 例例, 查詢選修了課程的學(xué)生人數(shù)。查詢選修了課程的學(xué)生人數(shù)。 select count(distinct sno) from sc; 例例, 計(jì)算計(jì)算1號(hào)課程的學(xué)生總分和平均成績。號(hào)課程的學(xué)生總分和平均成績。select sum(grade) 總分總分, av

9、g(grade) 平均分平均分 from sc where cno= 1; 例例, 查詢選修查詢選修3號(hào)課程的學(xué)生最高分?jǐn)?shù)與最低分號(hào)課程的學(xué)生最高分?jǐn)?shù)與最低分的差。的差。 select max(grade)-min(grade) from sc where cno= 3; group by子句子句 使用使用group by子句對(duì)查詢結(jié)果分組子句對(duì)查詢結(jié)果分組 細(xì)化集函數(shù)的作用對(duì)象細(xì)化集函數(shù)的作用對(duì)象 未對(duì)查詢結(jié)果分組，集函數(shù)將作用于整個(gè)未對(duì)查詢結(jié)果分組，集函數(shù)將作用于整個(gè)查詢結(jié)果查詢結(jié)果 對(duì)查詢結(jié)果分組后，集函數(shù)將分別作用于對(duì)查詢結(jié)果分組后，集函數(shù)將分別作用于每個(gè)組每個(gè)組 例例, 求各個(gè)課程號(hào)

10、及相應(yīng)的選課人數(shù)。求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)。 select cno, count(sno) from sc group by cno; 注意：使用注意：使用group by子句后，子句后，select子句的列名列表中只能出現(xiàn)分組屬性和集子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù)函數(shù)group by子句子句having短語短語 使用使用having短語設(shè)定篩選條件短語設(shè)定篩選條件 例例, 查詢選修了查詢選修了3門以上課程的學(xué)生學(xué)號(hào)，門以上課程的學(xué)生學(xué)號(hào)，及選修的課程數(shù)。及選修的課程數(shù)。 select sno, count(*) from sc group by sno having count

11、(*) 3; having短語短語 例例, 查詢有查詢有2門以上課程是門以上課程是90分以上的分以上的學(xué)生的學(xué)號(hào)及（學(xué)生的學(xué)號(hào)及（90分以上的）課程數(shù)分以上的）課程數(shù) select sno, count(*) from sc where grade=90 group by sno having count(*)=2; having短語與短語與where子句的區(qū)別：子句的區(qū)別： 作用對(duì)象不同作用對(duì)象不同 where子句作用于基表或視圖，從中選擇子句作用于基表或視圖，從中選擇滿足條件的元組。滿足條件的元組。 having短語作用于組，從中選擇滿足條件短語作用于組，從中選擇滿足條件的組。的組。 練習(xí)

12、，求各門課程的平均分練習(xí)，求各門課程的平均分, ,最高分最高分, ,最低分及各門課程最低分及各門課程的選修人數(shù)的選修人數(shù). . select cno as 課號(hào)課號(hào), avg(grade) as 平均分平均分, max(grade) as 最高分最高分, min(grade) as 最低分最低分, count(sno) as 人數(shù)人數(shù)from scgroup by cno;連接查詢連接查詢 同時(shí)涉及多個(gè)表的查詢稱為連接查詢同時(shí)涉及多個(gè)表的查詢稱為連接查詢 用來連接兩個(gè)表的條件稱為連接條件或連接謂用來連接兩個(gè)表的條件稱為連接條件或連接謂詞詞 連接查詢包括內(nèi)連接、外連接和交叉連接等。連接查詢包括內(nèi)

13、連接、外連接和交叉連接等。 連接查詢中用于連接兩個(gè)表的條件稱為連接條連接查詢中用于連接兩個(gè)表的條件稱為連接條件或連接謂詞。件或連接謂詞。 一般格式：一般格式： . . 比較運(yùn)算符：比較運(yùn)算符：=、=、=80 and y.grade=80連接查詢連接查詢 自連接自連接 是一種特殊的內(nèi)連接。是一種特殊的內(nèi)連接。 相互連接的表物理上為同一張表。相互連接的表物理上為同一張表。 必須為兩個(gè)表取別名，使之在邏輯上成必須為兩個(gè)表取別名，使之在邏輯上成為兩個(gè)表。為兩個(gè)表。 查詢與劉晨在同一個(gè)系學(xué)習(xí)的學(xué)生的姓查詢與劉晨在同一個(gè)系學(xué)習(xí)的學(xué)生的姓名和所在的系。名和所在的系。 select s2.sname, s2.

14、sdept from student s1 join student s2 on s1.sdept = s2.sdept where s1.sname = 劉晨劉晨 and s2.sname != 劉晨劉晨 外連接外連接 只限制一張表中的數(shù)據(jù)必須滿足連接條件，而另只限制一張表中的數(shù)據(jù)必須滿足連接條件，而另一張表中數(shù)據(jù)可以不滿足連接條件。一張表中數(shù)據(jù)可以不滿足連接條件。 ansi方式的外連接的語法格式為：方式的外連接的語法格式為：from 表表1 left | right outer join 表表2 on theta方式的外連接的語法格式為：方式的外連接的語法格式為： 左外連接： from 表

15、1, 表2 where 表1.列名(+) 表2.列名 右外連接： from 表1, 表2 where 表1.列名 表2.列名(+) 例例,查詢學(xué)生的選課情況。查詢學(xué)生的選課情況。 select student.*,cno,grade from student left outer join sc on (student.sno=sc.sno)或者：或者：select student.*,cno,gradefrom student,scwhere student.sno*=sc.sno 查詢哪些課程沒有人選，列出課程名。查詢哪些課程沒有人選，列出課程名。 select cname from co

16、urse c left join sc on c.cno = sc.cno where sc.cno is null使用使用top限制結(jié)果集限制結(jié)果集 在進(jìn)行查詢時(shí)有時(shí)只希望列出結(jié)果集中的前幾在進(jìn)行查詢時(shí)有時(shí)只希望列出結(jié)果集中的前幾個(gè)結(jié)果，而不是全部結(jié)果。個(gè)結(jié)果，而不是全部結(jié)果。 例如，競賽時(shí)可能只取成績最高的前三名例如，競賽時(shí)可能只取成績最高的前三名. 可以使用可以使用top謂詞限制輸出的結(jié)果。格式如下謂詞限制輸出的結(jié)果。格式如下:top n percent with ties n為非負(fù)整數(shù)。為非負(fù)整數(shù)。 top n：表示取查詢結(jié)果的前：表示取查詢結(jié)果的前n行；行； top n percne

17、t：表示取查詢結(jié)果前：表示取查詢結(jié)果前n%行；行； with ties：表示包括并列的結(jié)果。：表示包括并列的結(jié)果。 查詢年齡最大的三個(gè)學(xué)生的姓名、年齡及查詢年齡最大的三個(gè)學(xué)生的姓名、年齡及所在的系。所在的系。select top 3 sname, sage, sdept from student order by sage desc 若包括年齡并列第若包括年齡并列第3名的學(xué)生，則：名的學(xué)生，則：select top 3 with ties sname, sage, sdept from student order by sage desc 查詢查詢vb課程考試成績前三名的學(xué)生的姓課程考試成績前

18、三名的學(xué)生的姓名和成績。名和成績。select top 3 with ties sname, grade from student s join sc on s.sno = sc.sno join course c on c.cno = sc.cno where cname = vb order by grade desc子查詢子查詢 在一個(gè)在一個(gè)select-from-where語句的語句的where子句中存在另一個(gè)子句中存在另一個(gè)select語句，語句，稱作子查詢。稱作子查詢。 select sname外外層查詢層查詢/父父查詢查詢 from student where sno in (s

19、elect sno 內(nèi)層查詢內(nèi)層查詢/子子查詢查詢 from sc where cno= 2 );嵌套查詢求解方法嵌套查詢求解方法 不相不相關(guān)關(guān)子子查詢查詢: :子子查詢查詢的的查詢條查詢條件不依件不依賴賴于父于父查詢查詢 是由里向外逐是由里向外逐層處層處理。即每理。即每個(gè)個(gè)子子查詢查詢在上一在上一級(jí)查詢處級(jí)查詢處理之前理之前求解，子求解，子查詢查詢的的結(jié)結(jié)果用于建立其父果用于建立其父查詢查詢的的查查找找條條件件 相相關(guān)關(guān)子子查詢查詢: :子子查詢查詢的的查詢條查詢條件依件依賴賴于父于父查詢查詢 首先取外首先取外層查詢層查詢中表的第一中表的第一個(gè)個(gè)元元組組，根據(jù)，根據(jù)它與內(nèi)層查詢它與內(nèi)層查詢相

20、相關(guān)關(guān)的的屬屬性性值處值處理理內(nèi)層查詢內(nèi)層查詢，若，若wherewhere子句返回子句返回值為真值為真，則則取此取此元元組組放入放入結(jié)結(jié)果表；然后再取外果表；然后再取外層層表的下一表的下一個(gè)個(gè)元元組組；重；重復(fù)這復(fù)這一一過過程，直至外程，直至外層層表全部表全部檢查檢查完完為為止。止。 。 嵌套查詢嵌套查詢1.子查詢的結(jié)果返回的值為單值。子查詢的結(jié)果返回的值為單值。 where子句中的子查詢由子句中的子查詢由 比較符比較符 ,=, 引導(dǎo)引導(dǎo)例，找出年齡小于李芳的學(xué)生姓名例，找出年齡小于李芳的學(xué)生姓名select sname from student where sage (select sage

21、 from student where sname=李芳李芳);嵌套查詢嵌套查詢例，查詢與例，查詢與“劉晨劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。在同一個(gè)系學(xué)習(xí)的學(xué)生。select sno,sname,sdept from student where sdept =(select sdept from student where sname= 劉晨劉晨 );嵌套查詢嵌套查詢 用自身連接完成本查詢要求用自身連接完成本查詢要求select s1.sno,s1.sname,s1.sdeptfrom student s1,student s2where s1.sdept = s2.sdept and s2.sna

22、me = 劉劉晨晨;嵌套查詢嵌套查詢2.子查詢返回的結(jié)果為一組值子查詢返回的結(jié)果為一組值 在在where子句中使用謂詞有子句中使用謂詞有in, any, all, exists 等等(1)in謂詞的用法謂詞的用法意義意義:判斷判斷where表達(dá)式的值是否在子查表達(dá)式的值是否在子查詢的結(jié)果集合里詢的結(jié)果集合里,可在可在in前加前加not表示相反表示相反的意義的意義.嵌套查詢嵌套查詢 例例:找出學(xué)習(xí)找出學(xué)習(xí)2號(hào)課程的學(xué)生姓名號(hào)課程的學(xué)生姓名. select sname from student where sno in( select sno from sc where cno=2)嵌套查詢嵌套查

23、詢 例例:查詢選修了課程名為查詢選修了課程名為“信息系統(tǒng)信息系統(tǒng)”的的學(xué)生學(xué)號(hào)和姓名學(xué)生學(xué)號(hào)和姓名 (select cno from course where cname= 信息系統(tǒng)信息系統(tǒng));(select sno from sc where cno inselect sno,sname from student where sno in嵌套查詢（子查詢）嵌套查詢（子查詢）(2)any（some）謂詞的用法）謂詞的用法意義意義:where表達(dá)式的值與子查詢結(jié)果集表達(dá)式的值與子查詢結(jié)果集合中任何一個(gè)值比較的結(jié)果為真合中任何一個(gè)值比較的結(jié)果為真,整個(gè)條整個(gè)條件為真件為真.和和any配合使用的比較

24、符有配合使用的比較符有:(!=), =, , =, any (select grade from sc where cno=02);嵌套查詢嵌套查詢 例例:求選修求選修1號(hào)課程的學(xué)生的學(xué)號(hào)號(hào)課程的學(xué)生的學(xué)號(hào),成績成績,這這些學(xué)生的成績比選修些學(xué)生的成績比選修2號(hào)課程的最低成績號(hào)課程的最低成績要高要高嵌套查詢嵌套查詢 例例, 查詢其他系中比信息系任意一個(gè)查詢其他系中比信息系任意一個(gè)(其其中某一個(gè)中某一個(gè))學(xué)生年齡小的學(xué)生姓名和年齡學(xué)生年齡小的學(xué)生姓名和年齡select sname,sage from student where sage any (select sage from student

25、 where sdept= is) and sdept is ; 嵌套查詢嵌套查詢(3)all謂詞的用法謂詞的用法意義意義:where表達(dá)式的值與子查詢結(jié)果集表達(dá)式的值與子查詢結(jié)果集合中每一個(gè)值比較的結(jié)果為真合中每一個(gè)值比較的結(jié)果為真,整個(gè)條件整個(gè)條件為真為真. 和和all配合使用的比較符有配合使用的比較符有: (!=), =, , =, =all (select grade from sc where cno=2)嵌套查詢嵌套查詢 any和和all謂詞有時(shí)可以用集函數(shù)實(shí)現(xiàn)謂詞有時(shí)可以用集函數(shù)實(shí)現(xiàn) any與與all與集函數(shù)的對(duì)應(yīng)關(guān)系與集函數(shù)的對(duì)應(yīng)關(guān)系 = 或或!= =any in - maxmin= minall - not in minmax= max嵌套查詢嵌套查詢例例 ：用集函數(shù)實(shí)現(xiàn)：用集函數(shù)實(shí)現(xiàn)any例子例子sage (select max(sage) from student where sdept= is); select sname,sage fr