■集合函数

• 其它常用的SQL 表达式， 函数，和过程

第十章“SQL 基础”向你初步介绍了 SQL。你学会了如何用 SELECT 语句进行查询，你还学会了如何建立自己的表。在这一章里，你将加深你的 SQL 知识。你将学习如何建立索引来加快查询速度。你还将学会如果用更多的 SQL 语句和函数来操作表中的数据。

建立索引

假设你想找到本书中的某一个句子。你可以一页一页地逐页搜索，但这会花很多时间。而通过使用本书的索引，你可以很快地找到你要搜索的主题。

表的索引与附在一本书后面的索引非常相似。它可以极大地提高查询的速度。对一个较大的表来说，通过加索引，一个通常要花费几个小时来完成的查询只要几分钟就可以完成。因此没有理由对需要频繁查询的表增加索引。

聚簇索引和非聚簇索引

假设你已经通过本书的索引找到了一个句子所在的页码。一旦已经知道了页码后，你很可能漫无目的翻寻这本书，直至找到正确的页码。通过随机的翻寻，你最终可以到达正确的页码。但是，有一种找到页码的更有效的方法。

首先，把书翻到大概一半的地方，如果要找的页码比半本书处的页码小，就书翻到四分之一处，否则，就把书翻到四分之三的地方。通过这种方法，你可以继续把书分成更小的部分，直至找到正确的页码附近。这是找到书页的非常有效的一种方法。

SQL Sever 的表索引以类似的方式工作。一个表索引由一组页组成，这些页构成了一个树

形结构。根页通过指向另外两个页，把一个表的记录从逻辑上分成和两个部分。而根页所指向的两个页又分别把记录分割成更小的部分。每个页都把记录分成更小的分割，直至到达叶级页。

索引有两种类型：聚簇索引和非聚簇索引。在聚簇索引中，索引树的叶级页包含实际的数据：记录的索引顺序与物理顺序相同。在非聚簇索引中，叶级页指向表中的记录：记录的物理顺序与逻辑顺序没有必然的联系。

聚簇索引非常象目录表，目录表的顺序与实际的页码顺序是一致的。非聚簇索引则更象书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。一本书也许有多个索引。例如，它也许同时有主题索引和作者索引。同样，一个表可以有多个非聚簇索引。

通常情况下，你使用的是聚簇索引，但是你应该对两种类型索引的优缺点都有所理解。

每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。通常你要对一个表按照标识字段建立聚簇索引。但是，你也可以对其它类型的字段建立聚簇索引， 如字符型，数值型和日期时间型字段。

从建立了聚簇索引的表中取出数据要比建立了非聚簇索引的表快。当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。例如，假设你用一个表来记录访问者在你网点上的活动。如果你想取出在一定时间段内的登录信息，你应该对这个表的 DATETIME

型字段建立聚簇索引。

对聚簇索引的主要限制是每个表只能建立一个聚簇索引。但是，一个表可以有不止一个非聚簇索引。实际上，对每个表你最多可以建立 249 个非聚簇索引。你也可以对一个表同时建立聚簇索引和非聚簇索引。

假如你不仅想根据日期，而且想根据用户名从你的网点活动日志中取数据。在这种情况下，同时建立一个聚簇索引和非聚簇索引是有效的。你可以对日期时间字段建立聚簇索引，对用户名字段建立非聚簇索引。如果你发现你需要更多的索引方式，你可以增加更多的非聚簇索引。

非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中 取数据的速度，它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量。

索引属性

这两种类型的索引都有两个重要属性：你可以用两者中任一种类型同时对多个字段建立索引（复合索引）；两种类型的索引都可以指定为唯一索引。

你可以对多个字段建立一个复合索引，甚至是复合的聚簇索引。假如有一个表记录了你的网点访问者的姓和名字。如果你希望根据完整姓名从表中取数据，你需要建立一个同时对姓字段和名字字段进行的索引。这和分别对两个字段建立单独的索引是不同的。当你希望同时对不止一个字段进行查询时，你应该建立一个对多个字段的索引。如果你希望对各个字段进行分别查询，你应该对各字段建立独立的索引。

两种类型的索引都可以被指定为唯一索引。如果对一个字段建立了唯一索引，你将不能向这个字段输入重复的值。一个标识字段会自动成为唯一值字段，但你也可以对其它类型的字段建立唯一索引。假设你用一个表来保存你的网点的用户密码，你当然不希望两个用户有相同的密码。通过强制一个字段成为唯一值字段，你可以防止这种情况的发生。

用 SQL 建立索引

为了给一个表建立索引，启动任务栏 SQL Sever 程序组中的 ISQL/w 程序。进入查询窗口后，输入下面的语句：

CREATE INDEX mycolumn_index ON mytable (myclumn)

这个语句建立了一个名为 mycolumn_index 的索引。你可以给一个索引起任何名字，但你应该在索引名中包含所索引的字段名，这对你将来弄清楚建立该索引的意图是有帮助的。

索引 mycolumn_index 对表 mytable 的 mycolumn 字段进行。这是个非聚簇索引，也是个非唯一索引。（这是一个索引的缺省属性）

如果你需要改变一个索引的类型，你必须删除原来的索引并重建 一个。建立了一个索

引后，你可以用下面的 SQL 语句删除它： DROP INDEX mytable.mycolumn_index

注意在 DROP INDEX 语句中你要包含表的名字。在这个例子中，你删除的索引是mycolumn_index，它是表 mytable 的索引。

要建立一个聚簇索引，可以使用关键字 CLUSTERED。）记住一个表只能有一个聚簇索引。

（这里有一个如何对一个表建立聚簇索引的例子：

CREATE CLUSTERED INDEX mycolumn_clust_index ON mytable(mycolumn)

如果表中有重复的记录，当你试图用这个语句建立索引时，会出现错误。但是有重复记录的表也可以建立索引；你只要使用关键字 ALLOW_DUP_ROW 把这一点告诉 SQL Sever 即可：

CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

WITH ALLOW_DUP_ROW

这个语句建立了一个允许重复记录的聚簇索引。你应该尽量避免在一个表中出现重复记录，但是，如果已经出现了，你可以使用这种方法。

要对一个表建立唯一索引，可以使用关键字 UNIQUE。对聚簇索引和非聚簇索引都可以使用这个关键字。这里有一个例子：

CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

这是你将经常使用的索引建立语句。无论何时，只要可以，你应该尽量对一个对一个表建立唯一聚簇索引来增强查询操作。

最后，要建立一个对多个字段的索引──复合索引──在索引建立语句中同时包含多

个字段名。下面的例子对 firstname 和 lastname 两个字段建立索引： CREATE INDEX name_index ON username(firstname,lastname)

这个例子对两个字段建立了单个索引。在一个复合索引中，你最多可以对 16 个字段进

行索引。

用事务管理器建立索引

用事务管理器建立索引比用 SQL 语句容易的多。使用事务管理器，你可以看到已经建立的索引的列表，并可以通过图形界面选择索引选项。

使用事务管理器你可以用两种方式建立索引：使用 Manage Tables 窗口或使用 Manage Indexes 窗口。

要用 Manage Tables 窗口建立一个新索引，单击按钮 Advanced Options(它看起来象一个前面有一加号的表)。这样就打开了 Advanced Options 对话框。这个对话框有一部分标名为 Primary Key（见图 11.1）。

图 11。1

要建立一个新索引，从下拉列表中选择你想对之建立索引的字段名。如果你想建立一个对多字段的索引，你可以选择多个字段名。你还可以选择索引是聚簇的还是非聚簇的。在保存表信息后，索引会自动被建立。在 Manage Tables 窗口中的字段名旁边，会出现一把钥匙。

你已经为你的表建立了“主索引”。主索引必须对不包含空值的字段建立。另外，主索

引强制一个字段成为唯一值字段。

要建立没有这些限制的索引，你需要使用 Manage Indexes 窗口。从菜单中选择Manage|Indexes，打开 Manage Indexes 窗口。在 Manage Indexes 窗口中，你可以通过下拉框选择表和特定的索引。（见图 11.2）。要建立一个新索引，从 Index 下拉框中选择 New Index.，然后就可以选择要对之建立索引的字段。单击按钮 Add，把字段加人到索引中。

图 11。2

你可以为你的索引选择许多不同的选项。例如，你可以选择该索引是聚簇的还是非聚簇的。你还可以指定该索引为唯一索引。设计好索引后，单击按钮 Build，建立该索引。

SQL 核心语句

在第十章，你学会了如何用 SQL SELECT 语句从一个表中取数据。但是，到现在为止， 还没有讨论如何添加，修改或删除表中的数据。在这一节中，你将学习这些内容。

插入数据

向表中添加一个新记录，你要使用 SQL INSERT 语句。这里有一个如何使用这种语句的例子：

INSERT mytable (mycolumn) VALUES (‘some data’)

这个语句把字符串’some data’插入表 mytable 的 mycolumn 字段中。将要被插入数据的字段的名字在第一个括号中指定，实际的数据在第二个括号中给出。

INSERT 语句的完整句法如下：

INSERT [INTO] {table_name|view_name} [(column_list)] {DEFAULT VALUES | Values_list | select_statement}

如果一个表有多个字段，通过把字段名和字段值用逗号隔开，你可以向所有的字段中插入数据。假设表 mytable 有三个字段 first_column,second_column,和 third_column。下面的 INSERT 语句添加了一条三个字段都有值的完整记录：

INSERT mytable (first_column,second_column,third_column) VALUES (‘some data’,’some more data’,’yet more data’)

如果你在 INSERT 语句中只指定两个字段和数据会怎么样呢？换句话说，你向一个表中插入一条新记录，但有一个字段没有提供数据。在这种情况下，有下面的四种可能：

• 如果该字段有一个缺省值，该值会被使用。例如，假设你插入新记录时没有给字段third_column

  提供数据，而这个字段有一个缺省值’some value’。在这种情况下， 当新记录建立时会插入值’some value’。

• 如果该字段可以接受空值，而且没有缺省值，则会被插入空值。

• 如果该字段不能接受空值，而且没有缺省值，就会出现错误。你会收到错误信息：

  The column in table mytable may not be null.

• 最后，如果该字段是一个标识字段，那么它会自动产生一个新值。当你向一个有标

识字段的表中插入新记录时，只要忽略该字段，标识字段会给自己赋一个新值。

删除记录

要从表中删除一个或多个记录，需要使用 SQL DELETE 语句。你可以给 DELETE 语句提供 WHERE 子句。WHERE 子句用来选择要删除的记录。例如，下面的这个 DELETE 语句只删除字段 first_column 的值等于’Delete Me’的记录：

DELETE mytable WHERE first_column=’Deltet Me’ DELETE 语句的完整句法如下：

DELETE [FROM] {table_name|view_name} [WHERE clause]

在 SQL SELECT 语句中可以使用的任何条件都可以在 DELECT 语句的 WHERE 子句 中使用。例如，下面的这个 DELETE 语句只删除那些 first_column 字段的值为’goodbye’或second_column 字段的值为’so long’的记录：

DELETE mytable WHERE first_column=’goodby’ OR second_column=’so long’

如果你不给 DELETE 语句提供 WHERE 子句，表中的所有记录都将被删除。你不应该有这种想法。如果你想删除应该表中的所有记录，应使用第十章所讲的 TRUNCATE TABLE 语句。

更新记录

要修改表中已经存在的一条或多条记录，应使用 SQL UPDATE 语句。同 DELETE 语句一样，UPDATE 语句可以使用 WHERE 子句来选择更新特定的记录。请看这个例子：

UPDATE mytable SET first_column=’Updated!’ WHERE second_column=’Update Me!’ 这个 UPDATE 语句更新所有 second_column 字段的值为’Update Me!’的记录。对所有被

选中的记录，字段 first_column 的值被置为’Updated!’。

下面是 UPDATE 语句的完整句法：

UPDATE {table_name|view_name} SET [{table_name|view_name}]

{column_list|variable_list|variable_and_column_list} [,{column_list2|variable_list2|variable_and_column_list2}… [,{column_listN|variable_listN|variable_and_column_listN}]] [WHERE clause]

如果你不提供 WHERE 子句，表中的所有记录都将被更新。有时这是有用的。例如，如果你想把表 titles 中的所有书的价格加倍，你可以使用如下的 UPDATE 语句：

你也可以同时更新多个字段。例如，下面的 UPDATE 语句同时更新first_column,second_column,和 third_column 这三个字段：

UPDATE mytable SET first_column=’Updated!’

Second_column=’Updated!’ Third_column=’Updated!’

WHERE first_column=’Update Me1’

用 SELECT 创建记录和表

你也许已经注意到，INSERT 语句与 DELETE 语句和 UPDATE 语句有一点不同，它一次只操作一个记录。然而，有一个方法可以使 INSERT 语句一次添加多个记录。要作到这一点， 你需要把 INSERT 语句与 SELECT 语句结合起来，象这样：

INSERT mytable (first_column,second_column) SELECT another_first,another_second

FROM anothertable

WHERE another_first=’Copy Me!’

这个语句从 anothertable 拷贝记录到 mytable. 只有表 anothertable 中字段another_first 的值为’Copy Me！’的记录才被拷贝。

当为一个表中的记录建立备份时，这种形式的 INSERT 语句是非常有用的。在删除一

个表中的记录之前，你可以先用这种方法把它们拷贝到另一个表中。

如果你需要拷贝整个表，你可以使用 SELECT INTO 语句。例如，下面的语句创建了一个名为 newtable 的新表，该表包含表 mytable 的所有数据：

SELECT * INTO newtable FROM mytable

你也可以指定只有特定的字段被用来创建这个新表。要做到这一点，只需在字段列表中指定你想要拷贝的字段。另外，你可以使用 WHERE 子句来限制拷贝到新表中的记录。下面的例子只拷贝字段 second_columnd 的值等于’Copy Me!’的记录的 first_column 字段。

SELECT first_column INTO newtable FROM mytable

WHERE second_column=’Copy Me!’

使用 SQL 修改已经建立的表是很困难的。例如，如果你向一个表中添加了一个字段， 没有容易的办法来去除它。另外，如果你不小心把一个字段的数据类型给错了，你将没有办法改变它。但是，使用本节中讲述的 SQL 语句，你可以绕过这两个问题。

例如，假设你想从一个表中删除一个字段。使用 SELECT INTO 语句，你可以创建该表的一个拷贝，但不包含要删除的字段。这使你既删除了该字段，又保留了不想删除的数据。

如果你想改变一个字段的数据类型，你可以创建一个包含正确数据类型字段的新表。

创建好该表后，你就可以结合使用 UPDATE 语句和 SELECT 语句，把原来表中的所有数据拷

贝到新表中。通过这种方法，你既可以修改表的结构，又能保存原有的数据。

集合函数

到现在为止，你只学习了如何根据特定的条件从表中取出一条或多条记录。但是，假如你想对一个表中的记录进行数据统计。例如，如果你想统计存储在表中的一次民意测验的投票结果。或者你想知道一个访问者在你的站点上平均花费了多少时间。要对表中的任何类型的数据进行统计，都需要使用集合函数。

Microsoft SQL 支持五种类型的集合函数。你可以统计记录数目，平均值，最小值， 最大值，或者求和。当你使用一个集合函数时，它只返回一个数，该数值代表这几个统计值之一。

统计字段值的数目

函数 COUNT（）也许是最有用的集合函数。你可以用这个函数来统计一个表中有多少条记录。这里有一个例子：

SELECT COUNT(au_lname) FROM authors

这个例子计算表 authors 中名字（last name）的数目。如果相同的名字出现了不止一次，该名字将会被计算多次。如果你想知道名字为某个特定值的作者有多少个，你可以使用 WHERE 子句，如下例所示：

SELECT COUNT(au_lname) FROM authors WHERE au_lname=’Ringer’

这个例子返回名字为’Ringer’的作者的数目。如果这个名字在表 authors 中出现了两次，则次函数的返回值是 2。

假如你想知道有不同名字的作者的数目。你可以通过使用关键字 DISTINCT 来得到该数目。如下例所示：

SELECT COUNT(DISTINCT au_lname) FROM authors

如果名字’Ringer’出现了不止一次，它将只被计算一次。关键字 DISTINCT 决定了只有互不相同的值才被计算。

通常，当你使用 COUNT（）时，字段中的空值将被忽略。一般来说，这正是你所希望

的。但是，如果你仅仅想知道表中记录的数目，那么你需要计算表中所有的记录─不管它是否包含空值。下面是一个如何做到这一点的例子：

SELECT COUNT(*) FROM authors

注意函数 COUNT（）没有指定任何字段。这个语句计算表中所有记录所数目，包括有空值的记录。因此，你不需要指定要被计算的特定字段。

函数 COUNT（）在很多不同情况下是有用的。例如，假设有一个表保存了对你站点的

质量进行民意调查的结果。这个表有一个名为 vote 的字段，该字段的值要么是 0，要么是1。0 表示反对票，1 表示赞成票。要确定赞成票的数量，你可以所有下面的 SELECT 语句：

SELECT COUNT(vote) FROM opinion_table WHERE vote=1

计算字段的平均值

使用函数 COUNT（），你可以统计一个字段中有多少个值。但有时你需要计算这些值的平均值。使用函数 AVG（），你可以返回一个字段中所有值的平均值。

假如你对你的站点进行一次较为复杂的民意调查。访问者可以在 1 到 10 之间投票，表

示他们喜欢你站点的程度。你把投票结果保存在名为 vote 的 INT 型字段中。要计算你的用户投票的平均值，你需要使用函数 AVG（）：

SELECT AVG(vote) FROM opinion

这个 SELECT 语句的返回值代表用户对你站点的平均喜欢程度。函数 AVG（）只能对数值型字段使用。这个函数在计算平均值时也忽略空值。

计算字段值的和

假设你的站点被用来出售卡片，已经运行了两个月，是该计算赚了多少钱的时候了。假设有一个名为 orders 的表用来记录所有访问者的定购信息。要计算所有定购量的总和， 你可以使用函数 SUM（）：

SELECT SUM(purchase_amount) FROM orders

函数 SUM （）的返回值代表字段 purchase_amount 中所有值的平均值。字段purchase_amount 的数据类型也许是 MONEY 型，但你也可以对其它数值型字段使用函数 SUM

（）。

返回最大值或最小值

再一次假设你有一个表用来保存对你的站点进行民意调查的结果。访问者可以选择从1 到 10 的值来表示他们对你站点的评价。如果你想知道访问者对你站点的最高评价，你可以使用如下的语句：

SELECT MAX(vote) FROM opinion

你也许希望有人对你的站点给予了很高的评价。通过函数 MAX（），你可以知道一个数值型字段的所有值中的最大值。如果有人对你的站点投了数字 10，函数 MAX（）将返回该值。

另一方面，假如你想知道访问者对你站点的的最低评价，你可以使用函数 MIN（），如下例所示：

SELECT MIN(vote) FROM opinion

函数 MIN（）返回一个字段的所有值中的最小值。如果字段是空的，函数 MIN（）返回空值。

其它常用的SQL 表达式，函数，和过程

这一节将介绍一些其它的 SQL 技术。你将学习如何从表中取出数据，其某个字段的值处在一定的范围，你还将学习如何把字段值从一种类型转换成另一种类型，如何操作字符串和日期时间数据。最后，你将学会一个发送邮件的简单方法。

通过匹配一定范围的值来取出数据

假设你有一个表用来保存对你的站点进行民意调查的结果。现在你想向所有对你的站点的评价在 7 到 10 之间的访问者发送书面的感谢信。要得到这些人的名字，你可以使用如下的 SELECT 语句：

SELECT username FROM opinion WHERE vote>6 and vote<11

这个 SELECT 语句会实现你的要求。你使用下面的 SELECT 语句也可以得到同样的结果： SELECT username FROM opinion WHERE vote BETWEEN 7 AND 10

这个 SELECT 语句与上一个语句是等价的。使用哪一种语句是编程风格的问题，但你会发现使用表达式 BETWEEN 的语句更易读。

现在假设你只想取出对你的站点投了 1 或者 10 的访问者的名字。要从表 opinion 中取出这些名字，你可以使用如下的 SELECT 语句：

SELECT username FROM opinion WHERE vote=1 or vote

这个 SELECT 语句会返回正确的结果，没有理由不使用它。但是，存在一种等价的方式。使用如下的 SELECT 可以得到相同的结果：

SELECT username FROM opinion WHERE vote IN (1,10)

注意表达式 IN 的使用。这个 SELECT 语句只取出 vote 的值等于括号中的值之一的记

录。

你也可以使用 IN 来匹配字符数据。例如，假设你只想取出 Bill Gates 或 President Clinton 的投票值。你可以使用如下的 SELECT 语句：

SELECT vote FROM opinion WHERE username IN ( ‘Bill Gates’,’President Clinton’) 最后，你可以在使用 BETWEEN 或 IN 的同时使用表达式 NOT。例如，要取出那些投票值

不在 7 到 10 之间的人的名字，你可以使用如下的 SELECT 语句：

SELECT username FROM opinion WHERE vote NOT BETWEEN 7 and 10

要选取那些某个字段的值不在一列值之中的记录，你可以同时使用 NOT 和 IN，如下例所示：

SELECT vote FROM opinion

WHERE username NOT IN (‘Bill Gates’,’President Clinton’)

你不是必须在 SQL 语句中使用 BETWEEN 或 IN，但是，要使你的查询更接近自然语言， 这两个表达式是有帮助的。

转换数据

SQL Sever 足够强大，可以在需要的时候把大部分数值从一种类型转换为另一种类型。例如，要比较 SMALLINT 型和 INT 型数据的大小，你不需要进行显式的类型转换。SQL Sever 会为你完成这项工作。但是，当你想在字符型数据和其它类型的数据之间进行转换时，你的确需要自己进行转换操作。例如，假设你想从一个 MONEY 型字段中取出所有的值，并在结果后面加上字符串“US Dollars”。你需要使用函数 CONVERT（），如下例所示：

SELECT CONVERT(CHAR(8),price)+’US Dollars’ FROM orders

函数 CONVERT（）带有两个变量。第一个变量指定了数据类型和长度。第二个变量指定了要进行转换的字段。在这个例子中，字段 price 被转换成长度为 8 个字符的 CHAR 型字段。字段 price 要被转换成字符型，才可以在它后面连接上字符串’US Dollars’。

当向 BIT 型，DATETIME 型，INT 型，或者 NUMERIC 型字段添加字符串时，你需要进行同样的转换操作。例如，下面的语句在一个 SELECT 语句的查询结果中加入字符串’The vote is’，该 SELECT 语句返回一个BIT 型字段的值:

SELECT ‘The vote is’+CONVERT(CHAR(1),vote) FROM opinion 下面是这个语句的结果示例：

The vote is 1 The vote is 1 The vote is 0

(3 row(s) affected)

如果你不进行显式的转换，你会收到如下的错误信息：

Implicit conversion from datatype ‘varchar’ to ‘bit’ is not allowec. Use the CONVERT function to run this query.

操作字符串数据

SQL Sever 有许多函数和表达式，使你能对字符串进行有趣的操作，包括各种各样的模式匹配和字符转换。在这一节中，你将学习如何使用最重要的字符函数和表达式。

匹配通配符

假设你想建立一个与 Yahoo 功能相似的 Internet 目录。你可以建立一个表用来保存一系列的站点名称，统一资源定位器（URL），描述，和类别，并允许访问者通过在 HTML form中输入关键字来检索这些内容。

假如有一个访问者想从这个目录中得到其描述中包含关键字 trading card 的站点的列表。要取出正确的站点列表，你也许试图使用这样的查询：

SELECT site_name FROM site_directory WHERE site_desc=’trading card’

这个查询可以工作。但是，它只能返回那些其描述中只有 trading card 这个字符串的站点。例如，一个描述为 We have the greatest collection of trading cards in the world! 的站点不会被返回。

要把一个字符串与另一个字符串的一部分相匹配，你需要使用通配符。你使用通配符和关键字 LIKE 来实现模式匹配。下面的语句使用通配符和关键字 LIKE 重写了上面的查询， 以返回所有正确站点的名字：

SELECT SITE_name FROM site_directory

WHERE site_desc LIKE ‘%trading cark%’

在这个例子中，所有其描述中包含表达式 trading card 的站点都被返回。描述为 We have the greatest collection of trading cards in the world!的站点也被返回。当然，如果一个站点的描述中包含 I am trading cardboard boxes online ，该站点的名字也被返回。

注意本例中百分号的使用。百分号是通配符的例子之一。它代表 0 个或多个字符。通过把 trading card 括在百分号中，所有其中嵌有字符串 trading card 的字符串都被匹配。

现在，假设你的站点目录变得太大而不能在一页中完全显示。你决定把目录分成两部

分。在第一页，你想显示所有首字母在 A 到 M 之间的站点。在第二页，你想显示所有首字母在 N 到 Z 之间的站点。要得到第一页的站点列表，你可以使用如下的 SQL 语句：

SELECT site_name FROM site_directory WHERE site_name LIKE ‘[A-M]%’

在这个例子中使用了表达式[A-M]，只取出那些首字母在 A 到 M 之间的站点。中括号（[]）

用来匹配处在指定范围内的单个字符。要得到第二页中显示的站点，应使用这个语句：

SELECT site_name FROM site_directory

WHERE site_name LIKE ‘[N-Z]%’

在这个例子中，括号中的表达式代表任何处在 N 到 Z 之间的单个字符。

假设你的站点目录变得更大了，你现在需要把目录分成更多页。如果你想显示那些以A，B 或 C 开头的站点，你可以用下面的查询来实现：

SELECT site_name FROM site_directory WHERE site_name LIKE ‘[ABC]%’

在这个例子中，括号中的表达式不再指定一个范围，而是给出了一些字符。任何一个其名字以这些字符中的任一个开头的站点都将被返回。

通过在括号内的表达式中同时包含一个范围和一些指定的字符，你可以把这两种方法结合起来。例如，用下面的这个查询，你可以取出那些首字母在 C 到 F 之间，或者以字母Y 开头的站点：

SELECT site_name FROM site_directory WHERE site_name LIKE ‘[C-FY]%’

在这个例子中，名字为 Collegescape 和 Yahoo 的站点会被选取，而名字为 Magicw3 的站点则不会被选取。

你也可以使用脱字符（^）来排除特定的字符。例如，要得到那些名字不以 Y 开头的站点，你可以使用如下的查询：

SELECT site_name FROM site_directory WHERE site_name LIKE ‘[^Y]%’

对给定的字符或字符范围均可以使用脱字符。

最后，通过使用下划线字符（_），你可以匹配任何单个字符。例如，下面这个查询返回每一个其名字的第二个字符为任何字母的站点：

SELECT site_name FROM site_directory WHERE site_name LIKE ‘M_crosoft’

这个例子既返回名为 Microsoft 的站点，也返回名为 Macrosoft 的站点。但是，名字为 Moocrosoft 的站点则不被返回。与通配符’％’不同，下划线只代表单个字符。

匹配发音

Microsoft SQL 有两个允许你按照发音来匹配字符串的函数。函数 SOUNDEX（）给一

个字符串分配一个音标码，函数 DIFFERENCE（）按照发音比较两个字符串。当你不知道一个名字的确切拼写，但多少知道一点它的发音时，使用这两个函数将有助于你取出该记录。

例如，如果你建立一个 Internet 目录，你也许想增加一个选项，允许访问者按照站点

名的发音来搜索站点，而不是按名字的拼写。考虑如下的语句：

SELECT site_name FROM site_directory

WHERE DIFFERENCE(site_name , ‘Microsoft’>3

这个语句使用函数 DEFFERENCE（）来取得其名字的发音与 Microsoft 非常相似的站点。函数 DIFFERENCE（）返回一个 0 到 4 之间的数字。如果该函数返回 4，表示发音非常相近； 如果该函数返回 0，说明这两个字符串的发音相差很大。

例如，上面的语句将返回站点名 Microsoft 和 Macrosoft。这两个名字的发音与Microsoft 都很相似。如果你把上一语句中的大于 3 改为大于 2，那么名为 Zicrosoft 和Megasoft 的站点也将被返回。最后，如果你只需要差别等级大于 1 即可，则名为 Picosoft 和 Minisoft 的站点也将被匹配。

要深入了解函数 DIFFERENCE（）是如何工作的，你可以用函数 SOUNDEX（）来返回函数 DIFFERENCE（）所使用的音标码。这里有一个例子：

SELECT site_name ‘site name’,SOUNDEX(site_name) ‘sounds like’

这个语句选取字段 site_name 的所有数据及其音标码。下面是这个查询的结果： site name sounds like

……………………………………………………………… . Yahoo Y000

Mahoo M000

Microsoft M262

Macrosoft M262

Minisoft M521

Microshoft M262

Zicrosoft Z262

Zaposoft Z121

Millisoft M421

Nanosoft N521

Megasoft M221

Picosoft P221

(12 row(s) affected)

如果你仔细看一下音标码，你会注意到音标码的第一个字母与字段值的第一个字母相同。例如，Yahoo 和 Mahoo 的音标码只有第一个字母不同。你还可以发现 Microsoft 和Macrosoft 的音标码完全相同。

函数 DIFFERENDE（）比较两个字符串的第一个字母和所有的辅音字母。该函数忽略任何元音字母（包括 y），除非一个元音字母是一个字符串的第一个字母。

不幸的是，使用 SOUNDEX（）和 DIFFERENCE()有一个欠缺。WHERE 子句中包含这两个

函数的查询执行起来效果不好。因此，你应该小心使用这两个函数。

删除空格

有两个函数，TTRIM（）和 LTRIM（），可以用来从字符串中剪掉空格。函数 LTRIM（）去除应该字符串前面的所有空格；函数 RTRIM（）去除一个字符串尾部的所有空格。这里有一个任何使用函数 RTRIM（）的例子：

SELECT RTRIM(site_name) FROM site_directory

在这个例子中，如果任何一个站点的名字尾部有多余的空格，多余的空格将从查询结果中删去。

你可以嵌套使用这两个函数，把一个字符串前后的空格同时删去：

SELECT LTRIM(RTRIM(site_name) FROM site_directory

你会发现，在从 CHAR 型字段中剪掉多余的空格时，这两个函数非常有用。记住，如果你把一个字符串保存在 CHAR 型字段中，该字符串会被追加多余的空格，以匹配该字段的长度。用这两个函数，你可以去掉无用的空格，从而解决这个问题。

操作日期和时间

日期和时间函数对建立一个站点是非常有用的。站点的主人往往对一个表中的数据何时被更新感兴趣。通过日期和时间函数，你可以在毫秒级跟踪一个表的改变。

返回当前日期和时间

通过函数 GETDATE（），你可以获得当前的日期和时间。例如，语句 SELECT GETDATE()返回如下的结果：

…………………………… ..

NOV 30 1997 3:29AM

(1 row(s) affected)

显然，如果你将来使用这个函数，你得到的日期将比这个时间晚，或者梗早。

函数 GETDATE（）可以用来作为 DATEDIME（）型字段的缺省值。这对插入记录时保存当时的时间是有用的。例如，假设有一个表用来保存你站点上的活动日志。每当有一个访问者访问到你的站点时，就在表中添加一条新记录，记下访问者的名字，活动，和进行访问的时间。要建立一个表，其中的记录包含有当前的日期和时间，可以添加一个 DATETIME 型字段，指定其缺省值为函数 GETDATE（）的返回值，就象这样：

CREATE TABLE site_log (

username VARCHAR(40), useractivity VARCHAR(100),

entrydate DATETIME DEFAULT GETDATE())

转换日期和时间

你也许已经注意到，在上一节的例子中，函数 GETDATE（）的返回值在显示时只显示到秒。实际上，SQL Sever 内部时间可以精确到毫秒级（确切地说，可以精确到 3.33 毫秒）。要得到不同格式的日期和时间，你需要使用函数 CONVERT（）。例如，当下面的这个语

句执行时，显示的时间将包括毫秒：

SELECT CONVERT(VARCHAR(30),GETDATE(),9)

注意例子中数字 9 的使用。这个数字指明了在显示日期和时间时使用哪种日期和时间格式。当这个语句执行时，将显示如下的日期和时间：

………………………………… ..

Nov 30 1997 3:29:55:170AM

(1 row(s) affected)

在函数 CONVERT（）中你可以使用许多种不同风格的日期和时间格式。表 11.1 显示了所有的格式。

类型 0，9，和 13 总是返回四位的年。对其它类型，要显示世纪，把 style 值加上 100。类型 13 和 14 返回 24 小时时钟的时间。类型 0，7，和 13 返回的月份用三位字符表示（用 Nov 代表 November）.

对表 11.1 中所列的每一种格式，你可以把类型值加上 100 来显示有世纪的年（例如，

00 年将显示为 2000 年）。例如，要按日本标准显示日期，包括世纪，你应使用如下的语句：

SELECT CONVERT（VARCHAR（30），GETDATE（），111）

在这个例子中，函数 CONVERT（）把日期格式进行转换，显示为 1997/11/30

抽取日期和时间

在许多情况下，你也许只想得到日期和时间的一部分，而不是完整的日期和时间。例如，假设你想列出你的站点目录中每个站点被查询的月份。这时你不希望完整的日期和时间把网页弄乱。为了抽取日期的特定部分，你可以使用函数 DATEPART（），象这样：

SELECT site_name ‘Site Name’,

DATEPART(mm,site_entrydate) ‘Month Posted’ FROM site_directory

函数 DATEPART（）的参数是两个变量。第一个变量指定要抽取日期的哪一部分；第二个变量是实际的数据。在这个例子中，函数 DATEPART（）抽取月份，因为 mm 代表月份。下面是这个 SELECT 语句的输出结果：

Site Name Month Posted

………………………………………………………………

Month Posted 列显示了每个站点被查询的月份。函数 DATEPART（）的返回值是一个整数。你可以用这个函数抽取日期的各个不同部分，如表 11.2 所示。

表 11.2 日期的各部分及其简写

当你需要进行日期和时间的比较时，使用函数 DATEPART（）返回整数是有用的。但是， 上例中的查询结果（2，5）不是十分易读。要以更易读的格式得到部分的日期和时间，你

可以使用函数 DATENAME（），如下例所示：

SELECT site_name ‘Site Name’ DATENAME(mm,site_entrydate) ‘Month Posted’

FROM site_directory

函数 DATENAME（）和函数 DATEPART（）接收同样的参数。但是，它的返回值是一个字符串， 而不是一个整数。下面是上例该用 DATENAME（）得到的结果：

Site Name Month Postec

………………………………………………………………… . Yahoo February

Microsoft June

Magicw3 June

(3 row(s) affected)

你也可以用函数 DATENAE（）来抽取一个星期中的某一天。下面的这个例子同时抽取一周中的某一天和日期中的月份：

SELECT site_name ‘Site Name’,

DATENAME(dw,site_entrydate)+ ‘-’ + DATENAME（mm,site_entrydate）

‘Day and Month Posted’ FORM site_directory 这个例子执行时，将返回如下的结果：

Site Name Day and Month Posted

………………………………………………………………………

Yahoo Friday - February

Microsoft Tuesday - June

Magicw3 Monday - June

(3 row(s) affected)

返回日期和时间范围

当你分析表中的数据时，你也许希望取出某个特定时间的数据。你也许对特定的某一天中――比如说 2000 年 12 月 25 日――访问者在你站点上的活动感兴趣。要取出这种类型的数据，你也许会试图使用这样的 SELECT 语句：

SELECT * FROM weblog WHERE entrydate=”12/25/20000”

不要这样做。这个 SELECT 语句不会返回正确的记录――它将只返回日期和时间是12/25/2000 12:00:00:000AM 的记录。换句话说，只有刚好在午夜零点输入的记录才被返回。

问题是 SQL Sever 将用完整的日期和时间代替部分日期和时间。例如，当你输入一个日期，但不输入时间时，SQL Sever 将加上缺省的时间“12：00：00：000AM”。当你输入一个时间，但不输入日期时，SQL Sever 将加上缺省的日期“Jan 1 1900”。

要返回正确的记录，你需要适用日期和时间范围。有不止一种途径可以做到这一点。

例如，下面的这个 SELECT 语句将能返回正确的记录：

SELECT * FROM weblog

WHERE entrydate>=”12/25/2000” AND entrydate<”12/26/2000”

这个语句可以完成任务，因为它选取的是表中的日期和时间大于等于 12/25/2000 12:00:00:000AM 并小于 12/26/2000 12:00:00:000AM 的记录。换句话说，它将正确地返回

2000 年圣诞节这一天输入的每一条记录。

另一种方法是，你可以使用 LIKE 来返回正确的记录。通过在日期表达式中包含通配符“％”，你可以匹配一个特定日期的所有时间。这里有一个例子：

SELECT * FROM weblog WHERE entrydate LIKE ‘Dec 25 2000%’

这个语句可以匹配正确的记录。因为通配符“％”代表了任何时间。

使用这两种匹配日期和时间范围的函数，你可以选择某个月，某一天，某一年，某个小时，某一分钟，某一秒，甚至某一毫秒内输入的记录。但是，如果你使用 LIKE 来匹配秒或毫秒，你首先需要使用函数 CONVERT（）把日期和时间转换为更精确的格式（参见前面“转换日期和时间”一节）。

比较日期和时间

最后，还有两个日期和时间函数对根据日期和时间取出记录是有用的。使用函数DATEADD（）和 DATEDIFF（），你可以比较日期的早晚。例如，下面的 SELECT 语句将显示表中的每一条记录已经输入了多少个小时：

SELECT entrydate ‘Time Entered’

DATEDIFF(hh,entrydate,GETDATE()) ‘Hours Ago’ FROM weblog

如果当前时间是 2000 年 11 月 30 号下午 6 点 15 分，则会返回如下的结果： Time Entered Hours Ago

………………………………………………… ..

(3 row(s) affected)

函数 DADEDIFF（）的参数是三个变量。第个变量指定日期的某一部分。在这个例子中， 是按小时对日期进行比较，（要了解日期各部分的详细内容，请参考表 11.2）在日期 2000

年 11 月 1 日和 2000 年 11 月 30 日的指定时间之间有 689 个小时。另外两个参数是要进行比较的时间。为了返回一个正数，较早的时间应该先给。

函数 DATEADD（）把两个日期相加。当你需要计算截止日期这一类的数据时，这个函数是有用处的。例如，假设访问者必须先注册才能使用你的站点。注册以后，他们可以免费使用你的站点一个月。要确定什么时候他们的免费时间会用完，你可以使用如下的 SELECT 语句：

SELECT username ‘User Name’, DATEADD(mm,1,firstvisit_date) ‘Registration Expires’

FROM registration_table

函数 DATEADD（）的参数有三个变量。第一个变量代表日期的某一部分（参见表 11.2），这个例子用到了代表月份的 mm。第二个变量指定了时间的间隔――在本例中是一个月。最后一个变量是一个日期，在这个例子中，日期是取自 DATETIME 型字段 firstvisit_date. 假设当前日期是 June 30,2000，这个语句将返回如下的内容：

User Name Registration Expires

……………………………………………………………………………

Bill Gates Jul 30 2000 4:09PM

President Clinton Jul 30 2000 4:13PM

William Shakespeare Jul 1 2000 4:09PM

(3 row(s) affected)

发送邮件

你可以用 SQL Sever 发送简单的 e_mail 信息。要做到这一点，你需要在你的系统中安装邮件服务器，如 Microsoft Exchange Sever(参见第四章“Exchange Active Sever,Index Sever,和 NetShow”)。你还需要配置 SQL Sever 以识别邮件服务器。

要让 SQL Sever 能识别邮件服务器，启动事务管理器并从菜单中选择 Sever|SQL

Mail|Configue，这时会出现一个如图 11.3 所示的对话框。输入你在邮件服务器中注册的用户名和口令，然后单击 OK。

图 11。3

在发送邮件之前，你要先启动 SQL Mail。从菜单中选择 Sever|SQL Mail|Start。如果你的邮件服务器配置正确，并且你输入了正确的用户名和口令，则 SQL Mail 会成功启动。

要发送一个邮件，你可以使用名为 xp_sendmail 的扩展存储过程。这里有一个如何使用这个过程的例子：

master..xp_sendmail “president@whitehouse.gov ”, ”Hello Mr. President ”

这个过程调用向 e_mail 地址president@whitehouse.gov发送一个简单的 email 信息： “Hello Mr. President ”。你可以用任何其它的 email 地址和信息取代上例中相应的内容， 但是，你所发送的信息不能超过 255 个字符长度。

当你想随时了解你的站点数据库的状态时，存储过程 xp_sendmail 是有用处的。例如， 你可以向一个页面管理程序发送信息。如果你的站点出了什么问题，你马上就可以知道。下一章将讲述更多有关存储过程的内容。

总结

这一章加深了你的 SQL 知识。你学会了如何建立索引，使你的查询速度更快。你还学会了如何插入，删除和更新一个表中的数据，如何使用集合函数得到一个表中数据的统计信息。最后，你学会了许多有价值的表达式，函数和过程，用来操作字符串，日期和时间及邮件。

以后将进一步加深你对 Microsoft SQL Sever 的掌握。你将学习如何用 SQL 来进行程序设计，如何建立存储过程，触发器和执行计划。更另人兴奋的是，你将学会让 SQL Sever 自动创建网页的一个简单方法。