SQL中实用添加删除

create database ddd;
use ddd;
-- 添加字段角标
create table t1(c int);
desc t1;
alter table t1 add c1 char(1)
			   add c2 char(1)
               ...
               ...
               add c10 char(10)
-- 添加字段
delimiter //
create procedure 添加字段()
begin
   declare i int default 1;
   declare ci char(1) default '';
   set @sqlcode ='alter table t1 ' ;
   
   while i<10 do
		set ci =cast(i as char(1));
		set @sqlcode =concat(@sqlcode, 'add c',ci,' char(1),');
        set i=i+1;
	end while;
    
    set @sqlcode =concat(@sqlcode,'add c10 char(1);');

end //
delimiter ;
call 添加字段();
select @sqlcode;

PREPARE a FROM @sqlcode;
EXECUTE a;
desc t1;

-- 删除字段角标
delimiter //
create procedure 删除字段()
begin
   declare i int default 1;
   declare ci char(1) default '';
   set @sqlcode ='alter table t1 ' ;
   
   while i<10 do
		set ci =cast(i as char(1));
		set @sqlcode =concat(@sqlcode, 'drop c',ci,',');
        set i=i+1;
	end while;
    
    set @sqlcode =concat(@sqlcode,'drop c10;');

end //
delimiter ;

call 删除字段;
select @sqlcode;

prepare a from @sqlcode;
execute a;
desc t1;

-- 添加指定数量字段
delimiter //
create procedure 添加指定数量字段(in n int)
begin
   declare i int default 1;
   declare ci char(2) default '';
   set @sqlcode ='alter table t1 ' ;
   
   while i<n do
		set ci =cast(i as char(2));
		set @sqlcode =concat(@sqlcode, 'add c',ci,' char(1),');
        set i=i+1;
	end while;
    
    set @sqlcode =concat(@sqlcode,'add c',cast(n as char(2)),' char(1);');

end //
delimiter ;
call 添加指定数量字段(88);
select @sqlcode;

PREPARE a FROM @sqlcode;
EXECUTE a;

desc t1;

删除指定数量字段同上删除字段！这里就不一一展示了

-- 自由添加
delimiter //
create procedure 自由添加(in cname char(100),in n int)
begin
   declare i int default 1;
   declare ci char(2) default '';
   set @sqlcode ='alter table t1 ' ;
   
   while i<n do
		set ci =cast(i as char(2));
		set @sqlcode =concat(@sqlcode, 'add ',cname,ci,' char(1),');
        set i=i+1;
	end while;
    
    set @sqlcode =concat(@sqlcode,'add ',cname,cast(n as char(2)),' char(1);');

end //
delimiter ;
call 自由添加('ddd',20);
select @sqlcode;

PREPARE a FROM @sqlcode;
EXECUTE a;

desc t1;

-- 自由删除 
delimiter //
create procedure 自由删除(in cname char(100),in n int)
begin
   declare i int default 1;
   declare ci char(2) default '';
   set @sqlcode ='alter table t1 ' ;
   
   while i<n do
		set ci =cast(i as char(2));
		set @sqlcode =concat(@sqlcode, 'drop ',cname,ci,',');
        set i=i+1;
	end while;
    
    set @sqlcode =concat(@sqlcode,'drop ',cname,cast(n as char(2)),';');

end //
delimiter ;
call 自由删除('ddd',20);
select @sqlcode;

PREPARE a FROM @sqlcode;
EXECUTE a;

desc t1;

-- 自由添加删除 --加一个修改字段名
delimiter //
create procedure 自由添加删除(in seltype int,in cname char(100),in n int)
begin
   declare i int default 1;
   declare ci char(2) default '';
   set @sqlcode ='alter table t1 ' ;
   
   if seltype=1 then
   while i<n do
		set ci =cast(i as char(2));
		set @sqlcode =concat(@sqlcode, 'add ',cname,ci,' char(1),');
        set i=i+1;
	end while;
    
    set @sqlcode =concat(@sqlcode,'add ',cname,cast(n as char(2)),' char(1);');
     
     elseif seltype=2 then
     while i<n do
		set ci =cast(i as char(2));
		set @sqlcode =concat(@sqlcode, 'drop ',cname,ci,',');
        set i=i+1;
	end while;
    
    set @sqlcode =concat(@sqlcode,'drop ',cname,cast(n as char(2)),';');
    else set @sqlcode = '参数错误';
    end if;
end //
delimiter ;
call 自由添加删除(1,'ddd',20);`
select @sqlcode;

PREPARE a FROM @sqlcode;
EXECUTE a;

desc t1;

-- 高自由添加删除 --加一个修改数据类型
delimiter //
create procedure 高自由添加删除(in seltype int,in cname char(100),in n int,in ctype varchar(100))
begin
   declare i int default 1;
   declare ci char(2) default '';
   set @sqlcode ='alter table t1 ' ;
   
   if seltype=1 then
   while i<n do
		set ci =cast(i as char(2));
		set @sqlcode =concat(@sqlcode, 'add ',cname,ci,' ',ctype,',');
        set i=i+1;
	end while;
    
    set @sqlcode =concat(@sqlcode,'add ',cname,cast(n as char(2)),' ',ctype,';');
     
     elseif seltype=2 then
     while i<n do
		set ci =cast(i as char(2));
		set @sqlcode =concat(@sqlcode, 'drop ',cname,ci,',');
        set i=i+1;
	end while;
    
    set @sqlcode =concat(@sqlcode,'drop ',cname,cast(n as char(2)),';');
    else set @sqlcode = '参数错误';
    end if;
end //
delimiter ;
call 高自由添加删除(1,'ddd',20,'char(10)');
select @sqlcode;

PREPARE a FROM @sqlcode;
EXECUTE a;

desc t1;

-- 超高自由添加删除 --再添加一个修改表名
create table t2(a int);

delimiter //
create procedure 超高自由添加删除(in seltype int,in cname char(100),in n int,in ctype varchar(100),in tname varchar(100))
begin
   declare i int default 1;
   declare ci char(2) default '';
   set @sqlcode =concat('alter table ',tname,' ');
   
   if seltype=1 then
   while i<n do
		set ci =cast(i as char(2));
		set @sqlcode =concat(@sqlcode, 'add ',cname,ci,' ',ctype,',');
        set i=i+1;
	end while;
    
    set @sqlcode =concat(@sqlcode,'add ',cname,cast(n as char(2)),' ',ctype,';');
     
     elseif seltype=2 then
     while i<n do
		set ci =cast(i as char(2));
		set @sqlcode =concat(@sqlcode, 'drop ',cname,ci,',');
        set i=i+1;
	end while;
    
    set @sqlcode =concat(@sqlcode,'drop ',cname,cast(n as char(2)),';');
    else set @sqlcode = '参数错误';
    end if;
end //
delimiter ;
call 超高自由添加删除(1,'ddd',20,'char(10)','t2');
select @sqlcode;

PREPARE a FROM @sqlcode;
EXECUTE a;

desc t2;

以上就是本次分享的一个SQL存储实用的自由添加删除，希望能帮到你！