Access數(shù)據(jù)庫實(shí)用教程第4章查詢的創(chuàng)建和使用

第4章查詢的創(chuàng)建和使用查詢概述選擇查詢的創(chuàng)建在查詢中進(jìn)行計(jì)算交叉表查詢的創(chuàng)建參數(shù)查詢的創(chuàng)建操作查詢的創(chuàng)建SQL查詢的創(chuàng)建運(yùn)行已建的查詢14.1查詢概述4.1.1查詢的功能問題提出建立數(shù)據(jù)庫的目的是為更好地管理和使用其中的數(shù)據(jù)，獲取有用信息。但數(shù)據(jù)是分散放在各個(gè)表中的，如何有效地獲取待查詢的信息例如,“張三”的考試成績？男女同學(xué)的人數(shù)？24.1查詢概述4.1.1查詢的功能查詢含義查詢對象不是數(shù)據(jù)的集合，而是操作的集合查詢的結(jié)果是一個(gè)數(shù)據(jù)集，也稱為動(dòng)態(tài)集。它很像一個(gè)表，但并沒有存儲在數(shù)據(jù)庫中創(chuàng)建查詢后，只保存查詢的操作，只有在運(yùn)行查詢時(shí)，才會從查詢數(shù)據(jù)源中抽取數(shù)據(jù)，并創(chuàng)建它；只要關(guān)閉查詢，查詢的動(dòng)態(tài)集就會自動(dòng)消失34.1查詢概述4.1.1查詢的功能查詢功能選取數(shù)據(jù)編輯數(shù)據(jù)實(shí)現(xiàn)計(jì)算建立新表為窗體和報(bào)表提供數(shù)據(jù)44.1查詢概述4.1.2查詢的類型選擇查詢根據(jù)指定的查詢條件，從一個(gè)或多個(gè)數(shù)據(jù)源中獲取數(shù)據(jù)并顯示結(jié)果;也可以利用查詢條件對記錄進(jìn)行分組、并進(jìn)行求和、、計(jì)數(shù)、平均值等運(yùn)算。Access選擇查詢主要有簡單選擇查詢、統(tǒng)計(jì)查詢、重復(fù)項(xiàng)查詢、不匹配項(xiàng)查詢等幾種類型。54.1查詢概述4.1.2查詢的類型交叉表查詢將來源于某個(gè)數(shù)據(jù)源中的字段進(jìn)行分組，一組列在數(shù)據(jù)表的左側(cè)，一組列在數(shù)據(jù)表的上部，然后在行與列的交叉處顯示某字段的統(tǒng)計(jì)值，如合計(jì)、統(tǒng)計(jì)個(gè)數(shù)、求最大值和最小值等。64.1查詢概述4.1.2查詢的類型參數(shù)查詢利用對話框來提示用戶輸入查詢條件，并根據(jù)輸入的條件來檢索記錄。操作查詢四種：生成表、刪除、更新和追加74.1查詢概述4.1.2查詢的類型SQL查詢是使用SQL語句創(chuàng)建的查詢SQL查詢中某些SQL查詢稱為SQL特定查詢，包括四種：聯(lián)合查詢、傳遞查詢、數(shù)據(jù)定義查詢和子查詢84.2選擇查詢的創(chuàng)建4.2.1使用查詢向?qū)Р僮髡呖稍谙驅(qū)е甘鞠逻x擇一個(gè)或多個(gè)數(shù)據(jù)源、一個(gè)或多個(gè)字段創(chuàng)建查詢使用向?qū)Р荒軇?chuàng)建帶條件的查詢94.2選擇查詢的創(chuàng)建4.2.1使用查詢向?qū)褂谩昂唵尾樵兿驅(qū)А?/p>

例4-1查找“教師”表中的記錄，并顯示“姓名”、“性別”、“工作時(shí)間”和“系別”等字段信息所建查詢數(shù)據(jù)源來自于一個(gè)表例4-2查找每名學(xué)生選課成績，并顯示“學(xué)生編號”、“姓名”、“課程名稱”和“考試成績”等字段。查詢名為“2學(xué)生選課成績”所建查詢數(shù)據(jù)源來自于多個(gè)表104.2選擇查詢的創(chuàng)建4.2.1使用查詢向?qū)褂谩安檎也黄ヅ漤?xiàng)查詢向?qū)А崩?-3判斷“學(xué)生”表中是否有重名學(xué)生，如果有顯示“姓名”、“學(xué)生編號”、“性別”和“入校日期”，查詢名為“3學(xué)生重名查詢”

根據(jù)“查找重復(fù)項(xiàng)查詢向?qū)А眲?chuàng)建的查詢結(jié)果，可以確定“學(xué)生”表中的“姓名”字段是否存在相同的值。114.2選擇查詢的創(chuàng)建4.2.1使用查詢向?qū)褂谩安檎抑貜?fù)項(xiàng)查詢向?qū)А崩?-4查找哪些課程沒有學(xué)生選修，并顯示“課程編號”和“課程名稱”，保存為“4沒有學(xué)生選修的課程查詢”。124.2選擇查詢的創(chuàng)建4.2.2使用設(shè)計(jì)視圖

雖然查詢向?qū)Э梢钥焖?、方便地?chuàng)建查詢，但對于有條件的查詢需要使用查詢設(shè)計(jì)視圖完成。查詢設(shè)計(jì)視圖組成134.2選擇查詢的創(chuàng)建4.2.2使用設(shè)計(jì)視圖步驟添加數(shù)據(jù)源—表或查詢選擇查詢內(nèi)容—字段填寫查詢條件（可選）設(shè)置排序（可選）144.2選擇查詢的創(chuàng)建4.2.2使用設(shè)計(jì)視圖創(chuàng)建不帶條件的查詢例4-5使用設(shè)計(jì)視圖創(chuàng)建查詢，查找并顯示授課教師的“系別”、“姓名”、“課程名稱”和“學(xué)分”，要求按系別從大到小順序顯示.保存“5授課教師查詢”.分析：查詢用到“系別”、“姓名”、“課程名稱”和“學(xué)分”等字段分別來自“教師”和“課程”兩個(gè)表，但兩表間沒有直接關(guān)系，需通過“授課”表建立兩表間關(guān)系。故應(yīng)創(chuàng)建基于“教師”、“課程”和“授課”3個(gè)表的查詢。154.2選擇查詢的創(chuàng)建4.2.2使用設(shè)計(jì)視圖創(chuàng)建帶條件的查詢例4-6查找1992年參加工作的男教師，并顯示“姓名”、“性別”、“學(xué)歷”、“職稱”、“系別”和“電話號碼”。保存為“61992年參加工作的男教師”。分析：要?jiǎng)?chuàng)建上述查詢，需兩個(gè)條件，一是性別值為“男”，二是工作時(shí)間值為1992，且兩個(gè)字段值均應(yīng)等于條件給定的值。故兩條件是“與”關(guān)系164.2選擇查詢的創(chuàng)建4.2.2使用設(shè)計(jì)視圖創(chuàng)建帶條件的查詢例4-6查找1992年參加工作的男教師，并顯示“姓名”、“性別”、“學(xué)歷”、“職稱”、“系別”和“電話號碼”17例查找年齡小于19歲男生，或大于21歲女生，并顯示“姓名”、“性別”、“年齡”4.2選擇查詢的創(chuàng)建4.2.3查詢條件查詢條件是指在查詢中用于限制檢索記錄的條件表達(dá)式，由運(yùn)算符、常量、字段值、函數(shù)和字段名組成運(yùn)算符：是構(gòu)成條件表達(dá)式的基本元素。Access提供了四種運(yùn)算符，分別是算術(shù)運(yùn)算符、關(guān)系運(yùn)算符、邏輯運(yùn)算符和特殊運(yùn)算符184.2選擇查詢的創(chuàng)建4.2.3查詢條件運(yùn)算符算術(shù)運(yùn)算符：+-*/比較運(yùn)算符：>=<<=<>邏輯運(yùn)算符：andornot其他：Between…and…，like，in，IsNull，IsNotNull194.2選擇查詢的創(chuàng)建4.2.3查詢條件函數(shù)數(shù)值函數(shù)：Abs，Int，Sqr，……字符函數(shù)：Left，Right，Len，…………204.2選擇查詢的創(chuàng)建4.2.3查詢條件設(shè)置查詢條件時(shí)注意日期型數(shù)據(jù)兩邊加“#”，字符型數(shù)據(jù)兩邊加半角雙引號在條件中字段名必須用方括號括起來兩個(gè)以上條件時(shí)，同行相與，異行相或條件中引用表名時(shí)，應(yīng)用方括號括起來，與字段名之間用“！”。如[教師檔案表]![姓名]21設(shè)置查詢條件例如：性別為“男”的教師22設(shè)置查詢條件例如：高級職稱的教師23設(shè)置查詢條件例如：高級職稱的“女”教師24設(shè)置查詢條件例如：05級學(xué)生的成績假定學(xué)號的前四位為年級25設(shè)置查詢條件例如：沒有聯(lián)系電話的教師26設(shè)置查詢條件例如：姓“李”的學(xué)生27運(yùn)行已建查詢的方法方法1：在導(dǎo)航窗格中，右鍵單擊要運(yùn)行的查詢，然后從彈出的快捷菜單中選擇“打開”命令。方法2：在導(dǎo)航窗格中，直接雙擊要運(yùn)行的查詢。

284.3在查詢中進(jìn)行計(jì)算4.3.1查詢中的計(jì)算功能預(yù)定義計(jì)算是系統(tǒng)提供的用于對查詢中的記錄組或全部記錄進(jìn)行的計(jì)算包括：合計(jì)、平均值、最小值、最大值、計(jì)數(shù)等自定義計(jì)算用一個(gè)或多個(gè)字段進(jìn)行數(shù)值、日期和文本計(jì)算需要在查詢設(shè)計(jì)網(wǎng)格中直接添加字段294.3在查詢中進(jìn)行計(jì)算4.3.2總計(jì)查詢例4-8統(tǒng)計(jì)教師人數(shù)。304.3在查詢中進(jìn)行計(jì)算4.3.2總計(jì)查詢例4-9統(tǒng)計(jì)1992年參加工作的教師人數(shù)。314.3在查詢中進(jìn)行計(jì)算4.3.2總計(jì)查詢例4-10在“1992年參加工作人數(shù)統(tǒng)計(jì)”查詢中，將以“教師編號”字段統(tǒng)計(jì)的結(jié)果顯示標(biāo)題改為“教師人數(shù)”。324.3在查詢中進(jìn)行計(jì)算4.3.3分組總計(jì)計(jì)算將記錄進(jìn)行分組，對每個(gè)組的值進(jìn)行統(tǒng)計(jì)分組統(tǒng)計(jì)時(shí)，只要在該字段的“總計(jì)”行上選擇“分組”就可以了例4-11計(jì)算各類職稱的教師人數(shù)，并顯示“職稱”和“人數(shù)”。334.3在查詢中進(jìn)行計(jì)算4.3.4計(jì)算字段統(tǒng)計(jì)的數(shù)據(jù)不在表中，或者用于計(jì)算的數(shù)據(jù)值來源于多個(gè)字段時(shí)例4-12計(jì)算每名教師的工齡，并顯示“姓名”、“系別”、“職稱”和“工齡”。按照題目要求，需將“工齡”設(shè)置為計(jì)算字段，其值可根據(jù)系統(tǒng)當(dāng)前日期和工作時(shí)間計(jì)算得出。計(jì)算表達(dá)式為：Year(Date())-Year([工作時(shí)間])。344.3在查詢中進(jìn)行計(jì)算4.3.4計(jì)算字段統(tǒng)計(jì)的數(shù)據(jù)不在表中，或者用于計(jì)算的數(shù)據(jù)值來源于多個(gè)字段時(shí)例4-12計(jì)算每名教師的工齡，并顯示“姓名”、“系別”、“職稱”和“工齡”。354.4交叉表查詢的創(chuàng)建4.4.1交叉表查詢的概念所謂交叉表查詢，就是將來源于某個(gè)表中的字段進(jìn)行分組，一組列在數(shù)據(jù)表的左側(cè)，一組列在數(shù)據(jù)表的上部，然后在數(shù)據(jù)表行與列的交叉處顯示表中某個(gè)字段的各種計(jì)算值與Excel中的數(shù)據(jù)透視表一樣364.4交叉表查詢的創(chuàng)建4.4.2使用查詢向?qū)Ю?-14創(chuàng)建一個(gè)交叉表查詢，統(tǒng)計(jì)每班男女生人數(shù)。注意：若所建交叉表查詢涉及多張表,使用向?qū)r(shí)，應(yīng)先建立多表查詢，再建立交叉表查詢374.4交叉表查詢的創(chuàng)建4.4.3使用設(shè)計(jì)視圖例4-15創(chuàng)建一個(gè)交叉表查詢，使其顯示各班每門課程的總評成績的平均分。384.5參數(shù)查詢的創(chuàng)建參數(shù)查詢的含義利用對話框，提示用戶輸入?yún)?shù)，并檢索符合所輸參數(shù)的記錄或值可以建立一個(gè)參數(shù)的查詢，也可以建立多個(gè)參數(shù)的查詢

394.5參數(shù)查詢的創(chuàng)建4.5.1單參數(shù)查詢創(chuàng)建單參數(shù)查詢，即指定一個(gè)參數(shù)。在執(zhí)行單參數(shù)查詢時(shí)，輸入一個(gè)參數(shù)值

例4-16按學(xué)生姓名查找某學(xué)生成績，并顯示“學(xué)生編號”、“姓名”、“課程名稱”及“考試成績”等。40在“姓名”字段的“條件”行單元格中輸入：[請輸入學(xué)生姓名：]4.5參數(shù)查詢的創(chuàng)建4.5.2多參數(shù)查詢創(chuàng)建多參數(shù)查詢，即指定多個(gè)參數(shù)。在執(zhí)行多參數(shù)查詢時(shí)，需依次輸入多個(gè)參數(shù)值

例4-17建立一個(gè)查詢，使其顯示某門課程某成績范圍內(nèi)的學(xué)生“姓名”、“課程名稱”和“總評成績”。414.5參數(shù)查詢的創(chuàng)建在參數(shù)查詢中，如果要輸入的表達(dá)式比較長，可在“條件”單元格右鍵點(diǎn)擊，在彈出快捷方式中選擇“顯示比例”命令，彈出“縮放”對話框，輸入表達(dá)式。42參數(shù)查詢提供了一種靈活的交互式查詢，但實(shí)際數(shù)據(jù)庫開發(fā)宏，輸入的參數(shù)的常常在一個(gè)確定的數(shù)據(jù)集合中。所以常從數(shù)據(jù)集合選擇參數(shù)的參數(shù)查詢常結(jié)合窗體使用。后面窗體時(shí)會介紹。4.6操作查詢的創(chuàng)建操作查詢的含義操作查詢是指僅在一個(gè)操作中更改許多記錄的查詢例如，在一個(gè)操作中刪除一組記錄，更新一組記錄等等434.6操作查詢的創(chuàng)建操作查詢的類型生成表查詢:利用一個(gè)或多個(gè)表中的數(shù)據(jù)創(chuàng)建一個(gè)新表刪除查詢:從一個(gè)或多個(gè)表中刪除指定的數(shù)據(jù)更新查詢:對一個(gè)或多個(gè)表中的記錄進(jìn)行更新追加查詢:從一個(gè)或多個(gè)表中將一組記錄追加到另一個(gè)或多個(gè)表中444.6操作查詢的創(chuàng)建注意：操作查詢執(zhí)行后，不能撤消剛剛做過的更改操作454.6操作查詢的創(chuàng)建4.6.1生成表查詢生成表查詢利用一個(gè)或多個(gè)表中的全部或部分?jǐn)?shù)據(jù)創(chuàng)建新表。利用生成表查詢建立新表時(shí)，如果數(shù)據(jù)庫中已有同名的表，則新表將覆蓋該同名的表。注意，利用生成表查詢建立新表時(shí)，新表中的字段從生成表查詢的源表中繼承字段名稱、數(shù)據(jù)類型以及“字段大小”屬性。但是不繼承其它的字段屬性以及表的主鍵。464.6操作查詢的創(chuàng)建4.6.1生成表查詢例4-18將總評成績在90分以上的學(xué)生信息存儲到一個(gè)新表中，表名為“90分以上學(xué)生情況”，表內(nèi)容為“學(xué)生編號”、“姓名”、“性別”、“年齡”和“考試成績”等字段。474.6操作查詢的創(chuàng)建4.6.2刪除查詢

刪除查詢可以從一個(gè)或多個(gè)表中刪除一組記錄。使用刪除查詢，將刪除整個(gè)記錄，而不是只刪除記錄中所選的字段。如果啟用級聯(lián)刪除，則可以用刪除查詢從單個(gè)表中、從一對一關(guān)系的多個(gè)表中，或一對多關(guān)系中的多個(gè)表刪除相關(guān)的記錄。

484.6操作查詢的創(chuàng)建4.6.2刪除查詢例4-19將選課成績表中總評成績小于60分的記錄刪除。494.6操作查詢的創(chuàng)建4.6.3更新查詢更新查詢可以對表中的部分記錄或全部記錄作更改。更新查詢用在一次更新一批數(shù)據(jù)的操作中非常方便。

504.6操作查詢的創(chuàng)建4.6.3更新查詢例20將所有1988年及以前參加工作的教師職稱改為副教授。514.6操作查詢的創(chuàng)建4.6.4追加查詢

追加查詢是將一個(gè)或多個(gè)表中的一組記錄添加到另一個(gè)已存在的表的末尾。

要被追加記錄的表必須是已經(jīng)存在的表。這個(gè)表可以是當(dāng)前數(shù)據(jù)庫中的表，也可以是另外一個(gè)數(shù)據(jù)庫中的表。524.6操作查詢的創(chuàng)建4.6.4追加查詢例4-20建立一個(gè)追加查詢將考試成績在80～90分之間的學(xué)生成績添加到已建立的“90分以上學(xué)生情況”表中。534.7SQL查詢的創(chuàng)建54SQL（StructureQueryLanguage）的中文名稱為結(jié)構(gòu)化查詢語言。SQL是一種專門針對數(shù)據(jù)庫操作的計(jì)算機(jī)語言。SQL查詢是使用SQL語句創(chuàng)建的查詢。在Access數(shù)據(jù)庫中，查詢對象本質(zhì)上是一個(gè)SQL語言編寫的命令。當(dāng)使用查詢的“設(shè)計(jì)視圖”用可視化的方式創(chuàng)建一個(gè)查詢對象后，系統(tǒng)便自動(dòng)把它轉(zhuǎn)換為相應(yīng)的SQL語句保存起來。運(yùn)行一個(gè)查詢對象實(shí)質(zhì)上就是執(zhí)行該查詢中指定的SQL命令。4.7SQL查詢的創(chuàng)建4.7.1顯示SQL語句在Access中，任何一個(gè)查詢都對應(yīng)著一條SQL語句。在創(chuàng)建查詢時(shí)，系統(tǒng)會自動(dòng)地將操作命令轉(zhuǎn)換為SQL語句，只要打開查詢，切換到SQL視圖，就可以看到系統(tǒng)生成的SQL語句。554.7SQL查詢的創(chuàng)建4.7.2常用SQL語句SQL語句查詢：SELECT操縱：INSERT、UPDATE、DELETE定義：CREATE、DROP、ALTER控制：GRANT、REVOTE564.7SQL查詢的創(chuàng)建4.7.2常用SQL語句INSERT語句格式：INSERTINTO<表名>[(<字段名1>[,<字段名2>…])]VALUES(<常量1>)[,<常量2>]…);574.7SQL查詢的創(chuàng)建4.7.2常用SQL語句INSERT語句例4-22在“授課”表中插入一條新記錄(8,105,96011)。INSERTINTO授課VALUES(8,"105","96011");584.7SQL查詢的創(chuàng)建4.7.2常用SQL語句UPDATE語句格式：UPDATE<表名>SET<字段名1>=<表達(dá)式1>[,<字段名2>=<表達(dá)式2>]…[WHERE<條件>];594.7SQL查詢的創(chuàng)建4.7.2常用SQL語句UPDATE語句例4-23將“教師”表中“教師編號”為“96011”教師的工作時(shí)間改為：1992-2-26。

UPDATE教師SET工作時(shí)間=#1992-2-26#WHERE教師編號="96011";604.7SQL查詢的創(chuàng)建4.7.2常用SQL語句DELETE語句格式：DELETEFROM<表名>[WHERE<條件>];614.7SQL查詢的創(chuàng)建4.7.2常用SQL語句UPDATE語句例4-24將“教師”表中教師編號為“98014”的記錄刪除。DELETE*FROM教師WHERE教師編號="98014";624.7SQL查詢的創(chuàng)建4.7.2常用SQL語句SELECT語句格式：SELECT[ALL|DISTINCT|TOPn]*|[<[字段列表>]…FROM<表名1>[,<表名2>]…[WHERE<條件表達(dá)式>][GROUPBY<字段名1>[HAVING<條件表達(dá)式>]][ORDERBY<字段名2>[ASC|DESC]];634.7SQL查詢的創(chuàng)建4.7.2常用SQL語句SELECT語句例4-25查找并顯示“教師”表中“姓名”、“性別”、“工作時(shí)間”和“系別”4個(gè)字段。

SELECT姓名,性別,工作時(shí)間,系別FROM教師;644.7SQL查詢的創(chuàng)建4.7.2常用SQL語句SELECT語句例4-26查找1992年參加工作的男教師，并顯示“姓名”、“性別”、“學(xué)歷”、“職稱”、“系別”和“聯(lián)系電話”。SELECT姓名,性別,學(xué)歷,職稱,系別,電話號碼FROM教師WHERE性別="男"ANDYEAR([工作時(shí)間])=1992;654.7SQL查詢的創(chuàng)建4.7.2常用SQL語句SELECT語句例4-27查找具有高級職稱的教師，并顯示“姓名”和“職稱”。SELECT姓名,職稱FROM教師WHERE職稱IN("教授","副教授");664.7SQL查詢的創(chuàng)建4.7.2常用SQL語句SELECT語句例4-28僅顯示年齡排在前5位的學(xué)生“姓名”和“年齡”。SELECTTOP5姓名,年齡FROM學(xué)生ORDERBY年齡DESC;674.7SQL查詢的創(chuàng)建4.7.2常用SQL語句SELECT語句例4-29計(jì)算各類職稱的教師人數(shù)，顯示字段名為“各類職稱人數(shù)”。SELECTCount(教師編號)AS各類職稱人數(shù)FROM教師GROUPBY職稱;684.7SQL查詢的創(chuàng)建4.7.3SQL特定查詢的創(chuàng)建對于數(shù)據(jù)定義查詢、傳遞查詢、聯(lián)合查詢，不能在設(shè)計(jì)網(wǎng)格中創(chuàng)建，必須直接在“SQL視圖”中創(chuàng)建SQL語句。1聯(lián)合查詢將兩個(gè)或更多個(gè)表或查詢中的字段合并到查詢結(jié)果的一個(gè)字段中。使用聯(lián)合查詢可以合并兩個(gè)表中的數(shù)據(jù)。694.7SQL查詢的創(chuàng)建4.7.3SQL特定查詢的創(chuàng)建聯(lián)合查詢格式：SELECTFROM<表名1>[,<表名2>]…[WHERE<條件表達(dá)式1>]UNION[ALL]SELECTFROM<表名a>[,<表名b>]…[WHERE<條件表達(dá)式2>];704.7SQL查詢的創(chuàng)建4.7.3SQL特定查詢的創(chuàng)建聯(lián)合查詢例4-30顯示“90分以上學(xué)生情況”表中所有記錄和“學(xué)生選課成績”查詢中70分以下的記錄，顯示內(nèi)容為“學(xué)生編號”、“姓名”、“總評成績”等3個(gè)字段。SELECT學(xué)生編號,姓名,考試成績FROM學(xué)生選課成績WHERE考試成績<70UNIONSELECT學(xué)生編號,姓名,考試成績FROM90分以上學(xué)生情況;714.7SQL查詢的創(chuàng)建4.7.3SQL特定查詢的創(chuàng)建2數(shù)據(jù)定義查詢可以創(chuàng)建、刪除或修改表，也可以在數(shù)據(jù)庫表中創(chuàng)建索引。

CREATE語句

格式：CR