三月十九号作业

练习，随机插入20个用户，默认密码为‘123456’

create table tiancai(id int(4) primary key,name varchar(20))
select * from tiancai
delete from tiancai
alter table tiancai change id id int(4) auto_increment;
alter table tiancai add mima int(10);
show variables like 'log_bin_trust_function_creators'
set global log_bin_trust_function_creators=1

drop FUNCTION if EXISTS xiaoliu;
create FUNCTION xiaoliu(n int) returns varchar(200)
begin
DECLARE i varchar(100) DEFAULT '一二三四五六七八';
DECLARE j VARCHAR(100) DEFAULT '刘';
DECLARE q int DEFAULT 0;
while q<n DO
set q=q+1;
set j=concat (j,substring(i,floor(1+rand()*50),1));
end while;
RETURN j;
end

第二步；在存储中调第一步的函数方法
drop PROCEDURE if EXISTS inf;
create PROCEDURE inf(m int)
BEGIN
DECLARE i int DEFAULT 0;
while i<m DO
set i=i+1;
insert into tiancai (name,mima)VALUES(xiaoliu(5),123456);
end while;
select * from tiancai;
end
call inf(20)
create table xiao25(id int(4),score int(6))
insert into xiao25(id,score)VALUES(1,60)
alter table xiao25 change id id int(4)primary key auto_increment
select * from xiao25
1、创建存储
drop PROCEDURE IF EXISTS chengji;
create PROCEDURE chengji()
BEGIN
select * from xiao25;
END
call chengji()

2.第二步传参
drop PROCEDURE IF EXISTS chengji;
create PROCEDURE chengji(n int)
BEGIN
if n=0 THEN
select count(id) from xiao25;
  ELSE
select * from xiao25 where id<=n;
end if;

END
call chengji(20)
如果你传入的参数为0-10的整数，根据你传的参数显示指定的行数
如果你传入的参数为0时，统计表中所有数据，你传入的参数为0-10的整数，根据你传的参数显示指定的行数(if)
如果你传入的参数为（0）时，统计表中所有数据，你传入参数小于0时，求所有分数之和
你传入参数为0-10的整数，根据你传的参数显示指定的行数
如果你传入的参数为0时，统计表中所有数据
if 条件 then
   分支
else
   END IF

drop procedure IF exists chengji;
create PROCEDURE chengji(n int)
begin
declare i int default(select count(id) from xiao25);

if n=0 THEN
select count(id) from xiao25;
ELSE if n<0 THEN
select sum(score) from xiao25;
ELSE if n>i THEN
while i<n DO
set i=i+1;
insert into xiao25 (score)VALUES(88);
end while;
select * from xiao25;
else
select * from xiao25 where id<=n;
end if;
end if;
end if;
end

call chengji(23)