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.pro的长度
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)
13. 常用字符函数描述总结
请登录之后再进行评论