Book_Management_System/DataBase_operate.sql

-- (1) 比较条件查询： 需求： 查询所有借阅日期早于2024年1月1日的借阅记录，返回借阅ID、读者ID、图书ID和借阅日期。 
　　SELECT loan_id, reader_id, book_id, loan_dateFROM LoanRecordWHERE loan_date < '2024-01-01';
-- (2) 需求： 查询借阅超过30天的图书借阅记录，返回借阅ID、读者ID、图书ID、借阅日期和应还日期。 
　　SELECT loan_id, reader_id, book_id, loan_date, due_dateFROM LoanRecordWHERE DATEDIFF(CURRENT_DATE, loan_date) > 30;
-- (3) 需求：查询借书次数超过５次的读者姓名
SELECT R.reader_name
FROM Reader R
JOIN Loan L ON R.reader_id = L.reader_id
GROUP BY R.reader_name
HAVING COUNT(L.loan_id) > 5;
-- (4)需求：查询借阅日期在２０２３年之后的所有图书标题
SELECT B.book_title
FROM Book B
JOIN Loan L ON B.book_id = L.book_id
WHERE L.loan_date > '2023-01-01';
-- (5) 集合比较查询： 需求： 查询所有节约过的图书ID和标题，且这些图书至少被两位不同的读者借阅过。 
　　SELECT B.book_id, B.book_title
　　FROM Book B
　　JOIN Loan L ON B.book_id = L.book_id
　　GROUP BY B.book_id
HAVING COUNT(DISTINCT L.reader_id) > 1;
-- (6)需求：查询所有曾经借阅过数学类图书的学生姓名
SELECT DISTINCT R.reader_name
FROM Reader R
JOIN Loan L ON R.reader_id = L.reader_id
JOIN Book B ON L.book_id = B.book_id
WHERE B.book_type = '数学' AND R.reader_type = '学生';
-- (7)范围比较查询：需求：查询借阅时长在３０天以上的所有借阅记录
　　SELECT L.loan_id, R.reader_name, B.book_title, DATEDIFF(L.return_date, L.loan_date) AS loan_duration
　　FROM Loan L
　　JOIN Reader R ON L.reader_id = R.reader_id
　　JOIN Book B ON L.book_id = B.book_id
　　WHERE DATEDIFF(L.return_date, L.loan_date) > 30;
-- (8)需求：查询借阅期限为２０到６０天的图书
SELECT L.loan_id, R.reader_name, B.book_title
FROM Loan L
JOIN Reader R ON L.reader_id = R.reader_id
JOIN Book B ON L.book_id = B.book_id
WHERE DATEDIFF(L.return_date, L.loan_date) BETWEEN 20 AND 60;
-- (9)字符串相似比较：需求：查询标题中包含“数据”的所有图书信息
SELECT B.book_id, B.book_title, B.author
FROM Book B
WHERE B.book_title LIKE '%数据%';
-- (10)需求：查询所有以“王”字为开头的读者姓名
SELECT R.reader_name
FROM Reader R
WHERE R.reader_name LIKE '王%';
-- (11)多表连接查询： 需求：查询每位读者借阅的所有图书标题及借阅日期
SELECT R.reader_name, B.book_title, L.loan_date
FROM Loan L
JOIN Reader R ON L.reader_id = R.reader_id
JOIN Book B ON L.book_id = B.book_id;
-- (12)需求：查询每位教师的借阅规则，包括借阅数量、借阅期限。
SELECT R.reader_name, RLR.max_books, RLR.loan_duration, RLR.max_renewals
FROM Reader R
JOIN ReaderLoanRules RLR ON R.reader_id = RLR.reader_id
WHERE R.reader_type = '教师';
-- (12)嵌套查询：需求：查询借阅超过３本书的学生姓名。
SELECT R.reader_name
FROM Reader R
WHERE R.reader_type = '学生'
AND R.reader_id IN (
    SELECT L.reader_id
    FROM Loan L
    GROUP BY L.reader_id
    HAVING COUNT(L.loan_id) > 3
);
-- (13)需求：查询借阅最多图书的读者姓名和借阅的图书数量
SELECT R.reader_name, (SELECT COUNT(*) FROM Loan L WHERE L.reader_id = R.reader_id) AS book_count
FROM Reader R
ORDER BY book_count DESC
LIMIT 1;
-- (14)EXISTS查询：需求：查询至少借阅一本书的学生姓名
SELECT R.reader_name
FROM Reader R
WHERE R.reader_type = '学生'
AND EXISTS (
    SELECT 1
    FROM Loan L
    WHERE L.reader_id = R.reader_id
);
-- (15)需求：查询所有借阅过“计算机”类图书的教师姓名
SELECT R.reader_name
FROM Reader R
WHERE R.reader_type = '教师'
AND EXISTS (
    SELECT 1
    FROM Loan L
    JOIN Book B ON L.book_id = B.book_id
    WHERE L.reader_id = R.reader_id AND B.book_type = '计算机'
);
-- (16)排序和分组：需求：查询借阅每个图书类别的借阅次数最多的图书
SELECT B.book_title, B.book_type, COUNT(L.loan_id) AS loan_count
FROM Loan L
JOIN Book B ON L.book_id = B.book_id
GROUP BY B.book_type, B.book_title
ORDER BY loan_count DESC
LIMIT 1;
-- (17)需求：查询每月借阅次数最多的读者姓名
SELECT R.reader_name, COUNT(L.loan_id) AS loan_count
FROM Loan L
JOIN Reader R ON L.reader_id = R.reader_id
WHERE YEAR(L.loan_date) = 2023
GROUP BY MONTH(L.loan_date), R.reader_name
ORDER BY loan_count DESC;
-- (18)需求：查询某本书是否被预约
SELECT EXISTS (
    SELECT 1
    FROM ReservationRecord
    WHERE book_id = 1
) AS is_reserved;
-- (19)需求：查询未被预约的图书信息
SELECT book_id, book_name, author FROM Book WHERE book_id NOT IN (SELECT book_id FROM ReservationRecord);
-- (20)需求：查询借阅记录中，归还日期晚于应还日期的记录
SELECT loan_id, reader_id, book_id, loan_date, return_date FROM LoanRecord WHERE return_date > due_date;
-- 数据更新 --
-- 更新需求 1: 更新借阅记录的归还日期
　　UPDATE LoanRecord
　　SET return_date = '2024-12-20'
WHERE reader_id = 1 AND return_date IS NULL;

-- 更新需求 2:更新图书的借阅状态为已借出
　　UPDATE Book
　　SET status = '已借出'
WHERE book_id = 101;

-- 更新需求 3: 更新读者的联系方式 
　　UPDATE Reader
　　SET contact_info = '123-456-7890'
　　WHERE reader_id = 5;

-- 更新需求 4: 更新罚款金额上限
　　UPDATE FineRecord
　　SET fine_amount = 100
　　WHERE fine_amount > 100;

-- 更新需求 5: 更新图书分类为“编程语言” 
　　UPDATE Book
　　SET category_id = (SELECT category_id FROM BookCategory WHERE category_name = '编程语言')
　　WHERE category_id = (SELECT category_id FROM BookCategory WHERE category_name = '计算机科学');

-- 更新需求 6: 更新借阅记录的应归还日期 
　　UPDATE LoanRecord
　　SET due_date = DATE_ADD(due_date, INTERVAL 10 DAY)
　　WHERE loan_id = 123;
 
-- 更新需求 7: 更新图书的出版日期 
　　UPDATE Book
　　SET publish_date = '2024-01-01'
　　WHERE book_id = 201;
 
-- 更新需求 8: 更改借阅记录的图书名称（若图书名称发生更改） 
　　UPDATE LoanRecord L
　　JOIN Book B ON L.book_id = B.book_id
　　SET B.book_name = '高级编程'
　　WHERE B.book_id = 50;
 
-- 更新需求 9: 更新图书评论中的评分 
　　UPDATE BookReview
　　SET rating = 4
　　WHERE book_id = 200;
 
-- 更新需求 10: 更新读者借阅约状态为“冻结” 
　　UPDATE Reader
　　SET status = '冻结'
　　WHERE reader_id = 3;

-- 数据删除 --
-- 删除需求 1: 删除过期预约记录 
　　DELETE FROM ReservationRecord
　　WHERE reservation_date < '2024-01-01';
 
-- 删除需求 2: 删除图书类别为“小说”的所有图书 
　　DELETE FROM Book
　　WHERE category_id = (SELECT category_id FROM BookCategory WHERE category_name = '小说');
 
-- 删除需求 3: 删除某个读者的所有借阅记录 
　　DELETE FROM LoanRecord
　　WHERE reader_id = 7;
 
-- 删除需求 4: 删除已归还的借阅记录 
　　DELETE FROM LoanRecord
　　WHERE return_date IS NOT NULL;
 
-- 删除需求 5: 删除没有罚款记录的读者
　　DELETE FROM Reader
　　WHERE reader_id NOT IN (SELECT DISTINCT reader_id FROM FineRecord);
 
-- 删除需求 6: 删除借阅次数信号过２次的读者借阅记录
　　DELETE FROM ReservationRecord
　　WHERE reader_id IN (
    SELECT reader_id
    FROM LoanRecord
    GROUP BY reader_id
    HAVING COUNT(loan_id) > 2
);
 
-- 删除需求 7: 删除图书归还超过３０天的借阅记录 
　　DELETE FROM LoanRecord
　　WHERE DATEDIFF(CURRENT_DATE, return_date) > 30;
 
-- 删除需求 8: 删除过期的罚款记录
　　DELETE FROM FineRecord
　　WHERE fine_date < '2023-12-31';

-- 删除需求 9: 删除所有失效的读者记录（失效是指没有借阅历史的读者） 
　　DELETE FROM Reader
　　WHERE reader_id NOT IN (SELECT DISTINCT reader_id FROM LoanRecord);

-- 删除需求 10: 删除无效的销售记录 
　　DELETE FROM sales_records WHERE amount = 0;