北华航天工业学院《数据库原理与应用》实验报告报告题目：SＱL语言作者所在系部：计算机系作者所在专业:网络工程作者学号:201１40522３３作者姓名: 张旭刚指导教师姓名:李建义完成时间：201３４.2４北华航天工业学院教务处制ＳＱL语言一、实验目的1、理解数据库以及数据表的设计；2、熟悉SQLＳerver２005中的数据类型;3、熟悉使用SQL语句创建和删除模式和索引；4、掌握使用SQＬ语句创建、修改和删除数据表;5、掌握使用SQL语句查询表中的数据;6、掌握使用SQL语句插入、修改和删除数据表中的数据；7、掌握使用SＱＬ语句创建、删除、查询和更新视图。

二、实验内容（一）创建数据库和模式1、通过ＳQL语句创建图书信息管理数据库,命名为“db＿Libraｒy”，数据文件和日志文件放在D盘下以自己学号和姓名命名的文件夹中，数据文件的逻辑名为db_Ｌibｒaｒｙ＿dａta,数据文件的操作系统名为db_Liｂrary_data.ｍdf,文件初始大小为１0MＢ，最大可增加至300ＭB,增幅为１0%;日志文件的逻辑名为db_Ｌibrarｙ_ｌoｇ，日志文件的操作系统名为db_Ｌibrａry_dａｔa.lｄf,文件初始大小为5ＭB,最大可增加至20０MB，增幅为2ＭＢ。

2、通过SＱL语句在该数据库中创建模式L-C。

（二）创建和管理数据表要求为各数据表的字段选择合适的数据类型及名称;为各数据表设置相应的完整性约束条件。

1、通过SＱＬ语句将以下数据表创建在L－Ｃ模式下：课程信息表(tb_cｏｕｒse）——课程编号、课程名、先修课、学分2、通过SQＬ语句将以下数据表创建在该数据库的默认模式ｄbｏ下:图书类别信息表(tｂ＿ｂooktype)——类别编号、类别名称图书信息表(tｂ_ｂook)——图书编号、类别编号、书名、作者、出版社、定价、库存数读者信息表(tb_readeｒ）——读者编号、姓名、性别、学号、班级、系部借阅信息表（tｂ_bｏｒrow)——图书编号、读者编号、借阅日期、归还日期3、通过ＳQL语句对读者信息表进行修改:删除系部字段、添加所在系字段。

４、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL。

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

（三)创建和删除索引１、使用SQL语句在图书信息表上创建一个非聚簇索引IX_Ｓ_QUANTITＹ,要求按照该表中库存数字段的降序创建。

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

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

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

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

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

（五)数据查询通过SＳMS向各数据表中添加以下记录。

(1对以上数据表，完成以下操作:（１)查询每本图书的所有信息;(２)查询每个读者的读者编号、姓名和班级;(3）查询每条借阅记录的借阅天数(函数DA TEDIFＦ获取两个日期的差）；(4)查询被借阅过的图书的图书编号；（５)查询图书编号为“１00０6”的书名和作者;（6)查询库存数在５到10本之间的图书的图书编号和书名；(7）查询计算机系或电子系姓张的读者信息；(８)查询书名包括“英语”的图书信息；（9）统计男读者、女读者的人数；(10)统计各类图书的类别编号、平均定价以及库存总数;(11)统计每本书籍借阅的人数,要求输出图书编号和所借人数，查询结果按人数降序排列;（12）查询有库存的各类别图书的类别编号、类别名称和借阅数量;(１3）查询借阅了“大学英语”一书的读者,输出读者姓名、性别、系部；(１4）查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期;(LＥFT ＯUTEＲJOIN）(15)查询现有图书中价格最高的图书,输出书名、作者、定价;（1６）查询借阅了“大学英语”但没有借阅“C++程序设计”的读者，输出读者姓名、性别、系部；（17）统计借阅了2本以上图书的读者信息;(18)查询借阅了“大学英语”一书或者借阅了“C+＋程序设计”一书的读者信息；（用集合查询完成）(19）查询既借阅了“大学英语”一书又借阅了“C+＋程序设计”一书的读者信息;（用集合查询完成）(20)查询计算机系中比其他系所有读者借书数量都多的读者的信息;(２1）在读者信息表中插入一条新的记录(读者编号：R１0011；姓名：张三;所在系：电子系)；（22)定义一个表tｂ＿bｏoknew，包含图书编号、书名和类别名称字段，要求将类编编号为“3”的图书的图书编号、书名和类别名称插入到tb＿bkneｗ表中;（23）将类别编号为“3”的所有图书的库存数增加５;(２４）将“C++程序设计”这本书的归还日期增加一个月(函数DAＴEＡDＤ）。

（25)删除姓名为“张三”的读者的信息;（26）删除tｂ_bｋnew表中的所有数据；(27）创建一个名为“读者借阅信息＿VIＥＷ”的视图，要求显示计算机系所有读者的借阅信息,包括读者编号、姓名、所在系、图书编号、书名和借阅日期等字段,更新该视图时要保证只有计算机系的读者借阅信息;（２8)创建一个名为“图示借阅信息_VＩＥＷ”的视图,要求显示图书的借阅情况,包括图书编号、书名、库存数、借阅次数字段；(２９)查询借阅次数大于２的图书的图书编号、书名、库存数和借阅次数；（30）删除“图示借阅信息＿VIEＷ”视图。

三、实验步骤（1)查询每本图书的所有信息；sｅlecｔ*ｆｒom tｂ_boｏk;（2）查询每个读者的读者编号、姓名和班级；sｅｌｅct Rnuｍ,ｎame,clａssｆrｏm tｂ＿reader；（2) （3) (4）（3）查询每条借阅记录的借阅天数（函数DATEＤIFF获取两个日期的差)；seleｃt 'Borrowｄａy＇=datｅdifｆ(DD，Bodatａ,reｄata)froｍtb_borrow；（4)查询被借阅过的图书的图书编号；ｓeｌｅct Bnuｍfrom ｔb＿borroｗ;（5）查询图书编号为“１00０6”的书名和作者；sｅlecｔBｎaｍe,writｅrfｒoｍtｂ_bookwhere Ｂnｕm='10006';（6）查询库存数在５到10本之间的图书的图书编号和书名；sｅlｅｃt Ｂnｕm,Bnameｆｒom tb_boｏkwｈeｒe Knum betwｅeｎ5 ａnd 10;（7）查询计算机系或电子系姓张的读者信息;selｅct＊froｍtb＿rｅａderｗhere(ｎaｍｅlike'张%'ａｎd szxibu=＇计算机系')oｒ（naｍe liｋe'张%'and szxibu='电子系');(6)(７）（8）查询书名包括“英语”的图书信息；seleｃｔ＊frｏm ｔb_bookwheｒe Bnａmｅliｋe '%英语%';(9）统计男读者、女读者的人数；seleｃt Ｒsex,'num'＝cｏunｔ(*）from tb_rｅａｄeｒgroｕp by Rsｅx;（10)统计各类图书的类别编号、平均定价以及库存总数；selｅｃt Tynｕm,'Ａverａge ｐｒicｅ'=ＡVG(pricｅ)，'Bookstore'=SＵM（Knum)from tｂ_ｂooｋgroｕｐbｙTynum;(11）统计每本书籍借阅的人数,要求输出图书编号和所借人数,查询结果按人数降序排列；select Bnｕm,＇num'=COＵNT(Rnｕｍ)rom ｔb_bｏrｒowgrｏup by Bnumordｅr by ｎum dｅsc；（12）查询有库存的各类别图书的类别编号、类别名称和借阅数量;sｅｌecｔtb_ｂookｔype.Tynum，tb_bｏoｋtype.Tyname,＇ｑuantｉty＇=COＵNT(*)fｒom ｔｂ＿bｏok,tb_ｂooktyｐｅ,tb_boｒｒowwherｅtｂ_book．Ｋｎum＞0and tb_ｂoｏk．Tynum=tb_bｏｏktype.Ｔynuｍａnd tｂ＿bｏｏｋ.Ｂnum=tｂ_ｂorroｗ.Bnumgｒoup by ｔｂ_ｂoｏｋtｙpｅ.Tynｕm，ｔb＿bｏｏkｔype.Ｔｙname;（13）查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部;ｓeleｃｔname，Rsex,ｓzxiｂufrｏm tb＿reaｄｅｒwhｅｒeＲnuｍiｎ(sｅlect Rnｕｍfrom tb＿borrowwｈere Ｂnum=(selｅｃt Bｎumfroｍtｂ_ｂｏoｋwherｅBnaｍｅ=＇大学英语')）；（14）查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；(ＬEＦT OＵTＥＲＪOIN)select tb＿reader.Rnｕm,ｔb＿reader．name,Bnum, Bｏdａtafｒom ｔｂ_ｒeaｄｅｒＬＥＦT ＯUTER JOIＮｔｂ_boｒrow on（tb_reader.Ｒnum=tｂ_ｂoｒrow.Rnum)；(见下页)(15）查询现有图书中价格最高的图书，输出书名、作者、定价;selｅct Bｎame，wriｔer,pｒiｃeｆroｍtb_bookwhere ｐｒice=(seleｃt MAX(pｒice)froｍｔｂ_boｏｋ)；（16)查询借阅了“大学英语”但没有借阅“C++程序设计”的读者,输出读者姓名、性别、系部;(14）seｌeｃt ｎame,Ｒsex,szxibufrom tb_readｅrwhｅre Rnuｍin(select Rｎuｍｆrom ｔb_borｒowwheｒｅBnuｍ=(seｌect Bnumfrom tｂ_boｏkwhere Bnaｍe＝＇大学英语'）)exceptｓelect name,Rsｅx,szxｉｂufｒom tｂ_ｒeaderwheｒe Rnｕm ｉn(seｌｅcｔＲｎumfrom ｔb_ｂorrowｗheｒe Ｂｎum =(seｌect Ｂnumfrｏｍｔb＿ｂookwｈｅｒe Bname='Ｃ++程序设计') );（17）统计借阅了2本以上图书的读者信息；selecｔ*from tb_readｅｒwｈere Rnuｍin(selecｔRnｕmfｒom tb_ｂorｒoｗgroｕp bｙＲnumhａvｉｎg count(*)＞=2);（１8）查询借阅了“大学英语”一书或者借阅了“C++程序设计”一书的读者信息；（用集合查询完成）selｅｃt*from tｂ_rｅaderwheｒe Rnｕm in (select Rnumfrom tｂ_bｏrrowｗheｒe Bnｕm in(sｅlect Bnｕmfrom ｔb＿boｏkｗhｅre Bｎame ｉn('C+＋程序设计＇,＇大学英语'）));（19）查询既借阅了“大学英语”一书又借阅了“C++程序设计”一书的读者信息;（用集合查询完成)seｌect *from tｂ＿readerwherｅRｎum iｎ（seｌeｃt Ｒnuｍfrom tb_borrowwhere ｔb_borrow.Bnum ｉn(sｅlect Bnｕｍｆroｍtｂ_bookwheｒe Bｎamｅ='大学英语')iｎtｅrsｅctseｌｅct Rnumfrｏm tb＿borｒow,tb_ｂookｗhere tb_boｒrow.Bnｕm in(seleｃt Bnumｆｒoｍtb_ｂooｋｗheｒeＢnaｍe='C++程序设计＇）);（20）查询计算机系中比其他系所有读者借书数量都多的读者的信息；ｓeｌｅｃt*ｆｒom tb_readerwｈｅre szｘibu ='计算机系' and Rnuｍinﻩﻩﻩ(sｅｌecｔtb_ｒeadｅr.Rnumﻩfroｍtb＿reaｄer,tｂ_bｏrrowﻩﻩﻩﻩwhere szxibu ='计算机系'aｎd tb＿reaｄer.Rｎum=ｔb＿ｂorrow.Rnumﻩﻩﻩｇrouｐbｙtｂ_ｒeader.Rnum havｉng COUＮＴ(*)>ａll ﻩﻩﻩﻩﻩ(sｅleｃtＣＯUNT (*)ﻩﻩﻩﻩｆrom tb_ｒeaｄer,ｔb_borrｏwﻩﻩﻩﻩwherｅszxibu !='计算机系' aｎd tb＿reａｄer.Rｎum=ｔb＿borrow.Rnuｍﻩﻩﻩﻩﻩﻩgroｕp by tb＿rｅader．Ｒnuｍ)（21）在读者信息表中插入一条新的记录（读者编号:R10０1１;姓名：张三；所在系：电子系);IＮSEＲＴＩNTO tｂ_rｅader(Rnum,namｅ，szｘibu)ＶALUEＳ(＇R1０011','张三＇,＇电子系')；（2２）定义一个表tb＿bｏoｋｎｅｗ，包含图书编号、书名和类别名称字段，要求将类编编号为“3”的图书的图书编号、书名和类别名称插入到ｔb_bｋnew表中;creａｔe table tb＿bｏｏknew(Bnｕｍcｈar(１０)priｍary key,Bnamｅchar(20),Tynaｍe cｈａr(20)inserｔｉntｏtb_ｂｏoｋnｅwsｅｌｅｃt ｔb＿book．Bnum，tb_bｏok.Bnaｍｅ,tb_booｋtype.Tynａmeｆrｏm ｔb_bｏok,tb_bｏoｋｔyｐewhere tb_ｂooｋ.Ｔynum＝ｔb_booktyｐｅ.Tｙｎum ａnd tb_ｂook．Ｔｙnum＝'３';（23）将类别编号为“3”的所有图书的库存数增加10；uｐdate ｔb_book set Knｕｍ=Kｎuｍ＋10 ｗｈeｒe Tyｎｕm='３'（24）将“C＋+程序设计”这本书的归还日期增加二个月(函数DATEADD)。