用MySQL写药店进货管理系统要代码

以下是一个简单的药店进货管理系统的MySQL数据库设计和代码示例：

数据库设计：

1. 药品表

CREATE TABLE `medicine` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`price` decimal(10,2) NOT NULL,
`stock` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2. 供应商表

CREATE TABLE `supplier` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`contact` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

3. 进货记录表

CREATE TABLE `purchase_record` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`medicine_id` int(11) NOT NULL,
`supplier_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`price` decimal(10,2) NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`medicine_id`) REFERENCES `medicine` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`supplier_id`) REFERENCES `supplier` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

代码示例：

1. 添加药品

INSERT INTO `medicine` (`name`, `price`, `stock`) VALUES ('阿莫西林', 2.50, 100);

2. 添加供应商

INSERT INTO `supplier` (`name`, `contact`) VALUES ('江苏金贝尔药业有限公司', '18012345678');

3. 添加进货记录

INSERT INTO `purchase_record` (`medicine_id`, `supplier_id`, `quantity`, `price`, `date`) VALUES (1, 1, 100, 2.00, '2021-06-01 10:00:00');

4. 查询药品列表

SELECT `id`, `name`, `price`, `stock` FROM `medicine`;

5. 查询供应商列表

SELECT `id`, `name`, `contact` FROM `supplier`;

6. 查询进货记录列表

SELECT `purchase_record`.`id`, `medicine`.`name`, `supplier`.`name`, `quantity`, `price`, `date` FROM `purchase_record`
INNER JOIN `medicine` ON `purchase_record`.`medicine_id` = `medicine`.`id`
INNER JOIN `supplier` ON `purchase_record`.`supplier_id` = `supplier`.`id`;