用PHP和MySQL构建一个数据库驱动的网站(十)

mysql|数据|数据库

　　在我们目前的情况下，我们所需要的列是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 | kyank@attglobal.net |
| 2 | Joan Smith | joan@somewhere.net |
+----+------------+---------------------+
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 | kyank@attg...
| A man walked in | Kevin Yank | kyank@attg...
| Knock knock. Wh | Joan Smith | joan@somew...
+-------------------+------------+--------- -- -
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( "

$joketext
" .
        "(by ＜A HREF='mailto:$email'＞$name)

" );
}



　　随着你对数据库的使用，你会越来越发现连接的功能有多大的意义。例如，下面的查询用来显示所有由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 |
+---------------------+
| kevin@sitepoint.com |
| kyank@attglobal.net |
+---------------------+
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命令行中来解决问题。