七天掌握SQL---＞第七天：项目实践与总结

一、项目实践

1.1 项目背景

假设我们正在开发一个名为“在线图书管理系统”的项目。该项目旨在帮助图书馆管理员管理图书的借阅、归还、库存等操作，同时为读者提供一个便捷的图书查询和借阅平台。

1.2 数据库设计

1.2.1 需求分析

根据项目的需求，我们需要设计以下几个实体：

读者（Reader）：包括读者ID、姓名、性别、年龄、联系方式等字段。
图书（Book）：包括图书ID、书名、作者、出版社、ISBN、库存数量等字段。
借阅记录（BorrowRecord）：包括借阅ID、读者ID、图书ID、借阅日期、归还日期等字段。

1.2.2 E-R图

根据需求分析，我们可以绘制出如下的E-R图：

实体	属性
读者（Reader）	读者ID（PK）、姓名、性别、年龄、联系方式
图书（Book）	图书ID（PK）、书名、作者、出版社、ISBN、库存数量
借阅记录（BorrowRecord）	借阅ID（PK）、读者ID（FK）、图书ID（FK）、借阅日期、归还日期

其中，PK表示主键，FK表示外键。

1.2.3 数据库表结构

根据E-R图，我们可以设计出如下的数据库表结构：

Reader表：

字段名称	数据类型	约束条件
ReaderID	INT	PRIMARY KEY
Name	VARCHAR	NOT NULL
Gender	CHAR(1)	NOT NULL
Age	INT	NOT NULL
Contact	VARCHAR	NOT NULL

Book表：

字段名称	数据类型	约束条件
BookID	INT	PRIMARY KEY
Title	VARCHAR	NOT NULL
Author	VARCHAR	NOT NULL
Publisher	VARCHAR	NOT NULL
ISBN	VARCHAR	UNIQUE
Stock	INT	NOT NULL

BorrowRecord表：

字段名称	数据类型	约束条件
BorrowID	INT	PRIMARY KEY
ReaderID	INT	FOREIGN KEY REFERENCES Reader(ReaderID)
BookID	INT	FOREIGN KEY REFERENCES Book(BookID)
BorrowDate	DATE	NOT NULL
ReturnDate	DATE	NULL

1.3 SQL查询实践

1.3.1 创建数据库和表

首先，我们需要创建数据库和表：

CREATE DATABASE LibraryManagement;USE LibraryManagement;CREATE TABLE Reader (ReaderID INT PRIMARY KEY,Name VARCHAR(100) NOT NULL,Gender CHAR(1) NOT NULL,Age INT NOT NULL,Contact VARCHAR(100) NOT NULL
);CREATE TABLE Book (BookID INT PRIMARY KEY,Title VARCHAR(200) NOT NULL,Author VARCHAR(100) NOT NULL,Publisher VARCHAR(100) NOT NULL,ISBN VARCHAR(20) UNIQUE,Stock INT NOT NULL
);CREATE TABLE BorrowRecord (BorrowID INT PRIMARY KEY,ReaderID INT,BookID INT,BorrowDate DATE NOT NULL,ReturnDate DATE NULL,FOREIGN KEY (ReaderID) REFERENCES Reader(ReaderID),FOREIGN KEY (BookID) REFERENCES Book(BookID)
);

1.3.2 插入数据

接下来，我们向表中插入一些数据：

-- 插入读者数据
INSERT INTO Reader (ReaderID, Name, Gender, Age, Contact) VALUES
(1, '张三', '男', 25, '12345678901'),
(2, '李四', '女', 30, '09876543210');-- 插入图书数据
INSERT INTO Book (BookID, Title, Author, Publisher, ISBN, Stock) VALUES
(1, 'SQL必知必会', 'Ben Forta', '人民邮电出版社', '9787115430547', 10),
(2, 'Java编程思想', 'Bruce Eckel', '机械工业出版社', '9787111544303', 5);-- 插入借阅记录数据
INSERT INTO BorrowRecord (BorrowID, ReaderID, BookID, BorrowDate, ReturnDate) VALUES
(1, 1, 1, '2023-10-01', NULL),
(2, 2, 2, '2023-10-02', '2023-10-05');

1.3.3 查询数据

查询所有读者信息：

SELECT * FROM Reader;

查询库存数量大于5的图书信息：

SELECT * FROM Book WHERE Stock > 5;

查询张三借阅的图书信息：

SELECT b.Title
FROM BorrowRecord br
JOIN Book b ON br.BookID = b.BookID
WHERE br.ReaderID = (SELECT ReaderID FROM Reader WHERE Name = '张三');

查询未归还的借阅记录：

SELECT * FROM BorrowRecord WHERE ReturnDate IS NULL;

1.3.4 更新数据

更新图书《SQL必知必会》的库存数量：

UPDATE Book SET Stock = Stock - 1 WHERE BookID = 1;

更新读者张三的联系方式：

UPDATE Reader SET Contact = '11122233344' WHERE Name = '张三';

1.3.5 删除数据

删除读者李四的借阅记录：

DELETE FROM BorrowRecord WHERE ReaderID = 2;

删除库存数量为0的图书：

DELETE FROM Book WHERE Stock = 0;

1.4 事务处理

在图书管理系统中，借阅和归还操作需要保证数据的一致性，因此我们需要使用事务来处理这些操作。

1.4.1 借阅操作

借阅操作需要同时更新图书的库存数量和插入借阅记录，我们可以使用事务来保证这两个操作要么都成功，要么都失败。

START TRANSACTION;-- 更新图书库存数量
UPDATE Book SET Stock = Stock - 1 WHERE BookID = 1;-- 插入借阅记录
INSERT INTO BorrowRecord (BorrowID, ReaderID, BookID, BorrowDate, ReturnDate) VALUES (3, 1, 1, '2023-10-10', NULL);-- 提交事务
COMMIT;

如果在执行过程中发生错误，我们可以使用ROLLBACK来回滚事务：

START TRANSACTION;-- 更新图书库存数量（假设图书ID不存在，会报错）
UPDATE Book SET Stock = Stock - 1 WHERE BookID = 999;-- 插入借阅记录（由于前面的语句报错，此语句不会执行）
INSERT INTO BorrowRecord (BorrowID, ReaderID, BookID, BorrowDate, ReturnDate) VALUES (4, 1, 1, '2023-10-11', NULL);-- 回滚事务
ROLLBACK;

1.4.2 归还操作

归还操作需要同时更新借阅记录的归还日期和图书的库存数量，同样可以使用事务来处理。

START TRANSACTION;-- 更新借阅记录的归还日期
UPDATE BorrowRecord SET ReturnDate = '2023-10-15' WHERE BorrowID = 1;-- 更新图书库存数量
UPDATE Book SET Stock = Stock + 1 WHERE BookID = 1;-- 提交事务
COMMIT;