MySQL基础七：自定义函数

来源：互联网发布：北京装修多少钱知乎编辑：程序博客网时间：2024/05/10 17:27

本篇文章参考：http://www.imooc.com/video/2798

不带参数的函数；

mysql> #创建函数；mysql> SELECT NOW();+---------------------+| NOW()               |+---------------------+| 2016-04-13 21:53:16 |+---------------------+1 row in set (0.00 sec)mysql> SELECT DATE_FORMAT(NOW(),'%Y年%M月%D日%H点:%I分:%S秒');+-------------------------------------------------+| DATE_FORMAT(NOW(),'%Y年%M月%D日%H点:%I分:%S秒') |+-------------------------------------------------+| 2016年April月13th日21点:09分:50秒               |+-------------------------------------------------+1 row in set (0.00 sec)mysql> SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日-%H点:%I分:%S秒');+--------------------------------------------------+| DATE_FORMAT(NOW(),'%Y年%m月%d日-%H点:%I分:%S秒') |+--------------------------------------------------+| 2016年04月13日-21点:09分:34秒                    |+--------------------------------------------------+1 row in set (0.00 sec)mysql> #将上述过程封装成函数；mysql> CREATE FUNCTION f1() RETURNS VARCHAR(30)    -> RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日-%H点:%I分:%S秒');Query OK, 0 rows affected (0.03 sec)mysql> SELECT f1();+-------------------------------+| f1()                          |+-------------------------------+| 2016年04月13日-22点:10分:29秒 |+-------------------------------+1 row in set (0.04 sec)

带参数函数：

mysql> #求两个数的平均值；mysql>                                                                 mysql> CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)    -> RETURNS FLOAT(10,2) UNSIGNED    -> RETURN (num1+num2)/2;Query OK, 0 rows affected (0.02 sec)mysql> SELECT f2(15,13);+-----------+| f2(15,13) |+-----------+|     14.00 |+-----------+1 row in set (0.00 sec)

带多个参数的：

mysql> SHOW TABLES;+------------------+| Tables_in_world  |+------------------+| city             || country          || countrylanguage  || provinces        || tdb_goods        || tdb_goods_brands || tdb_goods_cate   || tdb_goods_types  || test             || users            |+------------------+10 rows in set (0.00 sec)mysql> DESC test;+----------+---------------------+------+-----+---------+----------------+| Field    | Type                | Null | Key | Default | Extra          |+----------+---------------------+------+-----+---------+----------------+| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(20)         | YES  |     | NULL    |                |+----------+---------------------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> SELECT * FROM test;+----+----------+| id | username |+----+----------+|  1 | John     ||  2 | 111      ||  3 | tom%     ||  4 | NULL     ||  5 | AA       ||  6 | BB       |+----+----------+6 rows in set (0.00 sec)mysql> #修改结束符为;-->//mysql> DELIMITER //mysql> SELECT VERSION()//+------------+| VERSION()  |+------------+| 5.7.10-log |+------------+1 row in set (0.00 sec)mysql> #多条语句必须加BEGIN 和END；mysql> CREATE FUNCTION adduser(username VARCHAR(20))    -> RETURNS INT UNSIGNED    -> BEGIN    -> INSERT test(username) VALUES(username);    -> RETURN LAST_INSERT_ID();    -> END    -> //Query OK, 0 rows affected (0.02 sec)mysql> SELECT adduser('Rose')//+-----------------+| adduser('Rose') |+-----------------+|               7 |+-----------------+1 row in set (0.06 sec)mysql> DELIMITER ;mysql> SELECT * FROM test;+----+----------+| id | username |+----+----------+|  1 | John     ||  2 | 111      ||  3 | tom%     ||  4 | NULL     ||  5 | AA       ||  6 | BB       ||  7 | Rose     |+----+----------+7 rows in set (0.00 sec)mysql> #删除函数；mysql> DROP FUNCTION f1;Query OK, 0 rows affected (0.01 sec)mysql> DROP FUNCTION f2;Query OK, 0 rows affected (0.00 sec)

0 0