mysql开启函数创建功能_MySQL 创建函数

函数

1:查看创建函数的功能是否开启

mysql> show variables like '%func%';//

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | OFF |

+---------------------------------+-------+

2:开启创建函数的功能

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：创建函数

mysql> create function fun01()

-> returns int

-> begin

-> return 10;

-> end;

-> //

Query OK, 0 rows affected (0.06 sec)

4：调用函数

mysql> select fun01();//

+---------+

| fun01() |

+---------+

| 10 |

+---------+

1 row in set (0.00 sec)

5:查看已创建的函数

mysql> show function status where db='wh'\G;

*************************** 1. row ***************************

Db: wh

Name: fun01

Type: FUNCTION

Definer: root@localhost

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:删除函数

mysql> show function status where db='wh';

+----+-------+----------+----------------+---------------------+--------------------

| Db | Name | Type | Definer | Modified | Created

+----+-------+----------+----------------+---------------------+--------------------

| wh | fun01 | FUNCTION | root@localhost | 2016-11-09 23:28:39 | 2016-11-09 23:28:39

| wh | fun02 | FUNCTION | root@localhost | 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 | root@localhost | 2016-11-09 23:28:39 | 2016-11-09 23:28:39

+----+-------+----------+----------------+---------------------+--------------------

5：查看某个具体的函数

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：函数与表关联使用(只能添加修改语句，不能添加查询语句？？？)

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 |

+-------+

级联查询

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 | |

+--------+--------------+------+-----+---------+----------------+