某个借阅者借阅图书在数据库中如何实现?

img

img

img


1.计算每个系所借图书的数量(多表group by、count(*))2.查询女性借阅者的个人信息、所借图书信息以及借阅信息(多表查询)。3.求读者号为6同学的借阅信息(子查询)4.某个借阅者借阅图书在数据库中如何实现?

create table reader(
    id int(11) auto_increment PRIMARY key,
    rno varchar(16) not null,
    name varchar(16) not null,
    sex enum('男','女') NOT NULL,
    age int(2),
    address varchar(32),
    department varchar(16)
);

create table book(
    id int(11) auto_increment PRIMARY key,
    bno varchar(16) not null,
    author varchar(16) not null,
    publish varchar(16),
    status int(2),
    bname varchar(16)
);

create table reader_book(
    id int(11) auto_increment PRIMARY key,
    book_id int(11) not null,
    reader_id int(11) not null,
    return_date date,
    lend_date date
);

1、使用 SELECT 语句检索借阅者表中读者号为 reader_id 的借阅者信息:

SELECT * FROM student WHERE reader_id = reader_id;

2、使用 SELECT 语句检索书籍表中书号为 book_id 的书的信息,并检查借阅状态是否为 "未借阅":

SELECT * FROM book WHERE book_id = book_id AND book_record = "未借阅";

3、使用 INSERT 语句向借阅记录表中插入一条新的记录:

INSERT INTO borrowinfo (book_id, student_id, borrow_date, return_date,book_name,student_name) VALUES (book_id, reader_id, borrow_date, return_date,book_name,student_name);

4、使用 UPDATE 语句更新书籍表中书号为 book_id 的书的借阅状态:

UPDATE book SET book_record = "已借阅" WHERE book_id = book_id;

5、使用 UPDATE 语句更新借阅者表中读者号为 reader_id 的借阅者的借阅信息:

UPDATE borrowinfo SET borrowed_books = borrowed_books + 1 WHERE reader_id = reader_id;

图```
READER (R_ID, R_Name, R_Address, R_Gender, R_Age, R_Department)
BOOK (B_ID, B_Name, B_Author, B_Publisher, B_Status)
BORROW (R_ID, B_ID, Borrow_Date, Return_Date)



表```sql

CREATE TABLE READER (
  R_ID CHAR(10) PRIMARY KEY,
  R_Name CHAR(20) NOT NULL,
  R_Address CHAR(50) NOT NULL,
  R_Gender CHAR(1) NOT NULL,
  R_Age INT NOT NULL,
  R_Department CHAR(20) NOT NULL
);

CREATE TABLE BOOK (
  B_ID CHAR(10) PRIMARY KEY,
  B_Name CHAR(50) NOT NULL,
  B_Author CHAR(20) NOT NULL,
  B_Publisher CHAR(20) NOT NULL,
  B_Status CHAR(1) NOT NULL
);

CREATE TABLE BORROW (
  R_ID CHAR(10) NOT NULL,
  B_ID CHAR(10) NOT NULL,
  Borrow_Date DATE NOT NULL,
  Return_Date DATE,
  PRIMARY KEY (R_ID, B_ID),
  FOREIGN KEY (R

希望采纳