1、第三章第三章 查詢查詢主講教師：王琳學習目標學習目標 o 使用設計器創建查詢對象的方法o 使用查詢向導創建查詢對象的方法o 在查詢中自定義字段數據的方法o 使用條件表達式描述用戶查詢要求的方法本章內容本章內容1. 查詢的基本知識2. 創建選擇查詢3. 創建交叉表查詢4. 創建參數查詢5. 創建操作查詢6. 創建SQL查詢7. 總結提高8. 思考與練習3.1 查詢的基本知識o 查詢功能o 查詢分類o 查詢準則查詢的功能查詢的功能o 選擇表o 選擇字段o 選擇記錄o 排序記錄o 執行計算o 建立表o 建立基于查詢的圖表o 使用查詢作為子查詢o 修改表查詢的分類1.選擇查詢 選擇查詢時最常見的查詢類

2、型，它從一個或多個表中檢索數據，并且在可以更新記錄的數據表中顯示結果。2.交叉表查詢 交叉表查詢用來顯示來源于表中某個字段的總結值，這個總結值可以是一個合計、計數以及平均等，并將它們分組，一組列在數據表左側，一組列在數據表的上部。查詢的分類3.操作查詢 操作查詢是對查詢所生成的動態集進行更改的查詢。操作查詢可以分為刪除查詢、更新查詢、追加查詢和生成表查詢。4.SQL查詢 SQL查詢是用戶使用SQL語句來創建的一種查詢。SQL查詢包括聯合查詢、傳遞查詢、數據定義查詢和子查詢4類。5.參數查詢 參數查詢是在執行查詢時顯示一個對話框，提示輸入信息的查詢。查詢準則1、準則中的運算符運算符運算符功能功能

3、舉例舉例=,=,=,!=,!,!75 and 84查詢準則2、準則中的通配符 Access表中常用通配符有?、*、#、等。n？代表任意一個字符n* 代表任意字符串n# 代表單一數字n字符表 表示字符表中的單一字符n!字符表 指不在字符表中的單一字符查詢準則函數函數功能功能舉例舉例SUM(字段名)返回字符字段總和返回字符字段總和Sum(成績成績)AVG(字段名)返回字段平均值返回字段平均值Avg (成績成績)COUNT(字段名)統計記錄個數統計記錄個數Count (成績成績)MAX(字段名)返回字段最大值返回字段最大值Max (成績成績)MIN(字段名)返回字段最小值返回字段最小值Min (成績

4、成績)3、準則中的函數3.2 創建選擇查詢o 問題的提出一個表中可能包含多個字段數據，有時用戶只需要表中的部分數據，這個問題如何解決呢？o 查詢對象可以解決這個問題，可以從一個表確定要查找的字段數據，并顯示查找到的數據。這類查詢稱為選擇查詢，它是查詢對象中最常用的一種查詢。【操作實例【操作實例1】o 創建名稱為“物品基本信息”的選擇查詢對象，從“物品”表中查找“物品編號”、“名稱”、“物品類型”、“制購類型”字段的數據。1、創建查詢從多個表中查找數據、創建查詢從多個表中查找數據【操作實例【操作實例1】o 操作步驟：n （1）在Access中打開“格達公司”數據庫。n （2）打開查詢設計器【操作

5、實例【操作實例1】n （3）選擇查詢對象的數據來源表【操作實例【操作實例1】n （4）確定查詢對象查找的數據字段n （5）保存查詢對象【操作實例【操作實例1】n （6）預覽查詢對象歸納分析o （1）【顯示表】對話框的使用o （2）查詢設計視圖的組成o （3）主窗口中菜單欄、工具欄的變化2、創建查詢從多個表中查找數據o 問題的提出【操作實例1】介紹了從一個表中查找數據的方法，如何從多個表中查找出需要的數據呢？o 【操作實例2】將介紹了如何從多個表中查找數據，通過這個實例可以理解為什么要在數據庫中建立表之間的關系。【操作實例【操作實例2】o 創建名稱為“外設物品價格”的查詢對象，從“物品”、“供應

6、商”、“供應商與物品”3個表中查找“物品編號”、“外設名稱”、“供應商名稱”、“價格”字段的數據，通過它們可以了解不同供應商提供的外設的價格信息。【操作實例【操作實例2】o 操作步驟：n （1）在Access中打開“格達公司”數據庫。n （2）打開查詢設計器n （3）選擇查詢數據的來源表【操作實例【操作實例2】n （4）確定查詢對象要查找的數據字段n （5）設置查找的條件歸納分析o 創建來自多個表的查詢對象的步驟n 打開查詢設計器n 選擇查詢數據來源n 建立表之間的關系n 添加目標字段。n 設置查詢條件n 保存查詢對象3、創建查詢通過輸入參數條件查找數據o 問題的提出【操作實例2】中查找了帶有

7、條件的數據，但條件是固定不變的。能不能根據用戶的需要在運行查詢時增加人機交互的接口，使用戶可以根據提示輸入參數條件，使查詢對象能根據用戶輸入的參數條件為不同用戶查找不同的數據呢？o 查詢對象可以解決這個問題，不僅可以提供一個輸入參數條件的接口，還可以提供多個人機交互的接口，按交互方式輸入的條件查找數據。這類查詢稱為參數查詢。【操作實例【操作實例3】o 創建名稱為創建名稱為“供應商物品價格供應商物品價格”的參數查詢的參數查詢對象，可以根據用戶輸入的不同物品名稱顯對象，可以根據用戶輸入的不同物品名稱顯示供應商提供的物品價格信息。示供應商提供的物品價格信息。【操作實例【操作實例3】o 操作步驟： n

8、 （1）打開查詢對象【操作實例【操作實例3】n （2）將查詢另存為一個新查詢對象n （3）定義輸入參數條件的提示文字【操作實例【操作實例3】n （4）保存并運行查詢歸納分析o 通過原有查詢對象生成新查詢對象的方法n 如果新創建的查詢對象與原有的查詢對象類似，可在設計視圖中打開原有的查詢對象，通過“另存為”對話框將它保存為新查詢對象的名稱，通過在設計視圖修改查詢字段與查詢條件得到新查詢對象。歸納分析o 定義輸入參數條件的提示文字的方式n 實現用戶人機交互的接口，只要在查找數據的字段【條件】單元格中輸入“ ”方括號，在方括號中定義的文字將在執行查詢對象時打開的輸入參數對話框中顯示，這些文字主要是為

9、了提示用戶輸入參數參考的，為可選項，如果不定義文字，將打開沒有提示信息的對話框。n 在打開的輸入參數對話框中一定要按字段數據要求輸入具體數據值，否則會找不到任何數據。歸納分析n 可在多個字段相應的【條件】單元格定義不同的參數，在執行查詢對象時將分別打開提示輸入對話框，可按要求輸入不同的參數，將按多個條件查找滿足要求的數據。3.創建查詢按總計生成新數據o 問題的提出問題的提出 n 對查找到的記錄數據能不能統計記錄的條數？對字段數據能不能進行匯總？求平均數？o 查詢對象可以解決這些問題，在查詢設計視圖下通過Access的總計計算功能，可在查詢中對查找到的數據記錄進行統計。【操作實例4】o 創建名稱

10、為“生成銷售物品總計數據”的查詢對象，該查詢用來生成銷售物品總數、最大的銷售價格、最小的銷售價格、平均的銷售價格數據【操作實例4】o 操作步驟：n （1）打開查詢設計視圖并添加“物品”表與“客戶與物品”表。n （2）選擇查詢字段n （3）添加【總計】行【操作實例4】n （4）選擇總計選項n （5）修改字段標題、保存并運行歸納分析o 通過查詢生成新數據，可通過總計選項來計算，常用的總計選項有總計、計數、最大值、最小值、平均、表達式、條件等。o 在總計下拉列表中可以看到總計包含12個選項，它們代表不同的函數和操作命令，通過總計計算可以生成不同的新數據，總計函數的功能說明見書中表5.1。4.創建查詢

11、按分組生成新數據o 問題的提出問題的提出 n 在實際應用中，不僅可以對所有的記錄進行統計，還可以將記錄分組，然后對每個組的數據進行統計。如何按分組數據計算出新數據呢？【操作實例5】o 創建名稱為“生成分組物品統計數據”的查詢對象，該查詢以“物品類型”的值進行分組，然后計算出電腦產品、配件、外設的統計數據。【操作實例5】o 操作步驟：n （1）打開查詢設計視圖并添加“物品”表。n （2）定義查詢字段n （3）設置字段的總計選項n （4）預覽查詢結果5. 在查詢中自行定義數據字段生成新數據o 問題的提出問題的提出 n 前面都是使用Access系統提供的總計函數對表中原有字段進行計算得出的新數據，如

12、果新數據與表中的字段沒有關系，或者新數據與多個字段有關系，前面介紹的總計函數就無能為力了，如何生成這兩種新數據呢？【操作實例6】o 創建名稱為“外設銷售參考價格”的查詢對象，該查詢將自定義“銷售價”、“稅后價”字段數據。【操作實例6】o 操作步驟：n （1）打開查詢設計視圖并添加“外設物品價格”查詢對象。n （2）選擇查詢字段n （3）添加計算字段n （4）設置新建字段的格式屬性5.47 在查詢中添加計算字段 圖5.48 “字段屬性”對話框 【操作實例6】n （5）設置分組、以平均值求成本價n （6）保存查詢n （7）預覽查詢圖5.49 設置總計計算方式 圖5.50 查詢生成的自定義字段數據

13、歸納分析o （1）自定義字段n自定義字段的數據可以通過表或查詢中已有字段建立的表達式構成，例如“銷售價+銷售價*0.17”；也可以全部由函數、常數、文字、操作符合等自定義的表達式構成，例如“5*78/3”。o （2）設置自定義字段數據的顯示格式n來自表與查詢中的字段，保留用來在表中設置的數據格式屬性，在查詢中自定義的新字段需要通過設置其格式屬性來確定顯示的格式。o （3）通過總計“分組”函數，可以消除重復的數據，并按分組數據求出對應數據。3.3 創建交叉表查詢o 問題的提出n在實際生活當中，經常用行標題顯示某類產品，用列標題顯示該類中的具體產品，在行列的交叉處顯示這類產品的具體數據，這種顯示數

14、據表稱為交叉表。在Access數據庫中能否找出交叉數據，并按交叉表顯示交叉信息呢？o Access的查詢對象可以解決這個問題，為解決這個問題可以專門創建一個查詢對象，這類查詢對象稱為交叉表查詢。【操作實例【操作實例7】o 創建名稱為“外設物品價格交叉表”的交叉表查詢對象，該查詢用來查找每位供應商提供的外設物品價格，交叉表上使用“供應商名稱”字段值做列標題、“名稱”字段值做行標題，交叉位置顯示該供應商提供的外設物品價格。【操作實例【操作實例7】o 操作步驟：n （1）啟動查詢向導n （2）回答向導提問o 回答查找數據的表或查詢的名稱（在哪里找數據）o 回答交叉表中用哪個字段的值做行標題o 回答交

15、叉表中哪個字段的值用來做列標題圖5.18 選擇交叉表查找的表/查詢 圖5.19 選擇做行標題的字段 圖5.20 選擇做列標題的字段 【操作實例【操作實例7】【操作實例【操作實例7】o 回答交叉表中交叉點計算什么數值o 回答新建查詢的名字【操作實例【操作實例7】n （3）自動創建交叉表查詢歸納分析o （1）使用向導創建查詢對象的步驟n 啟動查詢向導n 回答向導提問n 自動創建查詢對象。歸納分析o （2）創建交叉表對象的關鍵n 交叉表對象的實質是查找作為行標題的字段、列標題的字段、交叉位置的字段的數據，所以，創建交叉表對象的關鍵就是確定行標題的字段、列標題的字段、交叉位置的字段。n 在設計視圖可以

16、重新選擇行標題的字段、列標題的字段與交叉位置的字段。歸納分析o （3）交叉表查詢的數據源n 交叉表查詢只能在一個表或查詢中查找數據，如果交叉表中查找的三個字段數據來自于多個表和查詢中需要事先創建一個來自多表的查詢，然后再根據這個查詢對象創建交叉表查詢。3.4 創建參數查詢o 建立單參數查詢o 建立多參數查詢o 設定參數查詢順序3.5 創建操作查詢o 查詢對象不僅能從表及查詢中查找滿足指定條件的字段數據，還可以對查找到的記錄進行不同的處理。o 本節的主要任務就是掌握通過查詢對象將查找到的記錄生成新表、刪除、追加、更新等操作方法。1.創建查詢將查找的數據生成一個新表o 問題的提出n在Access中

17、，在表設計視圖通過“另存為”命令可以將原有的表保存為一個新表，但只是生成一個與原表相同的表結構。能不能將現有表中的字段及數據生成一個新表呢？o 查詢對象可以解決這個問題，不僅可以從一個表提取數據生成一個新表，還可以從幾個表或查詢中提取數據，將數據及表結構一同生成一個新表。這類查詢稱為生成表查詢。【操作實例8】o 創建名稱為“客戶銷售價格表生成表”的生成表查詢對象，該查詢用來創建一個包含字段為“物品編號、名稱、價格、客戶名稱”的新表“客戶銷售價格表”。【操作實例8】o 操作步驟：n （1）打開查詢設計視圖n （2）選擇查詢字段【操作實例8】n （3）將查詢定義為“生成表查詢”類型圖5.25 選擇

18、查詢類型為“生成表查詢” 圖5.26 “生成表”對話框 【操作實例8】n （4）預覽將要生成的表n （5）生成新表歸納分析o （1）通過查詢生成新表的步驟n 在查詢設計視圖確定新表的字段。n 定義查詢為“生成表查詢”類型。n 預覽查找到的新表中的數據。n 運行查詢生成新表。歸納分析o （2）生成新表注意事項n 如果預覽到的一組記錄不是你要生成的表的數據，可單擊工具欄【視圖】按鈕，返回查詢設計視圖，修改查找的字段設置查詢條件 ，直到滿意為止。n 以后運行生成表查詢都會生成該表，如果定義的表已經存在，將覆蓋已有的表。2.創建查詢將查找的數據從表中刪除o 問題的提出n整理數據的主要操作之一就是刪除數

19、據，前面介紹的刪除數據的方法，只能手動刪除表中某一個記錄或單個字段的數值，非常麻煩。能不能讓系統自動刪除一批記錄，或刪除一批滿足相同條件的記錄呢？o 查詢對象可以解決這個問題，查詢對象可以將刪除的數據按指定條件查找出來，再統一進行刪除處理。這類查詢稱為刪除查詢。【操作實例9】o 創建名稱為“刪除數據查詢”的刪除查詢對象，該查詢用來刪除“客戶銷售價格表”中銷售價格在1000元以下的客戶銷售物品的記錄。【操作實例9】o 操作步驟：n （1）打開查詢設計視圖n （2）選擇“客戶銷售價格表”表的所有字段作為查詢字段【操作實例9】n （3）選擇查詢類型為“刪除查詢”n （4）輸入查詢準則n （5）預覽要

20、刪除的數據圖5.30 查詢設計表格中添加的【刪除】行 圖5.31 輸入刪除準則 圖5.32瀏覽將要刪除的記錄 【操作實例9】n （6）執行刪除記錄的操作歸納分析o 創建刪除查詢對象與創建生成表步驟類似。o 但要注意刪除數據記錄后將不能再恢復，所以一定要小心運行刪除查詢。3.創建查詢將查找的數據統一更新o 問題的提出n 通過操作實例9知道可以自動刪除表中的一批數據，能不能對表中的一批數據自動進行更新操作呢？o 查詢對象可以解決這個問題，查詢對象可以將更新的數據按指定條件查找出來，再對這些數據進行更新處理。這類查詢稱為更新查詢。【操作實例10】o 創建名稱為“漲價操作”的更新查詢對象，該查詢用來將

21、“客戶銷售價格表”中的“高能PC”電腦的銷售價格漲價10。【操作實例10】o 操作步驟：n （1）打開查詢設計視圖n （2）選擇查詢字段【操作實例10】n （4）輸入查詢準則n （5）預覽“更新查詢”檢索的數據【操作實例10】n （6）執行更新數據的操作n （7）保存更新查詢歸納分析o 運行更新查詢一定要注意，每執行一次就會更新一次，如果是漲價，執行一次將漲價10。o 更新數據之前一定要認真瀏覽一下找出的數據是不是要更新的數據。4.創建查詢將查找的數據追加到指定表中o 問題的提出n 能不能將某個表中符合一定條件的數據添加到另一個表中呢？例如有兩個學生名單的表，能否合在一個表中呢？或者將其中的一

22、部分數據添加到另外一個表中呢？o 查詢對象可以解決這個問題，查詢對象可以將追加的數據按指定條件查找出來，再追加到指定的表中。這類查詢稱為追加查詢。【操作實例11】o 創建名稱為“添加學生名單”的追加查詢對象，該查詢用來將“ERP名單”中的數據追加到“學生名單”表中。【操作實例11】o 操作步驟：n （1）將excel表格文件“ERP概論名單”文件導入到“格達公司”數據庫，保存為“ERP名單”表對象。n （2）打開查詢設計視圖n （3）選擇查詢字段n （4）選擇查詢類型為“追加查詢”并確定追加的目的表【操作實例11】n （5）選擇追加數據的字段與目的表中對應的字段n （6）預覽查找到的數據【操作

23、實例11】n （7）執行追加數據的操作n （8）保存追加查詢歸納分析o （1）區分源表與目的表的字段n第1行“字段”為數據源表的字段，例如“ERP名單”表。n“追加到”單元格中顯示的字段為目的表中的字段，例如“學生名單”表。o （2）追加的條件n兩個表字段的數據類型、大小一致時才可以進行追加操作。如果兩個表字段數據類型、順序完全相同，可直接追加。n追加數據時還可以定義條件準則，有選擇的添加數據3.6 SQL查詢o SQL是Structured Query Language結構化查詢語言的縮寫，SQL是對存放在計算機數據庫數據進行組織、管理和檢索的一種工具，SQL是針對關系數據庫使用的。關系數據

24、庫都支持SQL。o 當用戶想要檢索數據庫中的數據時，可通過SQL語言發出請求，DBMS會對該SQL 請求進行處理并檢索所要求的數據，并將結果返回給用戶，此過程被稱作為數據庫查詢，這也就是數據庫查詢語言SQL這一名稱的來由。1、查詢語句selecto 查詢是SQL語言的核心，用于表達SQL查詢的select語句則是功能最強也是最為復雜的SQL語句，它從數據庫中檢索數據，并將查詢結果提供給用戶。n【例1】如果要列出“物品”表中物品編號、名稱、物品類型和制購類型的記錄，可用下面的select 語句：o select 物品編號,名稱,物品類型,制購類型 from 物品; n【例2】如果要列出物品表中所

25、有配件的物品編號、名稱、物品類型和制購類型的記錄，可用下面的select語句：o select 物品編號,名稱,物品類型,制購類型 from 物品where 物品類型 =配件;1、查詢語句selectn 【例3】如果要列出物品表中所有配件的物品編號、名稱、物品類型和制購類型的記錄，并按名稱排序，可用下面的Select語句：o select 物品編號,名稱,物品類型,制購類型 from 物品where 物品類型 =配件 order by 名稱;n 【例4】如果要按物品類型列出不同類型物品的總數，可用下面的Select語句：o select物品類型,制購類型 count(物品編號) as 物品總數

26、 from 物品 group by 物品類型;1、查詢語句selecto 分析一下Select 語句的結構，它可以由幾個子句構成：n select子句（查詢內容）n from子句（查詢對象）n where子句（查詢條件）n order by子句（排序方式）n group by子句（分組方式）1、查詢語句selecto 總結一下可得出Select 語句的語法格式：n select 列選項1，列選項2， from 表名1 表名2 where 條件表達式 order by排序選項ASCDESC group by 分組字段名;o Select 語句的功能：從數據庫表中檢索出滿足條件表達式要求的數據項。

27、1、查詢語句selecto Select 語句的說明1n （1）select子句說明由哪些列選項組成結果表，它放在select語句開始處，指定此查詢要檢索出的列名稱，這些列選項用“,”隔開，并按照從左到右的順序排列，這些列選項可能是以下項目：o 字段名o 常數o SQL表達式1、查詢語句selectn （2）from 子句列出查詢數據使用的表，它由關鍵字from后跟一組用逗號分開的表名組成。每個表名都代表一個提供該查詢數據源的表。這些表稱為此SQL語句的表源，因為它們是查詢的數據源。 n （3）Where子句告訴SQL查詢哪些行中的數據，這些行由“條件表達式”來確定。1、查詢語句selectn

28、 （4）order by子句將查詢結果按一列或多列中的數據排序。如果省略此子句，則查詢結果將是無序的。添加ASC屬性以升序（從小到大）排列，DESC屬性以降序(從大到小)排列。默認為升序。n （5）group by子句指定該查詢為分組總計查詢，即不是對每行產生一個查詢結果，而是以某個字段先將記錄分組，再對分組后的記錄進行總計給出其統計結果。1、查詢語句selectoSelect 語句的說明2 nSelect 語句中只有select子句和from 子句為必須有的，其他子句為可選項。oSelect 語句的說明3nWhere子句中的條件表達式，可以使用關系運算符、特殊運算符、邏輯運算符、函數組成，可

29、以構成各種靈活多樣不同的查詢結果。oSelect 語句的說明4nSelect子句中可以用“*”代表選取表中的所有字段，例如從“物品”表查詢所有配件的記錄，可使用下面的SQL語句：oselect * from 物品 where 物品類型”配件”;1、 查詢語句selecto Select 語句的說明5n（1）當查詢涉及兩個表時，稱為連接查詢。一般使用等值連接。即通過相同字段名進行連接，例如，學生.學號=學生選修課程.學號。n（2）多表查詢中要在字段名稱前加上表名，例如，學生.學號。例如要在“學生”表和“學生選修課程”表中，查詢所有學生的課程成績，可使用下面的SQL語句：o select 學生.姓

30、名，學生.班級，學生選修課程.課程號 學生選修課程.課程成績 from 學生，學生選修課程where學生.學號=學生選修課程.學號;2、在SQL視圖中創建SQL查詢 o 【操作實例12】創建名稱為“通過SQL查詢查找配件信息”的查詢對象，在“物品”表中查找“配件”物品有關“物品編號”、“名稱”、“物品類型”、“制購類型”信息，并按“名稱”排序。2、在SQL視圖中創建SQL查詢 o 【操作實例12】n 操作步驟：o （1）打開“格達公司”數據庫o （2）打開查詢設計視圖o （3）打開SQL視圖【操作實例12】o （4）輸入SQL語句o （5）保存查詢為“通過SQL查詢查找配件信息”o （6）瀏覽

31、查詢結果總結提高o 本章學習要點o 查詢對象的功能本章學習要點o 查詢對象的作用o 查詢對象的類型o 查詢準則o 創建查詢的方法o 在查詢中進行計算和添加計算字段o 操作已創建的查詢查詢對象的功能o 查找字段數據o 根據查詢條件查找數據o 通過查詢對數據進行處理o 生成新數據o 為創建其他對象打基礎查詢對象的類型o 選擇查詢o 參數查詢o 交叉表查詢o 操作查詢o SQL查詢o 其他類型的查詢查詢對象的操作o 查看查詢對象的結果o 編輯查詢對象的字段o 編輯查詢對象的數據源n 刪除字段列表n 添加表或查詢o 查詢結果的排序查詢準則o 查詢對象的主要工作是查出需要的數據。怎么才能讓數據庫幫你查找或計算出需要的數據呢？o 這需要制定一個讓計算機中的數據庫與人都懂得的描述用戶查詢要求的規則，一般稱為查詢準則。o 查詢準則一般分為兩種：簡單準則和復雜準則。 查詢準則o 1簡單準則n 簡單準則通過關系運算符、字段變量與常數值組成的關系表達式來描述，用來描述用戶的一個查