MySQL之视图、触发器、存储过程、函数、事务、数据库锁

-- 创建视图
　　create view 视图名 as sql语句;
　　create view v1 as select id,name from userinfo;
-- 使用视图
　　select * from 视图名;
　　select * from v1;
-- 更新视图
　　alter view 视图名 as sql语句;
　　alter view v1 as select id,name from userinfo where id<10;
-- 删除视图
    drop view 视图名;
    drop view v1;

-- 创建触发器语法        
create trigger 触发器名 after/before insert/update/delete
　　on 表名 for each row  -- 这句是固定的
begin
　　-- begin和end中间放置需要执行的sql语句
end

after/before：只能选一个 , after(后置触发), before(前置触发)
insert/update/delete：只能选一个

-- 商品表
create table goods_tb(
  id int primary key auto_increment,
  name varchar(20),
  num int
);

-- 订单表
create table order_tb(
    oid int primary key auto_increment,
    gid int,
    gnum int
);

-- 添加3条商品数据
insert into goods_tb(name,num) values('商品1',10),('商品2',10),('商品3',10);

-- 1.往订单表插入一条记录
insert into order_tb(gid,gnum) values(1,3);

-- 2.更新商品表商品1的剩余数量
update goods_tb set num=num-3 where id=1;

create trigger tg1 after insert
　　on order_tb for each row
begin
　　update goods_tb set num=num-3 where id=1;
end

insert into order_tb(gid,gnum) values(1,3);

insert into order_tb(gid,gnum) values(2,3);

create trigger tg2 after insert
　　on order_tb for each row
begin
　　update goods_tb set num=num-new.gnum where id=new.gid;
end

drop trigger tg1;

insert into order_tb(gid,gnum) values(2,3);

# 1.当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？
对于delete而言，即将删除的数据行用old来表示，原表本来有一行，但客户取消了交易（相当于删除了这一行数据），如果想引用被删除的这一行，用 old.字段名 可以引用删除的值。
那我们的触发器就该这样写：

create trigger tg3 after delete
　　on order_tb for each row
begin
　　update goods_tb set num=num+old.gnum where id=old.gid;
end    

delete from order_tb where id=1;

# 2.当客户修改一个订单的数量时，我们触发器修改怎么写?

create trigger tg4 after update
　　on order_tb for each row
begin
　　update goods_tb set num=num+old.gnum-new.gnum where id=old.gid;  -- new表示即将插入的数据行，old表示即将删除的数据行。（这里num=7+3-5）
end

update order_tb set gnum=5 where id=1;

-- 看现有的存储过程
    show procedure status;
    
-- 创建存储过程（无参数示例）
    create procedure p1()
    begin
    select * from t1;
    end
    
-- 调用存储过程
    call 存储过程名称(参数类型 参数名 数据类型);

-- 删除存储过程
    drop procedure 存储过程名称;

create procedure p2(in i int,inout io varchar(50))
begin
update goods_tb set name=io where id=i;
end

set @io="商品100";   # 设置初始值
call p2(3,@io);     # 执行存储调用
select @io;         # 拿到结果

 

create procedure p3(in i int,out o varchar(50))
begin
select name into o from teacher where id = i;
end

set @name=null;
call p3(1,@name);
select @name;

create procedure p4(in flag char(5),in num int)
begin
    if flag='true' then
        select name from teacher where num>id;
    elseif flag='false' then
        select name from teacher where num<id; 
    end if;  # 记得结束if
end

call p4("false",3)

create procedure p5(inout n int)
begin
    declare sum int default 0;  -- 声明总和变量,并且指定初始值0
    declare i int;  -- 声明变量i
    set i=0;        -- 通过set,为变量设置值
    while i<=n do   -- 开始循环
        set sum=sum +i;
        set i=i+1;
    end while;      -- 结束循环
    select sum;     -- 提供结果
    set n=sum;      -- 将计算结果提供给输出变量n;
end;

set @n=100;
call p5(@n);
select @n;

# 存储过程优点：
    1.存储过程增强了SQL语言灵活性
        存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性
        
    2.减少网络流量，降低了网络负载
        存储过程在服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行
        
    3.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译
        一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度

# 存储过程缺点：     
    1.扩展功能不方便
    2.不便于系统后期维护

-- 创建自定义函数
create function func1(
    i1 int,
    i2 int)
returns int  -- 设置返回类型 
begin
    declare num int;  -- 声明num类型
    set num=i1+i2;
    return(num);
end

-- 调用自定义函数
select func1(1,5);

select func1(参数1,参数2),字段名 from 表名;

drop function 自定义函数名;

begin或start transaction    -- 开启一个事务
commit       -- 提交事务，并使已对数据库进行的所有修改称为永久性的
rollback     -- 回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
savepoint    -- 保存点，可以把一个事物分割成几部分；在执行 rollback 时可以指定在什么位置上进行回滚操作

PS: set autocommit=0（禁止自动提交）
    set autocommit=1（开启自动提交）

-- 创建表
create table account(
    id int(50) not null auto_increment primary key,
    name varchar(50) not null,
    money decimal(10,2) not null);

-- 插入数据
insert into account(name,money) values("A",2000),("B",1000);

-- 执行转账
start transaction;  -- 开启事物
    -- 执行sql语句操作
    update account set money=money-500 where id=1;  
    update account set money=money+500 where id=2;
commit;  -- 手动提交事物
select * from account;  -- 查看结果 

-- 保存点使用
start transaction;
    insert into account(name,money) values("C",1111);
    savepoint sp1;  -- 设置保存点 
    insert into account(name,money) values("D",2222);
rollback to sp1;  -- 事物回滚到保存点
commit;
select * from account;

-- A进行充值（窗口1）

-- 充值前，先查看余额
set @m=0;
select money into @m from account where name="XX账户";
select @m;

-- 看到余额后，充值100块
update account set money=@m+100 where name="XX账户";
select * from account;


-- B进行取款（窗口2）

-- 取款前，先查看余额
set @m=0;
select money into @m from account where name="XX账户";
select @m;

-- 看到余额后，取款100块
update account set money=@m-100 where name="XX账户";
select * from account;

-- A进行充值（窗口1）
start transaction;  -- 开始事务1

-- 充值前，先查看余额
set @m=0;
select money into @m from account where name="XX账户" for update;
select @m;

-- 看到余额后，充值100块
update account set money=@m+100 where name="XX账户";
commit;
select * from account;


-- B进行取款（窗口2）
start transaction;  -- 开始事务2

-- 取款前，先查看余额
set @m=0;
select money into @m from account where name="XX账户" for update;
select @m;

-- 看到余额后，取款100块
update account set money=@m-100 where name="XX账户";
commit;
select * from account;

-- A进行充值

-- 充值前，先查看余额
set @m=0;
select money into @m from account where name="XX账户";
select @m;
-- 查询版本号
set @version=0;
select version into @version from account where name="XX账户";
select @version;
 
-- 看到余额后，充值100块
update account set money =@m+100,version=version+1 where name="XX账户" and version=@version;
select * from account;


-- B进行取款（窗口2）

-- 取款前，先查看余额
set @m=0;
select money into @m from account where name="XX账户";
select @m;
-- 查询版本号
set @version=0;
select version into @version from account where name="XX账户";
select @version;
 
-- 看到余额后，取款100块
update account set money =@m-100,version=version+1 where name="XX账户" and version=@version;
select * from account;

悲观锁与乐观锁的优缺点:
　　两种锁各有其优点缺点，不能单纯的讲哪个更好；
　　乐观锁适用于写入比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量；
　　但如果经常产生冲突，上层应用会不断的进行重试操作，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。