会员(会员ID、姓名、联系方式、会员类型、会费) 书籍(书籍ID、书名、作者、出版社、分类、数量、价格) 借阅(借阅ID、会员ID、书籍ID、借阅日期、归还日期、应还日期、实还日期、借阅状态) 收入(收入ID、会员ID、收入类型、金额、收入日期)写出各字段数据类型及其备注利用mysql的相关命令创建,并利用相关mysql语句达成1.添加、修改、删除会员信息 2.添加、修改、删除书籍信息 3.查询、借阅、归还书籍 4.查询借阅信息、收入信息 5.书籍检索 6.计算罚款
时间: 2023-08-15 13:08:56 浏览: 95
好的,以下是您的问题的回答:
会员(会员ID int,姓名 varchar,联系方式 varchar,会员类型 varchar,会费 decimal)
书籍(书籍ID int,书名 varchar,作者 varchar,出版社 varchar,分类 varchar,数量 int,价格 decimal)
借阅(借阅ID int,会员ID int,书籍ID int,借阅日期 date,归还日期 date,应还日期 date,实还日期 date,借阅状态 varchar)
收入(收入ID int,会员ID int,收入类型 varchar,金额 decimal,收入日期 date)
利用mysql的相关命令创建表:
CREATE TABLE member (
memberID INT PRIMARY KEY,
name VARCHAR(50),
contact VARCHAR(50),
type VARCHAR(50),
fee DECIMAL(10,2)
);
CREATE TABLE book (
bookID INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(50),
publisher VARCHAR(50),
category VARCHAR(50),
quantity INT,
price DECIMAL(10,2)
);
CREATE TABLE borrow (
borrowID INT PRIMARY KEY,
memberID INT,
bookID INT,
borrowDate DATE,
returnDate DATE,
dueDate DATE,
actualReturnDate DATE,
status VARCHAR(50),
FOREIGN KEY (memberID) REFERENCES member(memberID),
FOREIGN KEY (bookID) REFERENCES book(bookID)
);
CREATE TABLE income (
incomeID INT PRIMARY KEY,
memberID INT,
type VARCHAR(50),
amount DECIMAL(10,2),
date DATE,
FOREIGN KEY (memberID) REFERENCES member(memberID)
);
添加会员信息:
INSERT INTO member (memberID, name, contact, type, fee) VALUES (1, '张三', '13812345678', 'VIP', 100.00);
修改会员信息:
UPDATE member SET fee=150.00 WHERE memberID=1;
删除会员信息:
DELETE FROM member WHERE memberID=1;
添加书籍信息:
INSERT INTO book (bookID, title, author, publisher, category, quantity, price) VALUES (1, '红楼梦', '曹雪芹', '人民文学出版社', '古典小说', 5, 50.00);
修改书籍信息:
UPDATE book SET price=60.00 WHERE bookID=1;
删除书籍信息:
DELETE FROM book WHERE bookID=1;
查询书籍:
SELECT * FROM book;
借阅书籍:
INSERT INTO borrow (borrowID, memberID, bookID, borrowDate, returnDate, dueDate, actualReturnDate, status) VALUES (1, 1, 1, '2021-01-01', NULL, '2021-01-15', NULL, '借出');
归还书籍:
UPDATE borrow SET actualReturnDate='2021-01-10', status='已归还' WHERE borrowID=1;
查询借阅信息:
SELECT * FROM borrow;
查询收入信息:
SELECT * FROM income;
书籍检索:
SELECT * FROM book WHERE title LIKE '%红楼梦%';
计算罚款:
SELECT DATEDIFF(actualReturnDate, dueDate) * 0.1 AS fine FROM borrow WHERE actualReturnDate > dueDate;
阅读全文