【MySql】实验十六 综合练习：图书管理系统数据库结构

文章目录

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

在本篇博客中，我们将一步步创建一个简单的图书管理系统数据库。我们将定义三个主要的数据表：

book

（图书信息）、

reader

（读者信息）和

borrow

（借书记录）。下面是每个表的详细结构。

一、创建数据表

1.1 book表

存储图书信息的表。
列名数据类型是否可为空主键bnoachar(4)否是bnamechar(20)否authorchar(10)否publishchar(20)否pubdatedatetime否

-- 创建book表CREATETABLE book(
    bnoa char(4)NOTNULLPRIMARYKEY,-- 图书编号，主键，不允许为空
    bname char(20)NOTNULL,-- 图书名称，不允许为空
    author char(10)NOTNULL,-- 作者，不允许为空
    publish char(20)NOTNULL,-- 出版社，不允许为空
    pubdate datetimeNOTNULL-- 出版日期，不允许为空);

1.2 reader表

存储读者信息的表。
列名数据类型是否可为空主键rnochar(4)否是rnamechar(10)否

-- 创建reader表CREATETABLE reader(
    rno char(4)NOTNULLPRIMARYKEY,-- 读者编号，主键，不允许为空
    rname char(10)NOTNULL-- 读者姓名，不允许为空);

1.3 borrow表

存储借书记录的表。
列名数据类型是否可为空主键borrownoint否是bnochar(4)是rnochar(4)是borrowdatedatetime是

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

二、插入示例数据

2.1 向book表插入数据

-- 向book表插入数据INSERTinto 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表插入数据insertinto reader(rno,rname)VALUES("0001","全志忠"),("0002","孙佳佳"),("0003","司马精");

2.3 向borrow表插入数据

-- 向borrow表插入数据insertinto 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列建立外键

altertable borrow addFOREIGNKEY(bno)REFERENCES book(bnoa);altertable borrow addFOREIGNKEY(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
ORDERBY publish DESC, pubdate ASC;

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

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

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

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

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

四、视图和索引

4.1 建立视图bookview

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

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

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

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

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

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

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

-- 建立tbook表格 继承 bookCREATEtable tbook like book;

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

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

4.3 删除tbook表

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

DROPTABLE tbook;

五、存储过程和触发器

5.1 创建存储过程bookproc

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

-- 创建存储过程bookprocDELIMITER//CREATEPROCEDURE bookproc(IN authorName char(10))BEGINSELECT bname, publish, pubdate
    FROM book
    WHERE author = authorName;END//DELIMITER;

5.2 执行存储过程bookproc

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

5.3 创建触发器newtrigger

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

-- 创建触发器newtriggerDELIMITER//CREATETRIGGER newtrigger AFTERDELETEON reader
FOR EACH ROWBEGINDELETEFROM borrow WHERE rno = OLD.rno;END//DELIMITER;

六、自定义函数

6.1 创建自定义函数borrowcount

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

-- 创建自定义函数borrowcountDELIMITER//CREATEFUNCTION borrowcount(rno char(4))RETURNSintBEGINDECLARE borrowCount int;SELECTCOUNT(*)INTO borrowCount FROM borrow WHERE rno = rno;IF borrowCount ISNULLTHENSET borrowCount =-1;ENDIF;RETURN borrowCount;END//DELIMITER;

七、数据库备份

7.1 备份数据库

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

请注意，以上备份命令需要在MySQL命令行中执行，并且您需要将

username

替换为您的MySQL用户名。此命令会将整个

Book

数据库备份到D盘根目录下的

bookbackup.sql

文件中。