📄 bookstore.sql
字号:
/*
SQLyog - Free MySQL GUI v5.12
Host - 5.0.21-community-nt : Database - bookstore
*********************************************************************
Server version : 5.0.21-community-nt
*/
SET NAMES utf8;
SET SQL_MODE='';
create database if not exists `bookstore`;
USE `bookstore`;
/*Table structure for table `bookshelf` */
DROP TABLE IF EXISTS `bookshelf`;
CREATE TABLE `bookshelf` (
`shelfId` int(11) NOT NULL auto_increment,
`parentShelfId` int(11) NOT NULL,
`shelfName` varchar(20) NOT NULL,
PRIMARY KEY (`shelfId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `bookshelf` */
insert into `bookshelf` (`shelfId`,`shelfName`,`parentShelfId`) values (1,'古典文学',0);
insert into `bookshelf` (`shelfId`,`shelfName`,`parentShelfId`) values (2,'现代文学',0);
insert into `bookshelf` (`shelfId`,`shelfName`,`parentShelfId`) values (3,'IT',0);
insert into `bookshelf` (`shelfId`,`shelfName`,`parentShelfId`) values (4,'散文',1);
insert into `bookshelf` (`shelfId`,`shelfName`,`parentShelfId`) values (5,'美文',2);
insert into `bookshelf` (`shelfId`,`shelfName`,`parentShelfId`) values (6,'计算机',3);
insert into `bookshelf` (`shelfId`,`shelfName`,`parentShelfId`) values (7,'小说',1);
insert into `bookshelf` (`shelfId`,`shelfName`,`parentShelfId`) values (8,'言情',2);
insert into `bookshelf` (`shelfId`,`shelfName`,`parentShelfId`) values (9,'软件',3);
/*Table structure for table `admininfo` */
DROP TABLE IF EXISTS `admininfo`;
CREATE TABLE `admininfo` (
`adminId` int(11) NOT NULL auto_increment,
`adminName` varchar(20) NOT NULL,
`adminPwd` varchar(16) NOT NULL,
`adminLevel` int(11) NOT NULL,
`adminTime` datetime NOT NULL,
PRIMARY KEY (`adminId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `admininfo` */
insert into `admininfo` (`adminId`,`adminName`,`adminPwd`,`adminLevel`,`adminTime`) values (1,'yzf','123',1,'2008-08-08 00:00:00');
insert into `admininfo` (`adminId`,`adminName`,`adminPwd`,`adminLevel`,`adminTime`) values (2,'zhangsan','123',2,'2008-08-08 00:00:00');
insert into `admininfo` (`adminId`,`adminName`,`adminPwd`,`adminLevel`,`adminTime`) values (3,'lisi','123',2,'2008-08-08 00:00:00');
/*Table structure for table `userinfo` */
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
`userId` int(11) NOT NULL auto_increment,
`userName` varchar(30) NOT NULL,
`userPwd` varchar(16) NOT NULL,
`userQuestion` varchar(50) NOT NULL,
`userAnswer` varchar(50) NOT NULL,
`userRname` varchar(15) NOT NULL,
`userAge` int(11) NOT NULL,
`userSex` int(11) NOT NULL,
`userTel` varchar(15) NOT NULL,
`userAddress` varchar(100) NOT NULL,
`userTime` datetime NOT NULL,
`userPost` varchar(6) NOT NULL,
PRIMARY KEY (`userId`),
UNIQUE KEY `UQ_userName` (`userName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `userinfo` */
insert into `userinfo` (`userId`,`userName`,`userPwd`,`userQuestion`,`userAnswer`,`userRname`,`userAge`,`userSex`,`userTel`,`userAddress`,`userTime`,`userPost`) values (1,'成龙','123','我的名字','成龙','阿龙',48,1,'1010110','香港九龙','2008-08-08 00:00:00','777777');
insert into `userinfo` (`userId`,`userName`,`userPwd`,`userQuestion`,`userAnswer`,`userRname`,`userAge`,`userSex`,`userTel`,`userAddress`,`userTime`,`userPost`) values (2,'杨过','123','我的名字','成龙','阿过',48,1,'1010110','忧伤谷','2008-08-09 00:00:00','888888');
insert into `userinfo` (`userId`,`userName`,`userPwd`,`userQuestion`,`userAnswer`,`userRname`,`userAge`,`userSex`,`userTel`,`userAddress`,`userTime`,`userPost`) values (3,'小龙女','123','我的名字','小龙女','阿龙',48,0,'1010110','陕西-华山','2008-08-10 00:00:00','999999');
/*Table structure for table `bookinfo` */
DROP TABLE IF EXISTS `bookinfo`;
CREATE TABLE `bookinfo` (
`bookId` int(11) NOT NULL auto_increment,
`bookName` varchar(50) NOT NULL,
`bookAuthor` varchar(50) NOT NULL,
`bookPrice` double NOT NULL,
`bookDiscount` double NOT NULL,
`bookPublish` varchar(50) NOT NULL,
`bookContent` varchar(100) NOT NULL,
`bookImgPath` varchar(100) NOT NULL,
`shelfId` int(11) NOT NULL,
`clickCount` int(11) NOT NULL,
`bookShow` int(11) NOT NULL,
PRIMARY KEY (`bookId`),
KEY `FK_shelfId` (`shelfId`),
CONSTRAINT `FK_shelfId` FOREIGN KEY (`shelfId`) REFERENCES `bookshelf` (`shelfId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `bookinfo` */
insert into `bookinfo` (`bookId`,`bookName`,`bookAuthor`,`bookPrice`,`bookDiscount`,`bookPublish`,`bookContent`,`bookImgPath`,`shelfId`,`clickCount`,`bookShow`) values (1,'蝴蝶飞','YZF',100,0.8,'交大出版社','蝴蝶飞联想到的爱情...','img/die.jpg',5,850,1);
insert into `bookinfo` (`bookId`,`bookName`,`bookAuthor`,`bookPrice`,`bookDiscount`,`bookPublish`,`bookContent`,`bookImgPath`,`shelfId`,`clickCount`,`bookShow`) values (2,'蝴蝶追','YZF',100,0.9,'交大出版社','蝴蝶追联想到的...','img/die.jpg',5,500,1);
insert into `bookinfo` (`bookId`,`bookName`,`bookAuthor`,`bookPrice`,`bookDiscount`,`bookPublish`,`bookContent`,`bookImgPath`,`shelfId`,`clickCount`,`bookShow`) values (3,'变成有钱人','YZF',230,0.95,'人民出版社','教你成为有钱人的方法','img/die.jpg',6,1500,1);
insert into `bookinfo` (`bookId`,`bookName`,`bookAuthor`,`bookPrice`,`bookDiscount`,`bookPublish`,`bookContent`,`bookImgPath`,`shelfId`,`clickCount`,`bookShow`) values (4,'飞狐外传','金庸',100,0.9,'上海出版社','飞狐...','img/feihu.jpg',7,1000,1);
insert into `bookinfo` (`bookId`,`bookName`,`bookAuthor`,`bookPrice`,`bookDiscount`,`bookPublish`,`bookContent`,`bookImgPath`,`shelfId`,`clickCount`,`bookShow`) values (5,'连城诀','金庸',130,0.85,'台湾出版社','连城诀......','img/lianchengjue.jpg',8,800,1);
insert into `bookinfo` (`bookId`,`bookName`,`bookAuthor`,`bookPrice`,`bookDiscount`,`bookPublish`,`bookContent`,`bookImgPath`,`shelfId`,`clickCount`,`bookShow`) values (6,'鹿鼎记','金庸',230,0.75,'北京出版社','韦小宝......','img/ludingji.jpg',9,1050,1);
insert into `bookinfo` (`bookId`,`bookName`,`bookAuthor`,`bookPrice`,`bookDiscount`,`bookPublish`,`bookContent`,`bookImgPath`,`shelfId`,`clickCount`,`bookShow`) values (7,'射雕英雄传','金庸',88,0.7,'内蒙古出版社','射雕英雄传传奇......','img/shediao.jpg',4,750,1);
insert into `bookinfo` (`bookId`,`bookName`,`bookAuthor`,`bookPrice`,`bookDiscount`,`bookPublish`,`bookContent`,`bookImgPath`,`shelfId`,`clickCount`,`bookShow`) values (8,'书剑恩仇录','金庸',150,0.95,'河北出版社','儿女情常......','img/shujian.jpg',9,2218,1);
insert into `bookinfo` (`bookId`,`bookName`,`bookAuthor`,`bookPrice`,`bookDiscount`,`bookPublish`,`bookContent`,`bookImgPath`,`shelfId`,`clickCount`,`bookShow`) values (9,'天龙八部','金庸',145,0.88,'契丹古出版社','天龙八部......','img/long.jpg',4,1001,1);
/*Table structure for table `bookreview` */
DROP TABLE IF EXISTS `bookreview`;
CREATE TABLE `bookreview` (
`reviewId` int(11) NOT NULL auto_increment,
`bookId` int(11) NOT NULL,
`userId` int(11) NOT NULL,
`reviewText` varchar(30) NOT NULL,
`reviewContent` varchar(100) NOT NULL,
`reviewTime` datetime NOT NULL,
PRIMARY KEY (`reviewId`),
KEY `FK_bookreview` (`bookId`),
KEY `FK_bookreview2` (`userId`),
CONSTRAINT `bookreview_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `userinfo` (`userId`),
CONSTRAINT `bookreview_ibfk_1` FOREIGN KEY (`bookId`) REFERENCES `bookinfo` (`bookId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `bookreview` */
insert into `bookreview` (`reviewId`,`bookId`,`userId`,`reviewText`,`reviewContent`,`reviewTime`) values (1,1,1,'强烈推荐','好书呀！','2008-08-08 00:00:00');
/*Table structure for table `booktype` */
DROP TABLE IF EXISTS `booktype`;
CREATE TABLE `booktype` (
`typeId` int(11) NOT NULL auto_increment,
`typeName` varchar(20) NOT NULL,
`bookId` int(11) NOT NULL,
PRIMARY KEY (`typeId`),
KEY `FK_booktype` (`bookId`),
CONSTRAINT `booktype_ibfk_1` FOREIGN KEY (`bookId`) REFERENCES `bookinfo` (`bookId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `booktype` */
insert into `booktype` (`typeId`,`typeName`,`bookId`) values (1,'变成有钱人',3);
insert into `booktype` (`typeId`,`typeName`,`bookId`) values (2,'蝴蝶飞',1);
/*Table structure for table `myfavorite` */
DROP TABLE IF EXISTS `myfavorite`;
CREATE TABLE `myfavorite` (
`favIid` int(11) NOT NULL auto_increment,
`bookId` int(11) NOT NULL,
`userId` int(11) NOT NULL,
`favTime` datetime NOT NULL,
PRIMARY KEY (`favIid`),
KEY `FK_bookId` (`bookId`),
KEY `FK_userId` (`userId`),
CONSTRAINT `FK_bookId` FOREIGN KEY (`bookId`) REFERENCES `bookinfo` (`bookId`),
CONSTRAINT `FK_userId` FOREIGN KEY (`userId`) REFERENCES `userinfo` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `myfavorite` */
insert into `myfavorite` (`favIid`,`bookId`,`userId`,`favTime`) values (1,1,1,'2008-08-08 00:00:00');
insert into `myfavorite` (`favIid`,`bookId`,`userId`,`favTime`) values (2,2,1,'2008-08-08 00:00:00');
/*Table structure for table `newsinfo` */
DROP TABLE IF EXISTS `newsinfo`;
CREATE TABLE `newsinfo` (
`newsId` int(11) NOT NULL auto_increment,
`newsTitle` varchar(100) NOT NULL,
`newsContent` varchar(500) NOT NULL,
`newsHide` tinyint(1) NOT NULL,
`newsTime` datetime NOT NULL,
PRIMARY KEY (`newsId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `newsinfo` */
insert into `newsinfo` (`newsId`,`newsTitle`,`newsContent`,`newsHide`,`newsTime`) values (1,'开店送书咯！！！','为庆祝本店开业：所有书籍大派送，全场6折。',0,'2008-08-08 00:00:00');
insert into `newsinfo` (`newsId`,`newsTitle`,`newsContent`,`newsHide`,`newsTime`) values (2,'喷血大放送','为庆祝本店一个礼拜售书100000本，再次答谢客户厚爱！',1,'2008-08-08 00:00:00');
/*Table structure for table `orderinfo` */
DROP TABLE IF EXISTS `orderinfo`;
CREATE TABLE `orderinfo` (
`orderId` int(11) NOT NULL auto_increment,
`userId` int(11) NOT NULL,
`custmorName` varchar(50) NOT NULL,
`custmorTel` varchar(20) NOT NULL,
`custmorSex` int(11) NOT NULL,
`custmorPostman` varchar(6) NOT NULL,
`custmorAddress` varchar(100) NOT NULL,
`custmorEmail` varchar(30) NOT NULL,
`sendStyle` varchar(20) NOT NULL,
`payStyle` varchar(20) NOT NULL,
`orderState` int(11) NOT NULL,
`orderTime` datetime NOT NULL,
PRIMARY KEY (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `orderinfo` */
insert into `orderinfo` (`orderId`,`userId`,`custmorName`,`custmorTel`,`custmorSex`,`custmorPostman`,`custmorAddress`,`custmorEmail`,`sendStyle`,`payStyle`,`orderState`,`orderTime`) values ('1',1,'成龙','1010110',1,'777777','香港九龙','YZF@126.com','快递','货到付款',1,'2008-08-08 00:00:00');
insert into `orderinfo` (`orderId`,`userId`,`custmorName`,`custmorTel`,`custmorSex`,`custmorPostman`,`custmorAddress`,`custmorEmail`,`sendStyle`,`payStyle`,`orderState`,`orderTime`) values ('2',2,'杨过','1010110',1,'888888','忧伤谷','YZF@126.com','快递','支付宝',2,'2008-08-09 00:00:00');
insert into `orderinfo` (`orderId`,`userId`,`custmorName`,`custmorTel`,`custmorSex`,`custmorPostman`,`custmorAddress`,`custmorEmail`,`sendStyle`,`payStyle`,`orderState`,`orderTime`) values ('3',3,'小龙女','1010110',0,'999999','陕西-华山','YZF@126.com','快递','邮局汇款',3,'2008-08-10 00:00:00');
insert into `orderinfo` (`orderId`,`userId`,`custmorName`,`custmorTel`,`custmorSex`,`custmorPostman`,`custmorAddress`,`custmorEmail`,`sendStyle`,`payStyle`,`orderState`,`orderTime`) values ('4',1,'成龙','1010110',1,'777777','香港九龙','YZF@126.com','快递','货到付款',1,'2008-08-08 00:00:00');
insert into `orderinfo` (`orderId`,`userId`,`custmorName`,`custmorTel`,`custmorSex`,`custmorPostman`,`custmorAddress`,`custmorEmail`,`sendStyle`,`payStyle`,`orderState`,`orderTime`) values ('5',3,'小龙女','1010110',0,'999999','陕西-华山','YZF@126.com','快递','货到付款',4,'2008-08-09 00:00:00');
/*Table structure for table `orderlist` */
DROP TABLE IF EXISTS `orderlist`;
CREATE TABLE `orderlist` (
`listId` int(11) NOT NULL auto_increment,
`orderId` int(11) NOT NULL,
`bookId` int(11) NOT NULL,
`bookCount` int(11) NOT NULL,
`finalPrice` double NOT NULL,
PRIMARY KEY (`listId`),
KEY `FK_orderId` (`orderId`),
CONSTRAINT `FK_orderId` FOREIGN KEY (`orderId`) REFERENCES `orderinfo` (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `orderlist` */
insert into `orderlist` (`listId`,`orderId`,`bookId`,`bookCount`,`finalPrice`) values (1,'1',1,10,1000);
insert into `orderlist` (`listId`,`orderId`,`bookId`,`bookCount`,`finalPrice`) values (2,'1',2,12,1000);
insert into `orderlist` (`listId`,`orderId`,`bookId`,`bookCount`,`finalPrice`) values (3,'2',2,25,1000);
insert into `orderlist` (`listId`,`orderId`,`bookId`,`bookCount`,`finalPrice`) values (4,'3',4,18,1000);
insert into `orderlist` (`listId`,`orderId`,`bookId`,`bookCount`,`finalPrice`) values (5,'3',5,15,1000);
insert into `orderlist` (`listId`,`orderId`,`bookId`,`bookCount`,`finalPrice`) values (6,'4',8,60,1000);
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -