文章目录
读者信息表
创建读者信息表
create table reader(
card_id char(18) primary key,
name varchar(20) not null,
sex enum('男','女','保密') default'保密',
age tinyint not null,
phone_number char(11) not null,
balance decimal(6,2) default 200
);
插入读者记录
insert into reader values('330100199201231104','小明','男',29,'15787223423',300);
insert into reader values('330100199201231105','小红','女',21,'15787223424',220);
insert into reader values('330100199201231106','王迷','男',19,'15787223425',310);
insert into reader values('330100199201231107','李思','女',24,'15787223426',230);
insert into reader values('330100199201231108','小王','女',32,'15787223427',240);
insert into reader values('330100199201231109','王霞','女',26,'15787223428',300);
查看下读者信息表
实例1:查询语句
创建一个查询读者的姓名、手机号、余额的存储过程
create procedure selectproc()
select name,phone_number,balance from reader;
call selectproc();
运行结果:
实例2:myql操作及查询
删除一个读者,并显示剩余读者的个数
delimiter//
create procedure deletepro(in myid char(18),out num int)
begin
delete from reader where card_id=myid;
select count(card_id) into num from reader;
end//
delimiter;
call deletepro('330100199201231108',@num);
select @num;
运行结果:
实例3:参数-数字交换
创建一个存储过程,实现数字交换
delimiter//
create procedure switchnum(inout num1 int,inout num2 int)
begin
declare temp int;
set temp=num1;
set num1=num2;
set num2=temp;
end//
delimiter;
set @num1=4,@num2=7;
call switchnum(@num1,@num2);
select @num1,@num2;
运行结果:
实例4:if判断-比较数字大小
delimiter//
create procedure comparenum(in num1 int,in num2 int,out result varchar(20))
begin
if num1>num2 then
set result='num1比num2大';
elseif num1<num2 then
set result='num1比num2小';
else
set result='num1等于num2';
end if;
end//
delimiter;
set @num1=3,@num2=5;
call comparenum(@num1,@num2,@result);
select @result;
运行结果:
实例5:case…when判断-比较数字大小
delimiter//
create procedure comparenum(in num1 int,in num2 int,out result varchar(20))
begin
case
when num1>num2 then set result='num1比num2大';
when num1<num2 then set result='num1比num2小';
else set result='num1和num2相等';
end case;
end//
delimiter;
call comparenum(3,1,@result);
select @result;
运行结果:
实例6:while…do循环-向表中插入数据
设计一个存储过程,向图书类别中插入n条数据。
创建图书类别表:
create table category(
id int(10) primary key,
name varchar(20) not null,
parent_id int(10) not null
);
存储过程,插入数据
delimiter//
create procedure insertpro(in num int)
begin
declare i int default 1;
while i<=num do
insert into category values(i,concat('图书类别',i),floor(rand()*i));
set i=i+1;
end while;
end//
delimiter;
call insertpro(10);
select*from category;
运行结果: