数据库的增删改查

//     创建library数据库
CREATE DATABASE library;
USE library;

//     创建读者表reader
CREATE TABLE IF NOT EXISTS reader (
   RNO VARCHAR(20) PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

//     创建书籍表book
CREATE TABLE IF NOT EXISTS book (
   BNO VARCHAR(20) PRIMARY KEY,
   title VARCHAR(100),
   author VARCHAR(50)
);

//     创建借阅表borrow
CREATE TABLE IF NOT EXISTS borrow (
   RNO VARCHAR(20),
   BNO VARCHAR(20),
   borrow_date DATE,
   return_date DATE,
   PRIMARY KEY (RNO, BNO),
   FOREIGN KEY (RNO) REFERENCES reader(RNO),
   FOREIGN KEY (BNO) REFERENCES book(BNO)
);

//     向读者表reader插入多条数据
INSERT INTO reader (RNO, name, age) VALUES
  ('R001', 'John', 25),
  ('R002', 'Alice', 28),
  ('R003', 'Mike', 30);

//     向书籍表book插入多条数据
INSERT INTO book (BNO, title, author) VALUES
  ('B001', 'Book 1', 'Author 1'),
  ('B002', 'Book 2', 'Author 2'),
  ('B003', 'Book 3', 'Author 3');

//     向借阅表borrow插入多条数据
INSERT INTO borrow (RNO, BNO, borrow_date) VALUES
  ('R001', 'B001', '2023-10-01'),
  ('R001', 'B002', '2023-10-02'),
  ('R002', 'B001', '2023-10-03');

//     根据书籍借阅情况修改借阅表borrow中的归还时间
UPDATE borrow
SET return_date = '2023-10-10'
WHERE BNO = 'B001';

//     删除读者表reader中某个读者的信息
DELETE FROM reader
WHERE RNO = 'R003';