张甲49站长

2018-03-01 01:48电脑端

查看作者

6-2：MySQL 字符函数

1. CONCAT()

CONCAT()：字符连接

比如连接zhang和jia这两个字符：

mysql> SELECT CONCAT('zhang','jia')
    -> ;
+-----------------------+
| CONCAT('zhang','jia') |
+-----------------------+
| zhangjia              |
+-----------------------+
1 row in set (0.00 sec)

再比如连接zhang和jia和.tv这三个字符：

mysql> SELECT CONCAT('zhang','jia','.tv');
+-----------------------------+
| CONCAT('zhang','jia','.tv') |
+-----------------------------+
| zhangjia.tv                 |
+-----------------------------+
1 row in set (0.00 sec)

也可以将一个数据表中的两个字段连接：

mysql> SELECT * FROM TEST;
+----+----------+
| id | username |
+----+----------+
|  1 | John     |
|  2 | John     |
|  3 | 111      |
+----+----------+
3 rows in set (0.00 sec)

mysql> SELECT CONCAT(id,username) AS iu FROM test;
+-------+
| iu    |
+-------+
| 1John |
| 2John |
| 3111  |
+-------+
3 rows in set (0.00 sec)

2. CONCAT_WS()

CONCAT_WS()：使用指定的分隔符进行字符连接

最少三个参数，第一个是连接符，剩下的参数是需要连接的字符

比如将zhangjia和tv用.连接起来

mysql> SELECT CONCAT_WS('.','zhang','jia');
+------------------------------+
| CONCAT_WS('.','zhang','jia') |
+------------------------------+
| zhang.jia                    |
+------------------------------+
1 row in set (0.00 sec)

3. FORMAT()

FORMAT()：数字格式化

将一个数字格式化，保留两位小数：

mysql> SELECT FORMAT(1000.55,2);
+-------------------+
| FORMAT(1000.55,2) |
+-------------------+
| 1,000.55          |
+-------------------+
1 row in set (0.00 sec)

4. LOWER()和UPPER()

LOWER()：转换成小写字母

将ZHANGJIA.TV转化为zhagnjia.tv:

mysql> SELECT LOWER('ZHANGJIA.tv');
+----------------------+
| LOWER('ZHANGJIA.tv') |
+----------------------+
| zhangjia.tv          |
+----------------------+
1 row in set (0.00 sec)

UPPER()：转换成大写字母

将zhagnjia.tv转化为ZHANGJIA.TV:

mysql> SELECT UPPER('zhangjia.tv');
+----------------------+
| UPPER('zhangjia.tv') |
+----------------------+
| ZHANGJIA.TV          |
+----------------------+
1 row in set (0.00 sec)

5. LEFT()和RIGHT()

LEFT()：获取左侧字符，RIGHT()：获取右侧字符

需要两个参数，第一个是从哪个字符串获取，第二个是获取几位：

mysql> SELECT LEFT('https;//zhangjia.tv',8);
+-------------------------------+
| LEFT('https;//zhangjia.tv',8) |
+-------------------------------+
| https;//                      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT RIGHT('https;//zhangjia.tv',11);
+---------------------------------+
| RIGHT('https;//zhangjia.tv',11) |
+---------------------------------+
| zhangjia.tv                     |
+---------------------------------+
1 row in set (0.00 sec)

6. LENGTH()

LENGTH()：获取字符串长度

比如获取https://zhangjia.io的长度

mysql> SELECT LENGTH('https;//zhangjia.tv');
+-------------------------------+
| LENGTH('https;//zhangjia.tv') |
+-------------------------------+
|                            19 |
+-------------------------------+
1 row in set (0.00 sec)

而字符中间如果有空格的话，也算为一个长度：

mysql> SELECT LENGTH('1 23');
+----------------+
| LENGTH('1 23') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

7. LTRIM()

LTRIM()：删除前导空格

前导空格指的的第一个字符之前的空格：

mysql> SELECT LTRIM('    zhangjia.tv    ');
+------------------------------+
| LTRIM('    zhangjia.tv    ') |
+------------------------------+
| zhangjia.tv                  |
+------------------------------+
1 row in set (0.00 sec)

8. RTRIM()

RTRIM()：删除后续空格

后续空格指的是最后一个字符之后的空格：

mysql> SELECT RTRIM('    zhangjia.tv    ');
+------------------------------+
| RTRIM('    zhangjia.tv    ') |
+------------------------------+
|     zhangjia.tv              |
+------------------------------+
1 row in set (0.00 sec)

9. TRIM()

TRIM()：删除前导和后续空格

mysql> SELECT TRIM('    zhangjia.tv    ');
+-----------------------------+
| TRIM('    zhangjia.tv    ') |
+-----------------------------+
| zhangjia.tv                 |
+-----------------------------+
1 row in set (0.00 sec)

除了删除前导和后续空格外，还可以删除指定字符：

mysql> SELECT TRIM(LEADING 'a' FROM 'aaaaaazhangjia.tv');
+--------------------------------------------+
| TRIM(LEADING 'a' FROM 'aaaaaazhangjia.tv') |
+--------------------------------------------+
| zhangjia.tv                                |
+--------------------------------------------+
1 row in set (0.00 sec)

其中LEADING 表示删除前导字符，另外TRAILING 表示删除后续字符，比如：

mysql> SELECT TRIM(TRAILING 'a' FROM 'zhangjia.tvaaaaaaaa');
+-----------------------------------------------+
| TRIM(TRAILING 'a' FROM 'zhangjia.tvaaaaaaaa') |
+-----------------------------------------------+
| zhangjia.tv                                   |
+-----------------------------------------------+
1 row in set (0.00 sec)

BOTH表示删除前导和后序：

mysql> SELECT TRIM(BOTH 'a' FROM 'aaaaaaazhangjia.tvaaaaaaaa');
+--------------------------------------------------+
| TRIM(BOTH 'a' FROM 'aaaaaaazhangjia.tvaaaaaaaa') |
+--------------------------------------------------+
| zhangjia.tv                                      |
+--------------------------------------------------+
1 row in set (0.00 sec)

10. SUBSTRING()

SUBSTRING()：字符串截取，一共三个参数，第一个从哪个字符串截取，第二个是从第几个字符开始截取，第三个是截取几位：

mysql> SELECT SUBSTRING('12345678',1,2);
+---------------------------+
| SUBSTRING('12345678',1,2) |
+---------------------------+
| 12                        |
+---------------------------+
1 row in set (0.00 sec)

注意mysql字符串编号不是从0开始，而是从1开始，另外如果第三个参数省略，则直接从第二个参数的字符开始截取到最后：

mysql> SELECT SUBSTRING('12345678',1);
+-------------------------+
| SUBSTRING('12345678',1) |
+-------------------------+
| 12345678                |
+-------------------------+
1 row in set (0.00 sec)

如果想截取67位，还可以用负数解决（只有起始位置可以，长度不可以用负数）：

mysql> SELECT SUBSTRING('12345678',-3,2);
+----------------------------+
| SUBSTRING('12345678',-3,2) |
+----------------------------+
| 67                         |
+----------------------------+
1 row in set (0.00 sec)

11. [NOT] LIKE

[NOT] LIKE：模式匹配

mysql> SELECT 'MySQL' LIKE 'M%';
+-------------------+
| 'MySQL' LIKE 'M%' |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

上面语句的意思是MySQL和M%像不像，1代表TRUE，%在这里代表的是任意零个或多个字符，可能这样说有些晦涩难懂，再来举个例子：

首先给test数据表增加一条记录：

mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
|  1 | John     |
|  2 | John     |
|  3 | 111      |
+----+----------+
3 rows in set (0.00 sec)

mysql> INSERT test VALUES(NULL,'tom%');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
|  1 | John     |
|  2 | John     |
|  3 | 111      |
|  4 | tom%     |
+----+----------+
4 rows in set (0.00 sec)

查询用户名中包含o的用户：

mysql> SELECT * FROM test WHERE username LIKE '%o%';
+----+----------+
| id | username |
+----+----------+
|  1 | John     |
|  2 | John     |
|  4 | tom%     |
+----+----------+
3 rows in set (0.00 sec)

o可能在username字符的任意位置，所以采用’%o%‘的方式查询，第一个%和最后一个%都代表任意个字符（还可以用_代表任意一个字符）

但是如果我们查询用户名中包含%的用户，再用上面的语句的话，会发生一个小问题：

mysql> SELECT * FROM test WHERE username LIKE '%%%';
+----+----------+
| id | username |
+----+----------+
|  1 | John     |
|  2 | John     |
|  3 | 111      |
|  4 | tom%     |
+----+----------+
4 rows in set (0.00 sec)

可以看到username中不带%的记录也被查询到了，这是因为在MySQL中，’%%%’被全部当成了任意字符，可以在第一个%后面加任意字符来解决这个问题：

mysql> SELECT * FROM test WHERE username LIKE '%6%%' ESCAPE'6';
+----+----------+
| id | username |
+----+----------+
|  4 | tom%     |
+----+----------+
1 row in set (0.00 sec)

12. REPLACE()

REPLACE()：字符串替换

mysql> SELECT REPLACE( '******zhang*j*i***a.tv***','*','');
+----------------------------------------------+
| REPLACE( '******zhang*j*i***a.tv***','*','') |
+----------------------------------------------+
| zhangjia.tv                                  |
+----------------------------------------------+
1 row in set (0.00 sec)