摘要

　　在這一章中，我們會對我們的例子進(jìn)行擴(kuò)充，學(xué)習(xí)一些有關(guān)MySQL的新知識，并試圖理解并掌握關(guān)系型數(shù)據(jù)庫所能提供的功能。

(2002-08-29 14:11:39)

--------------------------------------------------------------------------------
By Wing, 出處：Linuxaid


第五章：關(guān)系型數(shù)據(jù)庫設(shè)計

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

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

給予應(yīng)有的權(quán)限

　　在開始之前，讓我們回憶一下我們的Jokes數(shù)據(jù)表的結(jié)構(gòu)，這個表包含三個列：ID、JokeText和 JokeDate。這些列可以使我們標(biāo)識笑話（ID），明了他們的內(nèi)容（JokeText）以及他們被加入的時間（JokeDate）。

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

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



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


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



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


mysql> DESCRIBE Jokes;
+-------------+--------------+------+-----+-- - -
Field Type Null Key Def...
+-------------+--------------+------+-----+-- - -
ID int(11) PRI ...
JokeText text YES ...
JokeDate date ...
AuthorName varchar(100) YES ...
AuthorEMail varchar(100) YES ...
+-------------+--------------+------+-----+-- - -
5 rows in set (0.01 sec)



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

一個基本的規(guī)則：保持事物的分離

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

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

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

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


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

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

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

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

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

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

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


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
現(xiàn)在我們建立我們的新的數(shù)據(jù)表：
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;



　　現(xiàn)在剩下來的就是向新的表中添加一些作者，并通過填充AID列來對數(shù)據(jù)庫中已經(jīng)存在的笑話指定作者。

處理多個表

　　現(xiàn)在我們的數(shù)據(jù)被分布在兩個表當(dāng)中，要從其中獲得數(shù)據(jù)看上去變得更加復(fù)雜了。例如，我們最初的目標(biāo)是：顯示一個笑話的列表并在每一個笑話后面顯示作者的姓名和e-mail地址。在我們的單表結(jié)構(gòu)中，要獲得所有的信息，只需要在我們的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)中，這樣做初看起來是不可能了。因為有關(guān)每個笑話的作者的詳細(xì)資料不是存儲在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>" );
}



　　很混亂，而且對于每一個顯示的笑話都包含了一個對數(shù)據(jù)庫的查詢，這將會我們的頁面的顯示非常緩慢�，F(xiàn)在看來，“老方法”可能是更好的解決方案，盡管它有其自身的弱點。

　　幸運的是，關(guān)系型數(shù)據(jù)庫可以很容易地處理多個表中的數(shù)據(jù)！在SELECT語句中使用一個新的被稱之為“join”的格式，我們可以找到兩全其美的辦法。連接可以使我們象對存儲在單個表中的數(shù)據(jù)那樣對待多個表中的關(guān)聯(lián)數(shù)據(jù)。一個連接的格式應(yīng)該是這樣的：


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