数据库图书信息管理数据库SQL语句

数据库图书信息管理数据库SQL语句

实验 SQL语言

一、 实验目的

1、 理解数据库以及数据表的设计；

2、 熟悉SQL Server2005中的数据类型；

3、 熟悉使用SQL语句创建和删除模式和索引； 4、 掌握使用SQL语句创建、修改和删除数据表； 5、 掌握使用SQL语句查询表中的数据；

6、 掌握使用SQL语句插入、修改和删除数据表中的数据； 7、 掌握使用SQL语句创建、删除、查询和更新视图。

二、 实验内容

（一）创建数据库和模式

1、通过SQL语句创建图书信息管理数据库，命名为“db_Library”，数据文件和日志文件放在D盘下以自己学号和姓名命名的文件夹中，数据文件的逻辑名为db_Library_data，数据文件的操作系统名为db_Library_data.mdf，文件初始大小为10MB，最大可增加至300MB，增幅为10%；日志文件的逻辑名为db_Library_log，日志文件的操作系统名为db_Library_data.ldf，文件初始大小为5MB，最大可增加至200MB，增幅为2MB。

2、通过SQL语句在该数据库中创建模式L-C。 （二）创建和管理数据表

要求为各数据表的字段选择合适的数据类型及名称；为各数据表设置相应的完整性约束条件。 1、通过SQL语句将以下数据表创建在L-C模式下：

课程信息表（tb_course）——课程编号Course number 、课程名Course name 、先修课The first course 、学分credit

2、通过SQL语句将以下数据表创建在该数据库的默认模式dbo下：

图书类别信息表（tb_booktype)——类别编号Type number 、类别名称Category name

图书信息表（tb_book）——图书编号ISBN 、类别编号Type number、书名title 、作者author、出版社BookPublic、定价BookPrice、库存数Inventory number

读者信息表（tb_reader）——读者编号Reader ID 、姓名、性别、学号Student ID 、班级、系部pastern

借阅信息表（tb_borrow)——图书编号、读者编号、借阅日期Borrowing date 、归还日期Return date

3、通过SQL语句对读者信息表进行修改：删除系部字段、添加所在系字段。 4、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL。 5、通过SQL语句删除课程信息表。 （三）创建和删除索引

1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY，要求按照该表中库存数字段的降序创建。

2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME，要求按照该表中的姓名字段的升序创建。

3、使用SQL语句删除之前创建的两个索引。

数据库图书信息管理数据库SQL语句

（四）数据库及数据表设计

根据周围的实际应用情况，自选一个小型的数据库应用项目进行研究，完成该系统的设计。要求2人一组，通过需求分析，列出系统的主要功能，并完成该系统数据库的逻辑结构设计。例如可选择学籍管理系统、企业进销存管理系统、人事管理系统或在线考试系统等。

（五）数据查询

通过SSMS向各数据表中添加以下记录。 （1

（4

数据库图书信息管理数据库SQL语句

（1）查询每本图书的所有信息；

（2）查询每个读者的读者编号、姓名和班级；

（3）查询每条借阅记录的借阅天数（函数DATEDIFF获取两个日期的差）； （4）查询被借阅过的图书的图书编号；

（5）查询图书编号为“10006”的书名和作者；

（6）查询库存数在5到10本之间的图书的图书编号和书名； （7）查询计算机系或电子系姓张的读者信息； （8）查询书名包括“英语”的图书信息； （9）统计男读者、女读者的人数；

（10）统计各类图书的类别编号、平均定价以及库存总数；

（11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列； （12）查询有库存的各类别图书的类别编号、类别名称和借阅数量；

（13）查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部； （14）查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；（LEFT OUTER JOIN） （15）查询现有图书中价格最高的图书，输出书名、作者、定价；

（16）查询借阅了“大学英语”但没有借阅“C++程序设计”的读者，输出读者姓名、性别、系部；

（17）统计借阅了2本以上图书的读者信息；

（18）查询借阅了“大学英语”一书或者借阅了“C++程序设计”一书的读者信息；（用集合查询完成）

（19）查询既借阅了“大学英语”一书又借阅了“C++程序设计”一书的读者信息；（用集合查询完成）

（20）查询计算机系中比其他系所有读者借书数量都多的读者的信息；

（21）在读者信息表中插入一条新的记录（读者编号：R10011；姓名：张三；所在系：电子系）； （22）定义一个表tb_booknew，包含图书编号、书名和类别名称字段，要求将类编编号为“3”的图书的图书编号、书名和类别名称插入到tb_bknew表中；

（23）将类别编号为“3”的所有图书的库存数增加5；

（24）将“C++程序设计”这本书的归还日期增加一个月（函数DATEADD）。 （25）删除姓名为“张三”的读者的信息； （26）删除tb_bknew表中的所有数据；

（27）创建一个名为“读者借阅信息_VIEW”的视图，要求显示计算机系所有读者的借阅信息，包括读者编号、姓名、所在系、图书编号、书名和借阅日期等字段，更新该视图时要保证只有计算机系的读者借阅信息；

（28）创建一个名为“图示借阅信息_VIEW”的视图，要求显示图书的借阅情况，包括图书编号、书名、库存数、借阅次数字段；

（29）查询借阅次数大于2的图书的图书编号、书名、库存数和借阅次数； （30）删除“图示借阅信息_VIEW”视图。

三、 实验步骤

数据库图书信息管理数据库SQL语句

use [master] go

create database db_Library on primary ( name=N'db_Library_data', filename='E:\SQL\db_Library_data.mdf', size=10MB, maxsize=300MB, filegrowth=10% )

log on ( name='db_Library_log', filename='E:\SQL\db_Library_data.ldf', size=5MB, maxsize=200MB, filegrowth=2MB ) go

use db_Library go

create schema "L-C" authorization guest create table tb_course --课程信息表 ( Coursenumber int , Coursename char(20), Thefirstcourse char(20), Credit int) go

create table tb_booktype --图书类别信息表 ( Typenumber int, Categoryname char(20) ) go

create table tb_book --图书信息表 ( ISBN int, Typenumber int, Title char(20), Author char(20), BookPublic char(20), BookPrice float, Inventorynumber int) go

create table tb_reader --读者信息表

数据库图书信息管理数据库SQL语句

(

ReaderID char(20), Name char(20), Sex char(2), StudentID int , Class char(20), Pastern char(20) ) go

create table tb_borrow --借阅信息表 (

ISBN int,

ReaderID char(20), Borrowingdate char(20), Returndate char(20)) go

alter table tb_reader drop column Pastern alter table tb_reader add column Pastern alter table tb_book alter column BookPrice real drop table tb_course --创建和删除索引

create index IX_S_QUANTITY on tb_book(Inventorynumber) create index IX_S_NAME on tb_reader(Name asc) drop index IX_S_QUANTITY

drop index IX_S_NAME--图书类别信息表信息 insert into tb_booktype values(1,'数学') insert into tb_booktype values(2,'英语') insert into tb_booktype values(3,'计算机') insert into tb_booktype values(4,'文学') insert into tb_booktype values(5,'艺术') insert into tb_booktype values(6,'电子信息') go

--图书信息表信息

insert into tb_book values(10001,3,'数据库管理','王珊','高等教育出版社',35.50,10) insert into tb_book values(10002,3,'软件测试','贺平','机械工业出版社',24.60,5)

insert into tb_book values(10003,3,'C++程序设计','谭浩强','清华大学出版社',30.00,8) insert into tb_book values(10004,4,'红楼梦','曹雪芹','人民文学出版社',70.00,5) insert into tb_book values(10005,4,'西游记','罗贯中','人民文学出版社',60.00,8) insert into tb_book values(10006,4,'红与黑','司汤达','人民文学出版社',50.00,5) insert into tb_book values(10007,1,'高等数学','李翼','清华大学出版社',28.00,4) insert into tb_book values(10008,8,'有机化学','张翔','高等教育出版社',29.00,5) insert into tb_book values(10009,2,'大学英语','王琳','高等教育出版社',25.00,10) insert into tb_book values(10010,2,'英语教程','王琳','高等教育出版社',25.00,5) go

数据库图书信息管理数据库SQL语句

--读者信息表信息

insert into tb_reader values('R10001','张小航','男',0851101,'08511','计算机系') insert into tb_reader values('R10002','王文广', '女',0851102,'08511','计算机系') insert into tb_reader values('R10003','李理', '女',0851103,'08511','计算机系')

insert into tb_reader values('R10004','李彦宏', '男',0851201,'08512','计算机系') insert into tb_reader values('R10005','张丽霞', '女',0851202,'08512','计算机系') insert into tb_reader values('R10006','王强', '男',0721104,'07211','电子系')

insert into tb_reader values('R10007','张宝田', '男',0721204,'07212','电子系') insert into tb_reader values('R10008','宋文霞', '女',0761104,'07611','建工系') insert into tb_reader values('R10009','刘芳菲', '女',0881104,'08811','外语系') insert into tb_reader values('R10010','常江宁', '男',0881204,'08812','外语系') go

--借阅信息表信息

insert into tb_borrow values(10002,'R10003','2009-9-20','2009-10-20') insert into tb_borrow values(10003,'R10003','2009-9-20','2009-10-20') insert into tb_borrow values(10004,'R10003','2009-9-30','2009-10-30') insert into tb_borrow values(10009,'R10003','2009-9-30','2009-10-30') insert into tb_borrow values(10009,'R10007','2009-5-20','2009-6-20') insert into tb_borrow values(10010,'R10007','2009-5-20','2009-6-20') insert into tb_borrow values(10009,'R10009','2009-5-30','2009-6-30') insert into tb_borrow values(10010,'R10009','2009-5-22','2009-6-22') insert into tb_borrow values(10002,'R10009','2009-5-22','2009-6-22') insert into tb_borrow values(10003,'R10009','2009-5-30','2009-6-30') go

--(1)查询每本图书的所有信息； select * from tb_book

--（2）查询每个读者的读者编号、姓名和班级； select ReaderID,Name,Class from tb_reader

--（3）查询每条借阅记录的借阅天数； select datediff(DAY,Borrowingdate,Returndate) from tb_borrow

--（4）查询被借阅过的图书的图书编号； select distinct ISBN from tb_borrow

--（5）查询图书编号为“”的书名和作者； select Title,Author from tb_book

where ISBN='10006'

--（6）查询库存数在到本之间的图书的图书编号和书名； SELECT ISBN,Title from tb_book

where Inventorynumber between 5 and 10

数据库图书信息管理数据库SQL语句

--（7）查询计算机系或电子系姓张的读者信息； select *from tb_reader

where pastern in('计算机系','电子系')

--（8）查询书名包括“英语”的图书信息； select * from tb_book where Title = '英语'

--（9）统计男读者、女读者的人数； select Sex ,COUNT(*)from tb_reader group by Sex

--（10）统计各类图书的编号、平均定价以及库存总数；

select Typenumber,AVG(BookPrice) as 平均定价,SUM(Inventorynumber) as 库存总数 from tb_book

group by Typenumber

--（11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列； select ISBN ,COUNT(*)from tb_borrow group by ISBN

order by COUNT(*) desc

--（12）查询有库存的各类别图书的类别编号、类别名称和借阅数量； select tb_book.Typenumber ,Categoryname ,COUNT(*) from tb_book,tb_booktype,tb_borrow

where tb_book.Typenumber =tb_booktype.Typenumber and tb_book.ISBN=tb_borrow.ISBN group by tb_book.Typenumber ,tb_booktype.Categoryname

--（13）查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部； select Name ,Sex ,Pastern from tb_reader where ReaderID in (select ReaderID from tb_borrow where ISBN in

(select ISBN from tb_book where Title ='大学英语') )

--（14）查询每个读者的读者编号、姓名、所借图书编号及所借阅日期； select tb_reader.ReaderID ,Name ,Borrowingdate

from tb_reader left outer join tb_borrow on tb_reader.ReaderID =tb_borrow.ReaderID --（15）查询现有图书中价格最高的图书，输出书名、作者、定价； select Title ,Author ,BookPrice from tb_book

where BookPrice =

(select MAX(BookPrice ) from tb_book )

--（16）查询借阅了“大学英语”但没有借阅“C++程序设计”的读者，输出读者姓名、性别、系部 select Name ,Sex , Pastern

from tb_reader where Name in (select Name

数据库图书信息管理数据库SQL语句

from tb_borrow where ISBN in (select ISBN from tb_book where Title ='大学英语')) and Name not in (select Name from tb_borrow where ISBN in (select ISBN from tb_book where Title ='C++程序设计') ) --（17）统计借阅了本以上图书的读者信息； select *

from tb_reader

where ReaderID in

(select ReaderID from tb_borrow group by ReaderID having COUNT(*)>2) --（18）查询计算机系中比其他系所有读者借书数量都多的读者的信息； select * from tb_reader tbr1

where Pastern ='计算机系' and ReaderID in

(select tb_reader.ReaderID from tb_reader,tb_borrow

where tb_reader.ReaderID=tb_borrow.ReaderID and tb_reader.ReaderID=tbr1.ReaderID group by tb_reader.ReaderID having count(ISBN)>any (

select count(ISBN) from tb_reader,tb_borrow

where tb_reader.ReaderID =tb_borrow.ReaderID and Pastern <>'计算机系' group by tb_reader.ReaderID ) )

--（19）查询借阅了“大学英语”一书或者借阅了“C++程序设计”一书的读者信息；（用集合查询完成） select * from tb_reader

where ReaderID in (select ReaderID from tb_borrow,tb_book where tb_borrow.ISBN=tb_book.ISBN and Title ='大学英语') union

select * from tb_reader

where ReaderID in (select ReaderID from tb_borrow,tb_book where tb_borrow.ISBN=tb_book.ISBN and Title='C++程序设计')

--20）查询既借阅了“大学英语”一书又借阅了“C++程序设计”一书的读者信息。（用集合查询完成） select * from tb_reader

where ReaderID in (select ReaderID from tb_borrow,tb_book where tb_borrow.ISBN =tb_book.ISBN and Title='大学英语') intersect

select * from tb_reader

where ReaderID in (select ReaderID from tb_borrow,tb_book where tb_borrow.ISBN=tb_book.ISBN and Title='C++程序设计')

--21）在读者信息表中插入一条新的记录（读者编号：R10011；姓名：张三；所在系：电子系）； insert into tb_reader(ReaderID,Sex ,Pastern ) values ('R10011','张三','电子系')

--（22）假设存在一个表tb_booknew，包含图书编号、书名和类别名称字段，要求将类编编号为“”的图书的图书编号、书名和类别名称插入到tb_bknew表中； create table tb_booknew

(ISBN char(10),Title varchar(20),Typename varchar(20))

数据库图书信息管理数据库SQL语句

insert into tb_booknew

select tb_book.ISBN ,tb_book.Title,tb_booktype.Categoryname from tb_book,tb_booktype

where tb_book.Typenumber =tb_booktype.Typenumber and tb_book.Typenumber='3' --（23）将类别编号为“”的所有图书的库存数增加；

update tb_book set Inventorynumber=Inventorynumber+5 where Typenumber='3' --（24）将“C++程序设计”这本书的归还日期增加一个月（函数DATEADD）。 update tb_borrow set Borrowingdate =DATEADD(MONTH,1,Returndate ) where ISBN in

(select ISBN from tb_book where Title ='C++程序设计') --25）删除姓名为“张三”的读者的信息； delete from tb_reader where Name ='张三' --（26）删除tb_bknew表中的所有数据； delete from tb_booknew

--（27）创建一个名为“读者借阅信息_VIEW”的视图，要求显示计算机系所有读者的借阅信息， create view 读者借阅信息_VIEW ( ReaderID,Name ,Pastern , ISBN,Title ,Borrowingdate) as

select tb_reader.ReaderID ReaderID,Name ,Pastern ,tb_book.ISBN ISBN,Title ,Borrowingdate from tb_book,tb_reader,tb_borrow

where tb_book.ISBN=tb_borrow.ISBN and tb_reader.ReaderID =tb_borrow.ReaderID and Pastern='计算机系'

--（28）创建一个名为“图示借阅信息_VIEW”的视图，要求显示图书的借阅情况，包括图书编号、书名、库存数、借阅次数字段；

create view 图示借阅信息VIEW (ISBN ,Title ,Inventorynumber) as

select tb_book.ISBN ,Title ,Inventorynumber ,COUNT(*) number from tb_book,tb_borrow

where tb_book.ISBN =tb_borrow.ISBN

group by tb_book.ISBN ,Title ,Inventorynumber

--（29）查询借阅次数大于的图书的图书编号、书名、库存数和借阅次数； select *

from 读者借阅信息_VIEW where number>2 --（30）删除“图示借阅信息_VIEW”视图。

drop view 图示借阅信息_VIEW

本文来源：https://www.bwwdw.com/article/7g6i.html