1
-- 创建数据库
2
drop database if exists library_db;
3
create database library_db;
4
use library_db;
5

6
-- 创建图书类别表
7
create table library_db.book_category (
8
    category_id varchar(10) primary key,
9
    category_name varchar(50) not null,
10
    description varchar(100)
11
);
12

13
-- 创建作者表
14
create table library_db.author (
15
    author_id int primary key auto_increment,
16
    author_name varchar(50) not null,
17
    nationality varchar(20),
18
    birth_year int
19
);
20

21
-- 创建图书表
22
create table library_db.book (
23
    book_id int primary key,
24
    book_name varchar(100) not null,
25
    author_id int not null,
26
    category_id varchar(10),
27
    publish_year int,
28
    price decimal(10,2),
29
    stock int default 0
30
);
31

32
-- 创建借阅记录表
33
create table library_db.borrow_record (
34
    record_id int primary key auto_increment,
35
    book_id int not null,
36
    reader_name varchar(50) not null,
37
    borrow_date date not null,
38
    return_date date,
39
    is_returned boolean default false
40
);
41

42
-- 插入图书类别数据
43
insert into library_db.book_category values
44
('c001', '文学小说', '各类文学作品和小说'),
45
('c002', '科学技术', '计算机、工程等科技类书籍'),
46
('c003', '历史地理', '历史、地理相关书籍'),
47
('c004', '艺术设计', '绘画、设计等艺术类书籍'),
48
('c005', '经济管理', '经济、商业和管理类书籍');
49

50
-- 插入作者数据
51
insert into library_db.author(author_name, nationality, birth_year) values
52
('鲁迅', '中国', 1881),
53
('莫言', '中国', 1955),
54
('J.K.罗琳', '英国', 1965),
55
('刘慈欣', '中国', 1963),
56
('余华', '中国', 1960),
57
('东野圭吾', '日本', 1958),
58
('村上春树', '日本', 1949),
59
('钱钟书', '中国', 1910);
60

61
-- 插入图书数据
62
insert into library_db.book values
63
(1, '百年孤独', 3, 'c001', 1967, 59.80, 15),
64
(2, '三体', 4, 'c002', 2006, 45.00, 20),
65
(3, '活着', 5, 'c001', 1993, 35.00, 10),
66
(4, '白夜行', 6, 'c001', 1999, 56.00, 8),
67
(5, '围城', 8, 'c001', 1947, 36.00, 12),
68
(6, '1984', 3, 'c001', 1949, 42.00, 18),
69
(7, '哈利波特与魔法石', 3, 'c001', 1997, 48.00, 25),
70
(8, '人类简史', 4, 'c003', 2011, 68.00, 15),
71
(9, '设计中的设计', 7, 'c004', 2000, 88.00, 5),
72
(10, '挪威的森林', 7, 'c001', 1987, 45.00, 12),
73
(11, '大数据时代', 5, 'c002', 2012, 52.00, 8),
74
(12, '明朝那些事儿', 2, 'c003', 2006, 48.50, 20),
75
(13, '经济学原理', 2, 'c005', 1998, 78.00, 10),
76
(14, '平凡的世界', 1, 'c001', 1986, 75.00, 15),
77
(15, '时间简史', 3, 'c002', 1988, 45.00, 12);
78

79
-- 插入借阅记录
80
insert into library_db.borrow_record(book_id, reader_name, borrow_date, return_date, is_returned) values
81
(2, '张明', '2023-03-15', '2023-04-10', true),
82
(3, '李华', '2023-03-20', null, false),
83
(5, '王芳', '2023-04-01', '2023-04-15', true),
84
(2, '赵伟', '2023-04-10', null, false),
85
(8, '陈静', '2023-04-05', '2023-04-25', true),
86
(12, '刘强', '2023-04-12', '2023-05-01', true),
87
(14, '孙梅', '2023-04-18', null, false),
88
(7, '周杰', '2023-04-20', null, false),
89
(4, '吴婷', '2023-03-25', '2023-04-12', true),
90
(1, '郑刚', '2023-04-02', null, false);

1
create table class
2
(
3
    cid   int primary key,
4
    cname varchar(20)
5
);
6

7
create table student
8
(
9
    sid      int primary key,
10
    sname    varchar(20),
11
    class_id int,
12
    foreign key (class_id)
13
        references class (cid)
14
        on delete cascade
15
        on update cascade
16
);