中南大学数据库实验报告.docx
《中南大学数据库实验报告.docx》由会员分享，可在线阅读，更多相关《中南大学数据库实验报告.docx（21页珍藏版）》请在冰豆网上搜索。
中南大学数据库实验报告
中南大学数据库
实验报告
学院：
信息科学与工程学院
专业班级：
学号：
姓名：
指导老师：
实验一：
创建表和实施数据完整性---------------------1
实验二：
存储过程与触发器---------------------------11
实验三：
视图、事务与游标---------------------------15
数据库关系图---------------------------------------21
参考书籍-------------------------------------------22
实验1：
创建表和实施数据完整性
1.运行给定的SQLScript，建立数据库GlobalToyz。
了解表的结构。
2.利用系统预定义的存储过程sp_helpdb查看数据库的相关信息，例如所有者、大小、创建日期等。
3.利用系统预定义的存储过程sp_helpconstraint查看所有表中出现的约束（包括Primarykey,Foreignkey,checkconstraint,default,unique）
4.对表Toys实施下面数据完整性规则：
（1）玩具的现有数量应在0到200之间；
（2）玩具适宜的最低年龄缺省为1。
查询、更新数据库
1.显示属于California和Illinoi州的顾客的名、姓和emailId。
SELECTvFirstName,vLastName,vEmailId
FromShopper
WHEREcState='California'orcState='Illinoi';
2.显示定单号码、顾客ID，定单的总价值，并以定单的总价值的升序排列。
SELECTcOrderNo,cShopperId,mTotalCost
FromOrders
ORDERBYmTotalCostASC;
3.显示在orderDetail表中vMessage为空值的行。
SELECT*
FromorderDetail
WHEREvMessageISNULL;
4.显示玩具名字中有“Racer”字样的所有玩具的基本资料。
SELECT*
FromToys
WHEREvToyNameLIKE'%Racer%';
5.列出表PickofMonth中的所有记录，并显示中文列标题。
SELECTcToyId玩具编号,siMonth月,iYear年,iTotalSold销售总量
FromPickofMonth;
6.根据2000年的玩具销售总数，显示“PickoftheMonth”玩具的前五名玩具的ID。
selecttop5cToyId,SUM（iTotalSold）astotal
fromPickOfMonth
whereiYear='2000'
groupbycToyId
OrderBytotalDESC;
7.根据OrderDetail表，显示玩具总价值大于￥50的定单的号码和玩具总价值。
selectcOrderNo,mToyCost
fromOrderDetail
wheremToyCost>'50';
8.显示一份包含所有装运信息的报表，包括：
OrderNumber,ShipmentDate,ActualDeliveryDate,DaysinTransit.
selectcOrderNoOrder_Number,
dShipmentDateShipment_Date,
dActualDeliveryDate
Actual_Delivery_Date,
dActualDeliveryDate-dShipmentDateDays_in_Transit
fromShipment;
9.显示所有玩具的名称、商标和种类（ToyName,Brand,Category）。
selectvToyName,cBrandId,cCategoryId
fromToys;
10.以下列格式显示所有购物者的名字和他们的简称：
（Initials,vFirstName,vLastName）,例如AngelaSmith的Initials为A.S。
selectleft（vFirstName,1）+'.'+left（vLastName,1）Initials,
vFirstName,vLastName
fromshopper;
11.显示所有玩具的平均价格，并舍入到整数。
selectRound（AVG（mToyRate）,0）averagerate
fromToys;
12.显示所有购买者和收货人的名、姓、地址和所在城市，要求显示结果中的重复记录。
selectvFirstName,vLastName,vAddress,cCity
fromREcipient
union
selectvFirstName,vLastName,vAddress,cCity
fromshopper;
13.显示没有包装的所有玩具的名称。
（要求用In子查询实现）
selectvToyName
fromToys
wherecToyIdNOTIN（
selectcToyId
fromOrderDetail,Wrapper
whereOrderDetail.cWrapperId=Wrapper.cWrapperId）;
14.显示已收货定单的定单号码以及下定单的时间。
（要求用Exists子查询实现）
selectcOrderNo,dOrderDate
fromOrders
whereexists（
select*
fromshipment
whereOrders.cOrderNo=cOrderNo
anddactualDeliveryDateisnotNULL
）;
15.显示一份基于Orderdetail的报表，包括cOrderNo,cToyId和mToyCost，记录以cOrderNo升序排列，并计算每一笔定单的玩具总价值。
（提示：
使用运算符COMPUTEBY）。
selectcOrderNo,cToyId,mToyCost
fromOrderdetail
orderbycOrderNo
computesum（mToyCost）bycOrderNo;
16.把价格在$20以上的所有玩具的信息拷贝到称为PremiumToys的新表中。
createtablePremiumToys
（
cToyIdchar（6）check（cToyId
like（'[0-9][0-9][0-9][0-9][0-9][0-9]'））primarykey,
vToyNamevarchar（20）notnull,
vToyDescriptionvarchar（250）,
cCategoryIdchar（3）referencesCategory（cCategoryId）,
mToyRatemoneynotnull,
cBrandIdchar（3）referencesToyBrand（cBrandId）,
imPhotoimage,
siToyQohsmallintnotnull,
siLowerAgesmallintnotnull,
siUpperAgesmallintnotnull,
siToyWeightsmallint,
vToyImgPathvarchar（50）null
）
insertintoPremiumToys
select*fromToys
wheremToyRate>20
select*
fromPremiumToys
17.给id为‘000001’玩具的价格增加$1。
updateToys
setmToyRate=mToyRate+1
wherecToyId='000001'
selectcToyId,vToyName,mToyRate
fromToys
wherecToyId='000001'
修改前：
修改后：
18.删除“Largo”牌的所有玩具。
delete
fromToys
wherecBrandId=（selectcBrandId
fromToyBrand
wherecBrandName='Largo'）
select*
fromToys
wherecBrandId=（selectcBrandId
fromToyBrand
wherecBrandName='Largo'）
有外键约束存在，无法删除。
实验2：
存储过程与触发器
1.编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平均价格接近$24.5为止。
此外，任何玩具的最大价格不应超过$53。
createprocedureinprove_price
as
declare@averagemoney/*money是数据类型*/
select@average=AVG（mToyRate）fromToys
begin
while@average<24.5
begin
updateToys
setmToyRate=mToyRate+0.5
wheremToyRate<53
select@average=AVG（mToyRate）fromToys
end
end
go
execinprove_price
select*fromToys
2.创建一个称为prcCharges的存储过程，它返回某个定单号的装运费用和包装费用。
createprocedureprcCharges
@cOrderNochar（6）,
@mShippingChargesmoneyoutput,
@mGiftWrapChargesmoneyoutput
as
begin
select@mShippingCharges=mShippingCharges,
@mGiftWrapCharges=mGiftWrapCharges
fromOrders
wherecOrderNo=@cOrderNo
end
go
declare@mShippingChargesmoney,
@mGiftWrapChargesmoney
execprcCharges'000002',@mShippingChargesoutput,
@mGiftWrapChargesoutput
print'订单的装运费用为：
'+convert（char（10）,@mShippingCharges）
print'订单的包装费用为：
'+convert（char（10）,@mGiftWrapCharges）
3.创建一个称为prcHandlingCharges的过程，它接收定单号并显示经营费用。
PrchandlingCharges过程应使用prcCharges过程来得到装运费和礼品包装费。
提示：
经营费用=装运费+礼品包装费
createprocedureprcHandlingCharges
@cOrderNochar（6）,
@RunningExpensesmoneyoutput
as
begin
declare@mShippingChargesmoney,
@mGiftWrapChargesmoney
execprcCharges@cOrderNo,
@mShippingChargesoutput,
@mGiftWrapChargesoutput
set@RunningExpenses=@mShippingCharges+@mGiftWrapCharges
end
go
declare@RunningExpensesmoney
execprcHandlingCharges'000002',@RunningExpensesoutput
print'000002号的经营费用为：
'+convert（char（10）,@RunningExpenses）
4.在OrderDetail上定义一个触发器，当向OrderDetail表中新增一条记录时，自动修改Toys表中玩具的库存数量（siToyQoh）。
createtriggercpponOrderDetailforinsert
as
declare@siQtysmallint
declare@cToyIdchar（6）
select@cToyId=inserted.cToyId,@siQty=inserted.siQty
fromInserted
updateToys
setsiToyQoh=siToyQoh-@siQty
wherecToyId=@cToyId
return
5.在OrderDetail上定义一个触发器，如果购物者改变了定单的数量，玩具的成本也自动地改变。
（提示：
Toycost=Quantity*ToyRate）
createtriggerCheckToyCostonOrderDetailforupdate
as
ifupdate（siQty）
begin
updateOrderDetail
setmToyCost=siQty*mToyRate
fromOrderDetail,Toys
end
go
selectcOrderNo,cToyId,siQty,mToyCost
fromOrderDetail
wherecOrderNo='000001'
updateOrderDetail
setsiQty=siQty+10
wherecOrderNo='000001'
selectcOrderNo,cToyId,siQty,mToyCost
fromOrderDetail
wherecOrderNo='000001'--测试用例：
--对定单的数量修改前的OrderDetail：
--对定单的数量修改后的OrderDetail：
实验3：
视图、事务与游标
1.定义一个视图，包括购买者的姓名、所在州和他们所订购玩具的名称、价格和数量。
createviewppt
（vFirstName,vLastName,cState,vToyName,mToyRate,siQty）
as
selectvFirstName,vLastName,cState,vToyName,mToyRate,siQty
fromOrderDetail,Recipient,Toys
WhereOrderDetail.cOrderNo=Recipient.cOrderNo
andOrderDetail.cToyId=Toys.cToyId
2.基于
（1）中定义的视图，查询显示所有California州的购买者的姓名和他们所订购玩具的名称及数量。
selectvFirstName,vLastName,vToyName,siQty
fromppt
wherecState='California'
3.名为prcGenOrder的存储过程产生存在于数据库中的定单号：
CREATEPROCEDUREprcGenOrder
@OrderNochar（6）OUTPUT
as
SELECT@OrderNo=Max（cOrderNo）FROMOrders
SELECT@OrderNo=
CASE
WHEN@OrderNo>=0and@OrderNo<9Then
‘00000’+Convert（char,@OrderNo+1）
WHEN@OrderNo>=9and@OrderNo<99Then
‘0000’+Convert（char,@OrderNo+1）
WHEN@OrderNo>=99and@OrderNo<999Then
‘000’+Convert（char,@OrderNo+1）
WHEN@OrderNo>=999and@OrderNo<9999Then
‘00’+Convert（char,@OrderNo+1）
WHEN@OrderNo>=9999and@OrderNo<99999Then
‘0’+Convert（char,@OrderNo+1）
WHEN@OrderNo>=99999ThenConvert（char,@OrderNo+1）
END
RETURN
当购物者确认定单时，应该出现下面的步骤：
（1）用上面的过程产生定单号。
（2）定单号，当前日期，购物车ID，和购物者ID应该加到Orders表中。
（3）定单号，玩具ID和数量应加到OrderDetail表中。
（4）在OrderDetail表中更新玩具成本。
（提示：
Toycost=Quantity*ToyRate）.
将上述步骤定义为一个事务。
编写一个过程以购物车ID和购物者ID为参数，实现这个事务。
begintransactionOrder_Comfirmation
declare@cCartIdchar（6）
declare@ShopperIdchar（6）
declare@OrderNochar（6）
declare@cToyIdchar（6）
declare@siQtysmallint
declare@mToyRatemoney
set@cCartId='000009'
set@ShopperId='000007'
execprcGenOrder@OrderNooutput
set@cToyId='000008'
set@siQty=2
select@mToyRate=mToyRatefromToyswherecToyId=@cToyId
insertintoOrders（cOrderNo,dOrderDate,cCartId,cShopperId）
values（@OrderNo,getdate（）,@cCartId,@ShopperId）
insertintoOrderDetail（cOrderNo,cToyId,siQty）
values（@OrderNo,@cToyId,@siQty）
updateOrderDetail
setmToyCost=siQty*（selectmToyRate
fromToys
wherecToyId=@cToyId）
wherecOrderNo=@OrderNo
commit
测试：
select*fromOrders
select*fromOrderDetail
4.编写一个程序显示每天的定单状态。
如果当天的定单值总合大于170，则显示“Highsales”,否则显示”Lowsales”。
报告中要求列出日期、定单状态和定单总价值。
（要求用游标实现）
declaresales_statusscroll
cursorfor
selectdistinctdOrderDate,sum（mTotalCost）Date_Total
fromOrders
groupbydOrderDate
Opensales_status
declare@dOrderdatedatetime
declare@Date_Totalmoney
print'dOrderdateDate_Total'
fetchfirstfromsales_statusinto@dOrderdate,@Date_Total
printconvert（char（10）,@dOrderdate）+''
+convert（char（10）,@Date_Total）
if@Date_Total>170print'Highsales'elseprint'Lowsales'
while@@fetch_status=0
begin
fetchnextfromsales_statusinto@dOrderdate,@Date_Total
printconvert（char（10）,@dOrderdate）+''
+convert（char（10）,@Date_Total）
if@Date_Total>170and@Date_Totalisnotnullprint'Highsales'elseprint'Lowsales'
end
deallocatesales_status
数据库表关系图
参考书籍
1.数据库——原理、编程与性能（第二版）PatrickQ'Neil，ElìzabethO'Neil
机械工业出版社
2.《数据库系统概论》（第四版）王珊，萨师煊高等教育出版社
升级会员 