mysql 字符串处理

`#连接
SELECT concat(‘mysql’,‘8.0’)
SELECT concat_ws(’-’,‘first’,‘second’,‘third’)
#替换
SELECT INSERT(‘Quest’,2,4,‘What’) as coll
select lower (‘BEAUTIFUL’)
select upper(‘baeutiful’)
select left(‘football’,5)
select right(‘football’,5)
select LPAD(‘hello’,4,’??’)#缩短或者左边开始填充至位数
select LPAD(‘hello’,10,’??’)#依然从左边缩短，从右侧填充
select concat(’(’,LTRIM(’ book ‘),’)’)#删除字符串左侧空格，RTRIM删右侧,TRIM两侧都删，但中间空余删不了
select TRIM(‘XY’ FROM ‘XYSDHISOHX’)#删除指定字符串
select repeat(‘mysql’,3)
select concat(’(’,space(6),’)’)
#替换
SELECT INSERT(‘Quest’,2,4,‘What’) as coll
select replace(‘xxx.mysql.com’,‘x’,‘w’)
#比较字符串长度,前者小于-1，等于0，大于1
select STRCMP(‘TCT’,‘TCT2’)
#截取字符串
select substring(‘breakfast’,5),substring(‘breakfast’,5,3),subsrting(‘lunch’,-3)#负号就是表示从右边,substring与mid同样
#返回字符串在字符串中开始的位置
select locate(‘foot’,‘football’)
select postion(‘ball’ in ‘football’ )
select instr(‘football’,‘ball’)
#将字符串反转
select reverse (‘abc’)
#返回指定位置的string
select elt(3,‘1st’,‘2nd’,‘3rd’)
#返回指定字符串位置
select field(‘hi’,‘hihi’,‘hi’)
#返回,号 字符串列表中的位置
select find_in_set(‘hi’,‘hello,hi,how,are,you’)
#根据二进制选取 字符串
select make_set(1|4,‘hello’,‘nice’,‘world’)#1与4或操作后，是第一与第三个位置有1
select make_set(1|4,‘hello’,‘nice’,NULL,‘world’)
#日期
select CURDATE(),curdate()+0#日期型与数值型
select CURTIME(),CURTIME()+0#时间
select current_timestamp(),now(),sysdate()
select month(‘2020/08/09’)
select year(‘2020/08/09’)
select quarter(‘2020/08/09’)
select monthname(‘2020/08/09’)
select dayname(‘2020/08/09’)#名字第几周
select dayofweek(‘2020/08/09’)#周日是一周中第一天
select week(‘2020/08/09’)#一年中第几周
select dayofyear(‘2020/08/09’）#一年中第几天
select minute(‘10:05:03’)
select second(‘10:05:03’)
select extract(year from ‘2020/08/09’) as col1,
extract(year_month from ‘2020/08/09’) as col2,
extract(day_minute from’2020/08/09 01:05:02’) as col3
select time_to_second (‘23:08:09’)
select date_add(‘2018-01-08 10:59:06’,interval 1 second)#加时间
select date_sub(‘2018-09-08’,interval 30 day)#减时间

 select if(1>2,2,3)
 select ifnull(1,2)#若1非空，返回1，否则2
 select case 2 when 1 then 'one' when 2 then 'second' else 'more'end
 select case when 1<0 then true else 'false' end
 
 #有用函数
 select format(123.123,4)#数字转化，保留多少位小数转化
 select conv('a',16,2),conv(15,10,2)#不同价值转化
 select benchmark(500,sha('newpwd'))#重复执行例如sha操作500次，并返回使用时长
 select charset('string'),charset(convert('string',latin1))
 select cast(100 as char(2))#cast转化数据类型
 #窗口函数
 create database test
 use test
 create table branch(
 name char(255) not null,
 brcount int(11) not null)
 insert into branch(name,brcount) values('branch1',5),('branch2',10),('branch3',8),('branch4',20)
 ,('branch5',9)
 select* from branch order by brcount +
 select*,rank() over w1 as `rank` from branch 
 window w1 as (order by brcount)#创建了w1的窗口函数，规定对brcount排序，在select 对它执行了rank()
 #w1也可以省略 直接前面
 select round(rand()*10),round(rand()*10),round(rand()*10)
 create table member( m_id int auto_increment primary key,
 m_FN VARCHAR(100),
 m_LN VARCHAR(100),
 m_birth DATETIME,
 m_info VARCHAR(255) not null
)
 insert into member values(null,'Halen','Park','1970-06-29','GoodMan')
 select length(m_FN),CONCAT(m_FN,m_LN),LOWER(m_info),REVERSE(m_info) FROM member

select year(now())-year(m_birth) as age,dayofyear(m_birth) as days,
date_format(m_birth,’%W %D %M %Y’) as birthday from member
select m_birth,case when year(m_birth)<2000 then ‘old’
when year(m_birth)>2000 then ‘young’ else ‘not born’ end as status from member`