第 9 章 查詢與關聯.

Presentation on theme: "第 9 章 查詢與關聯."— Presentation transcript:

1 第 9 章 查詢與關聯

2 本章重點 9-1 準備本章的範例資料表 9-2 什麼是『查詢』？ 9-3 『查詢』的用途 9-4 以手動方式建立查詢 9-5 子資料工作表
9-6 資料表間的『永久性關聯』 9-7 善用『查閱欄位』 9-8 在『資料庫關聯圖』視窗設定永久性關聯 9-9 測試設定永久性關聯後的效果

3 9-1 準備本章的範例資料表 前幾章所使用的書籍訂單資料表範例並不適合本章的應用, 所以我們又重新規劃了一個新的書籍訂單資料庫, 在其中建立了 4 個資料表並填入許多的資料, 以便做一次較為正確的示範。這些資料表的結構及關聯如下圖所示：

4 準備本章的範例資料表 現在, 請讀者另外建立一個新的書籍訂單資料庫, 然後將書附光碟 "Ch09範例資料.accdb" 中的資料表匯入此資料庫。 請在啟動 Access 程式, 出現快速入門視窗時如下操作：

5 準備本章的範例資料表

6 準備本章的範例資料表

7 準備本章的範例資料表 接下來, 請您將 Ch09 範例資料.accdb 的 4 個資料表全部匯入。匯入資料表的方式和4-4 頁的內容相同, 若您不熟悉匯入的方法, 請您自行參考該頁。 成功匯入資料表後, 我們所來看看這 4 個資料表的內容：

8 準備本章的範例資料表

9 準備本章的範例資料表

10 準備本章的範例資料表 注意！這些匯入的資料表目前尚未建立任何關聯, 稍後我們會為您介紹如何建立『 暫時性』及『永久性』的關聯。

11 9-2 什麼是『查詢』？ 『查詢』(query) 就是一種萃取資料的方法, 它可以將我們想要的各種資料, 由一或多個資料表中選取出來, 必要時並做一些排序、計算或統計, 然後將結果放入虛擬的資料表中供我們使用。 單一資料表的查詢 多個資料表的查詢

12 單一資料表的查詢 查詢可以只從一個資料表中萃取資料, 就像是我們以前使用過的排序、篩選功能, 其實都是利用查詢來完成的。
以下是由書籍資料表中萃取資料的查詢：

13 單一資料表的查詢

14 多個資料表的查詢 經由資料表間的關聯, 我們也可以利用查詢從多個資料表中萃取出相關的資料, 例如：

15 多個資料表的查詢

16 9-3 『查詢』的用途 查詢出來的結果, 可以被當成是一個『 資料表』 來看, 所以我們可以對其中的記錄做新增、修改與刪除等動作, 也可以將查詢用來做為表單、報表或資料頁的資料來源, 甚至還可再拿來做為另一個查詢的對象呢！ 由此觀之, 經由查詢功能, 我們可以將資料庫中的各類資料加以萃取、計算, 然後再以各式各樣不同的面貌呈現出來。

17 『查詢』的用途

18 查詢本身不存放資料 查詢其實是一個『虛擬』的資料表, 所以當我們對它做編輯時, 所做的更動是自動地存回資料表中, 而不是存放在查詢中！換句話說, 查詢只是一個功能而已, 它本身並不存放任何的資料。 因此, 當我們每次啟動查詢時, 系統都會重新到資料表中去取出資料, 如此才能反應出最新的資料內容。

19 9-4 以手動方式建立查詢 雖然 Access 提供好幾種查詢精靈來幫我們建立各式各樣的查詢, 但為了讓各位能實際體會,接下來我們要利用最基本的方式, 來分別建立『 單一資料表』與『 多資料表』的查詢。 建立單一資料表的查詢 什麼是 SQL 檢視？ 建立多資料表的查詢

20 建立單一資料表的查詢 首先我們來看看如何建立查詢來找出單價小於 500 元的書籍, 並依單價做遞增排序：

21 建立單一資料表的查詢

22 建立單一資料表的查詢

23 建立單一資料表的查詢

24 建立單一資料表的查詢

25 建立單一資料表的查詢 請注意, 檢視鈕的功能會隨著您目前操作的資料庫物件而變化。
例如操作查詢時即用來切換查詢的設計檢視、資料工作表檢視、SQL 檢視、樞紐分析表檢視、及樞紐分析圖檢視視窗；而當我們在操作資料表時, 則變成用來切換資料表的設計檢視、資料工作表檢視、樞紐分析表檢視及樞紐分析圖檢視視窗。

26 建立單一資料表的查詢 檢視鈕的圖示也會隨著不同功能而變換唷！當您在切換視窗時可以注意一下它的變化。
事實上, 我們也可以直接按檢視鈕來做快速切換, 而檢視鈕中的圖示即表示按一下會切換到的視窗種類, 包括：

27 建立單一資料表的查詢 接著, 請按資料工作表檢視視窗右上方的關閉鈕, 則會出現如下視窗：

28 建立單一資料表的查詢 『篩選』 和『 排序』 其實都可算是查詢的一種, 只不過篩選和排序的設定是儲存在資料表中, 而查詢則是存成獨立的物件, 因此不會影響到其所使用到的資料表。 由於查詢也可以當成一般資料表來操作, 所以在查詢的資料工作表檢視視窗中我們一樣可以設定各種排序、篩選的條件, 其操作方法和資料表完全相同, 而且這些設定也可以儲存在查詢的版面設定中, 以便下次開啟時能產生同樣的效果。

29 什麼是 SQL 檢視？ 當您拉下檢視鈕時, 可以看到第四個項目為SQL 檢視, 這是什麼呢？我們直接切到這個畫面看看：

30 什麼是 SQL 檢視？ 以上視窗中顯示的就是 SQL (Structured Query Language) 指令, SQL 是一種應用在關聯式資料庫中的語言, 它的語法非常簡單並且容易使用, 而 Access 的查詢設計便是使用這種語言來描述的。 此外, Access 使用的 SQL 語言和 MS SQL Server 所使用的 SQL 語言十分類似 (想更了解 SQL 語法可參考旗標出版的『SQL Server 2005 設計實務』一書)。

31 什麼是 SQL 檢視？ 其實在 Access 中, 我們幾乎可以不用去了解 SQL 語法, 因為 Access 己經用『查詢設計視窗』將之包裝起來了, 我們只要用滑鼠拉曳點選, 就可輕鬆地完成查詢的設計, 所以幾乎是感覺不到 SQL 的存在！

32 建立多資料表的查詢 由於我們的訂單資料和客戶資料是分放在兩個不同的資料表中, 因此在查看訂單資料表時,無法看到相關的客戶名稱及聯絡人姓名。
以下我們就來建立一個可同時觀看訂單及相關客戶資料的查詢：

33 建立多資料表的查詢

34 建立多資料表的查詢 Access 是非常聰明的, 當我們在查詢視窗中加入資料表時, 便會自動幫我們尋找各資料表間是否有相同名稱及資料類型的欄位, 若找到了而且其中有一個欄位是主索引, 那麼 Access 便會以此欄位來設定關聯。 接著, 請分別在兩個資料表中選取要顯示的欄位：

35 建立多資料表的查詢 看到這裡, 您應該可以體會到查詢最大的好處, 就是可以將分散於各資料表中的資料整合起來, 而成為一份有意義的資訊！

36 9-5 子資料工作表 本節我們要介紹『 父子資料表』 的觀念, 以及配合子資料工作表功能, 來進行一些資料表關聯的操作。 何謂父子資料表
何謂子資料工作表 操作子資料工作表

37 何謂父子資料表 當我們為兩個資料表建立關聯時, 通常有一方是父資料表, 而另一方則是子資料表 (需依賴父資料表才有意義), 我們將這種資料表關聯稱為 "父和子的資料表" 關係, 例如：

38 何謂父子資料表

39 何謂父子資料表

40 何謂父子資料表 通常在一對多的資料表關聯中, "一" 的這一方是 "父資料表", 而 "多" 的這一方則是 "子資料表"。
這一點請您要牢記下來, 對接下來要介紹的子資料工作表會有很大的幫助。

41 何謂子資料工作表 若要檢視父、子資料表的資料, 比較直覺的想法就是分別開啟這兩個資料表來觀看。不過, Access 提供了一個好用的功能, 那就是 "子資料工作表", 更能表現出父、子資料表之間的關係。 "子資料工作表" 和 "子資料表" 不同的地方在於：子資料工作表可直接在其父資料表上開啟。並且開啟的子資料工作表, 就像一般的資料工作表一樣, 可以進行各種工作表的操作。

42 何謂子資料工作表

43 何謂子資料工作表 開啟客戶資料表看看是否有設定子資料工作表：

44 何謂子資料工作表

45 何謂子資料工作表 相信大家已經注意到在子資料工作表中還有加號, 這表示訂單資料表也有它的子資料表, 由於子資料工作表, 支援顯示多層的資料工作表, 所以就算是關聯性較複雜的資料表, 也能從其中一個資料表去瞭解它和其他資料表間的關係。

46 何謂子資料工作表

47 何謂子資料工作表 下面是這三個資料表的關聯圖：

48 操作子資料工作表 Access 提供我們刪除、完全展開及插入子資料工作表等功能, 接著就來看看如何操作。 刪除子資料工作表 插入子資料工作表

49 刪除子資料工作表 首先, 開啟客戶資料表, 然後依下圖操作：

50 刪除子資料工作表

51 插入子資料工作表 雖然前面子資料工作表被刪除了, 但 Access 也提供插入子資料工作表的功能, 來看看怎 麼做：

52 插入子資料工作表

53 插入子資料工作表

54 9-6 資料表間的『永久性關聯』 我們在 9-4 節的查詢範例中所設定的關聯, 只會作用在該查詢之中, 所以我們稱這樣的關聯為『 暫時性』 關聯。 如果, 我們希望這些表格之間的關聯不只是作用在一個查詢, 還可運用到其他的物件上, 那麼就可以在這些表格間建立『 永久性』 關聯, 讓它們之間的友誼永遠存在, 這樣做會有兩個好處：

55 資料表間的『永久性關聯』 當我們在建立新的查詢、表單或報表時, 只要是選用到有永久關聯的資料表, Access 便會直接幫我們套上已經建立好的『 永久性』 關聯。 Access 可以依照我們所設定的規則, 來幫忙維護資料庫中『 資料的參考完整性』。

56 資料表間的『永久性關聯』 資料的參考完整性 如何維護資料的參考完整性

57 資料的參考完整性 什麼是資料的參考完整性呢？簡言之, 就是當兩個資料表間有關聯時, 這個關聯的狀態是要完整的, 而不是支離破碎的。
例如在訂單資料表中, 有一筆訂單資料的客戶編號在客戶資料表中找不到, 那麼這筆訂單就成了一個『 孤兒』 訂單了。 這裡指的『 孤兒』 訂單, 就是一筆找不到父親的子資料。 所謂維護『 資料參考的完整性』 , 便是要在資料庫中防範孤兒資料的發生。

58 資料的參考完整性

59 如何維護資料的參考完整性 那麼我們要如何來防範資料庫中『 參考不完整』 的情形發生呢？以下是幾個要點：
在子資料表中輸入資料時, 要檢查輸入的正確性： 例如在輸入訂單時, 可以限制使用者只能輸入在客戶資料表中有登記的客戶編號。我們可以利用查閱欄來達成這樣的功能 (稍後會詳細介紹)：

60 如何維護資料的參考完整性

61 如何維護資料的參考完整性 管制在父資料表中修改關聯欄位的值：
如果有人任意將『 一品書店』 的編號由 3 改成 30, 那麼所有客戶編號 3 的訂單資料都變成孤兒了。因此, 像這樣的危險操作必須加以管制才行, 其方法有二： 不允許更改：這是最省時省力的方法。 要改就一起改：這是比較有彈性的作法, 就是當在父資料表中做修改時, 則子資料表中相關的欄位也要跟著修改, 那麼它們之間的關聯仍可保存。

62 如何維護資料的參考完整性 管制刪除父資料表中的記錄：
例如我們刪除了編號 3 的客戶, 那麼所有客戶編號 3 的訂單資料也都變成孤兒了。管制刪除的方法也是有二種： 不允許刪除。 要刪就連關聯的子資料一起刪。

63 如何維護資料的參考完整性 以上的 3 項防範措施, 都可以藉由設定永久性關聯來達成。
設定永久性關聯的方法有二種,第一是建立查閱欄位, 第二則是直接在資料庫的關聯視窗中設定, 以下二節我們分別介紹這二種方法。

64 9-7 善用『查閱欄位』 經由『 查閱欄位』 , 我們可以在一個資料表中查閱到另一個關聯資料表中的資料：

65 善用『查閱欄位』 建立查閱欄位的好處如下： Access 會將查閱所用到的關聯設為永久性關聯。
輸入資料時直接由查閱的列示窗中選取即可, 免除鍵盤輸入的麻煩。 必要時可以限制使用者只能由列示窗中選取, 而無法另外輸入其他的值, 如此可避免產生孤兒。 在瀏覽資料表時, 可以看到比較有意義的資料, 例如瀏覽訂單資料表時, 可以直接看到下單的客戶名稱, 而非沒意義的客戶編號。

66 善用『查閱欄位』 使用查閱精靈來建立查閱欄位 檢視查閱欄位 練習：在訂單細目中設定查閱欄位 查閱固定的資料集

67 使用查閱精靈來建立查閱欄位 『查閱精靈』 可以方便我們建立查閱欄位, 我們以訂單資料表為例來練習。
由於 9-19 頁插入子資料工作表時, 建立了客戶和訂單資料表的關聯, 將會導致無法變更訂單資料表的資料類型, 因此請先從 Ch09範例資料.accdb 資料庫重新匯入客戶和訂單這 2 個資料表, 並取代原本的客戶及訂單 (此時會詢問是否刪除關聯, 請選擇是) 以回復沒有關聯的狀態。 接著請開啟訂單資料表的設計視窗, 然後依照下面的步驟操作：

68 使用查閱精靈來建立查閱欄位

69 使用查閱精靈來建立查閱欄位

70 使用查閱精靈來建立查閱欄位

71 使用查閱精靈來建立查閱欄位

72 使用查閱精靈來建立查閱欄位

73 使用查閱精靈來建立查閱欄位

74 使用查閱精靈來建立查閱欄位

75 使用查閱精靈來建立查閱欄位 完成前面的步驟後, 查閱精靈一共幫我們做了二件事： 在客戶編號欄建立了一個查閱欄位。
建立訂單與客戶資料表間的永久性關聯。

76 檢視查閱欄位 建立好查閱欄之後我們就來看看成果吧！請按檢視區的檢視鈕, 切換到資料工作表檢視模式：

77 檢視查閱欄位 接著, 我們再切換回設計視窗, 看看查閱欄的相關設定：

78 檢視查閱欄位

79 檢視查閱欄位 其實查閱欄位和一般欄位在設定上的不同處就是在於查閱頁次：

80 練習：在訂單細目中設定查閱欄位 接下來, 請讀者自行練習將訂單細目資料表中的書籍編號欄改成查閱欄位, 不過這次我們要查閱的資料包括書籍資料表中的書籍名稱及單價二欄。整個操作步驟和前面查閱客戶的例子差不多：

81 練習：在訂單細目中設定查閱欄位 並在最後一個交談窗中依下圖填入標題：

82 練習：在訂單細目中設定查閱欄位 建立好之後的效果如下：

83 查閱固定的資料集 如果某個欄位值只有固定的幾個項目可以選, 例如性別欄只能輸入 "男" 或 " 女", 那麼我們也可以利用查閱精靈來建立一個『查閱固定資料集』的查閱欄, 以便直接使用下拉式選單內的值, 而不必自己一筆一筆輸入。 請開啟客戶資料表的設計視窗, 然後在性別欄的資料類型選擇查閱精靈：

84 查閱固定的資料集

85 查閱固定的資料集

86 查閱固定的資料集

87 查閱固定的資料集 設定好之後, 我們切到資料工作表檢視視窗中看看結果：

88 9-8 在『資料庫關聯圖』視窗 設定永久性關聯 資料庫關聯圖視窗主要是用來建立、檢視及修改各資料表間的永久性關聯。
9-8 在『資料庫關聯圖』視窗 設定永久性關聯 資料庫關聯圖視窗主要是用來建立、檢視及修改各資料表間的永久性關聯。 開啟資料庫關聯圖視窗的方式如下：

89 在『資料庫關聯圖』視窗 設定永久性關聯

90 在『資料庫關聯圖』視窗 設定永久性關聯 當查閱精靈建立查閱 欄位時會自動建立一 個永久性的關聯, 若 看不到關聯圖, 請再按 功能區的所有關聯鈕, 即可看到先前未顯示 出來的關聯圖了。 資料庫關聯圖視窗可以儲存各資料表在視窗中的位置, 以及是否顯示等資訊, 以便下次再開啟時可以看到同樣的版面配置。

91 在『資料庫關聯圖』視窗 設定永久性關聯 建立新的永久性關聯 什麼是『連接屬性』？ 修改關聯設定 刪除資料表及關聯 加入新的資料表或查詢
隱藏資料表 儲存與關閉資料庫關聯圖

92 建立新的永久性關聯 現在, 我們要來建立訂單資料表與訂單細目資料表間的永久性關聯, 請用滑鼠將訂單資料表中的訂單序號欄, 拉曳到訂單細目中的訂單序號欄上面：

93 建立新的永久性關聯 放開滑鼠後, 便會出現編輯關聯交談窗, 用來設定兩個資料表要如何關聯：

94 建立新的永久性關聯 由於我們希望要維護參考完整性, 所以請如下設定：

95 建立新的永久性關聯 當您選取強迫參考完整性多選鈕時, 其下的另外兩項才允許您設定, 這兩項的作用如下： 串聯更新關聯欄位：
串聯刪除關聯記錄：

96 建立新的永久性關聯 另外, 您也可以按連接類型鈕來設定兩個關聯資料表的『 連接屬性』 , 不過我們一般使用預設值即可, 所以您不用去更改它。
最後按編輯 關聯交談窗 的建立鈕, 便可關閉 關聯視窗 完成設定：

97 建立新的永久性關聯 在上圖中, 我們會發現新建立的關聯線和其他兩條不一樣, 在線的兩端多了 1 和 ∞ 符號, 這就表示其為 1 對多關聯, 而且要強迫參考完整性。

98 什麼是『連接屬性』？ 當我們要同時取用兩個關聯資料表中的資料時, 『連接屬性』可用來定義這兩個資料表的資料要如何做合併。請按連接類型鈕, 開啟如下交談窗：

99 什麼是『連接屬性』？ 以下我們將資料表加以簡化來說明這 3 個單選鈕的效果, 假設有下面 2 個資料表：

100 什麼是『連接屬性』？ 當您選擇第 1 個單選鈕時, 會選出： 當您選擇第 2 個單選鈕時, 會選出： 當您選擇第 3 個單選鈕時, 會選出：

101 修改關聯設定 接下來, 我們想將原來的兩個關聯也設定為要強迫參考完整性, 該怎麼設定呢？您只要在關聯線上雙按滑鼠, 或是按工具區的編輯關聯鈕, 便可開啟編輯關聯交談窗：

102 修改關聯設定

103 修改關聯設定 同樣的, 我們在書籍與訂單細目資料表間的關聯線上雙按：
由於書籍資料是固定的, 所以我們要強迫參考完整性, 而且也不允許重疊顯示更新或刪除。

104 刪除資料表及關聯 如果您想要刪除關聯, 那麼可以在該關聯線上按滑鼠左鈕, 然後按 Del 鍵：

105 刪除資料表及關聯 如果想要移除資料表呢？ 您必須先刪除連到該資料表的所有關聯線, 然後 Access 才允許將這個資料表從資料庫關聯圖視窗中除名。 底下我們示範將書籍資料表移除 (假設您已刪除了它的所有關聯線)：

106 刪除資料表及關聯

107 加入新的資料表或查詢 如果您想在資料庫關聯圖視窗中加入其他的資料表, 可按資料庫關聯圖區的顯示資料表鈕(或是在資料庫關聯圖視窗的空白處按右鈕, 並執行『 顯示資料表』命令), 則會開啟顯示資料表交談窗：

108 加入新的資料表或查詢 另外,查詢可以被當成一般的資料表來操作, 因此我們也可以把查詢加入資料庫關聯圖視窗中並設定關聯:

109 隱藏資料表 如果您覺得資料庫關聯圖視窗中的資料表及關聯太多太亂了, 可將某些比較不重要的資料表隱藏起來, 以方便閱覽。
以下我們將訂單資料表隱藏起來：

110 隱藏 資料表

111 隱藏資料表 如果您要將全部的資料表都隱藏起來, 那麼可以這樣做：

112 隱藏資料表 那麼, 我們要如何將隱藏的資料表回復呢？最快的方法是：

113 儲存與關閉資料庫關聯圖 最後, 我們按資料庫關聯圖視窗右上角的關閉鈕, 則會出現如下交談窗：

114 9-9 測試設定永久性關聯後的效果 如果我們想要看一下所有訂單的詳細資料, 包含訂單的日期、是否付款、客戶名稱與書籍名稱四項資料, 那麼可以依照下面的步驟建立一個查詢：

115 測試設定永久性關聯後的效果

116 測試設定永久性關聯後的效果

117 測試設定永久性關聯後的效果

118 查詢設計視窗的關聯項目設定 當您在查詢設計視窗中雙按資料表間的關聯線時, 只會開啟如下視窗讓您設定『連接屬性』, 其他的關聯項目則必須在永久性關聯中設定：