30 lines
1.1 KiB
SQL
30 lines
1.1 KiB
SQL
-- 查询验证触发器效果
|
|
---------- 查询验证 ----------
|
|
|
|
-- 1. 书籍可借数量和学生当前借阅量 (应由 trg_sync_book_student 更新):
|
|
SELECT book_id, title, total_copies, available_copies FROM books WHERE book_id <= 7;
|
|
SELECT student_id, name, max_borrow, current_borrow, account_status FROM students WHERE student_id <= 5;
|
|
|
|
-- 2. 孙七 (S2023005) 的罚款记录 (应有 trg_calc_fine 生成的逾期罚款 和 手动添加的罚款):
|
|
SELECT f.*, s.name as student_name
|
|
FROM fines f JOIN students s ON f.student_id = s.student_id
|
|
WHERE s.stu_no = 'S2023005';
|
|
|
|
-- 3. 孙七 (S2023005) 的账户状态 (应由 trg_freeze_account 根据总欠款更新):
|
|
SELECT stu_no, name, account_status, current_borrow
|
|
FROM students
|
|
WHERE stu_no = 'S2023005';
|
|
|
|
-- 4. 查看热门图书视图:
|
|
SELECT * FROM v_hot_books;
|
|
|
|
-- 5. 查看院系借阅统计视图:
|
|
SELECT * FROM v_dept_borrow_stats;
|
|
|
|
-- 6. 查看逾期详情视图:
|
|
SELECT od.*, b.title, s.name as student_name
|
|
FROM v_overdue_details od
|
|
JOIN books b ON od.book_id = b.book_id
|
|
JOIN students s ON od.student_id = s.student_id;
|
|
|
|
-- 测试数据插入完毕。 |