跟老紫竹学Java-图书销售系统-数据库结构-优快云博客

/*
MySQL Data Transfer
Source Host: localhost
Source Database: book
Target Host: localhost
Target Database: book
Date: 2008-7-25 13:55:48
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_book
-- ----------------------------
CREATE TABLE `t_book` (
  `id` int(11) NOT NULL auto_increment COMMENT '流水号',
  `name` varchar(100) collate utf8_unicode_ci NOT NULL COMMENT '书名',
  `price` decimal(10,2) NOT NULL COMMENT '价格',
  `quantity` int(11) NOT NULL COMMENT '数量',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Table structure for t_bookstockhistory
-- ----------------------------
CREATE TABLE `t_bookstockhistory` (
  `id` int(11) NOT NULL auto_increment COMMENT '流水号',
  `bookId` int(11) NOT NULL COMMENT '图书编号',
  `quantity` int(11) NOT NULL COMMENT '数量',
  `quantityOutstanding` int(11) NOT NULL COMMENT '数量结存',
  `price` decimal(10,2) NOT NULL COMMENT '价格',
  `datetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '时间',
  `note` varchar(255) collate utf8_unicode_ci default NULL COMMENT '备注',
  PRIMARY KEY  (`id`),
  KEY `bookId` (`bookId`),
  CONSTRAINT `t_bookstockhistory_ibfk_1` FOREIGN KEY (`bookId`) REFERENCES `t_book` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Table structure for t_order
-- ----------------------------
CREATE TABLE `t_order` (
  `id` int(11) NOT NULL auto_increment COMMENT '流水号',
  `UserId` int(11) NOT NULL COMMENT '会员编号',
  `Amount` decimal(10,0) NOT NULL COMMENT '总金额',
  `Datetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '时间',
  `Note` varchar(200) collate utf8_unicode_ci default NULL COMMENT '备注',
  PRIMARY KEY  (`id`),
  KEY `UserId` (`UserId`),
  CONSTRAINT `t_order_ibfk_1` FOREIGN KEY (`UserId`) REFERENCES `t_user` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Table structure for t_orderbook
-- ----------------------------
CREATE TABLE `t_orderbook` (
  `Id` int(11) NOT NULL auto_increment COMMENT '流水号',
  `OrderId` int(11) NOT NULL COMMENT '订单号',
  `BookId` int(11) NOT NULL COMMENT '图书编号',
  `Quantity` int(11) NOT NULL COMMENT '数量',
  PRIMARY KEY  (`Id`),
  KEY `OrderId` (`OrderId`),
  KEY `BookId` (`BookId`),
  CONSTRAINT `t_orderbook_ibfk_1` FOREIGN KEY (`OrderId`) REFERENCES `t_order` (`id`),
  CONSTRAINT `t_orderbook_ibfk_2` FOREIGN KEY (`BookId`) REFERENCES `t_book` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
CREATE TABLE `t_user` (
  `Id` int(11) NOT NULL auto_increment COMMENT '流水号',
  `username` varchar(20) collate utf8_unicode_ci NOT NULL COMMENT '用户名',
  `password` varchar(50) collate utf8_unicode_ci NOT NULL COMMENT '密码',
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `t_book` VALUES ('1', 'Java基础12', '25.01', '0');
INSERT INTO `t_book` VALUES ('2', 'Java高级编程', '89.23', '2');
INSERT INTO `t_book` VALUES ('3', '跟老紫竹学Java', '50.45', '3');
INSERT INTO `t_book` VALUES ('4', 'Java大全', '111.00', '4');
INSERT INTO `t_bookstockhistory` VALUES ('1', '3', '1', '1', '50.45', '2008-07-25 12:27:21', null);
INSERT INTO `t_bookstockhistory` VALUES ('2', '2', '1', '99', '89.23', '2008-07-25 12:29:18', null);
INSERT INTO `t_bookstockhistory` VALUES ('3', '3', '1', '0', '50.45', '2008-07-25 12:29:18', null);
INSERT INTO `t_bookstockhistory` VALUES ('4', '1', '1', '0', '25.01', '2008-07-25 13:32:24', '会员购买');
INSERT INTO `t_order` VALUES ('7', '2', '2238', '2008-07-25 11:33:27', null);
INSERT INTO `t_order` VALUES ('8', '2', '165', '2008-07-25 11:52:38', null);
INSERT INTO `t_order` VALUES ('9', '2', '25', '2008-07-25 11:58:51', null);
INSERT INTO `t_order` VALUES ('10', '2', '25', '2008-07-25 12:00:00', null);
INSERT INTO `t_order` VALUES ('11', '2', '140', '2008-07-25 12:29:18', null);
INSERT INTO `t_order` VALUES ('12', '1', '25', '2008-07-25 13:32:24', null);
INSERT INTO `t_orderbook` VALUES ('5', '7', '1', '11');
INSERT INTO `t_orderbook` VALUES ('6', '7', '2', '22');
INSERT INTO `t_orderbook` VALUES ('7', '8', '1', '1');
INSERT INTO `t_orderbook` VALUES ('8', '8', '2', '1');
INSERT INTO `t_orderbook` VALUES ('9', '8', '3', '1');
INSERT INTO `t_orderbook` VALUES ('10', '9', '1', '1');
INSERT INTO `t_orderbook` VALUES ('11', '10', '1', '1');
INSERT INTO `t_orderbook` VALUES ('12', '11', '2', '1');
INSERT INTO `t_orderbook` VALUES ('13', '11', '3', '1');
INSERT INTO `t_orderbook` VALUES ('14', '12', '1', '1');
INSERT INTO `t_user` VALUES ('1', 'admin', 'admin');
INSERT INTO `t_user` VALUES ('2', 'user', 'user');
INSERT INTO `t_user` VALUES ('3', 'test', 'test');