數(shù)據(jù)庫及其應(yīng)用2120第05章

數(shù)據(jù)庫及其應(yīng)用課程代碼2120

2005年版第5章查詢“查詢”是Access數(shù)據(jù)庫的一個重要對象，是基于表的一種視圖，查詢獲得的記錄集顯示在虛擬的數(shù)據(jù)表視圖中，并不占用實(shí)際空間。使用“查詢”可以從一個或多個表中按照某種準(zhǔn)則檢索數(shù)據(jù)，在查詢的同時(shí)可以進(jìn)行計(jì)算、更新或刪除等修改數(shù)據(jù)源的操作，還能通過查詢生成一個新表，更重要的是可以把查詢的結(jié)果作為窗體、報(bào)表或數(shù)據(jù)訪問頁的數(shù)據(jù)源。5.1

查詢類型與查詢視圖

5.1.1查詢類型

5.1.2查詢視圖

5.2

選擇查詢

5.2.l使用向?qū)?chuàng)建選擇查詢

5.2.2在設(shè)計(jì)視圖中創(chuàng)建選擇查詢

5.2.3在“設(shè)計(jì)”視圖中建立查詢準(zhǔn)則

5.3

參數(shù)查詢

5.4

交叉表查詢

5.4.1什么是交叉表

5.4.2使用向?qū)?chuàng)建交叉表查詢

5.5

操作查詢

5.5.1生成表查詢

5.5.2更新查詢

5.5.3追加查詢

5.5.4刪除查詢

5.6

SQL查詢

5.6.1SQL簡介

5.6.2SQL查詢命令

5.6.3SQL查詢示例

5.6.4聯(lián)合查詢

5.6.5傳遞查詢

5.6.6數(shù)據(jù)定義查詢

5.1查詢類型與查詢視圖根據(jù)查詢方法和對查詢結(jié)果的處理不同，可以把Access中的查詢分為“選擇查詢”、“參數(shù)查詢”、“交叉表查詢”、“操作查詢”和“SQL查詢”等五種查詢。Access為查詢提供了三種視圖方式：“設(shè)計(jì)視圖”、“數(shù)據(jù)表”視圖和“SQL”查詢視圖。5.1.1查詢類型1.選擇查詢選擇查詢是最重要的查詢類型，根據(jù)某些限制條件從一個或多個表中檢索數(shù)據(jù)，并在虛擬的數(shù)據(jù)表視圖中顯示結(jié)果，在查詢的同時(shí)還可對記錄進(jìn)行分組、統(tǒng)計(jì)、計(jì)數(shù)、求平均值或其他計(jì)算。2.參數(shù)查詢參數(shù)查詢是選擇查詢的一種，不同之處是它在執(zhí)行時(shí)顯示一個對話框，用來提示用戶輸入有關(guān)參數(shù)，該參數(shù)作為查詢條件檢索數(shù)據(jù)，并將結(jié)果按指定的形式顯示出來。參數(shù)查詢也可以作為窗體、報(bào)表和數(shù)據(jù)訪問頁的基礎(chǔ)。3.交叉表查詢交叉表查詢是一種創(chuàng)建類似于Excel數(shù)據(jù)透視表的查詢，這種查詢主要用于對數(shù)據(jù)進(jìn)行分析計(jì)算，可顯示來源于表中某個數(shù)值字段的合計(jì)、計(jì)數(shù)或平均值等。4.操作查詢操作查詢是一種處理表中記錄的查詢，并且一次可以批量地處理大量的記錄。⑴刪除查詢從一個或多個表中刪除記錄。⑵更新查詢更改已經(jīng)存在數(shù)據(jù)的表。⑶追加查詢是一種向已有數(shù)據(jù)的表中添加記錄的查詢⑷生成表查詢是一種通過復(fù)制查詢結(jié)果而生成新表的查詢，生成的表獨(dú)立于數(shù)據(jù)源，如同在數(shù)據(jù)庫中創(chuàng)建的表一樣5.SQL查詢是用戶使用SQL語句創(chuàng)建的查詢。⑴聯(lián)合查詢是一種將來自一個或多個表的數(shù)據(jù)合并到一個表中的查詢。⑵傳遞查詢是使用服務(wù)器能夠接受的SQL語言直接向網(wǎng)絡(luò)數(shù)據(jù)庫服務(wù)器發(fā)送的SQL語句，通過傳遞SQL語句訪問服務(wù)器上數(shù)據(jù)庫中的表。⑶數(shù)據(jù)定義查詢是一種包含SQL數(shù)據(jù)定義語言的查詢，用于創(chuàng)建或更新數(shù)據(jù)庫中的對象。5.1.2查詢視圖“查詢”和“表”類似，Access為查詢對象提供了“設(shè)計(jì)”、“數(shù)據(jù)表”和“SQL”三種視圖方式。如下圖，見教材P116圖5-2。設(shè)計(jì)視圖用于創(chuàng)建查詢，數(shù)據(jù)表視圖用于顯示查詢結(jié)果，而SQL視圖是一個用來輸入SQL查詢語言的窗口。1.“查詢”之“設(shè)計(jì)”視圖是一個用于創(chuàng)建或修改查詢的窗口，窗口中包含有創(chuàng)建或修改查詢的各種要素。設(shè)計(jì)視圖分為上下兩部分：上面用于顯示當(dāng)前查詢的數(shù)據(jù)來源，可以是數(shù)據(jù)庫中的表或已創(chuàng)建的其他查詢；下面用來設(shè)置查詢輸出的字段、查詢準(zhǔn)則和記錄排序方式等。2.“查詢”之“數(shù)據(jù)表”視圖數(shù)據(jù)表視圖以行、列形式顯示查詢數(shù)據(jù)，用于瀏覽、添加、搜索、編輯或刪除查詢數(shù)據(jù)。3.“查詢”之SQL查詢視圖Access中的查詢是使用SQL語言實(shí)現(xiàn)的。若熟悉SQL語言，可以直接在SQL窗口中書寫SQL語句；若不熟悉SQL語言，可以在“設(shè)計(jì)”視圖中采用可視化地操作方法，只需在“設(shè)計(jì)”視圖中給出要顯示的字段、準(zhǔn)則（條件）和排序方式等，系統(tǒng)就會自動生成SQL語句，切換到SQL視圖，就可以看到等效的SQL語句。本節(jié)結(jié)束5.2

選擇查詢5.2.1使用向?qū)?chuàng)建選擇查詢使用向?qū)?chuàng)建查詢，除了能檢索出所需要的數(shù)據(jù)外，還能對查詢結(jié)果集進(jìn)行計(jì)數(shù)、求最大值、求最小值、求平均值和統(tǒng)計(jì)匯總等工作。5.2選擇查詢5.2.1使用向?qū)?chuàng)建選擇查詢1.打開數(shù)據(jù)庫2.單擊“數(shù)據(jù)庫”窗口中的“對象”，單擊“使用向?qū)?chuàng)建查詢”的快捷方式→打開或雙擊“查詢”列表中的“使用向?qū)?chuàng)建查詢”的快捷方式3.選擇數(shù)據(jù)源和字段數(shù)據(jù)源可以是表或已經(jīng)存在的另一個查詢在表/查詢選擇表，在可用字段中選擇所需的字段后，單擊>5.2

選擇查詢5.2.1使用向?qū)?chuàng)建選擇查詢4.詢問采用明細(xì)查詢還是匯總查詢明細(xì)查詢:普通的選擇查詢匯總查詢:是在普通查詢基礎(chǔ)上進(jìn)行統(tǒng)計(jì)處理5.為查詢命名6.完成5.2

選擇查詢5.2.2在設(shè)計(jì)視圖中創(chuàng)建選擇查詢使用設(shè)計(jì)視圖創(chuàng)建選擇查詢的三個要點(diǎn):一是確定查詢數(shù)據(jù)源二是指定查詢輸出字段三是設(shè)計(jì)查詢準(zhǔn)則5.2

選擇查詢5.2.2在設(shè)計(jì)視圖中創(chuàng)建選擇查詢1.打開數(shù)據(jù)庫2.單擊“數(shù)據(jù)庫”窗口中的“對象”，然后單擊“在設(shè)計(jì)視圖中創(chuàng)建查詢””的快捷方式→打開或雙擊“查詢”列表中的“在設(shè)計(jì)視圖中創(chuàng)建查詢”的快捷方式3.將表添加到查詢“設(shè)計(jì)”窗口中作為數(shù)據(jù)源，然后關(guān)閉。4.建立多表之間的關(guān)系。5.向查詢中添加字段5.2選擇查詢5.2.2在設(shè)計(jì)視圖中創(chuàng)建選擇查詢6.查詢設(shè)置排序：指定是否按該字段對結(jié)果進(jìn)行排序顯示：指定是否顯示該字段準(zhǔn)則：指定查詢條件7.切換到“數(shù)據(jù)表”視圖，查看一下結(jié)果8.保存查詢例如：根據(jù)學(xué)生、課程和成績表建立學(xué)生成績S-C-G的查詢，包括學(xué)號、姓名、課程名和各科成績。5.2選擇查詢5.2.3在“設(shè)計(jì)”視圖中建立查詢準(zhǔn)則1.表達(dá)式表達(dá)式是由運(yùn)算符、常量、函數(shù)或字段等若干部分組成的有意義的式子。⑴操作符及特殊符號算術(shù)：+、-、*、/比較：>、>=、<、<=、=、<>、between……end（兩者之間）5.2

選擇查詢5.2.3在“設(shè)計(jì)”視圖中建立查詢準(zhǔn)則1.表達(dá)式⑴操作符及特殊符號邏輯：not、and、or通配符：*任意多個字符？單個字符[]與方括號內(nèi)的任何單個字符匹配！與不在方括號內(nèi)的任何字符匹配#與任何單個數(shù)字字符匹配5.2

選擇查詢5.2.3在“設(shè)計(jì)”視圖中建立查詢準(zhǔn)則1.表達(dá)式⑴操作符及特殊符號Like前后兩個字符串是否匹配in…給定的值是否在…之中&連接兩個字符串字符串常量的定界符為雙引號日期常量的定界符為#5.2

選擇查詢5.2.3在“設(shè)計(jì)”視圖中建立查詢準(zhǔn)則1.表達(dá)式⑵使用表達(dá)式設(shè)置查詢準(zhǔn)則例如:姓名like“張*”所有姓張的數(shù)量>500出生日期=#1985-01-01#性別=“女”AND職稱=“工程師”工資>=2#00工資大于2000,2100,2200…5.2

選擇查詢5.2.3在“設(shè)計(jì)”視圖中建立查詢準(zhǔn)則2.設(shè)置查詢準(zhǔn)則示例寫在同一行的條件為邏輯“與”寫在不同行的條件為邏輯“或”5.2選擇查詢5.2.3在“設(shè)計(jì)”視圖中建立查詢準(zhǔn)則3.在查詢中建立計(jì)算字段⑴打開數(shù)據(jù)庫⑵單擊“數(shù)據(jù)庫”窗口中的“對象”，然后單擊“在設(shè)計(jì)視圖中創(chuàng)建查詢”的快捷方式→打開或雙擊“查詢”列表中的“在設(shè)計(jì)視圖中創(chuàng)建查詢”的快捷方式⑶將表添加到查詢“設(shè)計(jì)”窗口中作為數(shù)據(jù)源，然后關(guān)閉。5.2選擇查詢5.2.3在“設(shè)計(jì)”視圖中建立查詢準(zhǔn)則3.在查詢中建立計(jì)算字段⑷在第一個空字段的位置上單擊一下，單擊“工具欄”上的“生成器”按鈕⑸在表達(dá)式生成器中輸入計(jì)算字段的表達(dá)式，確定⑹將表達(dá)式重命名：直接把表達(dá)式1改為字段名例如：在成績表中，計(jì)算總成績總成績=平時(shí)*20%+期末*80%5.3參數(shù)查詢參數(shù)查詢的特點(diǎn)是：在執(zhí)行時(shí)能顯示對話框來提示用戶輸入信息。操作步驟：⑴打開數(shù)據(jù)庫⑵單擊“數(shù)據(jù)庫”窗口中的“對象”，然后單擊“在設(shè)計(jì)視圖中創(chuàng)建查詢””的快捷方式→打開或雙擊“查詢”列表中的“在設(shè)計(jì)視圖中創(chuàng)建查詢”的快捷方式⑶將表添加到查詢“設(shè)計(jì)”窗口中作為數(shù)據(jù)源，然后關(guān)閉。5.3參數(shù)查詢操作步驟：⑷在要作為參數(shù)字段下面的“準(zhǔn)則”單元格中輸入提示信息，注意：提示信息一定要用方括號[]括起來。⑸單擊工具欄上的“運(yùn)行”按鈕，出現(xiàn)“輸入?yún)?shù)值”的對話框，在對話框中輸入要查詢的值。⑹單擊確定，顯示出查詢的結(jié)果。關(guān)閉顯示對話框。⑺保存查詢。5.4

交叉表查詢5.4.1什么是交叉表查詢是按行、列形式分組安排數(shù)據(jù)：一組作為行標(biāo)題顯示在表的左部，另一組作為列標(biāo)題顯示在表的頂部，而行與列的交叉點(diǎn)的單元格則顯示數(shù)值。交叉表即可以在表的基礎(chǔ)上創(chuàng)建，也可以在另一個查詢基礎(chǔ)上創(chuàng)建。5.4

交叉表查詢5.4.1使用向?qū)?chuàng)建交叉表查詢例如：根據(jù)學(xué)生成績查詢，建立交叉表查詢S-C-G

交叉表1.打開數(shù)據(jù)庫STUDENT，選擇“查詢”對象2.單擊“數(shù)據(jù)庫”窗口工具欄的“新建”按鈕，然后在“新建查詢”的對話框中雙擊“交叉表查詢向?qū)А?.4

交叉表查詢5.4.1使用向?qū)?chuàng)建交叉表查詢例如：例如：根據(jù)學(xué)生成績查詢，建立交叉表查詢S-C-G

交叉表3.選擇查詢數(shù)據(jù)源

S-C-G

，確定字段的行標(biāo)題（學(xué)號和姓名）和列標(biāo)題（課程名）。4.確定行列交叉的單元格上顯示的數(shù)據(jù)（成績），在函數(shù)列表中選擇“平均”，。5.保存命名，完成。5.5

操作查詢操作查詢是一種處理表中記錄的查詢，并且一次可以批量地處理大量的記錄。如刪除記錄、更新記錄、追加記錄，甚至生成一個新表。特點(diǎn)：在查詢的同時(shí)，會改變原來表中的數(shù)據(jù)。5.5

操作查詢5.5.1生成表查詢1.打開數(shù)據(jù)庫STUDENT，選擇“查詢”對象，單擊“數(shù)據(jù)庫”窗口工具欄的“新建”按鈕，然后在“新建查詢”的對話框中雙擊“設(shè)計(jì)視圖”2.選擇表作為生成表的數(shù)據(jù)源，3.創(chuàng)建一個選擇查詢4.單擊“查詢”菜單的“生成表查詢”，在“表名稱”中輸入表的名稱，選中存入“當(dāng)前數(shù)據(jù)庫”，確定。5.單擊工具欄上的“運(yùn)行”按鈕，提醒用戶要創(chuàng)建一個新表，單擊“是”。5.5操作查詢5.5.2更新查詢利用更新查詢可以按指定的條件一次性地更新一批記錄，而不必逐個地去修改每一條記錄。更新查詢將改變原來的數(shù)據(jù)，注意做好備份。1.打開數(shù)據(jù)庫，選擇“查詢”對象，單擊“數(shù)據(jù)庫”窗口工具欄的“新建”按鈕，然后在“新建查詢”的對話框中雙擊“設(shè)計(jì)視圖”2.選擇表作為查詢的數(shù)據(jù)源.

55操作查詢5.5.2更新查詢3.向查詢添加字段（查詢的條件和更新的字段）4.單擊“查詢”菜單的“更新查詢”，在更新字段的“更新到”單元格中輸入更新的表達(dá)式5.單擊工具欄上的“運(yùn)行”按鈕，提醒用戶是否要更新這些記錄，單擊“是”，將進(jìn)行更新并且不能撤消這種更改。6.打開原來的表，將會看到更新的結(jié)果。5.5操作查詢5.5.3追加查詢追加查詢是一種從一個表向另一個表追加記錄的操作，特別適合從已經(jīng)存在數(shù)據(jù)的源表按照指定條件向目標(biāo)表轉(zhuǎn)移的情況。要求源表和目標(biāo)表必須具有若干個相同類型的字段。1.先創(chuàng)建一個選擇查詢，該查詢應(yīng)包含追加記錄的源表。2.在查詢“設(shè)計(jì)”視圖中，單擊工具欄上的“查詢類型“按鈕”，選擇“追加查詢”5.5

操作查詢5.5.3追加查詢3.在“表名稱”框中輸入目標(biāo)文件名4.如果該表在當(dāng)前打開的數(shù)據(jù)庫中，單擊“當(dāng)前數(shù)據(jù)庫”，否則單擊“其他數(shù)據(jù)庫”，并鍵入存放這個數(shù)據(jù)庫的路徑和名稱，確定5.查詢類型變?yōu)椤白芳硬樵儭?，兩個表如有相同的名稱的字段，Access將自動在“追加到”行中填上相同的名稱；如果沒有相同的名稱的字段，在“追加到”行中輸入所要追加到表中字段的名稱5.5

操作查詢5.5.3追加查詢6.單擊工具欄上的“視圖”，切換到數(shù)據(jù)表視圖，可以預(yù)覽，必要時(shí)，返回到“設(shè)計(jì)”視圖進(jìn)行修改。7.單擊工具欄上的“運(yùn)行”按鈕，執(zhí)行追加操作。8.打開原來的表，將會看到追加的記錄。.5.5操作查詢5.5.4刪除查詢刪除查詢主要用于批量的刪除記錄。1.在查詢“設(shè)計(jì)”視圖中，選擇表2.單擊工具欄上的“查詢類型”按鈕，選擇“刪除查詢”3.把要設(shè)置刪除準(zhǔn)則的字段拖到設(shè)計(jì)網(wǎng)格，然后設(shè)置刪除準(zhǔn)則5.5操作查詢5.5.4刪除查詢4.如果要預(yù)覽即將刪除的記錄，可切換到“數(shù)據(jù)表”視圖查看，然后返回查詢“設(shè)計(jì)”視圖，必要時(shí)可進(jìn)行修改5.如果要刪除記錄，單擊工具欄上的“運(yùn)行”按鈕，執(zhí)行刪除操作。6.打開原來的表，查看記錄。7.保存查詢嗎？是保存。5.6SQL查詢SQL查詢是用戶使用SQL語句創(chuàng)建的查詢。Access將“聯(lián)合查詢”、“傳遞查詢”和“數(shù)據(jù)定義查詢”歸結(jié)為SQL查詢。5.6.1SQL簡介SQL突出之處是查詢。能查詢滿足各種條件的記錄，還能在查詢中進(jìn)行各種計(jì)算和對查詢結(jié)果進(jìn)行分組排序等。5.6

SQL查詢5.6.2SQL查詢命令1.命令格式SELECT[*][<表名>.]<字段名1>,…FROM[<數(shù)據(jù)庫名>!]<表名>,…[INSERT/LEFT/RIGHT/FULL]JOIN<數(shù)據(jù)庫名>!<表名>[ON<聯(lián)接條件>…][WHERE<聯(lián)接條件>[AND<聯(lián)接條件>]…][GROUPBY<分組表達(dá)式>…][HAVING<篩選條件>][UNION<SELECT命令>][ORDERBY<字段>[ASC/DESC]…]5.6

SQL查詢5.6.2SQL查詢命令1.命令格式注意:[]的內(nèi)容表示任選,<>內(nèi)容是必選/表示選擇其中任一子句各子句之間至少有一個空格一個SQL語句可以占多行,除最后一行以外其他行最后都有一個分號;2.命令的功能從指定的表中檢索出滿足條件的記錄5.6

SQL查詢5.6.2SQL查詢命令3.說明SELECT子句指出查詢的輸出字段FROM子句指出查詢的表WHERE子句指出查詢的條件*:表示選擇表中的所有字段<表名>.<字段名>:指出表中的某個字段5.6

SQL查詢5.6.2SQL查詢命令3.說明LEFTJOIN<數(shù)據(jù)庫名>!<表名>稱為左聯(lián)接，其含義是除了滿足聯(lián)接條件的記錄出現(xiàn)在結(jié)果表中外，對于左表（第1個表）不滿足聯(lián)接條件的記錄也出現(xiàn)在查詢結(jié)果中，而右表（第2個表）不滿足聯(lián)接條件的記錄返回空值。5.6SQL查詢5.6.2SQL查詢命令3.說明RIGHTJOIN<數(shù)據(jù)庫名>!<表名>稱為右聯(lián)接，其含義是除了滿足聯(lián)接條件的記錄出現(xiàn)在結(jié)果表中外，對于右表不滿足聯(lián)接條件的記錄也出現(xiàn)在查詢結(jié)果中，而左表不滿足聯(lián)接條件的記錄返回空值。FULLJOIN稱為完全聯(lián)接，其含義是除了滿足聯(lián)接條件的記錄出現(xiàn)在結(jié)果表中外，左有兩表不滿足聯(lián)接條件的記錄返回空值。5.6SQL查詢5.6.2SQL查詢命令3.說明GROUPBY子句用于對查詢結(jié)果進(jìn)行分組計(jì)算，需通過HAVING<篩選條件>指明分組條件UNION<SELECT命令>子句用于將兩個SELECT語句的查詢結(jié)果進(jìn)行“并”運(yùn)算，默認(rèn)組合結(jié)果中排除重復(fù)行。ORDERBY<字段>子句用于對查詢結(jié)果按指定的字段進(jìn)行排序，ASC-升序，DESC—降序，默認(rèn)升序5.6SQL查詢5.6.3SQL查詢示例例1：SELECT*FROM學(xué)生

WHERE性別=“男”例2：SELECT課程名，學(xué)分FROM課程

WHERE學(xué)分>=8例3：SELECTSUM(學(xué)時(shí)數(shù))AS總學(xué)時(shí)

FROM課程5.6SQL查詢5.6.3SQL查詢示例例4：SELECT姓名,性別,出生日期

FROM學(xué)生

WHERE出生日期IN(SELECTMIN(出生日期)FROM學(xué)生

WHERE性別=“女”)

查詢年齡最大的女生的姓名，性別，出生日期5.6

SQL查詢5.6.3SQL查詢示例例5：SELECT姓名,性別,出生日期

FROM學(xué)生

ORDERBY性別DESC，出生日期例6：SELECT性別，COUNT（性別）

FROM學(xué)生

GROUPBY性別

統(tǒng)計(jì)男女生人數(shù)。5.6

SQL查詢5.6.4聯(lián)合查詢1.打開數(shù)據(jù)庫，選擇“查詢”對象，單擊“數(shù)據(jù)庫”窗口工具欄的“新建”按鈕2.在“新建查詢”的對話框中單擊“設(shè)計(jì)視圖”，然后單擊確定3.單擊顯示表對話框的關(guān)閉按鈕4.選擇“查詢”菜單的“SQL特定查詢”，然后單擊“聯(lián)合”命令5.輸入SQL語句(SELECT學(xué)號FROM學(xué)生WHERE性別=“男”UNIONSELECT學(xué)號FROM成績