MySQL命令学习5

MYSQL学习5

1 .使用存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

为什么要使用存储过程？

• 通过把处理封装在容易使用的单元中，简化复杂的操作
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
• 简化对变动的管理。如果表名，列名或业务逻辑（或别的内容）有变化，只需更改存储过程的代码。使用人员不需要知道这些变化。
• 提高性能。因为使用存储过程比使用单独的SQL语句要快。
• 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

使用存储过程有三个好处：简单，安全，高性能；

缺陷：

• 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
• 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许创建存储过程。

① 使用存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它任意参数。

② 创建存储过程

create procedure user_total()
begin
	select count(*) as user_count from user;
end

命令行客户端无法执行存储过程，只能利用其他客户端软件执行

③ 调用与删除存储过程

call user_total();

执行drop procedure 存储名；

drop procedure user_total;

④ 使用参数

给存储过程使用参数

例如：输入参数为age输出该年龄的人数

create procedure getUserById(
	in user_age int,
    out user_count int
)
begin
	select count(*) from user
	where age = user_age into user_count;
end;

执行该存储过程

再执行call给一个int类型的年龄，和@参数名称

执行select @参数名称 获取相应结果

结果为年龄=20岁的用户有四个

2 .使用触发器

① insert触发器

当插入一条记录时返回其自动生成的id

#insert触发器
create trigger getNewId after insert on user for each row
begin
    select new.id into @user_id;
end;
#插入一个用户
insert into user(name,age,rid) values('kenewstar3',23,1);

MySQL 5以后必须将返回结果放入变量中，通过select @名称 查询

如图新插入的记录返回id=11

3 .管理事务处理

关键字：commit，rollback

MyISAM与InnoDB是经常使用的引擎，前者不支持事务，后者支持

事务处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

在使用事务和事务处理时，有几个关键词反复出现。下面是关于事务处理需要知道的几个术语：

• 事务(transaction) 指一组SQL语句；
• 回滚(rollback) 指撤销指定SQL语句的过程；
• 提交(commit) 指将未存储的SQL语句结果写入数据库表；
• 保留点(savepoint) 事务处理中设置的临时占位符，你可以对它发布回退;

MySQL使用下面的语句来标识事务的开始：

start transaction

① 使用rollback

MySQL的rollback命令用来回退MySQL语句，例如：

select * from user;
start transaction;
delete * from user where id=2;
select * from user;
rollback;
select * from user;

删除id=2的用户

继续执行rolllback，并且再执行select * from user;

结果如第一张查询所示；

② 使用commit

start transaction;
delete from user where id=2;
delete from user where id=3;
commit;

隐含事务关闭 当commit或rollback语句执行后，事务会自动关闭