第6章SQL查詢的操作

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

2、SQL2003等多個(gè)版本，每個(gè)新版本都較前面的版本有重大改進(jìn)。隨著數(shù)據(jù)庫技術(shù)的發(fā)展，還會(huì)有更新的SQL標(biāo)準(zhǔn)。 按照其實(shí)現(xiàn)的功能可以將SQL語句劃分為4類。數(shù)據(jù)查詢語言(Data Query Language，DQL)：按一定的查詢條件從數(shù)據(jù)庫對(duì)象中檢索符合條件的數(shù)據(jù)，如SELECT語句。數(shù)據(jù)定義語言(Data Definition Language，DDL)：用于定義數(shù)據(jù)的邏輯結(jié)構(gòu)及數(shù)據(jù)項(xiàng)之間的關(guān)系，如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í)行不同的任務(wù)，并且應(yīng)該被給予不同的權(quán)限。數(shù)據(jù)控制語言用于設(shè)置或更改用戶的數(shù)據(jù)庫操作權(quán)限，如GRANT，REVOKE語句等。6.1.2 SQL 視圖實(shí)際上，在使用查詢?cè)O(shè)計(jì)視圖創(chuàng)建查詢時(shí)，Access會(huì)自動(dòng)將操作步驟轉(zhuǎn)化為一條條等價(jià)的SQL語句，只要打開查詢，并進(jìn)入該查詢的SQL 視圖就可以看到系統(tǒng)生成的SQL語句。6.1.3 創(chuàng)建SQL查詢SQL查詢包括聯(lián)合查詢、傳遞查詢和數(shù)據(jù)定義查詢。創(chuàng)建SQL查詢的步驟如下。打開“教學(xué)管理”數(shù)據(jù)庫，單擊“創(chuàng)建”選項(xiàng)卡，再在“查詢”命令組中單擊“查詢?cè)O(shè)計(jì)

4、”命令按鈕，打開查詢?cè)O(shè)計(jì)視圖窗口，再在“顯示表”對(duì)話框中單擊“關(guān)閉”按鈕，不添加任何表或查詢，進(jìn)入空白的查詢?cè)O(shè)計(jì)視圖。在“查詢工具/設(shè)計(jì)”選項(xiàng)卡的“結(jié)果”命令組中單擊“視圖”命令按鈕，在下拉菜單中選擇“SQL視圖”命令，進(jìn)入SQL視圖并輸入SQL語句。也可以在“查詢工具/設(shè)計(jì)”選項(xiàng)卡的“查詢類型”命令組中選擇“聯(lián)合”、“傳遞”或“數(shù)據(jù)定義”命令，即打開相應(yīng)的特定查詢窗口，在窗口中輸入合適的SQL語句。將創(chuàng)建的查詢存盤并運(yùn)行查詢。6.2 SQL數(shù)據(jù)查詢SQL數(shù)據(jù)查詢通過SELECT語句實(shí)現(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)容是可選的，“|”表示多個(gè)選項(xiàng)中只能選擇其中之一。6.2.1 基本查詢1簡單的查詢語句簡單的SELECT語句只包含SELECT子句和FROM子句，其格式為SELECT ALL|DISTINCT|TOP n.AS ，.AS FROM ， 例6-1 對(duì)“學(xué)生”表進(jìn)行如下操作，寫出操作步驟和SQL語句。列出全部學(xué)生信息。列出前5個(gè)學(xué)生的姓名和年齡。操作1的SELECT語句：SELECT * FROM 學(xué)生操

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

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

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

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

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

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

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

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

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

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

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

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

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

19、用于OLE對(duì)象例6-13 在“教學(xué)管理”數(shù)據(jù)庫中建立“教師”表：教師(編號(hào)，姓名，性別，基本工資，出生年月，研究方向)，其中允許“出生年月”字段為“空值”。CREATE TABLE 教師 ( 編號(hào) Char(7)， 姓名 Char(8)， 性別 Char(2)， 基本工資 Money， 出生年月 Datetime Null， 研究方向 Text(50) )6.3.2 修改表結(jié)構(gòu)如果表不滿足要求，就需要進(jìn)行修改?？梢允褂肁LTER TABLE語句修改已建表的結(jié)抅，其語句格式為ALTER TABLE ADD 字段級(jí)完整性約束條件DROP ALTER 例6-14 對(duì)“課程”表的結(jié)構(gòu)進(jìn)行修改，寫出操作語句。為“課程”表增加一個(gè)整數(shù)類型的“學(xué)時(shí)”字段。刪除“課程”表中的“學(xué)時(shí)”字段。操作1：ALTER TABLE 課程 ADD 學(xué)時(shí) Smallint操作2：ALTER TABLE 課程 DROP 學(xué)時(shí)6.3.3 刪除表如果希望刪除某個(gè)不需要的表