mysql触发器if游标 mysql触发器if语句

————————

本期看点和重点等：

7.使用存储过程查询id大于3的学生数量

create procedure qq6(outshuliang int)
begin
select count(*) into shuliang from stu1 where stu1.s_id>3;
end;
set @shuliang=0;
call qq6(@shuliang);
select @shuliang;
select * from banji1
【

个人对out的储存过程的理解等：

用into来给自己声明的变量赋值等，比如上面的SQL语句中，count(*)into shuliang

就是表示把查到的count(*) 内容，赋值给自己命名的变量shuliang。

】

————————————————

1.查询编号大于指定的数字(比如3)，如果大于指定的数字就查询商品名称，小于指定的数字就查询商品名称和数量

create procedure ifNum(num int)
begin
if(num>3)then
select spname from sp  where spid=num ;
else
select spname,spshuliang from sp  where spid=num;
end if;
end;
--drop procedure ifNum
call ifNum(2);
call ifNum(4);

2.查询商品数量大于指定商品数量(比如3)的商品信息，如果商品数量

大于指定商品数量(比如3),就查询商品的商品信息，

如果商品数量小于指定商品数量(比如3)，就查询前5条商品信息

create procedure ifSPNum(num int)
begin
if(num>3)then
select * from sp  where spid=num ;
else
select * from sp  limit 5;
end if;
end;
--drop procedure ifSPNum
call ifSPNum(2);
call ifSPNum(4);
————————
常见例子：
create procedure qweq01(num int)
begin
if(num=3)then
select * from shangpin;
elseif(num>3)then
select * from dingdan;
else
select * from yonghu;
end if;
end;

——————————

老师写的答案(执行下面的语句前，先要建其要用得表和相关的数据等)：

1创建存储过程，查询商品表，传入一个数字ID，查询大于这个数字ID的商品信息

select * from shangpin
create procedure qwe06(id int)
begin
select * from shangpin where sp_id>id;
end;
call qwe06(7)

2创建存储过程,传入一个数字,查询每个商品的销售总数量大于这个数字的商品是那些

create procedure qwe07(shuzi int)
begin
select dingdan.sp_id,sum(dingdan.sp_buynum) from shangpin
inner join dingdan on shangpin.sp_id=dingdan.sp_id
group by dingdan.sp_id having sum(dingdan.sp_buynum)>shuzi;
end;
call qwe07(30)

输出参数:

3查询张三买了几件商品

create procedure qwe08(out maideshuliang int)
begin
select sum(dingdan.sp_buynum) into maideshuliang from yonghu inner join dingdan
on yonghu.yh_id=dingdan.sp_id
where yonghu.yh_name='张三';
end;
set @maideshulinag=0;
call qwe08(@maideshuliang);
select @maideshuliang;

4查询商品数量最高的商品名称和数量

select * from shangpin
create procedure qwe09(out name varchar(100),out shuliang int)
begin
select shangpin.sp_name,sum(shangpin.sp_num)
into name,shuliang from shangpin group by shangpin.sp_name
order by sum(shangpin.sp_num) desc limit 1;
end;
set @name='',@shuliang=0;
call qwe09(@name,@shuliang);
select @name,@shuliang;

创建四个表，进行SQL语句的操作：

下面是老师的答案：

1.使用存储过程查询学生姓名，生日，班级名称

create procedure qq1()
begin
select  stu1.s_name,stu1.s_birthday,banji1.b_name from stu1 inner join banji1 on
stu1.s_id=banji1.s_id;
end;
call qq1

2.使用存储过程给班级表增加一条语句

create procedure qq2()
begin
insert into banji1(b_name,s_id,t_id) values('八班',1,2);
insert into banji1(b_name,s_id,t_id) values('七班班',2,3);
end;
call qq2
select * from banji1

3.使用存储过程查询每个班级的学生人数和班级名称

create procedure qq3()
begin
select count(*) as '总人数',banji1.b_name as '班级名称' from stu1
inner join banji1 on stu1.s_id=banji1.s_id group by banji1.b_name;
end;
call qq3

4.使用存储过程查询年龄在20以上的学生信息

create procedure qq3()
begin
select * from stu1 where (year(now())-year(stu1.s_birthday))>20;
end;
drop procedure qq3
call qq3

5.使用存储过程查询编号在3到5之间的学生姓名和班级

create procedure qq4()
begin
select * from stu1 inner join banji1 on banji1.s_id=stu1.s_id
where stu1.s_id between 3 and 5;
end;
call qq4

6.使用存储过程得到两个数字的和

create procedure qq5(num1 int ,num2 int,out he int)
begin
set he=num1+num2;
end;
set @he=0;
call qq5(10,20,@he);
select @he;

7.使用存储过程查询id大于3的学生数量

create procedure qq6(outshuliang int)
begin
select count(*) into shuliang from stu1 where stu1.s_id>3;
end;
set @shuliang=0;
call qq6(@shuliang);
select @shuliang;
select * from banji1
【

个人对out的储存过程的理解等：

用into来给自己声明的变量赋值等，比如上面的SQL语句中，count(*)into shuliang

就是表示把查到的count(*) 内容，赋值给自己命名的变量shuliang。

】

8.使用存储过程删除班级表的班级id为3的数据，返回班级表剩余的数据条数

create procedure qq7(out shuliang int)
begin
delete from banji1 where b_id=4;
select count(*) into shuliang from banji1;
end;
set @shuliang=0;
call qq7(@shuliang);
select @shuliang;

9.创建一个存储过程，实现两个数字的交换

create procedure qq8(inout num1 int,inout num2 int)
begin
declare num3 int default 0;
set num3=num1;
set num1=num2;
set num2=num3;
end;
set @num1=10,@num2=20;
call qq8(@num1,@num2);
select @num1,@num2;

10.创建一个存储过程，查询每个班级里有多少学生

create procedure qq9()
begin
select banji1.b_name,count(*) from banji1 group by banji1.b_name;
end;
call qq9

11.创建一个存储过程，查询每个学生的平均成绩

create procedure qq10()
begin
select stu1.s_name,avg(cj1.c_chengji) from stu1 inner join cj1 on stu1.s_id=cj1.s_id
group by stu1.s_name;
end;
call qq10

12.创建一个存储过程，查询每个老师教多少名学生

create procedure qq11()
begin
select teacher1.t_name,count(*) from banji1 inner join teacher1 on banji1.t_id=teacher1.t_id
group by teacher1.t_name;
end;
call qq11
drop procedure qq12
select * from banji1 inner join teacher1 on banji1.t_id=teacher1.t_id

13.创建一个存储过程，查询每个学生的最高分和最低分

create procedure qq12()
begin
select stu1.s_name,max(cj1.c_chengji),min(cj1.c_chengji) from
cj1 inner join stu1 on stu1.s_id=cj1.s_id group by stu1.s_name;
end;
call qq12

14.创建一个存储过程，查询平均分大于60的学生

create procedure qq13()
begin
select stu1.s_name,avg(cj1.c_chengji) from
cj1 inner join stu1 on stu1.s_id=cj1.s_id group by stu1.s_name
having avg(cj1.c_chengji)>60;
end;
call qq13

15.创建一个存储过程，查询平均分最高分比最低分高多少

create procedure qq14()
begin
select(
(select avg(cj1.c_chengji) from cj1 group by cj1.s_id order by avg(cj1.c_chengji) desc limit 1)-
(select avg(cj1.c_chengji) from cj1 group by cj1.s_id order by avg(cj1.c_chengji)  limit 1)
);
end;
call qq14