图书、读者、借阅信息的相关管理、信息查询

数据库设计主要涉及到图书、读者、借阅信息的相关管理、信息查询项目。背景介绍：随着科学技术的发展，校园的图书管理也日趋完善，本次的数据库设计主要涉及到图书、读者、借阅信息的相关管理、信息查询。任务概述

现有图书管理数据库的三个关系模式：

图书（总编号，分类号，书名，作者，出版单位，单价）

读者（借书证号，单位，姓名，性别，职称，地址）

借阅（借书证号，总编号，借书日期）

(1).利用MySQL语句创建图书管理库和图书、读者和借阅三个基本的表结构；

(2).利用MySQL 插入工具在三个表中分别插入以下所给元组：

图书：

读者：

借阅：

用SQL语句完成下列查询：

(1). 创建图书管理库和图书、读者和借阅三个基本的表结构；

-- 创建数据库（如果尚未创建）  
create database if not exists library;  
-- 确保在library数据库中操作  
use library;  
  
create table 图书 (  
    总编号 varchar(20) primary key,  
    分类号 varchar(20) not null,  
    书名 varchar(100) not null,  
    作者 varchar(50) not null,  
    出版单位 varchar(100) not null,  
    单价 decimal(10, 2) not null  
);  
  
-- 创建读者表，包含字段约束  
create table if not exists 读者 (  
    借书证号 varchar(20) not null primary key,  -- 假设借书证号是唯一的  
    单位 varchar(100) not null,                 -- 单位名称不能为空  
    姓名 varchar(50) not null,                  -- 姓名不能为空  
    性别 varchar(10) not null,                  -- 性别不能为空，实际应用中可能需要更严格的约束，如'M'/'F'  
    职称 varchar(50) not null,                  -- 职称不能为空  
    地址 varchar(200) not null                  -- 地址不能为空  
);  
  
-- 创建借阅表，包含字段约束  
create table if not exists 借阅 (  
    借书证号 varchar(20) not null,           -- 假设借书证号与读者表中的借书证号关联，但不作为主键（因为可能有多条借阅记录）  
    总编号 varchar(20) not null,             -- 总编号与图书表中的总编号关联  
    借书日期 date not null,                  -- 借书日期不能为空，且必须是日期类型  
    primary key (借书证号, 总编号, 借书日期), -- 复合主键，确保每条借阅记录的唯一性  
    foreign key (借书证号) references 读者(借书证号), -- 外键约束，确保借书证号在读者表中存在  
    foreign key (总编号) references 图书(总编号)      -- 外键约束，确保总编号在图书表中存在  
);

录入数据：

-- 插入图书表数据  
insert into 图书 (总编号, 分类号, 书名, 作者, 出版单位, 单价) values  
('445501', 'TP3/12', '数据库导论', '王强', '科学出版社', 17.90),  
('445502', 'TP3/12', '数据库导论', '王强', '科学出版社', 17.90),  
('445503', 'TP3/12', '数据库导论', '王强', '科学出版社', 17.90),  
('332211', 'TP5/10', '计算机基础', '李伟', '高等教育出版社', 18.00),  
('112266', 'TP3/12', 'FoxBASE', '张三', '电子工业出版社', 23.60),  
('665544', 'TS7/21', '高等数学', '刘明', '高等教育出版社', 20.00),  
('114455', 'TR9/12', '线性代数', '孙业', '北京大学出版社', 20.80),  
('113388', 'TR7/90', '大学英语', '胡玲', '清华大学出版社', 12.50),  
('446601', 'TP4/13', '数据库基础', '马凌云', '人民邮电出版社', 22.50),  
('446602', 'TP4/13', '数据库基础', '马凌云', '人民邮电出版社', 22.50),  
('446603', 'TP4/13', '数据库基础', '马凌云', '人民邮电出版社', 22.50),  
('449901', 'TP4/14', 'FoxPro大全', '周虹', '科学出版社', 32.70),  
('449902', 'TP4/14', 'FoxPro大全', '周虹', '科学出版社', 32.70),  
('118801', 'TP4/15', '计算机网络', '黄力钧', '高等教育出版社', 21.80),  
('118802', 'TP4/15', '计算机网络', '黄力钧', '高等教育出版社', 21.80);  
  
-- 插入读者表数据  
insert into 读者 (借书证号, 单位, 姓名, 性别, 职称, 地址) values  
('111', '信息系', '王维利', '女', '教授', '1号楼424'),  
('112', '财会系', '李立', '男', '副教授', '2号楼316'),  
('113', '经济系', '张三', '男', '讲师', '3号楼305'),  
('114', '信息系', '周华发', '男', '讲师', '1号楼316'),  
('115', '信息系', '赵正义', '男', '工程师', '1号楼224'),  
('116', '信息系', '李明', '男', '副教授', '1号楼318'),  
('117', '计算机系', '李小峰', '男', '助教', '1号楼214'),  
('118', '计算机系', '许鹏飞', '男', '助工', '1号楼216'),  
('119', '计算机系', '刘大龙', '男', '教授', '1号楼318'),  
('120', '国际贸易', '李雪', '男', '副教授', '4号楼506'),  
('121', '国际贸易', '李爽', '女', '讲师', '4号楼510'),  
('122', '国际贸易', '王纯', '女', '讲师', '4号楼512'),  
('123', '财会系', '沈小霞', '女', '助教', '2号楼202'),  
('124', '财会系', '朱海', '男', '讲师', '2号楼210'),  
('125', '财会系', '马英明', '男', '副教授', '2号楼212');


-- 插入借阅表数据  
insert into 借阅 (借书证号, 总编号, 借书日期) values  
('112', '445501', '1997-03-19'),  
('125', '332211', '1997-02-12'),  
('111', '445503', '1997-08-21'),  
('112', '112266', '1997-03-14'),  
('114', '665544', '1997-10-21'),   
('120', '114455', '1997-11-02'),  
('120', '118801', '1997-10-18'),  
('119', '446603', '1997-12-12'),  
('112', '449901', '1997-10-23'),  
('115', '449902', '1997-08-21'),  
('118', '118801', '1997-09-10');

预览：

(2). 查找出姓李的读者姓名和所在单位；

select 姓名, 单位 from 读者 where 姓名 like '李%';

运行结果：

(3). 查找出高等教育出版社的所有图书及单价，结果按单价降序排序；

select 书名, 单价 from 图书 where 出版单位 = '高等教育出版社' order by 单价 desc;

运行结果：

(4). 查找书名以计算机打头的所有图书和作者；

select 书名, 作者 from 图书 where 书名 like '计算机%';

运行结果：

(5). 查找所有借了书的读者的读者的姓名及所在单位；

select distinct r.姓名, r.单位 from 读者 r join 借阅 b on r.借书证号 = b.借书证号;

运行结果：

(6). 查找1997年10月以后借书的读者借书证号、姓名和单位；

select b.借书证号, r.姓名, r.单位 from 借阅 b join 读者 r on b.借书证号 = r.借书证号 where b.借书日期 > '1997-10-01';

运行结果：

(7). 找出与赵正义在同一天借书的读者姓名、所在单位及借书日期；

select r.姓名, r.单位, b.借书日期 from 借阅 b join 读者 r on b.借书证号 = r.借书证号 where b.借书日期 = (select 借书日期 from 借阅 where 借书证号 = (select 借书证号 from 读者 where 姓名 = '赵正义'));

运行结果：

(8). 求科学出版社图书的最高单价、最低单价、平均单价；

select max(单价) as 最高单价, min(单价) as 最低单价, avg(单价) as 平均单价 from 图书 where 出版单位 = '科学出版社';

运行结果：

(9). 求出各个出版社图书的最高价格、最低价格和册数；

select 出版单位, max(单价) as 最高价格, min(单价) as 最低价格, count(*) as 册数 from 图书 group by 出版单位;

运行结果：