sportdb数据库.sql

一个自行开发的网上商店一个自行开发的网上商店一个自行开发的网上商店

--创建数据库sportsdb，创建前先查询如果存在则删除。
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'sportsDB')
	DROP DATABASE [sportsDB]
GO
--创建数据库sportsdb。
CREATE DATABASE [sportsDB]  
ON 
(NAME = N'sportsDB', 
FILENAME = N'D:\sportsDB_data.mdf' ,
 SIZE = 10, FILEGROWTH = 15%) 
LOG ON 
(NAME = N'sportsDB_log', 
FILENAME = N'D:\sportsDB_log.ldf' , 
SIZE = 1, 
MAXSIZE = 20, F
ILEGROWTH = 10%)
 COLLATE Chinese_PRC_CI_AS
GO


use [sportsDB]
GO
/*会员表*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[member_Tab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[member_Tab]
GO

CREATE TABLE [dbo].[member_Tab] (
	[Menid] [int] IDENTITY (1001, 1) NOT NULL,--会员ID
	[memName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,--会员姓名
	[memPwd] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,--会员密码
	[realName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,--会员真实姓名
	[memsex] [bit] NOT NULL ,--性别
	[memPoint] [int] NOT NULL ,--会员积分
	[memEmail] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,--电子邮件
	[memMobile] [varchar] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,--固定电话
	[memAddress] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,--地址
	[memPost] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL --邮编
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[member_Tab] WITH NOCHECK ADD 
	CONSTRAINT [pk_Menid] PRIMARY KEY  CLUSTERED 
	(
		[Menid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[member_Tab] WITH NOCHECK ADD 
	CONSTRAINT [DF_memPwd] DEFAULT ('888888') FOR [memPwd],--密码默认为888888
	CONSTRAINT [df_memsex] DEFAULT (1) FOR [memsex],--性别为布尔型，1为男，0为女
	CONSTRAINT [df_memPoint] DEFAULT (10) FOR [memPoint],--默认积分为10
	CONSTRAINT [df_memAddress] DEFAULT ('地址不详') FOR [memAddress],--默认地址为不详
	CONSTRAINT [ck_memEmail] CHECK ([memEmail] like '%@%'),--检查约束电子邮件必须包含‘@’
	CONSTRAINT [ck_memMobile] CHECK (len([memMobile]) >= 11),--
	CONSTRAINT [ck_memPwd] CHECK (len([memPwd]) >= 6)
GO
select * from member_Tab

--如果存在订单表则删除
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[orderItem_Tab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[orderItem_Tab]
GO

/*品牌表*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[brand_Tab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[brand_Tab]
GO

CREATE TABLE [dbo].[brand_Tab] (
	[brandID] [int] IDENTITY (2001, 1) NOT NULL ,--品牌ID
	[BrandName] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,--品牌名称
	[Remark] [text] COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

--主键约束
ALTER TABLE [dbo].[brand_Tab] WITH NOCHECK ADD 
	CONSTRAINT [pk_brandID] PRIMARY KEY  CLUSTERED 
	(
		[brandID]
	)  ON [PRIMARY] 
GO


/*订单表*/
CREATE TABLE [dbo].[orderItem_Tab] (
	[OrderId] [int] IDENTITY (3001, 1) NOT NULL ,--订单编号
	[ProductId] [int] NOT NULL ,--产品编号
	[Amount] [int] NOT NULL ,--购买数量
	[OrderStats] [varchar] (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,--订单状态
	[OrderName] [varchar] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,---订货人--
	[ShuoName] [varchar] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,--收货人
	[Oaddress] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,--送货地址
	[PostCode] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,--邮编
	[Phone] [varchar] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,--电话
	[Email] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--电子邮件
	[TransType] [int] NULL ,--交易类型
	[Remark] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,--备注
	[OrderTime] [timestamp] NULL --下单时间
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[orderItem_Tab] WITH NOCHECK ADD 
	CONSTRAINT [pk_OrderId] PRIMARY KEY  CLUSTERED 
	(
		[OrderId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[orderItem_Tab] WITH NOCHECK ADD 
	CONSTRAINT [df_OrderStats] DEFAULT ('未处理') FOR [OrderStats],
	CONSTRAINT [ck_Phone] CHECK (len([Phone]) >= 11),
	CONSTRAINT [ck_PostCode] CHECK (len([PostCode]) >= 6)
GO

/*商品表*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[product_Tab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[product_Tab]
GO

CREATE TABLE [dbo].[product_Tab] (
	[productId] [int] IDENTITY (4001, 1) NOT NULL ,
	psortid int not null,
	pbrandid int not null,
	[productname] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,	
	[unit] [char] (10) not null,/*单位*/
	[spec] [varchar] (30) not null ,/*规格*/
	[colour] [varchar] (50) not null,/*适用风格*/
 	[image1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 	[image2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 	[image3] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[amout] [int] NOT NULL ,
	[productPoint] [int] NOT NULL ,
	[procductclickcount] [int] NOT NULL ,
	[remark] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
	[salecount] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[product_Tab] WITH NOCHECK ADD 
	CONSTRAINT [pk_productId] PRIMARY KEY  CLUSTERED 
	(
		[productId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[product_Tab] WITH NOCHECK ADD 
	CONSTRAINT [df_productPoint] DEFAULT (0) FOR [productPoint],
	CONSTRAINT [df_procductclickcount] DEFAULT (0) FOR [procductclickcount],
	CONSTRAINT [df_salecount] DEFAULT (0) FOR [salecount]
GO

/*类别表*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sort_Tab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sort_Tab]
GO

CREATE TABLE [dbo].[sort_Tab] (
	[sortid] [int] IDENTITY (5001, 1) NOT NULL ,
	[sorName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Remark] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[sort_Tab] WITH NOCHECK ADD 
	CONSTRAINT [pk_sortid] PRIMARY KEY  CLUSTERED 
	(
		[sortid]
	)  ON [PRIMARY] 
GO

/*品牌表测试数据*/
insert into brand_Tab (brandname) values ('YONEX')
insert into brand_Tab (brandname) values ('VICTOR')
/*类别表测试数据*/
insert into sort_Tab (sorname) values ('羽毛球拍')
/*商品表测试数据*/
insert into product_Tab (psortid,pbrandid,productname,unit,spec,colour,amout,remark)
 values ('5001','2001','MP45','支','拍套 吊卡 质保卡','适合球员类型 中、高级',10,'高弹性碳素纤维+Ultimum Ti镍钛记忆合金')
/*会员表测试数据*/
insert into member_Tab (memName,memPwd,realName,memsex,memPoint,memEmail,memMobile,memAddress,memPost) 
values ('秦人','123456','秦仁',1,10,'[e-mail]','13954656987','上海！上海！','200000')
select *from member_Tab
select *from brand_Tab
select *from sort_Tab
select *from product_Tab