數(shù)據(jù)庫基礎(chǔ)之數(shù)據(jù)查詢教學課件(79張)

1、第5章 數(shù)據(jù)查詢 本章學習目標 l 掌握SELECT語句結(jié)構(gòu) l 熟練使用SELECT語句查詢數(shù)據(jù)5.1 SELECT語句結(jié)構(gòu) SELECT在任何一種SQL語言中，都是使用頻率最高的語句，它具有強大的查詢功能，有的用戶甚至只需要熟練掌握SELECT語句的一部分，就可以輕松地利用數(shù)據(jù)庫來完成自己的工作?？梢哉fSELECT是SQL語言的靈魂。SELECT語句的作用是讓數(shù)據(jù)庫服務器根據(jù)客戶端的要求搜尋出用戶所需要的信息資料，并按用戶規(guī)定的格式進行整理后返回給客戶端。用戶使用SELECT語句除可以查看普通數(shù)據(jù)庫中的表格和視圖的信息外，還可以查看SQL Server的系統(tǒng)信息。SELECT語句的語法格式

2、如下：SELECT select_list INTO new_table_name FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC 其中： l select_list 指明要查詢的選擇列表。列表可以包括若干個列名或表達式，列名或表達式之間用逗號隔開，用來指示應該返回哪些數(shù)據(jù)。表達式可以是列名、函數(shù)或常數(shù)的列表。l INTO new_table_name 指定用查詢的結(jié)果創(chuàng)建成一個新表

3、。new_table_name為新表名稱。lFROM table_source 指定所查詢的表或視圖的名稱。lWHERE search_condition 指明查詢所要滿足的條件。lGROUP BY group_by_expression 根據(jù)指定列中的值對結(jié)果集進行分組。l HAVING search_condition 對用FROM、WHERE或GROUP BY子句創(chuàng)建的中間結(jié)果集進行行的篩選。它通常與GROUP BY子句一起使用。l ORDER BY order_expression ASC | DESC 對查詢結(jié)果集中的行重新排序。ASC 和DESC關(guān)鍵字分別用于指定按升序或降序排序。

4、如果省略ASC或DESC，則系統(tǒng)默認為升序。5.2 使用SELECT語句查詢數(shù)據(jù)5.2.1 使用SELECT子句SELECT子句的語法為：SELECT ALL | DISTINCT TOP n PERCENT WITH TIES 其中：l ALL關(guān)鍵字 為默認設(shè)置，用于指定查詢結(jié)果集的所有行，包括重復行。l DISTINCT 用于刪除結(jié)果集中重復的行。l TOP n PERCENT 指定只返回查詢結(jié)果集中的前n行。如果加了PERCENT，則表示只返回查詢結(jié)果集中的前n%行。 WITH TIES 用于指定從基本結(jié)果集中返回附加的行。 l select_list 指明要查詢的選擇列表。列表可以包括

5、若干個列名或表達式，列名或表達式之間用逗號隔開，用來指示應該返回哪些數(shù)據(jù)。如果使用星號*則表示返回FROM子句中指定的表或視圖中的所的列。表達式可以是列名、函數(shù)或常數(shù)的列表?！纠?-1】查詢學生基本信息表中的所有信息。在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 學生基本信息表GO運行結(jié)果如圖5-1所示，它將學生基本信息表的所有信息均顯示出來。圖5-1 查詢學生基本信息表中的所有信息【例5-2】在學生基本信息表中查詢學生的學號、姓名、性別和族別信息。在查詢分析器中運行如下命令：USE XSCJGOSELECT 學號,姓名,性別,族別 FROM 學生基本信息表GO

6、運行結(jié)果如圖5-2所示。圖5-2 在學生基本信息表中查詢學生的學號、姓名、性別和族別信息【例5-3】從學生基本信息表中查詢學生由幾個民族構(gòu)成。 從例2結(jié)果可知，學生的族別有多行重復，要快速查詢學生的民族構(gòu)成，實際上就是對相同值的族別只需要顯示一行，可使用DISTINCT關(guān)鍵字實現(xiàn)。在查詢分析器中運行如下命令：USE XSCJGOSELECT DISTINCT 族別 FROM 學生基本信息表GO運行結(jié)果如圖5-3所示。圖5-3 從學生基本信息表中查詢學生由幾個民族構(gòu)成【例5-4】顯示課程信息表中前5行的信息。在查詢分析器中運行如下命令：USE XSCJGOSELECT TOP 5 * FROM

7、學生基本信息表GO運行結(jié)果如圖5-4所示，只顯示查詢結(jié)果的前5行數(shù)據(jù)。 圖5-4 只顯示查詢結(jié)果的前5行數(shù)據(jù) 【例5-5】從學生基本信息表中只顯示5%的信息。在查詢分析器中運行如下命令：USE XSCJGOSELECT TOP 5 PERCENT * FROM 學生基本信息表GO 運行結(jié)果如圖5-5所示。學生基本信息表共有106行數(shù)據(jù)，6行約占106行的5%，所以使用TOP 5 PERCENT檢索前5%的數(shù)據(jù)行，其結(jié)果是只顯示6行信息。 圖5-5 顯示基本信息表5%的數(shù)據(jù)行，其結(jié)果是只顯示6行信息5.2.2 使用INTO子句使用INTO子句可以創(chuàng)建一個新表，并將查詢結(jié)果直接插入到新表中。但是用

8、戶必須在要創(chuàng)建新表的數(shù)據(jù)庫中擁有CREATE TABLE權(quán)限，而且INTO子句不能與COMPUTE子句一起使用?！纠?-6】從學生基本信息表中查詢所有團員的信息資料，并形成新表為團員基本信息表。在查詢分析器中運行如下命令：USE XSCJGOSELECT * INTO 團員基本信息表 FROM 學生基本信息表WHERE 政治面貌=團員GOSELECT * FROM 團員基本信息表GO運行結(jié)果如圖5-6所示。圖5-6 使用INTO子句 5.2.3 使用FROM子句 FROM子句用于指定SELECT語句查詢的源表、視圖、派生表和聯(lián)接表，中間用逗號隔開。在FROM子句中最多可以使用16個表或視圖?！?/p>

9、例5-7】從成績表中查詢學生成績。在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 成績表GO運行結(jié)果如圖5-7所示。 圖5-7 使用FROM子句 【例5-8】從相關(guān)表中查詢每一位學生的學號、姓名、課程名稱、成績。從各表數(shù)據(jù)可知，“學號”存在于“學生基本信息表”和“成績表”，“姓名”存在于“學生基本信息表”，“課程名稱”存在于“課程信息表”，“成績”存在于“成績表”，要實現(xiàn)本例查詢，則需要對“學生基本信息表”、“課程信息表”、“成績表”進行多表檢索，也可以來自不同的數(shù)據(jù)庫。在查詢分析器中運行如下命令：USE XSCJGOSELECT 學生基本信息表.學號,姓名,課程名

10、稱,成績 FROM 學生基本信息表,課程信息表,成績表WHERE 學生基本信息表.學號=成績表.學號 AND 課程信息表.課程編號=成績表.課程編號GO運行結(jié)果如圖5-8所示。 圖5-8 從相關(guān)表中查詢每一位學生的學號、姓名、課程名稱、成績 5.2.4 使用WHERE子句 WHERE子句用于指明查詢所要滿足的條件。通常情況下，必須定義一個或多個條件限制檢索選擇的數(shù)據(jù)行。WHERE子句后跟邏輯表達式，結(jié)果集將返回表達式為真的數(shù)據(jù)行。 在WHERE子句中，可以包含比較運算符、邏輯運算符。比較運算符有：=（等于）、（大于）、=（對于等于）、（小于）、=（小于等于）、（不等于）、!（不大于）、!198

11、5-01-01 AND 性別=女；“成績60分以上”可表示為：成績=60或者WHERE NOT (成績60)等。 【例5-9】在課程信息表中查找“Delphi程序設(shè)計”課程的任課老師。在查詢分析器中運行如下命令： USE XSCJGOSELECT 任課教師 FROM 課程信息表WHERE 課程名稱=Delphi程序設(shè)計GO運行結(jié)果如圖5-9所示。 圖5-9 在課程信息表中查找“Delphi程序設(shè)計”課程的任課老師 【例5-10】查詢少數(shù)民族學生的基本情況。在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 學生基本信息表WHERE 族別漢族GO運行結(jié)果如圖5-10所示。

12、圖5-10 查詢少數(shù)民族學生的基本情況 【例5-11】檢索1985年1月1日以后出生的女生基本信息。在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 學生基本信息表WHERE 出生日期1985-01-01 AND 性別=女GO運行結(jié)果如圖5-11所示。 圖5-11 檢索1985年1月1日以后出生的女生基本信息 5.2.5 使用GROUP BY子句 本子句寫在WHERE子句之后，用于對查詢的結(jié)果集進行分組。當使用GROUP BY子句進行分組時，SELECT子句的選項列表中可以包含聚合函數(shù)，但子句后的各列或包含在聚合函數(shù)中或包含在GROUP BY子句中，否則，SQL Se

13、rver將返回如下錯誤信息：“表名.列名在選擇列表中無效，因為該列既不包含在聚合函數(shù)中，也不包含在GROUP BY子句中?！薄纠?-12】查詢每位同學的課程門數(shù)、總成績、平均成績。查詢每位學生的課程成績情況，實際上就是按照“學號”列分類統(tǒng)計，可使用GROUP BY 學號子句，統(tǒng)計課程門數(shù)、總成績、平均成績分別可以使用聚合函數(shù)COUNT（課程編號）、SUM（成績）、AVG（成績）。在查詢分析器中運行如下命令：USE XSCJGOSELECT 學號,COUNT(課程編號) AS 課程門數(shù),SUM(成績) AS 總成績,AVG(成績) AS 平均成績 FROM 成績表GROUP BY 學號GO運行結(jié)

14、果如圖5-12所示。 圖5-12 查詢每位同學的課程門數(shù)、總成績、平均成績 5.2.6 HAVING子句 HAVING用于限定組或聚合函數(shù)的查詢條件，通常用在GROUP BY子句之后。通常，其作用與WHERE子句基本一樣。但WHERE子句是對原始記錄進行過濾，HAVING子句對查詢結(jié)果進行過濾，而且SELECT子句和HAVING子句中可以使用聚合函數(shù)，WHERE子句中不能使用聚合函數(shù)。 【例5-13】從學生基本信息表中統(tǒng)計各民族學生人數(shù)。此例實際上是將要對學生按民族進行分類統(tǒng)計，可使用聚合函數(shù)COUNT(族別)實現(xiàn)功能。在查詢分析器中運行如下命令：USE XSCJGOSELECT 族別,COU

15、NT(族別) AS 學生人數(shù) FROM 學生基本信息表GROUP BY 族別GO運行結(jié)果如圖5-13所示。 圖5-13 從學生基本信息表中統(tǒng)計各民族學生人數(shù) 【例5-14】從學生基本信息表中統(tǒng)計漢族學生的人數(shù)。此例就是在上例統(tǒng)計出各民族學生人數(shù)的基礎(chǔ)上進一步限定查詢漢族學生人數(shù)，可在GROUP BY子句之后跟HAVING 族別=漢族子句實現(xiàn)此功能。在查詢分析器中運行如下命令：USE XSCJGOSELECT 族別,COUNT(族別) AS 學生人數(shù) FROM 學生基本信息表GROUP BY 族別HAVING 族別=漢族GO運行結(jié)果如圖5-14所示。圖5-14 從學生基本信息表中統(tǒng)計漢族學生的人

16、數(shù) 此例也可使用WHERE子句完成功能。USE XSCJGOSELECT 族別,COUNT(族別) AS 學生人數(shù) FROM 學生基本信息表WHERE 族別=漢族GROUP BY 族別GO運行結(jié)果如圖5-15所示。 圖5-15 使用WHERE子句完成功能 【例5-15】顯示平均成績大于等于80分以上的學生情況。此例的限定條件是AVG(成績)=80，只能使用HAVING子句，如果使用WHERE子句限定條件，則系統(tǒng)會顯示如圖5-16所示的錯誤信息。錯誤使用WHERE子句的SELECT語句如下：USE XSCJGOSELECT 學號,AVG(成績) AS 平均成績 FROM 成績表WHERE AVG

17、(成績)=80GROUP BY 學號GO運行結(jié)果如圖5-16所示。 圖5-16 顯示平均成績大于等于80分以上的學生情況 使用HAVING子句的正確語句如下：USE XSCJGOSELECT 學號,AVG(成績) AS 平均成績 FROM 成績表GROUP BY 學號HAVING AVG(成績)=80GO運行結(jié)果如圖5-17所示。 圖5-17 使用HAVING子句查詢平均成績大于等于80分以上的學生情況 5.2.7 ORDER BY子句 ORDER BY子句對查詢結(jié)果集中的行進行重新排序。ASC 和DESC關(guān)鍵字分別用于指定按升序或降序排序。如果省略ASC或DESC，則系統(tǒng)默認為升序?？梢栽贠

18、RDER BY子句中指定多個排序列，即嵌套排序，檢索結(jié)果首先按第1列進行排序，對第1列值相同的那些數(shù)據(jù)行，再按照第2列排序依此類推。要求ORDER BY子句要寫在WHERE子句的后面，而且在ORDER BY子句中不能使用ntext、text和image列。【例5-16】將學生平均成績按升序排序。 在查詢分析器中運行如下命令：USE XSCJGOSELECT 學號,AVG(成績) AS 平均成績 FROM 成績表GROUP BY 學號ORDER BY AVG(成績)GO運行結(jié)果如圖5-18所示，省略關(guān)鍵字ASC，系統(tǒng)默認為升序排序。 圖5-18 將學生平均成績按升序排序 【例5-17】查詢成績表

19、中的全部信息，要求查詢結(jié)果首先按學號升序排序，學號相同時，按成績降序排序。 在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 成績表ORDER BY 學號,成績 DESCGO 運行結(jié)果如圖5-19所示。 圖5-19 查詢成績表中的全部信息 5.2.8 使用COMPUTE子句 COMPUTE子句用在WHERE子句之后，用來計算總計并進行分組小計，可使用聚合函數(shù)，總計值或小計值將作為附加新行出現(xiàn)在檢索結(jié)果中。使用COMPUTE子句時必須使用ORDER BY對COMPUTE BY中BY指定的列進行排序，否則將出現(xiàn)錯誤信息。 【例5-18】按學號顯示學生成績，并計算每人的平均

20、成績和總成績。 此例要求按人對課程及成績進行分組顯示，并計算每人的平均成績、總成績。則顯示成績應按學號分類，分組計算平均成績、總成績的語句為COMPUTE AVG(成績),SUM(成績) BY 學號，使用COMPUTE BY子句首先要用ORDER BY子句對要分組的學號列排序，即ORDER BY 學號。在查詢分析器中運行如下命令：USE XSCJGOSELECT *FROM 成績表ORDER BY 學號COMPUTE AVG(成績),SUM(成績) BY 學號GO運行結(jié)果如圖5-20所示。 圖5-20 按學號顯示學生成績，并計算每人的平均成績和總成績 5.3 使用其它子句或關(guān)鍵字查詢數(shù)據(jù)5.3

21、.1 聯(lián)合查詢 聯(lián)合查詢是指將兩個或兩個以上的SELECT語句通過UNION運算符連接起來的查詢。聯(lián)合查詢可以將兩個或更多查詢的結(jié)果組合為單個結(jié)果集，該結(jié)果集包含聯(lián)合查詢中所有查詢的全部行。使用UNION組合多個查詢的結(jié)果時，必須注意：所有查詢中的列數(shù)和列的順序必須相同且數(shù)據(jù)類型必須兼容。【例5-19】從系部表中檢索系部名稱，從班級表中檢索班級名稱。從系部表中檢索系部名稱的SELECT語句為：SELECT 系部名稱 FROM 系部表，從班級表中檢索班級名稱的SELECT語句為：SELECT 班級名稱 FROM 班級表，合并這兩個查詢結(jié)果，需要使用UNION運算符。在查詢分析器中運行如下命令：

22、USE XSCJGOSELECT 系部名稱 FROM 系部表UNIONSELECT 班級名稱 FROM 班級表GO 運行結(jié)果如圖5-21所示，UNION結(jié)果集的列標題取自第一個SELECT語句。 圖5-21 UNION結(jié)果集的列標題取自第一個SELECT語句 5.3.2 檢索某一范圍內(nèi)的信息 檢索在某一范圍內(nèi)的信息，需要使用WHERE子句限定查詢條件，這個條件通常是一個邏輯表達式。在表達式中除了可以使用比較運算符=（等于）、（大于）、（不等于）等外，還可使用范圍運算符BETWEEN、NOT BETWEEN、IN、LIKE、IS NULL等，邏輯運算符NOT（非）、OR（或）、AND（與）等來限

23、定查詢條件。 1使用BETWEEN關(guān)鍵字 BETWEEN關(guān)鍵字總是與AND一起使用，用來檢索在一個指定范圍內(nèi)的信息，NOT BETWEEN檢索不在某一范圍內(nèi)的信息。 【例5-20】查詢1985年出生的學生基本信息。 1985年出生的學生即出生日期在1985年1月1日至12月31日之間的學生。在查詢分析器中運行如下命令： USE XSCJ GO SELECT * FROM 學生基本信息表WHERE 出生日期 BETWEEN 1985-01-01 AND 1985-12-31GO 運行結(jié)果如圖5-22所示。 圖5-22 查詢1985年出生的學生基本信息 【例5-21】查詢不及格學生成績信息。 查詢

24、不及格學生成績信息，也就是查詢059之間的學生成績，可用BETWEEN關(guān)鍵字表示為：WHERE 成績 BETWEEN 0 AND 59。在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 成績表WHERE 成績 BETWEEN 0 AND 59GO運行結(jié)果如圖5-23所示。圖5-23 查詢不及格學生成績信息 2使用IN關(guān)鍵字 IN關(guān)鍵字允許用戶選擇與列表中的值相匹配的行，指定項必須用括號括起來，并用逗號隔開，表示“或”的關(guān)系。NOT IN表示含義正好相反。 【例5-22】查詢課程編號為002、003、007、014的課程編號、課程名稱、任課教師和上課時間。課程編號為00

25、2、003、007、014可以寫成：WHERE 課程編號 IN(002,003,007,014)，也可寫成WHERE 課程編號=002 OR課程編號=003 OR課程編號=007 OR課程編號=014 。顯然，使用IN關(guān)鍵字進行檢索比使用3個OR運算符進行檢索更為簡單，而且易于理解和閱讀。在查詢分析器中運行如下命令： USE XSCJGOSELECT 課程編號,課程名稱,任課教師,上課時間 FROM 課程信息表WHERE 課程編號 IN(002,003,007,014) GO運行結(jié)果如圖5-24所示 圖5-24 查詢課程編號為002、003、007、014的課程編號、課程名稱、任課教師和上課時

26、間 在查詢分析器中運行以下命令，也可得到相同的查詢結(jié)果，但這種寫法顯然比較繁瑣。USE XSCJGOSELECT 課程編號,課程名稱,任課教師,上課時間 FROM 課程信息表WHERE 課程編號=002 OR課程編號=003 OR課程編號=007 OR課程編號=014 GO3使用LIKE關(guān)鍵字 LIKE關(guān)鍵字用于查詢與指定的某些字符串表達式模糊匹配的數(shù)據(jù)行。LIKE后的表達式被定義為字符串，必須用單引號（）括起來，字符串中可以使用4種通配符。它們是：l %：可匹配任意類型和長度的字符串。l _（下劃線）：可匹配任何單個字符。l ：指定范圍或集合中的任何單個字符。l ：不屬于指定范圍或集合的任何

27、單個字符。 例如：LIKE 劉%匹配以“劉”開始的字符串；LIKE %技術(shù)%匹配的是前后字符為任意，中間含有“技術(shù)”兩個字的字符串；LIKE _秀%匹配的是第2個字為“秀”的任意字符串。 a-i匹配的是a、b、c、d、e、f、g、h、I單個字符；LIKE mw-z %匹配所有以字母m開始并且第2個字母不為w、x、y、z的所有字符串。 【例5-23】檢索所有姓劉的學生基本信息。匹配所有姓劉的學生可以表示為：姓名 LIKE 劉%。在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 學生基本信息表WHERE 姓名 LIKE 劉% GO運行結(jié)果如圖5-25所示。 圖5-25 檢

28、索所有姓劉的學生基本信息 【例5-24】檢索包含“技術(shù)”兩字的課程信息。匹配“技術(shù)”兩字的課程名稱可以表示為：課程名稱 LIKE %技術(shù)%。在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 課程信息表WHERE 課程名稱 LIKE %技術(shù)% GO運行結(jié)果如圖5-26所示。 圖5-26 檢索包含“技術(shù)”兩字的課程信息 【例5-25】檢索少數(shù)民族學生的基本信息。少數(shù)民族學生或以表示為：WHERE 族別 NOT LIKE 漢族。在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 學生基本信息表WHERE 族別 NOT LIKE 漢族 GO運行結(jié)果如圖

29、5-27所示。 圖5-27 檢索少數(shù)民族學生的基本信息 【例5-26】查詢第2 個字為“麗”的學生信息。在學生基本信息表中，匹配第2 個字為“麗”的學生姓名應表示為：姓名 LIKE _麗%。在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 學生基本信息表WHERE 姓名 LIKE _麗% GO運行結(jié)果如圖5-28所示。 圖5-28 查詢第2 個字為“麗”的學生信息 4、使用IS NULL關(guān)鍵字IS NULL 關(guān)鍵字可以檢索數(shù)據(jù)列中沒有賦值的行?！纠?-27】查詢課程信息表中教師未定的課程信息。課程信息表中教師未定的表達式可以表示為：WHERE 任課教師 IS NULL

30、。在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 課程信息表WHERE 任課教師 IS NULL GO運行結(jié)果如圖5-29所示，因為沒有任課教師為空（NULL）的課程，所以查詢結(jié)果為0行。圖5-29 查詢課程信息表中教師未定的課程信息 5.3.3 指定結(jié)果集的列的別名有時需要為查詢結(jié)果集中的某些列增加可讀性或者為沒有名稱的導出列指定名稱，可使用AS子句?！纠?-28】統(tǒng)計成績表中各門課程的學生人數(shù)、總成績、平均成績。統(tǒng)計成績表中各門課程信息，需要將學生成績按課程編號分組GROUP BY 課程編號，統(tǒng)計學生人數(shù)、總成績、平均成績分別需要使用聚合函數(shù)COUNT（學號）、

31、SUM（成績）、AVG（成績）。因為新生成的學生人數(shù)、總成績、平均成績?nèi)袥]有列名，所以可使用AS子句實現(xiàn)。 在查詢分析器中運行如下命令：USE XSCJGOSELECT 課程編號,COUNT(學號) AS 學生人數(shù),SUM(成績) AS 總成績,AVG(成績) AS 平均成績 FROM 成績表GROUP BY 課程編號GO運行結(jié)果如圖5-30所示。 圖5-30 統(tǒng)計成績表中各門課程的學生人數(shù)、總成績、平均成績 5.3.4 子查詢子查詢是在查詢中包含另一個查詢的查詢。它本身是一個SELECT查詢，可以代替表達式出現(xiàn)在WHERE子句中。它返回單個值且嵌套在 SELECT、INSERT、UPDAT

32、E、DELETE 語句或其它子查詢中。任何允許使用表達式的地方都可以使用子查詢。子查詢的 SELECT 查詢總是使用圓括號括起來，且不能包括 COMPUTE子句，如果同時指定 TOP 子句，則可能只包括 ORDER BY 子句。 【例5-37】檢索單科成績高于全班平均分的學生成績信息。此例中，全班平均成績?yōu)镾ELECT AVG(成績) AS 平均成績 FROM 成績表，單科成績高于全班平均分可以表述為WHERE 成績(SELECT AVG(成績) FROM 成績表)。在查詢分析器中運行如下命令：USE XSCJGOSELECT AVG(成績) AS 平均成績 FROM 成績表GOSELECT

33、* FROM 成績表WHERE 成績(SELECT AVG(成績) FROM 成績表)GO運行結(jié)果如圖5-39所示。 圖5-39 檢索單科成績高于全班平均分的學生成績信息 5.4 連接查詢用戶在前面所作的查詢大多是對單個表進行的查詢，而在數(shù)據(jù)庫的應用中，經(jīng)常需要從多個相關(guān)的表中查詢數(shù)據(jù)，這就需要使用連接查詢。用戶通過連接可以使用一個表中的數(shù)據(jù)來查詢其他表的數(shù)據(jù)，從而大大增加了靈活性。由于連接涉及多個表及其之間的引用，所以列的引用均必須明確，對于重復的列名必須用表名限定。對多個表或視圖進行查詢，需要在FROM子句或WHERE子句中定義連接條件。在FROM子句中定義連接的語法形式為：FROM 表1

34、 連接類型 JOIN 表2 ON 表1.列=表2.列在WHERE子句中定義連接的語法形式為：FROM 表1,表2WHERE表1.列 連接操作 表2.列但由于在FROM子句中指定連接條件有助于區(qū)分連接條件與WHERE子句中指定的搜索條件，所以建議使用FROM子句的方法。連接的類型有內(nèi)連接、外連接、交叉連接3種。5.4.1 內(nèi)聯(lián)接內(nèi)連接（INNER JOIN）是組合兩個表的常用方法，它將兩個表中的列進行比較，將兩個表中滿足連接條件的行組合起來，作為結(jié)果。內(nèi)連接有等值連接、自然連接和不等值連接3種。1相等連接相等連接是將要連接的列值使用等值運算符（=）作相等比較后所作的連接，返回所有列（包括重復列）

35、。因為連接的列要顯示兩次，所以會產(chǎn)生冗余?！纠?-29】檢索系部信息和班級信息。此例要檢索系部表和班級表的所有信息，即顯示兩個表的所有信息?？稍赟ELECT子句中使用*、系部表.*或班級表.*，連接條件是兩個表的系部編號的值要相等，即系部表.系部編號=班級表.系部編號。 在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 系部表,班級表WHERE 系部表.系部編號=班級表.系部編號GO運行結(jié)果如圖5-31所示，檢索結(jié)果中有完全相同的兩列系部編號，數(shù)據(jù)產(chǎn)生了冗余。 5-31 檢索系部信息和班級信息 使用ANSI連接語法的SELECT語句如下：USE XSCJGOSELEC

36、T * FROM 系部表 INNER JOIN 班級表ON 系部表.系部編號=班級表.系部編號GO運行結(jié)果與圖5-31所示相同。2自然連接自然連接是將要連接的列作相等比較的連接，但連接的列只顯示一次，因而消除了等值連接產(chǎn)生的冗余。【例5-30】檢索系部信息和班級信息，要求連接的列只顯示一次。本例與上例的區(qū)別是對連接的列只顯示一列，用SELECT子句可以寫成：SELECT 系部表.*,班級編號,班級名稱。在查詢分析器中運行如下命令:USE XSCJGOSELECT 系部表.*,班級編號,班級名稱 FROM 系部表,班級表WHERE 系部表.系部編號=班級表.系部編號GO運行結(jié)果如圖5-32所示。

37、 5-32 檢索系部信息和班級信息，要求連接的列只顯示一次 使用ANSI連接語法的SELECT語句如下：USE XSCJGOSELECT 系部表.*,班級編號,班級名稱 FROM 系部表 INNER JOIN 班級表ON 系部表.系部編號=班級表.系部編號GO運行結(jié)果與圖5-32所示相同。3、不等值連接不等值連接就是在連接時不使用等值運算符，而采用比較運算符進行連接?！纠?-31】檢索沒有錄入成績的課課程情況。在查詢分析器中運行如下命令:USE XSCJGOSELECT DISTINCT 課程信息表.* FROM 課程信息表,成績表WHERE 課程信息表.課程編號成績表.課程編號GO運行結(jié)果如

38、圖5-33所示。 5-33 檢索沒有錄入成績的學生基本信息 5.4.2 外連接外連接（OUTER JOIN）只限制一個表，而對另外一個表不加限制（即所有的行都出現(xiàn)在結(jié)果集中）。外連接分為左外連接（LEFT OUTER JOIN）、右外連接（RIGHT OUTER JOIN）和全外連接（FULL OUTER JOIN）。括號中為使用FROM子句定義外連接的關(guān)鍵字，使用中可以省略O(shè)UTER。1、左外連接（LEFT OUTER JOIN）左外連接對連接條件中左邊的表不加限制。左外連接需要在FROM子句中采用下列語法格式：FROM 左表名 LEFT OUTER JOIN 右表名 ON 連接條件 【例5

39、-32】使用左外連接檢索學生成績信息（學號，姓名，課程名稱）。 在查詢分析器中運行如下命令:USE XSCJGOSELECT 學號,課程信息表.課程編號,課程信息表.課程名稱,成績 FROM 課程信息表 LEFT JOIN 成績表 ON 課程信息表.課程編號=成績表.課程編號GO運行結(jié)果如圖5-34所示（所影響的行數(shù)為 343 行）。 圖5-34 使用左外連接檢索學生成績信息 2右外連接（RIGHT OUTER JOIN）右外連接對右邊的表不加限制。右外連接需要在FROM子句采用下列語法格式：FROM 左表名 RIGHT OUTER JOIN 右表名 ON 連接條件【例5-33】使用右外連接檢

40、索學生成績信息（學號，姓名，課程名稱）。 在查詢分析器中運行如下命令:USE XSCJGOSELECT 學號,課程信息表.課程編號,課程信息表.課程名稱,成績 FROM 課程信息表 RIGHT JOIN 成績表 ON 課程信息表.課程編號=成績表.課程編號GO運行結(jié)果如圖5-35所示（所影響的行數(shù)為 322 行）。 圖5-35 使用右外連接檢索學生成績信息3、全外連接（FULL OUTER JOIN）全外連接對兩個表都不加限制，所有兩個表中的行都會包括在結(jié)果集中。使用全外連接需要在FROM子句采用下列語法格式：FROM 左表名 FULL OUTER JOIN 右表名 ON 連接條件【例5-34

41、】使用全外連接檢索學生成績信息（學號，姓名，課程名稱）。 在查詢分析器中運行如下命令:USE XSCJGOSELECT 學號,課程信息表.課程編號,課程信息表.課程名稱,成績 FROM 課程信息表 FULL JOIN 成績表 ON 課程信息表.課程編號=成績表.課程編號GO運行結(jié)果如圖5-36所示（所影響的行數(shù)為 343 行）。 圖5-36 使用全外連接檢索學生成績信息 5.4.3 交叉聯(lián)接（CROSS JOIN）交叉連接也叫非限制連接，它將兩個表不加任何約束地組合起來。在數(shù)學上，就是兩個表的笛卡爾積。交叉連接后得到的結(jié)果集的行數(shù)是兩個被連接表的行數(shù)的乘積。【例5-35】計算系部表和班級表的交

42、叉連接。在查詢分析器中運行如下命令：USE XSCJGOSELECT * FROM 班級表 CROSS JOIN 系部表GO運行結(jié)果如圖5-37所示，檢索結(jié)果為48行，由班級表的8行和系部表的6行組合而成（68=48），由連接結(jié)果可以看出，這種交叉連接的結(jié)果沒有實際意義。 圖5-37 計算系部表和班級表的交叉連接此例也可用FROM子句寫成如下語句，運行結(jié)果相同。USE XSCJGOSELECT * FROM 班級表,系部表GO 在實際應用中使用交叉連接產(chǎn)生的結(jié)果集一般沒有什么意義，但在數(shù)據(jù)庫的數(shù)學模式上有重要的作用。5.4.4 自聯(lián)接（SELF JOIN）自連接就是一個表與它自身的不同行進行連

43、接。因為表名要在FROM子句中出現(xiàn)兩次，所以需要對表指定兩個別名，使之在邏輯上成為兩張表。在SELECT子句中引用的列名也要使用表的別名進行限定。【例5-36】查找同名同姓的學生信息。該例是對學生基本信息表進行行自連接，這里將學生基本信息表分別定義別名為A1、A2，將FROM子句寫成FROM 學生基本信息表 A1,學生基本信息表 A2，連接條件為WHERE A1.姓名=A2.姓名 AND A1.學號A2.學號。 在查詢分析器中運行如下命令：USE XSCJGOSELECT A1.* FROM 學生基本信息表 A1,學生基本信息表 A2WHERE A1.姓名=A2.姓名 AND A1.學號A2.

44、學號GO運行結(jié)果如圖5-38所示 圖5-38 使用自連接查找同名同姓的學生信息 本章小結(jié) 本章主要介紹了SELECT語句在數(shù)據(jù)查詢中的應用技術(shù)，SELECT語句在SQL語言中功能最為強大，應用最為廣泛。要求同學們掌握SELECT語句結(jié)構(gòu)，能夠熟練使用SELECT語句查詢數(shù)據(jù)。練習與上機一選擇題1、在SELECT語句中，下列哪種子句用于選擇列表（ ）。A、SELECT子句 B、INTO子句 C、FROM 子句 D、WHERE子句2、在SELECT語句中，下列哪種子句用于將查詢結(jié)果存儲在一個新表中（ ）。A、SELECT子句 B、INTO子句 C、FROM 子句 D、WHERE子句3、在SELEC

45、T語句中，下列哪種子句用于指出所查詢的數(shù)據(jù)表名（ ）。A、SELECT子句 B、INTO子句 C、FROM 子句 D、WHERE子句4、在SELECT語句中，下列哪種子句用于對數(shù)據(jù)按照某個字段分組（ ）。A、SELECT子句 B、INTO子句 C、FROM 子句 D、WHERE子句 5、在SELECT語句中，下列哪種子句用于對分組統(tǒng)計進一步設(shè)置條件（ ）。A、HAVING子句 B、GROUP BY 子句 C、ORDER BY 子句 D、WHERE子句 6、在SELECT語句中，下列哪種子句用于對搜索的結(jié)果進行排序（ ）。A、HAVING子句 B、GROUP BY 子句 C、ORDER BY 子

46、句 D、WHERE子句 7、在SELECT語句中，如果想要返回的結(jié)果集中不包含相同的行，應該使用關(guān)鍵字（ ）。A、TOP B、AS C、 DISTINCT D、JOIN二填空題1、SELECT語句的子句有_、_、_、_、_、_等。2、連接查詢的類型有_、_、_等3種。3、內(nèi)連接有_、_、_等。4、外連接有_、_、_等。三簡答題1說明SELECT語句的基本語法結(jié)構(gòu)。2、使用SELECT語句時，在選擇列表中更改列標題有哪三種格式？ 四上機練習1打開XSCJ數(shù)據(jù)庫；2查看學生基本信息表中的全部信息；3顯示學生基本信息表中每位學生的學號、姓名、出生日期；4從學生基本信息表中查看政治面貌，要求取消政治面

47、貌代碼相同的行；5顯示成績表的前10行；6從成績表和課程信息表中查看所有學生的SQL Server 2000數(shù)據(jù)庫應用課程成績；7從成績表和課程信息表中查看SQL Server 2000數(shù)據(jù)庫應用課程的最高分、最低分、平均成績；8將成績表中課程ID=003的課程成績按降序排序；9從成績表中查看課程ID為002、003、006的學生成績；10從學生基本信息表中查看新疆的學生基本信息；11查看伊犁的學生信息；12查看所有姓馬的學生信息；13查看90分以上學生的成績、課程名稱、學生姓名；14對所有學生按學號分組并計算每人本學期平均成績；15顯示所有學生的學號、姓名、課程名稱、成績； 項目實訓1查詢p

48、ubs數(shù)據(jù)庫的authors表中的作者的姓（au_lname）、名（au_fname）和電話號碼（phone）。2使用TOP關(guān)鍵字，從Northwind數(shù)據(jù)庫的customers表中搜索返回前20%的數(shù)據(jù)。3查詢Northwind數(shù)據(jù)庫的Orders表中的數(shù)據(jù)，并將其中的貨物重量feight增加50%。4使用WHERE 子句從Northwind 數(shù)據(jù)庫的Products表中檢索出所有單位價格（UnitPrice）超過50美元的貨物名稱（Product Name ）、貨物代號（ProductID）以及每單位重量（QuantityPerUnit）。5在Northwind 數(shù)據(jù)庫的Employees

49、表中搜索出職務（Title）為銷售代表（Sales Representative），稱呼（TitleOfCourtesy）為小姐（MS）的所有職員的名（FirstName）、姓（LastName）和生日（BirhthDate）。6查詢在Northwind數(shù)據(jù)庫的Employees表中以字母A作Firstname第一個字母的雇員的Firstname和Lastname。7查詢Northwind數(shù)據(jù)庫Employees表中所有雇員的Firstname和Lastname，并按生日BirthDate從小到大進行排列。8在Northwind數(shù)據(jù)庫的Products表中查詢出每個供應商（Suppliers）所提供的每一種平均價格（Unitprice）超過15美元的產(chǎn)品，并按供應商的ID分類。5、世上最美好的事是：我已經(jīng)長大，父母還未老;我有能力報答，父母仍然健康。6、沒什么可怕的，大家都一樣，在試探中不斷前行。7、時間就像一張網(wǎng)，你撒在哪里，你的收獲就在哪里。紐