编辑代码

-- 创建数据库
CREATE DATABASE IF NOT EXISTS LibrarySystem CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE LibrarySystem;

-- 创建出版社表
CREATE TABLE ls_publisher (
    pub_id INT UNSIGNED AUTO_INCREMENT COMMENT '出版社ID',
    name VARCHAR(100) NOT NULL COMMENT '出版社名称',
    address VARCHAR(200) COMMENT '地址',
    phone VARCHAR(20) COMMENT '联系电话',
    PRIMARY KEY (pub_id),
    UNIQUE KEY uk_publisher_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '出版社信息表';

-- 创建作者表
CREATE TABLE ls_author (
    author_id INT UNSIGNED AUTO_INCREMENT COMMENT '作者ID',
    name VARCHAR(100) NOT NULL COMMENT '作者姓名',
    country VARCHAR(50) COMMENT '国籍',
    PRIMARY KEY (author_id),
    KEY idx_author_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '作者信息表';

-- 创建用户表（修正默认值问题）
CREATE TABLE ls_user (
    user_id INT UNSIGNED AUTO_INCREMENT COMMENT '用户ID',
    name VARCHAR(100) NOT NULL COMMENT '用户姓名',
    email VARCHAR(100) UNIQUE NOT NULL COMMENT '电子邮箱',
    phone VARCHAR(20) COMMENT '联系电话',
    register_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册日期',
    birth_date DATE COMMENT '出生日期',
    status TINYINT DEFAULT 1 COMMENT '用户状态(1-正常,0-冻结)',
    PRIMARY KEY (user_id),
    UNIQUE KEY uk_user_email (email),
    KEY idx_user_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '图书馆用户表';

-- 创建图书表
CREATE TABLE ls_book (
    book_id INT UNSIGNED AUTO_INCREMENT COMMENT '图书ID',
    title VARCHAR(200) NOT NULL COMMENT '书名',
    ISBN VARCHAR(20) UNIQUE NOT NULL COMMENT '国际标准书号',
    pub_id INT UNSIGNED NOT NULL COMMENT '出版社ID',
    pub_year YEAR COMMENT '出版年份',
    stock INT DEFAULT 1 COMMENT '库存数量',
    price DECIMAL(10,2) COMMENT '定价',
    category VARCHAR(50) COMMENT '分类',
    PRIMARY KEY (book_id),
    UNIQUE KEY uk_book_isbn (ISBN),
    KEY idx_book_title (title),
    KEY idx_book_pub_id (pub_id),
    FOREIGN KEY (pub_id) REFERENCES ls_publisher(pub_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '图书信息表';

-- 创建图书作者关联表
CREATE TABLE ls_book_author (
    book_id INT UNSIGNED NOT NULL COMMENT '图书ID',
    author_id INT UNSIGNED NOT NULL COMMENT '作者ID',
    PRIMARY KEY (book_id, author_id),
    KEY idx_book_author_book_id (book_id),
    KEY idx_book_author_author_id (author_id),
    FOREIGN KEY (book_id) REFERENCES ls_book(book_id) ON DELETE CASCADE,
    FOREIGN KEY (author_id) REFERENCES ls_author(author_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '图书作者关联表';

-- 创建借阅记录表
CREATE TABLE ls_borrow (
    borrow_id INT UNSIGNED AUTO_INCREMENT COMMENT '借阅记录ID',
    user_id INT UNSIGNED NOT NULL COMMENT '借阅用户ID',
    book_id INT UNSIGNED NOT NULL COMMENT '借阅图书ID',
    borrow_date DATE NOT NULL COMMENT '借阅日期',
    return_date DATE COMMENT '归还日期',
    due_date DATE NOT NULL COMMENT '应还日期',
    status TINYINT DEFAULT 0 COMMENT '借阅状态(0-未归还,1-已归还,2-逾期)',
    PRIMARY KEY (borrow_id),
    KEY idx_borrow_user_id (user_id),
    KEY idx_borrow_book_id (book_id),
    KEY idx_borrow_status (status),
    FOREIGN KEY (user_id) REFERENCES ls_user(user_id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES ls_book(book_id) ON DELETE CASCADE,
    CHECK (borrow_date <= due_date),
    CHECK ((status = 1 AND return_date IS NOT NULL) OR (status != 1 AND return_date IS NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '图书借阅记录表';

-- 创建触发器：借阅时减少库存
DELIMITER $$
CREATE TRIGGER trg_after_borrow_insert AFTER INSERT ON ls_borrow
FOR EACH ROW
BEGIN
    IF NEW.status = 0 THEN
        UPDATE ls_book SET stock = stock - 1 WHERE book_id = NEW.book_id;
    END IF;
END$$
DELIMITER ;

-- 创建触发器：归还时增加库存
DELIMITER $$
CREATE TRIGGER trg_after_borrow_update AFTER UPDATE ON ls_borrow
FOR EACH ROW
BEGIN
    IF OLD.status = 0 AND NEW.status = 1 THEN
        UPDATE ls_book SET stock = stock + 1 WHERE book_id = NEW.book_id;
    END IF;
END$$
DELIMITER ;

-- 插入出版社数据
INSERT INTO ls_publisher (name, address, phone) VALUES
('机械工业出版社', '北京市西城区百万庄大街22号', '010-88379001'),
('人民教育出版社', '北京市海淀区中关村南大街17号', '010-58758866'),
('电子工业出版社', '北京市海淀区万寿路173信箱', '010-88254444');

-- 插入作者数据
INSERT INTO ls_author (name, country) VALUES
('Douglas Crockford', '美国'),
('Thomas H. Cormen', '美国'),
('严蔚敏', '中国');

-- 插入用户数据（修正日期格式）
INSERT INTO ls_user (name, email, phone, register_date, birth_date) VALUES
('张三', 'zhangsan@example.com', '13800138000', '2022-01-15 00:00:00', '1990-05-10'),
('李四', 'lisi@example.com', '13900139000', '2022-03-20 00:00:00', '1995-08-15'),
('王五', 'wangwu@example.com', '13700137000', '2022-05-01 00:00:00', '1988-12-20');

-- 插入图书数据
INSERT INTO ls_book (title, ISBN, pub_id, pub_year, stock, price, category) VALUES
('JavaScript高级程序设计', '9787111636877', 1, 2020, 5, 128.00, '计算机'),
('算法导论', '9787111187776', 1, 2006, 3, 129.00, '计算机'),
('数据结构（C语言版）', '9787107196257', 2, 2007, 2, 45.00, '计算机');

-- 插入图书作者关联数据
INSERT INTO ls_book_author (book_id, author_id) VALUES
(1, 1),
(2, 2),
(3, 3);

-- 插入借阅数据
INSERT INTO ls_borrow (user_id, book_id, borrow_date, return_date, due_date, status) VALUES
(1, 1, '2023-01-01', '2023-01-15', '2023-01-16', 1),
(2, 2, '2023-02-01', '2023-02-10', '2023-02-15', 1),
(3, 3, '2023-03-01', NULL, '2023-03-15', 0);

-- 查询用户借阅历史
SELECT 
u.name AS '用户名',
b.title AS '书名',
br.borrow_date AS '借阅日期',
br.return_date AS '归还日期',
br.due_date AS '应还日期',
CASE br.status 
    WHEN 0 THEN '未归还'
    WHEN 1 THEN '已归还'
    WHEN 2 THEN '逾期'
END AS '借阅状态'
FROM ls_borrow br
JOIN ls_user u ON br.user_id = u.user_id
JOIN ls_book b ON br.book_id = b.book_id
WHERE u.user_id = 1
ORDER BY br.borrow_date DESC;

-- 查询逾期图书
SELECT 
u.name AS '用户名',
b.title AS '书名',
br.borrow_date AS '借阅日期',
br.due_date AS '应还日期',
DATEDIFF(CURDATE(), br.due_date) AS '逾期天数'
FROM ls_borrow br
JOIN ls_user u ON br.user_id = u.user_id
JOIN ls_book b ON br.book_id = b.book_id
WHERE br.status = 0 AND br.due_date < CURDATE();

-- 查询库存不足图书
SELECT 
b.title AS '书名',
b.ISBN AS 'ISBN',
p.name AS '出版社',
b.stock AS '当前库存'
FROM ls_book b
JOIN ls_publisher p ON b.pub_id = p.pub_id
WHERE b.stock <= 1;

-- 查询出版社图书
SELECT 
p.name AS '出版社',
b.title AS '书名',
b.pub_year AS '出版年份',
b.stock AS '库存'
FROM ls_book b
JOIN ls_publisher p ON b.pub_id = p.pub_id
WHERE p.pub_id = 1;
-- 修改出版社信息
UPDATE ls_publisher 
SET name = '机械工业出版社(新)', 
    address = '北京市西城区百万庄大街22号(新地址)'
WHERE pub_id = 1;

-- 修改用户邮箱和电话
UPDATE ls_user 
SET email = 'zhangsan_new@example.com', 
    phone = '13800138001'
WHERE user_id = 1;

-- 修改图书价格和库存
UPDATE ls_book 
SET price = 138.00, 
    stock = stock + 5
WHERE book_id = 1;

-- 批量修改借阅状态（将逾期未还的标记为逾期）
UPDATE ls_borrow 
SET status = 2 
WHERE status = 0 AND due_date < CURDATE();

-- 删除一个出版社（会级联删除关联的图书）
DELETE FROM ls_publisher WHERE pub_id = 3;

-- 删除一个用户（会级联删除关联的借阅记录）
DELETE FROM ls_user WHERE user_id = 3;

-- 删除一本图书（会级联删除关联的作者和借阅记录）
DELETE FROM ls_book WHERE book_id = 3;

-- 删除所有逾期超过30天的借阅记录
DELETE FROM ls_borrow 
WHERE status = 2 AND DATEDIFF(CURDATE(), due_date) > 30;

-- 查询修改后的出版社信息
SELECT * FROM ls_publisher;

-- 查询修改后的用户信息
SELECT * FROM ls_user;

-- 查询修改后的图书信息
SELECT * FROM ls_book;

-- 查询修改后的借阅记录
SELECT * FROM ls_borrow;
-- 删除表（按依赖顺序）
DROP TRIGGER IF EXISTS trg_after_borrow_insert;
DROP TRIGGER IF EXISTS trg_after_borrow_update;
DROP TABLE IF EXISTS ls_borrow;
DROP TABLE IF EXISTS ls_book_author;
DROP TABLE IF EXISTS ls_book;
DROP TABLE IF EXISTS ls_user;
DROP TABLE IF EXISTS ls_author;
DROP TABLE IF EXISTS ls_publisher;