MySQL使用数据处理函数和汇总数据.md

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

11.1 函数

多数SQL语句是可移植性的，但是函数的可移植性不强。几乎每种DBMS的实现都支持其他实现不支持的函数，有时差别很大。
如果坚持使用函数，应保证良好的代码注释。

11.2 使用函数

大多数SQL支持如下函数，
1. 用于处理文本串；
2. 用于数值计算；
3. 用于时间和日期的处理；
4. 返回DBMS正使用的特殊信息。

11.2.1 文本处理函数

利用Upper函数，

mysql> 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()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边
RTrim()	去掉穿 右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upeer()	将子串转化为大写

- Soundex()是一个将任一文本转化为语音表示的字母数字模式的算法。例子，

mysql> SELECT cust_name, cust_contact FROM customers WHERE cust_contact = 'Y. Lie';
Empty set (0.00 sec)

mysql> SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y. Lie');
+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+

11.2.2 日期和时间处理函数

$~~~~$日期和时间采用相应的数据类型和特殊的格式存储，以便快速访问和有效排序或过滤。常用时间处理函数，

函数	说明
AddDate()	增加一个日期
AddTime()	增加一个时间
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回特定格式日期或时间串
Day()	返回日期
DayOfWeek()	返回星期几

1. 不管插入或更新值，还是使用WHERE进行过滤，日期必须使用yyyy-mm-dd格式。
2. 若存储格式为datetime，直接使用2005-09-01会匹配失败，需要使用如下操作，

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

3.若想调用日期间订单，使用BETWEEN``AND函数。

11.2.3 数值处理函数

常用数值处理函数，

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个数的余弦
Exp()	返回一个数的指数值
Mod()	返回 除余 操作的余数
Pi()	返回圆周率
Rand()	返回随机数
Sin()	返回角度正弦
Sqrt()	返回平方根
Tan()	返回正切

第十二章 汇总数据

12.1 聚集函数

$~~~~$我们经常汇总数据而不用他们实际检索。检索例子有，
1. 确定表中行数；
2. 确定表中行组的和；
3. 找出最值。
- 聚集函数，运行在行组上，计算和返回单个值的函数，聚集函数如下，

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

12.1.1 AVG()函数

平均值计算函数，例子

mysql> SELECT AVG(prod_price) AS avg_price FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+

AVG()也可以用来确定特定列或行的平均值。

mysql> SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+

• 注意， 只能用于单个列。为获取多列各自的平均值，必须使用多个AVG()函数。
• AVG()忽略NULL值。

12.1.2 COUNT()函数

COUNT()函数用来计数。使用方式有两种，
1. 使用COUNT(*)对列中数目进行计数，同时包含NULL行；
2. 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

mysql> SELECT COUNT(*) as num_cust FROM customers;
+----------+
| num_cust |
+----------+
|        8 |
+----------+

mysql> SELECT COUNT(cust_email) as num_cust FROM customers;
+----------+
| num_cust |
+----------+
|        3 |
+----------+

12.1.3 MAX()函数

$~~~~$虽然MAX()一般用于找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，返回文本列中的最大值。如果用于文本数据时，如果数据安装相应的列排序，则MAX()返回最后一行。
- 注意，MAX()函数忽略列值为NULL的行。

同样适用于，MIN()函数。

12.1.4 SUM()函数

SUM()函数返回，指定列值的和。

mysql> SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+

同样，也可以用来计算合计值。

mysql> SELECT SUM(quantity*item_price) AS total_price FROM orderitems WHERE order_num = 200005;
+-------------+
| total_price |
+-------------+
|      149.87 |
+-------------+

• 注意，SUM()函数忽略列值为NULL的行。

12.2 聚集不同值