Mysql_LibraryDB

drop database if exists LibraryDB;
create database if not exists LibraryDB;
use LibraryDB;

-- 1. 读者表 (Readers Table)
drop table if exists readers;
create table readers (
    -- 读者编号 (Reader Number) 主键，不能为空
    ReaderNumber char(6) not null primary key,
    -- 姓名 (Name)，不能为空
    Name char(10) not null,
    -- 类别号 (Category Number)，不能为空
    CategoryNumber char(2) not null,
    -- 单位 (Organization)，可为空
    Organization VARCHAR(20),
    -- 有效性 (Validity)，可为空
    Validity char(10)
);

-- 2. 读者类型表 (Reader Types Table)
drop table if exists ReaderType;
create table ReaderType (
    -- 类别号 (Category Number) 主键，不能为空
    CategoryNumber char(2) not null primary key,
    -- 类名 (Category Name)，不能为空
    CategoryName char(10) not null,
    -- 可借数量 (Maximum Borrows)，可借书的数量
    MaximumBorrows int,
    -- 可借天数 (Maximum Borrow Days)，可借书的天数
    MaximumBorrowDays INT
);

-- 3. 库存表 (Stock Piles Table)
drop table if exists stockpiles;
create table stockpiles (
    -- 条码 (Barcode) 主键，不能为空
    Barcode char(20) not null primary key,
    -- 书号 (Book Number)，不能为空
    BookNumber char(10) not null,
    -- 位置 (Location)，库存位置，不能为空
    Location VARCHAR(20) NOT NULL,
    -- 库存状态 (Stock Status)，库存的状态
    StockStatus CHAR(10)
);

-- 4. 图书表 (Books Table)
drop table if exists books;
create table books (
    -- 书号 (Book Number) 主键，不能为空
    BookNumber char(10) not null primary key,
    -- 书名 (Book Title)，不能为空
    BookTitle VARCHAR(20) not null,
    -- 类别 (Category)，不能为空
    Category char(10) NOT NULL,
    -- 作者 (Author)，不能为空
    Author char(10) NOT NULL,
    -- 出版社 (Publisher)，不能为空
    Publisher VARCHAR(20) NOT NULL,
    -- 单价 (Unit Price)，图书的单价
    UnitPrice FLOAT(5,2),
    -- 数量 (Quantity)，库存的数量
    Quantity INT
);

-- 5. 借阅表 (Borrow Table)
drop table if exists borrow;
create table borrow (
    -- 借阅号 (Borrow Number) 主键，不能为空
    BorrowNumber INT not null primary key AUTO_INCREMENT,
    -- 条码 (Barcode)，不能为空
    Barcode CHAR(20) not null,
    -- 读者编号 (Reader Number)，不能为空
    ReaderNumber char(6) NOT NULL,
    -- 借阅日期 (Borrow Date)，借阅图书的日期
    BorrowDate DATE,
    -- 还书日期 (Return Date)，归还图书的日期
    ReturnDate DATE,
    -- 借阅状态 (Borrow Status)，借阅的状态
    BorrowStatus char(6)
);


-- (1)为LibraryDB中的读者表指定主键为“读者编号”。
-- ALTER TABLE readers
-- ADD PRIMARY KEY (ReaderNumber);
-- (2)为读者表创建外键，其“类别号”列的值必须是读者类型表中“类别号”列存在的值，删除或修改读者类型表中的类别号值时，读者表中“类别号”列的数据也要随之变化。
ALTER TABLE readers
ADD FOREIGN KEY (CategoryNumber) REFERENCES ReaderType(CategoryNumber) ON DELETE CASCADE ON UPDATE CASCADE;
-- (3)为借阅表创建外键，其“读者编号”列的值必须是读者表中“读者编号”列存在的值，删除或修改读者表中的读者编号值时，如果借阅表中该读者还有记录，则不得删除或修改。
ALTER TABLE borrow
ADD FOREIGN KEY (ReaderNumber) REFERENCES readers(ReaderNumber) ON DELETE RESTRICT ON UPDATE CASCADE;
-- (4)为借阅表创建外键，借阅表中“条码”列中的值必须是库存表中“条码”列存在的值，删除或修改库存表中的条码值时，借阅表中“条码”列的数据也要随之变化。
ALTER TABLE borrow
ADD FOREIGN KEY (Barcode) REFERENCES stockpiles(Barcode) ON DELETE CASCADE ON UPDATE CASCADE;
-- (5)修改读者类型表，可借数量必须在0~30本的范围内。
ALTER TABLE ReaderType
ADD CHECK (MaximumBorrows BETWEEN 0 AND 30);
-- (6)修改库存表，库存状态只能是“在馆”“借出”“丢失”3种状态之一。
ALTER TABLE stockpiles
ADD CHECK (StockStatus IN ('在馆', '借出', '丢失'));

INSERT INTO books 
    VALUES ('A0120', '庄子', '文学', '庄周', '吉林大学出版社', 18.5, 5),
    ('A0134', '唐诗三百首', '文学', '李平', '安徽科学出版社', 28, 10),
    ('B1101', '西方经济学史', '财经', '莫竹芩', '海南出版社', 39.8, 8),
    ('B2213', '商业博弈', '财经', '孔英', '北京大学出版社', 39, 15),
    ('C1269', '数据结构', '计算机', '李刚', '高等教育出版社', 29, 20),
    ('C3121', '品牌策划与推广实战', '计算机', '张晓红', '人民邮电出版社', 42, 6),
    ('C3182', 'C语言程序设计', '计算机', '李学刚', '高等教育出版社', 36.8, 11),
    ('C3256', 'MySQL数据库', '计算机', '孙季红', '电子工业出版社', 29, 9);

INSERT INTO stockpiles 
    VALUES ('123412', 'A0120', '1-A-56', '在馆'),
    ('123413', 'A0120', '1-A-57', '借出'),
    ('223410', 'A0134', '2-B-01', '在馆'),
    ('223411', 'A0134', '2-B-02', '借出'),
    ('311231', 'B1101', '2-C-23', '在馆'),
    ('321123', 'C1269', '3-A-12', '丢失'),
    ('321124', 'C1269', '3-A-13', '借出'),
    ('411111', 'C3256', '3-B-01', '借出'),
    ('411112', 'C3256', '3-B-02', '借出'),
    ('411113', 'C3256', '3-B-03', '在馆');

INSERT INTO ReaderType 
    VALUES(1, '学生', 10, 30),
    (2, '教师', 20, 60),
    (3, '职工',15, 20);

INSERT INTO readers 
    VALUES ('0001', '张小东', '1', '软件学院', '有效'),
    ('0002', '苏明', '1', '财经学院', '有效'),
    ('1001', '梁小红', '2', '软件学院', '有效'),
    ('1002', '赵明敏', '2', '传媒学院', '有效'),
    ('2001', '李丰年', '3', '计财处', '有效');

INSERT INTO borrow 
    VALUES (100001, '123413', '0001', '2020-11-05', DEFAULT, '借阅'),
    (100002, '223411', '0002', '2020-09-28', '2020-10-13', '已还'),
    (100003, '321123', '1001', '2020-07-01', DEFAULT, '过期'),
    (100004, '321124', '2001', '2020-10-09', '2020-10-14', '已还'),
    (100005, '321124', '0001', '2020-10-15', DEFAULT, '借阅'),
    (100006, '223411', '2001', '2020-10-16', DEFAULT, '借阅'),
    (100007, '411111', '1002', '2020-09-01', '2020-09-24', '已还'),
    (100008, '411111', '0001', '2020-09-25', DEFAULT, '借阅'),
    (100009, '411111', '1001', '2020-10-08', DEFAULT, '借阅');

INSERT INTO borrow
    VALUES (DEFAULT, '223410', '2001', CURDATE(), DEFAULT, '借阅');

UPDATE stockpiles
    SET StockStatus = '借出'
    WHERE Barcode = '223410';

INSERT INTO books 
    VALUES('C3325', '计算机基础', '计算机', '陈焕东', '高等教育出版社', 38.6, 2);

DELETE FROM readers WHERE ReaderNumber='苏明';