MySQL Create function

Function

1: See if function creation is turned on
Mysql> Show variables like '%func% ';//
+---------------------------------+-------+
| variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+

2: Turn on function creation
Mysql> Set Global log_bin_trust_function_creators=1;//
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show variables like '%func% ';//
+---------------------------------+-------+
| variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | On |
+---------------------------------+-------+
1 row in Set (0.00 sec)

3: Create a function
mysql> Create function Fun01 ()
-Returns INT
Begin
--return 10;
-End;
//
Query OK, 0 rows affected (0.06 sec)

4: Calling function
Mysql> select Fun01 ();//
+---------+
| Fun01 () |
+---------+
| 10 |
+---------+
1 row in Set (0.00 sec)

5: View the functions you have created
Mysql> Show function status where db= ' WH ' \g;
1. Row ***************************
Db:wh
Name:fun01
Type:function
Definer: [Email protected]
modified:2016-11-09 23:28:39
created:2016-11-09 23:28:39
Security_type:definer
Comment:
Character_set_client:latin1
Collation_connection:latin1_swedish_ci
Database Collation:latin1_swedish_ci
1 row in Set (0.14 sec)

4: Delete function
mysql> show function status where db= ' WH ';
+----+-------+----------+----------------+---------------------+--------------------
| Db | Name | Type | Definer | Modified | Created
+----+-------+----------+----------------+---------------------+--------------------
| wh | fun01 | FUNCTION | [Email protected] | 2016-11-09 23:28:39 | 2016-11-09 23:28:39
| wh | fun02 | FUNCTION | [Email protected] | 2016-11-09 23:38:03 | 2016-11-09 23:38:03
+----+-------+----------+----------------+---------------------+--------------------

mysql> drop function fun02;//
Query OK, 0 rows affected (0.25 sec)
Mysql> show function status where db= ' WH ‘;
+----+-------+----------+----------------+---------------------+--------------------
| Db | Name | Type | Definer | Modified | Created
+----+-------+----------+----------------+---------------------+--------------------
| wh | fun01 | FUNCTION | [Email protected] | 2016-11-09 23:28:39 | 2016-11-09 23:28:39
+----+-------+----------+----------------+---------------------+--------------------

5: View a specific function
Mysql> Show Create function fun01;//
+----------+----------------------------------------------------------------+---
| Function | Sql_mode | Create Function
+----------+----------------------------------------------------------------+---
| Fun01 | strict_trans_tables,no_auto_create_user,no_engine_substitution | CREATE definer= ' root ' @ ' localhost '
+----------+----------------------------------------------------------------+---
FUNCTION ' Fun01 ' () RETURNS int (11)
Begin
return 10;
End | Latin1 | Latin1_swedish_ci | Latin1_swedish_

6: function is used in association with table (only modify statement can be added, cannot add query statement??? ）
mysql> Create function fun05 (p int)
-Returns INT
Begin
INSERT into T values (p);
--return 10;
-End;
//
Query OK, 0 rows affected (0.01 sec)

Mysql> Select Fun05 (10000);//
+--------------+
| Fun05 (10000) |
+--------------+
| 10 |
+--------------+
1 row in Set (0.06 sec)

Mysql> SELECT * from t;//
+-------+
| S1 |
+-------+
| 10000 |
+-------+

cascading queries

INNER JOIN
Left Join
Right Join

mysql> desc User;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| ID | Int (11) | NO | PRI | NULL | auto_increment |
| name | varchar (20) | YES | | NULL | |
| Age | Int (11) | YES | | NULL | |
| Salary | Decimal (8,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+

MySQL Create function