建表啥的只点点鼠标，太外行了，不如来看看我的纯手写，让表从无到有一系列；还有存储过程临时表，不间断的重排序；
一：建数据库
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
建存储过程：动态取出另一张ID连续等于第几条到第几条，在这张表的数据
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
动态取出表的连续的第几条到第几条数据
希望能帮到有需要的人；
--一个快乐的码农！
原标题：SQL纯手写创建数据库到表内内容
关键词：sql
我知道了 