试验2——SQL语言

1、北华航天工业学院?数据库原理与应用?实验报告报告题目： SQL 语言作者所在院系：计算机与遥感信息技术学院作者所在专业：软件工程作者学号：2021405A607作者姓名：武俊其指导教师：春娥完成时间：2021.11.2北华航天工业学院教务处制SQL语言实验目的1、理解数据库以及数据表的设计；2、熟悉SQL Server2021中的数据类型；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 2021 联机丛书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、通过SQL

4、语句对图书信息表进行修改：将定价的数据类型改为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有机化学翔高等教育29.00510

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

7、宁男138120413812外语系(4)借阅信息表图书编号读名编R借阅日期归还日期10002R100032021-9-202021-10-2010003R100032021-9-202021-10-2010004R100032021-9-302021-10-3010009R100032021-9-302021-10-3010009R100072021-5-202021-6-2010010R100072021-5-202021-6-2010009R100092021-5-302021-6-3010010R100092021-5-222021-6-2210002R100092021-5-22202

8、1-6-2210003R100092021-5-302021-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)查询既借阅了 “大学英语 一书又借阅了 “ C+强序设计

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

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

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

13、(类别编号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(图书编号读者编号借阅日期归还日期);goALTER TABLE tb_reader

14、DROPCOLUMN(部;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_QUANTITY ON tb_book ;DROPINDEX IX_S_

15、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的书名和作者select 书名,作者from tb_bookwhere

16、图书编号='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 '%英语'9 .统计男读者,女读者的人数 select 性别,COUN*人

17、数 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 .查询有库存的各类别图书的类别编号,类别名称和借阅数量select tb_book .类别编号,类别名称,COUNT*借阅数量 from tb_b

18、ook , 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图书编号 inselect 图书编号 from tb_book where书名 ="大学英语sele

19、ct 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+程序设计的读者输出读者,性别,系部select ,性别,所在系from tb_readerwhere inselectf

20、rom tb_borrow where图书编号inselect图书编号from tb_bookwhere书名="大学英语 and not in selectfrom tb_borrowwhere图书编号inselect 图书编号 from tb_book where书名='C+程序设计,;17 .统计借阅了本以上图书的读者信息select *from tb_reader where读者编号 inselect 读者编号from tb_borrowgroup by 读者编号having COUNf 2;18 .查询计算机系中比其他系所有读者借书数量都多的读者的信息select *

21、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 图书编号 from tb_reader , tb_borrow where tb_reader .读者编号 =tb_borrow .读者编

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

23、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 where 读者编号 inselect 读者编号from tb_

24、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 .将"C+