建库建表

use master


GO 




IF EXISTS (SELECT * FROM SYSDATABASES WHERE name ='userinfoa')
BEGIN 
DROP DATABASE Userinfo
END


CREATE DATABASE userinfo
on primary
(
   name ='userinfo_data',
   filename='D:\QQlive.mdf',
   size = 3 ,
   maxsize =10,
   filegrowth =1


)
log on


(
   name ='userinfo_log',
   filename='D:\QQlive.ldf',
   size = 3 ,
   maxsize =10,
   filegrowth =1




)
go


use Userinfo
go
IF EXISTS (SELECT *FROM SYSOBJECTS WHERE name ='commodity')
BEGIN
  DROP TABLE commodity
END
--商品表  主键表
CREATE TABLE  commodity
(
    id int primary key identity (1,1), --商品编号
    name nvarchar (50) not null, -- 商品名称
) 
go
insert into commodity values('买家')
insert into commodity values('土豆粉')
insert into commodity values('大鸭梨')
insert into commodity values('芒果干')




--外键表 -- 所选商品表


IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name ='username')
BEGIN
DROP TABLE username
END 


CREATE TABLE USERNAME 
(
       it int identity (1,1), --用户编号
       hose int not null , --用户所选商品编号
       clevel nvarchar (30), -- 商品级别
       number int not null, --商品数量
       ordernumber int not null , --订单编号
)
GO
insert into USERNAME values('1001','1级','3','21001')
insert into USERNAME values('2001','2级','4','22001')
insert into USERNAME values('3001','3级','5','23001')
insert into USERNAME values('4001','4级','6','24001')
insert into USERNAME values('5001','5级','7','25001')
--订单表
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name ='orderform')
BEGIN 
DROP TABLE orderfrom
END


CREATE TABLE ORDERFROM
(
     it int identity(1,1), --用户编号
     consignee nvarchar(20), --收货人名称
     indent int not null , -- 订单类别
     address nvarchar (50) ,-- 收货地址
     serialnumber int not null,-- 邮编
     phone int not null , --联系电话


)
go


alter table ORDERFROM add constraint df_ORDERFROM_address default ('收货地址不详') for address  


insert into ORDERFROM values ('回高原','2','沈阳皇姑区','112700','151000')
insert into ORDERFROM values ('闫子月','1','海淀区','100086','143800')
insert into ORDERFROM values ('鲁震天','3','昌平区','222223','13801111')






--评论表


IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name ='comment')
BEGIN 
DROP TABLE COMMENT
END


CREATE TABLE COMMENT 
( 
    it int identity(1,1), --用户编号
    hose int not null , -- 被评商品商品编号(用户所选商品号)
    content nvarchar (50)not null , --评论内容
)
GO


insert into comment values('1011','产品太好了')
insert into comment values('2011','产品太好了')
insert into comment values('3011','产品太不好了')
insert into comment values('4011','产品太好了')


--货物储存地址


IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name ='caddress')
BEGIN 
DROP TABLE caddress
END 


CREATE TABLE caddress
(      
         
      it int identity(1,1), --用户编号
      chucun nvarchar (50) not null, --储存地址




)
GO


insert into caddress values('大东区')
insert into caddress values('沈河区')
insert into caddress values('和平区')
insert into caddress values('皇姑区')