MySQL入门学习实践笔记

MySQL学习笔记_常用函数

一、 字符串函数

函数	功能
CONCAT(str1,str2,…strn)	连接字符串str1,str2,…strn,返回一个新的字符串
INSERT(str,x,y,instr)	将str中起始位置为(x,y)这段字符串替换为instr
LOWER(str)	将str中所有字符转为小写
UPPER(str)	将str中所有字符转为大写
LEFT(str,x)	返回字符串str中最左边的x个字符
RIGHT(str,x)	返回字符串str中最右边的x个字符
LAPD(str,n,pad)	使用字符串pad对字符串str进行左填充，直到整个字符串长度为n个字符
RAPD(str,n,pad)	使用字符串pad对字符串str进行右填充，直到整个字符串长度为n个字符
LTRIM(str)	去掉字符串str左边的空格
RTRIM(str)	去掉字符串str右边的空格
REPEAT(str,x)	返回字符串str重复x次的结果
REPLACE(str,a,b)	将字符串str中出现的所有的字符串a替换成字符串b
STRCMP(str1,str2)	比较字符串str1和str2
TRIM(str)	去掉字符串str行头和行尾的空格
SUBSTRING(str,x,y)	返回字符串str中从x位置起共y个字符的子串

1. 合并字符串函数CONCAT()和CONCAT_WS()

# CONCAT(S1,S2,...SN)
mysql> SELECT CONCAT('My','S','QL') result;
+--------+
| result |
+--------+
| MySQL  |
+--------+
1 row in set (0.00 sec)

# 注意：如果有NULL参与合并则整个结果变为NULL
mysql> SELECT CONCAT('My','S','QL',null) result;
+--------+
| result |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

# CONCAT_WS(SEP,S1,S2,...SN):CONCAT With Separator
# SEP参数(第一个参数)是分隔符
mysql> SELECT CONCAT_WS('_','MySQL','is','powerfull') result;
+--------------------+
| result             |
+--------------------+
| MySQL_is_powerfull |
+--------------------+
1 row in set (0.00 sec)

# 注意：当分隔符为NULL时，合并后的返回结果将变为NULL
mysql> SELECT CONCAT_WS(NULL,'MySQL','is','powerfull') result;
+--------+
| result |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

# 合并字符串中有NULL参与，则忽略NULL
mysql> SELECT CONCAT_WS('_','MySQL',NULL,'is',NULL,'powerfull') result;
+--------------------+
| result             |
+--------------------+
| MySQL_is_powerfull |
+--------------------+
1 row in set (0.00 sec)

1. 比较字符串大小函数STRCMP(str1,str2): String Compare

# str1 > str2 : 返回 1
# str1 = str2 : 返回 0
# str1 < str2 : 返回-1
mysql> SELECT
    -> STRCMP('a','b') 'a<b',
    -> STRCMP('b','a') 'b>a',
    -> STRCMP('abc','abc') 'abc=abc',
    -> STRCMP('ab','abc') 'ab<abc';
+-----+-----+---------+--------+
| a<b | b>a | abc=abc | ab<abc |
+-----+-----+---------+--------+
|  -1 |   1 |       0 |     -1 |
+-----+-----+---------+--------+
1 row in set (0.02 sec)

2. 获取字符串长度(字节数)函数LENGTH()和字符数函数CHAR_LENGTH()
   注意：此处字节数跟字符集有关，我用的是UTF-8，一个汉字三或四个字节

mysql> SELECT
    -> LENGTH('a'),
    -> LENGTH('我'),
    -> CHAR_LENGTH('a'),
    -> CHAR_LENGTH('我');
+-------------+---------------+------------------+--------------------+
| LENGTH('a') | LENGTH('我')  | CHAR_LENGTH('a') | CHAR_LENGTH('我')  |
+-------------+---------------+------------------+--------------------+
|           1 |             3 |                1 |                  1 |
+-------------+---------------+------------------+--------------------+
1 row in set (0.00 sec)

3. 字符大小写转换函数UPPER()和LOWER()：略
4. 查找字符串FIND_IN_SET(str1,str2)函数：

# 返回str1在str2中的位置，str2中包含多个用逗号隔开的字符串
mysql> SELECT FIND_IN_SET('MySQL','JAVA,python,Scala,MySQL') position;
+----------+
| position |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

5. 查找字符串位置函数FIELD(str,str1,str2,str3,…)：

# 查找与第一个字符串相匹配的位置
mysql> SELECT FIELD('MySQL','JAVA','python','Scala','MySQL') position;
+----------+
| position |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

# 注意：与FIND_IN_SET()函数的参数区别：
# FIND_IN_SET('str', 'str1,str2,str3,str')
# 		FIELD('str','str1','str2','str3','str4')
# 其他位置函数：
mysql> SELECT LOCATE('SQL','MySQL'), POSITION('SQL' IN 'MySQL'), INSTR('MySQL','SQL');
+-----------------------+----------------------------+----------------------+
| LOCATE('SQL','MySQL') | POSITION('SQL' IN 'MySQL') | INSTR('MySQL','SQL') |
+-----------------------+----------------------------+----------------------+
|                     3 |                          3 |                    3 |
+-----------------------+----------------------------+----------------------+
1 row in set (0.00 sec)

6. 根据位置返回字符串ELT()函数：

# ELT(n,str1,str2,str3...)
# 返回第n个字符串
mysql> SELECT ELT(3,'JAVA','python','Scala','MySQL') result;
+--------+
| result |
+--------+
| Scala  |
+--------+
1 row in set (0.00 sec)

# 另外一个函数
# MAKE_SET(num,str1,str2,str3...):
# 该函数首先会将num转换为二进制数，然后返回二进制数相应位为1的对应字符串
# 如MAKE_SET(5,str1,str2,str3,str4)，5对应二进制101，第一、三位为1，则返回第一、三个字符串str1、str3。

7. 从现有字符串中截取子串

# 1.从左边或右边截取子串
# LEFT(str,num)/RIGHT(str,num)
# 截取前/后num个字符作为子串返回
mysql> SELECT RIGHT('love中国',3),LEFT('love中国',5);
+-----------------------+----------------------+
| RIGHT('love中国',3)   | LEFT('love中国',5)   |
+-----------------------+----------------------+
| e中国                 | love中               |
+-----------------------+----------------------+
1 row in set (0.11 sec)

# 2.SUBSTRING(str,start_index,len)/MID(str,start_index,len):
# 从str中第start_index个字符开始截取len个字符组成子串返回
mysql> SELECT
    -> SUBSTRING('I love China',3,10),
    -> MID('我爱中国',2,3);
+--------------------------------+-------------------------+
| SUBSTRING('I love China',3,10) | MID('我爱中国',2,3)     |
+--------------------------------+-------------------------+
| love China                     | 爱中国                  |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

8. 去除首/尾/首尾空格或指定符号TRIM()函数：

# 默认去除空格
#LTRIM(str)去除首位空格
# TRIM(str)去除首尾空格
#RTRIM(str)去除尾位空格
mysql> SELECT
    -> CONCAT('-',LTRIM(' | '),'-') as 'left',
    -> CONCAT('-',RTRIM(' | '),'-') as 'right',
    -> CONCAT('-',TRIM(' | '),'-') as 'both';
+------+-------+------+
| left | right | both |
+------+-------+------+
| -| - | - |-  | -|-  |
+------+-------+------+
1 row in set (0.00 sec)
#指定去除首/首尾/尾符号
# TRIM([LEADING|BOTH|TRAILING] symbol FROM str)
mysql> SELECT
    -> TRIM(LEADING '"' FROM '"|"') as 'left',
    -> TRIM(BOTH '"' FROM '"|"') as 'both',
    -> TRIM(TRAILING '"' FROM '"|"') as 'right';
+------+------+-------+
| left | both | right |
+------+------+-------+
| |"   | |    | "|    |
+------+------+-------+
1 row in set (0.00 sec)

9. 替换字符串函数

# 1.INSERT(str,pos,len,newstr)函数：
mysql> SELECT INSERT('I love JAVA and python',8,4,'Scala');
+----------------------------------------------+
| INSERT('I love JAVA and python',8,4,'Scala') |
+----------------------------------------------+
| I love Scala and python                      |
+----------------------------------------------+
1 row in set (0.00 sec)
# 2.REPLACE(str,substr,newstr)函数：
mysql> SELECT REPLACE('I love JAVA and python, especilly JAVA','JAVA','Scala') as result;
+------------------------------------------+
| result                                   |
+------------------------------------------+
| I love Scala and python, especilly Scala |
+------------------------------------------+
1 row in set (0.00 sec)