项目三 网络数据库技术实训 - 图文

子项目三 图书管理数据库的数据操作

3.1 插入记录

任务要求1：使用SQL Server Management Studio删除图书管理数据库library

中各个数据表中的现有数据，确保以下向数据表中输入数据无冲突和重复。

deletefromborrow deletefrompublishers deletefromreaders deletefrombooks deletefromdepartment deletefromtype

任务要求2：使用T-SQL语句为图书数据库中的各表插入记录，注意外键与主表之间的关系。

insertintodepartmentvalues('xi01','管理系') insertintodepartmentvalues('xi02','教育系') insertintodepartmentvalues('xi03','应用外语系')

insertintoreadersvalues('2006061201','xi02','王子刚','ffd@163.com','15912345666','3')

insertintoreadersvalues('2006061202','xi02','赵六安','kkk@sina.com','66666666666','4')

insertintoreadersvalues('2006072301','xi01','张三丰','ddd@gdfs.edu.cn','12345678911','2')

insertintoreadersvalues('2006072302','xi01','李斯','lisi@126.com','77777777777','0')

insertintoreadersvalues('2007031001','xi03','李文超','lwc@sina.com','88481236111','0')

insertintoreadersvalues('2007031002','xi03','胡丽丽','hull@163.com','13335642218','0')

insertintoreadersvalues('2007031003','xi03','田英','tiany@163.com','13233445588','1')

insertintoreadersvalues('2007031004','xi03','','yuxi@hotmail.com','13312345678','0')

insertintotypevalues('D9','法律') insertintotypevalues('F0','经济') insertintotypevalues('F12','电子商务') insertintotypevalues('G4','教育学') insertintotypevalues('I3','中国文学') insertintotypevalues('TP','计算机')

于

秀

娟

insertintobooksvalues('7030101431','计算机基础','谭浩强','TP','32.0000','科学出版社',null,null)

insertintobooksvalues('7030197632','电子商务应用','王洁实','F12','27.0000','科学出版社',null,null)

insertintobooksvalues('7107029872','红楼梦','曹雪芹','I3','35.0000','人民教育出版社',null,null)

insertintobooksvalues('7107102253','市场营销','李迪','G4','19.5000','人民教育出版社',null,null)

insertintobooksvalues('7107103692','经济教学','郭瑞军','G4','30.0000','人民教育出版社',null,null)

insertintobooksvalues('7111024356','会计电算计化教程','梁峰','G4','27.0000','机械工业出版社',null,null)

insertintobooksvalues('7111072049','VB程序设计','刘成勇','TP','25.0000','机械工业出版社',null,null)

insertintobooksvalues('7115030246','物流与电子商务','何子军','F12','23.0000','人民邮电出版社',null,null)

insertintobooksvalues('7121125581','经济研究','孙力','F0','25.0000','电子工业出版社',null,null)

insertintobooksvalues('7121154911','数据库教程','何文华','TP','28.0000','电子工业出版社',null,null)

insertintobooksvalues('7302013242','ASP.NET程序设计','马俊','TP','52.0000','清华大学出版社',null,null)

insertintobooksvalues('7302035714','网页设计','武迪生','TP','41.0000','清华大学出版社',null,null)

insertintobooksvalues('7302113585','经济学概论','吴畅','F0','30.0000','清华大学出版社',null,null)

insertintobooksvalues('7502413517','网络营销','于得水','TP','32.0000','冶金工业出版社',null,null)

insertintoborrowvalues('7030101431','2006072301',null,null) insertintoborrowvalues('7030197632','2006072302',null,null) insertintoborrowvalues('7107029872','2006072301',null,null) insertintoborrowvalues('7107102253','2006061202',null,null) insertintoborrowvalues('7107103692','2007031003',null,null) insertintoborrowvalues('7111072049','2007031001',null,null) insertintoborrowvalues('7115030246','2006061202',null,null) insertintoborrowvalues('7302013242','2006072302',null,null) insertintoborrowvalues('7302035714','2006061201',null,null) insertintoborrowvalues('7502413517','2006061202',null,null)

任务要求3：

（1） 为书刊类型信息表type添加5条记录('01','计算机')，('02','经济')，

('03','数学')，('04','艺术')，('05','电子商务')

insertintotypevalues('01','计算机') insertintotypevalues('02','经济') insertintotypevalues('03','数学') insertintotypevalues('04','艺术') insertintotypevalues('05','电子商务')

（2） 从书刊数据表中查询名称和价格插入到新表newbook

selectbookname,pricefrombooks

（3） 使用bulk insert命令将books表中的数据导入与books表结构相同的另外一

个新建的表newbook中。

sp_configure'show advanced options',1 reconfigure go

sp_configure'xp_cmdshell',1 reconfigure go

EXECmaster..xp_cmdshell'BCP Library11.dbo.books format nul -f C:\\xml\\book_fmt.xml -x -c -T'

EXECmaster..xp_cmdshell'BCP Library11.dbo.books outC:\\xml\\book.data -f C:\\xml\\book_fmt.xml -T'

bulkinsertLibrary.dbo.newbook from' C:\\xml\\book.data' with (

formatfile='C:\\xml\\book_fmt.xml' )

3.2 修改记录

任务要求1：

（1）使用T-SQL语句把书刊类型信息表中类别编号为03记录中的类别名称改为“教育”。

updatetype

settypename='教育' wheretypeID='03'

（2）使用T-SQL语句将科学出版社的所有图书价格下调10%。

updatebooks

setprice=price*0.9

（3）使用T-SQL语句将borrow表的所有借书日期设为2016年6月8日。 updateborrow

setborrowdate='2016-6-8'

（4）将读者借阅卡信息表中的所有读者的借阅数量修改为0

updatereaders setborrownum='0'

（5）统计汇总出借阅信息表中借书数,写入读者借阅卡信息表中

updatereaders setborrownum=(

selectcount(readerID)fromborrow wherereaderID=readers.readerID

（6）)3.3 删除记录

任务要求1：使用T-SQL语句完成下列要求

（1） 删除书刊类型信息表中类别编号为04的记录。

deletefromtype wheretypeID='04'

（2） 用DELETE语句删除读者借阅卡信息表中编号为2006061201的读者信息（有外

键约束）

deletefromreaders

wherereaderID='2006061201'

（3） 使用TRUNCATE TABLE删除newbook表中所有数据 TRUNCATETABLEnewbook

3.4 数据查询

针对Library数据库，在应用系统实际运行过程中，完成由用户提出的各种数据库查询操作：

3.4.1 简单数据查询

任务要求:

1） 显示所有书刊的信息；

select*frombooks

2） 查询部分书刊信息，书刊数据表中返回书名、作者和书的价格列，列名用中文显示。

selectbooknameas'书名',authoras'作者',priceas'价格'frombooks

3） borrow表中返回BookID,BorrowDate列，不显示重复记录。

selectdistinctbookID,borrowdatefromborrow

4） 返回books表中前3条记录）

selecttop 3 *frombooks

5） 遗失图书应按图书原价的2倍赔偿，请显示每本图书的书名和赔偿价格。

selectbookname,price*2frombooks

6） 遗失图书应按图书原价的2倍赔偿,将查询的结果保存在新表“赔偿”表中。

insertinto赔偿(bookname,price) selectbookname,price*2 frombooks

7） 从读者借阅卡信息表和书刊借阅信息表中返回读者信息和借阅信息。

select*fromreadersinnerjoinborrowonreaders.readerID=borrow.readerID

8） 求所有书的平均价格、最高价和最低价。

selectAVG(price)as'avg',MAX(price)as'max',MIN(price)as'min'fro

mbooks

3.4.2 条件查询

任务要求:

1） 查询所有“科学出版社”出版的书刊；

select*frombooks

wherepublisher='科学出版社'

2） 查询价格在[20，30]元之间图书的书名和作者。

selectbookname,authorfrombooks wherepricebetween 20 and 30

3） 查询所有含“迪”字作者的图书信息

select*frombooks whereauthorlike'%迪%'

4） 查询类别编号为“TP”和“F0”的图书信息

select*frombooks

wheretypeID='tp'ortypeID='F0'

5） 查询书刊借阅信息表中未还的图书

selectbooks.bookID,booknamefromborrowrightjoinbooksonborrow.bookID=bo

rrow.bookID

3.4.3 排序查询

任务要求:

1） 将各种书的情况按照价格的从高到低排列

select*frombooks

orderbypriceDESC

2） 将各种书的情况按照图书名称对应的拼音从低到高排列

select*frombooks orderbybookname

3） 查询每个学生的姓名和借阅图书数量，按借阅数量由高到低的顺序排列，并以汉字

标题显示列名。

selectnameas'姓名',borrownumas'借阅数量'fromreaders

orderbyborrownumDESC

4） 查询单价最高的三本图书的书名和价格。

selecttop 3 bookname,pricefrombooks orderbypriceDESC

5） 查询书名最长的三本图书的书名。

selecttop 3 booknamefrombooks orderbyLEN(bookname)DESC

6） 查询单价最高的图书的详细信息。

selecttop 1 *frombooks orderbypriceDESC

3.4.4 分组查询

任务要求:

1）按类别统计当前馆藏图书的书名个数。

selecttypeID,count(bookID)as'个数'frombooks groupbytypeID

2）按出版社统计当前馆藏图书的书名个数。

selectpublisher,count(bookID)as'个数'frombooks groupbypublisher

3）在读者借阅卡信息表中按部门编号对部门的人数进行统计

selectdeptID,count(bookID)as'人数'from

readersrightjoinborrowonreaders.readerID=borrow.readerID groupbydeptID

4）从borrow表统计每位读者借阅的图书数量。

selectreaderID,count(bookID)fromborrow groupbyreaderID

5）按出版社分别统计出版社当前馆藏图书的平均价，并将超过25元的显示出来

selectpublisher,AVG(price)frombooks groupbypublisher havingavg(price)>25

6）按出版社分别统计当前馆藏图书中单价在25元以上的图书的平均价，没有满足条件的出版社显示空值。

selectpublisher,AVG(price)frombooks whereprice>25 groupbypublisher

7）统计每个类别图书的最高单价与最低单价。

select typeID，MAX(price)as'max',MIN(price)as'min'frombooks groupbytypeID

8) 统计每个出版社出版图书的最高单价与最低单价。

selectpublisher，MAX(price)as'max',MIN(price)as'min'frombooks groupbypublisher

3.4.5 连接查询

任务要求:

1） 使用连接查询方式查询2016年借出去的图书的书名和出版社

selectbookname,publisherfrom

booksrightjoinborrowonbooks.bookID=borrow.bookID whereborrowdatebetween'2016-01-01'and'2016-12-31'

2） 使用左外连接查询每个读者的借书情况。如果读者没有借书，则在书刊借阅信息表

中将没有该读者的借阅记录，书刊编号和借阅日期用空值填充

select*from

readersleftouterjoinborrowonreaders.readerID=borrow.readerID

3） 将2中的左外连接改为使用右外连接，则查询结果为每本书的借阅读者情况。如果

有借书，而读者借阅信息卡中无此读者卡，则用空值填充。通常该情况表明，数据出现不一致，即书刊借阅信息表中的读者卡编号，而在读者借阅信息卡查无此号。

select*from

readersrightouterjoinborrowonreaders.readerID=borrow.readerID

4） 将3的左外连接改为使用全外连接，则查询结果为每本书的借阅读者情况和每个读

者的借书情况。如果有借书，而读者借阅信息卡中无此读者卡，则用空值填充。如果有该读者，而没有借阅书刊，则书刊借阅表的列为空值。

select*from

readersfullouterjoinborrowonreaders.readerID=borrow.readerID

5） 查询每位读者的姓名与所在系部名称。

selectname,deptfromreadersjoindepartmentonreaders.deptID=department.deptID

6） 查询所有馆藏图书的书名与所属类别名称

selectbookname,typenamefrom

typejoinbooksontype.typeID=books.typeID

7） 查询所有馆藏图书的书名、类别名称、单价和出版社名称

selectbookname,typename,price,publisherfrom typejoinbooksontype.typeID=books.typeID

8） 查询所有计算机类的图书的书名、作者和出版社名称。

selectbookname,author,publisherfrom booksjointypeonbooks.typeID=type.typeID wheretypename='计算机'

3.4.6 嵌套查询

任务要求:

1） 查询与借阅卡号为“2006061201”的读者在同一个部门的读者借阅卡号和姓名

selectreaderID,name,borrownumfromreaders

wheredeptID=(selectdeptIDfromreaderswherereaderID='2006061201')

2） 查询借书数高于读者借阅卡信息表中平均借书数的读者借阅号、姓名和借书数目

selectreaderID,name,borrownumfromreaders

whereborrownum>(selectAVG(borrownum)fromreaders)

3） 查询与读者“田英”在同一个部门的读者信息

select*fromreaders

wheredeptID=(selectdeptIDfromreaderswherename='田英')

4） 查询所有借阅了“7030101431”号的书刊的读者借阅卡号和姓名

selectreaders.readerID,namefromreadersjoinborrowonreaders.readerID=borrow.readerID wherebookID='7030101431'

5） 查询比《红楼梦》单价高的图书的书名、作者和价格。

selectbookname,author,pricefrombooks

whereprice>(selectpricefrombookswherebookname='红楼梦')

3.4.7 综合查询

任务要求:

1） 显示不同类别书刊的平均价格，及全部库存书刊的平均价格

2） 查询每个借阅者借书的数目

selectreaderID,borrownumfromreaders

3） 查询已借出的书刊总数目

selectcount(bookID)fromborrow

4） 联合查询，获得每个借阅者借书的数目和已借出的书刊总数目。

5）查询单价高于35元和清华大学出版社出版的图书书名和作者和出版社名称

selectbookname,author,publisherfrombooks whereprice>35 andpublisher='清华大学出版社'

6) 查询教育系学生借阅的所有图书书名和读者姓名

selectbookname,namefrom

booksjoinborrowonbooks.bookID=borrow.bookID joinreadersonborrow.readerID=readers.readerID

joindepartmentonreaders.deptID=department.deptID wheredept='教育系'

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