Microsoft PowerPoint - QuerySQL_961_ pptx

Size: px

Start display at page:

Download "Microsoft PowerPoint - QuerySQL_961_ pptx"

字押施
4 years ago
Views:

1 << 會計資訊系統課程講義 >> Access 2007 DBMS ~ 查詢及 SQL 語法 ~ 周國華屏東商業技術學院會計系 2007/11/26

2 查詢物件查詢 (query) 是 Access 2007 的六種資料庫物件之一, 目的在將常用的查詢條件預存起來, 以備後續使用查詢物件是由查詢條件所構成, 但在點選已儲存之查詢物件後, 使用者看到的是一份與資料表極為類似的查詢結果表格此份表格 ( 在某些 DBMS 中稱為視觀表 or View ) 並非真正的資料表, 其內容資料並非儲存在該表格中, 而是動態擷取自來源資料表或其他查詢物件查詢的結構如下 : 資料表 A 資料表 B 透過查詢 D 產生視觀表查詢 C 註 : 來源物件可多可少屏東商業技術學院周國華 2

3 建立查詢物件在 Access 2007 中建立查詢物件, 可從下列方式二選一 : 使用查詢精靈 : 由查詢精靈協助建立查詢物件使用查詢設計 : 以手動方式建立查詢物件說明範例 : 根據前一份講義建立的出版社資料庫, 包含以下四份資料表訂單編號訂單日期客戶編號收款條件客戶編號客戶名稱送貨地址客戶電話書籍編號書籍名稱銷貨定價訂單編號書籍編號訂購數量查詢範例 : 查出每筆訂單的內容, 依次列示訂單編號客戶名稱書籍名稱訂購數量及銷貨定價等五個欄位屏東商業技術學院周國華 3

4 使用查詢精靈按下功能區的建立索引標籤, 再按下其他群組的按鈕, 在新增查詢對話窗中選擇簡單查詢精靈, 按確定在簡單查詢精靈的資料表 / 查詢下拉式選單中選取資料表 : 訂單屏東商業技術學院周國華 4

5 在 ( 上左圖 ) 可用的欄位中, 選取訂單編號欄位 ( 上右圖 ) 然後依序從客戶書籍及訂單 - 條列 - 書籍資料表中選取客戶名稱書籍名稱訂購數量及銷貨定價等欄位 ( 右圖 ) 按完成屏東商業技術學院周國華 5

[ 客戶編號 ] = 訂單.[ 客戶編號 ]) INNER JOIN [ 訂單 - 條列 - 書籍 ] ON 訂單.

6 右圖為已完成之訂單內容查詢物件, 表格內所有欄位之資料均動態擷取自前述各資料表在資料工作表檢視視窗的右下角有等五個按鈕, 按下 SQL 按鈕後, 即可顯示此一查詢物件的 SQL 語法 SQL 語法如下 : SELECT 訂單. 訂單編號, 客戶. 客戶名稱, 書籍. 書籍名稱, [ 訂單 - 條列 - 書籍 ]. 訂購數量, 書籍. 銷貨定價 FROM 書籍 INNER JOIN (( 客戶 INNER JOIN 訂單 ON 客戶.[ 客戶編號 ] = 訂單.[ 客戶編號 ]) INNER JOIN [ 訂單 - 條列 - 書籍 ] ON 訂單.[ 訂單編號 ] = [ 訂單 - 條列 - 書籍 ].[ 訂單編號 ]) ON 書籍.[ 書籍編號 ] = [ 訂單 - 條列 - 書籍 ].[ 書籍編號 ]; 屏東商業技術學院周國華 6

7 使用查詢設計按下功能區的建立索引標籤, 再按下其他群組的按鈕在顯示資料表視窗中, 新增全部資料表, 畫面如下 : 屏東商業技術學院周國華 7

8 在右上圖下方的欄位中, 可透過下拉式選單選取欲顯示的欄位名稱 ; 或者, 可將滑鼠指標指向上方資料表的特定欄位名稱, 然後按住滑鼠左鍵, 以拖曳方式拉至下方特定欄位上放開, 即可選定該欄位已完成查詢設定的五個欄位如右下圖然後, 選按功能區設計索引標籤內的按鈕, 即出現查詢結果 ( 下圖 ) 存檔時, 再將查詢物件改為較符合語意內涵的名稱 (: 訂單內容查詢 ) 屏東商業技術學院周國華 8

9 查詢的種類 Access 2007 內的查詢共有四種類別 : 選取查詢 : 是最常見的查詢類型, 可從一個或多個資料表中擷取資料, 並可新增欄位分組統計設定條件過濾資料等等前述的訂單內容查詢範例, 即屬於選取查詢參數查詢 : 執行時會出現對話窗讓使用者輸入參數, 再根據參數值進行查詢交叉查詢 : 可產生同時具有欄標題及列標題的資料工作表動作查詢 : 此類查詢的目的是在對資料表內的多筆記錄進行變更或移除, 下達執行指令後會將結果直接套用在指定的資料表內, 且不可回復! * 本講義將針對前三種查詢類別舉例說明其建立步驟及功能含意屏東商業技術學院周國華 9

10 選取查詢查詢範例 : 從上述出版社資料庫中, 查出每筆訂單的總金額, 依次列示訂單編號客戶名稱訂購總額等欄位首先, 在前述客戶訂單查詢物件中, 增加小計欄 : 在銷貨定價欄位旁之空白欄位, 按滑鼠右鍵, 選擇建立幫手屏東商業技術學院周國華 10

11 在運算式建立幫手窗格中, 輸入 : 小計 :[ 訂購數量 ]*[ 銷貨定價 ] 按確定此時, 訂單內容查詢物件即增加小計欄位, 右下圖為執行此項查詢之結果以右圖之查詢內容為基礎, 繼續用查詢精靈的簡單查詢精靈選項建立一個新的訂單總額查詢物件 : 屏東商業技術學院周國華 11

12 在簡單查詢精靈對話窗中, 選取訂單編號客戶名稱及小計三個欄位按下一步在下一個頁面中選擇摘要, 並點選摘要選項在摘要選項對話窗中勾選總計, 按確定, 回到查詢精靈再按下一步屏東商業技術學院周國華 12

13 在右圖對話窗格中輸入訂單總額查詢, 做為此查詢物件的名稱按完成右下圖為已完成之訂單總額查詢物件, 但最後一個欄位名稱為小計之總計, 不符合原先設定的欄位名稱將畫面轉至設計檢視, 滑鼠指標移至小計之總計欄位, 按滑鼠右鍵, 選按屬性屏東商業技術學院周國華 13

14 在屬性表的標題欄中輸入訂購總額按 X 關閉屬性表轉回工作表檢視畫面, 此時訂單總額查詢物件的三個欄位名稱就完全符合原先設計要求了本查詢範例心得 : 查詢物件的資料來源物件, 可包含資料表及其他查詢物件小計總計這種可推論屬性不必儲存在資料表內, 透過查詢方式即可在需要時動態取得最新數值屏東商業技術學院周國華 14

15 參數查詢查詢範例 : 從上述出版社資料庫中, 查出特定期間的訂單資料, 依次列示訂單編號訂單日期客戶名稱書籍名稱及訂購數量等欄位首先, 開啟查詢精靈的簡單查詢精靈對話窗, 選取訂單編號訂單日期客戶名稱書籍名稱及訂購數量等欄位按下一步兩次屏東商業技術學院周國華 15

16 在查詢標題窗格中, 輸入特定期間訂單查詢, 並選擇修改查詢的設計, 按完成在設計檢視畫面中, 選按功能區顯示 / 隱藏群組的參數按鈕在查詢參數窗格中, 輸入開始日期及結束日期兩個參數, 並將資料類型設定為日期時間按確定屏東商業技術學院周國華 16

17 在訂單日期欄位的準則屬性中, 輸入 : Between [ 開始日期 ]A And d[ 結束日期 ] 按按鈕, 系統會先後出現兩個要求輸入參數值的對話窗假設分別輸入 2007/11/10 及 2007/11/15, 按確定後之查詢結果如下圖 : 屏東商業技術學院周國華 17

18 交叉查詢查詢範例 : 從上述出版社資料庫中, 查出每家書局訂購每本書的數量, 查詢結果以書籍名稱做為列標題, 以客戶名稱做為欄標題 * 注意事項 : 交叉查詢的來源物件僅限於一個, 因此, 若個別資料表或查詢物件的欄位內容不符需求, 必須先新增一個包含所需欄位的查詢物件首先, 開啟查詢精靈後, 在新增查詢對話窗中, 選擇交叉資料表查詢精靈, 按確定屏東商業技術學院周國華 18

19 在交叉資料表查詢精靈對話窗的檢視窗格中, 選擇查詢, 然後在上方顯示的查詢物件中, 選擇查詢: 訂單內容查詢按下一步在可用的欄位中, 選擇書籍名稱做為列標題按下一步繼續選擇客戶名稱做為欄標題按下一步屏東商業技術學院周國華 19

20 在欄位窗格中, 選擇訂購數量做為交叉表的欄列交點數值 ; 在函數窗格中, 選擇合計在圖中的你要為每列做合計嗎? 詢答中, 勾選是, 加上列合計按下一步將查詢命名為客戶訂購書籍數量交叉查詢, 按完成已完成之交叉查詢表如下圖屏東商業技術學院周國華 20

21 SQL 語法彙整前述訂單內容查詢物件的 SQL 語法如下 : SELECT 訂單. 訂單編號, 客戶. 客戶名稱, 書籍. 書籍名稱, [ 訂單 - 條列 - 書籍 ]. 訂購數量, 書籍. 銷貨定價,[ 訂購數量 ]*[ ][ 銷貨定價 ] AS 小計 FROM 書籍 INNER JOIN (( 客戶 INNER JOIN 訂單 ON 客戶. 客戶編號 = 訂單. 客戶編號 ) INNER JOIN [ 訂單 - 條列 - 書籍 ] ON 訂單. 訂單編號 =[ 訂單 - 條列 - 書籍 ]. 訂單編號 )ON 書籍. 書籍編號 =[ 訂單 - 條列 - 書籍 ]. 書籍編號 ; 前述訂單總額查詢物件的 SQL 語法如下 : SELECT DISTINCTROW [ 訂單內容查詢 ]. 訂單編號, [ 訂單內容查詢 ]. 客戶名稱, Sum([ 訂單內容查詢 ]. 小計 ) AS [ 小計之總計 ] FROM [ 訂單內容查詢 ] GROUP BY [ 訂單內容查詢 ]. 訂單編號,[ 訂單內容查詢 ]. 客戶名稱 ; 屏東商業技術學院周國華 21

22 SQL 語法彙整.. 前述特定期間訂單查詢物件的 SQL 語法如下 : PARAMETERS 開始日期 DateTime, 結束日期 DateTime; SELECT 訂單. 訂單編號, 訂單. 訂單日期, 客戶. 客戶名稱, 書籍. 書籍名稱, [ 訂單 - 條列 - 書籍 ]. 訂購數量 FROM 書籍 INNER JOIN (( 客戶 INNER JOIN 訂單 ON 客戶.[ 客戶編號 ] = 訂單.[ 客戶編號 ]) INNER JOIN [ 訂單 - 條列 - 書籍 ] ON 訂單.[ 訂單編號 ] = [ 訂單 - 條列 - 書籍 ].[ ][ 訂單編號 ]) ON 書籍.[ 書籍編號 ] = [ 訂單 - 條列 - 書籍 ].[ ][ 書籍編號 ] WHERE ((( 訂單. 訂單日期 ) Between [ 開始日期 ] And [ 結束日期 ])); 前述客戶訂購書籍數量交叉查詢物件的 SQL 語法如下 : TRANSFORM Sum([ 訂單內容查詢 ][ ].[ 訂購數量 ]) AS 訂購數量之總計 SELECT [ 訂單內容查詢 ].[ 書籍名稱 ], Sum([ 訂單內容查詢 ].[ 訂購數量 ]) AS [ 合計訂購數量 ] FROM [ 訂單內容查詢 ] GROUP BY [ 訂單內容查詢 ].[ 書籍名稱 ] PIVOT [ 訂單內容查詢 ].[ 客戶名稱 ]; 屏東商業技術學院周國華 22

Microsoft PowerPoint - QueryForm_ [相容模式]

Microsoft PowerPoint - QueryForm_ [相容模式] > Access 2007 DBMS ~ 查詢 SQL 語法及表單 ~ 周國華屏東商業技術學院會計系初版 :2007/11/25 本次修正 :2011/11/26 智慧財產權聲明本文件係由周國華老師獨自撰寫, 除引用之概念屬於原文作者外, 其餘文字及圖形內容之智慧財產權當然屬於周老師獨有任何機構或個人, 在未取得周老師同意前, 不得直接以本文件做為學校研究機構