MySQL入门命令之函数--单行函数-字符函数

概念：类似于Java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。
好处：1、隐藏实现细节 2、提高代码的重用性
调用：select 函数名(实参列表) [from 表(选择性使用)]
分类：
1、单行函数 如 length、ifnull
2、分组函数 做统计使用
单行函数分类：
1、字符函数
2、数学函数
3、日期函数
4、流程控制函数
5、其它
一：字符函数
1、length—获取字符串长度

mysql> select length('john');
+----------------+
| length('john') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

mysql> select length('张三丰');#根据字符集不同结果可能不同
+------------------+
| length('张三丰') |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

2、concat—字符串拼接

mysql> select concat(last_name,'_',first_name) from employees;
+----------------------------------+
| concat(last_name,'_',first_name) |
+----------------------------------+
| K_ing_Steven                     |
| Kochhar_Neena                    |
| De Haan_Lex                      |
| Hunold_Alexander                 |
| Ernst_Bruce                      |
| Austin_David                     |
| Pataballa_Valli                  |
| Lorentz_Diana                    |
| Greenberg_Nancy                  |
| Faviet_Daniel                    |
| Chen_John                        |
| Sciarra_Ismael                   |
| Urman_Jose Manuel                |
| Popp_Luis                        |
| Raphaely_Den                     |
#或
mysql> select concat(last_name,'_',first_name) as 姓名 from employees;
+-------------------+
| 姓名              |
+-------------------+
| K_ing_Steven      |
| Kochhar_Neena     |
| De Haan_Lex       |
| Hunold_Alexander  |
| Ernst_Bruce       |
| Austin_David      |
| Pataballa_Valli   |
| Lorentz_Diana     |
| Greenberg_Nancy   |
| Faviet_Daniel     |
| Chen_John         |
| Sciarra_Ismael    |
| Urman_Jose Manuel |

3、upper、lower—将字符转换为大写、小写

mysql> select upper('john');
+---------------+
| upper('john') |
+---------------+
| JOHN          |
+---------------+
1 row in set (0.00 sec)

mysql> select lower('JOHN');
+---------------+
| lower('JOHN') |
+---------------+
| john          |
+---------------+
1 row in set (0.00 sec)
#
mysql> select concat(upper(last_name),'__',lower(first_name)) as 姓名 from employees;
+--------------------+
| 姓名               |
+--------------------+
| K_ING__steven      |
| KOCHHAR__neena     |
| DE HAAN__lex       |
| HUNOLD__alexander  |
| ERNST__bruce       |
| AUSTIN__david      |
| PATABALLA__valli   |
| LORENTZ__diana     |
| GREENBERG__nancy   |
| FAVIET__daniel     |
| CHEN__john         |
| SCIARRA__ismael    |
| URMAN__jose manuel |
| POPP__luis         |
| RAPHAELY__den      |
| KHOO__alexander    |

4、substr—字符串截取
从1开始索引截取后面的字符

mysql> select substr('hyaloveyy',4);
+-----------------------+
| substr('hyaloveyy',4) |
+-----------------------+
| loveyy                |
+-----------------------+
1 row in set (0.00 sec)

截取指定位置指定长度的字符串

mysql> select substr('hyaloveyy',4,4);
+-------------------------+
| substr('hyaloveyy',4,4) |
+-------------------------+
| love                    |
+-------------------------+
1 row in set (0.00 sec)

5、返回子字符串在母字符串的第一次索引位置，若没有则返回0

mysql> select instr('hyloveyy','z') as output;
+--------+
| output |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

mysql> select instr('hyloveyy','love') as output;
+--------+
| output |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> select instr('hyloveyylove','love') as output;
+--------+
| output |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

6、trim—去除前后空格或指定字符

mysql> select length(trim('  love   love  ')) as output;
+--------+
| output |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)
mysql> select trim('y' from 'hyloveyy') as output;
+--------+
| output |
+--------+
| hylove |
+--------+
1 row in set (0.00 sec)

mysql> select trim('hy' from 'hyloveyy') as output;
+--------+
| output |
+--------+
| loveyy |
+--------+
1 row in set (0.00 sec)

mysql> select trim('yy' from 'hyloveyy') as output;
+--------+
| output |
+--------+
| hylove |
+--------+
1 row in set (0.00 sec)

mysql> select trim('y' from 'hyloveyy') as output;
+--------+
| output |
+--------+
| hylove |
+--------+
1 row in set (0.00 sec)

7、lpad—用指定的字符左填充知道达到指定的长度

mysql> select lpad('love',10,'o') as out_put;
+------------+
| out_put    |
+------------+
| oooooolove |
+------------+
1 row in set (0.00 sec)

超过则进行右截断

mysql> select lpad('lovelovelove',10,'o') as out_put;
+------------+
| out_put    |
+------------+
| lovelovelo |
+------------+
1 row in set (0.00 sec)

8、rpad–用指定的字符右填充知道达到指定的长度

mysql> select rpad('love',10,'o') as out_put;
+------------+
| out_put    |
+------------+
| loveoooooo |
+------------+
1 row in set (0.00 sec)

超过则进行左截断

mysql> select rpad('lovelovelove',10,'o') as out_put;
+------------+
| out_put    |
+------------+
| lovelovelo |
+------------+
1 row in set (0.00 sec)

9、replace—用指定字符串替换某字符串

mysql> select replace('loveyy','yy','hy') as loveyy;
+--------+
| loveyy |
+--------+
| lovehy |
+--------+
1 row in set (0.00 sec)

更多MySQL命令