day06-创建计算字段
计算字段
存储在数据库表中的数据一般不是应用程序所需要的格式，下面举几个例子。
需要显示公司名，同时还需要显示公司的地址，但这两个信息存储在不同的表列中。
城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签打印程序需要把它们作为一个有恰当格式的字段检索出来。
列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。
物品订单表存储物品的价格和数量，不存储每个物品的总价格（用价格乘以数量即可）。但为打印发票，需要物品的总价格。
需要根据表数据进行诸如总数、平均数的计算。
在上述每个例子中，存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化。
这就是计算字段可以派上用场的地方了。与前几课介绍的列不同，计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。
字段（field）
基本上与列（column）的意思相同，经常互换使用，不过数据库列一
般称为列，而字段这个术语通常在计算字段这种场合下使用。
需要特别注意，只有数据库知道 SELECT 语句中哪些列是实际的表列，哪些列是计算字段。从客户端（如应用程序）来看，计算字段的数据与其他列的数据的返回方式相同。
提示：客户端与服务器的格式
在 SQL 语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户端中完成要快得多。
拼接字段
为了说明如何使用计算字段，我们来举一个简单例子，创建由两列组成的标题。
Vendors 表包含供应商名和地址信息。假如要生成一个供应商报表，需
要在格式化的名称（位置）中列出供应商的位置。
此报表需要一个值，而表中数据存储在两个列 vend_name 和 vend_country中。此外，需要用括号将 vend_country 括起来，这些东西都没有存储在数据库表中。这个返回供应商名称和地址的 SELECT 语句很简单，但我们是如何创建这个组合值的呢？
拼接（concatenate）
将值联结到一起（将一个值附加到另一个值）构成单个值。
解决办法是把两个列拼接起来。在 SQL 中的 SELECT 语句中，可使用一个特殊的操作符来拼接两个列。根据你所使用的 DBMS，此操作符可用加号（+）或两个竖杠（||）表示。在 MySQL 和 MariaDB 中，必须使用特殊的函数。
说明：是+还是||？
SQL Server 使用+号。DB2、Oracle、PostgreSQL 和 SQLite 使用||。详细请参阅具体的 DBMS 文档。
下面是使用加号的例子（多数 DBMS 使用这种语法）：
SELECT vend_name + '(' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;
输出：
mysql> SELECT vend_name + '(' + vend_country + ')'
-> FROM Vendors
-> ORDER BY vend_name;
+--------------------------------------+
| vend_name + '(' + vend_country + ')' |
+--------------------------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+--------------------------------------+
6 rows in set, 24 warnings (0.00 sec)
mysql>
SELECT vend_name || '(' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;
输出：
mysql> SELECT vend_name || '(' || vend_country || ')'
-> FROM Vendors
-> ORDER BY vend_name;
+-----------------------------------------+
| vend_name || '(' || vend_country || ')' |
+-----------------------------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+-----------------------------------------+
6 rows in set, 17 warnings (0.00 sec)
mysql>
SELECT Concat(vend_name, ' (', vend_country, ')')
FROM Vendors
ORDER BY vend_name;
输出：
mysql> SELECT Concat(vend_name, ' (', vend_country, ')')
-> FROM Vendors
-> ORDER BY vend_name;
+--------------------------------------------+
| Concat(vend_name, ' (', vend_country, ')') |
+--------------------------------------------+
| Bear Emporium (USA) |
| Bears R Us (USA) |
| Doll House Inc. (USA) |
| Fun and Games (England) |
| Furball Inc. (USA) |
| Jouets et ours (France) |
+--------------------------------------------+
6 rows in set (0.00 sec)
mysql>
上面两个 SELECT 语句拼接以下元素：
存储在 vend_name 列中的名字；
包含一个空格和一个左圆括号的字符串；
存储在 vend_country 列中的国家；
包含一个右圆括号的字符串。
从上述输出中可以看到，SELECT 语句返回包含上述四个元素的一个列（计算字段）。
再看看上述 SELECT 语句返回的输出。结合成一个计算字段的两个列用空格填充。许多数据库（不是所有）保存填充为列宽的文本值，而实际上你要的结果不需要这些空格。为正确返回格式化的数据，必须去掉这些空格。这可以使用 SQL 的 RTRIM()函数来完成，如下所示：
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;
输出：
mysql> SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
-> FROM Vendors
-> ORDER BY vend_name;
+-----------------------------------------------------+
| RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' |
+-----------------------------------------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+-----------------------------------------------------+
6 rows in set, 24 warnings (0.00 sec)
下面是相同的语句，但使用的是||：
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
FROM Vendors
ORDER BY vend_name;
输出：
ysql> SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
-> FROM Vendors
-> ORDER BY vend_name;
+--------------------------------------------------------+
| RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' |
+--------------------------------------------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+--------------------------------------------------------+
6 rows in set, 17 warnings (0.00 sec)
mysql> ^C
mysql>
RTRIM()函数去掉值右边的所有空格。通过使用 RTRIM()，各个列都进
行了整理。
说明：TRIM 函数
大多数 DBMS 都支持 RTRIM()（正如刚才所见，它去掉字符串右边的空格）、LTRIM()（去掉字符串左边的空格）以及 TRIM()（去掉字符串左右两边的空格）。
使用别名
从前面的输出可以看到，SELECT 语句可以很好地拼接地址字段。但是，这个新计算列的名字是什么呢？实际上它没有名字，它只是一个值。如果仅在 SQL 查询工具中查看一下结果，这样没有什么不好。但是，一个未命名的列不能用于客户端应用中，因为客户端没有办法引用它。
为了解决这个问题，SQL 支持列别名。别名（alias）是一个字段或值的替换名。别名用 AS 关键字赋予。请看下面的 SELECT 语句：
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;
输出：
mysql> SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
-> AS vend_title
-> FROM Vendors
-> ORDER BY vend_name;
+------------+
| vend_title |
+------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+------------+
6 rows in set, 24 warnings (0.00 sec)
mysql>
下面是相同的语句，但使用的是||语法：
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;
输出：
mysql> SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
-> AS vend_title
-> FROM Vendors
-> ORDER BY vend_name;
+------------+
| vend_title |
+------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+------------+
6 rows in set, 17 warnings (0.00 sec)
mysql>
下面是 MySQL 和 MariaDB 中使用的语句：
SELECT Concat(RTrim(vend_name), ' (',
RTrim(vend_country), ')') AS vend_title
FROM Vendors
ORDER BY vend_name;
输出：
mysql> SELECT Concat(RTrim(vend_name), ' (',
-> RTrim(vend_country), ')') AS vend_title
-> FROM Vendors
-> ORDER BY vend_name;
+-------------------------+
| vend_title |
+-------------------------+
| Bear Emporium (USA) |
| Bears R Us (USA) |
| Doll House Inc. (USA) |
| Fun and Games (England) |
| Furball Inc. (USA) |
| Jouets et ours (France) |
+-------------------------+
6 rows in set (0.00 sec)
mysql>
SELECT 语句本身与以前使用的相同，只不过这里的计算字段之后跟了文本 AS vend_title。它指示 SQL 创建一个包含指定计算结果的名为vend_title 的计算字段。从输出可以看到，结果与以前的相同，但现在列名为 vend_title，任何客户端应用都可以按名称引用这个列，就像
它是一个实际的表列一样。
说明：AS 通常可选
在很多 DBMS 中，AS 关键字是可选的，不过最好使用它，这被视为一条最佳实践。
提示：别名的其他用途
别名还有其他用途。常见的用途包括在实际的表列名包含不合法的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它。
注意：别名
别名的名字既可以是一个单词，也可以是一个字符串。如果是后者，字符串应该括在引号中。虽然这种做法是合法的，但不建议这么去做。多单词的名字可读性高，不过会给客户端应用带来各种问题。因此，别名最常见的使用是将多个单词的列名重命名为一个单词的名字。
说明：导出列
别名有时也称为导出列（derived column），不管怎么叫，它们所代表的是相同的东西。
执行算术计算
计算字段的另一常见用途是对检索出的数据进行算术计算。举个例子，Orders 表包含收到的所有订单，OrderItems 表包含每个订单中的各项物品。下面的 SQL 语句检索订单号 20008 中的所有物品：
SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_num = 20008;
输出：
mysql> SELECT prod_id, quantity, item_price
-> FROM OrderItems
-> WHERE order_num = 20008;
+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| RGAN01 | 5 | 4.99 |
| BR03 | 5 | 11.99 |
| BNBG01 | 10 | 3.49 |
| BNBG02 | 10 | 3.49 |
| BNBG03 | 10 | 3.49 |
+---------+----------+------------+
5 rows in set (0.00 sec)
item_price 列包含订单中每项物品的单价。如下汇总物品的价格（单价乘以订购数量）：
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
输出：
mysql> SELECT prod_id,
-> quantity,
-> item_price,
-> quantity*item_price AS expanded_price
-> FROM OrderItems
-> WHERE order_num = 20008;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| RGAN01 | 5 | 4.99 | 24.95 |
| BR03 | 5 | 11.99 | 59.95 |
| BNBG01 | 10 | 3.49 | 34.90 |
| BNBG02 | 10 | 3.49 | 34.90 |
| BNBG03 | 10 | 3.49 | 34.90 |
+---------+----------+------------+----------------+
5 rows in set (0.00 sec)
输出中显示的 expanded_price 列是一个计算字段，此计算为 quantity*item_price。客户端应用现在可以使用这个新计算列，就像使用其他列一样。
提示：如何测试计算
SELECT 语句为测试、检验函数和计算提供了很好的方法。虽然 SELECT通常用于从表中检索数据，但是省略了 FROM 子句后就是简单地访问和处理表达式，例如 SELECT 3 * 2;将返回 6，SELECT Trim(' abc ');将返回 abc，SELECT Curdate();使用 Curdate()函数返回当前日期和时间。现在你明白了，可以根据需要使用 SELECT 语句进行检验。
小结
这一课介绍了计算字段以及如何创建计算字段。我们用例子说明了计算字段在字符串拼接和算术计算中的用途。此外，还讲述了如何创建和使用别名，以便应用程序能引用计算字段。
小练习
1.别名的常见用法是在检索出的结果中重命名表的列字段（为了符合特定的报表要求或客户需求）。编写 SQL 语句，从 Vendors 表中检索vend_id、vend_name、vend_address 和 vend_city，将 vend_name重命名为 vname，将 vend_city 重命名为 vcity，将 vend_address重命名为 vaddress。按供应商名称对结果进行排序（可以使用原始 名称或新的名称）。
SELECT vend_id,
vend_name as vname,
vend_address AS vaddress,
vend_city AS vcity
FROM Vendors
ORDER BY vname;
输出：
mysql> SELECT vend_id,
-> vend_name as vname,
-> vend_address AS vaddress,
-> vend_city AS vcity
-> FROM Vendors
-> ORDER BY vname;
+---------+-----------------+-----------------+------------+
| vend_id | vname | vaddress | vcity |
+---------+-----------------+-----------------+------------+
| BRE02 | Bear Emporium | 500 Park Street | Anytown |
| BRS01 | Bears R Us | 123 Main Street | Bear Town |
| DLL01 | Doll House Inc. | 555 High Street | Dollsville |
| FNG01 | Fun and Games | 42 Galaxy Road | London |
| FRB01 | Furball Inc. | 1000 5th Avenue | New York |
| JTS01 | Jouets et ours | 1 Rue Amusement | Paris |
+---------+-----------------+-----------------+------------+
6 rows in set (0.00 sec)
mysql>
2.我们的示例商店正在进行打折促销，所有产品均降价 10%。编写 SQL 语句，从 Products 表中返回 prod_id、prod_price 和 sale_price。 sale_price 是一个包含促销价格的计算字段。提示：可以乘以 0.9，得到原价的 90%（即 10%的折扣）。
SELECT prod_id, prod_price,
prod_price * 0.9 AS sale_price
FROM Products;
输出：
mysql> SELECT prod_id, prod_price,
-> prod_price * 0.9 AS sale_price
-> FROM Products;
+---------+------------+------------+
| prod_id | prod_price | sale_price |
+---------+------------+------------+
| BNBG01 | 3.49 | 3.141 |
| BNBG02 | 3.49 | 3.141 |
| BNBG03 | 3.49 | 3.141 |
| BR01 | 5.99 | 5.391 |
| BR02 | 8.99 | 8.091 |
| BR03 | 11.99 | 10.791 |
| RGAN01 | 4.99 | 4.491 |
| RYL01 | 9.49 | 8.541 |
| RYL02 | 9.49 | 8.541 |
+---------+------------+------------+
9 rows in set (0.00 sec)
mysql>
浙公网安备 33010602011771号