数据库实验二89342(9页).doc

-仲恺农业工程学院实验报告纸信息科学与技术 学院 网络工程 专业 131 班 组 数据库原理及应用 课学号 201320254123 姓名 苏德 实验日期 2015.10.26 教师评定 实验二 数据查询与修改一、实验目的1、 掌握使用简单查询、连接查询以及嵌套查询完成数据查询的方法2、 掌握应用SQL进行数据更新的方法；3、 掌握视图的创建与更新方法。二、 实验内容第一部分：使用附加方式恢复数据库LibraryLib，完成如下各题：1、数据库原理及应用实验指导P45 1.数据查询练习(1)-(6)（1）查询各个超期罚款用户及其罚款总数，结果按罚款总数降序排列。（2）查询已经超期一个月以上的还未归还图书的用户借书证编号。（3）查询已经超期一个月以上的还未归还图书的用户名称、联系方式以及超期图书名。（4）查询在同一天借阅了不同图书的用户借书证号。（5）查询借书从来没有被超期罚款的用户信息。（6）检索有图书所购册数大于或等于图书价格超过“50”的图书任一所购册数的图书的价格。分析：先找出图书价格超过“50”的图书的所购册书 假设a要查的是所购册数>= any a 的图书的价格第一部分：实验2.2 数据修改1. 使用SQL语句向“用户信息表Users”中插入元组（用户标识：LiS；用户权限：1；密码：2；用户名称：李生；性别：男（0）；部门：计算系；已借图书：0）。问：本题可以省略into子句中的属性名吗？如果要省略，需要注意什么？2. 向Publish(出版社信息) 表插入一条新的记录，在该记录中，出版社名为：国防工业出版社，地址为：北京市海淀大街籍海楼，出版社email为：gfgypublish。3. 对每个出版社，求出所购图书总数和图书总价格，并把结果存入新建表“图书统计Temp_CNTBK”表中。4. 读者“ZouY”于今天通过管理员“LuoHH”借阅了一本“Visual FoxPro及其应用系统开发”图书，使用T-SQL语句向Borrow表中插入该记录，并更改Book表中库存册书信息。说明：本题有一定难度，请先思考如果插入这么一条记录，需要哪些信息，又如何获取到，关键是获取后将其存入一些变量中即可5. 使用SQL语句将所有读者借书应归还日期向后延长一天。6. 将“清华大学出版社”的图书所购册书和库存册书增加10本。7. 删除所有实际归还日期为“2009-04-17”以前且所借图书的名称中含有“数据库”字样的超期罚款记录。8. 删除“出版社信息表Publish”中出版社名称为“机械工业出版社”的元组，讨论该删除操作所受到的约束。第二部分：实验2.3 视图1. 使用企业管理器创建一个行列子集视图View_Book，给出清华大学出版社的图书的的图书名称、书号、价格、书的页数和购买册书。2. 创建分组视图，将出版社标识及每个出版社的图书的最高价格和最低价格定义为一个视图。3. 创建一个基于题目1所建立视图的视图，定义一个视图，包括清华大学出版社的图书名称，以及购买该图书的总价格。4. 创建一个基于多个基表的视图，该视图由用户帐户、姓名、他在借书的名称组成。5. 查询用户“ZhangQF”所借图书的名称。6. 删除视图View_Publish。第四部分：为选做题使用附加方式恢复数据库student-course，然后完成下述各题1. 查询计算机系（CS）、年龄在21岁以上的学生的姓名、年龄、出生年份；可能会用到函数getdate()以及Year()2. 查询信息系（IS）和计算机（CS）系学生年龄不在2123岁之间的学生的姓名、系别和年龄；3. 查询所有名字里面第二个字为“敏”的学生的姓名、学号和性别；4. 查询选修了4号课程的学生的学号和成绩,查询结果按学号的升序和分数的降序排序；5. 查询出超过2个人选修且平均成绩大于82分的课程号和平均分。6. 数据结构成绩高于平均分（数据结构）的学生名字、系别。本题若改为：查询数据结构成绩高于它自己所选修课程的平均分的学生名字、系别，该怎么写查询语句。7. 查询出所有课程平均成绩高于张立平均成绩的同学姓名。三、 实验步骤1.1select UserID,FineMoneyfrom ExtraDateFeeOrder by FineMoney desc1.2select BookIDfrom BorrowHistorywhere BorrowReturnDate-BorrowEndDate>30;1.3select Users.UserID,UserTelephone,BookNamefrom Book,BorrowHistory,Userswhere BorrowHistory.BorrowReturnDate-BorrowHistory.BorrowEndDate>30 and BorrowHistory.bookID=Book.bookID and Users.UserID=BorrowHistory.UserID;1.4select distinct B1.UserIDfrom borrow B1,borrow B2where B1.BookID!=B2.BookID and year(B1.BorrowBeginDate)=year(B2.BorrowBeginDate)and month(B1.BorrowBeginDate)=month(B2.BorrowBeginDate)and day(B1.BorrowBeginDate)=day(B2.BorrowBeginDate);1.5select *from Userswhere not exists (select UserIDfrom ExtraDateFeewhere ExtraDateFee.UserID=Users.UserID);1.6select B1.BookPricefrom Book B1where B1.BookNum >=(select B2.BookNumfrom Book B2where BookPrice>50)2.1insert into Users(UserID,UserPassword,UserPower,UserName,UserSex,UserDepart,UserBorrowedBooks)values('LiS','1','2','李生','0','计算系','0');2.2insert into Publish(PublishID,PublishName,PublishAddress,PublishTelephone,PublishEmail,PublishPostCode)values('3','国防工业出版社','北京市海淀大街籍海楼',' ','gfgypublish',' ');2.3select BookPublishID,SUM(BookNum) As TotalBook,SUM(BookNum*BokPrice) as TotalPrice into Temp_CNTBK from Book group by BookPublishID 2.4insert into Borrow values(1,'ZouY',3,getdate(),getdate()+60,'LuoHH') update Book set BookCurNum=BookCurNum-1 where BookName='Visual FoxPro及其应用系统开发'2.5update Borrow set BorrowEndDate=BorrowEndDate-12.6update Book set BookNum=BookNum+10,BookCurNum=BookCurNum+10 where BookPublishID in(select PublishID from Publishwhere PublishName='清华大学出版社)2.7delete from ExtraDateFee where BorrowReturnDate<2009-04-17 and BookID in(select BookID from Book where BookName='%数据库%') 2.8delete from Publish where PublishName='机械工业出版社' 3.13.2create view V_publish(PublishID,maxBookPrice,minBookPrice) as select BookPublishID,max(BookPrice),min(BookPrice) from Book group by BookPublishID3.3create view  V_Book(BookName,SumBookName) as select  BookName,sum(BookPrice*BookNum) from View_Book where BookID in (select BookID from Book where  BookPublishID  in (select PublishID from  Publish where PublishName='清华大学出版社') group by BookName3.4create view V_Users (UserID,UserName,BookName) as select Users.UserID,UserName,BookName from Users,Book,Borrow where Users.UserID=Borrow.UserID and Book.BookID=Borrow.BookId  create view V_Users (UserID,UserName,BookName) as select Users.UserID,UserName,BookName from Users,Book,Borrow where Users.UserID in (select UserID from Borrow)and Book.BookID in (select BookID from Borrow) 3.5select BookName from V_Users where UserID='ZhangQF'3.6Drop View View_Public 四、 实验总结1.用T-SQL语句对数据库进行插入、更新和删除操作，让我们更加熟悉SQL语言对数据库进行基本的操作。2.DBMS在执行插入语句时会检查所插入元组是否破坏表上已定义的完整性规则，包括实体完整性、参照完整性、和用户自定义的完整性。 3.在修改数据时DBMS同样会对数据库的完整性进行检查。 4.DBMS在执行删除语句时，会检查所删除元组是否破坏表上已定义的参照完整性规则，检查是否不允许删除或是需要级联删除。-第 9 页-