智者一切求自己,愚者一切求他人 [登录·注册]

吕滔博客

首页 开发 运维 工具 摄影

MySQL事务、存储过程、视图、自定义函数、触发器、事件的简单使用及理解

数据库 memory 发布于November 20, 2017 标签: MySQL

事务:

通俗的说事务: 指一组操作,要么都成功执行,要么都不执行. -->原子性
在所有的操作没有执行完毕之前,其他会话不能够看到中间改变的过程. -->隔离性
事务发生前,和发生后,数据的总额依然匹配. -->一致性
事务产生的影响不能够撤消. -->持久性
如果出了错误,事务也不允许撤消, 只能通过"补偿性事务"

举个粟子:
李三: --->支出500, 李三 -500
赵四: --->收到500, 赵四 +500
关于事务的引擎:
选用innodb /bdb
开启事务:

start transaction;
Sql....
Sql....
commit 提交
rollback 回滚

注意:当一个事务commit,或者rollback就结束了
注意:有一些语句会造成事务的隐式的提交,比如 start transaction

触发器

场景:商城,下单成功,需要扣除商品库存,如果退单,要增加库存等相关联的操作。那可以多条SQL操作,但有时候总会忘记点撒。这时候你就可以用它啦...
创建触发器的语法:

create trigger trigger_name

after/before insert /update/delete on 表名

for each row

begin

sql语句:(触发的语句一句或多句)

end

举个粟子:
1.第一个触发器,购买一个,减少1个库存。
分析:
监视的地点: it_order表
监视的事件: it_order表的inset操作。
触发的时间: it_order表的inset之后
触发的事件: it_goods表减少库存的操作。

create trigger t1
after insert on it_order
for each row
begin
update it_goods set goods_number=goods_number-1 where id=2;
end$

2.购买商品,减少对应库存

create trigger t2
after insert on it_order
for each row
begin
update it_goods set goods_number=goods_number - new.much where id = new.goods_id;
end$

注意:如果在触发器中引用行的值。
对于insert 而言,新增的行用new来表示,行中的每一列的值,用new.列名来表示。
3.取消订单时,减掉的库存要添加回来
分析:
监视的地点: it_order表
监视的事件: it_order表的delete操作
触发的时间: it_order表的delete操作之后
触发的事件: it_goods表,把减掉的库存恢复过来。

create trigger t3
after delete on it_order
for each row
begin
update it_goods set goods_number=goods_number + old.much where id = old.goods_id;
end$

注意:对应it_order表,删除的行我们使用old来表示,如果要引用里面的数据,则使用old.列名来表示。
4.修改订单时,库存也要做对应修改.(可以修改购买数量,也可以修改类型)
监视的地点: it_order表
监视的事件: it_order表的update操作
触发的时间: it_order表的update操作之后
触发的事件: it_goods表,要修改对应的库存。

create trigger t4
after update on it_order
for each row
begin
update it_goods set goods_number=goods_number+old.much where id=old.goods_id;
update it_goods set goods_number=goods_number-new.much where id=new.goods_id;
end$

完成修改的思路:
(1)撤销订单,it_goods表里面的库存要恢复。
(2)重新下订单,it_goods表里面的库存要减少
注意:如果是修改操作,要引用it_order表里面的值,
修改前的数据,用old来表示,old.列名 引用被修改之前行中的值。
修改后的数据,用new来表示,new.列名 引用被修改之后行中的值。

删除触发器

语法:drop trigger 触发器的名称

查看触发器

语法: show triggers

before与after的区别

after是先完成数据的增删改,再触发,触发器中的语句晚于监视的增删改,无法影响前面的增删该动作。
就类似于先吃饭,再付钱。
before是先完成触发,再增删改,触发的语句先于监视的增删改发生,我们有机会判断修改即将发生的操作。
就类似于先付钱,再吃饭
典型案例:对于已下的订单,进行判断,如果订单的数量>5,就认为是恶意订单,强制把所定的商品数量改成5

视图

视图是由查询结果形成的一张虚拟表,是表通过某种运算得到的一个投影。同一张表可以创建多个视图
创建视图的语法:

create view view_name as select 语句

说明:
(1)视图名跟表名是一个级别的名字,隶属于数据库
(2)该语句的含义可以理解为:就是将该select命名为该名字(视图名);
(3)视图也可以设定自己的字段名,而不是select语句本身的字段名——通常不设置。
(4)视图的使用,几乎跟表一样!

视图的作用

(1)可以简化查询。
案例1:查询平均价格前3高的栏目。
传统的sql语句写法

select cat_id,avg(shop_price) as pj from ecs_goods group by cat_id order by pj desc limit 3;


创建一个视图,简化查询。
语法:

create view ecs_goods_v1 as select cat_id,avg(shop_price) pj from ecs_goods group by cat_id;

查询平均价格前3高的栏目,我们只需查询视图即可。

案例2:查询出商品表,以及所在的栏目名称;
传统写法:

select goods_id,goods_name,b.cat_name,shop_price from ecs_goods a left join ecs_category b on a.cat_id=b.cat_id;

创建一个视图

create view ecs_goods_v2 as select goods_id,goods_name,b.cat_name,shop_price from ecs_goods a left join ecs_category b on a.cat_id=b.cat_id;

查询视图;

select * from ecs_goods_v2;

(2)可以进行权限控制
把表的权限封闭,但是开放相应的视图权限,视图里只开放部分数据,比如某张表,用户表为例,2个网站搞合作,可以查询对方网站的用户,需要向对方开放用户表的权限,但是呢,又不想开放用户表中的密码字段。
再比如一个goods表,两个网站搞合作,可以相互查询对方的商品表,比如进货价格字段不能让对方查看。

第一步:创建一个视图,视图中不能包含in_price字段。
语法:create view goods_v1 as select id,goods_name,shop_price from goods;
第二步:创建一个用户,授予查询权限,只能操作goods_v1表(视图)
语法:grant select on php.goods_v1 to 'user'@'%' identified by '123456';

查询视图

语法:select * from 视图名 [where 条件]
视图和表一样,可以添加where 条件

修改视图

alter view view_name as select XXXX

删除视图

drop view 视图名称

查看视图结构

和表一样的,语法,desc 视图名称

查看所有视图

和表一样,语法:show tables;
注意:没有show views语句;

函数

创建:CREATE FUNCTION 函数名称(参数列表) RETURNS 返回值类型 函数体
修改:ALTER FUNCTION 函数名称 [characteristic ...]
删除:DROP FUNCTION [IF EXISTS] 函数名称
调用:SELECT 函数名称(参数列表)

来个粟子:

mysql> DELIMITER $$
mysql> CREATE FUNCTION hello()
    -> RETURNS VARCHAR(255)
    -> BEGIN
    -> RETURN 'Hello  world,i am mysql';
    -> END $$
mysql> DELIMITER;
mysql> SELECT hello();
+-------------------------+
| hello()                 |
+-------------------------+
| Hello  world,i am mysql |
+-------------------------+
row in set (0.00 sec)

存储过程

一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,比一个个执行sql语句效率高,用户通过指定存储过程的名字并给出参数来执行它。参数可以为IN, OUT, 或INOUT
语法:
创建:CREATE PROCEDURE 过程名 (参数列表) [characteristic ...]
修改:`ALTER PROCEDURE 过程名 [characteristic ...]
删除:DROP PROCEDURE [IF EXISTS] 过程名
调用:CALL 过程名(参数列表)
可以看看这篇文章
navicat中创建存储过程、触发器和使用游标的简单实例
MySQL存储过程

事件

事件调度器是MySQL5.1后新增的功能,可以将数据库按自定义的时间周期触发某种操作,可以理解为时间触发器,类似于Linux系统下面的任务调度器crontab,或者类似与window下面的计划任务。值得一提的是MySQL的事件调度器可以精确到每秒钟执行一个任务,而操作系统的计划任务(如:Linux下的CRON或Windows下的任务计划)只能精确到每分钟执行一次。
优点:一些对数据定时性操作不再依赖外部程序,而直接使用数据库本身提供的功能。可以实现每秒钟执行一个任务,这在一些对实时性要求较高的环境下就非常实用了。
缺点:定时触发,不可以调用。

一条create event语句创建一个事件。每个事件由两个主要部分组成,第一部分是事件调度(event schedule),表示事件何时启动以及按什么频率启动,第二部分是事件动作(event action ),这是事件启动时执行的代码,事件的动作包含一条SQL语句,它可能是一个简单地insert或者update语句,也可以使一个存储过程或者benin...end语句块,这两种情况允许我们执行多条SQL。
一个事件可以是活动(打开)的或停止(关闭)的,活动意味着事件调度器检查事件动作是否必须调用,停止意味着事件的声明存储在目录中,但调度器不会检查它是否应该调用。在一个事件创建之后,它立即变为活动的,一个活动的事件可以执行一次或者多次。

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

名词解释:
event_name :创建的event名字(唯一确定的)。
ON SCHEDULE:计划任务。
schedule: 决定event的执行时间和频率(注意时间一定要是将来的时间,过去的时间会出错),有两种形式 AT和EVERY。
[ON COMPLETION [NOT] PRESERVE]: 可选项,默认是ON COMPLETION NOT PRESERVE 即计划任务执行完毕后自动drop该事件;ON COMPLETION PRESERVE则不会drop掉。
[COMMENT 'comment'] :可选项,comment 用来描述event;相当注释,最大长度64个字节。
[ENABLE | DISABLE] :设定event的状态,默认ENABLE:表示系统尝试执行这个事件, DISABLE:关闭该事情,可以用alter修改
DO event_body: 需要执行的sql语句(可以是复合语句)。CREATE EVENT在存储过程中使用时合法的。

开启关闭事件调度器 : MySQL事件调度器event_scheduler负责调用事件,它默认是关闭的。这个调度器不断地监视一个事件是否要调用,要创建事件,必须打开调度器

mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+

开启事件调度器,可通过如下任何一个命令行

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

通过配置文件my.cnf

event_scheduler = 1 #或者ON  

查看调度器线程

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  2 | root            | localhost | NULL | Query   |    0 | NULL                   | show processlist |
|  3 | event_scheduler | localhost | NULL | Daemon  |    6 | Waiting on empty queue | NULL             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+

举个粟子:
例如:创建一个表记录每次事件调度的名字和事件戳

mysql> drop table if exists events_list;
mysql> create table events_list(event_name varchar(20) not null, event_started timestamp not null);

创建事件1(立即启动事件)

create event event_now 
on schedule 
at now() 
do insert into events_list values('event_now', now());

查看事件执行结果

mysql> select * from events_list;
+------------+---------------------+
| event_name | event_started       |
+------------+---------------------+
| event_now  | 2017-11-20 04:06:40 |
+------------+---------------------+

创建事件2(每分钟启动事件)

create event test.event_minute 
on schedule 
every  1 minute  
do insert into events_list values('event_now', now());

查看事件执行结果

mysql> select * from events_list;
+------------+---------------------+
| event_name | event_started       |
+------------+---------------------+
| event_now  | 2017-11-20 04:26:53 |
| event_now  | 2017-11-20 04:27:53 |
| event_now  | 2017-11-20 04:28:53 |
+------------+---------------------+

相关推荐

添加新评论

网站状态

  • 栏目分类:49个
  • 发布文章:1324篇
  • 用户评论:697条
  • 开博至今:3998天

正则速查

[abc] 匹配中括号中的单个字符,如a或b或c
[^abc] 匹配除了a、b、c等字符的其他单个字符
[a-z] 匹配一个字符范围,如a到z
[a-zA-Z] 匹配一个字符范围,如a-z 或 A-Z
^ 匹配行的开始
$ 匹配行的结束
\A 匹配一个字符串的开始
\z 匹配一个字符串的结束
. 匹配任意单个字符
\s 匹配空白字符,如空格,TAB
\S 匹配非空白字符
\d 匹配一个数字
\D 匹配非数字
\w 匹配一个字母
\W 匹配非字母
\b 匹配字符边界
(...) 引用所有括号中的内容
(a|b) a或者b
a? 零个或1个a
a* 零个或多个a
a+ 1个或多个a
a{3} 3次重复的a
a{3,} 3次或3次以上重复的a
a{3,6} 3到6次重复的a

修正符

/g 查找所有可能的匹配
/i 不区分大小写
/m 多行匹配
/s 单行匹配
/x 忽略空白模式
/e 可执行模式,PHP专有
/A 强制从目标字符串开头匹配
/D 使用$限制结尾字符,则不允许结尾有换行
/U 只匹配最近的一个字符串;不重复匹配

最新回复

  • tomxuetao: 能个实例吗?
  • memory: 哈哈哈。。。话说也没毛病。
  • 硫酸亚铁: 看了文章觉得自己已经是松鼠癌了 东西总是先占着 反正我有 什么...
  • xl: 支持一下
  • iHerb海淘攻略: 感谢分享
  • memory: POINT
  • sunyunlin: 数据库应该建些什么字段?
  • pengcheng: 已解决
  • pengcheng: Oops! It seems that sphinx was b...
  • 券都有: 感谢分享
  • memory: 这是我3年前玩过一次,当时因为安装完后,发现日常办公还好,但是好...
  • sdf: 想问一下,激活工具激活后,后面会出现提示要再次重新激活吗?
  • memory: 你要用碗装我吗?还是要我把扔的扔进你碗里???!!! &^_^&
  • 薛才杰: 同感,快扔到我的碗里来。。。
  • memory: 要说明一下,在大陆及香港的云主机或VPS,都是不支持的。原因都懂...
  • 历史趣谈LishiQtan: 很好的文章,值得收藏
  • memory: 现在回头看,,,这么烂的代码,也是醉了.