SQL学校图书借阅管理系统

以下是一个简单的学校图书借阅管理系统的SQL示例：

创建学生表：

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE
);

创建书籍表：

CREATE TABLE books (
    id INT PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    author VARCHAR(50) NOT NULL,
    publisher VARCHAR(50) NOT NULL,
    pub_date DATE NOT NULL,
    isbn VARCHAR(20) NOT NULL UNIQUE,
    quantity INT NOT NULL
);

创建借阅记录表：

CREATE TABLE borrow_records (
    id INT PRIMARY KEY,
    student_id INT NOT NULL,
    book_id INT NOT NULL,
    borrow_date DATE NOT NULL,
    return_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

查询某个学生借阅的所有书籍：

SELECT books.title, books.author, books.publisher, books.pub_date, books.isbn, borrow_records.borrow_date, borrow_records.return_date
FROM borrow_records
INNER JOIN books ON borrow_records.book_id = books.id
WHERE borrow_records.student_id = [学生ID];

查询某本书被哪些学生借阅过：

SELECT students.name, students.department, students.email, borrow_records.borrow_date, borrow_records.return_date
FROM borrow_records
INNER JOIN students ON borrow_records.student_id = students.id
WHERE borrow_records.book_id = [书籍ID];

更新某个学生的邮箱：

UPDATE students
SET email = '[新邮箱]'
WHERE id = [学生ID];

插入一条借阅记录：

INSERT INTO borrow_records (id, student_id, book_id, borrow_date, return_date)
VALUES ([借阅记录ID], [学生ID], [书籍ID], '[借阅日期]', NULL);

借出一本书并更新库存：

UPDATE books
SET quantity = quantity - 1
WHERE id = [书籍ID];

INSERT INTO borrow_records (id, student_id, book_id, borrow_date, return_date)
VALUES ([借阅记录ID], [学生ID], [书籍ID], '[借阅日期]', NULL);

归还一本书并更新库存：

UPDATE books
SET quantity = quantity + 1
WHERE id = [书籍ID];

UPDATE borrow_records
SET return_date = '[归还日期]'
WHERE student_id = [学生ID] AND book_id = [书籍ID];

注意：以上示例SQL语句仅供参考，具体实现可能会有所不同，需要根据具体需求进行调整。