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);