1、CONCAT() 字符连接
（1）mysql> SELECT CONCAT('imooc', 'MYSQL');
+--------------------------+
| CONCAT('imooc', 'MYSQL') |
+--------------------------+
| imoocMYSQL |
+--------------------------+
（2）mysql> SELECT CONCAT('imooc','-','MYSQL');
+-----------------------------+
| CONCAT('imooc','-','MYSQL') |
+-----------------------------+
| imooc-MYSQL |
+-----------------------------+
（3）mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
| 1 | John |
| 2 | Mary |
+----+----------+
2 rows in set (0.00 sec)
mysql> SELECT CONCAT(id, username) AS id_username FROM test;
+-------------+
| id_username |
+-------------+
| 1John |
| 2Mary |
+-------------+
2、CONCAT_WS() 使用指定的分隔符进行字符连接（最少三个字符）
mysql> SELECT CONCAT_WS('|', 'A','B','C');
+-----------------------------+
| CONCAT_WS('|', 'A','B','C') |
+-----------------------------+
| A|B|C |
+-----------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT CONCAT_WS('-','imooc','MYSQL');
+--------------------------------+
| CONCAT_WS('-','imooc','MYSQL') |
+--------------------------------+
| imooc-MYSQL |
+--------------------------------+
3、FORMAT() 数字格式化
mysql> SELECT FORMAT(12560.72,2); #小数点后面保留两位
+--------------------+
| FORMAT(12560.72,2) |
+--------------------+
| 12,560.72 |
+--------------------+
1 row in set (0.02 sec)
mysql>
mysql> SELECT FORMAT(12560.72,1);
+--------------------+
| FORMAT(12560.72,1) |
+--------------------+
| 12,560.7 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT FORMAT(12560.72,0);
+--------------------+
| FORMAT(12560.72,0) |
+--------------------+
| 12,561 |
4、LOWER() UPPER()
mysql> SELECT LOWER('Mysql');
+----------------+
| LOWER('Mysql') |
+----------------+
| mysql |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT UPPER('Mysql');
+----------------+
| UPPER('Mysql') |
+----------------+
| MYSQL |
+----------------+
1 row in set (0.00 sec)
5、LEFT() 获取左侧字符
RIGHT() 获取右侧字符
mysql> SELECT LEFT('MySQL', 2);
+------------------+
| LEFT('MySQL', 2) |
+------------------+
| My |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT LOWER( LEFT('MySQL', 2) );
+---------------------------+
| LOWER( LEFT('MySQL', 2) ) |
+---------------------------+
| my |
+---------------------------+
6、LENGTH() 获取字符串长度
mysql> SELECT LENGTH('MYSQL');
+-----------------+
| LENGTH('MYSQL') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.01 sec)
mysql> SELECT LENGTH('MYSQ L');
+------------------+
| LENGTH('MYSQ L') |
+------------------+
| 6 |
+------------------+
7、LTRIM() 删除前导空格（第一个字符之前的）
RTRIM() 删除后续空格（最后一个空格之后的）
TRIM() 删除前导和后续空格
mysql> SELECT LENGTH(' MYSQL ');
+-----------------------+
| LENGTH(' MYSQL ') |
+-----------------------+
| 11 |
+-----------------------+
mysql> SELECT LENGTH(LTRIM(' MYSQL '));
+------------------------------+
| LENGTH(LTRIM(' MYSQL ')) |
+------------------------------+
| 9 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT LENGTH(RTRIM(' MYSQL '));
+------------------------------+
| LENGTH(RTRIM(' MYSQL ')) |
+------------------------------+
| 7 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT LENGTH(TRIM(' MYSQL '));
+-----------------------------+
| LENGTH(TRIM(' MYSQL ')) |
+-----------------------------+
| 5 |
+-----------------------------+
1 row in set (0.02 sec)
mysql> SELECT TRIM(LEADING '?' FROM '??MySQL???'); #删除前导字符？
+-------------------------------------+
| TRIM(LEADING '?' FROM '??MySQL???') |
+-------------------------------------+
| MySQL??? |
+-------------------------------------+
mysql> SELECT TRIM(TRAILING '?' FROM '??MySQL???'); #删除后续字符？
+--------------------------------------+
| TRIM(TRAILING '?' FROM '??MySQL???') |
+--------------------------------------+
| ??MySQL |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRIM(BOTH '?' FROM '??MySQL???'); #删除前导和后续字符？
+----------------------------------+
| TRIM(BOTH '?' FROM '??MySQL???') |
+----------------------------------+
| MySQL |
+----------------------------------+
1 row in set (0.00 sec)
8、REPLACE()
mysql> SELECT TRIM(BOTH '?' FROM '??My??SQL???');
+------------------------------------+
| TRIM(BOTH '?' FROM '??My??SQL???') |
+------------------------------------+
| My??SQL |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT REPLACE('??My??SQL???', '?', ''); #将字符串中的？替换成空格
+----------------------------------+
| REPLACE('??My??SQL???', '?', '') |
+----------------------------------+
| MySQL |
+----------------------------------+
1 row in set (0.01 sec)
9、SUBSTRING() 字符串的截取
mysql> SELECT SUBSTRING('MySQL', 1, 2); #从字符串第一位开始截取，截取两个
+--------------------------+
| SUBSTRING('MySQL', 1, 2) |
+--------------------------+
| My |
+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING('MySQL', 3); #从第三位开始截取，取到最后
+-----------------------+
| SUBSTRING('MySQL', 3) |
+-----------------------+
| SQL |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING('MySQL', -1); #从最后一位开始截取，取到最后
+------------------------+
| SUBSTRING('MySQL', -1) |
+------------------------+
10、[NOT] LIKE # 进行模式的匹配
mysql> SELECT 'MySQL' LIKE 'M%'; # %匹配任意数量的字符， _（下划线）只匹配1个字符
+-------------------+
| 'MySQL' LIKE 'M%' |
+-------------------+
| 1 #代表TRUE |
+-------------------+
1 row in set (0.01 sec)
mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
| 1 | John |
| 2 | Mary |
| 3 | 1%Lily |
+----+----------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM test WHERE username LIKE '%1%%' ESCAPE '1'; #1后面的%不需要认为是通配符
+----+----------+
| id | username |
+----+----------+
| 3 | 1%Lily |
+----+----------+