MySQL综合实验 图书管理系统

目录

• 第一章 题目
• 第二章 整体思路与数据库设计
  • 2.1 功能与数据表的对应关系
  • 2.2 表之间的主外键关系
  • 2.3 完整性及约束设计
  • 2.4 数据库对象设计
• 第三章 数据库和数据表的创建
  • 3.1 创建数据库
  • 3.2 创建登陆表（user_login）
  • 3.3 创建图书信息表（book_info）
  • 3.4 创建读者信息表（reader_info）
  • 3.5 创建借阅信息表（borrow_info）
• 第四章 约束与索引
  • 4.1 主键及外键
  • 4.2 默认约束与CHECK约束
  • 4.3 创建索引
• 第五章 视图与存储过程设计
  • 5.1 视图：逾期未归还的图书信息
  • 5.2 存储过程：根据图书编号查询未归还的读者信息
  • 5.3 存储过程：读者资料查询
• 第六章 触发器设计
  • 6.1 删除触发器：读者有借阅书时不可删除
  • 6.2 借阅处理：在borrow_info表上INSERT触发器
  • 6.3 还书处理：在borrow_info表上UPDATE触发器
• 第七章 测试数据插入与功能验证
• 第八章 完整示例代码与测试
  • 8.1 输入示例
  • 8.2 结果示例
• 总结

第一章 题目

需求分析：

1. 基本功能需求：

   • 主要功能是对图书信息的添加，修改，删除；读者的添加，删除，修改；以及对读者权限登录的控制

     1）信息录入功能

     • 添加新图书信息。只有管理员有权限操作
     • 添加读者信息。用于登记新读者信息。只有管理员有权限操作。
     • 借阅信息。用于登记读者的借阅情况信息。

     2）逻辑功能要求

     • 修改和删除图书信息。图书被借出时，系统需要更新图书信息的可借数量，当可借数量为0时，表示该图书都已被借出。当输入的图书信息有错误或需要进行必要更新时，可以修改图书信息；当一种图书所有馆藏图书都已损毁或遗失并且不能重新买到时，该图书信息需要删除。
     • 修改和删除读者信息。如某读者毕业了可删除该读者的信息。
     • 还书处理。读者归还图书时，更新图书借阅信息表中的归还日期，读者信息表中的已借数量及ISBN类别信息表中该图书的可借数量。当图书期限过期，自动计算罚款。
     • 借书限制。当读者有罚款时，不能借书。可提示是否要交罚款，交完之后即可借书。

     3）查询功能

     • 图书查询功能。根据图书的各种已知条件来查询图书的详细信息，如书名、作者、ISBN书号等支持模糊查询。
     • 读者信息查询。输入读者的借书证号、姓名等信息，查询读者的基本信息。对查询到的每一个读者，能够显示其未归还的图书编号和书名。
     • 查询所有到期未归还的图书信息。要求结果显示图书编号、书名、读者姓名、借书证号码、借出日期等信息。

2. 数据表的创建
   根据功能要求的说明创建下列数据表：

   1）登陆表

   • 账号，密码

   2）图书信息表

   • ISBN书号、书名、出版社、作者、馆藏数量、可借数量、是否可借。

   3）读者信息表

   • 借书证号、姓名、性别、职称、可借数量、已借数量、工作部门、联系电话。

   4）借阅信息表（图书-读者关系）

   • 借书证号、借阅书号、借出日期、借阅期限、归还日期、罚款。

3. 数据库完整性设计

   • 给每个表实施主键及外键约束。
   • 设定缺省约束。如性别。
   • 设置非空约束如图书信息表中的书名。
   • 实施CHECK约束。

4. SQL Server数据库对象设计
   1）设计一个存储过程，以图书编号为输入参数，返回借阅该图书但未归还的读者姓名和借书证号。
   2）读者资料查询：设计一个有多个输入参数的存储过程，返回读者的详细信息。设计另一存储过程并以读者借书证号为输入参数，返回该读者未归还的图书名称和图书编号。
   3）到期图书查询：设计一个视图，返回所有逾期未归还的图书的编号、书名、读者姓名等信息。
   4）加快数据检索速度，用图书编号为图书信息表建立索引。
   5）为读者信息表创建一个删除触发器，当一个读者毕业或者其他情况时，将此读者的资料从读者信息表中删除。注意实施业务规则：有借阅书的读者不得从读者信息表中删除。
   6）借阅处理：为借阅信息表设计INSERT触发器，在读者借阅时更改ISBN类别信息表，且可借数量减1，图书信息表是否可借列的值变为“不可借”，读者信息表中该类读者已借阅数加1。
   7）还书处理：为借阅信息表设计UPDATE触发器，在该表的归还日期列被更改后，将图书信息表的是否可借列的值变为“可借”，读者信息表中已借数量减1及ISBN类别信息表中可借数量加1。

备注：该题目不能随意删除表的字段，可相应增加相关字段，除了上述的基本功能之外，可另外添加其他功能。

第二章 整体思路与数据库设计

在本章节中，我们将先从整体上理清所需的数据表、字段、主外键关系、以及基本的操作需求，然后一步步实现。

2.1 功能与数据表的对应关系

1. 登陆表：

   • 用于存储系统用户（管理员或普通读者）的账号和密码，实现登录验证。
   • 字段：账号、密码。

2. 图书信息表：

   • 存储所有图书的基本信息，比如：ISBN书号（主键）、书名、出版社、作者、馆藏数量、可借数量、是否可借。

3. 读者信息表：

   • 存储所有读者的基本信息，比如：借书证号（主键）、姓名、性别、职称、可借数量、已借数量、工作部门、联系电话等。

4. 借阅信息表：

   • 用于记录每一次图书借阅行为，比如：借书证号（外键）、借阅书号（外键，对应ISBN）、借出日期、借阅期限、归还日期、罚款等信息。

2.2 表之间的主外键关系

1. 登录表和其他表之间可能暂时无直接外键（取决于需求，看是否将管理员账号与读者账号都整合在同一表中，或单独存储）。
2. 图书信息表
   • 主键：ISBN书号
3. 读者信息表
   • 主键：借书证号
4. 借阅信息表
   • 外键1：借书证号 -> 读者信息表
   • 外键2：借阅书号 -> 图书信息表

2.3 完整性及约束设计

1. 主键约束：
   • 图书信息表 (ISBN)
   • 读者信息表 (借书证号)
   • 登录表 (账号)
   • 借阅信息表可设置一个自增主键或联合主键(借书证号 + 借阅书号 + 借出日期)等多字段。
2. 外键约束：
   • 借阅信息表的借书证号 -> 读者信息表
   • 借阅信息表的借阅书号 -> 图书信息表
3. 非空约束：
   • 图书信息表中的书名非空
   • 读者信息表中的姓名非空
4. 默认值：
   • 读者信息表中的性别可设置默认值，如’男’或’女’
5. CHECK约束：
   • 在MySQL 8.0+ 中，可以使用 CHECK 约束去控制例如：借书数量 >= 0。
6. 触发器：
   • 借阅前置触发器：借书时自动更新相关表
   • 还书触发器：还书时自动更新相关表
   • 删除触发器：删除读者时检查是否有未归还图书

2.4 数据库对象设计

1. 存储过程
   • 根据图书编号查询未归还的读者信息
   • 查询读者详细信息的存储过程
   • 查询某读者未归还的图书信息的存储过程
2. 视图
   • 查询所有逾期未归还图书的视图
3. 索引
   • 为图书信息表的ISBN字段创建索引（如果该字段是主键，那么本身就会有主键索引）
4. 触发器
   • 删除读者之前触发器
   • 借书时INSERT触发器
   • 还书时UPDATE触发器

第三章 数据库和数据表的创建

下面的示例将以 MySQL 8.0+ 的语法为基础进行演示，代码会略加简化说明，以方便初学者理解。完整代码请在最后一章查看。

3.1 创建数据库

-- 第1行：创建名为library_system的数据库
CREATE DATABASE library_system 
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

-- 第2行：使用刚刚创建的数据库
USE library_system;

解释：

• 第1行 CREATE DATABASE library_system: 创建一个名称为library_system的数据库。
• 第2行 USE library_system;：指定后续操作都在该数据库下进行。

3.2 创建登陆表（user_login）

CREATE TABLE user_login (
    user_id VARCHAR(50) NOT NULL,         -- 第1行：账号（主键）
    user_password VARCHAR(50) NOT NULL,   -- 第2行：密码
    user_role ENUM('admin', 'reader') DEFAULT 'reader', -- 第3行：用户角色（管理员或读者），默认为reader
    PRIMARY KEY (user_id)                -- 第4行：设置主键
);

解释：

• 第1行：user_id表示系统登陆用的账号，设置为NOT NULL以防止空值。
• 第2行：user_password为用户密码，同样不允许为空。
• 第3行：user_role设置为枚举类型，仅可取admin或reader，并给出默认值reader。
• 第4行：设置user_id为主键。

注意：在实际项目中，密码需加密存储，这里仅作示例。

3.3 创建图书信息表（book_info）

CREATE TABLE book_info (
    ISBN VARCHAR(20) NOT NULL,           -- 第1行：图书ISBN书号，主键
    book_name VARCHAR(100) NOT NULL,     -- 第2行：书名，非空
    publisher VARCHAR(100),             -- 第3行：出版社
    author VARCHAR(100),                -- 第4行：作者
    total_count INT DEFAULT 1,          -- 第5行：馆藏数量，默认值为1
    available_count INT DEFAULT 1,      -- 第6行：可借数量，默认值为1
    is_borrowable ENUM('可借','不可借') DEFAULT '可借', -- 第7行：是否可借，默认为可借
    PRIMARY KEY (ISBN),
    CHECK (available_count >= 0),       -- 第8行：CHECK约束，可借数量不能为负
    CHECK (total_count >= 0)            -- 第9行：CHECK约束，馆藏数量不能为负
);

解释：

• 第1行：ISBN为图书的唯一编号，设为NOT NULL并作为主键。
• 第2行：book_name为书名，设置为NOT NULL，即必须有书名。
• 第5行/第6行：total_count和available_count都设置了默认值为1。
• 第7行：is_borrowable可设置为“可借”或“不可借”，这里使用ENUM类型并赋默认值“可借”。
• 第8行/第9行：设置CHECK约束，保证available_count与total_count不小于0。MySQL 8.0+ 开始支持 CHECK 约束。

3.4 创建读者信息表（reader_info）

CREATE TABLE reader_info (
    reader_id VARCHAR(50) NOT NULL,       -- 第1行：借书证号，主键
    reader_name VARCHAR(50) NOT NULL,     -- 第2行：读者姓名，非空
    gender ENUM('男',