MySQL事务、存储过程、视图、自定义函数、触发器、事件的简单使用及理解
来源:互联网 发布:淘宝货源上架数据包 编辑:程序博客网 时间:2024/06/04 23:26
事务:
通俗的说事务: 指一组操作,要么都成功执行,要么都不执行. -->原子性
在所有的操作没有执行完毕之前,其他会话不能够看到中间改变的过程. -->隔离性
事务发生前,和发生后,数据的总额依然匹配. -->一致性
事务产生的影响不能够撤消. -->持久性
如果出了错误,事务也不允许撤消, 只能通过"补偿性事务"
举个粟子:
李三: --->支出500, 李三 -500
赵四: --->收到500, 赵四 +500
关于事务的引擎:
选用innodb /bdb
开启事务:
start transaction;Sql....Sql....commit 提交rollback 回滚
注意:当一个事务commit,或者rollback就结束了
注意:有一些语句会造成事务的隐式的提交,比如 start transaction
触发器
场景:商城,下单成功,需要扣除商品库存,如果退单,要增加库存等相关联的操作。那可以多条SQL操作,但有时候总会忘记点撒。这时候你就可以用它啦...
创建触发器的语法:
create trigger trigger_nameafter/before insert /update/delete on 表名for each rowbeginsql语句:(触发的语句一句或多句)end
举个粟子:
1.第一个触发器,购买一个,减少1个库存。
分析:
监视的地点: it_order表
监视的事件: it_order表的inset操作。
触发的时间: it_order表的inset之后
触发的事件: it_goods表减少库存的操作。
create trigger t1after insert on it_orderfor each rowbeginupdate it_goods set goods_number=goods_number-1 where id=2;end$
2.购买商品,减少对应库存
create trigger t2after insert on it_orderfor each rowbeginupdate 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 t3after delete on it_orderfor each rowbeginupdate 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 t4after update on it_orderfor each rowbeginupdate 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 |+------------+---------------------+
- MySQL事务、存储过程、视图、自定义函数、触发器、事件的简单使用及理解
- mysql事务、触发器、视图、存储过程、函数
- mysql事务、触发器、视图、存储过程、函数
- MySQL 触发器 存储过程 自定义函数 视图 简单示例
- 修改MySQL存储过程、函数、事件、触发器、视图的DEFINER
- 修改MySQL存储过程、函数、事件、触发器、视图的DEFINER
- 修改MySQL存储过程、函数、事件、触发器、视图的DEFINER
- MySQL触发器、存储过程、自定义函数、视图的一些笔记
- mySQL之视图、触发器、事务、存储过程、函数、流程控制
- Mysql视图, 存储过程, 触发器, 事务
- MySQL触发器、存储过程、自定义函数、视图 常用SQL
- MySQL 系列(三)你不知道的 视图、触发器、存储过程、函数、事务、索引、语句
- MYSQL的基本使用【表的创建和修改、视图、函数、存储过程、触发器和事件调度器】
- 查看MySQL所有存储过程、函数、视图、触发器及表
- 修改MySql的存储过程、函数、事件、触发器、视图的 DEFINER
- 表、视图、存储过程、函数、触发器、事件
- MySQL修改存储过程和函数、事件、触发器、视图的DEFINER
- mysql 存储过程、触发器、视图、游标、分布式事务--之触发器
- mysql常用表数据操作
- [ 线段树套treap ] [ TJOI2016&&HEOI2016 ] BZOJ4552
- hdu--1863--畅通工程
- clone
- 第四周实践项目7 多项式求和
- MySQL事务、存储过程、视图、自定义函数、触发器、事件的简单使用及理解
- unity 调用android方法
- C:单链表的循环和递归实现
- 那些青葱岁月回忆录
- MySQL使用explain分析sql效率
- C语言的发展历史
- http://www.jianshu.com/p/94e0f9ab3f1d
- iOS 视图样式 该怎么样复用
- 100. Same Tree