讲解计算字段和函数的使用。

1. 创建计算字段

1.1 计算字段

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

  • 小区、户型和楼层存储在不同列中,而打印程序需要把它们作为一个恰当格式的字段检索出来。
  • 列数据是大小写混合的,程序需要将它们按大写打印。
  • 需要对某列的数据进行平均数计算。

实质上,字段(field)基本上与列(column)意思相同,不过数据库列一般称为列,而术语字段通常用在计算字段的连接上

1.2 拼接字段

举个例子:在Vendor表中,包含了制造商的名字和国家,但分属于不同的列,我们想把这两个信息拼接起来。所谓拼接(concatenate)将值联结到一起构成单个值

MySQL使用 Concat()函数来拼接两个列,而其他DBMS使用+||来实现,所以转换时要小心。

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

可以用RTrim()去掉右边的空格,LTrim()去掉左边的空格,Trim()去掉两边的空格。


别名(alias)是一个字段或值的替换名,别名用AS关键字赋予。通过别名,客户机能够方便的引用它。

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

1.3 算数计算

举个例子假设有一列表示数量,有一列表示单价,现在我要生成一列计算总价

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.24 sec)

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

2. 基本的数据处理函数

由于不同DBMS的函数不通用,所以在使用时必须做好注释,表明使用的目的。

2.1 文本处理函数

比如,全部将其转化为大写:

mysql> SELECT vend_name,Upper(vend_name)
    -> FROM vendors;
+----------------+------------------+
| vend_name      | Upper(vend_name) |
+----------------+------------------+
| Anvils R Us    | ANVILS R US      |
| LT Supplies    | LT SUPPLIES      |
| ACME           | ACME             |
| Furball Inc.   | FURBALL INC.     |
| Jet Set        | JET SET          |
| Jouets Et Ours | JOUETS ET OURS   |
+----------------+------------------+
6 rows in set (0.26 sec)

其中,Soundx()是发音相似匹配,用于校正错误。

2.2 日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储,以便能快速和 有效地排序或过滤,并且节省物理存储空间。 需要注意日期必须为格式yyyy-mm-dd

假设我们希望检索一个创建于2005-09-01的订单,但是在order_Date中存储的是2005-09-01 11:30:05,这样匹配就变得麻烦了,所以我们需要将年月日单独提取出来。

mysql> SELECT cust_id,order_num
    -> FROM orders
    -> WHERE Date(order_date)='2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.26 sec)

另一个例子:我们需要9月里所有的订单信息。

mysql> SELECT cust_id,order_num,order_date
    -> FROM orders
    -> WHERE Year(order_date)=2005 AND Month(order_date)=9;
+---------+-----------+---------------------+
| cust_id | order_num | order_date          |
+---------+-----------+---------------------+
|   10001 |     20005 | 2005-09-01 00:00:00 |
|   10003 |     20006 | 2005-09-12 00:00:00 |
|   10004 |     20007 | 2005-09-30 00:00:00 |
+---------+-----------+---------------------+
3 rows in set (0.24 sec)

2.3 数值处理函数

数一般主要用于代数、三角或几何运算

3. 数据汇总函数

3.1 聚集函数

我们经常需要汇总数据而不用把它们实际检索出来,为此MySQL提供了专门的函数。常见的有以下几种:

  • 确定表中行数(或者满足特定情况的行数)
  • 计算表中行组的和
  • 找出某列的最值平均值。

(1)AVG函数

AVG()可用来返回所有列的平均值,也可以用来返回特定列或行的平均值。

mysql> SELECT AVG(prod_price) AS avg_price
    -> FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.24 sec)

当然也可以指定条件:

mysql> SELECT AVG(prod_price) AS avg_price
    -> FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.24 sec)

(2)COUNT函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特 定条件的行的数目。

  • 使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空 值(NULL)还是非空值。
  • 使用COUNT(column)对特定列中具有值的行进行计数,忽略 NULL值

下面的例子返回customers表中客户的总数:

mysql> SELECT COUNT(*) AS num_customers
    -> FROM customers;
+---------------+
| num_customers |
+---------------+
|             5 |
+---------------+
1 row in set (0.24 sec)

下面的例子只对具有电子邮件地址的客户计数:

mysql> SELECT COUNT(cust_email) AS num_customers
    -> FROM customers;
+---------------+
| num_customers |
+---------------+
|             3 |
+---------------+
1 row in set (0.24 sec)

(3)MAX和MIN函数

MAX()返回指定列中的最大值。MAX()要求指定列名,如下所示:

mysql> SELECT MAX(prod_price) AS max_price
    -> FROM products;
+-----------+
| max_price |
+-----------+
|     55.00 |
+-----------+
1 row in set (0.24 sec)

(4)SUM函数

SUM()用来返回指定列值的和(总计)。

下面举一个例子,orderitems表包含订单中实际的物品,每个物品 有相应的数量(quantity)。可如下检索所订购物品的总数:

mysql> SELECT SUM(quantity) AS items_ordered
    -> FROM orderitems
    -> WHERE order_num=20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+
1 row in set (0.24 sec)

SUM()也可以用来合计计算值。

mysql> SELECT SUM(quantity*item_price) AS items_ordered
    -> FROM orderitems
    -> WHERE order_num =20005;
+---------------+
| items_ordered |
+---------------+
|        149.87 |
+---------------+
1 row in set (0.24 sec)

3.2 组合聚集

实际上SELECT 语句可根据需要包含多个聚集函数。请看下面的例子:

mysql> SELECT COUNT(*) AS num_items,
    -> MAX(prod_price) AS price_max,
    -> MIN(prod_price) AS price_min
    -> FROM products;
+-----------+-----------+-----------+
| num_items | price_max | price_min |
+-----------+-----------+-----------+
|        14 |     55.00 |      2.50 |
+-----------+-----------+-----------+
1 row in set (0.24 sec)