MySQL - 函数与分组

MySQL 函数与分组

计算字段
数据处理函数
聚集函数
分组数据（GROUP BY）
过滤分组（HAVING）
SELECT 子句顺序

计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式，需要直接从数据库中检索出转换、计算或格式化后的数据。计算字段是运行时在 SELECT 语句内创建的。

拼接字段：将值联结到一起构成单个值。MySQL 使用 Concat() 函数拼接。

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

使用别名：拼接的结果是一个未命名的列，用 AS 关键字赋予别名。

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

执行算术计算：支持 +、-、*、/ 运算符。

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

数据处理函数

函数一般是在数据上执行的，给数据的转换和处理提供了方便。

文本处理函数：

函数	说明
`Left()`	返回串左边的字符
`Right()`	返回串右边的字符
`Length()`	返回串的长度
`Locate()`	找出串的一个子串
`Lower()`	将串转换为小写
`Upper()`	将串转换为大写
`LTrim()`	去掉串左边的空格
`RTrim()`	去掉串右边的空格
`Trim()`	去掉串两边的空格
`Soundex()`	返回串的 SOUNDEX 值（发音相似）
`Substring()`	返回子串的字符

-- 使用 Soundex 搜索发音相似的客户名
SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y Lie');

日期和时间处理函数：

函数	说明
`AddDate()`	增加一个日期（天、周等）
`AddTime()`	增加一个时间（时、分等）
`CurDate()`	返回当前日期
`CurTime()`	返回当前时间
`Date()`	返回日期时间的日期部分
`DateDiff()`	计算两个日期之差
`Date_Add()`	高度灵活的日期运算函数
`Date_Format()`	返回一个格式化的日期或时间串
`Day()`	返回一个日期的天数部分
`DayOfWeek()`	对于一个日期，返回对应的星期几
`Hour()`	返回一个时间的小时部分
`Minute()`	返回一个时间的分钟部分
`Month()`	返回一个日期的月份部分
`Now()`	返回当前日期和时间
`Second()`	返回一个时间的秒部分
`Time()`	返回一个日期时间的时间部分
`Year()`	返回一个日期的年份部分

-- 检索 2005 年 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;

MySQL 使用的日期格式必须是 yyyy-mm-dd。

数值处理函数：Abs()、Cos()、Exp()、Mod()、Pi()、Rand()、Sin()、Sqrt()、Tan() 等。

聚集函数

聚集函数运行在行组上，计算和返回单个值。

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

AVG()：只用于单个列，忽略列值为 NULL 的行。

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

COUNT()：使用 COUNT(*) 对表中行的数目计数（不管各列有什么值，包括 NULL）；使用 COUNT(column) 对特定列中具有值的行计数，忽略 NULL 值。

SELECT COUNT(*) AS num_cust FROM customers;
SELECT COUNT(cust_email) AS num_cust FROM customers;

MAX() / MIN()：返回指定列的最大/最小值，忽略 NULL 行。

SELECT MAX(prod_price) AS max_price FROM products;
SELECT MIN(prod_price) AS min_price FROM products;

SUM()：返回指定列值之和，可用来合计计算值。

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

聚集不同值：使用 DISTINCT 参数只包含不同的值。DISTINCT 不能用于 COUNT(*)，必须指定列名。

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

分组数据

使用 GROUP BY 子句和 HAVING 过滤分组，把聚集函数应用于一组数据。

创建分组：GROUP BY 按指定的列排序并分组。

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

GROUP BY 的重要规定：

可以包含任意数目的列
如果在 GROUP BY 中嵌套了分组，数据将在最后规定的分组上进行汇总
GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式（不能是聚集函数）
如果分组列中有 NULL 值，则 NULL 将作为一个分组返回
GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前

过滤分组：使用 HAVING 过滤分组，WHERE 过滤行，HAVING 过滤分组。

-- 列出具有两个以上产品且价格为 10 以上的供应商
SELECT vend_id, COUNT(*) AS num_prods FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

WHERE 在数据分组前过滤，HAVING 在数据分组后过滤。

分组和排序：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 BY ordertotal;

SELECT 子句顺序

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

MySQL - 函数与分组