MySQL 常用函数

更新时间：2021-11-22 11:36:26 标签：mysql sql 数据库

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 允许有重复值。

<< ASCII 码 IT 互联网技术知识提问的智慧：如何正确提出技术问题 >>