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

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

《数据库图书信息管理数据库SQL语句.docx》由会员分享，可在线阅读，更多相关《数据库图书信息管理数据库SQL语句.docx（18页珍藏版）》请在冰豆网上搜索。

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

实验SQL语言

一、实验目的

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

2、熟悉SQLServer2005中的数据类型；

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）——课程编号Coursenumber、课程名Coursename、先修课Thefirstcourse、学分credit

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

图书类别信息表（tb_booktype）——类别编号Typenumber、类别名称Categoryname

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

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

借阅信息表（tb_borrow）——图书编号、读者编号、借阅日期Borrowingdate、归还日期Returndate

3、通过SQL语句对读者信息表进行修改：

删除系部字段、添加所在系字段。

4、通过SQL语句对图书信息表进行修改：

将定价的数据类型改为REAL。

5、通过SQL语句删除课程信息表。

（三）创建和删除索引

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

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

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

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

根据周围的实际应用情况，自选一个小型的数据库应用项目进行研究，完成该系统的设计。

要求2人一组，通过需求分析，列出系统的主要功能，并完成该系统数据库的逻辑结构设计。

例如可选择学籍管理系统、企业进销存管理系统、人事管理系统或在线考试系统等。

（五）数据查询

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

（1）图书类别信息表

类别编号

类别名称

类别编号

类别名称

类别编号

类别名称

1

数学

4

文学

7

建筑

2

英语

5

艺术

8

化学

3

计算机

6

电子信息

9

物理

（2）图书信息表

图书编号

类别编号

书名

作者

出版社

定价

库存数

10001

3

数据库管理

王珊

高等教育出版社

35.50

10

10002

3

软件测试

贺平

机械工业出版社

24.60

5

10003

3

C++程序设计

谭浩强

清华大学出版社

30.00

8

10004

4

红楼梦

曹雪芹

人民文学出版社

70.00

5

10005

4

西游记

罗贯中

人民文学出版社

60.00

8

10006

4

红与黑

司汤达

人民文学出版社

50.00

5

10007

1

高等数学

李翼

清华大学出版社

28.00

4

10008

8

有机化学

张翔

高等教育出版社

29.00

5

10009

2

大学英语

王琳

高等教育出版社

25.00

10

10010

2

英语教程

王琳

高等教育出版社

25.00

5

（3）读者信息表

读者编号

姓名

性别

学号

班级

所在系

R10001

张小航

男

0851101

08511

计算机系

R10002

王文广

女

0851102

08511

计算机系

R10003

李理

女

0851103

08511

计算机系

R10004

李彦宏

男

0851201

08512

计算机系

R10005

张丽霞

女

0851202

08512

计算机系

R10006

王强

男

0721104

07211

电子系

R10007

张宝田

男

0721204

07212

电子系

R10008

宋文霞

女

0761104

07611

建工系

R10009

刘芳菲

女

0881104

08811

外语系

R10010

常江宁

男

0881204

08812

外语系

（4）借阅信息表

图书编号

读者编号

借阅日期

归还日期

10002

R10003

2009-9-20

2009-10-20

10003

R10003

2009-9-20

2009-10-20

10004

R10003

2009-9-30

2009-10-30

10009

R10003

2009-9-30

2009-10-30

10009

R10007

2009-5-20

2009-6-20

10010

R10007

2009-5-20

2009-6-20

10009

R10009

2009-5-30

2009-6-30

10010

R10009

2009-5-22

2009-6-22

10002

R10009

2009-5-22

2009-6-22

10003

R10009

2009-5-30

2009-6-30

对以上数据表，完成以下操作：

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

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

（3）查询每条借阅记录的借阅天数（函数DATEDIFF获取两个日期的差）；

（4）查询被借阅过的图书的图书编号；

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

（6）查询库存数在5到10本之间的图书的图书编号和书名；

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

（8）查询书名包括“英语”的图书信息；

（9）统计男读者、女读者的人数；

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

（11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；

（12）查询有库存的各类别图书的类别编号、类别名称和借阅数量；

（13）查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部；

（14）查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；（LEFTOUTERJOIN）

（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”视图。

三、实验步骤

use[master]

go

createdatabasedb_Library

onprimary

（

name=N'db_Library_data',

filename='E:

\SQL\db_Library_data.mdf',

size=10MB,

maxsize=300MB,

filegrowth=10%

）

logon

（

name='db_Library_log',

filename='E:

\SQL\db_Library_data.ldf',

size=5MB,

maxsize=200MB,

filegrowth=2MB

）

go

usedb_Library

go

createschema"L-C"authorizationguest

createtabletb_course--课程信息表

（Coursenumberint,

Coursenamechar（20）,

Thefirstcoursechar（20）,

Creditint）

go

createtabletb_booktype--图书类别信息表

（Typenumberint,

Categorynamechar（20）

）

go

createtabletb_book--图书信息表

（ISBNint,

Typenumberint,

Titlechar（20）,

Authorchar（20）,

BookPublicchar（20）,

BookPricefloat,

Inventorynumberint）

go

createtabletb_reader--读者信息表

（

ReaderIDchar（20）,

Namechar（20）,

Sexchar

（2）,

StudentIDint,

Classchar（20）,

Pasternchar（20）

）

go

createtabletb_borrow--借阅信息表

（

ISBNint,

ReaderIDchar（20）,

Borrowingdatechar（20）,

Returndatechar（20））

go

altertabletb_readerdropcolumnPastern

altertabletb_readeraddcolumnPastern

altertabletb_bookaltercolumnBookPricereal

droptabletb_course

--创建和删除索引

createindexIX_S_QUANTITYontb_book（Inventorynumber）

createindexIX_S_NAMEontb_reader（Nameasc）

dropindexIX_S_QUANTITY

dropindexIX_S_NAME--图书类别信息表信息

insertintotb_booktypevalues（1,'数学'）

insertintotb_booktypevalues（2,'英语'）

insertintotb_booktypevalues（3,'计算机'）

insertintotb_booktypevalues（4,'文学'）

insertintotb_booktypevalues（5,'艺术'）

insertintotb_booktypevalues（6,'电子信息'）

go

--图书信息表信息

insertintotb_bookvalues（10001,3,'数据库管理','王珊','高等教育出版社',35.50,10）

insertintotb_bookvalues（10002,3,'软件测试','贺平','机械工业出版社',24.60,5）

insertintotb_bookvalues（10003,3,'C++程序设计','谭浩强','清华大学出版社',30.00,8）

insertintotb_bookvalues（10004,4,'红楼梦','曹雪芹','人民文学出版社',70.00,5）

insertintotb_bookvalues（10005,4,'西游记','罗贯中','人民文学出版社',60.00,8）

insertintotb_bookvalues（10006,4,'红与黑','司汤达','人民文学出版社',50.00,5）

insertintotb_bookvalues（10007,1,'高等数学','李翼','清华大学出版社',28.00,4）

insertintotb_bookvalues（10008,8,'有机化学','张翔','高等教育出版社',29.00,5）

insertintotb_bookvalues（10009,2,'大学英语','王琳','高等教育出版社',25.00,10）

insertintotb_bookvalues（10010,2,'英语教程','王琳','高等教育出版社',25.00,5）

go

--读者信息表信息

insertintotb_readervalues（'R10001','张小航','男',0851101,'08511','计算机系'）

insertintotb_readervalues（'R10002','王文广','女',0851102,'08511','计算机系'）

insertintotb_readervalues（'R10003','李理','女',0851103,'08511','计算机系'）

insertintotb_readervalues（'R10004','李彦宏','男',0851201,'08512','计算机系'）

insertintotb_readervalues（'R10005','张丽霞','女',0851202,'08512','计算机系'）

insertintotb_readervalues（'R10006','王强','男',0721104,'07211','电子系'）

insertintotb_readervalues（'R10007','张宝田','男',0721204,'07212','电子系'）

insertintotb_readervalues（'R10008','宋文霞','女',0761104,'07611','建工系'）

insertintotb_readervalues（'R10009','刘芳菲','女',0881104,'08811','外语系'）

insertintotb_readervalues（'R10010','常江宁','男',0881204,'08812','外语系'）

go

--借阅信息表信息

insertintotb_borrowvalues（10002,'R10003','2009-9-20','2009-10-20'）

insertintotb_borrowvalues（10003,'R10003','2009-9-20','2009-10-20'）

insertintotb_borrowvalues（10004,'R10003','2009-9-30','2009-10-30'）

insertintotb_borrowvalues（10009,'R10003','2009-9-30','2009-10-30'）

insertintotb_borrowvalues（10009,'R10007','2009-5-20','2009-6-20'）

insertintotb_borrowvalues（10010,'R10007','2009-5-20','2009-6-20'）

insertintotb_borrowvalues（10009,'R10009','2009-5-30','2009-6-30'）

insertintotb_borrowvalues（10010,'R10009','2009-5-22','2009-6-22'）

insertintotb_borrowvalues（10002,'R10009','2009-5-22','2009-6-22'）

insertintotb_borrowvalues（10003,'R10009','2009-5-30','2009-6-30'）

go

--

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

select*

fromtb_book

--

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

selectReaderID,Name,Class

fromtb_reader

--（3）查询每条借阅记录的借阅天数；

selectdatediff（DAY,Borrowingdate,Returndate）

fromtb_borrow

--（4）查询被借阅过的图书的图书编号；

selectdistinctISBN

fromtb_borrow

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

selectTitle,Author

fromtb_book

whereISBN='10006'

--（6）查询库存数在到本之间的图书的图书编号和书名；

SELECTISBN,Title

fromtb_book

whereInventorynumberbetween5and10

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

select*fromtb_reader

wherepasternin（'计算机系','电子系'）

--（8）查询书名包括“英语”的图书信息；

select*fromtb_book

whereTitle='英语'

--（9）统计男读者、女读者的人数；

selectSex,COUNT（*）fromtb_reader

groupbySex

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

selectTypenumber,AVG（BookPrice）as平均定价,SUM（Inventorynumber）as库存总数

fromtb_book

groupbyTypenumber

--（11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；

selectISBN,COUNT（*）fromtb_borrow

groupbyISBN

orderbyCOUNT（*）desc

--（12）查询有库存的各类别图书的类别编号、类别名称和借阅数量；

selecttb_book.Typenumber,Categoryname,COUNT（*）

fromtb_book,tb_booktype,tb_borrow

wheretb_book.Typenumber=tb_booktype.Typenumberandtb_book.ISBN=tb_borrow.ISBN

groupbytb_book.Typenumber,tb_booktype.Categoryname

--（13）查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部；

selectName,Sex,Pastern

fromtb_reader

whereReaderIDin

（selectReaderID

fromtb_borrow

whereISBNin

（selectISBNfromtb_bookwhereTitle='大学英语'）

）

--（14）查询每个读者的读者编号、姓名、所借图书编号及所借阅日期；

selecttb_reader.ReaderID,Name,Borrowingdate

fromtb_readerleftouterjointb_borrowontb_reader.ReaderID=tb_borrow.ReaderID

--（15）查询现有图书中价格最高的图书，输出书名、作者、定价；

selectTitle,Author,BookPrice

fromtb_book

whereBookPrice=

（selectMAX（BookPrice）

fromtb_book

）

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

selectName,Sex,Pastern

fromtb_readerwhereNamein（selectName

fromtb_borrowwhereISBNin（selectISBNfromtb_bookwhereTitle='大学英语'））

andNamenotin（selectNamefromtb_borrowwhereISBNin

（selectISBNfromtb_bookwhereTitle='C++程序设计'））

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

select*

fromtb_reader

whereReaderIDin

（selectReaderIDfromtb_borrowgroupbyReaderIDhavingCOUNT（*）>2）

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

select*fromtb_readertbr1

wherePastern='计算机系'andReaderIDin

（selecttb_reader.Re