[MySQL] 统计函数记录

2022-10-18 08:28:09

时间段统计==========

按年汇总，统计：
select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, ‘%Y‘);

按月汇总，统计：
select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, ‘%Y-%m‘);

按季度汇总，统计：
select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, ‘%Y‘),FLOOR((date_format(col, ‘%m‘)+2)/3));

select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, ‘%Y‘),FLOOR((date_format(col, ‘%m‘)+2)/3));

按小时：
select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by date_format(col, ‘%Y-%m-%d %H ‘);

查询本年度的数据:
SELECT * FROM mytable WHERE year(FROM_UNIXTIME(my_time)) = year(curdate())

查询数据附带季度数:
SELECT id, quarter(FROM_UNIXTIME(my_time)) FROM mytable;

查询本季度的数据:
SELECT * FROM mytable WHERE quarter(FROM_UNIXTIME(my_time)) = quarter(curdate());

本月统计:
select * from mytable where month(my_time1) = month(curdate()) and year(my_time2) = year(curdate())

本周统计:
select * from mytable where month(my_time1) = month(curdate()) and week(my_time2) = week(curdate())

N天内记录:
WHERE TO_DAYS(NOW())-TO_DAYS(时间字段)<=N

GROUP_CONCAT()函数==========

语法：
GROUP_CONCAT([DISTINCT] expr [,expr ...][ORDER BY {unsigned_integer | col_name | expr}[ASC | DESC] [,col_name ...]][SEPARATOR str_val])

下面演示一下这个函数，先建立一个学生选课表student_courses，并填充一些测试数据。
SQL代码
CREATE TABLE student_courses (
student_id INT UNSIGNED NOT NULL,
courses_id INT UNSIGNED NOT NULL,
KEY(student_id)
);
INSERT INTO student_courses VALUES (1, 1), (1, 2), (2, 3), (2, 4), (2, 5);

若要查找学生ID为2所选的课程，则使用下面这条SQL：
SQL代码
mysql> SELECT student_id, courses_id FROM student_courses WHERE student_id=2;
+------------+------------+
| student_id | courses_id |
+------------+------------+
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
+------------+------------+
3 rows IN SET (0.00 sec)

而如果采用GROUP_CONCAT()函数和GROUP BY语句就显得非常简单了，如下所示：
SQL代码
mysql> SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
+------------+---------+
| student_id | courses |
+------------+---------+
| 2 | 3,4,5 |
+------------+---------+
1 row IN SET (0.00 sec)

GROUP_CONCAT将某一字段的值按指定的字符进行累加，系统默认的分隔符是逗号，可以累加的字符长度为1024字节。可以对这些参数进行修改。

1.MySQL统计函数GROUP_CONCAT使用：先举一个简单的例子
select group_concat(f_a) from t_one group by f_b;
按f_b进行分组查询，将每组中的f_a进行累加。

2.修改默认的分隔符
select group_concat(f_a separator ‘_‘) from t_one group by f_b;
separator 是一个关键字，后面跟着要进行分隔的字符

3.排序
select group_concat(f_a order by f_a separator ‘_‘) from t_one group by f_b;

4.修改默认字符大小
1).在MySQL配置文件中加上
group_concat_max_len = 102400 #你要的最大长度

2).可以简单一点，执行语句,可以设置作用范围
SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;

5.MySQL统计函数GROUP_CONCAT使用：和concat使用
group_concat默认返回的是BLOB大对象，可以使用concat，返回字符串，还可以在返回的内容，在加入其它的数据。

按条件计数==========

问题描述
为使讨论简单易懂，我将问题稍作简化，去掉诸多的背景。

从前有一个皇帝，他有50个妃子，这些妃子很没有天理的给他生了100,000个儿子，于是，皇帝很苦恼，海量的儿子很难管理，而且，他想知道每个妃子给他生了多少个儿子，从而论功行赏，这很难办。于是，皇帝请了一个程序员帮他编了一个程序，用数据库来存储所有的儿子的信息，这样就可以用程序来统计和管理啦。

数据库的结构如下：
id 皇子的唯一编号
mother 皇子母亲的唯一编号
皇帝把妃子分成了两个等级，天宫娘娘(编号小于25)和地宫娘娘(编号大于等于25)，他想知道天宫娘娘们和地宫娘娘们的生育能力孰强孰弱。于是，程序员开始写SQL Query了。

方法1：使用GROUP BY
SQL Query
SELECT COUNT(*) FROM `prince` GROUP BY `mother` > 24;

执行结果
count(*)
50029
49971
在100,000行数据上的运行时间：0.0335 秒

分析
这种GROUP BY方法的最大问题在于：无法区分所得到的结果。这两个数字哪一个是天宫娘娘们所生的皇子数，哪一个是地宫娘娘们所生的皇子数呢？不知道。所以，尽管它统计出了总数，但是没有什么意义。
因此，为了区分统计结果，必须要把条件 mother > 24 也作为一个字段在结果集中作为一个字段体现出来，修改后的sql如下：

SELECT COUNT(*) AS `number`, `mother` > 24 AS `type` FROM `prince` GROUP BY `mother` > 24;

执行结果
number type
50029 0
49971 1

条件表达式作为字段时，该字段的值就是该条件表达式的值，因此，对应我们的例子，type = 1 也就是表示 mother > 24 的值为1，因此，第二行中的数字代表地宫娘娘们所生的皇子数。
经过修改后，我们看出，天宫娘娘们略胜一筹。

优缺点
缺点是显而易见的，由于使用了条件表达式作为分组依据，它只能做二元的划分，对于要分成多类进行统计的情况不能够胜任。比如要分别统计1~10号、11~24号，25号~50号妃子的产子数，就无法实现了。
另外，由于使用了GROUP BY，因此涉及到排序，执行时间上要更长。

我暂时没有发现这种方法的优点。

方法2：使用嵌套的SELECT
使用嵌套的SELECT也可以达到目的，在每个SELECT子句中统计一个条件下的数据，然后用一个主SELECT把这些统计数据整合起来。

SQL Query
SELECT
( SELECT COUNT( * ) FROM `prince` WHERE `mother` >24 ) AS `digong`,
( SELECT COUNT( * ) FROM `prince` WHERE `mother` <=24 ) AS `tiangong`

执行结果
digong tiangong
49971 50029
在100,000行数据上的运行时间：0.0216 秒

分析
这种嵌套SELECT的方法非常直观，就是分别统计各个条件下的数值，最后进行汇总，通俗易懂，跟自然语言没啥区别了。

优缺点
优点就是直观，而且速度也比GROUP BY要快。虽然是3条SELECT语句，看起来比GROUP BY的方案多了2条语句，但是它不涉及到排序，这就节省了很多时间。
缺点可能就是语句稍多，对语句数量有洁癖的同学可能会比较不舒服。

方法3：使用CASE WHEN
CASE WHEN语句的功能很强大，可以定义灵活的查询条件，很适合进行分类统计。
SQL Query
SELECT
COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END ) AS `digong`,
COUNT( CASE WHEN `mother` <=24 THEN 1 ELSE NULL END ) AS `tiangong`
FROM prince

执行结果
digong tiangong
49971 50029
在100,000行数据上的运行时间：0.02365825 秒

分析

此方法的关键在于
COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END )
这里的COUNT和CASE WHEN联合使用，做到了分类计数。先使用CASE WHEN，当满足条件时，将字段值设置为 1，不满足条件时，将字段值设置为NULL，接着COUNT函数仅对非NULL字段进行计数，于是，问题解决。

优缺点
优点嘛，此方法也不涉及到排序，因此运行时间上与方法2相当，SELECT语句减少到了 1 条。
缺点就是语句比较长，对语句长度有洁癖的同学可能会比较不舒服。

总结
对于确定分类的按条件计数，可以尽量不用GROUP BY，从而避免排序动作，加速Query的执行。
如果需要根据某个字段的值进行分类，而该字段的值是可变的，比如皇帝要统计每一个妃子的产子数，而他可能不停的再娶很多妃子，这种情况下，使用方法2和方法3就不太灵光了，还是使用一个GROUP BY来得简单便捷。

Join连接详解==========

MySQL JOIN 语法概述

SQL（MySQL）JOIN用于根据两个或多个表中的字段之间的关系，从这些表中得到数据。

JOIN通常与ON关键字搭配使用，基本语法如下：

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

table1通常称为左表，table2称为右表。ON关键字用于设定匹配条件，用于限定在结果集合中想要哪些行。如果需要指定其他条件，后面可以加上 WHERE条件或者 LIMIT以限制记录返回数目等。

下面以最常见的两表连接来说明MySQLJOIN 的用法，关于多表JOIN请参见《MySQLJOIN 多表》。
MySQLJOIN 分类

JOIN按照功能大致分为如下三类：

INNERJOIN（内连接）：取得两个表中存在连接匹配关系的记录。

LEFTJOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。

RIGHTJOIN（右连接）：与LEFTJOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。

关于MySQLFULL JOIN 全连接

MySQL没有提供SQL标准中的FULLJOIN（全连接）：两个表记录都取出，而不管彼此是否有对应记录。要解决此问题，可以使用UNION关键字来合并LEFTJOIN 与RIGHTJOIN，达到模拟FULLJOIN 的目的。
MySQLINNER JOIN

INNERJOIN 用于取得两个表中存在连接匹配关系的记录。下面是两个原始数据表：

article文章表:

aid

title

content

uid

文章1

文章1正文内容...

文章2

文章2正文内容...

文章3

文章3正文内容...

文章4

文章4正文内容...

user用户表:

uid

username

admin

admin@5idev.com

小明

xiao@163.com

Jack

jack@gmail.com

article表中文章的所属用户是通过uid这个字段与user表关联起来的。通过观察数据不难发现，对于uid=3的用户，并没有发表任何文章；而文章中aid=4却无法在uid表中找到对应记录（可能是该用户被删除而其所属的文章却被保留了下来）。

我们列出所用文章与用户一一对应的数据。

SELECT... INNER JOIN ... ON 语句如下：

SELECT article.aid,article.title,user.username FROM article INNER JOIN user ON article.uid = user.uid

返回查询结果如下：

aid

title

username

文章1

admin

文章2

admin

文章3

小明

对于INNERJOIN，等同与下面的SQL语句：

SELECT article.aid,article.title,user.username FROM article,user WHERE article.uid = user.uid

CROSSJOIN

CROSSJOIN 即交叉连接，在不指定ON条件下：

SELECT article.aid,article.title,user.username FROM article CROSS JOIN user

得到的结果是被连接的两个数据表的乘积，即笛卡尔积。

实际上，在MySQL中（仅限于MySQL）CROSSJOIN 与INNERJOIN 的表现是一样的，在不指定ON条件得到的结果都是笛卡尔积，反之取得两个表完全匹配的结果。

INNERJOIN 与CROSSJOIN 可以省略INNER或CROSS关键字，因此下面的SQL效果是一样的：

... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2

MySQLLEFT JOIN 语法

SQL（MySQL）LEFTJOIN 会取得左表（table1）全部记录，即使右表（table2）并无对应匹配记录。LEFTJOIN 基本语法如下：

... FROM table1 LEFT JOIN table2 ON condition ...

MySQLLEFT JOIN 用法实例

下面是两个原始数据表：

article文章表:

aid

title

content

uid

文章1

文章1正文内容...

文章2

文章2正文内容...

文章3

文章3正文内容...

文章4

文章4正文内容...

user用户表:

uid

username

admin

admin@5idev.com

小明

xiao@163.com

Jack

jack@gmail.com

我们列出所有的文章及对应的所属用户，即使没有用户的文章也列出。

SELECT... LEFT JOIN ... ON 语句如下：

SELECT article.aid,article.title,user.username FROM article LEFT JOIN user ON article.uid = user.uid

返回查询结果如下：

aid

title

username

文章1

admin

文章2

admin

文章3

小明

文章4

NULL

可以看出来，与 INNERJOIN明显的区别是，左表记录被全部取出，即使右表无对应匹配记录。
提示

这里所谓记录被“全部”取出，是相对于INNERJOIN 的限制来说的。其实可以在上面的SQL语句后面加个WHERE条件或者LIMIT等关键字以同一般SQL语句一样对结果集做一个范围限制。
ISNULL

在上面的例子中，对于右表中没有对应匹配的数据记录，其所有的列都被置为NULL，因此要查询这部分记录（如在上面例子中体现为查找aid=4这类无对应用户的文章记录），可以附加ISNULL 条件：

SELECT article.aid,article.title,user.username FROM article LEFT JOIN user
ON article.uid = user.uid WHERE user.uid IS NULL

MySQLRIGHT JOIN 语法

SQL（MySQL）RIGHTJOIN 会取得右表（table2）全部记录，即使左表（table2）并无对应匹配记录。RIGHTJOIN 基本语法如下：

... FROM table1 RIGHT JOIN table2 ON condition ...

MySQLRIGHT JOIN 用法实例

下面是两个原始数据表：

article文章表:

aid

title

content

uid

文章1

文章1正文内容...

文章2

文章2正文内容...

文章3

文章3正文内容...

文章4

文章4正文内容...

user用户表:

uid

username

admin

admin@5idev.com

小明

xiao@163.com

Jack

jack@gmail.com

我们列出所有的用户，以及他们可能拥有的文章。

SELECT... RIGHT JOIN ... ON 语句如下：

SELECT article.aid,article.title,user.username FROM article RIGHT JOIN user ON article.uid = user.uid

返回查询结果如下：

aid

title

username

文章1

admin

文章2

admin

文章3

小明

NULL

Jack

对比 LEFTJOIN返回的查询结果，RIGHTJOIN 返回的结果与其刚好“相反”。
ISNULL

在上面的例子中，对于左表中没有对应匹配的数据记录，其所有的列都被置为NULL，因此要查询这部分记录（如在上面例子中体现为查找username=Jack这类无对应文章的所有用户），可以附加ISNULL 条件：

SELECT article.aid,article.title,user.username FROM article LEFT JOIN user
ON article.uid = user.uid WHERE article.aid IS NULL

MySQLJOIN 多表连接

除了常用的两个表连接之外，SQL（MySQL）JOIN语法还支持多表连接。多表连接基本语法如下：

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON condition INNER|LEFT|RIGHT JOIN table3 ON condition ...

JOIN多表连接实现了从多个表中获取相关数据，下面是三个原始数据表：

article文章表:

aid

title

content

uid

tid

文章1

文章1正文内容...

文章2

文章2正文内容...

文章3

文章3正文内容...

文章5

文章5正文内容...

user用户表:

uid

username

admin

admin@5idev.com

小明

xiao@163.com

Jack

jack@gmail.com

type文章类型表:

tid

typename

普通文章

精华文章

草稿
MySQLINNER JOIN 多表

我们使用 INNERJOIN列出三个表中都具有关联关系的数据：

SELECT article.aid,article.title,user.username,type.typename FROM article INNER JOIN user
ON article.uid=user.uid INNER JOIN type ON article.tid=type.tid

返回查询结果如下：

aid

title

username

typename

文章1

admin

普通文章

文章2

admin

精华文章

文章3

小明

普通文章
MySQLLEFT JOIN 多表

使用 LEFTJOIN三个表查询：

SELECT article.aid,article.title,user.username,type.typename FROM article LEFT JOIN user
ON article.uid=user.uid LEFT JOIN type ON article.tid=type.tid

返回查询结果如下：

aid

title

username

typename

文章1

admin

普通文章

文章2

admin

精华文章

文章3

小明

普通文章

文章4

NULL

普通文章
MySQLRIGHT JOIN 多表

使用 RIGHTJOIN三个表查询：

SELECT article.aid,article.title,user.username,type.typename FROM article RIGHT JOIN user
ON article.uid=user.uid RIGHT JOIN type ON article.tid=type.tid

返回查询结果如下：

aid

title

username

typename

文章1

admin

普通文章

文章2

admin

精华文章

文章3

小明

普通文章

NULL

草稿

可见，在RIGHTJOIN 右连接中，只是列出最后一个右连接表的所有数据。
说明

对于MySQL多表JOIN，还可以INNER、LEFT和RIGHT混用，其返回结果与各关键字顺序有关，感兴趣可自行测试。
MySQLSTRAIGHT_JOIN

STRAIGHT_JOIN是MySQL对标准SQL的扩展，用于在多表查询时指定表载入的顺序。在JOIN表连接中，同样可以指定表载入的顺序，本文只讲述STRAIGHT_JOIN在表连接JOIN中的应用。

MySQLSTRAIGHT_JOIN 语法如下：

... FROM table1 STRAIGHT_JOIN table2 ON condition ...

STRAIGHT_JOIN实际上与内连接INNERJOIN表现完全一致，不同的是使用了STRAIGHT_JOIN后，table1会先于table2载入。

提示

MySQL在执行INNERJOIN 的时候，会根据自己内部的优化规则来决定先载入table1还是table2，如果您确认MySQL载入表的顺序并不是最优化的时候，就可以使用STRAIGHT_JOIN以替代INNERJOIN。
MySQLSTRAIGHT_JOIN 例子

SELECT article.aid,article.title,user.username FROM article STRAIGHT_JOIN user ON article.uid=user.uid

注意：该SQL仅仅是STRAIGHT_JOIN使用示例，并不表示其合理性。

如果有更多表进行连接，那么使用STRAIGHT_JOIN后，其载入顺序就遵循从左往右的规则。最后，STRAIGHT_JOIN无法应用于LEFTJOIN 或RIGHTJOIN。
MySQLNATURAL JOIN

NATURALJOIN 也叫自然连接，实际是属于JOIN的一种。

MySQLNATURAL JOIN 语法如下：

... FROM table1 NATURAL JOIN table2 ...

使用NATURALJOIN 时，MySQL将表中具有相同名称的字段自动进行记录匹配，而这些同名字段类型可以不同。因此，NATURALJOIN 不用指定匹配条件。

NATURALJOIN 默认是同名字段完全匹配的INNERJOIN，也可以使用 LEFTJOIN或 RIGHTJOIN。一些例子如下：

SELECT article.aid,article.title,user.username FROM article NATURAL JOIN user
// LEFT
SELECT article.aid,article.title,user.username FROM article NATURAL LEFT JOIN user
// RIGHT
SELECT article.aid,article.title,user.username FROM article NATURAL RIGHT JOIN user

数值函数==========

可使用常见的算术操作符。注意就 -、 +和 *而言, 若两个参数均为正数，则其计算结果的精确度为 BIGINT (64比特)，若其中一个参数为无符号整数，而其它参数也是整数, 则结果为无符号整数。请参见12.8节，“Cast函数和操作符”。
+
加号:
mysql> SELECT 3+5;
        -> 8
-
减号:
mysql> SELECT 3-5;
        -> -2
-
一元减号。更换参数符号。
mysql> SELECT - 2;
        -> -2
注意：若该操作符同一个BIGINT同时使用，则返回值也是一个BIGINT。这意味着你应当尽量避免对可能产生–263的整数使用 –。
*
乘号:
mysql> SELECT 3*5;
        -> 15
mysql> SELECT 18014398509481984*18014398509481984.0;
        -> 324518553658426726783156020576256.0
mysql> SELECT 18014398509481984*18014398509481984;
        -> 0
最后一个表达式的结果是不正确的。原因是整数相乘的结果超过了BIGINT 计算的 64比特范围。 (见11.2节，“数值类型”.)
/
除号:
mysql> SELECT 3/5;
        -> 0.60
被零除的结果为 NULL：
mysql> SELECT 102/(1-1);
        -> NULL
只有当执行的语境中，其结果要被转化为一个整数时，除法才会和 BIGINT 算法一起使用。
DIV
整数除法。类似于 FLOOR()，然而使用BIGINT 算法也是可靠的。
mysql> SELECT 5 DIV 2;
        -> 2
12.4.2. 数学函数
若发生错误，所有数学函数会返回 NULL 。
ABS(X)
返回X 的绝对值。
mysql> SELECT ABS(2);
        -> 2
mysql> SELECT ABS(-32);
        -> 32
该函数支持使用BIGINT值。
ACOS(X)
返回X 反余弦, 即, 余弦是X的值。若X 不在-1到 1的范围之内，则返回 NULL 。
mysql> SELECT ACOS(1);
        -> 0
mysql> SELECT ACOS(1.0001);
        -> NULL
mysql> SELECT ACOS(0);
        -> 1.5707963267949
ASIN(X)
返回X 的反正弦，即，正弦为X 的值。若X 若X 不在-1到 1的范围之内，则返回 NULL 。
mysql> SELECT ASIN(0.2);
        -> 0.20135792079033
mysql> SELECT ASIN(‘foo‘);
+-------------+
| ASIN(‘foo‘) |
+-------------+
|          0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level | Code | Message                                |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: ‘foo‘ |
+---------+------+-----------------------------------------+
ATAN(X)
返回X 的反正切，即，正切为X 的值。
mysql> SELECT ATAN(2);
        -> 1.1071487177941
mysql> SELECT ATAN(-2);
        -> -1.1071487177941
ATAN(Y,X) , ATAN2(Y,X)
返回两个变量X 及Y的反正切。它类似于 Y 或 X的反正切计算, 除非两个参数的符号均用于确定结果所在象限。
mysql> SELECT ATAN(-2,2);
        -> -0.78539816339745
mysql> SELECT ATAN2(PI(),0);
        -> 1.5707963267949
CEILING(X) CEIL(X)
返回不小于X 的最小整数值。
mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEIL(-1.23);
        -> -1
这两个函数的意义相同。注意返回值会被转化为一个BIGINT。
COS(X)
返回X 的余弦，其中X在弧度上已知。
mysql> SELECT COS(PI());
        -> -1
COT(X)
返回X 的余切。
mysql> SELECT COT(12);
        -> -1.5726734063977
mysql> SELECT COT(0);
        -> NULL
CRC32(expr)
计算循环冗余码校验值并返回一个 32比特无符号值。若参数为NULL ，则结果为 NULL。该参数应为一个字符串，而且在不是字符串的情况下会被作为字符串处理（若有可能）。
mysql> SELECT CRC32(‘MySQL‘);
        -> 3259397556
mysql> SELECT CRC32(‘mysql‘);
        -> 2501908538
DEGREES(X)
返回参数 X, 该参数由弧度被转化为度。
mysql> SELECT DEGREES(PI());
        -> 180
mysql> SELECT DEGREES(PI() / 2);
        -> 90
EXP(X)
返回e的X乘方后的值(自然对数的底)。
mysql> SELECT EXP(2);
        -> 7.3890560989307
mysql> SELECT EXP(-2);
        -> 0.13533528323661
mysql> SELECT EXP(0);
        -> 1
FLOOR(X)
返回不大于X的最大整数值。
mysql> SELECT FLOOR(1.23);
        -> 1
mysql> SELECT FLOOR(-1.23);
        -> -2
注意，返回值会被转化为一个 BIGINT。
FORMAT(X,D)
将数字X 的格式写成‘#,###,###.##‘格式, 即保留小数点后 D位，而第D位的保留方式为四舍五入，然后将结果以字符串的形式返回。详见12.9.4节，“其他函数”。
LN(X)
返回X 的自然对数,即, X 相对于基数e 的对数。
mysql> SELECT LN(2);
        -> 0.69314718055995
mysql> SELECT LN(-2);
        -> NULL
这个函数同LOG(X)具有相同意义。
LOG(X) LOG(B,X)
若用一个参数调用，这个函数就会返回X 的自然对数。
mysql> SELECT LOG(2);
        -> 0.69314718055995
mysql> SELECT LOG(-2);
        -> NULL
若用两个参数进行调用，这个函数会返回X 对于任意基数B 的对数。
mysql> SELECT LOG(2,65536);
        -> 16
mysql> SELECT LOG(10,100);
        -> 2
LOG(B,X) 就相当于 LOG(X) / LOG(B)。
LOG2(X)
返回X 的基数为2的对数。
mysql> SELECT LOG2(65536);
        -> 16
mysql> SELECT LOG2(-100);
        -> NULL
对于查出存储一个数字需要多少个比特，LOG2()非常有效。这个函数相当于表达式 LOG(X) / LOG(2)。
LOG10(X)
返回X的基数为10的对数。
mysql> SELECT LOG10(2);
        -> 0.30102999566398
mysql> SELECT LOG10(100);
        -> 2
mysql> SELECT LOG10(-100);
        -> NULL
LOG10(X)相当于LOG(10,X)。
MOD(N,M) , N % M N MOD M
模操作。返回N 被 M除后的余数。
mysql> SELECT MOD(234, 10);
        -> 4
mysql> SELECT 253 % 7;
        -> 1
mysql> SELECT MOD(29,9);
        -> 2
mysql> SELECT 29 MOD 9;
        -> 2
这个函数支持使用BIGINT 值。
MOD() 对于带有小数部分的数值也起作用，它返回除法运算后的精确余数：
mysql> SELECT MOD(34.5,3);
        -> 1.5
PI()
返回 ? (pi)的值。默认的显示小数位数是7位,然而 MySQL内部会使用完全双精度值。
mysql> SELECT PI();
        -> 3.141593
mysql> SELECT PI()+0.000000000000000000;
        -> 3.141592653589793116
POW(X,Y) , POWER(X,Y)
返回X 的Y乘方的结果值。
mysql> SELECT POW(2,2);
        -> 4
mysql> SELECT POW(2,-2);
        -> 0.25
RADIANS(X)
返回由度转化为弧度的参数 X, (注意 ? 弧度等于180度）。
mysql> SELECT RADIANS(90);
        -> 1.5707963267949
RAND() RAND(N)
返回一个随机浮点值 v ，范围在 0 到1 之间 (即, 其范围为 0 ≤ v ≤ 1.0)。若已指定一个整数参数 N ，则它被用作种子值，用来产生重复序列。
mysql> SELECT RAND();
        -> 0.9233482386203
mysql> SELECT RAND(20);
        -> 0.15888261251047
mysql> SELECT RAND(20);
        -> 0.15888261251047
mysql> SELECT RAND();
        -> 0.63553050033332
mysql> SELECT RAND();
        -> 0.70100469486881
mysql> SELECT RAND(20);
        -> 0.15888261251047
若要在i ≤ R ≤ j 这个范围得到一个随机整数R ，需要用到表达式 FLOOR(i + RAND() * (j – i + 1))。例如，若要在7 到 12 的范围（包括7和12）内得到一个随机整数, 可使用以下语句：
SELECT FLOOR(7 + (RAND() * 6));
在ORDER BY语句中，不能使用一个带有RAND()值的列，原因是 ORDER BY 会计算列的多重时间。然而，可按照如下的随机顺序检索数据行：
mysql> SELECT * FROM tbl_name ORDER BY RAND();
ORDER BY RAND()同 LIMIT 的结合从一组列中选择随机样本很有用：
mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d
    -> ORDER BY RAND() LIMIT 1000;
注意，在WHERE语句中，WHERE每执行一次， RAND()就会被再计算一次。
RAND()的作用不是作为一个精确的随机发生器，而是一种用来发生在同样的 MySQL版本的平台之间的可移动ad hoc随机数的快速方式。
ROUND(X) ROUND(X,D)
返回参数X, 其值接近于最近似的整数。在有两个参数的情况下，返回 X ，其值保留到小数点后D位，而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D 位，可将 D 设为负值。
mysql> SELECT ROUND(-1.23);
        -> -1
mysql> SELECT ROUND(-1.58);
        -> -2
mysql> SELECT ROUND(1.58);
        -> 2
mysql> SELECT ROUND(1.298, 1);
        -> 1.3
mysql> SELECT ROUND(1.298, 0);
        -> 1
mysql> SELECT ROUND(23.298, -1);
        -> 20
返回值的类型同第一个自变量相同(假设它是一个整数、双精度数或小数)。这意味着对于一个整数参数,结果也是一个整数(无小数部分)。
当第一个参数是十进制常数时，对于准确值参数，ROUND() 使用精密数学题库：
对于准确值数字, ROUND() 使用“四舍五入” 或“舍入成最接近的数” 的规则:对于一个分数部分为 .5或大于 .5的值，正数则上舍入到邻近的整数值，负数则下舍入临近的整数值。(换言之, 其舍入的方向是数轴上远离零的方向）。对于一个分数部分小于.5 的值，正数则下舍入下一个整数值，负数则下舍入邻近的整数值，而正数则上舍入邻近的整数值。
对于近似值数字，其结果根据C 库而定。在很多系统中，这意味着 ROUND()的使用遵循“舍入成最接近的偶数”的规则：一个带有任何小数部分的值会被舍入成最接近的偶数整数。
以下举例说明舍入法对于精确值和近似值的不同之处：
mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          |            2 |
+------------+--------------+
详见第24章：精度数学。
SIGN(X)
返回参数作为-1、 0或1的符号，该符号取决于X 的值为负、零或正。
mysql> SELECT SIGN(-32);
        -> -1
mysql> SELECT SIGN(0);
        -> 0
mysql> SELECT SIGN(234);
        -> 1
SIN(X)
返回X 正弦，其中 X 在弧度中被给定。
mysql> SELECT SIN(PI());
        -> 1.2246063538224e-16
mysql> SELECT ROUND(SIN(PI()));
        -> 0
SQRT(X)
返回非负数X 的二次方根。
mysql> SELECT SQRT(4);
        -> 2
mysql> SELECT SQRT(20);
        -> 4.4721359549996
mysql> SELECT SQRT(-16);
        -> NULL
TAN(X)
返回X 的正切，其中X 在弧度中被给定。
mysql> SELECT TAN(PI());
        -> -1.2246063538224e-16
mysql> SELECT TAN(PI()+1);
        -> 1.5574077246549
TRUNCATE(X,D)
返回被舍去至小数点后D位的数字X。若D 的值为 0, 则结果不带有小数点或不带有小数部分。可以将D设为负数,若要截去(归零) X小数点左起第D位开始后面所有低位的值.
mysql> SELECT TRUNCATE(1.223,1);
        -> 1.2
mysql> SELECT TRUNCATE(1.999,1);
        -> 1.9
mysql> SELECT TRUNCATE(1.999,0);
        -> 1
mysql> SELECT TRUNCATE(-1.999,1);
        -> -1.9
mysql> SELECT TRUNCATE(122,-2);
      -> 100
mysql> SELECT TRUNCATE(10.28*100,0);
      -> 1028
所有数字的舍入方向都接近于零

[MySQL] 统计函数记录

码农公寓

时间段统计==========

按年汇总，统计：select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, ‘%Y‘);

相关文章

按年汇总，统计：
select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, ‘%Y‘);