存储程序
a)运行于服务器程序
b)优点
i 简化开发
ii 执行效率高
c)缺点
i 程序保存在服务器端,占用服务器(数据库)资源
ii 数据迁移(所有的存储程序都需要迁移)
iii 调试,编写程序不方便
d)分类
i 存储过程:有输入输出参数,可以执行一组sql命令。
ii 存储函数:有一个返回值,可以对sql进行有效的扩展
iii 触发器:置顶时间相应,比如执行insert 语句后执行另一个动作
#存储过程
#服务器端运行的可重复调用的sql代码,包含名称,输入输出参数,一组sql
#创建存储过程
#查询所有的用户名称和部门名称
#修改结束标示
delimiter //;
create procedure sel_emp()#括号内是输入输出参数
begin
#sql
-- select dname ,enaem from emp ,dept where dept.deptno = emp.deptno;
select * from classroom;
end;
call sel_emp();#调用存储过程
create procedure sel_classroom()#括号内是输入输出参数
begin
#sql
-- select dname ,enaem from emp ,dept where dept.deptno = emp.deptno;
select * from classroom;
end;
call sel_classroom()
#参数的传入(in 传入模式(默认),out传出模式 ,inout传入传出模式)
#根据部门编号查询员工(传入模式)
delimiter //;
create procedure findEmpByNo(in dno int)
begin
select *from emp where deptno = dno;
end;
call findEmpByNo(10);
#根据班级id查询班级中所有学生
delimiter //;
create procedure findStudentByCid(classid int)
begin
select * from student where cid = classid;
end;
call findStudentByCid(1);
#根据学生编号查询学生姓名,将姓名返回出去
delimiter //;
create procedure findNameByid(stuId int,out v_name varchar(20))
begin
select sname into v_name from student where sid = stuId;
end;
call findNameByid(1,@v_name);
select @v_name;
#根据学生姓名查询所在班级名称(参数为inout)
delimiter //;
create procedure findClass(inout name_class varchar(20))
begin
select cname into name_class from classroom c, student s where s.sname =name_class and s.cid = c.cid;
end;
set @name_class='lmk';
call findClass(@name_class);
select @name_class;
#if(根据学生成绩分级)
delimiter //;
create procedure score_level(score int )
begin
declare s_level varchar(20); #声明变量
if score >=80 then
set s_level = 'A';
elseif score >=60 then
set s_level = 'B';
else
set s_level = 'C';
end if;
select s_level;
end;
call score_level(37);
delimiter //;
create procedure score_level_withSid(s_sid int)
begin
declare v_score varchar(20); #声明变量
declare s_level varchar(20); #声明变量
select score into v_score from student where sid = s_sid;
if v_score >=80 then
set s_level = 'A';
elseif v_score >=60 then
set s_level = 'B';
else
set s_level = 'C';
end if;
select s_level;
end;
call score_level_withSid(1);
while 条件 do
...
end while
#循环(1+2——。。。+100)
delimiter //;
create procedure whileCalc()
begin
declare i int;
declare sum int;
set i=1;
set sum=0;
while i<= 100 do
set sum = sum + i;
set i = i + 1;
end while;
select sum;
end;
call whileCalc();
loop #死循环
判断 leave #离开循环
end loop
#循环(1+2——。。。+100)
delimiter //;
create procedure loopCalc()
begin
declare i int;
declare sum int;
set i=1;
set sum=0;
lip:loop #添加别名
set sum = sum +i;
set i = i +1;
if i >100 then
leave lip;
end if;
end loop;
select sum;
end;
call loopCalc();
repeat
判断 until
end repeat;
delimiter //;
create procedure repeatCalc()
begin
declare i int;
declare sum int;
set i=1;
set sum=0;
repeat
set sum = sum +i;
set i = i +1;
until i > 100 #这里不要加分号
end repeat;
select sum;
end;
call repeatCalc();
# 存储函数(函数)
存储在服务器端 ,有返回值,函数作为sql一部分使用
#根据学生id查询学生姓名
delimiter //;
create function findNameByNo(s_num int)
returns varchar(20) #返回值类型
deterministic #确定的
begin
declare v_name varchar(20);
select sname into v_name from student where sid = s_num;
return v_name;
end;
select findNameByNo(2);
函数和存储国琛区别
1关键字不同;
2存储过程三种参数模式实现数据输入输出,函数有返回值返回数据;
3.存储过程可以作为独立个体执行,函数只能作为sql的一部分执行;
#触发器(不要添加过多的触发器,降低查询效率)
存储程序,存储在服务器上 ,不能使用事务
由事件(增,删,改)调用,不能传参
用户表userinfo
注册:插入(insert)用户信息到userinfo表
注销:非Delete 用update
触发器可以在Delete 的时候将数据插入到另一张表中
语法:
#备份userinfo数据中的删除数据到user_bak表中;
create table user_bak select * from userinfo where 1;
#创建触发器,删除操作后会自动触发触发器
create trigger tri_user
after delete #触发器时间,事件
on userinfo for each row #添加行级触发器
begin
#old删除的记录 new插入/更新的数据
insert into user_bak values(old.uid,old.uname,old.card,old.password);
end;
delete from userinfo where uid =2;