mysql事务、触发器、视图、存储过程、函数
存储过程: procedure
概念类似于函数,就是把一段代码封装起来,
当要执行这一段代码的时候,可以通过调用该存储过程来实现.
在封装的语句体里面,可以用if/else, case,while等控制结构.
可以进行sql编程.
查看现有的存储过程:
Show procedure status
删除存储过程
Drop procedure 存储过程的名字
调用存储过程
Call 存储过程名字();
第1个存储过程 ,体会"封装sql"
第2个存储过程, 体会"参数"
第3个存储过程,体会"控制结构"
第4个存储过程,体会"循环"
在mysql中,存储过程和函数的区别,
一个是名称不同,
二个就是存储过程没有返回值.
mysql函数
触发器:
能监视: 增,删,改
触发操作: 增,删,改
四要素:
监视地点
监视事件
触发时间
触发事件
创建触发器的语法
Create trigger triggerName
After/before insert/update/delete on 表名
For each row #这句话是固定的
Begin
Sql语句; # 一句或多句,insert/update/delete范围内
End;
删除触发器的语法:
Drop trigger 触发器名
查看触发器
Show triggers
如何在触发器引用行的值
对于insert而言, 新增的行 用new 来表示,
行中的每一列的值 ,用new.列名来表示.
对于 delete来说, 原本有一行,后来被删除,
想引用被删除的这一行,用old,来表示, old.列名,就可以引用被删行中的值.
对于update来说,
被修改的行,
修改前的数据 ,用 old来表示, old.列名引用被修改之前行中的值
修改后的数据,用new 来表示, new.列名引用被修改之后行中的值
事务:
通俗的说事务: 指一组操作,要么都成功执行,要么都不执行.---->原子性
在所有的操作没有执行完毕之前,其他会话不能够看到中间改变的过程-->隔离性
事务发生前,和发生后,数据的总额依然匹配----->一致性
事务产生的影响不能够撤消------>持久性
如果出了错误,事务也不允许撤消, 只能通过"补偿性事务"
转账
李三: --->支出 500, 李三 -500
赵四: ---->收到500, 赵四 +500
关于事务的引擎:
选用innodb /bdb
开启事务: start transaction;
Sql....
Sql....
Commit 提交
rollback 回滚
注意: 当一个事务commit,或者rollback就结束了
注意:有一些语句会造成事务的隐式的提交,比如 start transaction
事务 的基本原理 (了解)
触发器:trigger, 枪击,扳击,引线的意思
一触即发,
作用: 监视某种情况并触发某种操作.
观察以下场景:
一个电子商城,
商品表, g
|
主键 |
商品名 |
库存 |
|
1 |
电脑 |
28 |
|
2 |
自行车 |
12 |
订单表, o
|
订单主键 |
商品外键 |
购买数量 |
|
1 |
2 |
3 |
|
2 |
1 |
5 |
完成下单与减少库存的逻辑
Insert into o (gid,num) values (2,3); // 插入语句
Update g set goods_num = goods_num - 3 where id = 2;// 更新过程
这两个逻辑可以看成一个整体,或者说, insert ---> 引来 update
用触发器可以解决上述问题.
我们可以监视某表的变化,当发生某种变化时,触发某个操作.
视图: view
查询每个栏目最贵商品 --->
Select goods_id,goods_name,shop_price from goods order by cat_id asc ,shop_price desc
查询结果 当成一张表看
如果某个查询结果出现的非常频繁,也就是,拿这个结果当做进行子查询出现的非常频繁,
Create table g2 like goods;
Insert into g2 select ........
上面两句,是想保存一个查询结果到表里面, 供其他查询用.
视图的定义:
视图是由查询结果形成的一张虚拟表.
视图的创建语法:
Create view 视图名 as select 语句;
视图的删除语法:
Drop view
为什么要视图?
答:1:可以简化查询
2: 可以进行权限控制
把表的权限封闭,但是开放相应的视图权限,视图里只开放部分数据
3: 大数据分表时可以用到
比如,表的行数超过200万行时,就会变慢,
可以把一张的表的数据拆成4张表来存放.
News表
Newsid, 1,2,3,4
News1,news2,news3,news4表
把一张表的数据分散到4张表里,分散的方法很多,
最常用可以用id取模来计算.
Id%4+1 = [1,2,3,4]
比如 $_GET['id'] = 17,
17%4 + 1 = 2, $tableName = 'news'.'2'
Select * from news2 where id = 17;
还可以用视图, 把4张表形成一张视图
Create view news as select from n1 union select from n2 union.........
视图的修改
Alter view as select xxxxxx
视图与表的关系
视图是表的查询结果,自然表的数据改变了,影响视图的结果.
视图改变了呢?
0: 视图增删改也会影响表
1: 但是,视图并是总是能增删改的.
答: 视图的数据与表的数据 一一对应时,可以修改.
对于视图insert还应注意,
视图必须包含表中没有默认值的列.
视图的algorithm
Algorithm = merge/ temptable/ undefined
Merge: 当引用视图时,引用视图的语句与定义视图的语句合并.
Temptable:当引用视图时,根据视图的创建语句建立一个临时表
Undefined:未定义,自动,让系统帮你选.
Merge,意味着视图只是一个规则,语句规则, 当查询视图时,
把查询视图的语句(比如where那些)与创建时的语句where子句等合并,分析.
形成一条select 语句.
例: 创建视图的语句:
MySQL> create view g2 as select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;
查询视图的语句:
select * from g2 group by cat_id;
最终执行的语句:
select goods_id,cat_id,goods_name,shop_price from goods group by cat_id order by cat_id asc,shop_price desc;
而temptable是根据创建语句瞬间创建一张临时表,
然后查询视图的语句从该临时表查数据.
create altorethm=temptable view g2 as select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;
查询视图的语句:
select * from g2 group by cat_id;
最终执行的2句话: 取数据并放在临时表,然后去查临时表.
