《数据库-SQL语言的实验.doc》由会员分享，可在线阅读，更多相关《数据库-SQL语言的实验.doc（12页珍藏版）》请在三一办公上搜索。
1、精选优质文档-倾情为你奉上北 华 航 天 工 业 学 院数据库原理与应用实验报告报告题目： SQL语言 作者所在系部： 计算机系 作者所在专业： 网络工程 作 者 学 号 ： 作 者 姓 名 ： 张旭刚 指导教师姓名： 李建义 完 成 时 间 ： 2013 4.24 北华航天工业学院教务处制SQL语言一、 实验目的1、 理解数据库以及数据表的设计；2、 熟悉SQL Server2005中的数据类型；3、 熟悉使用SQL语句创建和删除模式和索引；4、 掌握使用SQL语句创建、修改和删除数据表；5、 掌握使用SQL语句查询表中的数据；6、 掌握使用SQL语句插入、修改和删除数据表中的数据；7、 掌
2、握使用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。2、通过SQL语句在该数据库中创建模式L-
3、C。（二）创建和管理数据表要求为各数据表的字段选择合适的数据类型及名称；为各数据表设置相应的完整性约束条件。1、通过SQL语句将以下数据表创建在L-C模式下：课程信息表（tb_course）课程编号、课程名、先修课、学分2、通过SQL语句将以下数据表创建在该数据库的默认模式dbo下：图书类别信息表（tb_booktype)类别编号、类别名称图书信息表（tb_book）图书编号、类别编号、书名、作者、出版社、定价、库存数读者信息表（tb_reader）读者编号、姓名、性别、学号、班级、系部借阅信息表（tb_borrow)图书编号、读者编号、借阅日期、归还日期3、通过SQL语句对读者信息表进行修改
4、：删除系部字段、添加所在系字段。4、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL。5、通过SQL语句删除课程信息表。（三）创建和删除索引1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY，要求按照该表中库存数字段的降序创建。2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME，要求按照该表中的姓名字段的升序创建。3、使用SQL语句删除之前创建的两个索引。（四）数据库及数据表设计根据周围的实际应用情况，自选一个小型的数据库应用项目进行研究，完成该系统的设计。要求2人一组，通过需求分析，列出系统的主要功能，并完成该系统数据库的逻辑
5、结构设计。例如可选择学籍管理系统、企业进销存管理系统、人事管理系统或在线考试系统等。（五）数据查询通过SSMS向各数据表中添加以下记录。（1）图书类别信息表类别编号类别名称类别编号类别名称类别编号类别名称1数学4文学7建筑2英语5艺术8化学3计算机6电子信息9物理（2）图书信息表图书编号类别编号书名作者出版社定价库存数100013数据库管理王珊高等教育出版社35.5010100023软件测试贺平机械工业出版社24.605100033C+程序设计谭浩强清华大学出版社30.008100044红楼梦曹雪芹人民文学出版社70.005100054西游记罗贯中人民文学出版社60.008100064红与黑司
6、汤达人民文学出版社50.005100071高等数学李翼清华大学出版社28.004100088有机化学张翔高等教育出版社29.005100092大学英语王琳高等教育出版社25.0010100102英语教程王琳高等教育出版社25.005（3）读者信息表读者编号姓名性别学号班级所在系R10001张小航男08511计算机系R10002王文广女08511计算机系R10003李理女08511计算机系R10004李彦宏男08512计算机系R10005张丽霞女08512计算机系R10006王强男07211电子系R10007张宝田男07212电子系R10008宋文霞女07611建工系R10009刘芳菲女0881
7、1外语系R10010常江宁男08812外语系（4）借阅信息表图书编号读者编号借阅日期归还日期10002R100032009-9-202009-10-2010003R100032009-9-202009-10-2010004R100032009-9-302009-10-3010009R100032009-9-302009-10-3010009R100072009-5-202009-6-2010010R100072009-5-202009-6-2010009R100092009-5-302009-6-3010010R100092009-5-222009-6-2210002R100092009-5-
8、222009-6-2210003R100092009-5-302009-6-30对以上数据表，完成以下操作：（1）查询每本图书的所有信息；（2）查询每个读者的读者编号、姓名和班级；（3）查询每条借阅记录的借阅天数（函数DATEDIFF获取两个日期的差）；（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_booknew，包含图书编号、书名和类别名称字段，要求将类编编号为“3”的图书的图书编号、书名和类别名称插入到tb_bknew表中；（23）将类别编号为“3”的所有图书的库存数增加5；（24）将“C+程序设计”这本书的归还日期增加一个月（函数DATEADD）。（25）删除姓名为“张三”的读者的信息；（26）删除tb_bknew表中的所有数据；（27）创建一个名为
11、“读者借阅信息_VIEW”的视图，要求显示计算机系所有读者的借阅信息，包括读者编号、姓名、所在系、图书编号、书名和借阅日期等字段，更新该视图时要保证只有计算机系的读者借阅信息；（28）创建一个名为“图示借阅信息_VIEW”的视图，要求显示图书的借阅情况，包括图书编号、书名、库存数、借阅次数字段；（29）查询借阅次数大于2的图书的图书编号、书名、库存数和借阅次数；（30）删除“图示借阅信息_VIEW”视图。三、 实验步骤 （1）查询每本图书的所有信息； select * from tb_book; （2） 查询每个读者的读者编号、姓名和班级； select Rnum,name,class fro
12、m tb_reader; (2) (3) (4)（3） 查询每条借阅记录的借阅天数（函数DATEDIFF获取两个日期的差）； select Borrowday=datediff(DD,Bodata,redata) from tb_borrow; （4）查询被借阅过的图书的图书编号； select Bnum from tb_borrow;（5） 查询图书编号为“10006”的书名和作者； select Bname,writer from tb_book where Bnum=10006;（6） 查询库存数在5到10本之间的图书的图书编号和书名； select Bnum,Bname from tb
13、_book where Knum between 5 and 10;（7） 查询计算机系或电子系姓张的读者信息； select * from tb_reader where (name like 张%and szxibu=计算机系) or( name like 张%and szxibu=电子系); (6) (7)（8）查询书名包括“英语”的图书信息； select * from tb_book where Bname like %英语%;（9）统计男读者、女读者的人数； select Rsex,num=count(*) from tb_reader group by Rsex; （10）统计各
14、类图书的类别编号、平均定价以及库存总数； select Tynum,Average price=AVG(price),Bookstore=SUM(Knum) from tb_book group by Tynum; （11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列； select Bnum,num=COUNT(Rnum) rom tb_borrow group by Bnum order by num desc; （12）查询有库存的各类别图书的类别编号、类别名称和借阅数量； select tb_booktype.Tynum,tb_booktype.Tynam
15、e,quantity=COUNT(*) from tb_book,tb_booktype,tb_borrow where tb_book.Knum0 and tb_book.Tynum=tb_booktype.Tynum and tb_book.Bnum =tb_borrow.Bnum group by tb_booktype.Tynum,tb_booktype.Tyname; （13）查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部； select name,Rsex,szxibu from tb_reader where Rnum in ( select Rnum from tb
16、_borrow where Bnum= (select Bnum from tb_book where Bname=大学英语 ) ); （14）查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；（LEFT OUTER JOIN） select tb_reader.Rnum,tb_reader.name,Bnum, Bodata from tb_reader LEFT OUTER JOIN tb_borrow on (tb_reader.Rnum=tb_borrow.Rnum); （见下页）（15）查询现有图书中价格最高的图书，输出书名、作者、定价； select Bname,write
17、r,price from tb_book where price= (select MAX(price) from tb_book); （16）查询借阅了“大学英语”但没有借阅“C+程序设计”的读者，输出读者姓名、性别、系部； (14)select name,Rsex,szxibufrom tb_readerwhere Rnum in( select Rnum from tb_borrow where Bnum = (select Bnum from tb_book where Bname=大学英语) ) except select name,Rsex,szxibu from tb_reade
18、r where Rnum in (select Rnum from tb_borrow where Bnum = (select Bnum from tb_book where Bname=C+程序设计) ); （17） 统计借阅了2本以上图书的读者信息； select * from tb_reader where Rnum in( select Rnum from tb_borrow group by Rnum having count(*)=2); （18）查询借阅了“大学英语”一书或者借阅了“C+程序设计”一书的读者信息；（用集合查询完成） select * from tb_reader
19、 where Rnum in ( select Rnum from tb_borrow where Bnum in( select Bnum from tb_book where Bname in(C+程序设计,大学英语) ); （19）查询既借阅了“大学英语”一书又借阅了“C+程序设计”一书的读者信息；（用集合查询完成） select * from tb_reader where Rnum in ( select Rnum from tb_borrow where tb_borrow.Bnum in (select Bnum from tb_book where Bname=大学英语) in
20、tersect select Rnum from tb_borrow,tb_book where tb_borrow.Bnum in (select Bnum from tb_book where Bname=C+程序设计 ) ); （20）查询计算机系中比其他系所有读者借书数量都多的读者的信息； select * from tb_reader where szxibu =计算机系 and Rnum in (select tb_reader.Rnumfrom tb_reader,tb_borrowwhere szxibu =计算机系 and tb_reader.Rnum=tb_borrow.R
21、numgroup by tb_reader.Rnum having COUNT(*)all (select COUNT (*)from tb_reader,tb_borrow where szxibu !=计算机系 and tb_reader.Rnum=tb_borrow.Rnumgroup by tb_reader.Rnum) （21）在读者信息表中插入一条新的记录（读者编号：R10011；姓名：张三；所在系：电子系）； INSERT INTO tb_reader(Rnum,name,szxibu) VALUES(R10011,张三,电子系); （22）定义一个表tb_booknew，包含图
22、书编号、书名和类别名称字段，要求将类编编号为“3”的图书的图书编号、书名和类别名称插入到tb_bknew表中； create table tb_booknew (Bnum char(10) primary key, Bname char(20), Tyname char(20) insert into tb_booknew select tb_book.Bnum,tb_book.Bname,tb_booktype.Tyname from tb_book,tb_booktype where tb_book.Tynum=tb_booktype.Tynum and tb_book.Tynum=3;
23、（23）将类别编号为“3”的所有图书的库存数增加10； update tb_book set Knum=Knum+10 where Tynum=3（24） 将“C+程序设计”这本书的归还日期增加二个月（函数DATEADD）。 update tb_borrow set redata=DATEADD(MONTH,1,redata) where Bnum in (select Bnum from tb_book where Bname=C+程序设计) （25） 删除姓名为“张三”的读者的信息； delete from tb_reader where name=张三;（26） 删除tb_bknew表中
24、的所有数据； delete from tb_booknew（27） 创建一个名为“读者借阅信息_VIEW”的视图，要求显示计算机系所有读者的借阅信息，包括读者编号、姓名、所在系、图书编号、书名和借阅日期等字段，更新该视图时要保证只有计算机系的读者借阅信息； create view 读者借阅信息_VIEW(Rnum,name,szxibu, Bname,Bodata,redata) as select tb_reader.Rnum,name,szxibu,tb_book.Bname,Bodata,redata from tb_borrow,tb_reader,tb_book where tb_b
25、orrow.Rnum=tb_reader.Rnum and tb_book.Bnum=tb_borrow.Bnum and tb_borrow.Rnum in (select Rnum from tb_reader where szxibu=计算机系);（28） 创建一个名为“图示借阅信息_VIEW”的视图，要求显示图书的借阅情况，包括图书编号、书名、库存数、借阅次数字段； create view 图书借阅信息_VIEW (Bnum,Bname,Knum,times) as select tb_book.Bnum,Bname,Knum, times=COUNT(Rnum) from tb_bo
26、rrow,tb_book where tb_book.Bnum=tb_borrow.Bnum and tb_book.Bnum in (select Bnum from tb_borrow) group by tb_book.Bnum,Bname,Knum; （29）查询借阅次数大于2的图书的图书编号、书名、库存数和借阅次数； select tb_book.Bnum,Bname,Knum,COUNT(Rnum) from tb_book,tb_borrow where tb_book.Bnum=tb_borrow.Bnum and tb_book.Bnum in (select Bnum fr
27、om tb_borrow group by Bnum having COUNT(Rnum)2) group by tb_book.Bnum,Bname,Knum; （30）删除“图示借阅信息_VIEW”视图。 drop view 图书借阅信息_VIEW四 实验总结 本次试验总共做了四次，时间比较长，刚开始机房的许多电脑由于中病毒，我用代码建了几次数据库没成功。每次去机房做实验时，附加数据库时，总是连不上，所以光建表就建了四五次，后来才发现我的版本和机房里的不一样，所以后来我是在我笔记本上完成的。在这次试验中，我遇到了好多问题，主要是通过看我自己借的SQL2008解决的，当然也学了好多东西。以后得多看书，多试验。专心-专注-专业
