65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL Server 查询之美 - 第一部分

Mehdi Mousavi
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.52/5 (20投票s)

2002年10月15日

CPOL

14分钟阅读

viewsIcon

190975

详细讨论了 SQL 的 SELECT 语句。

摘要

乍一看，SQL 的 SELECT 语句似乎很简单，用于从数据库中检索一组行。然而，SELECT 语句的完整语法非常复杂，正确使用所有（甚至大部分）语法对于应用程序的性能至关重要。本系列文章将提供必要的信息，帮助您出色地驾驭这一切。

引言

SQL Server（发音为 see-cool）查询是 SQL Server 中被低估的宝藏。大多数人更喜欢处理易于理解和管理的查询，这样一来，他们通过在数据库和 Web 服务器之间引入不必要的往返来牺牲 SQL Server 的速度。今天，我们将讨论 SQL Server 查询的各个方面，并向您展示如何创建复杂的 SELECT 语句。是的，今天的文章将完全致力于 MS-SQL Server 2000 的 SELECT 语句。感谢 Microsoft 随其 SQL Server 一起提供的 pubs 和 Northwind 数据库，这样我们就不必从头开始创建一个数据库了。只需打开它，然后按照我的指导进行操作。

SELECT 语法

SQL 引入了 SELECT 语句来检索表中的数据。通过检索数据，我们指的是从一个或多个数据库中的一个或多个表中获取一行或多行以及一列或多列！实际上，SELECT 语句分为两个主要部分，即第一个子句和第二个子句。

SELECT select_list /*this is the first clause*/ 
  FROM table_source(s) /*this is the second clause*/

其中 `select_list` 是一个（或多个）列的名称，`table_source` 是要由查询检索的一个（或多个）表的名称。目前就到这里。启动您的 SQL Server 管理工具，转到 pubs 数据库，然后从左侧面板选择“表”项，右侧面板将刷新并显示 pubs 数据库中已包含的表名。右键单击 Authors 表，然后在打开的上下文菜单中，在“打开表”项下选择“返回所有行”。这将导致 SQL Server 返回 Authors 表中已存储的所有行。现在，单击工具栏上的“显示/隐藏 SQL 窗格”按钮。

我们现在可以开始探索了。正如您所见，Authors 表包含 9 列，分别是 `au_id`、`au_lname`、`au_fname`、`phone`、`address`、`city`、`state`、`zip` 和 `contract`。在 SQL 窗格中，编写以下 SELECT 语句：

SELECT state FROM authors

要执行查询，请单击工具栏中的“棕色感叹号”。您将获得 Authors 表的 `state` 列。

state
-----
CA
CA
CA
CA
CA
KS
CA
CA
CA
CA
TN
OR
CA
MI
IN
CA
CA
CA
MD
CA
CA
UT
UT
(23 row(s) affected)

如果您仔细观察，您会注意到有许多作者居住在 CA。如果我们想知道我们的作者来自多少个州呢？换句话说，我们不想得到 15 次 CA，2 次 UT 等。为此，我们使用 DISTINCT 关键字。

SELECT DISTINCT state FROM authors
state
----- 
CA
IN
KS
MD
MI
OR
TN
UT
(8 row(s) affected)

正如您所见，DISTINCT 关键字从结果集中消除了重复的行。现在，是时候计算上述查询返回到客户端的行数了。这可以通过聚合函数 COUNT 来完成。

SELECT COUNT(DISTINCT state) AS NUMBER_OF_ITEMS FROM authors
NUMBER_OF_ITEMS
----------------------
8
(1 row(s) affected)

上面的查询中的 AS 关键字究竟是做什么的？嗯，AS 关键字只是用于命名派生列。派生列是没有名称的列。为了更好地理解这一点，让我们以这种方式重写上述查询：

SELECT COUNT(DISTINCT state) FROM authors

这将产生以下结果集：

----------- 
8
(1 row(s) affected)

您会看到，该列没有名称！这样的列在 SQL Server 中称为派生列。有两种方法可以命名派生列：

  • 使用 AS 关键字（如上所述）。
  • 使用赋值运算符（如下）。
SELECT NUMBER_OF_ITEMS = COUNT(DISTINCT state) FROM authors
NUMBER_OF_ITEMS
----------------------
8
(1 row(s) affected)

但是，两者都应该做同样的事情。无论如何，上述 SQL 语句向我们展示了我们的作者来自 8 个不同的州。很棒，对吧？

现在，是时候从派生列转向聚合函数了。聚合函数是一个处理结果集并生成单个值的例程；即，您不能期望 COUNT 过程返回结果集！这不适用于聚合函数！它们只应向用户返回给定的统计信息。有许多聚合函数，包括但不限于 AVG、MAX、MIN、SUM 和 STDEV，它们分别计算给定行的平均值、最大值、最小值、总和和标准差。要记住的重要一点是，NULL 值会被聚合函数消除，因此对它们没有影响。

为了更好地理解这一点，让我们切换到 'titles' 表。正如您所见，Titles 表有一个名为 `price` 的列。假设我们要计算我们已库存的所有图书的总价。为此，我们只需执行以下查询：

SELECT SUM(price) AS TOTAL FROM titles
TOTAL
--------------------- 
236.2600
(1 row(s) affected)

但是，请注意，有 2 个 NULL 值价格被 SUM 聚合函数忽略了。关于聚合就到这里。让我们切换回 Authors 表，继续探索 SELECT 语句的第一个子句。

假设作者的详细信息已在 Authors 表中录入，一位接一位地录入，因此第一个姓名已在表中的作者比第二个作者更早注册。现在，假设我们需要列出最早注册的 5 位幸运作者的名字和姓氏。为此，我们将使用 TOP 关键字和我们的 SELECT 语句。

SELECT TOP 5 au_lname, au_fname FROM authors
au_lname au_fname
----------------------------------- -------------------- 
Bennet Abraham
Blotchet-Halls Reginald
Carson Cheryl
DeFrance Michel
del Castillo Innes
(5 row(s) affected)

除了直接要求前 5 行外，我们还可以要求 SQL Server 检索前 x% 的行。例如，要获得前 7% 的行，我们可以这样查询：

SELECT TOP 7 PERCENT au_lname, au_fname FROM authors
au_lname au_fname
----------------------------------- -------------------- 
Bennet Abraham
Blotchet-Halls Reginald
(2 row(s) affected)

由于 Authors 表总共有 23 条记录，指定 7% 的行将得到 2 行，因为：

23 * 7 / 100 = 1.61
round(1.61) = 2

所以，我们处理了 2 行！

现在，假设我们希望在 ASP 页面中显示作者的名字和姓氏。我们有两个选择。首先，我们可以使用 ASP 中的 `RecordSet` 对象获取作者的名字和姓氏，并如下所示显示它们：

Response.Write(oRS(“au_fname”) & “ “ & oRS(“au_lname”))

其次，我们可以要求 SQL Server 将名字和姓氏分隔为一个空格，作为单个列返回：

SELECT au_fname + SPACE(1) + au_lname AS au_fullname FROM authors
au_fullname
-------------------------------------
Abraham Bennet
Reginald Blotchet-Halls
Cheryl Carson
Michel DeFrance
Innes del Castillo
Ann Dull
Marjorie Green
Morningstar Greene
Burt Gringlesby
Sheryl Hunter
Livia Karsen
Charlene Locksley
Stearns MacFeather
Heather McBadden
Michael O'Leary
Sylvia Panteley
Albert Ringer
Anne Ringer
Meander Smith
Dean Straight
Dirk Stringer
Johnson White
Akiko Yokomoto
(23 row(s) affected)

其中 SPACE 函数返回一个由空格组成的字符串。例如，如果您想通过在名字和姓氏之间插入 3 个空格（而不是 1 个）来区分它们，您可以编写以下 SQL 语句：

SELECT au_fname + SPACE(3) + au_lname AS au_fullname FROM authors

无论如何，完成此操作后，我们可以编写以下代码在屏幕上显示作者的全名：

Response.Write(oRS(“au_fullname”))

现在的问题是，我们应该选择哪种方式，为什么。在回答这个问题之前，我恳请您永远记住两个简单的指导原则：

  • 如果您确实不想使用某些列，请不要检索它们。
  • 尽可能（在适用范围内）在数据库服务器端执行处理，而不是在客户端或 Web 服务器端。

如果您违反了其中一项规则，您就是在数据库和 Web 服务器之间引入了不必要的往返。我将在本文后面完全解释所有这些。现在，只需记住这两个简单的指导原则。

在上面的示例中，当您分别检索两列时，您正在尝试在 Web 服务器和调用进程之间移动不必要的结果集。然而，当您使用第二种方法获取记录时，您只是要求 SQL Server 将这两列作为单个列（例如 `au_fullname`）返回。由于第二种方法遵循了这两个指导原则，因此我们选择这种方式。没有如果，也没有但是。

您还可以使用其他 SQL 函数来填充结果集。例如，要以大写形式获取上述结果集，我们可以使用 UPPER 函数，如下所示：

SELECT UPPER(au_fname + SPACE(1) + au_lname) AS au_fullname FROM authors
au_fullname
------------------------------
ABRAHAM BENNET
REGINALD BLOTCHET-HALLS
CHERYL CARSON
MICHEL DEFRANCE
INNES DEL CASTILLO
ANN DULL
MARJORIE GREEN
MORNINGSTAR GREENE
BURT GRINGLESBY
SHERYL HUNTER
LIVIA KARSEN
CHARLENE LOCKSLEY
STEARNS MACFEATHER
HEATHER MCBADDEN
MICHAEL O'LEARY
SYLVIA PANTELEY
ALBERT RINGER
ANNE RINGER
MEANDER SMITH
DEAN STRAIGHT
DIRK STRINGER
JOHNSON WHITE
AKIKO YOKOMOTO
(23 row(s) affected)

标识列

为了继续我们的探索，让我们看看如何检索标识列。首先，看一下 'jobs' 表。它包含一个标识列，名为 `job_id`，其数据类型定义为 'smallint'。要检索此列，我们有两种选择。首先，传统的做法：

SELECT job_id FROM jobs

这会检索指定的列（`job_id`）。其次，使用 IDENTITYCOL 关键字：

SELECT IDENTITYCOL FROM jobs

但是，第二种选择可以在您知道 Jobs 表包含标识列的情况下使用！如果您尝试对不包含标识列的表（例如 authors）执行上述查询，您将收到一个错误。

检索表的列名

在某些情况下，您需要获取特定表的列。这个问题基本上有 3 种方法：

  1. 使用 `sp_help` 存储过程获取有关数据库对象的扩展信息。数据库对象指的是表、视图等（您可以在 BOL 中找到有关此过程的扩展信息）。
  2. 您可以使用以下 select 语句检索表的列：
    SELECT TOP 0 * FROM table_name
  3. 第三种方法，我个人更喜欢，是使用 SQL Server 的架构对象。例如，要检索 Authors 表的列名，您可以使用以下 SELECT 语句：
    SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'authors'

    这将产生以下结果集：

    column_name
----------------------------
au_id
au_lname
au_fname
phone
address
city
state
zip
contract
(9 row(s) affected)

命名约定

我们已经说过，SELECT 语句具有以下语法：

SELECT select_list FROM table_source(s)

我们没有提到的是，我们如何从两个（或更多）不同的数据库中检索某些列。要理解这一点，我们需要了解 SQL Server 中涉及的命名约定。SQL Server 的任何实例可能包含不同的数据库。每个数据库又可能包含许多其他表，而每个表又可能包含一个或多个列。在这些情况下，当您需要命名特定列时，您必须遵循以下方式：

<数据库名称>.<所有者>.<表名>.<列名>

当我们在处理单个数据库时，我们不需要提及完整的名称。相反，我们使用相对名称：

SELECT au_fname FROM authors

由于我们正在处理 Pubs 数据库，SQL Server 会自行管理所有其他事务。然而，正确的做法是使用 USE 关键字来指定我们要交互的数据库：

USE pubs
SELECT au_fname FROM authors

但是，使用完整名称也没有坏处：

SELECT pubs.dbo.authors.au_fname FROM pubs.dbo.authors

这意味着我们将从 Pubs 数据库的 Authors 表中获取作者的名字。现在，您可以将所有这些组合起来，从 Pubs 数据库中获取作者的名字，以及 Northwind 数据库的 Categories 表中的 `CatgoryID` 列：

SELECT pubs..authors.au_fname, Northwind..Categories.CategoryID
  FROM pubs..authors, Northwind..Categories

上面的查询强调了您可以省略表的所有者名称。这样，SQL Server（隐式地）将连接的创建者视为表的所有者。例如，如果您使用特定的成员 ID 连接到数据库并执行上述查询，则表的所有者将隐式地被视为同一成员 ID 的角色。这就是 SQL Server 如何管理检索数据库中的必要列。

表别名（相关名称）

什么是别名？别名是用于给“SELECT”语句增加可读性的昵称。它有时也称为“相关名称”。有两种方法可以为表分配别名：

  • table_name AS table_alias
  • table_name table_alias

考虑上一个示例中的以下 select 语句：

SELECT pubs..authors.au_fname, Northwind..Categories.CategoryID
   FROM pubs..authors, Northwind..Categories

要使用别名重写此查询，您需要为这两个表（pubs..authors 和 Northwind..Categories）分配两个不同的别名，例如 `a` 和 `c`：

SELECT a.au_fname, c.CategoryID FROM pubs..authors a, Northwind..Categories c

请注意，如果您为表指定了别名，则需要使用别名引用该特定表，因此以下查询是非法的：

SELECT pubs..authors.au_fname, c.CategoryID
  FROM pubs..authors a, Northwind..Categories c

安全问题

安全，恕我直言，是每个人都应该注意的有趣问题之一。您不能在不考虑安全问题的情况下在数据库服务器中存储一条或两条记录。如果一个角色被攻破，CC# 被盗怎么办？嗯？如果一个入侵者在没有适当权限的情况下创建新的帐户/密码对怎么办？

然而，考虑安全问题并不意味着您可以阻止每一个入侵者。我真诚地相信，无论您做什么来阻止入侵者，地球上总会有另一个人能够打破规则。所以，您能做的就是尽力阻止大多数入侵者——记住，您无法阻止所有人！

好吧！现在的问题是，安全与 SELECT 语句有什么关系。要理解这一点，您应该知道 SQL Server 中有 3 种类型的权限：

  1. 对象权限——涉及授予或撤销对对象的权限。通过对象，我们指的是数据和/或存储过程。某个用户是否有权访问某个数据库以查询特定表和/或视图？她是否有权在某个数据库的表中插入、更新或删除行？这就是“对象权限”发挥作用的地方。这种数据操作需要用户拥有称为“对象权限”的权限类别。
  2. 语句权限——涉及授予或撤销在数据库中创建数据库和/或表的权限。 CREATE TABLE、CREATE VIEW 和 CREATE PROCEDURE 是 SQL Server 中引入的三种语句权限。
  3. 隐含权限——规定对象的拥有者有权控制对象的操作。一旦“foo”用户拥有“bar”表，她就可以在“bar”表上添加和删除数据。隐含权限还规定“foo”用户可以控制其他用户如何与“bar”表交互。

有了这些信息，现在是时候说授予或撤销这三种类型的权限给用户是由数据库管理员完成的，因此程序员不必关心这些权限。那么，程序员还剩下什么？嗯。这就是我们考虑的“安全问题”，并在下面讨论。

首先，考虑一个典型的 ASP 应用程序，该应用程序应该允许授权用户登录系统。要开发这样的 ASP 应用程序，我们只需创建一个包含两个编辑框的表单，一个用于输入用户名，另一个用于获取访问者的密码。

<!—-login.asp file!-->
<form method="GET" action="login.asp">
    <table border="0" width="100%" cellspacing="0" cellpadding="5">
        <tr>
          <td width="13%"><font size="2" face="Verdana">User ID:</font></td>
          <td width="87%"><input type="text" name="UserID" size="20"></td>
        </tr>
        <tr>
          <td width="13%"><font size="2" 
              face="Verdana">Password:</font></td>
          <td width="87%"><input type="password" 
                         name="Password" size="20"></td>
        </tr>
        <tr>
          <td width="13%"></td>
          <td width="87%"><input type="submit" 
                   value="Submit" name="btnSubmit"></td>
        </tr>
    </table>
</form>

以“Mehdi”作为用户名，“mypass”作为密码，按下提交按钮时将生成以下 URL：

login.asp?UserID=Mehdi&Password=mypass&btnSubmit=Submit

因此，为了授权给定用户，我们可以简单地编写一个名为 `IsAuthorized` 的函数，如下所示：

Function IsAuthorized(szUser, szPassword)
    IsAuthorized = FALSE
    Set oConnection = Server.CreateObject("ADODB.Connection")
    szConnection = "provider=sqloledb; server=myserver; uid=myid; "
    szConnection = szConnection & "pwd=mypwd; database=mydb;" 
    oConnection.Open szConnection

    szSqlStatement = "SELECT * FROM Login WHERE UserID = '" & szUser
    szSqlStatement = szSqlStatement & "' AND Password = '" & szPassword & "'"

    Set oRS = Server.CreateObject("ADODB.Recordset")
    oRS.Open szSqlStatement, _
                oConnection, _
                adOpenStatic, _
                adLockOptimistic, _
                adCmdText

    If Not oRS.EOF Then
        IsAuthorized = TRUE
    End If

    oRS.Close()
    Set oRS = Nothing

    oConnection.Close()
    Set oConnection = Nothing
End Function

现在，让我们仔细看看我们已经写过的。再次，考虑一个入侵者使用以下用户 ID 访问我们的登录页面：' OR 1 = 1 --

在这种情况下实际发生了什么？为了理解这一点，让我们看看我们在 `IsAuthorized` 函数中根据上述条目构建的 SQL 语句：

SELECT * FROM Login WHERE UserID = '' OR 1 = 1 --' AND Password = ''

由于入侵者提到的两个连字符在 SQL-92 标准中表示用户提供的文本（即注释），因此连字符之后的内容不被视为 SQL 语句，因此上述语句简化为：

SELECT * FROM Login WHERE UserID = '' OR 1 = 1

好吧！您可能已经猜到了其余的。由于 1 等于 1，上述查询将返回 Login 表中已有的所有行。您可以看到入侵者既没有用户 ID 也没有密码，但他已被识别为授权会员。

在某些情况下，当入侵者将 ID 和密码对插入表中时，情况会变得更糟。那么，我们该如何防止滥用系统呢？基本上有 3 种方法可以防止这种情况：

  1. 创建一个存储过程，例如 `sp_is_authorized()`，在数据库端执行实际授权。这样，入侵者将无法绕过上述授权过程。指定的存储过程如下：
    CREATE PROCEDURE sp_is_authorized
    @user_id char(16), @password char(16) AS
    declare @nRet int
    select @nRet = count(*) from Login where 
            UserID = @user_id AND Password = @password
    return @nRet
GO

    我们将在本文后面讨论 SELECT 语句的这种用法。

  2. 使用 ADO 参数化查询重写 `IsAuthorized` 函数。
    Function IsAuthorized(szUser, szPassword)
    IsAuthorized = FALSE
    On Error Resume Next
    Set oConnection = Server.CreateObject("ADODB.Connection")

    szConnection = "provider=sqloledb; server=myserver; "
    szConnection = szConnection & "uid=myid; pwd=mypass; database=pubs;"

    oConnection.Open szConnection

    Set oCmd = Server.CreateObject("ADODB.Command")

    oCmd.ActiveConnection = oConnection
    oCmd.CommandText = _
      "select * from Login where UserID= ? AND Password = ?"
    oCmd.CommandType = adCmdText

    oCmd.Parameters.Append(oCmd.CreateParameter("UserID",
                                                adChar,
                                                adParamInput,
                                                16,
                                                szUser))
 
   oCmd.Parameters.Append(oCmd.CreateParameter("Password",
                                                adChar,
                                                adParamInput,
                                                16,
                                                szPassword))

    Set oRS = Server.CreateObject("ADODB.Recordset")
    Set oRS = oCmd.Execute()
    If Not oRS.EOF Then
        IsAuthorized = TRUE
    End If

    oRS.Close()
    Set oRS = Nothing

    oCmd.Close()
    Set oCmd = Nothing

    oConnection.Close()
    Set oConnection = Nothing
End Function
  3. 在检查给定用户 ID 和密码是否包含无效字符后，构建您的动态 SQL 语句。最常见的方法是使用正则表达式组件 `RegExp`，它随 IE 5.0 一起引入。由于描述它超出了本文的范围，我们将其留给读者。

关于这一点，我们最后要说的是，即使您使用 POST 方法（而不是 GET 方法）提交表单，上述情况仍然适用。我严格强调，使用 POST 方法提交表单仍然不安全，如果您不应用上述条件。入侵者可以通过多种方式将非法信息 POST 到您的表单，因此您不能仅靠此来依赖。因此，我们上面提到的适用于任何表单（无论是通过 POST 还是 GET 方法提交）的处理用户输入的情况。

结论

在本系列的第一篇文章中，我们讨论了 SELECT 语法及其基本功能。我们希望在本文的下一部分中继续探讨更多内容，包括但不限于 XML 相关功能和 JOIN。

任何评论、问题和/或建议，一如既往，都受到欢迎。它们确实能改善工作方式！

© . All rights reserved.