学Java-图书贩卖系统-数据库构造[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');