logistic.sql

物流系统之仓库管理 jsp javabean servlet 很详细

-- MySQL dump 10.11
--
-- Host: localhost    Database: logistic
-- ------------------------------------------------------
-- Server version	5.0.37-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES gbk */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `carmanagetable`
--

DROP TABLE IF EXISTS `carmanagetable`;
CREATE TABLE `carmanagetable` (
  `ID` int(4) NOT NULL auto_increment COMMENT '车辆信息表自动ID',
  `CarId` varchar(20) NOT NULL COMMENT '车辆Id',
  `CarDriverId` varchar(20) NOT NULL COMMENT '司机Id',
  `CarTeamId` varchar(20) NOT NULL COMMENT '车队Id',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `CarId` (`CarId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `carmanagetable`
--

LOCK TABLES `carmanagetable` WRITE;
/*!40000 ALTER TABLE `carmanagetable` DISABLE KEYS */;
INSERT INTO `carmanagetable` VALUES (1,'豫ACC277','10009','10005'),(2,'豫ADC200','10009','10009'),(3,'豫A87627','10007','10009'),(4,'豫B23147','10008','10006');
/*!40000 ALTER TABLE `carmanagetable` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `carteaminfotable`
--

DROP TABLE IF EXISTS `carteaminfotable`;
CREATE TABLE `carteaminfotable` (
  `ID` int(4) NOT NULL auto_increment COMMENT '车队信息表自动ID',
  `CarTeamId` varchar(20) NOT NULL COMMENT '车队Id',
  `CarTeamName` varchar(20) NOT NULL COMMENT '车队名称',
  `ChargeId` varchar(20) NOT NULL COMMENT '负责人Id',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `CarTeamId` (`CarTeamId`),
  KEY `FK_carteaminfo_employee` (`ChargeId`),
  CONSTRAINT `carteaminfotable_ibfk_1` FOREIGN KEY (`ChargeId`) REFERENCES `employeeinfotable` (`EmployeeId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `carteaminfotable`
--

LOCK TABLES `carteaminfotable` WRITE;
/*!40000 ALTER TABLE `carteaminfotable` DISABLE KEYS */;
INSERT INTO `carteaminfotable` VALUES (5,'10005','第一车队','10005'),(6,'10006','第二车队','10006'),(7,'10007','第三车队','10005'),(8,'10008','第五车队','10006'),(9,'10009','第四车队','10007');
/*!40000 ALTER TABLE `carteaminfotable` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `commcatetable`
--

DROP TABLE IF EXISTS `commcatetable`;
CREATE TABLE `commcatetable` (
  `ID` int(4) NOT NULL auto_increment COMMENT '商品分类表自动ID',
  `CommodityId` varchar(20) NOT NULL COMMENT '商品分类Id',
  `CommodityName` varchar(20) NOT NULL COMMENT '商品分类名称',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `CommodityId` (`CommodityId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `commcatetable`
--

LOCK TABLES `commcatetable` WRITE;
/*!40000 ALTER TABLE `commcatetable` DISABLE KEYS */;
INSERT INTO `commcatetable` VALUES (1,'10001','电视'),(2,'10002','空调'),(3,'10003','冰箱'),(4,'10004','电脑'),(5,'10005','洗衣机'),(6,'10006','厨具'),(7,'10007','手机');
/*!40000 ALTER TABLE `commcatetable` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `departmentinfotable`
--

DROP TABLE IF EXISTS `departmentinfotable`;
CREATE TABLE `departmentinfotable` (
  `ID` int(4) NOT NULL auto_increment COMMENT '部门信息表自动ID',
  `DepartmentId` varchar(20) NOT NULL COMMENT '部门Id',
  `DepartmentName` varchar(20) NOT NULL COMMENT '部门名称',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `DepartmentId` (`DepartmentId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `departmentinfotable`
--

LOCK TABLES `departmentinfotable` WRITE;
/*!40000 ALTER TABLE `departmentinfotable` DISABLE KEYS */;
INSERT INTO `departmentinfotable` VALUES (1,'1001','研发部'),(2,'1002','销售部'),(3,'1003','采购部'),(4,'1004','售后服务部'),(5,'1005','维修部'),(6,'1007','内勤部'),(7,'1008','运输部');
/*!40000 ALTER TABLE `departmentinfotable` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `diplomainfotable`
--

DROP TABLE IF EXISTS `diplomainfotable`;
CREATE TABLE `diplomainfotable` (
  `ID` int(4) NOT NULL auto_increment,
  `DiplomaName` varchar(20) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `diplomainfotable`
--

LOCK TABLES `diplomainfotable` WRITE;
/*!40000 ALTER TABLE `diplomainfotable` DISABLE KEYS */;
INSERT INTO `diplomainfotable` VALUES (1,'博士后'),(2,'博士'),(3,'硕士'),(4,'学士'),(5,'大专'),(6,'高中'),(7,'中专'),(8,'初中'),(9,'小学');
/*!40000 ALTER TABLE `diplomainfotable` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `employeeinfotable`
--

DROP TABLE IF EXISTS `employeeinfotable`;
CREATE TABLE `employeeinfotable` (
  `ID` int(8) NOT NULL auto_increment COMMENT '职员信息表自动ID',
  `EmployeeId` varchar(20) NOT NULL COMMENT '职员Id',
  `EmployeeName` varchar(20) NOT NULL COMMENT '职员姓名',
  `EmployeeSex` varchar(4) NOT NULL COMMENT '职员性别',
  `EmployeeAge` int(3) NOT NULL COMMENT '职员年龄',
  `EmployeeIdenCard` varchar(20) NOT NULL COMMENT '职员身份证号',
  `EmployeeBirthDate` varchar(20) default NULL COMMENT '职员出生日期',
  `EmployeeDiploma` varchar(20) NOT NULL COMMENT '职员学历',
  `EmployeeJoinDate` varchar(20) default NULL COMMENT '职员加入公司日期',
  `EmployeeAdd` varchar(50) NOT NULL COMMENT '职员家庭住址',
  `EmployeePhone` varchar(20) default NULL COMMENT '职员固定电话',
  `EmployeeMobile` varchar(20) default NULL COMMENT '职员移动电话',
  `DepartmentId` varchar(20) NOT NULL COMMENT '职员所属部门编号',
  `JobsId` varchar(20) NOT NULL COMMENT '职员的职务编号',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `EmployeeId` (`EmployeeId`),
  KEY `FK_employeeinfo_department` (`DepartmentId`),
  CONSTRAINT `employeeinfotable_ibfk_1` FOREIGN KEY (`DepartmentId`) REFERENCES `departmentinfotable` (`DepartmentId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `employeeinfotable`
--

LOCK TABLES `employeeinfotable` WRITE;
/*!40000 ALTER TABLE `employeeinfotable` DISABLE KEYS */;
INSERT INTO `employeeinfotable` VALUES (4,'10003','苏长城','男',23,'410108198309110515','1983-12-14','2','2001-12-11','河南省周口市','037163982910','13837178301','1001','1001'),(6,'10005','陈凯','男',24,'410108198309110515','1983-09-11','1','2002-09-12','河南省郑州市惠济区','037163982910','13837178301','1001','1007'),(7,'10006','张冠军','男',25,'410108198309110511','2007-04-27','1','2007-04-14','河南省平顶山市','037163982910','13837178301','1001','1001'),(8,'10007','冯杰','女',30,'410108198309110515','1982-04-23','1','2003-07-18','河南省信阳市','037163982910','13837178301','1002','1010'),(9,'10008','程传林','男',25,'410108198209120516','1982-03-22','3','2002-06-11','河南省信阳市','037163982910','13837178301','1008','1010'),(10,'10009','肖雷','男',25,'410108198309110519','1981-09-15','4','2001-12-11','河南省鹤壁市','037163982910','13837178301','1008','1010');
/*!40000 ALTER TABLE `employeeinfotable` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `goodsinfotable`
--

DROP TABLE IF EXISTS `goodsinfotable`;
CREATE TABLE `goodsinfotable` (
  `ID` int(8) NOT NULL auto_increment COMMENT '商品信息表自动ID',
  `GoodsId` varchar(20) NOT NULL COMMENT '商品Id',
  `GoodsName` varchar(20) NOT NULL COMMENT '商品名称',
  `GoodsCateId` varchar(20) NOT NULL COMMENT '商品分类Id',
  `GoodsSupplyId` varchar(20) NOT NULL COMMENT '商品供应商Id',
  `GoodsABC` varchar(10) NOT NULL COMMENT '商品ABC分类',
  `GoodsProvince` varchar(20) NOT NULL COMMENT '商品产地省',
  `GoodsCity` varchar(20) NOT NULL COMMENT '商品产地市',
  `GoodsStockId` varchar(20) NOT NULL COMMENT '商品所在仓库Id',
  `GoodsPrice` float NOT NULL COMMENT '商品单价',
  `GoodsStockNumber` int(8) NOT NULL COMMENT '商品库存量',
  `GoodsOrderDate` varchar(20) NOT NULL COMMENT '商品定购日期',
  `GoodsStockInDate` varchar(20) NOT NULL COMMENT '商品入库日期',
  `GoodsUnit` varchar(4) NOT NULL COMMENT '商品单位',
  `StockRegionId` varchar(20) NOT NULL COMMENT '商品在库中位置编号',
  `TypeId` varchar(20) NOT NULL COMMENT '商品型号Id',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `GoodsId` (`GoodsId`),
  KEY `FK_goodinfo_supply` (`GoodsSupplyId`),
  KEY `FK_goodinfo_warehouse` (`GoodsStockId`),
  KEY `FK_goodinfo_commoditycate` (`GoodsCateId`),
  CONSTRAINT `goodsinfotable_ibfk_1` FOREIGN KEY (`GoodsSupplyId`) REFERENCES `supplytable` (`SupplyId`),
  CONSTRAINT `goodsinfotable_ibfk_2` FOREIGN KEY (`GoodsStockId`) REFERENCES `warehouseinfotable` (`WareHouseId`),
  CONSTRAINT `goodsinfotable_ibfk_3` FOREIGN KEY (`GoodsCateId`) REFERENCES `commcatetable` (`CommodityId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `goodsinfotable`
--

LOCK TABLES `goodsinfotable` WRITE;
/*!40000 ALTER TABLE `goodsinfotable` DISABLE KEYS */;
INSERT INTO `goodsinfotable` VALUES (1,'10001','长虹34寸液晶彩电','10001','10001','A','四川省','成都市','10001',10000,30,'2005-04-20','2007-04-18','台','10001','10002'),(2,'10002','长虹普通25寸彩电','10001','10001','A','四川省','成都市','10001',2500,30,'2004-04-07','2004-06-16','台','10002','10001'),(3,'10003','长虹29寸平板彩电','10001','10001','B','四川省','成都市','10001',3000,20,'2004-06-17','2004-09-10','台','10003','10001');
/*!40000 ALTER TABLE `goodsinfotable` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `goodslocationtable`
--

DROP TABLE IF EXISTS `goodslocationtable`;
CREATE TABLE `goodslocationtable` (
  `ID` int(4) NOT NULL auto_increment COMMENT '商品在仓库中位置表自动ID',
  `LocationId` varchar(20) NOT NULL COMMENT '商品位置Id',
  `LocationName` varchar(20) NOT NULL COMMENT '商品位置名称',
  PRIMARY KEY  (`ID`,`LocationId`),
  UNIQUE KEY `LocationId` (`LocationId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `goodslocationtable`
--

LOCK TABLES `goodslocationtable` WRITE;
/*!40000 ALTER TABLE `goodslocationtable` DISABLE KEYS */;
INSERT INTO `goodslocationtable` VALUES (1,'1001','东一区'),(2,'1002','东二区'),(3,'1003','东三区'),(4,'1004','东四区'),(5,'1005','东五区'),(6,'1006','西一区'),(7,'1007','西二区'),(8,'1008','西三区'),(9,'1009','西四区'),(10,'1010','西五区'),(11,'1011','南一区'),(12,'1012','南二区'),(13,'1013','南三区'),(14,'1014','南四区'),(15,'1015','南五区'),(16,'1016','北一区'),(17,'1017','北二区'),(18,'1018','北三区'),(19,'1019','北四区'),(20,'1020','北五区');
/*!40000 ALTER TABLE `goodslocationtable` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `goodsrowstable`
--

DROP TABLE IF EXISTS `goodsrowstable`;
CREATE TABLE `goodsrowstable` (
  `ID` int(4) NOT NULL auto_increment COMMENT '商品在仓库排号表自动ID',
  `RowsId` varchar(20) NOT NULL COMMENT '排号Id',
  `RowsName` varchar(20) NOT NULL COMMENT '排号名称',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `RowsId` (`RowsId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `goodsrowstable`
--

LOCK TABLES `goodsrowstable` WRITE;
/*!40000 ALTER TABLE `goodsrowstable` DISABLE KEYS */;
INSERT INTO `goodsrowstable` VALUES (1,'1001','一排'),(2,'1002','二排'),(3,'1003','三排'),(4,'1004','四排'),(5,'1005','五排'),(6,'1006','六排'),(7,'1007','七排'),(8,'1008','八排'),(9,'1009','九排'),(10,'1010','十排'),(11,'1011','十一排'),(12,'1012','十二排'),(13,'1013','十三排'),(14,'1014','十四排'),(15,'1015','十五排'),(16,'1016','十六排'),(17,'1017','十七排'),(18,'1018','十八排'),(19,'1019','十九排'),(20,'1020','二十排');
/*!40000 ALTER TABLE `goodsrowstable` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `goodsseatstable`
--

DROP TABLE IF EXISTS `goodsseatstable`;
CREATE TABLE `goodsseatstable` (
  `ID` int(4) NOT NULL auto_increment COMMENT '商品在仓库座号表自动ID',
  `SeatsId` varchar(20) NOT NULL COMMENT '商品位Id',