数据库实验二：图书信息管理系统数据查询_查询图书管理系统数据库

实验目的与要求

1. 掌握使用INSERT、UPDATE、DELETE语句向表中插入数据、修改和删除数据; 
2. 熟悉和掌握对数据表中数据的查询操作和SQL命令的使用；
3. 能掌握各种查询方法，能通过查询获取对自己有用的数据，能对查询结果进行编辑;
4. 理解不同类型的查询语句其执行原理及查询效率；
5. 加深理解关系运算的各种操作（尤其是关系的选择，投影，连接和除运算）;
6.理解视图概念 

实验设备与软件环境

1.Windows XP Professional SP3/Windows7

2.SQL Server 2012

实验原理与内容

        1． 实验原理：

 1.1表的概念

表是数据的集合，是用来存储数据和操作数据的逻辑结构。和电子表格类似，数据在表中是按照行和列的格式来组织排列的，每一行代表一条唯一的记录，每一列代表一个属性。

 1.2数据类型

常用的数据类型：整型、浮点型、货币类、日期和时间、字符类型(含Unicode字符串)、二进制类型等

1.3数据的完整性

数据完整性是指数据的正确性、一致性和安全性，它是衡量数据库中数据质量好坏的重要标准。当用户使用INSERT、DELETE或UPDATE语句修改数据库内容时，数据的完整性就可能会遭到破环。为此，SQL Sever提供了实施数据完整性的方法包括约束、规则等。数据的完整性大致分为四种类型：实体完整性：表中的每一行都能由主键的属性列来唯一标识，且不存在重复的数据行；域完整性：限制向表中输入值的范围，保证给定列的输入的有效性。可以通过限制数据类型，值域或数据格式实现；参照完整性：当一个表引用了另一个表中的某些数据时，要防止非法的数据更新，以保持表间数据的一致性；用户自定义完整性：体现实际运用的业务规则。

1.4 各种查询语句其执行原理及查询效率

1.5 视图作为一种数据库对象，可以让用户对数据源进行查询和修改。视图是SQL Sever中重要的数据库对象。视图常用于集中、简化和定制数据库中的数据信息，为用户从多角度观察数据库中的数据提供方便。

        2 . 实验内容：

1. 数据更新

        请使用SQL语句方法完成以下操作。

        1.1建立数据库后，运行实验二提供的相关代码。每个学生根据下面提供的表结构，用SQL命令方式，给图书信息表添加本课程教材书籍。同时添加学生本人的班级和学生信息。

实验过程与结果

        1. 数据更新：

请使用SQL语句方法完成以下操作。
        1.1建立数据库后，运行实验二提供的相关代码。每个学生根据下面提供的表结构，用SQL命令方式，给图书信息表添加本课程教材书籍。同时添加学生本人的班级和学生信息。

• 图书类别信息表 t_book_type

--图书类别信息表 t_book_type
	create table t_book_type(
	Tid char(10) primary key not null,
	TName varchar(50) not null	
	);

• 图书表t_book

--建立图书表t_book
	create table t_book(
	Bid char(15) primary key not null,
	Tid char(10) foreign key references t_book_type(Tid) not null,
	Bname varchar(50) not null,
	ISEN char(15) not null,
	BWriter varchar(50) not null,
	BCompany varchar(20) not null,
	BDate date not null,
	BPrice numeric(10,2)not null,
	BBrief varchar(100), 
	BNum tinyint default(1) not null,
	);

• 建借书证表t_libraryCard

--创建借书证表t_libraryCard
	create table t_libraryCard(
	LCardNo char(7) primary key not null,
	LName varchar(50) not null,
	IssueTime datetime not null,
	Available char(1) default('Y') not null,
	BorrowNum numeric(2,0) default(0) not null,
	MaxNum numeric(2,0) default(3) not null
	);

• 班级信息表t_class

--班级信息表t_class
	create table t_class(
	SClassID char(10) primary key not null,
	CName varchar(50) not null,
	MajorName varchar(50) not null,
	DName varchar(50) not null,
	EYear int not null
	);

• 学生信息表t_student

--学生信息表t_student
	create table t_student(
	Sid char(11) primary key not null,
	LCardNo char(7) foreign key references t_libraryCard(LCardNo),
	SName varchar(50) not null,
	SPwd char(6) not null,
	SGender char(2) check(SGender='男'or SGender='女'),
	DoB date not null,
	STel varchar(15),
	SClassID char(10) foreign key references t_class(SClassID) not null
	);

• 借阅信息表 t_borrow

 
--借阅信息表 t_borrow
	create table t_borrow(
	Book_id			char(15)			not null,
	LCardNo			char(7)				not null,
	BorrowDate		datetime			not null,
	ReturnDate		datetime,
	Overdue			char(1) default('N') not null,
	RenewTimes		tinyint default(0) not null,
	Fine			numeric(10,2) default(0) not null,
	primary key(Book_id,LCardNo,BorrowDate),
	foreign key(Book_id) references t_book(Bid),
	foreign key(LCardNo) references t_libraryCard(LCardNo)
	);

            1.2 添加数据：

--插入数据
	insert into t_book_type values('T0001','计算机');
	insert into t_book_type values('T0002','历史');
	insert into t_book_type values('T0003','人文');
	insert into t_book_type values('T0004','经济');
	insert into t_book_type values('T0005','体育');
	insert into t_book_type values('T0006','军事');
 
	insert into t_book values
	('B0001','T0001','数据化决策','9787218122106','道格拉斯','广东人民出版社','2017-12-13',34.5,'麻省理工学院指定教材',5),
	('B0002','T0001','大数据时代','9787218122107','吉格斯','浙江人民出版社','2019-4-15',26.5,'央视推荐书籍',5),
	('B0003','T0001','正在来到的数字革命','9787218122108','徐子平','广西师范大学出版社','2018-06-19',42.1,'大数据深刻影响人类',3),
	('B0004','T0002','霍去病将军创奇','9787218122109','易中天','清华出版社','2021-08-03',54.5,'青少年推荐读物',10),
	('B0005','T0002','明朝那些事','9787218122110','朱小青','清华出版社','2022-07-03',34.5,'青少年推荐读物',6),
	('B0006','T0002','梦回唐朝','9787218122111','魏征','机械工业出版社','2022-01-07',63.5,'爱国教育书籍',10),
	('B0007','T0003','中国崛起','9787218122112','吕小国','清华出版社','2022-02-17',33.5,'爱国主题优秀书籍',10),
	('B0008','T0003','宗教起源','9787218122113','本尼','广东人民出版社','1999-05-17',42.0,'趣味世界书籍',3);
 
	insert into t_class values('C0001','21软件大数据1班','软件工程','计算机系',2021);
	insert into t_class values('C0002','21软件大数据2班','软件工程','计算机系',2021);
	insert into t_class values('C0003','21软件大数据3班','软件工程','计算机系',2021);
	insert into t_class values('C0004','21软件1班','软件工程','计算机系',2021);
	insert into t_class values('C0005','21软件2班','软件工程','计算机系',2021);
	insert into t_class values('C0006','19会计1班','工商管理','信息管理系',2019);
	insert into t_class values('C0007','19外贸英语1班','商务英语','外国语系',2019);
 
	insert into t_libraryCard values('L0001','余骏昌','2021-03-01','Y',0,3);
	insert into t_libraryCard values('L0002','朱威','2021-03-01','Y',0,3);
	insert into t_libraryCard values('L0003','徐广声','2021-03-01','Y',0,3);
	insert into t_libraryCard values('L0004','潘奕寰','2021-05-02','Y',0,3);
	insert into t_libraryCard values('L0005','战美','2021-05-02','Y',0,3);
	insert into t_libraryCard values('L0006','邹思柔','2021-03-01','Y',0,3);
	insert into t_libraryCard values('L0007','黄小宇','2021-03-01','Y',0,3);
	insert into t_libraryCard values('L0008','梁帅峰','1999-06-22','Y',0,3);
	insert into t_libraryCard values('L0009','林武文','1999-08-23','Y',0,3);
	insert into t_libraryCard values('L0010','曾紫怡','2022-03-20','Y',0,3);
	insert into t_libraryCard values('L0011','卢君羽','2022-03-20','Y',0,3);
	insert into t_libraryCard values('L0012','林小娜','2021-07-05','Y',0,3);
	insert into t_libraryCard values('L0013','庞勇斌','2021-07-05','Y',0,3);
 
 
 
	insert into t_student values('21210120113','L0001','余骏昌','123456','男','1995-12-23','18988567873','C0001');
	insert into t_student values('21210120114','L0002','朱威','123456','男','1996-08-03','18988567873','C0001');
	insert into t_student values('21210120115','L0003','徐广声','123456','男','1995-09-02','18988567873','C0002');
	insert into t_student values('21210120116','L0004','潘奕寰','123456','男','1995-02-20','18988567873','C0002');
	insert into t_student values('21210120117','L0005','战美','123456','男','1996-10-13','18988567873','C0003');
	insert into t_student values('21210120118','L0006','邹思柔','123456','女','1996-02-05','18988567873','C0003');
	insert into t_student values('21210120119','L0007','黄小宇','123456','女','1996-03-08','18988567873','C0003');
	insert into t_student values('21210120120','L0008','梁帅峰','123456','男','1993-03-09','18988567873','C0003');
	insert into t_student values('21210120121','L0009','林武文','123456','男','1993-09-16','18988567873','C0004');
	insert into t_student values('21210120122','L0010','曾紫怡','123456','女','1995-07-15','18988567873','C0004');
	insert into t_student values('21210120123','L0011','李晓君','123456','女','1995-06-08','18988567873','C0004');
	insert into t_student values('21210120124','L0012','林小娜','123456','女','1996-07-02','18988567873','C0005');
	insert into t_student values('21210120125','L0013','庞勇斌','123456','男','1996-08-13','18988567873','C0006');
 
	insert into t_borrow values('B0001','L0002','2022-03-17','2022-04-27','N',0,0);
	insert into t_borrow values('B0001','L0013','2022-04-02','2022-05-27','N',0,0);
	insert into t_borrow values('B0001','L0008','1999-08-03','1999-10-27','N',3,0);
	insert into t_borrow values('B0001','L0009','2022-06-04','2022-07-27','N',0,0);
	insert into t_borrow values('B0001','L0002','2022-07-05','2022-08-27','Y',0,15.5);
	insert into t_borrow values('B0001','L0011','2022-08-06','2022-09-27','N',0,0);
	insert into t_borrow values('B0001','L0012','2022-09-08','2022-09-27','N',0,0);
	insert into t_borrow values('B0001','L0004','2022-10-09','2022-11-27','N',0,0);
	insert into t_borrow values('B0001','L0005','2022-09-03','2022-10-27','N',0,0);
	insert into t_borrow values('B0001','L0006','2022-08-07','2022-09-27','N',0,0);
	insert into t_borrow values('B0002','L0003','2021-09-05','2021-10-27','N',0,0);
	insert into t_borrow values('B0002','L0003','2021-07-03','2021-08-27','Y',0,20);
	insert into t_borrow values('B0002','L0005','2022-06-07','2022-08-27','N',0,0);
	insert into t_borrow values('B0002','L0010','2022-05-01','2022-06-27','N',0,0);
	insert into t_borrow values('B0002','L0009','2022-04-20','2022-05-27','N',0,0);
	insert into t_borrow values('B0002','L0008','1999-09-19','1999-10-27','N',0,0);
	insert into t_borrow values('B0003','L0007','2022-04-18','2022-05-27','N',0,0);
	insert into t_borrow values('B0003','L0001','2021-05-18','2021-06-27','N',0,0);
	insert into t_borrow values('B0003','L0002','2021-06-17','2021-08-27','N',0,0);
	insert into t_borrow values('B0003','L0003','2021-07-17','2021-10-27','N',3,0);
	insert into t_borrow values('B0003','L0004','2022-08-16','2022-09-27','Y',0,6.5);
	insert into t_borrow values('B0004','L0005','2022-09-15','2022-10-27','N',0,0);
	insert into t_borrow values('B0004','L0009','2022-10-14','2022-12-27','N',1,0);
	insert into t_borrow values('B0004','L0010','2022-10-13','2022-11-27','N',0,0);
	insert into t_borrow values('B0004','L0002','2022-09-12','2022-10-27','N',0,0);
	insert into t_borrow values('B0004','L0003','2022-08-11','2022-09-27','Y',0,8.5);
	insert into t_borrow values('B0005','L0007','2022-07-10','2022-08-27','N',0,0);
	insert into t_borrow values('B0005','L0008','1999-09-09','1999-10-27','N',0,0);
	insert into t_borrow values('B0005','L0009','2022-05-08','2022-08-27','N',3,0);
	insert into t_borrow values('B0005','L0005','2022-04-07','2022-05-27','N',0,0);
	insert into t_borrow values('B0006','L0001','2021-06-06','2021-07-27','N',0,0);
	insert into t_borrow values('B0006','L0002','2021-12-05','2022-01-27','Y',0,12.5);
	insert into t_borrow values('B0006','L0003','2022-05-04','2022-06-27','N',0,0);
	insert into t_borrow values('B0006','L0009','2022-06-03','2022-08-27','N',2,0);
	insert into t_borrow values('B0007','L0011','2022-07-02','2022-08-27','N',0,0);
	insert into t_borrow values('B0007','L0010','2022-08-01','2022-09-27','N',0,0);
 

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

update t_book set BWriter='阿历克斯',BPrice=50.5 where Bid='B0001';
select * from t_book where Bid='B0001';

--修改上题中添加的学生信息，密码为Ab12@@,联系电话为，18812345688。
--先插入一条学生信息
insert into t_student values('20231111','L0001','Tom','123456','女','2003-08-08','13556748967','C0001');
update t_student set SPwd='Ab12@@',STel=18812345688 where Sid='20231111';

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

delete from t_student where Sid='23215150133';
select * from t_student;

2. 数据查询

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

        2.1 单表查询

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

select * from t_book;

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

select Bid,Bname,BPrice from t_book;

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

select LcardNo,Sname,STel from t_student where SClassID in(
select SClassID from t_class where DName='计算机系');

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

select * from t_borrow where LCardNo='L0001' and Book_id='B0003';

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

select * from t_borrow where ReturnDate > CAST('2022-10-20' as datetime); 

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

select * from t_borrow where BorrowDate 
between CAST('1998-02-06' as datetime) 
and CAST('2021-12-06' as datetime); 

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

select * from t_student where SName like '徐%';

        2.2对查询结果进行编辑

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

select t_student.SName,t_class.DName from t_student,t_class 
where t_student.SClassID=t_class.SClassID and LCardNo in(
select LCardNo from t_borrow);

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

select sum(DATEDIFF(DAY,BorrowDate,GETDATE()))borrowDate from t_borrow 
where LCardNo='L0002';

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

select count(distinct Book_id)count_Num from t_borrow where LCardNo='L0003';

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

select * from t_book order by BPrice DESC;

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

select BCompany,COUNT(Bid)'图书数量' from t_book group by BCompany having COUNT(BCompany)>=2;

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

select Bname,SUM(BNum)'库存总量'from t_book group by Bname;

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

select Book_id,LCardNo,BorrowDate,ReturnDate,Overdue,RenewTimes,Fine * 2 'Fine' 
from t_borrow where Overdue='Y';

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

update t_borrow 
set Fine='0',RenewTimes=RenewTimes+1,Overdue='N' where LCardNo='L0002';
select * from t_borrow where LCardNo='L0002';

        2.3连接查询

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

select distinct Sid,t_student.LCardNo,SName,STel,Book_id,BorrowDate 
from t_student,t_borrow where t_student.LCardNo=t_borrow.LCardNo;

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

select * from t_book INNER JOIN t_book_type on t_book.Tid=t_book_type.Tid;

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

select distinct t_student.LCardNo,SName,SClassID,Book_id,BorrowDate 
from t_student left outer join t_borrow on(t_student.LCardNo=t_borrow.LCardNo);

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

select distinct LCardNo,Book_id,BorrowDate,BCompany 
from t_borrow left outer join t_book on (t_borrow.Book_id=t_book.Bid);

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

select t_student.Sid,t_student.SName,t_student.STel,t_class.CName,t_libraryCard.LCardNo,
t_libraryCard.IssueTime,t_book.Bid,t_book.Bname,t_borrow.ReturnDate,t_borrow.Fine
from t_student 
left outer join t_class on (t_student.SClassID=t_class.SClassID)
left outer join t_borrow on (t_student.LCardNo=t_borrow.LCardNo)
left outer join t_book on(t_borrow.Book_id=t_book.Bid)
left outer join t_libraryCard on(t_student.LCardNo=t_libraryCard.LCardNo)
where t_borrow.Fine > 0;

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

select t_student.LCardNo,SName,STel,t_book.Bid,t_book_type.TName,t_borrow.BorrowDate,t_book.BCompany,t_book.ISEN
from t_student
left outer join t_borrow on (t_student.LCardNo=t_borrow.LCardNo)
left outer join t_book on (t_borrow.Book_id=t_book.Bid)
left outer join t_book_type on (t_book.Tid=t_book_type.Tid);

        2.4子查询

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

select * from t_student where LCardNo in (
select LCardNo from t_borrow where Book_id='B0001');

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

select distinct * from t_student where LCardNo in(
select LCardNo from t_borrow where Book_id in (
select Bid from t_book where BCompany='广东人民出版社'or BCompany='清华出版社'));

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

select LCardNo,SName,t_class.CName,STel from t_student
left outer join t_class on (t_student.SClassID=t_class.SClassID)
where LCardNo in (
select LCardNo from t_borrow where ReturnDate>'2022-10-20');

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

select * from t_borrow where LCardNo in(
select LCardNo from t_student where SName='朱威');

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

select distinct * from t_student where LCardNo in(
select LCardNo from t_borrow where Book_id in(
select Book_id from t_borrow where LCardNo in(
select LCardNo from t_libraryCard where LName='徐广声')));

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

select * from t_borrow where LCardNo in(
select LCardNo from t_libraryCard where LName='邹思柔');

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

select * from t_book where BPrice >(select avg(BPrice) from t_book);

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

select * from t_book
where BPrice >(
select BPrice from t_book where Bid='B0002')
and BPrice >(
select BPrice from t_book where Bid='B0003');

        2.5视图

        根据应用，请同学定义2张视图。

        第一个视图输出学生学号、姓名、电话号码、班级名称、系部名称。

create view ONE
as
select Sid,SName,STel,CName,DName 
from t_student left outer join t_class on (t_student.SClassID=t_class.SClassID);

        第二个视图输出罚金超过10元的学生学号、学生、电话号码、借书证、图书名字、借书日期、应还书日期及罚金。

create view TWO
as
select Sid,SName,STel,t_student.LcardNo,t_book.Bname,BorrowDate,returnDate,t_borrow.Fine 
from t_student
left outer join t_borrow on(t_student.LCardNo=t_borrow.LCardNo)
left outer join t_book on(t_borrow.Book_id=t_book.Bid)where t_borrow.Fine>10;