菜鸟的MySQL学习笔记（五）

7、自定义函数

用户自定义函数（user-defined function，UDF）是一种对MySQL扩展的途径，其用法与内置函数相同。包含了两个必要条件，参数与返回值。没有必然内在联系。

函数可以返回任意类型的值，同样可以接收这些类型的参数；

创建自定义函数：

CREATE FUNCTION function_name #函数名

RETURNS

{STRING | INTEGER | REAL | DECIMAL} #返回值类型

routine_body #函数体

函数体可以包含合法的SQL语句；也可以是简单的SELECT或INSERT语句；

函数体如果是复合结构则可以使用BEGIN ... END 语句；

复合结构可以包含声明，循环，控制结构。

创建不带参数的函数

#SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点：%i分：%s秒')； #将时间显示为年月日，时分秒，函数要实现的功能

#CREATE FUNCTION f1() RETURNS VARCHAR(30)

RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点：%i分：%s秒')； #此时使用select f1（）即可获得上面的结果

创建带参数的函数

#CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)

RETURNS FLOAT(10,2) UNSIGED

RETURN (num1 + num2)/2; #计算两个值的平均值

创建带多个参数的函数

CREATE FUNCTION adduser（username VARCHAR(20)）

RETURNS INT UNSIGNED

BEGIN #由于使用了两个语句所以需要使用BEGIN 。。END语句

INSERT test(username) VALUES(username); #将username值插入

RETURN LAST_INSERT_ID(); #返回插入的ID

END

// #修改后的分隔符如果不修改会报错，需要将默认分隔符分号修改为//或其他的符号，命令为DELIMITER //

删除函数

DROP FUNCTION [IF EXISTS] function_name;

#DROP FUNCTION f2；

8、存储过程

MySQL的执行过程：

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

优点：

增强SQL语句的功能和灵活性

实现了较快的执行速度

减少了网络流量

创建存储过程：

CREATE

[DEFINER = { user | CURRENT_USER } ] #指向创建者

PROCEDURE sp_name ([proc_parameter [,...] ]) #带0个或多个参数

[characteristic ...] routine_body #特性

proc_parameter: #参数的写法

[IN | OUT | INOUT ] param_name type #IN，表示该参数的值必须在调用存储过程时指定；OUT，表示该参数的值可以被存储过程改变，并且可以返回； INOUT，表示该参数的调用时指定，并且可以被改变和返回。

特性：

characteristic 'string'

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

#COMMENT：注释

CONTAINS SQL : 包含SQL语句，但不包含读或写数据的语句

NO SQL：不包含SQL语句

READS SQL DATA ：包含读数据的语句

MODIFIES SQL DATA : 包含写数据的语句

SQL SECURITY { DEFINER | INVOKER } 指明谁有权限来执行

过程体

过程体由合法的SQL语句构成，可以是任意（对记录的增删改查，多表的链接操作）SQL语句；

过程体如果为复合结构则使用BEGIN ... END语句；复合结构可包含声明，循环，控制结构；

创建没有参数的过程：

CREATE PROCEDURE sp1() SELECT VERSION();

调用存储过程

CALL sp_name ([parameter [, ... ] ])

CALL sp_name [ ( ) ] #带参数小括号不可以省略

创建一个带有IN类型参数的过程：

CREATE PROCEDURE removeUserById （IN p_id INT UNSIGNED ) #创建过程，为删除userID

BEGIN

DELETE FROM users WHERE id = p_id; #第一个ID是数据表的字段，第二个为过程传递的参数

END

// #需要将该过程分隔符修改为//

创建过程需要将字段和参数的名称分别开来，不然会认为两个均为字段，将过程传参修改为p_id

创建一个带有OUT类型的过程

CREATE PROCEDURE removeUserAndRetrunUserNums （IN p_id INT UNSIGNED , OUT userNums INT UNSIGNED） #创建过程，删除user 返回userID

BEGIN

DELETE FROM users WHERE id = p_id;

SELECT count(id) FROM users INTO userNums;

END

调用该过程

CALL removeUserAndRetrunUserNums (24,@Nums); # @Nums 为局部变量，只在BEGIN。。END之间声明

SELECT @Nums ;

创建一个带有INOUT类型的过程

CREATE PROCEDURE removeUserByAgeAndRrturnInfos( IN p_age SMALLINT UNSIGNED ,OUT deleteUsers SMALLINT UNSIGNEND, OUT userCounts SMALLINT UNSIGNED)

BEGIN

DELETE FROM users WHERE age = p_age;

SELECT ROW_COUNT( ) INTO deleteUsers; # ROW_COUNT( )用来统计最近受影响的行数，结果为数值几行

SELECT COUNT( id ) FROM users INTO userCounts;

END

// #此处需要修改分隔符

CALL removeUserByAgeAndRrturnInfos（23，@a，@b）； #此处@a，@b为局部变量

存储过程与自定义函数的区别

1、存储过程实现的功能要复杂一些；而函数的针对性更强

2、存储过程可以返回多个值，函数只有一个返回值；

3、存储过程一般独立的来执行，而函数可以作为其他SQL语句的组成部分来实现。

9、存储引擎

MyISAM

InnoDB

Memory

CSV