数据库基础入门（二）——具体项目实现

曾经终败给现在 2022-04-12 10:42 141阅读 0赞

##### **宠物社交网站数据库设计** #####

###  项目简介：用户通过给自己的宠物申请账号，从而在网路上以宠物为第一人称与其他宠物进行社交（类似微博）。 ###

### 数据库基本架构： ###

用户（ID，用户名，密码，邮箱，电话，地址，身份证号，性别，姓名，生日）

宠物（ID，名字，性别，生日，智商，简介，用户ID，品种ID）

科目（ID，科目名）

品种（ID，品种名，科目ID）

动态（ID，用户ID，内容，时间，赞数）

评论（ID，用户ID，内容，时间，动态ID）

回复（ID，用户ID，内容，时间，评论ID）

管理员（ID，用户名，密码）

##### 用户表   ![cf693d25-37d9-39e3-bb68-1793bfcee4e9.png][] #####

##### create table user(    \-> id int not null auto\_increment,    \-> usr\_name varchar(20) not null,    \-> password varchar(20) not null,    \-> email varchar(20) not null,    \-> tel varchar(11),    \-> address varchar(50),    \-> sex boolean,    \-> true\_name varchar(20),    \-> birthday Date,    \-> primary key(id));   ![6d8bf8ab-e8d6-3ba8-8be7-0b39dbf2b9ed.png][] #####

##### 宠物表 #####

#####    ![0b786e70-a93e-3eee-9bfb-e1f36e0f8a4c.png][] #####

##### create table pet( #####

#####  \-> id int not null auto\_increment, #####

#####  \-> name varchar(20) not null, #####

#####  \-> sex boolean, #####

#####  \-> birthday Date, #####

#####  \-> IQ int, #####

#####  \-> intro varchar(200), #####

#####  \-> user\_id int not null, #####

#####  \-> kind\_id int not null, #####

##### \-> primary key(id)); #####

#####  //设置外键 #####

##### alter table pet add constraint fk\_pet\_user foreign key(user\_id) references user(id) on delete cascade on update cascade;  #####

##### alter table pet add constraint fk\_pet\_kind foreign key(kind \_id) references kind (id) on delete cascade on update cascade; #####

![df6af1df-9951-3db3-a6a7-27d012bf61cf.png][]

##### 科目表 #####

![9b863cc2-264d-3c74-ade8-63096db27dd3.png][]

##### mysql> create table family( #####

#####  #####

#####  \-> id int not null primary key auto\_increment, #####

#####  #####

#####  \-> family varchar(20)); #####

![04f63e50-2795-3962-90d1-1bd68c66ff5d.png][]

##### 品种表 #####

![54de1226-be51-3f1e-a0c6-960ca629fc00.png][]

##### mysql> create table kind( #####

#####  #####

#####  \-> id int not null primary key auto\_increment, #####

#####  #####

#####  \-> kind varchar(20) not null, #####

#####  #####

##### \-> family\_id int not null); #####

##### //设置外键 #####

#####  #####

##### alter table kind add constraint fk\_breed\_family foreign key(family\_id) references family(id) on delete cascade on update cascade; #####

![ad18d0a5-341f-378c-995f-a0fb433d02fc.png][]

##### 动态表 #####

![d30ced86-e45d-3fec-91cf-72a3cede8b31.png][]

##### mysql> create table dynamic( #####

#####  \-> id int not null primary key auto\_increment, #####

#####  \-> user\_id int not null, #####

#####  \-> content varchar(280) not null, #####

#####  \-> time time not null, #####

##### \-> praise int); #####

##### //设置外键 #####

##### alter table dongtai add constraint fk\_dynamic\_user foreign key(user\_id)  #####

#####  #####

##### references user(id) on delete cascade on update cascade; #####

![eb608746-eacd-320f-b8c6-7771e11ba56c.png][]

##### 评论表 #####

![624dbc97-eb75-3cc4-94c4-7e8d3f7c3a9d.png][]

##### mysql> create table comment( #####

#####  \-> id int not null primary key auto\_increment, #####

#####  \-> user\_id int not null, #####

#####  \-> content varchar(280) not null, #####

#####  \-> time time not null, #####

##### \-> from\_id int not null); #####

##### //设置外键 #####

##### alter table comment add constraint fk\_comment\_user foreign key(user\_id) references user(id) on delete cascade on update cascade; #####

##### alter table comment add constraint fk\_comment\_dongtai foreign key(from\_id) references dongtai(id) on delete cascade on update cascade; #####

![9985c510-8d91-3863-8fff-d5ad3aafdf20.png][]

##### 管理员表 #####

![6cbe6d78-45d0-38c8-887a-58f5efa5eb5b.png][]

##### mysql> create table admin( #####

#####  \-> id int not null primary key auto\_increment, #####

#####  \-> name varchar(20) not null, #####

#####  \-> password varchar(20) not null); #####

![106944f1-f472-332c-9501-de776721fb62.png][]

##### 回复表 #####

#####    ![5bc96b4f-4fc8-38ee-a3fa-25826766ccfc.png][]    #####

##### create table reply\_comment( #####

#####  id int not null auto\_increment, #####

#####  user\_id int not null, #####

#####  time Time not null, #####

#####  owner\_id int not null, #####

#####  primary key(id)); #####

##### //设置外键 #####

#####  alter table reply\_comment add constraint fk\_reply\_user foreign key(user\_id) references user(id) on delete cascade on update cascade; #####

#####  alter table reply\_comment add constraint fk\_reply\_comment foreign key(owner\_id) references comment(id) on delete cascade on update cascade; #####

![11b9bdc6-aaf5-3ff4-9ec1-6d01fcddcc41.png][]

[cf693d25-37d9-39e3-bb68-1793bfcee4e9.png]: /images/20220412/213ab62156dc4372851cd5613f9c83ec.png
[6d8bf8ab-e8d6-3ba8-8be7-0b39dbf2b9ed.png]: /images/20220412/bd94b189a3fd42719ca860804d1c7af1.png
[0b786e70-a93e-3eee-9bfb-e1f36e0f8a4c.png]: /images/20220412/c394c49381a84dd18d10e8842acd710b.png
[df6af1df-9951-3db3-a6a7-27d012bf61cf.png]: /images/20220412/077270fcaf9c40ad8f654712466176e1.png
[9b863cc2-264d-3c74-ade8-63096db27dd3.png]: /images/20220412/3a923097480a40b49bea7ff1572bbd2a.png
[04f63e50-2795-3962-90d1-1bd68c66ff5d.png]: /images/20220412/b36add2488474aea89deb936899a9a74.png
[54de1226-be51-3f1e-a0c6-960ca629fc00.png]: /images/20220412/8ac6cbb5744f46a9bbfff7aa7675af67.png
[ad18d0a5-341f-378c-995f-a0fb433d02fc.png]: /images/20220412/c506093733894004843149c685a77efd.png
[d30ced86-e45d-3fec-91cf-72a3cede8b31.png]: /images/20220412/cd95bb4ae6b64211a8c1c7fcfa373a3d.png
[eb608746-eacd-320f-b8c6-7771e11ba56c.png]: /images/20220412/5a7226a7c576473a869020a2da8af074.png
[624dbc97-eb75-3cc4-94c4-7e8d3f7c3a9d.png]: /images/20220412/1dcb08f58e5b4187a6a0cc4a2d79e9a5.png
[9985c510-8d91-3863-8fff-d5ad3aafdf20.png]: /images/20220412/b4ee50adcf08469181484b4bc30573ba.png
[6cbe6d78-45d0-38c8-887a-58f5efa5eb5b.png]: /images/20220412/b075e7b1a748440ca2265e0f4a3e4c43.png
[106944f1-f472-332c-9501-de776721fb62.png]: /images/20220412/cba892b0c94641dc9b1e12e2a5977700.png
[5bc96b4f-4fc8-38ee-a3fa-25826766ccfc.png]: /images/20220412/2bfc1a3f2e45465d9f0e1c5d2c32d455.png
[11b9bdc6-aaf5-3ff4-9ec1-6d01fcddcc41.png]: /images/20220412/ac5dfe9c48ba4e5db2c09cd7ebad6426.png