北华航天工业学院《数据库原理与应用》实验报告报告题目：作者所在系部：作者所在专业：作者学号：作者姓名：指导教师姓名：完成时间：北华航天工业学院教务处制交互式SQL一、实验目的1、理解数据库以及数据表的设计；2、熟悉SQL Server2005中的数据类型；3、熟悉使用SQL语句创建和删除模式和索引；4、掌握使用SQL语句创建、修改和删除数据表；5、掌握使用SQL语句查询表中的数据；6、掌握使用SQL语句插入、修改和删除数据表中的数据；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。

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

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

1、通过SQL语句将以下数据表创建在L-C模式下：课程信息表（tb_course）——课程编号、课程名、先修课、学分CREATE SCHEMA"L_C" AUTHORIZATION dbo;CREATE TABLE L_C.tb_course/* 课程信息表（tb_course）课程编号、课程名、先修课、学分*/(课程编号CHAR(9) PRIMARY KEY,课程名CHAR(20) UNIQUE,先修课CHAR(10),学分CHAR(100));2、通过SQL语句将以下数据表创建在该数据库的默认模式dbo下：图书类别信息表（tb_booktype)——类别编号、类别名称图书信息表（tb_book）——图书编号、类别编号、书名、作者、出版社、定价、库存数读者信息表（tb_reader）——读者编号、姓名、性别、学号、班级、系部借阅信息表（tb_borrow)——图书编号、读者编号、借阅日期、归还日期CREATE TABLE tb_booktype /* 图书类别信息表（tb_booktype)类别编号、类别名称*/(类别编号CHAR(9) PRIMARY KEY,类别名称CHAR(10));CREATE TABLE tb_book /*图书信息表（tb_book）——图书编号、类别编号、书名、作者、出版社、定价、库存数*/(图书编号CHAR(100)PRIMARY KEY,类别编号CHAR(10),书名CHAR(20),作者CHAR(20),出版社CHAR(20),定价float,库存数int);CREATE TABLE tb_reader /*读者信息表（tb_reader）——读者编号、姓名、性别、学号、班级、系部(tie)*/(读者编号CHAR(100) PRIMARY KEY,读者姓名CHAR(10),读者性别CHAR(10),读者学号CHAR(100),读者班级CHAR(20),读者系部CHAR(20));CREATE TABLE tb_borrow /*借阅信息表（tb_borrow)——图书编号、读者编号、借阅日期、归还日期*/(图书编号CHAR(100),读者编号CHAR(100),借阅日期CHAR(100),归还日期CHAR(100)PRIMARY KEY(图书编号,读者编号),);3、通过SQL语句对读者信息表进行修改：删除系部字段、添加所在系字段。

ALTER TABLE tb_reader DROP column 读者系部;ALTER TABLE tb_reader ADD 读者系部char;4、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL。

ALTER TABLE tb_book ALTER COLUMN 定价real;通过SQL语句删除课程信息表。

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

DROP TABLE L_C.tb_course;（三）创建和删除索引1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY，要求按照该表中库存数字段的降序创建。

CREATE INDEX IX_S_QUANTITY ON tb_book(库存数DESC);2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME，要求按照该表中的姓名字段的升序创建。

CREATE UNIQUE INDEX IX_S_NAME ON tb_reader(读者姓名ASC);3、使用SQL语句删除之前创建的两个索引。

DROP INDEX tb_book.IX_S_QUANTITY,tb_reader.IX_S_NAME;（四）数据库及数据表设计根据周围的实际应用情况，自选一个小型的数据库应用项目进行研究，完成该系统的设计。

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

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

（五）数据查询通过SSMS向各数据表中添加以下记录。

（1）图书类别信息表insert into tb_booktype values('2','英语');insert into tb_booktype values('3','计算机');insert into tb_booktype values('4','文学');insert into tb_booktype values('5','艺术');insert into tb_booktype values('6','电子信息');insert into tb_booktype values('7','建筑');insert into tb_booktype values('8','化学');insert into tb_booktype values('9','物理');SELECT *FROM tb_booktype order by 类别编号ASC;delete from tb_booktype;（2）图书信息表insert into tb_book values('10003','3','C++程序设计','谭浩强','清华大学出版社','30.0','8'); insert into tb_book values('10004','4','红楼梦','曹雪芹','人民文学出版社','70.00','5'); insert into tb_book values('10005','4','西游记','罗贯中','人民文学出版社','60.00','8'); insert into tb_book values('10006','4','红与黑','司汤达','人民文学出版社','50.00','5'); insert into tb_book values('10007','1','高等数学','李翼','清华大学出版社','28.00','4');insert into tb_book values('10008','8','有机化学','张翔','高等教育出版社','29.00','5'); insert into tb_book values('10009','2','大学英语','王琳','高等教育出版社','25.00','10'); insert into tb_book values('10010','2','英语教程','王琳','高等教育出版社','25.00','5'); SELECT *FROM tb_book;delete from tb_book;insert into tb_reader values('R10003','李理','女','0851103','08511','计算机系');insert into tb_reader values('R10004','李彦宏','男','0851201','08512','计算机系'); insert into tb_reader values('R10005','张丽霞','女','0851202','08512','计算机系'); insert into tb_reader values('R10006','王强','男','0721104','07211','电子系');insert into tb_reader values('R10007','张宝田','男','0721204','07212','电子系');insert into tb_reader values('R10008','宋文霞','女','0761104','07611','建工系');insert into tb_reader values('R10009','刘芳菲','女','0851106','08811','外语系');insert into tb_reader values('R10010','常江宁','男','0881204','08812','外语系'); SELECT *FROM tb_reader;delete from tb_reader;（4insert into tb_borrow values('10002','R10003','2009-9-20','2009-10-20'); insert into tb_borrow values('10003','R10003','2009-9-20','2009-10-20');insert into tb_borrow values('10004','R10003','2009-9-30','2009-10-30');insert into tb_borrow values('10009','R10003','2009-9-30','2009-10-30');insert into tb_borrow values('10009','R10007','2009-5-20','2009-6-20');insert into tb_borrow values('100010','R10007','2009-5-20','2009-6-20');insert into tb_borrow values('10009','R10009','2009-5-20','2009-6-30');insert into tb_borrow values('10010','R10009','2009-5-22','2009-6-22');insert into tb_borrow values('10002','R10009','2009-5-22','2009-6-22');insert into tb_borrow values('10003','R10009','2009-5-30','2009-6-30');SELECT *FROM tb_borrow;delete from tb_borrow;对以上数据表，完成以下操作：（1）查询每本图书的所有信息；SELECT * FROM tb_book;（2）查询每个读者的读者编号、姓名和班级；SELECT 读者编号,姓名,班级FROM tb_reader;（3）查询每条借阅记录的借阅天数（函数DATEDIFF获取两个日期的差）；SELECT DATEDIFF (day,借阅日期,归还日期) AS DATEDATA from tb_borrow（4）查询被借阅过的图书的图书编号；select distinct 图书编号from tb_borrow（5）查询图书编号为“10006”的书名和作者；SELECT 书名,作者FROM tb_book where 图书编号='10006'（6）查询库存数在5到10本之间的图书的图书编号和书名；SELECT 图书编号,书名FROM tb_book where 库存数between 5 and 10（7）查询计算机系或电子系姓张的读者信息；select * from tb_reader where 所在系in('计算机','电子系')and 姓名like '张%'（8）查询书名包括“英语”的图书信息；select * from tb_book where 书名like '%英语%';（9）统计男读者、女读者的人数；select 性别,count(读者编号) 个数from tb_reader group by 性别（10）统计各类图书的类别编号、平均定价以及库存总数；select 类别编号,avg(定价),sum(库存数) from tb_book group by 类别编号（11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；select 图书编号,count(读者编号) 人数from tb_borrow group by 图书编号order by 人数desc（12）查询有库存的各类别图书的类别编号、类别名称和借阅数量；select tb_book.类别编号,类别名称,COUNT(*) 借阅数量from tb_book,tb_borrow,tb_booktypeWHERE tb_book.类别编号=tb_booktype.类别编号AND tb_book.图书编号=tb_borrow.图书编号GROUP BY tb_book.类别编号,tb_booktype.类别名称;（13）查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部；sselect 读者姓名,读者性别,读者系部from tb_readerwhere 读者编号in(select 读者编号from tb_borrow where 图书编号in(select 图书编号from tb_book where 书名='大学英语'))（14）查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；（LEFT OUTER JOIN）select tb_reader.读者编号,读者姓名,tb_book.图书编号,借阅日期from tb_reader,tb_book,tb_borrowWHERE tb_reader.读者编号=tb_borrow.读者编号AND tb_book.图书编号=tb_borrow.图书编号;;（15）查询现有图书中价格最高的图书，输出书名、作者、定价；select 书名,作者,定价from tb_book where 定价in (select max(定价) from tb_book)（16）查询借阅了“大学英语”但没有借阅“C++程序设计”的读者，输出读者姓名、性别、系部；select 读者姓名,读者性别,读者系部from tb_readerWHERE tb_reader.读者编号IN(select tb_borrow.读者编号from tb_borrowWHERE tb_borrow.读者编号IN(select tb_borrow.读者编号from tb_book,tb_borrow WHERE tb_book.图书编号=tb_borrow.图书编号and tb_book.书名='大学英语'except select tb_borrow.读者编号from tb_book,tb_borrowWHERE tb_book.书名='C++程序设计' and tb_book.图书编号=tb_borrow.图书编号))（117）统计借阅了2本以上图书的读者信息；select * from tb_reader where 读者编号in(select 读者编号from tb_borrow group by 读者编号having count(*)>2)（18）查询借阅了“大学英语”一书或者借阅了“C++程序设计”一书的读者信息；（用集合查询完成）select * from tb_readerWHERE 读者编号IN(select 读者编号from tb_borrow,tb_bookWHERE tb_borrow.图书编号=tb_book.图书编号AND 书名='大学英语')UNION select *from tb_readerWHERE 读者编号IN(select 读者编号from tb_borrow,tb_bookWHERE tb_borrow.图书编号=tb_book.图书编号AND 书名='C++程序设计')（19）查询既借阅了“大学英语”一书又借阅了“C++程序设计”一书的读者信息；（用集合查询完成）select 读者姓名,读者性别,读者系部from tb_reader where 读者编号in(select 读者编号from tb_borrow where 图书编号in(select 图书编号from tb_book where 书名='大学英语' ))intersect select 读者姓名,读者性别,读者系部from tb_reader where 读者编号in (select 读者编号from tb_borrow where 图书编号in(select 图书编号from tb_book where 书名='c++程序设计'))（20）查询计算机系中比其他系所有读者借书数量都多的读者的信息；select *from tb_reader tbr1where 读者系部='计算机系'and 读者编号in( select tb_reader.读者编号from tb_reader,tb_borrowwhere tb_reader.读者编号=tb_borrow.读者编号and tb_reader.读者编号=tbr1.读者编号group by tb_reader.读者编号having count(图书编号)>any(select count(图书编号)from tb_reader,tb_borrowwhere tb_reader.读者编号=tb_borrow.读者编号and读者系部<>'计算机系'group by tb_reader.读者编号))（21）在读者信息表中插入一条新的记录（读者编号：R10011；姓名：张三；所在系：电子系）；Insert into tb_reader(读者编号,姓名,所在系) values('R10011','张三','电子系')（22）定义一个表tb_bknew，包含图书编号、书名和类别名称字段，要求将类别编号为“3”的图书的图书编号、书名和类别名称插入到tb_bknew表中；CREATE TABLE tb_booknew(图书编号CHAR(15),书名CHAR(15),类别名称CHAR(15));insert into tb_booknewselect tb_book.图书编号,tb_book.书名,tb_booktype.类别名称from tb_book,tb_booktypeWHERE tb_book.类别编号=tb_booktype.类别编号AND tb_book.类别编号='3';（23）将类别编号为“3”的所有图书的库存数增加5；UPDATE tb_book set 库存数=库存数+5WHERE 类别编号='3';（24）将“C++程序设计”这本书的归还日期增加一个月（函数DATEADD）。