mysql内置函数之字符串函数实验讲解
程序员文章站
2022-10-19 22:38:05
6.6 mysql 内置函数-字符串函数
6.6.1 链接字符串 – concat
concat(string2[,...])
实验一：...
6.6 mysql 内置函数-字符串函数
6.6.1 链接字符串 – concat
concat(string2[,...])
实验一：
mysql> select * from student;
+------+------+--------+
| id | name | sex |
+------+------+--------+
| 1 | aa | female |
| 2 | bb | male |
+------+------+--------+
2 rows in set (0.00 sec)
mysql> select concat(name,sex) from student;
+------------------+
| concat(name,sex) |
+------------------+
| aafemale |
| bbmale |
+------------------+
2 rows in set (0.00 sec)
6.6.2 分组链接字符串并以’,’隔开 – group_concat
group_concat(列名)
实验一：
mysql> insert into student values(1,'vv','male');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+------+------+--------+
| id | name | sex |
+------+------+--------+
| 1 | aa | female |
| 2 | bb | male |
| 1 | vv | male |
+------+------+--------+
3 rows in set (0.00 sec)
mysql> select concat(name) from student group by id;
+--------------+
| concat(name) |
+--------------+
| aa |
| bb |
+--------------+
2 rows in set (0.00 sec)
mysql> select group_concat(name) from student group by id;
+--------------------+
| group_concat(name) |
+--------------------+
| aa,vv |
| bb |
+--------------------+
2 rows in set (0.00 sec)
mysql> select group_concat(name) from student;
+--------------------+
| group_concat(name) |
+--------------------+
| aa,bb,vv |
+--------------------+
1 row in set (0.00 sec)
mysql> select group_concat(name,sex) from student;
+------------------------+
| group_concat(name,sex) |
+------------------------+
| aafemale,bbmale,vvmale |
+------------------------+
1 row in set (0.00 sec)
mysql> select group_concat(name,sex) from student group by id;
+------------------------+
| group_concat(name,sex) |
+------------------------+
| aafemale,vvmale |
| bbmale |
+------------------------+
2 rows in set (0.00 sec)
6.6.3 转换为小写 – lcase
lcase(string2)
实验一：
mysql> select * from student;
+------+------+--------+
| id | name | sex |
+------+------+--------+
| 1 | aa | female |
| 2 | bb | male |
+------+------+--------+
2 rows in set (0.00 sec)
mysql> select lcase(sex) from student;
+------------+
| lcase(sex) |
+------------+
| female |
| male |
+------------+
2 rows in set (0.00 sec)
6.6.4 转换为大写 – ucase
ucase(string2)
实验一：
mysql> select * from student;
+------+------+--------+
| id | name | sex |
+------+------+--------+
| 1 | aa | female |
| 2 | bb | male |
+------+------+--------+
2 rows in set (0.00 sec)
mysql> select ucase(sex) from student;
+------------+
| ucase(sex) |
+------------+
| FEMALE |
| MALE |
+------------+
2 rows in set (0.00 sec)
6.6.5 字符串长度 – length
length(string2)
实验一：
mysql> select * from student;
+------+------+--------+
| id | name | sex |
+------+------+--------+
| 1 | aa | female |
| 2 | bb | male |
+------+------+--------+
2 rows in set (0.00 sec)
mysql> select length(sex) from student;
+-------------+
| length(sex) |
+-------------+
| 6 |
| 4 |
+-------------+
2 rows in set (0.00 sec)
6.6.6 去除前端空格 – ltrim
ltrim(string2)
实验一：
mysql> select ltrim(" aa") ;
+---------------+
| ltrim(" aa") |
+---------------+
| aa |
+---------------+
1 row in set (0.00 sec)
6.6.7 去除后端空格– rtrim
rtrim(string2)
实验一：
mysql> select rtrim("aa ");
+----------------+
| rtrim("aa ") |
+----------------+
| aa |
+----------------+
1 row in set (0.00 sec)
mysql> select length( rtrim("aa "));
+-------------------------+
| length( rtrim("aa ")) |
+-------------------------+
| 2 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select length("aa ");
+-----------------+
| length("aa ") |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)
6.6.8 重复字符串– repeat
repeat(string2,count)
实验一：
mysql> select repeat(name,3) from student;
+----------------+
| repeat(name,3) |
+----------------+
| aaaaaa |
| bbbbbb |
+----------------+
2 rows in set (0.00 sec)
6.6.9 替换字符串 – replace
replace(srcStr,serach_str,replace_str)
实验一：
mysql> select replace(name,'aa','cc') from student;
+-------------------------+
| replace(name,'aa','cc') |
+-------------------------+
| cc |
| bb |
+-------------------------+
2 rows in set (0.00 sec)
6.6.10 截取字符串 – substring
substring(str,position,length)
实验一：
mysql> select substring(name,0,1) from student;
+---------------------+
| substring(name,0,1) |
+---------------------+
| |
| |
+---------------------+
2 rows in set (0.00 sec)
mysql> select substring(name,1,1) from student;
+---------------------+
| substring(name,1,1) |
+---------------------+
| a |
| b |
+---------------------+
2 rows in set (0.00 sec)
mysql> select substring(name,2,1) from student;
+---------------------+
| substring(name,2,1) |
+---------------------+
| a |
| b |
+---------------------+
2 rows in set (0.00 sec)
mysql> select substring(name,1,2) from student;
+---------------------+
| substring(name,1,2) |
+---------------------+
| aa |
| bb |
+---------------------+
2 rows in set (0.00 sec)
结论：substring的开始位置为1
6.6.11 生成空格 – space
space(count)
实验一：
mysql> select length(space(2));
+------------------+
| length(space(2)) |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec) 下一篇： Docker Swarm从部署到基本操作