mysql func_MySQL function

MySQL function

MySQL 拥有很多可用于计数和计算的内建函数

用法：

SELECT function(column_name) FROM table_name;

Aggregate Functions(合计函数)

Aggregate 函数的操作面向一系列的值，并返回一个单一的值

如果在 SELECT 语句的项目列表中的众多其它表达式中使用 SELECT 语句，则这个 SELECT 必须使用 GROUP BY 语句

GROUP BY

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组

语法：

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

示例：

table scores:

+------+---------+-------+

| name | object | score |

+------+---------+-------+

| Jack | Math | 90 |

| Jack | English | 80 |

| Tom | English | 100 |

| Tom | History | 90 |

| Lucy | Math | 85 |

| Lucy | English | 85 |

| Lucy | History | 90 |

+------+---------+-------+

SELECT name, AVG(score) FROM scores GROUP BY name;

执行结果：

+------+------------+

| name | avg(score) |

+------+------------+

| Jack | 85.0000 |

| Tom | 95.0000 |

| Lucy | 86.6667 |

+------+------------+

HAVING

由于 WHERE 无法与 Aggregate Functions 合用，需要使用 HAVING 来进行筛选

SELECT name, AVG(score) FROM scores GROUP BY name HAVING AVG(score) > 90;

执行结果：

+------+------------+

| name | avg(score) |

+------+------------+

| tom | 95.0000 |

+------+------------+

AVG()

返回平均值

SELECT AVG(column_name) FROM table_name;

COUNT()

返回满足条件的行数

注意： COUNT(column_name) 不会包含 NULL，COUNT(*) 会包含

SELECT COUNT(*) FROM table_name;

SELECT COUNT(*) FROM table_name WHERE condition;

SELECT COUNT(DISTINCT column_name) FROM table_name;

MAX() & MIN()

返回最大(小)值

SELECT MAX(column_name) FROM table_name;

SELECT MIN(column_name) FROM table_name;

SUM()

返回数值列的和

SELECT SUM(column_name) FROM table_name;

Scalar Functions(标量函数)

UCASE & LCASE

将字段转换为大写

SELECT UCASE(column_name) FROM table_name;

SELECT LCASE(column_name) FROM table_name;

MID()

截取字段

SELECT MID(column_name, start[, length]) FROM table_name;

start 为起始位置，从 1 开始，正数为正数、负数为倒数

length 为截取长度

超出范围不会报错，但是会截取不到字符

ROUND()

指定数值字段的小数位数

SELECT ROUND(column_name, decimals) FROM table_name;

NOW()

获取当前时间

SELECT NOW();

FORMAT()

格式化字段

SELECT FORMAT(123456.789, 2);