用PHP和MySQL構建一個數據庫驅動的網站(十)
2024-07-24 12:56:16
供稿：網友
在我們目前的情況下，我們所需要的列是jokes表中的joketext列以及authors表中的name列和email列。jokes表和authors表的關聯條件是jokes表中的aid列的值等于authors表中的id列的值。下面是一個連接的例子（前兩個查詢只是用來顯示我們的兩個表中所包含的內容）：
mysql> select left(joketext,20), aid from jokes;
+----------------------+------+
| left(joketext,20) | aid |
+----------------------+------+
| why did the chicken | 1 |
| a man walked into a | 1 |
| knock knock. who's t | 2 |
+----------------------+------+
3 rows in set (0.00 sec)
mysql> select * from authors;
+----+------------+---------------------+
| id | name | email |
+----+------------+---------------------+
| 1 | kevin yank | [email protected] |
| 2 | joan smith | [email protected] |
+----+------------+---------------------+
2 rows in set (0.00 sec)
mysql> select left(joketext,15), name, email
-> from jokes, authors where aid = authors.id;
+-------------------+------------+--------- -- -
| left(joketext,15) | name | email
+-------------------+------------+--------- -- -
| why did the chi | kevin yank | [email protected]
| a man walked in | kevin yank | [email protected]
| knock knock. wh | joan smith | [email protected]
+-------------------+------------+--------- -- -
3 rows in set (0.00 sec)
現在明白了嗎？第三個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>" );
}
隨著你對數據庫的使用，你會越來越發現連接的功能有多大的意義。例如，下面的查詢用來顯示所有由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地址將會是很簡單的：
mysql> select email from authors, emails where
-> name="kevin yank" and aid=authors.id;
+---------------------+
| email |
+---------------------+
| [email protected] |
| [email protected] |
+---------------------+
2 rows in set (0.00 sec)
多對多的關系
ok，現在你有了一個發布在你的網站上的穩定增長的笑話數據庫。事實上，這種增長是非常迅速的，笑話的數量會變得難以管理！你的訪問者將面對一個龐大的頁面，在這個頁面上雜亂地排列了數以百計的笑話。現在，我們不得不考慮作一些變動了。
你決定將你的笑話放置到不同的目錄中，這些目錄可能是“knock-knock笑話”、“crossing the road笑話”、“lawyer笑話”和“political笑話”。記住我們之前的處理規則，因為我們的笑話目錄是一個不同類型的“事物”，所以我們要為它們建立一個新的數據表：
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”可能也是一個“lawyer”笑話。一個單個的笑話可能屬于許多目錄，每一個目錄也會包含許多笑話。這是一個多對多的關系。
許多沒有經驗的設計者又會想到將幾個數據存儲到一個列中，最直接的解決方案是在jokes表中增加categories列，并在其中列舉笑話所屬的目錄的id。現在適用我們的第二個處理規則了：如果你需要在一個列中存儲多個值，那證明你的設計可能是有缺陷的。
描述一個多對多關系的正確方法是使用一個“lookup”表。這個表不包含任何實際的數據，只是用來定義關聯的事物。這兒是我們這部分的數據庫設計的示意圖：
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構建一個內容管理系統。我們希望這個系統可以提供一個可定制的、安全的、基于web的界面來管理數據庫的內容，而不再是在mysql命令行中來解決問題。
