列名

數(shù)據(jù)類型

長(zhǎng)度

約束

說(shuō)明

Id

Char(10)

10

Not null primary key

ID編號(hào)

Name

Varchar(30)

30

Not null

管理員名稱

Password

Varchar(30)

30

Not null

管理員密碼

列名

數(shù)據(jù)類型

長(zhǎng)度

約束

說(shuō)明

Id

Char(10)

10

Not null foreign key

ID編號(hào)

Bookname

Varchar(60)

60

Not null

書(shū)名

Author

Varchar(60)

60

Not null

作者

BookId

Char(10)

10

Not null primary key

圖書(shū)編號(hào)

Price

Decimal(10,2)

30

Not null

價(jià)格

Publish

Varchar(30)

30

Not null

出版社

列名

數(shù)據(jù)類型

長(zhǎng)度

約束

說(shuō)明

BookId

Char(10)

10

Not null primary key

圖書(shū)編號(hào)

Bookname

Varchar(60)

60

Not null

書(shū)名

Quantity

Char(20)

20

Not null

數(shù)量

Type

Varchar(30)

30

Not null

類型

Shelf

Varchar(20)

20

Not null

所在書(shū)架

列名

數(shù)據(jù)類型

長(zhǎng)度

約束

說(shuō)明

ReaderId

Char(10)

10

Not null primary key

讀者編號(hào)

ReaderName

Varchar(10)

10

Not null

讀者姓名

BookId

Char(10)

10

Not null foreign key

圖書(shū)編號(hào)

BorrowTime

Date

Not null

借書(shū)時(shí)間

BackTime

Date

Not null

歸還時(shí)間

IfBack

Varchar(10)

10

Not null

是否歸還

列名

數(shù)據(jù)類型

長(zhǎng)度

約束

說(shuō)明

ReaderId

Char(10)

10

Not null primary key

讀者編號(hào)

ReaderName

Varchar(10)

10

Not null

讀者姓名

BookId

Char(10)

10

Not null foreign key

圖書(shū)編號(hào)

BackTime

Date

Not null

歸還時(shí)間

列名

數(shù)據(jù)類型

長(zhǎng)度

約束

說(shuō)明

ReaderId

Char(10)

10

Not null primary key

讀者編號(hào)

ReaderName

Varchar(10)

10

Not null

姓名

Sex

Varchar(10)

10

Not null

性別

Vocation

Varchar(30)

30

Not null

職業(yè)

Birthday

Date

Not null

出生日期

Identify

Varchar(20)

20

Not null

證件號(hào)碼

Tel

Varchar(20)

20

Not null

電話

Email

Varchar(20)

20

Not null

電子郵箱

數(shù)據(jù)庫(kù)名

定義數(shù)據(jù)庫(kù)語(yǔ)句

說(shuō)明

library_lwl

create database library_lwl;

創(chuàng)建數(shù)據(jù)庫(kù)

表名

定義表語(yǔ)句

說(shuō)明

manager

create table manager(

Id char(10) not null primary key,

Name varchar(30) not null,

Password varchar(30) not null );

創(chuàng)建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) );

創(chuàng)建bookinfo(圖書(shū)信息表)

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 );

創(chuàng)建booktype(圖書(shū)類型表)

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) );

創(chuàng)建bookborrow(圖書(shū)借閱表)

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) );

創(chuàng)建bookback(圖書(shū)歸還表)

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 );

創(chuàng)建readerinfo(讀者信息表)

數(shù)據(jù)操縱類型

數(shù)據(jù)操縱語(yǔ)句

說(shuō)明

查詢數(shù)據(jù)

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

查詢讀者編號(hào)為R0001的讀者信息和圖書(shū)借閱信息

插入數(shù)據(jù)

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

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

insert into bookinfo values ('001', 'MySql數(shù)據(jù)庫(kù)', '林某某', '000001', 49.8, '人民郵電出版社');

insert into bookinfo values ('001', 'Java程序設(shè)計(jì)', '趙某某', '000002', 59.0, '機(jī)械工業(yè)出版社');

insert into booktype values ('000001', 'MySql數(shù)據(jù)庫(kù)', 100, '編程書(shū)籍', '1號(hào)書(shū)架');

insert into booktype values ('000002', 'Java程序設(shè)計(jì)', '89', '編程書(shū)籍', '1號(hào)書(shū)架');

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', '青玉案', '男', '學(xué)生', '2001-04-12', '411456', '1582476', '297@qq.com');

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

在每張表里分別插入兩條數(shù)據(jù)

修改數(shù)據(jù)

update bookinfo set Price = 59.8 where BookId in (select BookId from booktype where Bookname = "MySql數(shù)據(jù)庫(kù)");

通過(guò)圖書(shū)名稱在類型表里查詢圖書(shū)編號(hào)，根據(jù)編號(hào)更改信息表里的圖書(shū)價(jià)格

刪除數(shù)據(jù)

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

根據(jù)從讀者信息表里查找的讀者編號(hào)在圖書(shū)歸還表里刪除數(shù)據(jù)

視圖名

定義視圖和查詢視圖語(yǔ)句

說(shuō)明

readerinfo_v

create view readerinfo_v (讀者編號(hào),姓名,性別,職業(yè),出生日期,證件號(hào)碼,電話,電子郵箱)as select ReaderId,ReaderName,Sex,Vocation,Birthday,Identify,Tel,Emailfrom readerinfo where Vocation = '學(xué)生';

select * from readerinfo_v;

創(chuàng)建視圖readerinfo_v，字段名用中文表示，包含所有學(xué)生的讀者編號(hào)、姓名、性別、職業(yè)、出生日期、證件號(hào)碼、電話和電子郵箱

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;

創(chuàng)建視圖readerlist_v，包含讀者編號(hào)，姓名，書(shū)名，借書(shū)時(shí)間和歸還時(shí)間

索引名

定義索引語(yǔ)句

說(shuō)明

manager_index

create index manager_index on manager(Name);

show index from manager;

對(duì)管理員信息表的管理員名稱創(chuàng)建普通索引

bookinfo_index

create unique index bookinfo_index on bookinfo(Bookname);

show index from bookinfo;

對(duì)圖書(shū)信息表中的Bookname創(chuàng)建唯一性索引

combine_index

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

show index from readerinfo;

對(duì)讀者信息表中的讀者編號(hào)、姓名和電話創(chuàng)建組合索引

存儲(chǔ)過(guò)程名

定義及調(diào)用存儲(chǔ)過(guò)程語(yǔ)句

說(shuō)明

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');

創(chuàng)建存儲(chǔ)過(guò)程，通過(guò)輸入圖書(shū)編號(hào)來(lái)查詢圖書(shū)信息和圖書(shū)借閱信息

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',"浪淘沙");

創(chuàng)建存儲(chǔ)過(guò)程，根據(jù)輸入的讀者編號(hào)來(lái)更改圖書(shū)歸還表中的讀者姓名

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;

創(chuàng)建存儲(chǔ)過(guò)程，通過(guò)讀者借書(shū)時(shí)間與當(dāng)前時(shí)間比較，得出實(shí)際借閱天數(shù)

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 = "推薦您讀第一本書(shū)！";

else set v = "推薦您讀第二本書(shū)！" ;

end if;

end//

delimiter ;

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

select @v;

創(chuàng)建存儲(chǔ)過(guò)程，通過(guò)比較兩本圖書(shū)的圖書(shū)數(shù)量，來(lái)為讀者推薦所讀書(shū)籍

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();

創(chuàng)建存儲(chǔ)過(guò)程，使用fetch語(yǔ)句檢索數(shù)據(jù)，并將游標(biāo)指向從圖書(shū)借閱表里查詢出的結(jié)果集里

存儲(chǔ)函數(shù)名

定義及調(diào)用存儲(chǔ)函數(shù)語(yǔ)句

說(shuō)明

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();

創(chuàng)建存儲(chǔ)函數(shù)，返回圖書(shū)價(jià)格不小于50的總數(shù)

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');

創(chuàng)建存儲(chǔ)函數(shù)，通過(guò)輸入的圖書(shū)編號(hào)來(lái)計(jì)算剩余書(shū)本的總價(jià)

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');

創(chuàng)建存儲(chǔ)函數(shù)，通過(guò)輸入的讀者編號(hào)來(lái)查詢讀者姓名，如果為空返回"未找到該讀者！"，否則返回讀者姓名

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 = "第一本書(shū)較便宜！";

else

set Bn = "第二本書(shū)較便宜！";

end if;

return Bn;

end//

delimiter ;

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

創(chuàng)建存儲(chǔ)函數(shù)，通過(guò)輸入兩個(gè)圖書(shū)編號(hào)來(lái)比較相應(yīng)價(jià)格，為讀者選擇較便宜的書(shū)籍

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');

創(chuàng)建存儲(chǔ)函數(shù)，通過(guò)輸入圖書(shū)編號(hào)來(lái)返回對(duì)應(yīng)的圖書(shū)價(jià)格

觸發(fā)器名

定義及使用觸發(fā)器語(yǔ)句

說(shuō)明

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';

創(chuàng)建觸發(fā)器，修改讀者信息表中的讀者姓名時(shí)同時(shí)修改圖書(shū)借閱表和圖書(shū)歸還表中的讀者姓名

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';

創(chuàng)建觸發(fā)器，刪除讀者信息表中的讀者信息時(shí)同時(shí)把借閱信息與歸還信息全部刪除

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 = '價(jià)格不能低于20！';

end if ;

end //

delimiter ;

insert into bookinfo values ('001', '軟件工程', '張某某', '000003', 19.8, '清華大學(xué)出版社');

創(chuàng)建觸發(fā)器，限制插入數(shù)據(jù)，當(dāng)圖書(shū)價(jià)格低于20時(shí)插入失敗并給出提示

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';

創(chuàng)建觸發(fā)器，刪除圖書(shū)歸還表中的數(shù)據(jù)時(shí)提示“非管理員，刪除失敗”

事件名

定義事件語(yǔ)句

說(shuō)明

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;

創(chuàng)建事件，現(xiàn)在開(kāi)始并每?jī)赡陥?zhí)行一次，把逾期未歸還的讀者的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;

創(chuàng)建事件，清空readerinfo表，現(xiàn)在開(kāi)始并每15S執(zhí)行一次，在規(guī)定時(shí)間終止

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;

創(chuàng)建事件，現(xiàn)在開(kāi)始并每一年執(zhí)行一次，把逾期未歸還的讀者信息插入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;

創(chuàng)建事件，現(xiàn)在開(kāi)始并每一年執(zhí)行一次，把變?yōu)楹诿麊蔚膱D書(shū)歸還表信息刪掉

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;

創(chuàng)建事件，現(xiàn)在開(kāi)始并每一年執(zhí)行一次，合計(jì)圖書(shū)總數(shù)量

存儲(chǔ)過(guò)程名

定義及調(diào)用存儲(chǔ)過(guò)程語(yǔ)句

說(shuō)明

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';

創(chuàng)建存儲(chǔ)過(guò)程，在其中使用事務(wù)，當(dāng)刪除借閱表中的信息同時(shí)刪除歸還表的對(duì)應(yīng)信息

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語(yǔ)言程序設(shè)計(jì)",97,"編程書(shū)籍","3號(hào)書(shū)架");

select * from booktype where BookId=000003;

創(chuàng)建存儲(chǔ)過(guò)程，在其中使用事務(wù)，向圖書(shū)類型表中插入數(shù)據(jù)

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';

創(chuàng)建存儲(chǔ)過(guò)程，在其中使用事務(wù)，當(dāng)修改讀者信息表中的讀者姓名同時(shí)修改借閱表和歸還表中的讀者姓名

用戶名

定義用戶語(yǔ)句

權(quán)限分配與回收語(yǔ)句

說(shuō)明

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;

創(chuàng)建用戶，授予用戶對(duì)讀者信息表select權(quán)限，對(duì)圖書(shū)信息表update權(quán)限。回收?qǐng)D書(shū)信息表update權(quán)限

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;

創(chuàng)建用戶，授予用戶對(duì)管理員信息表的所有權(quán)限，并允許授予其他用戶。回收修改和刪除權(quán)限

操作類型

對(duì)應(yīng)操作的SQL語(yǔ)句

說(shuō)明

備份

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

使用mysqldump命令備份library_lwl數(shù)據(jù)庫(kù)中的所有表到D盤(pán)

恢復(fù)

drop table bookback;

mysql -u root -p library_lwl <D:\library_lwl_backup.sql

source D:\library_lwl_backup.sql

select * from bookback;

使用mysql或source命令將備份文件恢復(fù)到數(shù)據(jù)庫(kù)

導(dǎo)出

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

fields terminated by ' '

optionally enclosed by '"'

lines terminated by ';';

導(dǎo)出讀者信息表的數(shù)據(jù)到D盤(pán)，字符用雙引號(hào)標(biāo)注，字段值間用空格隔開(kāi)，每行以分號(hào)結(jié)束

導(dǎo)入

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;

創(chuàng)建bk_readerinfo表，將導(dǎo)出的數(shù)據(jù)導(dǎo)入到該表中