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
希望采纳