实验2——SQL语言

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

2、数据；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。（参照SQL Server 2008

3、联机丛书）2、通过SQL语句在该数据库中创建模式L_C。（二）创建和管理数据表要求为各数据表的字段选择合适的数据类型及名称；为各数据表设置相应的完整性约束条件。1、通过SQL语句将以下数据表创建在L_C模式下：课程信息表（tb_course）课程编号、课程名、先修课、学分2、通过SQL语句将以下数据表创建在该数据库的默认模式dbo下：图书类别信息表（tb_booktype)类别编号、类别名称图书信息表（tb_book）图书编号、类别编号、书名、作者、出版社、定价、库存数读者信息表（tb_reader）读者编号、姓名、性别、学号、班级、系部借阅信息表（tb_borrow)图书编号、读者编号、借阅

4、日期、归还日期3、通过SQL语句对读者信息表进行修改：删除系部字段、添加所在系字段。4、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL。5、通过SQL语句删除课程信息表。（三）创建和删除索引1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY，要求按照该表中库存数字段的降序创建。2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME，要求按照该表中的姓名字段的升序创建。3、使用SQL语句删除之前创建的两个索引。（四）数据库及数据表设计根据周围的实际应用情况，自选一个小型的数据库应用项目进行研究，完成该系统的设计。通过需求分析，列出系

5、统的主要功能，并完成该系统数据库的逻辑结构设计。例如可选择学籍管理系统、企业进销存管理系统、人事管理系统或在线考试系统等。（五）数据查询通过SSMS向各数据表中添加以下记录。（1）图书类别信息表类别编号类别名称类别编号类别名称类别编号类别名称1数学4文学7建筑2英语5艺术8化学3计算机6电子信息9物理（2）图书信息表图书编号类别编号书名作者出版社定价库存数100013数据库管理王珊高等教育出版社35.5010100023软件测试贺平机械工业出版社24.605100033C+程序设计谭浩强清华大学出版社30.008100044红楼梦曹雪芹人民文学出版社70.005100054西游记罗贯中人民文学

6、出版社60.008100064红与黑司汤达人民文学出版社50.005100071高等数学李翼清华大学出版社28.004100088有机化学张翔高等教育出版社29.005100092大学英语王琳高等教育出版社25.0010100102英语教程王琳高等教育出版社25.005（3）读者信息表读者编号姓名性别学号班级所在系R10001张小航男135110113511计算机系R10002王文广女135110213511计算机系R10003李理女135110313511计算机系R10004李彦宏男135120113512计算机系R10005张丽霞女135120213512计算机系R10006王强男1221

7、10412211电子系R10007张宝田男122120412212电子系R10008宋文霞女126110412611建工系R10009刘芳菲女138110413811外语系R10010常江宁男138120413812外语系（4）借阅信息表图书编号读者编号借阅日期归还日期10002R100032014-9-202014-10-2010003R100032014-9-202014-10-2010004R100032014-9-302014-10-3010009R100032014-9-302014-10-3010009R100072014-5-202014-6-2010010R100072014-

8、5-202014-6-2010009R100092014-5-302014-6-3010010R100092014-5-222014-6-2210002R100092014-5-222014-6-2210003R100092014-5-302014-6-30对以上数据表，完成以下操作：（1）查询每本图书的所有信息；（2）查询每个读者的读者编号、姓名和班级；（3）查询每条借阅记录的借阅天数（函数DATEDIFF获取两个日期的差）；（4）查询被借阅过的图书的图书编号；（5）查询图书编号为“10006”的书名和作者；（6）查询库存数在5到10本之间的图书的图书编号和书名；（7）查询计算机系或电子系姓

9、张的读者信息；（8）查询书名包括“英语”的图书信息；（9）统计男读者、女读者的人数；（10）统计各类图书的类别编号、平均定价以及库存总数；（11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；（12）查询有库存的各类别图书的类别编号、类别名称和借阅数量；（13）查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部；（14）查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；（LEFT OUTER JOIN）（15）查询现有图书中价格最高的图书，输出书名、作者、定价；（16）查询借阅了“大学英语”但没有借阅“C+程序设计”的读者，输出读者姓名、性别、系部

10、；（17）统计借阅了2本以上图书的读者信息；（18）查询借阅了“大学英语”一书或者借阅了“C+程序设计”一书的读者信息；（用集合查询完成）（19）查询既借阅了“大学英语”一书又借阅了“C+程序设计”一书的读者信息；（用集合查询完成）（20）查询计算机系中比其他系所有读者借书数量都多的读者的信息；（21）在读者信息表中插入一条新的记录（读者编号：R10011；姓名：张三；所在系：电子系）；（22）定义一个表tb_bknew，包含图书编号、书名和类别名称字段，要求将类别编号为“3”的图书的图书编号、书名和类别名称插入到tb_bknew表中；（23）将类别编号为“3”的所有图书的库存数增加5；（24

11、）将“C+程序设计”这本书的归还日期增加一个月（函数DATEADD）。（25）删除姓名为“张三”的读者的信息；（26）删除tb_bknew表中的所有数据；（27）创建一个名为“读者借阅信息_VIEW”的视图，要求显示计算机系所有读者的借阅信息，包括读者编号、姓名、所在系、图书编号、书名和借阅日期等字段，更新该视图时要保证只有计算机系的读者借阅信息；（28）创建一个名为“图示借阅信息_VIEW”的视图，要求显示图书的借阅情况，包括图书编号、书名、库存数、借阅次数字段；（29）查询借阅次数大于2的图书的图书编号、书名、库存数和借阅次数；（30）删除“图示借阅信息_VIEW”视图。三、 实验步骤cr

12、eate database d_libraryon(name=db_library_data, filename='d:2015405A607武俊其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"AUTHORIZATION W

13、U /创建模式CREATE TABLE tb_course( 课程编号 int, 课程名 char(20), 先修课 char(20), 学分 int);CREATE TABLE tb_booktype( 类别编号 int, 类别名称 char(20);CREATE TABLE tb_book( 图书编号 int, 类别编号 int, 书名 char(20), 作者 char(20), 出版社 char(20), 定价 float, 库存数 int );CREATE TABLE tb_reader( 读者编号 char(20), 姓名 char(20), 性别 char(2), 学号 int,

14、 班级 char(10), 系部 char(20) );CREATE TABLE tb_borrow( 图书编号 int, 读者编号 char(20), 借阅日期 char(20), 归还日期 char(20),);goALTER TABLE tb_reader DROP COLUMN 系部 ; /删除系部ALTER TABLE tb_reader ADD 所在系 CHAR ; /添加所在系 LTER TABLE tb_book ALTER COLUMN 定价 REAL; /定价的数据类型改为REALDROP TABLE tb_course CASCADE; /删除课程信息表CREATE UN

15、IQUE INDEX IX_S_QUANTITY ON tb_book(库存数); CREATE UNIQUE INDEX IX_S_NAME ON tb_reader(姓名); /创建索引DROP INDEX IX_S_QUANTITY ON tb_book;DROP INDEX IX_S_NAME ON tb_reader; /删除索引1.查询每本图书的所有信息 select * from tb_book; 2.查询每个读者的读者编号,姓名和班级 select 读者编号,姓名,班级 from tb_reader ;3.查询每条借阅记录的借阅天数(函数 DATEDIFF 获取两个日期的差)

16、select datediff(DAY,借阅日期,归还日期)借阅天数 from tb_borrow4.查询被借阅过的图书的图书编号 select distinct 图书编号 from tb_borrow; 5.查询图书编号为"10006"的书名和作者 select 书名,作者 from tb_book where 图书编号='10006'6.查询库存数在到本之间的图书的图书编号和书名 select 图书编号,书名 from tb_book where 库存数 between 5 and 10; 7.查询计算机系或电子系姓张的读者信息 select * fro

17、m tb_reader where 姓名 like '张%'and(所在系='计算机系'or 所在系='电子系'); 8.查询书名包括"英语"的图书信息 select * from tb_book where 书名 like '%英语' 9.统计男读者,女读者的人数 select 性别, COUNT(*)人数 from tb_reader group by 性别; 10.统计各类图书的类别编号,平均定价以及库存总数 select 类别编号,AVG(定价)平均定价,sum(库存数)库存总数 from tb_boo

18、k group by 类别编号; 11.统计每本书籍借阅的人数要求输出图书编号和所借人数查询结果 按人数降序排列 select 图书编号,COUNT(*)所借人数 from tb_borrow group by 图书编号 order by COUNT(*) desc; 12.查询有库存的各类别图书的类别编号,类别名称和借阅数量 select tb_book.类别编号,类别名称,COUNT(*)借阅数量 from tb_book,tb_booktype,tb_borrow where tb_book.类别编号 =tb_booktype.类别编号 and tb_book.图书编号=tb_borro

19、w.图书编号 group by tb_book.类别编号 ,tb_booktype.类别名称; 13.查询借阅了大学英语一书的读者,输出读者姓名,性别,系部 select 姓名 ,性别 ,所在系 from tb_reader where 读者编号 in ( select 读者编号 from tb_borrow where 图书编号 in ( select 图书编号 from tb_book where 书名 ='大学英语') ) 14.查询每个读者的读者编号,姓名,所借图书编号及所借阅日期 select tb_reader.读者编号 ,姓名 ,借阅日期 from tb_read

20、er left outer join tb_borrow on tb_reader.读者编号 =tb_borrow.读者编号; 15.查询现有图书中价格最高的图书,输出书名,作者,定价 select 书名,作者,定价 from tb_book where 定价= (select MAX(定价 ) from tb_book ); 16.查询借阅了大学英语但没有借阅C+程序设计的读者输出读者姓名,性别,系部 select 姓名 ,性别, 所在系 from tb_reader where 姓名 in (select 姓名 from tb_borrow where 图书编号 in (select 图书

21、编号 from tb_book where 书名 ='大学英语') and 姓名 not in ( select 姓名 from tb_borrow where 图书编号 in (select 图书编号 from tb_book where 书名='C+程序设计') );17.统计借阅了本以上图书的读者信息 select * from tb_reader where 读者编号 in ( select 读者编号 from tb_borrow group by 读者编号 having COUNT(*)>2);18.查询计算机系中比其他系所有读者借书数量都多的读

22、者的信息 select * from tb_reader tb where 所在系='计算机系' 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(图书编号) from tb_reader,tb_borrow where tb_reader.读者编号 =

23、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 书名 ='大学英语') union select * from tb_reader where 读者编号 in

24、( 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 书名='大学英语') intersect select * from tb_reader

25、 where 读者编号 in (select 读者编号 from tb_borrow,tb_book where tb_borrow.图书编号=tb_book.图书编号 and 书名='C+程序设计')21.在读者信息表中插入一条新的记录 insert into tb_reader(读者编号,姓名,所在系) values ('R10011','张三','电子系'); 22.定义一个表 tb_booknew,要求将类别编号为"3"的图书的图书编号,书名和类别名称插入到 tb_bknew 表中 CREATE TABLE tb_booknew ( 图书编号 int, 书名 char(10), 类别名称 char(10), ); insert into tb_booknew select 图书编号,书名,类别名称 from tb_book,tb_booktype where tb_booktype.类别编号=tb_book.类别编号 and tb_book.类别编号='3' 23. 将类别编号为的所有图书的库存数增加 update tb_book set 库存数=库存数+5 where 类别编号='3' 24.将"C+程序设计"这本书的归还日