第7章 创建计算字段

7.1 计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式，下面举几个例子。

• 需要显示公司名，同时还需要显示公司的地址，但这两个信息存储在不同的表列中。
• 城市、州和邮政编码存储在不同的列中，但邮件标签打印程序需要把它们作为一个有恰当格式的字段检索出来。
• 列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。
• 物品订单表存储物品的价格和数量，不存储每个物品的总价格。但为打印发票，需要物品的总价格。
• 需要根据表数据进行诸如总数、平均数的计算。

上述例子中，存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化。

字段（field）
基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常与计算字段一起使用。计算字段并不实际存在于数据库表中，是运行时在 SELECT 语句内创建的。

注意
只有数据库知道 SELECT 语句中哪些列是实际的表列，哪些列是计算字段。从客户端（如应用程序）来看，计算字段的数据与其他列的数据的返回方式相同。

提示：客户端与服务器的格式
在 SQL 语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户端中完成要快得多。

7.2 拼接字段

例子，创建由两列组成的标题。Vendors 表包含供应商名和地址信息。假如要生成一个供应商报表，需要在格式化的名称（位置）中列出供应商的位置。

此报表需要一个值，而表中数据存储在两个列 vend_name 和 vend_country 中。此外，需要用括号将 vend_country 括起来，这些东西都没有存储在数据库表中。

拼接（concatenate）
将值联结到一起（将一个值附加到另一个值）构成单个值。
解决办法：把两个列拼接起来。在 SQL 中的 SELECT 语句中，用特殊操作符拼接两个列。根据 DBMS，此操作符可用加号（+）或两个竖杠（||）表示。

• 在 MySQL 和 MariaDB 中，必须使用特殊的函数。
• Access 和 SQL Server 使用+号。
• DB2、Oracle、PostgreSQL、SQLite 和 Open Office Base 使用||。

下面是使用加号的例子（多数 DBMS 使用这种语法）：

SELECT vend_name + '(' + vend_country + ')' FROM Vendors
ORDER BY vend_name;

屏幕快照 2018-05-27 13.25.53.png

下面是使用 MySQL 或 MariaDB 时需要使用的语句：

SELECT Concat(vend_name, '(', vend_country, ')') FROM Vendors
ORDER BY vend_name;

分析
上面两个 SELECT 语句拼接以下元素：

• 存储在 vend_name 列中的名字；
• 包含一个空格和一个左圆括号的字符串；
• 存储在 vend_country 列中的国家；
• 包含一个右圆括号的字符串。

从输出中看到，SELECT 语句返回包含上述四个元素的一个列（计算字段）。

上述 SELECT 语句返回的输出，结合成一个计算字段的两个列用空格填充。许多数据库保存填充为列宽的文本值，而实际上要的结果不需要这些空格。为正确返回格式化的数据，必须去掉这些空格，使用 SQL 的 RTRIM()函数来完成：

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
FROM Vendors ORDER BY vend_name;

屏幕快照 2018-05-27 14.09.22.png

RTRIM()函数去掉值右边的所有空格，通过使用 RTRIM()，各个列都进行了整理。

• RTRIM()：去掉字符串右边的空格
• LTRIM()：去掉字符串左边的空格
• TRIM()：去掉字符串左右两边的空格

使用别名
从输出可以看到，SELECT 语句可以很好地拼接地址字段。但是，这个新计算列没有名字，它只是一个值。
如果仅在 SQL 查询工具中查看一下结果，这样没有什么不好。但是，一个未命名的列不能用于客户端应用中，因为客户端没有办法引用它。

为了解决这个问题，SQL 支持列别名。别名（alias）是一个字段或值的替换名，别名用 AS 关键字赋予：

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
       AS vend_title
FROM Vendors ORDER BY vend_name;

屏幕快照 2018-05-27 14.20.09.png

下面是 MySQL 和 MariaDB 中使用的语句：

SELECT Concat(vend_name, '(', vend_cpuntry, ')')
       AS vend_title
FROM Vendors ORDER BY vend_name;

SELECT 语句的计算字段之后跟了文本 AS vend_title。它指示 SQL 创建一个包含指定计算结果的名为 vend_title 的计算字段。
从输出看到，结果与以前的相同，但现在列名为 vend_title，任何客户端应用都可以按名称引用这个列，就像它是一个实际的表列一样。

在很多 DBMS 中，AS 关键字是可选的，不过最好使用它。
别名还有其他用途，包括在实际的表列名包含不合法的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它。

7.3 执行算数计算

计算字段的另一常见用途是对检索出的数据进行算术计算。
例子，Orders 表包含收到的所有订单，OrderItems 表包含每个订单中的各项物品。下面的 SQL 语句检索订单号 20008 中的所有物品：

SELECT prod_id, quantity, item_price FROM OrderItems
WHERE order_num = 20008;

屏幕快照 2018-05-27 14.33.24.png

item_price 列包含订单中每项物品的单价。如下汇总物品的价格（单价乘以订购数量）：

SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM OrderItems WHERE order_num = 20008;

屏幕快照 2018-05-27 14.37.03.png

输出中的 expanded_price 列是一个计算字段，此计算为quantity* item_price。客户端应用现在可以使用这个新计算列，就像使用其他列一样。

第8章 使用函数处理数据

8.1 函数

函数在数据上执行，为数据的转换和处理提供方便。但是 SQL 函数会带来一些问题：
每一个 DBMS 都有特定的函数，只有少数几个函数被所有主要的 DBMS 支持。虽然所有类型的函数一般都可以在每个 DBMS 中使用，但各个函数的名称和语法可能极其不同。

为了说明可能存在的问题，列出 3 个常用的函数及其在各个 DBMS 中的语法：

屏幕快照 2018-05-27 14.44.38.png

8.2 使用函数

大多数 SQL 实现支持以下类型的函数：

• 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。
• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。
• 返回 DBMS 正使用的特殊信息（如返回用户登录信息）的系统函数。

文本处理函数
UPPER()将文本转换为大写

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors ORDER BY vend_name;

屏幕快照 2018-05-27 16.59.53.png

表中的 SOUNDEX 需要注意，SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX 考虑类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。

日期和时间处理函数
日期和时间采用相应的数据类型存储在表中，每种 DBMS 都有自己的特殊形式。日期和时间值以特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。

应用程序一般不使用日期和时间的存储格式，因此日期和时间函数总是用来读取、统计和处理这些值。

Orders 表中的订单都带有订单日期，在 SQL Server 中检索2012年的所有订单：

SELECT order_num FROM Orders
WHERE DATEPART(yy, order_date) = 2012;

屏幕快照 2018-05-27 17.50.16.png

SQL Server 使用 DATEPART() 函数，此函数返回日期的某一部分。DATEPART()函数有两个参数，分别是返回的成分和从中返回成分的日期。
例子中，DATEPART()只从 order_date 列中返回年份。通过与2012比较，WHERE 子句只过滤出此年份的订单。

PostgreSQL 版本的DATE_PART()函数：

SELECT order_num FROM Orders
WHERE DATE_PART('year', order_date) = 2012;

MySQL 和 MariaDB 具有各种日期处理函数，但没有 DATEPART() 。MySQL 和 MariaDB 用户可使用名为YEAR()的函数从日期中提取年份：

SELECT order_num FROM Orders
WHERE YEAR(order_date) = 2012;

这里给出的例子提取和使用日期的成分（年）。按月份过滤，可以进行相同的处理，使用 AND 操作符可以进行年和月份的比较。

大多数 DBMS 具有比较日期、执行基于日期的运算、选择日期格式等的函数。但是，不同 DBMS 的日期-时间处理函数可能不同。

数值处理函数
数值处理函数仅处理数值数据，用于代数、三角或几何运算，因此不像字符串或日期-时间处理函数使用那么频繁。