实验二图书信息管理系统数据查询与数据更新

create table 图书信息管理表

(

    图书编号 char(15)          not null primary key,

    类别编号 char(10)          null,

    书名     varchar(50)       not null,

    标准书号 char(15)          not null,

    作者     varchar(50)       not null,

    出版社   varchar(20)       not null,

    出版日期 date not null,

    图书价格 numeric(10, 2)    not null,

    简介     varchar(100)      null,

    图书存量 tinyint default 1 not null,

);

create table 借书证表

(

    借书证号       char(7)                not null primary key,

    借阅者         varchar(50)            not null,

    发证日期       datetime               not null,

    是否有效       char       default 'Y' not null,

    现借书数量     numeric(2) default 0   not null,

    最大允许借书数 numeric(2) default 3   not null

);

create table 学生信息表

(

    学号     char(11)                                           not null primary key,

    借书证号 char(7)                                            null,

    姓名     varchar(50)                                        not null,

    密码     char(6)                                            not null,

    性别     char(2) check (性别 = '男' or 性别 = '女') not null,

    生日     date                                               not null,

    联系电话 varchar(15)                                        null,

    所在班级 char(10)                                           not null,

);

create table 班级信息表

(

    班级编号 char(10)    not null primary key,

    班级名称 varchar(50) not null,

    专业名称 varchar(50) not null,

    所属系部 varchar(50) not null,

    入学学年 int         not null,

);

create table 借阅信息表

(

    图书编号   char(15)                   not null,

    借书证号   char(7)                    not null,

    借阅日期   datetime                   not null,

    应还书日期 datetime                   not null,

    是否逾期   char           default 'N' not null,

    续借次数   tinyint        default 0   not null,

    罚金       numeric(10, 2) default 0   not null,

    primary key (图书编号, 借书证号, 借阅日期)

);

create table 图书类别信息表

(

    类别编号 char(10)    not null primary key,

    类别名称 varchar(50) not null,

);

添加外键并且设置为级联：

alter table 图书信息管理表

    add constraint 图书信息管理表_图书类别信息表_类别编号_fk

        foreign key (类别编号) references 图书类别信息表 (类别编号)

            on update cascade on delete cascade;



alter table 学生信息表

    add constraint 学生信息表_班级信息表_班级编号_fk

        foreign key (所在班级) references 班级信息表 (班级编号)

            on update cascade on delete cascade;



alter table 学生信息表

    add constraint 学生信息表_借书证表_借书证号_fk

        foreign key (借书证号) references 借书证表 (借书证号)

            on update cascade on delete cascade;



alter table 借阅信息表

    add constraint 借阅信息表_图书信息管理表_图书编号_fk

        foreign key (图书编号) references 图书信息管理表 (图书编号)

            on update cascade on delete cascade;



alter table 借阅信息表

    add constraint 借阅信息表_借书证表_借书证号_fk

        foreign key (借书证号) references 借书证表 (借书证号)

            on update cascade on delete cascade;

添加数据：

insert into 图书信息表(图书编号, 书名, 标准书号, 作者, 出版社, 出版日期, 图书价格,图书存量)

values ('B0001','数据库系统原理','00001','不知道','人民出版社','2002-04-06','39.20',10);


insert into 班级信息表 values ('00001','网络工程1班','网络工程','计算机学院',2022);


insert into 学生信息表(学号, 姓名, 密码, 性别, 生日, 联系电话, 所在班级)

values('22215220121','雷燚辉','123456','男','2002-12-02','12345678910','00001');

1.2 在图书表里面的B0001的记录中，作者信息更改为“阿历克斯”，图书的价格为50.5元。修改上题中添加的学生信息，密码为Ab12@@,联系电话为，18812345688。

update 图书信息表 set 作者 = '阿历克斯',图书价格=50.5 where 图书编号 ='B0001';


update 学生信息表 set 密码 = 'Ab12@@',联系电话='18812345688' where 学号='22215220121';

1.3 删除学生信息表中，上题修改学生的记录。

delete from 学生信息表 where 学号='22215220121';

2. 数据查询

请针对以下业务的具体需求，对上述参考表及其数据做对应查询。

插入数据

代码如下：(从大佬那边扒过来的)

insert into 图书类别信息表 values('T0001','计算机');
insert into 图书类别信息表 values('T0002','历史');
insert into 图书类别信息表 values('T0003','人文');
insert into 图书类别信息表 values('T0004','经济');
insert into 图书类别信息表 values('T0005','体育');
insert into 图书类别信息表 values('T0006','军事');


insert into 图书信息管理表 values('B0002','T0001','大数据时代','9787218122107','吉格斯','浙江人民出版社','2023-4-15',26.5,'央视推荐书籍',5);
insert into 图书信息管理表 values('B0003','T0001','正在来到的数字革命','9787218122108','徐子平','广西师范大学出版社','2019-06-19',42.1,'大数据深刻影响人类',3);
insert into 图书信息管理表 values('B0004','T0002','拖拖拉拉影响前途','9787218122109','安计划','东软电子出版社','2023-08-03',54.5,'青少年推荐读物',3);
insert into 图书信息管理表 values('B0005','T0002','明朝那些事','9787218122110','朱小青','清华出版社','2023-07-03',34.5,'青少年推荐读物',2);
insert into 图书信息管理表 values('B0006','T0002','chatGPT','9787218122111','魏征','机械工业出版社','2023-01-07',63.5,'青少年推荐读物',3);
insert into 图书信息管理表 values('B0007','T0003','中国崛起','9787218122112','吕小国','清华出版社','2023-02-17',33.5,'爱国主题优秀书籍',2);
insert into 图书信息管理表 values('B0008','T0003','宗教起源','9787218122113','本尼','广东人民出版社','2022-05-17',42.0,'趣味世界书籍',1);
insert into 图书信息管理表 values('B0009','T0001','AIGC','9787218122114','爱思考','广东人民出版社','2023-05-17',47.0,'趣味世界书籍',15);
insert into 图书信息管理表 values('B0010','T0001','数据化决策','9787218122106','道格拉斯','广东人民出版社','2019-12-13',34.5,'麻省理工学院指定教材',6);


insert into 班级信息表 values('C0001','21软件大数据1班','软件工程','计算机系',2021);
insert into 班级信息表 values('C0002','21软件大数据2班','软件工程','计算机系',2021);
insert into 班级信息表 values('C0003','21软件大数据3班','软件工程','计算机系',2021);
insert into 班级信息表 values('C0004','21软件1班','软件工程','计算机系',2021);
insert into 班级信息表 values('C0005','21软件2班','软件工程','计算机系',2021);
insert into 班级信息表 values('C0006','19会计1班','工商管理','信息管理系',2023);
insert into 班级信息表 values('C0007','19外贸英语1班','商务英语','外国语系',2023);


insert into 借书证表 values('L0001','余骏昌','2021-09-01','Y',0,3);
insert into 借书证表 values('L0002','朱威','2021-09-01','Y',0,3);
insert into 借书证表 values('L0003','徐广声','2021-09-01','Y',0,3);
insert into 借书证表 values('L0004','潘奕寰','2021-05-02','Y',0,3);
insert into 借书证表 values('L0005','战美','2021-05-02','Y',0,3);
insert into 借书证表 values('L0006','邹思柔','2021-09-01','Y',0,3);
insert into 借书证表 values('L0007','黄小宇','2021-09-01','Y',0,3);
insert into 借书证表 values('L0008','梁帅峰','1999-06-22','Y',0,3);
insert into 借书证表 values('L0009','林武文','1999-08-23','Y',0,3);
insert into 借书证表 values('L0010','曾紫怡','2023-09-20','Y',0,3);
insert into 借书证表 values('L0011','卢君羽','2023-09-20','Y',0,3);
insert into 借书证表 values('L0012','林小娜','2021-07-05','Y',0,3);
insert into 借书证表 values('L0013','庞勇斌','2021-07-05','Y',0,3);


insert into 学生信息表 values('21210120113','L0001','余骏昌','123456','男','1995-12-23','18988567873','C0001');
insert into 学生信息表 values('21210120114','L0002','朱威','123456','男','1996-08-03','18988567873','C0001');
insert into 学生信息表 values('21210120115','L0003','徐广声','123456','男','1995-09-02','18988567873','C0002');
insert into 学生信息表 values('21210120116','L0004','潘奕寰','123456','男','1995-02-20','18988567873','C0002');
insert into 学生信息表 values('21210120117','L0005','战美','123456','男','1996-10-13','18988567873','C0003');
insert into 学生信息表 values('21210120118','L0006','邹思柔','123456','女','1996-02-05','18988567873','C0003');
insert into 学生信息表 values('21210120119','L0007','黄小宇','123456','女','1996-09-08','18988567873','C0003');
insert into 学生信息表 values('21210120120','L0008','梁帅峰','123456','男','1993-09-09','18988567873','C0003');
insert into 学生信息表 values('21210120121','L0009','林武文','123456','男','1993-09-16','18988567873','C0004');
insert into 学生信息表 values('21210120122','L0010','曾紫怡','123456','女','1995-07-15','18988567873','C0004');
insert into 学生信息表 values('21210120123','L0011','李晓君','123456','女','1995-06-08','18988567873','C0004');
insert into 学生信息表 values('21210120124','L0012','林小娜','123456','女','1996-07-02','18988567873','C0005');
insert into 学生信息表 values('21210120125','L0013','庞勇斌','123456','男','1996-08-13','18988567873','C0006');


insert into 借阅信息表 values('B0001','L0002','2023-09-17','2023-10-17','N',0,0);
insert into 借阅信息表 values('B0003','L0001','2023-10-02','2012-07-27','N',0,0);
insert into 借阅信息表 values('B0004','L0008','2023-08-03','2015-07-27','N',3,0);
insert into 借阅信息表 values('B0005','L0009','2023-06-04','2023-07-27','N',0,0);
insert into 借阅信息表 values('B0006','L0002','2023-07-05','2023-08-27','Y',0,15.5);
insert into 借阅信息表 values('B0007','L0011','2023-08-06','2023-09-27','N',0,0);
insert into 借阅信息表 values('B0008','L0012','2023-09-08','2005-09-27','N',0,0);
insert into 借阅信息表 values('B0009','L0004','2023-10-09','2027-09-27','N',0,0);
insert into 借阅信息表 values('B0001','L0005','2023-09-03','2010-09-27','N',0,0);
insert into 借阅信息表 values('B0002','L0006','2023-08-07','2023-09-27','N',0,0);

2.1 单表查询

1. 查询所有图书的基本信息

select * from 图书信息管理表;

2. 查询所有图书的编号、名称和价格

select 图书编号,书名,图书价格 from 图书信息管理表;

3. 查询计算机系学生读者的借书证号、姓名和联系电话

select *from 学生信息表

where 所在班级 in(select 班级编号 from 班级信息表 where 所属系部='计算机系');

4. 查询学生借书证号为L0001且所借图书编号为B0003的借阅记录信息

select * from 借阅信息表 where 借书证号='L0001' and 图书编号 ='B0003';

5. 查询t_borrow表未还书的记录(假设当前的日期是2022-10-28号)。

select * from 借阅信息表 where 应还书日期 >'2022-10-28';

6. 查询借阅日期“1998-02-06”至“2021-12-06”的借书记录信息

select * from 借阅信息表 where  借阅日期 between '1998-02-06' and'2021-12-06';

7. 查询姓徐的学生读者的基本信息

select * from 学生信息表 where 姓名 like '徐%';

2.2对查询结果进行编辑

1. 查询所有曾经借书学生的姓名和所在院系，输出结果的字段名是：姓名和所在院系。

select 学生信息表.姓名,班级信息表.所属系部 

from 学生信息表 

join 班级信息表 on 学生信息表.所在班级=班级信息表.班级编号 

where 学生信息表.借书证号 in(select 借书证号 from 借阅信息表);

2. 查询借书证号为L0002的读者所借图书至今的天数总和。

select sum(DATEDIFF(day,借阅日期,GETDATE())) 至今天数 
from 借阅信息表 where借书证号 = 'L0002';

3. 查询借书证号为L0003的读者一共借了多少本书（同一本书多次续借的话，仅统计一次）。

select distinct count(*) 借书数量 from 借阅信息表 where 借书证号 ='L0002';

4. 查询所有图书信息，结果按价格的降序排列。

 select * from 图书信息管理表 order by  图书价格 desc;

5. 统计图书信息表中不同出版社的图书的数目，把统计结果大于或等于2的记录输出。

select 出版社,count(*) 数量 from 图书信息管理表  group by 出版社 having
        count(*)>=2;

6. 查询所有图书的库存总量。

select 书名,图书存量 from 图书信息管理表;

7. 输出借阅过期的借阅记录，并且输出的罚金翻倍。

select 图书编号, 借书证号, 借阅日期, 应还书日期, 是否逾期, 续借次数,
       罚金*2 from 借阅信息表 where 应还书日期<GETDATE();

8. 持有L0002的读者已经缴纳了全部的罚金，请把对应借书记录的罚金更改为0，然后把续借的数量增加1，把过期记录的“是否过期”属性修改为“N”。

update 借阅信息表 set 罚金=0,续借次数=续借次数+1,
                      是否逾期 ='N' where 借书证号='L0002';
select * from 借阅信息表 where  借书证号='L0002';

2.3连接查询

1. 采用等值连接的方式查询学生读者的学号、借书证号、姓名、联系电话以及所借书的图书编号，借阅日期。

select 学生信息表.学号,
       学生信息表.借书证号,
       学生信息表.姓名,
       学生信息表.联系电话,
       借阅信息表.图书编号,
       借阅信息表.借阅日期
from   学生信息表,借阅信息表
where 学生信息表.借书证号=借阅信息表.借书证号;

2. 采用内连接的方法查询图书信息以及对应图书类别的详细信息。

select 图书信息管理表.图书编号,书名, 书名, 作者, 图书价格,
       图书类别信息表.类别编号, 类别名称 from 图书信息管理表 , 图书类别信息表
where 图书信息管理表.类别编号=图书类别信息表.类别编号;

3. 查询学生读者借书证号、姓名、班级以及所借图书的图书编号、借阅日期，没有借阅记录的学生也要输出。

select 学生信息表.借书证号,姓名,所在班级,借阅信息表.图书编号,借阅日期
from 学生信息表 left join 借阅信息表 on 学生信息表.借书证号=借阅信息表.借书证号;

4. 查询借书信息表中读者的借书证号、图书编号、借阅日期以及所借图书在图书信息表中对应的出版社编号，图书信息表没有连接成功的记录也要输出。

select 借阅信息表.借书证号,借阅日期,图书信息管理表.图书编号,出版社
from 借阅信息表 left join 图书信息管理表 on 借阅信息表.图书编号=图书信息管理表.图书编号;

5. 查询在被罚款的同学的学号、姓名、电话、班级名称、借书证号、借书证发证日期、图书编号、图书名称、应还书日期、罚金数量。

select 学生信息表.学号,姓名,联系电话,
       班级信息表.班级名称,
       借书证表.借书证号,发证日期,
       图书信息管理表.图书编号,书名,
       借阅信息表.应还书日期,罚金
from 学生信息表
         left join 借阅信息表 on 借阅信息表.借书证号=学生信息表.借书证号
         left join 图书信息管理表 on 借阅信息表.图书编号 = 图书信息管理表.图书编号
         left join 借书证表 on 借阅信息表.借书证号 = 借书证表.借书证号
         left join 班级信息表 on 学生信息表.所在班级=班级信息表.班级编号
where 借阅信息表.罚金!=0;

通过图形化工具设置罚金变量 (想改啥就改啥但是要大于0 不然查询没有结果)

6. 查询读者的借书证号、姓名、联系电话、以及所借图书的图书编号、类别名称、借阅日期、出版社和编号。

select 学生信息表.借书证号,姓名,联系电话,

       图书信息管理表.图书编号,出版社,

       图书类别信息表.类别编号,类别名称,

       借阅信息表.借阅日期

from 借阅信息表

         left join 学生信息表 on 借阅信息表.借书证号=学生信息表.借书证号

         left join 图书信息管理表 on 借阅信息表.图书编号 = 图书信息管理表.图书编号

         left join 图书类别信息表 on 图书信息管理表.类别编号 = 图书类别信息表.类别编号;

2.4子查询

1. 查询借阅了图书编号为B0001的图书的读者信息。

select 学生信息表.* from 学生信息表
where 学生信息表.借书证号 in(select 借书证号 from 借阅信息表 where 图书编号 ='B0001');

2. 查询借阅了广东人民出版社和清华出版社的图书的读者信息。

select 学生信息表.* from 学生信息表
where 学生信息表.借书证号
in(select 借书证号 from 借阅信息表 where 图书编号
in(select 图书编号 from 图书信息管理表 where 出版社='广东人民出版社' or 出版社='清华出版社'));

3. 查询还未归还图书的读者的借书证号、姓名、部门和联系电话(假设当前的日期是2022-10-20号，图书归还时候，一定要缴纳罚金)。

select 学生信息表.借书证号,姓名,联系电话,班级信息表.所属系部
from 学生信息表
join 班级信息表 on 学生信息表.所在班级 = 班级信息表.班级编号
where 借书证号 in(select 借书证号 from 借阅信息表 where 应还书日期<'2022-10-20');

4. 查询姓名为朱威读者的借阅记录。

select * from 借阅信息表 where 借书证号 =(select 借书证号 from 学生信息表 where 姓名='朱威');

5. 查询跟徐广声借阅了相关图书的其他读者详细信息。

select * from 学生信息表 where 借书证号
in(select 借书证号 from 借阅信息表 where 图书编号
in(select 图书编号 from 借阅信息表 where 借书证号
=(select 借书证号 from 学生信息表 where 姓名='徐广声')));

6. 利用相关子查询邹思柔是否有借阅图书的记录，若有则输出借阅记录。

select * from 借阅信息表 where 借书证号=
(select 借书证号 from 学生信息表 where 姓名='邹思柔');

7. 查询图书价格比所有图书平均价格高的图书信息。

select  * from 图书信息管理表 
where 图书价格>all(select avg(图书价格) from 图书信息管理表);

8. 查询图书价格比图书编号为B0002和B0003的价格都高的图书信息。

select  * from 图书信息管理表
where 图书价格>all
(select 图书价格 from 图书信息管理表 where 图书编号 = 'B0002'or 图书编号 ='B0003');

2.5视图

根据应用，请同学定义2张视图。第一个视图输出学生学号、姓名、电话号码、班级名称、系部名称。第二个视图输出罚金超过10元的学生学号、学生、电话号码、借书证、图书名字、借书日期、应还书日期及罚金。

create view 学生信息 as
select 学生信息表.学号,姓名,联系电话,班级信息表.班级名称,所属系部
from 学生信息表 join 班级信息表 on 学生信息表.所在班级 = 班级信息表.班级编号;
create view 罚金超过10的信息 as
select 学生信息表.学号,姓名,联系电话,借阅信息表.借书证号,图书信息管理表.书名,借阅信息表.借阅日期,应还书日期,罚金
from 学生信息表 join 借阅信息表 on 借阅信息表.借书证号= 学生信息表.借书证号
join 图书信息管理表 on 借阅信息表.图书编号 = 图书信息管理表.图书编号
where 借阅信息表.罚金>10;