ch05 查詢 access數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)教程第三版 幻燈片課件

Access數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)教程(第三版)

第五

章查詢學(xué)習(xí)目標(biāo)

本章主要介紹有關(guān)查詢的知識(shí)。在創(chuàng)建查詢對(duì)象時(shí)，需要用到結(jié)構(gòu)化查詢語(yǔ)言SQL的知識(shí)，因?yàn)槟壳瓣P(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)都采用SQL語(yǔ)言作為數(shù)據(jù)庫(kù)語(yǔ)言。Access作為一種關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，當(dāng)然也不例外。本章主要介紹Access支持的SQL語(yǔ)言的功能和使用方法，以及Access中查詢的創(chuàng)建與使用。教學(xué)提綱SQL語(yǔ)言查詢類型與查詢條件創(chuàng)建各種查詢查詢中的計(jì)算編輯和使用查詢5.1SQL語(yǔ)言簡(jiǎn)介

SQL語(yǔ)言是一種介于關(guān)系代數(shù)和關(guān)系演算之間的結(jié)構(gòu)化查詢語(yǔ)言，其功能并不僅僅是查詢，還具備數(shù)據(jù)定義和數(shù)據(jù)操縱等功能。SQL概述SQL數(shù)據(jù)定義功能SQL數(shù)據(jù)查詢功能SQL數(shù)據(jù)操縱功能5.1.1SQL概述

SQL，即結(jié)構(gòu)化查詢語(yǔ)言(StructuredQueryLanguage)。ANSI(美國(guó)國(guó)家標(biāo)準(zhǔn)協(xié)會(huì))規(guī)定SQL為關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)的標(biāo)準(zhǔn)語(yǔ)言。SQL語(yǔ)言的主要功能就是同各種數(shù)據(jù)庫(kù)建立聯(lián)系，進(jìn)行溝通，以達(dá)到操縱數(shù)據(jù)庫(kù)數(shù)據(jù)的目的。SQL語(yǔ)句可以用來(lái)執(zhí)行各種各樣的操作，例如，更新數(shù)據(jù)庫(kù)中的數(shù)據(jù)、從數(shù)據(jù)庫(kù)中檢索數(shù)據(jù)等。目前，絕大多數(shù)流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，如Oracle、Sybase、MicrosoftSQLServer、Access等，都采用了SQL語(yǔ)言標(biāo)準(zhǔn)。5.1.2SQL數(shù)據(jù)定義功能

SQL的數(shù)據(jù)定義功能主要體現(xiàn)在表的建立、修改和刪除操作上。MicrosoftJet數(shù)據(jù)庫(kù)引擎的SQL數(shù)據(jù)類型由13個(gè)基本數(shù)據(jù)類型組成，它們是由MicrosoftJet數(shù)據(jù)庫(kù)引擎和幾個(gè)驗(yàn)證過(guò)的有效同義字定義的。使用SQL語(yǔ)言定義數(shù)據(jù)表的一般語(yǔ)法格式如下：CREATETABLE表名(<字段1><數(shù)據(jù)類型>[列級(jí)完整性約束條件][,字段2<數(shù)據(jù)類型>[列級(jí)完整性約束條件]]……[，<表級(jí)完整性約束條件>])；5.1.2SQL數(shù)據(jù)定義功能（續(xù)）

隨著應(yīng)用環(huán)境和應(yīng)用需求的變化，有時(shí)需要修改已建立好的基本表，SQL語(yǔ)言用ALTERTABLE語(yǔ)句修改基本表，其一般格式如下：ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束>][ALTERCOLUMN<列名><數(shù)據(jù)類型>];5.1.2SQL數(shù)據(jù)定義功能（續(xù)）

使用SQL語(yǔ)句刪除數(shù)據(jù)表的一般格式如下：DROPTABLE<表名>[RESTRICT|CASCADE];DROPTABLE語(yǔ)句將刪除數(shù)據(jù)庫(kù)中一個(gè)已有表。若選擇RESTRICT，則該表的刪除是有限制條件的，即欲刪除的表不能被其他表的約束所引用，如CHECK，F(xiàn)OREIGNKEY等約束，不能有視圖，不能有觸發(fā)器，不能有存儲(chǔ)過(guò)程或函數(shù)等，如果存在這些依賴該表的對(duì)象，則此表不能被刪除；若選擇CASCADE，則該表的刪除沒(méi)有限制條件，在刪除基本表的同時(shí)，相關(guān)的依賴對(duì)象都將被一起刪除。默認(rèn)情況下是RESTRICT。5.1.3SQL數(shù)據(jù)查詢功能

數(shù)據(jù)庫(kù)查詢是數(shù)據(jù)庫(kù)的核心操作。SQL提供了SELECT語(yǔ)句進(jìn)行數(shù)據(jù)庫(kù)的查詢，該語(yǔ)句具有靈活的適用方式和豐富的功能。其一般格式如下：SELECT[ALL|DISTINCT]<目標(biāo)列表表達(dá)式>[,<目標(biāo)列表表達(dá)式>]…FROM<表名或視圖名>[,<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]];整個(gè)SELECT語(yǔ)句的含義是，根據(jù)WHERE子句的條件表達(dá)式，從FROM子句指定的基本表或視圖中找出滿足條件的元組，再按SELECT子句中的目標(biāo)列表表達(dá)式，選出元組中的屬性值形成結(jié)果表。5.1.4SQL數(shù)據(jù)操縱功能

數(shù)據(jù)操縱即數(shù)據(jù)更新，一共有3種：向表中添加若干行記錄、修改表中的數(shù)據(jù)和刪除表中的若干行數(shù)據(jù)。在SQL中有相應(yīng)的3類語(yǔ)句，即INSERT、UPDATE和DELETE語(yǔ)句。INSERTINTO表名VALUES(第一個(gè)字段值,...,最后一個(gè)字段值)UPDATE表名SET字段1=值1,…,字段N=值N,［WHERE條件表達(dá)式］DELETEFROM表名［WHERE條件表達(dá)式］5.2查詢概述

數(shù)據(jù)表創(chuàng)建好后，即可建立基于表的各種對(duì)象，最重要的對(duì)象就是查詢對(duì)象。査詢是Access數(shù)據(jù)庫(kù)的重要對(duì)象，是用戶按照一定條件從Access數(shù)據(jù)庫(kù)表或已建立的査詢中檢索需要數(shù)據(jù)的最主要方法。查詢的功能查詢與表的區(qū)別 查詢的類型查詢條件5.2.1查詢的功能

査詢最主要的目的是根據(jù)指定的條件對(duì)表或者其他查詢進(jìn)行檢索，篩選出符合條件的記錄，構(gòu)成一個(gè)新的數(shù)據(jù)集合，從而方便對(duì)數(shù)據(jù)庫(kù)表進(jìn)行查看和分析。在Access中，利用査詢可以實(shí)現(xiàn)多種功能。査詢對(duì)象不是數(shù)據(jù)的集合，而是操作的集合。査詢的運(yùn)行結(jié)果是一個(gè)數(shù)據(jù)集，也稱為動(dòng)態(tài)集。它很像一個(gè)表，但并沒(méi)有存儲(chǔ)在數(shù)據(jù)庫(kù)中。創(chuàng)建査詢后，只保存查詢的操作，只有在運(yùn)行査詢時(shí)才會(huì)從查詢數(shù)據(jù)源中抽取數(shù)據(jù)，并創(chuàng)建它；只要關(guān)閉查詢，查詢的動(dòng)態(tài)集就會(huì)自動(dòng)消失。5.2.2查詢與表的區(qū)別

查詢與表的區(qū)別主要表現(xiàn)在以下幾個(gè)方面。表是存儲(chǔ)數(shù)據(jù)的數(shù)據(jù)庫(kù)對(duì)象，而查詢則是對(duì)數(shù)據(jù)表中的數(shù)據(jù)進(jìn)行檢索、統(tǒng)計(jì)、分析、查看和更改的又一個(gè)非常重要的數(shù)據(jù)庫(kù)對(duì)象。如果說(shuō)，數(shù)據(jù)表將數(shù)據(jù)進(jìn)行了分割，那么查詢則是將不同表的數(shù)據(jù)進(jìn)行了組合，它可以從多個(gè)數(shù)據(jù)表中查找到滿足條件的記錄組成一個(gè)動(dòng)態(tài)集，以數(shù)據(jù)表視圖的方式顯示。查詢僅僅是一個(gè)臨時(shí)表，當(dāng)關(guān)閉查詢的數(shù)據(jù)視圖時(shí)，保存的是查詢的結(jié)構(gòu)。查詢所涉及的是表、字段和篩選條件等，而不是記錄。表和查詢都是查詢的數(shù)據(jù)源，查詢是窗體、報(bào)表、數(shù)據(jù)訪問(wèn)頁(yè)的數(shù)據(jù)源。建立多表查詢之前，一定要先建立數(shù)據(jù)表之間的關(guān)系。5.2.3查詢的類型

通過(guò)使用查詢，可以利用不同的方式查看、更改和分析數(shù)據(jù)，也可以用查詢作為窗體、報(bào)表和數(shù)據(jù)訪問(wèn)頁(yè)的數(shù)據(jù)源。MicrosoftAccess中的查詢類型主要包括選擇查詢、交叉表查詢、參數(shù)查詢、操作查詢和SQL查詢5種。5.2.4查詢條件

在實(shí)際應(yīng)用中，并非只是簡(jiǎn)單的査詢，往往需要指定一定的條件。例如，査找選修3門(mén)課以上的所有女同學(xué)。這種帶條件的査詢需要通過(guò)設(shè)置査詢條件來(lái)實(shí)現(xiàn)。査詢條件是運(yùn)算符、常量、字段值、函數(shù)以及字段名和屬性等的任意組合，能夠計(jì)算出一個(gè)結(jié)果。查詢條件在創(chuàng)建帶條件的査詢時(shí)經(jīng)常用到；因此，了解條件的組成，掌握它的書(shū)寫(xiě)方法非常重要。5.2.4查詢條件（續(xù)）

運(yùn)算符是構(gòu)成査詢條件的基本元素。Access提供了關(guān)系運(yùn)算符、邏輯運(yùn)算符和特殊運(yùn)算符。關(guān)系運(yùn)算符包括：>(大于)、<(小于)、<>(不等于)、>=(大于等于)、<=(小于等于)、=(等于)。利用關(guān)系運(yùn)算符可以設(shè)置字段的取值范圍。邏輯運(yùn)算符包括：And、Or和Not。用And連接多個(gè)表達(dá)式時(shí)，表示篩選滿足所有條件的記錄；用Or連接多個(gè)表達(dá)式時(shí)，表示篩選只要滿足任何一個(gè)條件的記錄；利用Not運(yùn)算符可以設(shè)置字段的不匹配值；如查詢學(xué)號(hào)不是9903260的記錄。特殊運(yùn)算符包括：IN、BETWEEN、LIKE、ISNULL、ISNOTNULL5.2.4查詢條件（續(xù)）Access提供了大量的內(nèi)置函數(shù)，也稱為標(biāo)準(zhǔn)函數(shù)或函數(shù)，如算術(shù)函數(shù)、字符函數(shù)、日期/時(shí)間函數(shù)和統(tǒng)計(jì)函數(shù)等。這些函數(shù)為更好地構(gòu)造査詢條件提供了極大的便利，也為更準(zhǔn)確地進(jìn)行統(tǒng)計(jì)計(jì)算、實(shí)現(xiàn)數(shù)據(jù)處理提供了有效的方法。5.3創(chuàng)建查詢

本節(jié)將按查詢的類型介紹創(chuàng)建各種查詢的操作方法及相關(guān)知識(shí)。創(chuàng)建選擇查詢創(chuàng)建交叉表查詢創(chuàng)建參數(shù)查詢創(chuàng)建操作查詢創(chuàng)建SQL查詢5.3.1創(chuàng)建選擇查詢?cè)贏ccess中可以有兩種方法創(chuàng)建選擇查詢：使用向?qū)Ш筒皇褂孟驅(qū)?直接在設(shè)計(jì)視圖中創(chuàng)建)創(chuàng)建查詢。選擇查詢主要用于從一個(gè)或多個(gè)表或查詢對(duì)象中指定的字段檢索數(shù)據(jù)。如果需要，向?qū)б部梢詫?duì)記錄組或全部記錄進(jìn)行總計(jì)、計(jì)數(shù)以及平均值的計(jì)算，并且可以計(jì)算字段中的最小值或最大值，但不能通過(guò)設(shè)置條件來(lái)限制檢索的記錄。(1)在數(shù)據(jù)庫(kù)窗口中，單擊窗口左邊“對(duì)象”列表中的“查詢”選項(xiàng)。(2)單擊數(shù)據(jù)庫(kù)窗口的工具欄中的“新建”按鈕，打開(kāi)“新建查詢”對(duì)話框(3)可以直接雙擊數(shù)據(jù)庫(kù)窗口右側(cè)的“使用向?qū)?chuàng)建查詢”選項(xiàng)直接打開(kāi)查詢向?qū)А?4)按照向?qū)?duì)話框的提示進(jìn)行每一步的設(shè)置。5.3.2創(chuàng)建交叉表查詢交叉表查詢是Access特有的一種查詢類型。它可以使大量的數(shù)據(jù)以更直觀的形式顯示出來(lái)，可以更方便地對(duì)數(shù)據(jù)進(jìn)行比較或分析。同時(shí)，交叉表查詢所得到的數(shù)據(jù)還可作為圖表或報(bào)表的數(shù)據(jù)來(lái)源。交叉表查詢將用于查詢的字段分成兩組，一組以行標(biāo)題的方式顯示在表格的左邊；一組以列標(biāo)題的方式顯示在表格的頂端，在行和列交叉的地方對(duì)數(shù)據(jù)進(jìn)行總合、平均、計(jì)數(shù)或者是其他類型的計(jì)算，并顯示在交叉點(diǎn)上。創(chuàng)建交叉表查詢有兩種方法：一種是使用向?qū)?chuàng)建交叉表查詢，另一種是直接在查詢的設(shè)計(jì)視圖中創(chuàng)建交叉表查詢。5.3.3創(chuàng)建參數(shù)查詢?nèi)绻枰?jīng)常運(yùn)行同一個(gè)查詢，但每次都得改變查詢條件，就顯得非常麻煩，利用參數(shù)查詢可以解決這一問(wèn)題。每當(dāng)運(yùn)行一個(gè)參數(shù)查詢時(shí)，不必去打開(kāi)查詢的設(shè)計(jì)視圖窗口對(duì)查詢條件進(jìn)行修改，只需在提示框中輸入條件參數(shù)即可。設(shè)置參數(shù)查詢時(shí)，可以在條件行中輸入以方括號(hào)[]括起來(lái)的名字和短語(yǔ)作為參數(shù)名。參數(shù)查詢可以顯示一個(gè)或多個(gè)提示參數(shù)值的預(yù)定義對(duì)話框，也可以創(chuàng)建提示查詢參數(shù)的自定義對(duì)話框。5.3.4創(chuàng)建操作查詢操作查詢是一種比較特殊的查詢，它能夠提高管理數(shù)據(jù)的質(zhì)量和效率。利用操作查詢可以在一個(gè)操作中更改多條記錄。常用的操作查詢包括刪除查詢、更新查詢、追加查詢和生成表查詢。利用刪除查詢，可以刪除表中符合條件的記錄。利用更新查詢可以更新表中符合條件的記錄。利用追加查詢可以從一個(gè)數(shù)據(jù)表中讀取記錄，追加到其他表中。追加記錄時(shí)只追加相匹配的字段，其他字段將被忽略。生成表查詢是從一個(gè)或多個(gè)表的全部或部分?jǐn)?shù)據(jù)中創(chuàng)建新的數(shù)據(jù)表。實(shí)際上，在Access數(shù)據(jù)庫(kù)系統(tǒng)中，如果需要反復(fù)使用同一個(gè)選擇查詢從幾個(gè)數(shù)據(jù)表中提取數(shù)據(jù)，最好能把這個(gè)選擇查詢提取的數(shù)據(jù)存儲(chǔ)為一個(gè)數(shù)據(jù)表，可以提高查詢的效率。5.3.5創(chuàng)建SQL查詢

SQL查詢是使用SQL語(yǔ)言創(chuàng)建的查詢。SQL查詢的類型主要有聯(lián)合查詢、傳遞查詢、數(shù)據(jù)定義查詢等。使用Access數(shù)據(jù)庫(kù)提供的“查詢向?qū)А?，可以?chuàng)建大部分類型的查詢。使用SQL查詢則可以創(chuàng)建任何類型的查詢。創(chuàng)建查詢的另一種方式是使用“按窗體篩選”，從所創(chuàng)建的篩選中創(chuàng)建查詢，可以將篩選作為查詢來(lái)保存。5.4查詢中的計(jì)算

前面介紹了創(chuàng)建查詢的一般方法，同時(shí)也使用這些方法創(chuàng)建了一些査詢，但所建査詢僅僅是為了獲取符合條件的記錄，并沒(méi)有對(duì)査詢得到的結(jié)果進(jìn)行更深入的分析和利用。而在實(shí)際應(yīng)用中，常常需要對(duì)查詢結(jié)果進(jìn)行統(tǒng)計(jì)計(jì)算，如計(jì)算學(xué)生的總成績(jī)、平均成績(jī)，某科成績(jī)的最高分和最低分等。Access允許在査詢中利用設(shè)計(jì)網(wǎng)格中的“總計(jì)”行進(jìn)行各種統(tǒng)計(jì)，通過(guò)創(chuàng)建計(jì)算字段進(jìn)行任意類型的計(jì)算。在Access査詢中，可以執(zhí)行兩種類型的計(jì)算，預(yù)定義計(jì)算和自定義計(jì)算。預(yù)定義計(jì)算自定義計(jì)算5.4.1預(yù)定義計(jì)算預(yù)定義計(jì)算用于對(duì)查詢中的分組記錄或全部記錄進(jìn)行“總計(jì)”計(jì)算。例如，求總和、平均值、計(jì)數(shù)、最小值、最大值、標(biāo)準(zhǔn)偏差或方差，Access通過(guò)聚合函數(shù)來(lái)完成這些計(jì)算。

單擊工具欄上的“總計(jì)”按鈕，可以在設(shè)計(jì)網(wǎng)格中顯示出“總計(jì)”行。對(duì)設(shè)計(jì)網(wǎng)格中的每個(gè)字段，都可在“總計(jì)”行中選擇總計(jì)項(xiàng)，來(lái)對(duì)查詢中的全部記錄、一條或多條記錄組進(jìn)行計(jì)算。

在査詢中，如果要對(duì)記錄進(jìn)行分類統(tǒng)計(jì)，可以使用分組統(tǒng)計(jì)功能。分組統(tǒng)計(jì)時(shí)，只需在“設(shè)計(jì)”視圖中將用于分組字段的“總計(jì)”行設(shè)置成“分組”即可。5.4.2自定義計(jì)算自定義計(jì)算可以用一個(gè)或多個(gè)字段的值進(jìn)行數(shù)值、日期和文本計(jì)算。例如，用某一個(gè)字段值乘上某一數(shù)值，用兩個(gè)日期時(shí)間字段的值相減等。對(duì)于自定義計(jì)算，必須直接在設(shè)計(jì)網(wǎng)格中創(chuàng)建新的計(jì)算字段，創(chuàng)建方法是將表達(dá)式輸入到設(shè)計(jì)網(wǎng)格的空字段行中，表達(dá)式可以由多個(gè)計(jì)算組成。創(chuàng)建計(jì)算字段的方法是：將表達(dá)式輸入到查詢?cè)O(shè)計(jì)視圖中的空“字段”單元格中，如果表達(dá)式包含字段名，則必須用方括號(hào)將字段名括起來(lái)。5.5編輯和使用查詢

創(chuàng)建查詢后，如果對(duì)其中的設(shè)計(jì)不滿意，或因情況發(fā)生變化，使得所建查詢不能滿足需要，可以在“設(shè)計(jì)”視圖中進(jìn)行修改。例如，添加、刪除、移動(dòng)或更改字段，添加、刪除表等。如果需要也可以對(duì)查詢進(jìn)行一些相關(guān)操作。例如，運(yùn)行査詢査看結(jié)果，依據(jù)某個(gè)字段排列查詢中的記錄等。運(yùn)行已創(chuàng)建的查詢編輯查詢中的字段編輯查詢中斷數(shù)據(jù)源5.5.1運(yùn)行已創(chuàng)建的查詢?cè)趧?chuàng)建查詢時(shí)，用戶可以通過(guò)工具欄上的“運(yùn)行”按鈕和“視圖”按鈕看到査詢結(jié)果。査詢創(chuàng)建好以后，可以通過(guò)以下兩種方法運(yùn)行查詢：在“數(shù)據(jù)庫(kù)”窗口中，單擊“査詢”對(duì)象，選中要運(yùn)行的査詢，然后單擊“打開(kāi)”按鈕圖標(biāo)。在“數(shù)據(jù)庫(kù)”窗口中，單擊“査詢”對(duì)象，然后雙擊要運(yùn)行的查詢。5.5.2編輯查詢中的字段編輯字段主要包括添加、刪除字段和移動(dòng)字段。在已創(chuàng)建的查詢中添加字段的操作步驟如下：(1)在“數(shù)據(jù)庫(kù)”窗口的“查詢”對(duì)象中，單擊要修改的査詢，然后單擊“設(shè)計(jì)”按鈕，打開(kāi)査詢的“設(shè)計(jì)”視圖。(2)雙擊要添加的字段，則該字段將添加到設(shè)計(jì)網(wǎng)格中的第1個(gè)空白列中；如果要在某一字段前插人字段，則單擊要添加的字段，并按住鼠標(biāo)左鍵不放，將其拖放到該字段的位置上；如果一次要添加多個(gè)字段，則按住Ctrl鍵并單擊要添加的字段，然后將它們拖放到設(shè)計(jì)網(wǎng)格中；如果要將某一表的所有字段添加到設(shè)計(jì)網(wǎng)格中，則雙擊該表的標(biāo)題欄，選中所有字段，然后將光標(biāo)放到字段列表中的任意一個(gè)位置，按下鼠標(biāo)左鍵拖動(dòng)鼠標(biāo)到設(shè)計(jì)網(wǎng)格中的第1個(gè)空白列中，然后釋放鼠標(biāo)左鍵。(3)單擊工具欄上的“保存”按鈕保存所做的修改。5.5.2編輯查詢中的字段（續(xù)）在已創(chuàng)建的查詢中刪除字段的操作步驟如下：(1)使用“設(shè)計(jì)”視圖，打開(kāi)要修改的查詢。(2)單擊要?jiǎng)h除字段的字段選擇器或單擊要?jiǎng)h除字段所在的列，然后選擇“編輯”|“蒯除”命令或直接按Delete鍵。(3)單擊工具欄上的“保存”按鈕，保存所做的修改。移動(dòng)字段的操作步驟如下：(1)使用查詢“設(shè)計(jì)”視圖，打開(kāi)要修改的査詢。(2)單擊要移動(dòng)字段對(duì)應(yīng)的字段選擇器，并按住鼠標(biāo)左鍵不放，拖動(dòng)鼠標(biāo)至新的位置。如果需要將移動(dòng)的字段移到某―字段的左側(cè)，則將鼠標(biāo)拖到該列，將把被移動(dòng)的字段移到光標(biāo)所在列的左側(cè)。(3)單擊工具欄上的“保存”按鈕，保存所做的修改。5.5.3編輯查詢中的數(shù)據(jù)源在已創(chuàng)建查詢的“設(shè)計(jì)”視圖窗口的上半部分，每個(gè)表或查詢的“字段列表”中，列出了可以添加到設(shè)計(jì)網(wǎng)格中的所有字段。但是，如果在列出的所有