第四章 查詢的創(chuàng)建和使用

第4章查詢的創(chuàng)建和使用4.1查詢概述4.1.1查詢的概念根據(jù)給定的條件從數(shù)據(jù)庫的一個或多個表中篩選出符合條件的記錄，構(gòu)成一個數(shù)據(jù)集合，供使用者查看、更改和分析使用。查詢從中獲取數(shù)據(jù)的表稱為查詢的數(shù)據(jù)源。查詢的結(jié)果是一個動態(tài)的數(shù)據(jù)集合，會隨著數(shù)據(jù)源的變化而變化。查詢的主要功能:1.提取數(shù)據(jù)可以從一個或多個表或查詢中重新組合、檢索出用戶需要的數(shù)據(jù)。可以從一個或多個表中選擇部分或全部字段，也可以從一個或多個表中將符合條件的記錄選取出來。2.數(shù)據(jù)更新更新操作包括添加、修改、刪除記錄。查詢的主要功能:3.實現(xiàn)計算在查詢中對相關(guān)數(shù)據(jù)進行統(tǒng)計、計算，還可以建立新的字段來保存計算的結(jié)果，這樣可以簡化處理工作，用戶不必每次都在原始數(shù)據(jù)上進行檢索，從而提高了整個數(shù)據(jù)庫的性能。4.產(chǎn)生新的表并可作為其他對象的數(shù)據(jù)源可以建立一個新表來保存查詢的結(jié)果。查詢的結(jié)果還可作為窗體、報表等對象的數(shù)據(jù)源。4.1.2查詢的類型1.選擇查詢最常用、最基本的一種查詢。它可以根據(jù)用戶提供的條件，從一個或多個表中檢索數(shù)據(jù)，并且在數(shù)據(jù)表中顯示結(jié)果。2.參數(shù)查詢是一種交互式查詢，在建立查詢時為用戶留有動態(tài)參數(shù)接口。在執(zhí)行參數(shù)查詢時會顯示一個對話框，提示用戶根據(jù)需要輸入查詢條件，然后再檢索相應(yīng)的記錄。參數(shù)查詢具有很強的靈活性。3.交叉表查詢可以計算并重新組織數(shù)據(jù)的結(jié)構(gòu)，更加方便地分析數(shù)據(jù)。交叉表查詢可以計算數(shù)據(jù)的平均值、總計、最大值、最小值或其他類型的總和。4.1.2查詢的類型4.操作查詢操作查詢用于添加、更改或刪除數(shù)據(jù)。操作查詢有以下4種類型：刪除查詢：刪除查詢可以從一個或多個表中刪除一組記錄。追加查詢：追加查詢可將一組記錄添加到一個或多個表的尾部。運行追加查詢的結(jié)果是向相關(guān)表中自動添加記錄，增加表的記錄數(shù)。更新查詢：更新查詢可根據(jù)指定條件對一個或多個表中的記錄進行更改。生成表查詢：生成表查詢是利用一個或多個表中的全部或部分數(shù)據(jù)創(chuàng)建新表。4.1.2查詢的類型5.SQL查詢SQL是一種結(jié)構(gòu)化查詢語言，是數(shù)據(jù)庫操作的工業(yè)化標準語言，所有的數(shù)據(jù)庫管理系統(tǒng)都支持這種語言。SQL查詢就是使用SQL語句創(chuàng)建的查詢。4.1.3查詢視圖1.設(shè)計視圖設(shè)計視圖用于對查詢設(shè)計進行編輯，通過設(shè)計視圖可以創(chuàng)建SQL之外的各種類型查詢。2.數(shù)據(jù)表視圖數(shù)據(jù)表視圖用于查看查詢運行的結(jié)果，是查詢的數(shù)據(jù)瀏覽器。3.SQL視圖SQL視圖是查看和編輯SQL語句的窗口，可以查看用設(shè)計視圖創(chuàng)建的查詢所產(chǎn)生的SQL語句，也可以對SQL語句進行編輯和修改。4.1.3查詢視圖4.數(shù)據(jù)透視表和數(shù)據(jù)透視圖視圖在數(shù)據(jù)透視表和數(shù)據(jù)透視圖視圖中，可以根據(jù)需要生成數(shù)據(jù)透視表和數(shù)據(jù)透視圖，從而對數(shù)據(jù)進行分析，得到直觀的分析結(jié)果。4.2選擇查詢的創(chuàng)建選擇查詢的概念：根據(jù)用戶提供的條件，從一個或多個表中檢索數(shù)據(jù)，并且在數(shù)據(jù)表中顯示結(jié)果。1.建立單表查詢【例4-1】查詢學(xué)生的基本信息，要求顯示學(xué)生的學(xué)號、姓名、性別、出生日期和院系等信息，所建查詢命名為“學(xué)生基本信息查詢”。4.2.1使用簡單查詢向?qū)?.建立多表查詢當所需要查詢的信息來自兩個或兩個以上的表或查詢時就需要建立多表查詢。建立多表查詢的各個表必須要有關(guān)聯(lián)字段，并且事先應(yīng)通過這些關(guān)聯(lián)字段建立了表間關(guān)系。【例4-2】查詢學(xué)生的課程成績，要求顯示“學(xué)號”、“姓名”、“課程號”、“課程名稱”和“成績”。4.2.2使用查詢設(shè)計視圖打開查詢設(shè)計視圖的方法查詢設(shè)計視圖的結(jié)構(gòu)【例4-3】查詢院系為“信息學(xué)院”的學(xué)生信息，要求顯示“學(xué)號”、“姓名”、“出生日期”及“籍貫”?！纠?-4】查詢選修了“大學(xué)計算機基礎(chǔ)”課程的學(xué)生信息，要求顯示“學(xué)號”、“姓名”、“院系”及“課程名稱”和“成績”，并按成績降序顯示。4.2.3查詢條件打開表達式生成器的方法表達式生成器的結(jié)構(gòu)表達式就是運算符、常量、函數(shù)和字段名稱、控件和屬性的任意組合，計算結(jié)果為單個值。1.常量（1）數(shù)字型常量：如分數(shù)“89”、“90”等。（2）文本型常量：如“信息學(xué)院”、“物流學(xué)院”等，可使用“*”和“？”通配符。（3）日期型常量：如出生日期#1992-7-1#。（4）是否型常量：Yes、No、True、False。（5）空字段值：“IsNull”表示字段值為空，“IsNotNull”表示字段值不為空。表達式中常用的運算符有算術(shù)運算符、關(guān)系運算符、邏輯運算符、標識符和特殊操作符。

算術(shù)運算符：如+、-、*、/等。

關(guān)系運算符：如>、>=、<、<=、=、<>。

邏輯運算符：如AND、OR、NOT。

標識符：通常是一個對象的名字，一般指的是字段的名字。引用字段名稱時需要用“[]”將其名稱括起來。2.表達式中的運算符

（1）用邏輯運算符組合條件

And表示當兩個操作數(shù)的值都為TRUE時，表達式的值才為TRUE。

Or表示兩個操作數(shù)的值有一個為TRUE時，表達式的值就為TRUE。用Or運算符可將條件進行擴展。

Not則生成操作數(shù)的相反值。

（2）用關(guān)系運算符組合條件（3）用Between運算符組合條件Between運算符也可用于指定字段的取值范圍，范圍之間用And連接。（4）用like組合條件Like運算符用于查找字段的部分值，格式為：Like字符模式在所定義的字符模式中，可用“？”表示該位置可匹配任何一個字符；用“*”表示該位置可匹配零個或多個字符；用“#”表示該位置可匹配一個數(shù)字；用“[]”描述一個范圍。（5）用In組合條件In運算符用于指定字段的一系列的值，與like不同，in運算中不允許使用通配符。3.函數(shù)常用的數(shù)值函數(shù)函數(shù)

功能Abs（數(shù)值表達式）

返回數(shù)值表達式值的絕對值Int（數(shù)值表達式）

返回數(shù)值表達式值的整數(shù)部分Sqr（數(shù)值表達式）

返回數(shù)值表達式值的平方根Sgn（數(shù)值表達式）

返回數(shù)值表達式值的符號值常用的文本函數(shù)函數(shù)

功能Left（字符串表達式，n）

從字符串表達式左側(cè)第1個字符開始截取n個字符Right（字符串表達式，n）

從字符串表達式右側(cè)第1個字符開始截取n個字符Len（字符串表達式）

返回字符串表達式中字符的個數(shù)Mid（字符串表達式，n1[,n2]）

從字符串表達式左邊n1位置開始，截取連續(xù)n2個字符常用的日期時間函數(shù)函數(shù)

功能Now（）

返回系統(tǒng)當前的日期時間Date（）

返回系統(tǒng)當前的日期Time（）

返回系統(tǒng)當前的時間Day（日期表達式）

返回日期中的日Month（日期表達式）

返回日期中的月份Year（日期表達式）

返回日期中的年份4.2.4編輯查詢1.增加字段2.刪除字段3.修改字段4.移動字段5.調(diào)整設(shè)計網(wǎng)格的列寬4.3在查詢中進行計算4.3.1查詢中的計算功能預(yù)定義計算自定義計算查詢中的常用計算計算名

功能合計

計算一組記錄中某個字段值的總和平均值

計算一組記錄中某個字段值的平均值最大值

計算一組記錄中某個字段值的最大值最小值

計算一組記錄中某個字段值的最小值計數(shù)

計算一組記錄中記錄的個數(shù)First

一組記錄中某個字段的第一個值Last

一組記錄中某個字段的最后一個值Expression

創(chuàng)建一個由表達式產(chǎn)生的計算字段Where

設(shè)定分組條件以便選擇記錄4.3.2總計查詢總計查詢是通過對查詢設(shè)計視圖窗口中的總計行進行設(shè)置實現(xiàn)的，用于對查詢中的全部記錄進行總和、平均值、最大值、最小值的計算?！纠?-5】查詢?nèi)W(xué)生的入學(xué)成績情況：平均入學(xué)成績、最高成績和最低成績，并定義查詢字段依次為平均分、最高分、最低分。4.3.3分組總計查詢分組是指在查詢設(shè)計窗口中指定某一字段為分組字段，將該字段值相同的所有記錄組合在一起，并對分組進行統(tǒng)計計算。【例4-6】查詢?nèi)８髟合祵W(xué)生的入學(xué)成績情況：即查詢各院系的平均入學(xué)成績、最高成績和最低成績，定義查詢字段依次為平均分、最高分、最低分，并要求平均分保留小數(shù)點后1位。4.3.4添加計算字段當需要統(tǒng)計的數(shù)據(jù)在表中沒有相應(yīng)的字段，或者用于計算的數(shù)據(jù)值來源于多個字段時，應(yīng)在設(shè)計網(wǎng)格中添加一個計算字段，計算字段是指根據(jù)一個或多個表中的一個或多個字段并使用表達式建立的新字段?！纠?-7】計算所有學(xué)生的年齡，要求顯示每個學(xué)生的“學(xué)號”、“姓名”和“年齡”?！纠?-8】查詢每個學(xué)生的獎學(xué)金級別，要求顯示的字段有：“學(xué)號”、“姓名”、“院系”、“入學(xué)成績”和“獎學(xué)金等級”。獎學(xué)金等級的劃分標準是：入學(xué)成績>=650的為“一級”，620=<入學(xué)成績<650的為“二級”，590=<入學(xué)成績<620的為“三級”，否則沒有。iif()函數(shù)的格式如下：iif(條件表達式1，表達式2，表達式3）功能：首先計算條件表達式1，若條件表達式1的值為真返回表達式2的值否則返回表達式3的值。4.4其他查詢的設(shè)計4.4.1交叉表查詢交叉表查詢以一種緊湊的、類似于電子表格的形式顯示查詢字段，并且可以計算該字段的合計或平均值等。使用交叉表查詢計算和重構(gòu)數(shù)據(jù)，可以簡化數(shù)據(jù)分析。1.交叉表查詢向?qū)А纠?-9】建立學(xué)生與課程成績交叉表查詢，統(tǒng)計每名學(xué)生選修課程的成績。2.設(shè)計視圖中的交叉表查詢【例4-10】利用交叉表查詢，統(tǒng)計每名學(xué)生選修課程情況。要求顯示學(xué)生“姓名”、選課數(shù)量、平均成績以及所學(xué)課程的成績。4.4.2參數(shù)查詢創(chuàng)建這種參數(shù)的查詢只要在查詢設(shè)計窗口中，在要定義為參數(shù)的字段的條件行中輸入用“[]”括起來的提示信息，在運行查詢時這些提示信息出現(xiàn)在“輸入?yún)?shù)值”輸入框的上方，然后保存查詢文件?！纠?-11】創(chuàng)建一個帶有參數(shù)的查詢，要求先輸入學(xué)生的學(xué)號，之后顯示該學(xué)生的“學(xué)號”、“姓名”、“性別”、“課程名稱”及“成績”。4.4.3操作查詢Access2010的操作查詢包括以下幾種查詢：生成表查詢。利用從一個或多個表獲得的數(shù)據(jù)創(chuàng)建一個新的表。更新查詢。對一個或多個表中的多個記錄的某些字段的值進行修改。追加查詢。為一個或多個表添加從一個或多個表中獲得的一組記錄。刪除查詢。對一個或多個表中滿足條件的一組記錄進行刪除操作。1.生成表查詢【例4-12】在“新教務(wù)管理數(shù)據(jù)庫”中創(chuàng)建一個“不及格學(xué)生名單”的表。要求該表中要有：“學(xué)號”、“姓名”、“性別”、“院系”、“課程名稱”和“成績”等字段?！纠?-13】將“學(xué)生”表中院系為“信息學(xué)院”的學(xué)生記錄保存到當前數(shù)據(jù)庫名為“信息學(xué)院學(xué)生名單”的新表中。2.更新查詢【例4-14】在“不及格學(xué)生名單”表中添加一個“補考日期”字段，并將“2012/10/10”輸入到“補考日期”字段中。3.追加查詢【例4-15】將“學(xué)生”表中院系為物流學(xué)院的所有學(xué)生追加到“信息學(xué)院學(xué)生名單”表中。4.刪除查詢【例4-16】將“信息學(xué)院學(xué)生名單”表中院系字段為“物流學(xué)院”的所有學(xué)生刪除。4.4.4重復(fù)項、不匹配項查詢1.查找重復(fù)項查詢根據(jù)“重復(fù)項查詢向?qū)А彼鶆?chuàng)建的查詢結(jié)果可以確定在表中是否有重復(fù)的記錄，或確定記錄在表中是否共享相同的值。【例4-17】使用重復(fù)項查詢向?qū)?，在“學(xué)生”表中查詢同名學(xué)生，要求顯示學(xué)生“學(xué)號”、“姓名”、“性別”和“院系”等字段。2.查找不匹配項查詢在關(guān)系數(shù)據(jù)庫中，當建立了一對多的關(guān)系后，通常在“一方”表中的每一條記錄，與“多方”表中的多條記錄相匹配。但是也有可能存在“多方”表中沒有記錄與之匹配。因此要執(zhí)行查找不匹配查詢至少需要兩個表，并且這兩個表要在同一個數(shù)據(jù)庫中?！纠?-18】利用不匹配項查詢，查找出沒有考試成績的學(xué)生名單，要求顯示學(xué)生“學(xué)號”、“姓名”、“性別”和“院系”等字段。

4.5SQL查詢4.5.1查詢與SQL視圖Access2010中的查詢是以SQL語句為基礎(chǔ)來實現(xiàn)查詢功能的，因此，Access2010中所有的查詢都可認為是SQL查詢。各種查詢操作，系統(tǒng)自動地將操作命令轉(zhuǎn)換為SQL語句。只要單擊“SQL視圖”就可以看到系統(tǒng)所生成的SQL代碼。4.5.2SQL簡介SQL語言是1974年由Boyce和Chamberlin公司提出，并首先在IBM公司研制的關(guān)系數(shù)據(jù)庫