宠物商店项目

一、数据库结构

 二、SQL语句

CREATE DATABASE pet;
use pet;


-- 宠物表
CREATE TABLE pet(
id INT(4) AUTO_INCREMENT COMMENT '宠物id',
name CHAR(50) COMMENT '宠物名',
typeName CHAR(20) COMMENT '宠物类型',
health INT(4) COMMENT '是否健康',
love INT(4) COMMENT '爱心指数',
birthday TIMESTAMP COMMENT '出生日期',
owner_id INT(4) COMMENT '宠物主人id',
store_id INT(4) COMMENT '宠物所属商店id',
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8


-- 宠物主人表
CREATE TABLE petowner(
id INT(4) AUTO_INCREMENT COMMENT '宠物主人id',
name CHAR(10) not null COMMENT '宠物主人名',
`password` CHAR(10)NOT NULL  COMMENT '宠物主人密码',
money INT COMMENT '宠物主人元宝',
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 宠物商店表
CREATE TABLE petshop(
id INT AUTO_INCREMENT COMMENT'宠物商店id',
`name` CHAR(10) COMMENT '宠物商店名字',
`password` CHAR(10) COMMENT '宠物商店密码',
balance INT(4) COMMENT '宠物商店结余',
PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8


-- 账目表
CREATE TABLE account(
id INT(4) AUTO_INCREMENT COMMENT '账单id',
deal_type INT(4) COMMENT '交易类型',
pet_id INT(4) COMMENT '宠物id',
seller_id INT(4) COMMENT '买家id',
buyer_id INT(4) COMMENT '卖家id',
price INT(4) COMMENT '交易时间',
deal_time TIMESTAMP COMMENT '交易时间',
PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8



-- 建立主外键关系
 ALTER TABLE pet ADD CONSTRAINT fk_owner_pet FOREIGN KEY (owner_id) REFERENCES petowner(id);
 ALTER TABLE account ADD CONSTRAINT fk_owner_account FOREIGN KEY (buyer_id) REFERENCES petowner(id);
 ALTER TABLE account ADD CONSTRAINT fk_pet_account FOREIGN KEY (pet_id) REFERENCES pet(id);
 ALTER TABLE pet ADD CONSTRAINT fk_shop_pet FOREIGN KEY (store_id) REFERENCES petshop(id);

-- 插入数据

INSERT INTO `petowner` (`name`, `password`, `money`) VALUES ('小明', '123456', 178);
INSERT INTO `petowner` (`name`, `password`, `money`) VALUES ('小强 ', '654321', 498);  
  
  
INSERT INTO `petshop` (`name`, `passWord`,`balance` )VALUES ('北京信息中心', '123456', 624);  
INSERT INTO `petshop` (`name`, `passWord`,`balance` ) VALUES ('重庆观音桥     ', '123456', 800);  
  
INSERT `pet`  (`name`,`typeName`, `health`, `love`, `birthday`, `owner_id`, `store_id`) VALUES ('花花','dog', 1, 50, '2015-08-20', 1, 1);  
INSERT `pet`  (`name`,`typeName`, `health`, `love`, `birthday`, `owner_id`, `store_id`) VALUES ('贝贝','penguin', 1, 60, '2015-08-20', NULL, 2);  
INSERT `pet`  (`name`,`typeName`, `health`, `love`, `birthday`, `owner_id`, `store_id`) VALUES ('成成','dog', 1, 60,  '2015-09-10', NULL, 1);  
INSERT `pet`  (`name`,`typeName`, `health`, `love`, `birthday`, `owner_id`, `store_id`) VALUES ('露露','bird', 1, 70,  '2016-01-10', NULL, 1);  
INSERT `pet`  (`name`,`typeName`, `health`, `love`, `birthday`, `owner_id`, `store_id`) VALUES ('老虎','tiger', 1, 2,  '2016-02-10', 2, 1);  
INSERT `pet`  (`name`,`typeName`, `health`, `love`, `birthday`, `owner_id`, `store_id`) VALUES ('老虎','tiger', 1, 2,  '2016-3-15', NULL, 1);  
INSERT `pet`  (`name`,`typeName`, `health`, `love`, `birthday`, `owner_id`, `store_id`) VALUES ('老虎','tiger', 1, 11,   '2016-2-15', NULL, 1);  
INSERT `pet`  (`name`,`typeName`, `health`, `love`, `birthday`, `owner_id`, `store_id`) VALUES ('狮子','lion', 1, 2,   '2016-4-15', NULL, 2);  
  
  
INSERT `account` (`deal_type`,`pet_id`, `seller_id`, `buyer_id`, `price`,`deal_time`) VALUES (1, 4, 1, 1, 5, '2016-08-20');  
INSERT `account` (`deal_type`,`pet_id`, `seller_id`, `buyer_id`, `price`,`deal_time`) VALUES (1, 3, 1, 1, 5,'2016-08-20');  
INSERT `account` (`deal_type`,`pet_id`, `seller_id`, `buyer_id`, `price`,`deal_time`) VALUES (1, 3, 1, 1, 5, '2016-09-10');  
INSERT `account` (`deal_type`,`pet_id`, `seller_id`, `buyer_id`, `price`,`deal_time`) VALUES (2, 2, 2, 1, 3,  '2016-09-10');  
INSERT `account` (`deal_type`,`pet_id`, `seller_id`, `buyer_id`, `price`,`deal_time`) VALUES (2, 3, 1, 1, 3, '2016-10-15'); 

三、代码设计