4.1查詢的創建與編輯4.2查詢的分類4.3SQL查詢第四章查詢4.1查詢的創建與編輯查詢是對數據庫中的表數據進行查找，同時產生一個類似于表的結果。查詢可以作為結果，也可以作為來源，即查詢可以根據條件從數據表中檢索數據，并將結果存儲起來；查詢也可以作為創建表、查詢、窗體或報表的數據源。根據其應用目的不同，查詢的分為5種類型：選擇查詢、計算查詢、參數查詢、操作查詢、SQL查詢4.1.1查詢簡介4.1.2查詢視圖Access中的查詢視圖主要包括：1、數據表視圖：主要用于在行和列格式下顯示表、查詢以及窗體中的數據。用戶可以通過這種方式進行打開查詢、查看信息、更改數據、追加記錄和刪除記錄等操作。2、設計視圖：設計查詢的窗口，主要用于查詢的編輯操作。窗口中包含了創建查詢所需要的各個組件。3、SQL視圖：自動創建與查詢對應的SQL語句。用戶也可以通過修改SQL語句，靈活地改變查詢。常用的查詢向導有：

1、簡單查詢向導

2、交叉表查詢向導

3、查找重復項查詢向導

4、查找不匹配項查詢向導4.1.3查詢向導1、指定檢索字段：檢索“讀者信息表”中所有記錄的姓名、權限和身份證號2、匯總數值字段：檢索“圖書信息表”中價格最高的書籍將數據分別行列顯示，并在交叉點進行數據匯總運算：統計“讀者信息表”中各權限男女讀者的人數確定表中是否存在重復項：查找“圖書信息表”中書籍價格相同的記錄查找表中與其他記錄不相關的記錄：查找有哪些讀者還未借過書查詢準則查詢準則是用來在查詢中限制檢索記錄的表達式，表達式一般由常量、變量、函數和各類運算符等構成，通常為條件表達式或邏輯表達式。

計算機是根據表達式運算結果的真或假來判斷是否應該進行記錄的檢索。1、運算符分類常用的運算符算術運算符+、-、*、/、\、^、MOD關系運算符=、<>、<、<=、>、>=邏輯運算符NOT、AND、OR特殊運算符IN：用于指定一個字段值的列表BETWEEN：用于指定一個字段的范圍LIKE：用于指定查找文本字段的字符模式ISNULL：用于指定一個字段為空為方便用戶更高效、準確地進行數據運算，Access提供了大量的標準函數，并利用這些函數構造查詢準則。函數類型舉例數值函數Abs()、sqr()、sgn()int()、fix()、round()字符函數left()、right()、mid()space()、Cstr()、len()

日期函數day()、month()、year()hour()、minute()、second()date()、time()、now()weekday()統計函數sum()、avg()、max()、min()、count()2、函數字段準則功能職稱=“副教授”院系=“外語”or“會計”課程名

like“*計算機*”院系

in（“外語”，“會計”）姓名

notlike“王*”姓名

left([姓名],1)=“張”姓名

len([姓名])<=4編號

mid([編號],1,2)=“12”查詢準則示例1、文本值查詢職稱為“副教授”的記錄查詢院系為“外語”或“會計”的記錄查詢課程名中含“計算機”的記錄查詢院系為“外語”或“會計”的記錄查詢不姓“王”的記錄查詢姓“張”的記錄查詢名字小于或等于4個字的記錄查詢編號的前2位為“12”的記錄字段名準則功能工作時間between#1995-01-01#and#1995-12-31#工作時間<date()-15出生日期weekday([出生日期])=7出生日期year([出生日期])=year(date())Andmonth([出生日期])=42、日期值查詢工作時間在95年的記錄查詢工作時間在15天以上的記錄查詢星期六出生的記錄查詢出生日期為今年4月份的記錄4.1.4查詢設計器1、編輯查詢字段增加字段刪除字段顯示/隱藏字段移動字段修改字段標題2、運行查詢運行按鈕視圖切換按鈕4.2查詢分類按照應用目的的不同，Access中的查詢分為五類：選擇查詢計算查詢參數查詢操作查詢

SQL查詢4.2.1選擇查詢選擇查詢是Access支持的多種類型查詢對象中最常見、最重要的一種，它從一個或多個表中根據準則（或條件）檢索數據。選擇查詢就是對數據的篩選，是在大量數據中篩選出滿足條件的若干行和列記錄。1、顯示檢索數據的全部字段內容舉例：檢索所有男讀者選擇查詢的設計主要包括以下幾種情況：2、顯示檢索數據的部分字段內容舉例：檢索所有男讀者顯示字段：(讀者姓名、出生日期、性別)3、帶多個條件的數據檢索舉例：(1)檢索所有90后男讀者(2)檢索“人民郵電出版社”和“電子工業出版社”書籍4、對運算后的字段進行條件檢索舉例：(1)檢索下旬出生的讀者(2)檢索書名超過(不包含)6個字的書籍練習：(1)檢索出生日期為單號的讀者思考：顯示所有“人民郵電出版社”書籍的借閱情況（讀者姓名、書籍名稱、出版社、借書日期、還書日期）(2)檢索星期三出生的讀者(3)查找所有90-95年之間出生的讀者4.2.2計算查詢計算查詢通過在查詢對象中設計一個或多個計算查詢列來完成表內部或各表之間數據的運算。計算查詢主要有三類操作：添加新字段(自定義計算查詢)單字段匯總(預定義計算/匯總查詢)多字段匯總(交叉表計算查詢)選擇查詢的設計主要包括以下幾種情況：在實際查詢過程中，我們經常會碰到這樣一個情況：需要對已有的數據進行計算，再將計算結果顯示出來。這時候就可以使用“添加字段計算”的方法了。1、添加新字段在Access中，添加字段計算有一個固定的方法，就是將計算表達式寫在查詢設計器的“字段”行。【基本格式】顯示字段名：字段計算表達式運行查詢時，出現在“字段名”位置半角的冒號，是表達式前后參數的分割符運行查詢時，表達式的運算結果出現在對應字段的記錄行舉例：(1)顯示所有讀者信息(姓、名、出生日期、性別)練習：檢索下半年出生的讀者基本信息(姓名、出生日期、生日月份)思考：計算讀者年齡的差值(年齡差值：最大年齡-最小年齡)(2)計算所有讀者的年齡(姓名、性別、年齡)我們利用查詢計算數據的時候，也經常會碰到需要進行“分類匯總”的情況，也就是說，我們需要的數據不能直接從數據表中獲取，而需要對來源表進行一些函數運算才能總結出來。這時候就需要用到“匯總計算”了。2、單字段匯總匯總計算可以分為單字段匯總和多字段匯總2種，Access也提供了一個進行匯總計算的固定方法:1、設計—顯示/隱藏—匯總（我們也可以右擊查詢設計器的下半部分，找到“匯總”命令）2、再在“總計”行進行匯總方式的設置匯總計算利用預定義方式就可以實現，系統提供了一些常用的聚合函數，方便用戶進行快速設置。總計項含義總計項含義SUM求某字段的累加值VAR求某個字段值的方差AVG求某個字段的平均值GROUPBY定義要執行計算的組MIN求某個字段的最小值FIRST求在表中或查詢中第一條記錄的字段值MAX求某個字段的最大值LAST求在表或查詢中最后一條記錄的字段值COUNT求某個字段中的非空值數EXPRESSION

創建表達式中包含統計函數的計算字段STDEV求某個字段值的標準偏差WHERE指定不可用于分組的字段準則舉例：(1)統計各權限的讀者人數(2)計算各出版社書籍的平均價格(3)統計各出版社書籍的平均價格(保留一位小數)(4)檢索各出版社最貴的書籍(5)檢索“人民郵電出版社”最貴的書籍練習：分別計算男女讀者的平均年齡(平均年齡保留一位小數)思考：統計學生權限的男女讀者人數(權限、性別、人數)從上例可以看出，進行分類匯總運算操作的時候，不一定只有一個字段的分組，如果有多個字段的分組，顯示的數據就沒有那么簡潔了。3、多字段匯總為迎合用戶的計算需求，Access還提供了一種多字段匯總的方式——交叉表匯總。我們以對兩個或兩個以上的字段分組，對第三個字段進行匯總的查詢為例，這種匯總方式是將一個分組字段的值放在查詢結果的第一列(即行標題位置)，再將另一個分組字段的值放在查詢結果的第一行(即列標題位置)，最后將匯總的結果放在行和列的交叉單元格內形成統計結果，所以，我們稱之為“交叉表”。交叉表查詢的設計并不復雜，系統提供了交叉表查詢的設計方式，用戶在建立了一個新的查詢后，只需要點擊“設計—查詢類型—交叉表”就可以切換到交叉表查詢了。同前面的“匯總計算”相比，交叉表的設計多出了一個“交叉”行，用戶可以利用這一行來設置數據的排列方式。舉例：(1)統計各權限男女讀者的人數(2)統計各年份入校的男女讀者人數練習：統計各年齡的男女讀者人數思考：統計各出版社不同價格段的書籍個數(每10元為一個價格段，0-10,11-20,21-30……)4.2.3參數查詢參數查詢是動態的，它利用輸入框(InputBox)提示用戶輸入參數并檢索符合所輸入參數的記錄或值。舉例：(1)輸入權限，查找該權限的所有讀者記錄(2)輸入權限和年份，查找該權限對應年份出生的所有讀者基本方法：在條件行輸入運算符[提示語句](3)輸入姓氏，查找該姓氏的所有讀者(4)輸入年齡，查找大于該年齡的所有讀者練習：輸入任意一個漢字，查找姓名中包含這個漢字的所有讀者思考：輸入年齡下限和年齡上限，查找該年齡段的所有讀者4.2.4操作查詢操作查詢是指僅在一個操作中更改多個記錄的查詢。主要包括：更新查詢、追加查詢、刪除查詢、生成表查詢。

操作查詢是對原有數據的破壞操作，創建該類型的查詢前，建議對數據進行必要的備份！(1)更新查詢：對表中數據進行有規律的成批替換。更新操作會破壞原有數據，在運行查詢前，必須進行原有數據的備份。舉例：將所有學生的讀者編號前2位改為“24”，后面字符不變（如“5010006”改為“2410006”）操作步驟：1、打開“查詢設計視圖”2、在工具欄的“設計”選項卡的“查詢類型”分組中點擊“更新查詢”3、在“字段”欄選擇需要更新的字段；在“條件”欄輸入需要更新數據必須滿足的條件；在“更新到”欄輸入更新表達式4、保存查詢后，再次運行查詢就可以更新指定數據了思考：將所有書籍價格提高5%練習：將讀者編號的3-4位替換為入校年份(2)追加查詢：對某個數據表進行篩選后，將數據追加到另一個相同結構的數據表中。舉例：將所有“教職工”和“學生”權限的讀者追加到Reader表中(假設已存在一個與讀者信息表相同結構的Reader表)操作步驟：1、復制"讀者信息表"表結構到stu2、新建查詢，打開“查詢設計視圖”3、在工具欄的“設計”選項卡的“查詢類型”分組中點擊“追加查詢”3、在“條件”欄設置條件；4、保存查詢后，再次運行查詢就可以更新指定數據了思考：條件還可以如何設置？In(“教職工”,”學生”)(3)刪除查詢：對現有表中滿足條件的記錄進行刪除。刪除操作會破壞原有數據，在運行查詢前，必須進行原有數據的備份。舉例：刪除所有“教職工”權限的讀者操作步驟：1、新建查詢，打開“查詢設計視圖”3、在工具欄的“設計”選項卡的“查詢類型”分組中點擊“刪除查詢”3、在“條件”欄設置條件；4、保存查詢后，再次運行查詢就可以更新指定數據了(4)生成表查詢：將查詢結果以新表的形式保存舉例：將所有“教職工”和“學生”權限的讀者追加到新表Reader中(假設不存在一個與讀者信息表相同結構的Reader表)操作步驟：1、打開“查詢設計視圖”3、在工具欄的“設計”選項卡的“查詢類型”分組中點擊“生成表查詢”3、在“條件”欄設置條件；4、保存查詢后，再次運行查詢就可以更新指定數據了4.3SQL查詢在Access中，創建和修改查詢最便利的方法是使用“查詢設計視圖”。但是，在創建查詢時并不是所有的查詢都可以在系統提供的查詢“設計”視圖中進行，有的查詢只能通過SQL語句來實現。例如：檢索“計算機等級考試成績單”中所有考試合格的學生由于一級、二級語種與三級、四級語種的合格判定條件不一樣，所以很難利用查詢設計器一步完成上述功能SQL的九個核心動詞:

數據定義語言(DDL)：CREATE、DROP、ALTER

數據查詢語言(DQL)：SELECT

數據操縱語言(DML)：INSERT、UPDATE、DELETE

數據控制語言(DCL)：GRANT、REVOKESQL簡介結構化查詢語言(StructureQueryLanguage)簡稱SQL，是目前關系型數據庫的通用語言。它集數據定義(DDL)、數據操縱(DML)、數據管理(DCL)的功能于一體，可以獨立完成數據庫的全部操作。打開“查詢設計器”后，點擊【設計】選項卡【結果】分組中的【視圖】按鈕，選擇【SQL視圖】即可完成視圖切換。SQL查詢視圖的切換SQL中的查詢語句——SELECT【格式】SELECT[ALL|DISTINCT|topN]<選項>as顯示列名

FROM<表名>【功能】無條件查詢【說明】(1)ALL表示顯示全部查詢記錄，包括重復記錄

DISTINCT表示顯示無重復結果的記錄TOPN表示前N條記錄

(2)選項:字段名,表達式或函數

顯示列名:在輸出結果中自行設置的一個名稱1.基本查詢【例1】顯示所有讀者信息【例2】檢索有哪些讀者借過書select*

from讀者信息表selectdistinct

讀者編號from借閱情況表SQL中的查詢語句——SELECT【例3】顯示前5條讀者信息【例4】顯示所有讀者的“編號”和“內容”信息

“內容”顯示為“姓名-權限”

selecttop5*from讀者信息表select讀者編號as編號,姓名&"-"&權限as內容from讀者信息表SQL中的查詢語句——SELECT【練習1】顯示最貴的書籍價格【練習2】顯示所有讀者的姓、名selectmax(書籍價格)as最貴書籍價格from圖書信息表selectleft(姓名,1)as姓,mid(姓名,2,7)as名from讀者信息表SQL中的查詢語句——SELECT【格式】

SELECT<選項>as<顯示列名>

FROM<表名>WHERE<條件表達式>【功能】從一個表中查詢滿足條件的數據。【說明】條件表達式為篩選條件

2.條件查詢SQL中的查詢語句——SELECT【例1】顯示所有姓“李”的讀者信息【例2】顯示所有1995年出生的讀者信息select*from讀者信息表where姓名like“李*"select*from讀者信息表where出生日期between#1995-1-1#and#1995-12-31#select*from讀者信息表whereyear(出生日期)=1995SQL中的查詢語句——SELECT【練習1】顯示所有男教職工的讀者信息【練習2】顯示所有未還的借閱信息select*from讀者信息表where權限="教職工"and性別="男"圖書未還：在借閱情況表中的某條記錄，“實還日期”為空select*from借閱情況表或whereIsNull(實還日期)select*from借閱情況表where實還日期IsNullSQL中的查詢語句——SELECT在一個查詢語句中同時涉及到兩個或兩個以上的表時，這種查詢稱之為連接查詢（也稱為多表查詢）。在多表之間查詢必須處理表與表之間的連接關系。3.多表查詢【格式】SELECT[ALL|DISTINCT]別名.選項AS列名

FROM<表1名as別名1>,<表2名as別名2>WHERE<條件表達式>【功能】多表連接進行多字段查詢【說明】條件表達式為多表連接條件SQL中的查詢語句——SELECT【例】顯示圖書的分類情況，要求顯示書籍類別、書籍細類selecta.書籍類別,b.書籍細類from圖書類別表asa,圖書細類表asbwherea.類別代碼=b.類別代碼查詢中用到的相關表結構及關系如圖所示SQL中的查詢語句——SELECT【練習】顯示圖書借還情況，要求顯示讀者姓名、書籍名稱、借書日期、應還日期、實還日期selecta.姓名,b.書籍名稱,c.借書日期,c.應還日期,c.實還日期from讀者信息表asa,圖書信息表asb,借閱情況表ascwherea.讀者編號=c.讀者編號andb.書籍編號=c.書籍編號查詢中用到的相關表結構及表間關系如圖所示SQL中的查詢語句——SELECT在SQL語句中，一個SELECT－FROM－WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢或子查詢。4.嵌套查詢使用子查詢可以定義字段或定義字段的條件，但子查詢不能單獨作為一個查詢，它必須與其他查詢相結合，通常是做為另外一個查詢的字段、準則（或條件）來使用。如：查詢年齡比“許丹”大的所有讀者記錄。SQL中的查詢語句——SELECT【例】顯示與“秦晨”相同權限的所有讀者信息【練習】顯示書籍價格高于平均價格的所有圖書信息select*from圖書信息表where書籍價格>(selectavg(書籍價格)from圖書信息表)select*from讀者信息表where權限=(select權限from讀者信息表where姓名="秦晨")SQL中的查詢語句——SELECT【格式】SELECT[ALL|DISTINCT]<字段列表>FROM<表>[系統別名][WHERE<條件表達式>][GROUPBY<分類字段列表>…][HAVING<分組過濾條件>][ORDERBY<排序項>[ASC|DESC][,……]查詢結果按指定的字段分組。定義在GROUPBY

之后,表示指定每一分組所應滿足的條件，只有滿足條件的分組才在結果中顯示。若未定義GROUPBY，則其作用等同于WHERE子句。指定查詢結果按排序項輸出，缺省為ASC，它必須放在整個語句之后。5.分組查詢SQL中的查詢語句——SELECT【例1】按年齡從小到大的順序顯示所有讀者信息【例2】統計各權限的讀者人數select*from讀者信息表或orderby出生日期descselect權限,count(*)as人數from讀者信息表groupby權限

select*from讀者信息表orderbyyear(date())-year(出生日期)

SQL中的查詢語句——SELECT【練習1】將讀者信息表按身份證號中的生日(第7-14位)降序排序select*from讀者信息表orderbymid(身份證號,7,8)descselect權限,count(*)as人數from讀者信息表groupby權限

having權限="學生"【練習2】計算學生權限的讀者人數SQL中的查詢語句——SELECT(6)聯合查詢聯合查詢可以將兩個或兩個以上的表或查詢所對應的多個字段的記錄合并為一個查詢表中的記錄。【格式】<SELECT命令>UNION[ALL]<SELECT命令>【例】查詢所有“教職工”和“學生”權限的讀者select讀者編號,姓名from讀者信息表where權限="教職工"unionselect讀者編號,姓名from讀者信息表where權限="學生"【說明】聯合查詢不包含備注、OLE、超鏈接字段SQL中的查詢語句——SELECT數據操縱功能是實現SQL利用數據操縱語言（DataManipulationLanguage）對數據庫中的數據進行增刪改操作的。數據定義查詢的核心動詞也有三個：INSERT、UPDATE、DELETE數據操縱查詢（1）更新查詢：更新查詢是一種特殊的查詢方式，其目的不是為了進行數據的檢索，而是為了有效的進行成批數據的有規律替換。【格式】UPDATE表名SET字段名=表達式WHERE<篩選條件>【例】將所有圖書的價格提高5元update圖書信息表set書籍價格

=書籍價格+5【練習】將“人民郵電出版社”的圖書價格提高5元update圖書信息表set書籍價格

=書籍價格+5where出版社="人民郵電出版社"（2）追加查詢：將查詢到的數據以追加的方式添加到指定表中。進行追加查詢操作的時候要注意指定表中的主鍵。【格式】INSERTINTO表名SELECT查詢語句【例】將“讀者信息表”中的所有“學生”讀者追加到“學生讀者表”insertinto學生讀者表select*from讀者信息表where權限="學生"（3）刪除查詢：將指定表中滿足條件的記錄做成批刪除操作。該操作不能恢復，所以在操作前盡量對原數據進行備份。【格式】DELETE*FROM表名WHERE<篩選條件>【例】刪除所有“教職工”權限的讀者delete*from讀者信息表where權限="教職工"數據定義查詢是SQL的一種特定查詢。使用數據定義語言（

DataDefinitionLanguage）可以進行數據庫、數據表或索引的創建、刪除、更改操作。使用數據定義查詢修改數據庫對象可能會有風險，因為完成操作時并不出現確認對話框，如果操作出錯，可能造成數據丟失或意外更改表的設計。所以，最好對要運行此類查詢的表制作備份。數據操縱查詢的核心動詞包括三個：CREATE、ALTER、DROP數據定義查詢(1)創建表——CREATETABLE【格式】CREATETABLE表名(字段名

字段類型(字段大小)，……)常見字段類型引用方法文本text數值short/long/single/double日期/時間datetime是否yesno【例】利用SQL新建一張“成績表”，表結構如下：createtable成績表(姓名text(8),性別yesno,考試時間datetime,成績single)字段名字段類型字段大小姓名文本型8性別邏輯型默認考試時間日期時間型默認成績單精度型默認(2)修改表——ALTERTABLE【格式】ALTERTABLE表名ADD字段名數據類型(寬度)DROP字段名ALTER字段名數據類型(寬度)【說明】ADD：添加新字段DROP：刪除舊字段ALTER：更新已有字段【例】刪除“成績表”的“性別”字段altertable成績表drop性別(3)刪除表——DROPTABLE【格式】droptable表名【例】利用SQL刪除“成績表”droptable成績表(1)創建表——CREATETABLE【格式】CREATETABLE表名(字段名

字段類型(字段大小)，……)常見字段類型引用方法文本text數值short/long/single/double日期/時間datetime是否yesno【例】利用SQL新建一張“成績表”，表結構如下：createtable成績表(姓名text(8),性別yesno,考試時間datetime,成績single)字段名字段類型字段大小姓名文本型8性別邏輯型默認考試時間日期時間型默認成績單精度型默認(2)修改表——ALTERTABLE【格式】ALTERTABLE表名ADD字段名數據類型(寬度)DROP字段名ALTER字段名數據類型(寬度)【說明】ADD：添加新字段DROP：刪除舊字段ALTER：更新已有字段【例】刪除“成績表”的“性別”字段altertable成績表drop性別(3)刪除表——DROPTABLE【格式】droptable表名【例】利用SQL刪除“成績表”droptable成績表本章習題【1】在ACCESS數據庫中使用向導創建查詢，其數據可以來自________。A)一個表的一部分B)表或查詢C)多個表D)一個表本章習題【2】在顯示查詢結果時，如果要將數據表中的"籍貫"字段名，顯示為"出生地"，可在查詢設計視圖中改動________。A)條件B)顯示C)排序D)字段本章習題【3】在創建交叉表查詢時，列標題字段的值顯示在交叉表的位置是________。A)上面若干行B)左面若干列C)第一行D)第一列本章習題【4】創建參數查詢時，在查詢設計視圖準則中應將參數提示文本放置在________中。A)[]B)<>C){}D)()本章習題【5】下列不屬于操作查詢的是________。A)更新查詢B)刪除查詢C)參數查詢D)生成表查詢本章習題【6】在建立查詢時，若要篩選出圖書編號是"T01"或"T02"的記錄，可以在查詢設計器條件行中輸入________。A)IN("T01"AND"T02") B)NOTIN("T01"AND"T02")C)"T01"OR"T02"D)"T01"AND"T02"本章習題【7】表達式FIX（-3.25）和FIX（3.75）的結果分別是________。A)-3，4B)-4，4C)-3，3D)-4，3本章習題【8】RND()為隨機函數，執行如下語句：S=INT(100*RND())執行完畢后，S的值是________。A)[1，99]的隨機整數B)[1，100]的隨機整數C)[0，99]的隨機整數D)[0，100]的隨機整數本章習題【9】假設有一組數據：工資為8