4.MySQL常用函数

4.1.数字

– 平方根

sqrt(a)

select sqrt(9);   # 3

– 绝对值

abs(a)

select abs(-9), abs(9);

– 取余 mod(a,b)

mod(a,n)

select mod(10, 3);  # 1

– 四舍五入

round(a) 取整
round(a,n) 保留小数点后n位

select round(10.345), round(10.345, 2);

– 只舍 floor ,只入 ceil

floor(a) 向下取整

ceil(a) 向上取整

select floor(10.345), ceil(10.345);

– 随机数[0.0, 1.0) rand()

rand() 随机生成 0~1.0 的浮点数

select rand(), rand(), rand();

– 随机整数[20, 30)

floor(rand() * 整数个数 + 从几开始)

-- 随机生成 20 ~ 29 
select floor(rand()*10 + 20);

4.2.字符串

– 空值转换

ifnull(字段名,替换的值)

select stu_name, ifnull(stu_info, '没有介绍')  from student;

if(判断, 默认, 字段)

select stu_name, if(stu_info is null , '~~没有介绍~~', stu_info) info from student;

– 字符串长度

char_length(字段名) :字符数量

length(字段名) : 字节数量, 一个中文字符占 3 个字节, 一个英文字符占1个字节

select stu_name, stu_info, char_length(stu_info), length(stu_info)  from student;

– 截取字段串

substr(字段名,从哪开始,截取多长)
substr(字段名,从哪开始) 默认到最后

select stu_name, stu_info, substr(stu_info, 2, 1), substr(stu_info, 2) from student;

– 从左右截取字符串

返回字符串中从左边/右边开始的指定的个数的字符

left( 字符串, 长度)

right( 字符串, 长度)

select 'abcdefg', left('abcdefg',3), right('abcdefg',3);

– 字符串拼接

concat(字符串1,字符串2...)

select stu_name, concat('姓名:', stu_name) from student;select stu_name from student where stu_name like concat('%', '小', '%');

– 字段信息拼接

group_concat(字段)

将多条记录字段信息用 , 拼接到一起

select group_concat(stu_name) names from student;
select group_concat(stu_name separator '-') names from student;
select group_concat(distinct stu_name order by stu_height ) names from student;

–返回字符数值

ascii( 字符串 )

返回字符表达式中最左侧字符的数值

select ascii('abc');

– 填充字符串

lpad(str,len,padstr)

rpad(str,len,padstr)

返回字符串str，左(右)填充用字符串padstr填补到len字符长度。 如果str为大于len长，返回值被缩短至len个字符(即，不能超过 len 长)。

select lpad('hi',4,'??'),lpad('hello',4,'??'), rpad('hi',4,'??');

– 替换 replace()

replace(字段, 原, 新)

update student
set stu_info = replace(stu_info, 'null', '空信息');

– 数据类型转换函数

select convert(91.2345,char(5)), 91.2345, '91.2345';

4.3.时间

– 当前日期 now()

select now(), sysdate(), curdate(), current_time();

– 把字符串转成日期

select str_to_date('2000-01-01 13:34:56','%Y-%m-%d %H:%i:%s');

– 日期转成字符串

date_format(date,'%Y-%m-%d %H:%i:%s')
%W 星期几

select date_format(now(),'%Y-%M-%D %H:%I:%S %W'), date_format(now(),'%y-%m-%d %h:%i:%s %w'), date_format(now(),'%Y年%m月%d日 %H:%i') ;select date_format(now(), '%Y') year;
select year(now()) year;
select date_format(now(), '%H') hours;
select hour(now()) hours;-- 从年份0开始的天数
select to_days(now());

– 调整时间

date_add(日期，INTERVAL 数量 类型) 增加时间

date_sub(日期，INTERVAL 数量 类型) 减少时间

select date_add(now(), INTERVAL 10 DAY);
select date_add('2013-01-18', interval '1,2' YEAR_MONTH);
select date_add('2013-02-18', interval -1 YEAR_MONTH);select date_sub(now(), INTERVAL 10 DAY);

– 时间差

timestampdiff(类型, 时间1, 时间2)

select timestampdiff(DAY, '2013-01-18', '2013-02-18' ), to_days('2013-01-18')-to_days('2013-02-18');
select timestampdiff(MONTH, '2012-01-18', '2013-02-18' );

生日 求年龄

select timestampdiff(YEAR, 生日, now() );

– 返回当前时间的时间戳

返回值：自’1970-01-01 00:00:00’的到当前时间的秒数差

select unix_timestamp();
select unix_timestamp('2018-12-05 01:10:00');

–随机时间

select DATE_SUB(now(), INTERVAL FLOOR(1 + (RAND() * 1000)) DAY)

4.4.匹配

– 准备

DROP TABLE IF EXISTS `set_test`;
CREATE TABLE `set_test` (`id` int(11) NOT NULL AUTO_INCREMENT,`msg` varchar(50) DEFAULT NULL,`info` varchar(200) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;INSERT INTO `set_test` VALUES ('1', '1', '刘首一');
INSERT INTO `set_test` VALUES ('2', '12', '王小二');
INSERT INTO `set_test` VALUES ('3', '3', '李小三');
INSERT INTO `set_test` VALUES ('4', '4', '赵小四');
INSERT INTO `set_test` VALUES ('5', '1,12', '刘首一,王小二');
INSERT INTO `set_test` VALUES ('6', '1,12,3', '刘首一,王小二,李小三');
INSERT INTO `set_test` VALUES ('7', '12,4', '王小二,赵小四');
INSERT INTO `set_test` VALUES ('8', '1,3,12,4', '刘首一,李小三,王小二,赵小四');
INSERT INTO `set_test` VALUES ('9', '1,3', '刘首一,李小三');

– find_in_set

-- 在 set_test 表中 msg 字段 包含 1的记录select id, msg, info
from set_test
where find_in_set('1', msg);

– locate

select id, msg, info
from set_test
where locate('1,12',msg);