本期看點和重點

使用存儲過程查詢id大于3的學(xué)生數(shù)量
create?procedure?qq6(out?shuliang?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(*) 內(nèi)容，賦值給自己命名的變量shuliang。

1.查詢編號大于指定的數(shù)字（比如3），如果大于指定的數(shù)字就查詢商品名稱，小于指定的數(shù)字就查詢商品名稱和數(shù)量

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.查詢商品數(shù)量大于指定商品數(shù)量（比如3）的商品信息，如果商品數(shù)量

大于指定商品數(shù)量（比如3）,就查詢商品的商品信息，

如果商品數(shù)量小于指定商品數(shù)量（比如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;

老師寫的答案（執(zhí)行下面的語句前，先要建其要用得表和相關(guān)的數(shù)據(jù)等）：

1創(chuàng)建存儲過程，查詢商品表，傳入一個數(shù)字ID，查詢大于這個數(shù)字ID的商品信息
select?*?from?shangpin
create?procedure?qwe06(id?int)
begin
select?*?from?shangpin?where?sp_id>id;
end;
call?qwe06(7)
2創(chuàng)建存儲過程,傳入一個數(shù)字,查詢每個商品的銷售總數(shù)量大于這個數(shù)字的商品是那些
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)
輸出參數(shù):
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查詢商品數(shù)量最高的商品名稱和數(shù)量
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;

創(chuàng)建四個表，進行SQL語句的操作：

下面是老師的答案：

1.使用存儲過程查詢學(xué)生姓名，生日，班級名稱
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.使用存儲過程查詢每個班級的學(xué)生人數(shù)和班級名稱
create?procedure?qq3()
begin
select?count(*)?as?'總?cè)藬?shù)',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以上的學(xué)生信息
create?procedure?qq3()
begin
select?*?from?stu1?where?(year(now())-year(stu1.s_birthday))>20;
end;
drop?procedure?qq3
call?qq3
5.使用存儲過程查詢編號在3到5之間的學(xué)生姓名和班級
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.使用存儲過程得到兩個數(shù)字的和
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的學(xué)生數(shù)量
create?procedure?qq6(out?shuliang?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(*) 內(nèi)容，賦值給自己命名的變量shuliang。

】
8.使用存儲過程刪除班級表的班級id為3的數(shù)據(jù)，返回班級表剩余的數(shù)據(jù)條數(shù)
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.創(chuàng)建一個存儲過程，實現(xiàn)兩個數(shù)字的交換
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.創(chuàng)建一個存儲過程，查詢每個班級里有多少學(xué)生
create?procedure?qq9()
begin
select?banji1.b_name,count(*)?from?banji1?group?by?banji1.b_name;?
end;
call?qq9
11.創(chuàng)建一個存儲過程，查詢每個學(xué)生的平均成績
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.創(chuàng)建一個存儲過程，查詢每個老師教多少名學(xué)生
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.創(chuàng)建一個存儲過程，查詢每個學(xué)生的最高分和最低分
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.創(chuàng)建一個存儲過程，查詢平均分大于60的學(xué)生
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.創(chuàng)建一個存儲過程，查詢平均分最高分比最低分高多少
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