工作任務(wù)6 設(shè)計(jì)和創(chuàng)建查詢.ppt

1、工作任務(wù)6 設(shè)計(jì)和創(chuàng)建查詢,6.1 任務(wù)描述 現(xiàn)在商店管理系統(tǒng)中相關(guān)的一些數(shù)據(jù)表已建成。為了使用戶能夠輕松、快捷地從數(shù)據(jù)庫中檢索有關(guān)商品、訂單、客戶和供應(yīng)商的各種信息，本任務(wù)將創(chuàng)建訂單明細(xì)查詢、客戶信息的精確和模糊查詢、顯示每筆訂單的備貨期的計(jì)算型查詢。,通過操作查詢來完成更新商品價(jià)格、追加急需商品信息、生成緊急訂單表、刪除備貨期為3天的記錄等操作。,6.2 業(yè)務(wù)咨詢,6.2.1 表達(dá)式的書寫規(guī)則 表達(dá)式是Microsoft Access運(yùn)算的基本組成部分。 表達(dá)式是算術(shù)或邏輯運(yùn)算符、常數(shù)、函數(shù)和字段名稱、控件以及屬性的任意組合，計(jì)算結(jié)果為單個(gè)值。 表達(dá)式可執(zhí)行計(jì)算、操作字符或測試數(shù)據(jù)。,在書

2、寫表達(dá)式時(shí)，要遵循以下的規(guī)則： （1）取值范圍操作符（、=、或Between.And），如表2.8所示。 （2）排除不匹配的值（Not），如表2.9 所示。,表2.8取值范圍示例,表2.9排除不匹配的值示例,（3）列表值（In），如表2.10所示。 （4）全部或部分匹配的文本值，如表2.11所示。 （5）匹配模式（Like），如表2.12所示。 （6）日期值，如表2.13所示。 （7）空和零長度字符串，如表2.14所示。,表2.10列表值示例,表2.11全部或部分匹配的文本值示例,表2.12匹配模式示例,表2.13日期值示例,表2.14空和零長度字符串示例,6.2.2 表達(dá)式生成器,表達(dá)式生成

3、器由表達(dá)式框、運(yùn)算符按鈕和表達(dá)式元素三部分組成，如圖2.34所示。 選中一個(gè)表，打開它的“設(shè)計(jì)視圖”； 選擇一個(gè)數(shù)字型字段，展開“有效性規(guī)則”屬性右邊的按鈕，如下圖所示。,圖2.34 表達(dá)式生成器,1表達(dá)式框,表達(dá)式框:用戶可在其中創(chuàng)建表達(dá)式。 生成器的下方區(qū)域:可以創(chuàng)建表達(dá)式的元素，然后將這些元素粘貼到表達(dá)式框中以形成表達(dá)式。 也可以直接在表達(dá)式框中鍵入表達(dá)式。,2運(yùn)算符按鈕,單擊某個(gè)運(yùn)算符按鈕，表達(dá)式生成器將在表達(dá)式框中插入相應(yīng)的運(yùn)算符。,選擇左下方列表框中的運(yùn)算符文件夾和中部列表框中相應(yīng)的運(yùn)算符類別，可以得到表達(dá)式中所能使用的運(yùn)算符的完整列表。 右側(cè)的列表框中列出的是所選類別中的所有運(yùn)算

4、符。,3表達(dá)式元素,生成器下方有3個(gè)列表框。 左側(cè)的列表框:包含文件夾，該文件夾列出了表、查詢、窗體及報(bào)表等數(shù)據(jù)庫對象，以及內(nèi)置和用戶定義的函數(shù)、運(yùn)算符以及常用表達(dá)式。,中間的列表框:列出了左側(cè)列表框中選定的文件夾內(nèi)特定的元素或特定的元素類別。 例如，如果在左邊的框中選擇“內(nèi)置函數(shù)”，那么中間的列表框中將列出Microsoft Access函數(shù)的類別。,右側(cè)的列表框:列出了在左側(cè)和中間列表框中選定的元素的值。 例如，如果在左側(cè)的列表框中選擇“內(nèi)置函數(shù)”，并在中間列表框中選定一種函數(shù)類別，那么右側(cè)的列表框中將列出選定的類別中所有的內(nèi)置函數(shù)。,6.3 任務(wù)實(shí)施,6.3.1 查詢訂單明細(xì) 在“訂單”

5、表中，為了便于輸入數(shù)據(jù)和減少數(shù)據(jù)庫的冗余度，我們將其中的商品信息和客戶信息均采用編號形式進(jìn)行輸入。,在查看訂單時(shí)，我們可以通過表之間數(shù)據(jù)的關(guān)聯(lián)性，采用多表查詢顯示訂單明細(xì)信息，即增加商品名稱、型號規(guī)格、單價(jià)、客戶的公司名稱以及地址等信息。,（1）打開“商店管理系統(tǒng)”數(shù)據(jù)庫。 （2）在數(shù)據(jù)庫窗口中選擇“查詢”對象，雙擊“在設(shè)計(jì)視圖中創(chuàng)建查詢”，打開查詢設(shè)計(jì)器。 （3）在“顯示表”對話框中選擇“訂單”、“商品”和“客戶”表作為查詢數(shù)據(jù)源。,（4）在查詢設(shè)計(jì)器下方的字段行中添加如圖2.35所示的字段。 （5）將查詢保存為“訂單明細(xì)查詢”，運(yùn)行查詢的結(jié)果如圖2.36所示。,6.3.2 查詢北京和上海的

6、客戶信息,進(jìn)行信息查詢時(shí)，經(jīng)常會涉及多個(gè)條件，即需要對多個(gè)字段設(shè)置條件或在一個(gè)字段上設(shè)置多個(gè)條件，我們可使用And和Or運(yùn)算符來構(gòu)造復(fù)合條件。,例如：要查詢北京和上海的客戶信息，需在“客戶”表的“城市”字段上設(shè)置條件“北京”或“上?！薄?（1）打開“商店管理系統(tǒng)”數(shù)據(jù)庫。 （2）選中“客戶”表作為查詢數(shù)據(jù)源。 （3）單擊【創(chuàng)建】菜單中的【查詢設(shè)計(jì)】項(xiàng)。 （4）在彈出“顯示表”對話框中選“客戶”表。如圖所示。,（5）在查詢設(shè)計(jì)器下方的字段行中添加“客戶”表中的所有字段。即直接將“客戶”字段列表中的“*”拖曳到下方的“字段”行中。,（6）雙擊“城市”字段，將其添加到下方的設(shè)計(jì)區(qū)中。取消勾選其“顯示

7、”復(fù)選框，并在“條件”行中輸入“北京”，在“或”行中輸入“上海”，如圖2.37所示。,圖2.38 “北京和上海的客戶信息”查詢結(jié)果,（7）將查詢保存為“北京和上海的客戶信息”，運(yùn)行查詢的結(jié)果如圖2.38所示。,6.3.3 查詢客戶地址含有“路”的客戶信息,在進(jìn)行信息查詢時(shí)，除了能夠按照條件進(jìn)行精確查詢外，Access也提供了模糊條件查詢，即用Like運(yùn)算符來構(gòu)造條件表達(dá)式。 這里，我們將查詢客戶地址中含有“路”的客戶信息。 （1）利用查詢設(shè)計(jì)器新建查詢。,（2）設(shè)置“客戶”表作為查詢數(shù)據(jù)源。 （3）將“客戶”表中的所有字段添加到設(shè)計(jì)區(qū)中。 （4）在“地址”字段下方設(shè)置條件：Like *路*，如

8、圖2.39所示。,（5）將查詢保存為“客戶地址中含有路的客戶信息”，運(yùn)行查詢的結(jié)果如圖2.40所示。,圖2.40 “客戶地址中含有路的客戶信息”查詢結(jié)果,6.3.4 查詢顯示每筆訂單的備貨期,前面創(chuàng)建的查詢僅僅是從數(shù)據(jù)源中獲取符合條件的記錄，并沒有對符合條件的記錄進(jìn)行更深入的分析和計(jì)算。 在實(shí)際應(yīng)用中，常常需要對查詢的結(jié)果進(jìn)行計(jì)算。,例如，在“訂單”表中，我們需要查看每筆訂單的備貨期，以便及時(shí)提供所需商品。 備貨期則可通過發(fā)貨日期和訂購日期計(jì)算得出。,（1）利用查詢設(shè)計(jì)器新建查詢，并將“訂單”表作為數(shù)據(jù)源。 （2）雙擊“訂單”表中的“*”，將表的全部字段加入到下方的設(shè)計(jì)區(qū)中。,（3）構(gòu)造計(jì)算型

9、字段“備貨期”。在設(shè)計(jì)區(qū)后面的“字段”列中寫入字段名稱“備貨期: 發(fā)貨日期-訂購日期”，如圖2.41所示。 （4）將查詢保存為“顯示每筆訂單的備貨期”，運(yùn)行查詢的結(jié)果如圖2.42所示。,6.3.5 將“商品”表中“筆記本電腦”類的商品價(jià)格下調(diào)5%,在創(chuàng)建和維護(hù)數(shù)據(jù)庫的過程中，常常需要對表中的記錄進(jìn)行更新和修改。 如果用戶通過數(shù)據(jù)表視圖來更新表中記錄，那么當(dāng)更新的記錄很多或更新的記錄符合一定條件時(shí)，簡單有效的方法是利用Access提供的更新查詢。,（1）打開查詢設(shè)計(jì)器，將“商品”表和“類別”表添加到查詢設(shè)計(jì)器中作為數(shù)據(jù)源。 （2）將“商品”表的“單價(jià)”字段和“類別”表的“類別名稱”字段添加到查詢

10、設(shè)計(jì)器的設(shè)計(jì)區(qū)中。,（3）選擇【查詢工具】【更新】命令，指定創(chuàng)建更新查詢。,（4）在“單價(jià)”字段的“更新到”網(wǎng)格內(nèi)輸入如圖2.43所示的利用原來的“單價(jià)”字段的內(nèi)容計(jì)算新單價(jià)的公式，即單價(jià)*(1-0.05)。,（5）在“類別名稱”字段的“條件”網(wǎng)格內(nèi)輸入“筆記本電腦”。 （6）將查詢保存為“筆記本電腦單價(jià)下調(diào)5%”。,（7）運(yùn)行查詢，彈出如圖2.44所示的提示對話框，單擊【是】按鈕，執(zhí)行更新操作。 （8）從數(shù)據(jù)庫窗口中選擇“表”對象，打開“商品”表，看到每種筆記本電腦的“單價(jià)”都下調(diào)了5%，如圖2.45所示。,6.3.6 將數(shù)量低于5件的商品追加到“急需商品信息”表中,在進(jìn)行數(shù)據(jù)庫維護(hù)時(shí)，常常

11、需要將某個(gè)表中符合一定條件的記錄添加到另外一個(gè)表中。 Access提供的追加查詢能夠很容易地實(shí)現(xiàn)一組記錄的添加。,例如，在工作任務(wù)5中，我們通過 復(fù)制“商品”表的結(jié)構(gòu)創(chuàng)建了“急需商品信息”表，但未在“急需商品信息”表中輸入記錄，即該表為空表。 這里，我們將通過追加查詢，將“商品”表中數(shù)量低于5件的商品信息添加到“急需商品信息”表中。,（1）打開查詢設(shè)計(jì)器，添加“商品”表到查詢設(shè)計(jì)器中作為數(shù)據(jù)源。 （2）將“商品”表的所有字段依次添加到查詢設(shè)計(jì)器的設(shè)計(jì)區(qū)中。,（3）選擇【查詢工具】【追加查詢】命令，彈出 如圖2.46所示的“追加”對話框。在“表名稱”下拉列表中選擇“急需商品信息”表，默認(rèn)數(shù)據(jù)庫為

12、“當(dāng)前數(shù)據(jù)庫”。,（4）單擊【確定】按鈕，返回查詢設(shè)計(jì)器。查詢設(shè)計(jì)器標(biāo)題欄上顯示的查詢類型由“選擇查詢”變?yōu)榱恕白芳硬樵儭薄Ｔ凇皵?shù)量”字段下方設(shè)置“條件”為“=5”，如圖2.47所示。,（5）將查詢保存為“追加數(shù)量低于5件 的商品信息”。 （6）運(yùn)行查詢，彈出如圖2.48所示的提示對話框，單擊【是】按鈕，執(zhí)行追加操作。,（7）從數(shù)據(jù)庫窗口中選擇“表”對象，打開“急需商品信息”表，即可看見查詢結(jié)果。該表中的所有商品的數(shù)量均小于等于5，如圖2.49所示。,6.3.7 將“訂單”表中備貨期低于3日的訂單生成新表“緊急訂單”,在Access中，從表中訪問數(shù)據(jù)要比從查詢中訪問數(shù)據(jù)快得多。 如果經(jīng)常要從幾

13、個(gè)表中提取數(shù)據(jù)，那么最好的方法是使用Access提供的生成表查詢，從多個(gè)表中提取數(shù)據(jù)，然后組合起來生成一個(gè)新表并永久保存。,生成表查詢是利用已有的數(shù)據(jù)創(chuàng)建一個(gè)新表，實(shí)際上就是將查詢出的動態(tài)集合以表的形式保存。 通常，可以將復(fù)雜的查詢結(jié)果保存為一個(gè)臨時(shí)表，這樣可以提高工作效率。,現(xiàn)在，為了提高商品銷售和物流環(huán)節(jié)的工作效率，及時(shí)處理緊急訂單，我們可將“訂單”表中備貨期低于3日的訂單信息生成新表。,（1）打開查詢設(shè)計(jì)器，添加“訂單”表到查詢設(shè)計(jì)器中作為數(shù)據(jù)源。 （2）將“訂單”表的所有字段依次添加到查詢設(shè)計(jì)器的設(shè)計(jì)區(qū)中。,（3）選擇【查詢工具】【生成表】命令，彈出如圖2.50所示的“生成表”對話框。

14、在“表名稱”下拉列表中選擇“緊急訂單”， 默認(rèn)數(shù)據(jù)庫為“當(dāng)前數(shù)據(jù)庫”。,（4）單擊【確定】按鈕，返回查詢設(shè)計(jì)器。查詢設(shè)計(jì)器標(biāo)題欄上顯示的查詢類型由“選擇查詢”變?yōu)榱恕吧杀聿樵儭?。在“發(fā)貨日期”字段下方設(shè)置“條件”為“=訂購日期+3”，如圖2.51所示。,（5）將查詢保存為“生成備貨期低于3日的緊急訂單表”。 （6）運(yùn)行查詢，彈出如圖2.52所示的提示對話框，單擊【是】按鈕，執(zhí)行生成表操作。,（7）在數(shù)據(jù)庫窗口中選擇“表”對象，即可看見新生成的“緊急訂單”表。打開該表，即可看見查詢結(jié)果，如圖2.53 所示。,6.3.8 刪除“緊急訂單”表中備貨期為3天的訂單,在數(shù)據(jù)庫的維護(hù)過程中，經(jīng)常需要對表

15、中的一些過時(shí)或無用的數(shù)據(jù)進(jìn)行刪除。 盡管用戶可以比較容易地從數(shù)據(jù)表中刪除某條記錄，但如果要刪除滿足某些條件的一組記錄時(shí)，就可以使用Access提供 的刪除查詢，利用該查詢可以一次刪除一組同類的記錄。,這里，我們將通過刪除查詢，將生成的“緊急訂單”表中備貨期為3天的記錄刪除。 （1）打開查詢設(shè)計(jì)器，添加“緊急訂單”表到查詢設(shè)計(jì)器中作為數(shù)據(jù)源。 （2）將作為控制條件的“發(fā)貨日期”字段添加到查詢設(shè)計(jì)器的設(shè)計(jì)區(qū)中。,（3）選擇【查詢工具】【刪除】命令，指定創(chuàng)建刪除查詢。查詢設(shè)計(jì)器標(biāo)題欄上顯示的查詢類型由“選擇查詢”變?yōu)榱恕皠h除查詢”，如圖2.54所示。在“發(fā)貨日期”字段下方設(shè)置刪除條件“訂購日期+3”

16、。,（4）將查詢保存為“刪除緊急訂單表中備貨期為3天的訂單”。 （5）運(yùn)行查詢，彈出如圖2.55所示的提 示對話框，單擊【是】按鈕，執(zhí)行刪除操作。,（6）從數(shù)據(jù)庫窗口中選擇“表”對象，打開“緊急訂單”表即可看見查詢結(jié)果，表中備貨期為3天的訂單已被刪除，如圖2.56所示。,6.4 任務(wù)拓展,6.4.1 查詢指定價(jià)格區(qū)間內(nèi)的商品信息 在查詢設(shè)計(jì)器中打開“商品”表作為數(shù)據(jù)源，并選中所有字段加入到設(shè)計(jì)器的下方。 在“單價(jià)”下方的 條件中輸入“Between最低單價(jià) and 最高單價(jià)”，如圖所示。 將查詢保存為“查詢指定價(jià)格區(qū)間的商品信息”，運(yùn)行查詢。 輸入價(jià)格在200-500之間的商品信息。結(jié)果如圖所

17、示。,6.4.2 查看B部6月份的銷售額 （1）利用查詢設(shè)計(jì)器，將“訂單”表和“商品”表作為數(shù)據(jù)源； （2）將“訂單”表的所有字段放到下方設(shè)計(jì)區(qū)中。 （3）構(gòu)造計(jì)算型字段“銷售額”。鼠標(biāo)右鍵單擊字段行中的空白網(wǎng)格，在彈出的菜單中選“生成器”命令，彈出“表達(dá)式生成器”對話框，如圖所示。 （4）構(gòu)造如圖所示的表達(dá)式。然后單擊【確定】按鈕。,（5）在“訂購日期”字段下方設(shè)置查詢條件“Month(訂購日期)=6”，在“銷售部門”字段下方設(shè)置查詢條件“B部”，如圖所示。 （6）保存查詢?yōu)椤安榭碆部6月份的銷售額”，并運(yùn)行查詢。結(jié)果如下圖所示。,6.4.3查詢供應(yīng)商地址中不含“路”的記錄,（1）單擊【創(chuàng)建】-【查詢設(shè)計(jì)】，在查詢設(shè)計(jì)器窗口中將“供應(yīng)商”表作為數(shù)據(jù)源； （2）將“供應(yīng)商”表的全部字段放入到下方的設(shè)計(jì)區(qū)。 （3）在“地址”字段的下方設(shè)置條件為“not like *路*”。 （4）將查詢保存為“查詢供應(yīng)商地址不含“路”的信息” （5）運(yùn)行查詢，結(jié)果如圖所示。,6.5 任務(wù)檢測,（1）打開商店管理系統(tǒng)，選擇“查詢”對象，查看數(shù)據(jù)庫窗口中的查詢是否如圖2.64所示，包含11個(gè)查詢。,（2）分別運(yùn)行其中的7個(gè)選擇查詢，查看查詢運(yùn)行的結(jié)果是否如圖2.36、圖2.38、圖2.40、圖2.42、圖2.59、圖2