数据库-SQL语言的实验
北华航天工业学院《数据库原理与应用》
实验报告
报告题目：SQL语言
作者所在系部：计算机系
作者所在专业：网络工程
作者学号：20114052233
作者姓名：张旭刚
指导教师姓名：李建义
完成时间：2013 4.24
北华航天工业学院教务处制
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）——课程编号、课程名、先修课、学分
2、通过SQL语句将以下数据表创建在该数据库的默认模式dbo下：
图书类别信息表（tb_booktype)——类别编号、类别名称
图书信息表（tb_book）——图书编号、类别编号、书名、作者、出版社、定价、库存数读者信息表（tb_reader）——读者编号、姓名、性别、学号、班级、系部
借阅信息表（tb_borrow)——图书编号、读者编号、借阅日期、归还日期
3、通过SQL语句对读者信息表进行修改：删除系部字段、添加所在系字段。
4、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL。
5、通过SQL语句删除课程信息表。
（三）创建和删除索引
1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY，要求按照该表中库存数字段的降序创建。
2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME，要求按照该表中的姓名字段的升序创建。
3、使用SQL语句删除之前创建的两个索引。
（四）数据库及数据表设计
根据周围的实际应用情况，自选一个小型的数据库应用项目进行研究，完成该系统的设计。要求2人一组，通过需求分析，列出系统的主要功能，并完成该系统数据库的逻辑结构设计。例如可选择学籍管理系统、企业进销存管理系统、人事管理系统或在线考试系统等。
（五）数据查询
通过SSMS向各数据表中添加以下记录。（1）图书类别信息表
（2
（3）读者信息表
对以上数据表，完成以下操作：
（1）查询每本图书的所有信息；
（2）查询每个读者的读者编号、姓名和班级；
（3）查询每条借阅记录的借阅天数（函数DATEDIFF获取两个日期的差）；
（4）查询被借阅过的图书的图书编号；
（5）查询图书编号为“10006”的书名和作者；
（6）查询库存数在5到10本之间的图书的图书编号和书名；
（7）查询计算机系或电子系姓张的读者信息；
（8）查询书名包括“英语”的图书信息；
（9）统计男读者、女读者的人数；
（10）统计各类图书的类别编号、平均定价以及库存总数；
（11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；
（12）查询有库存的各类别图书的类别编号、类别名称和借阅数量；
（13）查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部；
（14）查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；（LEFT OUTER JOIN）（15）查询现有图书中价格最高的图书，输出书名、作者、定价；
（16）查询借阅了“大学英语”但没有借阅“C++程序设计”的读者，输出读者姓名、性别、系部；
（17）统计借阅了2本以上图书的读者信息；
（18）查询借阅了“大学英语”一书或者借阅了“C++程序设计”一书的读者信息；（用集合查询完成）
（19）查询既借阅了“大学英语”一书又借阅了“C++程序设计”一书的读者信息；（用集合查询完成）
（20）查询计算机系中比其他系所有读者借书数量都多的读者的信息；
（21）在读者信息表中插入一条新的记录（读者编号：R10011；姓名：张三；所在系：电子系）；
（22）定义一个表tb_booknew，包含图书编号、书名和类别名称字段，要求将类编编号为“3”的图书的图书编号、书名和类别名称插入到tb_bknew表中；
（23）将类别编号为“3”的所有图书的库存数增加5；
（24）将“C++程序设计”这本书的归还日期增加一个月（函数DA TEADD）。
（25）删除姓名为“张三”的读者的信息；
（26）删除tb_bknew表中的所有数据；
（27）创建一个名为“读者借阅信息_VIEW”的视图，要求显示计算机系所有读者的借阅信息，包括读者编号、姓名、所在系、图书编号、书名和借阅日期等字段，更新该视图时要保证只有计算机系的读者借阅信息；
（28）创建一个名为“图示借阅信息_VIEW”的视图，要求显示图书的借阅情况，包括图书编号、书名、库存数、借阅次数字段；
（29）查询借阅次数大于2的图书的图书编号、书名、库存数和借阅次数；
（30）删除“图示借阅信息_VIEW”视图。
三、实验步骤
（1）查询每本图书的所有信息；
select *
from tb_book;
（2）查询每个读者的读者编号、姓名和班级；
select Rnum,name,class
from 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_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）统计各类图书的类别编号、平均定价以及库存总数；
select Tynum,'Average price'=A VG(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.Tyname,'quantity'=COUNT(*)
from tb_book,tb_booktype,tb_borrow
where tb_book.Knum>0 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_borrow
where Bnum=
(select Bnum
from tb_book
where Bname='大学英语'
)
);
（14）查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；（LEFT OUTER JOIN）select tb_reader.Rnum,tb_https://www.360docs.net/doc/c45841586.html,,Bnum, Bodata
from tb_reader LEFT OUTER JOIN tb_borrow on
(tb_reader.Rnum=tb_borrow.Rnum); （见下页）
（15）查询现有图书中价格最高的图书，输出书名、作者、定价；
select Bname,writer,price
from tb_book
where price=
(select MAX(price)
from tb_book);
（16）查询借阅了“大学英语”但没有借阅“C++程序设计”的读者，输出读者姓名、性别、
系部；
(14)
select name,Rsex,szxibu
from tb_reader
where Rnum in(
select Rnum
from tb_borrow
where Bnum =
(select Bnum
from tb_book
where Bname='大学英语')
)
except
select name,Rsex,szxibu
from tb_reader
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
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='大学英语')
intersect
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.Rnum
from tb_reader,tb_borrow
where szxibu ='计算机系' and tb_reader.Rnum=tb_borrow.Rnum
group by tb_reader.Rnum having COUNT(*)>all
(select COUNT (*)
from tb_reader,tb_borrow
where szxibu !='计算机系' and tb_reader.Rnum=tb_borrow.Rnum
group by tb_reader.Rnum) （21）在读者信息表中插入一条新的记录（读者编号：R10011；姓名：张三；所在系：电子系）；
INSERT
INTO tb_reader(Rnum,name,szxibu)
V ALUES('R10011','张三','电子系');
（22）定义一个表tb_booknew，包含图书编号、书名和类别名称字段，要求将类编编号为“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）将类别编号为“3”的所有图书的库存数增加10；
update tb_book set Knum=Knum+10 where Tynum='3'
（24）将“C++程序设计”这本书的归还日期增加二个月（函数DATEADD）。
update tb_borrow set redata=DA TEADD(MONTH,1,redata)
where Bnum in
(select Bnum from tb_book where Bname='C++程序设计')
（25）删除姓名为“张三”的读者的信息；
delete from tb_reader where name='张三';
（26）删除tb_bknew表中的所有数据；
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_borrow.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_borrow,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 from tb_borrow group by Bnum having COUNT(Rnum)>2)
group by tb_book.Bnum,Bname,Knum;
（30）删除“图示借阅信息_VIEW”视图。
drop view 图书借阅信息_VIEW
四．实验总结
本次试验总共做了四次，时间比较长，刚开始机房的许多电脑由于中病毒，我用代码建了几次数据库没成功。每次去机房做实验时，附加数据库时，总是连不上，所以光建表就建了四五次，后来才发现我的版本和机房里的不一样，所以后来我是在我笔记本上完成的。在这次试验中，我遇到了好多问题，主要是通过看我自己借的SQL2008解决的，当然也学了好多东西。以后得多看书，多试验。