--創(chuàng)建數(shù)據(jù)庫(kù)sportsdb，創(chuàng)建前先查詢?nèi)绻嬖趧t刪除。
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'sportsDB')
	DROP DATABASE [sportsDB]
GO
--創(chuàng)建數(shù)據(jù)庫(kù)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
/*會(huì)員表*/
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,--會(huì)員ID
	[memName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,--會(huì)員姓名
	[memPwd] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,--會(huì)員密碼
	[realName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,--會(huì)員真實(shí)姓名
	[memsex] [bit] NOT NULL ,--性別
	[memPoint] [int] NOT NULL ,--會(huì)員積分
	[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],--密碼默認(rèn)為888888
	CONSTRAINT [df_memsex] DEFAULT (1) FOR [memsex],--性別為布爾型，1為男，0為女
	CONSTRAINT [df_memPoint] DEFAULT (10) FOR [memPoint],--默認(rèn)積分為10
	CONSTRAINT [df_memAddress] DEFAULT ('地址不詳') FOR [memAddress],--默認(rèn)地址為不詳
	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 ,--訂單編號(hào)
	[ProductId] [int] NOT NULL ,--產(chǎn)品編號(hào)
	[Amount] [int] NOT NULL ,--購(gòu)買數(shù)量
	[OrderStats] [varchar] (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,--訂單狀態(tài)
	[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 --下單時(shí)間
) 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 ,/*規(guī)格*/
	[colour] [varchar] (50) not null,/*適用風(fēng)格*/
 	[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

/*品牌表測(cè)試數(shù)據(jù)*/
insert into brand_Tab (brandname) values ('YONEX')
insert into brand_Tab (brandname) values ('VICTOR')
/*類別表測(cè)試數(shù)據(jù)*/
insert into sort_Tab (sorname) values ('羽毛球拍')
/*商品表測(cè)試數(shù)據(jù)*/
insert into product_Tab (psortid,pbrandid,productname,unit,spec,colour,amout,remark)
 values ('5001','2001','MP45','支','拍套 吊卡 質(zhì)保卡','適合球員類型 中、高級(jí)',10,'高彈性碳素纖維+Ultimum Ti鎳鈦記憶合金')
/*會(huì)員表測(cè)試數(shù)據(jù)*/
insert into member_Tab (memName,memPwd,realName,memsex,memPoint,memEmail,memMobile,memAddress,memPost) 
values ('秦人','123456','秦仁',1,10,'dasa@yahoo.com.cn','13954656987','上海！上海！','200000')
select *from member_Tab
select *from brand_Tab
select *from sort_Tab
select *from product_Tab