在SQL中如何创建数据库中的表?

发布网友 发布时间：2022-04-23 16:02

我来回答

共5个回答

懂视网 时间：2022-04-08 02:08

1create Database Show

2 on 

 3  primary
 4 (
 5 name= Show_data ,
 6 filename= ‘C:Program FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESSMSSQLDATAShow.mdf‘ ,
 7 size=10MB,
 8 maxsize=UNLIMITED,
 9 filegrowth=10%
10 
11 )
12 log on
13 (
14 name=Show_log,
15 filename=‘C:Program FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESSMSSQLDATAShow_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 @Size
12 declare @a int , @b varchar(100)
13  declare @tc table
14  (
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  begin
23 --  select * from @ta;
24  select top 1 @a=[proID] from @ta;
25  declare @c int
26  select @c =proID from T_proMain where proID=@a ; --proID
27  
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 table
36   (
37   [imgURL] varchar(100)
38   )
39  
40  insert into @tb (imgURL) select imgURL from T_proImg where proID=@a;
41 --  select * from @tb
42   declare @g varchar(max);
43  set @g=‘‘;
44  while exists(select [imgURL] from @tb)
45   begin
46   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 bit
57   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=@n
63    begin
64    set @p=‘1‘
65    end
66   else
67    begin
68    set @p=‘0‘
69    end
70   set @g+=@p;
71   set @g+=‘*‘;
72    print @g;
73   delete from @tb where [imgURL] = @b;
74   end
75  
76   insert into @tc values(@c,@e,@d,@g); 
77  delete from @tb;
78  delete from @ta where [proID]=@a;
79  end
80 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 @Size
12 declare @a int , @b varchar(100)
13  declare @tc table
14  (
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  begin
23 --  select * from @ta;
24  select top 1 @a=[ID] from @ta;
25  declare @c int
26  select @c =ID from T_Pic where ID=@a ; --proID
27  
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  end
40 select * from @tc;
41  end 
42 exec proc_getPic 1,10 

希望能帮到有需要的人；

　　　　　　--一个快乐的码农！

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

标签：

热心网友 时间：2022-04-07 23:16

热心网友 时间：2022-04-08 00:34

热心网友 时间：2022-04-08 02:08

热心网友 时间：2022-04-08 04:00