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);