客户订购登记数据库设计

/* Navicat MySQL Data Transfer Source Server : exp Source Server Version : 50146 Source Host : localhost:3306 Source Database : 客户订购 Target Server Type : MYSQL Target Server Version : 50146 File Encoding : 65001 Date: 2010-07-04 18:22:31 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `订单` -- ---------------------------- DROP TABLE IF EXISTS `订单`; CREATE TABLE `订单` ( `订单编号` int(10) NOT NULL AUTO_INCREMENT, `消费者编号` int(10) NOT NULL, `下单时间` datetime NOT NULL, PRIMARY KEY (`订单编号`), KEY `消费者编号` (`消费者编号`), CONSTRAINT `@8ba2@5355_ibfk_1` FOREIGN KEY (`消费者编号`) REFERENCES `消费者` (`消费者编号`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gb2312; -- ---------------------------- -- Records of 订单 -- ---------------------------- INSERT INTO `订单` VALUES ('1', '1', '2010-07-04 16:57:19'); INSERT INTO `订单` VALUES ('2', '2', '2010-07-04 16:58:48'); -- ---------------------------- -- Table structure for `订单细则` -- ---------------------------- DROP TABLE IF EXISTS `订单细则`; CREATE TABLE `订单细则` ( `细则编号` int(10) NOT NULL AUTO_INCREMENT, `订单编号` int(10) NOT NULL, `商品编号` int(10) NOT NULL, `订购数量` int(10) NOT NULL, PRIMARY KEY (`细则编号`), KEY `订单编号` (`订单编号`), KEY `商品编号` (`商品编号`), CONSTRAINT `@8ba2@5355@7ec6@5219_ibfk_2` FOREIGN KEY (`订单编号`) REFERENCES `订单` (`订单编号`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `@8ba2@5355@7ec6@5219_ibfk_3` FOREIGN KEY (`商品编号`) REFERENCES `商品` (`商品编号`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=gb2312; -- ---------------------------- -- Records of 订单细则 -- ---------------------------- INSERT INTO `订单细则` VALUES ('3', '1', '3', '1'); INSERT INTO `订单细则` VALUES ('4', '1', '4', '2'); INSERT INTO `订单细则` VALUES ('5', '1', '5', '1'); INSERT INTO `订单细则` VALUES ('6', '2', '6', '2'); INSERT INTO `订单细则` VALUES ('7', '2', '3', '2'); -- ---------------------------- -- Table structure for `发票` -- ---------------------------- DROP TABLE IF EXISTS `发票`; CREATE TABLE `发票` ( `发票编号` int(10) NOT NULL AUTO_INCREMENT, `订单编号` int(10) NOT NULL, `开票时间` datetime NOT NULL, `支付方式` enum('信用卡','支票','现金') NOT NULL DEFAULT '现金', PRIMARY KEY (`发票编号`), KEY `订单编号` (`订单编号`) USING BTREE, CONSTRAINT `@53d1@7968_ibfk_1` FOREIGN KEY (`订单编号`) REFERENCES `订单` (`订单编号`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gb2312 ROW_FORMAT=COMPACT; -- ---------------------------- -- Records of 发票 -- ---------------------------- INSERT INTO `发票` VALUES ('3', '1', '2010-07-04 18:19:11', '现金'); INSERT INTO `发票` VALUES ('4', '2', '2010-07-04 18:19:36', '支票'); -- ---------------------------- -- Table structure for `商品` -- ---------------------------- DROP TABLE IF EXISTS `商品`; CREATE TABLE `商品` ( `商品编号` int(10) NOT NULL AUTO_INCREMENT, `商品名称` varchar(50) NOT NULL, `单价` decimal(10,2) NOT NULL, ` 库存数量` int(10) NOT NULL, `商品描述` varchar(200) DEFAULT NULL, PRIMARY KEY (`商品编号`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=gb2312; -- ---------------------------- -- Records of 商品 -- ---------------------------- INSERT INTO `商品` VALUES ('2', '高露洁牙膏', '3.50', '200', '250g'); INSERT INTO `商品` VALUES ('3', '漫步者低音炮', '96.00', '2', '音箱'); INSERT INTO `商品` VALUES ('4', '三笑牙刷', '2.00', '100', null); INSERT INTO `商品` VALUES ('5', 'NOKIA 5320', '1200.00', '10', '手机'); INSERT INTO `商品` VALUES ('6', '水杯', '10.00', '12', null); -- ---------------------------- -- Table structure for `消费者` -- ---------------------------- DROP TABLE IF EXISTS `消费者`; CREATE TABLE `消费者` ( `消费者编号` int(10) NOT NULL AUTO_INCREMENT, `姓名` varchar(20) NOT NULL, `电话` varchar(11) NOT NULL, `地址` varchar(100) NOT NULL, `邮编` int(6) NOT NULL, `性别` enum('女','男') DEFAULT NULL, `电子邮件` varchar(30) DEFAULT NULL, `备注` varchar(200) DEFAULT NULL, PRIMARY KEY (`消费者编号`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=gb2312; -- ---------------------------- -- Records of 消费者 -- ---------------------------- INSERT INTO `消费者` VALUES ('1', '徐剑', '15827218888', '武汉', '430036', '男', 'v39192@126.com', null); INSERT INTO `消费者` VALUES ('2', '尹作文', '88888888', '武汉', '430063', '女', null, null); INSERT INTO `消费者` VALUES ('3', '刘健', '11111111', '河北', '61032', '女', null, null); INSERT INTO `消费者` VALUES ('4', '袁小宝', '22222222', 'H1-101', '430063', null, null, null); INSERT INTO `消费者` VALUES ('5', '郑传建', '55555555', '武汉', '430036', '男', 'zheng@126.com', null);