你的位置:首页 > 数据库

[数据库]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 

建存储过程:动态取出另一张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 

动态取出表的连续的第几条到第几条数据

希望能帮到有需要的人;

      --一个快乐的码农!