【SQL笔记】三、创建计算字段

目录

         1.相关概念

         2.拼接字段

         3.执行算术计算

         4.测试题

---

 1.相关概念

      存储在数据库表中的数据一般不是应用程序所需要的格式。例如：

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

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

        这就是计算字段可以派上用场的地方了。计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。

        字段（field），基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而字段这个术语通常在计算字段这种场合下使用。

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

2.拼接字段

        拼接（concatenate），是将值联结到一起（将一个值附加到另一个值）构成单个值。

        在 SQL 中的SELECT语句中，可使用一个特殊的操作符来拼接两个列。根据所使用的 DBMS，此操作符可用加号（+）或两个竖杠（||）表示。

以+为例，

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

 以 || 为例，

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

输出结果均为：

-----------------------------------------------------------
Bear Emporium                                (USA        )
Bears R Us                                   (USA        )
Doll House Inc.                              (USA        )
Fun and Games                                (England    )
Furball Inc.                                 (USA        )
Jouets et ours                               (France     )

        从输出结果可以看出，许多数据库（不是所有）保存填充为列宽的文本值，而实际上你要的结果不需要这些空格。为正确返回格式化的数据，必须去掉这些空格。这可以使用 SQL 的RTRIM（）函数来完成。

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

输出：

-----------------------------------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

注：大多数 DBMS 都支持 RTRIM()（去掉字符串右边的空格）、LTRIM()（去掉字符串左边的空格）以及 TRIM()（去掉字符串左右两边的空格）

        SELECT 语句可以很好地拼接地址字段。但是，这个新计算列的名字是什么呢？实际上它没有名字，它只是一个值。一个未命名的列不能用于客户端应用中，因为客户端没有办法引用它。

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

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

注：别名有时也称为导出列（derived column）。

3.执行算术计算

举例

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

输出：

prod_id        quantity        item_price
----------     -----------     ---------------------
RGAN01         5               4.9900
BR03           5               11.9900
BNBG01         10              3.4900
BNBG02         10              3.4900
BNBG03         10              3.4900

创建计算字段expanded_price = quantity * item_price :

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.9900           24.9500
BR03           5               11.9900          59.9500
BNBG01         10              3.4900           34.9000
BNBG02         10              3.4900           34.9000
BNBG03         10              3.4900           34.9000

        注：虽然 SELECT 通常用于从表中检索数据，但是省略了 FROM 子句后就是简单地访问和处理表达式，例如 SELECT 3 * 2; 将返回 6，SELECT Trim(' abc '); 将返回 abc，SELECT Curdate(); 使用 Curdate() 函数返回当前日期和时间。

4.测试题

        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;

注：前面已经用AS改名了，所以后面用ORDER BY进行排序时，使用新名字。

        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;