视图,触发器,事务,存储过程及其相关

1.视图 

视图是有一条sql语句的查询结果构成的虚拟表 其不是物理存在的 使用方式与普通表相同

视图的作用
1.简化sql语句的编写

2.限制可以查看的数据
    可以使用权限来完成  权限某一个库 的某一个表 的某一个字段

视图的特点:
    对于视图的增删改查  都会同步到原始表
    每一次对视图的查询 本质上都是 执行了之前创建视图时 指定的sql语句

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

创建视图 语法
    create [or replace] view   view_name  as sql语句;
修改
    alter view  view_name  as sql语句;
删除
    drop view view_name;

 

 

2.触发器

是一段与某个表有关的mysql程序 当达到某种条件时,触发某个东西的执行

条件:
    时间点   before  |   after
    具体事件
        update   |   delete   |   insert
        new old       old           new

达到条件后:
    自动执行一段mysql程序

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

语法:
    create trigger name  after  delete on tablename for each row
    begin
        # 具体要执行的操作
    end

在触发器中有两个隐藏对象
    new(新的数据)  和 old(旧的数据)

案例:
    创建一个博客表
        id title content commit_time
    更新记录表
        id content update_time b_id

    创建触发器
        在 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 //

a 和 b b 有外键 关联a的主键 并且设置级联 删除 和更新 当删除a中的记录时 不会触发b表触发器

 

mysql 最想干的事情 就是 把与数据相关的所有逻辑代码全部放mysql中 这样应用程序的操作减少了

 

 

3.事务

事务是逻辑上一组sql语句的集合 特点是:一个事务的所有sql语句 要么全部执行成功 要么全部失败

事务最强大的地方在于 当发生了一些不可控因素时(火山爆发,断电,系统崩溃,网络中断) 可以保证数据是完整的 (原子性)

事务的四个特征 原子性 一个事务是一个整体 不可拆分 要么都成功 要么都失败 一致性 一个事务无论成功或失败 相关数据的约束一定是完整的 隔离性 与效率息息相关 两个事务之间相互独立

 

 

 

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

    read_committed----可以防止脏读,不能防止不可重复读(并发修改),和幻读(并发添加和删除),

                对于同一条记录 同一事务的两次查询结果不一致   称之为不可重复度
                start transaction
                select *from account where name = "张三";   # 有1000块
                select *from account where name = "张三";   # 0
                原因是:一个事务在查询数据   另一个在更新数据

 

 

幻觉
                select count(*) from account;   本来有8个人
                一段时间后
                select count(*) from account;   本来有9个人

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

    # 可重复度
    Repeatable_read --可以防止脏读,不可重复读(加锁保证 查询事务和更新事务不能并发),不能防止  幻读
            insert/delete 和查询事务 可以并发执行

    # 最严格的锁
    Serializable--数据库运行在串行化实现,所有问题都没有,就是性能低

 

 

永久性

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

 

查看当前隔离级别

select @@tx_isolation;

 

设置新的隔离级别

set global transaction isolation level Serializable;

 

 

4.存储过程

一个存储过程中包含任意sql语句,以及流程控制,事务等等.... 简答的就说 将一系列较为复杂的逻辑封装到了mysql中 好处: 例如要是注册功能 接收用户名 密码 判断用户名是否存在 需要编写一条sql语句 传给服务器 服务器执行完毕返回结果 客户端在发起请求 把用户名 和密码传给服务器

 

 经历了至少2次网络传输
    如果使用存储过程 就可以一次性将用户名和密码传给服务器  等到接收结果即可
    降低了网络传输 提高了效率

弊端: 学习成本高,运营成本高,沟通成本高 三高
正常开发中有三种常见方式完成数据库相关操作

    1.应用程序开发者 只关注业务逻辑  数据相关的逻辑交给存储过程
        优点: 优化了网络传输  耦合度降低
        弊端:学习成本高,运营成本高,沟通成本高 三高   存储过程移植性非常差 每种数据语法都不同

    2.应用程序开发者 不仅关注业务逻辑  还需要编写原生的sql语句
        优点: 一个人负责所有 没有沟通成本
        缺点: sql语句编写繁琐 导致开发效率降低

    3.应用程序开发者 仅关注业务逻辑  把sql语句相关的交给ORM(对象关系映射 帮你封装了增删改查  自动生成sql语句)
        优点: 开发效率提高了
        缺点: 执行效率较低

1.创建存储过程
    create procedure p1(type 参数名 数据类型)

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

 需求 编写一个存储过程 查询 account 表 可以指定 工资的区间范围
 select *from account where money >= a and  money <= b;

delimiter //
create procedure p1(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 ;

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