首页 > 分享 > 基于MySQL的宠物商店案例资源

基于MySQL的宠物商店案例资源

萌宠菠菠乐园
2024-08-18 12:02

/* SQLyog Ultimate v11.24 (32 bit) MySQL - 5.5.19 : Database - petstore ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_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 `account` */ CREATE TABLE `account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deal_type` int(11) NOT NULL COMMENT '1：商店卖给主人 2：主人卖给商店', `pet_id` int(11) NOT NULL, `store_id` int(11) NOT NULL, `owner_id` int(11) NOT NULL, `price` int(11) NOT NULL, `deal_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `FK_account_pet_id` (`pet_id`), CONSTRAINT `FK_account_pet_id` FOREIGN KEY (`pet_id`) REFERENCES `pet` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; /*Data for the table `account` */ LOCK TABLES `account` WRITE; insert into `account`(`id`,`deal_type`,`pet_id`,`store_id`,`owner_id`,`price`,`deal_time`) values (1,1,3,1,1,5,'2018-07-13 12:15:48'),(2,1,2,1,1,5,'2018-07-13 12:17:45'); UNLOCK TABLES; /*Table structure for table `owner` */ CREATE TABLE `owner` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL, `password` varchar(10) NOT NULL, `money` int(11) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*Data for the table `owner` */ LOCK TABLES `owner` WRITE; insert into `owner`(`id`,`name`,`password`,`money`) values (1,'张三丰','123456',190),(2,'李四','123456',20),(5,'赵六','888888',500); UNLOCK TABLES; /*Table structure for table `pet` */ CREATE TABLE `pet` ( `id` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(50) NOT NULL, `type_name` varchar(20) NOT NULL, `health` int(11) NOT NULL, `love` int(11) NOT NULL, `birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `owner_id` int(11) DEFAULT NULL, `store_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_pet_owner_id` (`owner_id`), KEY `FK_pet_store_id` (`store_id`), CONSTRAINT `FK_pet_owner_id` FOREIGN KEY (`owner_id`) REFERENCES `owner` (`id`), CONSTRAINT `FK_pet_store_id` FOREIGN KEY (`store_id`) REFERENCES `store` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; /*Data for the table `pet` */ LOCK TABLES `pet` WRITE; insert into `pet`(`id`,`NAME`,`type_name`,`health`,`love`,`birthday`,`owner_id`,`store_id`) values (1,'花花','pig',100,0,'2018-08-17 14:58:13',1,NULL),(2,'贝贝','dog',100,0,'2018-07-13 12:17:45',1,NULL),(3,'露露','dog',100,0,'2018-07-13 12:15:48',1,NULL),(4,'老虎','cat',100,0,'2018-02-28 09:45:04',NULL,2); UNLOCK TABLES; /*Table structure for table `store` */ CREATE TABLE `store` ( `id` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(10) NOT NULL, `PASSWORD` varchar(10) NOT NULL, `balance` int(11) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; /*Data for the table `store` */ LOCK TABLES `store` WRITE; insert into `store`(`id`,`NAME`,`PASSWORD`,`balance`) values (1,'北京信息中心','123456',410),(2,'重庆观音桥','123456',280); UNLOCK TABLES; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;