列名

数据类型

长度

约束

说明

Id

Char(10)

10

Not null primary key

ID编号

Name

Varchar(30)

30

Not null

管理员名称

Password

Varchar(30)

30

Not null

管理员密码

列名

数据类型

长度

约束

说明

Id

Char(10)

10

Not null foreign key

ID编号

Bookname

Varchar(60)

60

Not null

书名

Author

Varchar(60)

60

Not null

作者

BookId

Char(10)

10

Not null primary key

图书编号

Price

Decimal(10,2)

30

Not null

价格

Publish

Varchar(30)

30

Not null

出版社

列名

数据类型

长度

约束

说明

BookId

Char(10)

10

Not null primary key

图书编号

Bookname

Varchar(60)

60

Not null

书名

Quantity

Char(20)

20

Not null

数量

Type

Varchar(30)

30

Not null

类型

Shelf

Varchar(20)

20

Not null

所在书架

列名

数据类型

长度

约束

说明

ReaderId

Char(10)

10

Not null primary key

读者编号

ReaderName

Varchar(10)

10

Not null

读者姓名

BookId

Char(10)

10

Not null foreign key

图书编号

BorrowTime

Date

Not null

借书时间

BackTime

Date

Not null

归还时间

IfBack

Varchar(10)

10

Not null

是否归还

列名

数据类型

长度

约束

说明

ReaderId

Char(10)

10

Not null primary key

读者编号

ReaderName

Varchar(10)

10

Not null

读者姓名

BookId

Char(10)

10

Not null foreign key

图书编号

BackTime

Date

Not null

归还时间

列名

数据类型

长度

约束

说明

ReaderId

Char(10)

10

Not null primary key

读者编号

ReaderName

Varchar(10)

10

Not null

姓名

Sex

Varchar(10)

10

Not null

性别

Vocation

Varchar(30)

30

Not null

职业

Birthday

Date

Not null

出生日期

Identify

Varchar(20)

20

Not null

证件号码

Tel

Varchar(20)

20

Not null

电话

Email

Varchar(20)

20

Not null

电子邮箱

数据库名

定义数据库语句

说明

library_lwl

create database library_lwl;

创建数据库

表名

定义表语句

说明

manager

create table manager(

Id char(10) not null primary key,

Name varchar(30) not null,

Password varchar(30) not null );

创建manager(管理员信息表)

bookinfo

create table bookinfo(

Id char(10) not null,

Bookname varchar(60) not null,

Author varchar(60) not null,

BookId char(10) not null primary key,

Price Decimal(10,2) not null,

Publish varchar(30) not null,

constraint bookinfo_fk foreign key (Id) references manager(Id) );

创建bookinfo(图书信息表)

booktype

create table booktype(

BookId char(10) not null primary key,

Bookname varchar(60) not null,

Quantity char(20) not null,

Type varchar(30) not null,

Shelf varchar(20) not null );

创建booktype(图书类型表)

bookborrow

create table bookborrow(

ReaderId char(10) not null primary key,

ReaderName varchar(10) not null,

BookId char(10) not null,

BorrowTime date not null,

BackTime date not null,

IfBack varchar(10) not null,

constraint bookborrow_fk foreign key (BookId) references bookinfo(BookId) );

创建bookborrow(图书借阅表)

bookback

create table bookback(

ReaderId char(10) not null primary key,

ReaderName varchar(10) not null,

BookId char(10) not null,

BackTime date not null,

constraint bookback_fk foreign key (BookId) references bookinfo(BookId) );

创建bookback(图书归还表)

readerinfo

create table readerinfo(

ReaderId char(10) not null primary key,

ReaderName varchar(10) not null,

Sex varchar(10) not null,

Vocation varchar(30) not null,

Birthday date not null,

Identify varchar(20) not null,

Tel varchar(20) not null,

Email varchar(20) not null );

创建readerinfo(读者信息表)

数据操纵类型

数据操纵语句

说明

查询数据

select * from bookborrow,readerinfo where readerinfo.ReaderId = 'R0001' and bookborrow.ReaderId = readerinfo.ReaderId;

查询读者编号为R0001的读者信息和图书借阅信息

插入数据

insert into manager values ('001', '李**', 'l123');

insert into manager values ('002', '董**', 'd123');

insert into bookinfo values ('001', 'MySql数据库', '林某某', '000001', 49.8, '人民邮电出版社');

insert into bookinfo values ('001', 'Java程序设计', '赵某某', '000002', 59.0, '机械工业出版社');

insert into booktype values ('000001', 'MySql数据库', 100, '编程书籍', '1号书架');

insert into booktype values ('000002', 'Java程序设计', '89', '编程书籍', '1号书架');

insert into bookborrow values ('R0001', '青玉案', '000001', '2022-10-23', '2022-12-10', '是');

insert into bookborrow values ('R0002', '北顾亭', '000001', '2022-10-24', '2022-12-10', '否');

insert into bookback values ('R0001', '青玉案', '000001','2022-12-10');

insert into bookback values ('R0002', '北顾亭', '000001','2022-12-10');

insert into readerinfo values ('R0001', '青玉案', '男', '学生', '2001-04-12', '411456', '1582476', '297@qq.com');

insert into readerinfo values ('R0002', '北顾亭', '男', '工人', '2001-11-23', '411423', '1352686', '225@qq.com');

在每张表里分别插入两条数据

修改数据

update bookinfo set Price = 59.8 where BookId in (select BookId from booktype where Bookname = "MySql数据库");

通过图书名称在类型表里查询图书编号，根据编号更改信息表里的图书价格

删除数据

delete from bookback where ReaderId = (select ReaderId from readerinfo where ReaderId = 'R0001');

根据从读者信息表里查找的读者编号在图书归还表里删除数据

视图名

定义视图和查询视图语句

说明

readerinfo_v

create view readerinfo_v (读者编号,姓名,性别,职业,出生日期,证件号码,电话,电子邮箱)as select ReaderId,ReaderName,Sex,Vocation,Birthday,Identify,Tel,Emailfrom readerinfo where Vocation = '学生';

select * from readerinfo_v;

创建视图readerinfo_v，字段名用中文表示，包含所有学生的读者编号、姓名、性别、职业、出生日期、证件号码、电话和电子邮箱

readerlist_v

create view readerlist_v (ReaderId,ReaderName,Bookname,BorrowTime,BackTime)as select readerinfo.ReaderId,readerinfo.ReaderName,Bookname,BorrowTime,BackTimefrom readerinfo,bookinfo,bookborrow where readerinfo.ReaderId = bookborrow.ReaderId and bookborrow.BookId = bookinfo.BookId;

select * from readerlist_v;

创建视图readerlist_v，包含读者编号，姓名，书名，借书时间和归还时间

索引名

定义索引语句

说明

manager_index

create index manager_index on manager(Name);

show index from manager;

对管理员信息表的管理员名称创建普通索引

bookinfo_index

create unique index bookinfo_index on bookinfo(Bookname);

show index from bookinfo;

对图书信息表中的Bookname创建唯一性索引

combine_index

create index combine_index on readerinfo(ReaderId,ReaderName,Tel);

show index from readerinfo;

对读者信息表中的读者编号、姓名和电话创建组合索引

存储过程名

定义及调用存储过程语句

说明

select_p

delimiter //

create procedure select_p(in Bid char(10))

begin

select * from bookinfo,bookborrow

where bookinfo.BookId = Bid and bookinfo.BookId = bookborrow.BookId;

end//

delimiter ;

call select_p('000001');

创建存储过程，通过输入图书编号来查询图书信息和图书借阅信息

update_p

delimiter //

create procedure update_p(in Rid char(10),in Rn varchar(10))

begin

update bookback set ReaderName = Rn where ReaderId = Rid;

end//

delimiter ;

call update_p('R0001',"浪淘沙");

创建存储过程，根据输入的读者编号来更改图书归还表中的读者姓名

interval_p

delimiter //

create procedure interval_p(in Date1 date,out Date2 integer)

begin

set Date2 = datediff(curdate(),Date1);

end//

delimiter ;

call interval_p('2022-10-23',@Date2);

select @Date2;

创建存储过程，通过读者借书时间与当前时间比较，得出实际借阅天数

compare_p

delimiter //

create procedure compare_p(in b1 char(10),in b2 char(10),out v varchar(30))

begin declare quantity1,quantity2 integer;

select quantity into quantity1 from booktype where BookId = b1;

select quantity into quantity2 from booktype where BookId = b2;

if quantity1>quantity2

then set v = "推荐您读第一本书！";

else set v = "推荐您读第二本书！" ;

end if;

end//

delimiter ;

call compare_p('000001','000002',@v);

select @v;

创建存储过程，通过比较两本图书的图书数量，来为读者推荐所读书籍

cursor_p

delimiter //

create procedure cursor_p()

begin

declare ReaderIds char(10);

declare BorrowTimes date;

declare BackTimes date;

declare IfBacks varchar(10);

declare infor_cursor cursor for select ReaderId,BorrowTime,BackTime,IfBack from bookborrow;

declare continue handler for not found set @cur=0;

set @cur=1;

open infor_cursor;

fetch infor_cursor into ReaderIds,BorrowTimes,BackTimes,IfBacks;

while @cur do

select ReaderIds,BorrowTimes,BackTimes,IfBacks;

fetch infor_cursor into ReaderIds,BorrowTimes,BackTimes,IfBacks;

end while;

close infor_cursor;

end//

delimiter ;

call cursor_p();

创建存储过程，使用fetch语句检索数据，并将游标指向从图书借阅表里查询出的结果集里

存储函数名

定义及调用存储函数语句

说明

count_fun

delimiter //

create function count_fun()

returns char(10)

deterministic

begin

declare sumc char(20);

select count(*) into sumc from bookinfo where Price >= 50;

return sumc;

end //

delimiter ;

select count_fun();

创建存储函数，返回图书价格不小于50的总数

total_fun

delimiter //

create function total_fun(Bid char(10))

returns decimal(10,2)

deterministic

begin

declare pri decimal(10,2);

declare qy char(20);

declare totals decimal(10,2);

select Price into pri from bookinfo where BookId = Bid;

select Quantity into qy from booktype where BookId = Bid;

set totals = pri * qy;

return totals;

end //

delimiter ;

select total_fun('000001');

创建存储函数，通过输入的图书编号来计算剩余书本的总价

select_fun

delimiter //

create function select_fun(Rid char(10))

returns char(10)

deterministic

begin

declare Rne char(10);

select ReaderName into Rne from readerinfo where ReaderId = Rid;

if Rne is null then

set Rne = "未找到该读者！";

return Rne;

else

return Rne;

end if;

end //

delimiter ;

select select_fun('R0001');

创建存储函数，通过输入的读者编号来查询读者姓名，如果为空返回"未找到该读者！"，否则返回读者姓名

compare_fun

delimiter //

create function compare_fun(Id1 char(10),Id2 char(10))

returns char(20)

DETERMINISTIC

begin

declare Rid1 char(10);

declare Rid2 char(10);

declare pce1 Decimal(10,2);

declare pce2 Decimal(10,2);

declare Bn varchar(60);

select Price into pce1 from bookinfo where BookId = Rid1;

select Price into pce2 from bookinfo where BookId = Rid2;

if pce1 < pce2 then

set Bn = "第一本书较便宜！";

else

set Bn = "第二本书较便宜！";

end if;

return Bn;

end//

delimiter ;

select compare_fun('000001','000002');

创建存储函数，通过输入两个图书编号来比较相应价格，为读者选择较便宜的书籍

pric_fun

delimiter //

create function pric_fun(Bid char(10))

returns decimal(10,2)

deterministic

begin

declare p decimal(10,2);

select Price into p from bookinfo where BookId=Bid;

return p;

end //

delimiter ;

select pric_fun('000001');

创建存储函数，通过输入图书编号来返回对应的图书价格

触发器名

定义及使用触发器语句

说明

update_trig

delimiter //

create trigger update_trig before update

on readerinfo for each row

begin

update bookborrow set ReaderName=new.ReaderName;

update bookback set ReaderName=new.ReaderName;

end //

delimiter ;

update readerinfo set ReaderName = "望海潮" where ReaderId = 'R0001';

select * from bookborrow where ReaderId = 'R0001';

select * from bookback where ReaderId = 'R0001';

创建触发器，修改读者信息表中的读者姓名时同时修改图书借阅表和图书归还表中的读者姓名

delete_trig

delimiter //

create trigger delete_trig after delete

on readerinfo for each row

begin

delete from bookborrow where ReaderId=old.ReaderId;

delete from bookback where ReaderId=old.ReaderId;

end //

delimiter ;

delete from readerinfo where ReaderId='R0002';

select * from bookborrow where ReaderId = 'R0002';

select * from bookback where ReaderId = 'R0002';

创建触发器，删除读者信息表中的读者信息时同时把借阅信息与归还信息全部删除

insert_trig

delimiter //

create trigger insert_trig before insert

on bookinfo for each row

begin

if new.Price < 20 then signal sqlstate 'HY000' set message_text = '价格不能低于20！';

end if ;

end //

delimiter ;

insert into bookinfo values ('001', '软件工程', '张某某', '000003', 19.8, '清华大学出版社');

创建触发器，限制插入数据，当图书价格低于20时插入失败并给出提示

drop_trig

delimiter //

create trigger drop_trig after delete

on bookback for each row

begin

if old.ReaderId = 'R0001' then signal sqlstate 'HY000' set message_text = '删除失败，你不是管理员！';

end if ;

end //

delimiter ;

delete from bookback where ReaderId = 'R0001';

创建触发器，删除图书归还表中的数据时提示“非管理员，删除失败”

事件名

定义事件语句

说明

update_event

set @@global.event_scheduler = true;

delimiter //

create event update_event on schedule every 2 year starts now()

do begin

update bookborrow set IfBack = "黑名单"

where datediff(curdate(),BorrowTime) > datediff(BackTime,BorrowTime)

and IfBack = "否";

end//

delimiter ;

select * from bookborrow;

创建事件，现在开始并每两年执行一次，把逾期未归还的读者的IfBack改为黑名单

clean_event

set @@global.event_scheduler = true;

create event clean_event on schedule

every 15 second ends '2022-12-13 16:10:00'

do truncate readerinfo;

select * from readerinfo;

创建事件，清空readerinfo表，现在开始并每15S执行一次，在规定时间终止

blacklist_event

set @@global.event_scheduler = true;

create table blacklist like bookborrow;

delimiter //

create event blacklist_event on schedule every 1 year starts now()

do begin

insert into blacklist select * from bookborrow

where datediff(curdate(),BorrowTime) > datediff(BackTime,BorrowTime)

and IfBack = "否";

end //

delimiter ;

select * from blacklist;

创建事件，现在开始并每一年执行一次，把逾期未归还的读者信息插入blacklist表中

delback_event

set @@global.event_scheduler = true;

delimiter //

create event delback_event on schedule every 1 year starts now()

do begin

delete from bookback

where ReaderId in (select ReaderId from bookborrow where IfBack = "黑名单");

end //

delimiter ;

select * from bookback;

创建事件，现在开始并每一年执行一次，把变为黑名单的图书归还表信息删掉

book_total

set @@global.event_scheduler = true;

create table book_total(counts int not null primary key);

delimiter //

create event counts_event on schedule every 1 year starts now()

do begin

insert into book_total select sum(Quantity) from booktype;

end//

delimiter ;

select * from book_total;

创建事件，现在开始并每一年执行一次，合计图书总数量

存储过程名

定义及调用存储过程语句

说明

delete_aff

delimiter //

create procedure delete_aff(in Rid char(10))

begin

declare exit handler for sqlexception rollback;

start transaction;

delete from bookborrow where bookborrow.ReaderId = Rid;

delete from bookback where bookback.ReaderId = Rid;

commit;

end //

delimiter ;

call delete_aff('R0002');

select * from bookborrow where ReaderId='R0002';

select * from bookback where ReaderId='R0002';

创建存储过程，在其中使用事务，当删除借阅表中的信息同时删除归还表的对应信息

insert_aff

delimiter //

create procedure insert_aff(in Bid char(10),in Bne varchar(60),in Qty char(20),in Tp varchar(30),in Sf varchar(20))

begin

declare exit handler for sqlexception rollback;

start transaction;

insert into booktype values(Bid,Bne,Qty,Tp,Sf);

commit;

end //

delimiter ;

call insert_aff('000003',"C语言程序设计",97,"编程书籍","3号书架");

select * from booktype where BookId=000003;

创建存储过程，在其中使用事务，向图书类型表中插入数据

update_aff

delimiter //

create procedure update_aff(in Rne varchar(10),in Rid char(10))

begin

declare exit handler for sqlexception rollback;

start transaction;

update readerinfo set ReaderName = Rne where ReaderId = Rid;

update bookborrow set ReaderName = Rne where ReaderId = Rid;

update bookback set ReaderName = Rne where ReaderId = Rid;

commit;

end //

delimiter ;

call update_aff("燕歌行",'R0002');

select * from readerinfo where ReaderId='R0002';

select * from bookborrow where ReaderId='R0002';

select * from bookback where ReaderId='R0002';

创建存储过程，在其中使用事务，当修改读者信息表中的读者姓名同时修改借阅表和归还表中的读者姓名

用户名

定义用户语句

权限分配与回收语句

说明

admin01

create user 'admin01'@'localhost' identified by '123456';

grant select on readerinfo to 'admin01'@'localhost';

grant update on bookinfo to 'admin01'@'localhost';

revoke update on bookinfo from 'admin01'@'localhost';

show grants for admin01@localhost;

创建用户，授予用户对读者信息表select权限，对图书信息表update权限。回收图书信息表update权限

admin02

create user 'admin02'@'localhost' identified by '123456';

grant all on manager to 'admin02'@'localhost' identified by '123456' with grant option;

revoke update,delete on manager from 'admin02'@'localhost';

show grants for admin02@localhost;

创建用户，授予用户对管理员信息表的所有权限，并允许授予其他用户。回收修改和删除权限

操作类型

对应操作的SQL语句

说明

备份

mysqldump -u root -p library_lwl >D:\library_lwl_backup.sql

使用mysqldump命令备份library_lwl数据库中的所有表到D盘

恢复

drop table bookback;

mysql -u root -p library_lwl

source D:\library_lwl_backup.sql

select * from bookback;

使用mysql或source命令将备份文件恢复到数据库

导出

select * from readerinfo into outfile 'D:/readerinfo.txt'

fields terminated by ' '

optionally enclosed by '"'

lines terminated by ';';

导出读者信息表的数据到D盘，字符用双引号标注，字段值间用空格隔开，每行以分号结束

导入

create table bk_readerinfo like readerinfo;

select * from bk_readerinfo;

load data infile 'D:/readerinfo.txt' into table bk_readerinfo

fields terminated by ' '

optionally enclosed by '"'

lines terminated by ';';

select * from bk_readerinfo;

创建bk_readerinfo表，将导出的数据导入到该表中