sql编程小结

对照mysql5.1手册，对这几天学的sql编程进行小结，主要涉及触发器、存储过程、权限管理、主从分离等，权当抛砖引玉，高手请略过。

一、触发器 通俗的说就是在指定的数据表增删改的前或后触发执行特定的sql语句，数据表为引用永久性表。不能将触发程序与TEMPORARY表或视图关联起来。可以从四个方面理解触发器：

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

创建语法：

create trigger 触发器名称
after/before
insert/update/delete
on 表名
for each row
begin
sql1;
..
sqlN;
end

首先声明结束符

delimiter $ ---默认是;号结尾，但是sql语句以分号结尾后执行触发器会报语法错误，所以要提前声明结束符为分号以外的

---创建测试需要的两张表

CREATE TABLE `goods` (

`gid` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`num` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8$

CREATE TABLE `orders` (

`oid` int(11) DEFAULT NULL,
`gid` int(11) DEFAULT NULL,
`much` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8$
插入测试数据

INSERT INTO goods VALUES(1,'BMW',20),(2,'mini',18),(3,'ford',50)$

---创建触发器t2,监视订单表,当订单表增加订单时,商品表就得减少相应的商品数量;

delimiter $
create trigger t2
after
insert --不允许同一张表同一时刻有两个相同的动作 
on orders
for each row
begin
update goods set num=num-new.much where gid =new.gid;-- new.much就是新增的订单表row的值;
end$

---调用

INSERT INTO orders VALUES(123,1,2)$

再次select * from goods$查看对比前后数据

-- 创建触发器 t3,监视订单表,当订单被删掉时,商品表增加对应数量;

create trigger t3
after
delete
on orders
for each row
begin
update goods set num=num+old.much where gid =old.gid;
end$

-- 创建触发器 t5,监视订单表,在订单增加前判断是否大于库存,如果大于库存量,就让其等于库存量 区别before和after

create trigger t5
before
insert
on orders

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$

 

---删除触发器

DROP TRIGGER [schema_name.]trigger_name

舍弃触发程序。方案名称（schema_name）是可选的。如果省略了schema（方案），将从当前方案中舍弃触发程序。

一般都是直接删除触发器名的

6.for each row具体介绍
行级触发器
语句级触发器

7.存储过程
相当于一个没有返回值的"函数"
create procedure procedureName()
begin
--sql 语句
end$

 

8.引入变量与控制结构
--在存储过程中 用declare声明变量
--格式 declare 变量名 变量类型[default默认值]

create procedure p3()
begin
declare age int default 18;
set age =age +20;
select concat('20年后',age)；
end$

create procedure p4()
begin
declare age int default 18;
if age >=18 then
select '已成年';
else
select '未成年';
end if;
end $

9.存储过程的参数传递

给存储过程传参
语法 [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
select '你挺瘦';
else
select '你挺方';
end if;
end$

10.过程中使用循环结构
--求1-100之和
create procedure p6()
begin
declare sum int default 0;
declare num int default 0;

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

--in型参数 input type
create procedure p7(in n int)
begin
declare sum int default 0;
declare num int default 0;

while num <n do
set num=num+1;
set sum =sum +num;

end while;
select sum;
end$

11.何为输出型参数
delimiter $
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$

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

set @nowage=12$

call p9(@nowage)$

select @nowage$

12.case 结构的运用
create procedure pten()
begin
declare pos int default 0;

set pos :=floor(5*rand());
case pos
when 1 then select 'still flying';
when 2 then select 'fall in sea';
when 3 then select 'in the island';
else select 'unkonw param';
end case;
end$

13.repeat循环结构

create procedure p11()
begin
declare i int default 0;
repeat
select i;
set i :=i+1;
until i>10 end repeat;
end$

create procedure p12()
begin
declare i int default 0;
declare sum int default 0;
repeat
set i :=i+1;
set sum :=sum+i;

until i>=100 end repeat;
select sum;
end$

14.游标的概念
--cursor 游标 游动的标志
--1条sql对应N条结果集的资源 取出资源的接口/句柄 就是游标
--沿着游标 可以一次取出1行

--declare 声明：declare 游标名 cursor for select statement
--open 打开；open 游标名
--fetch 取值；fetch 游标名 into var1，var2...
--close 关闭；close 游标名;

create procedure p13()
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_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$

--02000

15 游标循环
--way1
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;
select row_num,row_name;
until i>=cnt end repeat;
close getgoods;
end$

call p14()$ call p14$
16.declare 处理条件

--越界值

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

declare rhave int default 1;

declare getgoods cursor for select gid, num,name from goods;
declare continue handler for NOT FOUND set rhave :=0;

open getgoods;

repeat

fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
until rhave=0 end repeat;
close getgoods;
end$

17.对比continue与exit的区别

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

declare rhave int default 1;

declare getgoods cursor for select gid, num,name from goods;
declare exit handler for NOT FOUND set rhave :=0;

open getgoods;

repeat

fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
until rhave=0 end repeat;
close getgoods;
end$

18.游标循环读取的正确逻辑
--declare getgoods cursor for select gid, num,name from goods; 不一定取出数据
create procedure p17()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);

declare rhave int default 1;

declare getgoods cursor for select gid, num,name from goods;
declare exit handler for NOT FOUND set rhave :=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 rhave=0 end repeat;
close getgoods;
end$

19.mysql权限检查原理
[用户]<------>[服务器]
分为两个阶段
1：你有木有权限连接上来
2：你有没有权限执行操作（CURD）
对于1：服务器如何判断用户有没有权限连接上来
依据3个参数
你从哪来？host
你是谁？user
你的密码是多少？password

用户的这3个信息存储在mysql库下的user表内

--修改host域 使得ip可以连接
mysql>update user set password=password('root') where user='';
mysql>flush privileges;

20.全局授权与收回(从user全局表上)
[user]<------->[db]<------>[table-prv]
--新增一个用户
grant [权限1,权限2,权限3....] on *.* to user@'host' identified by 'password';
常用权限有all,select,create,drop,insert,delete,update...

grant all on *.* to leo@'192.168.1.%' identified by '123456';

--收回权限
revoke all on *.* from leo@'192.168.1.%';
--左边第一个*所有库
--左边第二个*所有表
flush privileges;

21.库及表级别授权与收回（db级别）

--针对某个库做授权

grant all on trdb.* to leo@'192.168.1.%';

--收回权限
revoke all on trdb.* from leo@'192.168.1.%';

--针对表级别做授权
grant select,insert,update on trdb.goods to leo@'192.168.1.%';

--mysql的权限控制可以精确到列看手册