文件名称：数据库表操作代码
文件大小：4KB
文件格式：PHP
更新时间：2021-12-02 05:17:13
php
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;