SQL进阶

在我们完成我们的Internet笑话数据库网站的例子的过程中，我们已经研究了许多结构化查询语言(SQL)的大部分问题。从一个CREATE TABLE查询的基本格式，到INSERT查询的两种语法，你现在也许还知道很多这样的命令。
在这一周中，我们将会学习一些新的我们以前没看到过的SQL的技巧，有些是因为的确很先进，也有些仅仅是因为“以前没接触过”。作为其中的典型情况，绝大部分是对我们已经了解的知识的扩充。让我们先从最复杂也是最容易让人搞糊涂的SQL命令：SELECT查询开始。
对SELECT的结果集进行排序
一个信息的长的列表如果能按一定的顺序排列，肯定能给我们带来方便。例如，如果在我们的数据库中有很多注册的作者，要从我们的Authors表的清单中找到某一个作者肯定是一件比较困难的事。虽然它首先会按数据库中插入的顺序排列(最老的记录在最前面，最新的记录在最后面)，但是你很快会注意到如果有记录被删除将会打乱这种顺序。
这意味着从最初的SELECT查询得到的结果集的排列顺序并不是让人信赖的。幸运的是，对于SELECT查询有一个可选项可以让我们根据指定的列对我们的结果集进行排序。以打印出我们的Authors表中的记录的清单为例。我们可以回忆一下，这个表共有三个列：ID、Name和eMail。因为ID列没有什么实际意义(它仅仅提供了与Jokes表中的作者的一个关联)，我们通常只需要列出剩下的两个列。下面是作者数据表的一个清单：
mysql> SELECT Name, eMail FROM Authors;


正如你看到的，这些记录并没有按什么顺序排列。对于一个短的清单这也许没什么关系，但是如果我们是从一个很长的作者清单(也许是几百个)中寻找一个指定的作者(例如是Amy Mathieson)的email地址，能够让作者的姓名按字母顺序排列肯定会对我们有帮助。你可以这样做：
mysql> SELECT Name, eMail FROM Authors ORDER BY Name;


现在记录是按作者姓名的字母顺序排列的。就象我们可以在SELECT语句中增加一个WHERE子句以过滤结果集一样，我们也可以增加一个ORDER BY子句使得结果集按指定列排序。
在排序的列后面增加DESC关键字，你可以以降序排列这些记录：
mysql> SELECT Name, eMail FROM Authors ORDER BY Name DESC;


你还可以在ORDER BY子句中使用以逗号分隔的一组列名，以使得记录首先按第一个列排序，对于一个列相同的再按第二个列排序。在ORDER BY子句中列出的任意列都可以使用DESC关键字来颠倒排列顺序。
设置LIMIT
我们经常是工作在一个很大的数据表中，但是往往我们只对其中的几条记录感兴趣。假如你是想要统计你的站点中的不同的笑话的受欢迎程序。你可以向你的Jokes表中增加一个名为TimesViewed的列。对于一个新笑话，它的初始值设为零，而这个笑话每被显示一次，就将这个值加一，这样你就可以对你的数据库中的每一个笑话被阅读的次数进行记数。
对于指定ID的笑话的TimesViewed列加一的PHP脚本如下：
$sql = "UPDATE Jokes SET TimesViewed=TimesViewed+1 ".
"WHERE ID=$id";
if (!mysql_query($sql)) {
echo("<P>Error adding to times viewed ".
"for this joke!</P> ");
}

利用这个"笑话显示计数"，你可以在你的站点的首页设置"最受欢迎的10个笑话"。使用ORDER BY TimesViewed DESC可以将笑话按TimesViewed从高到低排列，我们仅仅需要取出其中的前十名就行了。但是如果在我们的数据库中有数千个笑话，得到全部的列表可能是相当浪费处理时间的以及服务器的系统资源(例如内存和CPU的负载)的，因为实际上我们只需要其中的十个。
使用一个LIMIT子句，我们可以指定返回的结果的数目。在我们的这个例子中，我们只需要最先的十个：
$sql = "SELECT * FROM Jokes ORDER BY TimesViewed DESC LIMIT 10";
你也可以不用DESC从而得到最不受欢迎的十个笑话。
通常，你如果想要让用户查看一个很长的记录清单(也就是一个搜索的结果集)，你一次只想为他显示其中的几个。想想你上次使用搜索引擎寻找网站时，你就可以发现这种做法。你可以使用一个LIMIT子句指定清单显示的开始位置及最大数目来达到这种目的，例如，下面的查询会显示数据库中第21到25个最受欢迎的笑话：
$sql = "SELECT * FROM Jokes ORDER BY TimesViewed DESC LIMIT 20, 5";
记住，结果集中第一个记录的记录号是0。这样，第21个记录的记录号就是20。
对表进行锁定
现在请注意，在上面的UPDATE查询中，我们将TimesViewed的值加一以代替原来的值。
$sql = "UPDATE Jokes SET TimesViewed=TimesViewed+1 WHERE ID=$id";
如果你不知道可以这样做，你可能会先做一个SELECT来获取当前的值，将其加一，然后用一个UPDATE将计算出来的值取代原来的值。除了这样做会用到两次查询，从而耗费两倍的时间以外，这种方法还带来了一个危险。如果在你对这个新的值进行计算时，另一个人又查看了同一个笑话，你想想会发生什么情况？PHP脚本会为新的请求运行第二次。当它执行SELECT获取TimesViewed的"当前值"时，它会获得与第一次执行时一样的值，因为这个值还没有被更改。于是虽然程序被执行了两次，但是这个值只会被加一。看看发生了什么？两个用户查看了这个笑话，但是TimesViewed只是被加了一次！
在有些情况下，这种获取-计算-更改的过程是不可避免的，我们必须处理对于同时发生的两个请求可能会造成的冲突(就象我们前面所看到那样)。另外一种情况是我们有时必须针对同一项事务同时更改几个表(例如，在一个电子商务的网站，我们必须针对一项销售同时更改存货和销售表)。许多高级的数据库服务(例如Oracle、MS SQL Server等等)支持一种叫做"事务"的特征，这样我们可以将复杂的操作定义为必须同时执行，中间不会被中断。而目前MySQL还不支持事务，但是它有另一种解决方案！
在执行一个多重查询的操作时，通过对一个表或几个表进行"锁定"，你就可以获得唯一的访问以避免在操作过程中其它同时发生的操作可能造成的破坏。锁定一个表的语法相当简单：
LOCK TABLES tblName { READ | WRITE }
正如前面看到的，当锁定一个表时，你可以指定是"读锁定"还是"写锁定"。前一种可以防止其它进程改变这个表，但是可以允许其它进程读这个表。而后一种将禁止对这个表的所有访问。
当你对一个表进行锁定，工作完成后，你必须对表进行解锁以重新允许其它进程访问这个表：
UNLOCK TABLES
一个LOCK TABLES查询会对以前的其它锁定自动解锁；因此要安全地执行对多个表的操作，你必须用一个命令对所有的表进行锁定。下面是处理我们前面所说的电子商务网站的那种情况的PHP代码：
mysql_query("LOCK TABLES inventory WRITE, shipping WRITE");
// Perform the operation...
mysql_query("UNLOCK TABLES");
列名和表名的别名
在有些情况下，使用不同的名字来代表MySQL的列和表会给我们带来方便。让我们以一个航空公司的在线定票系统的数据库为例。为了表示这个航空公司所提供的航班，数据库包含两个表：Flights和Cities。在Flights表中的每一条记录表示一个实际的在两个城市之间的航班--这个航班的起点和终点。明显地，Origin和Destination应该是Flights中的两个列，而其它的列分别表示航班的日期和时间、飞机类型、航班号以及各项费用。
Cities表包含了与这个航空公司相关的所有城市的列表。这样，Flights表中的Origin和Destination都仅仅包含对应于Cities表中的记录的ID。现在，让我们看下面的查询。
要得到每个航班的起点：
mysql> SELECT Flights.Number, Cities.Name
-> FROM Flights, Cities
-> WHERE Flights.Origin = Cities.ID;



要得到每个航班的终点：
mysql> SELECT Flights.Number, Cities.Name
-> FROM Flights, Cities
-> WHERE Flights.Destination = Cities.ID;



现在我们如何通过一个简单的查询来同时获得每个航班的起点和终点呢？我们会试着这样做：
mysql> SELECT Flights.Number, Cities.Name, Cities.Name
-> FROM Flights, Cities
-> WHERE Flights.Origin = Cities.ID
-> AND Flights.Destination = Cities.ID;
Empty set (0.01 sec)

为什么没有工作呢？让我们再来看看这个查询，看看它究竟完成了什么功能。我们在Flights和Cities之间建立了连接，并列出了所有Origin和城市ID匹配而且Destination和城市ID匹配的记录的航班号、城市名和城市名(是的，是两个城市名)。换句话说，Origin、Destination都必须和城市ID相等！这样的结果是列出所有起点和终点相同的航班！除非你的航空公司提供游览航班，不会有记录满足这个条件(这样就出现了上面的"Empty set")。
我们所需要的是一种能够为每一个结果从Cities表中返回两个不同的记录(一个对应起点，另一个对应终点)的方法。如果我们有这个表的两个拷贝，一个叫做Origins，另一个叫做Destinations，这会容易得多，但是为什么要为同一个城市清单而做两个数据表呢？正确的解决方案是给Cities两个不同的临时名(别名)以满足这个查询。
通过在SELECT查询的FROM部分的表名后面使用AS Alias，我们可以赋予这个表一个临时名，并可以在查询的其它要用到这个表名的地方使用它。下面我们重新写我们的第一个查询(仅仅显示航班号和起点)，这一次我们给Cities表赋予了一个别名：Origins.
mysql> SELECT Flights.Number, Origins.Name
-> FROM Flights, Cities AS Origins
-> WHERE Flights.Origin = Origins.ID;

这对查询工作的方法并没有什么实质上的改变--事实上，它根本就没改变结果。如果我们分别用F和O来作为Flights和Cities的别名。这个查询可以写得更短一些。
现在让我们回到我们的有问题的查询。通过两次使用不同的别名引用Cities表，我们可以使用一个三表连接(其中有两个表实际上是同一个)来获得我们想要的效果：
mysql> SELECT Flights.Number, Origins.Name,
-> Destinations.Name
-> FROM Flights, Cities AS Origins,
-> Cities AS Destinations
-> WHERE Flights.Origin = Origins.ID
-> AND Flights.Destination = Destinations.ID;



你也可以为列名指定别名。例如，我们可以使用这种方法对上面结果中的两个"Name"列进行区分：
mysql> SELECT F.Number, O.Name AS Origin,
-> D.Name AS Destination
-> FROM Flights AS F, Cities AS O, Cities AS D
-> WHERE F.Origin = O.ID AND F.Destination = D.ID;



对SELECT的结果集进行分组
在第二章中，我们曾经看到过下面的查询，它告诉我们在我们的Jokes表中存储了多少笑话：
mysql> SELECT COUNT(*) FROM Jokes;


在这个查询中使用的MySQL函数COUNT属于一类特殊的被称之为"集合函数"或"分组函数"的函数。你可以从MySQL参考手册的7.4.13找到这类函数的全部列表。与其它函数不一样，这些函数会个别地影响SELECT查询结果中每一条记录，集合函数对所有结果进行分组，并返回单个的结果。在上面的例子中，COUNT返回了结果行的总数。
如果我们想要显示一个作者的列表同时还要显示它们发表的笑话的数目。你的第一个反应将可能是得到所有的作者及其ID，然后对每一个作者的ID使用SELECT的COUNT函数计算出他们发表的笑话的数目。PHP的代码(为了简单起见，我们没有对错误进行处理)看上去将是这样的：
// Get a list of all the authors
$authors = mysql_query( "SELECT Name, ID FROM Authors" );
// Process each author
while ($author = mysql_fetch_array($authors)) {
$name = $author["Name"];
$id = $author["ID"];
// Get count of jokes attributed to this author
$result = mysql_query(
"SELECT COUNT(*) AS NumJokes ".
"FROM Jokes WHERE AID=$id" );
$row = mysql_fetch_array($result);
$numjokes = $row["NumJokes"];
// Display the author & number of jokes
echo("<P>$name ($numjokes jokes)</P>");
}

请注意在第二个查询中使用了AS对COUNT(*)的结果赋予了一个别名(NumJokes)。
如果采用这种方法，需要n+1个独立的查询(这里n是数据库中作者的数目)。这么多的查询显然是我们不想看到的(如果有很多作者，这段脚本的执行将非常之慢而且还耗费大量的资源！)。幸运的是，SELECT的另一个较为高级的功能可以解决这个问题！
通过在SELECT查询中增加一个GROUP BY子句，你可以要求MySQL对查询结果按指定的列进行分组。而象COUNT这样的集体函数将对这些分组进行操作--而不再是将结果作为一个整体。例如，下面的这个单一的查询，就可以列出数据库中每个作者发表的笑话数：
mysql> SELECT Authors.Name, COUNT(*) AS NumJokes
-> FROM Jokes, Authors
-> WHERE AID = Authors.ID
-> GROUP BY AID;

通过将结果集根据作者的ID(AID)进行分组，我们得到了相对于每一个作者的结果。请注意我们也可以指定GROUP BY Authors.ID，这将会得到同样的结果(因为，根据WHERE子句，这两个列必须相等)。在多数情况下，你也可以使用GROUP BY Authors.Name，但是因为我们不能保证不同的作者不会有相同的名字(在这种情况下，这样的作者将会被混淆)，最好是使用ID列，因为这个列对每个作者是可以保证唯一的。
左连接
从上面的结果我们可以看出Kevin Yank有三个笑话，Joan Smith有一个，而Ted E. Bear有五个。这个结果没有显示出我们的第四个作者，Amy Mathieson，她还没有发表任何笑话。因为在Jokes表中没有任何记录的AID与她的ID匹配，所以对于她没有结果满足上面查询中的WHERE子句，因此在结果集中不包含她。
好象只有一个唯一的方法能解决这个问题，那就是在Authors表中增加另外的一个列以保存每一个作者发表的笑话数。对这个列的维护实在是个痛苦的事情，因为我们不得不记住每次在Jokes表中添加笑话、删除笑话或者是更新笑话(例如，AID的值发生了改变)时都同时对这个列进行更新。为了保持同步，每当我们做这样的改变时，我们都得使用LOCK TABLES。这看上去至少相当混乱！
MySQL提供了表连接的另外一种方法，叫做“左连接”，它就是用来解决这类问题的。要理解左连接和标准的连接有什么不同，我们首先必须回忆一下标准的连接是如何工作的。
MySQL通过列出两个表的所有可能的行的组合来执行两个表的标准连接。一种简单的情况是，对两个各有两行的标准连接会生成四行：表1中的行1和表2中的行1、表1中的行1和表2中的行2、表1中的行2和表2中的行1以及表1中的行2和表2中的行2。在这个结果的基础上，MySQL再根据WHERE子句确定哪些行实际被保留(例如，表1中的AID列必须与表2中的ID列匹配)。
而我们的目的是同时显示不与表2(例如，Jokes)中任何行匹配的表1(例如Authors)的行，由于上面的原因，标准连接不能满足我们的需要。而一个左连接可以实际这样的功能，左连接将强制显示第一个(左手的)表中的所有行，而不考虑这些行是否与第二个(右手的)表中的记录相匹配。对于在第二个表中没有匹配记录的这样的一些行，在结果集中第二个表的列置为NULL。
在MySQL中要执行两个表的左连接，你必须在FROM子句的两个表名中间用LEFT JOIN分隔，而不是你原来使用的逗号。然后你必须在第二个表名后面使用ON <condition>，这里<condition>定义了两个表中行匹配的标准(也就是你当初放在WHERE子句中的内容)。这里是我们的经过调整的查询，它用来列出作者及其发表的笑话数：
mysql> SELECT Authors.Name, COUNT(*) AS NumJokes
-> FROM Authors LEFT JOIN Jokes
-> ON AID = Authors.ID
-> GROUP BY AID;



稍等一下...怎么突然Amy Mathieson有了一个笑话？这肯定是不对的！事实上，这个查询仍然是错误的。COUNT(*)对每个作者返回的行进行了记数。如果你看看LEFT JOIN未分组前的结果，你就会明白发生了什么：
mysql> SELECT Authors.Name, Jokes.ID AS JokeID
-> FROM Authors LEFT JOIN Jokes
-> ON AID = Authors.ID;


0
明白了吗？Amy Mathieson的确是有一行。事实上，虽然这一行的笑话ID值是NULL，但是这并不影响COUNT(*)--它仍然将它看作一行来记数。如果在COUNT函数中你指定一个实际的列名(比如是Jokes.ID)来取代*，它会忽略这一列的 NULL值，正确的查询应该是这样的：
mysql> SELECT Authors.Name, COUNT(Jokes.ID) AS NumJokes
-> FROM Authors LEFT JOIN Jokes
-> ON AID = Authors.ID
-> GROUP BY AID;


1
使用HAVING对结果进行限制
如果我们仅仅是想要列出没有发表过笑话的作者，我们该怎么做呢？绝大多数用户会做这样的尝试：
mysql> SELECT Authors.Name, COUNT(Jokes.ID) AS NumJokes
-> FROM Authors LEFT JOIN Jokes
-> ON AID = Authors.ID
-> WHERE NumJokes = 0
-> GROUP BY AID;
ERROR 1054: Unknown column 'NumJokes' in 'where clause'

到现在，你可能不会再奇怪这个查询为什么没能正常工作。:)出错的原因是WHERE NumJokes = 0有错误，因为在WHERE子句中的条件对GROUP BY子句分组前的记录发生了影响。因此如果你在计数时想要排除包含单词"chicken"的笑话，你可以使用WHERE子句；然而，因为NumJokes列在GROUP BY进行分组前并不存在，我们需要使用另一种方法去设置这个条件。
对分组后的结果发生影响的条件必须用一个特别的HAVING子句来指定。这里是正确的查询：
mysql> SELECT Authors.Name, COUNT(Jokes.ID) AS NumJokes
-> FROM Authors LEFT JOIN Jokes
-> ON AID = Authors.ID
-> GROUP BY AID
-> HAVING NumJokes = 0;


2
有些条件既可以在HAVING子句中表示也可以在WHERE子句中表示。例如，如果你想要排除一个指定姓名的作者，你既可以在WHERE子句中使用Authors.Name != "AuthorName"，也可以在HAVING子句中使用Authors.Name != "AuthorName"，因为不论你是在分组发生前还是在分组发生后过滤出这个作者，所得到的结果都是一样的。在这样的情况下，你最好还是使用WHERE子句，因为MySQL会对这样的查询进行优化以提高速度。
结语
这一周，为了更好地支持MySQL的使用，我们了解了更多的结构化查询语言(SQL)的知识。我们着重讨论了SELECT的一些特征，利用它，我们几乎可以随心所欲地显示存储在数据库中的信息。合理地使用SELECT的这些特征，你可以让MySQL更好的工作，并且可以减轻PHP处理的负担。
还有一些查询类型我们没有介绍到(主要是对索引的操作)，MySQL还提供一个完整的内置函数库以完成诸如计算日期和格式化字符串这样的工作。要想真正精通MySQL，你还必须完成领会MySQL所提供的不同的列类型。例如，TIMESTAMP类型可以用来保存时间。从MySQL指南你可以看到所有有关这些的完整文档，你也可以参看Paul DuBois的"MySQL" (见我的回顾)。我建议将来你有机会时看看这些文档。
在本教程的最后--第十章中，我们会学习一些新的有关PHP的有用的特征。安全地发送email、处理文件上载等等。我保证这个结尾是不可错过的。
欢迎访问PHP专题

本文选自LinuxAID
（出处：www.clearcase/" target="_blank" >ccidnet.com）

原文转自：http://www.ltesting.net