Transcript 投影片 1
資料庫管理 Access 2003 許欽嘉 老師 資料庫管理 第1章、資料庫概論與Access 2003 第2章、建立與管理Access資料庫 第3章、資料表的建立與設計 第4章、資料輸入、排序與篩選 第5章、資料關聯 資料庫管理 第6章、建立查詢 第7章、建立與使用表單 第8章、建立與使用報表 第9章、建立資料頁 第10章、Access 在web上的應用 第11章、與Office 2003其他軟體整合 第12章、好用的資料庫工具 附錄A、淺談巨集與模組 第六章 建立查詢 6-1 6-2 6-3 6-4 6-5 6-6 6-7 6-8 查詢簡介 選取查詢 自訂新查詢 設定查詢條件 交叉資料表查詢 參數查詢 動作查詢 SQL 查詢 6-1 查詢簡介 一個資料庫可以使用 查詢 來產生多樣 化的資料，其資料來源可以是一個或 多個的資料表，或者是另一個查詢。 查詢 和第4-6節提到的“資料篩選”在 功能上很類似，都是藉由設定適當的 條件，從資料表中將所要的資料找出 來 但是查詢的功能更彈性，使用者可以 靈活應用到所需的事務上。 單一資料表的查詢 多個資料表的查詢 6-1 查詢簡介 6-1-1 資料篩選與查詢 6-1-2 查詢的種類 6-1-3 查詢與資料表的關係 6-1-1 資料篩選與查詢 篩選：只能在目前的資料表中設定， 且無法單獨存在，沒有篩選出來的資 料只是暫時隱藏，只要移除篩選就會 全部呈現。 查詢：可以從多個資料表查詢資料， 並單獨儲存成查詢物件，且可再做進 一步的查詢。查詢的結果是動態的， 隨著資料表的異動而跟著更新。 6-1-2 查詢的種類 1. 選取查詢：輸入條件後，由一個或多個 資料表中，將符合條件的記錄顯示出來 2. 交叉資料表查詢：如樞紐分析表，用於 數值資料的統計或分析。 3. 參數查詢：使用者輸入不同參數，依所 輸入的參數值查看符合條件的結果。 4. 動作查詢：可編輯資料表中的記錄，如 新增、刪除、更新記錄…。 5. SQL查詢：使用SQL語法來建立查詢。 6-1-3 查詢與資料表的關係 查詢是由資料表過濾擷取而來的。 查詢是虛擬資料表可再查詢。 資料表或查詢，可做為表單、報表或 資料頁等物件的參照來源。 若要查詢多個資料表，則資料表間必 須設定關聯欄位。 可在查詢設計視窗中建立暫時性關聯 若選用到具有永久性關聯的資料表， Access會自動套上已有的關聯。 第六章 建立查詢 6-1 6-2 6-3 6-4 6-5 6-6 6-7 6-8 查詢簡介 選取查詢 自訂新查詢 設定查詢條件 交叉資料表查詢 參數查詢 動作查詢 SQL 查詢 6-2 選取查詢 6-2-1 簡單查詢精靈 查看部份欄位的所有記錄 6-2-2 尋找重複資料查詢精靈 查看資料表中重複值的資料 6-2-3 尋找不吻合資料查詢精靈 查詢A與B資料表的關聯中，不 吻合(沒適當對應)的(孤兒)記錄 A與B資料表必須先建立關聯 第六章 建立查詢 6-1 6-2 6-3 6-4 6-5 6-6 6-7 6-8 查詢簡介 選取查詢 自訂新查詢 設定查詢條件 交叉資料表查詢 參數查詢 動作查詢 SQL 查詢 6-3 自訂新查詢 6-3-1 查詢設計視窗介紹 6-3-2 自訂查詢條件 6-3-3 查詢設計視窗的基本操作 6-3-1 查詢設計視窗介紹 來源區 條件區 來源區 條件區 6-3 自訂新查詢 若選擇同一列、兩個以上的欄位做為查 詢條件，則必須同時滿足兩個欄位條件 故同一列的條件句為「且」、「And」 若選擇不同列同一欄位，則滿足其中一 個欄位即可，因為不可能同時滿足。 故不同列的條件句為「或」、「Or」 方便輸入：按右鍵顯示比例 (放大) 第六章 建立查詢 6-1 6-2 6-3 6-4 6-5 6-6 6-7 6-8 查詢簡介 選取查詢 自訂新查詢 設定查詢條件 交叉資料表查詢 參數查詢 動作查詢 SQL 查詢 6-4 設定查詢條件 6-4-1 文字欄位 6-4-2 數值欄位 6-4-3 使用運算式建立幫手 6-4-4 使用計算欄位 6-4-5 合計的應用 6-4-1 文字欄位 設定文字欄位的準則：＊、？、＃ –＊：代表所有字元 –？：代表單一字元 –＃：代表單一數字 準則範例 ”志凌”OR”資訊” NOT”志凌資訊” Is Null Is Not Null Like “ ” Like “＊String” Like “String＊” Like “*String*” Like “??公司” = #2003/09/28# 說明 尋找”志凌”或”資訊”的文字 尋找除了”志凌資訊”之外的文字 尋找該欄位無資料者 尋找非空白資料考 尋找空字串 尋找以String為結尾的字串 尋找以String為開頭的字串 尋找含有String的字串 尋找只有二個字的公司名字 尋找日期等於2003/09/28的記錄 Between #2003/09/01# 日期介於2003/09/01~2003/09/30 And #2003/09/30# 6-4-3 使用運算式建立幫手 6-4-4 使用計算欄位 經由運算產生的欄位，只存在目前的查 詢，並沒有實際存放在的資料表中。 計算欄位所參考的欄位必須是可計算的 欄位類型，如數字、貨幣等。 新增的欄位名稱：[欄位A]＊[欄位B] 欄位名稱預設值為Expr1(建議自行命名) Between #2003/09/29# And #2003/10/9# Between Data( ) And Data( )-10 6-4-5 合計的應用 合計也是針對數值欄位作計算，但不 同的是它可以依某特定的”群組”作 數值計算。 Access提供的計算方式有：總計、平 均、最大值、最小值、筆數、標準差、 變異數、第一筆、最後一筆… 計算方式也可自行定義合計公式。 在合計的應用中，至少必須有一個群 組欄位、一個計算欄位。 第六章 建立查詢 6-1 6-2 6-3 6-4 6-5 6-6 6-7 6-8 查詢簡介 選取查詢 自訂新查詢 設定查詢條件 交叉資料表查詢 參數查詢 動作查詢 SQL 查詢 6-5 交叉資料表查詢 將數值資料經過整理分析後，建立具有 欄、列標題且於中間交錯的儲存格中， 顯示與對應欄、列有關的資料。 單純的交叉資料表查詢，含有三個欄位 欄和列標題可以再擁有子標題，而得到 多層次的交叉資料表(最多三個層次)。 又稱為交叉分析或樞紐分析 實際案例—2004民意調查 選項 % 陳呂配 38 連宋配 41 未表態 21 總 傾民 傾國 傾親 無傾 計 進黨 民黨 民黨 向 陳呂配 38 91 6 3 27 連宋配 41 3 86 94 28 未表態 21 5 8 3 45 選項 實際案例—2004民意調查 選項 總 大台 桃竹 中彰 雲嘉 計 北 苗 投 南 陳呂配 38 34 30 39 46 連宋配 41 49 48 35 32 未表態 21 17 22 27 23 高屏 宜花 澎 東 48 25 35 38 17 37 第六章 建立查詢 6-1 6-2 6-3 6-4 6-5 6-6 6-7 6-8 查詢簡介 選取查詢 自訂新查詢 設定查詢條件 交叉資料表查詢 參數查詢 動作查詢 SQL 查詢 6-6 參數查詢 前面幾節所介紹的查詢，都是依照 我們事先所定義的準則，找到所要 的查詢結果。 但如果我們希望依據同一準則，使 用不同的參數 (文字、數字、日期) 查詢，以使得到不同的結果，此時 就需要使用 參數查詢，例如：查詢 每個月的訂單、查詢公司名稱…… 第六章 建立查詢 6-1 6-2 6-3 6-4 6-5 6-6 6-7 6-8 查詢簡介 選取查詢 自訂新查詢 設定查詢條件 交叉資料表查詢 參數查詢 動作查詢 SQL 查詢 6-7 動作查詢 動作查詢可以有效率的直接對資 料表中的記錄做新增、修改、刪 除等動作，或是再產生新資料表 在查詢設計視窗中建立，直接修 改資料表中的資料。 6-7 動作查詢 6-7-1 產生資料表查詢 複製現有資料表資料，到全新的資料表中 6-7-2 更新查詢 (修改) 整批更新所指定的資料表內的資料 6-7-3 新增查詢 將符合條件的記錄，新增到現有的資料表 6-7-4 刪除查詢 直接刪除符合條件的資料 6-7-2 更新查詢 IIf (expr, truepart, falsepart) – expr 這是要求值的運算式 – truepart當expr為true時，傳回的值或運算式 – falsepart當expr為false時，傳回的值或運算式 IIF ([數量之總計]<350, [定價]*0.5,[定價]) 第六章 建立查詢 6-1 6-2 6-3 6-4 6-5 6-6 6-7 6-8 查詢簡介 選取查詢 自訂新查詢 設定查詢條件 交叉資料表查詢 參數查詢 動作查詢 SQL 查詢 6-8 SQL 查詢 結構化查詢語言(Structured Query Language, SQL) 它是一種標準的資料庫查詢語言，目前任何的資料 庫系統均支援此種語言。 Access中大多數的查詢，都可以使用各式查詢精靈， 或是在設計檢視視窗中建立，而這些建立好的查詢， Access會自動將其編譯成SQL指令。 當使用者點選 查詢/執行 指令，或按一下工具列上 的 執行 工具鈕時，系統即是執行對應的SQL指令 使用者可以點選 檢視/SQL檢視 指令，來查看所建 立之查詢的對應SQL指令。 課堂練習(開啟Ch05_結果.mdb範例檔) 1. 簡單查詢精靈 客戶!公司名稱、訂單!訂單日期、訂單明細!數量 摘要選項：總計、日期分組：年 檔案名稱：客戶訂單數量統計 2. 尋找重複資料查詢精靈 資料表：客戶 包含重複值的欄位：客戶編號 附加的查詢欄位：公司名稱 檔案名稱：尋找 客戶 的重複值 3. 尋找不吻合資料查詢精靈 資料表：客戶 vs. 資料表：訂單 符合的欄位：客戶編號、查看公司名稱 檔案名稱：客戶 與不符 訂單 自訂新查詢 4. 請找出所有地址為台北縣(或台北市)而且訂 單數量>=10的客戶之公司名稱、地址、數 量等資料，檔名為台北地區重要客戶 5. 找出日期為2003/4/1至4/30的公司名稱、訂 單日期、產品名稱，檔名為4月份訂單 6. 增加欄位訂單金額: [定價]*[數量]，並做遞 減排序，欄位屬性設為貨幣，並呈現公司 名稱、訂單日期、訂單金額等欄位，檔名 為使用計算欄位應用。 7. 以公司名稱做為群組依據、以數量做為計 算欄位(總計)，檔名為合計的應用。 8. 交叉資料表：選取使用計算欄位應 用查詢物件，選取欄為公司名稱； 列為訂單日期(以季來區隔)；計算 欄位為訂單金額(函數為合計)，並 勾選列合計，檔名為使用計算欄位 應用_交叉資料表。(請記得將設計 格式修改為format) 9. 參數查詢：設定日期參數，依開始 日期及截止日期來輸入參數，並顯 示客戶名稱、訂單日期等欄位，檔 名為參數查詢應用。 10. 動作查詢 請利用查詢產生一個內容為2003年1 月至3月份的訂單之新資料表，呈現 公司名稱、訂單日期、產品名稱，資 料表及查詢之檔名為第一季訂單。 查詢並呈現產品資料表的所有欄位， 將訂價>=20000的產品打八折，檔案 名稱為更新查詢應用。