mysql中批量增删改示例

tb_user:
//新增
BEGIN

#DECLARE @UserIdx INT,@UserPrefix VARCHAR(10),@CompanyID int,@AgentID int;
set @UserIdx =0;
set @CompanyID=1;
set @AgentID=1;
set @UserPrefix = CAST(CEILING(RAND()*9000) as CHAR);

WHILE @UserIdx < 10
DO

insert into tb_User(User_Account,User_Password,User_Name,User_CompanyId,User_AgentID,User_Type,User_AudioStatus,User_Enable,User_CreateTime,User_UpdateTime)
        VALUES(( RTrim(CAST(@UserPrefix as CHAR)) + CAST(@UserIdx as CHAR)),'C4CA4238A0B923820DCC509A6F75849B',(RTrim(CAST(@UserPrefix as CHAR))  + CAST(@UserIdx as CHAR)),@CompanyID,@AgentID,0,1,1,NOW(),NOW());
#print @UserIdx;
set @UserIdx = @UserIdx+1;

end WHILE;

END

//修改
begin
update tb_User
set User_name = CAST(CEILING(RAND()*9000+1000) as CHAR)
where USER_ID in(select t.user_id from (select user_id from tb_User order by User_ID desc limit 10)as t);

end

//删除(物理删除，逻辑删除参照修改)
BEGIN

delete from tb_User where USER_ID in(SELECT t.USER_ID FROM (select USER_ID from tb_User order by USER_ID desc limit 7) as t);

END