項(xiàng)目5學(xué)生成績(jī)查詢ppt課件

1、SQL Server 2005 數(shù)據(jù)庫(kù)根底工程五 學(xué)生成果查詢 模塊 5-1 統(tǒng)計(jì)查詢 學(xué)習(xí)目的任務(wù)義務(wù)義務(wù)1 各類職稱的教師人數(shù)的統(tǒng)計(jì) 【義務(wù)分析】 根據(jù)義務(wù)要求，此查詢要用到的數(shù)據(jù)庫(kù)為student，用到的表為teacher。最后要顯示的信息為兩列，一列是職稱的稱號(hào)，一列為該類職稱的教師人數(shù)。職稱列在表中對(duì)應(yīng)的字段為ttitle，而人數(shù)是需求統(tǒng)計(jì)的信息，這就需求用到聚合函數(shù)COUNT了。義務(wù)1 各類職稱的教師人數(shù)的統(tǒng)計(jì)【程序代碼】USE studentGOSELECT ttitle AS 職稱,COUNT(*) AS 人數(shù)FROM teacherGROUP BY ttitle義務(wù)1 各類職

2、稱的教師人數(shù)的統(tǒng)計(jì)【執(zhí)行結(jié)果】義務(wù)2 統(tǒng)計(jì)各課程的最高分、最低分和平均成果【義務(wù)分析】 根據(jù)義務(wù)要求，此查詢要用到的數(shù)據(jù)庫(kù)為student，用到的表為choice。最后要顯示的信息為四列，即課程編號(hào)、最高分、最低分和平均分。其中最高分、最低分和平均分都不是表中的列，要利用聚合函數(shù)MAX、MIN和AVG顯示信息?！境绦虼a】USE studentGOSELECT cno AS 課程編號(hào),MAX(grade) AS 最高分,MIN(grade) AS 最低分,AVG(grade) AS 平均分FROM choiceGROUP BY cno義務(wù)2 統(tǒng)計(jì)各課程的最高分、最低分和平均成果【執(zhí)行結(jié)果】義務(wù)

3、2 統(tǒng)計(jì)各課程的最高分、最低分和平均成果義務(wù)3 查詢選修了4門以上包括4門選修課的學(xué)生【義務(wù)分析】 根據(jù)義務(wù)要求，此查詢要用到的數(shù)據(jù)庫(kù)為student，用到的表為choice。最后要顯示學(xué)生學(xué)號(hào)和課程門數(shù)，這里要挑選出的學(xué)生是選修了四門課程及以上的學(xué)生學(xué)號(hào)。要完成這個(gè)義務(wù)，首先可以統(tǒng)計(jì)出每個(gè)學(xué)生選修的課程門數(shù)，然后將選修課程門數(shù)大于等于4的學(xué)生挑選出來(lái)。這樣就要用到GROUP BY和HAVING子句，HAVING子句可以對(duì)分類匯總的結(jié)果進(jìn)展挑選【義務(wù)代碼】USE studentGOSELECT sno AS 學(xué)號(hào),COUNT(*) AS 課程門數(shù)FROM choiceGROUP BY snoH

4、AVING COUNT(*)=4義務(wù)3 查詢選修了4門以上包括4門選修課的學(xué)生【執(zhí)行結(jié)果】義務(wù)3 查詢選修了4門以上包括4門選修課的學(xué)生知識(shí)闡明 聚合函數(shù) 聚合函數(shù)屬于系統(tǒng)內(nèi)置函數(shù)之一，它與前面引見的數(shù)學(xué)函數(shù)、字符串函數(shù)等內(nèi)置函數(shù)不同，它可以對(duì)一組值執(zhí)行計(jì)算并前往單一的值。聚合函數(shù)經(jīng)常與 SELECT 語(yǔ)句的 GROUP BY 子句一同運(yùn)用。除 COUNT 函數(shù)之外，聚合函數(shù)普通忽略空值。知識(shí)闡明聚合函數(shù)的種類 表51常用聚合函數(shù)的功能聚合函數(shù)功能AVG返回組中值的平均值COUNT返回組中項(xiàng)目的數(shù)量MAX返回表達(dá)式的最大值MIN返回表達(dá)式的最小值SUM返回表達(dá)式中所有值的和STDEV返回表達(dá)式

5、中所有值的統(tǒng)計(jì)標(biāo)準(zhǔn)偏差VAR返回表達(dá)式中所有值的統(tǒng)計(jì)標(biāo)準(zhǔn)方差知識(shí)闡明 GROUP BY 子句 GROUP BY語(yǔ)句從英文的字面意義上了解就是“根據(jù)(BY)一定的規(guī)那么進(jìn)展分組(GROUP)。它的作用是經(jīng)過(guò)一定的規(guī)那么將一個(gè)數(shù)據(jù)集劃分成假設(shè)干個(gè)小的區(qū)域，然后針對(duì)假設(shè)干個(gè)小區(qū)域進(jìn)展數(shù)據(jù)處置。 HAVING 子句 用于在包含 GROUP BY 子句的 SELECT 語(yǔ)句中指定顯示哪些分組記錄。在 GROUP BY 對(duì)記錄進(jìn)展組合之后，將顯示滿足 HAVING 子句條件的 GROUP BY 子句進(jìn)展分組的任何記錄。模塊 5-2 多表查詢銜接查詢學(xué)習(xí)目的任務(wù)義務(wù)義務(wù)1 查詢孫曉龍的一切選修課的成果【義務(wù)

6、分析】 此查詢要用到的數(shù)據(jù)庫(kù)為student，用到的表為student和choice。最后要顯示的信息為兩列，一列是課程編號(hào)，一列為課程成果。雖然這兩列在choice表中都有，但是本例中要顯示的是名叫孫曉龍的學(xué)生的選課信息。 處理這個(gè)問(wèn)題可以將student和choice兩表進(jìn)展內(nèi)銜接操作，然后再挑選出滿足條件的記錄，即姓名為孫曉龍的學(xué)生的選課信息?！境绦虼a】USE studentGOSELECT cno,gradeFROM student INNER JOIN choiceON student.sno=choice.snoWHERE sname=孫曉龍 義務(wù)1 查詢孫曉龍的一切選修課的成果

7、【執(zhí)行結(jié)果】義務(wù)1 查詢孫曉龍的一切選修課的成果義務(wù)2 查詢選修了課程編號(hào)為“0101001的學(xué)生的姓名和成果【義務(wù)分析】 根據(jù)義務(wù)要求，此查詢要用到的數(shù)據(jù)庫(kù)為student，用到的表為student和choice。最后要顯示的信息為兩列，一列是學(xué)生姓名，一列為課程成果。 處理這個(gè)問(wèn)題可以將student和choice兩表進(jìn)展內(nèi)銜接操作，然后再挑選出滿足條件的記錄，即課程編號(hào)為0101001的課程成果?！境绦虼a】USE studentGOSELECT sname AS 姓名,grade AS 成果FROM student INNER JOIN choice ON student.sno=ch

8、oice.snoWHERE cno=0101001義務(wù)2 查詢選修了課程編號(hào)為“0101001的學(xué)生的姓名和成果【執(zhí)行結(jié)果】義務(wù)2 查詢選修了課程編號(hào)為“0101001的學(xué)生的姓名和成果義務(wù)3 查詢選修了“Illustrator平面設(shè)計(jì)課程的學(xué)生的姓名和課程成果，并按成果降序陳列【義務(wù)分析】 根據(jù)義務(wù)要求，此查詢要用到的數(shù)據(jù)庫(kù)為student，用到的表為student、choice和course。最后要顯示的信息為兩列，一列是學(xué)生姓名，一列為課程成果。學(xué)生姓名在student表中，而課程成果那么在choice表中。 處理這個(gè)問(wèn)題可以將student、choice和course三表進(jìn)展內(nèi)銜接操作

9、，然后再挑選出滿足條件的記錄，即課程稱號(hào)為“Illustrator平面設(shè)計(jì)的課程成果?！玖x務(wù)代碼】USE studentGOSELECT A.sname AS 姓名,B.grade AS 成果FROM student AS A JOIN choice AS BON A.sno=B.sno JOIN course AS C ON Bo=CoWHERE Came=Illustrator平面設(shè)計(jì)ORDER BY B.grade DESC義務(wù)3 查詢選修了“Illustrator平面設(shè)計(jì)課程的學(xué)生的姓名和課程成果，并按成果降序陳列【執(zhí)行結(jié)果】義務(wù)3 查詢選修了“Illustrator平面設(shè)計(jì)課程的學(xué)生

10、的姓名和課程成果，并按成果降序陳列知識(shí)闡明銜接查詢銜接查詢是關(guān)系型數(shù)據(jù)庫(kù)中重要的查詢類型之一，經(jīng)過(guò)表間的相關(guān)字段，可以追蹤各個(gè)表之間的邏輯關(guān)系，從而實(shí)現(xiàn)跨表間的查詢。知識(shí)闡明銜接查詢的種類 表52 JOIN關(guān)鍵字的含義連接類型連接符號(hào)備注左外連接LEFT JOIN外連接右外連接RIGHT JOIN全外連接FULL JOIN交叉連接CROSS JOIN交叉連接內(nèi)連接INNER JOININNER可省略知識(shí)闡明內(nèi)銜接的種類等值銜接 在銜接條件中運(yùn)用等號(hào)(=)運(yùn)算符比較被銜接列的列值，其查詢結(jié)果中列出被銜接表中的任何列，包括其中的反復(fù)列。自然銜接 自然銜接是等值銜接的一種特殊情況，即在銜接條件中運(yùn)用

11、等于(=)運(yùn)算符比較被銜接列的列值，但它運(yùn)用選擇列表指出查詢結(jié)果集合中所包括的列，并刪除銜接表中的反復(fù)列。不等值銜接 在銜接條件運(yùn)用除等于運(yùn)算符以外的其他比較運(yùn)算符比較被銜接的列的列值。這些運(yùn)算符包括、=、=、!和。 模塊 5-3 多表查詢子查詢學(xué)習(xí)目的任務(wù)義務(wù)義務(wù)1 查詢和“孫曉龍同班的學(xué)生信息【義務(wù)分析】 根據(jù)義務(wù)要求，此查詢要用到的數(shù)據(jù)庫(kù)為student，用到的表為student。最后要顯示的信息為與孫曉龍同班的學(xué)生的一切信息。 處理這個(gè)問(wèn)題可以分兩步走，首先查出孫曉龍所在的班級(jí)編碼，然后再以班級(jí)編碼作為查詢條件找出該班一切學(xué)生的信息。子查詢的本質(zhì)就是嵌套查詢，內(nèi)層查詢的結(jié)果作為外層查詢

12、的條件來(lái)進(jìn)展查詢。【程序代碼】USE studentGOSELECT *FROM studentWHERE classno = (SELECT classno FROM student WHERE sname=孫曉龍)義務(wù)1 查詢和“孫曉龍同班的學(xué)生信息 【執(zhí)行結(jié)果】義務(wù)1 查詢和“孫曉龍同班的學(xué)生信息義務(wù)2 查詢比“07010111班學(xué)生入學(xué)成果都高的其他班的學(xué)生的學(xué)號(hào)和姓名 【義務(wù)分析】 根據(jù)義務(wù)要求，此查詢要用到的數(shù)據(jù)庫(kù)為student，用到的表為student。最后要顯示的信息為三列，分別是學(xué)生學(xué)號(hào)、姓名和班級(jí)編碼。 處理這個(gè)問(wèn)題可以利用帶ALL的子查詢，首先從student表中找出“

13、07010111班的一切學(xué)生的入學(xué)成果，然后再將此作為父查詢的條件，從表student找出比該班一切學(xué)生的入學(xué)成果均高且不在該班的學(xué)生信息?！境绦虼a】USE studentGOSELECT sno,sname,classnoFROM studentWHERE sscore ALL(SELECT sscore FROM student WHERE classno= 07010111) AND classno 07010111義務(wù)2 查詢比“07010111班學(xué)生入學(xué)成果都高的其他班的學(xué)生的學(xué)號(hào)和姓名【執(zhí)行結(jié)果】義務(wù)2 查詢比“07010111班學(xué)生入學(xué)成果都高的其他班的學(xué)生的學(xué)號(hào)和姓名義務(wù)3

14、查詢選修課考試不及格的學(xué)生的學(xué)號(hào)和姓名【義務(wù)分析】 根據(jù)義務(wù)要求，此查詢要用到的數(shù)據(jù)庫(kù)為student，用到的表為student和choice。最后要顯示的信息為兩列，一列是學(xué)生學(xué)號(hào)，一列為學(xué)生姓名。 處理這個(gè)問(wèn)題可以利用子查詢，首先從choice表中找出考試不及格的學(xué)生的學(xué)號(hào)，然后再將此作為父查詢的條件，從表student找出學(xué)生的姓名。義務(wù)4 查詢選修了課程編號(hào)為“0101001的學(xué)生的學(xué)號(hào)和姓名【義務(wù)代碼】USE studentGOSELECT sno,snameFROM studentWHERE EXISTS (SELECT * FROM choice WHERE student.sno = choice.sno AND cno=0101001)【執(zhí)行結(jié)果】義務(wù)4 查詢選修了課程編號(hào)為“0101001的學(xué)生的學(xué)號(hào)和姓名知識(shí)闡明子查詢子查詢也稱為內(nèi)部查詢，而包含子查詢的語(yǔ)句也稱為外部查詢或父查詢。子查詢是一個(gè)SELECT 語(yǔ)句，它嵌套在一個(gè) SELECT語(yǔ)句、SELECT.INTO 語(yǔ)句、INSERT.INTO 語(yǔ)句、DELETE 語(yǔ)句、或 UPDATE 語(yǔ)句或嵌套在另一子查詢中。子查詢的 SELECT 查詢總是運(yùn)用圓括號(hào)括起來(lái)。它不能包含 COMPUTE 或者 FOR BROWSE 子句，假好像時(shí)指定了TOP 子