4.2 图书借阅系统数据库设计 --MySQL

柔光的暖阳◎ 2023-09-25 23:13 53阅读 0赞

![CSDN成就一亿技术人][CSDN]

--------------------

#### 本文目录 ####

*  前言
 *  一、背景和需求分析
 *   *  1.1 背景
     *  1.2 信息需求
     *  1.3 功能需求
     *   *  管理员
         *  学生
     *  1.4 数据流图
 *  二、概念结构设计
 *   *  1. 抽象出系统实体
     *  2. 局部E-R图
     *   *  2.1 学生 E-R图
         *  2.2 借阅证 E-R图
         *  2.3 图书 E-R图
         *  2.4 管理员 E-R图
         *  2.5 公告 E-R图
         *  2.6 申请 E-R图
         *  2.7 借阅 E-R图
         *  2.8 评论 E-R图
         *  2.9 收藏 E-R图
     *  3. 全局E-R图
 *  三、逻辑结构设计
 *   *   *  3.1 转换规则
         *  3.2 关系模式
         *   *  3.2.1 按转换规则分析
             *  3.2.2 实际转换结果
         *  3.3 表结构
 *  四、物理结构设计与实施
 *   *   *  4.1 创建数据库
         *  4.2 创建数据表
         *  4.3 初始化数据
 *  五、具体业务分析
 *   *  5.1 录入图书
     *  5.2 学生注册 + 申请借阅证
     *   *  5.2.1 注册
         *  5.2.2 填写学生信息，申请借阅证
     *  5.3 管理员审核借阅证通过
     *  5.4 搜索图书
     *  5.5 查看图书详情
     *  5.6 图书收藏 和 取消收藏
     *   *  5.6.1 收藏
         *  5.6.2 取消收藏
     *  5.7 图书评论 和 审核评论
     *   *  5.7.1 评论
         *  5.7.2 审核评论
     *  5.8 借阅 和 借阅审核
     *   *  5.8.1 借阅
         *  5.8.2 借阅审核
         *  5.8.3 归还
 *  参考
 *  最后

--------------------

## 前言 ##

大家好，我是天罡gg，一个有十多年丰富经验的高级架构师，参与过很多系统的数据库设计，在数据库设计方面有相当丰富的经验。正赶上这篇实战专栏的数据库设计，所以今天让我们来一起做一下《图书借阅系统的数据库设计》，一篇既有理论知识，又能实战落地的数据库设计！  
本文从【需求分析】开始，到【概念结构设计】的**画ER图**, 再到【逻辑结构设计】的**ER图转关系模型,** 最后到【物理结构设计】的**MySQL表设计**, 再加上【具体业务分析】的**SQL实现**，你是不是已经怀疑真有这么齐全吗？你没看错，全文HTML统计**1万3千多字**，Markdown统计**1万6千多字**，设计图片**24张**，让我们拭目以待！

> 数据库设计的基本步骤  
> 按照规范设计的方法，考虑数据库及其应用系统开发全过程，将数据库设计分为以下6个阶段

1.  需求分析：分析用户的需求，包括数据、功能和性能需求
2.  概念结构设计：主要采用E-R模型进行设计，包括画E-R图
3.  逻辑结构设计：通过将E-R图转换成表，实现从E-R模型到关系模型的转换
4.  物理结构设计：主要是为所设计的数据库选择合适的存储结构和存取路径
5.  数据库实施：包括编程、测试和试运行
6.  数据库的运行和维护：在运行过程中不断对其进行调整、修改。

--------------------

## 一、背景和需求分析 ##

> 说明：分析用户的需求，包括数据、功能和性能需求。

### 1.1 背景 ###

图书馆作为一种信息资源的交换场所，图书和用户借阅资料繁多，包含很多数据信息的管理，因此实现图书管理的电子信息化，可以简化繁琐的工作模式，有效解决图书借阅过程中的诸多问题，给图书管理员和借阅者带来极大的便利。

图书借阅系统是为了满足图书馆借阅图书的工作而设计的，它的功能主要分为数据维护和基本功能两大模块。其中数据维护模块包括图书的数据维护和借阅者的数据维护，基本功能模块包括对图书和借阅者的信息浏览、借书、还书、查询、统计等功能。

### 1.2 信息需求 ###

图书借阅系统主要针对两种身份：`管理员`、`学生`。

**学生：** 可以在线查找自己想借的图书，能查看图书的基本信息是最低要求。为了更好的借书体验，可以查看图书的借阅次数、评论数和具体评论 以及 借阅状态。通过查看借阅次数和评论，增加对图书的兴趣，当查看图书的借阅状态是闲置中时，可以直接在线发起借阅；如果图书已经在借阅中，我们可以提供一个收藏功能，方便日后再借阅；那么当对图书有话说时，还可以发起评论，这些都是电子信息化带来的好处。

**管理员：** 主要职责是对图书进行维护，对学生进行管理。为了更好的运营，可以发布一些新闻公告、做一些新书推荐，为了更规范运作，需要拥有对学生的借阅借的审批、对学生借书的审批、对学生还书的审批登记、以及对评论的审批等工作。

![图书借阅系统-业务流程图][-]

### 1.3 功能需求 ###

#### 管理员 ####

1.  图书管理
    
    包括图书录入、修改的功能，这也是必备的功能。
    
    图书**包含**字段：**图书编号**、**图书名称**、**图书类型**、**作者**、**图书简介**、**图书封面**、**出版社**、**出版时间**
    
     *  新书推荐：有对图书进行推荐的功能，当管理员操作后，推荐的图书会展示到学生能看到的推荐区域
     *  借阅审批：学生发起的借阅申请，需要经过管理员审批，审批通过后才可以线下领书。管理员可以根据图书和学生的情况进行审批，包括审批通过和拒绝两个功能项，拒绝需要填写拒绝原因
     *  书评管理：学生在图书详情对图书进行评论、打分后，不会直接展示，需要经过管理员审批以后才可以公共显示，包括审批通过和删除两个功能项
     *  确认归还：当学生将书归还后，由管理员进行确认归还的功能操作
2.  学生管理
    
     *  学生发起的借阅证申请，需要经过管理员审批，审批通过才会拥有进行图书借阅的资格。管理员根据学生情况进行审批，包括：通过和驳回两个功能项
     *  对学生借阅资格的冻结或恢复，这是给管理员更大的权限，可以对不遵守规则的学生进行冻结，学生被冻结后将无法借书，直到管理员给恢复。
3.  公告管理
    
    此功能主要是针对一些公告信息的录入，例如寒假即将到来，图书系统即将关闭，请同学们及时借阅，假期补充知识之类的公告。
    
    包括新闻公告的新增、修改、删除的功能。
    
    公告**包含**字段：**标题**、**内容**、**发布时间**

#### 学生 ####

1.  学生注册
    
    学生注册时需要填写**用户名**、**密码**。
    
    登录后，进行借阅证申请，需要填写字段：**学生编号**、**学生姓名**、**学生昵称**、**学生所属(例如XX学院XX系XX班)**、**学生证照片**
2.  首页
    
    可以查看管理员发布的新闻公告、新书推荐。
3.  我要借阅
    
    借阅证申请通过后，才可以看到我要借阅的入口：
    
     *  查找图书
        
        **图书类型**进行筛选，可以通过**借阅次数、出版时间**分别进行排序
     *  借阅
        
        当图书借阅状态为**闲置**时，才可以点击借阅。提交借阅后，会发给管理员进行审批。可以在个人借阅记录查询审批结果：通过或拒绝。
     *  收藏
        
        对图书感兴趣，可以点击收藏，也可以取消收藏。可以在我的收藏查看。
     *  评论
        
        对图书有话说，可以点击评论、打分，会发给管理员进行审批，审批通过后，会在图书下公共展示该评论。
4.  个人中心
    
     *  个人信息展示
     *  我的借阅
     *  我的收藏
     *  修改密码、密记密码

### 1.4 数据流图 ###

待补充

--------------------

## 二、概念结构设计 ##

> 说明：主要抽象出系统实体，并采用E-R模型进行设计

### 1. 抽象出系统实体 ###

学生-》申请 -》借阅证，可以得出【学生】和【借阅证】两个实体，【申请】是联系。

学生-》借阅 -》图书，可以得出【图书】实体，【借阅】是联系。

学生-》收藏 -》图书，可以得出【收藏】是联系。

学生-》评论 -》图书，可以得出【评论】是联系。

管理员-》发布 -》公告，可以得出【管理员】和【公告】两个实体，【发布】是联系。

管理员-》管理 -》图书，可以得出【管理】是联系。

> 涉及的5个实体有：

*  1.  . 学生
 *  1.  . 借阅证
 *  1.  . 图书
 *  1.  . 管理员
 *  1.  . 公告

> 5个实体之间的联系如下：

*  1名学生，只能`申请`1个借阅证，一个借阅证也只能属于一名学生，所以是一对一的联系；
 *  1名学生，可以`借阅`N本图书，1本图书，也可以被N名学生借阅，所以学生与图书是多对多的借阅联系；
 *  1名学生，可以`收藏`N本图书，1本图书，也可以被N名学生收藏，所以学生与图书是多对多的收藏联系；
 *  1名学生，可以`评论`N本图书，1本图书，也可以被N名学生评论，所以学生与图书是多对多的评论联系;
 *  1名管理员，可以`发布`N条公告，但1条公告，只能由一名管理员发布，所以是一对多联系;
 *  1名管理员，可以`管理`N本图书，1本图书，只能由一名管理员最后管理，所以是一对多联系。

### 2. 局部E-R图 ###

> E-R图：Entity Relationship，实体关系图。
> 
> 常见的有4个部分：
> 
>  *  矩形框：表示实体，在框中记入实体名。
>  *  椭圆形框：表示属性，在框中记入属性名。
>  *  菱形框：表示联系，在框中记入联系名。
>  *  连线：用于实体与属性之间；实体与联系之间；联系与属性之间;
>     
>     并在实体与实体之间标注联系的类型（对于一对一联系，要在两个实体连线方向各写1； 对于一对多联系，要在一的一方写1，多的一方写n；对于多对多关系，则要在两个实体连线方向各写m，n。）

#### 2.1 学生 E-R图 ####

这是一个实体。

根据需求得知学生基本信息包括：**学生编号**、**学生姓名**、**学生昵称**、**学生所属(例如XX学院XX系XX班)**、**学生证照片**

因为需求中冻结的状态，所以我们增加一个属性：

*  是否冻结：代表学生的借阅状态是否冻结

![在这里插入图片描述][d65650d8ce8e4f7bacf547523173a1cf.png_pic_center]

#### 2.2 借阅证 E-R图 ####

这是一个实体。  
![在这里插入图片描述][22b4fc58235848b1b40297d5908cbc97.png_pic_center]

#### 2.3 图书 E-R图 ####

这是一个实体。

根据需求得知图书的基本信息包括：**图书编号**、**图书名称**、**图书类型**、**作者**、**图书简介**、**图书封面**、**出版社**、**出版时间**

还有2个引申出来的额外属性：

*  是否是新书推荐：这是管理员可以维护的新书推荐的标识
 *  图书状态：闲置中或借阅中

![在这里插入图片描述][8ac090e3a73d4bf593788014ebb0d8ec.png_pic_center]

#### 2.4 管理员 E-R图 ####

这是一个实体。

![在这里插入图片描述][277b0bb040284a198a165e5e01d6437a.png_pic_center]

#### 2.5 公告 E-R图 ####

这是一个实体。

根据需求得知公告的基本信息包括：**标题**、**内容**、**发布时间**

有可能被删除，所以还有2个引申出来的额外属性：

*  是否已删除：这是管理员删除公告的标识
 *  删除时间：这是管理员删除公告的时间

![在这里插入图片描述][9351bc6c18644c5c9951683840d49132.png_pic_center]

#### 2.6 申请 E-R图 ####

这是一个联系，【学生】和【借阅证】之间存在着【申请】的联系。

![在这里插入图片描述][e04cea0fe88b495d8c3e98e1f64035a6.png_pic_center]

【`申请`】需要【管理员】审批，所以包括如下属性：

![在这里插入图片描述][542ffed5d799470da3b290705c241924.png_pic_center]

#### 2.7 借阅 E-R图 ####

这是一个联系，【学生】和【图书】之间存在着【借阅】的联系。

![在这里插入图片描述][da6b504150cc403e9d1ec9f018b03b36.png_pic_center]

【`借阅`】需要【管理员】审批和归还，所以包括如下属性：

![在这里插入图片描述][e49b21c35e6244c69c23f83b3ea3be94.png_pic_center]

#### 2.8 评论 E-R图 ####

这是一个联系，【学生】和【图书】之间存在着【评论】的联系。

![在这里插入图片描述][feba7f6602ff4ca6b429924faac024af.png_pic_center]

【`评论`】需要【管理员】审批，所以包括如下属性：

![在这里插入图片描述][947279bc622d481e983e71670c5ba896.png_pic_center]

#### 2.9 收藏 E-R图 ####

这是一个联系，【学生】和【图书】之间存在着【收藏】的联系。

![在这里插入图片描述][4a4aa4300f8546b5a952e9a17d9bddad.png_pic_center]

【`收藏`】不需要【管理员】审批，但可以【取消】，所以包括如下属性：

![在这里插入图片描述][6cc9028c2dc34e1bbc9a22312a6999b1.png_pic_center]

> 公告发布和图书管理都不需要保存记录，所以没有关联的属性。

### 3. 全局E-R图 ###

> 我省略了属性，带颜色的实体和联系需要自行补充属性，根据上面的局部E-R图补充，别偷懒，实际画一下你可能会有更多的领悟！

![图书借阅系统-全局ER图][-_ER]

--------------------

## 三、逻辑结构设计 ##

> 说明：把E-R图转成关系模式。

#### 3.1 转换规则 ####

一个实体型转换为一个关系模式，有一对一、一对多、多对多的联系。不同的联系转关系模式也不同。

*  一对一：两个一对一的实体可以转成两个关系模式，也可以合成成一个关系模式，它们之间的联系可以单独转成一个关系模式，也可以合并到与之关联的任一实体中记录下来。
 *  一对多：每一个实体还是转成一个关系模式，可以单独把它们之间的联系转成一个关系模式，也可把联系记录到多（N）对应的实体中。
 *  多对多：每一个实体还是转成一个关系模式，它们之间的联系必须转成一个关系模式。

#### 3.2 关系模式 ####

##### 3.2.1 按转换规则分析 #####

*  学生-》申请 -》借阅证
    
     *  【`学生`】实体可以转换为一个关系模式；
     *  【`申请`】需要保存记录，所以也可以转换为一个关系模式，`并增加【学生编号】属性`；
     *  【学生】和【借阅证】是一对一的联系，因为需求中对【借阅证】没有其它用途，所以我们将【借阅证】合并到【学生】关系模式中，称为`【是否申请通过】属性`。当然，如果日后有新需求，我们再增加【借阅证】关系模式也不迟。
 *  学生-》借阅 -》图书
    
     *  【`图书`】实体可以转换为一个关系模式；
     *  【`借阅`】需要保存记录，所以可以转换为一个关系模式，`并增加【学生编号】、【图书编号】属性`。
     *  因为【借阅次数】对【图书】是非常常用的查询属性，所以【`图书`】实体`增加【借阅次数】属性`。
 *  学生-》收藏 -》图书
    
     *  【`收藏`】需要保存记录，所以可以转换为一个关系模式，`并增加【学生编号】、【图书编号】属性`。
 *  学生-》评论 -》图书
    
     *  【`评论`】需要保存记录，所以可以转换为一个关系模式，`并增加【学生编号】、【图书编号】属性`。
     *  因为【评论数】对【图书】是非常常用的查询属性，所以【`图书`】实体`增加【评论数】属性`。
 *  管理员-》发布 -》公告
    
     *  【`管理员`】和【`公告`】两个实体可以转换为两个关系模式，因为是一对多的联系，【公告】为多的一方，所以将`【管理员编号】属性保存到【公告】`中。
     *  【发布】不需要保存记录，所以不需要转换。
     *  `注意：`管理员和学生，都属于用户，所以我们将管理员和学生抽象成【`用户`】，并且管理员并没有属性，所以只做为【用户】的一个角色，【学生】`增加【用户编号】属性`。
 *  管理员-》管理 -》图书
    
     *  【管理】不需要保存记录，所以不需要转换。

##### 3.2.2 实际转换结果 #####

> 根据以上规则和分析得到如下**8个**关系模型（红色字体的属性为主码）

*  **用户表：** `用户编号`、用户名、密码、角色(0-学生 1-管理员)
 *  **学生表：** `学生编号`、用户编号、学生姓名、学生昵称、学生所属、学生证图片、是否申请通过(0-未通过 1-通过)、是否冻结(0-正常 1-冻结)
 *  **借阅证申请记录表：** `申请编号`、学生编号、申请时间、管理员操作时间、申请状态(0-待审核 1-通过 2-驳回)、驳回原因、管理员编号
 *  **图书表：** `图书编号`、图书名称、图书类型、作者、图书简介、图书封面、出版社、出版时间、图书状态(0-闲置 1-借阅中)、是否是新书推荐(0-否 1-是) 、借阅次数、评论数
 *  **图书借阅记录表：** `借阅记录编号`、学生编号、图书编号、借阅时间、借阅状态(0-待审核 1-ok 2-拒绝 3-已还)、拒绝原因、审核时间、归还时间、管理员编号
 *  **图书收藏表：** `收藏编号`、学生编号、图书编号、收藏时间、是否已取消、取消时间
 *  **图书评论表：** `评论编号`、学生编号、图书编号、评分(1,2,3,4,5)、评论内容、评论时间、审核状态(0-待审核 1-通过 2-驳回)、管理员编号
 *  **公告表：** `公告编号`、标题、内容、发布时间、是否已删除、删除时间、管理员编号

![图书借阅系统-全局类图][- 1]

> 对8个表之间的关系分析如下：

*  **学生与用户**

1名学生，即是1个用户，所以**学生与用户**之间是**1对1**(1 : 1)关系;

*  **\[学生/管理员\]与\[借阅证申请记录\]**

1名学生，可以发起N次借阅证申请(存在不通过再次申请的情况)，而1次借阅证申请只能由1名学生发起，所以**学生与借阅证申请记录**之间是**1对多**(1 : n)关系;

1名管理员，可以对N次借阅证申请审批，而1次借阅证申请只能由1名管理员审批，所以**管理员与借阅证申请记录**之间是**1对多**(1 : n)关系;

*  **\[学生/管理员\]与\[图书借阅记录\]**

1名学生，可以发起N次借阅，而1次借阅只能由1名学生发起，所以**学生与图书借阅记录**之间是**1对多**(1 : n)关系;

1名管理员，可以对N次借阅审批，而1次借阅只能由1名管理员审批，所以**管理员与图书借阅记录**之间是**1对多**(1 : n)关系;

1本图书，可以被N次借阅，而1次借阅只能针对1本图书，所以**图书与图书借阅记录**之间是**1对多**(1 : n)关系。

> 注意：如果1次可以借N本图书的话，那么**图书与图书借阅记录**之间将是**多对多**(m : n)关系

*  **\[学生/管理员\]与\[图书评论\]**

1名学生，可以发起N条评论，而1条评论只能由1名学生发起，所以**学生与评论**之间是**1对多**(1 : n)关系;

1名管理员，可以对N条评论审批，而1条评论只能由1名管理员审批，所以**管理员与评论**之间是**1对多**(1 : n)关系;

1本图书，可以有N条评论，而1条评论只能针对1本图书，所以**图书与评论**之间是**1对多**(1 : n)关系;

*  **学生与图书收藏**

1名学生，可以收藏N本图书，而1次收藏只能属于1名学生，所以**学生与收藏**之间是**1对多**(1 : n)关系;

1本图书，可以被不同学生收藏N次，而1次收藏只能针对1本图书，所以**图书与图书收藏**之间是**1对多**(1 : n)关系;

*  **管理员与公告**

1名管理员，可以发布N条公告，而1个公告只能由1名管理员发布，所以**管理员与公告**之间是**1对多**(1 : n)关系;

#### 3.3 表结构 ####

*  **用户表：**

![在这里插入图片描述][6f452cbc7dca4f5584aef54448907d70.png_pic_center]

*  **学生表：**

![在这里插入图片描述][2df4cacc35dc412c917ac671cdbf7a10.png_pic_center]

*  **图书表：**

![在这里插入图片描述][3f187e603aee4d919854fb9f6c049cdb.png_pic_center]

*  **公告表：**

![在这里插入图片描述][94e49acbcca24b349ce7426ff0ae67f7.png_pic_center]

*  **借阅证申请记录表：**

![在这里插入图片描述][213b2ca410454ec7bcd73faa9297628c.png_pic_center]

*  **图书借阅记录表：**

![在这里插入图片描述][c55c1a4a393c42938843dc4e2f9e0e54.png_pic_center]

*  **图书评论表：**

![在这里插入图片描述][3cd64de96ecb4fc89fc06f34bc613ff1.png_pic_center]

*  **图书收藏表：**

![在这里插入图片描述][b8affddfb4bb4b7092b648a34d1b379d.png_pic_center]

--------------------

## 四、物理结构设计与实施 ##

使用MySQL数据库，InnoDB存储引擎。

#### 4.1 创建数据库 ####

显示指定字符集为：utf8mb4，支持表情。

`数据库命名规则`：只包含小写字母和下划线，且以小写字母开头

CREATE DATABASE `db_book` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

#### 4.2 创建数据表 ####

显示指定字符集为：utf8mb4，支持表情。

`表和字段命名规则`：（参考《阿里巴巴手册 - 泰山版》）

*  只包含小写字母和下划线，且以小写字母开头
 *  针对不用分库分表的场景，主键命名为id，且为自增AUTO\_INCREMENT
 *  任何字段如果为非负数，必须是 unsigned
 *  表名不使用复数名词
 *  表必备三字段：id, gmt\_create, gmt\_modified。gmt\_create 创建时间, gmt\_modified 更新时间，类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新
 *  小数类型为 decimal，禁止使用 float 和 double
 *  禁用保留字，请参考 MySQL 官方保留字
 *  禁止使用外键
 *  禁止使用存储过程

![在这里插入图片描述][bd5e743f438742a89bca536fc1a87d3f.png_pic_center]

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user`  (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
      `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名',
      `password` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密码',
      `role` tinyint(1) UNSIGNED NOT NULL COMMENT '角色(0-学生 1-管理员)',
      `gmt_create` datetime(0) NOT NULL COMMENT '创建时间',
      `gmt_modified` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student`  (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学生编号',
      `user_id` int(11) UNSIGNED NOT NULL COMMENT '用户编号',
      `student_no` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生编号',
      `student_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生姓名',
      `nick_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生昵称',
      `department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生所属(例如XX学院XX系XX班)',
      `id_card_image` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '学生证照片',
      `is_approved` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否申请通过(0-未通过 1-通过)',
      `is_frozen` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否冻结(0-正常 1-冻结)',
      `gmt_create` datetime(0) NOT NULL COMMENT '创建时间',
      `gmt_modified` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    -- 剩余6张表的完整代码，请私信我、或添加文章底部微信联系我
    
    SET FOREIGN_KEY_CHECKS = 1;

#### 4.3 初始化数据 ####

只需要初始化添加管理员账号用户即可。

INSERT INTO `user` VALUES (1,'admin','2469c47aa7b758eefff3d75939b20c7b',1,NOW(),NOW());

--------------------

## 五、具体业务分析 ##

### 5.1 录入图书 ###

录入四大名著

INSERT INTO `book` VALUES (1, 'ts_00001', '西游记', 1, '吴承恩', '《西游记》主要描写了孙悟空、猪八戒、沙僧三人保护唐僧西行取经，唐僧从投胎到取经归来共遇到八十一难，一路降妖伏魔，化险为夷，最后到达西天、取得真经的故事。','xxx出版社','1990-10-10', '', 0, 0, 0, 1, 1, NULL, '2023-04-07 00:00:10', '2023-04-07 00:00:10');
    INSERT INTO `book` VALUES (2, 'ts_00002', '三国演义', 1, '罗贯中', '《三国演义》可大致分为黄巾起义、董卓之乱、群雄逐鹿、三国鼎立、三国归晋五大部分，描写了从东汉末年到西晋初年之间近百年的历史风云，以描写战争为主，诉说了东汉末年的群雄割据混战和魏、蜀、吴三国之间的政治和军事斗争，最终司马炎一统三国，建立晋朝的故事。','xxx出版社','1993-10-10', '', 0, 0, 0, 1, 1, NULL, '2023-04-07 00:00:10', '2023-04-07 00:00:10');
    INSERT INTO `book` VALUES (3, 'ts_00003', '水浒传', 1, '施耐庵', '《水浒传》是第一部描写农民起义的小说，全书围绕“官逼民反”这一线索展开情节，表现了一群不堪暴政欺压的“好汉”揭杆而起，聚义水泊梁山，直至接受招安致使起义失败的全过程。','xxx出版社','1995-10-10', '', 0, 0, 0, 1, 1, NULL, '2023-04-07 00:00:10', '2023-04-07 00:00:10');
    INSERT INTO `book` VALUES (4, 'ts_00004', '红楼梦', 1, '曹雪芹', '《红楼梦》是以贾、史、王、薛四大家族的兴衰为背景，以富贵公子贾宝玉为视角，以贾宝玉与林黛玉、薛宝钗的爱情婚姻悲剧为主线，描绘了一些闺阁佳人的人生百态，展现了真正的人性美和悲剧美，是一部从各个角度展现女性美以及中国古代社会百态的史诗性著作。','xxx出版社','1992-10-10', '', 0, 0, 0, 1, 1, NULL, '2023-04-07 00:00:10', '2023-04-07 00:00:10');

### 5.2 学生注册 + 申请借阅证 ###

#### 5.2.1 注册 ####

INSERT INTO `user` VALUES(2,'1032975@qq.com', '2469c47aa7b758eefff3d75939b20c7b',0,NOW(),NOW());

#### 5.2.2 填写学生信息，申请借阅证 ####

-- 填写学生信息
    INSERT INTO `student` VALUES (1, 2, 's_00001', '张三', 'better man', 'XX学院XX系XX班', '', 0, 0, '2023-04-08 00:00:00', '2023-04-08 00:00:00');
    -- student_id=1的学生，申请借阅证
    INSERT INTO `qualification`(`id`, `student_id`, `status`, `verify_time`, `verify_user_id`, `reject_reason`, `gmt_create`, `gmt_modified`) VALUES (1, 1, 0, NULL, NULL, '', '2023-04-08 00:00:00', '2023-04-08 00:00:00');

### 5.3 管理员审核借阅证通过 ###

-- verify_user_id=1的管理员，审批通过 申请借阅证记录id=1的申请，status=1审批通过
    update qualification set `status` = 1, 
    verify_time ='2023-04-08 00:10:00', 
    verify_user_id = 1, 
    gmt_modified = '2023-04-08 00:10:00' 
    where id = 1;
    -- 让id=1学生，拥有借阅资格，is_approved = 1为有借阅资格
    update student set is_approved = 1, 
    gmt_modified = '2023-04-08 00:10:00' 
    where id = 1;

### 5.4 搜索图书 ###

例如：按图书类型和名称搜索

展示字段包括：**图书名称**、**图书封面**、**借阅次数**、**评论条数**

按借阅次数倒序，分页从第0条开始，取10条

select id, book_name, cover_image, borrow_count, comment_count from book
    where book_type = 1 and book_name LIKE '红%'
    order by borrow_count desc
    limit 0,10

按出版时间正序，分页从第20条开始，取10条

select id, book_name, cover_image, borrow_count, comment_count from book
    where book_type = 1 and book_name LIKE '三%'
    order by publish_date
    limit 20,10

### 5.5 查看图书详情 ###

展示字段包括：**查看图书基本信息、我是否收藏、图书评论**

-- id=1的图书基本信息
    select * from book where id = 1;
    -- student_id=1的学生，对book_id=1的图书，未取消收藏 的 收藏记录
    select * from book_favorite 
    where book_id = 1 and student_id = 1 and is_cancelled = 0;
    -- student_id=1的学生，对book_id=1的图书，审核通过 的 评论，按创建时间倒序
    select * from book_comment 
    where book_id = 1 and student_id = 1 and `status` = 1 
    order by gmt_create desc;

### 5.6 图书收藏 和 取消收藏 ###

#### 5.6.1 收藏 ####

student\_id=1的学生，收藏 book\_id=1的图书

INSERT INTO book_favorite(`student_id`, `book_id`, `favorite_time`, `is_cancelled`, `cancel_time`, `gmt_create`, `gmt_modified`) VALUES (1, 1, '2023-04-08 00:10:00', 0, NULL, '2023-04-08 00:10:00', '2023-04-08 00:10:00');

#### 5.6.2 取消收藏 ####

student\_id=1的学生，取消收藏 book\_id=1的图书

update book_favorite 
    set is_cancelled = 1, cancel_time = '2023-04-08 02:10:00'
    where book_id = 1 and student_id = 1;

### 5.7 图书评论 和 审核评论 ###

#### 5.7.1 评论 ####

student\_id=1的学生，评论 book\_id=1的图书：status=0待审批

INSERT INTO `book_comment`(`student_id`, `book_id`, `score`, `comment`, `status`, `gmt_create`, `gmt_modified`) VALUES (1, 1, 5, '好书值得推荐', 0, '2023-04-08 00:10:00', '2023-04-08 00:10:00');

#### 5.7.2 审核评论 ####

verify\_user\_id=1的管理员，审核id=1的评论： status = 1审批通过

`审批后, 图书评论次数+1`

update book_comment 
    set `status` = 1, 
    verify_time = '2023-04-08 03:10:00',
    verify_user_id = 1,
    gmt_modified = '2023-04-08 03:10:00'
    where id = 1;
    -- 审批后, 图书评论次数+1
    update book
    comment_count = comment_count+1,
    gmt_modified = '2023-04-08 03:10:00'
    where id = 1;

### 5.8 借阅 和 借阅审核 ###

#### 5.8.1 借阅 ####

student\_id=1的学生，借阅 book\_id=1的图书

INSERT INTO `book_borrowing`(`student_id`, `book_id`, `borrow_time`, `status`,  `gmt_create`, `gmt_modified`) 
    VALUES (1, 1, '2023-04-08 03:10:00', 0, '2023-04-08 03:10:00', '2023-04-08 03:10:00');

#### 5.8.2 借阅审核 ####

verify\_user\_id=1的管理员，审核 id=1的借阅记录： status=1通过审批

`审批后, 图书借阅次数+1`

update book_borrowing 
    set `status` = 1,
    verify_time = '2023-04-08 04:10:00',
    verify_user_id = 1,
    gmt_modified = '2023-04-08 04:10:00'
    where id = 1;
    -- 审批后, 图书借阅次数+1，状态置为借阅中
    update book
    set status = 1,
    borrow_count = borrow_count+1,
    gmt_modified = '2023-04-08 04:10:00'
    where id = 1;

#### 5.8.3 归还 ####

id=1的借阅记录，书已归还：status = 3归还

`归还后, 更新图书状态为空闲`

update book_borrowing 
    set `status` = 3,
    return_time = '2023-04-18 05:10:00',
    gmt_modified = '2023-04-18 05:10:00'
    where id = 1;
    -- 归还后, 更新图书状态为空闲
    update book
    set status = 0,
    gmt_modified = '2023-04-18 05:10:00'
    where id = 1;

--------------------

## 参考 ##

[https://blog.csdn.net/Artificial\_idiots/article/details/106830628][https_blog.csdn.net_Artificial_idiots_article_details_106830628]  
[https://blog.csdn.net/weixin\_44584292/article/details/106636642][https_blog.csdn.net_weixin_44584292_article_details_106636642]

--------------------

## 最后 ##

本篇数据库设计花了很长时间来润笔，另外，还征求了大学生朋友的建议，这里特别感谢[会洗碗的CV工程师][CV]为我提供了课设的相关资料，更为本文提供了很多建设性的建议，希望能在实战的同时能给大学生朋友更多的帮助，如果有任何建议和意见，也欢迎大家留言或私信，我计划将持续更新，目的只为打造精品，互相切磋，共同成长！能让大家通过这一篇数据库设计，学到**实战中设计技巧**！当然，这只是一个小小的系统，有很多设计技巧还无法体现，一个合理的设计非常重要，可以支撑更大的业务场景，所以仅以本设计抛转引玉，后面有机会我们可以拿更多的**场景或系统**来设计，如果你有需求，也可以找我来设计，我尽可能满足！

想学习更多实战真东西的，欢迎点击链接订阅：

**服务端实战** ：[SpringBoot+Vue前后端分离项目实战][SpringBoot_Vue]  
**前端实战** ：[Vue + SpringBoot前后端分离项目实战][Vue _ SpringBoot]

另外，别忘了关注：[天罡gg][gg] ，发布新文不容易错过： https://blog.csdn.net/scm\_2008

老规矩，请投票给我反馈，谢谢大家的支持！

[CSDN]: https://img-blog.csdnimg.cn/63748e72dc314943857316f3b1f6a386.gif#pic_center
[-]: https://img-blog.csdnimg.cn/942a596c824443339dc55d98c30cb17e.png#pic_center
[d65650d8ce8e4f7bacf547523173a1cf.png_pic_center]: https://img-blog.csdnimg.cn/d65650d8ce8e4f7bacf547523173a1cf.png#pic_center
[22b4fc58235848b1b40297d5908cbc97.png_pic_center]: https://img-blog.csdnimg.cn/22b4fc58235848b1b40297d5908cbc97.png#pic_center
[8ac090e3a73d4bf593788014ebb0d8ec.png_pic_center]: https://img-blog.csdnimg.cn/8ac090e3a73d4bf593788014ebb0d8ec.png#pic_center
[277b0bb040284a198a165e5e01d6437a.png_pic_center]: https://img-blog.csdnimg.cn/277b0bb040284a198a165e5e01d6437a.png#pic_center
[9351bc6c18644c5c9951683840d49132.png_pic_center]: https://img-blog.csdnimg.cn/9351bc6c18644c5c9951683840d49132.png#pic_center
[e04cea0fe88b495d8c3e98e1f64035a6.png_pic_center]: https://img-blog.csdnimg.cn/e04cea0fe88b495d8c3e98e1f64035a6.png#pic_center
[542ffed5d799470da3b290705c241924.png_pic_center]: https://img-blog.csdnimg.cn/542ffed5d799470da3b290705c241924.png#pic_center
[da6b504150cc403e9d1ec9f018b03b36.png_pic_center]: https://img-blog.csdnimg.cn/da6b504150cc403e9d1ec9f018b03b36.png#pic_center
[e49b21c35e6244c69c23f83b3ea3be94.png_pic_center]: https://img-blog.csdnimg.cn/e49b21c35e6244c69c23f83b3ea3be94.png#pic_center
[feba7f6602ff4ca6b429924faac024af.png_pic_center]: https://img-blog.csdnimg.cn/feba7f6602ff4ca6b429924faac024af.png#pic_center
[947279bc622d481e983e71670c5ba896.png_pic_center]: https://img-blog.csdnimg.cn/947279bc622d481e983e71670c5ba896.png#pic_center
[4a4aa4300f8546b5a952e9a17d9bddad.png_pic_center]: https://img-blog.csdnimg.cn/4a4aa4300f8546b5a952e9a17d9bddad.png#pic_center
[6cc9028c2dc34e1bbc9a22312a6999b1.png_pic_center]: https://img-blog.csdnimg.cn/6cc9028c2dc34e1bbc9a22312a6999b1.png#pic_center
[-_ER]: https://img-blog.csdnimg.cn/1c2aee6b8d034afb863713ac046d66af.png#pic_center
[- 1]: https://img-blog.csdnimg.cn/f9e007e252a647599a15e1cc8b9e3e4f.png#pic_center
[6f452cbc7dca4f5584aef54448907d70.png_pic_center]: https://img-blog.csdnimg.cn/6f452cbc7dca4f5584aef54448907d70.png#pic_center
[2df4cacc35dc412c917ac671cdbf7a10.png_pic_center]: https://img-blog.csdnimg.cn/2df4cacc35dc412c917ac671cdbf7a10.png#pic_center
[3f187e603aee4d919854fb9f6c049cdb.png_pic_center]: https://img-blog.csdnimg.cn/3f187e603aee4d919854fb9f6c049cdb.png#pic_center
[94e49acbcca24b349ce7426ff0ae67f7.png_pic_center]: https://img-blog.csdnimg.cn/94e49acbcca24b349ce7426ff0ae67f7.png#pic_center
[213b2ca410454ec7bcd73faa9297628c.png_pic_center]: https://img-blog.csdnimg.cn/213b2ca410454ec7bcd73faa9297628c.png#pic_center
[c55c1a4a393c42938843dc4e2f9e0e54.png_pic_center]: https://img-blog.csdnimg.cn/c55c1a4a393c42938843dc4e2f9e0e54.png#pic_center
[3cd64de96ecb4fc89fc06f34bc613ff1.png_pic_center]: https://img-blog.csdnimg.cn/3cd64de96ecb4fc89fc06f34bc613ff1.png#pic_center
[b8affddfb4bb4b7092b648a34d1b379d.png_pic_center]: https://img-blog.csdnimg.cn/b8affddfb4bb4b7092b648a34d1b379d.png#pic_center
[bd5e743f438742a89bca536fc1a87d3f.png_pic_center]: https://img-blog.csdnimg.cn/bd5e743f438742a89bca536fc1a87d3f.png#pic_center
[https_blog.csdn.net_Artificial_idiots_article_details_106830628]: https://blog.csdn.net/Artificial_idiots/article/details/106830628
[https_blog.csdn.net_weixin_44584292_article_details_106636642]: https://blog.csdn.net/weixin_44584292/article/details/106636642
[CV]: https://blog.csdn.net/qq_53317005
[SpringBoot_Vue]: https://blog.csdn.net/scm_2008/category_12236048.html
[Vue _ SpringBoot]: https://blog.csdn.net/xingyu_qie/category_12222258.html
[gg]: https://blog.csdn.net/scm_2008