Hey, Scripting Guy!: Can This Relationship Be Saved?

發行項
08/17/2016

嗨，Scripting Guy！如何建立關係/關聯性？

Microsoft Scripting Guy

下載本文程式碼: HeyScriptingGuy2007_09.exe (150KB)

人際關係很重要。但是 Scripting Guy 又是怎麼知道這一點的呢？至少我們都有看電視，電視上都有說：人際關係很重要。

而且我們指的不只是連續劇或是電視上播的本週強檔影片。例如，某理財貸款公司的廣告就熱烈歡迎大家上門借錢。這是因為他們願意借錢給原來沒本事借錢的人嗎？不是。那難道是因為他們的年利率高達 900% 嗎？當然不是這樣的。真正的原因是，這家公司會與您建立密切的個人關係。就像他們的廣告說的：「人際關係真的大有關係」。

請注意：是啦，我們已經感動得落淚，而且這也沒什麼好丟臉的。畢竟，想到有人竟然能夠和他的高利貸公司建立起深厚不變的親密關係，真的很感人嘛。

我們知道讀者們正在想什麼：「如果高利貸公司和借款人之間的關係 (Relationship) 有那麼重要，撰寫與關聯式資料庫互動的指令碼的人不就更要注重關聯性 (Relationship) 囉。」沒錯，事實上，撰寫與資料庫互動的指令碼的人，一定要注重關聯性。可惜的是，許多指令碼撰寫者有時會忽略關聯性的重要性。但是沒有關係，在本月的專欄中，我們會糾正大家的觀念。

我們知道許多系統管理員都會使用資料庫，而常見用途之一就是追蹤硬體數量。問題是，大多數的資料庫都沒有採用最佳化的架構。舉例而言，如果您要建立一個資料庫來記錄所有電腦上的硬碟機。如果您對於資料庫設計不熟，就可能會建立一個只有單一資料表的資料庫，如 [圖 1] 所示。

圖 1 單一資料表資料庫

圖 1** 單一資料表資料庫 **

如您所見，這個設計十分簡單：其中有一個電腦名稱的欄位和數個是/否的欄位，以記載電腦是否具有 C:磁碟機和 D:磁碟機。就這樣而已。很簡單明瞭。

那麼這個資料庫到底有什麼問題呢？老實說，問題太多了。如果您可以確保所有電腦上的硬碟機都不會超過兩顆，這個設計才能勝任 (應該可以)。但是，萬一有台電腦裝了三顆硬碟機，那該如何？在這樣的情況下，您就需要新增一個可以記錄 E 磁碟機的欄位。那麼，萬一有台電腦裝了 11 顆硬碟機，那該怎麼辦呢？而且，如果您必須追蹤每一顆硬碟機的屬性，那又怎麼辦？例如，如果您想要追蹤每一顆硬碟機的總大小。那麼，您就需要加入一個 Drive_C_Size 欄位、一個 Drive_D_Size 欄位，以及一個 Drive_E_Size 欄位...好啦，您知道問題出在哪了吧 (而且搞不好您還需要追蹤可用磁碟空間、磁碟機連接類型、是否有啟用磁碟配額...等等)。

您需要牢記的基本原則如下：只要項目需要追蹤一個以上的項目 (例如，電腦可以配置一個以上的硬碟機)，就不應該採用「一般檔案」(單一資料表) 的設計。是的，就硬碟機的案例而言，或許您會認為單一資料表的設計還勉強可用，畢竟會安裝到電腦上的硬碟機數目有限。但是，如果您想要查詢資料庫以傳回硬碟機大小大於 100 GB 的清單，要如何在這樣的設計中達成目標？答案是，您需要先在 Drive_C_Size 欄位中搜尋大於 100 GB 的硬碟機，然後在 Drive_D_Size 欄位中執行相同的搜尋，然後在 Drive_E_Size 欄位中執行相同的搜尋，然後...由此可見，這絕對不是很有效率或可行的方法。那麼，應該採取什麼樣的替代方案呢？如果無法使用一般資料庫檔案，那應該使用哪一種資料庫？

答案很簡單：當然是關聯式資料庫囉。關聯式資料庫原本就設計為可以處理一對多的關聯性 (例如，一部電腦可以有多顆硬碟機)。我們知道您想要問的問題，答案是：您不需要另購任何設備即可使用關聯式資料庫，因為只要有某一種資料庫 (例如 Microsoft® Access™、SQL Server™、Oracle...等等)，就應該有關聯式資料庫的功能。您不需要另購任何設備，只需要知道兩件事：1) 如何在資料庫中的兩個資料表之間建立關聯性；以及 2) 如何撰寫能夠運用該關聯性的指令碼。

請注意：電視上有說，我們不應該運用人際關係來獲取好處，但是，此關係非彼關係。

由於本專欄並不是「嗨，資料庫設計家！」專欄，所以我們不會花費太多時間討論資料庫的設計。我們將使用一個簡單的關聯式資料庫設計，幫助您了解撰寫關聯式查詢的方法。在此資料庫中，我們有兩個資料表。第一個的名稱為「Computers」，其中有兩個欄位：ComputerName 和 SerialNumber。ComputerName 欄位是存放電腦名稱的地方，而 SerialNumber 欄位則是要存放...哦，您已經猜到了嗎？沒錯，就是序號。

那我們到底要把硬碟機資訊存放在哪裡？這些資訊會存放在第二個資料表中，命名為 DiskDrives。此資料表包含三個性質簡單明確的欄位：SerialNumber、DriveLetter，以及 DriveSize。

此處的關鍵欄位是 SerialNumber。如果您覺得此欄位名稱很耳熟，沒錯，這就是在 Computers 資料表中的相同欄位名稱。這可不是意外或巧合；這是我們刻意設計的。這是為了要在這兩個資料表之間建立關聯性，所以需要有一個會出現在各個資料表中的欄位；這樣我們才知道哪一顆硬碟機屬於哪一部電腦。

那麼，為什麼我們要使用序號，而不使用電腦名稱？原因很簡單：電腦名稱可以 (而且也經常會) 變更。序號通常不會變更。

讓我們別再紙上談兵了。請看看 [圖 2] 的指令碼，它會搜尋這兩個資料表，並擷取屬於各電腦的硬碟機清單。[圖 3] 顯示此指令碼傳回的資料。

Figure 3 搜尋結果

atl-ws-01
C:

atl-ws-01
D:

atl-ws-01
E:

atl-ws-02
C:

atl-ws-02
D:

Figure 2 搜尋資料表

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
 "Provider = Microsoft.Jet.OLEDB.4.0; " & _
 "Data Source = c:\scripts\test.mdb" 

objRecordSet.Open "SELECT Computers.*, DiskDrives.* FROM Computers " & _
 "INNER JOIN DiskDrives ON Computers.SerialNumber = DiskDrives.SerialNumber " & _
 "ORDER BY Computers.ComputerName, DiskDrives.Drive", objConnection, adOpenStatic, _
  adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordset.EOF
 Wscript.Echo objRecordset.Fields.Item("ComputerName")
 Wscript.Echo objRecordset.Fields.Item("Drive")
 Wscript.Echo
 objRecordset.MoveNext
Loop

objRecordSet.Close
objConnection.Close

我們不會在本月的專欄中討論連接與使用資料庫的基本概念；如果您是資料庫指令碼的新手，建議您先觀賞 Scripting Guy 的網路廣播「系統管理員的資料庫指令碼需知」 (go.microsoft.com/fwlink/?LinkId=22089)。我們只能透漏一點點消息 -- 我們所連接的是 Access 資料庫 (C:\Scripts\Test.mdb)，而且使用的是名為 Computers 和 DiskDrives 的關聯式資料表。從指令碼中也應該看得出來。

對了，另一件需要提及的是，如果您要使用 Access 2007，就必須執行一項小變更：Provider 必須從 Microsoft.Jet.OLEDB.4.0 變更為 Microsoft.ACE.OLEDB.12.0，如下所示：

objConnection.Open _
 "Provider = Microsoft.ACE.OLEDB.12.0; " & _
 "Data Source = c:\scripts\test.accdb"

就這樣。我們的重點放在如何從兩個資料表擷取資料的 SQL 查詢：

objRecordSet.Open "SELECT Computers.*, " & _
 "DiskDrives.* FROM Computers " & _
 "INNER JOIN DiskDrives ON " & _
 "Computers.SerialNumber = " & _
 "DiskDrives.SerialNumber " & _
 "ORDER BY ComputerName, Drive", _
 objConnection, adOpenStatic, adLockOptimistic

您覺得複雜嗎？或許有一點。讓我們一一分解其中的指令碼，使您更容易理解。

查詢的第一個部分很簡單。我們要選取兩個資料表中的所有欄位：Computers 和 DiskDrives。此舉的 SQL 查詢指令碼如下：

SELECT Computers.*, DiskDrives.*

這個部分還好；星號 (*) 代表萬用字元，亦即「所有項目」。

如果您之前有撰寫過 SQL 查詢 (或是 Windows® Management Instrumentation (WMI) 的查詢，其中使用的是 SQL 查詢語言的子集)，就應該清楚採用的慣例：指定要選取的項目之後，您需要指定要從哪裡選取項目。下一段查詢指令碼就會長這樣：

FROM Computers INNER JOIN DiskDrives ON Computers.SerialNumber = DiskDrives.SerialNumber

請不要慌張。雖然這比一般的 FROM 陳述式還要複雜一點，但這不是沒有理由的。畢竟，在一般的 SQL 查詢中，我們只會從單一資料表選取資料；而我們這次是要同時從兩個資料表選取資料。

讓我們仔細看看語法以及運作方式。如您所見，我們要指令碼從 Computers 資料表和從 DiskDrives 資料表中選取資料。請注意：我們使用的詞彙並非 "and"；而是使用 INNER JOIN。此詞彙會定義關聯性類型，而這又會描述我們要如何將兩個資料表的資訊聯結成單一資料錄集 (沒錯，其實聯結 (Join) 方式有很多種；我們稍後會討論)。

如上所述，我們需要指定資料表之間的關聯性類型；我們也需要指定資料表的關聯性。這就是下列程式碼的作用：

ON Computers.SerialNumber = DiskDrives.SerialNumber

我們現在要定義聯結資料表的方式。只要 Computers 資料表的 SerialNumber 欄位和 DiskDrives 資料表內的 SerialNumber 欄位符合，記錄就會合併。如果我們使用不同的欄位 (例如 ComputerName) 做為聯結欄位，又會如何？若是如此，程式碼就會變成如下：

ON Computers.ComputerName = DiskDrives.ComputerName

在這裡先告訴您，這兩個欄位的名稱的確不需要一樣，只需要包含相同的資料。使用相同的名稱只是要讓關聯性欄位較容易辨識。另有一項重點要請您注意：由於我們有兩個相同名稱的欄位，所以在參考其中一個欄位時，一定要使用 TableName.FieldName 的語法。所以程式碼會變成：Wscript.Echo obj- Recordset.Fields.Item("Computers.SerialNumber")。

我們所需的大概只有這樣；其他的查詢程式碼僅會將資料排序，以電腦名稱為主，再以磁碟機代號為輔：

ORDER BY ComputerName, Drive

其實沒有那麼難，對吧？現在讓我們休息片刻，討論一下聯結 (Join)。為什麼我們要在查詢中使用內部聯結 (Inner Join)？很簡單。內部聯結只會傳回各資料表中，值相符的記錄。換句話說，假設有一個項目的序號是 121989。如果該序號同時出現在 Computers 和 DiskDrives 資料表中，就會傳回相對的記錄 (當然，相符的序號不能是任何欄位中的資料，必須是 SerialNumber 欄位中的資料)。這樣清楚了吧？很好。

那麼，現在假設我們有一部電腦，序號為 121989；但是其中沒有該序號的硬碟機。在這樣的情況下，序號為 121989 的電腦就不會傳回任何資料。這是因為內部聯結僅會在各個聯結之資料表中都具有相符值時，才會傳回該記錄。

換句話說，內部聯結查詢會傳回所有具有硬碟機的電腦；但是不會傳回沒有硬碟機的電腦，或沒有安裝在電腦中的硬碟機。這樣很好，因為這通常就是我們要的結果。但是如果您想要產生一份清單，其中包含沒有硬碟機的電腦或是沒有安裝在電腦中的硬碟機，那該怎麼做？

這就是外部聯結 (Outer Join) 的用途 (喔，原來這就是外部聯結的用途！)。目前讓我們僅考慮兩種外部聯結的類型：也就是左和右。在我們的資料庫中有兩個資料表：Computers (這會被視為「左」資料表，因為它是主要資料表)，以及 DiskDrives (這會被視為「右」資料表，因為...它不是主要資料表)。假設我們希望傳回的資料錄集會包含所有的電腦，即使是沒有安裝硬碟機的電腦，在這樣的情況下，我們就可以使用 LEFT OUTER JOIN 查詢語法，如下所示：

objRecordSet.Open "SELECT Computers.*, " & _
 "DiskDrives.* FROM Computers " & _
 "LEFT OUTER JOIN DiskDrives ON " & _
 "Computers.SerialNumber = " & _
 "DiskDrives.SerialNumber " & _
 "ORDER BY ComputerName, Drive", _
 objConnection, adOpenStatic, adLockOptimistic

您可能已經看出來，左外部聯結 (Left Outer Join) 會傳回左資料表中的所有記錄，即使右資料表中沒有相符的記錄。如此一來，傳回的資料中就會包含所有的電腦，但是來自右資料表 (硬碟機) 的記錄則只會包含與電腦相關聯的記錄。

相對的，如果我們希望傳回的資料錄集會包含所有的硬碟機，包括沒有安裝在電腦中的硬碟機：由於 DiskDrives 資料表是關聯性的右資料表，因此我們需要使用 RIGHT OUTER JOIN 查詢語法，如下所示：

objRecordSet.Open "SELECT Computers.*, " & _
 "DiskDrives.* FROM Computers " & _
 "RIGHT OUTER JOIN DiskDrives ON " & _
 "Computers.SerialNumber = " & _
 "DiskDrives.SerialNumber " & _
 "ORDER BY ComputerName, Drive", _
 objConnection, adOpenStatic, adLockOptimistic

讓我們比照一下此案例和真實生活的情況：如果 DiskDrives 是這個關聯性的右 (譯註：Right，與「對」同字) 資料表，那麼 Computers 就應該是「老公」資料表，而不是「左」資料表囉。這是因為多位 Scripting Guy 都曾經從痛苦的經驗中學習到：「老公」在任何關係/關聯性裡，都不會是「對 (譯註：Right，與「右」同字)」的一方。

使用右外部聯結 (Right Outer Join) 之後，傳回的會是右資料表的所有記錄 (所有的硬碟機)，但是其中僅會包含與硬碟機相關聯的電腦記錄 (左資料表中的紀錄)。

這目前可能還會有點難懂，但是只要您稍微玩一下，很快就可以抓到其中的竅門。如果您需要一個可以盡情測試的資料庫，我們有張貼一個範例資料庫可供使用，下載位置為 microsoft.com/technet/scriptcenter/resources/tnmag/archive.mspx。

在使用外部聯結時有一點很重要，就是您至少要在指令碼的第一行加入 On Error Resume Next 的指令行。為什麼呢？因為當我們執行左外部聯結查詢時，有可能會傳回沒有安裝任何硬碟機的電腦。這看起來好像沒事 (畢竟這就是我們要的資料)，直到我們碰上下列這一行程式碼，其中應該要以磁碟機代號回應：

Wscript.Echo objRecordset.Fields.Item("Drive")

由於此電腦沒有 Drive 欄位，就會導致指令碼發生錯誤而終止：

C:\Scripts\database.vbs(22, 9) Microsoft VBScript runtime error: Type mismatch

只要我們實作了 On Error Resume Next，指令碼就可以忽略沒有安裝硬碟機的電腦，順暢地繼續執行。另一個替代方案是先使用下列程式碼來檢查 Drive 欄位，然後再採取適當的動作：

If IsNull(objRecordset.Fields.Item("Drive"))_Then
    Wscript.Echo "No disk drives installed."
Else
    Wscript.Echo _
      objRecordset.Fields.Item("Drive")
End If

有了上列程式碼，我們就可以先檢查傳回的值是否為 Null，還是有傳回實際的磁碟機代號。如果是 Null，我們就以「沒有安裝硬碟機」回應。如果傳回的並非 Null 值，則我們就以 Drive 欄位的值回應。結果的輸出如 [圖 4] 所示。

Figure 4 顯示適當的結果

atl-ws-01
C:

atl-ws-01
D:

atl-ws-02
C:

atl-ws-02
D:

alt-ws-03
No disk drives installed.

接下來讓我們看看其她的查詢範例，並省略詳細說明。例如，以下的內部聯結查詢會傳回電腦與所安裝硬碟機的清單，只要硬碟機的大小大於 50 GB (我們假設硬碟機的大小是以 GB 為單位)：

objRecordSet.Open "SELECT Computers.*, " & _
 "DiskDrives.* FROM Computers " & _
 "INNER JOIN DiskDrives ON " & _
 "Computers.SerialNumber = " & _
 "DiskDrives.SerialNumber " & _
 "WHERE DriveSize > 50 " & _
 "ORDER BY Computers.ComputerName, " & _
 "DiskDrives.Drive", objConnection, _
 adOpenStatic, adLockOptimistic

如您所見，我們僅在原始的查詢中加了一個 WHERE 子句：

WHERE DriveSize > 50

如果我們只要所有電腦的 E: 磁碟機資訊呢？沒有問題，只要加入適當的 Where 子句即可：

WHERE Drive = 'E:'

接下來是一個較複雜的查詢，這會傳回沒有安裝硬碟機的電腦集合：

objRecordSet.Open _
 "SELECT Computers.ComputerName, " & _
 "DiskDrives.Drive " & _
 "FROM Computers LEFT JOIN DiskDrives " & _
 "ON Computers.SerialNumber = " & _
 "DiskDrives.SerialNumber " & _
 "GROUP BY Computers.ComputerName, " & _
 "DiskDrives.Drive " & _
 "HAVING (DiskDrives.Drive) Is Null " & _
 "ORDER BY Computers.ComputerName", _
 objConnection, adOpenStatic, adLockOptimistic

如上所述，這一個查詢比較複雜一些，但是由於空間的限制，我們將略過其運作方式的說明。最重要的是，這確實可以正常運作。

或許應該說是第二重要的吧；因為我們已經提過好幾次，最重要的是關係/關聯性。但這並不代表關係/關聯性不會出錯喔。正如伍迪．艾倫 (Woody Allen) 在「安妮霍爾」(Annie Hall) 一片中所說：「關係...就像鯊魚一樣；它必須一直向前進，否則就會滅亡。而且我認為，在我們手上的是一條已死的鯊魚。」可惜伍迪沒有機會認識內部聯結和外部聯結，因為只要掌握這些查詢技巧，關係/關聯性的建立一定可以成功！

Microsoft Scripting Guy 為 Microsoft 做事，也就是受雇於 Microsoft。在比賽、訓練、看棒球賽 (以及其他各種活動) 之餘，他們也負責管理 TechNet Script Center。請到 www.scriptingguys.com 一探究竟。

共用方式為

其他資源