（八）MySQL数据库-常用函数

我的系统版本为CentOS7.5，MySQL版本为5.7.26

数学函数

使用格式：一般用于插入、修改语句中，直接 函数（参数） 即可，把返回结果用于插入、修改。
可以使用select 函数名进行验证函数的作用

ABS求绝对值

返回 x 的绝对值

ABS(x)

RAND随机数

返回0到1的随机数

select rand()

ROUND四舍五入

得到X的Y位四舍五入小数，如果没有Y，代表四舍五入求整

ROUND(X,Y)

LOG对数运算

得到以x为底，y的对数

LOG(x，y)

SQRT求平方根

得到x的平方根

SQRT(x)

MOD求余

x对y求余

MOD(x,y)

CEIL、CEILING向上取整

向上取整

CEIL(x)
CEILING(x)

FLOOR向下取整

向下取整

FLOOR(x)

SIGN获取正负

返回x的符号，-1为负数，0不变，1为正数

SIGN(x)

POW、POWER幂运算

幂运算，求x的y次方幂

POW(x,y)
POWER(x,y)

BIN求二进制

BIN(N)：返回N的二进制值

CONV进制转换

不同进制的转换。返回str字符串由from_base进制转化为 to_base 进制的数字串表示

CONV(15,10,2)

聚集函数

使用格式：聚集函数一般是配合GROUP BY语句使用的，也可以用于统计整表、整列

AVG平均数

返回某列的平均值

AVG() 

COUNT统计行数

返回某列/某组/整表的行数（即记录数）

COUNT() 

MAX最大值

返回某列的最大值

MAX() 

MIN最小值

返回某列的最小值

MIN() 

SUM求和

返回某个列之和

SUM()

字符串函数

字符串函数的使用格式：select 函数(参数) from 表

CONCAT参数连接为字符串

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL

SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook")

CONCAT_WS参数连接为字符串（加分隔符）

同 CONCAT() 函数，但是每个字符串直接要加上 x，x 可以是分隔符

SELECT CONCAT_WS("-" , "SQL ", "Runoob ", "Gooogle ", "Facebook")

ASCII 返回第一位对应的ASCII值

返回字符串str 的最左字符的十进制数值。假如str为空字符串，则返回值为 0 。假如str 为NULL，则返回值为 NULL。 ASCII()用于带有从 0到255的数值的字符。

ASCII("str")

CHAR_LENGTH、LENGTH字符长度

返回值为字符串str 的长度，长度的单位为字符

CHAR_LENGTH(str)
LENGTH(str)

ELT区第N个参数

返回第一个参数后面的第N个参数。若N = 1，则返回值为 str1 ，若N = 2，则返回值为 str2 ，以此类推

 ELT(N,str1,str2,str3)

FIELD查找列表中字符位置

返回str1, str2, str3,……列表中的str 的位置。在找不到str 的情况下，返回值为 0

SELECT FIELD("c", "a", "b", "c", "d", "e");

FIND_IN_SET查找字符串列表中字符位置

返回在字符串s2中与s1匹配的字符串的位置

SELECT FIND_IN_SET("c", "a,b,c,d,e");

INSTR查找字符串中字符串位置

返回字符串 str 中子字符串substr的第一个出现位置

SELECT INSTR("abcdec", "c");

POSITION查找字符串中字符串位置

返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0，与INSTR一样，只是用法不同

SELECT POSITION("c" IN "abcdec");

LOCATE查找字符串中字符串位置（指定位置）

返回子串substr在字符串str第一个出现的位置，从位置pos开始。如果substr不是在str里面，返回0

SELECT LOCATE("c", "abcdec",4);

FORMAT数字格式化

将number X设置为格式 ‘#,###,###.##’, 以四舍五入的方式保留到小数点后D位, 而返回结果为一个字符串。

SELECT FORMAT(250500.5634, 2);     -- 输出 250,500.56

INSERT按位置替换字符串

字符串 str的pos到len长的位置被newstr替换

SELECT INSERT("google.com", 1, 6, "runnob");  -- 输出：runoob.com

REPLACE按字符串替换字符串

把str中的from_str内容替换为to_str

REPLACE(str,from_str,to_str)

LEFT 取左侧字符

返回字符串 s 的前 n 个字符

SELECT LEFT("abcdec",4);

RIGHT取左侧字符

返回字符串 s 的后 n 个字符

SELECT RIGHT("abcdec",4);

SUBSTRING、SUBSTRING第N位开始截取字符串

字符串str返回一个子字符串，起始于位置 pos

SUBSTRING(str,pos)
SUBSTRING(str FROM pos)

LOWER转化为小写

把str全部变为小写

LOWER(str)

UPPER转化为大写

把str转为大写

UPPER(str)

REPEAT将字符串重复N次

str重复count次而成的新字符串

 REPEAT(str,count)

REVERSE字符串倒序

把str倒序

REVERSE(str)

RPAD填充到指定长度

把str用padstr填充到len长

RPAD(str,len,padstr)

TRIM剔除指定字符串，去空格

从str中删除remstr

TRIM(remstr FROM str)

剔除str前后的空格

TRIM(str)

日期和时间函数

NOW语句开始执行的时间

NOW():返回该条语句【注意，是该条语句，而不是该函数。区别在于下面的sysdate（）函数】运行时的具体日期时间

select NOW();    ----2016-09-12 21:25:06

sysdate 动态获取时间

日期时间函数跟 now() 类似，不同之处在于：now() 在语句执行开始时值就得到了， sysdate() 在函数执行时动态得到值。

mysql> select now(), sleep(3), now();
+---------------------+----------+---------------------+
| now()               | sleep(3) | now()               |
+---------------------+----------+---------------------+
| 2019-05-03 12:03:49 |        0 | 2019-05-03 12:03:49 |
+---------------------+----------+---------------------+

mysql> select sysdate(), sleep(3), sysdate();
+---------------------+----------+---------------------+
| sysdate()           | sleep(3) | sysdate()           |
+---------------------+----------+---------------------+
| 2019-05-03 12:04:17 |        0 | 2019-05-03 12:04:20 |
+---------------------+----------+---------------------+

可以看到，虽然中途 sleep 3 秒，但 now() 函数两次的时间值是相同的； sysdate() 函数两次得到的时间值相差 3 秒

curdate获取日期

获取当前日期

curdate()

curtime 获取时间

获得当前时间（time）函数

curtime()

EXTRACT时间选取函数

选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒
EXTRACT(type FROM d)

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

WEEKDAY星期索引

返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)

select WEEKDAY(now());

DAYNAME星期名

返回date的星期名字，比如：Friday

select DAYNAME(now());

DAYOFMONTH 返回日

返回date的月份中日期，在1到31范围内

select DAYOFMONTH(now());

DAYOFYEAR一年中的第几天

返回date在一年中的日数, 在1到366范围内

select DAYOFYEAR(now());

MONTH月份索引

返回date的月份，范围1到12

select MONTH(now());

MONTHNAME月份名

返回date的月份名字

select MONTHNAME(now());

QUARTER季度

返回date一年中的季度，范围1到4

select QUARTER(now());

LAST_DAY 某月最后一天

某月最后一天

select LAST_DAY('2019-5-3');
select LAST_DAY(now());

ADDDATE N天后的时间

计算起始日期 d 加上 n 天的日期

select ADDDATE(now(),5);

ADDTIME N秒后的时间

时间 t 加上 n 秒的时间

select ADDTIME(now(),500);

DATE_ADD 指定时间后的时间

计算起始日期 d 加上一个时间段后的时间

select ADDDATE(now(),1);        (默认是天)

select ADDDATE(now(), INTERVAL 5 MINUTE);   (TYPE的取值与EXTRACT时间选取函数类似)

SUBDATE N天前的时间

计算起始日期 d 减去 n 天的日期

select SUBDATE(now(),5);

SUBTIME N秒前的时间

时间 t 减去 n 秒的时间

select SUBTIME(now(),500);

DATE_SUB 指定时间后的时间

计算起始日期 d 加上一个时间段后的时间

select DATE_SUB(now(),1);        (默认是天)

select DATE_SUB(now(), INTERVAL 5 MINUTE);   (TYPE的取值与EXTRACT时间选取函数类似)

DATEDIFF日期间隔

计算日期 d1->d2 之间相隔的天数

select DATEDIFF('2019-5-6',now());

DATE_FORMAT格式化

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
语法：
DATE_FORMAT(date,format)

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')    ----Dec 29 2008 11:45 PM
DATE_FORMAT(NOW(),'%m-%d-%Y')    ----12-29-2008
DATE_FORMAT(NOW(),'%d %b %y')    ----29 Dec 08
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')    ----29 Dec 2008 16:25:46.635

加密解密函数

ENCODE加密

加密解密字符串。该函数有两个参数：被加密或解密的字符串和作为加密或解密基础的密钥。Encode结果是一个二进制字符串，以BLOB类型存储，ENCODE加密是可逆的。

insert into b(id,name)values(10,ENCODE('super', 'abcdefg'));

DECODE解密

select decode(name,'abcdefg') from b where id=10;

MD5加密

计算字符串的MD5校验

select MD5('super');

类型转换函数

CAST() 和CONVERT() 函数可用来获取一个类型的值，并产生另一个类型的值。

语法：
CAST(xxx AS 类型)
CONVERT(xxx,类型)

二进制 : BINARY    
字符型,可带参数 : CHAR()     
日期 : DATE     
时间: TIME     
日期时间型 : DATETIME     
浮点数 : DECIMAL      
整数 : SIGNED     
无符号整数 : UNSIGNED 

#将123转换成时间
select CAST(123 AS time);

系统信息函数

使用格式：select 函数();

VERSION服务版本号

返回数据库版本号

VERSION()

CONNECTION_ID客户端标识ID

当前连接的客户端ID

CONNECTION_ID()

DATABASE、SCHEMA 当前数据库

返回当前数据库名

DATABASE()
SCHEMA()

USER、*_USER 当前用户

USER()、SYSTEM_USER()、SESSION_USER()，CURRENT_USER()、CURRENT_USER:返回当前用户名

select user();

LAST_INSERT_ID 最近的自增ID

返回最近生成的AUTO_INCREMENT值.

LAST_INSERT_ID()

更多详见：https://www.runoob.com/mysql/mysql-functions.html