MySQL已经成为世界上最受欢迎的数据库管理系统之一。无论是用 在小型开发项目上,还是用来构建那些声名显赫的网站,MySQL都证明 了自己是个稳定、可靠、快速、可信的系统,足以胜任任何数据存储业 务的需要。
借工作之余,大家一起聊一下什么是计算字段,如何创建计算字段以及怎样从应用程序 中使用别名引用它们。
1.计算字段
有时候,储存在数据库表中的数据不是应用程序所需要的格式,那么就需要进行创建字段以满足应用程序。下面举几个例子:
a.需要显示公司名,同时还需要显示公司的地址,但这两个信息储存在不同的表列中;
b.城市、州和邮政编码存储在不同的列中(应该这样),但邮件标签 打印程序却需要把它们作为一个恰当格式的字段检索出来;
c.列数据是大小写混合的,但报表程序需要把所有数据按大写表示 出来;
d.物品订单表存储物品的价格和数量,没有(但不需要存储)每个物品的总 价格(用价格乘以数量即可)。为打印发票,需要物品的总价格;
e.需要根据表数据进行总数、平均数计算或其他计算.
在上述例子中,储存在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据,而不是检索出数据,然后再在客户端应用程序中重新格式化。这时候就是计算字段可以派上用场的地方了。计算字段并不实际存在于数据库表中,计算字段是运行时在SELECT语句内创建的。
字段(field)
基本上与列(column)的意思相同,经常互换使 用,不过数据库列一般称为列,而术语字段通常用在计算字段的 连接上。
重要的是要注意到,只有数据库知道SELECT语句中哪些列是实际的 表列,哪些列是计算字段。从客户机(如应用程序)的角度来看,计算 字段的数据是以与其他列的数据相同的方式返回的。
提示:客户机与服务器的格式
在SQL语句内完成的许多转换 和格式化工作都可以直接在客户机应用程序内完成。但一 般来说,在数据库服务器上完成这些操作比在客户机中完 成要快得多,因为DBMS是设计来快速有效地完成这种处 理的。
2.拼接字段
为了说明如何使用计算字段,举一个创建由两列组成的标题的简单 例子。
vendors表包含供应商名和位置信息。假如要生成一个供应商报表, 需要在供应商的名字中按照name(location)这样的格式列出供应商的位 置。
此报表需要单个值,而表中数据存储在两个列vend_name和vend_ country中。此外,需要用括号将vend_country括起来,这些东西都没有 明确存储在数据库表中。我们来看看怎样编写返回供应商名和位置的 SELECT语句。
拼接(concatenate) 将值联结到一起构成单个值
解决办法是把两个列拼接起来。在MySQL的SELECT语句中,可使用 Concat()函数来拼接两个列。
提示: MySQL的不同之处
多数DBMS使用+或||来实现拼接, MySQL则使用Concat()函数来实现。当把SQL语句转换成 MySQL语句时一定要把这个区别铭记在心。
输入:SELECT Concat(vend_name,' (',vend_country,')') FROM Vendors ORDER BY vend_name;
输出:Concat(vend_name,' (',vend_country,')')
ACME (USA)
Anvlis R Us (USA)
Furball Inc. (USA)
Bears R Us (USA)
Fun and Games (England)
Jouets et ours (France)
分析:Concat()拼接串,即把多个串连接起来形成一个较长的串。 Concat()需要一个或多个指定的串,各个串之间用逗号分隔。
上面的SELECT语句连接以下4个元素:
存储在vend_name列中的名字;
包含一个空格和一个左圆括号的串;
存储在vend_country列中的国家;
包含一个右圆括号的串。
从上述输出中可以看到,SELECT语句返回包含上述4个元素的单个列 (计算字段)。
Mysql可以通过删除数据右侧多余的空格来整理数据,这可以 使用MySQL的RTrim()函数来完成,如下所示:
输入:SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),')') FROM Vendors ORDER BY vend_name;
分析:RTrim()函数去掉值右边的所有空格。通过使用RTrim(),各个 列都进行了整理。
Trim函数
MySQL除了支持RTrim()(正如刚才所见,它去掉 串右边的空格),还支持LTrim()(去掉串左边的空格)以及 Trim()(去掉串左右两边的空格)。
使用别名
从前面的输出中可以看到,SELECT语句拼接地址字段工作得很好。 但此新计算列的名字是什么呢?实际上它没有名字,它只是一个值。如 果仅在SQL查询工具中查看一下结果,这样没有什么不好。但是,一个未 命名的列不能用于客户机应用中,因为客户机没有办法引用它。
为了解决这个问题,SQL支持列别名。别名(alias)是一个字段或值 的替换名。别名用AS关键字赋予。请看下面的SELECT语句:
输入:
SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),')')
as vend_title
FROM Vendors
ORDER BY vend_name;
输出:
vend_title
ACME (USA)
Anvlis R Us (USA)
Furball Inc. (USA)
Bears R Us (USA)
Fun and Games (England)
Jouets et ours (France)
分析:
SELECT语句本身与以前使用的相同,只不过这里的语句中计算 字段之后跟了文本AS vend_title。它指示SQL创建一个包含 指定计算的名为vend_title的计算字段。从输出中可以看到,结果与以 前的相同,但现在列名为vend_title,任何客户机应用都可以按名引用 这个列,就像它是一个实际的表列一样。
扩展:别名的其他用途
别名还有其他用途。常见的用途包括在实际 的表列名包含不符合规定的字符(如空格)时重新命名它,在 原来的名字含混或容易误解时扩充它,等等。
导出列
别名有时也称为导出列(derived column),不管称为 什么,它们所代表的都是相同的东西。
3.执行算数计算
计算字段的另一常见用途是对检索出的数据进行算术计算。举一个 例子,orders表包含收到的所有订单,orderitems表包含每个订单中的 各项物品。下面的SQL语句检索订单号20005中的所有物品:
输入:
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
item_price列包含订单中每项物品的单价。如下汇总物品的价格(单 价乘以订购数量):
输入:
SELECT prod_id,quantity,item_price,quantity*item 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
分析:
输出中显示的expanded_price列为一个计算字段,此计算为 quantity*item_price。客户机应用现在可以使用这个新计算 列,就像使用其他列一样。
MySQL支持下表中列出的基本算术操作符。此外,圆括号可用来 区分优先顺序。
SQL算术操作符
操 作 符 说 明
+ 加
- 减
* 乘
/ 除
扩展
如何测试计算
SELECT提供了测试和试验函数与计算的一个 很好的办法。虽然SELECT通常用来从表中检索数据,但可以 省略FROM子句以便简单地访问和处理表达式。例如,SELECT 3*2;将返回6,SELECT Trim('abc');将返回abc,而SELECT Now()利用Now()函数返回当前日期和时间。通过这些例子, 可以明白如何根据需要使用SELECT进行试验 。
4.小结
至此我们总结了计算字段以及如何创建计算字段。我们用例子说明了计 算字段在串拼接和算术计算的用途。此外,还学习了如何创建和使用别 名,以便应用程序能引用计算字段。