1_创建admins表以及插入该表测试数据的脚本
DROP TABLE IF EXISTS `admins`;
CREATE TABLE `admins` (
`管理员帐号` char(30) NOT NULL DEFAULT '',
`用户名` char(30) NOT NULL DEFAULT '',
`密码` char(8) DEFAULT NULL,
PRIMARY KEY (`管理员帐号`,`用户名`),
UNIQUE KEY `管理员帐号` (`管理员帐号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `admins` VALUES ('admin1', '赵毅', '12345678');
INSERT INTO `admins` VALUES ('admin2', '陈丹', '123');
2_创建inventory表以及插入该表测试数据的脚本
DROP TABLE IF EXISTS `inventory`;
CREATE TABLE `inventory` (
`商品编号` int(10) unsigned NOT NULL DEFAULT '0',
`商品名称` char(30) NOT NULL DEFAULT '',
`当前库存量` int(10) unsigned NOT NULL,
`最大库存量` int(10) unsigned NOT NULL,
`最小库存量` int(10) unsigned NOT NULL,
PRIMARY KEY (`商品编号`,`商品名称`),
UNIQUE KEY `商品编号` (`商品编号`),
UNIQUE KEY `商品名称` (`商品名称`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `inventory` VALUES ('15001', '计算机', '150', '500', '100');
INSERT INTO `inventory` VALUES ('15002', '打印机', '9', '30', '5');
INSERT INTO `inventory` VALUES ('15003', '扫描仪', '11', '20', '3');
INSERT INTO `inventory` VALUES ('15004', '路由器', '9', '20', '0');
INSERT INTO `inventory` VALUES ('15005', '交换机', '15', '30', '5');
3_创建checkin表以及插入该表测试数据的脚本
DROP TABLE IF EXISTS `checkin`;
CREATE TABLE `checkin` (
`序号` int(10) unsigned NOT NULL AUTO_INCREMENT,
`耗材编号` int(10) unsigned DEFAULT NULL,
`入库耗材` char(30) DEFAULT NULL,
`数量` int(10) unsigned NOT NULL,
`价格` float NOT NULL,
`入库日期` date DEFAULT NULL,
`入库时间` time DEFAULT NULL,
PRIMARY KEY (`序号`),
KEY `耗材编号` (`耗材编号`,`入库耗材`),
CONSTRAINT `checkin_ibfk_1` FOREIGN KEY (`耗材编号`, `入库耗材`) REFERENCES `inventory` (`商品编号`, `商品名称`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
INSERT INTO `checkin` VALUES ('1', '15002', '打印机', '6', '2000', '2015-08-14', '15:59:12');
INSERT INTO `checkin` VALUES ('2', '15004', '路由器', '2', '3000', '2015-08-16', '11:33:55');
INSERT INTO `checkin` VALUES ('3', '15001', '计算机', '30', '2500', '2015-11-13', '15:30:50');
INSERT INTO `checkin` VALUES ('4', '15004', '路由器', '4', '2500', '2015-11-13', '08:33:12');
4_创建checkout表以及插入该表测试数据的脚本
DROP TABLE IF EXISTS `checkout`;
CREATE TABLE `checkout` (
`序号` int(10) unsigned NOT NULL AUTO_INCREMENT,
`耗材编号` int(10) unsigned DEFAULT NULL,
`出库耗材` char(30) DEFAULT NULL,
`数量` int(10) unsigned NOT NULL,
`库存管理员` char(30) DEFAULT NULL,
`出库负责人` char(30) DEFAULT NULL,
`出库日期` date DEFAULT NULL,
`出库时间` time DEFAULT NULL,
PRIMARY KEY (`序号`),
KEY `耗材编号` (`耗材编号`,`出库耗材`),
KEY `库存管理员` (`库存管理员`,`出库负责人`),
CONSTRAINT `checkout_ibfk_1` FOREIGN KEY (`耗材编号`, `出库耗材`) REFERENCES `inventory` (`商品编号`, `商品名称`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `checkout_ibfk_2` FOREIGN KEY (`库存管理员`, `出库负责人`) REFERENCES `admins` (`管理员帐号`, `用户名`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
