// 监视地点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