函数
-
内置函数function
-
字符串
-- 常用函数串操作函数 set @str = 'hello中国'; select @str,length(@str),char_length(@str),character_length(@str),reverse(@str); select space(6),repeat('*',10),left(@str,2),mid(@str,2,3),right(@str,3); select rand(),round(1.5),round(1.2),floor(1.1),floor(1.9),ceil(1.1),ceil(1.9); select repeat('*',round(rand()*9+1)); select 5%3,mod(5,3); -- 字符串连接 select concat('hello','mysql','java'),concat_ws('--','hello','mysql','java'); -- 会计,法学,美术,计算机科学,计算机网络 select group_concat(distinct dept) from stu; -- 会计-法学-美术-计算机科学-计算机网络 select group_concat(distinct dept separator '-') from stu; select name,group_concat(id order by id desc separator '_') from t group by name; -- jack 7 -- andy 6_5_1 -- mary 4_2 select name,group_concat(id) from stu group by name; select name,group_concat(id order by id desc) from stu group by name; select name,group_concat(id order by id desc separator '_') from stu group by name; -- 字符串替换 hello-mysql select replace('hello-java','java','mysql') -- 检查字符串的位置 7 select position('java' in 'hello-java'); -- 3 select instr('hello','llo'); -- 0 select instr('hello','allo'); -- uuid随机字符串，加密 select uuid(),md5('1'),sha('1'),sha1('a') select lower('HELLO'),upper('hello'); -- 相当于字符串插入 helljavao select insert('hello',5,0,'java'); -- helljava select insert('hello',5,1,'java') ; -- 转换base64字符串 aGVsbG/kuK3lm70= hello中国 select to_base64(@str),from_base64('aGVsbG/kuK3lm70='); select substr('hello' from 3),substring('hello' from 3); select substring('hello',3); select substring('hello' from 3); select substring('hello',3,1); select substring('hello123456',3),substring('hello123456',-3) -- substring_index() select substring_index('www.mysql.com.cn','.',1); select substring_index('www.mysql.com.cn','.',-1); select substring_index('www.mysql.com.cn','.',-2); -- ip 转换函数 select inet_ntoa(978559132),inet_aton('58.83.160.156') select inet_aton('58.83.160.156'); -
日期、时间、时间戳相关函数
-- 日期相关函数 -- 日期 时间 select curdate(),current_date(),curtime(),current_time(); -- 日期时间 select now(),sysdate(),current_timestamp(); -- 时间戳 select unix_timestamp(),from_unixtime(0),from_unixtime(1622018477) -- 日期时间格式化 select date_format(curdate(),'%Y-%m-%d %H:%i:%s'); select date_format(now(),'%Y-%m-%d %H:%i:%s'); select from_unixtime(unix_timestamp(),'%Y年%m月%d日%H时%i分%s秒'); select date_format(curdate(),'%m月%d日'); select year('2018-2-16'),month(curdate()),day(curdate()),date(now()),time(now()); select hour(curtime()),minute(curtime()),second(curtime()); -- 日期计算 -- 本月第一天日期 最后一天日期 三天前的日期 5天后日期 select now(),date_add(now(),interval -3 day),date_add(now(),interval 5 day),date_add(now(),interval -100 hour ); -- 计算两个日期有多少天 now() - 2000-1-16 select to_days(curdate()) - to_days('2000-1-16'); -- 求指定日期的那个月最后一天日期 ,返回日期,没有时分秒 select last_day(curdate()),last_day('2000-2-4'),last_day(now()); select day(curdate()),dayname(curdate()),dayofmonth(curdate()),dayofweek(curdate()),dayofyear(curdate()); select concat_ws('-',year(curdate()),month(curdate()),1); -- 获取当月第一天日期 select date_add(curdate(),interval -day(curdate())+1 day); -- 获取当前月的总天数 select day(last_day('2021-2-3')); select date_add(curdate()-day(curdate())+1,interval -1 month); -- 获取下个月的第一天 -- date_diff select datediff(curdate(),'2018-3-15'); select timestampdiff(day,'2018-3-15',curdate()) select timestampdiff(year,'2018-3-15',curdate()) select timestampdiff(hour,'2021-5-25 08:30:50','2021-5-25 18:20:30') -
select user(),database(),version(),now(); select * from stu; -- 查看stu中有多少个专业 select count(distinct dept) from stu; select distinct dept from stu; -- 每个专业的人数 select dept,count(*) from stu group by dept; select distinct dept from stu order by dept desc; select group_concat(distinct dept order by dept desc) from stu; set @ss = '计算机网络,计算机科学,美术,法学,会计'; -- 计算机科学,美术,法学 select substring_index(substring_index(@ss,',',4),',',-3) select reverse(substring_index(reverse(substring_index(@ss,',',4)),',',3)); -- select substring_index(group_concat(distinct dept order by dept desc),',',1) from stu; SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',1); select name,group_concat(id) from ( select id,name from stu limit 10) ttt group by name; id name pid 1 电器 2,3 select group_concat(distinct name order by name desc) from stu; -- if() ifnull() select if(true,'yes','no'),ifnull(null,'hello'),ifnull('a','b'); select id,name,ifnull(gender,'保密'),if(gender is null,'保密',gender) from stu;
-
-
自定义函数
-- 自定义函数
delimiter $
create function pf(i int)
returns int
begin
return i * i;
end$
delimiter ;
-- 查看声明了哪些函数
show function status where Db = 'wxdb';
select `SPECIFIC_NAME`
from `INFORMATION_SCHEMA`.`ROUTINES`
where `ROUTINE_SCHEMA` = 'wxdb'
and ROUTINE_TYPE = 'FUNCTION';
-- 调用函数
select pf(19), pf(2), pf(6);
-- 声明函数 返回等级 >90优秀 >80良好 >60 及格 else 补考
delimiter $$
create function f_level(s int)
returns varchar(2)
begin
return if(s >= 90, '优秀', if(s > 80, '良好', if(s >= 60, '及格', '补考')));
end $$
delimiter ;
-- 删除定义的函数
drop function `f_level`;
-- 调用函数
select f_level(90);
-- 在一个表的查询中使用函数
select f_level(score) le, count(*), group_concat(concat_ws('-', id, name))
from stu
group by le;
-- 自定义函数实现 留言动态计算效果
/*
1 jack helloworld 2021-05-27 10:42:15
1 jack hellowrld
*/
select uuid(), length(uuid())
create table cf_guestbook
(
id char(36) not null,
name varchar(30) not null,
msg varchar(255) not null,
addtime datetime default now(),
primary key (id)
);
select *
from cf_guestbook;
insert cf_guestbook
values (uuid(), 'jack', 'helloworld', '2021-5-3 18:20:30'),
(uuid(), 'lisi', '你好呀', '2021-5-27 8:3:50'),
(uuid(), 'lisi', '在干什么？', now());
-- 声明定义函数
delimiter $
create function f_addtime(t datetime)
returns varchar(100)
begin
declare t1 int;
declare t2 int;
declare m varchar(30);
set t1 = unix_timestamp();
set t2 = unix_timestamp(t);
if t1 - t2 < 60 then
set m = '刚刚';
elseif t1 - t2 < 3600 then
set m = concat(round((t1 - t2) / 60), '分钟前');
elseif t1-t2 < (3600*24) then
set m = concat(round((t1 - t2) / 3600), '小时前');
else
set m = from_unixtime(t2);
end if;
return m;
end $
delimiter ;
drop function f_addtime;
select name, msg, f_addtime(addtime)
from cf_guestbook;
-- select unix_timestamp(),unix_timestamp(addtime) from cf_guestbook;
