【学习05】MySQL常用函数及计算字段

一、计算字段

1.1、计算字段

  存储在数据库表中的数据一般不是应用程序所需要的格式。下面举 几个例子。

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

  存储在表中的数据都不是应用程序所需要的。 我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是 检索出数据，然后再在客户机应用程序或报告程序中重新格式化。 这就是计算字段发挥作用的所在了。与前面各章介绍过的列不同， 计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句 内创建的。

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

1.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()拼接串，即把多个串连接起来形成一个较长的串。 CONCAT()需要一个或多个指定的串，各个串之间用逗号分隔。 上面的SELECT语句连接以下4个元素：

• 存储在vend_name列中的名字；
• 包含一个空格和一个左圆括号的串；
• 存储在vend_country列中的国家；
• 包含一个右圆括号的串。

通过删除数据右侧多余的空格来整理数据，这可以 使用MySQL的RTRIM()函数来完成，如下所示：
输入：

SELECT CONCAT(RTRIM(vend_name),'(',vend_country,')')
FROM vendors
ORDER BY vend_name;

分析： RTRIM()函数去掉值右边的所有空格。通过使用RTRIM()，各个 列都进行了整理。

RTRIM函数 MySQL除了支持RTRIM()（正如刚才所见，它去掉 串右边的空格），还支持LTRIM()（去掉串左边的空格）以及TRIM()（去掉串左右两边的空格）。

使用别名

SQL支持列别名。别名（alias）是一个字段或值 的替换名。别名用AS关键字赋予。请看下面的SELECT语句：
输入：

SELECT CONCAT(RTRIM(vend_name),'(',vend_country,')') AS vend_title
FROM vendors
ORDER BY vend_name;

输出：

分析： SELECT语句本身与以前使用的相同，只不过这里的语句中计算 字段之后跟了文本AS vend_title。它指示SQL创建一个包含 指定计算的名为vend_title的计算字段。从输出中可以看到，结果与以 前的相同，但现在列名为vend_title，任何客户机应用都可以按名引用 这个列，就像它是一个实际的表列一样。

别名的其他用途 别名还有其他用途。常见的用途包括在实际 的表列名包含不符合规定的字符（如空格）时重新命名它，在 原来的名字含混或容易误解时扩充它，等等。

1.3、执行算术计算

  计算字段的另一常见用途是对检索出的数据进行算术计算。举一个 例子，orders表包含收到的所有订单，orderitems表包含每个订单中的 各项物品。下面的SQL语句检索订单号20005中的所有物品：
输入：

SELECT prod_id,quantity,item_price
FROM orderitems
WHERE order_num = 20005;

输出：

item_price列包含订单中每项物品的单价。如下汇总物品的价格（单 价乘以订购数量）：
输入：

SELECT prod_id,
	quantity,
	item_price,
	quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

输出：

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

二、使用数据处理函数

什么是函数，MySQL支持何种函数，以及如何使用这些函数。

2.1、函数

与其他大多数计算机语言一样，SQL支持利用函数来处理数据。函数 一般是在数据上执行的，它给数据的转换和处理提供了方便。

2.2、使用函数

大多数SQL实现支持以下类型的函数。

• 用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数。
• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算） 的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分（例如，返回 两个日期之差，检查日期有效性等）的日期和时间函数。
• 返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本 细节）的系统函数。

2.2.1、文本处理函数

UPPER()函数：
输入：

SELECT vend_name,UPPER(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

输出：

分析： 正如所见，UPPER()将文本转换为大写，因此本例子中每个供 应商都列出两次，第一次为vendors表中存储的值，第二次作 为列vend_name_upcase转换为大写。
表列出了某些常用的文本处理函数。

2.2.2、日期和时间处理函数

  日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和 有效地排序或过滤，并且节省物理存储空间。
  一般，应用程序不使用用来存储日期和时间的格式，因此日期和时 间函数总是被用来读取、统计和处理这些值。由于这个原因，日期和时 间函数在MySQL语言中具有重要的作用。
某些常用的日期和时间处理函数：

2.2.3、数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角 或几何运算，因此没有串或日期—时间处理函数的使用那么频繁。
出一些常用的数值处理函数：

三、汇总数据

什么是SQL的聚集函数以及如何利用它们汇总表的数据。

3.1、聚集函数

  我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提 供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分 析和报表生成。这种类型的检索例子有以下几种。

• 确定表中行数（或者满足某个条件或包含某个特定值的行数）。
• 获得表中行组的和。
• 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值。

上述例子都需要对表中数据（而不是实际数据本身）汇总。因此， 返回实际表数据是对时间和处理资源的一种浪费（更不用说带宽了）。重 复一遍，实际想要的是汇总信息。

聚集函数（aggregate function） 运行在行组上，计算和返回单 个值的函数。

下表是聚集函数：

3.1.1、AVG()函数

  AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均 值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平 均值。
  下面的例子使用AVG()返回products表中所有产品的平均价格：
输入：

SELECT AVG(prod_price) AS avg_price
FROM products;

输出：

分析： 此SELECT语句返回值avg_Price，它包含products表中所有 产品的平均价格。如第10章所述，avg_price是一个别名。
AVG()也可以用来确定特定列或行的平均值，使用where条件即可。

只用于单个列 AVG()只能用来确定特定数值列的平均值，而 且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。

NULL值 AVG()函数忽略列值为NULL的行。

3.1.2、COUNT()函数

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

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

下面的例子返回customers表中客户的总数：
输入：

SELECT COUNT(*) AS num_cust
FROM customers;

输出：

分析： 在此例子中，利用COUNT(*)对所有行计数，不管行中各列有 什么值。计数值在num_cust中返回。

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

SELECT COUNT(cust_email) AS num_cust
FROM customers;

输出：

分析： 这条SELECT语句使用COUNT(cust_email)对cust_email列 中有值的行进行计数。在此例子中，cust_email的计数为3（表 示5个客户中只有3个客户有电子邮件地址）。

NULL值 如果指定列名，则指定列的值为空的行被COUNT() 函数忽略，但如果COUNT()函数中用的是星号（*），则不忽 略。

12.1.3、MAX()函数

MAX()返回指定列中的最大值。MAX()要求指定列名，如下所示：
输入：

SELECT MAX(prod_price) AS max_price
FROM products;

输出：

分析： 这里，MAX()返回products表中最贵的物品的价格。

对非数值数据使用MAX() 虽然MAX()一般用来找出最大的 数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数 据按相应的列排序，则MAX()返回最后一行

NULL值 MAX()函数忽略列值为NULL的行。

MIN()函数与MAX()函数用法相似，不再赘述。

12.1.4、SUM()函数

SUM()用来返回指定列值的和（总计）。
下面举一个例子，orderitems表包含订单中实际的物品，每个物品 有相应的数量（quantity）。可如下检索所订购物品的总数（所有 quantity值之和）：
输入：

SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;

输出：

分析： 函数SUM(quantity)返回订单中所有物品数量之和，WHERE子 句保证只统计某个物品订单中的物品。
SUM()也可以用来合计计算值。只需合计每项物品的 item_price*quantity，便可得出总的订单金额：

NULL值 SUM()函数忽略列值为NULL的行。

3.2、聚集不同值

以上5个聚集函数都可以如下使用：

• 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认 行为）；
• 只包含不同的值，指定DISTINCT参数。

ALL为默认 ALL参数不需要指定，因为它是默认行为。如果 不指定DISTINCT，则假定为ALL。

  下面的例子使用AVG()函数返回特定供应商提供的产品的平均价格。 它与上面的SELECT语句相同，但使用了DISTINCT参数，因此平均值只 考虑各个不同的价格：
输入：

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

输出：

分析： 可以看到，在使用了DISTINCT后，此例子中的avg_price比 较高，因为有多个物品具有相同的较低价格。排除它们提升了 平均价格。

注意 如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT）， 否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
将DISTINCT用于MIN()和MAX() 虽然DISTINCT从技术上可 用于MIN()和MAX()，但这样做实际上没有价值。一个列中的 最小值和最大值不管是否包含不同值都是相同的。

3.3、组合聚集函数

  目前为止的所有聚集函数例子都只涉及单个函数。但实际上SELECT 语句可根据需要包含多个聚集函数。请看下面的例子：
输入：

SELECT COUNT(*) AS num_items,
	MIN(prod_price) AS price_min,
	MAX(prod_price) AS price_max,
	AVG(prod_price) AS price_avg
FROM products;

输出：

分析： 这里用单条SELECT语句执行了4个聚集计算，返回4个值 （products表中物品的数目，产品价格的最高、最低以及平均
值）。