[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-%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
文章1正文内容...
1
2
文章2
文章2正文内容...
1
3
文章3
文章3正文内容...
2
4
文章4
文章4正文内容...
4
user用户表:
uid
username
1
admin
admin@5idev.com
2
小明
xiao@163.com
3
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
文章1
admin
2
文章2
admin
3
文章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
文章1正文内容...
1
2
文章2
文章2正文内容...
1
3
文章3
文章3正文内容...
2
4
文章4
文章4正文内容...
4
user用户表:
uid
username
1
admin
admin@5idev.com
2
小明
xiao@163.com
3
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
文章1
admin
2
文章2
admin
3
文章3
小明
4
文章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
文章1正文内容...
1
2
文章2
文章2正文内容...
1
3
文章3
文章3正文内容...
2
4
文章4
文章4正文内容...
4
user用户表:
uid
username
1
admin
admin@5idev.com
2
小明
xiao@163.com
3
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
文章1
admin
2
文章2
admin
3
文章3
小明
NULL
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
文章1正文内容...
1
1
2
文章2
文章2正文内容...
1
2
3
文章3
文章3正文内容...
2
1
5
文章5
文章5正文内容...
4
1
user用户表:
uid
username
1
admin
admin@5idev.com
2
小明
xiao@163.com
3
Jack
jack@gmail.com
type文章类型表:
tid
typename
1
普通文章
2
精华文章
3
草稿
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
文章1
admin
普通文章
2
文章2
admin
精华文章
3
文章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
文章1
admin
普通文章
2
文章2
admin
精华文章
3
文章3
小明
普通文章
4
文章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
文章1
admin
普通文章
2
文章2
admin
精华文章
3
文章3
小明
普通文章
NULL
NULL
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
所有数字的舍入方向都接近于零
--------------------------------------
欢迎您,进入 我系程序猿 的cnBlog博客。
你不能改变你的过去,但你可以让你的未来变得更美好。一旦时间浪费了,生命就浪费了。
You cannot improve your past, but you can improve your future. Once time is wasted, life is wasted.
--------------------------------------