Access數(shù)據(jù)庫技術(shù)與應(yīng)用教程課件第4章 查詢

第四章查詢4.1查詢的基本知識(shí)查詢的原理通過某種規(guī)則，從數(shù)據(jù)庫中的一張或多張工作表中建立這樣的動(dòng)態(tài)記錄集查詢的功能（1）選擇字段（2）選擇記錄（3）排序記錄（4）實(shí)現(xiàn)計(jì)算（5）建立表（6）建立基于查詢的報(bào)表和窗體（7）建立基于查詢的圖表（8）建立輔助查詢（9）實(shí)現(xiàn)數(shù)據(jù)的修改查詢的分類（1）選擇查詢（2）交叉表查詢（3）操作查詢（4）SQL查詢（5）參數(shù)查詢4.2查詢準(zhǔn)則查詢準(zhǔn)則的含義查詢準(zhǔn)則是指在設(shè)計(jì)查詢的過程中所定義的查詢條件，它通常是運(yùn)算符、常量、函數(shù)以及字段名稱和屬性等的組合4.2.1運(yùn)算符1.算術(shù)運(yùn)算符（7種，注意優(yōu)先級(jí)）^、*、/、\、Mod、+、-2.連接運(yùn)算符（2種，注意連接的是字符串）+、&4.2查詢準(zhǔn)則3.關(guān)系（比較）運(yùn)算符（4種）Is、In、Between…And…、Like4.邏輯運(yùn)算符（3種）Not、And、Or5.標(biāo)識(shí)運(yùn)算符（2種）！、.4.2.2函數(shù)Access中的函數(shù)是一個(gè)預(yù)先編制好的程序模塊常用的標(biāo)準(zhǔn)函數(shù)（5類）數(shù)學(xué)函數(shù)、字符串函數(shù)、日期/時(shí)間函數(shù)、統(tǒng)計(jì)函數(shù)、其他函數(shù)4.2查詢準(zhǔn)則4.2.3查詢準(zhǔn)則建立需注意的問題不同類型數(shù)據(jù)的標(biāo)識(shí)符號(hào)（1）日期型數(shù)據(jù)必須用符號(hào)“#”括起來；（2）文本型數(shù)據(jù)必須用半角的雙引號(hào)括起來；（3）字段名稱必須用一對(duì)方括號(hào)“[]”括起來。運(yùn)算符的優(yōu)先級(jí)優(yōu)先級(jí)高的運(yùn)算先進(jìn)行，優(yōu)先級(jí)相同的運(yùn)算依照從左向右的順序進(jìn)行

詳見表4-54.2查詢準(zhǔn)則4.2.4本書的查詢數(shù)據(jù)表本章所有的例題都使用一個(gè)關(guān)于學(xué)生成績的數(shù)據(jù)庫該數(shù)據(jù)庫由tStud、tCourse、tScore三張數(shù)據(jù)表組成（見表4-6、表4-7、表4-8）注意它們的內(nèi)在聯(lián)系（見圖4-2）4.3選擇查詢選擇查詢是按照查詢規(guī)則從一個(gè)或者多個(gè)表或其他查詢中檢索數(shù)據(jù)，并按照所需的排列順序顯示數(shù)據(jù)，主要用于瀏覽、檢索、統(tǒng)計(jì)數(shù)據(jù)庫的數(shù)據(jù)。

創(chuàng)建方式（4種）使用查詢向?qū)?chuàng)建選擇查詢使用設(shè)計(jì)視圖創(chuàng)建選擇查詢創(chuàng)建多表選擇查詢創(chuàng)建匯總查詢4.3選擇查詢4.3.1使用查詢向?qū)?chuàng)建【例4-6】用查詢向?qū)?chuàng)建一個(gè)選擇查詢，查找所學(xué)生的“學(xué)號(hào)”、“姓名”和“所屬院系”三個(gè)字段內(nèi)容，所建查詢命名為“qT1”。操作步驟：（1）導(dǎo)引（2）選擇查詢字段（3）指定查詢標(biāo)題4.3選擇查詢4.3.2使用設(shè)計(jì)視圖創(chuàng)建選擇查詢【例4-7】用設(shè)計(jì)視圖創(chuàng)建一個(gè)選擇查詢，查找年齡大于25的學(xué)生的“學(xué)號(hào)”、“姓名”和“所屬院系”三個(gè)字段內(nèi)容，所建查詢命名為“qT2”。操作步驟：（1）添加數(shù)據(jù)表（2）添加查詢字段（3）設(shè)置查詢條件關(guān)鍵點(diǎn)：將“年齡”字段的“顯示”選項(xiàng)去掉并在該字段的條件框中輸入“>25”；（4）保存并顯示查詢4.3選擇查詢4.3.3創(chuàng)建多表選擇查詢【例4-8】用設(shè)計(jì)視圖創(chuàng)建一個(gè)選擇查詢，查找女學(xué)生的“學(xué)號(hào)”、“課程名”和“成績”三個(gè)字段內(nèi)容，所建查詢命名為“qT3”。操作步驟：（1）加入數(shù)據(jù)表（2）添加查詢字段（3）設(shè)置查詢條件關(guān)鍵點(diǎn)：將“性別”字段的“顯示”選項(xiàng)去掉，并且在該字段的條件框中輸入“女”（注意，要使用雙引號(hào)將“女”字標(biāo)引）。

（4）保存并顯示查詢4.3選擇查詢4.3.4創(chuàng)建匯總查詢【例4-9】用設(shè)計(jì)視圖創(chuàng)建一個(gè)查詢，計(jì)算每名學(xué)生所選課程的學(xué)分總和，并依次顯示“姓名”和“學(xué)分”，其中“學(xué)分”為計(jì)算出的學(xué)分總和，查詢結(jié)果按照學(xué)分總和降序排列，所建查詢名為“qT4”。操作步驟：（1）加入數(shù)據(jù)表（2）添加查詢字段（3）計(jì)算關(guān)鍵點(diǎn)：在第2個(gè)“字段”中輸入“學(xué)分:學(xué)分”，點(diǎn)擊工具欄上的總計(jì)按鈕，在出現(xiàn)“總計(jì)”選項(xiàng)框中，對(duì)“姓名”字段下的“總計(jì)”選項(xiàng)設(shè)置為“分組”，而對(duì)“學(xué)分:學(xué)分”字段下的“總計(jì)”選項(xiàng)設(shè)置為“總計(jì)”（4）設(shè)置排序選項(xiàng)（5）保存并顯示查詢4.4交叉表查詢交叉表查詢就是由用戶建立起來的二維總計(jì)矩陣。使用交叉表查詢可以計(jì)算并重新組織數(shù)據(jù)的結(jié)構(gòu)，這樣有利于數(shù)據(jù)分析和比較。

創(chuàng)建一個(gè)交叉表查詢的三要素行標(biāo)題列標(biāo)題值值是用戶選擇在交叉表中顯示的數(shù)據(jù)。是通過Sum、Avg、Max、Min和Count等總計(jì)函數(shù)或某種表達(dá)式計(jì)算得到的值。

4.4交叉表查詢【例4-10】以表對(duì)象“tScore”和“tCourse”為基礎(chǔ)，創(chuàng)建一個(gè)交叉表查詢。要求：選擇學(xué)生的“學(xué)號(hào)”為行標(biāo)題、“課程號(hào)”為列標(biāo)題來統(tǒng)計(jì)輸出學(xué)分小于3分的學(xué)生平均成績，所建查詢命名為“qT5”。

操作步驟：加入數(shù)據(jù)表選擇交叉表查詢命令設(shè)定查詢?cè)O(shè)置項(xiàng)關(guān)鍵點(diǎn)1：“行標(biāo)題”選擇“課程號(hào)”字段；“列標(biāo)題”選擇“成績”字段；“值”在其“總計(jì)”設(shè)置項(xiàng)中選擇“平均值”；關(guān)鍵點(diǎn)2：選擇“學(xué)分”字段，并在其“條件”設(shè)置項(xiàng)中輸入“<3”，在其“總計(jì)”設(shè)置項(xiàng)中選擇“條件”；保存并顯示查詢4.5參數(shù)查詢參數(shù)查詢是指查詢?cè)趫?zhí)行時(shí)顯示一個(gè)對(duì)話框以提示用戶輸人查詢的信息，常用于用戶對(duì)某個(gè)字段進(jìn)行反復(fù)查詢，而且在每次查詢時(shí)都需要更改查詢的具體內(nèi)容的情形。分類（2類）單參數(shù)查詢?cè)谧侄沃兄付ㄒ粋€(gè)參數(shù)，在執(zhí)行查詢時(shí)，用戶只需要輸入一個(gè)參數(shù)值。多參數(shù)查詢

在字段中指定多個(gè)參數(shù)，在執(zhí)行多參數(shù)查詢時(shí)，用戶依次輸入多個(gè)參數(shù)值。4.5參數(shù)查詢4.5.1單參數(shù)查詢【例4-11】以表“tStud”為數(shù)據(jù)源創(chuàng)建一個(gè)參數(shù)查詢，當(dāng)運(yùn)行該查詢時(shí)，提示框中應(yīng)顯示“請(qǐng)輸入愛好”，輸入愛好后，在簡(jiǎn)歷字段中查找具有指定愛好的學(xué)生，顯示“學(xué)號(hào)”、“姓名”、“性別”、“年齡”和“簡(jiǎn)歷”字段內(nèi)容，所建查詢命名為“qT6”。操作步驟：（1）加入數(shù)據(jù)表（2）添加查詢字段（3）設(shè)定參數(shù)查詢項(xiàng)目關(guān)鍵點(diǎn)：“在“簡(jiǎn)歷”字段的“條件”行中，輸入“Like"*"+[請(qǐng)輸入愛好]+"*"”

（4）保存并顯示查詢4.5參數(shù)查詢4.5.2多參數(shù)查詢【例4-12】以表“tStud”為數(shù)據(jù)源創(chuàng)建一個(gè)多參數(shù)查詢，只查詢年齡在一定范圍的學(xué)生信息，顯示“學(xué)號(hào)”、“姓名”、“性別”、“年齡”和“所屬院系”字段內(nèi)容，所建查詢命名為“qT7”。

操作步驟：（1）加入數(shù)據(jù)表（2）添加查詢字段（3）設(shè)定參數(shù)查詢項(xiàng)目關(guān)鍵點(diǎn)在“年齡”字段的“條件”行中輸入“Between[年齡上限為：]And[年齡下限為：]”

（4）保存并顯示查詢4.6操作查詢操作數(shù)據(jù)庫的過程中，有時(shí)需要將表中的一部分?jǐn)?shù)據(jù)復(fù)制到一個(gè)行新表中，或需要對(duì)表中的數(shù)據(jù)進(jìn)行更新、刪除或是追加操作，這就需要使用到操作查詢

分類（4類）生成表查詢根據(jù)一個(gè)或者多個(gè)表中的全部或者部分?jǐn)?shù)據(jù)來新建數(shù)據(jù)表追加查詢將一個(gè)或多個(gè)表中的一組記錄添加到一個(gè)已有表的末尾更新查詢對(duì)大批量的數(shù)據(jù)進(jìn)行修改刪除查詢刪除一批數(shù)據(jù)4.6操作查詢4.6.1生成表查詢【例4-13】創(chuàng)建生成表查詢，組成字段是沒有書法愛好學(xué)生的“學(xué)號(hào)”、“姓名”和“入校年”三列內(nèi)容（其中“入校年”數(shù)據(jù)由“入校時(shí)間”字段計(jì)算得到，顯示為四位數(shù)字年的形式），生成的數(shù)據(jù)表命名為“tTemp”，所建查詢命名為“qT8”。操作步驟：（1）加入數(shù)據(jù)表（2）添加查詢字段（3）設(shè)定生成表查詢項(xiàng)目關(guān)鍵點(diǎn)：在設(shè)計(jì)視圖中“字段列表”區(qū)的第三個(gè)字段中輸入：入校年:Year([入校時(shí)間])；第四個(gè)字段中輸入：InStr([簡(jiǎn)歷],"書法")，在這個(gè)字段的“條件”行中輸入：“0”，并把該字段的“顯示”行中的鉤去掉（4）生成表（5）保存并運(yùn)行查詢4.6操作查詢4.6.2追加查詢【例4-14】在完成【例4-13】的基礎(chǔ)上創(chuàng)建追加查詢，將表對(duì)象“tStud”中有書法愛好學(xué)生的“學(xué)號(hào)”、“姓名”和“入校年”三列內(nèi)容追加到目標(biāo)表“tTemp”的對(duì)應(yīng)字段內(nèi)，所建查詢命名為“qT9”

操作步驟：（1）加入數(shù)據(jù)表（2）追加表（3）添加追加字段關(guān)鍵點(diǎn)：從“tStud”選擇“學(xué)號(hào)”和“姓名”字段，再在“字段”行的第三個(gè)空格處輸入“Year([入校時(shí)間])”，“追加到”行對(duì)應(yīng)的字段中選擇“入校年”，再從“tStud”選擇“簡(jiǎn)歷”字段，在“條件”中輸入“Like"*書法*"”

（4）保存并運(yùn)行查詢4.6操作查詢4.6.3更新查詢【例4-15】創(chuàng)建一個(gè)更新查詢，將表“tStud”中“年齡”字段值加1，，所建查詢命名為“qT10”。

操作步驟：（1）加入數(shù)據(jù)表（2）追加表（3）更新字段關(guān)鍵點(diǎn)：從tStud表中選擇“年齡”字段，在“年齡”字段的“更新到”中輸入“[年齡]+1”

（4）保存并運(yùn)行查詢4.6操作查詢4.6.4刪除查詢【例4-16】創(chuàng)建一個(gè)刪除查詢，刪除表“tStud”中性別為男的記錄，所建查詢命名為“qT11”。操作步驟：（1）加入數(shù)據(jù)表（2）刪除查詢（3）設(shè)置刪除條件關(guān)鍵點(diǎn)：從tStud表中選擇“性別”字段，在條件行中輸入“"男"”

（4）保存并運(yùn)行查詢4.7SQL查詢SQL——結(jié)構(gòu)化查詢語言（StructuredQueryLanguage）是集數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操縱和數(shù)據(jù)控制功能于一體的關(guān)系數(shù)據(jù)庫語言。4.7SQL查詢4.7.1SQL語言簡(jiǎn)介SQL語言是關(guān)系數(shù)據(jù)庫語言的標(biāo)準(zhǔn)1.SQL語言的特點(diǎn)高度的綜合非過程化語言結(jié)構(gòu)簡(jiǎn)潔2.SQL語言的功能數(shù)據(jù)定義功能數(shù)據(jù)查詢功能數(shù)據(jù)更新功能視圖管理功能數(shù)據(jù)控制功能

3.進(jìn)入SQL視圖的方法（2種）4.7SQL查詢4.7.2SQL的數(shù)據(jù)定義SQL的數(shù)據(jù)定義功能是指定義數(shù)據(jù)庫的結(jié)構(gòu)，包括定義基本表、定義視圖和定義索引三個(gè)部分

1.定義基本表CREATETABLE命令格式：CREATETABLE<表名>(<列名1><數(shù)據(jù)類型>[列級(jí)完整性約束條件] [,<列名2><數(shù)據(jù)類型>[列級(jí)完整性約束條件]…] [,<表級(jí)完整性約束條件>])4.7SQL查詢【例4-17】用CREATETABLE語句建立一個(gè)“學(xué)生”表，它由學(xué)生編號(hào)、姓名、性別、年齡和所在系五個(gè)字段組成，其中學(xué)生編號(hào)字段為主鍵、不能為空并且其值是唯一的，建立完成后以“qT12”存盤。

關(guān)鍵SQL語句：CREATETABLE學(xué)生(學(xué)生編號(hào)CHAR(5)PRIMARYKEYNOTNULLUNIQUE,姓名CHAR(8),性別CHAR(2),年齡INT,所在系CHAR(15))4.7SQL查詢2.修改基本表ALTERTABLE命令格式：ALTERTABLE<表名> [ADD<新字段><數(shù)據(jù)類型>[完整性約束]] [DROP<字段>|<完整性約束>]【例4-18】在【例4-17】的基礎(chǔ)上，在學(xué)生表中增加“入學(xué)時(shí)間”列，設(shè)置其數(shù)據(jù)類型為日期型，所建查詢命名為“qT13”。關(guān)鍵SQL語句：ALTERTABLE學(xué)生ADD入學(xué)時(shí)間DATE4.7SQL查詢【例4-19】在【例4-18】的基礎(chǔ)上，將學(xué)生表中的年齡的數(shù)據(jù)類型改6個(gè)字符長度的字符串，所建查詢命名為“qT14”。

關(guān)鍵SQL語句：ALTERTABLE學(xué)生ALTER年齡CHAR(6)【例4-20】在【例4-18】的基礎(chǔ)上，刪除學(xué)生表中“所在系”字段，所建查詢命名為“qT15”。關(guān)鍵SQL語句：ALTERTABLE學(xué)生DROP所在系4.7SQL查詢3.刪除基本表DROPTABLE命令格式： DROPTABLE<表名>【例4-21】刪除【例4-17】建立的學(xué)生表，所建查詢命名為“qT16”。

關(guān)鍵SQL語句：DROPTABLE學(xué)生4.7SQL查詢4.7.3SQL的數(shù)據(jù)操作利用SQL語句可以進(jìn)行多種的數(shù)據(jù)操作，包括向表中插入數(shù)據(jù)、更新數(shù)據(jù)、刪除數(shù)據(jù)等操作4.7SQL查詢1.插入數(shù)據(jù)INSERTINTO命令格式：

INSERTINTO <表名>[(<列名1>[,<列名2>,…])] VALUES([<常量1>[,<常量2>,…])【例4-22】用INSERT語句向“tStud”表插入新的一個(gè)學(xué)生記錄：“"000026","柳琴","女","20","04","1999-11-11","愛好：體育舞蹈"”，所建查詢命名為“qT17”。關(guān)鍵SQL語句：INSERTINTOtStudVALUES("000026","柳琴","女","20","04","1999-11-11","愛好：體育舞蹈","")4.7SQL查詢2.更新數(shù)據(jù)UPDATE命令格式：

UPDATE <表名>SET<列名>=<表達(dá)式>[,<列名>=<表達(dá)式>][,…] [WHERE<條件>]【例4-23】用UPDATE語句將“tStud”表中的“李四”同學(xué)的入校時(shí)間改為“1997-9-1”，所建查詢命名為“qT18”。

關(guān)鍵SQL語句：UPDATEtStudSET入校時(shí)間=#1997-9-1#WHERE姓名="李四"4.7SQL查詢3.刪除數(shù)據(jù)DELETE命令格式：

DELETEFROM<表名>WHERE[<條件>]【例4-24】用DELETE語句將“tStud”表中的所有男同學(xué)的數(shù)據(jù)記錄刪除，所建查詢命名為“qT19”。

關(guān)鍵SQL語句：DELETEFROMtStudWHERE性別="男"4.7SQL查詢4.7.4SQL的數(shù)據(jù)查詢1.SELECT語句格式SELECT[謂詞]<字段列表>|<目標(biāo)表達(dá)式>|<函數(shù)>[AS別名]FROM表名[WHERE條件…][GROUPBY字段名][HAVING分組的條件][ORDERBY字段名[ASC|DESC]];4.7SQL查詢2.簡(jiǎn)單查詢【例4-25】查詢“tStud”數(shù)據(jù)表中男生的情況，按入校時(shí)間降序排序，所建查詢命名為“qT20”。關(guān)鍵SQL語句SELECT*FROMtStudWHERE(tStud.性別="男")ORDERBYtStud.入校時(shí)間DESC4.7SQL查詢2.簡(jiǎn)單查詢【例4-26】查詢“tStud”數(shù)據(jù)表中年齡最大的三位男生的情況，只顯示“學(xué)號(hào)”、“姓名”、“性別”“年齡”、“所屬院系”五個(gè)字段的信息，所建查詢命名為“qT21”。

關(guān)鍵SQL語句SELECT*FROMtStudWHERE(tStud.性別="男")ORDERBYtStud.入校時(shí)間DESC4.7SQL查詢3.聯(lián)接查詢【例4-27】創(chuàng)建一個(gè)查詢，查找男同學(xué)的“學(xué)號(hào)”、“姓名”、“課程號(hào)”和“成績”的內(nèi)容，所建查詢命名為“qT22”。

關(guān)鍵SQL語句SELECTtStud.學(xué)號(hào),tStud.姓名,tScore.課程號(hào),tScore.成績FROMtStud,tScoreWHEREtStud.學(xué)號(hào)=tScore.學(xué)號(hào)ANDtStud.性別="男"4.7SQL查詢【例4-28】創(chuàng)建一個(gè)查詢，查找年齡大于25的學(xué)生的“姓名”、“課程名”和“成績”三個(gè)字段內(nèi)容，所建查詢命名為“qT23”。關(guān)鍵SQL語句SELECTtStud.姓名,tCourse.課程名,tScore.成績FROM(tCourseINNERJOINtScoreONtCourse.課程號(hào)=tScore.課程號(hào))INNERJOINtStudONtScore.學(xué)號(hào)=tStud.學(xué)號(hào)WHEREtStud.年齡>254.7SQL查詢4.使用函數(shù)的查詢【例4-29】統(tǒng)計(jì)各院系的人數(shù)，所建查詢命名為“qT24”。關(guān)鍵SQL語句SELECT所屬院系,Count(所屬院系)AS人數(shù)FROMtStudGROUPBY所屬院系【例4-30】統(tǒng)計(jì)各門課程的平均分、最高分和最低分，所建查詢命名為“qT25”。

關(guān)鍵SQL語句SELECTtScore.課程號(hào),tCourse.課程名,tScore.成績,Avg(tScore.成績)AS成績之平均值,Max(tScore.成績)AS成績之最大值,Min(tScore.成績)AS成績之最小值FROMtScore,tCourseWHEREtScore.課程號(hào)=tCourse.課程號(hào)4.7SQL查詢5.聯(lián)合查詢基本格式為：SELECT*FROM表或查詢UNIONSELECT*FROM表或查詢

【例4-31】創(chuàng)建一個(gè)查詢，查找年齡大于等于30歲或者年齡小于等于20歲的學(xué)生，顯示“學(xué)號(hào)”、