MySQL必知必会——第十章创建计算字段

创建计算字段

本章介绍什么是计算字段，如何创建计算字段以及怎样从应用程序中使用别名引用它们。

计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式。例如，想在一个字段中显示公司名和地址，但公司名和地址在不同的列中等等。

我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，再让客户机应用程序或报告程序中重新格式化。

这就需要计算字段来实现了。计算字段并不实际存在于数据库表中。它是运行时在SELECT语句内创建的。但只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段。从客户机角度看，它们没有差别。

• 字段（field） 基本上与列（column）的意思相同，经常互换使用。但数据库列一般称列，而字段通常用在计算字段的连接上。

客户机与服务器格式 许多转换和格式化工作都可在SQL语句或客户机应用程序上完成，但一般SQL语句比客户机应用程序快很多，因为DBMS就是设计来快速有效地完成这种处理的。

---

拼接字段

vendors表包含供应商名和位置信息。假设我们要生成一个供应商报表，需要在供应商名中按name(location)的格式列出供应商位置。

这列的信息，分别储存在vend_name列和vend_country列中。此外我们还需要用括号将地址括起来。为了解决问题，我们可以把两个列拼接起来。

• 拼接（concatenate） 将值联结到一起构成单个值。
• MySQL的不同之处 多数DBMS使用+或者||来实现拼接，MySQL则使用Concat()函数来实现。

MySQL的SELECT语句中，可使用Concat()函数来拼接两个列：

mysql> SELECT Concat(vend_name, ' (', vend_country, ')')
    -> FROM vendors
    -> ORDER BY vend_name;
+--------------------------------------------+
| Concat(vend_name, ' (', vend_country, ')') |
+--------------------------------------------+
| ACME (USA)                                 |
| Anvils R Us (USA)                          |
| Furball Inc. (USA)                         |
| Jet Set (England)                          |
| Jouets Et Ours (France)                    |
| LT Supplies (USA)                          |
+--------------------------------------------+
6 rows in set (0.03 sec)

Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔。以上语句拼接4个元素：

1. 存储在vend_name列中的名字。
2. 包含一个空格和一个左圆括号的串。
3. 存储在vend_country列中的国家。
4. 包含一个右圆括号的串。

结果返回包含以上元素的单个列值（计算字段）。

第八章（MySQL必知必会——第八章用通配符进行过滤）中提到的通过删除数据右侧多余空格来整理数据，可以使用MySQL的RTrim()函数来完成：

mysql> SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
    -> FROM vendors
    -> ORDER BY vend_name;
+----------------------------------------------------------+
| Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') |
+----------------------------------------------------------+
| ACME (USA)                                               |
| Anvils R Us (USA)                                        |
| Furball Inc. (USA)                                       |
| Jet Set (England)                                        |
| Jouets Et Ours (France)                                  |
| LT Supplies (USA)                                        |
+----------------------------------------------------------+
6 rows in set (0.00 sec)

RTrim()函数去掉值右边的所以空格。

• Trim函数 MySQL除了支持RTrim()，还支持LTrim()以及Trim()，去掉右、左以及两端空格。

使用别名

SELECT语句可以拼接字段。但新计算的列并没有名字，它只是一个值。未命名的列不能应用于客户机应用，因为客户机没有办法引用它。

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

mysql> SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
    -> FROM vendors
    -> ORDER BY vend_name;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
6 rows in set (0.00 sec)

此语句除计算字段后跟了AS vend_title其他都与前面一致。它指示了SQL创建一个包含指定计算的名为vend_title的计算字段。任何客户机都可以通过名字来引用这个计算字段。

• 别名的其他用途 别名还要其他用途。常见的用途包括，实际列名不合法时重新命名，原列名易混淆时扩充它，等等。
• 导出列 别名有时也称为导出列（derived column），它们代表相同的东西。

---

执行算术计算

计算字段另一常用用法是，对检索出的数据进行算数运算。

例如，以下是订单号20005中的所有物品：

mysql> SELECT prod_id, quantity, item_price
    -> FROM orderitems
    -> WHERE order_num = 20005;
+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| ANV01   |       10 |       5.99 |
| ANV02   |        3 |       9.99 |
| TNT2    |        5 |      10.00 |
| FB      |        1 |      10.00 |
+---------+----------+------------+
4 rows in set (0.01 sec)

item_price列包含订单中每项物品的单价，我们来计算物品的总价（单价*数量）：

mysql> SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
    -> FROM orderitems
    -> WHERE order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.00 sec)

结果中的expended_price列是一个计算字段，计算为quantity*item_price，即数量*单价。

MySQL支持的基本算数操作符：

操作符	说明
+	加
-	减
*	乘
/	除

圆括号可以区分优先顺序。

• 任何测试计算 SELECT提供了测试和试验函数与计算的一个很好的办法。SELECT可以省略FROM子句来简单地访问和处理表达式。例如：SELECT 3*2; 将返回6，SELECT Trim(’ abc '); 将返回abc,等等。