1、計(jì)算機(jī)應(yīng)用基礎(chǔ),武漢科技大學(xué)計(jì)算機(jī)學(xué)院,第5章 數(shù)據(jù)查詢,查詢是Access處理和分析數(shù)據(jù)的工具，它能夠把多個(gè)表中的數(shù)據(jù)抽取出來(lái)，供使用者查看、更改和分析使用。,教學(xué)目標(biāo),掌握查詢的概念； 掌握各種查詢的建立和使用方法；,雙語(yǔ)課堂 C,重點(diǎn)與難點(diǎn)： 查詢的建立、查詢準(zhǔn)則、使用查詢,5.1 查詢的基本概念,查詢就是按給定的要求（包括條件、范圍、方式等）從指定的數(shù)據(jù)源中查找，將符合條件的數(shù)據(jù)提取出來(lái)，形成一個(gè)新的數(shù)據(jù)集合，但這個(gè)數(shù)據(jù)集在數(shù)據(jù)庫(kù)中實(shí)際上并不存在，只是在運(yùn)行查詢時(shí)，Access才會(huì)從查詢?cè)幢淼臄?shù)據(jù)中抽取出來(lái)。 查詢的數(shù)據(jù)源：一個(gè)表或多個(gè)相關(guān)聯(lián)的表、其他查詢 查詢的結(jié)果：窗體、報(bào)表，另一

2、個(gè)查詢的基礎(chǔ),5.1.1 查詢的功能,提取數(shù)據(jù) 實(shí)現(xiàn)計(jì)算 數(shù)據(jù)更新 產(chǎn)生新的表 作為其他對(duì)象的數(shù)據(jù)源,查詢結(jié)果,1提取數(shù)據(jù),從一個(gè)或多個(gè)表中選擇部分或全部字段 例如，從學(xué)生表的若干個(gè)字段中選取3個(gè)字段：學(xué)號(hào)、姓名、家庭住址，這是對(duì)列進(jìn)行的操作。也可以從一個(gè)或多個(gè)表中將符合某個(gè)指定條件的記錄選取出來(lái)，例如，從成績(jī)表中提取期末成績(jī)?cè)?0分以上的記錄，這是對(duì)行進(jìn)行的操作，這兩種操作可以單獨(dú)進(jìn)行，也可以同時(shí)進(jìn)行。 用來(lái)提供選擇數(shù)據(jù)的表稱為查詢操作的數(shù)據(jù)源，作為查詢數(shù)據(jù)源的也可以是已建立好的其他查詢。選擇記錄的條件稱為查詢準(zhǔn)則，也就是查詢表達(dá)式，查詢結(jié)果是一種臨時(shí)表，又稱為動(dòng)態(tài)的記錄集，通常不被保存，也

3、就是說(shuō)，每次運(yùn)行查詢，系統(tǒng)都是按事先定義的查詢準(zhǔn)則從數(shù)據(jù)源中提取數(shù)據(jù)，這樣既可以節(jié)約存儲(chǔ)空間，又可以保持查詢結(jié)果與數(shù)據(jù)源中數(shù)據(jù)的同步。,2實(shí)現(xiàn)計(jì)算,在建立查詢時(shí)可以進(jìn)行一系列的計(jì)算，例如統(tǒng)計(jì)每個(gè)班學(xué)生的人數(shù)、計(jì)算每個(gè)學(xué)生的平均分等，也可以定義新的字段來(lái)保存計(jì)算的結(jié)果。,3數(shù)據(jù)更新,在Access中，對(duì)數(shù)據(jù)表中的記錄進(jìn)行的更新操作也是查詢的功能，主要包括添加記錄、修改記錄和刪除記錄。,4產(chǎn)生新的表,利用查詢得到的結(jié)果可以建立一個(gè)新表。例如，將期末成績(jī)?cè)?0分以上的記錄找出來(lái)并存放在一個(gè)新表中。,5作為其他對(duì)象的數(shù)據(jù)源,查詢的運(yùn)行結(jié)果可以作為窗體、報(bào)表和數(shù)據(jù)訪問(wèn)頁(yè)的數(shù)據(jù)源，也可以作為其他查詢的數(shù)據(jù)

4、源。,Access的查詢不僅僅是從數(shù)據(jù)源中提取數(shù)據(jù)，有的查詢操作還包含了對(duì)原來(lái)數(shù)據(jù)表的編輯和維護(hù)。,5.1.2 查詢的類型,選擇查詢 交叉表查詢 參數(shù)查詢 操作查詢 SQL查詢,生成表查詢 刪除查詢 更新查詢 追加查詢,1選擇查詢,（1）按指定的條件，從數(shù)據(jù)源中提取數(shù)據(jù) （2）產(chǎn)生新的字段保存計(jì)算的結(jié)果 （3）分組統(tǒng)計(jì),2參數(shù)查詢,參數(shù)查詢也屬于選擇查詢，與上面的選擇查詢不同的是，它的查詢準(zhǔn)則中的具體值（即參數(shù)值）是在查詢運(yùn)行時(shí)由用戶輸入的，而選擇查詢的查詢準(zhǔn)則中的參數(shù)值則是在查詢的設(shè)計(jì)階段事先指定的。,3交叉表查詢,交叉表查詢將來(lái)源于表或查詢中的字段進(jìn)行分組，一組列在數(shù)據(jù)表的左側(cè)，一組列在數(shù)

5、據(jù)表的頂端，然后在數(shù)據(jù)表行與列的交叉處顯示表中某個(gè)字段的統(tǒng)計(jì)值?？梢哉f(shuō)交叉表查詢就是利用了表中的行和列來(lái)統(tǒng)計(jì)數(shù)據(jù)。,4操作查詢,先按照條件查詢結(jié)果，然后用查詢的結(jié)果對(duì)數(shù)據(jù)表進(jìn)行編輯操作。 （1）生成表查詢 （2）刪除查詢 （3）更新查詢 （4）追加查詢,5SQL查詢,SQL（Structured Query Laguage，結(jié)構(gòu)化查詢語(yǔ)言）查詢就是使用SQL語(yǔ)句來(lái)創(chuàng)建的一種查詢。 查詢的實(shí)現(xiàn) 在數(shù)據(jù)庫(kù)中建立查詢對(duì)象 在VBA程序代碼中使用結(jié)構(gòu)化查詢語(yǔ)言SQL,5.1.3 建立查詢的方法,查詢向?qū)?chuàng)建查詢 使用設(shè)計(jì)視圖創(chuàng)建查詢 在SQL窗口中創(chuàng)建查詢,1使用查詢向?qū)?chuàng)建查詢,利用查詢向?qū)?chuàng)建的查

6、詢只能從數(shù)據(jù)源中指定若干個(gè)字段進(jìn)行輸出，但不能通過(guò)設(shè)置條件來(lái)限制檢索的記錄。 例5.1 為學(xué)生表創(chuàng)建名為“入學(xué)成績(jī)”的查詢，查詢結(jié)果中包括學(xué)號(hào)、姓名、性別、專業(yè)和入學(xué)成績(jī)5個(gè)字段。,2使用設(shè)計(jì)視圖建立查詢,利用查詢向?qū)Ы⒉樵儠r(shí)，不能按指定條件選擇記錄，這時(shí)可以使用設(shè)計(jì)視圖來(lái)建立。 例5.2 在學(xué)生表中查詢?nèi)雽W(xué)成績(jī)?cè)?50分（含550）以上的記錄，查詢結(jié)果中包括的字段與上例相同，查詢名稱為“入學(xué)成績(jī)高于550”。,3在SQl_窗口中建立SQl_查詢,在Access中，也可以在SQL窗口中直接輸入SQL命令建立查詢。對(duì)于上例，在“視圖”按鈕中選擇“SQL視圖”，可以在SQL視圖中顯示出下圖的內(nèi)容

7、。,1“查詢”菜單 2視圖方式 3工具欄按鈕,5.1.4 創(chuàng)建查詢使用的工具,5.1.5 運(yùn)行查詢,創(chuàng)建查詢時(shí) （1）單擊工具欄上的“運(yùn)行”按鈕。 （2）單擊工具欄上的視圖按鈕，將視圖方式切換到“數(shù)據(jù)表視圖”。 查詢創(chuàng)建后 （1）在“數(shù)據(jù)庫(kù)”窗口中，單擊“查詢”對(duì)象，然后雙擊要運(yùn)行的查詢。 （2）在“數(shù)據(jù)庫(kù)”窗口中，單擊“查詢”對(duì)象，選擇要運(yùn)行的查詢，然后單擊“打開(kāi)”按鈕。,5.2 查詢準(zhǔn)則,查詢建立的過(guò)程 (1) 選擇數(shù)據(jù)源 (2) 指定查詢類型 (3) 設(shè)置查詢準(zhǔn)則 (4) 為查詢命名 查詢準(zhǔn)則是用運(yùn)算符將常量、字段名（變量）、函數(shù)連接起來(lái)構(gòu)成的表達(dá)式，即查詢表達(dá)式，例如前面例子中的“=5

8、50”。,常量書寫,數(shù)字常量，則直接書寫，例如：3.1416。 文本型常量，要用半角的雙引號(hào)“”將文本括起來(lái)，如“王中華”。 日期型常量，要用井號(hào)“#”將日期括起來(lái)，如#1988-10-01#。,字段名書寫,通常要將字段名放在方括號(hào)中，如學(xué)號(hào)、姓名等，在輸入時(shí)，如果不寫方括號(hào)，系統(tǒng)會(huì)在條件中自動(dòng)加上方括號(hào)，如果字段名中含有空格，則方括號(hào)是不能省的。 如果在一個(gè)查詢中的數(shù)據(jù)源不止一個(gè)，還應(yīng)該在字段名前標(biāo)明字段所在的表或查詢，表示格式是： 表名 ! 字段名 或 查詢名 ! 字段名 例如，學(xué)生表中的姓名應(yīng)該寫成學(xué)生 ! 姓名,5.2.1 條件中使用的運(yùn)算符,1算術(shù)運(yùn)算符 包括加（+）、減（-）、乘（

9、*）、除（）、整除（）、乘方（）、求余（mod）。 2關(guān)系運(yùn)算符 等于（=）、不等于（）、小于（）、大于等于（=）。 3邏輯運(yùn)算符 常用的邏輯運(yùn)算符有3個(gè)：與（And）、或（Or）、非（Not）。 4其他的特殊運(yùn)算符 (1) In (2) Between A and B (3) 與空值有關(guān)的運(yùn)算符 ：Is Null、Is NotNull (4) Like “?”、“*”、“#” 、“” (5) & 將兩個(gè)字符串進(jìn)行連接。,5.2.2 條件中使用的函數(shù),數(shù)值函數(shù) 文本函數(shù) 日期時(shí)間函數(shù),1數(shù)值函數(shù),(1) 絕對(duì)值函數(shù)：Abs(數(shù)值表達(dá)式) 功能：返回?cái)?shù)值表達(dá)式值的絕對(duì)值。 (2) 取整函數(shù)：In

10、t(數(shù)值表達(dá)式) 功能：返回?cái)?shù)值表達(dá)式值的整數(shù)部分。 (3) 平方根函數(shù)：Sqr(數(shù)值表達(dá)式) 功能：返回?cái)?shù)值表達(dá)式值的算術(shù)平方根。 (4) 符號(hào)函數(shù)：Sgn(數(shù)值表達(dá)式) 功能：返回?cái)?shù)值表達(dá)式值的符號(hào)值，當(dāng)表達(dá)式的值為正、負(fù)和零時(shí)，函數(shù)值分別為1、-1和0。,2文本函數(shù),在本類函數(shù)中的參數(shù)n、n1、n2都是數(shù)字表達(dá)式，文本函數(shù)用于對(duì)字符串進(jìn)行處理，在Access的字符串中，一個(gè)漢字也作為一個(gè)字符處理。 (1) 空格函數(shù) 格式：Space(n) 功能：返回由n個(gè)空格組成的字符串。 (2) 重復(fù)字符函數(shù) 格式：String(n，文本表達(dá)式) 功能：返回“文本表達(dá)式”的第1個(gè)字符組成的字符串，字符

11、個(gè)數(shù)是n個(gè)。,2文本函數(shù),(3) 截取子串函數(shù) 格式：Left(文本表達(dá)式,n) Right(文本表達(dá)式,n) Mid(文本表達(dá)式,nl,n2 ) 功能：Left( )從文本表達(dá)式左邊第1個(gè)字符開(kāi)始截取n個(gè)字符。 Right( )從文本表達(dá)式右邊第1個(gè)字符開(kāi)始截取n個(gè)字符。 Mid( )從文本表達(dá)式左邊第n1位置開(kāi)始，截取連續(xù)n2個(gè)字符。 說(shuō)明： 文本表達(dá)式是Null時(shí)，返回Null值。 n為0時(shí)，返回一個(gè)空串。 n的值大于或等于文本表達(dá)式的字符個(gè)數(shù)時(shí)，返回文本表達(dá)式。 省略n2，則從n1位置開(kāi)始截取以后的所有字符串。,Left(“計(jì)算機(jī)等級(jí)考試”，3) =“計(jì)算機(jī)” Right(“計(jì)算機(jī)等級(jí)

12、考試”，2) =“考試” Mid(“計(jì)算機(jī)等級(jí)考試”，4，2) =“等級(jí)”,2文本函數(shù),(4) 字符串長(zhǎng)度函數(shù) 格式：Len(文本表達(dá)式) 功能：返回文本表達(dá)式中字符的個(gè)數(shù)，即字符串的長(zhǎng)度。 例如，函數(shù)Len(“計(jì)算機(jī)等級(jí)考試”)的結(jié)果是7。 表達(dá)式Len(姓名)=2表示查詢姓名為兩個(gè)字的記錄。 (5) 刪除前后空格函數(shù) 格式：Ltrim(文本表達(dá)式) Rtrim(文本表達(dá)式) Trim(文本表達(dá)式) 功能：Ltrim( )返回去掉文本表達(dá)式前導(dǎo)空格后的字符串；Rtrim( )返回去掉文本表達(dá)式尾部空格后的字符串；Trim( )返回去掉文本表達(dá)式前導(dǎo)和尾部空格后的字符串。,3日期時(shí)間函數(shù),(1

13、) 系統(tǒng)日期與時(shí)間函數(shù) 格式： Now( ) Date( ) Time( ) 功能： Now( )返回系統(tǒng)當(dāng)前的日期時(shí)間，由操作系統(tǒng)控制。 Date( )返回系統(tǒng)當(dāng)前的日期。 Time( )返回系統(tǒng)當(dāng)前的時(shí)間。,3日期時(shí)間函數(shù),(2) 求年份、月份、日和星期函數(shù) 格式： Year(日期表達(dá)式|日期時(shí)間表達(dá)式) Month(日期表達(dá)式|日期時(shí)間表達(dá)式) Day(日期表達(dá)式|日期時(shí)間表達(dá)式) Weekday(日期表達(dá)式|日期時(shí)間表達(dá)式) 功能： Year( )返回日期中的年份； Month( )返回日期中的月份； Day( )返回日期中的日； Weekday( )返回日期中的星期，從星期日到星期六

14、的值分別是17。,3日期時(shí)間函數(shù),(3) 時(shí)、分和秒函數(shù) 格式： Hour(時(shí)間表達(dá)式|日期時(shí)間表達(dá)式) Minute(時(shí)間表達(dá)式|日期時(shí)間表達(dá)式) Second(時(shí)間表達(dá)式|日期時(shí)間表達(dá)式) 功能： Hour( )返回時(shí)間中的小時(shí)值； Minute( )返回時(shí)間中的分鐘； Second( )返回時(shí)間中的秒。 使用日期函數(shù)可以構(gòu)成比較復(fù)雜的表達(dá)式。,例如，為出生日期定義下面的條件： (1) Between #1988-01-01# and #1988-12-31# 表示查詢1988年出生的記錄。 (2) Year(出生日期)=1988 查詢結(jié)果與上面是一樣的。 (3) Year(出生日期)=1

15、988 and Month(出生日期)=10 表示查詢1988年10月出生的記錄。 (4) Date()-30 查詢30天前出生的記錄。,5.3 選擇查詢,自由地從一個(gè)或多個(gè)表或查詢中抽取相關(guān)的字段和記錄進(jìn)行分析和處理。,5.3.1 組合條件查詢,例5.3 在學(xué)生表中查詢已通過(guò)四級(jí)的女生。,5.3.1 組合條件查詢,例5.4 查詢期中或期末成績(jī)不及格的記錄，要求顯示學(xué)號(hào)、姓名、性別、期中和期末5個(gè)字段。本題的數(shù)據(jù)源是兩個(gè)表，這兩個(gè)表之間已經(jīng)建立了一對(duì)多的關(guān)系，主表是學(xué)生表，從表是成績(jī)表。,5.3.1 組合條件查詢,例5.5 查詢“計(jì)算機(jī)”課程期末考試不及格學(xué)生的學(xué)號(hào)和姓名。,例5.6 查詢“專

16、業(yè)”是工商或者法學(xué)，選修了“體育”這門課程的學(xué)生。,5.3.1 組合條件查詢,5.3.2 自定義計(jì)算查詢,通常，查看學(xué)生成績(jī)時(shí)，用戶更關(guān)心學(xué)生每門課程的綜合成績(jī)。但由于綜合成績(jī)是由平時(shí)成績(jī)、期中成績(jī)和期末成績(jī)折合而成的，根據(jù)數(shù)據(jù)庫(kù)的設(shè)計(jì)原則，這類字段不宜作為原始數(shù)據(jù)保存，所以在數(shù)據(jù)表中沒(méi)有存儲(chǔ)學(xué)生的綜合成績(jī)。為查看此類信息，需要在查詢中重新定義字段。自定義計(jì)算就是在設(shè)計(jì)網(wǎng)格中創(chuàng)建新的計(jì)算字段。,5.3.2 自定義計(jì)算查詢,例5.7 用成績(jī)表創(chuàng)建查詢，計(jì)算并顯示每個(gè)人的綜合成績(jī)，綜合成績(jī)是平時(shí)成績(jī)10%，期中成績(jī)20%，期末成績(jī)70%。,說(shuō)明：“綜合成績(jī)”字段是虛擬字段，計(jì)算的結(jié)果并不保存在表中

17、。Access在每次運(yùn)行查詢時(shí)都將重新進(jìn)行計(jì)算，以使計(jì)算結(jié)果永遠(yuǎn)都以數(shù)據(jù)庫(kù)中最新的數(shù)據(jù)為準(zhǔn)。,5.3.3 預(yù)定義計(jì)算查詢,預(yù)定義計(jì)算用于對(duì)查詢中的分組記錄或全部記錄進(jìn)行“總計(jì)”計(jì)算，通過(guò)在設(shè)計(jì)視圖窗口的“總計(jì)”行設(shè)置聚合函數(shù)來(lái)實(shí)現(xiàn)。Access中可以使用的聚合函數(shù)及其作用如下： 總計(jì)：計(jì)算某個(gè)字段的累加值。 平均值：計(jì)算某個(gè)字段的平均值。 計(jì)數(shù)：統(tǒng)計(jì)某個(gè)字段中非空值的個(gè)數(shù)。 最大值：計(jì)算某個(gè)字段中的最大值。 最小值：計(jì)算某個(gè)字段中的最小值。 標(biāo)準(zhǔn)差：計(jì)算某個(gè)字段的標(biāo)準(zhǔn)差。 方差：計(jì)算某個(gè)字段的方差。 分組：定義用來(lái)分組的字段。 第一條記錄：求出在表或查詢中第一條記錄的字段值。 最后一條記錄：求

18、出在表或查詢中最后一條記錄的字段值 表達(dá)式：創(chuàng)建表達(dá)式中包含統(tǒng)計(jì)函數(shù)的計(jì)算字段。 條件：指定分組滿足的條件。,1對(duì)全部記錄進(jìn)行“總計(jì)”計(jì)算,例5.8 建立一查詢，統(tǒng)計(jì)期中和期末考試的平均成績(jī)。,2對(duì)分組記錄進(jìn)行“總計(jì)”計(jì)算,例5.9 建立一查詢，統(tǒng)計(jì)每個(gè)專業(yè)的人數(shù)。,2對(duì)分組記錄進(jìn)行“總計(jì)”計(jì)算,例5.10 創(chuàng)建“計(jì)算機(jī)期末成績(jī)統(tǒng)計(jì)”查詢，實(shí)現(xiàn)對(duì)“計(jì)算機(jī)”課程期末成績(jī)的最高分、最低分和平均分的統(tǒng)計(jì)。,2對(duì)分組記錄進(jìn)行“總計(jì)”計(jì)算,例5.11 建立一查詢，統(tǒng)計(jì)每個(gè)學(xué)生所學(xué)課程的綜合平均成績(jī)。,5.3.4 排序查詢結(jié)果,在前面的總成績(jī)查詢和綜合平均成績(jī)查詢結(jié)果中，顯示的默認(rèn)順序是學(xué)號(hào)。若用戶需要查

19、看成績(jī)排名，則需要在查詢中設(shè)計(jì)排序規(guī)則。,5.3.4 排序查詢結(jié)果,例5.12 修改“總成績(jī)表”查詢，實(shí)現(xiàn)按課程成績(jī)排名。,5.3.4 排序查詢結(jié)果,例5.13 從“總成績(jī)表”查詢中查找綜合成績(jī)最高的前3名。,5.4 參數(shù)查詢,參數(shù)查詢利用對(duì)話框，提示輸入?yún)?shù)，并檢索符合所輸參數(shù)的記錄?？梢越⒁粋€(gè)參數(shù)提示的單參數(shù)查詢，也可以建立多個(gè)參數(shù)提示的多參數(shù)查詢。,5.4.1 單參數(shù)查詢,創(chuàng)建單參數(shù)查詢，就是在字段中指定一個(gè)參數(shù)，在執(zhí)行參數(shù)查詢時(shí)，輸入一個(gè)參數(shù)值。 例5.14 創(chuàng)建“期末成績(jī)統(tǒng)計(jì)”查詢，統(tǒng)計(jì)各門課程期末成績(jī)的最高分、最低分和平均分。,5.4.2 多參數(shù)查詢,例5.15 創(chuàng)建“學(xué)生成績(jī)查

20、詢”，每次運(yùn)行時(shí)輸入不同的學(xué)號(hào)和課程名稱，可以查詢?cè)搶W(xué)號(hào)學(xué)生某門課程的綜合成績(jī)，查詢結(jié)果中要求有學(xué)號(hào)、姓名、性別、課程名和綜合成績(jī)等字段。,5.5 交叉表查詢,所謂交叉表查詢，就是將來(lái)源于某個(gè)表中的字段進(jìn)行分組，一組列在查詢表的左則，一組列在查詢表的上部，然后在查詢表行與列的交叉處顯示表中某個(gè)字段的各種計(jì)算值。,5.5.1 使用查詢向?qū)?chuàng)建交叉表查詢,例5.16 顯示每個(gè)學(xué)生每門課程的期末成績(jī)。,5.5.2 使用設(shè)計(jì)視圖創(chuàng)建交叉表查詢,例5.17 分別統(tǒng)計(jì)每個(gè)專業(yè)每門課程的平均綜合成績(jī)。,5.6 操作表查詢,除了能從數(shù)據(jù)源中選擇數(shù)據(jù)外，還可以改變表中的內(nèi)容，例如增加數(shù)據(jù)、刪除記錄和更新數(shù)據(jù)等。 用戶在進(jìn)行操作查詢之前，應(yīng)該先對(duì)數(shù)據(jù)表進(jìn)行備份。,5.6.1 生成表查詢,生