mysql学习-字符串相关函数01

所有的字符串函数

函数名称	作 用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

常用的字符串函数

1.concat 字符串拼接

select concat('aa','-','cc') as aa,concat_ws('-','aa','bb','cc') as bb;

1.2 group_concat 多行数据合并

group_concat 函数实现对分组后的数据根据指定的分隔符，来将一个分组中的多个数据合并成一个数据。
注意：使用 GROUP_CONCAT（）函数必须对源数据进行分组（使用 group by），否则所有数据会被合并成一行
案例：
表：

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `add_time` datetime DEFAULT NULL,
  `ruuid` varchar(255) DEFAULT NULL COMMENT 'uuid去掉-后结果值大写',
  `address` varchar(255) DEFAULT NULL COMMENT '用户住址',
  PRIMARY KEY (`id`),
  KEY `name` (`name`(250)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert into user(name,add_time,ruuid,address) values('aaa',now(),upper(replace(uuid(),'-','')),'北京');
insert into user(name,add_time,ruuid,address) values('afuck',now(),upper(replace(uuid(),'-','')),'北京');
insert into user(name,add_time,ruuid,address) values('aaa',now(),upper(replace(uuid(),'-','')),'上海');
insert into user(name,add_time,ruuid,address) values('afuck',now(),upper(replace(uuid(),'-','')),'上海');
insert into user(name,add_time,ruuid,address) values('bbb',now(),upper(replace(uuid(),'-','')),'深圳');
insert into user(name,add_time,ruuid,address) values('bbb',now(),upper(replace(uuid(),'-','')),'广州');
insert into user(name,add_time,ruuid,address) values('aaa',now(),upper(replace(uuid(),'-','')),'深圳');
insert into user(name,add_time,ruuid,address) values('afuck',now(),upper(replace(uuid(),'-','')),'深圳');

数据库中的数据量：

需求：将每个人的行程地址汇聚成一行。

select name as '姓名', group_concat(address) as '曾到达地方',--  默认用逗号分隔开。
group_concat(address separator '|')
from user group by name; 

2.字符串截取

2.1 substring

substring(col_name, start, length)函数: col_name列名（必选），start从第几个开始（必选 为整数 从1开始），length截去长度

当start >0 即 start为正整数的时候：

select 'aa-bb-cc-dd' as oo,
substring('aa-bb-cc-dd',1) as pp,
substring('aa-bb-cc-dd',3) as aa, --  截取从第三个字符开始的字符串（包含第三个字符串） 
substring('aa-bb-cc',3,4) as bb; --  截取从第三个字符开始长度为4的字符串

当start为负整数的时候 ：

select 'aa-bb-cc-dd' as oo,
substring('aa-bb-cc-dd',-1) as pp,
substring('aa-bb-cc-dd',-3) as aa, --  截取从倒数第三个字符开始的字符串（包含第三个字符串） 
substring('aa-bb-cc-dd',-3,2) as bb; --  截取从倒数第三个字符开始长度为2的字符串

2.1.2 substr

substr() 函数 ：截取字符串
substr( )与substring( )意思相等

1、substr(str,pos);
截取从pos位置开始到最后的所有str字符串
2、substr(str from pos);

3、substr(str,pos,len);//str:字符串，pos：起始位置，len：截断长度
参数说明:
str为列名/字符串；
pos为起始位置；mysql中的起始位置pos是从1开始的；如果为正数，就表示从正数的位置往下截取字符串（起始坐标从1开始），反之如果起始位置pos为负数，那么 表示就从倒数第几个开始截取；
len为截取字符个数/长度。
4、substr(str from pos len);

select 'aa-bb-cc-dd' as oo,
SUBSTR('aa-bb-cc-dd',4) as aa, -- 从第四个字符开始截取之后的字符串，包括第四个字符
SUBSTR('aa-bb-cc-dd' from 4) as ab,   -- 和上面没加from实现一样 
SUBSTR('aa-bb-cc-dd',-4) as ac,       -- 从倒数第四个字符开始截取之后的字符串，包括倒数第四个字符。
SUBSTR('aa-bb-cc-dd',4,3) as ba,      -- 从第四个字符开始截取字符，包括第四个字符，截取字符串的长度为3。
substr('aa-bb-cc-dd',-4,3) as bb;     -- 从倒数第四个字符开始截取字符，包括倒数第四个字符，截取字符串的长度为3。

2.2 substring_index

substring_index(col, a, num)函数：从第num个a来分割col，
当num为正整数时，从左往右分割并左往右取；
num为负整数时，从右往左分割并从右往左取。

案例：

select substring_index('aa-bb-cc-dd-ee-ff','-',3) as aa,
	substring_index('aa-bb-cc-dd-ee-ff','-',-2) as bb;

3 replace

replace(colName,search,replace)
意思：把colName中出现search的全部替换为replace

update table_name set col_name=replace(col_name,'#','') where col_name like '%#%';
update `news` set `content`=replace(`content`,' ',''); -- 清除news表中content字段中的空格

select 'abcd###' as aa ,replace('abcd###','#','*') as bb;

3.1 uuid()

用mysql的uuid()函数 ，生成的uuid是36位的，其中包含32个字符以及4个分隔符（-），往往这个分隔符对我们来说是没有用的，可以使用mysql自带的replace函数去掉分隔符

select uuid() as aa,
	replace(uuid(),'-','') as bb, -- 去掉uuid()生成的36位字符串中的横杠-
	upper(replace(uuid(),'-','')) as cc -- 将去掉横杠的32位字符串转换成大写；

uuid() 拓展：
在 MySQL 中，可以有如下3种途径实现唯一值：
1.自增序列
2.UUID() 函数
3.程序自定义
UUID 基于 16 进制，由 32 位小写的 16 进制数字组成，
比如123e4567-e89b-12d3-a456-426655440000就是一个典型的 UUID。
MySQL 实现了 UUID，并且提供 UUID() 函数方便用户生成 UUID。在 MySQL 的 UUID() 函数中，前三组数字从时间戳中生成，第四组数字暂时保持时间戳的唯一性，第五组数字是一个 IEEE 802 节点标点值，保证空间唯一。
使用 UUID() 函数，可以生成时间、空间上都独一无二的值。
据说只要是使用了 UUID，都不可能看到两个重复的 UUID 值。当然，这个只是在理论情况下。

同一个 SQL 语句中，多处调用 UUID() 函数得到的值不相同，多次调用或执行得到的后两组值相同。
同一台服务器，重启 MySQL 前后的 UUID() 第四组值发生变化，第五组值不变；
MySQL 中，utf8 字符集下，生成的 UUID 长度为 36 位；
不同机器生成的 UUID 不同，包括第五组值；
在复制环境中，使用到 UUID() 函数，则一定要使用基于行或者基于混合模式复制方式。
学习链接

3.2replace into函数(拓展)

为什么会接触到replace into函数，是因为业务需要向数据库中插入数据，前提是重复的不能再次插入。以前用where解决的，今天才知道还有一个更简洁的方法replace。
replace具备替换拥有唯一索引或者主键索引重复数据的能力，也就是如果使用replace into插入的数据的唯一索引或者主键索引与之前的数据有重复的情况，将会删除原先的数据，然后再进行添加。
语法：replace into table( col1, col2, col3 ) values ( val1, val2, val3 )
语义：向table表中col1, col2, col3列replace数据val1，val2，val3。

4 insert

insert 也是用来替换字符串的
insert(str,pos,len,newstr)
str：指定字符串
pos：开始被替换的位置
len：被替换的字符串长度
newstr：新的字符串
总结：替换掉 str 范围为 [ pos, pos+len ] 的字符串
注意：如果 pos > str 长度以内，则返回 str 不会被替换；如果 len > str 剩余字符串的长度，则将 str 剩下所有字符都替换成 newstr
案例：

select 'aa-bb-cc-dd' as aa ,
insert('aa-bb-cc-dd',3,4,'####') as bb,
insert('aa-bb-cc-dd',-3,2,'####') as cc;-- 无效，不进行字符串替换

5 instr

instr(str,substr) 在一个字符串(str)中搜索指定的字符(substr),返回发现指定的字符的位置(index);
str 被搜索的字符串
substr 希望搜索的字符串
结论：在字符串str里面,字符串substr出现的第一个位置(index)，index是从1开始计算，如果没有找到就直接返回0，没有返回负数的情况。

案例：实现对用户名称
1.创建一个用户基础信息表

create table user(
	id int(11) unsigned not null auto_increment,
	name varchar(255) character set utf8mb4 default null,
	add_time datetime default null,
	primary key (id),
	key `name` (`name`(250)) using btree 
);

2.添加一个列属性；

alter table user add column ruuid varchar(255) default null comment 'uuid去掉-后结果值大写';

3.插入数据

insert into user(name,add_time,ruuid) values('cfuck',now(),upper(replace(uuid(),'-','')));
......

4.实现需求:

4.1 返回用户的所有信息，并对用户表洪用户名称是否包含敏感词 fuck 做标记。
如果返回结果为0 则说明不包含；如果返回结果大于0，则说明包含敏感词。

select *, instr(name,'fuck')  as '敏感词返回结果，为0则说明不包含敏感词' from user;

4.2 筛选出用户名中含有关键词的数据。

select * from user where instr(name,'fuck') >0;

6 regexp 正则表达式查询