概念:类似于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)