数据库实验二41245

仲恺农业工程学院实验报告纸

信息科学与技术学院网络工程专业 131 班组数据库原理及应用课学号 4123 姓名苏德实验日期教师评定

实验二数据查询与修改

一、实验目的

1、掌握使用简单查询、连接查询以及嵌套查询完成数据查询的方法

2、掌握应用SQL进行数据更新的方法；

3、掌握视图的创建与更新方法。

二、实验内容

第一部分：使用附加方式恢复数据库LibraryLib，完成如下各题：

1、《数据库原理及应用实验指导》P45 1.数据查询练习(1)-(6)

（1）查询各个超期罚款用户及其罚款总数，结果按罚款总数降序排列。

（2）查询已经超期一个月以上的还未归还图书的用户借书证编号。

（3）查询已经超期一个月以上的还未归还图书的用户名称、联系方式以及超期图书名。

（4）查询在同一天借阅了不同图书的用户借书证号。

（5）查询借书从来没有被超期罚款的用户信息。

（6）检索有图书所购册数大于或等于图书价格超过“50”的图书任一所购册数的图书的价格。

分析：先找出图书价格超过“50”的图书的所购册书假设a

要查的是所购册数>= any a 的图书的价格

第一部分：实验数据修改

1.使用SQL语句向“用户信息表Users”中插入元组（用户标识：LiS；用户权限：1；密码：2；用户名称：李生；性别：男（0）；部门：计算系；已借

图书：0）。

问：本题可以省略into子句中的属性名吗如果要省略，需要注意什么

2.向Publish(出版社信息) 表插入一条新的记录，在该记录中，出版社名为：国防工业出版社，地址为：北京市海淀大街籍海楼，出版社email为。

3.对每个出版社，求出所购图书总数和图书总价格，并把结果存入新建表“图书统计Temp_CNTBK”表中。

4.读者“ZouY”于今天通过管理员“LuoHH”借阅了一本“Visual FoxPro 及其应用系统开发”图书，使用T-SQL语句向Borrow表中插入该记录，并更改Book表中库存册书信息。

说明：本题有一定难度，请先思考如果插入这么一条记录，需要哪些信息，又如何获取到，关键是获取后将其存入一些变量中即可

5.使用SQL语句将所有读者借书应归还日期向后延长一天。

6.将“清华大学出版社”的图书所购册书和库存册书增加10本。

7.删除所有实际归还日期为“2009-04-17”以前且所借图书的名称中含有“数据库”字样的超期罚款记录。

8.删除“出版社信息表Publish”中出版社名称为“机械工业出版社”的元组，讨论该删除操作所受到的约束。

第二部分：实验视图

1.使用企业管理器创建一个行列子集视图View_Book，给出清华大学出版社的图书的的图书名称、书号、价格、书的页数和购买册书。

2.创建分组视图，将出版社标识及每个出版社的图书的最高价格和最低价格定义为一个视图。

3.创建一个基于题目1所建立视图的视图，定义一个视图，包括清华大学出版社的图书名称，以及购买该图书的总价格。

4.创建一个基于多个基表的视图，该视图由用户帐户、姓名、他在借书的名称组成。

5.查询用户“ZhangQF”所借图书的名称。

6.删除视图View_Publish。

第四部分：为选做题

使用附加方式恢复数据库student-course，然后完成下述各题

1. 查询计算机系（CS）、年龄在21岁以上的学生的姓名、年龄、出生年份；可能会用到函数getdate()以及Year()

2. 查询信息系（IS）和计算机（CS）系学生年龄不在21－23岁之间的学生的姓名、系别和年龄；

3. 查询所有名字里面第二个字为“敏”的学生的姓名、学号和性别；

4. 查询选修了4号课程的学生的学号和成绩,查询结果按学号的升序和分数的降序排序；

5. 查询出超过2个人选修且平均成绩大于82分的课程号和平均分。

6. 数据结构成绩高于平均分（数据结构）的学生名字、系别。

本题若改为：查询数据结构成绩高于它自己所选修课程的平均分的学生名字、系别，该怎么写查询语句。

7. 查询出所有课程平均成绩高于张立平均成绩的同学姓名。

三、实验步骤

select UserID,FineMoney

from ExtraDateFee

Order by FineMoney desc

select BookID

from BorrowHistory

where BorrowReturnDate-BorrowEndDate>30;

select,UserTelephone,BookName

from Book,BorrowHistory,Users

where

and=and=;

select distinct

from borrow B1,borrow B2

where!=and year=year

and month=month

and day=day;

select*

from Users

where not exists(

select UserID

from ExtraDateFee

where=

);

select

from Book B1

where>=

(select

from Book B2

where BookPrice>50

)

insert

into

Users(UserID,UserPassword,UserPower,UserName,UserSex,UserDepart,UserBorrowedBooks) values('LiS','1','2','李生','0','计算系','0');

insert

into

Publish(PublishID,PublishName,PublishAddress,PublishTelephone,PublishEmail,PublishP ostCode)

values('3','国防工业出版社','北京市海淀大街籍海楼',' ','',' ');

select BookPublishID,SUM(BookNum) As TotalBook,SUM(BookNum*BokPrice) as TotalP rice

into Temp_CNTBK

from Book

group by BookPublishID

insert into Borrow

values(1,'ZouY',3,getdate(),getdate()+60,'LuoHH') update Book

set BookCurNum=BookCurNum-1

where BookName='Visual FoxPro及其应用系统开发'

update Borrow

set BorrowEndDate=BorrowEndDate-1

update Book

set BookNum=BookNum+10,BookCurNum=BookCurNum+10 where BookPublishID in

(select PublishID

from Publish

where PublishName='清华大学出版社’)

delete from ExtraDateFee

where

BorrowReturnDate<2009-04-17

and BookID in

(select BookID

from Book

where BookName='%数据库%')

delete from Publish

where PublishName='机械工业出版社'

create view V_publish(PublishID,maxBookPrice,minBookPrice )

as select BookPublishID,max(BookPrice),min(BookPrice) from Book

group by BookPublishID

create 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 BookName

create view V_Users (UserID,UserName,BookName) as select ,UserName,BookName

from Users,Book,Borrow

where =

and =

create view V_Users (UserID,UserName,BookName)

as select ,UserName,BookName

from Users,Book,Borrow

where in (select UserID from Borrow)

and in (select BookID from Borrow)

select BookName

from V_Users

where UserID='ZhangQF'

Drop View View_Public

四、实验总结

1.用T-SQL语句对数据库进行插入、更新和删除操作，让我们更

加熟悉SQL语言对数据库进行基本的操作。

在执行插入语句时会检查所插入元组是否破坏表上已定义的完整性规则，包括实体完整性、参照完整性、和用户自定义的完整性。

3.在修改数据时DBMS同样会对数据库的完整性进行检查。

在执行删除语句时，会检查所删除元组是否破坏表上已定义的参照完整性规则，检查是否不允许删除或是需要级联删除。