记mysql一些常用语句

1.存储过程的创建及调用

drop procedure if exists inload;   
create procedure inload()   
begin  
declare i int;            
set i=50;    
while i < 1000 do            
INSERT INTO `tb_load`(
id,
order_number,
user_id,
sum,
periods,
`procedure`,
fail_teason,
state,
adopt_time,
expire_time,
create_time,
update_time
) VALUES (i, 'L20171027172559', i, i+1000, '14', 1, '22', 0, '2017-11-10 23:59:59', '2017-11-10 23:59:59', '2017-10-27 17:26:00', '2017-11-10 23:59:59');
INSERT INTO `tb_user`(
user_id,
username,
device_token,
load_total,
`procedure`,
submitted,
fail_teason,
state,
`lock`,
mobile,
mobile_code,
mobile_operators,
service_password,
`password`,
id_number,
id_card_photo_front,
id_card_photo_behind,
video,
video_graphic,
contact_address,
contact_detailed_address,
company,
company_address,
company_detailed_address,
job,
seniority,
company_tel,
monthly_income,
bank_name,
bank_number,
operator,
create_time,
update_time

) VALUES (
i, 
'17623827573', 
'AklLs3Rf_qz3yEMdVaJ3Lw1W3NYYJKLVe4QWBWQ6FxMc', 
'0',
 '1', 
 'collect_jobInfo_personal_socialInfo_mobile_IDcard_',
  '', 
 '0',
'0', 
'17623827573', 
'659646', 
'未知运营商',
 '664646', 
null, 
null,
 null,
null,
'浙江省 绍兴市 上虞区', 
'护手霜',
 '活到九十九', 
'几点结束', 
'打击打击', 
'11', 
'6464646469494', 
null, 
'邮政储蓄银行', 
'6221886********9227', 
'1', 
'2017-10-27 16:01:59', 
'2017-10-27 17:13:17'
);


 set i = i+1;  
end while;  
 end;        
-- 调用
call inload()

2.创建sql函数（生成随机字符串）

CREATE DEFINER=`root`@`localhost` FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1
BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
        SET i = i +1;
    END WHILE;
    RETURN return_str;
END;