MySQL 常用函数

MySQL 互联网上是最多使用的关系型数据库，本页面收集了常用函数表，方便在使用过程中快速查询使用。需要注意的是，MySql 语法对大小写不敏感，可使用大写或者小写。

下文中 x, y, z 代表字段名。

聚合计算

函数语法	功能说明
avg(x)	平均数
count(x)	个数，数量，即结果中的记录数。count(distinct x) 为去重后数量
sum(x)	总数，求和
max(x)	最大值，日期时间字段时为最近的
min(x)	最小值，日期时间字段时为最早的
distinct x	去重

数学统计

函数语法	功能说明
abs(x)	绝对值
round(x,y)	返回参数 x 的四舍五入（其实是银行家算法）的有 y 位小数的值
ceiling(x)	大于 x 的最小整数, -1.3 -> -2, 8.8 -> 9
floor(x)	小于 x 的最大整数（正数可取整）, -1.3 -> -2, 8.8 -> 8
exp(x)	e（自然对数的底）的 x 次方
truncate(x,y)	x 截断为 y 位小数, 直接舍去, (8.888,1) -> 9.8
greatest(x, y, x)	取集合中最大的值
least(x, y, x)	取集合中最小的值
ln(x)	x 的自然对数
log(x)	x 的以 y 为底的对数
mod(x,y)	x/y 的模（余数）
pi()	pi（圆周率）的值，固定数
rand()	返回０到１内的随机值, 可指定参数（种子）使其成固定值
sign(x)	x 的符号的值, 判断正负
sqrt(x)	x 的算术平方根

字符及类型处理

函数语法	功能说明
CAST()	CAST (expression AS data_type) ，把一个值转化为指定的数据类型
bin(x)	x的二进制（OCT返回八进制，HEX返回十六进制）
group_concat()	将分组中的字符串与各种选项进行连接
concat_ws('-','h',3,4)	用指定的连接符号连接成字符串
concat('a','b')	将内容连接成字符串
length()	以字节获取字符串的长度
char_length()	以字符获取字符串的长度
left('str', 2)	取字符左边指定长度的字符
right('str', 2)	取字符右边指定长度的字符
replace(str,old,new)	字符替换
substring('1234', 2)	取指定位置开始的字符
substring('1234' FROM -3 FOR 2)	取指定位置开始指定长度的字符
trim(str)	删除头尾空格
format(2.9055, 3)	格式化数字并指定位数
UCASE(str) / UPPER(str)	转大写
LCASE(str) / LOWER(str)	转小写
STRCMP(s1,s2)	比较两个字符串
REVERSE(str)	颠倒字符串str的结果
INSERT(str,x,y,instr)	从第 x 位置开始，y 个字符长的子串替换为字符串 instr
POSITION(substr,str)	子串 substr 在字符串 str 中第一次出现的位置

常用功能：

SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;
SELECT CAST('9.0' AS decimal)  -- 结果：9

逻辑判断

函数语法	功能说明
if	条件判断。
CASE	流程控制。
IFNULL(x,y)	如果 x 不是 NULL 则返回 x, 否则返回 y
NULLIF(x,y)	如果 x 等于 y，则返回 NULL ，否则返回 x

详细说明：

-- IF 语句
IF expression THEN
   statements;
ELSEIF elseif-expression THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF

-- CASE 表达式
CASE value
    WHEN c_1 THEN r_1
    WHEN c_2 THEN r_2
    ELSE r_0
END AS value_name

日期和时间

函数语法	功能说明
now()	当前日期和时间
CURDATE()	当前的日期。或 CURRENT_DATE()
CURTIME()	当前的时间。或 CURRENT_TIME()
DATEDIFF(d1,d2)	计算两个日期的差值，间隔几天
date_add()	将时间值添加到日期值。
DATE_SUB()	减去间隔时间
DAYOFWEEK(date)	一星期中的第几天(1~7)
DAYOFMONTH(date)	一个月的第几天(1~31)
DAYOFYEAR(date)	一年的第几天(1~366)
DAYNAME(date)	星期名，如 Tuesday
FROM_UNIXTIME(ts,fmt)	指定的fmt格式，格式化UNIX时间戳ts
YEAR(date)	年份(1000~9999)
weekofyear(date)	返回一个中的第几周
week(date)	返回一个中的第几周
QUARTER(date)	一年中的季度, 1~4
MONTH(date)	月份值，1~12
MONTHNAME(date)	月份名，如
DAY(d)	获取天，20190818 得 18
HOUR(time)	返回小时数字，0-23
MINUTE(time)	分钟值，0~59

常用功能：

-- 取当前系统的时间
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
-- 距今多少天
SELECT datediff('20200101', current_date());
-- 获取今日的天值
SELECT DAY(current_date());
-- 指定时间加1秒
SELECT DATE_ADD('2017-12-31 23:59:59', INTERVAL 1 SECOND) result;
-- 从周日开始新的一周，2019825 是周日，分别输出 34 和 33
select week(20190825) as week, 
       week(date_sub(20190825, interval 1 day)) as new_week;
--  返回两个日期值之间的相差月数， 返回 -24
SELECT PERIOD_DIFF(201702, 201902);

其他

WITH AS

WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。

-- 相当于建了 a、b 临时表
with
     a as (select * from scott.emp),
     b as (select * from scott.dept)

select * from a, b where a.deptno = b.deptno;

UNION

UNION 操作符合并的结果集，不允许重复值；
UNION ALL 允许有重复值。