• 项目

您好，脚本专家！再述数据库脚本

The Microsoft Scripting Guys

下载这篇文章的代码: HeyScriptingGuy2008_04.exe (151KB)

您知道， 如果脚本专家们有弱项（嘿，我们说的是如果），那就是：我们对于所采取操作或未采取操作的可能后果担忧过多。我们行事时不是以自己为出发点，而是常常从他人的出发点着想。而且并未考虑这些行为对我们意味着什么。

例如，以有关数据库脚本的文章为例。坦白地讲，有关数据库脚本的文章对于脚本专家而言很难下笔。并不是因为数据库脚本很难；它实际上非常简单。问题在于脚本专家们并没有太多机会操作数据库；因此，每次写到数据库时，我们都必须停下来想想我们要做些什么。您可能很早以前就已经看出来了，思考肯定并非成为脚本专家的必要条件。

事实上，如果我们中有任何人开始就有这种想法，脚本专家可能就不会出现了。

那么，既然有关数据库脚本的文章令脚本专家们如此难以下笔，那么为什么还要写呢？答案很简单：我们担心的是如果我们停止撰写这些文章，会对其他人造成怎样的影响。当亚瑟·柯南道尔爵士决定将夏洛克·福尔摩斯写死时（通过在故事“最后一案”中让他坠下悬崖），他认为他只是除去了他不愿意再写下去的一个角色。结果一石激起千层浪。据某些报道称，当伦敦居民听到消息时，他们甚至在帽子和袖子上戴上了丧布。“最后一案”刊登在 1893 年的《海滨杂志》上；文章中止后，有 20,000 人取消了其订阅。

哎，真让人挠头。

脚本专家们担心，如果不再撰写有关数据库脚本的文章也会在全球造成类似的影响。我们不想造成这么多伤心和失望，并且肯定不希望你们中有 20,000 人取消订阅《TechNet 杂志》**。因此，我们想要宣布脚本专家们会继续撰写有关数据库脚本的文章。什么时候写？嗯，现在如何？

向数据库添加记录

本月我们将向您展示一些极好的数据库使用小技巧。对，我们在 2007 年 9 月的专栏 (technetmagazine.com/issues/2007/09/HeyScriptingGuy) 中向您展示过一些技巧；但这次是新增的内容，脚本专家们还不至于差到一稿两投。

除非我们存在极大的侥幸心理。

我们首先来介绍通过一个简单的方法向数据库添加记录。假设我们有个名为 C:\Scripts\Inventory.mdb 的数据库，并且该数据库包含一个名为 Computers 的表。Computers 表包含以下字段：

  • ComputerName
  • SerialNumber
  • UserName
  • Department

现在，如何将一台新的计算机添加到该数据库中？请看一看图 1 中的代码。

Figure 1 Adding a record to the Computers table

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "INSERT INTO Computers (ComputerName, SerialNumber, UserName, Department)" &  _
    "VALUES ('atl-ws-07', '11234', 'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, adLockOptimistic

我们并不准备详细介绍该脚本的首行；如果需要该类背景信息，可参阅我们在脚本中心中提供的一些资源，网址为 microsoft.com/technet/scriptcenter。需要说明的是我们使用常量 adOpenStatic 和 adLockOptimistic 来控制记录集的游标类型和记录锁定。（是的，听起来令人印象深刻，但它的确相当简单。）在创建 ADODB.Connection 和 ADODB.Recordset 对象实例之后，我们使用以下命令来打开数据库：

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Scripts\Inventory.mdb"

顺便提一句，该命令打开的是 Microsoft® Access® 2003 数据库。要打开 Access 2007 数据库，改为使用以下命令：

objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = C:\Scripts\Inventory.accdb"

并且，虽说有些跑题，但我们还是要指出：可使用类似以下的命令来打开 SQL Server® 数据库：

objConnection.Open _
"Provider=SQLOLEDB;" & _
"Data Source=atl-sql-01;" & _
"Trusted_Connection=Yes;" & _ "InitialCatalog=Inventory;" & _
"User ID=fabrikam\kenmyer;Password=34DE6t4G!;"

到现在为止，我们向您展示的都是样板代码；您很快就会发现，几乎所有的数据库脚本都始于这几行代码。我们要强调的部分实际是以下这行：

objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName, Department) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, _
        adLockOptimistic

现在，我们将使用 INSERT INTO 查询来将一条新记录插入到 Computers 表中。请注意，表名之后是拥有值的所有字段的名称，通过逗号分隔这些字段并用括号将所有名称括起来。

您可能会问：脚本专家说“拥有值的所有字段”是什么意思？这是个好问题。好的，我们假设尚未为这台新计算机指定 Department。假定数据库允许在 Department 字段中放入 Null 值，可将 Department 放在列表外并且不指定值。换句话说，可编写类似如下所示的一个查询：

objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer')", _
        objConnection, adOpenStatic, _
        adLockOptimistic

说到指定值，请注意紧随字段名称之后的是：关键字 VALUES，后面是要指定给每个字段的值（也括在括号中）。请记住：在列出字段名称时，可任意排定其顺序；例如，我们可将 ComputerName 放在最前面，即使它在数据库中可能并非第一个字段。对于字段名称可以这样，但对于值就另当别论。值的顺序必须与字段完全相同。如果列出的第一个字段是 ComputerName，则第一个值必须是计算机的名称。否则就会出问题。（例如，我们最终将用户名或序列号分配给 ComputerName 字段。）

正如您所见，指定值并不太复杂；只需确保根据数据类型格式化值即可：日期和字符串值应用单引号引起来；数字和布尔值则不应用单引号引起来。

对了，如果您的值本身就有单引号（如姓名 O'Brien)，需写两个单引号来“转义”它：

'O''Brien'

很奇怪的表示法，但事实的确如此。

从数据库删除记录

实际上，这相当酷：仅执行一个 SQL 查询就能向数据库添加记录。还有比这更好的事吗？不敢再奢望了。

这就很让人满意了，当然，要是还能使用单个查询来从数据库删除多条记录更就锦上添花了。

假设您的公司决定撤消其人力资源部。（这不是玩笑；Microsoft HR 部就常有这样的难题。）如何从存储数据库删除所有的人力资源计算机？图 2 显示了一种实现方法。

Figure 2 Deleting multiple records

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "DELETE * FROM Computers Where Department='Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic

正如您所看到的，该特定脚本的开头与用于添加新记录的脚本一样：定义一些常量、创建几个对象，然后连接 Inventory.mdb 数据库。 之后，只需使用 DELETE 查询删除 Computers 表的所有记录 (*)，或者至少其 Department 字段等于 Human Resources 的所有记录：

objRecordSet.Open _
  "DELETE * FROM Computers " & _
   "Where Department='Human Resources'", _
    objConnection, adOpenStatic, _
    adLockOptimistic

它就像掉下悬崖一样简单。

对不起，不是故意冒犯您的，福尔摩斯先生。

更新数据库中的记录

毫无疑问，所有人都认同删除人力资源部门的所有相关内容相当有趣。（请 Microsoft HR 部门注意：我们的意思是其他人和他们的 HR 部门感到有趣。不是我们。我们绝没有从中取乐的意思）。但是，从存储数据库删除所有 HR 计算机可能并非上策。为什么呢？嗯，除非您的 HR 员工将所有硬件都藏起来了（听起来他们可能会这么做，哈哈，请别介意；不是闹着玩的），这些计算机仍属于您的公司；即这些计算机应仍然列在数据库中。明确这一点后，执行以下操作。不再从数据库删除这些记录，而是仅更新每条记录；即将每台计算机的部门名称从 Human Resources 更改为 None。换句话说，运行图 3 中所示的脚本。

Figure 3 Updating records

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "UPDATE Computers Set Department = 'None' " & _
    "Where Department = 'Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic

和之前一样，该脚本唯一的不同之处是 SQL 查询。这次，我们使用 UPDATE 查询来更新 Computers 表中的记录。除 UPDATE 关键字外，需添加两个参数来告知查询如何更新记录以及更新哪些记录：

  • Set Department = 'None'.它仅指定了 Department 字段的新值。实际上，可使用类似以下的语法来一次设置多个字段值：Set Department = 'None', UserName = 'None'.正如您所看到的，它将把 Department 和 UserName 字段都设成 None。
  • Where Department = 'Human Resources'.它是一个标准的 Where 语句，指出了应更新哪些记录；在本示例中，即是 Department 字段等于 Human Resources 的所有记录。使用这条语句的原因很简单：我们不想修改数据库中的所有记录，仅修改之前属于人力资源部的那些记录（计算机）。

使用 Update 查询还能完成一件很酷的事。假设您的公司决定为每个人增加 10% 的生活补助。（脚本专家与亚瑟·柯南·道尔爵士至少有一个方面明显相似：在我们的作品中，都是些虚幻的事情。）以下脚本将打开一个名为 Employees 的表并修改每名员工的 Salary 字段。那么它究竟将 Salary 字段设成什么呢？此查询将该字段设置为员工的当前工资乘以 1.1；换句话说，为每名员工提供 10% 的涨幅。（就是这么简单！）具体查询如下：

objRecordSet.Open _
  "Update Employees " & _
  "Set Salary = (Salary * 1.1)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

很酷的事吧？正如您所见，可在 Update 查询中执行计算。您决定要将所有临时雇员的合同期再延长 60 天吗？是的话可以使用类似如下的查询：

objRecordSet.Open _
  "Update TempEmployees " & _
  "Set ContractExpirationDate = " & _
  "(ContractExpirationDate + 60)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

用于检索数据的超酷方法

说到可派上用场的查询，我们再研究研究从数据库检索数据的一些好方法。例如，以下就是一个简单但非常有用的脚本。假设 Inventory 数据库包含一个名为 Price 的字段，该字段从逻辑上足够代表计算机的价格。想知道您组织中最贵的五台计算机是哪些吗？图 4 中的示例脚本会告知您答案。

Figure 4 Sorting the recordset

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Test.mdb" 

objRecordSet.Open "SELECT Top 5 * FROM Computers Order By Price", _
     objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("ComputerName")
    objRecordset.MoveNext
Loop

objRecordSet.Close
objConnection.Close

正如您所见，我们通过该 SQL 查询完成了两件事：按 Price 对记录集进行排序 (Order By Price)，选出前五个（也就是五个最贵的）计算机，即 SELECT Top 5 所实现的功能。如果需要前 10 个最贵的计算机的列表，使用以下查询：

objRecordSet.Open _
  "SELECT Top 10 * FROM Computers " & _
  "Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic

注意：我们还添加了一些代码，以遍历记录集并显示每台计算机的名称。之前的脚本并不需要此代码，因为那些脚本不返回也不显示数据。

也可使用以下查询获取前 10%：

objRecordSet.Open _
  "SELECT Top 10 PERCENT * " & _
  "FROM Computers Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic

现在，如果我们需要了解最便宜的计算机又该怎么办？没问题，只需使用相同的方法，只是此时以降序方式排序记录集（即从最低价格到最高价格）。换句话说，使用以下查询，并使用 DESC 来表示按降序方式排序记录集：

objRecordSet.Open _
  "SELECT Top 5 * FROM Computers " & _
  "Order By Price DESC", _
    objConnection, adOpenStatic, _
    adLockOptimistic

看到了吗？我们说过它们非常有用的。

还有两个您可能会觉得有趣的功能。假设您的数据库有个字段名为 Budgeted，用于跟踪计算机的最初预算金额。想要比较实际价格和预算金额吗？以下查询将返回价格高于预算金额的计算机的列表：

objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "Where (Budgeted < Price)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

同时，该查询会计算所有计算机的平均价格 (SELECT AVG(Price) FROM Computers)，然后返回价格低于平均价格的所有计算机的列表：

objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "WHERE Price < " & _
  "(SELECT AVG(Price) FROM Computers)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

是的，您说对了，我们确实在 Where 语句中使用了一个 SELECT 查询。它的工作原理是什么？那就是另一个故事了。但是，正如您所见，它可让您使用 SQL 查询来检索所有类型的超酷信息。

故事的内涵

正如我们在本月专栏开头提到的，我们并不确定是否会撰写另一篇有关数据库脚本的文章。但是，很高兴我们写了，部分原因是我们认为您会发现某些查询非常有用，但还因为：如果现在不写，迟早我们还是必须得写。就算是亚瑟·柯南道尔爵士，也不得不让夏洛克·福尔摩斯死而复生。亚瑟爵士尝试撰写了一些所谓发生在侦探死亡之前的新夏洛克·福尔摩斯故事。然后，迫于读者的压力，编造了一个有点可疑的故事来解释夏洛克·福尔摩斯谎报了自己的死亡。最终取得了相当圆满的结果。

更有趣的是，当最新的夏洛克·福尔摩斯故事面市后，又有 30,000 名新读者订阅了《海滨杂志》。英国和美国的发行商非常感谢亚瑟爵士，他也因此成为了全球稿酬最高的作者。

亚瑟·柯南道尔爵士带回了倍受喜爱的角色并成为全球最高稿酬的作者；现在脚本专家也带回了倍受喜爱的主题 — 并且无须伪造任何死亡事件。《TechNet 杂志》**的忠诚读者们注意到这点了吗？

Scripto 博士的脚本谜题

每月一次的挑战！不仅测试您的解谜技能，还测试您的脚本编写技能。

2008 年 4 月：选择字母

在本月的迷题中，您需要在蓝色区域中插入 A 到 O 之间的字母，从而显示某个 VBScript 函数的名称。每个字母（A 到 O）应仅使用一次，并且字母不以字母顺序出现。插入的字母可位于函数名称开头、函数名称结尾或者函数名称中间的任意位置。例如，可通过以下代码行插入字母 D 以完成函数名称 IsDate：

  (单击该图像获得较大视图)

现在您可以试试；插入 A 到 O 的字母来在此表格中显示 VBScript 函数名称：

**** (单击该图像获得较大视图)

ANSWER:

Scripto 博士的脚本谜题

答案：选择字母，2008 年 4 月

  (单击该图像获得较大视图)

The Microsoft Scripting Guys 为 Microsoft 工作，也就是受雇于 Microsoft。在玩、教或看棒球（以及各种其他活动）的闲暇之余，他们还负责维护 TechNet 脚本中心。要查看相关信息，请登录 www.scriptingguys.com。

© 2008 Microsoft Corporation 与 CMP Media, LLC.保留所有权利；不得对全文或部分内容进行复制.