MySQL圖書管理系統實踐總結

作為一個數據庫課程的大作業，我們小組選擇了開發一個基于MySQL數據庫的圖書管理系統。下面是我們的實踐總結。

一、數據庫設計

CREATE DATABASE Library;
USE Library;
CREATE TABLE book_info(
book_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
book_name VARCHAR(50) NOT NULL,
author VARCHAR(30) NOT NULL,
publisher VARCHAR(50) NOT NULL,
pub_year YEAR NOT NULL,
price DECIMAL(7,2) UNSIGNED NOT NULL
);
CREATE TABLE user_info(
user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(30) NOT NULL,
department VARCHAR(50) NOT NULL
);
CREATE TABLE borrow_info(
borrow_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
book_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
borrow_date DATE NOT NULL,
due_date DATE NOT NULL,
FOREIGN KEY(book_id) REFERENCES book_info(book_id),
FOREIGN KEY(user_id) REFERENCES user_info(user_id)
);

二、數據輸入與管理

-- 插入圖書記錄
INSERT INTO book_info(book_name, author, publisher, pub_year, price)
VALUES('Database Systems: Concepts, Design and Implementation', 'K. Korth, S. Sudarshan', 'McGraw-Hill Education', 2014, 99.99);
-- 插入用戶記錄
INSERT INTO user_info(user_name, department)
VALUES('Bob', 'Computer Science');
-- 借書操作
INSERT INTO borrow_info(book_id, user_id, borrow_date, due_date)
VALUES(1, 1, '2021-06-12', '2021-06-29');
-- 還書操作
DELETE FROM borrow_info
WHERE borrow_id = 1;

三、SQL查詢與統計

-- 查詢所有用戶記錄
SELECT * FROM user_info;
-- 查詢已借出的圖書記錄
SELECT * FROM book_info WHERE book_id IN (SELECT book_id FROM borrow_info);
-- 統計每個用戶已借閱的圖書數目
SELECT user_id, user_name, COUNT(*) as borrowed_num
FROM user_info
LEFT JOIN borrow_info ON user_info.user_id = borrow_info.user_id
GROUP BY user_info.user_id;

以上是MySQL圖書管理系統的一些實踐總結，我們通過實踐不僅鞏固了數據庫基礎，提高了編程能力，而且還鍛煉了團隊協作能力。