MySQL常用函数

来源：互联网发布：网络标识域是什么意思编辑：程序博客网时间：2024/06/14 08:02

concat将多高字符连接起来

格式：concat(S1,S2,S3,S4....SN)将S1到SN连接起来

mysql>select concat('abc','def','ghi');

+---------------------------+

| concat('abc','def','ghi') |

+---------------------------+

| abcdefghi |

+---------------------------+

1 row in set (0.01 sec)

mysql>select concat(1,2,3,4);

+-----------------+

| concat(1,2,3,4) |

+-----------------+

| 1234 |

+-----------------+

1 row in set (0.00 sec)

insert替换字符串

格式：insert(str,x,y,instr)将str中的第x字符到第y个字符替换成instr

mysql>select insert('abcdefghijklmn',2,5,'aaaa');

+-------------------------------------+

| insert('abcdefghijklmn',2,5,'aaaa') |

+-------------------------------------+

| aaaaaghijklmn |

+-------------------------------------+

1 row in set (0.00 sec)

mysql>select insert('abcdefghijklmn',2,5,'#');

+----------------------------------+

| insert('abcdefghijklmn',2,5,'#') |

+----------------------------------+

| a#ghijklmn |

+----------------------------------+

1 row in set (0.00 sec)

LOWER将字符转换为小写

格式：LOWER(str)将str转换为小写

mysql>select LOWER('SaBcds');

+-----------------+

| LOWER('SaBcds') |

+-----------------+

| sabcds |

+-----------------+

1 row in set (0.00 sec)

UPPER将字符转换为大写

格式：UPPER(str)将str转换为大写

mysql> select UPPER('SaBcds');

+-----------------+

| UPPER('SaBcds') |

+-----------------+

| SABCDS |

+-----------------+

1 row in set (0.00 sec)

LEFT显示左边两个字符

格式：LEFT(str,x)将str的最左边x个字符显示出来

mysql>select left('abcdefg',2);

+-------------------+

| left('abcdefg',2) |

+-------------------+

| ab |

+-------------------+

1 row in set (0.00 sec)

RIGHT显示又边两个字符

格式：RIGHT(str,x)将str的最右边x个字符显示出来

mysql>select right('abcdefg',2);

+--------------------+

| right('abcdefg',2) |

+--------------------+

| fg |

+--------------------+

1 row in set (0.00 sec)

LPAD字符填充

格式：LPAD(str,n,pad)用字符串pad对str最左边进行填充，直到长度为N个字符串，如果字符串长度大于N则截取N个字符串显示

mysql>select LPAD('abcdefg',20,'#');

+------------------------+

| LPAD('abcdefg',20,'#') |

+------------------------+

| #############abcdefg |

+------------------------+

1 row in set (0.00 sec)

mysql>select LPAD('abcdefg',2,'#');

+-----------------------+

| LPAD('abcdefg',2,'#') |

+-----------------------+

| ab |

+-----------------------+

1 row in set (0.00 sec

RPAD字符填充

格式：RPAD(str,n,pad)用字符串pad对str最又边进行填充，直到长度为N个字符串，如果字符串长度大于N则截取N个字符串显示

mysql>select RPAD('abcdefg',20,'#');

+------------------------+

| RPAD('abcdefg',20,'#') |

+------------------------+

| abcdefg############# |

+------------------------+

1 row in set (0.00 sec)

LTRIM忽略左边空格

格式：LTRIM（str）忽略str左边空格

mysql>select CONCAT('#',LTRIM(' abc '),'#');

+-----------------------------------------------------------------+

| CONCAT('#',LTRIM(' abc '),'#') |

+-----------------------------------------------------------------+

| #abc # |

+-----------------------------------------------------------------+

1 row in set (0.00 sec)

RTRIM忽略左边空格

格式：RTRIM（str）忽略str右边空格

mysql> select CONCAT('#',rTRIM(' abc '),'#');

+-----------------------------------------------------------------+

| CONCAT('#',rTRIM(' abc '),'#') |

+-----------------------------------------------------------------+

| # abc# |

+-----------------------------------------------------------------+

1 row in set (0.00 sec)

REPEAT重复字符串

格式：repeat(str,x)将str重复X次

mysql>select repeat('abc|',20);

+----------------------------------------------------------------------------------+

| repeat('abc|',20) |

+----------------------------------------------------------------------------------+

|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc||

+----------------------------------------------------------------------------------+

1 row in set (0.00 sec)

REPLACE替换字符

格式：repeat(str1,a,b)使用b替换掉str中的a

mysql>select replace('abcdefgaaacdbssdfbaljfklsaf','a','#');

+------------------------------------------------+

|replace('abcdefgaaacdbssdfbaljfklsaf','a','#') |

+------------------------------------------------+

| #bcdefg###cdbssdfb#ljfkls#f |

+------------------------------------------------+

1 row in set (0.00 sec)

STRCMP比较两个字符串的ASCII码大小

格式：STRCMP(s1,s2)比较两个字符串的ASCII码大小，如果s1>s2则返回1，如果相等则返回0反之返回-1

mysql>select STRCMP('a','b'),STRCMP('b','a'),STRCMP('b','b');

+-----------------+-----------------+-----------------+

| STRCMP('a','b') | STRCMP('b','a') |STRCMP('b','b') |

+-----------------+-----------------+-----------------+

| -1 | 1 | 0 |

+-----------------+-----------------+-----------------+

1 row in set (0.00 sec)

TRIM去掉字符串头与尾的空格

格式：TRIM(STR)去掉str左右的空格

mysql>select concat('|',TRIM(' a b '),'|');

+-----------------------------------------+

| concat('|',TRIM(' a b '),'|') |

+-----------------------------------------+

| |a b| |

+-----------------------------------------+

1 row in set (0.00 sec)

SUBSTRING显示指定的字符

格式：substring(str,x,y)显示str中从第x个字符开始往后数y个字符

mysql>select substring('abcdefghijkl',2,5);

+-------------------------------+

| substring('abcdefghijkl',2,5) |

+-------------------------------+

| bcdef |

+-------------------------------+

1 row in set (0.00 sec)

0 0