图书管理系统
设计图书管理系统,包含学生和图书信息,且图书可以进行分类,学生可以在一个时间范围内借阅 图书,并在这个时间范围内归还图书。
1. 涉及以上场景的数据库表,并建立表关系
DROP DATABASE IF EXISTS ebook;
CREATE DATABASE ebook CHARACTER SET 'utf8mb4';
USE ebook;
– Table structure for category
DROP TABLE IF EXISTS category;
CREATE TABLE category (
id int(11) PRIMARY KEY AUTO_INCREMENT,
name varchar(20)
);
– Records of category
INSERT INTO category VALUES (1, '历史');
INSERT INTO category VALUES (2, '艺术');
INSERT INTO category VALUES (3, '计算机');
INSERT INTO category VALUES (4, '数学');
INSERT INTO category VALUES (5, '小说');
– Table structure for student
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id int(11) PRIMARY KEY AUTO_INCREMENT,
name varchar(20)
);
– Records of student
INSERT INTO student VALUES (1, '王昭君');
INSERT INTO student VALUES (2, '李白');
INSERT INTO student VALUES (3, '貂蝉');
INSERT INTO student VALUES (4, '小乔');
INSERT INTO student VALUES (5, '韩信');
– Table structure for book
DROP TABLE IF EXISTS book;
CREATE TABLE book (
id int(11) PRIMARY KEY AUTO_INCREMENT,
name varchar(20),
author varchar(20),
price decimal(10, 2),
category_id int(11),
CONSTRAINT fk_book_category_id FOREIGN KEY (category_id) REFERENCES category (id)
);
– Records of book
INSERT INTO book VALUES (1, '深入理解Java虚拟机', '周志明', 57.90, 3);
INSERT INTO book VALUES (2, '西游记', '吴承恩', 30.68, 5);
INSERT INTO book VALUES (3, '儒林外史', '吴敬梓', 18.80, 5);
INSERT INTO book VALUES (4, '聊斋志异', '蒲松龄', 21.00, 5);
INSERT INTO book VALUES (5, '史记', '司马迁', 278.20, 1);
INSERT INTO book VALUES (6, '资治通鉴', '司马光', 524.00, 1);
INSERT INTO book VALUES (7, 'Java核心技术 卷I:基础知识', 'Cay S. Horstmann'