试验2——SQL语言

1、北华航天工业学院数据库原理与应用实验报告报告题目： SQL 语言作者所在院系：计算机与遥感信息技术学院作者所在专业：软件工程作者学号：2015405A607作者姓名：武俊其指导教师：春娥完成时间：2016.11.2北华航天工业学院教务处制SQL语言实验目的1、理解数据库以及数据表的设计；2、熟悉SQL Server2008中的数据类型；3、熟悉使用SQL语句创建和删除模式和索引；4、掌握使用SQL语句创建、修改和删除数据表；5、掌握使用SQL语句查询表中的数据；6、掌握使用SQL语句插入、修改和删除数据表中的数据;7、掌握使用SQL语句创建、删除、查询和更新视图。实验容（一）创建数据库和模式1

2、、通过SQL语句创建图书信息管理数据库，命名为“ db_Library "，数据文件和日志文件放在 D盘下以自己学号和命名的文件夹中，数据文件的逻辑名为 db_Library_data ,数据文件的物理名为db_Library_data.mdf ,文件初始大小为 10MB最大可增加至 300MB增巾高为10%日志文件的逻辑 名为db_Library_log ,日志文件的物理名为db_Library_data.ldf ,文件初始大小为 5MB最大可增加至200MB增巾昌为2MB （参照SQL Server 2008 联机丛书）2、通过SQL语句在该数据库中创建模式L_C。（二）创建和管

3、理数据表要求为各数据表的字段选择合适的数据类型及名称；为各数据表设置相应的完整性约束条件。1、通过SQL语句将以下数据表创建在L_C模式下：课程信息表（tb_course 课程编号、课程名、先修课、学分2、通过SQL语句将以下数据表创建在t数据库的默认模式dbo下：图书类别信息表（tb_booktype）类别编号、类别名称图书信息表（tb_book ）图书编号、类别编号、书名、作者、 、定价、库存数读者信息表（tb_reader ）读者编号、性别、学号、班级、系部 借阅信息表（tb_borrow）图书编号、读者编号、借阅日期、归还日期 3、通过SQL语句对读者信息表进行修改：删除系部字段、添加

4、所在系字段。4、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL5、通过SQL语句删除课程信息表。（三）创建和删除索引1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY要求按照该表中库存数字段的降序创建。2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME要求按照该表中的字段的升序创建。3、使用SQL语句删除之前创建的两个索引。（四）数据库及数据表设计根据周围的实际应用情况，自选一个小型的数据库应用项目进行研究，完成该系统的设计。通 过需求分析，列出系统的主要功能，并完成该系统数据库的逻辑结构设计。例如可选择学籍管理系 统、企业进

5、销存管理系统、人事管理系统或在线考试系统等。(五)数据查询通过ssmso各数据表中添加以下记录。(1)图书类别信息表类别编号类别名称类别编号类别名称类别编号类别名称1数学4乂字7建筑2英语5艺术8化学3计算机6电子信息9物理(2)图书信息表图书编号类别编号书名作者定价库存数100013数据库管理王珊高等教育35.5010100023软件测试贺平机械工业24.605100033C+程序设计谭浩强清华大学30.008100044红楼梦雪芹人民文学70.005100054西游记罗贯中人民文学60.008100064红与黑司汤达人民文学50.005100071高等数学翼清华大学28.004100088

6、有机化学翔高等教育29.005100092大学英语王琳高等教育25.0010100102英语教程王琳高等教育25.005(3)读者信息表读名编p性别学号班级所在系R10001小航男135110113511P 计算机系 R10002王文J女135110213511计算机系R10003理女135110313511计算机系R10004男135120113512计算机系R10005丽霞女135120213512计算机系R10006王强男122110412211r 电子系 R10007宝田男122120412212电子系R10008宋文霞女126110412611建工系R10009芳菲女13811041

7、3811p外语系 R10010常江宁男138120413812外语系(4)借阅信息表图书编号读名编R借阅日期归还日期10002R100032014-9-202014-10-2010003R100032014-9-202014-10-2010004R100032014-9-302014-10-3010009R100032014-9-302014-10-3010009R100072014-5-202014-6-2010010R100072014-5-202014-6-2010009R100092014-5-302014-6-3010010R100092014-5-222014-6-2210002R

8、100092014-5-222014-6-2210003R100092014-5-302014-6-30对以上数据表，完成以下操作：(1)查询每本图书的所有信息；(2)查询每个读者的读者编号、和班级；(3)查询每条借阅记录的借阅天数(函数DATEDIF或取两个日期的差)；(4)查询被借阅过的图书的图书编号；(5)查询图书编号为“ 10006”的书名和作者；(6)查询库存数在 5到10本之间的图书的图书编号和书名；(7)查询计算机系或电子系姓的读者信息；(8)查询书名包括“英语”的图书信息；(9)统计男读者、女读者的人数；(10)统计各类图书的类别编号、平均定价以及库存总数；(11)统计每本书籍

9、借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；(12)查询有库存的各类别图书的类别编号、类别名称和借阅数量；(13)查询借阅了 “大学英语” 一书的读者，输出读者、性别、系部；(14)查询每个读者的读者编号、所借图书编号以及所借阅日期；(LEFT OUTER JOIN(15)查询现有图书中价格最高的图书，输出书名、作者、定价；(16)查询借阅了 “大学英语”但没有借阅“C+锂序设计”的读者，输出读者、性别、系部；(17)统计借阅了 2本以上图书的读者信息；(18)查询借阅了 “大学英语” 一书或者借阅了 “ C+强序设计” 一书的读者信息；(用集合查询完成)(19)查询既借阅

10、了 “大学英语” 一书又借阅了 “ C+强序设计” 一书的读者信息；(用集合查询完成)(20)查询计算机系中比其他系所有读者借书数量都多的读者的信息；(21)在读者信息表中插入一条新的记录(读者编号：R10011;:三；所在系：电子系)；(22)定义一个表tb_bknew,包含图书编号、书名和类别名称字段，要求将类别编号为“3”的图书的图书编号、书名和类别名称插入到tb_bknew表中；(23)将类别编号为“ 3”的所有图书的库存数增加5;(24)将“C+程序设计”这本书的归还日期增加一个月(函数DATEADR(25)删除为“三”的读者的信息；(26)删除tb_bknew表中的所有数据；(27

11、)创建一个名为“读者借阅信息 _VIEW的视图，要求显示计算机系所有读者的借阅信息， 包括读者编号、所在系、图书编号、书名和借阅日期等字段，更新该视图时要保证只有计算机系的 读者借阅信息；(28)创建一个名为“图示借阅信息_VIEW的视图，要求显示图书的借阅情况，包括图书编号、 书名、库存数、借阅次数字段；(29)查询借阅次数大于2的图书的图书编号、书名、库存数和借阅次数；(30)删除“图示借阅信息 _VIEW视图。三、实验步骤create database d_library on (name=db_library_data ,filename ='d:2015405A607 武俊其

12、 db_library_data.mdf' ,size =10,maxsize =300,filegrowth =1) log on(name=db_library_log ,filename ='d:2015405A607 武俊其 db_library_data.ldf' ,size =5, maxsize =200,filegrowth =2)/ 创建数据库create schema"L_C" AUTHORIZATIONWU/ 创建模式CREATETABLE tb_course(课程编号int ,课程名char(20),先修课char(20),学

13、分int); CREATETABLE tb_booktype(类别编号int ,类别名称char (20); CREATETABLE tb_book(图书编号int ,类别编号int ,书名char(20),作者char(20),char (20), 定价float ,库存数int); CREATETABLE tb_reader (char (20), char( 20),char (2), int ,char (10), char(20)tb_borrowint ,char(20) char(20)char (20),读者编号性别学号班级系部);CREATETABLE(图书编号读者编号借阅日

14、期归还日期);goALTER TABLE tb_readerDROPCOLUMN(部;ALTER TABLE tb_reader ADD 所在系 CHAR; LTER TABLE tb_book ALTER COLUMNS介 REAL DROPTABLE tb_course CASCADE/删除系部/添加所在系/定价的数据类型改为REAL/删除课程信息表CREATEUNIQUE INDEX IX_S_QUANTITY ON tb_book (库存数);CREATEUNIQUE INDEX IX_S_NAME ON tb_reader ();/ 创建索引DROPINDEX IX_S_QUANTI

15、TY ON tb_book ;DROPINDEX IX_S_NAMEONtb_reader ;删除索引1 .查询每本图书的所有信息select * from tb_book ;2 .查询每个读者的读者编号，和班级select 读者编号,班级from tb_reader ;3 .查询每条借阅记录的借阅天数(函数DATEDIFF获取两个日期的差)select datediff (DAY借阅日期，归还日期)借阅天数 from tb_borrow4 .查询被借阅过的图书的图书编号select distinct图书编号from tb_borrow ;5 .查询图书编号为"10006”的书名和作

16、者select 书名，作者from tb_bookwhere 图书编号='10006,;6 .查询库存数在到本之间的图书的图书编号和书名select 图书编号，书名 from tb_bookwhere 库存数 between 5 and 10 ;7 .查询计算机系或电子系姓的读者信息 select *from tb_reader where like '%' and（所在系='计算机系'or所在系='电子系'）;8 .查询书名包括“英语”的图书信息 select *from tb_book where 书名 like '%英语&#

17、39;9 .统计男读者，女读者的人数 select 性别，COUN（*）人数 from tb_reader group by 性另I ;10 .统计各类图书的类别编号，平均定价以及库存总数select类别编号，AVQ定彳）平均定价，sum（库存数）库存总数 from tb_book group by类别编号；11 .统计每本书籍借阅的人数要求输出图书编号和所借人数查询结果按人数降序排列select图书编号，COUNT）所借人数 from tb_borrow group by图书编号 order by COUNT*） desc;12 .查询有库存的各类别图书的类别编号，类别名称和借阅数量sele

18、ct tb_book .类别编号，类别名称，COUNT*）借阅数量 from tb_book , tb_booktype , tb_borrowwhere tb_book .类别编号 =tb_booktype .类别编号 and tb_book .图书编号=tb_borrow .图书编号 group by tb_book .类别编号 ，tb_booktype .类别名称；13 .查询借阅了大学英语一书的读者，输出读者，性别，系部select ，性别，所在系from tb_reader where读者编号 in （select读者编号from tb_borrow where图书编号 in（sel

19、ect 图书编号 from tb_book where书名 ="大学英语）select tb_reader .读者编号 from tbreader left outer14 .查询每个读者的读者编号,所借图书编号及所借阅日期,，借阅日期join tb_borrow on tb_reader .读者编号 =tb_borrow .读者编号;15 .查询现有图书中价格最高的图书，输出书名，作者，定价select 书名，作者，定价from tb_book where 定价=（select MAX定彳） from tb_book ）;16 .查询借阅了大学英语但没有借阅C+程序设计的读者输出读

20、者，性别，系部select ，性别，所在系from tb_readerwhere in（selectfrom tb_borrow where图书编号in（select图书编号from tb_bookwhere书名="大学英语） and not in （ selectfrom tb_borrowwhere图书编号in（select 图书编号 from tb_book where书名='C+程序设计,）;17 .统计借阅了本以上图书的读者信息select *from tb_reader where读者编号 in（select 读者编号from tb_borrowgroup by

21、读者编号having COUNf） 2）;18 .查询计算机系中比其他系所有读者借书数量都多的读者的信息select *from tb_reader tbwhere所在系='计算机系'and读者编号in （select tb_reader .读者编号 from tb_reader , tb_borrow where tb_reader .读者编号=tb_borrow .读者编号 and tb_reader .读者编号 =tb .读者编号 group by tb_reader .读者编号 having count （图书编号）>any（ select count （图书编号

22、） from tb_reader , tb_borrow where tb_reader .读者编号 =tb_borrow .读者编号 and所在系 <>'计算 机系group by tb_reader .读者编号 ）;19 .查询借阅了大学英语一书或者借阅了C+程序设计一书的读者信息用集合查询完成select * from tb_reader where读者编号 in（select tb_borrow .图书编号 from tb_borrow , tb_book where tb_borrow .图书编号=tb_book .图书编号 and书名 ="大学英语）

23、union select * from tb_reader where读者编号 in（select读者编号from tb_borrow , tb_book where tb_borrow .图书编号=tb_book .图书编号 and书名='C+程序设计,）20 .查询既借阅了大学英语一书又借阅了C+程序设计一书的读者信息用集合查询完成select * from tb_reader where读者编号 in （select 读者编号from tb_borrow , tb_book where tb_borrow .图书编号 =tb_book .图书编号 and书名="大学英语

24、'） intersect select * from tb_reader where 读者编号 in（select 读者编号from tb_borrow , tb_book where tb_borrow .图书编号=tb_book ,图书编号 and书名="C+程序 设计）21 .在读者信息表中插入一条新的记录insertinto tb_reader （读者编号,所在系） values （'R10011','三'，'电子系'）;22 .定义一个表tb_booknew ,要求将类别编号为“3"的图书的图书编号，书名和类别名称插入到tb_bknew表中CREATETABLE tb_booknew（图书编号int ,书名 char （10）,类别名称char （10）,）；insertintotb_booknewselect图书编号，书名，类别名称from tb_book , tb_booktypewhere tb_booktype .类别编号=tb_book .类别编号 and tb_book .类别编号="3'23 .将类别编号为的所有图书的库存数增加update tb_bookset库存数=库存数+5 where类别编号="3"24 .将&quo