第6章SQL查詢的操作

1、本章要點SQL與SQL查詢SQL 數(shù)據(jù)查詢SQL數(shù)據(jù)定義SQL數(shù)據(jù)操縱第6章 SQL查詢的操作6.1 SQL與SQL查詢6.1.1 SQL的發(fā)展與功能SQL最早是在20世紀70年代由IBM公司開發(fā)出來的，并被應用在DB2關系數(shù)據(jù)庫系統(tǒng)中，主要用于關系數(shù)據(jù)庫中的信息檢索。1986年10月，美國國家標準協(xié)會(ANSI)的數(shù)據(jù)庫委員會批準了SQL作為關系數(shù)據(jù)庫語言的美國標準。1987年6月，國際標準化組織(ISO)將其采納為國際標準，這個標準也稱為SQL86。SQL標準的出臺使SQL作為標準關系數(shù)據(jù)庫語言的地位得到了加強。隨后，SQL標準幾經(jīng)修改和完善，其間經(jīng)歷了SQL89，SQL92，SQL99，

2、SQL2003等多個版本，每個新版本都較前面的版本有重大改進。隨著數(shù)據(jù)庫技術的發(fā)展，還會有更新的SQL標準。 按照其實現(xiàn)的功能可以將SQL語句劃分為4類。數(shù)據(jù)查詢語言(Data Query Language，DQL)：按一定的查詢條件從數(shù)據(jù)庫對象中檢索符合條件的數(shù)據(jù)，如SELECT語句。數(shù)據(jù)定義語言(Data Definition Language，DDL)：用于定義數(shù)據(jù)的邏輯結構及數(shù)據(jù)項之間的關系，如CREATE，DROP，ALTER語句等。數(shù)據(jù)操縱語言(Data Manipulation Language，DML)：用于增加、刪除、修改數(shù)據(jù)等，如INSERT，UPDATE，DELETE語句

3、等。數(shù)據(jù)控制語言(Data Control Language，DCL)：在數(shù)據(jù)庫系統(tǒng)中，具有不同角色的用戶執(zhí)行不同的任務，并且應該被給予不同的權限。數(shù)據(jù)控制語言用于設置或更改用戶的數(shù)據(jù)庫操作權限，如GRANT，REVOKE語句等。6.1.2 SQL 視圖實際上，在使用查詢設計視圖創(chuàng)建查詢時，Access會自動將操作步驟轉化為一條條等價的SQL語句，只要打開查詢，并進入該查詢的SQL 視圖就可以看到系統(tǒng)生成的SQL語句。6.1.3 創(chuàng)建SQL查詢SQL查詢包括聯(lián)合查詢、傳遞查詢和數(shù)據(jù)定義查詢。創(chuàng)建SQL查詢的步驟如下。打開“教學管理”數(shù)據(jù)庫，單擊“創(chuàng)建”選項卡，再在“查詢”命令組中單擊“查詢設計

4、”命令按鈕，打開查詢設計視圖窗口，再在“顯示表”對話框中單擊“關閉”按鈕，不添加任何表或查詢，進入空白的查詢設計視圖。在“查詢工具/設計”選項卡的“結果”命令組中單擊“視圖”命令按鈕，在下拉菜單中選擇“SQL視圖”命令，進入SQL視圖并輸入SQL語句。也可以在“查詢工具/設計”選項卡的“查詢類型”命令組中選擇“聯(lián)合”、“傳遞”或“數(shù)據(jù)定義”命令，即打開相應的特定查詢窗口，在窗口中輸入合適的SQL語句。將創(chuàng)建的查詢存盤并運行查詢。6.2 SQL數(shù)據(jù)查詢SQL數(shù)據(jù)查詢通過SELECT語句實現(xiàn)。SELECT語句中包含的子句很多，其語法格式為SELECT ALL|DISTINCT|TOP n.AS ，

5、.AS FROM ， WHERE GROUP BY ，HAVING UNIONALL SELECT語句ORDER BY ASC|DESC，ASC|DESC以上格式中“”中的內(nèi)容是必選的，“”中的內(nèi)容是可選的，“|”表示多個選項中只能選擇其中之一。6.2.1 基本查詢1簡單的查詢語句簡單的SELECT語句只包含SELECT子句和FROM子句，其格式為SELECT ALL|DISTINCT|TOP n.AS ，.AS FROM ， 例6-1 對“學生”表進行如下操作，寫出操作步驟和SQL語句。列出全部學生信息。列出前5個學生的姓名和年齡。操作1的SELECT語句：SELECT * FROM 學生操

6、作2的SELECT語句：SELECT TOP 5 姓名，Year(Date()-Year(出生日期) AS 年齡 FROM 學生例6-2 求出所有學生的平均入學成績。SELECT語句如下。SELECT Avg(入學成績) AS 入學成績平均分 FROM 學生2帶條件查詢WHERE子句用于指定查詢條件，其格式為WHERE 例6-3 寫出對“教學管理”數(shù)據(jù)庫進行如下操作的語句。列出入學成績在580分以上的學生記錄。求出湖南學生入學成績平均值。操作1：SELECT * FROM 學生 WHERE 入學成績580操作2：SELECT Avg(入學成績) AS 入學成績平均分 FROM 學生 WHERE

7、 籍貫=湖南例6-4 寫出對“教學管理”數(shù)據(jù)庫進行如下操作的語句。列出江蘇籍和貴州籍的學生名單。列出入學成績在560650分之間的學生名單。列出所有姓張的學生名單。列出所有成績?yōu)椤翱罩怠钡膶W生學號和課程編號。操作1：SELECT 學號,姓名,籍貫 FROM 學生 WHERE 籍貫 In(江蘇“,貴州)語句中的WHERE子句還有如下等價的形式。WHERE 籍貫江蘇 Or 籍貫貴州操作2：SELECT 學號,姓名,入學成績 FROM 學生 WHERE 入學成績 Between 560 And 650語句中的WHERE子句還有如下等價的形式。WHERE 入學成績=560 And 入學成績=650操作

8、3：SELECT 學號，姓名 FROM 學生 WHERE 姓名 Like張*語句中的WHERE子句還有如下等價的形式。WHERE Left(姓名,1)=張或WHERE Mid(姓名,1,1)=張或WHERE InStr(姓名,張)=1操作4：SELECT 學號，課程編號 FROM 選課 WHERE 成績 Is Null注意：在查詢時用“字段名Is Null”的形式，而不能寫成“字段名=Null”。3查詢結果處理1)排序輸出(ORDER BY)其格式為ORDER BY ASC|DESC，ASC|DESC例6-5 對“教學管理”數(shù)據(jù)庫，按性別順序列出學生的學號、姓名、性別、年齡及籍貫，性別相同的再

9、按年齡由大到小排序。SELECT 學號,姓名,性別,Year(Date()-Year(出生日期) AS 年齡,籍貫 FROM 學生 ORDER BY 性別,Year(Date()-Year(出生日期) DESC要注意語句中“年齡”的表達方法。在該語句中，由于兩個排序選項是第3、第4個輸出選項，所以ORDER BY子句也可以寫成ORDER BY 3,4 DESC2)分組統(tǒng)計(GROUP BY)與篩選(HAVING)使用GROUP BY子句可以對查詢結果進行分組，其格式為GROUP BY ，其中，是作為分組依據(jù)的字段名。GROUP BY子句可以將查詢結果按指定列進行分組，每組在列上具有相同的值。要

10、注意的是，如果使用了GROUP BY子句，則查詢輸出選項要么是分組選項，要么是統(tǒng)計函數(shù)，因為分組后每個組只返回一行結果。若在分組后還要按照一定的條件進行篩選，則需使用HAVING子句，其格式為HAVING HAVING子句與WHERE子句一樣，也可以起到按條件選擇記錄的功能，但兩個子句作用的對象不同。WHERE子句作用于表，而HAVING子句作用于組，必須與GROUP BY子句連用，用來指定每一分組內(nèi)應滿足的條件。HAVING子句與WHERE子句不矛盾，在查詢中先用WHERE子句選擇記錄，然后進行分組，最后再用HAVING子句選擇記錄。當然，GROUP BY子句也可單獨出現(xiàn)。例6-6 寫出對“

11、教學管理”數(shù)據(jù)庫進行如下操作的語句。分別統(tǒng)計男女生人數(shù)。分別統(tǒng)計男女生中少數(shù)民族學生人數(shù)。列出平均成績大于80分的課程編號，并按平均成績升序排序。統(tǒng)計每個學生選修課程的門數(shù)(超過一門的學生才統(tǒng)計)，要求輸出學生學號和選修門數(shù)，查詢結果按選修門數(shù)降序排序，若門數(shù)相同，按學號升序排序。操作1：SELECT 性別,Count(*) AS 人數(shù) FROM 學生 GROUP BY 性別操作2：SELECT 性別,Count(*) AS 人數(shù) FROM 學生 WHERE 是否少數(shù)民族 GROUP BY 性別操作3：SELECT 課程編號,Avg(考試成績) AS 平均考試成績 FROM 選課 GROUP

12、BY 課程編號 HAVING Avg(考試成績)=80 ORDER BY Avg(考試成績) ASC操作4：SELECT 學號, Count(課程編號) AS 選課門數(shù) FROM 選課 GROUP BY 學號 HAVING Count(課程編號)1 ORDER BY 2 DESC,16.2.2 嵌套查詢1返回單值的子查詢例6-7 對“教學管理”數(shù)據(jù)庫，列出選修“數(shù)據(jù)庫技術與應用”的所有學生的學號。SELECT 學號 FROM 選課 WHERE 課程編號= (SELECT 課程編號 FROM 課程 WHERE 課程名稱=“數(shù)據(jù)庫技術與應用)2返回一組值的子查詢1)Any運算符的用法Any運算符可

13、以找出滿足子查詢中任意一個值的記錄，使用格式為 Any()例6-8 對“教學管理”數(shù)據(jù)庫，列出選修“J0015”課的學生中成績比選修“K1002”課的最低成績高的學生的學號和成績。SELECT 學號,成績 FROM 選課 WHERE 課程編號=“J1005 And 成績Any (SELECT 成績 FROM 選課 WHERE 課程編號=“K1002)2)All運算符的用法All運算符可以找出滿足子查詢中所有值的記錄，使用格式為 All()例6-9 對“教學管理”數(shù)據(jù)庫，列出選修“J1005”課的學生中成績比選修“K1002”課的最高成績還要高的學生的學號和考試成績。SELECT 學號,成績 F

14、ROM 選課 WHERE 課程編號 =J1005 And 成績All (SELECT 成績 FROM 選課 WHERE 課程編號=K1002)3)In運算符的用法In是屬于的意思，等價于“Any”，即等于子查詢中任何一個值。例5-10 寫出對“教學管理”數(shù)據(jù)庫進行如下操作的語句。列出選修“數(shù)據(jù)庫技術與應用”或“微積分”的所有學生的學號。顯示“選課”表的第610號記錄。操作1：SELECT 學號 FROM 選課 WHERE 課程編號 In (SELECT 課程編號 FROM 課程 WHERE 課程名稱=“數(shù)據(jù)庫技術與應用 Or 課程名稱=微積分)操作2：SELECT TOP 5 * FROM 選

15、課 WHERE 學號 Not In (SELECT TOP 5 學號 FROM 選課)6.2.3 多表查詢例6-11 寫出對“教學管理”數(shù)據(jù)庫進行如下操作的語句。輸出所有學生的成績單，要求給出學號、姓名、課程編號、課程名稱和成績。列出少數(shù)民族學生的選課情況，要求列出學號、姓名、課程編號、課程名稱和成績。求選修“J1005”課的女生的平均年齡。操作1：SELECT a.學號,姓名,b.課程編號,課程名稱,平時成績,考試成績,總評成績 FROM 學生 a,選課 b,課程 c WHERE a.學號=b.學號 And b.課程編號=c.課程編號操作2：SELECT a.學號,a.姓名,b.課程編號,課

16、程名稱,平時成績,考試成績,總評成績 FROM 學生 a,選課 b,課程 c WHERE a.學號=b.學號 And b.課程編號=c.課程編號 And 是否少數(shù)民族操作3：SELECT Avg(Year(Date()-Year(出生日期) AS 平均年齡 FROM 學生,選課 WHERE 學生.學號=選課.學號 And 課程編號=J0015 And 性別=男6.2.4 聯(lián)合查詢聯(lián)合查詢實際是將兩個或更多個表或查詢中的記錄縱向合并成為一個查詢結果。數(shù)據(jù)合并(UNION)子句的格式為UNION ALL 其中，ALL表示結果全部合并。若沒有ALL，則重復的記錄將被自動去掉。合并的規(guī)則如下。不能合并

17、子查詢的結果。兩個SELECT語句必須輸出同樣的列數(shù)。兩個表各相應列的數(shù)據(jù)類型必須相同，數(shù)字和字符不能合并。僅最后一個SELECT語句中可以用ORDER BY子句，且排序選項必須用數(shù)字說明。例6-12 對“教學管理”數(shù)據(jù)庫，列出選修“J1005”課或“K1002”課的所有學生的學號和姓名，要求建立聯(lián)合查詢。SELECT 學生.學號, 學生.姓名 FROM 選課,學生 WHERE 課程編號=“J1005 And 選課.學號=學生.學號UNION SELECT 學生.學號, 學生.姓名 FROM 選課,學生 WHERE 課程編號=“K1002 And 選課.學號=學生.學號6.3 SQL數(shù)據(jù)定義6

18、.3.1 建立表結構在SQL中可以通過CREATE TABLE語句建立表結構，其語句格式為CREATE TABLE ( 字段級完整性約束1 ， 字段級完整性約束2 ， ， 字段級完整性約束n ， )Microsoft Access SQL常用數(shù)據(jù)類型數(shù)據(jù)類型字段寬度說明Smallint短整型，按2個字節(jié)存儲Integer長整型，按4個字節(jié)存儲Real單精度浮點型，按4個字節(jié)存儲Float雙精度浮點型，按8個字節(jié)存儲Money貨幣型，按8個字節(jié)存儲Char(n)n字符型(存儲0255個字符)Text(n)n備注型Bit是/否型，按1個字節(jié)存儲Datetime日期/時間型，按8個字節(jié)存儲Image

19、用于OLE對象例6-13 在“教學管理”數(shù)據(jù)庫中建立“教師”表：教師(編號，姓名，性別，基本工資，出生年月，研究方向)，其中允許“出生年月”字段為“空值”。CREATE TABLE 教師 ( 編號 Char(7)， 姓名 Char(8)， 性別 Char(2)， 基本工資 Money， 出生年月 Datetime Null， 研究方向 Text(50) )6.3.2 修改表結構如果表不滿足要求，就需要進行修改?？梢允褂肁LTER TABLE語句修改已建表的結抅，其語句格式為ALTER TABLE ADD 字段級完整性約束條件DROP ALTER 例6-14 對“課程”表的結構進行修改，寫出操作語句。為“課程”表增加一個整數(shù)類型的“學時”字段。刪除“課程”表中的“學時”字段。操作1：ALTER TABLE 課程 ADD 學時 Smallint操作2：ALTER TABLE 課程 DROP 學時6.3.3 刪除表如果希望刪除某個不需要的表，可以使用DROP TABLE語句，其語句格式為DROP TABLE 其中，是指要刪除的表的名稱。例6-15 在“教學管理”數(shù)據(jù)庫中刪除已建立的“教師”表。DROP TABLE 教師注意：表一旦被刪除，表中數(shù)據(jù)將自動被刪除，并且無法恢復，因此，執(zhí)行刪除表的操作時一定要慎重。6.4 SQL數(shù)據(jù)操縱6.