資料庫管理(Access 2003) 第五章 利用查詢來 統計與分析資料 許欽嘉 老師

第五章 利用查詢來統計與分析資料 5-1 認識查詢物件 5-2 5-2 查詢精靈 5-5 5-3 選取查詢 5-12 5-1 認識查詢物件 5-2 5-1-1 何謂查詢物件 5-2 5-1-2 查詢與資料表的關係 5-2 5-1-3 查詢物件的種類 5-3 5-2 查詢精靈 5-5 5-2-1 用簡單查詢精靈建立查詢 5-5 5-2-2 用尋找重複精靈建立查詢 5-9 5-3 選取查詢 5-12 1

5-1 認識查詢物件 資料表物件之篩選功能： 查詢物件： 只能在目前存在的單一資料表中設定 無法單獨存在，或更進一步的查詢或表單 資料只是暫時隱藏，移除篩選就全部呈現 查詢物件： 可以從多個資料表中來查詢資料 可以單獨儲存成查詢物件，做進一步查詢 查詢結果隨著資料表的異動而動態的更新 2

5-1 認識查詢物件

5-1-2 查詢與資料表的關係 資料表之間要有關聯存在，才能擷取多個資料表 資料表間已設定關聯，查詢後會自動顯示關聯性 若未建立關聯，會自動以該欄位建立臨時性關聯 2

5-1-3 查詢物件的種類 選取查詢 參數查詢 交叉查詢 動作查詢 SQL 查詢 3

第五章 利用查詢來統計與分析資料 5-1 認識查詢物件 5-2 5-2 查詢精靈 5-5 5-3 選取查詢 5-12 5-1 認識查詢物件 5-2 5-1-1 何謂查詢物件 5-2 5-1-2 查詢與資料表的關係 5-2 5-1-3 查詢物件的種類 5-3 5-2 查詢精靈 5-5 5-2-1 用簡單查詢精靈建立查詢 5-5 5-2-2 用尋找重複精靈建立查詢 5-9 5-3 選取查詢 5-12 5

5-2 查詢精靈 5-2-1 簡單查詢精靈 5-2-2 尋找重複資料查詢精靈 補充 尋找不吻合資料查詢精靈 查看部份欄位的所有記錄 查看A資料表中重複值的資料 補充 尋找不吻合資料查詢精靈 查詢A與B資料表的關聯中，不 吻合(沒適當對應)的(孤兒)記錄 A與B資料表必須先建立關聯 5

5-2-1 使用簡單查詢精靈建立查詢 開啟：開心商行-精靈.mdb到查詢物件 5

5-2-1 使用簡單查詢精靈建立查詢 選單一資料表：產品編號、產品、供應商 嘗試選擇不同(跨資料表)的欄位 加選「單價」(有數值資料的欄位) 查詢精靈無法自動設定資料表間的關聯 使用設計檢視建立查詢才會自動建關聯 加選「單價」(有數值資料的欄位) 詳細(顯示每筆記錄的每個欄位) 摘要(總計、平均、最大、最小值、個數) 5

5-2-2 使用尋找重複的精靈來建立查詢 每個商品都有供應商，有些商品的供應商是相同的 透過尋找重複的查詢精靈，來找出哪些供應商是重複出現的重要的供應商(或找重要的大客戶) 9

補充、尋找不吻合資料查詢精靈 前提：A與B資料表必須先建立關聯 有訂單卻找不到客戶可以付錢孤兒訂單

第五章 利用查詢來統計與分析資料 5-1 認識查詢物件 5-2 5-2 查詢精靈 5-5 5-3 選取查詢 5-12 5-1 認識查詢物件 5-2 5-1-1 何謂查詢物件 5-2 5-1-2 查詢與資料表的關係 5-2 5-1-3 查詢物件的種類 5-3 5-2 查詢精靈 5-5 5-2-1 用簡單查詢精靈建立查詢 5-5 5-2-2 用尋找重複精靈建立查詢 5-9 5-3 選取查詢 5-12 12

5-1-3 查詢物件的種類 選取查詢 參數查詢 交叉查詢 動作查詢 SQL 查詢 3

5-3-1 使用設計檢視方式來新增查詢 來源區 條件區

5-3-2 查詢準則與運算式的認識 20

5-3-2 查詢準則與運算式的認識 準則範例 說明 ”志凌”Or”資訊” 尋找”志凌”或”資訊”的文字 Not”志凌資訊” 尋找除了”志凌資訊”之外的文字 Is Null 尋找該欄位無資料者 Is Not Null 尋找非空白資料考 Like “ ” 尋找空字串 Like “＊String” 尋找以String為結尾的字串 Like “String＊” 尋找以String為開頭的字串 Like “*String*” 尋找含有String的字串 Like “??公司” 尋找只有二個字的公司名字 = #2003/09/28# 尋找日期等於2003/09/28的記錄 Between #2003/09/01# And #2003/09/30# 日期介於2003/09/01~2003/09/30

5-3-2 查詢準則與運算式的認識 ？：表示所要尋找的是任一字元 ＃：表示所要尋找的是任一數字 ＊：表示要尋找任何長度的字串(含0) *在前：*怡最後一個字為怡(不限長度) *在後：怡*找第一個字為怡(不限長度) 前後*：*怡*出現在任何處(不限長度) 前後?：?怡?怡在中間 (僅限三個字) 20

5-3-4 加入準則 若選擇同一列、兩個以上的欄位做篩選條件 若選擇不同列相同或不同欄位 「且」、「And」，必須同時滿足兩個欄位條件 「或」、「Or」，則滿足其中一個欄位即可 開啟：餐管一甲-動作查詢.mdb到查詢物件 單一條件：找出姓 「張」 的同學方式 多條件(And)：找出男同學而且有學生保險的 多層次條件篩選(Or)：找出姓李或身高 177 公分 21

5-3-5 加入總計與平均欄位 開心商行-精靈.mdb資料表第一季銷售數量 經由運算產生的欄位，只存在目前的查詢，並沒有 實際存放在的資料表中 計算欄位所參考的欄位必須是可計算的欄位類型， 如數字、貨幣等 新增的欄位名稱：[欄位A]＋[欄位B] 第一季總銷售量:[一月銷售量]+[二月銷售量]+[三月銷售量] 第一季平均銷售量: [第一季總銷售量]／3 設定小數位數：設計檢視／屬性／一般／小數位數／ 2 23

小技巧(一) 使用建立幫手來設定運算式 57

小技巧(二) 設定要顯示的資料筆數 60

5-3-6 加入合計 合計也是針對數值欄位作計算，但不同的 是它可以依某特定的「群組」作數值計算。 合計也是針對數值欄位作計算，但不同的 是它可以依某特定的「群組」作數值計算。 Access提供的計算方式有：總計、平均、 最大值、最小值、筆數、標準差、變異數、 第一筆、最後一筆… 計算方式也可自行定義合計公式。 在合計的應用中，至少必須有一個群組欄 位、一個計算欄位。 26

26

5-1-3 查詢物件的種類 選取查詢 參數查詢 交叉查詢 動作查詢 SQL 查詢 3

5-4 參數查詢 前面都是依我們事先定義的準則， 找到所要查詢的結果 前面都是依我們事先定義的準則， 找到所要查詢的結果 參數查詢：可以依據同一準則，使 用不同的參數 [文字、數字、日期] 查詢，以使得到不同的結果 例如：查詢每個月的訂單、查詢公 司名稱、每月的客戶生日(壽星)… 28

5-1-3 查詢物件的種類 選取查詢 參數查詢 交叉查詢 動作查詢 SQL 查詢 3

5-5 交叉查詢 交叉查詢又稱交叉分析或樞紐分析 將數值資料(放中間)，顯示與對應欄、 列有關的資料 單純的交叉資料表查詢，含有三個欄位 將數值資料(放中間)，顯示與對應欄、 列有關的資料 單純的交叉資料表查詢，含有三個欄位 欄和列標題可以再擁有子標題，而得到 多層次的交叉資料表(最多三個層次)。 32

實際案例—2004民意調查 選項 % 陳呂配 38 連宋配 41 未表態 21 選項 總計 傾民進黨 傾國民黨 傾親民黨 無傾向 陳呂配 91 6 3 27 連宋配 41 86 94 28 未表態 21 5 8 45

實際案例—2004民意調查 選項 總計 大台北 桃竹苗 中彰投 雲嘉南 高屏澎 宜花東 陳呂配 38 34 30 39 46 48 25 連宋配 41 49 35 32 未表態 21 17 22 27 23 37

5-1-3 查詢物件的種類 選取查詢 參數查詢 交叉查詢 動作查詢 SQL 查詢 3

5-6 動作查詢 5-6-1 產生資料表：複製資料，產生全新的資料表 5-6-2 新增：將A資料表的資料，新增到B資料表 5-6-3 刪除：直接刪除現有資料表符合條件的資料 5-6-4 更新：整批修改所指定的資料表內的資料 35

5-6-1 產生資料表查詢 可結合多個資料表的欄位，產生新的資料表 甚至可以再加入虛擬欄位或是一些基本計算 製作出住在台北的男生資料表 (單一資料表產 生資料表查詢) 各科小考成績組合成一張新的資料表 (多資料 表產生資料表查詢) 36

5-6-2 新增查詢 可將甲資料表符合的記錄，貼附到乙資料表 使用此查詢有 3 點規則要注意： 兩個資料表的欄位及格式必須一模一樣(文字長 度)，且新增的資料會出現在資料表的最底部 索引值 (鍵值) 不能有重複現象 新增查詢還可以加入準則的判斷 45

5-6-3 刪除查詢 可在現有資料表，動態刪除特定條件的資料 準則的設定採取反向的設定方式 在設計檢視畫面中，會多出一個 刪除列 之前的查詢準則都是要找到符合的記錄 刪除查詢則是要消滅不符合準則的記錄 在設計檢視畫面中，會多出一個 刪除列 49

5-6-4 更新查詢 可在現有資料表，動態更新特定條件的資料 在設計檢視模式中，會多出一個 更新列 53

5-1-3 查詢物件的種類 選取查詢 參數查詢 交叉查詢 動作查詢 SQL 查詢 3

補充、SQL 查詢 結構化查詢語言(Structured Query Language) Access大多數查詢，皆可自動編譯成SQL指令 點選 查詢／執行 指令，或按一下工具列上的 執行 工具鈕時，系統就執行對應的SQL指令 使用者可以點選 檢視／SQL檢視 指令，來查 看所建立之查詢的對應SQL指令