---

创建图书管理系统数据库结构

在本篇博客中，我们将一步步创建一个简单的图书管理系统数据库。我们将定义三个主要的数据表：book（图书信息）、reader（读者信息）和borrow（借书记录）。下面是每个表的详细结构。

一、创建数据表

1.1 book表

存储图书信息的表。

列名	数据类型	是否可为空	主键
bnoa	char(4)	否	是
bname	char(20)	否
author	char(10)	否
publish	char(20)	否
pubdate	datetime	否

-- 创建book表
CREATE TABLE book(
    bnoa char(4) NOT NULL PRIMARY KEY,  -- 图书编号，主键，不允许为空
    bname char(20) NOT NULL,             -- 图书名称，不允许为空
    author char(10) NOT NULL,            -- 作者，不允许为空
    publish char(20) NOT NULL,           -- 出版社，不允许为空
    pubdate datetime NOT NULL            -- 出版日期，不允许为空
);

1.2 reader表

存储读者信息的表。

列名	数据类型	是否可为空	主键
rno	char(4)	否	是
rname	char(10)	否

-- 创建reader表
CREATE TABLE reader(
    rno char(4) NOT NULL PRIMARY KEY,  -- 读者编号，主键，不允许为空
    rname char(10) NOT NULL             -- 读者姓名，不允许为空
);

1.3 borrow表

存储借书记录的表。

列名	数据类型	是否可为空	主键
borrowno	int	否	是
bno	char(4)	是
rno	char(4)	是
borrowdate	datetime	是

-- 创建borrow表
CREATE TABLE borrow(
    borrowno int PRIMARY KEY,           -- 借书记录编号，主键，不允许为空
    bno char(4),                        -- 图书编号，可以为空
    rno char(4),                        -- 读者编号，可以为空
    borrowdate datetime                 -- 借书日期，可以为空
);

二、插入示例数据

2.1 向book表插入数据

-- 向book表插入数据
INSERT into book(bnoa,bname,author,publish,pubdate)
VALUES 
(0001,"数据库原理","张小海","人民邮电出版社","2020-10-01"),
(0002,"软件工程","李莎莎","高等教育出版社","2020-08-09"),
(0003,"操作系统","钱东升","人民邮电出版社","2021-03-06"),
(0004,"数据结构","鲁明浩","清华大学出版社","2021-05-28"),
(0005,"编译原理","张悦","高等教育出版社","2021-10-30");

2.2 向reader表插入数据

-- 向reader表插入数据
insert into reader(rno,rname)
VALUES
("0001","全志忠"),
("0002","孙佳佳"),
("0003","司马精");

2.3 向borrow表插入数据

-- 向borrow表插入数据
insert into borrow
VALUES
("1","0001","0001","2021-11-15"),
("2","0002","0001","2021-11-20"),
("3","0002","0002","2021-11-30"),
("4","0003","0002","2021-12-05"),
("5","0003","0001","2021-12-12"),
("6","0004","0001","2021-12-21");

三、查询操作

3.1 根据语义为借书表borrow的bno列和 rno列建立外键

alter table borrow add FOREIGN KEY (bno)  REFERENCES book(bnoa); 
alter table borrow add FOREIGN KEY (rno)  REFERENCES reader(rno);

3.2 查询张小海编写的“数据库原理”的出版日期。

-- 查询张小海编写的“数据库原理”的出版日期
select pubdate
FROM book
where author="张小海" AND bname="数据库原理";

3.3 查询“操作系统”的所有借书记录。

-- 查询“操作系统”的所有借书记录
select *
from book b,borrow bo,reader r
where b.bnoa=bo.bno and bo.rno=r.rno
and b.bname="操作系统";

3.4 查询图书表的所有记录，按出版社和出版日期排序。

查询图书表的所有记录，查询结果按出版社和出版日期排序,出版社的排序条件为降序,出版日期的排序条件为升序。

-- 查询图书表的所有记录，按出版社和出版日期排序
SELECT *
FROM book
ORDER BY publish DESC, pubdate ASC;

3.5 查询每个读者借书的次数。

查询每个读者借书的次数,查询结果由“读者编号”和“借书次数”两列组成。

-- 查询每个读者借书的次数
select r.rno as "读者编号",COUNT(bw.borrowno) as "借书次数"
FROM reader r
left join borrow bw on r.rno=bw.rno
GROUP BY r.rno;

3.6 查询没有被任何人借过的图书的图书编号、图书名称、作者。

-- 查询没有被任何人借过的图书的图书编号、图书名称、作者
select b.bnoa,b.bname,b.author
from book b
LEFT JOIN borrow bw on b.bnoa =bw.bno
where bw.bno is NULL;

四、视图和索引

4.1 建立视图bookview

建立新的名为bookview的视图，该视图检索“人民邮电出版社”出版的所有图书的图书名称、作者和出版日期。

-- 建立视图bookview
CREATE VIEW bookview as
select bname,author,pubdate
FROM book
where publish="人民邮电出版社";

4.2 在图书表 book 的出版日期列上建立索引

在图书表 book 的出版日期列上建立名为pubdatcindex的普通索引,要求该索引采取降序排列。

-- 在图书表 book 的出版日期列上建立索引
CREATE INDEX pubdatcindex ON book (pubdate DESC);

4.2 继承book创建tbook表信息插入

建立新的名为tbook 的表，该表的结构与 book表完全一样,利用一个INSERT语句将“人民邮电出版社”出版的所有图书信息插人入book表中。

-- 建立tbook表格 继承 book
CREATE table tbook like book;

• 将“人民邮电出版社”出版的所有图书信息插人tbook表中。

INSERT INTO tbook
SELECT *
FROM book
WHERE publish = '人民邮电出版社';

4.3 删除tbook表

（12）删除第(11）步建立的表tbook。

DROP TABLE tbook;

五、存储过程和触发器

5.1 创建存储过程bookproc

创建拥有一个参数的bookproc()存储过程，用来查询图书表中由该参数指定作者的所有图书的图书名称、出版社和出版日期,然后执行该存储过程,并将输入参数赋值“张小海”。

-- 创建存储过程bookproc
DELIMITER //
CREATE PROCEDURE bookproc(IN authorName char(10))
BEGIN
    SELECT bname, publish, pubdate
    FROM book
    WHERE author = authorName;
END //
DELIMITER ;

5.2 执行存储过程bookproc

-- 执行存储过程bookproc
CALL bookproc("张小海");

5.3 创建触发器newtrigger

建立名为newtrigger的触发器，触发器规定，当删除读者表中的一条记录时将借书表中相应读者的借书记录一并删除,然后删除读者表中的一条记录以触发触发器。

-- 创建触发器newtrigger
DELIMITER //
CREATE TRIGGER newtrigger AFTER DELETE ON reader
FOR EACH ROW
BEGIN
    DELETE FROM borrow WHERE rno = OLD.rno;
END //
DELIMITER ;

六、自定义函数

6.1 创建自定义函数borrowcount

建立borrowcount(自定义函数，该函数用于输人读者的编号以返回该读者的借书次数,如果编号不存在则返回-1。

-- 创建自定义函数borrowcount
DELIMITER //
CREATE FUNCTION borrowcount(rno char(4))
RETURNS int
BEGIN
    DECLARE borrowCount int;
    SELECT COUNT(*) INTO borrowCount FROM borrow WHERE rno = rno;
    IF borrowCount IS NULL THEN
        SET borrowCount = -1;
    END IF;
    RETURN borrowCount;
END //
DELIMITER ;

七、数据库备份

7.1 备份数据库

-- 备份数据库
mysqldump -u username -p Book > D:/bookbackup.sql

请注意，以上备份命令需要在MySQL命令行中执行，并且您需要将username替换为您的MySQL用户名。此命令会将整个Book数据库备份到D盘根目录下的bookbackup.sql文件中。