mysql 常用内置函数
1. length() ,char_length()
select length ('hello 中国'),char_length('hello 中国');
2. concat() ,concat_ws();
select concat('a','b','c') ,concat_ws('_','a','b','c');
3.format()
select format(68,3);
4. upper(),lower();
select upper('MySQL'),lower('MySQL');
5. left(),right(),mid();
select left('mysql',2),right('mysql',2),mid('mysql',2,2);
6.insert()
select insert('hellodbms',5,0,' mysql ');
select insert('hellodbms',4,1,' mysql ');
7.position()
select position('hello' in 'mysqlhell');
select position('hello' in 'mysqlhello');
8.replace();
select replace('hello','ll','aa');
9. if() ,ifnull()
select if(true,'y','n'),ifnull(null,'yes');
10. uuid(),rand(),round(),round(rand(),2),pow(2,3);
select uuid(),rand(),round(rand()),round(rand(),2),pow(2,3);
11. inet_ntoa(978559132),inet_aton(‘58.83.160.156’);
数字转ip , ip转数字
12 distinct.
-- 90,70,50,30,0,0,0
select group_concat(score order by score desc) from st;
-- 90,70,50,30,0
select group_concat(distinct score order by score desc) from st;
13.repeat()
select repeat('*',round(rand()*30)),space(6); space 6个空格
14.lpad() ,rpad()
select lpad('hello',8,'#'),rpad('java',2,'*');
15.reverse()
select reverse('hello-mysql');
lqsym-olleh
16.truncate(1.6666,2),round(1.6666,3),round(1.6666);
select truncate(1.6666,2),round(1.6666,3),round(1.6666);
17. cast(1.6466 as decimal(3,1))
(如果没有看懂就敲几遍 试验)
select cast(1.6466 as decimal(3,1));
第一个参数3 是指得一共是多少为(1.666) 第二个参数1 是保留 位小数
18.select cast(‘1.666’ as double),convert(1.6666,SIGNED);
select cast('1.666' as double),convert(1.6666,SIGNED);
19. sysdate(),now(),current_timestamp,current_timestamp();
select sysdate(),now(),current_timestamp,current_timestamp();
20. current_date(),curdate(),date(now());
select current_date(),curdate(),date(now());
21. current_time(),curtime(),time(now());
select current_time(),curtime(),time(now());
22. adddate(),adddate()
select adddate(curdate(),10),adddate('1985-6-18',-20);
第一个是10天后的日期 第二个是 20天前
23. adddate(now(),interval -20 day);
select adddate(now(),interval -20 day);
20天前
24. date_add(curdate(),interval -day(curdate())+1 day);
获取当前月的第一天
select date_add(curdate(),interval -day(curdate())+1 day);
date_add和addtate一样
25. last_day(curdate());
当前月的最后一月
select last_day(curdate());
26. abs(to_days(‘2020-10-20’) - to_days(curdate()));
两个日期之间的天数
select abs(to_days('2020-10-20') - to_days(curdate()));
select datediff(curdate(),'2020-10-22');
之间的天数
select timestampdiff(day,'2020-10-22',curdate());
之间的分钟数
select timestampdiff(minute,'2020-10-30 14:01:15',now());
27. unix_timestamp(),unix_timestamp(now()),unix_timestamp(‘2020-10-20 08:10:30’);
时间戳
select unix_timestamp(),unix_timestamp(now()),unix_timestamp('2020-10-20 08:10:30');
select unix_timestamp(curdate());
28. date_format()
select date_format(from_unixtime(1603987200),'%Y年%m月%d日 %H:%i:%s');
from_unixtime(1603987200) 将时间戳转化为时间
select from_unixtime(1603987200,'%m月%d日') 日期;
29.dayname()
select dayname(adddate(now(),-2)),
dayname(adddate(now(),-1)),
dayname(now()) 今日,
dayname(adddate(now(),1));
30. monthname(curdate()),month(curdate()),year(now()),day(now());
当前月份 当前年 今天几号
select monthname(curdate()),month(curdate()),year(now()),day(now());
31.hour(now()),minute(now()),second(curtime());
同上
select hour(now()),minute(now()),second(curtime());
32.week(curdate()),weekday(curdate());
一年中的第几个周 一月中的第几周
select week(curdate()),weekday(curdate());
33. date_format(curdate(),’%y %Y’);
select date_format(curdate(),'%y %Y');