「SQL 数据分析系列」7. 数据生成、转换以及操作

写在前面：

大家好，我是强哥，一个热爱分享的技术狂。目前已有 12 年大数据与 AI 相关项目经验， 10 年推荐系统研究及实践经验。平时喜欢读书、暴走和写作。

业余时间专注于输出大数据、AI 等相关文章，目前已经输出了 40 万字的推荐系统系列精品文章，今年 6 月底会出版「构建企业级推荐系统：算法、工程实现与案例分析」一书。如果这些文章能够帮助你快速入门，实现职场升职加薪，我将不胜欢喜。

想要获得更多免费学习资料或内推信息，一定要看到文章最后喔。

内推信息

如果你正在看相关的招聘信息，请加我微信：liuq4360，我这里有很多内推资源等着你，欢迎投递简历。

免费学习资料

如果你想获得更多免费的学习资料，请关注同名公众号【数据与智能】，输入“资料”即可！

学习交流群

如果你想找到组织，和大家一起学习成长，交流经验，也可以加入我们的学习成长群。群里有老司机带你飞，另有小哥哥、小姐姐等你来勾搭！加小姐姐微信：epsila，她会带你入群。

第七章数据生成、转换以及操作

正如我在前言中提到的，本书致力于讨论可以跨多个数据库服务器应用的通用 SQL 技术，然而本章讨论字符串、数字和时间型数据的生成、转换和操作。由于 SQL 语言本身并不包含有关这些功能的命令，所以各数据库服务器使用内置函数来处理数据的生成、转换和操作。虽然 SQL 标准指定了一些函数，但数据库供应商通常并没有遵守这些函数规范。

因此，本章主要是介绍一些在 SQL 语句中生成和操作数据的常见方法，然后演示 Microsoft SQL Server, Oracle Database 和 MySQL 的一些内置函数。在阅读本章的同时，我强烈建议你下载一本数据库服务器的参考手册，里面会包含它所有的内置函数。倘若你使用多个数据库服务器，那么就需要涵盖多种服务器的参考手册了，比如 Kevin Kline 等人的《SQL in a Nutshell》和 Jonathan Gennick 的《SQL Pocket Guide》，这两本书都是由 O'Reilly 出版的。

使用字符串数据

使用字符串数据时，可以使用以下字符数据类型之一：

• CHAR

 容纳固定长度的字符串，不足部分由空格填充。MySQL 允许 CHAR 长度最多为 255 个字符，Oracle 数据库允许的长度最多为 2000 个字符，而 SQL Server 允许的长度最多为 8000 个字符。

• varchar

 容纳长度可变的字符串。MySQL 允许 varchar 长度最多为 65535 个字符，Oracle 数据库（varchar2 类型）允许最大长度为 4000 个字符，而 SQL Server 允许的长度最多为 8000 个字符。

• text (MySQL 和 SQL Server)或 clob (Oracle Database)

 容纳超大长度的变长字符串（通常指的是文档）。MySQL 有多种 text 类型（tinytext、text、mediumtext 和 longtext），用于保存大小不超过 4GB 的文档数据。SQL Server 为大小不超过 2GB 的文档提供了唯一的 text 类型，而 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 以来，默认的处理方式是“strict”模式，表示在出现问题时抛出异常，而在旧版本的服务器中，默认方式是截断字符串并发出警告。如果希望数据库引擎截断字符串并发出警告而不是抛出异常，则可以将它设置为 ANSI 模式。下面的示例演示如何查看数据库模式的状态，以及使用 set 命令更改模式：

mysql> SELECT @@session.sql_mode;

+----------------------------------------------------------------+

| @@session.sql_mode |

+----------------------------------------------------------------+| STRICT_TRANS_TABLES,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,ONLY_FULL_GROUP_BY,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 处于 strict 模式的情况下抛出异常），最好是将列长的上限设置得足够大，以处理可能存储在列中的最长字符串（请记住，服务器存储字符串时是按需分配的，因此为 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)

要提取用于导出的数据时，可以使用 quote()函数处理非系统生成的字符列，比如 customer_notes 列。

包含特殊字符

对于国际化的应用程序，你可能会发现自己使用的字符串中包含键盘上没有的字符。例如，在使用法语和德语时，你可能需要包含重音字符，例如é和ö。SQL Server 和 MySQL 服务器包含内置函数 char()，可以用来从 ASCII 字符集中的 255 个字符中任意构建字符串（Oracle Database 的用户可以使用 chr()函数）。为了演示这一点，下面的示例检索一个输入的字符串以及它作为 ASCII 码格式的等效字符串：

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) |

+---------------------------------------+

| ׃áíóúñÑ |

+---------------------------------------+

1 row in set (0.01 sec)

注意：我在本节的示例中使用的是 utf8mb4 字符集。如果你将会话配置为使用其他字符集，那么会看到与此处显示不同的字符集。其实概念都是差不多的，但你要熟悉相应字符集以找到特定的字符。

一个字符一个字符地构建字符串可能很繁琐，特别是当字符串中只有少数字符是重音符（或其他特殊字符）时。幸运的是，可以使用 concat()函数连接若干字符串，你可以键入一部分字符串，另一部分可以通过 char()函数生成。下面演示如何使用 concat()和 char()函数构建短语 danke schö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()函数，该函数与 position()函数类似，只是它接受可选的第三个参数，用于定义搜索的起始位置。locate()函数也是专有的，而 position()函数是符合 SQL:2003 标准的。下面的示例请求从 vchar_fld 列的第五个字符开始查找字符串'is'出现的位置：

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()函数，当提供两个参数时，它会模拟 position()函数，当提供三个参数时，它会模拟 locate()函数。SQL Server 也不包含 position()或 locate()函数，但它包含 charindx()函数，与 Oracle 的 instr()函数一样，它也接受两个或三个参数。

另一个将字符串作为参数并返回数字的函数是字符串比较函数 strcmp()。strcmp()仅由 MySQL 实现，在 Oracle Database 或 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'), 

-> ('xyz'), 

-> ('QRSTUV'), 

-> ('qrstuv'), 

-> ('12345');

Query OK,

5 rows affected (0.05 sec)Records: 5 Duplicates: 0 Warnings: 0

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

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（表示 true）或 0（表示 false）。因此，这些操作符允许构建返回数字的表达式，这与本节中描述的函数很相似。下面是一个使用 like 的例子：

mysql> SELECT name, name LIKE '%y' ends_in_y

 -> FROM category;

+-------------+-----------+

| name | ends_in_y |

+-------------+-----------+

| Action | 0 |

| Animation | 0 |

| Children | 0 |

| Classics | 0 |

| Comedy | 1 |

| Documentary | 1 |

| Drama | 0 |

| Family | 1 |

| Foreign | 0 |

| Games | 0 |

| Horror | 0 |

| Music | 0 |

| New | 0 |

| Sci-Fi | 0 |

| Sports | 0 |

| Travel | 0 |

+-------------+-----------+

16 rows in set (0.00 sec)

本例检索了所有的类别名称，如果名称以“y”结尾，则返回 1，否则返回 0。如果要执行更复杂的模式匹配，可以使用 regexp 操作符，如下所示：

mysql> SELECT name, name REGEXP 'y$' ends_in_y 

-> FROM category;

+-------------+-----------+

| name | ends_in_y |

+-------------+-----------+

| Action | 0 |

| Animation | 0 |

| Children | 0 |

| Classics | 0 |

| Comedy | 1 |

| Documentary | 1 |

| Drama | 0 |

| Family | 1 |

| Foreign | 0 |

| Games | 0 |

| Horror | 0 |

| Music | 0 |

| New | 0 |

| Sci-Fi | 0 |

| Sports | 0 |

| Travel | 0 |

+-------------+-----------+

16 rows in set (0.00 sec)

如果 name 列中存储的值与给定的正则表达式匹配，则此查询的第二列返回 1。

注意：Microsoft SQL Server 和 Oracle Database 用户可以通过构建 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(first_name, ' ', last_name, 

-> ' has been a customer since ', date(create_date)) cust_narrative

 -> FROM customer;

+---------------------------------------------------------+

| cust_narrative |

+---------------------------------------------------------+

| MARY SMITH has been a customer since 2006-02-14 |

| PATRICIA JOHNSON has been a customer since 2006-02-14 |

| LINDA WILLIAMS has been a customer since 2006-02-14 |

| BARBARA JONES has been a customer since 2006-02-14 |

| ELIZABETH BROWN has been a customer since 2006-02-14 |

| JENNIFER DAVIS has been a customer since 2006-02-14 |

| MARIA MILLER has been a customer since 2006-02-14 |

| SUSAN WILSON has been a customer since 2006-02-14 |

| MARGARET MOORE has been a customer since 2006-02-14 |

| DOROTHY TAYLOR has been a customer since 2006-02-14 

|...

| RENE MCALISTER has been a customer since 2006-02-14 |

| EDUARDO HIATT has been a customer since 2006-02-14 |

| TERRENCE GUNDERSON has been a customer since 2006-02-14 |

| ENRIQUE FORSYTHE has been a customer since 2006-02-14 |

| FREDDIE DUGGAN has been a customer since 2006-02-14 |

| WADE DELVALLE has been a customer since 2006-02-14 |

| AUSTIN CINTRON has been a customer since 2006-02-14 |

+---------------------------------------------------------+

599 rows in set (0.00 sec)

concat()函数可以处理任何返回字符串的表达式，甚至可以将数字和日期转换为字符串格式，比如上面用作参数的日期列（create_date）就是这样。尽管 Oracle Database 也包含 concat()函数，但它只接受两个字符串参数，因此前面的查询无法在 Oracle 上运行。相反，可以使用串联操作符（||），而不是函数调用，如下所示：

SELECT first_name |

| ' ' |

| last_name |

| ' has been a customer since ' |

| date(create_date)) cust_narrative

FROM customer;

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 Database 没有提供和 MySQL 的 insert()函数有同样灵活性的函数，但是它也提供了 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 开始的 5 个字符被删除，然后插入'goodbye cruel'，从而生成字符串'goodbye cruel world'。

除了在字符串中插入字符外，还可能需要从字符串中提取子字符串。为此，这三种服务器都提供了 substring()函数（Oracle Database 中为 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 参考指南，如《SQL in a Nutshell》（O'Reilly）。

使用数值型数据

与字符串数据（以及后面要介绍的时间数据）不同，数值型数据的生成非常简单。你可以键入一个数字，从另一列检索它，或通过计算生成它。所有常用的算术操作符（+、-、*、/）都可用于执行计算，并且可以使用括号指定优先级，如下所示：

mysql> SELECT (37 * 59) / (78 - (8 * 6));

+----------------------------+

| (37 * 59) / (78 - (8 * 6)) |

+----------------------------+

| 72.77 |

+----------------------------+

1 row in set (0.00 sec)

正如我在第二章中提到的，存储数值型数据时的主要问题是，如果数字精度大于所在列的指定长度，则在存储它的时候可能会对其进行四舍五入。例如，要将数字 9.96 存储在定义为 float(3,1)的列中，最终存储的数字会是四舍五入后得到的 10.0。

执行算术函数

大多数内置的数值函数都用于特定的算术目的，例如求某数的平方根。下表（7-1）列出了一些常见数值函数，它们接受单个数值参数并返回一个数字：

这些函数执行非常特定的任务，这里我并不想演示相关示例（其实从函数名和描述就能知道它的用途，如果你不知道要用什么函数，那么你可能根本就不需要用到这个函数）。然而还有些其他的算术函数，它们的灵活性更高，也更值得解释。

例如，在 MySQL 和 Oracle Database 中通过 mod()函数实现了 modulo 操作符，它计算两个数相除的余数。以下示例计算 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()函数，作为替代，它使用操作符 %来计算余数。在 SQL Server 中，表达式 10 % 4 的结果是 2。

另一个接受两个数值参数的函数是 pow()函数（在 Oracle Database 和 SQL Server 中是 power()），它返回第一个参数的第二个参数的幂次方，如下所示：

mysql> SELECT POW(2,8);

+----------+

| POW(2,8) |

+----------+

| 256 |

+----------+

1 row in set (0.03 sec)

因此，MySQL 中 pow(2,8)相当

于 2^8。由于计算机内存是以 2x 个字节为单位分配的，因此 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 会比记住它等于 1073741824 这个数字要容易得多。

控制数字精度

在处理浮点数时，你可能并不总是希望数字以全部精度进行计算以及显示。例如，在处理金融交易数据时，6 位数字的精度会更合适，然而在显示的时候，精确到百分位就可以了。有四个函数可以用来限制浮点数的精度：ceil()，floor()，round()和 truncate()。三种数据库服务器都包含这些函数，但是 Oracle Database 用的 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()，小数部分大于等于 0.5 的数字都会向上取整，反之则向下取整。

大多数情况下，需要保留部分小数部分，而不是四舍五入取整数，因此 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 为单位来售卖它们，倘若有顾客要买十七个产品，那么可以选择下面的做法，以调整顾客的订单数量（为 10 的倍数）：

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 个图钉的差别都不大，但是如果该产品是劳力士手表，那么最好还是取整，如此一来你的业务会更好。

处理有符号数

如果使用的数字列允许存储负数（在第二章中，我演示了如何将数字列标记为 unsigned，表示该列只允许存放正数），那么可能需要使用下面介绍的数值函数。例如，如果要从 account 表中使用以下数据生成每个银行帐户当前状态的报告：

+------------+--------------+---------+| account_id | acct_type | balance |+------------+--------------+---------+| 123 | MONEY MARKET | 785.22 || 456 | SAVINGS | 0.00 || 789 | CHECKING | -324.22 |+------------+--------------+---------+

以下查询返回三列数据用于生成报告：

mysql> SELECT account_id, SIGN(balance), ABS(balance)

 -> FROM account;

+------------+---------------+--------------+

| account_id | SIGN(balance) | ABS(balance) |

+------------+---------------+--------------+

| 123 | 1 | 785.22 |

| 456 | 0 | 0.00 |

| 789 | -1 | 324.22 |

+------------+---------------+--------------+

3 rows in set (0.00 sec)

第二列使用 sign()函数返回−1（如果帐户余额为负）、0（如果帐户余额为零）和 1（如果帐户余额为正）。第三列通过 abs()函数返回帐户余额的绝对值。

使用时间数据

在本章讨论的三种数据类型（字符型、数值型和时间型）中，时间型数据的生成和操作最复杂，其复杂性的部分原因是存在多种记录日期和时间的方式。例如，下面这段使用了多种方式描述日期：

• Wednesday, June 5, 2019 

• 6/05/2019 2:14:56 P.M. EST 

• 6/05/2019 19:14:56 GMT 

• 1562019 (Julian format) 

• Star date [−4] 97026.79 14:14:56 (Star Trek format)

虽然其中一些差异纯粹是格式上的问题，但大多数复杂性还与所在时区有关，我们将在下一节中对此进行详细探讨。

处理时区

因为全世界的人都喜欢将太阳直射它们所在地点的时间作为正午，所以没办法强迫所有人使用统一的时钟。因此世界被划分为 24 个时区，在同一个时区内，每个人参照相同的时间，不同时区的人在同一时刻参照的时间也不同。虽然这看起来很简单，但是有些地区在一年中会两次调整时间（实施所谓的夏时制），而另一些地区不采取这种做法，这样会造成地球上两个地方在一年中有半年的时差为四小时，另外半年时差为五小时。即使在同一个时区内，不同的地区也可能使用或不使用夏时制，导致一年中有半年时间相同，另外半年有一个小时的时差。

从航海时期开始，人们就一直在处理时差问题，到了计算机时代，该问题被加剧了。为了保证参照的时间是统一的，15 世纪的航海者在英国格林威治将他们的时钟设置为统一的时间，这被称为格林威治标准时间或 GMT（Greenwich Mean Time）。所有其他时区都可以用与 GMT 的时差来描述。例如，美国东部的时区，即东部标准时间可以用 GMT -5:00 或者说比 GMT 早五个小时来描述。

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

大多数数据库服务器根据当前所在地区设置默认时区，也提供了修改时区的工具。例如，数据库要存储世界范围内证券交易所交易的数据，则通常配置为使用 UTC 时间，而如果要存储特定零售机构的交易，则会使用服务器所在时区的时间。

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 Database 用户可以通过以下命令更改会话的时区设置：

ALTER SESSION TIMEZONE = 'Europe/Zurich'

生成时间数据

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

• 从已有 date、datetime 或 time 列复制数据；

• 执行返回 date、datetime 或 time 的内置函数；

• 构建可以被服务器识别的时间数据的字符串表示；

要使用最后一种方法，需要首先了解格式化日期的各种组成部分。

日期数据的字符串表示

第二章中的表 2-4 展示了常用的的日期组成部分，为了加深你的记忆，下表（7-2）再次显示了这些组件：

 为了构建服务器可以将之识别为 date、datetime 或 time 类型的字符串，你需要按照下表（7-3）所示的顺序整合格式化日期的各个部分：

 因此，要向 datetime 列插入一条表示 2019 年 9 月 17 日下午 3:30 的时间数据，需要构建以下字符串：

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

如果服务器需要 datetime 类型的值，例如在更新 datetime 列或调用接受 datetime 参数的内置函数时，可以为之提供一个按照正确的日期格式构建的字符串，服务器能够自动转换格式。例如，下面的语句用于修改电影租赁的归还日期：

UPDATE rental

SET return_date = '2019-09-17 15:30:00'

WHERE rental_id = 99999;

由于字符串要用于构建 datetime 列，所以服务器需要确定 set 子句中提供的字符串必须是 datetime 类型的。因此，服务器将尝试通过将字符串解析为默认 datetime 格式中包含的六个组成部分（年、月、日、小时、分钟、秒）来对其进行转换。

字符串到日期的转换

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

mysql> SELECT CAST('2019-09-17 15:30:00' AS DATETIME);

+-----------------------------------------+

| CAST('2019-09-17 15:30:00' AS DATETIME) |

+-----------------------------------------+

| 2019-09-17 15:30:00 |

+-----------------------------------------+

1 row in set (0.00 sec)

我们将在本章末尾介绍 cast()函数。虽然本例演示的是如何构建 datetime 类型值，但同样的逻辑也适用于 date 和 time 类型。以下查询使用 cast()函数生成 date 和 time 类型的值：

mysql> SELECT CAST('2019-09-17' AS DATE) date_field,

 -> CAST('108:17:57' AS TIME) time_field;

+------------+------------+

| date_field | time_field |

+------------+------------+

| 2019-09-17 | 108:17:57 |

+------------+------------+

1 row in set (0.00 sec)

当然，即使服务器需要的是 date、datetime 或 time 类型的值，但是也需要显式的转换，而不会默认进行隐式转换。

当字符串被显式或隐式地转换为时间型数据时，必须按规定次序提供所有日期组件。有些服务器对日期格式要求很严格，但 MySQL 服务器对组件之间使用的分隔符要求很宽松。例如，MySQL 将接受以下所有字符串作为 2019 年 9 月 17 日下午 3:30 的有效表示：

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

'2019/09/17 15:30:00'

'2019,09,17,15,30,00'

'20190917153000'

]

尽管这样的灵活性更高，但是你可能会发现自己正在尝试生成一个没有默认日期组件的时间值。下面演示的内置函数比 cast()函数灵活得多。

生成日期的函数

如果需要从字符串生成时间数据，而该字符串的格式不是 cast()函数接受的格式，则可以使用内置函数将字符串格式化为日期字符串。MySQL 为此提供了 str_to_date()函数。例如，你从一个文件中提取出字符串'September 17, 2019'并使用它来更新日期列。由于字符串不是所需的 YYYY-MM-DD 格式，因此可以使用 str_to_date()函数格式化，使之能用于 cast()函数，如下所示：

UPDATE rental

SET return_date = STR_TO_DATE('September 17, 2019', '%M %d, %Y')

WHERE rental_id = 99999;

str_to_date()的第二个参数定义了日期字符串的格式，在本例中，它包含一个月名（%M）、一个日（%d）和一个四位数的年份（%Y）。虽然它可以识别 30 多种格式组件，但是下表（7-4）只介绍十几个最常用的组件的定义：

函数 str_to_date()的作用是：根据格式字符串的内容返回 date、datetime 或 time 类型的值。例如，如果格式字符串仅包含 %H、%i 和 %s，则将返回 time 值。

注意：Oracle Database 用户可以使用 to_date()函数，它与与 MySQL 的 str_to_date()函数的功能相同。而 SQL Server 包含的 convert()函数不如 MySQL 和 Oracle Database 的相应函数灵活，它只能接受 21 种预定义格式的字符串，而不提供自定义格式的字符串。

如果要生成当前日期/时间，则不需要手动构造字符串，因为以下内置函数将访问系统时钟返回当前的日期或时间字符串：

mysql> SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP();

+----------------+----------------+---------------------+

| CURRENT_DATE() | CURRENT_TIME() | CURRENT_TIMESTAMP() |

+----------------+----------------+---------------------+

| 2019-06-05 | 16:54:36 | 2019-06-05 16:54:36 |

+----------------+----------------+---------------------+

1 row in set (0.12 sec)

这些函数按照所返回时间类型的默认格式返回当前日期/时间值。返回的值是所返回的时间类型的默认格式。Oracle Database 包含 current_date()和 current_timestamp()函数，但不包含 current_time()函数，而 Microsoft SQL Server 仅包含 current_timestamp()函数。

操作时间数据

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

返回日期的时间函数

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

mysql> SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY);

+------------------------------------------+

| DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY) |

+------------------------------------------+

| 2019-06-10 |

+------------------------------------------+

1 row in set (0.06 sec)

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

虽然上表（7-5）中列出的前六种类型非常简单，但后三种类型由于有多个元素，所以需要进一步解释。例如，如果你被告知某部影片实际上比最初指定的时间晚了 3 小时 27 分 11 秒，你可以通过以下方法修正时间：

UPDATE rental

SET return_date = DATE_ADD(return_date, INTERVAL '3:27:11' HOUR_SECOND)

WHERE rental_id = 99999;

在本例中，date_add()函数获取 return_date 列中的值，并使其增加 3 小时 27 分钟 11 秒，然后使用生成的值修改 return_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 Database 用户可以在本例中使用 add_months()函数，如下所示：

UPDATE employee

SET birth_date = ADD_MONTHS(birth_date, 119)

WHERE emp_id = 4789;

某些情况下，你想给某个日期加上一段时间，然而你只知道目标时间却不知道离原定日期相差的具体天数。例如，假设一个银行客户登录网上银行系统，并安排在月末转账。你可以调用 last_day()函数，而不是编写代码计算当前月份并计算该日到月底剩余的天数，该函数能为你完成这项工作（MySQL 和 Oracle Database 都包含 last_day()函数而 SQL Server 没有类似的函数）。如果客户要求在 2019 年 9 月 17 日转账，则可以通过以下方式找到 9 月的最后一天：

mysql> SELECT LAST_DAY('2019-09-17');

+------------------------+

| LAST_DAY('2019-09-17') |

+------------------------+

| 2019-09-30 |

+------------------------+

1 row in set (0.10 sec)

无论提供的是 date 还是 datetime 值，last_day()函数都会返回 date。虽然这个函数看起来并没怎么节省大量时间，但是它实际上处理了很繁琐棘手的逻辑，比如当你要找到二月的最后一天前还要先确定该年是否是闰年。

返回字符串的时间函数

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

mysql> SELECT DAYNAME('2019-09-18');

+-----------------------+

| DAYNAME('2019-09-18') |

+-----------------------+

| Wednesday |

+-----------------------+

1 row in set (0.00 sec)

MySQL 中包含了许多这样的函数，它们用于从日期值中提取信息，但我建议你改用 extract()函数实现这些功能，因为记住一个函数的几个变体比记住十几个不同的函数更容易。另外，extract()函数是 SQL:2003 标准的一部分，并在 MySQL 和 Oracle Database 中都有实现。

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

mysql> SELECT EXTRACT(YEAR FROM '2019-09-18 22:19:05');

+------------------------------------------+

| EXTRACT(YEAR FROM '2019-09-18 22:19:05') |

+------------------------------------------+

| 2019 |

+------------------------------------------+

1 row in set (0.00 sec)

注意：SQL Server 不包含 extract()的实现，但提供 datepart()函数。下面是如何使用 datepart()函数从 datetime 值中提取年份的语句：

SELECT DATEPART(YEAR, GETDATE())

返回数字的时间函数

我在本章前面介绍过一个函数，它用于向某日期值增加一段时间间隔，从而生成另一个日期值。关于日期的处理，还有一种常用的行为是接受两个日期值，求出它们之间相差的时间间隔（天、周、年）。为此，MySQL 包含函数 datediff()，它返回两个日期之间的天数。例如，如果我想知道我的孩子今年暑假放多久，可以查询如下：

mysql> SELECT DATEDIFF('2019-09-03', '2019-06-21');

+--------------------------------------+

| DATEDIFF('2019-09-03', '2019-06-21') |

+--------------------------------------+

| 74 |

+--------------------------------------+

1 row in set (0.00 sec)

也就是说在孩子们安全返校前，我得忍受 74 天的痛苦生活了。datediff()函数忽略参数中的时钟值，即使我将第一个日期设置为午夜最后一秒，第二个日期设置为午夜开始的第一秒，这些对计算结果都不会有任何影响：

mysql> SELECT DATEDIFF('2019-09-03 23:59:59', '2019-06-21 00:00:01');

+--------------------------------------------------------+

| DATEDIFF('2019-09-03 23:59:59', '2019-06-21 00:00:01') |

+--------------------------------------------------------+

| 74 |

+--------------------------------------------------------+

1 row in set (0.00 sec)

如果我交换参数的次序，使用较早的日期作为第一个参数，那么 datediff()将返回一个负数，如下所示：

mysql> SELECT DATEDIFF('2019-06-21', '2019-09-03');

+--------------------------------------+

| DATEDIFF('2019-06-21', '2019-09-03') |

+--------------------------------------+

| -74 |

+--------------------------------------+

1 row in set (0.00 sec)

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

SELECT DATEDIFF(DAY, '2019-06-21', '2019-09-03')

此外，Oracle Database 允许通过两日期相减的方式求出它们之间相差的天数。

转换函数

我在本章前面介绍过如何使用 cast()函数将字符串转换为 datetime 值。其实每个数据库服务器都包含许多用于将数据从一种类型转换为另一种类型的专有函数，但我建议使用 cast()函数，该函数包含在 SQL:2003 标准中并已由 MySQL、Oracle Database 和 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。不过服务器产生了一条警告，提示并非所有字符串都已被转换。

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