[TOC]

本部分主要涉及到MySQL进行简单的四则计算，处理数值，文本和日期与时间的各种函数。常见的聚集函数求均值，最值等，最后是分组处理。

第十章创建计算字段

为什么要创建计算字段呢？通常来讲，存储在数据库中的数据往往都不是应用程序所需要的，比如说我们想要获得每个月的收益值，那么就不需要从数据库中获取一个月当中每一天的收益，而只需要对当月数据进行求和即可，这样就可以最大程度地减轻访问数据库的压力。

计算字段是运行时在SELECT语句内创建的。

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

10.1 拼接操作

例如：在vendors表中包含供应商名和位置信息，我们想要生成一个供应商的报表，需要在供应商的名字中按照name(location)这样的格式列出供应商的位置。我们需要一个拼接操作

在vendors表中有两个列，分别是vend_name和vend_country。

拼接(concatenate)将值连接到一起构成单个值
口令是Concat(在其他DBMS中，会用+或者||进行拼接)

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

+-----------------------------------------+
| Concat(vend_name,' (',vend_country,')') |
+-----------------------------------------+
| ACME (USA)                              |
| Anvils R Us (USA)                       |
| Furball Inc. (USA)                      |
| Jet Set (England)                       |
| Jouets Et Ours (France)                 |
| LT Supplies (USA)                       |
+-----------------------------------------+

RTrim是用来删除值右侧的所有空格（LTrim用来删除左侧所有的空格以及Trim删除两侧的空格）

SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),')') FROM vendors
ORDER BY vend_name;

+-------------------------------------------------------+
| Concat(RTrim(vend_name),' (',RTrim(vend_country),')') |
+-------------------------------------------------------+
| ACME (USA)                                            |
| Anvils R Us (USA)                                     |
| Furball Inc. (USA)                                    |
| Jet Set (England)                                     |
| Jouets Et Ours (France)                               |
| LT Supplies (USA)                                     |
+-------------------------------------------------------+

上述操作，我们只是简单地看一眼当然没有什么问题，新值只是被计算出来了而已，并没有被实际命名，我们自然也就不能引用，于是我们要给他一个别名。

SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;

+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+

别名的另一个作用就是当列名不符合规范或者名字不好时，可以通过别名达到重新命名的结果。

10.2 执行算术计算

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

+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| ANV01   |       10 |       5.99 |
| ANV02   |        3 |       9.99 |
| TNT2    |        5 |      10.00 |
| FB      |        1 |      10.00 |
+---------+----------+------------+

我们首先获取orderitems表中的订单数量，物品价格。此时我们想要知道订单的总价是多少，即数量乘以单价。

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 |
+---------+----------+------------+----------------+

我们通过*得到了一个新的计算字段。四则运算都可以，圆括号用来区分优先顺序。

第十一章使用数据处理函数

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

函数：function, 和其他程序语言一样，函数就是实现某个具体功能的代码块。

注意：函数的可移植性不好，所以往往在不同的DBMS之间差异很大，所以如果要使用函数的话们应该做好代码注释。

11.1 使用不同类型的函数

函数主要包括以下几种类型：

用于处理文本串，如删除或填充值，转化为大写或小写；
数值运算函数，例如绝对值，指数，对数之类的；
处理日期和时间值的函数；
返回DBMS的特殊信息，比如用户登录信息，版本信息等系统函数。

11.1.1 文本处理函数

上一节中，我们用到了RTrim和Concat函数，我们再来看几个,

将文本转化为大写；

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

+----------------+------------------+
| vend_name      | vend_name_upcase |
+----------------+------------------+
| ACME           | ACME             |
| Anvils R Us    | ANVILS R US      |
| Furball Inc.   | FURBALL INC.     |
| Jet Set        | JET SET          |
| Jouets Et Ours | JOUETS ET OURS   |
| LT Supplies    | LT SUPPLIES      |
+----------------+------------------+

Left(str,len): 返回串str左边长度为Len的字符串；(Right)

SELECT Left("abc",2); /* 返回字符串ab */

+---------------+
| Left("abc",2) |
+---------------+
| ab            |
+---------------+

Length: 返回串的长度；

Locate: 格式为LOCATE(substr,str), LOCATE(substr,str,pos)；

1 2	SELECT LOCATE('bar', 'foobarbar'); -> 4 -- 返回4，即第一次bar出现的位置

1 2	SELECT LOCATE('xbar', 'foobar'); -> 0 -- 返回0,是因为查不到

1 2	SELECT LOCATE('bar', 'foobarbar', 5); -> 7 -- 从第5个位置开始索引，找到第一个bar的起始位置

LTrim,RTrim,Upper,Lower.

Soundex: 返回串的SOUNDEX值，使能够对串进行发音比较而不是字母比较。

SELECT cust_name,cust_contact
FROM customers
WHERE cust_contact = 'Y. Lie';
				-> Empty set -- 返回空集合，不会返回数据，这是因为名字写错了，应该是Y Lee
				
SELECT cust_name,cust_contact
FROM customers
WHERE cust_contact = 'Y Lee'; -- 则会出现返回值

+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+

-- 为了避免这种情况，我们采用发音比较的方式
SELECT cust_name,cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y. Lie'); -- 此时返回Y Lee的记录，发音上比较相似

+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+

SubString: 格式为下述之一，起到取子串的作用。

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len),
SUBSTRING(str FROM pos FOR len)

11.1.2 日期和事件处理函数

日期和时间采用相应的数据类型和特殊的格式存储。一般地，应用程序不使用用来存储日期和时间的格式，因此日期和时间函数总是被用来读取，统计和处理这些值，由于这个原因，日期和时间函数在MySQL语言中具有重要的作用。

日期和时间函数有以下几类：

获取当前时间的信息，Now, CurDate(Current),CurTime;
获取某个日期的具体信息， Date，Time, Year, Month, Day, Hour, Minute, Second, DayOfWeek(星期几);
日期的添删运算AddDate, AddTime, DateDiff, Date_Add, Date_Format.

函数	用途	示例	输出
AddDate	增加日期（比给出日期变化多少）	SELECT ADDDATE(‘2008-01-02’, INTERVAL 31DAY); SELECT ADDDATE(‘2008-01-02’, 31);	2008-02-02
AddTime	增加时间
CurDate	返回当前日期	SELECT CurDate();	2020-04-03
CurTime	返回当前时间	SELECT CurTime();	15:54:33
Date	返回日期部分	SELECT Date(‘2020-04-03 15:54:33’);	2020-04-03
DateDiff	日期之差	SELECT DATEDIFF(‘2007-12-31 23:59:59’,’2007-12-30’);	1
Date_Add	灵活的日期运算函数(可以加减小时分钟)	SELECT DATE_ADD(‘2008-01-02’, INTERVAL 31 DAY);	2008-02-02
Date_Format	日期格式化	SELECT DATE_FORMAT(‘2009-10-04 22:23:00’, ‘%W %M %Y’);	‘Sunday October 2009’
Day	返回某个日期的天	SELECT Day(‘2020-04-03 15:54:33’);	3
DayOfWeek	返回某个日期是星期几	SELECT DAYOFWEEK(‘2020-04-03’);	6（星期天是第一天）
Hour	返回某个日期的小时	SELECT Hour(‘2020-04-03 15:54:33’);	15
Minute	返回某个日期的分钟	SELECT Minute(‘2020-04-03 15:54:33’);	54
Month	返回某个日期的月份	SELECT Month(‘2020-04-03 15:54:33’);	4
Now	返回当前日期和时间	SELECT now();	2020-04-03 15:54:33
Second	返回某个日期的秒	SELECT second(‘2020-04-03 15:54:33’);	33
Time	返回时间部分	SELECT Time(‘2020-04-03 15:54:33’);	15:54:33
Year	返回某个日期的年份	SELECT Year(‘2020-04-03 15:54:33’);	2020

我们看一些具体例子。

首选日期格式 yyyy-mm-dd;

给出完整的4位数字年份更加可靠；

SELECT cust_id,order_num
FROM orders
WHERE order_date = '2005-09-01';

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+

-- 我们看看整个表长成什么样子
SELECT * FROM orders;

+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20006 | 2005-09-12 00:00:00 |   10003 |
|     20007 | 2005-09-30 00:00:00 |   10004 |
|     20008 | 2005-10-03 00:00:00 |   10005 |
|     20009 | 2005-10-08 00:00:00 |   10001 |
+-----------+---------------------+---------+

我们可以发现order_date为2005-09-01虽然被找出来了，但是一旦有时间的信息，比如2005-09-01 11:30:05则匹配失败，我们也不可能记得如此精确地时间进行检索，所以通常我们会比较列中日期的一部分，为此，可以用Date函数进行日期的比较。

SELECT cust_id,order_num
FROM orders
Where Date(order_date) = '2005-09-01';

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+

另一种场景是当我们想要获得9月份所有的订单信息，可以比较年份和月份，也可以指定日期范围。

SELECT cust_id,order_num
From orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

-- 也可以如下，结果相同

SELECT cust_id,order_num
From orders
WHERE Year(order_date)=2005 AND Month(order_date)=9;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+

11.1.3 数值处理函数

常见的代数，三角函数，几何运算。

Abs, Cos, Exp, Mod, Pi, Rand, Sin, Sqrt, Tan估计看名字也就知道意思了。

SELECT Sin(Pi());

+------------------------+
| Sin(Pi())              |
+------------------------+
| 1.2246467991473532e-16 |
+------------------------+

第十二章汇总数据

本章介绍什么是SQL的聚集函数以及如何利用他们汇总表的数据。

12.1 聚集函数

我们通常需要汇总数据而不是直接将他们检索出来。有如下几个方面的例子，例如

确定表中的行数，例如9月份订单总数
确定行的和，比如9月份消费总金额
找出列中的最大值，最小值，平均值等

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

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

12.2 常见聚集函数的示例

12.2.1 AVG均值函数

SELECT AVG(prod_price) AS avg_price
FROM products;

-- prod_price这列所有行的平均值

+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+

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

-- prod_price这列，满足vend_id=1003的所有行的平均值

+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+

注意：

avg只能用于一列的计算，如果要计算多列，则需要多个avg语句；
avg会忽略值为NULL的行。

12.2.2 COUNT计数函数

返回表中行的数目，或者满足条件的行的数目。

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

SELECT COUNT(*) AS num_cust
FROM customers;

-- 表customers总共具有5行数据

+----------+
| num_cust |
+----------+
|        5 |
+----------+

SELECT COUNT(cust_email) AS num_cust
FROM customers;

-- 只有3行具有email地址的顾客，有两行email地址为NULL

+----------+
| num_cust |
+----------+
|        3 |
+----------+

12.2.3 MAX，MIN最值函数

返回指定列中的最大值,忽略NULL。

SELECT MAX(prod_price) AS max_price
FROM products;

+-----------+
| max_price |
+-----------+
|     55.00 |
+-----------+

12.2.4 SUM求和函数

用法类似于求均值，只是不用除以求和的总个数了。

12.3 聚集函数的其他用法

对以上5个聚集函数在使用时，

对所有的行执行计算，指定ALL参数或者不给参数（默认ALL）
只包含不同的值，指定DISTINCT参数

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

-- 上节中未加DISTINCT,均值为13.212857，现在加入DISTINCT,我们只算不同的值的平均数，二者有了差别。
+-----------+
| avg_price |
+-----------+
| 15.998000 |

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;

+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+

第十三章分组数据

本章介绍如何分组数据，以便能汇总表内容的子集，这涉及两个新SELECT语句子句，分别是GROUP BY和HAVING子句。

SELECT COUNT(*) AS num_prods
FROM products
WHERE vend_id =1003;

+-----------+
| num_prods |
+-----------+
|         7 |
+-----------+

-- 这里我们返回了供应商1003提供的商品数目，但是如果我们想知道每个供应商提供的商品数目怎么办？
-- 难道找出所有的vend_id, 然后逐个按照上面的代码查询吗？显然比较麻烦，这个时候就需要分组数据了。

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

-- 按照vend_id分组，然后对每组使用聚集函数

Group by 可以包含任意数目的列，这使得能对分组进行嵌套，实施更加精细的分组；
在建立分组时，Group by后面的所有都参与分组，只要有一个列取值不同，就可以作为一个分组；
Group by后面的列必须是有效的表达式，不能是聚集函数，若在select中使用表达式，则group by中使用相同的表达式，不能使用别名；
1
select vend_id, vend_id*prod_price as alias from products group by vend_id,alias;

除聚集语句外，在select后面的所有列都必须出现在group by中；

1 2	select vend_id from products group by prod_price; -- 错误 select vend_id from products group by vend_id; -- 成功

如果分组具有NULL值，则所有具有NULL值的行将作为一组；
Group by子句出现在WHERE之后，ORDER BY之前。

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;
-- 有with rollup的结果,对分组结果进行汇总，比如总共有14个num_prods,即列和为14
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
|    NULL |        14 |
+---------+-----------+
-- 没有with rollup的结果
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

13.1 过滤分组

假设我们想要知道在orders表中，每位顾客的订单数，只需要按顾客编号进行分组，然后用count计数即可。但如果我们的需求是找出买了至少两个东西的顾客呢，此时分组就不够了，我们还要对分组进行过滤，找出计数数不小于2的分组，此时用HAVING子句。

SELECT cust_id,COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
-- 以下输出结果表明只有编号为10001的顾客下了至少两个单
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+

13.1.1 HAVING与WHERE的差别

在之前章节中，HAVING完全可以替代WHERE.

SELECT cust_id FROM orders HAVING cust_id = 10001;
-- 此处和WHERE相同
+---------+
| cust_id |
+---------+
|   10001 |
|   10001 |
+---------+

但是，HAVING可以用在分组处理中，而where是对所有的行进行过滤。

另外一种理解方式，就是where首先对数据进行过滤，排除不需要的行，然后group by对剩下的行进行分组，最后是having进行分组过滤。来看如下例子：

SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price >=10
GROUP BY vend_id
HAVING count(*) >=2;

-- 用where过滤了，所以行数变少了，相应地能够达到超过两行的数目也就变少了

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+

SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING count(*) >=2;

-- 1002供应商也有两个产品，但是价格都低于10，因此在上个代码输出中不会出现1002

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

13.2 分组和排序

分组Group by是分组行，但输出不一定是什么顺序, 排序Order by是排序产生的输出。我们有时候在分组汇总之后，还要对分组的顺序加以限定，于是就出现了分组和排序的组合。

SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price)>=50;

+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
+-----------+------------+

SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price)>=50
ORDER BY ordertotal;
-- 指定按照ordertotal排序
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+

13.3 SELECT子句顺序

子句	说明	是否必须
SELECT	选择列或表达式	是
FROM	从表	仅在从表中选择数据时使用
WHERE	行过滤	否
GROUP BY	分组	仅在按组计算聚集时使用
HAVING	按组过滤	否
ORDER BY	对输出排序	否
LIMIT	限制检索行数	否

第十章 创建计算字段