第7章 数据生成、转换与操作

正如我在序言中提到的，本书致力于教授通用的SQL技术，这些技术可应用于多种数据库服务器。然而，本章涉及字符串、数字和时间数据的生成、转换与操作，而SQL语言并未包含涵盖这些功能的命令。相反，内置函数用于辅助数据的生成、转换和操作。虽然SQL标准规定了一些函数，但数据库供应商往往并不遵循这些函数规范。

因此，本章的讲解方式是，先向你展示在SQL语句中操作数据的一些常见方法，然后展示Microsoft SQL Server、Oracle Database和MySQL实现的一些内置函数。除了阅读本章内容，我强烈建议你购买一本涵盖你所使用服务器的所有函数的参考指南。如果你使用多种数据库服务器，有几本参考指南涵盖多个服务器，比如Kevin Kline等人编写的《SQL 基础教程》（SQL in a Nutshell，网址：http://oreilly.com/catalog/9780596518844/ ），以及Jonathan Gennick编写的《SQL口袋指南》（SQL Pocket Guide，网址：http://oreilly.com/catalog/9780596526887/ ），这两本书均由O’Reilly出版。

处理字符串数据

在处理字符串数据时，你会用到以下字符数据类型之一：

• CHAR：用于存储固定长度、以空格填充的字符串。MySQL中CHAR值的长度上限为255个字符，Oracle Database允许最多2000个字符，而SQL Server则允许最多8000个字符。
• varchar：用于存储可变长度的字符串。MySQL中varchar列的长度上限为65535个字符，Oracle Database（通过varchar2类型）允许最多4000个字符，SQL Server允许最多8000个字符。
• text（MySQL和SQL Server）或CLOB（Character Large Object，字符大对象；Oracle Database）：用于存储非常大的可变长度字符串（在这种情况下通常称为文档）。MySQL有多种text类型（tinytext、text、mediumtext和longtext），可存储大小达4GB的文档。SQL Server有单一的text类型，可存储大小达2GB的文档，Oracle Database包含CLOB数据类型，它可以存储高达128TB的文档。SQL Server 2005还包含varchar(max)数据类型，并建议使用它替代text类型，因为text类型将在未来的某个版本中从服务器上移除。

为了演示如何使用这些不同的数据类型，我在本节的部分示例中使用以下表：

CREATE TABLE string_tbl
 (char_fld CHAR(30),
  vchar_fld VARCHAR(30),
  text_fld TEXT
);

接下来的两个小节将展示如何生成和操作字符串数据。

字符串生成

填充字符列的最简单方法是将字符串用引号括起来，如下所示：

mysql> INSERT INTO string_tbl (char_fld, vchar_fld, text_fld)
    -> VALUES ('This is char data',
    ->         'This is varchar data',
    ->         'This is text data');
Query OK, 1 row affected (0.00 sec)

在向表中插入字符串数据时，请记住，如果字符串的长度超过了字符列的最大大小（无论是指定的最大值还是数据类型允许的最大值），服务器将抛出异常。虽然这是这三种服务器的默认行为，但你可以配置MySQL和SQL Server，使其在遇到这种情况时默默截断字符串，而不是抛出异常。为了演示MySQL如何处理这种情况，以下update语句尝试用一个长度为46个字符的字符串修改vchar_fld列，该列的最大长度定义为30：

mysql> UPDATE string_tbl
    -> SET vchar_fld = 'This is a piece of extremely long varchar data';
ERROR 1406 (22001): Data too long for column 'vchar_fld' at row 1

在MySQL 6.0中，默认行为现在是“严格”模式，这意味着遇到问题时会抛出异常，而在早期版本的服务器中，字符串会被截断并发出警告。如果你希望引擎截断字符串并发出警告，而不是抛出异常，可以选择进入ANSI模式。以下示例展示了如何查看当前所处的模式，以及如何使用SET命令更改模式：

mysql> SELECT @@session.sql_mode;
+-----------------------------------------------------------------+
| @@session.sql_mode                                              |
+-----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode='ansi';
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT @@session.sql_mode;
+--------------------------------------------------------------+
| @@session.sql_mode                                           |
+--------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI  |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

如果你重新运行前面的UPDATE语句，会发现该列已被修改，但会生成以下警告：

mysql> SHOW WARNINGS;
+---------+-------+--------------------------------------------------+
| Level   | Code  | Message                                          |
+---------+-------+--------------------------------------------------+
| Warning | 1265  | Data truncated for column 'vchar_fld' at row 1   |
+---------+-------+--------------------------------------------------+
1 row in set (0.00 sec)

如果你检索vchar_fld列，会发现字符串确实被截断了：

mysql> SELECT vchar_fld
    -> FROM string_tbl;
+-----------------------------------+
| vchar_fld                         |
+-----------------------------------+
| This is a piece of extremely l    |
+-----------------------------------+
1 row in set (0.05 sec)

可以看到，46个字符的字符串中只有前30个字符存入了vchar_fld列。在处理varchar列时，避免字符串截断（或者在Oracle Database或处于严格模式的MySQL中避免异常）的最佳方法是，将列的上限设置得足够高，以处理可能存储在该列中的最长字符串（要记住，服务器只会分配存储字符串所需的空间，所以为varchar列设置较高的上限不会造成浪费）。

包含单引号

由于字符串是用单引号界定的，所以遇到包含单引号或撇号的字符串时要格外注意。例如，你无法插入以下字符串，因为服务器会认为单词“doesn’t”中的撇号标志着字符串的结束：

UPDATE string_tbl
SET text_fld = 'This string doesn't work';

为了让服务器忽略“doesn’t”中的撇号，需要在字符串中添加转义字符，这样服务器就会将撇号视为字符串中的普通字符。这三种服务器都允许通过在单引号前直接添加另一个单引号来转义单引号，如下所示：

mysql> UPDATE string_tbl
    -> SET text_fld = 'This string didn''t work, but it does now';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1   Changed: 1   Warnings: 0

Oracle Database和MySQL的用户也可以选择在单引号前立即添加反斜杠字符来转义单引号，如下所示：

UPDATE string_tbl
SET text_fld = 'This string didn\'t work, but it does now'

如果你检索一个字符串用于屏幕显示或报表字段，处理嵌入的引号时无需特殊操作：

mysql> SELECT text_fld
    -> FROM string_tbl;
+----------------------------------------------+
| text_fld                                     |
+----------------------------------------------+
| This string didn't work, but it does now     |
+----------------------------------------------+
1 row in set (0.00 sec)

然而，如果你检索字符串是为了添加到另一个程序将读取的文件中，可能需要在检索到的字符串中包含转义字符。如果你使用的是MySQL，可以使用内置函数quote()，它会在整个字符串周围加上引号，并对字符串中的任何单引号/撇号添加转义字符。通过quote()函数检索我们的字符串时，结果如下：

mysql> SELECT quote(text_fld)
    -> FROM string_tbl;
+------------------------------------------------+
| QUOTE(text_fld)                                |
+------------------------------------------------+
| 'This string didn\'t work, but it does now'    |
+------------------------------------------------+
1 row in set (0.04 sec)

在检索数据用于数据导出时，对于所有非系统生成的字符列（如customer_notes列），你可能都希望使用quote()函数。

包含特殊字符

如果你的应用程序具有跨国使用的需求，可能会遇到包含键盘上没有的字符的字符串。例如，在处理法语和德语时，可能需要包含带重音符号的字符，如“é”和“ö”。SQL Server和MySQL服务器包含内置函数char()，这样你就可以从ASCII字符集中的255个字符里构建字符串（Oracle Database的用户可以使用chr()函数）。为了演示这一点，下一个示例检索一个输入的字符串及其通过单个字符构建的等效字符串：

mysql> SELECT 'abcdefg', CHAR(97, 98, 99, 100, 101, 102, 103);
+---------+--------------------------------------+
| abcdefg | CHAR(97, 98, 99, 100, 101, 102, 103) |
+---------+--------------------------------------+
| abcdefg | abcdefg                              |
+---------+--------------------------------------+
1 row in set (0.01 sec) 

因此，ASCII字符集中的第97个字符是字母“a”。虽然前面示例中显示的字符并不特殊，但以下示例展示了带重音符号的字符以及其他特殊字符（如货币符号）的位置：

mysql> SELECT CHAR(128,129,130,131,132,133,134,135,136,137);
+---------------------------------------------------+
| CHAR(128,129,130,131,132,133,134,135,136,137)     |
+---------------------------------------------------+
| Çüéâäàåçêë                                        |
+---------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CHAR(138,139,140,141,142,143,144,145,146,147);
+---------------------------------------------------+
| CHAR(138,139,140,141,142,143,144,145,146,147)     |
+---------------------------------------------------+
| èïîìÄÅÉæÆô                                        |
+---------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CHAR(148,149,150,151,152,153,154,155,156,157);
+---------------------------------------------------+
| CHAR(148,149,150,151,152,153,154,155,156,157)     |
+---------------------------------------------------+
| öòÛùÿ . . .Ü¢£¥                                   |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR(158,159,160,161,162,163,164,165);
+------------------------------------------------+
| CHAR(158,159,160,161,162,163,164,165)          |
+------------------------------------------------+
| fáíóúñÑ                                        |
+------------------------------------------------+
1 row in set (0.01 sec)

本节的示例使用的是latin1字符集。如果你的会话配置为不同的字符集，看到的字符会与这里展示的不同。虽然概念是相同的，但你需要熟悉自己使用的字符集布局，才能找到特定的字符。

逐个字符构建字符串可能非常繁琐，尤其是当字符串中只有少数几个字符带重音符号时。幸运的是，你可以使用concat()函数连接单个字符串，其中一些字符串可以直接输入，另一些则可以通过char()函数生成。例如，以下示例展示了如何使用concat()和char()函数构建短语“dankeschön”：

mysql> SELECT CONCAT('danke sch', CHAR(148), 'n');
+--------------------------------------+
| CONCAT('danke sch', CHAR(148), 'n')  |
+--------------------------------------+
| danke schön                          |
+--------------------------------------+
1 row in set (0.00 sec)

Oracle Database的用户可以使用连接运算符（||）替代concat()函数，如下所示：

SELECT 'danke sch' || CHR(148) || 'n'
FROM dual;

SQL Server没有concat()函数，所以你需要使用连接运算符（+），如下所示：

SELECT 'danke sch' + CHAR(148) + 'n'

如果你有一个字符，需要查找它的ASCII等效值，可以使用ascii()函数，该函数会获取字符串中最左边的字符并返回一个数字：

mysql> SELECT ASCII('ö');
+-----------+
| ASCII('ö') |
+-----------+
|       148 |
+-----------+
1 row in set (0.00 sec)

使用char()、ascii()和concat()函数（或连接运算符），即使你使用的键盘没有带重音符号或特殊字符，也应该能够处理任何基于罗马字母的语言。

字符串操作

每个数据库服务器都包含许多用于操作字符串的内置函数。本节将探讨两种类型的字符串函数：返回数字的函数和返回字符串的函数。不过，在开始之前，我将string_tbl表中的数据重置为以下内容：

mysql> DELETE FROM string_tbl;
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO string_tbl (char_fld, vchar_fld, text_fld)
-> VALUES ('This string is 28 characters',
-> 'This string is 28 characters',
-> 'This string is 28 characters');
Query OK, 1 row affected (0.00 sec)

返回数字的字符串函数

在返回数字的字符串函数中，最常用的函数之一是length()函数，它返回字符串中的字符数（SQL Server用户需要使用len()函数）。以下查询将length()函数应用于string_tbl表中的每一列：

mysql> SELECT LENGTH(char_fld) char_length,
-> LENGTH(vchar_fld) varchar_length,
-> LENGTH(text_fld) text_length
-> FROM string_tbl;
+----------------+-------------------+----------------+
| char_length    | varchar_length    | text_length    |
+----------------+-------------------+----------------+
|              28|                 28|              28|
+----------------+-------------------+----------------+
1 row in set (0.00 sec)

虽然varchar和text列的长度符合预期，但你可能原本期望char列的长度为30，因为我之前提到过，存储在char列中的字符串会在右侧用空格填充。然而，MySQL服务器在检索char数据时会删除尾随空格，所以无论字符串存储在何种类型的列中，你从所有字符串函数中看到的结果都是相同的。

除了查找字符串的长度，你可能还想查找子字符串在字符串中的位置。例如，如果你想查找字符串'characters'在vchar_fld列中出现的位置，可以使用position()函数，如下所示：

mysql> SELECT POSITION('characters' IN vchar_fld)
-> FROM string_tbl;
+-------------------------------------+
| POSITION('characters' IN vchar_fld) |
+-------------------------------------+
|                                   19|
+-------------------------------------+
1 row in set (0.12 sec)

如果找不到子字符串，position()函数将返回0。

对于使用C或C++等语言编程的人来说，数组的第一个元素位于位置0，但在处理数据库时要记住，字符串中的第一个字符位于位置1。position()函数返回0表示找不到子字符串，而不是表示子字符串在字符串的第一个位置被找到。

如果你不想从目标字符串的第一个字符开始搜索，就需要使用locate()函数。locate()函数与position()函数类似，不同之处在于它允许有一个可选的第三个参数，用于定义搜索的起始位置。locate()函数是特定数据库厂商专有的，而position()函数是SQL:2003标准的一部分。以下是一个示例，用于查找字符串'is'在vchar_fld列中从第五个字符开始的位置：

mysql> SELECT LOCATE('is', vchar_fld, 5)
-> FROM string_tbl;
+--------------------------------+
| LOCATE('is', vchar_fld, 5)     |
+--------------------------------+
|                             13 |
+--------------------------------+
1 row in set (0.02 sec)

Oracle数据库不包含position()或locate()函数，但它包含instr()函数。当提供两个参数时，instr()函数的功能与position()函数类似；当提供三个参数时，其功能与locate()函数类似。SQL Server也不包含position()或locate()函数，但它包含charindx()函数，该函数也接受两个或三个参数，与Oracle的instr()函数类似。

另一个以字符串为参数并返回数字的函数是字符串比较函数strcmp()。strcmp()函数仅在MySQL中实现，在Oracle数据库和SQL Server中没有类似的函数。它接受两个字符串作为参数，并返回以下值之一：

• 如果第一个字符串在排序顺序中位于第二个字符串之前，则返回 -1。
• 如果两个字符串相同，则返回0。
• 如果第一个字符串在排序顺序中位于第二个字符串之后，则返回1。

为了说明该函数的工作原理，我首先使用一个查询展示五个字符串的排序顺序，然后展示如何使用strcmp()函数比较这些字符串。以下是我插入到string_tbl表中的五个字符串：

mysql> DELETE FROM string_tbl;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO string_tbl(vchar_fld) VALUES ('abcd');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO string_tbl(vchar_fld) VALUES ('xyz');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO string_tbl(vchar_fld) VALUES ('QRSTUV');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO string_tbl(vchar_fld) VALUES ('qrstuv');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO string_tbl(vchar_fld) VALUES ('12345');
Query OK, 1 row affected (0.00 sec)

以下是这五个字符串的排序顺序：

mysql> SELECT vchar_fld
-> FROM string_tbl
-> ORDER BY vchar_fld;
+-----------+
| vchar_fld |
+-----------+
| 12345     |
| abcd      |
| QRSTUV    |
| qrstuv    |
| xyz       |
+-----------+
5 rows in set (0.00 sec)

接下来的查询对这五个不同的字符串进行了六次比较：

mysql> SELECT STRCMP('12345','12345') 12345_12345,
-> STRCMP('abcd','xyz') abcd_xyz,
-> STRCMP('abcd','QRSTUV') abcd_QRSTUV,
-> STRCMP('qrstuv','QRSTUV') qrstuv_QRSTUV,
-> STRCMP('12345','xyz') 12345_xyz,
-> STRCMP('xyz','qrstuv') xyz_qrstuv;
+--------------+-----------+-----------------+-------------------+-----------+----------------+
| 12345_12345  | abcd_xyz  | abcd_QRSTUV     | qrstuv_QRSTUV     | 12345_xyz | xyz_qrstuv     |
+--------------+-----------+-----------------+-------------------+-----------+----------------+
|            0 |        -1 |              -1 |                  0|         -1|               1|
+--------------+-----------+-----------------+-------------------+-----------+----------------+
1 row in set (0.00 sec)

第一次比较的结果为0，这在意料之中，因为我是将一个字符串与它自身进行比较。第四次比较的结果也为0，这有点令人惊讶，因为这两个字符串由相同的字母组成，只是一个全为大写，另一个全为小写。出现这个结果的原因是MySQL的strcmp()函数不区分大小写，在使用该函数时要记住这一点。其他四次比较根据第一个字符串在排序顺序中是位于第二个字符串之前还是之后，分别返回 -1或1。例如，strcmp('abcd','xyz')返回 -1，因为字符串'abcd'在字符串'xyz'之前。

除了strcmp()函数，MySQL还允许你在select子句中使用like和regexp运算符来比较字符串。这样的比较将返回1（表示真）或0（表示假）。因此，这些运算符允许你构建返回数字的表达式，与本节中描述的函数类似。以下是一个使用like的示例：

mysql> SELECT name, name LIKE '%ns' ends_in_ns
-> FROM department;
+-------------------+-------------+
| name              | ends_in_ns  |
+-------------------+-------------+
| <相关内容> | <相关结果> |
+-------------------+-------------+
3 rows in set (0.25 sec)

这个示例检索所有部门名称，以及一个表达式，如果部门名称以“ns”结尾，则该表达式返回1，否则返回0。如果你想执行更复杂的模式匹配，可以使用regexp运算符，如下所示：

mysql> SELECT cust_id, cust_type_cd, fed_id,
-> fed_id REGEXP '.{3}-.{2}-.{4}' is_ss_no_format
-> FROM customer;
+---------+---------------+--------------+--------------------+
| cust_id | cust_type_cd   | fed_id       | is_ss_no_format   |
+---------+---------------+--------------+--------------------+
|       1 | I             | 111-11-1111   |                 1 |
|       2 | I             | 222-22-2222   |                 1 |
|       3 | I             | 333-33-3333   |                 1 |
|       4 | I             | 444-44-4444   |                 1 |
|       5 | I             | 555-55-5555   |                 1 |
|       6 | I             | 666-66-6666   |                 1 |
|       7 | I             | 777-77-7777   |                 1 |
|       8 | I             | 888-88-8888   |                 1 |
|       9 | I             | 999-99-9999   |                 1 |
|      10 | B             | 04-1111111    |                 0 |
|      11 | B             | 04-2222222    |                 0 |
|      12 | B             | 04-3333333    |                 0 |
|      13 | B             | 04-4444444    |                 0 |
+---------+---------------+--------------+--------------------+
13 rows in set (0.00 sec)

此查询的第四列，如果fed_id列中存储的值符合社会保险号的格式，则返回1。

SQL Server和Oracle数据库的用户可以通过构建case表达式来实现类似的结果，我将在第11章详细介绍case表达式。

返回字符串的字符串函数

在某些情况下，你需要修改现有字符串，要么提取字符串的一部分，要么在字符串中添加额外的文本。每个数据库服务器都包含多个函数来帮助完成这些任务。在开始之前，我再次重置string_tbl表中的数据：

mysql> DELETE FROM string_tbl;
Query OK, 5 rows affected (0.00 sec)

mysql> INSERT INTO string_tbl (text_fld)
-> VALUES ('This string was 29 characters');
Query OK, 1 row affected (0.01 sec)

在本章前面，我演示了如何使用concat()函数来构建包含重音字符的单词。concat()函数在许多其他情况下也很有用，包括当你需要在存储的字符串末尾追加额外字符时。例如，以下示例通过在text_fld列存储的字符串末尾添加一个短语来修改该字符串：

mysql> UPDATE string_tbl
-> SET text_fld = CONCAT(text_fld, ', but now it is longer');
Query OK, 1 row affected (0.03 sec)
Rows matched: 1   Changed: 1   Warnings: 0

text_fld列的内容现在如下：

mysql> SELECT text_fld
-> FROM string_tbl;
+-----------------------------------------------------+
| text_fld                                            |
+-----------------------------------------------------+
| This string was 29 characters, but now it is longer |
+-----------------------------------------------------+
1 row in set (0.00 sec)

因此，与所有返回字符串的函数一样，你可以使用concat()函数来替换存储在字符列中的数据。

concat()函数的另一个常见用途是从单个数据片段构建字符串。例如，以下查询为每个银行出纳员生成一个叙述性字符串：

mysql> SELECT CONCAT(fname, ' ', lname, ' has been a ',
    ->                  title, ' since ', start_date) emp_narrative
    -> FROM employee
    -> WHERE title = 'Teller' OR title = 'Head Teller';
+---------------------------------------------------------+
| emp_narrative                                           |
+---------------------------------------------------------+
| Helen Fleming has been a Head Teller since 2008-03-17   |
| Chris Tucker has been a Teller since 2008-09-15         |
| Sarah Parker has been a Teller since 2006-12-02         |
| Jane Grossman has been a Teller since 2006-05-03        |
| Paula Roberts has been a Head Teller since 2006-07-27   |
| Thomas Ziegler has been a Teller since 2004-10-23       |
| Samantha Jameson has been a Teller since 2007-01-08     |
| John Blake has been a Head Teller since 2004-05-11      |
| Cindy Mason has been a Teller since 2006-08-09          |
| Frank Portman has been a Teller since 2007-04-01        |
| Theresa Markham has been a Head Teller since 2005-03-15 |
| Beth Fowler has been a Teller since 2006-06-29          |
| Rick Tulman has been a Teller since 2006-12-12          |
+---------------------------------------------------------+
13 rows in set (0.30 sec)

concat()函数可以处理任何返回字符串的表达式，甚至会将数字和日期转换为字符串格式，用作参数的日期列（start_date）就是证明。虽然Oracle数据库包含concat()函数，但它只接受两个字符串参数，所以前面的查询在Oracle上无法运行。相反，你需要使用连接运算符（||），而不是函数调用，如下所示：

SELECT fname || ' ' || lname || ' has been a ' || title || ' since ' || start_date emp_narrative
FROM employee
WHERE title = 'Teller' OR title = 'Head Teller';

SQL Server不包含concat()函数，所以你需要使用与前面查询相同的方法，只是要使用SQL Server的连接运算符（+），而不是||。

虽然concat()函数对于在字符串的开头或结尾添加字符很有用，但你可能还需要在字符串中间添加或替换字符。这三个数据库服务器都为此提供了函数，但它们各不相同。我先演示MySQL的函数，然后展示其他两个服务器的函数。

MySQL包含insert()函数，它接受四个参数：原始字符串、开始位置、要替换的字符数以及替换字符串。根据第三个参数的值，该函数可用于在字符串中插入或替换字符。当第三个参数的值为0时，替换字符串将被插入，后面的字符会向右移动，如下所示：

mysql> SELECT INSERT('goodbye world', 9, 0, 'cruel ') string;
+---------------------+
| string              |
+---------------------+
| goodbye cruel world |
+---------------------+
1 row in set (0.00 sec)

在这个例子中，从位置9开始的所有字符都向右移动，字符串'cruel'被插入。如果第三个参数大于0，那么该数量的字符将被替换字符串替换，如下所示：

mysql> SELECT INSERT('goodbye world', 1, 7, 'hello') string;
+-------------+
| string      |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)

在这个例子中，前七个字符被字符串'hello'替换。Oracle数据库没有提供像MySQL的insert()函数那样灵活的单个函数，但Oracle提供了replace()函数，用于将一个子字符串替换为另一个子字符串。以下是使用replace()函数重新编写的前面的示例：

SELECT REPLACE('goodbye world', 'goodbye', 'hello')
FROM dual;

字符串'goodbye'的所有实例都将被字符串'hello'替换，结果为字符串'hello world'。replace()函数会将搜索字符串的每个实例都替换为替换字符串，所以你需要注意，不要出现比预期更多的替换。

SQL Server也包含一个replace()函数，其功能与Oracle的相同，但SQL Server还包含一个名为stuff()的函数，其功能与MySQL的insert()函数类似。以下是一个示例：

SELECT STUFF('hello world', 1, 5, 'goodbye cruel')

执行此查询时，从位置1开始删除五个字符，然后将字符串'goodbye cruel'插入到起始位置，结果为字符串'goodbye cruel world'。

除了在字符串中插入字符，你可能还需要从字符串中提取子字符串。为此，这三个服务器都包含substring()函数（不过Oracle数据库的版本叫substr()），它从指定位置开始提取指定数量的字符。以下示例从字符串的第九个位置开始提取五个字符：

mysql> SELECT SUBSTRING('goodbye cruel world', 9, 5);
+------------------------------------------------+
| SUBSTRING('goodbye cruel world', 9, 5)         |
+------------------------------------------------+
| cruel                                          |
+------------------------------------------------+
1 row in set (0.00 sec)

除了这里展示的函数，这三个服务器还包含许多其他用于操作字符串数据的内置函数。其中许多函数是为特定目的设计的，例如生成八进制或十六进制数字的字符串等效形式，也有许多通用函数，比如用于删除或添加尾随空格的函数。如需更多信息，请查阅服务器的SQL参考指南，或者像《SQL 快速入门》（O’Reilly出版）这样的通用SQL参考指南。

处理数值数据

与字符串数据（以及稍后你将看到的时间数据）不同，数值数据的生成非常简单。你可以输入一个数字，从另一列中检索它，或者通过计算生成它。所有常见的算术运算符（+、-、*、/）都可用于执行计算，并且可以使用括号来指定运算优先级，例如：

mysql> SELECT (37 * 59) / (78 - (8 * 6));
+------------------------------------------------+
| (37 * 59) / (78 - (8 * 6))                     |
+------------------------------------------------+
| 72.77                                          |
+------------------------------------------------+
1 row in set (0.00 sec)

正如我在第2章中提到的，存储数值数据时主要需要关注的是，如果数字大于数值列指定的大小，可能会被四舍五入。例如，如果将数字9.96存储在定义为float(3,1)的列中，它将被四舍五入为10.0。

执行算术函数

大多数内置的数值函数用于特定的算术目的，例如计算一个数的平方根。表7-1列出了一些常见的接受单个数值参数并返回一个数值的函数。

函数名	描述
Acos(x)	计算x的反余弦值
Asin(x)	计算x的反正弦值
Atan(x)	计算x的反正切值
Cos(x)	计算x的余弦值
Cot(x)	计算x的余切值
Exp(x)	计算$e^x$
Ln(x)	计算x的自然对数
Sin(x)	计算x的正弦值
Sqrt(x)	计算x的平方根
Tan(x)	计算x的正切值

这些函数执行非常特定的任务，我就不再展示这些函数的示例了（如果你不通过名称或描述识别某个函数，那么你可能并不需要它）。然而，其他用于计算的数值函数则更加灵活，值得进行一些解释。

例如，取模运算符用于计算一个数除以另一个数的余数，在MySQL和Oracle数据库中通过mod()函数实现。以下示例计算10除以4的余数：

mysql> SELECT MOD(10,4);
+----------------+
| MOD(10,4)      |
+----------------+
| 2              |
+----------------+
1 row in set (0.02 sec)

虽然mod()函数通常用于整数参数，但在MySQL中你也可以使用实数，例如：

mysql> SELECT MOD(22.75, 5);
+---------------------+
| MOD(22.75, 5)       |
+---------------------+
| 2.75                |
+---------------------+
1 row in set (0.02 sec)

SQL Server没有mod()函数。相反，使用运算符%来求余数。因此，表达式10 % 4将产生值2。

另一个接受两个数值参数的数值函数是pow()函数（如果你使用的是Oracle数据库或SQL Server，则为power()函数），它返回一个数的另一个数次幂，例如：

mysql> SELECT POW(2,8);
+----------------+
| POW(2,8)       |
+----------------+
| 256            |
+----------------+
1 row in set (0.03 sec)

因此，pow(2,8)在MySQL中相当于指定 2^8。由于计算机内存是以 2^x 字节为单位分配的，pow()函数可以方便地确定一定数量内存中的字节数：

mysql> SELECT POW(2, 10) kilobyte,
    ->        POW(2, 20) megabyte,
    ->        POW(2, 30) gigabyte,
    ->        POW(2, 40) terabyte;
+----------+----------+------------+---------------+
| kilobyte | megabyte | gigabyte   | terabyte      |
+----------+----------+------------+---------------+
| 1024     | 1048576  | 1073741824 | 1099511627776 |
+----------+----------+------------+---------------+
1 row in set (0.00 sec) 

我不知道你怎么想，但我发现记住 1GB 是 2^30 字节比记住1,073,741,824这个数字更容易。

控制数字精度

在处理浮点数时，你可能并不总是希望以其完整精度来处理或显示一个数字。例如，你可能会将货币交易数据存储到小数点后六位，但为了显示目的，你可能希望四舍五入到最接近的百分位。在限制浮点数精度时，有四个函数很有用：ceil()、floor()、round()和truncate()。所有这三个数据库服务器都包含这些函数，不过Oracle数据库包含的是trunc()而不是truncate()，SQL Server包含的是ceiling()而不是ceil()。

ceil()和floor()函数用于向上或向下舍入到最接近的整数，如下所示：

mysql> SELECT CEIL(72.445), FLOOR(72.445);
+--------------+---------------+
| CEIL(72.445) | FLOOR(72.445) |
+--------------+---------------+
| 73           | 72            |
+--------------+---------------+
1 row in set (0.06 sec)

因此，72到73之间的任何数字，ceil()函数会将其求值为73，floor()函数会将其求值为72。请记住，即使一个数字的小数部分非常小，ceil()函数也会向上舍入；即使小数部分相当大，floor()函数也会向下舍入，例如：

mysql> SELECT CEIL(72.000000001),
    ->        FLOOR(72.999999999);
+--------------------+---------------------+
| CEIL(72.000000001) | FLOOR(72.999999999) |
+--------------------+---------------------+
| 73                 | 72                  |
+--------------------+---------------------+
1 row in set (0.00 sec) 

如果这对你的应用程序来说有点过于严格，你可以使用round()函数在两个整数的中间点向上或向下舍入，例如：

mysql> SELECT ROUND(72.49999),
    ->        ROUND(72.5),
    ->        ROUND(72.50001);
+-----------------+-------------+-----------------+
| ROUND(72.49999) | ROUND(72.5) | ROUND(72.50001) |
+-----------------+-------------+-----------------+
| 72              | 73          | 73              |
+-----------------+-------------+-----------------+
1 row in set (0.00 sec)

使用round()函数时，任何小数部分在两个整数中间或超过中间的数字将向上舍入，而小数部分小于两个整数中间的数字将向下舍入。

大多数时候，你会希望保留数字的至少一部分小数部分，而不是四舍五入到最接近的整数；round()函数允许使用一个可选的第二个参数来指定要四舍五入到小数点后多少位。下一个示例展示了如何使用第二个参数将数字72.0909四舍五入到一位、两位和三位小数：

mysql> SELECT ROUND(72.0909, 1),
    ->        ROUND(72.0909, 2),
    ->        ROUND(72.0909, 3);
+-------------------+-------------------+-------------------+
| ROUND(72.0909, 1) | ROUND(72.0909, 2) | ROUND(72.0909, 3) |
+-------------------+-------------------+-------------------+
| 72.1              | 72.09             | 72.091            |
+-------------------+-------------------+-------------------+
1 row in set (0.00 sec)

与round()函数类似，truncate()函数也允许使用一个可选的第二个参数来指定小数点后的位数，但truncate()函数只是丢弃不需要的数字而不进行四舍五入。下一个示例展示了数字72.0909被截断到一位、两位和三位小数的情况：

mysql> SELECT TRUNCATE(72.0909, 1),
    ->        TRUNCATE(72.0909, 2),
    ->        TRUNCATE(72.0909, 3);
+----------------------+----------------------+----------------------+
| TRUNCATE(72.0909, 1) | TRUNCATE(72.0909, 2) | TRUNCATE(72.0909, 3) |
+----------------------+----------------------+----------------------+
| 72.0                 | 72.09                | 72.090               |
+----------------------+----------------------+----------------------+
1 row in set (0.00 sec)

SQL Server不包含truncate()函数。相反，round()函数允许使用一个可选的第三个参数，如果该参数存在且不为零，则要求对数字进行截断而不是四舍五入。

truncate()和round()函数也允许第二个参数为负值，这意味着小数点左边的数字将被截断或四舍五入。一开始这可能看起来很奇怪，但它有实际的应用场景。例如，你销售的产品可能只能以10个为单位购买。如果客户订购17个单位，你可以从以下方法中选择一种来修改客户的订单数量：

mysql> SELECT ROUND(17, -1),
    ->        TRUNCATE(17, -1);
+---------------+------------------+
| ROUND(17, -1) | TRUNCATE(17, -1) |
+---------------+------------------+
| 20            | 10               |
+---------------+------------------+
1 row in set (0.00 sec) 

如果所涉及的产品是图钉，那么当客户只要求17个时，卖给客户10个还是20个可能对你的利润影响不大；然而，如果你销售的是劳力士手表，通过四舍五入可能对你的业务更有利。

处理有符号数据

如果你处理的是允许负值的数值列（在第2章中，我展示了如何将数值列标记为unsigned，这意味着只允许正数），有几个数值函数可能会有用。例如，假设你被要求生成一份报告，显示每个银行账户的当前状态。以下查询返回三个对生成报告有用的列：

mysql> SELECT account_id,
    ->        SIGN(avail_balance),
    ->        ABS(avail_balance)
    -> FROM account;
+------------+---------------------+--------------------+
| account_id | SIGN(avail_balance) | ABS(avail_balance) |
+------------+---------------------+--------------------+
| 1          | 1                   | 1057.75            |
| 2          | 1                   | 500.00             |
| 3          | 1                   | 3000.00            |
| 4          | 1                   | 2258.02            |
| 5          | 1                   | 200.00             |
| ...        | ...                 | ...                |
| 19         | 1                   | 1500.00            |
| 20         | 1                   | 23575.12           |
| 21         | 0                   | 0.00               |
| 22         | 1                   | 9345.55            |
| 23         | 1                   | 38552.05           |
| 24         | 1                   | 50000.00           |
+------------+---------------------+--------------------+
24 rows in set (0.00 sec)

第二列使用sign()函数，如果账户余额为负，则返回-1；如果账户余额为零，则返回0；如果账户余额为正，则返回1。第三列通过abs()函数返回账户余额的绝对值。

处理时间数据

在本章讨论的三种数据类型（字符型、数值型和时间型）中，时间数据在数据生成和处理方面最为复杂。时间数据的一些复杂性是由于描述单个日期和时间的方式多种多样造成的。例如，我写这一段内容的日期可以用以下所有方式描述：

• 2008年9月17日，星期三
• 2008年9月17日，下午2:14:56，美国东部标准时间（EST）
• 2008年9月17日，19:14:56，格林威治标准时间（GMT）
• 2612008（儒略历格式）
• 星际日期[-4] 85712.03 14:14:56（《星际迷航》格式）

虽然其中一些差异纯粹是格式问题，但大多数复杂性与你的参考框架有关，我们将在下一节探讨这一点。

处理时区问题

由于世界各地的人们都希望中午大致与太阳在当地的最高点重合，所以从未有人试图强制所有人使用统一的时钟。相反，世界被划分为24个虚拟区域，称为时区；在特定的时区内，每个人对当前时间的认知是一致的，而不同时区的人则不一致。虽然这看起来很简单，但一些地理区域每年会两次将时间调整一小时（实行所谓的夏令时），而有些地区则不调整，因此地球上两个地点之间的时间差可能在一年中的一半时间是4小时，另一半时间是5小时。即使在同一个时区内，不同地区可能遵守也可能不遵守夏令时，这导致同一时区内的不同时钟在一年中的一半时间是一致的，但在其余时间相差一小时。

虽然计算机时代加剧了这个问题，但人们从早期的航海探索时代就开始处理时区差异了。为了确保计时有一个共同的参考点，15世纪的航海家将他们的时钟设置为英国格林威治的当地时间。这就是后来被称为格林威治标准时间（GMT）的时间。所有其他时区都可以通过与格林威治标准时间的小时差来描述；例如，美国东部的时区，即美国东部标准时间（EST），可以描述为GMT -5:00，也就是比格林威治标准时间早5个小时。

如今，我们使用格林威治标准时间的一种变体，称为协调世界时（UTC），它基于原子钟（或者更准确地说，是全球50个地点的200个原子钟的平均时间，这被称为世界时）。SQL Server和MySQL都提供了返回当前协调世界时时戳的函数（SQL Server中是getutcdate()，MySQL中是utc_timestamp()）。

大多数数据库服务器默认使用其所在服务器的时区设置，并提供在需要时修改时区的工具。例如，用于存储世界各地证券交易所交易数据的数据库通常会配置为使用协调世界时，而用于存储特定零售机构交易数据的数据库可能会使用服务器的时区。

MySQL有两种不同的时区设置：全局时区和会话时区，每个登录到数据库的用户的会话时区可能不同。你可以通过以下查询查看这两种设置：

mysql> SELECT @@global.time_zone,
    ->        @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

system这个值表示服务器正在使用数据库所在服务器的时区设置。

如果你坐在瑞士苏黎世的一台计算机前，通过网络打开到位于纽约的MySQL服务器的会话，你可能想要更改你的会话的时区设置，你可以通过以下命令来实现：

mysql> SET time_zone = 'Europe/Zurich';
Query OK, 0 rows affected (0.18 sec)

如果你再次检查时区设置，你会看到以下内容：

mysql> SELECT @@global.time_zone,
    ->        @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | Europe/Zurich       |
+--------------------+---------------------+
1 row in set (0.00 sec)

现在，你会话中显示的所有日期都将符合苏黎世时间。

Oracle数据库用户可以通过以下命令更改会话的时区设置：

ALTER SESSION TIMEZONE = 'Europe/Zurich'

生成时间数据

你可以通过以下任何一种方式生成时间数据：

• 从现有的日期（date）、日期时间（datetime）或时间（time）列复制数据。
• 执行返回日期、日期时间或时间的内置函数。
• 构建时间数据的字符串表示形式，以供服务器求值。

要使用最后一种方法，你需要了解用于格式化日期的各种组件。

时间数据的字符串表示形式

第2章的表2-5展示了较常用的日期组件；为了帮你回顾，表7-2列出了相同的组件。

在Windows平台上运行MySQL服务器时加载MySQL时区数据

1. 从http://dev.mysql.com/downloads/timezones.html下载时区数据。
2. 关闭MySQL服务器。
3. 从下载的ZIP文件中解压文件（在我的例子中，文件名为timezone-2006p.zip），并将它们放置在MySQL安装目录下的/data/mysql文件夹中（我的安装路径为/Program Files/MySQL/MySQL Server 6.0/data/mysql） 。
4. 重启MySQL服务器。

要查看时区数据，可通过use mysql命令切换到mysql数据库，并执行以下查询：

mysql> SELECT name
    -> FROM time_zone_name;
+----------------------------------+
| name                             |
+----------------------------------+
| Africa/Abidjan                   |
| Africa/Accra                     |
| Africa/Addis_Ababa               |
| Africa/Algiers                   |
| Africa/Asmera                    |
| Africa/Bamako                    |
| Africa/Bangui                    |
| Africa/Banjul                    |
| Africa/Bissau                    |
| Africa/Blantyre                  |
| Africa/Brazzaville               |
| Africa/Bujumbura                 |
...
| US/Alaska                        |
| US/Aleutian                      |
| US/Arizona                       |
| US/Central                       |
| US/East-Indiana                  |
| US/Eastern                       |
| US/Hawaii                        |
| US/Indiana-Starke                |
| US/Michigan                      |
| US/Mountain                      |
| US/Pacific                       |
| US/Pacific-New                   |
| US/Samoa                         |
| UTC                              |
| W-SU                             |
| WET                              |
| Zulu                             |
+----------------------------------+
546 rows in set (0.01 sec)

要更改时区设置，请从前面查询结果中选择最符合你所在位置的名称。

表7-2. 日期格式组件

组件	定义	范围
YYYY	年份，包含世纪	1000到9999
MM	月份	01（一月）到12（十二月）
DD	日期	01到31
HH	小时	00到23
HHH	已流逝的小时数	−838到838
MI	分钟	00到59
SS	秒	00到59

要构建一个服务器可以解释为日期、日期时间或时间的字符串，你需要按照表7-3所示的顺序组合各个组件。

表7-3. 必需的日期组件

类型	默认格式
日期	YYYY-MM-DD
日期时间、时间戳	YYYY-MM-DD HH:MI:SS
时间	HHH:MI:SS

因此，要在datetime列中填充2008年9月17日下午3:30，你需要构建以下字符串：

'2008-09-17 15:30:00'

如果服务器期望一个日期时间值，例如在更新日期时间列或调用需要日期时间参数的内置函数时，你可以提供一个格式正确且包含必需日期组件的字符串，服务器会为你进行转换。例如，以下语句用于修改银行交易的日期：

UPDATE transaction
SET txn_date = '2008-09-17 15:30:00'
WHERE txn_id = 99999;

服务器确定set子句中提供的字符串必须是日期时间值，因为该字符串用于填充日期时间列。因此，服务器会尝试通过将字符串解析为默认日期时间格式中包含的六个组件（年、月、日、时、分、秒）来为你转换该字符串。

字符串到日期的转换

如果服务器不期望一个日期时间值，或者你希望使用非默认格式表示日期时间，则需要告诉服务器将字符串转换为日期时间。例如，以下是一个简单的查询，使用cast()函数返回一个日期时间值：

mysql> SELECT CAST('2008-09-17 15:30:00' AS DATETIME);
+--------------------------------------------+
| CAST('2008-09-17 15:30:00' AS DATETIME)    |
+--------------------------------------------+
| 2008-09-17 15:30:00                        |
+--------------------------------------------+
1 row in set (0.00 sec)

我们将在本章末尾介绍cast()函数。虽然此示例演示了如何构建日期时间值，但相同的逻辑也适用于日期和时间类型。以下查询使用cast()函数生成一个日期值和一个时间值：

mysql> SELECT CAST('2008-09-17' AS DATE) date_field,
       CAST('108:17:57' AS TIME) time_field;
+------------+------------+
| date_field | time_field |
+------------+------------+
| 2008-09-17 | 108:17:57  |
+------------+------------+
1 row in set (0.00 sec)

当然，即使服务器期望日期、日期时间或时间值，你也可以显式转换字符串，而不是让服务器进行隐式转换。当字符串转换为时间值时，无论是显式还是隐式转换，你都必须按所需顺序提供所有日期组件。虽然有些服务器对日期格式要求非常严格，但MySQL服务器对组件之间使用的分隔符则相当宽松。例如，MySQL会将以下所有字符串视为2008年9月17日下午3:30的有效表示形式：

'2008-09-17 15:30:00'
'2008/09/17 15:30:00'
'2008,09,17,15,30,00'
'20080917153000'

虽然这为你提供了更多灵活性，但你可能会遇到需要生成不包含默认日期组件的时间值的情况；下一节将介绍一个比cast()函数灵活得多的内置函数。

生成日期的函数

如果你需要从字符串生成时间数据，而该字符串的格式又不适合使用cast()函数，那么你可以使用一个允许你同时提供格式字符串和日期字符串的内置函数。MySQL为此提供了str_to_date()函数。例如，假设你从一个文件中提取了字符串September 17, 2008，并需要用它来更新一个日期列。由于该字符串不是所需的YYYY-MM-DD格式，你可以使用str_to_date()函数，而不是重新格式化字符串以便使用cast()函数，如下所示：

UPDATE individual
SET birth_date = STR_TO_DATE('September 17, 2008', '%M %d, %Y')
WHERE cust_id = 9999;

调用str_to_date()函数时的第二个参数定义了日期字符串的格式，在这个例子中，包含月份名称（%M）、数字日期（%d）和四位数字年份（%Y）。虽然有30多个已识别的格式组件，但表7-4定义了大约12个最常用的组件。

表7-4. 日期格式组件

格式组件	描述
%M	月份名称（一月到十二月）
%m	月份数字（01到12）
%d	日期数字（01到31）
%j	一年中的第几天（001到366）
%W	星期几的名称（星期日到星期六）
%Y	四位数字的年份
%y	两位数字的年份
%H	小时（00到23）
%h	小时（01到12）
%i	分钟（00到59）
%s	秒（00到59）
%f	微秒（000000到999999）
%p	上午或下午

str_to_date()函数根据格式字符串的内容返回日期时间、日期或时间值。例如，如果格式字符串仅包含%H、%i和%s，则会返回一个时间值。

Oracle数据库用户可以像MySQL的str_to_date()函数一样使用to_date()函数。SQL Server包含一个convert()函数，它不如MySQL和Oracle数据库的函数灵活；你不能提供自定义格式字符串，日期字符串必须符合21种预定义格式之一。

如果你想生成当前日期/时间，则无需构建字符串，因为以下内置函数将访问系统时钟并为你返回当前日期和/或时间的字符串形式：

mysql> SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP();
+----------------+----------------+---------------------+
| CURRENT_DATE() | CURRENT_TIME() | CURRENT_TIMESTAMP() |
+----------------+----------------+---------------------+
| 2008-09-18     | 19:53:12       | 2008-09-18 19:53:12 |
+----------------+----------------+---------------------+
1 row in set (0.12 sec)

这些函数返回的值采用返回的时间类型的默认格式。Oracle数据库包含current_date()和current_timestamp()函数，但不包含current_time()函数，而SQL Server仅包含current_timestamp()函数。

处理时间数据

本节将探讨接受日期参数并返回日期、字符串或数字的内置函数。

返回日期的时间函数

许多内置的时间函数接受一个日期作为参数并返回另一个日期。例如，MySQL的date_add()函数允许你向指定日期添加任何类型的时间间隔（例如，天、月、年）以生成另一个日期。以下示例展示了如何在当前日期的基础上添加五天：

mysql> SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY);
+------------------------------------------+
| DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY) |
+------------------------------------------+
| 2008-09-22                               |
+------------------------------------------+
1 row in set (0.06 sec)

第二个参数由三个元素组成：interval关键字、所需的数量和时间间隔类型。表7-5展示了一些常用的时间间隔类型。

表7-5. 常用时间间隔类型

描述	时间间隔名称
秒	秒数
分钟	分钟数
小时	小时数
天	天数
月	月数
年	年数
分钟_秒	分钟和秒数，用“:”分隔
小时_秒	小时、分钟和秒数，用“:”分隔
年_月	年数和月数，用“-”分隔

虽然表7-5中列出的前六种类型相当直观，但最后三种类型需要多做一些解释，因为它们包含多个元素。例如，如果被告知事务ID 9999的实际发生时间比记录到事务表中的时间晚了3小时27分11秒，可以通过以下方式进行修正：

UPDATE transaction
SET txn_date = DATE_ADD(txn_date, INTERVAL '3:27:11' HOUR_SECOND)
WHERE txn_id = 9999;

在这个例子中，date_add()函数获取txn_date列中的值，为其加上3小时27分11秒，并用计算结果修改txn_date列的值。

或者，如果你在人力资源部门工作，发现员工ID 4789声称的年龄比实际年龄小，你可以为他的出生日期加上9年11个月，如下所示：

UPDATE employee
SET birth_date = DATE_ADD(birth_date, INTERVAL '9-11' YEAR_MONTH)
WHERE emp_id = 4789;

SQL Server的用户可以使用dateadd()函数来实现上述示例：

UPDATE employee
SET birth_date = DATEADD(MONTH, 119, birth_date)
WHERE emp_id = 4789

SQL Server没有组合时间间隔（即year_month），所以我将9年11个月换算成了119个月。

Oracle数据库的用户可以使用add_months()函数来实现这个示例，如下：

UPDATE employee
SET birth_date = ADD_MONTHS(birth_date, 119)
WHERE emp_id = 4789;

在某些情况下，你想给一个日期加上一个时间间隔，你知道想要得到的结果日期，但不知道需要增加多少天。例如，假设一位银行客户登录网上银行系统，并计划在月底进行转账。你无需编写代码来确定当前月份并查找该月的天数，而是可以调用last_day()函数，它会为你完成这项工作（MySQL和Oracle数据库都包含last_day()函数；SQL Server没有类似的函数 ）。如果客户要求在2008年9月17日进行转账，你可以通过以下方式找到9月的最后一天：

mysql> SELECT LAST_DAY('2008-09-17');
+--------------------------+
| LAST_DAY('2008-09-17')   |
+--------------------------+
| 2008-09-30               |
+--------------------------+
1 row in set (0.10 sec)

无论你提供的是日期值还是日期时间值，last_day()函数始终返回一个日期。虽然这个函数看起来可能并没有节省很多时间，但如果你试图查找2月的最后一天，并且需要判断当前年份是否为闰年，其底层逻辑可能会很复杂。

另一个返回日期的时间函数是将日期时间值从一个时区转换到另一个时区的函数。为此，MySQL包含convert_tz()函数，Oracle数据库包含new_time()函数。例如，如果我想将当前本地时间转换为协调世界时（UTC），可以执行以下操作：

mysql> SELECT CURRENT_TIMESTAMP() current_est,
       CONVERT_TZ(CURRENT_TIMESTAMP(), 'US/Eastern', 'UTC') current_utc;
+---------------------+---------------------+
| current_est         | current_utc         |
+---------------------+---------------------+
| 2008-09-18 20:01:25 | 2008-09-19 00:01:25 |
+---------------------+---------------------+
1 row in set (0.76 sec)

当接收到的日期与数据库中存储的日期处于不同时区时，这个函数就会很有用。

返回字符串的时间函数

大多数返回字符串值的时间函数用于提取日期或时间的一部分。例如，MySQL包含dayname()函数，用于确定某一日期是星期几，如下所示：

mysql> SELECT DAYNAME('2008-09-18');
+-----------------------+
| DAYNAME('2008-09-18') |
+-----------------------+
| Thursday              |
+-----------------------+
1 row in set (0.08 sec)

MySQL包含许多用于从日期值中提取信息的函数，但我建议你使用extract()函数，因为记住一个函数的几种变体比记住十几个不同的函数更容易。此外，extract()函数是SQL:2003标准的一部分，Oracle数据库和MySQL都已实现该函数。

extract()函数使用与date_add()函数相同的时间间隔类型（见表7-5）来定义你感兴趣的日期元素。例如，如果你只想提取日期时间值中的年份部分，可以执行以下操作：

mysql> SELECT EXTRACT(YEAR FROM '2008-09-18 22:19:05');
+------------------------------------------+
| EXTRACT(YEAR FROM '2008-09-18 22:19:05') |
+------------------------------------------+
| 2008                                     |
+------------------------------------------+
1 row in set (0.00 sec)

SQL Server没有实现extract()函数，但它包含datepart()函数。下面展示如何使用datepart()函数从日期时间值中提取年份：

SELECT DATEPART(YEAR, GETDATE())

返回数字的时间函数

在本章前面，我向你展示了一个函数，用于给一个日期值加上给定的时间间隔，从而生成另一个日期值。在处理日期时，另一个常见的操作是获取两个日期值，并确定这两个日期之间的时间间隔数（天数、周数、年数）。为此，MySQL包含datediff()函数，它返回两个日期之间的完整天数。例如，如果我想知道我的孩子今年夏天放假多少天，可以执行以下操作：

mysql> SELECT DATEDIFF('2009-09-03', '2009-06-24');
+--------------------------------------+
| DATEDIFF('2009-09-03', '2009-06-24') |
+--------------------------------------+
| 71                                   |
+--------------------------------------+
1 row in set (0.05 sec)

因此，在孩子们安全返回学校之前，我将不得不忍受71天的毒葛、蚊虫叮咬和擦伤膝盖的困扰。datediff()函数在计算时会忽略参数中的时间部分。即使我在日期中包含时间，将第一个日期的时间设置为午夜前一秒，将第二个日期的时间设置为午夜后一秒，这些时间对计算也没有影响：

mysql> SELECT DATEDIFF('2009-09-03 23:59:59', '2009-06-24 00:00:01');
+--------------------------------------------------------+
| DATEDIFF('2009-09-03 23:59:59', '2009-06-24 00:00:01') |
+--------------------------------------------------------+
| 71                                                     |
+--------------------------------------------------------+
1 row in set (0.00 sec)

如果我交换参数顺序，将较早的日期放在前面，datediff()函数将返回一个负数，如下所示：

mysql> SELECT DATEDIFF('2009-06-24', '2009-09-03');
+--------------------------------------+
| DATEDIFF('2009-06-24', '2009-09-03') |
+--------------------------------------+
| -71                                  |
+--------------------------------------+
1 row in set (0.01 sec)

SQL Server也包含datediff()函数，但它比MySQL的实现更灵活，因为你可以指定时间间隔类型（即年、月、日、小时），而不仅仅是计算两个日期之间的天数。下面展示SQL Server如何实现上述示例：

SELECT DATEDIFF(DAY, '2009-06-24', '2009-09-03')

Oracle数据库允许你通过直接用一个日期减去另一个日期来确定两个日期之间的天数。

转换函数

在本章前面，我向你展示了如何使用cast()函数将字符串转换为日期时间值。虽然每个数据库服务器都包含许多用于将数据从一种类型转换为另一种类型的专有函数，但我建议使用cast()函数，它是SQL:2003标准的一部分，并且已在MySQL、Oracle数据库和Microsoft SQL Server中实现。

要使用cast()函数，你需要提供一个值或表达式、as关键字以及你想要将该值转换为的类型。下面是一个将字符串转换为整数的示例：

mysql> SELECT CAST('1456328' AS SIGNED INTEGER);
+-----------------------------------+
| CAST('1456328' AS SIGNED INTEGER) |
+-----------------------------------+
| 1456328                           |
+-----------------------------------+
1 row in set (0.01 sec)

在将字符串转换为数字时，cast()函数会尝试从左到右转换整个字符串；如果在字符串中发现任何非数字字符，转换将停止，但不会报错。考虑以下示例：

mysql> SELECT CAST('999ABC111' AS UNSIGNED INTEGER);
+---------------------------------------+
| CAST('999ABC111' AS UNSIGNED INTEGER) |
+---------------------------------------+
| 999                                   |
+---------------------------------------+
1 row in set, 1 warning (0.08 sec)

mysql> show warnings;
+-------+-------+-------------------------------------------------+
| Level | Code  | Message                                         |
+-------+-------+-------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '999ABC111' |
+-------+-------+-------------------------------------------------+
1 row in set (0.07 sec)

在这种情况下，字符串的前三位被转换，而字符串的其余部分被丢弃，结果为999。不过，服务器发出了一个警告，让你知道并非整个字符串都被转换了。

如果你要将字符串转换为日期、时间或日期时间值，那么你需要遵循每种类型的默认格式，因为你不能为cast()函数提供格式字符串。如果你的日期字符串不是默认格式（即对于日期时间类型为YYYY-MM-DD HH:MI:SS），那么你将需要使用另一个函数，比如本章前面介绍的MySQL的str_to_date()函数。

测试你的知识

这些练习旨在测试你对本章中介绍的一些内置函数的理解。答案见附录C。

练习7-1

编写一个查询，返回字符串'Please find the substring in this string'中第17到25个字符。

练习7-2

编写一个查询，返回数字-25.76823的绝对值和符号（-1、0或1），并将该数字四舍五入到小数点后两位。

练习7-3

编写一个查询，仅返回当前日期的月份部分。