weidagang2046的專欄

在這篇文章的第二章中，我們已經建立了一個供我們使用的非常簡單的笑話數據庫，這個庫中只包括了一個名叫Jokes的數據表。這作為我們使用MySQL數據庫的入門已經是足夠了，但是在關系型數據庫的設計中還有很多其它的東西。在這一章中，我們會對我們的例子進行擴充，學習一些有關MySQL的新知識，并試圖理解并掌握關系型數據庫所能提供的功能。

首先，我們得說明我們對許多問題的解決只是不正規(guī)的（也就是說非正式的）。正如你在許多計算機科學專業(yè)中了解的那樣，數據庫設計是一個嚴肅的領域，數據庫設計必須包括對它的測試并會涉及到一些數學的原理。但這些可能是超過我們這篇文章的范圍了。要得到更多的信息，你可以停下來到http://www.datamodel.org/去看看，在那兒你可以看到許多好的書籍，并得到一些關于這個問題的有用的資源。

給予應有的權限
在開始之前，讓我們回憶一下我們的Jokes數據表的結構，這個表包含三個列：ID、JokeText和 JokeDate。這些列可以使我們標識笑話（ID），明了他們的內容（JokeText）以及他們被加入的時間（JokeDate）。

現在我們想要保存我們的笑話中的其它一些信息：提交者的姓名。這看上去很自然，我們需要在我們的Jokes數據表中添加一個新的列。SQL的ALTER命令（我們在之前沒看到過這個命令）可以幫助我們完成這件事。使用mysql命令行程序登錄到MySQL服務器，選擇你的數據庫（如果你使用我們在第二章中的命名，數據庫名應該是joke），然后輸入下面的命令：

mysql> ALTER TABLE Jokes ADD COLUMN -> AuthorName VARCHAR(100);

這將會在我們的數據表中增加一個叫AuthorName的列。其數據類型是一個可變長度的字符串，其最大長度是100個字符（這對于最復雜的名字應該也是足夠了）。讓我們再添加一列用來保存作者的e-mail地址：

mysql> ALTER TABLE Jokes ADD COLUMN -> AuthorEMail VARCHAR(100);

要得到更多的有關ALTER命令的信息，請參看MySQL參考手冊。要確認我們是不是正確地添加了兩列，你可以要求MySQL為我們對這個表進行描述：

看上去很不錯。明顯地，我們需要對我們在第四章中建立的添加新笑話的HTML以及PHP格式的代碼進行調整，但是我們會把這留給你作為一個練習。使用UPDATE查詢，你現在可以對表中的所有笑話添加作者的詳細資料。然而，在你開始接受這個數據結構之前，我們必須考慮一下我們在這兒選擇的設計是否確當。在這種情況下，我們會發(fā)現一些我們還沒有做到的事情。

一個基本的規(guī)則：保持事物的分離
在你建立數據庫驅動的網站的過程中，你已經覺得僅僅是有一個笑話列表是不夠的。事實上，除了你自己的笑話以外，你開始接收其他人提交的笑話。你決定做一個讓全世界人都可以共享笑話的網站。你有沒有聽說過Internet電影數據庫（IMDB）？實際上你現在做的是Internet笑話數據庫（IJDB）！對每一個笑話添加作者的姓名和e-mail地址肯定是最容易想到的辦法，但是這種方法會導致一些潛在的問題：

如果一個經常投稿的名叫Joan Smith的人改變了她的e-mail地址將會發(fā)生什么什么情況呢？她會開始使用新地址來提交新的笑話，但是對于所有的舊笑話，你所能看到的還是舊的地址。從你的數據庫來看，你也許只能認為有兩人名字都叫Joan Smith的人在向你的數據庫中提交笑話。如果她是特別體貼的，她也許會通知你改變地址，你可以將所有的舊笑話改成新的地址，但是如果你遺漏了一個，那就意味著你的數據庫中存儲了錯誤的信息。數據庫設計專家將這種類型的問題稱之為一個“更正異常”。

很自然地你會想到從你的數據庫中得到所有曾經向你的站點提交過笑話的人的列表。實際上，你可以使用下面的查詢很容易地得到這樣的列表：

mysql> SELECT DISTINCT AuthorName, AuthorEMail -> FROM Jokes;

上面查詢中DISTINCT是告訴MySQL不輸出重復的結果行。例如，如果Joan Smith向我們的站點提交過20個笑話，如果我們使用了DISTINCT選項，她的名字和e-mail地址將會只在列表中出現一次，否則會出現20次。

如果因為某種原因，你決定要從數據庫中刪除某個特定的作者所提交的所有笑話，但是，與此同時，你將不能再通過e-mail與他們聯系！而你的e-mail清單可能是你的網站的收入的主要來源，所以你并不想只因為你不喜歡他們提交的笑話，就刪除他們的e-mail地址。數據庫設計專家將這稱之為“刪除異常”。

你并不能保證不會出現這樣的情況：Joan Smith輸入的姓名一會兒是“Joan Smith”，一會兒是“J. Smith”，一會兒又是“Smith, Joan”。這將使得你要確定一個特定的作者變得非常困難（特別是Joan Smith又經常使用幾個不同的email地址的時候）。

這些問題的解決其實很簡單。只要你不再將作者的信息存儲到Jokes數據表中，而是建立一個新的數據表來存儲作者列表。因為我們在Jokes數據表中使用了一個叫ID的列來用一個數據標識每個笑話，所以我們在新的數據表中使用了同樣名字的列來標識我們的作者。我們可以在我們的Jokes表中使用“author ID's”來建立笑話和他的作者之間的關聯。全部的數據庫設計應該是這樣的：

上面的兩個表包含了三個笑話和兩個作者。Jokes表的AID列（“Author ID”的縮寫）提供了兩個表之間的關聯（指出Kevin Yank 提交了笑話1和笑話2，Joan Smith提交了笑話3）。在這里，你還需要注意到每一個作者只會在數據庫中出現一次，而且他們是獨立于他們提交的笑話而存在的，因此我們已經解決了我們上面提出的那些問題。

這個數據庫設計的最重要的特征是，因為我們要存儲兩種類型的事物（笑話和作者），所以我們設計兩個表。這是我們在數據庫設計中要遵守的一個基本規(guī)則：對于每一個要存儲其信息的實體（或事物），我們都應該給他一個自己的表。

重新生成上面的數據是非常簡單的（只要使用兩個CREATE TABLE 查詢就行了），但是因為我們想要在做這些變動時不會有破壞性的效果（也就是說不會丟失我們已經存入的笑話），所以我們需要再次使用ALTER命令。 首先，我們刪除Jokes表中有關作者的列：

mysql> ALTER TABLE Jokes DROP COLUMN AuthorName; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE Jokes DROP COLUMN AuthorEMail; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0

現在我們建立我們的新的數據表：

mysql> CREATE TABLE Authors ( -> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name VARCHAR(100), -> EMail VARCHAR(100) -> );

最后，我們在我們的Jokes表中添加AID列：
mysql> ALTER TABLE Jokes ADD COLUMN AID INT;
現在剩下來的就是向新的表中添加一些作者，并通過填充AID列來對數據庫中已經存在的笑話指定作者。

處理多個表
現在我們的數據被分布在兩個表當中，要從其中獲得數據看上去變得更加復雜了。例如，我們最初的目標是：顯示一個笑話的列表并在每一個笑話后面顯示作者的姓名和e-mail地址。在我們的單表結構中，要獲得所有的信息，只需要在我們的PHP代碼中使用一個SELECT語句就行了：

$jokelist = mysql_query( "SELECT JokeText, AuthorName, AuthorEMail ". "FROM Jokes"); while ($joke = mysql_fetch_array($jokelist)) { $joketext = $joke["JokeText"]; $name = $joke["AuthorName"]; $email = $joke["AuthorEMail"]; // Display the joke with author information echo( "<P>$joketext<BR>" . "(by ＜HREF='mailto:$email'＞$name)</P>" ); }

在我們的新系統(tǒng)中，這樣做初看起來是不可能了。因為有關每個笑話的作者的詳細資料不是存儲在Jokes表中，我們可能想到的一個解決方案是我們對于我們想要顯示的笑話單獨地獲得這些資料。代碼將是這樣的：

// Get the list of jokes $jokelist = mysql_query( "SELECT JokeText, AID FROM Jokes"); while ($joke = mysql_fetch_array($jokelist)) { // Get the text and Author ID for the joke $joketext = $joke["JokeText"]; $aid = $joke["AID"]; // Get the author details for the joke $authordetails = mysql_query( "SELECT Name, Email FROM Authors WHERE ID=$aid"); $author = mysql_fetch_array($authordetails); $name = $author["Name"]; $email = $author["EMail"]; // Display the joke with author information echo( "<P>$joketext<BR>" . "(by ＜A HREF='mailto:$email'＞$name)</P>" ); }

很混亂，而且對于每一個顯示的笑話都包含了一個對數據庫的查詢，這將會我們的頁面的顯示非常緩慢。現在看來，“老方法”可能是更好的解決方案，盡管它有其自身的弱點。
幸運的是，關系型數據庫可以很容易地處理多個表中的數據！在SELECT語句中使用一個新的被稱之為“join”的格式，我們可以找到兩全其美的辦法。連接可以使我們象對存儲在單個表中的數據那樣對待多個表中的關聯數據。一個連接的格式應該是這樣的：

mysql> SELECT <columns> FROM <tables> -> WHERE <condition(s) for data to be related>;

在我們目前的情況下，我們所需要的列是Jokes表中的JokeText列以及Authors表中的Name列和Email列。Jokes表和Authors表的關聯條件是Jokes表中的AID列的值等于Authors表中的ID列的值。下面是一個連接的例子（前兩個查詢只是用來顯示我們的兩個表中所包含的內容）：

現在明白了嗎？第三個SELECT的結果就是一個連接，它將存儲在兩個表中的數據關聯數據顯示到了一個結果表中，盡管我們的數據是存儲在兩個表中的，我們仍然可以使用一個數據庫查詢就獲得我們的Web頁面所需要的笑話列表的全部信息。

在這里，要注意一個問題，因為在兩個表中都有一個叫ID的列，所以我們在用到Authors表中的ID列時我們必須指定表名（Authors.ID）。如果我們沒有指定表名，MySQL將無法知道我們指的是哪一個表中的ID，這會導致這樣的一個錯誤：

mysql> SELECT LEFT(JokeText,20), Name, Email -> FROM Jokes, Authors WHERE AID = ID; ERROR 1052: Column: 'ID' in where clause is ambiguous

現在我們知道如何有效率地從我們的兩個表中獲取信息了，我們可以利用連接來重新編寫我們的笑話列表的程序：

$jokelist = mysql_query( "SELECT JokeText, Name, EMail " . "FROM Jokes, Authors WHERE AID=Authors.ID"); while ($joke = mysql_fetch_array($jokelist)) { $joketext = $joke["JokeText"]; $name = $joke["Name"]; $email = $joke["EMail"]; // Display the joke with author information echo( "<P>$joketext<BR>" . "(by ＜A HREF='mailto:$email'＞$name)</P>" ); }

隨著你對數據庫的使用，你會越來越發(fā)現連接的功能有多大的意義。例如，下面的查詢用來顯示所有由Joan Smith寫的笑話：

mysql> SELECT JokeText FROM Jokes, Authors WHERE -> Name="Joan Smith" AND AID=Authors.ID;

上面的查詢的輸出結果僅僅來源于Jokes表，但是我們使用了一個連接來通過存儲在Authors表中的值搜索笑話。在我們的這篇文章中會有更多的這樣的精巧的查詢，在實際應用中，連接是經常會被使用的，而且在絕大多數的情況下，這會很大程度地簡化我們的工作！

簡單的數據關系
對于給定的情況的最好的數據模型往往決定于我們所工作的兩種數據之間的關系類型。我這篇文章中，我們將對典型的關系類型進行研究，并學會如何在一個關系型數據中用最好的方法描述它。

對于簡單的一對一的關系，只要用一個表就足夠了。一對一關系的一個例子就是我們在前面已經看到的在笑話數據庫中的每一個作者的e-mail地址。因為對于每一個作者只有一個e-mail地址，而且對于一個e-mail地址對應的也只有一個作者，將它們分到兩個數據庫中是沒有道理的。

多對一的關系可能會稍微復雜一點，但是在之前其實我們也已經解決了這個問題，我們的數據庫中的每一個笑話只會有一個作者，但是同一個作者可能寫了很多笑話。笑話和作者之間的關系就是一個多對一的關系。我們曾經有過一個初步的解決方案，那就是將與這個笑話關聯的作者的信息也促成在同一個數據庫中。但是這樣做，對于同一個數據會有許多拷貝，這不僅會在同步上造成困難，而且會浪費空間。將數據分開到兩個數據表中并使用一個ID列來連接兩個表（象上面所說的那樣使用連接），所有的問題會得到很好的解決。

到目前為止，我們還沒接觸到一對多的關系，但是想象這樣的一個關系應該是不困難的。在我們之前建立的數據庫中，我們假定一個作者只有一個e-mail地址。事實上情況并不總是這樣的，作出這個限制的理由只是因為我們只需要一個e-mail地址來與作者聯系。我們簡單地假設了作者總會輸入他們常用的e-mail地址，或者至少是一個正常使用的e-mail地址。如果我們想要支持多個e-mail地址，我們將面對一個一對多的關系（一個作者會有幾個e-mail地址，但是一個e-mail地址只會與一個確定的作者對應）。

一個沒有經驗的數據庫設計者面對一個一對多的關系時，他首先會想到的是試圖把多個數據存儲到一個數據庫域中，就象這樣：

這種結構在投入使用后，要從數據庫中獲得一個單個的e-mail地址，將不得不通過搜索逗號（或者你所選擇的用來分隔的其他符號）來分割字符串，這樣做并不簡單，而且會很耗時。設想一下如果要用PHP來刪除某個作者的某個e-mail地址，那也將會是很困難的事。另外，對于EMail列我們需要很長的長度，這會導致磁盤空間的浪費，因為大多數的作者都只會有一個e-mail地址。

解決一對多的關系和我們上面解決多對一的關系是非常類似的。實際上兩者之前只是一個簡單的顛倒。我們可將Authors表分成兩個表，Authors和EMails，然后在EMails表中使用作者的ID（AID）這樣的一個列來實現兩個表之間的連接：

使用一個連接，顯示某個作者的所有E-mail地址將會是很簡單的：

多對多的關系
Ok，現在你有了一個發(fā)布在你的網站上的穩(wěn)定增長的笑話數據庫。事實上，這種增長是非常迅速的，笑話的數量會變得難以管理！你的訪問者將面對一個龐大的頁面，在這個頁面上雜亂地排列了數以百計的笑話。現在，我們不得不考慮作一些變動了。

你決定將你的笑話放置到不同的目錄中，這些目錄可能是“Knock-Knock笑話”、“Crossing the Road笑話”、“Lawyer笑話”和“Political笑話”。記住我們之前的處理規(guī)則，因為我們的笑話目錄是一個不同類型的“事物”，所以我們要為它們建立一個新的數據表：

mysql> CREATE TABLE Categories ( -> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name VARCHAR(100), -> Description TEXT -> ); Query OK, 0 rows affected (0.00 sec)

對你的笑話定義其所屬目錄將會是一個困難的任務。因為一個“political”笑話可能也是一個“crossing the road”笑話，同樣，一個“knock-knock”可能也是一個“l(fā)awyer”笑話。一個單個的笑話可能屬于許多目錄，每一個目錄也會包含許多笑話。這是一個多對多的關系。

許多沒有經驗的設計者又會想到將幾個數據存儲到一個列中，最直接的解決方案是在Jokes表中增加Categories列，并在其中列舉笑話所屬的目錄的ID。現在適用我們的第二個處理規(guī)則了：如果你需要在一個列中存儲多個值，那證明你的設計可能是有缺陷的。

描述一個多對多關系的正確方法是使用一個“l(fā)ookup”表。這個表不包含任何實際的數據，只是用來定義關聯的事物。這兒是我們這部分的數據庫設計的示意圖：

JokeLookup 表將笑話的ID（JID）的目錄的ID（CID）進行了關聯。從上面的例子我們可以看出，以“How many lawyers...”開頭的笑話既屬于“Lawyer”目錄，又屬于“Light Bulb”目錄。

建立lookup表的方法和建立其他表的方法基本一樣。不同點在于選擇主鍵。我們之前所建立的每一個表都有一個名為ID的列，這一列被我們定義為PRIMARY KEY。將一個列定義為主鍵意味著這一列不會出現重復值。而且可以加快基于這一列的連接操作的速度。

對于我們的lookup表來說，沒有一個單個的列可以保證不出現重復值。每一個笑話可以屬于幾個目錄，所以一個joke ID可能會出現多次；同樣的，一個目錄可能包含多個笑話，所以一個category ID也可能會出現多次。我們所要求的只是相同的數據對不應重復出現。因為我們這個表的唯一作用就是用來實現連接，所以使用主鍵來提高連接操作的速度對我們肯定有價值。所以，我們通常會為lookup表建立一個多列的主鍵：

mysql> CREATE TABLE JokeLookup ( -> JID INT NOT NULL, -> CID INT NOT NULL, -> PRIMARY KEY(JID,CID) -> );

現在我們的表中的JID和CID共同組成了這個表的主鍵。保持lookup表中數據的唯一性是有價值的（防止重復定義某一個笑話屬于某一個目錄），而且這會提高這個表用來連接時的速度。
使用我們的lookup表中包含的目錄分配，我們可以使用連接來建立幾個有趣而且非常實用的查詢。下面的查詢列出了“Knock-Knock”目錄下的所有笑話：

mysql> SELECT JokeText -> FROM Jokes, Categories, JokeLookup -> WHERE Name="Knock-Knock" AND -> CID=Categories.ID AND JID=Jokes.ID;

下面這個查詢列舉了以“How many lawyers...”開頭的笑話所屬的所有目錄：

mysql> SELECT Categories.Name -> FROM Jokes, Categories, JokeLookup -> WHERE JokeText LIKE "How many lawyers%" -> AND CID=Categories.ID AND JID=Jokes.ID;

下面的查詢，同時使用了我們的Authors表形成了一個四個表的連接（！！！），列舉了寫過 Knock-Knock笑話的所有作者的名字：

mysql> SELECT Authors.Name -> FROM Jokes, Authors, Categories, JokeLookup -> WHERE Categories.Name="Knock-Knock" -> AND CID=Categories.ID AND JID=Jokes.ID -> AND AID=Authors.ID;

結語
這一章中，我們學習了正確的數據庫設計的基本原則，以及MySQL（實際上，對其他關系型數據庫同樣適用）如何對描述事件之間的不同類型的關系提供支持。我們不僅僅探討了一對一的關系，還詳細討論了多對一、一對多以及多對多的關系。

在這一過程中，我們還學習了一些有關SQL命令的新的東西。特別的，我們學習了如何使用一個SELECT去連接多個表中的數據并將其反映到一個結果集中。

在第六章中，我們將使用我們已經獲得的知識，并加上很少的一些新知識，去用PHP構建一個內容管理系統(tǒng)。我們希望這個系統(tǒng)可以提供一個可定制的、安全的、基于Web的界面來管理數據庫的內容，而不再是在MySQL命令行中來解決問題。

from: http://www0.ccidnet.com/tech/web/2001/12/03/92_3846.html