摘要

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

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

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


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

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

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

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

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

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

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



　　這將會(huì)在我們的數(shù)據(jù)表中增加一個(gè)叫AuthorName的列。其數(shù)據(jù)類型是一個(gè)可變長(zhǎng)度的字符串，其最大長(zhǎng)度是100個(gè)字符（這對(duì)于最復(fù)雜的名字應(yīng)該也是足夠了）。讓我們?cè)偬砑右涣杏脕肀４孀髡叩膃-mail地址：


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



　　要得到更多的有關(guān)ALTER命令的信息，請(qǐng)參看MySQL參考手冊(cè)。要確認(rèn)我們是不是正確地添加了兩列，你可以要求MySQL為我們對(duì)這個(gè)表進(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)



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

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

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

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

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

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


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

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

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

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

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

　　這個(gè)數(shù)據(jù)庫(kù)設(shè)計(jì)的最重要的特征是，因?yàn)槲覀円�存�?chǔ)兩種類型的事物（笑話和作者），所以我們?cè)O(shè)計(jì)兩個(gè)表。這是我們?cè)跀?shù)據(jù)庫(kù)設(shè)計(jì)中要遵守的一個(gè)基本規(guī)則：對(duì)于每一個(gè)要存儲(chǔ)其信息的實(shí)體（或事物），我們都應(yīng)該給他一個(gè)自己的表。

　　重新生成上面的數(shù)據(jù)是非常簡(jiǎn)單的（只要使用兩個(gè)CREATE TABLE 查詢就行了），但是因?yàn)槲覀兿胍�在做這些變動(dòng)時(shí)不會(huì)有破壞性的效果（也就是說不會(huì)丟失我們已經(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)
-> );
最后，我們?cè)谖覀兊腏okes表中添加AID列：
mysql> ALTER TABLE Jokes ADD COLUMN AID INT;



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

處理多個(gè)表

　　現(xiàn)在我們的數(shù)據(jù)被分布在兩個(gè)表當(dāng)中，要從其中獲得數(shù)據(jù)看上去變得更加復(fù)雜了。例如，我們最初的目標(biāo)是：顯示一個(gè)笑話的列表并在每一個(gè)笑話后面顯示作者的姓名和e-mail地址。在我們的單表結(jié)構(gòu)中，要獲得所有的信息，只需要在我們的PHP代碼中使用一個(gè)SELECT語(yǔ)句就行了：


$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)中，這樣做初看起來是不可能了。因?yàn)橛嘘P(guān)每個(gè)笑話的作者的詳細(xì)資料不是存儲(chǔ)在Jokes表中，我們可能想到的一個(gè)解決方案是我們對(duì)于我們想要顯示的笑話單獨(dú)地獲得這些資料。代碼將是這樣的：


// 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>" );
}



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

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


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