mysql之存储
一、存储过程介绍
什么是存储过程？
存储过程是实现某个特定功能的sql语句的集合，编译后的存储过程会保存在数据库中，通过存储过程的名称可以反复的调用执行。
存储过程的优点：
1.存储过程创建后，就可以反复的调用和使用，不需要重新写复杂的sql语句
2.创建，修改存储过程不会对数据有任何的影响
3.存储过程可以通过输入参数返回输出值
4.通过存储过程中加入控制语句，可以加强sql语句的功能和灵活性
5.对于单个的增删改查语句(insert,delete,update,select)可以直接封装在一个函数体当中(或者说封装在一个集合当中),存储过程一旦创建可以直接调用,且可以重复的去调用
6. 单个的SQL语句每次执行都需要数据库进行编译,而存储过程被创建只需要编译一次,后续即可调用。
7.创建的存储过程,可以重复进行调用,可以减少数据库开发人员的工作量
8.防止SQL注入 f(x)=x+y
9、造数据（重点）
在MySQL5.0版本之后就支持存储过程
存储过程是由sql语句和控制语句组成的
二、基本格式：
delimiter // 分隔符/定格符
create procedure 存储过程名（ in/out/inout）参数
begin —开始
sql语句 ； —执行的语句
end —结束
//
call 存储过程名称（） 调用一个存储过程
如：
– create procedure E() 创建一个存储 ，并命名
– begin
– select * from dept ;
– select * from emp ;
– end
– //
call E
三、存储过程的基本语句：
删除一个存储过程
drop procedure +存储过程名称
查看单个存储过程的详情
show create procedure +存储过程名称
查看所有已经创建好的存储过程详情
show procedure status
如：show procedure status ;
查询数据库里创建了哪些存储过程
格式：
show procedure status where db=“数据库名称”
show PROCEDURE STATUS where db=‘dcs02’
四、
无参数的存储过程
delimiter //
create procedure 存储名称 （）
begin
sql语句 中使用参数
end
//
call 存储名称（参数）
举例：
delimiter //
create procedure hz1( )
BEGIN
select * from emp ;
select * from dept;
END
//
call hz1()
in 输入参数
创建一个in有输入参数的存储过程
#in 表示输入参数
delimiter //
create procedure hz2( in x int )
BEGIN
select * from emp where dept2=x;
select * from dept where dept1=x;
END
//
call hz2(101)
in 输入参数
in n int 输入参数n,n的数据类型是int
out 输出参数
创建一个out ，有输入参数的存储过程
举例1：
delimiter //
create procedure pro_5( out n int)
select sid into n from emp where name=“张三”;
end
//
call pro_5( @n)
select @n
注意：调用时没有任何输出，只能把调用的结果赋值给传参的参数，调用显示存储过程的值。
in 和out 结合使用
案例1：
delimiter //
drop procedure if exists pro1;
create procedure pro1(in n int,out age int)
BEGIN
select emp_age into age from emp where emp_id=n;
end
//
call pro1(2,@age)
select @age
案例2：
delimiter //
create procedure hz5( in x int ,out y int )
BEGIN
select class into y from student2 where english=x;
END
//
call hz5(56,@y)
select @y
inout 参数
案例1：
delimiter //
create procedure pro_6( inout n int)
begin
set n:=n+1;
end
//
set @n=2
call pro_6(@n)
select @n
如果输出的是字符：（out,name char(20)）
注意点：必须要加字符长度，不加字符长度就只会显示name的第一个字符 ，
三、用户变量： 定义语法：
set @变量名 ; 赋值语法：
（1）方式一：普通赋值
set @变量名:=值;或set @变量名=值;
select @变量名:=值;
（2）方式二：通过查询结果为变量赋值
select 字段|表达式 into 变量名 from 表名 【where 条件】
情况
场景一：存储内固定插入数据案例讲解：
通过存储过程在一个指定的空表中插入10条数据
delimiter //
drop procedure if exists pro4;
drop table if exists money;
create procedure pro4()
BEGIN
declare i int default 0;# 变量名称i int 数据类型 默认为0
create table money(id int primary key auto_increment,
money int(10)
);
while(i<10)DO
insert into money(money) values(100);
set i=i+1;
end while;
select * from money;
end
//
call pro4()
场景二：存储灵活插入数据案例讲解：（需要插入多少条数据，就插入多少条）
delimiter //
drop table if exists money;
drop procedure if exists pro4;
create procedure pro4(in x int)
begin
declare i int default 0;# 变量名称i int 数据类型 默认为0
drop table if exists money;
create table money(id int primary key auto_increment,money int(10));
while(i<x)DO
insert into money(money) values(100);
set i=i+1;
end while;
select * from money;
end
//
call pro4(20)
面试题：
1、你会存储吗？存储的结构？怎么实现？
2、存储在工作中用来干嘛？ 造数据
注意点：
增强
drop procedure if exists 存储名; #增强语句的健壮性，判断并删除存在的存储
drop table if exists 表名; #增强语句的健壮性，判断并删除存在的表
declare i int default 0; declare 声明变量 变量名称i int 数据类型 默认为0
注意：declare 声明变量后面可以接sql语句，也可以默认值，也可以设置值
方式1：declare i int default （select count (id) from emp ）
方式2：declare i int default 0;
两张表的字段一致，插入数据：
方法一：insert into 目标表 select * from 来源表；#插入全部数据
方法二：insert into 目标表（字段 ）select 字段1,字段2 from 来源表。
2、循环语句
三种格式：
WHILE……DO……END WHILE
REPEAT……UNTIL END REPEAT
LOOP……END LOOP
我们这里主讲：
while 循环语句的结构：
格式
while 条件DO
执行语句
end while;
案例：
while(i<10)DO
insert into money(money) values(100);
set i=i+1;
end while;
if判断语句
if 条件 THEN
执行sql1
else
执行sql2
end if;
if单分支案例：
场景一：
delimiter //
drop table if exists money;
drop procedure if exists pro5;
create procedure pro5(in a int)
begin
if a >10 THEN
select * from dept ;
else
select * from emp ;
END if;
end
//
call pro5(12)
if 多分支
在if判断语句中，有几个判断分支 if ，就有几个结束语 end if
if 条件1 THEN
执行select * from student limit 0,2;
else if 条件2 THEN
执行select * from student limit 0,4；
else if 条件3 THEN
执行select * from student limit 0,6;
else
select * from student;
end if;
end if;
end if;
案例：场景一
create procedure pr_add ( in a int)
begin
if a >10 THEN
select * from dept ;
else if a>0 and a<10 then
select * from emp ;
else if a=10 then
select * from dept,emp where dept1=dept2 ;
else
select count(name) from emp ;
end if;
end if;
end if;
end
//
call pr_add(-1)
练习题：
drop table if exists student;
create table student2(
id int primary key ,
name char(20),
sex char(10),
age int(3),
mobile char(20),
class char(10),
english int(10),
chinese int(10),
math int(10)
)engine=INNODB default charset=utf8;
insert into student2 values
(1,‘小红’,‘女’,23,‘13813828824’,‘1719’,77,88,98),
(2,‘小明’,‘男’,23,‘13713713711’,‘1720’,56,66,55),
(3,‘小李’,‘男’,23,‘15915913911’,‘1719’,78,64,87),
(4,‘小张’,‘男’,23,‘15915913912’,‘1720’,77,76,77),
(5,‘小白’,‘女’,24,‘15915913913’,‘1719’,90,89,98),
(6,‘小陈’,‘女’,19,‘15915913914’,‘1719’,84,100,81),
(7,‘小钱’,‘女’,20,‘15915913915’,null,45,99,93);
面试题：根据student学生表去写
1.当传入的参数（大于0）小于等于表里面数据的条数时，则根据分组显示班级的总成绩
2.当传入的参数大于表里面数据的条数时，则统计表里面的数据有多少条
3.当传入其他，则查询表里面的所有数据
select * from student;
delimiter //
drop procedure if exists dcs06;
create procedure dcs06(in n int)
begin
declare i int default (select count(1) from student);
if n>0 && n<=i then #&& 且
select class,sum(chinese+math+english) from student group by class;
else if n>i then
select count(1) from student;
else
select * from student;
end if;
end if;
end;
//
call dcs02(4)
传入数据大于表中已存在的数据的实际记录和行数，则往最高id上自动叠加一行
方法一：
delimiter //
drop procedure if exists bb;
create procedure bb(in n int)
begin
declare i int default (select MAX(id) from student2);
if n=0 then
select max(id) from student2;
else if n>0 && n<=i then
select * from student2 order by id desc ;
else
insert into student2(id) values(i+1);
select * from student2;
end if;
end if;
end
//
call bb(1117)
delimiter // #定义标识符为双斜杠
drop procedure if exists test; #如果存在test存储过程则删除
create procedure test() #创建无参存储过程,名称为test
begin
declare i int; #申明变量
set i = 0; #变量赋值
while i < 10 do #结束循环的条件: 当i大于10时跳出while循环
insert into test values (i); #往test表添加数据
set i = i + 1; #循环一次,i加一
end while; #结束while循环
select * from test; #查看test表数据
end
// #结束定义语句
call test( ); #调用存储过程
注意事项：
1.分隔符//必须接在 delimiter 后面,不能换行.
2.存储过程名称后面必须接() 例子:pro_1()
3.sql end if 语句必须用;结尾.
4.未调用前 sql 语句未使用.调用后才生效.
5.加强存储过程的功能（表或存储功能都要加分号）
6.判断存储过程是否存在,存在就删除,不存在就不操作. if exists