一个存放图书的数据库

y___________u

已于 2024-06-29 18:39:29 修改

阅读量279

点赞数 5

文章标签：数据库 mysql

于 2024-06-29 18:10:54 首次发布

本文链接：https://blog.csdn.net/y___________u/article/details/140068025

版权

设计一个存放图书的数据库时，我通常会考虑几个主要的实体以及它们之间的关系。以下是一个简单的图书数据库设计的示例：

1. 实体定义

图书 (Books)

BookID (主键, 唯一标识符)

Title (书名)
Description (描述或摘要)
- Author (作者)
- ISBN (国际标准书号)
- PublicationDate (出版日期)
- Publisher (出版社)
- Pages (页数)
用户 (Users) (可选，如果你希望跟踪借阅信息)
- UserID (主键, 唯一标识符)
- Username (用户名)
- Password (密码, 注意：应使用哈希存储)
- Email (电子邮件)
- PhoneNumber (电话号码)
- ... (其他用户相关信息)
借阅记录 (Borrowings) (可选，如果你希望跟踪借阅信息)
- BorrowingID (主键, 唯一标识符)
- BookID (外键, 引用Books表的BookID)
- UserID (外键, 引用Users表的UserID)
- BorrowDate (借阅日期)
- ReturnDate (归还日期, 可为空，直到图书归还)

3. SQL示例

以下是基于上述设计的示例：

CREATE DABATASE BOOK;#创建库

USE BOOK;#选择库

CREATE TABLE books (
   `借阅号` int(0) NOT NULL AUTO_INCREMENT,
   `条码` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
   `读者编号` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
   `借阅日期` date NULL DEFAULT NULL,
   `还书日期` date NULL DEFAULT NULL,
   `借阅状态` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
   PRIMARY KEY (`借阅号`) USING BTREE,
   INDEX `读者编号`(`读者编号`) USING BTREE,
   INDEX `条码`(`条码`) USING BTREE,
   CONSTRAINT `借阅表_ibfk_1` FOREIGN KEY (`读者编号`) REFERENCES `读者表` (`读者编号`) ON DELETE RESTRICT ON UPDATE RESTRICT,
   CONSTRAINT `借阅表_ibfk_2` FOREIGN KEY (`条码`) REFERENCES `库存表` (`条码`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE Users (
`书号` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`书名` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`类别` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`作者` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`出版社` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`单价` float(5, 2) NULL DEFAULT NULL,
`数量` int(0) NULL DEFAULT NULL,
PRIMARY KEY (`书号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE Borrowings (
`读者编号` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`姓名` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`类别号` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`单位` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`有效性` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`读者编号`) USING BTREE,
INDEX `类别号`(`类别号`) USING BTREE,
CONSTRAINT `读者表_ibfk_1` FOREIGN KEY (`类别号`) REFERENCES `读者类型表` (`类别号`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;