-- 用户表
CREATE TABLE `user`
(
userId INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
`password` VARCHAR(20) NOT NULL,
email VARCHAR(50) NOT NULL,
address VARCHAR(50) NOT NULL
);
-- 图书表
CREATE TABLE book
(
bookId INT PRIMARY KEY AUTO_INCREMENT,
bookName VARCHAR(50) NOT NULL,
bookPrice DOUBLE NOT NULL,
bookSum INT NOT NULL,
bookPic VARCHAR(20) NOT NULL
);
-- 购物车表
CREATE TABLE shopping
(
shoppingId INT PRIMARY KEY AUTO_INCREMENT,
userId INT NOT NULL,
bookId INT NOT NULL,
bookNum INT NOT NULL
);
-- 订单表
CREATE TABLE `order`
(
orderId INT PRIMARY KEY AUTO_INCREMENT,
userId INT NOT NULL,
sumPrice DOUBLE NOT NULL,
state TINYINT NOT NULL,
orderDate DATETIME NOT NULL
);
-- 订单明细表
CREATE TABLE detail
(
detailId INT PRIMARY KEY AUTO_INCREMENT,
orderId INT NOT NULL,
bookId INT NOT NULL,
bookSum INT NOT NULL
);
-- 添加外键
ALTER TABLE shopping ADD CONSTRAINT FK_userId1 FOREIGN KEY (userId) REFERENCES `user`(userId);
ALTER TABLE shopping ADD CONSTRAINT FK_bookId1 FOREIGN KEY (bookId) REFERENCES `book`(bookId);
ALTER TABLE `order` ADD CONSTRAINT FK_userId2 FOREIGN KEY (userId) REFERENCES `user`(userId);
ALTER TABLE detail ADD CONSTRAINT FK_orderId FOREIGN KEY (orderId) REFERENCES `order`(orderId);
ALTER TABLE detail ADD CONSTRAINT FK_bookId2 FOREIGN KEY (bookId) REFERENCES `book`(bookId);
-- 初始化数据
INSERT INTO `user` (username,`password`,email,address) VALUES('admin','admin','[email protected]','北京市海淀区');
INSERT INTO `user` (username,`password`,email,address) VALUES('jack','jack','[email protected]','北京市宣武区');
INSERT INTO `user` (username,`password`,email,address) VALUES('rose','rose','[email protected]','北京市朝阳区');
INSERT INTO `book` (bookName,bookPrice,bookSum,bookPic) VALUES('泰戈尔诗集',18.0,999,'book_01.gif');
INSERT INTO `book` (bookName,bookPrice,bookSum,bookPic) VALUES('痕迹',22.8,999,'book_02.gif');
INSERT INTO `book` (bookName,bookPrice,bookSum,bookPic) VALUES('天堂之旅',25.0,999,'book_03.gif');
INSERT INTO `book` (bookName,bookPrice,bookSum,bookPic) VALUES('钱钟书全集(共十册)',332.5,999,'book_04.gif');
INSERT INTO `book` (bookName,bookPrice,bookSum,bookPic) VALUES('赵俪生高昭—夫妻回忆录',38.0,999,'book_05.gif');
INSERT INTO `book` (bookName,bookPrice,bookSum,bookPic) VALUES('无聊斋(张绍刚首部随笔杂文作品)',28.0,999,'book_06.gif');
INSERT INTO `book` (bookName,bookPrice,bookSum,bookPic) VALUES('一颗热土豆是一张温馨的床',38.0,999,'book_07.gif');
INSERT INTO `book` (bookName,bookPrice,bookSum,bookPic) VALUES('李戡戡乱记',22.0,999,'book_08.gif');
INSERT INTO `book` (bookName,bookPrice,bookSum,bookPic) VALUES('生生世世未了缘',17.5,999,'book_09.gif');
INSERT INTO `book` (bookName,bookPrice,bookSum,bookPic) VALUES('一生有多少爱',17.5,999,'book_10.gif');