标准sql建表语句--sqlserver

USE Master
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name = 'library')
DROP DATABASE library
GO

CREATE DATABASE library
GO
USE library
GO

--书籍信息表
CREATE TABLE [dbo].[bookInfo](
[bookId] [int] IDENTITY(2001,1) NOT NULL,
[bookName] [nvarchar](30) NOT NULL,
[tid] [int] NOT NULL,
[author] [nvarchar](20) ,
[publish] [nvarchar](30) ,
[isbn] [nvarchar](20) ,
[introduce] [nvarchar](100) ,
[Price] [float] NOT NULL,
[vipPrice] [float] NOT NULL,
[imagePath] [nvarchar](50) ,
[publishDate] [datetime] ,
[outDate] [datetime] ,
CONSTRAINT [PK_bookId] PRIMARY KEY CLUSTERED
(
[bookId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
CONSTRAINT [UK_bookId] UNIQUE NONCLUSTERED
(
[bookId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


INSERT INTO [library].[dbo].[bookInfo] ([bookName],[tid],[author],[publish],[isbn]
,[introduce],[Price],[vipPrice],[imagePath],[publishDate],[outDate])
     VALUES ('洋文连篇','1003','march','北京大学出版社','SS001','学习书籍',20,16,'image1/1.jpg','2011-3-9 0:00:00','2011-3-9 0:00:00');
INSERT INTO [library].[dbo].[bookInfo] ([bookName],[tid],[author],[publish],[isbn]
,[introduce],[Price],[vipPrice],[imagePath],[publishDate],[outDate])
     VALUES ('我的美女老板','1005','天下第二','华东师范大学出版社','SS003','学习书籍',35,28,'image1/2.jpg','2011-4-19 0:00:00','2011-4-19 0:00:00');
INSERT INTO [library].[dbo].[bookInfo] ([bookName],[tid],[author],[publish],[isbn]
,[introduce],[Price],[vipPrice],[imagePath],[publishDate],[outDate])
     VALUES ('球事儿','1006','杜庆峰','西安工业大学出版社','SS004','说事书籍',25,20,'image1/3.jpg','2011-3-19 0:00:00','2011-4-19 0:00:00');
INSERT INTO [library].[dbo].[bookInfo] ([bookName],[tid],[author],[publish],[isbn]
,[introduce],[Price],[vipPrice],[imagePath],[publishDate],[outDate])
     VALUES ('奥林霹客','1007','魏国福','西安工业大学出版社','SS006','故事书籍',18,14.4,'image1/4.jpg','2011-4-19 0:00:00','2011-4-19 0:00:00');
INSERT INTO [library].[dbo].[bookInfo] ([bookName],[tid],[author],[publish],[isbn]
,[introduce],[Price],[vipPrice],[imagePath],[publishDate],[outDate])
     VALUES ('童言无忌','1008','金晨','武汉工业大学出版社','SS007','明理书籍',30,24.4,'image1/5.jpg','2011-4-19 0:00:00','2011-4-19 0:00:00');
INSERT INTO [library].[dbo].[bookInfo] ([bookName],[tid],[author],[publish],[isbn]
,[introduce],[Price],[vipPrice],[imagePath],[publishDate],[outDate])
     VALUES ('挑食宝贝','1009','王锦锦','西安工程大学出版社','SS008','营养书籍',31,25,'image1/6.jpg','2011-4-19 0:00:00','2011-4-19 0:00:00');
INSERT INTO [library].[dbo].[bookInfo] ([bookName],[tid],[author],[publish],[isbn]
,[introduce],[Price],[vipPrice],[imagePath],[publishDate],[outDate])
     VALUES ('丰田的方式','1010','申达慧','西北工业大学出版社','SS009','管理书籍',30,26,'image1/7.jpg','2011-4-19 0:00:00','2011-4-19 0:00:00');
INSERT INTO [library].[dbo].[bookInfo] ([bookName],[tid],[author],[publish],[isbn]
,[introduce],[Price],[vipPrice],[imagePath],[publishDate],[outDate])
     VALUES ('神兵小将','1011','吴磊','南京邮电大学出版社','SS010','管理书籍',28,25,'image1/8.jpg','2011-4-19 0:00:00','2011-4-19 0:00:00');
INSERT INTO [library].[dbo].[bookInfo] ([bookName],[tid],[author],[publish],[isbn]
,[introduce],[Price],[vipPrice],[imagePath],[publishDate],[outDate])
     VALUES ('风光','1012','燕子小三','南京邮电大学出版社','SS011','言情书籍',18,10,'image1/9.jpg','2011-4-19 0:00:00','2011-4-19 0:00:00');
INSERT INTO [library].[dbo].[bookInfo] ([bookName],[tid],[author],[publish],[isbn]
,[introduce],[Price],[vipPrice],[imagePath],[publishDate],[outDate])
     VALUES ('薛丁山征西','1013','浪子小三','南京大学出版社','SS012','历史书籍',40,34.8,'image1/10.jpg','2011-4-19 0:00:00','2011-4-19 0:00:00');
INSERT INTO [library].[dbo].[bookInfo] ([bookName],[tid],[author],[publish],[isbn]
,[introduce],[Price],[vipPrice],[imagePath],[publishDate],[outDate])
     VALUES ('倒悬的天空','1014','幽灵','南京大学出版社','SS013','悬疑书籍',17,10.5,'image1/11.jpg','2011-4-19 0:00:00','2011-4-19 0:00:00');
INSERT INTO [library].[dbo].[bookInfo] ([bookName],[tid],[author],[publish],[isbn]
,[introduce],[Price],[vipPrice],[imagePath],[publishDate],[outDate])
     VALUES ('咖啡红茶','1015','风光无限','南京大学出版社','SS014','生活书籍',35,15.5,'image1/12.jpg','2011-4-19 0:00:00','2011-4-19 0:00:00');




--管理员表
CREATE TABLE [dbo].[managerInfo](
[adminId] [nvarchar](18) NOT NULL,
[adminPwd] [nvarchar](18) NOT NULL,
[alastLoginTime] [datetime] ,
[aloginCount] [int] ,
[aregister] [datetime] ,
CONSTRAINT [PK_adminId] PRIMARY KEY CLUSTERED
(
[adminId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
CONSTRAINT [UK_adminId] UNIQUE NONCLUSTERED
(
[adminId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO managerInfo (adminId,adminPwd,alastLoginTime,aloginCount,aregister)
VALUES ('admin','admin','2011-05-01',2,'2011-01-01');

INSERT INTO managerInfo (adminId,adminPwd,alastLoginTime,aloginCount,aregister)
VALUES ('zxn','123','2011-02-01',1,'2011-01-01');

--用户表
CREATE TABLE [dbo].[memberInfo](
[muid] [nvarchar](18) NOT NULL,
[mpwd] [nvarchar](18) NOT NULL,
[memail] [nvarchar](30) ,
[mquestion] [nvarchar](30) ,
[manswer] [nvarchar](30) ,
[mname] [nvarchar](20) ,
[mregister] [datetime] ,
CONSTRAINT [PK_muid] PRIMARY KEY CLUSTERED
(
[muid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
CONSTRAINT [UK_muid] UNIQUE NONCLUSTERED
(
[muid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO memberInfo (muid,mpwd,memail,mquestion,manswer,mname,mregister)
VALUES ('zxn','zxn','[e-mail]','one','大脸猫','叶子','2011-4-19 0:00:00');
INSERT INTO memberInfo (muid,mpwd,memail,mquestion,manswer,mname,mregister)
VALUES ('zhangxiaoni','123','[e-mail]','two','qwqw','qweq','2011-4-19 0:00:00');


--订单人表
CREATE TABLE [dbo].[orderFormInfo](
[orderId] [int] IDENTITY(3001,1) NOT NULL,
[muid] [nvarchar](18) NOT NULL,
[buyerName] [nvarchar](20) NOT NULL,
[buyerTel] [nvarchar](15) NOT NULL,
[postcode] [nvarchar](15) NOT NULL,
[postAddress] [nvarchar](50) NOT NULL,
[email] [nvarchar](30) NOT NULL,
[payModel] [int] NOT NULL,
[orderDatetime] [datetime] NOT NULL CONSTRAINT [DF_orderDatetime]  DEFAULT (''),
CONSTRAINT [PK_orderId] PRIMARY KEY CLUSTERED
(
[orderId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
CONSTRAINT [UK_orderId] UNIQUE NONCLUSTERED
(
[orderId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[orderFormInfo]  WITH CHECK ADD  CONSTRAINT [FK_muid] FOREIGN KEY([muid])
REFERENCES [dbo].[memberInfo] ([muid])
GO
ALTER TABLE [dbo].[orderFormInfo] CHECK CONSTRAINT [FK_muid]
GO
ALTER TABLE [dbo].[orderFormInfo]  WITH CHECK ADD  CONSTRAINT [CK_payModel] CHECK  (([payModel] like '[1,2,3]'))
GO
ALTER TABLE [dbo].[orderFormInfo] CHECK CONSTRAINT [CK_payModel]
GO

INSERT INTO orderFormInfo (muid,buyerName,buyerTel,postcode,postAddress,email,payModel,orderDatetime)
VALUES ('zxn','wrwr','wrw','rwr','wrwr','rrwe',1,'1900-1-12 0:00:00');
INSERT INTO orderFormInfo (muid,buyerName,buyerTel,postcode,postAddress,email,payModel,orderDatetime)
VALUES ('admin','qqqq','11111','11111111','1111111','11111',1,'1930-6-4 0:00:00');
INSERT INTO orderFormInfo (muid,buyerName,buyerTel,postcode,postAddress,email,payModel,orderDatetime)
VALUES ('admin','wewe','weqwe','weq','eqwe','eqe',1,'1905-6-2 0:00:00');

--订单表
CREATE TABLE [dbo].[orderProduceInfo](
[orderId] [int] NOT NULL,
[bookId] [int] NOT NULL,
[bookCount] [int] NOT NULL CONSTRAINT [DF_bookCount] DEFAULT 0,
[allPrice] [float] NOT NULL CONSTRAINT [DF_allPrice] DEFAULT 0.0,
CONSTRAINT [PK_orderId_bookId] PRIMARY KEY CLUSTERED
(
[orderId] ASC,
[bookId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[orderProduceInfo]  WITH CHECK ADD  CONSTRAINT [FK_bookId] FOREIGN KEY([bookId])
REFERENCES [dbo].[bookInfo] ([bookId])
GO
ALTER TABLE [dbo].[orderProduceInfo] CHECK CONSTRAINT [FK_bookId]
GO
ALTER TABLE [dbo].[orderProduceInfo]  WITH CHECK ADD  CONSTRAINT [FK_orderId] FOREIGN KEY([orderId])
REFERENCES [dbo].[orderFormInfo] ([orderId])
GO
ALTER TABLE [dbo].[orderProduceInfo] CHECK CONSTRAINT [FK_orderId]
GO




--评价书表
CREATE TABLE [dbo].[reviewInfo](
[reviewId] [int] IDENTITY(4001,1) NOT NULL,
[muid] [nvarchar](18) NOT NULL,
[bookId] [int] NOT NULL,
[content] [nvarchar](500) NOT NULL,
[reviewTime] [datetime] NOT NULL CONSTRAINT [DF_reviewTime]  DEFAULT (GETDATE()),
CONSTRAINT [PK_reviewId] PRIMARY KEY CLUSTERED
(
[reviewId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[reviewInfo]  WITH CHECK ADD  CONSTRAINT [FK_bookId2] FOREIGN KEY([bookId])
REFERENCES [dbo].[bookInfo] ([bookId])
GO
ALTER TABLE [dbo].[reviewInfo] CHECK CONSTRAINT [FK_bookId2]
GO
ALTER TABLE [dbo].[reviewInfo]  WITH CHECK ADD  CONSTRAINT [FK_muid2] FOREIGN KEY([muid])
REFERENCES [dbo].[memberInfo] ([muid])
GO
ALTER TABLE [dbo].[reviewInfo] CHECK CONSTRAINT [FK_muid2]
GO