第5章 SQL查詢語句基礎

1、第 5_2 章 SQL查詢語句根底 5.1 SELECT 條件列表5.2 FROM子句5.3 WHERE子句5.4 統(tǒng)計5.5 利用查詢結果創(chuàng)立新表5.6 使用UNION運算符實現(xiàn)多查詢聯(lián)合5.7 連接5.8 子查詢 本章要點：掌握簡單的Transact-SQL查詢語句。學會對查詢結果進行分組統(tǒng)計、合計和排序等復雜的SELECT語句操作。5.1 SELECT 條件列表一、根本結構SELECT 記錄顯示范圍 字段列表 INTO 新表名 FROM 表名或表名列表及其連接方式 WHERE 條件表達式 GROUP BY 分組字段名列表 HAVING 分組條件表達式 ORDER BY 排序字段名列表 A

2、SC | DESC COMPUTE 集合函數(列名1) BY 列名2 n 說明：SELECT語句中各子句的順序： SELECT 記錄范圍 字段列表 INTO FROM WHERE GROUP BY HAVING ORDER BY COMPUTE lFROM用于指定數據來源： 單表查詢簡單格式：FROM 表名 多表查詢時的格式：FROM 表名列表及其連接方式lCOMPUTE子句不能與INTO子句或GROUP BY子句同時使用。SELECT 條件列表(1) SELECT子句：指明目標列字段、表達式、函數表達式、常量。根本表中相同的列名表示為：表名.列名(2) FROM子句：指明數據源。表間用“，分

3、割。數據源不在當前數據庫中，使用“數據庫名.表名表示。一表多用，用別名標識。定義表別名：表名別名(3) WHERE子句：元組選擇條件。(4) GROUP BY子句：結果集分組。當目標列中有統(tǒng)計函數，那么統(tǒng)計為分組統(tǒng)計，否那么為對整個結果集統(tǒng)計。子句后帶上HAVING子句表達組選擇條件帶函數的表達式。(5) ORDER BY子句：排序。當排序要求為ASC時升序排序；排序要求為DESC時降序排列。2. SELECT語句的操作符(1) 算術操作符+加號、減號、*乘號和 /除號。(2) 比較操作符=等于、大于、小于、=大于等于、!=不等于、小于大于、!不大于和 !、=、90;自連接自內連接簡稱自連接，

4、是一張表自己對自己的內連接，即在一張表的兩個副本之間進行內連接。用自連接可以將同一個表的不同行連接起來。 使用自連接時，必須為兩個副本指定別名，使之在邏輯上成為兩個表。 語法格式： SELECT 列名列表 FROM 表名 AS 別名1 join 表名.別名2 ON 別名1.列名=別名2.列名查詢每一門課的間接先行課。例如，課程表中的先行課是在上學期應開設的，先行課的先行課，即間接先行課應提前一學年開設。如果求查詢某門課的間接先行課或全部課程的間接先行課，就需要對課程表進行自身連接。SELECT A.課程號,A.課程名,B.先行課 FROM 課程例子課程號課程名先行課C1計算機引論C2PASCA

5、L語言C1C3數據結構C2C4數據庫C3C5軟件工程C4課程的先行關系鏈為：C5C4C3C2C1，課程的間接關系鏈為：C5C3C1。 課程號課程名先行課課程號課程名先行課C1計算機引論C1計算機引論C2Pascal語言C1C2Pascal語言C1C3數據結構C2C3數據結構C2C4數據庫C3C4數據庫C3C5軟件工程C4C5軟件工程C4A.課程號A.課程名B.先行課C2Pascal語言C3數據結構C1C4數據庫C2C5軟件工程C3AB結果【例5-18】自連接雖然使用一個表但有兩個拷貝，在邏輯上是兩個表而且字段完全相同，因此字段列表中字段名必須加上其中一個表的別名做前綴。使用自連接會產生許多重復

6、行，一般加關鍵字DISTINCT過濾掉重復行。自連接默認按ON使用的連接字段排序供貨商ID，貨號，為了按廠家順序再按進貨日期排序，本例使用了ORDER BY指定排序。由于使用了DISTINCT，所以不允許使用字段列表沒有指定的“g1.進貨日期排序，也不允許使用別名“供貨日期進行排序，本例使用了字段列表中的表達式。(3) 外部連接左外部連接操作是在結果集中保存連接表達式左表中的非匹配記錄；右外部連接操作是在結果集中保存連接表達式右表中的非匹配記錄。外部連接符號為“*=，右外部連接符號為“=*。外部連接中不匹配的分量用NULL表示。職工號姓名性別年齡所在部門部門號部門名稱電話1010李勇男2011

7、11生產科5661011劉晨女1912計劃科5781012王敏女221213一車間4671014張立男211314科研所職工表 部門表 連接的結果集 職工號姓名性別年齡所在部門部門名稱電話1010李勇男2011生產科5661012王敏女2212計劃科5781014張立男2113一車間467職工號姓名性別年齡所在部門部門名稱電話1010李勇男2011生產科5661011劉晨女191012王敏女2212計劃科5781014張立男2113一車間467內連接的結果集 左外部連接的結果集 【例5-19】內連接： SELECT 職工.*，部門名稱， FROM 職工，部門 WHERE 職工.所在部門= 部門

8、.部門號；左外部連接： SELECT 職工.*，部門名稱， FROM 職工，部門 WHERE 職工.所在部門*= 部門.部門號；右外部連接： SELECT 職工.*，部門名稱， FROM 職工，部門 WHERE 職工.所在部門 =*部門.部門號； 用SQL表達職工和部門之間的內連接、左外部連接和右外部連接的語句5.8 子查詢(嵌套查詢)子查詢是嵌套在另一查詢中的 Select-From-Where 表達式Where/HavingSQL允許多層嵌套，由內而外地進行分析，子查詢的結果作為父查詢的查找條件可以用多個簡單查詢來構成復雜查詢，以增強SQL的查詢能力子查詢中不使用 Order By 子句，

9、Order By子句只能對最終查詢結果進行排序子查詢(嵌套查詢)我們可能會提出這樣的問題，在雇員中誰的工資最高，或者誰的工資比趙軍的高。通過把一個查詢的結果作為另一個查詢的一局部，可以實現(xiàn)這樣的查詢功能。具體的講：要查詢工資高于趙軍的雇員的名字和工資，必須通過2個步驟來完成，第一步查詢雇員趙軍的工資，第二步查詢工資高于趙軍的雇員。第一個查詢可以作為第二個查詢的一局部出現(xiàn)在第二個查詢的條件中，這就是子查詢。出現(xiàn)在其他查詢中的查詢稱為子查詢，包含其他查詢的查詢稱為主查詢。子查詢(嵌套查詢)子查詢一般出現(xiàn)在SELECT語句的WHERE子句中。子查詢比主查詢先執(zhí)行，結果作為主查詢的條件。在書寫上要用圓

10、括號擴起來，并放在比較運算符的右側。子查詢可以嵌套使用，最里層的查詢最先執(zhí)行。子查詢可以在SELECT、INSERT、UPDATE、DELETE等語句中使用。查找年齡比李明大的同學SELECT 姓名,年齡 FROM 學生WHERE 年齡(SELECT 年齡 FROM 學生 WHERE 姓名=李明);執(zhí)行過程分兩步：1、 SELECT 年齡 FROM 學生 WHERE 姓名=李明); 得出年齡=212、SELECT 姓名,年齡 FROM 學生 WHERE 年齡21使用IN操作符的嵌套查詢【例5-20】求選修了高等數學的學生學號和姓名。 SELECT 學號,姓名 FROM 學生 WHERE 學號

11、IN ( SELECT 學號 FROM 選課 WHERE 課程號 IN ( SELECT 課程號 FROM 課程 WHERE 課程名=高等數學);該題也可以使用下面的連接查詢表達。 SELECT 學生.學號，姓名 FROM 學生，課程，選課 WHERE 學生.學號=課程.學號 AND 課程.課程號=選課.課程號 AND 課程.課程名=高等數學；(2) 使用比較符的嵌套查詢【例5-21】求C1課程的成績高于張三的學生學號和成績。SELECT 學號,成績 FROM 選課 WHERE 課程號=C1 AND 成績 ( SELECt 成績 FROM 選課 WHERE 課程號=C1 AND 學號= (SE

12、LECT 學號 FROM 學生 WHERE 姓名=張三);(3) 使用ANY或ALL操作符的嵌套查詢格式為：字段比較符ANY|ALL子查詢操作符語意ANY大于子查詢結果中的某個值，即表示大于查詢結果中最小值ALL大于子查詢結果中的所有值，即表示大于查詢結果中最大值ANY小于子查詢結果中的某個值，即表示小于查詢結果中最大值=ANY大于等于子查詢結果中的某個值，即表示大于等于結果集中最小值=ALL大于等于子查詢結果中的所有值，即表示大于等于結果集中最大值=ANY小于等于子查詢結果中的某個值，即表示小于等于結果集中最大值=ALL小于等于子查詢結果中的所有值，即表示小于等于結果集中最小值=ANY等于子

13、查詢結果中的某個值，即相當于IN=ALL等于子查詢結果中的所有值(通常沒有實際意義)!=(或)ANY不等于子查詢結果中的某個值，!=(或)ALL不等于子查詢結果中的任何一個值，即相當于NOT IN例子【例5-22】求其他系中比計算機系某一學生年齡小的學生。 SELECT * FROM 學生 WHERE 年齡 ANY (SELECT 年齡 FROM 學生 WHERE 所在系=計算機系) AND 所在系計算機系;【例5-23】求其他系中比計算機系學生年齡都小的學生SELECT * FROM 學生 WHERE 年齡 ALL (SELECT 年齡 FROM 學生 WHERE 所在系=計算機) AND

14、所在系 計算機;嵌套查詢分類不相關子查詢子查詢的查詢條件不依賴于父查詢相關子查詢子查詢的查詢條件依賴于父查詢嵌套查詢求解方法不相關子查詢是由里向外逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結果用于建立其父查詢的查找條件。相關子查詢首先取外層查詢中表的第一個元組，根據它與內層查詢相關的屬性值處理內層查詢，假設WHERE子句返回值為真，那么取此元組放入結果表；然后再取外層表的下一個元組；重復這一過程，直至外層表全部檢查完為止引出子查詢的謂詞帶有IN謂詞的子查詢帶有比較運算符的子查詢帶有ANY或ALL謂詞的子查詢帶有EXISTS謂詞的子查詢一、帶有IN謂詞的子查詢【例5-20】求選修了

15、高等數學的學生學號和姓名。 SELECT 學號,姓名 FROM 學生 WHERE 學號 IN ( SELECT 學號 FROM 選課 WHERE 課程號 IN ( SELECT 課程號 FROM 課程 WHERE 課程名=高等數學);不相關子查詢四、帶有EXISTS謂詞的子查詢1. EXISTS謂詞存在量詞 帶有EXISTS謂詞的子查詢不返回任何數據，只產生邏輯真值“true或邏輯假值“false。假設內層查詢結果非空，那么返回真值假設內層查詢結果為空，那么返回假值由EXISTS引出的子查詢，其目標列表達式通常都用* ，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義2. NOT

16、 EXISTS謂詞(4) 使用EXISTS操作符的嵌套查詢【例5-24】求選修了C2課程的學生姓名。 SELECT 姓名 FROM 學生 WHERE EXISTS (SELECT * FROM 選課 WHERE 學生.學號=學號 AND 課程號=C2)思路分析： 本查詢涉及學生和選課關系。 在學生中依次取每個元組的學號的值，用此值去檢查選課關系。 假設選課中存在這樣的元組，其學號的值等于此學生.學號值，并且其課程號= C2，那么取此學生.姓名送入結果關系。【例5-25】求沒有選修C2課程的學生姓名。 SELECT 姓名 FROM 學生 WHERE NOT EXISTS ( SELECT * F

17、ROM 選課 WHERE =學號 AND 課程號=C2);用等值連接實現(xiàn)SELECT 姓名 FROM 學生 WHERE EXISTS (SELECT * FROM 選課 WHERE 學生.學號=學號 AND 課程號=C2);3. 不同形式的查詢間的替換一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價替換所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換。例：例37查詢與“李明在同一個系學習的學生?？梢杂脦XISTS謂詞的子查詢替換：SELECT 學號,姓名,所在系 FROM 學生 s1 WHERE exists (SE

18、LECT * FROM 學生 s2 WHERE s2.所在系 = s1.所在系 AND s2.姓名=李明)SELECT s1.學號,s1.姓名,s1.所在系 FROM 學生 s1, 學生 s2 WHERE s2.所在系 = s1.所在系 AND s2.姓名=李明例子【例5-26】查詢選修了全部課程的學生的姓名。 SELECT 姓名 FROM 學生 WHERE NOT EXISTS (SELECT * FROM 課程 WHERE NOT EXISTS (SELECT * FROM 選課 WHERE 學生.學號=學號 AND 課程.課程號=課程號)；第一個not exists表示不在這些課程記錄,

19、第二個not exists表示不存在這樣的選課記錄演變成:查詢這樣的學生姓名,沒有一門課是他不選的.SELECT * FROM 選課 xk ,學生 xs,課程 kc WHERE xs.學號=xk.學號 AND kc.課程號=xk.課程號例子【例5-27】求選修了學號為“S2的學生所選修的全部課程的學生學號和姓名。 SELECT 學號，姓名 FROM 學生 WHERE NOT EXISTS (SELECT * FROM 選課 選課1 WHERE 選課1.學號=S2 AND NOT EXISTS (SELECT * FROM 選課 選課2 WHERE 學生.學號=選課2.學號 AND 選課2 .課

20、程號=選課1.課程號)；查詢學生X選修的課程Z和S2學生選修的課程Y,并要求Z中包括全部的Y.不存在這樣的課程y，學生S2選修了y，而學生x沒有選。5.4 統(tǒng)計 把一列中的值進行聚合運算，返回單值的函數五個預定義的聚合函數平均值：AvgALL|DISTINCT總和： SumALL|DISTINCT最小值：MinALL|DISTINCT最大值：MaxALL|DISTINCT計數： CountALL|DISTINCTCount(*)、Count(Distinct)在SELECT語句中，可以使用聚合函數、行聚合函數、GROUP BY子句和COMPUTE子句對查詢結果進行統(tǒng)計。GROUP BY子句可與

21、行聚合函數或聚合函數一起使用。COMPUTE子句只能與行聚合函數一起使用。GROUP BY子句ROUP BY子句將一列或多列定義為一組，使得組內所有的行在某些列中的數值都相同。查詢有些什么系?SELECT 所在系 FROM 學生 group by 所在系例如在SELECT語句中，也可以單純使用聚合函數而不使用GROUP BY子句和COMPUTE子句進行統(tǒng)計，這時，它將所有符合條件的數據統(tǒng)計在一起，形成一行統(tǒng)計數據，這種統(tǒng)計方法叫做標量統(tǒng)計。【例5-31】求學生的總人數。 SELECT COUNT (*) FROM 學生;【例5-32】求選修了課程的學生人數。 SELECT COUNT(DIST

22、INCT 學號) FROM 選課;例：李明同學的成績最高分，最低分，總分和平均分。SELECT max(成績),min(成績),avg(成績) FROM 學生 st ,選課 kc where st.學號=kc.學號 and 姓名=李明組函數分組函數中SUM和AVG只應用于數值型的列;MAX、MIN和COUNT可以應用于字符、數值和日期類型的列。組函數忽略列的空值。統(tǒng)計函數使用GROUP BY 從句可以對數據進行分組。所謂分組就是按照列的相同內容，將記錄劃分成組，對組可以應用組函數。在組函數中可使用DISTINCT或ALL關鍵字。ALL表示對所有非NULL值可重復進行運算COUNT除外。DIST

23、INCT 表示對每一個非NULL值，如果存在重復值組函數只運算一次。如果不指明上述關鍵字，默認為ALL。 分組統(tǒng)計結果限定 在使用GROUP BY子句時，還可以用HAVING子句為分組統(tǒng)計進一步設置統(tǒng)計條件，HAVING子句與GROUP BY子句的關系和WHERE子句與SELECT子句的關系類似。HAVING子句可以參照選擇列表中的任一項，在HAVING子句中還可以使用邏輯運算符連接多個條件。最多為128個。HAVING從句過濾分組后的結果，HAVING從句只能出現(xiàn)在GROUP BY從句之后。而WHERE從句要出現(xiàn)在GROUP BY從句之前。WHERE 是在對記錄分組之前過濾不滿條件的記錄HA

24、VING是過濾掉整個分組,不滿足條件的組不要分組統(tǒng)計【例5-33】求課程和選修該課程的人數。 SELECT 課程號,COUNT(學號) as 選修課程人數 FROM 選課 GROUP BY 課程號;【例5-34】求選修課超過3門課的學生學號。 SELECT 學號 FROM 選課 GROUP BY 學號 HAVING COUNT(*)2;訓練查找圖書類別，要求類別中最高圖書定價不低于全部按類別分組的平均定價的2倍。 SELECT A.* FROM 圖書 AGROUP BY A.類別 HAVING MAX(A.定價)=ALL(SELECT 2*AVG(B.定價) FROM 圖書 B GROUP B

25、Y B.類別)訓練SELECT count (*) , 類別 FROM 圖書 GROUP BY 類別 SELECT AVG(定價) FROM 圖書 GROUP BY 類別SELECT count (*) , avg(定價),類別 FROM 圖書 GROUP BY 類別 SELECT count (*) , avg(定價), 類別 FROM 圖書 GROUP BY 類別 having avg(定價)202求機械工業(yè)出版社出版的各類圖書的平均定價，用GROUP BY表示。SELECT 類別,AVG(定價)平均價 FROM 圖書WHERE 出版社=機械工業(yè)出版社GROUP BY 類別 ORDER B

26、Y 類別 ASC使用compute格式：compute 集合函數(列名1), By 列名2 ,.功能：先按列名2分類顯示參加匯總記錄的詳細信息，再在附加行中顯示對列名1的匯總值(單用集合函數或GROUP BY僅顯示統(tǒng)計匯總值)。COMPUTE子句可以指定多個集合函數，但不允許指定列別名。SELECT 指定的字段列表是顯示詳細信息使用的字段，必須包含COMPUTE子句集合函數使用的列名1，與BY分組字段列名2無關，也可以使用*表示全部字段。COMPUTE子句不帶BY表示對全部記錄統(tǒng)計，相當于在SELECT查詢結果后面帶一個統(tǒng)計值的后綴。本卷須知COMPUTE子句帶BY子句時表示分組統(tǒng)計，必須配合

27、ORDER BY排序子句使用，且緊跟ORDER BY之后。BY后的列名2是要分組的字段相當于GROUP BY，可以不在SELECT指定的字段中，但必須包含在ORDER BY子句中，而且必須是第一順序。BY指定多個字段分組時，也必須與ORDER BY的第一順序一致。COMPUTE子句不能與INTO子句或GROUP BY子句同時使用。一個SELECT語句中可使用多個COMPUTE子句，一個子句顯示一個附加行，多個子句時BY分組字段必須一致，且與ORDER BY一致，子句之間不能使用逗號。select sum(定價) from 圖書 group by 類別select * from 圖書 compu

28、te sum(定價) select * from 圖書 order by 類別 compute sum(定價) select * from 圖書 order by 類別 compute sum(定價) by 類別3列出計算機類圖書的書號、名稱及價格，最后求出冊數和總價格SELECT 書號,書名,定價 FROM 圖書WHERE 類別=計算機 ORDER BY 書號 ASCCOMPUTE count (書號),SUM(定價)列出計算機類圖書的書號、名稱及價格，并求出各出版社這類書的總價格，最后求出全部冊數和總價格。SELECT書號，書名，定價 FROM 圖書WHERE 類別=計算機類ORDER B

29、Y出版社CONPUTE COUNT*，SUM定價BY 出版社COMPUTE COUNT*，SUM定價6.6 使用UNION運算符實現(xiàn)多查詢聯(lián)合UNION運算符可以將兩個或兩個以上的查詢結果合并成一個結果集合顯示。UNION運算符的語法格式為： 查詢1 UNION ALL 查詢n ORDER BY 子句COMPUTE 子句其中查詢的格式為： SELECT select_listINTO子句FROM子句WHERE子句 GROUP BY 子句HAVING 子句操作描述UNION并集，合并兩個操作的結果，去掉重復的部分。UNION ALL并集，合并兩個操作的結果，保留重復部分。(所有的)MINUS差集

30、，從前面的操作結果中去掉與后面操作結果相同的部分。INTERSECT交集，取兩個操作結果中的相同的部分。UNION 運算符使用準那么在使用 UNION 運算符組合的語句中，所有選擇列表的表達式數目必須相同列名、算術表達式、聚合函數等。在使用 UNION 組合的結果集中的相應列、或個別查詢中使用的任意列的子集必須具有相同數據類型，并且兩種數據類型之間必須存在可能的隱性數據轉換，或提供了顯式轉換。例如，在 datetime 數據類型的列和 binary 數據類型的列之間不可能存在 UNION 運算符，除非提供了顯式轉換，而在 money 數據類型的列和 int 數據類型的列之間可以存在 UNION

31、 運算符，因為它們可以進行隱性轉換。用 UNION 運算符組合的各語句中對應的結果集列出現(xiàn)的順序必須相同，因為 UNION 運算符是按照各個查詢給定的順序逐個比較各列。4. 組合查詢【例5-28】求選修了C1課程或選修了C2課程的學生學號。SELECT 學號 FROM 選課 WHERE 課程號=C1 UNION SELECT 學號 FROM 選課 WHERE 課程號=C2【例5-29】求選修C1課程，并且也選修C2課程的學生學號。 SELECT 學號 FROM 選課 WHERE 課程號=C1 INTERSECT SELECT 學號 FROM 選課 WHERE 課程號=C2；查詢計算機類和機械工

32、業(yè)出版社出版的圖書。SELECT * FROM 圖書WHERE 類別=計算機類UNION ALLSELECT * FROM 圖書WHERE 出版社=機械工業(yè)出版社【例5-30】求選修了C1課程但沒有選修C2課程的學生學號。SELECT 學號 FROM 選課 WHERE 課程號=C1 MINUSSELECT 學號 FROM 選課 WHERE 課程號=C2；本例也可以用下面的EXISTS嵌套查詢表示。 SELECT 學號 FROM 選課 選課1 WHERE 課程號=C1 AND NOT EXISTS (SELECT 學號 FROM 選課 選課2 WHERE 選課1.學號=選課2.學號 AND 選課

33、2.課程號=C2);5.4 數據更新語句5.4.1 數據插入語句1. 使用常量插入單個元組格式為： INSERT INTO表名(屬性列1，屬性列2) VALUES (常量1，常量2)；例子【例5-35】將一個新學生記錄(學號：98010，姓名：張三，年齡：20，所在系：計算機系 )插入到學生表中。INSERTINTO 學生VALUES (98010，張三，20，計算機系)；【例5-36】插入一條選課記錄(學號：98011，課程號：C10，成績不詳)。 INSERT INTO 選課 (學號，課程號) VALUES (98011，C10)；2. 在表中插入子查詢的結果集INSERT INTO表名(

34、屬性列1，屬性列2) 子查詢；【例5-37】求每個系學生的平均年齡，把結果存入數據庫中。 CREATE TABLE 系平均年齡 (系名稱 CHAR(20), 平均年齡 SMALLINT); INSERT INTO 系平均年齡 SELECT 所在系,AVG(年齡) FROM 學生 GROUP BY 所在系;select * from 系平均年齡5.4.2 數據修改語句UPDATE表名SET列名=表達式，列名=表達式，nWHERE條件；【例5-38】將學生表中全部學生的年齡加上2歲。 UPDATE 學生 SET 年齡=年齡+2；【例5-39】將選課表中的數據庫課程的成績乘以。 UPDATE 選課 SET 成績= 成績*1.2 WHERE 課程號= (SELECT 課程號 FROM 課程 WHERE 課程