- 字符串
-- 常用函数串操作函数
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;
![Java String.format方法的简单使用[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)