数据库之视图，触发器，事务，存储过程

视图

　　视图是一条sql语句的查询结果构成的虚拟表

　　其不是物理存在的，使用方式与普通表相同

 

　　视图的作用

　　1.简化sql语句的编写

　　2.限制可以查看的数据

　　　　可以使用权限来完成,权限某一个库的某一个表的某一个字段

　　　　例如：一张表存储了所有人的工资，但是每个员工只能看自己的工资信息，那就可以用视图来限制

 

　　视图的特点：

　　　　对于视图的增删改查，都会同步到原始表

　　　　每一次对视图的查询，本质上都是执行了之前创建视图时指定的sql语句

 

　　　　对于原始表的修改也能在视图中查看到，前提是你修改的数据包含在创建视图时指定的sql语句中

　　　　select *from users where name = toms;

 

　　创建视图的语法

　　　　create [or replace] view view_name as sql语句;

　　修改

　　　　alter view view_name as sql语句

　　删除

　　　　drop view view_name;

 

触发器

　　是一段与某个表有关的mysql程序

　　当达到某种条件时，触发某个东西的执行

 

　　条件

　　　　时间点　　before  after

　　　　具体事件

　　　　　　update   |   delete   |   insert

　　　　　　new old   |   old       |   new

 

　　达到条件后：

　　　　自动执行一段mysql程序

 

　　作用

　　　　做一个博客系统

　　　　需要在你执行更新时，自动记录更新时间，以及更新具体内容

 

　　　　python是什么玩意儿？（第一次编辑）

　　　　python是什么玩意？是一门变成语言（第二次编辑）

　　　　总的来说就是可以帮你在一个表被修改时做一些额外的操作

 

　　语法：

　　　　create trigger name after delete on tablename for each row

　　　　begin

　　　　　　具体要执行的操作

　　　　end

 

　　在触发器中有两个隐藏对象

　　　　new（新的数据）和old（旧的数据）

　　案例1：　　

创建一个博客表
    在blog表发生了update事件时，要自动将更新后的数据插入到记录表
重新定义结束符号 // 因为触发器中包含 ; 而分号是默认的结束符

delimiter //
create trigger t1 after update on blog for each row
begin
    insert into update_log values(null,new.content,now(),new.id);
end //
delimiter;
select *from users //

　　案例2：

cmd表
存储的是什么时间执行了什么指令，结果是什么？
errlog表存储的是所有执行失败的指令信息
当数据插入cmd表时判断如果success为no就将这个信息插入errlog表中

delimiter ||
create trigger t3 after insert on cmd for each row
begin
    select *from cmd;
    if new.success = "no" then
        insert into errlog values(null,new.cmd,now());
    end if;
end ||
delimiter;

 

触发器不会影响原表的操作，只会增加一些额外的操作

 

注意：

a和b

b有外键，关联a的主键，并且设置级联，删除和更新

当删除a中的记录时，不会触发b表触发器

 

mysql最想干的事情就是把与数据相关的所有逻辑代码全部放在mysql中

这样应用程序的操作就减少了

 

事务

　　事务是逻辑上一组sql语句的集合

　　特点是：一个事务的所有sql语句，要么全部执行成功，要么全部执行失败

 

　　一个转账操作

　　从a账户减去100

　　update account set money = money - 100 where name = "Dante";

　　再给b账户加上100

　　update account set money = money + 100 where name = "Lady";

 

　　使用案例

　　　　开启事务

　　　　start transaction;

　　　　

　　　　一堆sql

　　　　update account set money = money - 100 where name = "Dante";

　　　　update account set money = money + 100 where name = "Lady";

 

　　　　如果发生一些错误可以回滚

　　　　rollback;

　　　　如果没有问题就提交，只有执行了提交操作，你的修改才会生效

　　　　commit

　　　　一旦commit就无法再回滚了

　　　　

　　　　rollback是全部撤回（回滚）

　　　　savepoint可以创建保存点（相当于游戏的存档）可以选择性的回滚一部分

　　

　　事务最强大的地方在于

　　　　当发生了一些不可控因素时（地震之类）可以保证数据是完整的（原子性）

 

　　事务最强大的地方在于

　　　　原子性

　　　　　　一个事务是一个整体，不可拆分，要么都成功，要么都失败

　　　　一致性

　　　　　　一个事务无论成功或失败，相关数据约束一定是完整的

　　　　隔离性

　　　　　　与效率息息相关

　　　　　　两个事务之间相互独立

　　　　　　

　　　　　　read_uncommitted --不做任何隔离，可能脏读（读取到其他事务未提交的数据）

 

　　　　　　read_committed --可以防止脏读，不能防止不可重读（并发修改），和幻读（并发添加和删除）

　　　　　　　　对于同一条记录，同一事务的两次查询结果不一致，称之为不可重复读

　　　　　　　　start transaction

　　　　　　　　select *from account where name = "张三";

　　　　　　　　select *from account where name = "张三";

　　　　　　　　原因是：一个事务在查询数据，另一个在更新数据

　　　　

　　　　　　　　幻读

　　　　　　　　select count（*） from account; 本来有8人

　　　　　　　　一段时间后

　　　　　　　　select count（*） from account; 本来有9人

　　　　　　　　本质原因：一个事务在查询，另一个事务在添加/删除数据

 

　　　　可重复读

　　　　Repeatable_read --可以防止脏读，不可重复读（加锁保证，查询事务和更新事务不能并发），不能防止幻读

　　　　　　insert/delete和查询事务，可以并发执行

 

　　　　最严格的锁

　　　　Serializable--数据库运行在串行化实现，所有问题都没有，就是性能低

 

　　永久性

　　　　一个事务一旦提交就是永久性，无法回滚

 

　　查看当前隔离级别

　　select @@tx_isolation;

　　设置新的隔离级别

　　set global transaction isolation level Serializable;

 

存储过程

　　一个存储过程中包含任意sql语句，以及流程控制，事务等等...

　　简单的就是说，将一系列较为复杂的逻辑封装到了mysql中

　　好处：

　　　　例如要是注册功能

　　　　接收用户名，密码

　　　　判断用户名是否存在，需要编写一条sql语句，传给服务器，服务器执行完毕返回结果

　　　　客户端再发起请求，把用户名和密码传给服务器

　　　　

　　　　经历了至少2次网络传输

　　　　如果使用存储过程就可以一次性将用户名和密码传给服务器，等到接收结果即可

　　　　降低了网络传输，提高了效率

 

　　弊端：学习成本高，运营成本高，沟通成本高，三高

　　正常开发中有三种常见方式完成数据库相关操作

　　　　1.应用程序开发者，只关注业务逻辑，数据相关的逻辑交给存储过程

　　　　　　优点：优化了网络传输，耦合度降低

　　　　　　弊端：学习成本高，运营成本高，沟通成本高，三高，存储过程移植性非常差，每种数据语法都不同

　　　　2.应用程序开发者，不仅关注业务逻辑，还需要编写原生的sql语句

　　　　　　优点：一个人负责所有，没有沟通成本

　　　　　　缺点：sql语句编写繁琐，导致开发效率降低

　　　　3.应用程序开发者仅关注业务逻辑，把sql语句相关的交给ORM（对象关系映射，帮你封装了增删改查，自动生成sql语

　　　　　句）

　　　　　　优点：开发效率提高了

　　　　　　缺点：执行效率降低

 

　　　　番外：摩尔定律，每过18个月 CPU的计算能力至少翻一番

　　

　　1.创建存储过程

　　　　create procedure pl（type 参数名 数据类型）

　　　　type:in输入参数 out输出参数 inout既能输出又能输入

　　需求：编写一个存储过程，查询 account表，可以指定工资的去件范围

　　select *from account where money >= a and money <= b;

delimiter//
create procedure pl(in a double,in b double)
begin
    select *from account where money >= a and money <= b;
end//
delimiter;

　　

　　存储过程与函数的区别

　　函数仅仅是一个单纯工具，与数据无关，所以函数中不能出现sql语句

　　存储过程既可以包含mysql的逻辑代码，也能包含sql语句

delimiter//
create procedure p2(in a double,in b double,out res char(20))
begin
    select *from account where money >= a and money <= b;
    set res = "success"
end//
delimiter;

　　输出参数，必须是一个变量，用@定义变量