本篇文章参考: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)