自定义无参函数:
mysql> CREATE FUNCTION NOW_()
RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时:%i分:%s秒');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT NOW_();
+-----------------------------------------+
| NOW_() |
+-----------------------------------------+
| 2018年05月17日 17时:18分:37秒 |
+-----------------------------------------+
1 row in set (0.00 sec)
自定义有参函数:
mysql> CREATE FUNCTION AVG_(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
-> RETURNS FLOAT(10,2) UNSIGNED
-> RETURN (num1+num2)/2;
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT AVG_(2,3);
+-----------+
| AVG_(2,3) |
+-----------+
| 2.50 |
+-----------+
1 row in set (0.02 sec)
删除函数:
mysql> DROP FUNCTION AVG_;
Query OK, 0 rows affected (0.00 sec)
修改终止符:DELIMITER
mysql> DELIMITER //
mysql> DESC 名单//
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| 姓 | varchar(12) | NO | | NULL | |
| 名 | varchar(24) | NO | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
复合结构函数体:将插入到名单的方法封装成一个函数,返回插入id值
mysql> CREATE FUNCTION add_name(first_name VARCHAR(12),last_name VARCHAR(24))
-> RETURNS SMALLINT UNSIGNED
-> BEGIN
-> INSERT 名单 VALUES(NULL,first_name,last_name);
-> RETURN LAST_INSERT_ID();
-> END//
Query OK, 0 rows affected (0.00 sec)
使用:
mysql> SELECT add_name('黄','月英')//
+--------------------------+
| add_name('黄','月英') |
+--------------------------+
| 7 |
+--------------------------+
1 row in set (0.01 sec)
mysql> SELECT*FROM 名单//
+----+--------+--------+
| id | 姓 | 名 |
+----+--------+--------+
| 1 | 张 | 飞 |
| 2 | 刘 | 备 |
| 3 | 关 | 羽 |
| 4 | 诸葛 | 亮 |
| 5 | 赵% | 云 |
| 6 | 马 | 超 |
| 7 | 黄 | 月英 |
+----+--------+--------+
7 rows in set (0.00 sec)
mysql> DELIMITER ;
mysql>