SQL纯手写创建数据库到表内内容

建表啥的只点点鼠标，太外行了，不如来看看我的纯手写，让表从无到有一系列；还有存储过程临时表，不间断的重排序；

一：建数据库

1create Database Show

2 on

 3  PRimary 4 ( 5     name= Show_data , 6     filename= 'C:/Program Files/Microsoft SQL Server/MSSQL11.SQLEXPRESS/MSSQL/DATA/Show.mdf' , 7     size=10MB, 8     maxsize=UNLIMITED, 9     filegrowth=10%10      11 )12 log on13 (14   name=Show_log,15   filename='C:/Program Files/Microsoft SQL Server/MSSQL11.SQLEXPRESS/MSSQL/DATA/Show_log .ldf' ,16     size=10MB,17     maxsize=UNLIMITED,18     filegrowth=10%19 )

二：建表

三：建外键

四：添加表数据

五：删除数据库、表、外键、存储过程

 1 drop database Show;      --删除数据库 2  3 drop table T_user;       4 drop table T_proMain; 5 drop table T_proType; 6 drop table T_proImg;       --删除表 7  8 alter table T_proMain drop constraint fk_typeID  9 alter table T_proMain drop constraint fk_UID 10 alter table T_proImg drop constraint fk_proID   --删除外键约束11 12 drop  proc proc_getPic   --删除存储过程

六：建存储过程

 1  create proc proc_getproM  ( 2     @Index int, 3     @Size int 4 ) 5 as 6 begin 7    declare @ta table  8    ( 9         [proID] [int]10    )11    insert into @ta(proID) select proID from  (SELECT ROW_NUMBER() over ( order by CTR desc) as id ,* from T_proMain )a where id between @Index and @Size12    declare @a int , @b  varchar(100)13    declare @tc table14    (15         [proID] int,16         [proExp] varchar(200),17         [UName] varchar(20),18         [UrlName] varchar(max) 19     )20     21    while exists(select [proID] from @ta)22        begin23     --         select * from @ta;24              select top 1 @a=[proID] from @ta;25              declare @c int26              select   @c =proID  from T_proMain where proID=@a ;       --proID27               28              declare @e varchar(200)29              select @e=proExp  from T_proMain where proID=@a ;    --proExp 第一张的 项目名30              31              32              declare @d varchar(20),@l int;33              select @l=UID  from T_proMain where proID=@a ;34              select @d=UName from T_user where UID=@l;             --UName 也就是作者名35              declare @tb table36             (37                 [imgURL]  varchar(100)38             )39             40              insert into @tb (imgURL) select imgURL from   T_proImg  where proID=@a;41     --         select * from @tb42            declare @g varchar(max);43            set @g='';44             while exists(select [imgURL] from @tb)45                 begin46                     select top 1 @b=[imgURL] from @tb;47                     declare @f varchar(100)48                     49                     select @f=imgURL   from T_proImg where imgURL=@b ;  --imgURL 第一张的图片地址50                     set @g+=@f;51                     set @g+='#';52                     print @f;53                     declare @h varchar(200)54                     select @h=imgName   from T_proImg where imgURL=@b ;  --imgName 第一张的图片地址55                     print @h;56                     declare @o bit57                     select @o=ISDefault from T_proImg where imgURL=@b ;58                     set @g+=@h;59                     set @g+='#';60                         declare @n bit,@p varchar(2)61                     set @n=1;62                     if   @o=@n63                         begin64                           set @p='1'65                         end66                     else67                         begin68                             set @p='0'69                         end70                     set @g+=@p;71                     set @g+='*';72                     print @g;73                     delete from @tb where [imgURL] = @b;74                 end75              76             insert into @tc  values(@c,@e,@d,@g); 77             delete from @tb;78             delete from @ta where [proID]=@a;79        end80        select * from @tc;81   end 

 1 create proc proc_getPic  ( 2     @Index int, 3     @Size int 4 ) 5 as 6 begin 7    declare @ta table  8    ( 9         [ID] [int]10    )11    insert into @ta(ID) select ID from  (SELECT ROW_NUMBER() over ( order by ID desc) as id   from  T_Pic )aa where ID between @Index and @Size12    declare @a int , @b  varchar(100)13    declare @tc table14    (15         [ID] int,16         [Pic] varchar(50),17         [PicName] varchar(20),18         [PicAuthor] varchar(20) 19     )20     21    while exists(select [ID] from @ta)22        begin23     --         select * from @ta;24              select top 1 @a=[ID] from @ta;25              declare @c int26              select   @c =ID  from T_Pic where  ID=@a ;       --proID27               28              declare @e varchar(50)29              select @e=Pic  from T_Pic where ID=@a ;    --proExp 第一张的 项目名30              31              32              declare @d varchar(20),@l varchar(20);33              select @l=PicName  from T_Pic where  ID=@a ;34              select @d=PicAuthor from T_Pic where ID=@a;             --UName 也就是作者名35          36             insert into @tc  values(@c,@e,@l,@d); 37              38             delete from @ta where [ID]=@a;39        end40        select * from @tc;41   end 42   exec  proc_getPic   1,10  

希望能帮到有需要的人；

　　　　　　--一个快乐的码农！