第十章 宠物商店 数据库建立插入信息

第十章 宠物商店 数据库建立插入信息"/>

第十章 宠物商店 数据库建立插入信息

# 创建宠物数据库
CREATE DATABASE db_pet;# 宠物主人表
CREATE TABLE MasterPet(
`MasterID` INT COMMENT '宠物主人' PRIMARY KEY,
`LoginName` CHAR(10) COMMENT '主人名',
`LoginPwd` CHAR(10) COMMENT '登录密码',
`Price` INT COMMENT '余额'
)
# 宠物商店表
CREATE TABLE ShopPet(
`ShopID` int COMMENT '商店编号' PRIMARY KEY,
`LoginName` CHAR(10) COMMENT '商店名',
`LoginPwd` CHAR(10) COMMENT '登录密码',
`Price` int COMMENT '账目余额'
)
# 宠物信息表
CREATE TABLE pet(
`PetID` int COMMENT '宠物编号' PRIMARY KEY,
`MasterID` int COMMENT '主人编号',
FOREIGN KEY(`MasterID`) REFERENCES masterpet(`MasterID`),
`ShopID` int COMMENT '商店编号',
FOREIGN KEY(`ShopID`) REFERENCES shoppet(`ShopID`),
`RegisterName` char(10) COMMENT '宠物名',
`TypeName` char(255) COMMENT '类型名',
`HealthValue` int COMMENT '健康值',
`Intimate` int COMMENT '亲密度',
`Datebirth` TIMESTAMP COMMENT '出生日期'
)
# 交易类型
CREATE TABLE TypeDeal(
`TypeID` INT COMMENT '类型编号' PRIMARY KEY,
`DealType` VARCHAR(20) COMMENT '交易类型' not null
)
# 账目信息表
CREATE TABLE accounts(
`AccountsID` INT COMMENT '账目编号' PRIMARY KEY,
`PetID` INT COMMENT '宠物编号',
FOREIGN KEY(`PetID`) REFERENCES pet(`PetID`),
`SellerID` INT COMMENT '卖家编号',
FOREIGN KEY(`SellerID`) REFERENCES masterpet(`MasterID`),
`DealType` INT COMMENT '交易类型',
FOREIGN KEY(`Dealtype`) REFERENCES TypeDeal(`TypeID`),
`buyerID` INT COMMENT '买家编号',
`DealPrice` INT COMMENT '交易价格',
`DealDate` TIMESTAMP COMMENT '交易时间'
)# 插入宠物主人数据
INSERT INTO masterpet VALUES(1,'小明','123456',956);
INSERT INTO masterpet VALUES(2,'小红','123456',1203);
INSERT INTO masterpet VALUES(3,'小刚','123456',120);
INSERT INTO masterpet VALUES(4,'葫芦爷爷','123456',586);# 插入宠物商店数据
INSERT INTO shoppet VALUES(1,'国际宠物总部','12345',230);
INSERT INTO shoppet VALUES(2,'益州宠物店','12345',120);
INSERT INTO shoppet VALUES(3,'凉州宠物店','12345',152);
INSERT INTO shoppet VALUES(4,'非洲宠物店','12345',632);# 插入宠物表信息
INSERT INTO pet VALUES(1,2,3,'花卉','拉布拉多',100,23,'2010-04-09');
INSERT INTO pet VALUES(2,1,4,'葫芦娃','咖啡猫',90,60,'2021-06-06');
INSERT INTO pet VALUES(3,3,1,'傲天','藏獒',89,80,'2018-08-12');
INSERT INTO pet VALUES(4,4,3,'大虎','泰迪',30,100,'2019-10-01');INSERT INTO pet VALUES(5,2,3,'小黄','拉布拉多',100,23,'2000-05-09');
INSERT INTO pet VALUES(6,1,4,'七娃','咖啡猫',90,60,'2015-04-02');
INSERT INTO pet VALUES(7,3,1,'懒毛','藏獒',89,80,'2016-04-02');
INSERT INTO pet VALUES(8,4,3,'狗熊','泰迪',30,100,'2015-10-01');INSERT INTO pet VALUES(9,2,3,'猪人','拉布拉多',100,23,'2003-04-09');
INSERT INTO pet VALUES(10,1,4,'六娃','咖啡猫',90,60,'2015-06-06');
INSERT INTO pet VALUES(11,3,1,'狗图','藏獒',89,80,'2014-08-12');
INSERT INTO pet VALUES(12,4,3,'荀彧','泰迪',30,100,'2016-11-01');INSERT INTO pet VALUES(13,2,3,'荀攸','拉布拉多',100,23,'2002-04-09');
INSERT INTO pet VALUES(14,1,4,'三娃','咖啡猫',90,60,'2001-06-06');
INSERT INTO pet VALUES(15,3,1,'草丛','藏獒',89,80,'2006-08-12');
INSERT INTO pet VALUES(16,4,3,'蒂法','泰迪',30,100,'2007-10-01');# 插入交易类型信息
INSERT into typedeal VALUES(1,'卖家卖宠物给宠物主人');
INSERT into typedeal VALUES(2,'卖家购买宠物主人的宠物');
# 插入账目信息
INSERT into accounts VALUES(1,14,1,1,4,200,'2001-06-11');