mysql 的存储过程

create trigger t1
after
insert 
on ord
for each row
begin
sql....
sql....
update goods xxxx
end;




create table goods(


gid int,
name varchar(20),
num smallint
);


create table ord(
oid int,
gid int,
much smallint
);


insert into goods values(1,'cat',34),(2,'dog',65),(3,'pig',21);




delimiter $
create trigger t1
after
insert
on ord
for each row
begin
update goods set num=num-2 where gid=1;//
end$




create trigger t2
after
insert
on ord
for each row 
begin
update goods set num=num-new.much where gid=new.gid;
end;




查看已有的triggers:
show triggers;


删除已有的triggers:
drop trigger triggername;


//清理表
truncate ord;


create trigger t3
after 
delete
on ord
for each row 
begin
update goods set num=num+old.much where gid=old.gid;
end;
delimiter $
create trigger t4
before
update
on ord
for each row 
begin
update goods set num=num+old.much - new.much where gid=new.gid;
end$




delimiter $
create trigger t5
after
insert
on ord


for each row 
begin


declare rnum int;
select num into rnum from goods where gid=new.gid;


if new.much > rnum then
set new.much=rnum;


end if;


update goods set num=num - new.much where gid=new.gid;


end$


//after 之后已经没有new 了。
//Updating of NEW row is not allowed in after trigger
//因此应该这样写：


delimiter $
create trigger t5
before
insert
on ord


for each row 
begin


declare rnum int;
select num into rnum from goods where gid=new.gid;


if new.much > rnum then
set new.much=rnum;


end if;


update goods set num=num - new.much where gid=new.gid;


end$


触发器中的for each row:


在oracle 触发器中：
分语句级触发器和行级触发器；
行级触发器：影响多少行就出发多少次
create table temp(
id int
)


create trigger t4
before 
update 
on ord
for each row //在oracle中如果不写，无论update语句一次影响了多少行，都只执行1次
begin
insert into tmp values(5);
end$


比如：1人下了订单，买了5件商品，insert5次，可以用行级触发器，修改5次库存
用语句触发器，insert一条发货提醒。


遗憾的是：mysql目前不支持语句级触发器。


存储过程：
把若干条sql语句封装起来，起个名字----过程
把此过程存储在数据库中----存储过程


create procedure procedureName()
begin
   --sql 语句
end$




查看已有的procedure
show procedure status$


调用存储过程：
call procedureName()


在存储过程中用declare来声明变量
格式：declare 变量名 变量类型 [default 默认值]
create procedure p2()
begin
   declare age smallint default 18;
   declare height int default 180;


   select concat('年龄是',age,'身高是',height);
end$


存储过程中，变量可以sql语句中合法的运算，如++--
注意的是，运算的结果，如何赋值给变量
set 变量名 :=expression


create procedure p3()
begin
   declare age smallint default 18;
   set age:=age+20;


   select concat('20年后年龄是',age);
end$


if/else 控制语句
if confition then 
statement
else
end;


create procedure p4()
begin
declare age int default 18;
if age>=18 then
select '已经成年';//select 将变量写入out参数
else 
select '未成年';
end if;
end$


给存储过程传递参数：
/*
存储过程的括号里，可以声明参数，
语法是[in/out/inout]参数名 参数类型
**/
create procedure p5(width int,height int)
begin

select concat('你的面积是',width * height) as area;
if width > height then
select '你挺胖';
elseif width < height then //注意这里elseif要写在一起
select '你挺瘦';
else 
select '你挺方';
end if;
end$


p6控制结构
顺序，选择，循环
求1-100之和


create procedure p6()
begin 
declare total int default 0;
declare num int default 0;


while num < 100 do
set total :=total+num;
set num :=num+1;
end while;
select total;
end$


p7 求1到N的和
create procedure p6(N int)
begin 
declare total int default 0;
declare num int default 0;


while num < N do
set total :=total+num;
set num :=num+1;
end while;
select total;
end$




create procedure p8(in n int,out total int)
begin
declare num int default 0;
set total :=0;
while num < n do
set num :=num+1;
set total :=total + num;
end while;
end$
//调用
call p8(100,@sum)$
//显示
select @sum$


create procedure p9(inout age int)
begin
set age :=age+20;
end$


call p9(@current)$


select @current$


create procedure p10()
begin

declare pos int default 0;


set pos := floor(5*rand());
case pos 
when 1 then select '仍在非';
when 2 then select '掉进大海中了';
when 3 then select '已经在地上';
else select '我不知道';
end case;
end$


createt procedure p11()
begin
declare total int default 0;
declare i int default 0;


repeat 


set i :=i+1;
set total :=total+i;
util i>=100 end repeat;
select total;
end$


游标：cursor


1条sql，对应N条结果集的资源，取出资源的接口/句柄，就是游标
沿着游标，可以一次取出1行


declare 声明：declare 游标名 cursor for select_statement;
open 打开；open 游标名
fetch 取值;fetch 游标名 into var1,var2,[.....]
close 关闭；close 游标名；


create procedure p12()
begin
//变量的声明要在游标之前 
declare row_gid int;
declare row_num int;
declare row_name varchar(20);


declare getgoods cursor for select * from goods;
open getgoods;


fetch getgoods into row_gid,row_num,row_name;//吧取得的值存入变量

select row_num,row_name;
close getgoods;
end$


create procedure p12()
begin
//变量的声明要在游标之前 
declare row_gid int;
declare row_num int;
declare row_name varchar(20);


declare getgoods cursor for select * from goods;
open getgoods;


fetch getgoods into row_gid,row_num,row_name;//吧取得的值存入变量

select row_num,row_name;
close getgoods;
end$


create procedure p12()
begin
//变量的声明要在游标之前 
declare row_gid int;
declare row_num int;
declare row_name varchar(20);


declare getgoods cursor for select * from goods;
open getgoods;


fetch getgoods into row_gid,row_num,row_name;//吧取得的值存入变量
select row_num,row_name;//正常取值

fetch getgoods into row_gid,row_num,row_name;//吧取得的值存入变量
select row_num,row_name;//正常取值

fetch getgoods into row_gid,row_num,row_name;//吧取得的值存入变量
select row_num,row_name;//正常取值

fetch getgoods into row_gid,row_num,row_name;//吧取得的值存入变量
select row_num,row_name;//游标到头之后会报错。
close getgoods;
end$




游标与循环结合：


create procedure p14()
begin


declare row_gid int;
declare row_num int;
declare row_name varchar(20);


declare cnt int default 0;
declare i int default 0;

declare getgoods cursor for select  gid,num,name from goods;

select count(*) into cnt from goods;

open getgoods;


repeat
set i :=i+1;
fetch getgoods into row_gid,row_num,row_name;


until i>=cnt end repeat;
close getgoods;
ends$




游标取值越界时，有没有标识？利用标识来结束
在mysql cursor中，可以declare continue handler 来操作1个越界标识
declare continue handler for NOT FOUND statement;


create procedure p14()
begin


declare row_gid int;
declare row_num int;
declare row_name varchar(20);


declare i int default 0;

declare getgoods cursor for select  gid,num,name from goods;

declare continue handler for NOT FOUND set you:=0;

open getgoods;


repeat
set i :=i+1;
fetch getgoods into row_gid,row_num,row_name;


until you=0 end repeat;


close getgoods;
ends$
//执行完之后发现多取了一行。
//这样改：
create procedure p14()
begin


declare row_gid int;
declare row_num int;
declare row_name varchar(20);


declare i int default 0;

declare getgoods cursor for select  gid,num,name from goods;

declare exit handler for NOT FOUND set you:=0;

open getgoods;


repeat
set i :=i+1;
fetch getgoods into row_gid,row_num,row_name;


until you=0 end repeat;


close getgoods;
ends$


除了continue，exit，还有一种undo handler,
continue 是触发后，后面的语句继续执行
exit是触发后，后面的语句不再执行，
undo是触发后，前面的语句撤销(mysql目前还不支持)


create procedure p17()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);


declare you int default 1;
declare getgoods cursor for select gid,num,name from goods;
declare continue handler for NOT FOUND set you :=0;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;

repeat
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;
until you=0 end repeat;

close getgoods;
end$




mysql权限检察院里：
【用户】<------>[服务器]
分为2个阶段：
1：有没有权限连接上来
2：有没有权执行此操作(select ,update等)


对于1：服务器如何判断用户有没有权连接上来？
依据3个参数：
你从哪儿来？host
你是谁？ user
你的密码是多少？password


在mysql的user表中保留有之前的链接信息。
可以通过更改表中的信息来设置新的连接
update user set host='192.168.1.10' where user='root';
//刷新一下权限
flush privileges;


这样就可以通过mysql -h192.168.1.10 -uroot -proot 连接了。
//新增一个用户
grant [权限1，权限2.。。] on *.* to user@'host' identify by 'password';


常用权限 all,create,drop,insert,delete,update,select 


grant all on *.* to lisi@'192.168.1.%' identified by '111111'; 


权限1,权限2,…权限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。
当权限1,权限2,…权限n被all privileges或者all代替，表示赋予用户全部权限。
当数据库名称.表名称被*.*代替，表示赋予用户操作服务器上所有数据库所有表的权限。
用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以用’%'表示从任何地址连接。
‘连接口令’不能为空，否则创建失败。




select * from user where user='lisi' \G


收回权限：
revoke all on *.* from lisi@'192.168.1.%';


针对某个库授权：
grant all on ecshop.* to lisi@'192.168.1.%';




主从复制：


主 master 从slave




主服务器要配置binlog
从服务器要配置relaylog


从服务器如何有权读取master的binlog?
(binlog是比较敏感的)
答：授权master要授予slave账号


从服务器用账号连接master