MySQL 实践教程：构建图书管理系统（第五阶段）

本文链接：https://blog.csdn.net/m0_54490473/article/details/140676294

MySQL 实践教程：构建图书管理系统

引言

在数据管理领域，MySQL 是一种广泛使用的关系型数据库管理系统。无论是个人项目还是企业应用，掌握 MySQL 可以帮助我们有效地存储和管理数据。本文将指导读者构建一个简单的图书管理系统，同时参与开源项目，并深入学习 MySQL 的高级特性。

项目概述：图书管理系统

本项目的目标是构建一个简单的图书管理系统，允许用户进行图书的增、删、改、查，以及借阅和归还管理。系统将使用 MySQL 作为数据存储后端。

功能需求

用户管理：注册、登录、查看用户信息。
图书管理：
- 增加新书
- 编辑书籍信息
- 删除书籍
- 查询书籍
借阅管理：
- 借阅图书
- 归还图书
- 查询借阅记录

环境准备

1. 安装 MySQL

在你的机器上安装 MySQL，可以参考以下链接进行安装：MySQL 官方文档。

2. 准备开发环境

选择一门编程语言（如 Python、Java、PHP），并确保你的开发环境已设置好。例如，如果选择 Python，可以安装 Flask 框架来快速构建 Web 应用。

3. 安装必要的库（以 Python 为例）

使用 pip 安装 Flask 及 MySQL Connector：

pip install Flask mysql-connector-python

数据库设计

我们将设计如下四个基本表：

1. Users 表

字段名	类型	描述
user_id	INT	用户 ID (主键)
username	VARCHAR(50)	用户名
password	VARCHAR(100)	密码（加密存储）

2. Books 表

字段名	类型	描述
book_id	INT	图书 ID (主键)
title	VARCHAR(100)	书名
author	VARCHAR(100)	作者
published	DATE	出版日期
is_available	BOOLEAN	是否可借

3. Borrowed_Books 表

字段名	类型	描述
borrowed_id	INT	借阅记录 ID (主键)
user_id	INT	借阅用户 ID (外键)
book_id	INT	借阅图书 ID (外键)
borrowed_date	DATE	借阅日期
return_date	DATE	归还日期

创建数据库和表

连接到 MySQL 数据库，执行以下 SQL 语句创建数据库和表：

CREATE DATABASE library;

USE library;

CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL
);

CREATE TABLE Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(100) NOT NULL,
    published DATE,
    is_available BOOLEAN DEFAULT TRUE
);

CREATE TABLE Borrowed_Books (
    borrowed_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    book_id INT,
    borrowed_date DATE,
    return_date DATE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (book_id) REFERENCES Books(book_id)
);

开发图书管理系统

Flask 应用结构

library_management/
    ├── app.py
    ├── requirements.txt
    ┓── templates/
    ┓   ├── index.html
    ┓   ├── login.html
    ┓   └── dashboard.html

app.py 文件

from flask import Flask, request, render_template, redirect, url_for
import mysql.connector
from mysql.connector import Error

app = Flask(__name__)

# MySQL 连接配置
def create_connection():
    connection = mysql.connector.connect(
        host='localhost',
        database='library',
        user='your_username',
        password='your_password'
    )
    return connection

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == "POST":
        username = request.form['username']
        password = request.form['password']
        connection = create_connection()
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM Users WHERE username=%s AND password=%s", (username, password))
        user = cursor.fetchone()
        cursor.close()
        connection.close()
        if user:
            return redirect(url_for('dashboard'))
    return render_template('login.html')

@app.route('/dashboard')
def dashboard():
    connection = create_connection()
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM Books")
    books = cursor.fetchall()
    cursor.close()
    connection.close()
    return render_template('dashboard.html', books=books)

if __name__ == '__main__':
    app.run(debug=True)

HTML 模板

index.html

<!DOCTYPE html>
<html>
<head>
    <title>图书管理系统</title>
</head>
<body>
    <h1>欢迎访问图书管理系统</h1>
    <a href="/login">登录</a>
</body>
</html>

login.html

<!DOCTYPE html>
<html>
<head>
    <title>登录</title>
</head>
<body>
    <h1>用户登录</h1>
    <form method="post">
        用户名:<input type="text" name="username" required><br>
        密码:<input type="password" name="password" required><br>
        <input type="submit" value="登录">
    </form>
</body>
</html>

dashboard.html

<!DOCTYPE html>
<html>
<head>
    <title>仪表盘</title>
</head>
<body>
    <h1>图书列表</h1>
    <table>
        <tr>
            <th>ID</th>
            <th>书名</th>
            <th>作者</th>
        </tr>
        {% for book in books %}
        <tr>
            <td>{{ book[0] }}</td>
            <td>{{ book[1] }}</td>
            <td>{{ book[2] }}</td>
        </tr>
        {% endfor %}
    </table>
</body>
</html>

参与开源项目

选择开源项目：访问 GitHub，寻找带有 MySQL 的开源项目，如 Bookshelf。
贡献代码：
- Fork 项目，克隆到本地。
- 解决 issue 或实现新功能。
- 提交 Pull Request。

深入学习 MySQL

存储过程、触发器和函数

存储过程：封装 SQL 操作，如插入新书：

CREATE PROCEDURE AddBook(IN title VARCHAR(100), IN author VARCHAR(100), IN published DATE)
BEGIN
    INSERT INTO Books (title, author, published) VALUES (title, author, published);
END;

触发器：在图书删除前记录日志：

CREATE TRIGGER before_delete_book
BEFORE DELETE ON Books
FOR EACH ROW
BEGIN
    INSERT INTO Deleted_Books_log (book_id, deleted_at) VALUES (OLD.book_id, NOW());
END;

函数：计算借出书籍数量：

CREATE FUNCTION CountBorrowedBooks(user_id INT) RETURNS INT
BEGIN
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM Borrowed_Books WHERE user_id = user_id;
    RETURN count;
END;