第十一章 使用数据处理函数
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
的行。