借阅者信息、借书信息与图书信息的相关查询与编辑

项目背景介绍本次的数据库设计主要涉及到借阅者信息、借书信息与图书信息的相关查询与编辑。任务概述具体关系数据库：图书管理库含数据表如下：创建如下三个基本表，表结构如下：

BORROWER：

借书证号　　　姓名　　系名　　班级

03001　　　　赵垒　　信管系　　03－1

03002　　　　刘诚　　信管系　　03－1

03101　　　　陈林　　计算机　　03－2

......

LOANS:

借书证号　　图书登记号　　借书日期

03001　　　　T00101　　　　2004.04.01

03002　　　　T00102　　　　2004.05.01

03001　　　　T00201　　　　2004.05.31

.......

BOOKS:

索书号　　书名　　　　作者　　　图书登记号　　出版社　　价格

TP311.13　数据结构　　李卫　　　T00101　　　　科学　　　19.00

TP311.13　数据结构　　李卫　　　T00102　　　　科学　　　19.00

TP.065　　数据结构导论　李卫　　T00201　　　　北航　　　16.50

TP.1599　 数据通信　　　杨志　 　T00301　　　 清华　　　28.50

请用SQL语句完成以下功能： 

(1). 创建数据库：图书管理库，并创建三个数据表

create database 图书管理库;  
use 图书管理库;  
create table borrower (  
    借书证号 char(6) primary key,  
    姓名 varchar(20),  
    系名 varchar(20),  
    班级 char(5)  
);  
  
create table loans (  
    借书证号 char(6),  
    图书登记号 char(6),  
    借书日期 date,  
    primary key (借书证号, 图书登记号),  
    foreign key (借书证号) references borrower(借书证号)  
);  
  
create table books (  
    索书号 char(8),  
    书名 varchar(50),  
    作者 varchar(20),  
    图书登记号 char(6) primary key,  
    出版社 varchar(20),  
    价格 decimal(5, 2)  
);

预览效果：

(2). 向已建的三个表插入上述记录

insert into borrower values ('03001', '赵垒', '信管系', '03-1');  
insert into borrower values ('03002', '刘诚', '信管系', '03-1');  
insert into borrower values ('03101', '陈林', '计算机', '03-2');  
  
insert into loans values ('03001', 'T00101', '2004-04-01');  
insert into loans values ('03002', 'T00102', '2004-05-01');  
insert into loans values ('03001', 'T00201', '2004-05-31');  
  
insert into books values ('TP311.13', '数据结构', '李卫', 'T00101', '科学', 19.00);  
insert into books values ('TP311.13', '数据结构', '李卫', 'T00102', '科学', 19.00);  
insert into books values ('TP.065', '数据结构导论', '李卫', 'T00201', '北航', 16.50);  
insert into books values ('TP.1599', '数据通信', '杨志', 'T00301', '清华', 28.50);

(3). 检索借了5本书以下的学生的借书证号，姓名，系名和借书数量。

select b.借书证号, b.姓名, b.系名, count(l.图书登记号) as 借书数量  
from borrower b  
left join loans l on b.借书证号 = l.借书证号  
group by b.借书证号, b.姓名, b.系名  
having count(l.图书登记号) < 5;

运行结果：

(4). 检索借书和赵垒同学所借书中的任意一本相同的学生的姓名，系名，书名，借书日期。

select distinct b.姓名, b.系名, bk.书名, l.借书日期  
from borrower b  
join loans l on b.借书证号 = l.借书证号  
join books bk on l.图书登记号 = bk.图书登记号  
where l.图书登记号 in (  
    select 图书登记号  
    from loans  
    where 借书证号 = '03001'  
);

运行结果：

(5). 建立信管系学生借书的视图SB，该视图的属性列由借书证号，姓名，班级，图书登记号，书号，出版社和借书日期组成

create view sb as  
select b.借书证号, b.姓名, b.班级, l.图书登记号, bk.索书号, bk.出版社, l.借书日期  
from borrower b  
join loans l on b.借书证号 = l.借书证号  
join books bk on l.图书登记号 = bk.图书登记号  
where b.系名 = '信管系';

运行结果：

(6). 检索出借阅《数据结构》这本书的读者个人信息及借书日期

select b.*, l.借书日期  
from borrower b  
join loans l on b.借书证号 = l.借书证号  
join books bk on l.图书登记号 = bk.图书登记号  
where bk.书名 = '数据结构';

运行结果：