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