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 聚集不同值