/*
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');
