没学过sqlserver 参加学校的比赛必须用sqlserver数据库,我这些代码都是Mysql中的,麻烦大神指点一下怎么改成sqlserver的
*==============================================================*/
/* Table: BigType */
/*==============================================================*/
CREATE TABLE BigType
(
bigId INT(4) NOT NULL AUTO_INCREMENT,
bigName VARCHAR(20),
bigCreateTime DATE,
PRIMARY KEY (bigId)
);/*==============================================================*/
/* Table: Goods */
/*==============================================================*/
CREATE TABLE Goods
(
goodsId INT(8) NOT NULL AUTO_INCREMENT,
smallId INT(4),
goodsName VARCHAR(20),
goodsFrom VARCHAR(20),
price NUMERIC(8,2),
freePrice NUMERIC(8,2),
storeNumber INT,
goodsCreateTime DATE,
goodsImg VARCHAR(50),
goodsDesc VARCHAR(255),
isFree INT,
saleNumber INT, default '0'
PRIMARY KEY (goodsId)
);/*==============================================================*/
/* Table: Link */
/*==============================================================*/
CREATE TABLE Link
(
LinkId INT(4) NOT NULL AUTO_INCREMENT,
LinkName VARCHAR(20),
LinkUrl VARCHAR(255),
PRIMARY KEY (LinkId)
);/*==============================================================*/
/* Table: Master */
/*==============================================================*/
CREATE TABLE MASTER
(
MasterId INT(4) NOT NULL AUTO_INCREMENT,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
realName VARCHAR(20),
PRIMARY KEY (MasterId)
);/*==============================================================*/
/* Table: Member */
/*==============================================================*/
CREATE TABLE Member
(
memberid INT(4) NOT NULL AUTO_INCREMENT,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
realName VARCHAR(20),
age VARCHAR(20),
job VARCHAR(20),
email VARCHAR(50),
question VARCHAR(200),
answer VARCHAR(100),
PRIMARY KEY (memberId)
);/*==============================================================*/
/* Table: OrderDetail */
/*==============================================================*/
CREATE TABLE OrderDetail
(
itemId INT(8) NOT NULL AUTO_INCREMENT,
goodsId INT(8),
orderId VARCHAR(20),
itemNumber INT,
itemPrice NUMERIC(8,2),
PRIMARY KEY (itemId)
);/*==============================================================*/
/* Table: UserOrder */
/*==============================================================*/
CREATE TABLE UserOrder
(
orderId VARCHAR(20) NOT NULL,
totalPrice NUMERIC(10,2),
userName VARCHAR(20),
realName VARCHAR(20),
userAddress VARCHAR(20),
usertel CHAR(11),
payment VARCHAR(10),
postType VARCHAR(10),
note VARCHAR(255),
orderCreateTime DATE,
isSend INT,
PRIMARY KEY (orderId)
);/*==============================================================*/
/* Table: affiche */
/*==============================================================*/
CREATE TABLE affiche
(
afficheId INT(4) NOT NULL AUTO_INCREMENT,
afficheTitle VARCHAR(20),
afficheContent VARCHAR(255),
afficheCreateTime DATE,
PRIMARY KEY (afficheId)
);/*==============================================================*/
/* Table: smallType */
/*==============================================================*/
CREATE TABLE smallType
(
smallId INT(4) NOT NULL AUTO_INCREMENT,
bigId INT(4),
smallName VARCHAR(20),
smallCreateTime DATE,
PRIMARY KEY (smallId)
);ALTER TABLE Goods ADD CONSTRAINT FK_Relationship_2 FOREIGN KEY (smallId)
REFERENCES smallType (smallId) ON DELETE RESTRICT ON UPDATE RESTRICT;ALTER TABLE OrderDetail ADD CONSTRAINT FK_Relationship_3 FOREIGN KEY (orderId)
REFERENCES UserOrder (orderId) ON DELETE RESTRICT ON UPDATE RESTRICT;ALTER TABLE OrderDetail ADD CONSTRAINT FK_Relationship_4 FOREIGN KEY (goodsId)
REFERENCES Goods (goodsId) ON DELETE RESTRICT ON UPDATE RESTRICT;ALTER TABLE smallType ADD CONSTRAINT FK_Relationship_1 FOREIGN KEY (bigId)
REFERENCES BigType (bigId) ON DELETE RESTRICT ON UPDATE RESTRICT;
*==============================================================*/
/* Table: BigType */
/*==============================================================*/
CREATE TABLE BigType
(
bigId INT(4) NOT NULL AUTO_INCREMENT,
bigName VARCHAR(20),
bigCreateTime DATE,
PRIMARY KEY (bigId)
);/*==============================================================*/
/* Table: Goods */
/*==============================================================*/
CREATE TABLE Goods
(
goodsId INT(8) NOT NULL AUTO_INCREMENT,
smallId INT(4),
goodsName VARCHAR(20),
goodsFrom VARCHAR(20),
price NUMERIC(8,2),
freePrice NUMERIC(8,2),
storeNumber INT,
goodsCreateTime DATE,
goodsImg VARCHAR(50),
goodsDesc VARCHAR(255),
isFree INT,
saleNumber INT, default '0'
PRIMARY KEY (goodsId)
);/*==============================================================*/
/* Table: Link */
/*==============================================================*/
CREATE TABLE Link
(
LinkId INT(4) NOT NULL AUTO_INCREMENT,
LinkName VARCHAR(20),
LinkUrl VARCHAR(255),
PRIMARY KEY (LinkId)
);/*==============================================================*/
/* Table: Master */
/*==============================================================*/
CREATE TABLE MASTER
(
MasterId INT(4) NOT NULL AUTO_INCREMENT,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
realName VARCHAR(20),
PRIMARY KEY (MasterId)
);/*==============================================================*/
/* Table: Member */
/*==============================================================*/
CREATE TABLE Member
(
memberid INT(4) NOT NULL AUTO_INCREMENT,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
realName VARCHAR(20),
age VARCHAR(20),
job VARCHAR(20),
email VARCHAR(50),
question VARCHAR(200),
answer VARCHAR(100),
PRIMARY KEY (memberId)
);/*==============================================================*/
/* Table: OrderDetail */
/*==============================================================*/
CREATE TABLE OrderDetail
(
itemId INT(8) NOT NULL AUTO_INCREMENT,
goodsId INT(8),
orderId VARCHAR(20),
itemNumber INT,
itemPrice NUMERIC(8,2),
PRIMARY KEY (itemId)
);/*==============================================================*/
/* Table: UserOrder */
/*==============================================================*/
CREATE TABLE UserOrder
(
orderId VARCHAR(20) NOT NULL,
totalPrice NUMERIC(10,2),
userName VARCHAR(20),
realName VARCHAR(20),
userAddress VARCHAR(20),
usertel CHAR(11),
payment VARCHAR(10),
postType VARCHAR(10),
note VARCHAR(255),
orderCreateTime DATE,
isSend INT,
PRIMARY KEY (orderId)
);/*==============================================================*/
/* Table: affiche */
/*==============================================================*/
CREATE TABLE affiche
(
afficheId INT(4) NOT NULL AUTO_INCREMENT,
afficheTitle VARCHAR(20),
afficheContent VARCHAR(255),
afficheCreateTime DATE,
PRIMARY KEY (afficheId)
);/*==============================================================*/
/* Table: smallType */
/*==============================================================*/
CREATE TABLE smallType
(
smallId INT(4) NOT NULL AUTO_INCREMENT,
bigId INT(4),
smallName VARCHAR(20),
smallCreateTime DATE,
PRIMARY KEY (smallId)
);ALTER TABLE Goods ADD CONSTRAINT FK_Relationship_2 FOREIGN KEY (smallId)
REFERENCES smallType (smallId) ON DELETE RESTRICT ON UPDATE RESTRICT;ALTER TABLE OrderDetail ADD CONSTRAINT FK_Relationship_3 FOREIGN KEY (orderId)
REFERENCES UserOrder (orderId) ON DELETE RESTRICT ON UPDATE RESTRICT;ALTER TABLE OrderDetail ADD CONSTRAINT FK_Relationship_4 FOREIGN KEY (goodsId)
REFERENCES Goods (goodsId) ON DELETE RESTRICT ON UPDATE RESTRICT;ALTER TABLE smallType ADD CONSTRAINT FK_Relationship_1 FOREIGN KEY (bigId)
REFERENCES BigType (bigId) ON DELETE RESTRICT ON UPDATE RESTRICT;
/* Table: BigType */
/*==============================================================*/
CREATE TABLE BigType
(
bigId INT identity(1,1) NOT NULL primary key,
bigName VARCHAR(20),
bigCreateTime DATE
)/*==============================================================*/
/* Table: Goods */
/*==============================================================*/
CREATE TABLE Goods
(
goodsId BIGINT identity(1,1) NOT NULL PRIMARY KEY,
smallId INT,
goodsName VARCHAR(20),
goodsFrom VARCHAR(20),
price NUMERIC(8,2),
freePrice NUMERIC(8,2),
storeNumber INT,
goodsCreateTime DATE,
goodsImg VARCHAR(50),
goodsDesc VARCHAR(255),
isFree INT,
saleNumber INT default '0'
);/*==============================================================*/
/* Table: Link */
/*==============================================================*/
CREATE TABLE Link
(
LinkId INT identity(1,1) NOT NULL PRIMARY KEY,
LinkName VARCHAR(20),
LinkUrl VARCHAR(255)
);/*==============================================================*/
/* Table: Master */
/*==============================================================*/
CREATE TABLE MASTER
(
MasterId INT identity(1,1) NOT NULL PRIMARY KEY,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
realName VARCHAR(20)
);/*==============================================================*/
/* Table: Member */
/*==============================================================*/
CREATE TABLE Member
(
memberid INT identity(1,1)NOT NULL PRIMARY KEY,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
realName VARCHAR(20),
age VARCHAR(20),
job VARCHAR(20),
email VARCHAR(50),
question VARCHAR(200),
answer VARCHAR(100)
);/*==============================================================*/
/* Table: OrderDetail */
/*==============================================================*/
CREATE TABLE OrderDetail
(
itemId BIGINT identity(1,1) NOT NULL PRIMARY KEY ,
goodsId BIGINT,
orderId VARCHAR(20),
itemNumber INT,
itemPrice NUMERIC(8,2)
);/*==============================================================*/
/* Table: UserOrder */
/*==============================================================*/
CREATE TABLE UserOrder
(
orderId VARCHAR(20) NOT NULL primary key,
totalPrice NUMERIC(10,2),
userName VARCHAR(20),
realName VARCHAR(20),
userAddress VARCHAR(20),
usertel CHAR(11),
payment VARCHAR(10),
postType VARCHAR(10),
note VARCHAR(255),
orderCreateTime DATE,
isSend INT
);/*==============================================================*/
/* Table: affiche */
/*==============================================================*/
CREATE TABLE affiche
(
afficheId INT identity(1,1)NOT NULL primary key,
afficheTitle VARCHAR(20),
afficheContent VARCHAR(255),
afficheCreateTime DATE
);/*==============================================================*/
/* Table: smallType */
/*==============================================================*/
CREATE TABLE smallType
(
smallId INT identity(1,1) NOT NULL primary key,
bigId INT,
smallName VARCHAR(20),
smallCreateTime DATE,DATE--SQL2008以上版本可用,其它版本用Datetime
);ALTER TABLE Goods ADD CONSTRAINT FK_Relationship_2 FOREIGN KEY (smallId)
REFERENCES smallType (smallId) ON DELETE cascade ON UPDATE cascade;ALTER TABLE OrderDetail ADD CONSTRAINT FK_Relationship_3 FOREIGN KEY (orderId)
REFERENCES UserOrder (orderId) ON DELETE cascade ON UPDATE cascade;ALTER TABLE OrderDetail ADD CONSTRAINT FK_Relationship_4 FOREIGN KEY (goodsId)
REFERENCES Goods (goodsId) ON DELETE cascade ON UPDATE cascade;ALTER TABLE smallType ADD CONSTRAINT FK_Relationship_1 FOREIGN KEY (bigId)
REFERENCES BigType (bigId) ON DELETE cascade ON UPDATE cascade;