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;
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的权限控制可以精确到列看手册

巴特西

sql编程小结

最新文章

热门文章