use github;
drop table user;
-- auto-generated definition
create table user
(
 userid varchar(10) primary key,
 password varchar(20),
 identity int,
# Integer
 online boolean
);


-- auto-generated definition
create table appointment
(
 book_id bigint not null comment '图书ID',
 student_id bigint not null comment '学号',
 appoint_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '预约时间',
 primary key (book_id, student_id)
)
 comment '预约图书表';

create index idx_appoint_time
 on appointment (appoint_time);


-- auto-generated definition
create table book
(
 book_id bigint auto_increment comment '图书ID'
 primary key,
 name varchar(100) not null comment '图书名称',
 number int not null comment '馆藏数量'
)
 comment '图书表';



-- auto-generated definition
create table identity
(
 number int auto_increment comment '自增序号,供user表调用。'
 primary key,
 title varchar(10) not null comment '身份称号',
 constraint identity_title_uindex
 unique (title)
)
 comment '身份表,供user表使用';




-- auto-generated definition
# [2019-12-31 19:10:39] [HY000][1824] Failed to open the referenced table 'petrelease'
# [2019-12-31 19:38:59] [HY000][1681] Integer display width is deprecated and will be removed in a future release.
create table petdetails
(
 petid int not null comment '主键,子表。串联标识,这几个表串联的。一起生成,一起删除?修改可以个别。'
 primary key,
 petname varchar(20) not null comment '非空即可,自己随便取一个名字。',
 petphotopath varchar(100) null comment '可以为空,为空就是没有?无所谓?有了也可以,反正是前端处理。路径而已。',
 gender tinyint(1) not null comment '性别,true男?还能怎么存?0/1?非空',
 age int not null comment '年龄,非空,数字,怎么保证>=0且<=?',
 health varchar(100) not null comment '健康状况,可以直接描述。或者怎么用一个数字作为标识符?代表多种状态?0、1、2、3、4、5、6之类的?int(1)可以吗?但还是要写字才行。',
 species varchar(20) not null,
 constraint petdetails_petrelease_petid_fk
 foreign key (petid) references petrelease (petid)
 on update cascade on delete cascade,
 constraint petdetails_species_name_fk
 foreign key (species) references species (name)
)
 comment '宠物详情,仍然以petid为主键存在这里代表字表,三个表是关联的。但是修改可以只是用一小部分表?有优化效果??吗';




-- auto-generated definition
# [2019-12-31 19:10:39] [HY000][1824] Failed to open the referenced table 'store'
create table petrelease
(
 petid int auto_increment comment '主键,唯一标识,自增,其他详细信息,见子表,可以看名字(可重复)。'
 primary key,
 storeid varchar(20) not null comment '外键,容器,商店id,存放很多,petid。可以重复,比如发布有多个宠物。',
 releasetime timestamp default CURRENT_TIMESTAMP not null comment '自动生成的时间,当前时间。一次即可。不需要update那是怎么实现的?一句话?',
 constraint petrelease_store_storeid_fk
 foreign key (storeid) references store (storeid)
 on update cascade on delete cascade
)
 comment '宠物发布表,有两个子表,都是根据petid来的,但是这个petid限制于storeid,有店才可以发布宠物。记录发布时间。其他信息见子表。注意外键格式。';



-- auto-generated definition
# [2019-12-31 19:10:39] [HY000][1824] Failed to open the referenced table 'petrelease'
create table pettrading
(
 petid int not null comment '外键,主键,以宠物id为标识。相关表。子表。'
 primary key,
 flag tinyint(1) default 0 not null comment '非空,非fasle(没卖出去)即true(卖出去了),表示卖出去没有。',
 money int default 0 not null comment '金钱,非空,最好大于或者等于零【表示寄养】,如何添加?',
 constraint pettrading_petrelease_petid_fk
 foreign key (petid) references petrelease (petid)
 on update cascade on delete cascade
)
 comment '宠物交易信息表,根据宠物petid记录其交易金额,和交易状态。【其中,金额0表示可以被领养,其他大于0表示出售金额。】';



-- auto-generated definition
# [2019-12-31 19:10:39] [HY000][1824] Failed to open the referenced table 'petrelease'
create table pettradinglist
(
 id int auto_increment comment '自动生成的自增id'
 primary key,
 buyerid varchar(10) not null comment '购买者的id,外键。',
 petid int not null comment '外键,购买的宠物。',
 purchasetime timestamp default CURRENT_TIMESTAMP not null comment '自动生成的时间。',
 constraint pettradinglist_petrelease_petid_fk
 foreign key (petid) references petrelease (petid),
 constraint pettradinglist_user_userid_fk
 foreign key (buyerid) references user (userid)
 on update cascade on delete cascade
)
 comment '宠物交易信息表。可以记录宠物购买或者领养信息。';



-- auto-generated definition
create table species
(
 id int auto_increment comment '自动标记的id,没有实际意义。自增。'
 primary key,
 name varchar(20) not null comment '名字,可以用来唯一标识这个内容,必须唯一。长度放宽,限为20',
 constraint species_name_uindex
 unique (name)
)
 comment '宠物种类表,这是建立必要的可以变动(主要是增加)内容的表格,不断增加的从来没有是别过的种类。第一次增加过后,以后都可以显示在下拉框中。主键为自增id作为标识,但是种类名称一定不可以重复,使用unique即可。';



-- auto-generated definition
# [2019-12-31 19:10:39] [HY000][3734] Failed to add the foreign key constraint. Missing column 'userid' for constraint 'store_user_userid_fk' in the referenced table 'user'
create table store
(
 userid varchar(10) not null comment '用户ID,即用户表中id,以外键关联。'
 primary key,
 storeid varchar(20) not null comment '商店id,即名字,与userid唯一关联。作为发布宠物的渠道。',
 description varchar(50) default '该用户还没有描述' null,
 constraint store_storeid_uindex
 unique (storeid),
 constraint store_user_userid_fk
 foreign key (userid) references user (userid)
 on update cascade on delete cascade
)
 comment '商店信息,用户id、商店id[作为宠物发布的依据]、商店信息描述。[与商店注册表有关系]';



-- auto-generated definition
# [2019-12-31 19:10:39] [HY000][1824] Failed to open the referenced table 'store'
# [2019-12-31 19:10:39] Summary: 11 of 11 statements executed, 6 failed in 428 ms (4692 symbols in file)
create table storeregistration
(
 storeid varchar(20) not null comment '商店id,即用户自定义的商店名称。这里通过二重关系关联到唯一主表user的主键userid'
 primary key,
 registertime timestamp default CURRENT_TIMESTAMP not null comment '自动生成的注册时间。以timestamp保存。',
 constraint storeregistration_store_storeid_fk
 foreign key (storeid) references store (storeid)
 on update cascade on delete cascade
)
 comment '商店注册表,即当商店表生成的同时,这张表记录当时注册的时间和一些必要的信息。主键为store表storeid即可。这样就可以额外查到商店注册信息了。';


-- auto-generated definition
# create table user
# (
# name varchar(10) null,
# password varchar(20) null
# );

# 恢复User

INSERT INTO github.user (userid, password, identity, online) VALUES ('xxx', 'xxx', 2, 0);