面向开发者的 SQL： 基本数据检索
开发人员编写强大 SQL 命令的指南，同时避免因对底层机制了解不全而带来的陷阱。
引言
作为一名软件开发人员，我经常被要求编写 SQL 查询以从数据库中检索数据。由于 DBA 也是我的职责之一，所以我发现这样做非常自然。然而，我遇到了一些不理解如何编写高效 T-SQL 命令的开发人员。本文旨在为开发人员编写，但对于想要提高技能的数据库开发人员同样有用。本文主要关注如何从数据库中检索数据的基本知识。
目的
在本文中，我将介绍如何从 SQL 数据库访问数据的基本知识，以及我对如何最好地使用这些工具的建议。这些技术中的大多数应该适用于任何现代版本的 Microsoft SQL。我将尽力指出哪些命令只在特定版本的 SQL 中可用。所有查询都旨在针对 AdventureWorks 数据库运行。Microsoft 在其网站上免费提供此示例数据库。
请注意，本文不打算深入讨论如何调用这些查询（直接从代码或通过存储过程）。我想说，如有疑问，请使用存储过程。
T-SQL 测试平台
本文附带了一个我开发的一个非常简单的测试平台，可以帮助您了解这些命令的运行方式。通过它，您可以安全地尝试不同的选项并混合搭配不同的命令。该工具并非生产就绪代码。它只是一种无需打开 SQL 命令窗口即可快速执行这些命令的方法。我为那些不想加载和构建项目的人提供了 EXE 文件。此项目还附带了我在本文中提供的所有示例。
标准命令
学习如何访问数据库中的数据时，首先要学习以下命令：SELECT、FROM、WHERE、ORDER BY。这些命令是大多数查询构建的基础。SELECT 告诉系统您想要哪些列，FROM 说明从何处获取列，WHERE 过滤结果，ORDER BY 将结果按所需顺序排列。以下是此类查询的基本示例
SELECT Name, ProductNumber, Color
FROM Production.Product
WHERE Color = 'Black'
ORDER BY Name ASC, ProductNumber ASC
Name ProductNumber Color
-------------------------------------------------- ------------------------- ---------------
Chainring CR-7833 Black
Full-Finger Gloves, L GL-F110-L Black
Full-Finger Gloves, M GL-F110-M Black
Full-Finger Gloves, S GL-F110-S Black
Half-Finger Gloves, L GL-H102-L Black
…
(93 row(s) affected)
最佳实践：ORDER BY 命令可以按升序或降序排列。这通过列名后的 ASC 或 DESC 指定。如果您未指定任何内容，则列按升序排序。但是，最好明确写出，而不是隐含。
最佳实践：虽然 SELECT * 将为您提供所有列，但如果可能，请避免使用此方法。通过指定您想要的列，您将减少将结果传输到客户端所需的带宽。
要点：您无需在 SELECT 语句中列出某列即可在 WHERE 子句中使用它。它只需要是 FROM 语句中引用的项目之一中的列。
要点：查询部分的评估顺序如下：FROM、WHERE、SELECT、ORDER BY。这意味着如果您在 SELECT 语句中创建别名（见下文），WHERE 语句将无法引用它（因为它尚未评估），但 ORDER BY 语句可以使用它。
要点：除非明确指定顺序，否则查询结果不会排序。似乎它们总是在没有 ORDER BY 语句的情况下以相同的顺序返回，但这是不可靠的。如果您希望记录以特定顺序返回，则必须指定它。
使用 WHERE 过滤结果
我们已经在上面讨论了如何使用 WHERE 语句，但我想更深入地探讨您可以使用 WHERE 语句做什么，更重要的是，您应该和不应该使用 WHERE 语句做什么。WHERE 语句的最佳用途是利用表的索引来快速限制结果集中返回的数据。例如，此查询利用了 Name 列上的非聚集唯一索引
SELECT Name,StandardCost
FROM Production.Product
WHERE Name = 'HL Bottom Bracket'
Name StandardCost
-------------------------------------------------- ---------------------
HL Bottom Bracket 53.9416
(1 row(s) affected)
然而，如果我们想要数据库中的所有底部支架，而不仅仅是 HL 底部支架呢？在这种情况下，我们可以在 WHERE 语句中使用 LIKE 关键字而不是等号，并使用百分号 (%) 表示多字符通配符。该查询将如下所示
SELECT Name,StandardCost
FROM Production.Product
WHERE Name LIKE '% Bottom Bracket'
Name StandardCost
-------------------------------------------------- ---------------------
HL Bottom Bracket 53.9416
LL Bottom Bracket 23.9716
ML Bottom Bracket 44.9506
(3 row(s) affected)
这给了我们想要的结果，但付出了相当大的代价。第一个查询使用了索引查找（非常高效），而第二个查询使用了索引扫描（效率低下）。原因是我们将通配符放在语句的开头。有时您无法避免这种情况。例如，在此查询中，要找到所有文本中包含“Bottom Bracket”的项目将非常困难。将通配符移动到文本中的任何其他位置将允许系统再次进行索引查找。
最佳实践：当 WHERE 部分有多个语句时，使用括号来确保正确的执行顺序。例如，` (Name='Wrench' AND Color='Blue') OR Color='Black'` 与 `Name='Wrench' AND (Color='Blue' OR Color='Black')` 不同。
最佳实践：如上所述，避免在搜索文本的开头放置通配符。这将显著提高查询的性能。
最佳实践：避免在 WHERE 语句的左侧执行函数。例如，以下查询在功能上与我们上面所做的查询等效，但它的性能甚至比我们现有性能不佳的查询还要差
SELECT Name,StandardCost
FROM Production.Product
WHERE SUBSTRING(Name,4,14) = 'Bottom Bracket'
Name StandardCost
-------------------------------------------------- ---------------------
LL Bottom Bracket 23.9716
ML Bottom Bracket 44.9506
HL Bottom Bracket 53.9416
(3 row(s) affected)
使用 TOP 限制结果
如果您只想返回特定数量的记录，可以使用 TOP 命令指定要返回的记录数量或记录百分比。请注意，这不会过滤结果，它只是从查询的顶部获取指定数量（或百分比）的记录并返回它们。此命令可以通过多种方式使用。一种有用的方法是，如果您想根据成本返回库存中排名前五的商品。您只需按降序对查询按成本排序，使最昂贵的产品位于顶部，然后您只需选择前五条记录。以下是这样做的示例
SELECT TOP 5 Name,StandardCost
FROM Production.Product
ORDER BY StandardCost DESC
Name StandardCost
-------------------------------------------------- ---------------------
Road-150 Red, 62 2171.2942
Road-150 Red, 44 2171.2942
Road-150 Red, 48 2171.2942
Road-150 Red, 52 2171.2942
Road-150 Red, 56 2171.2942
(5 row(s) affected)
如果您想根据价格返回产品的前百分之五，您只需修改上述语句，以 `SELECT TOP 5 PERCENT` 开头……
如果您想返回一组随机的行，请在 SELECT 中使用 TOP 语句，并在末尾添加 `ORDER BY newid()`，如下所示（我运行了两次以向您展示它是随机的）
SELECT TOP 3 Name,StandardCost
FROM Production.Product
ORDER BY NEWID()
Name StandardCost
-------------------------------------------------- ---------------------
ML Fork 77.9176
Mountain-500 Silver, 40 308.2179
HL Touring Frame - Yellow, 50 601.7437
(3 row(s) affected)
Name StandardCost
-------------------------------------------------- ---------------------
Mountain End Caps 0.00
LL Road Frame - Red, 52 187.1571
Mountain-200 Black, 38 1251.9813
(3 row(s) affected)
要点：我向您展示的查找最昂贵项目的方法没有考虑价格相同的项目。在我的示例中，返回的所有五个项目都具有相同的标准成本。如果我只想要前三个，它会给我哪些？答案是它找到的前三个。这个顺序可能会在没有通知的情况下改变，除非您指定了第二个要排序的列。
使用 JOIN 连接两个（或更多）表或查询
使用 JOIN 语句连接多个表或查询本身是一个很大的话题。虽然我可以深入介绍它，但 CodeProject 上已经有一篇很棒的文章很好地涵盖了这个问题。这是链接：Visual_SQL_Joins.aspx。它甚至还有图片。
我将只添加我对如何最好地实现联接的评论和想法。您会特别注意到这里，但也在本文的其余部分，我主张在您所做的一切中保持明确。不要假设下一个来的人会知道所有隐含的规则。此外，完整地写出内容通常会使查询更具可读性。
最佳实践：仅使用关键字 JOIN 表示 INNER JOIN。但是，请写出来。不要让下一个来的人记住它是 INNER 还是 OUTER。
最佳实践：您可以在 FROM 语句或 WHERE 语句中指定两个表如何连接。为了清晰起见，请在 FROM 语句中指定此信息。当您将其放入 WHERE 语句时，它会将连接信息与过滤信息混淆。
最佳实践：当您指定 OUTER JOIN 的类型（LEFT、RIGHT、FULL）时，可以省略关键字 OUTER。这可能更多是一种偏好，因为它变得明显是 OUTER 连接，但请包含关键字 OUTER。
使用 AS 关键字重命名项
有时，表的列名不是您想要的。它可能晦涩难懂（GL00101 – 这是 Microsoft Dynamics Great Plains 数据库中的实际列名）、太长（TheTotalNumberOfProducts）、太通用（Name）或仅仅不合您的心意。无论原因是什么，您都可以通过简单地添加 AS 关键字，后跟新名称来重命名列（只要新名称不与查询中现有列冲突）。这是一个示例
SELECT TOP 3 Name AS Product_Name, StandardCost
FROM Production.Product
WHERE Name LIKE 'Mountain%'
ORDER BY Product_Name ASC
Product_Name StandardCost
-------------------------------------------------- ---------------------
Mountain Bike Socks, L 3.3963
Mountain Bike Socks, M 3.3963
Mountain Bottle Cage 3.7363
(3 row(s) affected)
我将结果限制为三个，因为这是一个演示查询。请注意，我将 Name 列重命名为 Product_Name，以便更容易阅读。如果我们的查询同时包含客户姓名和产品名称，则更有意义。
我故意让这个查询有点棘手，只是为了展示我们已经讨论过的内容。请注意，WHERE 子句引用 Name 列，而 ORDER BY 子句引用 Product_Name 列。请记住，这是因为 WHERE 子句在 SELECT 之前评估，而 ORDER BY 在 SELECT 之后评估。如果我们要想在 WHERE 子句中使用 Product_Name 名称，我们可以这样做
SELECT TOP 3 *
FROM (
SELECT Name AS Product_Name, StandardCost
FROM Production.Product
) AS Prod
WHERE Product_Name LIKE 'Mountain%'
ORDER BY Product_Name ASC
Product_Name StandardCost
-------------------------------------------------- ---------------------
Mountain Bike Socks, L 3.3963
Mountain Bike Socks, M 3.3963
Mountain Bottle Cage 3.7363
(3 row(s) affected)
基本上，我们需要在括号内创建查询（没有限制），并将其用作 FROM 语句中的表。请注意，我们将整个嵌套查询命名为 Prod。您需要给查询命名，以便可以引用它。所有这一切的最终结果是相同的，只是我们可以在 WHERE 子句中使用列名 Product_Name。在这样做时，请注意将项目放在正确的查询中。例如，我将我的“TOP 3”限制器放在外部查询中而不是内部查询中，因为否则我会得到错误的结果（我们需要将 WHERE 应用于整个表，而不仅仅是三条记录）。
使用 BETWEEN 和 IN 查找数据范围
我们可以在 WHERE 子句中使用典型的运算符，如 >、< 和 = 来查找所需的数据。但是，我们还可以将一些额外的命令放入我们的工具箱中。我们将查看的第一个命令是 BETWEEN 运算符。以下是此运算符的用法示例
SELECT StandardCost
FROM Production.Product
WHERE StandardCost BETWEEN 1 AND 2
StandardCost
---------------------
1.8663
1.8663
1.4923
1.8663
(4 row(s) affected)
语法足够简单易读。此方法在语法上等同于以下语句
SELECT StandardCost
FROM Production.Product
WHERE StandardCost >= 1
AND StandardCost <=2
StandardCost
---------------------
1.8663
1.8663
1.4923
1.8663
(4 row(s) affected)
如果你查看执行计划，这两个语句实际上会评估为相同的计划，这意味着使用 BETWEEN 运算符不会带来性能上的增益或损失。我们最大的收获是更简洁的语句。请注意，BETWEEN 运算符包括下限和上限，因此如果有一个商品售价为 2 美元，它也会包含在此列表中。对于售价为 1 美元的商品也是如此。
我们接下来要看的运算符是 IN 运算符。此运算符告诉系统查找与列表中某个项目完全匹配的项。该列表可以包含两个或更多项。以下是 IN 运算符的用法示例
SELECT StandardCost
FROM Production.Product
WHERE StandardCost IN (1.8663, 44.9506, 1.4923)
StandardCost
---------------------
1.8663
1.8663
1.4923
1.8663
44.9506
(5 row(s) affected)
如果我们使用标准运算符写出这个表达式，它会是这样的
SELECT StandardCost
FROM Production.Product
WHERE StandardCost = 1.8663
OR StandardCost = 44.9506
OR StandardCost = 1.4923
StandardCost
---------------------
1.8663
1.8663
1.4923
1.8663
44.9506
(5 row(s) affected)
虽然执行计划没有写出此查询的翻译，但它对这两个查询执行相同的计划。同样，这意味着 IN 运算符不会导致任何性能增益或损失。它只是编写查询的一种更简单的方式。
高级主题 (Advanced Topics)
我故意没有涵盖一些高级主题，包括结果分组、临时表、参数以及其他重要的 T-SQL 主题。我打算在下一篇文章中涵盖这些以及更多内容。SQL 为开发人员提供了很多东西。虽然如果您能完整地阅读一两本好的 SQL 书籍以全面了解如何正确构建 SQL 语句会很好，但我打算在这些文章中为您提供精简版本。
结论
在本文中，我们学习了如何在 T-SQL 语句中使用基本命令和运算符。有了这些知识，您可以构建简单的命令来从 Microsoft SQL 的一个或多个表中收集数据。在此过程中，我们讨论了一些应该遵循的最佳实践以及在构建查询时应该注意的一些陷阱。我希望您从本文中至少发现了一些对您有益的东西。我感谢建设性的反馈，并期待您对如何改进本文的想法。
历史
- 2011 年 1 月 11 日：初始版本