第3章_查詢 6.22

1、二級二級ACCESSACCESS培訓(xùn)講義培訓(xùn)講義第3章 查詢 二級二級ACCESS培訓(xùn)講義培訓(xùn)講義目錄3.1 查詢概述3.2 創(chuàng)建選擇查詢3.3 創(chuàng)建交叉表查詢3.4 創(chuàng)建參數(shù)查詢3.5 創(chuàng)建操作查詢3.6 創(chuàng)建SQL查詢2二級二級ACCESS培訓(xùn)講義培訓(xùn)講義33.1 查詢概述一、查詢的功能查詢最主要的目的，是根據(jù)指定的條件，對表或者其他查詢進(jìn)行檢索，篩選出符合條件的記錄，構(gòu)成一個新的數(shù)據(jù)集合，從而方便對數(shù)據(jù)庫中的表進(jìn)行查看和分析。但查詢對象本身是一個操作的集合。查詢的主要功能：（1）選擇字段 （2）選擇記錄 （3）編輯記錄 （4）實(shí)現(xiàn)計算 （5）建立新表 （6）為窗體、報表或數(shù)據(jù)訪問頁提供數(shù)

2、據(jù)二級二級ACCESS培訓(xùn)講義培訓(xùn)講義4二、查詢的類型（1）選擇查詢通過指定條件，從一個或多個表中檢索數(shù)據(jù)，并且在數(shù)據(jù)表中按照順序顯示數(shù)據(jù)，還可以對記錄進(jìn)行計數(shù)、求和、平均值及其他類型的計算。（2）交叉表查詢使用交叉表查詢能夠以行列的格式分組和匯總數(shù)據(jù)，就像Excel的數(shù)據(jù)透視表一樣。它將數(shù)據(jù)分組，一組列于數(shù)據(jù)表的左側(cè)，另一組列在數(shù)據(jù)表的上部。二級二級ACCESS培訓(xùn)講義培訓(xùn)講義5（3）參數(shù)查詢參數(shù)查詢是在執(zhí)行時顯示對話框，要求用戶輸入查詢信息，根據(jù)輸入信息檢索字段中的記錄。（4）操作查詢操作查詢是指在一個操作中可以對一條或多條記錄進(jìn)行更改或移動的查詢。包括：生成表查詢、更新查詢、追加查詢和刪

3、除查詢四種。二級二級ACCESS培訓(xùn)講義培訓(xùn)講義6 （5）SQL查詢SQL查詢是指用戶利用SQL語句進(jìn)行查詢，SQL特定查詢包括聯(lián)合查詢、傳遞查詢、數(shù)據(jù)定義查詢和子查詢等。查詢的要素：數(shù)據(jù)源數(shù)據(jù)源查詢內(nèi)容查詢內(nèi)容條件條件二級二級ACCESS培訓(xùn)講義培訓(xùn)講義7三、查詢的條件查詢條件：指對查詢的記錄作限制條件，利用條件限制查詢的范圍，條件表達(dá)式是由操作符、文字、標(biāo)識符和函數(shù)等組合成的。1.運(yùn)算符（1）算術(shù)操作符：加（+）、減（-）、乘（*）、除（/）和乘方（）（2）關(guān)系操作符小于（）、小于等于（）、大于等于（=）、等于（=）和不等于（）二級二級ACCESS培訓(xùn)講義培訓(xùn)講義8（3）邏輯操作符AND（

4、邏輯與）、OR（邏輯或）和NOT（邏輯非）（4）連接操作符“&” 和 “+”（5）標(biāo)識操作符“！”（感嘆號）和 “.”（句號）（6）特殊操作符In、Like、Is Null、Is Not Null和Between特殊運(yùn)算符說明In用于指定一個字段值的列表，表中任意值均可與查詢的字段相匹配Like用于指定查找文本字段的字符模式Is Null用于指定某一字段為空Is Not Null用于指定某一字段為非空Between用于指定一個字段值的范圍二級二級ACCESS培訓(xùn)講義培訓(xùn)講義92.函數(shù)函數(shù)功能Abs(數(shù)值表達(dá)式)求表達(dá)式的絕對值Int(數(shù)值表達(dá)式）求運(yùn)算結(jié)果的整數(shù)部分Sqr(數(shù)值表達(dá)式)

5、計算數(shù)值表達(dá)式的平方根Log(數(shù)值表達(dá)式)計算數(shù)值表達(dá)式的自然對數(shù)Sgn(數(shù)值表達(dá)式)取數(shù)值表達(dá)式的符號值函數(shù)功能Date( )取得當(dāng)前的系統(tǒng)日期Year(date)取當(dāng)前日期的年值Month(date)取當(dāng)前日期的月值Hour(date)取當(dāng)前時間的小時值Weekday(date)取當(dāng)前日期的星期值Time( )取得當(dāng)前的系統(tǒng)時間Sec(date)取得當(dāng)前時間的秒值數(shù)值函數(shù)日期時間函數(shù)二級二級ACCESS培訓(xùn)講義培訓(xùn)講義10函數(shù)功能Sum(字符表達(dá)式)求和Avg(字符表達(dá)式)求平均值Count(字符表達(dá)式)統(tǒng)計記錄個數(shù)Max(字符表達(dá)式)求最大值Min(字符表達(dá)式)求最小值函數(shù)功能Left(

6、字符表達(dá)式，數(shù)值表達(dá)式)從左側(cè)截取指定位數(shù)的字符串Len(字符表達(dá)式)求字符串的長度Space(數(shù)值表達(dá)式)生成指定數(shù)值的空格Right(字符表達(dá)式，數(shù)值表達(dá)式)從右側(cè)截取指定位數(shù)的字符串Ltrim(字符表達(dá)式)去掉前導(dǎo)空格的字符串Rtrim(字符表達(dá)式)去掉尾部空格的字符串Trim(字符表達(dá)式)去掉首尾空格的字符串String(數(shù)值表達(dá)式，字符表達(dá)式)返回一個由字符表達(dá)式的首字符重復(fù)組成的指定長度為數(shù)值表達(dá)式值的字符串字符函數(shù)統(tǒng)計函數(shù)二級二級ACCESS培訓(xùn)講義培訓(xùn)講義113.使用數(shù)值作為查詢條件在創(chuàng)建查詢時，經(jīng)常會用數(shù)值作為查詢條件。使用數(shù)值作為查詢條件的示例如下：字段名條件功能成績=80

7、 And =90二級二級ACCESS培訓(xùn)講義培訓(xùn)講義124.使用文本值作為查詢條件使用文本值作為查詢條件示例如下：字段名條件功能職稱“教授”查詢職稱為“教授”的記錄“教授”O(jiān)r “副教授”查詢職稱為“教授”或“副教授”的記錄Right(職稱,2)= “教授”InStr (職稱, “教授”)=1 Or InStr (職稱, “教授”)=2姓名In(“李明”,”王朋”)查詢姓名為“李明”或“王朋”的記錄“李明” Or ”王朋”Not“李明”查詢姓名不為“李明”的記錄Left(姓名,1)=“王”查詢姓“王”的記錄Like“王*”InStr (姓名, “王”)=1Len (姓名)=2查詢姓名為兩個字的

8、記錄課程名稱Right(課程名稱,2)=“概論”查詢課程名稱最后兩個字為“概論”的記錄學(xué)生編號Mid(學(xué)生編號,5,2)=“03”查詢學(xué)生編號第5和第6個字符為03的記錄InStr (學(xué)生編號, “03”)=5字符串檢索檢索子字符串在字符串中出現(xiàn)的最早位置二級二級ACCESS培訓(xùn)講義培訓(xùn)講義13在查找職稱為“教授”的職工，查詢條件可以表示為：=“教授”，為了輸入方便，Access允許在條件中省略“=”，所以可以直接表示為：“教授”。輸入時如果沒有加雙引號，Access會自動加上雙引號。5.使用處理日期結(jié)果作為查詢條件字段名條件功能工作時間Between #1992-01-01# And #19

9、92-12-31#查詢1992年參加工作的記錄Year(工作時間)=1992Date( )-20查詢20天前參加工作的記錄Between Date( ) And Date( )-20查詢20天之內(nèi)參加工作的記錄出生日期Year(出生日期)=1980查詢1980年出生的記錄工作時間Year(工作時間)=1999 And Month (工作時間)=4查詢1999年4月參加工作的記錄二級二級ACCESS培訓(xùn)講義培訓(xùn)講義14注意：日期常量要用英文的“#”號括起來。6.使用字段的部分值作為查詢條件使用字段的部分值作為查詢條件的示例如下：注意：在條件中，字段名必須使用方括號括起來，而且數(shù)據(jù)類型與對應(yīng)字段定

10、義的類型相符合。字段名條件功能課程名稱Like”計算機(jī)*”查詢課程名稱以”計算機(jī)“開頭的記錄Left(課程名稱,3)=“計算機(jī)”InStr(課程名稱,“計算機(jī)”)=1Like” *計算機(jī)*”查詢課程名稱中包含”計算機(jī)“的記錄姓名Not”張*”查詢不姓”張“的記錄Left(姓名,1)“張”二級二級ACCESS培訓(xùn)講義培訓(xùn)講義157.使用空值或空字符串作為查詢條件空值是使用Null或空白來表示字段的值；空字符串是用雙引號括起來的字符串，且雙引號中間沒有空格。使用空值或空字符串作為查詢條件的示例如下：字段名條件功能姓名Is Null查詢姓名為Null（空值）的記錄Is Not Null查詢姓名有值（

11、不是空值）的記錄聯(lián)系電話“”查詢沒有聯(lián)系電話的記錄二級二級ACCESS培訓(xùn)講義培訓(xùn)講義16設(shè)置查詢條件時注意：1、日期型數(shù)據(jù)兩邊加“#”，字符型數(shù)據(jù)兩邊加半角雙引號2、在條件中字段名必須用方括號括起來3、兩個以上條件時，同行相與，異行相或4、條件中引用表名時，應(yīng)用方括號括起來，與字段名之間用“！”。如 教師檔案表!姓名二級二級ACCESS培訓(xùn)講義培訓(xùn)講義173.2 創(chuàng)建選擇查詢一、使用”查詢向?qū)А皠?chuàng)建查詢在數(shù)據(jù)庫視圖窗口中單擊”對象“控制面板中的”查詢“對象，切換到查詢窗口界面，在右邊的列表中雙擊”使用向?qū)?chuàng)建查詢“選項，使用向?qū)Р荒軇?chuàng)建帶條件的查詢二級二級ACCESS培訓(xùn)講義培訓(xùn)講義18二、

12、使用設(shè)計視圖Access有5種查詢視圖，分別為：“設(shè)計”視圖、數(shù)據(jù)表視圖、SQL視圖、數(shù)據(jù)透視表視圖和數(shù)據(jù)透視圖視圖。在“設(shè)計”視圖中，既可創(chuàng)建不帶條件的查詢，也可創(chuàng)建帶條件的查詢，還可對已建查詢進(jìn)行修改。查詢”設(shè)計“視圖窗口工具欄如下圖所示：二級二級ACCESS培訓(xùn)講義培訓(xùn)講義19使用“設(shè)計”視圖創(chuàng)建查詢的步驟：1、添加數(shù)據(jù)源表或查詢2、選擇查詢內(nèi)容及顯示內(nèi)容字段3、填寫查詢條件（可選）4、設(shè)置排序（可選）二級二級ACCESS培訓(xùn)講義培訓(xùn)講義20創(chuàng)建不帶條件的查詢：演示：查詢教師的信息，并顯示“教師編號”、“姓名”、“性別”、“學(xué)歷”、“政治面貌”、“職稱”、“系別”、“電話號碼”的信息，查

13、詢命名為“教師基本信息”。創(chuàng)建帶條件的查詢：演示：1、查詢姓“吳”的學(xué)生，并顯示其“姓名”、“性別”、“年齡”、“團(tuán)員否”的信息，查詢命名為“姓吳的學(xué)生”2、查詢職稱為“副教授或教授”的“女”教師，顯示教師全部信息，查詢命名為“高級職稱女教師”二級二級ACCESS培訓(xùn)講義培訓(xùn)講義21操作：1、查詢學(xué)生的選課成績，并顯示“學(xué)生編號”、“姓名”、“課程名稱”、“成績”等字段，查詢命名為“學(xué)生選課成績”。2、查詢職稱為“副教授或教授”的教師，顯示“教師編號”、“姓名”、“性別”、“工作時間”、“政治面目”、“職稱”、“電話號碼”的信息 ，查詢命名為“高級職稱教師”。3、查詢1983年參加工作，性別為

14、“女”的教師，并顯示其全部信息，查詢命名為“1983年工作的女教師”。 二級二級ACCESS培訓(xùn)講義培訓(xùn)講義22三、在查詢中進(jìn)行計算Access查詢中，可以執(zhí)行兩種類型的計算：預(yù)定義計算 和 自定義計算預(yù)定義計算即“總計”計算，是系統(tǒng)提供的對查詢中的記錄組或全部記錄進(jìn)行的計算，包括總計、平均值、計數(shù)、最大值、最小值、標(biāo)準(zhǔn)偏差或方差等。自定義計算可以用一個或多個字段的值進(jìn)行數(shù)值、日期和文本計算。直接在設(shè)計網(wǎng)格中創(chuàng)建新的計算字段二級二級ACCESS培訓(xùn)講義培訓(xùn)講義總計項及其意義23總計項意義分組用于執(zhí)行計算中的記錄分組總計計算每個分組字段值的總和平均值計算每個分組字段值的平均值最大值計算每個分組字

15、段值的最大值最小值計算每個分組字段值的最小值計數(shù)計算每個分組字段值的數(shù)量標(biāo)準(zhǔn)差計算每個分組字段值的標(biāo)準(zhǔn)偏差值方差計算每個分組字段值的方差值第一條記錄按照輸入時間的順序返回第一條記錄的值最后一條記錄按照輸入時間的順序返回最后一條記錄的值表達(dá)式創(chuàng)建表達(dá)式中包含的計算字段條件限制表中的部分記錄參加匯總二級二級ACCESS培訓(xùn)講義培訓(xùn)講義241.在查詢中進(jìn)行計算 例1：統(tǒng)計“教師”表中的教師人數(shù)，查詢命名為“教師人數(shù)統(tǒng)計” 例2：統(tǒng)計“學(xué)生”表中學(xué)生成績的最大值、最小值以及平均值，查詢命名為“成績統(tǒng)計”二級二級ACCESS培訓(xùn)講義培訓(xùn)講義2.在查詢中進(jìn)行分組統(tǒng)計分組統(tǒng)計時，只需在“設(shè)計”視圖中將用于分

16、組字段的“總計”行設(shè)置成“分組”即可。例如：1、統(tǒng)計“教師”表中各職稱的教師人數(shù)，查詢命名為“各職稱人數(shù)統(tǒng)計”更改計算字段名稱將“各職稱人數(shù)統(tǒng)計”中的“教師編號之計數(shù)”字段改名為“總?cè)藬?shù)”25二級二級ACCESS培訓(xùn)講義培訓(xùn)講義3.添加計算字段計算字段：是指根據(jù)一個或者多個表中的一個或者多個字段，利用表達(dá)式建立的新字段。當(dāng)用戶需要統(tǒng)計的字段不在數(shù)據(jù)表中時，或用于計算的數(shù)據(jù)值源于多個字段時，就應(yīng)該添加一個字段來顯示需要統(tǒng)計的數(shù)據(jù)。例如：查詢“教學(xué)管理”數(shù)據(jù)庫中平均分低于班級平均分的學(xué)生，并顯示其“姓名”、“班級號”（學(xué)生編號的前八位）、“平均成績”，查詢命名為“低于所在班平均成績的學(xué)生”。26二

17、級二級ACCESS培訓(xùn)講義培訓(xùn)講義練習(xí)題：1、統(tǒng)計“學(xué)生”表中的男、女學(xué)生人數(shù)，并顯示“性別”、“總數(shù)”兩個字段，查詢命名為“男女學(xué)生人數(shù)統(tǒng)計”。2、統(tǒng)計“學(xué)生”表中各門課程的平均值，顯示“課程名稱”、“平均成績”兩個字段，查詢命名為“課程成績統(tǒng)計”3、計算并輸出“學(xué)生”表中最高成績與最低成績的差值，顯示標(biāo)題為“成績差值”，查詢命名為“qT1”。4、查詢“教學(xué)管理”數(shù)據(jù)庫中平均分低于班級平均分的學(xué)生，并顯示其“姓名”、“班級號”（學(xué)生編號的前八位）、“平均成績”，查詢命名為“低于所在班平均成績的學(xué)生”。27二級二級ACCESS培訓(xùn)講義培訓(xùn)講義283.3 創(chuàng)建交叉表查詢交叉表查詢：就是將源于某個

18、數(shù)據(jù)表的字段進(jìn)行分組，一組列在數(shù)據(jù)表左側(cè)，另一組列于上部，然后在數(shù)據(jù)表行列交叉處顯示表中某個字段的各類計算值。交叉表字段說明：行標(biāo)題位于數(shù)據(jù)表左側(cè)第一列。它是指把與某一字段或記錄相關(guān)的數(shù)據(jù)放入指定的一行中，以便進(jìn)行概括；列標(biāo)題位于表的頂端。它是對某一列的字段或表進(jìn)行統(tǒng)計，并把結(jié)果放入該列；列中值字段它是用戶選擇在交叉表中顯示的字段，用戶需要為該字段指定一個總結(jié)類型，例如Sum、Avg、Min、Max函數(shù)等二級二級ACCESS培訓(xùn)講義培訓(xùn)講義29二級二級ACCESS培訓(xùn)講義培訓(xùn)講義30一、使用“交叉表查詢向?qū)А倍⑹褂谩霸O(shè)計”視圖演示：查詢“教學(xué)管理”數(shù)據(jù)庫中每個學(xué)生每門課程的成績，查詢命名為“

19、課程成績查詢”操作：1、查詢“教師”表中各院系各政治面貌的教師人數(shù)，查詢命名為“政治面貌查詢”。2、統(tǒng)計“教學(xué)管理”數(shù)據(jù)庫中各班男女成績的平均值，查詢命名為“男女生成績”。（班級號為學(xué)號的前八位）二級二級ACCESS培訓(xùn)講義培訓(xùn)講義313.4 創(chuàng)建參數(shù)查詢參數(shù)查詢是通過對話框提示用戶輸入查詢參數(shù)，然后檢索數(shù)據(jù)庫中符合用戶要求的記錄或值。分為單參數(shù)查詢和多參數(shù)查詢。一、單參數(shù)查詢在字段中只指定一個參數(shù)，在執(zhí)行查詢時，用戶只需要輸入一個參數(shù)查詢。二級二級ACCESS培訓(xùn)講義培訓(xùn)講義32二、多參數(shù)查詢就是在字段中指定多個參數(shù)，在執(zhí)行查詢時，用戶需要輸入多個參數(shù)。 根據(jù)參數(shù)查詢，可以設(shè)置輸入數(shù)據(jù)的條件

20、范圍，根據(jù)不同的條件查詢所需數(shù)據(jù)。二級二級ACCESS培訓(xùn)講義培訓(xùn)講義333.5 創(chuàng)建操作查詢操作查詢可以在一個操作中更改許多記錄。操作查詢包括：生成表查詢、刪除查詢、更新查詢和追加查詢4種。選擇查詢、交叉表查詢以及參數(shù)查詢，不會對原始數(shù)據(jù)進(jìn)行修改；而操作查詢既可以查詢數(shù)據(jù)，也可以對原始數(shù)據(jù)進(jìn)行修改。二級二級ACCESS培訓(xùn)講義培訓(xùn)講義34一、生成表查詢就是從一個或多個表中提取有用數(shù)據(jù)，創(chuàng)建為新的表。二、刪除查詢從一個或多個表中刪除一組記錄或一類記錄。三、更新查詢對一個或多個表中的記錄進(jìn)行更新和修改。四、追加查詢從一個或多個表中添加一組記錄到一個或多個表的尾部。教材P9699 例3-9至例3-

21、14二級二級ACCESS培訓(xùn)講義培訓(xùn)講義353.6 創(chuàng)建SQL查詢SQL查詢分為：聯(lián)合查詢、傳遞查詢、數(shù)據(jù)定義查詢和子查詢4種。一、查詢與SQL視圖設(shè)計視圖設(shè)計視圖SQL視圖視圖二級二級ACCESS培訓(xùn)講義培訓(xùn)講義36打開SQL視圖的方法是：先打開查詢”設(shè)計“視圖，然后選擇”視圖“菜單中的”SQL視圖“命令，或單擊工具欄中的”視圖“按鈕右側(cè)的向下箭頭按鈕，從下拉列表中選擇”SQL視圖“選項。二、SQL語言簡介SQL（結(jié)構(gòu)化查詢語言）是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言。SQL的功能：數(shù)據(jù)的查詢、操縱、定義、控制。SQL支持三級模式結(jié)構(gòu)。操作對象創(chuàng)建語句刪除語句修改語句基本表CREATE TABLEDROP

22、TABLEALTER TABLE索引CREATE INDEXDROP INDEX視圖CREATE VIEWDROP VIEW數(shù)據(jù)庫CREATE DATABASEDROP DATABASEALTER DATABASESQL的數(shù)據(jù)定義語句二級二級ACCESS培訓(xùn)講義培訓(xùn)講義37SQL基本語句包括查詢語句（SELECT語句）、插入語句（INSERT語句）、刪除語句（DELETE語句）和修改語句（UPDATE語句），后三者統(tǒng)稱為數(shù)據(jù)更新語句。意義符號語句格式約定符號實(shí)際語義 任選項必選項|必選項，但只能選擇多項中的一項,n前面的項可重復(fù)多次一般語法規(guī)定,數(shù)據(jù)項分隔符字符串常數(shù)的定界符特殊語法規(guī)定;語句

23、結(jié)束符約定符號和語法規(guī)定二級二級ACCESS培訓(xùn)講義培訓(xùn)講義38SELECT語句的基本格式：SELECT ALL/DISTINCT * | FROM , WHERE ORDER BY ASC|DESC;SELECT語句舉例：SELECT 姓名，學(xué)號，班級 FROM 學(xué)生檔案表SELECT 姓名，學(xué)號，班級 FROM 學(xué)生檔案表WHERE 性別=男;學(xué)生檔案表學(xué)生檔案表.性別性別=“男男”;二級二級ACCESS培訓(xùn)講義培訓(xùn)講義39三、創(chuàng)建SQL特定查詢1.創(chuàng)建聯(lián)合查詢聯(lián)合查詢是將一個或多個表或查詢的字段組合為查詢結(jié)果中的一個字段。使用Union聯(lián)接語句。2.創(chuàng)建傳遞查詢傳遞查詢是Access自己

24、本身不執(zhí)行查詢而傳遞給數(shù)據(jù)庫來執(zhí)行的查詢。創(chuàng)建傳遞查詢時，首先要建立和數(shù)據(jù)庫之間的連接，然后在SQL窗口中輸入相應(yīng)的SQL語句。二級二級ACCESS培訓(xùn)講義培訓(xùn)講義403.創(chuàng)建數(shù)據(jù)定義查詢數(shù)據(jù)定義查詢包括創(chuàng)建表、添加字段、刪除表及創(chuàng)建索引。（1）使用Create語句操作數(shù)據(jù)庫 格式：Create Table ( 約束條件， 約束條件)表名：要創(chuàng)建表的名稱。字段1/字段2：表中要創(chuàng)建的字段。數(shù)據(jù)類型：設(shè)置字段的數(shù)據(jù)類型。Not Null：表示字段不能為空。Unique：表示字段值是唯一的。Char：表示文本類型；Date：表示日期類型；Money：表示貨幣類型。二級二級ACCESS培訓(xùn)講義培訓(xùn)講義41（2）使用Alter語句操作數(shù)據(jù)庫 Alter語句用于向創(chuàng)建表中添加字段。格式：Alter Table Add 約束條件Drop Modi