创建查询
在过去的几年中，越来越多的网站开始集成关系数据库管理系统 (RDBMS)。数据库擅长快速轻松地存储和检索信息，并使 Web 开发人员能够创建复杂的应用程序，而不会陷入细节的困境。
虽然将数据库集成到网站中并不难，但有时设计数据库表以及构建使用它们的复杂应用程序可能很棘手。
上个月，我们研究了（规范化）这项技术，它可以减少潜在错误，同时提高数据库的速度和灵活性。这使得使用相同的数据创建各种应用程序变得更加容易。这是有代价的：从数据库中检索信息所需的查询变得更加复杂，通常需要连接来自两个、三个或四个表的数据。然而，经过一点培训，程序员可以轻松克服对大型连接的恐惧，使用它们只检索他们需要的信息。
大多数用户不是程序员，期望他们自己构建复杂的查询是不现实的。诀窍是创建一个用户界面，使其能够回答大部分可能的问题，而不会让用户感到各种选项不堪重负。
本月，我们将花一些时间研究如何从相对易于使用的界面生成复杂的 SQL 查询。我们的示例将借鉴上个月的示例表，该表描述了以色列的火车系统。
最后，我们将编写两种类型的程序。一些程序（在数据库行业中通常称为“查询生成器”）将生成可用于创建查询的 HTML 表单。其他程序会将 HTML 表单转换为实际的 SQL 查询，并在用户的浏览器中显示结果。
Linux 用户经常嘲笑 Macintosh 界面，因为它限制了可用选项。在 bash 提示符下，Linux 用户可以调用数千个命令。此外，任何程序的输出都可以通过管道传输到文件或另一个程序。这种大量的选项使 Linux 成为一个特别强大的系统，但同时也是一个难以学习和掌握的系统。
大多数用户对权力不感兴趣，而只是想完成他们的工作。给予用户过多的灵活性有时可能是一种障碍。在这种情况下，我们希望限制用户的选项，迫使他们做出我们的程序可以处理的选择。这是 GUI 的主要优势之一，它通过减少可用选项的数量来减少用户出错的机会。它还减少了程序可能接收的潜在输入数量，从而更容易测试应用程序。
例如，考虑一个 HTML 表单，要求用户输入他们的目的地火车站。许多 HTML 表单使用文本区域来输入此类信息，如下所示
<input type="text" name="destination" size="30">
允许用户以这种方式输入车站名称打开了潘多拉魔盒，迫使程序处理拼写错误、大小写问题，甚至是由空格引起的问题。数据库可能擅长很多事情，但它们确实需要精确的输入，并且必须完全匹配。
我们可以通过使用 <select> 列表来限制用户的操作，从而改善情况并简化我们的程序。此 <select> 列表中的每个 <option> 将对应于 RailStations 表中的一行，其中 value 属性设置为行的 “ID”，用户可见的文本设置为 name 属性。例如，我们可以这样做
<select name="destination"> <option value="1">Nahariya <option value="2">Akko <option value="3">Hof Hacarmel <option value="4">Tel Aviv Central <option value="5">Tel Aviv Hashalom <option value="6">Lod <option value="7">Rehovot <option value="8">Herzliya </select>
上面的 <select> 列表提供了比文本字段更可靠的输入。但是，它也存在一些问题。首先也是最重要的，将上述静态 HTML 放在文件中意味着 RailStations 表和 <select> 列表将不可避免地失去同步。从用户的角度来看，上面的 <select> 列表很难使用，因为它按照 ID 号而不是车站名称对项目进行排序。
我们可以使用查询生成器（一个 CGI 程序，根据数据库中的信息生成 HTML 表单）来解决这两个问题。清单 1（参见资源）包含一个简单的非 CGI 程序 select-list-from-table.pl，它根据 RailStations 表的当前内容生成上述 <select> 列表，并且车站按字母顺序排列。如果火车站的名称更改或添加了新车站，则由 select-list-from-table.pl 创建的 HTML 表单将立即反映新值。
由于 SELECT 语句返回的行可以按照 ORDER BY 子句指定的任何顺序返回，因此可以生成一个非字母顺序的 <select> 列表。例如，用户可能更喜欢按位置查看火车站列表。
清单 1 生成的 <select> 列表至少有一个问题。如果用户不关心 <select> 列表中的特定信息怎么办？例如，假设用户有兴趣在一天中的某个时间从特拉维夫前往雷霍沃特，但尚未决定何时出发。强迫用户从 <select> 列表中选择时间会使事情变得比应有的更困难。最好在每个 <select> 列表中都有一个“任何”选项，允许用户指示此特定字段可以包含任何信息。
实施此策略需要两件事。首先，可以保证一个 <option> 值不对应于现有行的主键。其次，创建最终查询的 CGI 程序将识别此值并相应地修改 SQL。
幸运的是，MySQL 和 Perl 的组合在这种情况下效果很好。MySQL 的自动递增主键从 1 开始，并递增到最大值。由于自动递增主键永远不能为零，我们可以在 <select> 列表中创建一个额外的 <option> 行
<option value="0" selected>Any
通过将此值设置为 0，我们确保它不能对应于任何实际行的主键。通过将其标记为“selected”，我们将其设置为 <select> 菜单的默认值。接受默认值的新访问者将获得最广泛的搜索，并且 WHERE 子句最少。每个选择非零值的 <option> 将向生成的 SQL 添加一个新的 WHERE 子句。
以下查询列出了从 Nahariya（ID 1）出发，终点站为特拉维夫（ID 5）的所有列车的时间
SELECT S.name, DT.departure_time
FROM RailStations S, DepartureTimes DT, Trains T,
StationLines SL
WHERE T.id = DT.train_id
AND T.line_id = SL.line_id
AND S.id = 1
AND SL.station_id = DT.station_id
AND DT.station_id = S.id
AND T.destination_id = 5
ORDER BY DT.departure_time
;
我们可以通过将两个 ID 号替换为占位符值，将上述查询转换为 CGI 程序。如果我们用 “origin” 和 “endpoint” HTML 表单元素的内容填充占位符，我们可以找到从一个车站开往特定终点站的所有列车的时间。这样的 CGI 程序 list-trains-to-endpoint.pl 位于清单 2（参见资源）中。
上面的代码工作正常，直到有人选择值 为 0 的 Any 项目之一。如果发生这种情况，MySQL 将不会从 SELECT 返回任何行，因为没有车站的 ID 为 0。解决方案是使查询的这些部分成为有条件的，仅在指示实际值时才插入它们。
我们通过在组装 SQL 语句的代码中间放置两个 “if” 语句来实现这一点。由于通用 ID 为 0，我们的程序可以通过将变量名放在括号内来测试是否设置了值，这意味着测试非零值
my $sql = "SELECT S.name, DT.departure_time ";
$sql .= "FROM RailStations S, DepartureTimes DT, Trains T, StationLines SL ";
$sql .= " WHERE T.id = DT.train_id ";
$sql .= " AND T.line_id = SL.line_id ";
if ($origin)
{
$sql .= " AND S.id = ? ";
push @placeholders, $origin;
}
$sql .= " AND SL.station_id = DT.station_id ";
$sql .= " AND DT.station_id = S.id ";
if ($endpoint)
{
$sql .= " AND T.destination_id = ? ";
push @placeholders, $endpoint;
}
$sql .= " ORDER BY DT.departure_time ";
清单 3（参见资源）包含一个程序 better-list-trains-to-endpoint.pl，它允许用户指定始发站、列车的终点站、两者都不指定或两者都指定。如果用户仅指定始发站，则程序将显示从该站出发的列车列表，而不考虑方向。如果用户仅指定终点站，则它将列出所有开往该站的列车。大多数应用程序不需要给予用户如此大的自由度，并且可能希望捕获两个元素都被赋值为 0 的输入。同时，允许用户用无意义的查询自娱自乐也没有什么坏处。
上面的查询工作正常，但它们忽略了处理火车时刻表时的一个关键问题：人们通常希望指定他们首选的出发或到达时间。当然，可以为用户提供一组 <select> 列表，对应于一天中的各个小时和分钟，让他们可以精细地控制组装的查询。我们也可以允许他们在文本字段中输入日期和时间，但与车站名称一样，此类输入出错的可能性太多了。
对于他们来说，可能更容易关联诸如 “上午”、“下午” 和 “晚上” 之类的时间信息，而不是指定小时。我们可以通过使用另一组 <select> 列表来做到这一点，这次将小时指定为值，并将小时设置为指定期间的结束时间。例如
<select name="time"> <option value="12:00">Morning <option value="17:00">Afternoon <option value="21:00">Evening <option value="24:00">Night </select>
我们可以通过询问早于 12:00 的列车来查找所有上午的列车，通过询问早于 17:00 的列车来查找下午的列车，依此类推。
通过在查询生成器表单中放置这样的 <select> 列表，我们可以确保用户能够找到他们的火车。如果我们担心太多火车会匹配他们的查询，我们可以向 HTML 表单添加另一个 <select> 列表，允许用户限制返回的火车数量。MySQL 在 SELECT 查询上支持 LIMIT 子句，从而可以返回最大行数。
请注意，LIMIT 将始终包含一个数值，因此可以直接插入到 SQL 查询中，而无需使用占位符。实际上，尝试在 LIMIT 子句中使用占位符将强制将数字用引号引起来，这将导致 MySQL 错误。
best-list-trains.pl，一个实现这两个想法（“时间” 元素和 “限制” 元素）的程序，位于清单 4（参见资源）中。
虽然让用户可以无限自由地在网站上输入信息很诱人，但通常最好尽可能限制他们的输入。创建基于 HTML 的简单查询生成器并不困难，一旦您掌握了窍门，甚至可能很容易。诀窍是以这样一种方式制定查询，即用户可以在尽可能少地了解底层数据库的情况下获得最大的信息。即使查询很容易创建，找到将这些查询转换为适合非程序员的语言的方法也可能是一个挑战。
