学习数据库，看这一篇就够了！

city 不 city

已于 2024-10-10 11:23:13 修改

阅读量884

点赞数 23

文章标签：学习

于 2024-09-18 12:36:22 首次发布

本文链接：https://blog.csdn.net/m0_74750121/article/details/142320434

版权

实验一图书管理系统的数据库搭建

实验内容是设计一个简单的图书管理数据库系统。

书籍收藏
师生信息
借阅信息
管理员和借阅读者
管理员：增删查改、借阅功能
读者：借阅、续借、归还、预约确认等操作

第一部分

mysql数据库的网络服务启动是有两种启动方式的。

第一种是通过任务管理器中打开服务进程

第二种是进去管理员模式下的cmd，输入对应的命令启动网络服务

网络服务启动有两种，同样的使用数据库也有两种方法，比如可以在cmd里创建、删除等操作，但是更常用的是在MySQLworkbench里使用。

net stop mysql80

mysql -u root -p

net start mysql80

mysql -u root -p

create database student_test;

show databases;

drop database student_test;

show databases;

exit

CREATE TABLE dbLibrary.books (
    book_id INT AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    publisher VARCHAR(255) NOT NULL,
    published_date DATE,
    genre VARCHAR(100),
    price DECIMAL(10, 2),
    PRIMARY KEY (book_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO dbLibrary.books (title, author, publisher, published_date, genre, price)
VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 'Scribner', '1925-04-10', 'Fiction', 10.99),
('To Kill a Mockingbird', 'Harper Lee', 'J.B. Lippincott & Co.', '1960-07-11', 'Fiction', 7.99),
('1984', 'George Orwell', 'Secker & Warburg', '1949-06-08', 'Dystopian', 8.99),
('Pride and Prejudice', 'Jane Austen', 'T. Egerton', '1813-01-28', 'Romance', 6.50),
('The Catcher in the Rye', 'J.D. Salinger', 'Little, Brown and Company', '1951-07-16', 'Fiction', 9.99);

select *from dblibrary.books;

疑惑点

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

这些是什么，为什么要在创建数据表后面写这些？

这些是在创建 MySQL 数据表时指定的参数，用于设置表的存储引擎、默认字符集和排序规则。它们的作用如下：

ENGINE=InnoDB：
- InnoDB 是 MySQL 的一种存储引擎，支持事务处理、外键约束、崩溃恢复等高级功能。
- 指定存储引擎可以决定数据在磁盘上的存储方式，以及支持的功能特性。
- 如果不指定，MySQL 会使用默认的存储引擎（通常也是 InnoDB，但可能因配置而异）。
DEFAULT CHARSET=utf8mb4：
- utf8mb4 是一种字符集，支持完整的 Unicode，包括表情符号和其他特殊字符。
- 指定默认字符集可以确保存储和检索数据时的编码一致，避免乱码或数据丢失。
- 如果不指定，MySQL 会使用数据库或服务器的默认字符集，可能无法满足特殊字符的存储需求。
COLLATE=utf8mb4_bin：
- COLLATE（排序规则）定义了字符串比较和排序的方式。
- utf8mb4_bin 表示使用二进制比较，区分大小写和重音符号，比较严格。
- 指定排序规则可以影响 ORDER BY、GROUP BY 和字符串比较等操作的结果，确保符合业务需求。

总结：在创建数据表时指定这些参数，可以精确控制表的行为，确保数据的存储、检索和比较方式符合预期，从而避免潜在的问题。

实验二数据库表的创建及完整性

第一部分

创建如下9个数据表，并插入数据。

表1：读者信息

#创建读者信息表
CREATE TABLE Reader (
    ReaderID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    Phone VARCHAR(20),
    Email VARCHAR(100),
    Department VARCHAR(100),
    Status VARCHAR(20),
    ReaderType INT,
    Note VARCHAR(255)
);

属性：读者编号（主键），读者姓名，联系电话，邮箱地址，所在系，权限状况，读者类型，备注

数据：15条数据

-- 插入示例数据
INSERT INTO Reader (Name, Phone, Email, Department, Status, ReaderType, Note) VALUES
('张三', '12345678901', 'zhangsan@example.com', '计算机科学', '正常', 1, ''),
('李四', '12345678902', 'lisi@example.com', '经济学', '正常', 2, ''),
('王五', '12345678903', 'wangwu@example.com', '物理系', '正常', 1, ''),
('赵六', '12345678904', 'zhaoliu@example.com', '化学系', '正常', 1, ''),
('孙七', '12345678905', 'sunqi@example.com', '生物科学', '正常', 2, ''),
('周八', '12345678906', 'zhouba@example.com', '材料科学', '正常', 1, ''),
('吴九', '12345678907', 'wujiu@example.com', '环境科学', '正常', 1, ''),
('郑十', '12345678908', 'zhengshi@example.com', '机械工程', '正常', 2, ''),
('王十一', '12345678909', 'wangshiyi@example.com', '电子工程', '正常', 1, ''),
('李十二', '12345678910', 'lishier@example.com', '计算机工程', '正常', 1, ''),
('张十三', '12345678911', 'zhangshisan@example.com', '数学系', '正常', 2, ''),
('赵十四', '12345678912', 'zhaoshi@example.com', '统计学', '正常', 1, ''),
('钱十五', '12345678913', 'qianshiwu@example.com', '哲学系', '正常', 1, ''),
('孙十六', '12345678914', 'sunshiliu@example.com', '法学系', '正常', 2, ''),
('周十七', '12345678915', 'zhoushiqi@example.com', '历史系', '正常', 1, '');

表2：书籍信息

#书籍信息表及数据插入
CREATE TABLE Book (
    BookID INT PRIMARY KEY AUTO_INCREMENT,
    ISBN VARCHAR(20),
    Title VARCHAR(100),
    Author VARCHAR(100),
    Publisher VARCHAR(100),
    PublishDate DATE,
    Summary TEXT,
    CoverImage BLOB,
    Price DECIMAL(10, 2),
    SubjectType INT,
    StoreType INT
);

属性：图书编号（主键），ISBN，书名，作者，出版社，出版日期，简介，封面图片，价格，学科类型，藏书类型

数据：30条数据

-- 插入示例数据
INSERT INTO Book (ISBN, Title, Author, Publisher, PublishDate, Summary, Price, SubjectType, StoreType) VALUES
('978-6-16-148410-3', '化学基础', '赵六', '科技出版社', '2019-08-05', '基本的化学原理和实验方法。', 40.00, 4, 2),
('978-7-16-148410-4', '生物科学入门', '孙七', '教育出版社', '2018-12-12', '生物科学的基础知识和前沿技术。', 55.00, 5, 3),
('978-8-16-148410-5', '材料科学概论', '周八', '工业出版社', '2020-07-01', '介绍现代材料科学及其应用。', 70.00, 6, 4),
('978-9-16-148410-6', '环境科学导论', '吴九', '环境科学出版社', '2021-02-15', '环境问题与科学技术的解决方案。', 48.00, 7, 1),
('978-10-16-148410-7', '机械工程基础', '郑十', '工业技术出版社', '2019-11-23', '机械工程的基本理论与实践应用。', 62.00, 8, 2),
('978-11-16-148410-8', '电子工程入门', '王十一', '电子工业出版社', '2021-05-14', '电子工程的基本概念和技术应用。', 80.00, 9, 3),
('978-12-16-148410-9', '计算机网络', '李十二', '通信出版社', '2020-03-18', '计算机网络的原理与维护。', 45.00, 1, 4),
('978-13-16-148410-10', '高级数学', '张十三', '教育出版社', '2022-01-10', '高等教育中的数学分析与几何。', 55.00, 2, 1),
('978-14-16-148410-11', '统计学基础', '赵十四', '学术出版社', '2021-07-22', '统计方法和应用实例。', 49.95, 3, 2),
('978-15-16-148410-12', '哲学导论', '钱十五', '文科出版社', '2018-10-30', '哲学的基本问题与思考方式。', 33.00, 4, 3),
('978-16-16-148410-13', '世界历史概览', '孙十六', '历史出版社', '2019-09-19', '全球历史的重要事件和影响。', 75.00, 5, 4),
('978-17-16-148410-14', '现代法学', '周十七', '法律出版社', '2020-04-25', '现代法律框架和案例研究。', 60.00, 6, 1),
('978-18-16-148410-15', '古代文明研究', '周八', '文化出版社', '2021-06-11', '探索古代文明的起源和发展。', 68.00, 7, 2),
('978-19-16-148410-16', '量子物理入门', '吴九', '科学出版社', '2022-02-20', '量子物理的基础理论和实验。', 90.00, 8, 3),
('978-20-16-148410-17', '经济全球化', '郑十', '经济学出版社', '2021-08-05', '全球经济一体化的历史与未来。', 50.00, 9, 4),
('978-21-16-148410-18', '人工智能基础', '王十一', '技术出版社', '2022-05-09', '人工智能技术的基础和应用。', 95.00, 1, 1),
('978-22-16-148410-19', '现代建筑艺术', '李十二', '建筑出版社', '2018-12-15', '现代建筑设计与艺术表达。', 85.00, 2, 2),
('978-23-16-148410-20', '新时代教育技术', '张十三', '教育技术出版社', '2019-07-07', '教育技术的发展趋势与实践。', 45.00, 3, 3),
('978-24-16-148410-21', '信息安全基础', '赵十四', '安全技术出版社', '2020-09-12', '信息安全的理论与实务。', 78.00, 4, 4),
('978-25-16-148410-22', '现代心理学', '钱十五', '心理学出版社', '2021-10-17', '心理学理论与临床研究。', 64.00, 5, 1);

表3：管理员信息

CREATE TABLE admin1 (
    AdminID INT PRIMARY KEY AUTO_INCREMENT,
    Username VARCHAR(50),
    Password VARCHAR(50)
);

属性：编号（主键），账号，密码

数据：5条数据

-- 插入示例数据
INSERT INTO admin1 (Username, Password) VALUES
('admin1', 'password123'),
('admin2', 'password234'),
('admin3', 'password345'),
('admin4', 'password456'),
('admin5', 'password567');

表4：读者类型

#读者类型表及数据插入
-- 创建读者类型表
CREATE TABLE ReaderType (
    TypeID INT PRIMARY KEY AUTO_INCREMENT,
    Type VARCHAR(50),
    Description VARCHAR(255)
);

属性：编号（主键），类型，说明

数据：3种

-- 插入示例数据
INSERT INTO ReaderType (Type, Description) VALUES
('学生', '普通在校学生，可以借阅一般藏书类型'),
('教师', '学校教师，可以借阅学术类及一般藏书类型'),
('外部人员', '非学校人员，可以借阅部分藏书类型');

表5：藏书类型

#藏书类型表及数据插入
-- 创建藏书类型表
CREATE TABLE StoreType (
    TypeID INT PRIMARY KEY AUTO_INCREMENT,
    Type VARCHAR(50),
    Description VARCHAR(255)
);

属性：编号（主键），类型，说明

数据：4种不同类型的藏书类型

-- 插入示例数据
INSERT INTO StoreType (Type, Description) VALUES
('普通图书', '普通图书类别，所有读者均可借阅'),
('期刊', '学术期刊和杂志，仅限部分读者借阅'),
('参考书', '不可外借的参考书，仅供馆内使用'),
('电子资源', '电子版资源和多媒体资料，可在线访问');

表6：学科类型

#创建学科类型表
CREATE TABLE SubjectType (
    TypeID INT PRIMARY KEY AUTO_INCREMENT,
    Type VARCHAR(50),
    Description VARCHAR(255)
);

属性：编号（主键），类型，说明

数据：6种不同的学科类型

-- 插入学科类型数据，确保在书籍信息表中都有体现
INSERT INTO SubjectType (Type, Description) VALUES
('计算机科学', '研究计算机和算法的科学。'),
('经济学', '研究经济活动及其规律的社会科学。'),
('物理学', '研究物质、能量及其相互作用的自然科学。'),
('化学', '研究物质的组成、性质及变化规律的自然科学。'),
('生物学', '研究生命及生命现象的科学。'),
('机械工程', '研究机械设计、制造、运行的工程学科。'),
('环境科学', '研究环境问题及解决措施的综合学科。'),
('电子工程', '研究电子系统和电路设计的工程学科。'),
('人工智能', '研究智能机器的原理、设计和应用。');

表7：借阅信息

#创建借阅信息表
-- 创建借阅信息表
CREATE TABLE Borrowing (
    BookID INT,
    ReaderID INT,
    BorrowDate DATE,
    ReturnDate DATE,
    PRIMARY KEY (BookID, ReaderID)
);

属性：图书编号（主键），读者编号（主键），借阅日期，应还日期

数据：50条数据，其中15个读者

-- 插入借阅信息数据，确保至少有15个不同的读者和50条以上记录
INSERT INTO Borrowing (BookID, ReaderID, BorrowDate, ReturnDate) VALUES
(1, 1, '2023-01-05', '2023-01-20'),
(2, 2, '2023-01-06', '2023-01-21'),
(3, 3, '2023-01-07', '2023-01-22'),
(4, 4, '2023-01-08', '2023-01-23'),
(5, 5, '2023-01-09', '2023-01-24'),
(6, 6, '2023-01-10', '2023-01-25'),
(7, 7, '2023-01-11', '2023-01-26'),
(8, 8, '2023-01-12', '2023-01-27'),
(9, 9, '2023-01-13', '2023-01-28'),
(10, 10, '2023-01-14', '2023-01-29'),
(11, 11, '2023-01-15', '2023-01-30'),
(12, 12, '2023-01-16', '2023-01-31'),
(13, 13, '2023-01-17', '2023-02-01'),
(14, 14, '2023-01-18', '2023-02-02'),
(15, 15, '2023-01-19', '2023-02-03'),
(16, 1, '2023-01-20', '2023-02-04'),
(17, 2, '2023-01-21', '2023-02-05'),
(18, 3, '2023-01-22', '2023-02-06'),
(19, 4, '2023-01-23', '2023-02-07'),
(20, 5, '2023-01-24', '2023-02-08'),
(21, 6, '2023-01-25', '2023-02-09'),
(22, 7, '2023-01-26', '2023-02-10'),
(23, 8, '2023-01-27', '2023-02-11'),
(24, 9, '2023-01-28', '2023-02-12'),
(25, 10, '2023-01-29', '2023-02-13'),
(1, 11, '2023-02-01', '2023-02-14'),
(2, 12, '2023-02-02', '2023-02-15'),
(3, 13, '2023-02-03', '2023-02-16'),
(4, 14, '2023-02-04', '2023-02-17'),
(5, 15, '2023-02-05', '2023-02-18'),
(6, 1, '2023-02-06', '2023-02-19'),
(7, 2, '2023-02-07', '2023-02-20'),
(8, 3, '2023-02-08', '2023-02-21'),
(9, 4, '2023-02-09', '2023-02-22'),
(10, 5, '2023-02-10', '2023-02-23'),
(11, 6, '2023-02-11', '2023-02-24'),
(12, 7, '2023-02-12', '2023-02-25'),
(13, 8, '2023-02-13', '2023-02-26'),
(14, 9, '2023-02-14', '2023-02-27'),
(15, 10, '2023-02-15', '2023-02-28'),
(16, 11, '2023-02-16', '2023-03-01'),
(17, 12, '2023-02-17', '2023-03-02'),
(18, 13, '2023-02-18', '2023-03-03'),
(19, 14, '2023-02-19', '2023-03-04'),
(20, 15, '2023-02-20', '2023-03-05'),
(21, 1, '2023-02-21', '2023-03-06'),
(22, 2, '2023-02-22', '2023-03-07'),
(23, 3, '2023-02-23', '2023-03-08'),
(24, 4, '2023-02-24', '2023-03-09'),
(25, 5, '2023-02-25', '2023-03-10');

表8：预约信息

#创建预约信息表
-- 创建预约信息表
CREATE TABLE Reservation (
    BookID INT,
    ReaderID INT,
    ReserveDate DATE,
    PRIMARY KEY (BookID, ReaderID)
);

属性：图书编号（主键），读者编号（主键），预约日期

数据：20条数据

-- 插入预约信息数据，确保至少有20条记录
INSERT INTO Reservation (BookID, ReaderID, ReserveDate) VALUES
(1, 1, '2023-01-01'),
(2, 2, '2023-01-02'),
(3, 3, '2023-01-03'),
(4, 4, '2023-01-04'),
(5, 5, '2023-01-05'),
(6, 6, '2023-01-06'),
(7, 7, '2023-01-07'),
(8, 8, '2023-01-08'),
(9, 9, '2023-01-09'),
(10, 10, '2023-01-10'),
(11, 11, '2023-01-11'),
(12, 12, '2023-01-12'),
(13, 13, '2023-01-13'),
(14, 14, '2023-01-14'),
(15, 15, '2023-01-15'),
(16, 1, '2023-01-16'),
(17, 2, '2023-01-17'),
(18, 3, '2023-01-18'),
(19, 4, '2023-01-19'),
(20, 5, '2023-01-20'),
(21, 6, '2023-01-21'),
(22, 7, '2023-01-22'),
(23, 8, '2023-01-23'),
(24, 9, '2023-01-24'),
(25, 10, '2023-01-25');

表9：借阅规则

#创建借阅规则表
-- 创建借阅规则表
CREATE TABLE LendingRule (
    BookType INT,
    ReaderType INT,
    Period INT,          -- 借阅期限（天）
    Quantity INT,        -- 允许借阅册数
    RenewalTimes INT,    -- 允许续借次数
    OverdueFine DECIMAL(10, 2),  -- 逾期罚款（元）
    PRIMARY KEY (BookType, ReaderType)
);

属性：图书类型（主键），读书类型（主键），期限，册数，续借次数，逾期罚款

数据：3种不同的借阅规则

-- 插入借阅规则数据，确保至少包含3种不同的规则
INSERT INTO LendingRule (BookType, ReaderType, Period, Quantity, RenewalTimes, OverdueFine) VALUES
(1, 1, 30, 5, 2, 0.50),   -- 普通图书，学生，借阅期限30天，最多借5册，可续借2次，逾期罚款0.50元/天
(2, 2, 60, 10, 3, 0.25),  -- 期刊，教师，借阅期限60天，最多借10册，可续借3次，逾期罚款0.25元/天
(3, 3, 15, 2, 1, 1.00),   -- 参考书，外部人员，借阅期限15天，最多借2册，可续借1次，逾期罚款1.00元/天
(4, 1, 20, 3, 2, 0.75),   -- 电子资源，学生，借阅期限20天，最多借3册，可续借2次，逾期罚款0.75元/天
(1, 2, 40, 7, 3, 0.50);   -- 普通图书，教师，借阅期限40天，最多借7册，可续借3次，逾期罚款0.50元/天

第二部分

创建数据库的自增序列

在上面创建表的主键里，选择以下的6个序列中一个，用命令行的方式创建表时创建以恶搞自动增长的主键序列：读者编号，图书编号，读者类型编号，藏书类型编号，学科编号，管理员编号

DROP TABLE IF EXISTS Reader;
#1. 读者编号（ReaderID）自增序列
-- 创建读者信息表，并设置读者编号为自增主键
CREATE TABLE Reader (
    ReaderID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    Phone VARCHAR(20),
    Email VARCHAR(100),
    Department VARCHAR(100),
    Status VARCHAR(20),
    ReaderType INT,
    Note VARCHAR(255)
);

修改数据库的表

要求一

随机选择其中一个表，描述一个现实中的业务，在已有数据的表上，用命令行的方式多增加一个字段

#3.1
-- 业务场景：
-- 在 Reader 表新增 RegisterDate 字段，记录读者注册时间。

ALTER TABLE Reader ADD COLUMN RegisterDate DATETIME;

要求二

在3.1新增加的字段上，描述一个业务场景，添加该字段的默认值

#3.2
-- 业务场景：
-- 为了在新读者注册时自动记录注册时间，将 RegisterDate 字段的类型修改为 DATETIME，
-- 并设置默认值为当前时间，这样可以自动记录读者注册的具体时间，方便数据分析。

-- 修改 RegisterDate 字段为 DATETIME 并设置默认值为当前时间
ALTER TABLE Reader MODIFY COLUMN RegisterDate DATETIME DEFAULT CURRENT_TIMESTAMP;

修改数据库的表数据

描述一个现实的业务场景，对上述随意一个表的数据进行数据更新

#4. 修改数据库的表数据
#4.1 业务场景描述及数据更新
-- 业务场景：
-- 管理员发现读者张三的联系方式已更换，需要更新其电话和邮箱信息。
-- 更新 Reader 表中 ReaderID 为 1 的记录，修改其 Phone 和 Email 字段。

-- 更新读者信息
UPDATE Reader
SET Phone = '13888888888',       -- 更新电话
    Email = 'zhangsan_new@example.com'  -- 更新邮箱
WHERE ReaderID = 1;               -- 目标读者的 ReaderID

删除数据库的表数据

描述一个现实的业务场景，对上述随意一个表的数据，删除其中一条数据

#5. 删除数据库的表数据
#5.1 业务场景描述及数据删除
-- 业务场景：
-- 读者李四已毕业，不再使用图书馆的服务。为了保持数据的清洁，管理员决定删除李四的记录。
-- 使用 ReaderID 定位李四的记录，并执行删除操作。

-- 删除读者李四的信息
DELETE FROM Reader
WHERE ReaderID = 2;  -- 根据 ReaderID 精确定位需要删除的记录

删除数据库的表

要求一

描述一个现实中的业务场景，在已有数据表上多增加一个新的表来记录信息

#6. 删除数据库的表
#6.1 业务场景描述及创建新表
-- 业务场景：
-- 图书管理系统需要记录特殊事件（如图书丢失、损坏、读者违规）的信息。
-- 为此，创建一个 EventLog 表，用于记录这些事件，便于后续处理和跟踪。

-- 创建 EventLog 表
CREATE TABLE EventLog (
    EventID INT PRIMARY KEY AUTO_INCREMENT,  -- 事件编号，自增主键
    EventType VARCHAR(50),                   -- 事件类型，例如“图书丢失”、“读者违规”
    EventDate DATE,                          -- 事件发生日期
    Description TEXT                         -- 事件的详细描述
);

-- 插入示例数据
INSERT INTO EventLog (EventType, EventDate, Description) VALUES
('图书丢失', '2023-09-01', '读者张三报告图书《计算机科学导论》丢失'),
('读者违规', '2023-09-05', '读者李四未按时归还图书，已超期30天'),
('图书损坏', '2023-09-10', '图书《物理学基础》封面损坏，需要修复');

要求二

删除6.1所建立的新表

#6.2 删除 6.1 所建立的新表
-- 业务场景：
-- 由于系统设计的调整，EventLog 表已经不再需要，或者信息需要合并到其他表中。
-- 为了保持数据库结构的整洁和优化，需要删除 EventLog 表。

-- 删除 EventLog 表
DROP TABLE IF EXISTS EventLog;

疑惑点

1.

为什么表7种有两个主键？

"图书编号"和"读者编号"都被指定为主键，这是因为它们组成了一个复合主键（Composite Primary Key）。也就是说，这两个字段的组合共同唯一地标识了表中的每一条记录。

2.

在3.2中的后面这个DEFAULT CURRENT_TIMESTAMP;是什么意思？

在 SQL 中，`DEFAULT CURRENT_TIMESTAMP` 是一个约束，用于设置默认值。这意味着如果在插入数据时没有为 `RegisterDate` 字段指定一个值，数据库会自动使用当前的日期和时间来填充这个字段。

`CURRENT_TIMESTAMP` 是一个 SQL 函数，返回当前的日期和时间，格式通常为 `YYYY-MM-DD HH:MM:SS`。这对于记录数据创建或注册的时间非常有用。在你的代码中，当向 `Reader` 表添加一个新的行但没有指定 `RegisterDate` 的值时，`RegisterDate` 将自动设置为该记录被插入时的日期和时间。

3.

varchar(100)，到底多少才合适？但是这个是可变字符串！

在设定简介的数据类型的时候，应该设为text更好。

varchar和text有什么区别？

`VARCHAR` 和 `TEXT` 都用于存储字符串数据，但 `VARCHAR` 需要指定最大长度并且在存储空间和性能上更高效，适合存储长度可预测的数据，如用户姓名。相反，`TEXT` 适用于存储长度不确定的大型文本，如博客文章或用户评论，因为它不需要指定最大长度，可以存储更大的数据量。例如，如果一个字段用于保存用户的电子邮件地址，可以选择 `VARCHAR(255)`；而用于保存用户在论坛上的发帖，则更适合使用 `TEXT` 类型。

上面建立的很多哥表没有设定外键约束，比如表1 读者信息

外键用于表之间的关联，确保数据的一致性和完整性。

如果你的表设计需要引用其他表的数据，你可以添加外键约束来实现这一点。

例如，如果 ReaderType 字段应该引用另一个表中的类型，你可以通过添加类似下面的外键约束来实现：

ALTER TABLE Reader
ADD CONSTRAINT FK_ReaderType
FOREIGN KEY (ReaderType) REFERENCES AnotherTable(TypeID);

这里，AnotherTable 和 TypeID 需要替换为实际的表名和列名。这样，ReaderType 就会确保只包含在 AnotherTable 的 TypeID 列中已存在的值。

代码汇总

-- 使用或切换到 'dblibrary' 数据库
USE dblibrary;

-- 首先删除现有的表，确保没有依赖关系导致错误
DROP TABLE IF EXISTS Book_Fine_Records;
DROP TABLE IF EXISTS Reservation;
DROP TABLE IF EXISTS Borrowing;
DROP TABLE IF EXISTS Book;
DROP TABLE IF EXISTS Reader;
DROP TABLE IF EXISTS LendingRule;
DROP TABLE IF EXISTS admin1;
DROP TABLE IF EXISTS Department;
DROP TABLE IF EXISTS ReaderType;
DROP TABLE IF EXISTS StoreType;
DROP TABLE IF EXISTS SubjectType;

-- 使用或切换到 'dblibrary' 数据库
USE dblibrary;

-- 创建系别信息表
CREATE TABLE Department (
    DepartmentID INT PRIMARY KEY AUTO_INCREMENT,
    DepartmentName VARCHAR(100)
);

-- 创建读者类型表
CREATE TABLE ReaderType (
    TypeID INT PRIMARY KEY AUTO_INCREMENT,
    Type VARCHAR(50) UNIQUE NOT NULL,
    Description TEXT
);

-- 创建藏书类型表
CREATE TABLE StoreType (
    TypeID INT PRIMARY KEY AUTO_INCREMENT,
    Type VARCHAR(50) UNIQUE NOT NULL,
    Description VARCHAR(255)
);

-- 创建学科类型表
CREATE TABLE SubjectType (
    TypeID INT PRIMARY KEY AUTO_INCREMENT,
    Type VARCHAR(50) UNIQUE NOT NULL,
    Description VARCHAR(255)
);

-- 创建读者信息表
CREATE TABLE Reader (
    ReaderID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    Phone VARCHAR(20),
    Email VARCHAR(100),
    DepartmentID INT,
    Status CHAR(3) CHECK (Status IN ('000', '001', '100', '101', '110', '111')),
    Type INT,
    Note TEXT,
    FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID),
    FOREIGN KEY (Type) REFERENCES ReaderType(TypeID)
);

-- 创建书籍信息表
CREATE TABLE Book (
    BookID INT PRIMARY KEY AUTO_INCREMENT,
    ISBN VARCHAR(20) UNIQUE NOT NULL,
    Title VARCHAR(100),
    Author VARCHAR(100),
    Publisher VARCHAR(100),
    PublishDate DATE,
    Summary TEXT,
    CoverImage VARCHAR(255),
    Price DECIMAL(10, 2),
    BookSubjectType INT,
    BookStoreType INT,
    FOREIGN KEY (BookSubjectType) REFERENCES SubjectType(TypeID),
    FOREIGN KEY (BookStoreType) REFERENCES StoreType(TypeID)
);

-- 创建借阅信息表
CREATE TABLE Borrowing (
    BorrowingID INT PRIMARY KEY AUTO_INCREMENT,
    BookID INT NOT NULL,
    ReaderID INT NOT NULL,
    BorrowDate DATE NOT NULL,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (ReaderID) REFERENCES Reader(ReaderID),
    CHECK (ReturnDate >= BorrowDate)
);

-- 添加 Borrowing 表的唯一索引
ALTER TABLE Borrowing ADD UNIQUE INDEX idx_borrowing_unique (BookID, ReaderID, BorrowDate);

-- 创建预约信息表
CREATE TABLE Reservation (
    BookID INT NOT NULL,
    ReaderID INT NOT NULL,
    ReserveDate DATE NOT NULL,
    PRIMARY KEY (BookID, ReaderID, ReserveDate),
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (ReaderID) REFERENCES Reader(ReaderID)
);

-- 创建借阅规则表
CREATE TABLE LendingRule (
    RuleID INT PRIMARY KEY AUTO_INCREMENT,
    BookStoreType INT NOT NULL,
    ReaderType INT NOT NULL,
    Period INT CHECK (Period BETWEEN 1 AND 90),
    Quantity INT CHECK (Quantity BETWEEN 1 AND 10),
    RenewalTimes INT CHECK (RenewalTimes BETWEEN 1 AND 5),
    OverdueFine DECIMAL(10,2) CHECK (OverdueFine >= 0),
		Note TEXT,
    FOREIGN KEY (BookStoreType) REFERENCES StoreType(TypeID),
    FOREIGN KEY (ReaderType) REFERENCES ReaderType(TypeID)
);

-- 创建管理员信息表
CREATE TABLE admin1 (
    AdminID INT PRIMARY KEY AUTO_INCREMENT,
    Username VARCHAR(100) UNIQUE NOT NULL,
    AdminPassword VARCHAR(30) UNIQUE NOT NULL
);

-- 创建图书罚款记录表
CREATE TABLE Book_Fine_Records (
    fineid INT PRIMARY KEY AUTO_INCREMENT,
    bookid INT NOT NULL,
    readerid INT NOT NULL,
    borrowdate DATE NOT NULL,
    returndate DATE,
    fineamount DECIMAL(6,2) NOT NULL,
    paymentstatus BOOLEAN,
    FOREIGN KEY (bookid, readerid, borrowdate) REFERENCES Borrowing(BookID, ReaderID, BorrowDate)
);







-- 插入部门信息
INSERT INTO Department (DepartmentName) VALUES
('计算机科学与技术'),
('数据科学与大数据技术'),
('物理系'),
('化学系'),
('生物科学'),
('材料科学'),
('环境科学'),
('机械工程'),
('电子工程'),
('通信工程'),
('数学系'),
('统计学'),
('哲学系'),
('法学系'),
('历史系');

-- 插入读者类型数据
INSERT INTO ReaderType (Type, Description) VALUES
('学生', '普通在校学生，可以借阅一般藏书类型'),
('教师', '学校教师，可以借阅学术类及一般藏书类型'),
('外部人员', '非学校人员，可以借阅部分藏书类型');

-- 插入藏书类型数据
INSERT INTO StoreType (Type, Description) VALUES
('普通图书', '普通图书类别，所有读者均可借阅'),
('期刊', '学术期刊和杂志，仅限部分读者借阅'),
('参考书', '不可外借的参考书，仅供学校内人员使用'),
('电子资源', '电子版资源和多媒体资料，可在线访问');

-- 插入学科类型数据
INSERT INTO SubjectType (Type, Description) VALUES
('计算机科学与技术', '研究计算机系统、算法、硬件与软件设计的学科。'),
('数据科学与大数据技术', '专注于数据分析、数据挖掘和大数据处理，提取有价值的信息。'),
('物理系', '研究物质、能量及其相互作用，涵盖量子力学到宇宙学的广泛领域。'),
('化学系', '探讨物质的结构、组成、性质及变化规律，涵盖有机、无机和物理化学等分支。'),
('生物科学', '研究生命现象及生物体的结构、功能和进化，从微生物到生态系统。'),
('材料科学', '研究材料的性质、结构与应用，涉及金属、陶瓷、聚合物等。'),
('环境科学', '研究人与自然环境之间的相互作用，解决全球环境问题。'),
('机械工程', '研究机械系统的设计、制造与运作，涉及动力系统、自动化等。'),
('电子工程', '研究电子设备和电路的设计与应用，涵盖通信和嵌入式系统等。'),
('通信工程', '专注于信息传输与处理，涉及无线通信、网络技术等领域。'),
('数学系', '研究数学理论及其应用，涵盖纯数学与应用数学的多个分支。'),
('统计学', '研究数据的收集、分析、解释与表示，广泛应用于多个领域。'),
('哲学系', '探讨存在、知识、伦理等基本问题，培养批判性思维与逻辑推理。'),
('法学系', '研究法律制度及其应用，涵盖民法、刑法等多个法律领域。'),
('历史系', '研究人类历史事件、人物及社会变迁，分析过去理解未来。');

-- 插入读者信息
INSERT INTO Reader (Name, Phone, Email, DepartmentID, Status, Type, Note) VALUES
('Liu Wei', '18612345678', 'liuwei@example.com', 1, '000', 1, ''),
('Zhang Yu', '18598765432', 'zhangyu@example.com', 2, '001', 2, ''),
('Wang Ting', '13676543210', 'wangting@example.com', 3, '100', 1, ''),
('Chen Min', '18887654321', 'chenmin@example.com', 4, '110', 1, ''),
('Zhao Liang', '13723456789', 'zhaoliang@example.com', 5, '100', 2, ''),
('Sun Lei', '18923456780', 'sunlei@example.com', 6, '101', 1, ''),
('He Fei', '13876543219', 'hefei@example.com', 7, '110', 1, ''),
('Xue Yuan', '13709876543', 'xueyuan@example.com', 8, '111', 2, ''),
('Li Qiang', '18776543211', 'liqiang@example.com', NULL, '000', 3, ''),
('Tang Yu', '13912345679', 'tangyu@example.com', 10, '001', 1, ''),
('Yao Ling', '13687654322', 'yaoling@example.com', 11, '001', 2, ''),
('Gao Hui', '18912345677', 'gaohui@example.com', NULL, '101', 3, ''),
('Fang Jie', '13876543218', 'fangjie@example.com', 13, '100', 1, ''),
('Luo Jie', '13723456788', 'luojie@example.com', 14, '001', 2, ''),
('Wang Yi', '13398760439', 'wangyi@example.com', 15, '001', 2, ''),
('An Ling', '15606513219', 'anling@example.com', 6, '000', 1, ''),
('Chen Yun', '18987604127', 'chenyun@example.com', 7, '001', 1, ''),
('Zhuang Li', '18720426182', 'zhuangli@example.com', 8, '100', 2, ''),
('Han Mei', '18623456787', 'hanmei@example.com', 9, '110', 1, ''),
('Liu Fang', '13876543217', 'liufang@example.com', 10, '111', 2, ''),
('Qian Hua', '18634567891', 'qianhua@example.com', 11, '000', 1, ''),
('Zheng Kai', '18987654320', 'zhengkai@example.com', 12, '001', 2, ''),
('Yin Yu', '18734567890', 'yinyu@example.com', 13, '001', 1, ''),
('Mao Shan', '15698765432', 'maoshan@example.com', 14, '011', 1, ''),
('Duan Ming', '13865432109', 'duanming@example.com', 15, '100', 2, '');

-- 插入书籍信息
INSERT INTO Book (ISBN, Title, Author, Publisher, PublishDate, Summary, Price, BookSubjectType, BookStoreType) VALUES
('978-6-16-148410-3', '化学基础', '赵六', '科技出版社', '2019-08-05', '基本的化学原理和实验方法。', 140.00, 4, 2),
('978-7-16-148410-4', '生物科学入门', '孙七', '教育出版社', '2018-12-12', '生物科学的基础知识和前沿技术。', 55.00, 5, 3),
('978-8-16-148410-5', '材料科学概论', '周八', '工业出版社', '2020-07-01', '介绍现代材料科学及其应用。', 70.00, 6, 4),
('978-9-16-148410-6', '环境科学导论', '吴九', '环境科学出版社', '2021-02-15', '环境问题与科学技术的解决方案。', 48.00, 7, 1),
('978-10-16-148410-7', '机械工程基础', '郑十', '工业技术出版社', '2019-11-23', '机械工程的基本理论与实践应用。', 62.00, 8, 2),
('978-11-16-148410-8', '电子工程入门', '王十一', '电子工业出版社', '2021-05-14', '电子工程的基本概念和技术应用。', 80.00, 9, 3),
('978-12-16-148410-9', '计算机网络', '李十二', '通信出版社', '2020-03-18', '计算机网络的原理与维护。', 45.00, 1, 4),
('978-13-16-148410-10', '高等数学', '张十三', '教育出版社', '2022-01-10', '高等教育中的数学分析与几何。', 55.00, 2, 1),
('978-14-16-148410-11', '统计学基础', '赵十四', '学术出版社', '2021-07-22', '统计方法和应用实例。', 149.95, 12, 2),
('978-15-16-148410-12', '哲学导论', '钱十五', '文科出版社', '2018-10-30', '哲学的基本问题与思考方式。', 133.00, 13, 3),
('978-16-16-148410-13', '世界历史概览', '孙十六', '历史出版社', '2019-09-19', '全球历史的重要事件和影响。', 75.00, 15, 4),
('978-17-16-148410-14', '现代法学', '周十七', '法律出版社', '2020-04-25', '现代法律框架和案例研究。', 60.00, 14, 1),
('978-18-16-148410-15', '古代文明研究', '周八', '文化出版社', '2021-06-11', '探索古代文明的起源和发展。', 68.00, 15, 2),
('978-19-16-148410-16', '量子物理入门', '吴九', '科学出版社', '2022-02-20', '量子物理的基础理论和实验。', 90.00, 13, 3),
('978-20-16-148410-17', '经济全球化', '郑十', '经济学出版社', '2021-08-05', '全球经济一体化的历史与未来。', 150.00, 3, 4),
('978-21-16-148410-18', '人工智能基础', '王十一', '科技出版社', '2022-05-09', '人工智能技术的基础和应用。', 95.00, 2, 1),
('978-22-16-148410-19', '现代建筑艺术', '李十二', '建筑出版社', '2018-12-15', '现代建筑设计与艺术表达。', 85.00, 6, 2),
('978-23-16-148410-20', '新时代教育技术', '张十三', '教育技术出版社', '2019-07-07', '教育技术的发展趋势与实践。', 145.00, 11, 3),
('978-24-16-148410-21', '信息安全基础', '赵十四', '安全技术出版社', '2020-09-12', '信息安全的理论与实务。', 78.00, 1, 4),
('978-25-16-148410-22', '现代心理学', '钱十五', '心理学出版社', '2021-10-17', '心理学理论与临床研究。', 64.00, 13, 1);

-- 插入管理员信息
INSERT INTO admin1 (Username, AdminPassword) VALUES
('admin1', 'password123'),
('admin2', 'password234'),
('admin3', 'password345'),
('admin4', 'password456'),
('admin5', 'password567');

-- 插入借阅信息
INSERT INTO Borrowing (BookID, ReaderID, BorrowDate, ReturnDate) VALUES
(1, 1, '2023-01-05', '2023-01-20'),
(3, 3, '2023-01-07', '2023-01-22'),
(5, 5, '2023-01-09', '2023-01-24'),
(7, 7, '2023-01-11', '2023-01-26'),
(9, 20, '2023-01-13', '2023-01-28'),
(10, 10, '2023-01-14', '2023-01-29'),
(11, 11, '2023-01-17', '2023-01-30'),
(14, 14, '2023-01-18', '2023-02-02'),
(20, 1, '2023-01-24', '2023-02-08'),
(3, 8, '2023-01-28', '2023-02-10'),
(5, 10, '2023-01-29', '2023-02-13'),
(6, 11, '2023-02-01', '2023-02-14'),
(8, 13, '2023-02-03', '2023-02-16'),
(11, 16, '2023-02-06', '2023-02-19'),
(13, 18, '2023-02-08', '2023-02-21'),
(14, 19, '2023-02-09', '2023-02-22'),
(15, 20, '2023-02-10', '2023-02-23'),
(16, 1, '2023-02-11', '2023-02-24'),
(17, 2, '2023-02-12', '2023-02-25'),
(18, 3, '2023-02-13', '2023-02-26'),
(20, 5, '2023-02-15', '2023-02-28'),
(1, 12, '2023-02-16', '2023-03-01'),
(4, 15, '2023-02-19', '2023-03-04'),
(5, 16, '2023-02-20', '2023-03-05'),
(7, 9, '2023-02-21', '2023-03-08'), 
(12, 7, '2023-02-21', '2023-03-23'), 
(3, 11, '2023-02-27', '2023-03-23'), 
(9, 23, '2023-02-27', '2023-03-26'), 
(19, 14, '2023-03-07', '2023-03-27'), 
(8, 10, '2023-03-08', '2023-04-05'), 
(15, 6, '2023-03-15', '2023-04-07'), 
(5, 1, '2023-03-16', '2023-04-15'), 
(17, 4, '2023-03-20', '2023-04-16'), 
(15, 6, '2023-04-26', '2023-05-21'), 
(10, 2, '2023-04-26', '2023-06-17'), 
(3, 8, '2023-05-22', '2023-06-20'), 
(1, 10, '2023-05-23', '2023-06-27'), 
(2, 11, '2023-05-23', '2023-07-12'), 
(4, 12, '2023-06-02', '2023-07-17'), 
(7, 14, '2023-06-25', '2023-07-18'), 
(20, 15, '2023-07-01', '2023-08-11'), 
(19, 17, '2023-07-22', '2023-08-14'), 
(8, 20, '2023-07-29', '2023-09-11'), 
(11, 23, '2023-08-15', '2023-09-02'), 
(14, 24, '2023-08-30', '2023-10-12'), 
(17, 22, '2023-09-01', '2023-12-03'), 
(14, 8, '2024-07-26', NULL), 
(6, 21, '2024-08-15', NULL), 
(16, 20, '2024-08-16', NULL), 
(4, 4, '2024-08-24', NULL), 
(12, 7, '2024-08-25', NULL), 
(17, 24, '2024-08-26', NULL), 
(15, 19, '2024-09-02', NULL), 
(1, 12, '2024-09-05', NULL);
;

-- 插入预约信息
INSERT INTO Reservation (BookID, ReaderID, ReserveDate) VALUES
(1, 1, '2023-01-01'),
(2, 3, '2023-01-02'),
(3, 2, '2023-01-03'),
(4, 7, '2023-01-04'),
(5, 10, '2023-01-05'),
(6, 6, '2023-01-06'),
(7, 4, '2023-01-07'),
(8, 8, '2023-01-08'),
(9, 9, '2023-01-09'),
(10, 5, '2023-01-10'),
(11, 11, '2023-01-11'),
(12, 17, '2023-01-12'),
(13, 13, '2023-01-13'),
(14, 14, '2023-01-14'),
(15, 15, '2023-01-15'),
(16, 16, '2023-01-16'),
(17, 12, '2023-01-17'),
(18, 18, '2023-01-18'),
(19, 19, '2023-01-19'),
(10, 23, '2023-01-20'),
(1, 21, '2023-01-21'),
(2, 22, '2023-01-22'),
(3, 20, '2023-01-23'),
(4, 24, '2023-01-24'),
(5, 25, '2023-01-25');
;

-- 插入借阅规则，11种不同的规则，参考书不能借与外部人员
INSERT INTO LendingRule (BookStoreType, ReaderType, Period, Quantity, RenewalTimes, OverdueFine, Note) VALUES
(1, 1, 30, 5, 2, 0.50, '普通图书，学生，借阅期限30天，最多借5册，可续借2次，逾期罚款0.50元/天'),
(2, 2, 60, 10, 3, 0.25, '期刊，教师，借阅期限60天，最多借10册，可续借3次，逾期罚款0.25元/天'),
(4, 1, 20, 3, 2, 0.75, '电子资源，学生，借阅期限20天，最多借3册，可续借2次，逾期罚款0.75元/天'),
(1, 2, 40, 7, 3, 0.50, '普通图书，教师，借阅期限40天，最多借7册，可续借3次，逾期罚款0.50元/天'),
(2, 1, 25, 4, 1, 0.30, '期刊，学生，借阅期限25天，最多借4册，可续借1次，逾期罚款0.30元/天'),
(3, 2, 45, 6, 2, 0.20, '参考书，教师，借阅期限45天，最多借6册，可续借2次，逾期罚款0.20元/天'),
(4, 3, 10, 2, 1, 1.50, '电子资源，外部人员，借阅期限10天，最多借2册，可续借1次，逾期罚款1.50元/天'),
(1, 3, 35, 5, 2, 0.75, '普通图书，外部人员，借阅期限35天，最多借5册，可续借2次，逾期罚款0.75元/天'),
(2, 3, 20, 4, 1, 1.25, '期刊，外部人员，借阅期限20天，最多借4册，可续借1次，逾期罚款1.25元/天'),
(3, 1, 30, 3, 1, 0.50, '参考书，学生，借阅期限30天，最多借3册，可续借1次，逾期罚款0.50元/天'),
(4, 2, 15, 3, 2, 1.00, '电子资源，教师，借阅期限15天，最多借3册，可续借2次，逾期罚款1.00元/天')
;

-- 数据插入，包括逾期罚款记录
INSERT INTO Book_Fine_Records (bookid, readerid, borrowdate, returndate, fineamount, paymentstatus) VALUES
(7, 9, '2023-02-21', '2023-03-08', 6.50, 1),    -- 10天逾期, 1.5元/天, 已缴费
(12, 7, '2023-02-21', '2023-03-24', 0.50, 1),   -- 30天逾期, 0.5元/天, 已缴费
(3, 11, '2023-02-27', '2023-03-25', 11.00, 1),  -- 15天逾期, 1.0元/天, 已缴费
(9, 23, '2023-02-27', '2023-03-26', 0.60, 1),   -- 25天逾期, 0.3元/天, 已缴费
(19, 14, '2023-03-07', '2023-03-27', 5.00, 1),  -- 15天逾期, 1.0元/天, 已缴费
(8, 10, '2023-03-08', '2023-04-05', 0.90, 1),   -- 25天逾期, 0.3元/天, 已缴费
(15, 6, '2023-03-15', '2023-04-07', 2.25, 1),   -- 20天逾期, 0.75元/天, 已缴费
(5, 1, '2023-03-16', '2023-04-15', 7.50, 1),    -- 25天逾期, 0.3元/天, 已缴费
(17, 4, '2023-03-20', '2023-04-16', 1.50, 1),   -- 25天逾期, 0.3元/天, 已缴费
(15, 6, '2023-04-26', '2023-05-21', 3.75, 0),   -- 20天逾期, 0.75元/天, 未缴费
(10, 2, '2023-04-26', '2023-06-17', 1.40, 0),   -- 45天逾期, 0.2元/天, 未缴费
(3, 8, '2023-05-22', '2023-06-20', 14.00, 0),   -- 15天逾期, 1.0元/天, 未缴费
(1, 10, '2023-05-23', '2023-06-27', 3.00, 0),   -- 25天逾期, 0.3元/天, 未缴费
(2, 11, '2023-05-23', '2023-07-12', 1.00, 0),   -- 45天逾期, 0.2元/天, 未缴费
(4, 12, '2023-06-02', '2023-07-17', 7.50, 0),   -- 35天逾期, 0.75元/天, 未缴费
(7, 14, '2023-06-25', '2023-07-18', 8.00, 0),   -- 15天逾期, 1.0元/天, 未缴费
(20, 15, '2023-07-01', '2023-08-11', 0.50, 0),  -- 40天逾期, 0.5元/天, 未缴费
(19, 17, '2023-07-22', '2023-08-14', 2.25, 0),  -- 20天逾期, 0.75元/天, 未缴费
(8, 20, '2023-07-29', '2023-09-11', 2.00, 0),   -- 40天逾期, 0.5元/天, 未缴费
(11, 23, '2023-08-15', '2023-09-12', 6.00, 0),  -- 20天逾期, 0.75元/天, 未缴费
(14, 24, '2023-08-30', '2023-10-12', 6.50, 0),  -- 30天逾期, 0.5元/天, 未缴费
(17, 22, '2023-09-01', '2023-11-03', 0.75, 0),  -- 60天逾期, 0.25元/天, 未缴费
(14, 8, '2024-07-26', NULL, 0.00, NULL),        -- 在借, 45天逾期, 0.2元/天, 尚未产生罚款
(6, 21, '2024-08-15', NULL, 0.00, NULL),        -- 在借, 30天逾期, 0.5元/天, 尚未产生罚款
(16, 20, '2024-08-16', NULL, 0.00, NULL),       -- 在借, 40天逾期, 0.5元/天, 尚未产生罚款
(4, 4, '2024-08-24', NULL, 0.00, NULL),         -- 在借, 30天逾期, 0.5元/天, 尚未产生罚款
(12, 7, '2024-08-25', NULL, 0.00, NULL),        -- 在借, 30天逾期, 0.5元/天, 尚未产生罚款
(17, 24, '2024-08-26', NULL, 0.00, NULL),       -- 在借, 25天逾期, 0.3元/天, 尚未产生罚款
(15, 19, '2024-09-02', NULL, 0.00, NULL),       -- 在借, 20天逾期, 0.75元/天, 尚未产生罚款
(1, 12, '2024-09-05', NULL, 0.00, NULL);        -- 在借, 20天逾期, 1.25元/天, 尚未产生罚款




 -- 实验3
-- 作业3
-- 1. 针对一般图书管理信息系统的包含的9个数据表，使用数据库命令的方式，实现下面所有的数据库单表简单查询要求。
-- 1) Select语句里面使用 * 号，显示读者信息表里面的全部信息 
select * from Reader;

-- 2）查询语句里面，只是显示书籍信息表里的全部图书编号，ISBN，书名，作者，及价格信息，其他属性都不显示
select BookID as '图书编号',ISBN,Title as '书名',Author as '作者',Price as '价格'
from Book;

-- 3) 查询出管理员信息表的所有信息，其中编号属性名字显示改为“管理员编号”，密码属性名字显示改为“加密密码”
select AdminID as '管理员编号',username,AdminPassword as '加密密码'
 from admin1;


-- 4）描述一个业务场景，使用where语句，对借阅信息表的记录，进行筛选显示。
-- 假设图书馆管理系统需要显示所有当前仍未归还的书籍借阅记录，以便图书馆管理员可以跟踪哪些书籍还在外借并可能需要催还,使用 WHERE 语句在借阅信息表（Borrowing）中筛选出那些 ReturnDate 为空的记录，这表示这些书籍尚未归还.
select ReturnDate
from Borrowing
where ReturnDate is null;


-- 5）请对借阅信息表的记录按照读者编号进行升序排序，然后显示所有信息。
select * from Borrowing
order by ReaderID asc;


-- 6）请对借阅信息表的记录按照图书编号进行升序，同时读者编号进行降序排序，然后显示所有信息。
select * from Borrowing
order by BookID asc,readerID desc;

-- 7）使用limit子句，显示最早借书的5个同学的图书编号，读者编号，借阅日期等信息。
select BookID as '图书编号', readerID as '读者编号',BorrowDate as '借阅日期'
from Borrowing
limit 5;

-- 2. 数据库的函数
	-- 2.1 查询出书籍信息表中某一学科类型（自选）的书籍数量总数，并显示出来。
    SELECT COUNT(*) as TotalBooks 
    FROM Book
    WHERE BookSubjectType = 1; -- 计算机科学与技术
    
    select count(*) TotalBooks
    from Book,SubjectType
    where BookSubjectType=TypeID and TypeID=1; -- 1是计算机科学与技术
    
   -- 2.2 显示书籍信息表中最贵和最便宜书的价格。
   select max(Price) as'最贵的书价格' ,min(Price) as '最便宜的书价格'
   from Book;

   
   
-- 2.3对借阅信息表里面的图书编号进行分组，然后统计出每本书的借阅次数，只显示借阅次数大于3次的图书编号及次数信息。
select BookID, count(BookID) as BorrowCount
from Borrowing
group by BookID
having BorrowCount >3;

-- 2.4 在借阅信息表中，显示出所有信息，并利用rank()函数输出按照读者编号排序的记录行序数值。
select * ,rank() over B as '读者编号排序'
from Borrowing
window B as (order by readerID);


 -- 3. 创建表的索引
    -- 3.1 请随意选择其中一个表，描述一个现实中的业务场景，需要再在已有数据的表上，新增加一个索引，以提高查询的速度。
    -- 在图书管理系统中，我们可以选择Reader（读者）表作为示例来描述一个现实中的业务场景，并说明为何需要在该表上新增一个索引以提高查询速度。

-- 业务场景描述

-- 假设图书管理系统中的Reader表存储了读者的基本信息，包括读者ID、姓名、性别、年龄、联系电话、注册日期等。随着图书馆读者数量的不断增加，管理员发现，在查找特定读者（例如，根据姓名查找）时，查询速度变得越来越慢。特别是在高峰时段，如开学初或学期末，大量学生同时注册或借阅书籍，导致数据库查询压力增大。
-- 为了提高查询速度，我们可以在Reader表上新增一个索引，用于优化根据姓名进行的查询。
CREATE INDEX idx_reader_name ON Reader(Name);



 -- 实验4
 
 -- 使用或切换到 'dblibrary' 数据库
USE dblibrary;

-- 1.1 查询所有读者编号，读者名字，读者类型，读者说明和读者电话
SELECT 
    Reader.ReaderID, 
    Reader.Name, 
    ReaderType.Type, 
    ReaderType.Description AS ReaderDescription, 
    Reader.Phone
FROM Reader
JOIN ReaderType ON Reader.Type = ReaderType.TypeID;

-- 1.2 查询所有读者编号，读者名字，读者类型，和读者借阅相关规则（使用 WHERE 实现连接）
SELECT 
    Reader.ReaderID, 
    Reader.Name, 
    ReaderType.Type, 
    LendingRule.BookStoreType, 
    LendingRule.Period, 
    LendingRule.Quantity, 
    LendingRule.OverdueFine,
    LendingRule.Note
FROM Reader, ReaderType, LendingRule
WHERE Reader.Type = ReaderType.TypeID
AND ReaderType.TypeID = LendingRule.ReaderType;

-- 1.3 查询所有读者编号，读者名字，读者类型，和读者借阅相关规则（使用 INNER JOIN 实现连接）
SELECT 
    Reader.ReaderID, 
    Reader.Name, 
    ReaderType.Type, 
    LendingRule.BookStoreType, 
    LendingRule.Period, 
    LendingRule.Quantity, 
    LendingRule.OverdueFine,
    LendingRule.Note
FROM Reader
INNER JOIN ReaderType ON Reader.Type = ReaderType.TypeID
INNER JOIN LendingRule ON ReaderType.TypeID = LendingRule.ReaderType;

-- 1.4 查询所有读者编号，读者名字，读者类型，预约图书编号和预约日期，筛选读者编号和读者类型，并排序
SELECT 
    Reader.ReaderID, 
    Reader.Name, 
    ReaderType.Type, 
    Reservation.BookID, 
    Reservation.ReserveDate
FROM Reader
INNER JOIN ReaderType ON Reader.Type = ReaderType.TypeID
INNER JOIN Reservation ON Reader.ReaderID = Reservation.ReaderID
WHERE Reader.ReaderID BETWEEN 1 AND 50 -- 示例筛选条件
AND ReaderType.TypeID IN (1, 2, 3) -- 示例筛选类型
ORDER BY Reader.ReaderID, Reservation.ReserveDate;

-- 1.5 使用左外连接查询所有读者编号，读者名字，预约图书编号和预约日期，显示未预约的读者信息
SELECT 
    Reader.ReaderID, 
    Reader.Name, 
    Reservation.BookID, 
    Reservation.ReserveDate
FROM Reader
LEFT JOIN Reservation ON Reader.ReaderID = Reservation.ReaderID
ORDER BY Reader.ReaderID, Reservation.ReserveDate;



-- 1.6 使用右外连接查询图书信息及学科类型信息，显示没有对应学科类型图书的学科信息
SELECT 
    Book.BookID, 
    Book.ISBN, 
    Book.Title, 
    Book.Author, 
    SubjectType.Type
FROM Book
RIGHT JOIN SubjectType ON Book.BookSubjectType = SubjectType.TypeID;

-- 1.7 业务需求交叉连接示例
/*业务需求：查询所有读者与他们已借阅图书的关联，错误的使用交叉连接导致每个读者与每本书都关联。
交叉连接将产生所有读者和书籍的笛卡尔积，这种结果不符合特定业务场景需求（例如，仅显示借阅关系）。*/
SELECT 
    Reader.ReaderID, 
    Book.BookID
FROM Reader
CROSS JOIN Book;

-- 2.1 使用 UNION 合并两种不同藏书类型信息
SELECT 
    Book.BookID, 
    Book.Title, 
    Book.Author, 
    StoreType.Type
FROM Book
JOIN StoreType ON Book.BookStoreType = StoreType.TypeID
WHERE StoreType.TypeID = 1

UNION

SELECT 
    Book.BookID, 
    Book.Title, 
    Book.Author, 
    StoreType.Type
FROM Book
JOIN StoreType ON Book.BookStoreType = StoreType.TypeID
WHERE StoreType.TypeID = 2;

-- 2.2 使用 UNION 并排序显示前3条记录
SELECT * FROM (
    SELECT 
        Book.BookID, 
        Book.Title, 
        Book.Author, 
        StoreType.Type
    FROM Book
    JOIN StoreType ON Book.BookStoreType = StoreType.TypeID
    WHERE StoreType.TypeID = 1

    UNION

    SELECT 
        Book.BookID, 
        Book.Title, 
        Book.Author, 
        StoreType.Type
    FROM Book
    JOIN StoreType ON Book.BookStoreType = StoreType.TypeID
    WHERE StoreType.TypeID = 2
) AS union_result
ORDER BY BookID
LIMIT 3;



-- 3.1 创建第10个表 图书罚款记录表 并插入数据（Book_Fine_Records在之前已创建）


-- 3.2 查询所有图书信息和罚款金额总额
SELECT 
    Book.BookID, 
    SubjectType.Type AS SubjectName, 
    StoreType.Type AS StoreTypeName, 
    Book.Title, 
    Book.Author, 
    Book.Price, 
    SUM(Book_Fine_Records.fineamount) AS TotalFine
FROM Book
LEFT JOIN SubjectType ON Book.BookSubjectType = SubjectType.TypeID
LEFT JOIN StoreType ON Book.BookStoreType = StoreType.TypeID
LEFT JOIN Book_Fine_Records ON Book.BookID = Book_Fine_Records.bookid
GROUP BY Book.BookID;

-- 3.3 派生表过滤图书价格和罚款金额
SELECT * FROM (
    SELECT 
        Book.BookID, 
        SubjectType.Type AS SubjectName, 
        StoreType.Type AS StoreTypeName, 
        Book.Title, 
        Book.Author, 
        Book.Price, 
        SUM(Book_Fine_Records.fineamount) AS TotalFine
    FROM Book
    LEFT JOIN SubjectType ON Book.BookSubjectType = SubjectType.TypeID
    LEFT JOIN StoreType ON Book.BookStoreType = StoreType.TypeID
    LEFT JOIN Book_Fine_Records ON Book.BookID = Book_Fine_Records.bookid
    WHERE Book.Price > 100
    GROUP BY Book.BookID
) AS DerivedBooks
WHERE TotalFine <= 30;

-- 3.4 查找最贵书籍和同价书籍信息
SELECT * FROM Book
WHERE Price = (SELECT MAX(Price) FROM Book);

-- 3.5 查找罚款最多的读者及其借阅信息
SELECT 
    ReaderID, 
    BookID, 
    (SELECT Title FROM Book WHERE Book.BookID = Book_Fine_Records.bookid) AS Title, 
    borrowdate, 
    returndate, 
    fineamount
FROM Book_Fine_Records
WHERE ReaderID = (
    SELECT ReaderID FROM Book_Fine_Records
    GROUP BY ReaderID
    ORDER BY SUM(fineamount) DESC
    LIMIT 1
);



-- 4.1 创建多表来源的视图
/*业务场景：创建一个视图显示借阅时间超过30天的借阅记录：*/