实验 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）——课程编号Course number 、课程名Course name 、先修课The first course 、学分credit2、通过SQL语句将以下数据表创建在该数据库的默认模式dbo下：图书类别信息表（tb_booktype)——类别编号Type number 、类别名称Category name图书信息表（tb_book）——图书编号ISBN 、类别编号Type number、书名title 、作者author、BookPublic、定价BookPrice、库存数Inventory number读者信息表（tb_reader）——读者编号Reader ID 、、性别、学号Student ID 、班级、系部pastern 借阅信息表（tb_borrow)——图书编号、读者编号、借阅日期Borrowing date 、归还日期Return date3、通过SQL语句对读者信息表进行修改：删除系部字段、添加所在系字段。
4、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL。
5、通过SQL语句删除课程信息表。
（三）创建和删除索引1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY，要求按照该表中库存数字段的降序创建。
2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME，要求按照该表中的字段的升序创建。
3、使用SQL语句删除之前创建的两个索引。
（四）数据库及数据表设计根据周围的实际应用情况，自选一个小型的数据库应用项目进行研究，完成该系统的设计。
要求2人一组，通过需求分析，列出系统的主要功能，并完成该系统数据库的逻辑结构设计。
例如可选择学籍管理系统、企业进销存管理系统、人事管理系统或在线考试系统等。
（五）数据查询通过SSMS向各数据表中添加以下记录。
（1（2）图书信息表（4）借阅信息表对以上数据表，完成以下操作：（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++程序设计”这本书的归还日期增加一个月（函数DATEADD）。
（25）删除为“三”的读者的信息；（26）删除tb_bknew表中的所有数据；（27）创建一个名为“读者借阅信息_VIEW”的视图，要求显示计算机系所有读者的借阅信息，包括读者编号、、所在系、图书编号、书名和借阅日期等字段，更新该视图时要保证只有计算机系的读者借阅信息；（28）创建一个名为“图示借阅信息_VIEW”的视图，要求显示图书的借阅情况，包括图书编号、书名、库存数、借阅次数字段；（29）查询借阅次数大于2的图书的图书编号、书名、库存数和借阅次数；（30）删除“图示借阅信息_VIEW”视图。
三、实验步骤use[master]gocreate database db_Libraryon primary(name=N'db_Library_data',filename='E:\SQL\db_Library_data.mdf',size=10MB,maxsize=300MB,filegrowth=10%)log on(name='db_Library_log',filename='E:\SQL\db_Library_data.ldf',size=5MB,maxsize=200MB,filegrowth=2MB)gouse db_Librarygocreate schema"L-C"authorization guest create table tb_course--课程信息表(Coursenumber int,Coursename char(20),Thefirstcourse char(20),Credit int)gocreate table tb_booktype--图书类别信息表(Typenumber int,Categoryname char(20))gocreate table tb_book--图书信息表(ISBN int,Typenumber int,Title char(20),Author char(20),BookPublic char(20),BookPrice float,Inventorynumber int)gocreate table tb_reader--读者信息表(ReaderID char(20),Name char(20),Sex char(2),StudentID int,Class char(20),Pastern char(20))gocreate table tb_borrow--借阅信息表(ISBN int,ReaderID char(20),Borrowingdate char(20),Returndate char(20))goalter table tb_reader drop column Pasternalter table tb_reader add column Pasternalter table tb_book alter column BookPrice realdrop table tb_course--创建和删除索引create index IX_S_QUANTITY on tb_book(Inventorynumber)create index IX_S_NAME on tb_reader(Name asc)drop index IX_S_QUANTITYdrop index IX_S_NAME--图书类别信息表信息insert into tb_booktype values(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,'电子信息')go--图书信息表信息insert into tb_book values(10001,3,'数据库管理','王珊','高等教育',35.50,10) insert into tb_book values(10002,3,'软件测试','贺平','机械工业',24.60,5) insert into tb_book values(10003,3,'C++程序设计','谭浩强','清华大学',30.00,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)go--读者信息表信息insert into tb_reader values('R10001','小航','男',0851101,'08511','计算机系') insert into tb_reader values('R10002','王文广','女',0851102,'08511','计算机系') 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','芳菲','女',0881104,'08811','外语系') insert into tb_reader values('R10010','常江宁','男',0881204,'08812','外语系') go--借阅信息表信息insert 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(10010,'R10007','2009-5-20','2009-6-20')insert into tb_borrow values(10009,'R10009','2009-5-30','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')go--(1)查询每本图书的所有信息；select*from tb_book--（2）查询每个读者的读者编号、和班级；select ReaderID,Name,Classfrom tb_reader--（3）查询每条借阅记录的借阅天数；select datediff(DAY,Borrowingdate,Returndate)from tb_borrow--（4）查询被借阅过的图书的图书编号；select distinct ISBNfrom tb_borrow--（5）查询图书编号为“”的书名和作者；select Title,Authorfrom tb_bookwhere ISBN='10006'--（6）查询库存数在到本之间的图书的图书编号和书名；SELECT ISBN,Titlefrom tb_bookwhere Inventorynumber between 5 and 10--（7）查询计算机系或电子系姓的读者信息；select*from tb_readerwhere pastern in('计算机系','电子系')--（8）查询书名包括“英语”的图书信息；select*from tb_bookwhere Title='英语'--（9）统计男读者、女读者的人数；select Sex,COUNT(*)from tb_readergroup by Sex--（10）统计各类图书的编号、平均定价以及库存总数；select Typenumber,AVG(BookPrice)as平均定价,SUM(Inventorynumber)as库存总数from tb_bookgroup by Typenumber--（11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；select ISBN,COUNT(*)from tb_borrowgroup by ISBNorder by COUNT(*)desc--（12）查询有库存的各类别图书的类别编号、类别名称和借阅数量；select tb_book.Typenumber,Categoryname,COUNT(*)from tb_book,tb_booktype,tb_borrowwhere tb_book.Typenumber=tb_booktype.Typenumber and tb_book.ISBN=tb_borrow.ISBNgroup by tb_book.Typenumber,tb_booktype.Categoryname--（13）查询借阅了“大学英语”一书的读者，输出读者、性别、系部；select Name,Sex,Pasternfrom tb_readerwhere ReaderID in(select ReaderIDfrom tb_borrowwhere ISBN in(select ISBN from tb_book where Title='大学英语'))--（14）查询每个读者的读者编号、、所借图书编号及所借阅日期；select tb_reader.ReaderID,Name,Borrowingdatefrom tb_reader left outer join tb_borrow on tb_reader.ReaderID=tb_borrow.ReaderID--（15）查询现有图书中价格最高的图书，输出书名、作者、定价；select Title,Author,BookPricefrom tb_bookwhere BookPrice=(select MAX(BookPrice)from tb_book)--（16）查询借阅了“大学英语”但没有借阅“C++程序设计”的读者，输出读者、性别、系部select Name,Sex,Pasternfrom tb_reader where Name in(select Namefrom tb_borrow where ISBN in(select ISBN from tb_book where Title='大学英语'))and Name not in(select Name from tb_borrow where ISBN in(select ISBN from tb_book where Title='C++程序设计'))--（17）统计借阅了本以上图书的读者信息；select*from tb_readerwhere ReaderID in(select ReaderID from tb_borrow group by ReaderID having COUNT(*)>2)--（18）查询计算机系中比其他系所有读者借书数量都多的读者的信息；select*from tb_reader tbr1where Pastern='计算机系'and ReaderID in(select tb_reader.ReaderID from tb_reader,tb_borrowwhere tb_reader.ReaderID=tb_borrow.ReaderID and tb_reader.ReaderID=tbr1.ReaderIDgroup by tb_reader.ReaderIDhaving count(ISBN)>any(select count(ISBN)from tb_reader,tb_borrowwhere tb_reader.ReaderID=tb_borrow.ReaderID and Pastern<>'计算机系'group by tb_reader.ReaderID))--（19）查询借阅了“大学英语”一书或者借阅了“C++程序设计”一书的读者信息；（用集合查询完成）select*from tb_readerwhere ReaderID in(select ReaderID from tb_borrow,tb_book where tb_borrow.ISBN=tb_book.ISBN and Title='大学英语')unionselect*from tb_readerwhere ReaderID in(select ReaderID from tb_borrow,tb_book where tb_borrow.ISBN=tb_book.ISBN and Title='C++程序设计')--20）查询既借阅了“大学英语”一书又借阅了“C++程序设计”一书的读者信息。