在信息技术日益发达的今天，数据库已经成为了数据存储和管理的核心工具，无论是企业的数据管理，还是个人项目的数据组织，掌握数据库技术都是至关重要的，MySQL作为一种广泛使用的关系型数据库管理系统（RDBMS），因其开源、跨平台、性能优越等特点，受到了广大开发者的青睐，本文将通过一系列练习，帮助读者深入理解MySQL的基本操作及其应用。

一、数据库设计与创建

1. 创建数据库

我们需要创建一个名为library的数据库。

CREATE DATABASE library;
USE library;

2. 创建图书类别表（booktype）

图书类别表用于存储不同类型图书的信息。

CREATE TABLE booktype (
    typeid INT PRIMARY KEY,
    typename VARCHAR(20) NOT NULL
);

插入一些示例数据：

INSERT INTO booktype (typeid, typename) VALUES
(1, '自然科学'),
(2, '数学'),
(3, '计算机'),
(4, '建筑水利'),
(5, '旅游地理'),
(6, '励志/自我实现'),
(7, '工业技术'),
(8, '基础医学'),
(9, '室内设计'),
(10, '人文景观');

3. 创建图书信息表（book）

图书信息表用于存储图书的基本信息。

CREATE TABLE book (
    bookid CHAR(10) PRIMARY KEY,
    bookname VARCHAR(20) NOT NULL,
    typeid INT,
    bookauthor VARCHAR(20),
    bookpublisher VARCHAR(50),
    bookprice DOUBLE,
    borrowsum INT,
    FOREIGN KEY (typeid) REFERENCES booktype(typeid)
);

插入一些示例数据：

INSERT INTO book (bookid, bookname, typeid, bookauthor, bookpublisher, bookprice, borrowsum) VALUES
('TP39/1712', 'Java程序设计', 3, '陈永红', '机械工业出版社', 35.5, 30),
('013452', '离散数学', 2, '张小新', '机械工业出版社', 45.5, 10),
('TP/3452', 'JSP程序设计案例', 3, '刘城清', '电子工业出版社', 42.8, 8),
('TH/2345', '机械设计手册', 7, '黄明凡', '人民邮电出版社', 40, 10),
('R/345677', '中医的故事', 8, '李奇德', '国防工业出版社', 20.0, 5);

4. 创建图书存储信息表（bookstorage）

图书存储信息表用于记录图书的存储和状态信息。

CREATE TABLE bookstorage (
    bookbarcode CHAR(20) PRIMARY KEY,
    bookid CHAR(10) NOT NULL,
    bookintime DATETIME,
    bookstatus VARCHAR(4),
    FOREIGN KEY (bookid) REFERENCES book(bookid)
);

插入一些示例数据：

INSERT INTO bookstorage (bookbarcode, bookid, bookintime, bookstatus) VALUES
('123456789012345', 'TP39/1712', NOW(), '在馆'),
('234567890123456', '013452', NOW(), '在馆');

5. 创建读者类别表（readertype）

读者类别表用于区分不同类型的读者。

CREATE TABLE readertype (
    retypeid INT PRIMARY KEY,
    typename VARCHAR(20) NOT NULL,
    borrowquantity INT NOT NULL,
    borrowday INT NOT NULL
);

插入一些示例数据：

INSERT INTO readertype (retypeid, typename, borrowquantity, borrowday) VALUES
(1, '学生', 10, 30),
(2, '教师', 20, 60),
(3, '管理员', 15, 30),
(4, '职工', 15, 20);

6. 创建读者信息表（reader）

读者信息表用于存储读者的基本信息。

CREATE TABLE reader (
    readerid CHAR(10) PRIMARY KEY,
    readername VARCHAR(20) NOT NULL,
    readerpass VARCHAR(20) NOT NULL,
    retypeid INT,
    readerdate DATETIME,
    readerstatus VARCHAR(4),
    FOREIGN KEY (retypeid) REFERENCES readertype(retypeid)
);

插入一些示例数据：

INSERT INTO reader (readerid, readername, readerpass, retypeid, readerdate, readerstatus) VALUES
('S001', '张三', '123456', 1, NOW(), '正常'),
('S002', '李四', 'abcdef', 2, NOW(), '正常');

7. 创建图书借阅表（bookborrow）

图书借阅表用于记录图书的借阅信息。

CREATE TABLE bookborrow (
    borrowid CHAR(10) PRIMARY KEY,
    bookbarcode CHAR(20) NOT NULL,
    readerid CHAR(10) NOT NULL,
    borrowtime DATETIME,
    returntime DATETIME,
    borrowstatus VARCHAR(4),
    FOREIGN KEY (bookbarcode) REFERENCES bookstorage(bookbarcode),
    FOREIGN KEY (readerid) REFERENCES reader(readerid)
);

插入一些示例数据：

INSERT INTO bookborrow (borrowid, bookbarcode, readerid, borrowtime, returntime, borrowstatus) VALUES
('B001', '123456789012345', 'S001', NOW(), NULL, '借出'),
('B002', '234567890123456', 'S002', NOW(), NULL, '借出');

二、查询练习

1. 查询所有图书类别的名称和图书数量

SELECT b.typename, COUNT(*) AS book_count
FROM booktype b
LEFT JOIN book k ON b.typeid = k.typeid
GROUP BY b.typename;

这个查询展示了每个图书类别下有多少本书，使用了左连接来确保即使某个类别下没有图书，也会显示该类别。

2. 查询每个读者的借书情况，包括未归还的书籍和已归还的书籍数量

SELECT r.readername, COUNT(b.bookid) AS borrowed_books, SUM(CASE WHEN b.returntime IS NOT NULL THEN 1 ELSE 0 END) AS returned_books
FROM reader r
LEFT JOIN bookborrow br ON r.readerid = br.readerid
LEFT JOIN bookstorage bs ON br.bookbarcode = bs.bookbarcode
GROUP BY r.readername;

这个查询统计了每个读者借阅的图书数量和归还的图书数量，使用了条件聚合来计算归还的书籍数量。

3. 查询某本书的借阅历史，包括每次借阅的读者和借阅时间

SELECT b.bookname, r.readername, br.borrowtime
FROM bookstorage bs
JOIN bookborrow br ON bs.bookbarcode = br.bookbarcode
JOIN book b ON bs.bookid = b.bookid
JOIN reader r ON br.readerid = r.readerid
WHERE b.bookname = 'Java程序设计';

这个查询展示了某本书的所有借阅记录，包括每次借阅的读者和借阅时间。

4. 查询当前借阅次数最多的读者

SELECT r.readername, COUNT(br.borrowid) AS borrow_count
FROM reader r
JOIN bookborrow br ON r.readerid = br.readerid
GROUP BY r.readername
ORDER BY borrow_count DESC
LIMIT 1;

这个查询找到了借阅次数最多的读者，按借阅次数降序排列并限制结果为1条。

5