数据库图书信息管理组织数据库SQL语句

实验SQL语言一、实验目的1、理解数据库以及数据的设计；2、熟悉SQLServer2005中的数据类型；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-课程编号Coursenumber、课程名Coursename、先修课Thefirstcourse、学分credit2、通过SQL语句将以下数据表创建在该数据库的默认模式dbo下：图书类别信息表（tb_booktype）类别编号Typenumber、类别名称Categoryname图书信息表（tb_book）图书编号ISBN、类别编号Typenumber、书名title、作者author、出版社BookPublic、定价BookPrice、库存数Inventorynumber读者信息表（tb_reader-读者编号ReaderID、姓名、性别、学号StudentID、班级、系部pastern借阅信息表（tb_borrow）图书编号、读者编号、借阅日期Borrowingdate、归还日期Returndate3、通过SQL语句对读者信息表进行修改：删除系部字段、添加所在系字段。4、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL。5、通过SQL语句删除课程信息表。（三）创建和删除索引1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY,要求按照该表中库存数字段的降序创建。2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME,要求按照该表中的姓名字段的升序创建。3、使用SQL语句删除之前创建的两个索引。（四）数据库及数据表设计根据周围的实际应用情况，自选一个小型的数据库应用项目进行研究，完成该系统的设计。要求2人一组，通过需求分析，列出系统的主要功能，并完成该系统数据库的逻辑结构设计。例如可选择学籍管理系统、企业管理系统、人事管理系统或在线考试系统等。(五)数据查询通过SSMS向各数据表中添加以下。(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.005100092大学英语王琳高等教育出版社25.0010100102英语教程王琳高等教育出版社25.005(3)读者信息表读名编p姓名性别学号班级所在系R10001张小航男085110108511计算机系R10002王文J女085110208511计算机系R10003李理女085110308511计算机系R10004李彦宏男085120108512计算机系R10005张丽霞女085120208512计算机系R10006王强男072110407211电子系R10007张宝田男072120407212电子系R10008宋文霞女076110407611建工系R10009刘芳菲女088110408811外语系R10010常江宁男088120408812外语系(4)借阅信息表图书编号读名编p借阅日期归还日期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-222009-6-2210003R100092009-5-302009-6-30对以上数据表，完成以下操作：(1)查询每本图书的所有信息；(2)查询每个读者的读者编号、姓名和班级；(3)查询每条借阅记录的借阅天数(函数DATEDIFF获取两个日期的差)；(4)查询被借阅过的图书的图书编号；(5)查询图书编号为“10006”的书名和作者；(6)查询库存数在5到10本之间的图书的图书编号和书名；(7)查询计算机系或电子系姓张的读者信息；(8)查询书名包括“英语”的图书信息；(9)统计男读者、女读者的人数；(10)统计各类图书的类别编号、平均定价以及库存总数；(11)统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列;(12)查询有库存的各类别图书的类别编号、类别名称和借阅数量；(13)查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部；(14)查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；(LEFTOUTERJOIN)(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]gocreatedatabasedb_Libraryonprimary(name=N'db_Library_data',filename='E:\SQL\db_Library_data.mdf,size=10MB,maxsize=300MB,filegrowth=10%)logon(name='db_Library_log',filename='E:\SQL\db_Library_data.ldf,size=5MB,maxsize=200MB,filegrowth=2MBgousedb_Librarygocreateschema"L-C"authorizationguestcreatetabletb_course--课程信息表(Coursenumberint,Coursenamechar(20),Thefirstcoursechar(20),Creditint)gocreatetabletb_booktype--图书类别信息表(Typenumberint,Categorynamechar(20))gocreatetabletb_book--图书信息表(ISBNint,Typenumberint,Titlechar(20),Authorchar(20),BookPublicchar(20),BookPricefloat,Inventorynumberint)gocreatetabletb_reader--读者信息表(ReaderIDchar(20),Namechar(20),Sexchar(2),StudentIDint,Classchar(20),Pasternchar(20))gocreatetabletb_borrow--借阅信息表(ISBNint,ReaderIDchar(20),Borrowingdatechar(20),Returndatechar(20))goaltertabletb_readerdropcolumnPasternaltertabletb_readeraddcolumnPasternaltertabletb_bookaltercolumnBookPricerealdroptabletb_course--创建和删除索引createindexIX_S_QUANTITYontb_book（Inventorynumber）createindexIX_S_NAMEontb_reader（Nameasc）dropindexIXSQUANTITYdropindexIX_S_NAMEinsertintotb_booktypeinsertintotb_booktypeinsertintotb_booktypeinsertintotb_booktypeinsertintotb_booktypeinsertintotb_booktype--图书类别信息表信息values（1,'数学'）values（2,'英语'）values（3,'计算机'）values（4,'文学'）values（5,'艺术'）values（6,'电子信息’）go--图书信息表信息insertintotb_bookvalues（10001,3,'数据库管理‘,‘王珊’,‘高等教育出版社',35.50,10）insertintotb_bookvalues（10002,3,‘软件测试',‘贺平','机械工业出版社’,24.60,5）insertintotb_bookvalues（10003,3,'C++程序设计’,‘谭浩强’,‘清华大学出版社’,30.00,8）insertintotb_bookvalues（10004,4,‘红楼梦’,‘曹雪芹’,‘人民文学出版社’,70.00,5）insertintotb_bookvalues（10005,4,‘西游记’,‘罗贯中',‘人民文学出版社’,60.00,8）insertintotb_bookvalues（10006,4,‘红与黑’,‘司汤达’,‘人民文学出版社’,50.00,5）insertintotb_bookvalues（10007,1,‘高等数学‘,‘李翼‘,‘清华大学出版社’,28.00,4）insertintotb_bookvalues(10008,8，'有机化学’,'张翔‘，'高等教育出版社’,29.00,5)insertintotb_bookvalues（10009,2,'大学英语，‘王琳‘，'高等教育出版社’,25.00,10）insertintotb_bookvalues（10010,2,‘英语教程',‘王琳','高等教育出版社',25.00,5）go--读者信息表信息insertintotb_readervalues('R10001'，'张小航’,'男',0851101,'08511','计算机系’)insertintotb_readervalues('R10002','王文J女',0851102,'08511','计算机系')insertintotb_readervalues('R10003','李理',女',0851103,'08511','计算机系')insertintotb_readervalues('R10004','李彦宏'，'男',0851201,'08512','计算机系')女',0851202,'08512','计算机系')insertintotb_readervalues('R10005','张丽霞insertintotb_readervalues('R10006','王强',男',0721104,'07211','电子系’)insertintotb_readervalues('R10007'，'张宝田男',0721204,'07212','电子系’)insertintotb_readervalues('R10008','宋文霞女',0761104,'07611','建工系')insertintotb_readervalues('R10009'，'刘芳菲女',0881104,'08811'，'外语系’)insertintotb_readervalues('R10010','常江宁男',0881204,'08812','外语系')go--借阅信息表信息insertintotb_borrowvalues(10002,'R10003','2009-9-20','2009-10-20')insertintotb_borrowvalues(10003,'R10003','2009-9-20','2009-10-20')insertintotb_borrowvalues(10004,'R10003','2009-9-30','2009-10-30')insertintotb_borrowvalues(10009,'R10003','2009-9-30','2009-10-30'insertintotb_borrowvalues(10009,'R10007','2009-5-20','2009-6-20')insertintotb_borrowvalues(10010,'R10007','2009-5-20','2009-6-20')insertintotb_borrowvalues(10009,'R10009','2009-5-30','2009-6-30')insertintotb_borrowvalues(10010,'R10009','2009-5-22','2009-6-22')insertintotb_borrowvalues(10002,'R10009','2009-5-22','2009-6-22')insertintotb_borrowvalues(10003,'R10009','2009-5-30','2009-6-30')go--(1)查询每本图书的所有信息；select*fromtb_book-(2)查询每个读者的读者编号、姓名和班级；selectReaderID,Name,Classfromtb_reader-(3)查询每条借阅记录的借阅天数；selectdatediff(DAY,Borrowingdate,Returndate)fromtb_borrow-(4)查询被借阅过的图书的图书编号；selectdistinctISBNfromtb_borrow-(5)查询图书编号为“”的书名和作者；selectTitle,Authorfromtb_bookwhereISBN='10006'-(6)查询库存数在到本之间的图书的图书编号和书名；SELECTISBN,Titlefromtb_bookwhereInventorynumberbetween5and10-(7)查询计算机系或电子系姓张的读者信息；select*fromtb_readerwherepasternin('计算机系',‘电子系’)-(8)查询书名包括“英语”的图书信息；select*fromtb_bookwhereTitle='英语'-(9)统计男读者、女读者的人数；selectSexCOUNT(*)fromtb_readergroupbySex-(10)统计各类图书的编号、平均定价以及库存总数；selectTypenumber,AVG(BookPrice)as平均定价，SUM(Inventorynumber)as库存总数fromtb_bookgroupbyTypenumber-(11)统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；selectISBN,COUNT(*)fromtb_borrowgroupbyISBNorderbyCOUNT(*)desc-(12)查询有库存的各类别图书的类别编号、类别名称和借阅数量；selecttb_book.Typenumber,Categoryname,COUNT(*)fromtb_book,tb_booktype,tb_borrowtb_book.ISBN=tb_borrow.ISBNwheretb_book.Typenumber=tb_booktype.Typenumberandgroupbytb_book.Typenumber,tb_booktype.Categoryname-(13)查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部；selectName,Sex,Pasternfromtb_readerwhereReaderIDin(selectReaderIDfromtb_borrowwhereISBNin(selectISBNfromtb_bookwhereTitle='大学英语'))-(14)查询每个读者的读者编号、姓名、所借图书编号及所借阅日期；selecttb_reader.ReaderID,Name,Borrowingdatefromtb_readerleftouterjointb_borrowontb_reader.ReaderID=tb_borrow.ReaderID-(15)查询现有图书中价格最高的图书，输出书名、作者、定价；selectTitle,Author,BookPricefromtb_bookwhereBookPrice=(selectMAX(BookPrice)fromtb_book)-(16)查询借阅了“大学英语”但没有借阅“C++程序设计”的读者，输出读者姓名、性别、系部selectName,Sex,Pasternfromtb_readerwhereNamein(selectNamefromtb_borrowwhereISBNin(selectISBNfromtb_bookwhereTitle='大学英语’))andNamenotin(selectNamefromtb_borrowwhereISBNin(selectISBNfromtb_bookwhereTitle='C++程序设计'))-(17)统计借阅了本以上图书的读者信息；select*fromtb_readerwhereReaderIDin(selectReaderIDfromtb_borrowgroupbyReaderIDhavingCOUNT(*)>2)-(18)查询计算机系中比其他系所有读者借书数量都多的读者的信息；select*fromtb_readertbr1wherePastern='计算机系'andReaderIDin(selecttb_reader.ReaderIDfromtb_reader,tb_borrowwheretb_reader.ReaderID=tb_borrow.ReaderIDandtb_reader.ReaderID=tbr1.ReaderIDgroupbytb_reader.ReaderIDhavingcount(ISBN)>anyselectcount(ISBN)fromtb_reader,tb_borrowwheretbreader.ReaderlD=tbborrow.ReaderlDandPastern<>'计算机系'groupbytb_reader.ReaderID(19)查询借阅了“大学英语”一书或者借阅了“C++程序设计书的读者信息；（用集合查询完成）select*fromtbreaderwhereReaderIDin(selectReaderIDfromtb_borrow,tb_bookwheretbborrow.ISBN=tbbook.ISBNandTitle='大学英语’）unionselect*fromtbreaderwhereReaderIDin(selectReaderIDfromtb_borrow,tb_bookwheretbborrow.ISBN=tbbook.ISBNandTitle='C++程序设计')--20)查询既借阅了“大学英语”一书又借阅了“C++程序设计”书的读者信息。（用集合查询完成）select*fromtbreaderwhereReaderIDin(selectReaderIDfromtb_borrow,tb_bookwheretb_borrow.ISBN=tb_book.ISBNandTitle='大学英语')intersectselect*fromtb_readerwhereReaderIDin(selectReaderIDfromtb_borrow,tb_bookwheretb_borrow.ISBN=tb_book.ISBNandTitle='C++程序设计')-21)在读者信息表中插入一条新的记录(读者编号：R10011;姓名：张三；所在系：电子系)insertintotb_reader(ReaderlD,Sex,Pastern)values('R10011','张三，'电子系')-(22)假设存在一个表tb_booknew,包含图书编号、书名和类别名称字段，要求将类编编号为“”的图书的图书编号、书名和类别名称插入到tb_bknew表中；createtabletb_booknew(ISBNchar(10),Titlevarchar(20),Typenamevarchar(20))insertintotb_booknewselecttb_book.ISBN,tb_book.Title,tb_booktype.Categorynamefromtb_book,tb_booktypewheretb_book.Typenumber=tb_booktype.Typenumberandtb_book.Typenumber='3'-(23)将类别编号为“"的所有图书的库存数增加；updatetb_booksetInventorynumber=Inventorynumber+5whereTypenumber='3'-(24)将“C++程序设计”这本书的归还日期增加一个月(函数DATEADD)。updatetb_borrowsetBorrowingdate=DATEADD(MONTH,1,Returndate)whereISBNin(selectISBNfromtb_bookwhereTitle='C++程序设计’)-25)删除姓名为“张三”的读者的信息；deletefromtb_readerwhereName='张三'-(26)删除tb_bknew表中的所有数据；deletefromtb_booknew(27)创建一个名为“读者借阅信息_VIEW”的视图，要求显示计算机系所有读者的借阅信息,createview读者借阅信息_VIEW(ReaderlD,Name,Pastern,ISBN,TitleBorrowingdate)asselecttb_reader.ReaderIDReaderID,Name,Pastern,tb_book.ISBNISBN,Title,Borrowingdatefromtb_book,tb_reader,tb_borrowwheretb_book.ISBN=tb_borrow.ISBNandtb_reader.ReaderID=tb_borrow.ReaderIDandPastern='计算机系'-(28)创建一个名为“图示借阅信息_VIEW”的视图，要求显示图书的借阅情况，包括图书编号、书名、库存数、借阅次数字段；createview图示借阅信息VIEW(ISBN,Title,Inventorynumber)asselecttb_book.ISBN,Title,Inventorynumber,COUNT(*)numberfromtb_book,tb_borrowwheretb_book.ISBN=tb_borrow.ISBNgroupbytb_book.ISBN,Title,Inventorynumber-(29)查询借阅次数大于的图书的图书编号、书名、库存数和借阅次数；select*from读者借阅信息_VIEWwherenumber>2-(30)删除“图示借阅信息_VIEW”视图。dropview图示借阅信息_VIEW