2019年8月18日 星期日

Access 2016資料庫系統概論與實務學習筆記

　　雖然目前的工作內容包含系統管理員的工作，但是自己對於資料庫相關技術與知識並非因此充足，有鑑於目前的自己僅會使用簡單的SQL語法與Python的Pandas套件，決定先從Microsoft Office系列的Access著手，以初學者的姿態學習最簡單好用的資料庫工具。這本由王仲麒著作的《Access 2016資料庫系統概論與實務》，會是一本很好的入門書，可以讓讀者掌握好Access的功能架構，並透過範例的說明來學習資料庫系統的規劃。

一、資料庫導論

本書附錄與範例檔
l   《Access 2016資料庫系統概論與實務》附錄與範例檔：
http://books.gotop.com.tw/download/AED003100

資料庫正規化
l   第一階段：
必須設定主要關鍵欄位，而欄位中只有一個單一資料值，並沒有重複的資料值。
l   第二階段：
必須除去資料的部分相依性，也就是分割欄位資料值一再重複的資料欄位。
l   第三階段：
必須除去資料的間接相依性，也就是分割與主要關鍵無關的資料欄位。

定義資料庫主索引
l   如果僅有一個相關欄位是主索引或唯一索引，則建立一對多關聯。
l   如果兩者的相關欄位是主索引或唯一索引，則建立一對一關聯。
l   多對多的關聯是使用第三個資料表建立兩個一對多的關聯，第三個資料表的主索引包含二個欄位－來自兩個不同資料表的外部索引。

Access資料庫的架構
l   Access資料庫物件為：
資料表(Table)、查詢(Query)、表單(Form)、報表(Report)、巨集(Marco)、模組(Module)
l   Access資料庫物件的儲存與選項設定：
資料表可以另存物件為資料表、查詢、表單、報表；
查詢可以另存物件為查詢、表單、報表；
表單可以另存物件為表單、報表；
報表可以另存物件為報表。
l   Access資料庫檢視：
資料表－設計檢視、資料表工作檢視；
查詢－設計檢視、資料表工作檢視、SQL檢視；
表單－設計檢視、表單檢視、版面配置檢視；
報表－設計檢視、報表檢視、版面配置檢視、預覽列印。
l   Access本機資料庫：
在本機的硬碟或區域網路的存取空間中進行獨佔式或非獨佔式的資料庫存取並與他人共用。
l   Access Web App資料庫：
建立可發佈至SharePoint網站的網路關聯式資料庫應用程式，然後在網頁瀏覽器中以SharePoint應用程式形式使用資料庫。

二、建立並管理查詢

Access資料庫的查詢功能
l   選取查詢(Selection Query)：
選取查詢(Select)
合計查詢(Totals)
參數查詢(Parameters)
交叉資料表查詢(Cross Table)
l   動作查詢(Action Query)：
製成資料表查詢(Make Table)
更新資料查詢(Update)
新增資料查詢(Append)
刪除資料查詢(Delete)

Access資料庫的查詢準則定義
l   [建立]索引標籤à[查詢]群組à[查詢精靈]按鈕之[簡單查詢精靈](或[查詢設計]按鈕)à在欲查詢欄位的[準則]列輸入：
範例
說明
Like "?肉乾"
列出字首是任一個單字，爾後是"肉乾"的資料
Like "S*"
列出字母開頭為S的資料
Like "*有限公司"
列出以"有限公司"字串結束的資料
Like "[A-D]*"
列出字母開頭為A到D的資料
Like "*電子*"
列出欄位資料內包含"電子"字串的資料
Between #2010/5/9# And "2011/10/15"
列出某日期欄位介於2010年5月9日(含)至2011年10月15日(含)之間的資料
"1996/5/3"
列出某日期欄位為1996年5月3日的資料
In("加拿大", "日本")
列出加拿大或日本的資料
Not "韓國"
列出除了韓國以外的資料
>30
列出某數值欄位大於30的資料

Access資料庫的選取查詢
l   選取查詢：
...à[設計]索引標籤à[查詢設定]群組à[建立器]按鈕
範例：稅: [運費]*0.06
範例：小計: [訂貨明細]![單價]*[訂貨明細]![數量]*(1-[訂貨明細]![折扣])
l   合計查詢：
...à[設計]索引標籤à[顯示/隱藏]群組à[合計]按鈕à在欲查詢欄位的[合計]列點選想要計算的方式，[群組]選項為定義要執行計算的紀錄群組
範例：小計: [訂貨明細]![單價]*[數量]*(1-[折扣])
l   參數查詢：
...à[設計]索引標籤à[顯示/隱藏]群組à[參數]按鈕à在欲查詢欄位的[準則]列輸入方才在[參數]按鈕所建立的自訂參數名稱
範例：[請輸入客戶編號：]
l   交叉資料表查詢：
[建立]索引標籤à[查詢]群組à[查詢精靈]按鈕之[交叉資料表查詢精靈]
([交叉資料表查詢]的資料來源可以是資料表或查詢裡的資料欄位，[查詢]的資料來源只可以是資料表裡的資料欄位)

Access資料庫的動作查詢
l   製成資料表查詢：
...à[設計]索引標籤à[查詢類型]群組à[製成資料表]按鈕à輸入欲製成的資料表名稱
等同SQL語法：SELECT... INTO... FROM... WHERE...
l   更新資料查詢：
...à[設計]索引標籤à[查詢類型]群組à[更新]按鈕à在增加的[更新至]列輸入各個對應的資料欄位
等同SQL語法：UPDATE... FROM... WHERE...
l   新增資料查詢：
...à[設計]索引標籤à[查詢類型]群組à[新增]按鈕à選擇欲附加的資料表後，在增加了[附加至]列輸入各個對應的資料欄位
等同SQL語法：INSERT INTO... SELECT... FROM... ORDER BY...
l   刪除資料查詢：
...à[設計]索引標籤à[查詢類型]群組à[刪除]按鈕à增加了[刪除]列，[條件]猶如SQL語法的WHERE，[由]猶如SQL語法的FROM，刪除資料紀錄的動作一定是刪除整筆資料紀錄的每一個資料欄位
等同SQL語法：DELETE... FROM... WHERE...

Access資料庫的聯集查詢
l   [員工資料]查詢：
SELECT 員工編號, 姓名, Year(Date()) - Year([出生日期]) AS 年齡, 職稱
FROM 員工;
l   [員工配偶]查詢：
SELECT 員工編號, 扶養者姓名, Year(Date()) - Year([出生年月日]) AS 年齡, 職業
FROM 眷屬
WHERE (((關係) = "妻" Or (關係) = "夫"));
l   [員工配偶資訊]查詢：
SELECT * FROM 員工資料 UNION SELECT * FROM 員工配偶;
l   等同[員工配偶資訊]查詢：
SELECT 員工編號, 姓名, Year(Date()) - Year([出生日期]) AS 年齡, 職稱
FROM 員工
UNION SELECT 員工編號, 扶養者姓名, Year(Date()) - Year([出生年月日]) AS 年齡, 職業
FROM 眷屬
WHERE (((關係) = "妻" Or (關係) = "夫"));

三、與其他軟體的整合應用

與Word的整合－合併列印
l   選定的資料表à匯出àWord合併

與Excel的整合－分析資料
l   Excelà[資料]索引標籤à[從Access]按鈕

四、Access巨集

獨立巨集(Stand-alone Macro)
l   [建立]索引標籤à[巨集與程式碼]群組à[巨集]按鈕
l   範例：
OpenForm巨集指令à表單名稱：選定的表單àWhere條件：[選定的資料表]![選定的欄位] = "選定的值"à資料模式：編輯
GoToControl巨集指令à控制項名稱：選定的表單欄位

嵌入式巨集(Embedded Macro)
l   選定的表單à設計檢視à[設計]索引標籤à[控制項]群組à[按鈕]按鈕(使用控制項精靈)(或à[工具]群組à[屬性表]按鈕)

資料巨集(Data Macro)
l   選定的資料表à[表格]索引標籤à[前置事件]、[後置事件]、[指定的巨集]群組
l   範例－建立事件驅動的資料巨集：
選定的資料表à[表格]索引標籤à[前置事件]群組à[變更前]按鈕
If程式流程à條件運算式：[選定的欄位] = "選定的值"àSetField巨集指令à名稱：選定的欄位à值："選定的值"àElse If程式流程à以此類推
l   範例－建立具名的資料巨集：
STEP 1：
選定的資料表à[表格]索引標籤à[指定的巨集]群組à[建立指定的巨集]按鈕
建立參數à名稱：自訂的參數名稱à描述：選定的欄位
ForEachRecord巨集指令à對於下列位置的每一筆記錄：選定的資料表àWhere條件：[選定的欄位A] = [自訂的參數名稱A] And [選定的欄位B] <> [自訂的參數名稱B]
EditRecord巨集指令àSetField巨集指令à名稱：選定的欄位à值："選定的值"
另存新檔：輸入自訂的巨集名稱
STEP 2：
選定的資料表à[表格]索引標籤à[後置事件]群組à[更新後]按鈕
If程式流程à條件運算式：[選定的欄位] = "選定的值"àRunDataMacro巨集指令à巨集名稱：選定的資料表.自訂的巨集名稱à參數：自訂的參數名稱 = [選定的資料表].[選定的欄位]

五、Access VBA(Visual Basic for Applications)

Access資料庫的模組物件
l   模組存放的是使用者所撰寫的VBA程式碼，可分為一般模組與類別模組兩大類型，一般模組會顯示在功能窗格的類別中，可應用於整個資料庫，類別模組則是附加在資料庫裡的表單或報表等物件中，應用於表單或報表的特定事件程序。
l   一般模組：
[建立]索引標籤à[巨集與程式碼]群組à[Visual Basic]按鈕
l   類別模組：
選定的表單à設計檢視à[設計]索引標籤à[控制項]群組à[按鈕]按鈕à[工具]群組à[屬性表]按鈕à[事件]索引標籤àOn Clickà程式碼建立器

VBA程式設計簡介
l   程序(Sub Procedure)：
Sub SayHi()
    MsgBox "歡迎加入Access家族～"
End Sub
l   函數(Function Procedure)：
Function SalesBonus(Income As Long) AS Integer
    If Income > 30000 then
        SalesBonus = Income * 0.03
    Else
        SalesBonus = Income * 0.01
    End If
End Function
l   變數(Variable)：
(若未指定變數的資料型別，則自動預設為Variant)
Dim creditNours As Integer
Dim salary As Long
Dim courseName As String
l   程序(Sub Procedure)、函數(Function Procedure)與變數(Variable)的整合應用：
Sub InputData()
    Dim Message, Title, Default, MyValue AS Long
    Message = "請輸入業績計算："
    Title = "收入與紅利計算"
    Default = "1"
    MyValue = InputBox(Message, Title, Default)
    MsgBox "您的業績收入是：" & MyValue & Chr(13) & _
    "可獲得獎金：" & SalesBonus(MyValue)
End Sub

VBA程式流程控制
l   If... Then... Else... End If分歧判斷敘述(Branching)：
If 條件判斷式 Then
    條件判斷式成立時所要執行的敘述
Else
    條件判斷式不成立時所要執行的敘述
End If
l   Select Case... End Select分歧判斷敘述(Branching)：
Select Case 運算式
    Case 值1
        運算式為值1時所要執行的敘述
    Case 值2
        運算式為值2時所要執行的敘述
    Case 值3
        運算式為值3時所要執行的敘述
    Case Else
        運算式不為以上任何值時所要執行的敘述
End Select
l   For... Next迴路控制敘述(Looping)：
For 變數 = 初值 To 終值 Step 間距值
    迴路裡的敘述
Next
l   Do While... Loop迴路控制敘述(Looping)：
Do While 條件判斷式
    條件判斷式成立時所要執行的敘述
Loop
l   Do... Loop While迴路控制敘述(Looping)：
Do
    條件判斷式成立時所要執行的敘述
Loop While 條件判斷式
l   Do Until... Loop迴路控制敘述(Looping)：
Do Until 條件判斷式
    條件判斷式成立時所要執行的敘述
Loop
l   Do... Loop Until迴路控制敘述(Looping)：
Do
    條件判斷式成立時所要執行的敘述
Loop Until 條件判斷式
l   While... Wend迴路控制敘述(Looping)：
(多層的巢狀結構，每個Wend都要有相對應的While陳述式)
While 條件判斷式
    條件判斷式成立時所要執行的敘述
Wend

將巨集轉為VBA
l   選定的巨集à設計檢視à[設計]索引標籤à[工具]群組à[將巨集轉換成Visual Basic]按鈕
於
標籤： ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)