存储引擎

输入SQL命令,MySQL引擎对命令进行分析,查看输入的语法是否正确,如果正确,再进行编译,编译成MySQL引擎可识别的命令,最后再进行执行,并将执行结果返回给客户端。

如果省略了语法分析和编译的阶段,则效率可提高。

存储过程:

是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理(类似函数)。存储过程存储在数据库内,可以由应用程序调用执行,而且允许用户申明变量以及进行流程控制。存储过程可以接受输入类型的参数和输出类型的参数,并且可以存在多个返回值。只在第一次调用时进行语法分析和编译,以后的调用直接调用编译的结果,效率大大提高。

优点:

1、增强SQL语句的功能和灵活性:可以通过控制语句对流程进行控制和判断

2、实现较快的执行速度:客户端第一次调用存储过程时,MySQL引擎会对其进行语法分析、编译等操作,然后将编译结果存储到内存中,所以第一次和之前的效率一样,然而以后会直接调用内存中的编译结果,效率提高

3、减少网络流量:例如删除一个记录,我们原本要输入DELETE FROM xx WHERE ...; 要传输的字符较多,如果写成存储过程,就只要调用存储过程的名字和相应参数就行,传输的字符数量较少,所以减少了网络流量。

1、存储过程语法结构分析

CREATE
[DEFINER = {userCURRENT_USER}]//定义时的用户,若是不写就默认为当前用户
PROCEDURE sp_name ([proc_parameter[,...]]) //可以带0到多个参数
[characteristic ...] routine_body

其中参数

proc_parameter:
[IN OUT INOUT] param_name type

IN, 表示该参数的值必须在调用存储过程时指定

OUT, 表示该参数的值可以被存储过程改变,并且可以返回

INOUT, 表示该参数的值调用时指定,并且可以被改变和返回

2.特性

COMMENT 'string'
{CONTAINS SQLNO SQLREADS SQL DATAMODIFIES SQL DATASQL SECURITY{DEFINERINVOKER}
COMMENT:注释
CONTAINS SQL:包含SQL语句, 但不包含读或写数据的语句
NO SQL:不包含SQL语句
READS SQL DATA:包含读数据的语句
MODIFIES SQL DATA:包含写数据的语句
SQL SECURITY {DEFINERINVOKER}指明谁有权限来执行

3.过程体

(1)过程体由合法的SQL语句构成;

(2)过程体可以是任意SQL语句;对表格进行增删,连接,但是不能创建数据表

(3)过程体如果为复合结构则使用BEGIN...END语句

(4)复合结构可以使用条件、循环等控制语句

创建不带参数的存储过程:

1.创建没有参数的存储过程

CREATE PROCEDURE sp1() SELECT VERSION();

2.调用存储过程

CALL sp_name([parameter[,...]]) - 带参数的存储过程的调用
CALL sp_name[()] - 不带参数的存储过程调用
CALL sp1;
CALL sp1();

1.创建带有INT类型参数的存储过程

DESC users;
DELIMITER //
CREATE PROCEDURE removeUserByID(IN id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = id;
END
//
DELIMITER ;
CALL removeUserById(3); //参数名称最好不要和表中的字段相同
SELECT * FROM users; //全删除了
DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN DELETE FROM users WHERE id = p_id;
END
//
DELIMITER ;
SELECT * FROM users;
CALL removeUserById(22);
SELECT * FROM users WHERE id = 22;

2.修改存储过程

ALTER PROCEDURE sp_name [characteristic]
COMMENT 'string'
|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY{DEFINER|INVOKER}

3.删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name;
DROP removeUserById;

1.用户变量:以"@"开始,形式为"@变量名"

用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效

2.全局变量:定义时,以如下两种形式出现,set GLOBAL 变量名 或者 set @@global.变量名

对所有客户端生效。只有具有super权限才可以设置全局变量

3.会话变量:只对连接的客户端有效。

4.局部变量:作用范围在begin到end语句块之间。在该语句块里设置的变量

declare语句专门用于定义局部变量。set语句是设置不同类型的变量,包括会话变量和全局变量

创建带有多个OUT类型参数的存储过程:

1.明确什么是OUT类型的参数,有什么作用?

答:OUT类型参数,表示在调用存储过程时,该参数的值可以被存储过程改变,并且返回一个值。通常是这种类型时,调用时写入的参数都是变量用“@”符号开头的变量,在BEGIN和END之中的变量是局部变量,在调用存储过程时写入的变量是“用户变量”,比如有一个存储过程的名称叫做“addUser()”,调用时addUser(@sum),sum就是用户变量。

2.ROW_COUNT()函数就相当于PHP的MySQL函数库中的mysql_affected_rows()这个函数,两者的作用都是相同的,都是计算当插入记录和修改记录、以及添加和删除记录时的个数总和的。

3.例子:

SELECT ROW_COUNT() INTO delete_User ;是指调用ROW_COUNT()这个函数,并且把返回值存储在delete_User这个局部变量中。

SELECT COUNT(id) FROM users INTO remain_User ;是指在删除用户后,调用COUNT()函数对剩下的用户数做一个统计,在哪张表中要说明清楚,然后把返回值存储在remain_User这个局部变量中。

【因为delete_User和remain_User是OUT类型的参数,所以会返回一个值给调用时的用户变量,用"SELECT @a,@b"可以知道返回的值是多少了】

//创建带有多个OUT类型参数的存储过程
DESC users;
SELECT * FROM users;
SELECT ROW_COUNT(); //
DESC test;
INSERT test(username) VALUES('A'),('B,'),('C');
SELECT ROW_COUNT(); //返回被插入的记录总数
SELECT * FROM test;
UPDATE test SET username = CONTCAT(username, '--immoc') WHERE id <= 2;
SELECT row_COUNT(); //得到被更新的记录总数
DELIMITER //
CREATE PROCEDURE rmUserByAgeAndRtInfos(IN p_age SMALLINT UNSIGNED, OUT delNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE WHERE age = p_age; //注意变量不同
SELECT ROW_COUNT() INTO delNums;
SELECT COUNT(id) FROM users INTO leftNums;
END
//
DELIMITER ;
SELECT * FROM users;
SELECT COUNT(id) FROM users WHERE age = 20;
CALL rmUserByAgeAndRtInfos(20, @a, @b);
SELECT * FROM users;
SELECT COUNT(id) FROM users WHERE age = 23;
CALL rmUserByAgeAndRtInfos(23, @a, @b);
SELECT @a, @b;
存储过程与自定义函数的区别
//存储过程与自定义函数的区别

A、存储过程实现的功能相对复杂,函数针对性较强

B、存储过程可以返回多个值,函数只能有一个返回值

C、存储过程一般独立执行,函数可以作为 sql 语句的组成部分来出现

//修改存储过程
alter procedure sp_name [characteristic ...]
comment 'string'
| {contains sql | no sql | reads sql data | modifies sql data} | sql security {definer | invoker}
存储过程只能修改简单的特性,并不能修改过程体
//删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name;

1、存储过程:是SQL语句和控制语句的预编译集合,以一个名字存储并作为一个单元来处理。

2、参数:输入类型 输出类型 输入&&是输出

3、创建:CREATE...PROCEDURE...

4、注意事项:

(1)创建存储过程或自定义函数时需要通过delimiter语句修改定界符。

(2)如果函数体或过程题有多个语句,需要包含在BEGIN...END语句块中。

(3)存储过后才能通过call来调用。