MySQL 建立函數

標籤：nec   char   data   sel   type   表關聯   return   關聯   function   

函數

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: [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:刪除函數
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：查看某個具體的函數
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 | |
+--------+--------------+------+-----+---------+----------------+

 

MySQL 建立函數