mysql部分总结

// 监视地点table
// 监视事件 insert/update/delete
// after/before
// 触发事件 insert/update/delete

create trigger t1
after insert
on ord
for each row
begin 
update goods  XXX;
end;

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

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

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


// 引用参数触发器
create TRIGGER t2 
after insert 
on ord
for each ROW
begin 
update goods set num=num-new.much WHERE new.gid=gid;
END;

// ---------------------------------------
CREATE TRIGGER t4 
BEFORE 
update 
on ord
for each row 
begin 
update goods set num = num+old.much -new.much where gid = old .gid;
end;





// ----------------------------------------
;Updating of NEW row is not allowed in after trigger
// 在new行插入之前就要改
// insert :new 
// 原因：insert 之后,new行已经插入到表中，成为事实，改new已经晚了
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;

// 过程：封装若干条语句，调用时这些封装体执行
// 函数：是有返回值的过程 过程没有返回值
// 若干条sql语句封装起来----过程    把此过程存储在数据库中--存储过程

create procedure procedureName()
begin

end;

// declare 变量名 变量类型 default 默认值

create p2()
begin 
declare age int  default 18;
declare height int default 180;

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

end;

-- create PROCEDURE p3()
-- begin 
-- declare age int  default 18;
-- declare height int default 180;
-- set age:=age+20;
-- select concat('年龄是',age,'身高是',height);
-- end;

create procedure p4()
begin 

 declare height int default 180;
 set age:=age+20;
 select concat('年龄是',age,'身高是',height);

end;

// 存储过程传参

create procedure p5(width int,height int)
begin 

	if width>height then 
		select '你挺胖';
	elseif width < height
		select '你挺瘦';
	else 
		select '你挺方';

	end if;

end;

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;

// 游标
create procedure p()
begin 

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

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

open getgoods;

fetch getgoods into row_gid,row_num,row_name;
select row_gid,row_name;

fetch getgoods into row_gid,row_num,row_name;
select row_gid,row_name;

fetch getgoods into row_gid,row_num,row_name;
select row_gid,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 ;

	open getgoods;

	repeat 
	set i=i+1;
	fetch getgoods into ...;
	until i>=cnt end repeat;

end;

//mysql结束标志
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 have int default 1;
	declare exit handler for NOT FOUND set have = 0;
	// declare continue handler for NOT FOUND set have = 0;
	//判断标志  最后一次执行repeat  fetch->无数据->触发not found ->set have=0 
	// ->continue->继续执行（后面sql语句）->select  row_num,row_gid,row_name->最后一行被取出两次
	//用 declare exit handler for NOT FOUND set have = 0;替换
	
	// continue 是触发后，后面语句继续执行
	// exit是触发后，后面语句不再执行
	// undo是触发后，后面语句不再执行 目前mysql不支持

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

	open getgoods;

	repeat 
	
	fetch getgoods into row_gid,row_num,row_name;

	until have=0 end repeat;//最后一条记录会出现两次

end;

// 游标循环读取正确逻辑
create procedure p16()
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 have int default 1;
	
	declare getgoods cursor for select gid,num,name from goods ;
	declare exit handler for NOT FOUND set have = 0;

	open getgoods;
	fetch getgoods into row_gid,row_num,row_name;

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

end;

//用户连接到mysql 并做各种查询

[用户]<!----->[服务器]
分为两个阶段:

1、你有无权连接上来
2、你有无权执行此操作（select、update等）

对于1 ：服务器如何判断：
3个参数：
1、从哪儿来host 
2、你是谁 user 
3、密码多少 pwd 
用户3个信息存储在mysql下的user表下
// 修改host域 使IP能连接
update user set host='192.168.1.101' where user = 'root';
flush privileges;// 刷新权限 flush privileges;
// 修改用户密码
update user set password=password('111111') where xxxx;
flush privileges;

// 新增一个用户
grant [权限1，权限2] on *.* to user@'host' identified by 'password';
常用权限 all,create,drop,insert,delete,update,select; 
grant all on *.* to lisi@'192.168.1.%' identified by '111111'; //%通配

// 收回权限
revoke all on *.* form lisi@'192.168.1.%';

//针对某个库授权
grant all on test2.*  to lisi@'192.168.1.%' ;
revoke all on test2.* from ...;
// 针对某个表授权
grant  create,update,insert,select on test2.goods to lisi@...;

总结：
1、user表 看看让不让连
2、db级
3、tables_priv级

// 主从复制原理
1、主服务器配置binlog
2、从服务器配置relaylog
3、从服务器如何有权读取master的binlog？(binlog比较敏感)
授权 master要授予slave账号
4、从服务器用账号连接master