函数是指一段可以调用的的程序或代码

函数

• 字符串函数

• 数值函数

• 日期函数

• 流程函数

字符串函数

例：

mysql> select concat ('hellow','mysql'); +---------------------------+| concat ('hellow','mysql') |+---------------------------+| hellowmysql               |+---------------------------+1 row in set (0.02 sec)mysql> select lower ('Hellow');+------------------+| lower ('Hellow') |+------------------+| hellow           |+------------------+1 row in set (0.04 sec)mysql> select upper('Hellow');+-----------------+| upper('Hellow') |+-----------------+| HELLOW          |+-----------------+1 row in set (0.03 sec)mysql> select lpad('01',5,'-');+------------------+| lpad('01',5,'-') |+------------------+| ---01            |+------------------+1 row in set (0.01 sec)mysql> select rpad('01',5,'-');+------------------+| rpad('01',5,'-') |+------------------+| 01---            |+------------------+1 row in set (0.03 sec)mysql> select trim(' Hellow MySQL ');+------------------------+| trim(' Hellow MySQL ') |+------------------------+| Hellow MySQL           |+------------------------+1 row in set (0.03 sec)mysql> select substring('Hellow Mysql',1,5);+-------------------------------+| substring('Hellow mysql',1,5) |+-------------------------------+| Hello                         |+-------------------------------+1 row in set (0.04 sec)

练习：由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如：1号员工的工号应该为00001。

update class set id=lpad(id,5,'0');

数字函数

mysql> select ceil(1.1);+-----------+| ceil(1.1) |+-----------+|         2 |+-----------+1 row in set (0.05 sec)mysql> select floor(1.1);+------------+| floor(1.1) |+------------+|          1 |+------------+1 row in set (0.02 sec)mysql> select mod(3,4);+----------+| mod(3,4) |+----------+|        3 |+----------+1 row in set (0.05 sec)mysql> select rand();+---------------------+| rand()              |+---------------------+| 0.03044703978155464 |+---------------------+1 row in set (0.02 sec)mysql> select round(2.34,2);+---------------+| round(2.34,2) |+---------------+| 2.34          |+---------------+1 row in set (0.06 sec)

练习：通过数据库的函数生成一个六位的随机验证码

mysql> select lpad(round(rand()*1000000,0),6,'0');+-------------------------------------+| lpad(round(rand()*1000000,0),6,'0') |+-------------------------------------+| 089983  |+-------------------------------------+1 row in set (0.04 sec)

日期函数

流程函数

mysql> select if(false,'OK','Error');+------------------------+| if(false,'OK','Error') |+------------------------+| Error                  |+------------------------+1 row in set (0.04 sec)mysql> select ifnull('OK','Default');+------------------------+| ifnull('OK','Default') |+------------------------+| OK                     |+------------------------+1 row in set (0.04 sec)mysql> select ifnull('','Default');+----------------------+| ifnull('','Default') |+----------------------+|                      |+----------------------+1 row in set (0.03 sec)mysql> select ifnull(null,'Default');+------------------------+| ifnull(null,'Default') |+------------------------+| Default                |+------------------------+1 row in set (0.02 sec)

select    name,    (case when age > 30 then '中年' else '青年' end)from employee;select    name,    (case workaddress when '北京市' then '一线城市' when '上海市' then '一线城市' else '二线城市' end) as '工作地址'from employee;

来源地址：https://blog.csdn.net/m0_60322614/article/details/128816303