shop.sql

网上购物系统 asp+sql server 2005

-----------------------------建库shop----------------------------------
use master
create database shop
go
use shop
go

-----------------------------删除各主表之间的关系----------------------------------
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_s_order_s_orderList]') AND parent_object_id = OBJECT_ID(N'[s_order]'))
ALTER TABLE [s_order] DROP CONSTRAINT [FK_s_order_s_orderList]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_s_order_s_produc]') AND parent_object_id = OBJECT_ID(N'[s_order]'))
ALTER TABLE [s_order] DROP CONSTRAINT [FK_s_order_s_produc]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_s_orderList_s_buser]') AND parent_object_id = OBJECT_ID(N'[s_orderList]'))
ALTER TABLE [s_orderList] DROP CONSTRAINT [FK_s_orderList_s_buser]
GO

-----------------------------用户表s_buser----------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[s_buser]') AND type in (N'U'))
DROP TABLE [s_buser]
GO
CREATE TABLE [s_buser](
	[usernum] [int] IDENTITY(1,1),
	[UserId] [nvarchar](20) PRIMARY KEY,
	[UserName] [nvarchar](20),
	[UserPassword] [nvarchar](50),
	[UserQuestion] [nvarchar](100),
	[UserAnswer] [nvarchar](100),
	[UserMail] [nvarchar](100),
	[UserQQ] [nvarchar](15),
	[UserICQ] [nvarchar](20),
	[UserMSN] [nvarchar](100),
	[Address] [nvarchar](255),
	[City] [nvarchar](50),
	[Province] [nvarchar](20),
	[Country] [nvarchar](30),
	[ZipCode] [nvarchar](10),
	[Birthday] [nvarchar](50),
	[TotalLogin] [int]  DEFAULT ((0)),
	[Sex] [nvarchar](1),
	[HomePhone] [nvarchar](50),
	[CompPhone] [nvarchar](50),
	[Memo] [ntext],
	[memo2] [ntext],
	[SignDate] [datetime]  DEFAULT (getdate()),
	[LastLogin] [datetime]  DEFAULT (getdate()),
	[UserType] [nvarchar](10),
	[UserKou] [nvarchar](10),
	[IP] [nvarchar](50),
	[TJR] [nvarchar](20),
	[FAV] [nvarchar](200),
	[totalsum] [real]  DEFAULT ((0)),
	[jifen] [int] DEFAULT ((0)),
	[Status] [nvarchar](5) DEFAULT ((1))
)
GO
-----------------------------类别表s_class----------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[s_class]') AND type in (N'U'))
DROP TABLE [s_class]
GO
CREATE TABLE [s_class](
	[ClassId] [int] IDENTITY(1,1) PRIMARY KEY,
	[LarSeq] [int] DEFAULT ((0)),
	[LarCode] [nvarchar](20) ,
	[MidSeq] [int] DEFAULT ((0)),
	[MidCode] [nvarchar](20)
)
GO
-----------------------------产品表s_produc----------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[s_produc]') AND type in (N'U'))
DROP TABLE [s_produc]
GO
CREATE TABLE [s_produc](
	[ProdNum] [int] IDENTITY(1,1),
	[ProdIdtext] [nvarchar](20),
	[ProdId] [nvarchar](20) PRIMARY KEY,
	[ProdNametext] [nvarchar](20),
	[ProdName] [nvarchar](60),
	[Modeltext] [nvarchar](20),
	[Model] [nvarchar](50),
	[Prodtext1] [nvarchar](20),
	[Prod1] [nvarchar](50),
	[Prodtext2] [nvarchar](50),
	[Prod2] [nvarchar](50),
	[PriceListtext] [nvarchar](50),
	[PriceList] [money]  DEFAULT ((0)),
	[PriceOrigintext] [nvarchar](50),
	[PriceOrigin] [money] DEFAULT ((0)),
	[ImgPrev] [nvarchar](100),
	[more_pic] [int] DEFAULT ((0)),
	[ProdDisctext] [nvarchar](50),
	[ProdDisc] [ntext],
	[MemoSpectext] [nvarchar](50),
	[MemoSpec] [ntext],
	[LarCode] [nvarchar](20),
	[MidCode] [nvarchar](20),
	[Online] [bit] DEFAULT ((1)),
	[AddDate] [datetime] DEFAULT (getdate()),
	[TJDate] [datetime] ,
	[ClickTimes] [int] DEFAULT ((0)),
	[Remark] [bit]  DEFAULT ((0)),
	[tejia] [bit]  DEFAULT ((0)),
	[Quantity] [bit] DEFAULT ((1)),
	[other] [nvarchar](50)
)
GO
-----------------------------订单主表s_orderList----------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[s_orderList]') AND type in (N'U'))
DROP TABLE [s_orderList]
GO
CREATE TABLE [s_orderList](
	[ID] [int] IDENTITY(1,1),
	[OrderNum] [nvarchar](20)  PRIMARY KEY,
	[UserId] [nvarchar](20) ,
	[OrderTime] [datetime] DEFAULT (getdate()),
	[OrderSum] [numeric](8, 2) ,
	[PayType] [nvarchar](50) ,
	[RecName] [nvarchar](50) ,
	[RecAddress] [nvarchar](255) ,
	[RecPhone] [nvarchar](50) ,
	[RecMail] [nvarchar](50) ,
	[ZipCode] [nvarchar](10) ,
	[CompPhone] [nvarchar](50) ,
	[Gettime] [nvarchar](50) ,
	[Notes] [ntext] ,
	[Memo] [ntext] ,
	[LastModifytime] [datetime] ,
	[Del] [bit] DEFAULT ((0)),
	[pei] [nvarchar](100) ,
	[fei] [int],
	[thiskou] [float],
	[Status] [varchar](50)  DEFAULT ((0))
)
GO
-----------------------------订单详细表s_order----------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[s_order]') AND type in (N'U'))
DROP TABLE [s_order]
GO
CREATE TABLE [s_order](
	[ID] [int] IDENTITY(1,1) PRIMARY KEY,
	[OrderNum] [nvarchar](20) ,
	[UserId] [nvarchar](20) ,
	[ProdId] [nvarchar](20) ,
	[ProdName] [nvarchar](60) ,
	[ProdUnit] [int],
	[BuyPrice] [money],
	[OrderTime] [datetime] DEFAULT (getdate())
)
GO
-----------------------------图片新闻s_gundong----------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[s_gundong]') AND type in (N'U'))
DROP TABLE [s_gundong]
GO
CREATE TABLE [s_gundong](
	[id] [int] IDENTITY(1,1) PRIMARY KEY,
	[title] [nvarchar](50) ,
	[hrefurl] [nvarchar](50) ,
	[picurl] [nvarchar](50) ,
	[status] [bit]  DEFAULT ((1))
) 
GO
-----------------------------文字新闻s_news----------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[s_news]') AND type in (N'U'))
DROP TABLE [s_news]
GO
CREATE TABLE [s_news](
	[NewsID] [int] IDENTITY(1,1) PRIMARY KEY,
	[uup] [int]  DEFAULT ((0)),
	[NewsTitle] [nvarchar](255) ,
	[NewsContain] [ntext] ,
	[NewsClass] [nvarchar](10) ,
	[PubDate] [datetime] DEFAULT (getdate()),
	[OffDate] [datetime] ,
	[Source] [nvarchar](50) ,
	[Author] [nvarchar](20) ,
	[Publisher] [nvarchar](20) ,
	[Online] [bit] DEFAULT ((1)),
	[cktimes] [int] DEFAULT ((0))
)
GO
-----------------------------在线留言s_book----------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[s_book]') AND type in (N'U'))
DROP TABLE [s_book]
GO
CREATE TABLE [s_book](
	[ID] [int] IDENTITY(1,1) PRIMARY KEY,
	[UserName] [nvarchar](50) ,
	[QQ] [nvarchar](100) ,
	[pic] [nvarchar](2) ,
	[face] [nvarchar](2) ,
	[URL] [nvarchar](100) ,
	[UserMail] [nvarchar](100) ,
	[Comments] [ntext] ,
	[Postdate] [datetime] DEFAULT (getdate()),
	[Replay] [ntext] ,
	[Online] [nvarchar](1)  DEFAULT ((1)),
	[IP] [nvarchar](20) ,
	[top] [int] DEFAULT ((0))
)
GO
-----------------------------产品评论s_pinglun----------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[s_pinglun]') AND type in (N'U'))