数据库实验报告.docx
- 文档编号:30707335
- 上传时间:2023-08-19
- 格式:DOCX
- 页数:32
- 大小:653.01KB
数据库实验报告.docx
《数据库实验报告.docx》由会员分享,可在线阅读,更多相关《数据库实验报告.docx(32页珍藏版)》请在冰豆网上搜索。
数据库实验报告
中南大学
数据库实验报告
专业班级:
计科0905班
学号:
0909091823
姓名:
肖军
报告时间:
2011年12月15日
实验一创建表,更新表和实施数据完整性
1.运行给定的SQLScript,建立数据库GlobalToyz。
2.创建所有表的关系图。
(如下)
3.列出所有表中出现的约束(包括Primarykey,Foreignkey,checkconstraint,default,unique)
execsp_helpconstraintCategory;
execsp_helpconstraintCountry;
execsp_helpconstraintOrderDetail;
execsp_helpconstraintOrders;
execsp_helpconstraintPickOfMonth;
execsp_helpconstraintRecipient;
execsp_helpconstraintShipment;
execsp_helpconstraintShippingMode;
execsp_helpconstraintShippingRate;
execsp_helpconstraintShopper;
execsp_helpconstraintShoppingCart;
execsp_helpconstraintToyBrand;
execsp_helpconstraintToys;
execsp_helpconstraintWrapper;
4.对Recipient表和Country表中的cCountryId属性定义一个用户自定义数据类型,并将该属性的类型定义为这个自定义数据类型。
EXECsp_addtypecdefine,'char(3)','null';
5.把价格在$20以上的所有玩具的材料拷贝到称为PremiumToys的新表中。
CREATETABLEPremiumToys
(cToyIdCHAR(6)CHECK(cToyIdlike('[0-9][0-9][0-9][0-9][0-9][0-9]'))CONSTRAINTtt_idPRIMARYKEYCLUSTERED,
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;
6.对表Toys实施下面数据完整性规则:
(1)玩具的现有数量应在0到200之间;
(2)玩具适宜的最低年龄缺省为1。
ALTERTABLEToys
ADDCONSTRAINTsiToyQoh_sizeCHECK(siToyQoh>0ANDsiToyQoh<200);
ALTERTABLEToys
ADDCONSTRAINTsiLowerAge_defaultCHECK(siLowerAge>=1);
7.不修改已创建的Toys表,利用规则实现以下数据完整性:
(1)玩具的价格应大于0;
(2)玩具的重量应缺省为1。
CREATERULEmToyRateMin
AS@mToyRate>0
EXECSP_BINDRULEmToyRateMin,'Toys.mToyRate';
CREATERULEsiToyWeightDefault
AS@siToyWeight=1
CREATESP_BINDRULEsiToyWeightDefault,'Toys.siToyWeight';
8.给id为‘000001’玩具的价格增加$1。
UPDATEToys
SETmToyRate=1+mToyRate
WHEREcToyId='000001';
实验二查询数据库
1.显示属于California和Illinoi州的顾客的名、姓和emailID。
SELECT
[vFirstName]
[vLastName]
[vEmailId]
[cState]
FROM[GlobalToyz].[dbo].[Shopper]
WHEREcState='California'ORcState='Illinois';
2.显示定单号码、商店ID,定单的总价值,并以定单的总价值的升序排列。
SELECT[cOrderNo]
[cShopperId]
[mTotalCost]
FROM[GlobalToyz].[dbo].[Orders]
ORDERBYmTotalCost;
3.显示在orderDetail表中vMessage为空值的行。
SELECT[cOrderNo]
[cToyId]
[siQty]
[cGiftWrap]
[cWrapperId]
[vMessage]
[mToyCost]
FROM[GlobalToyz].[dbo].[OrderDetail]
WHEREvMessageISNULL;
4.显示玩具名字中有“Racer”字样的所有玩具的材料。
SELECT[cToyId]
[vToyName]
[vToyDescription]
[cCategoryId]
[mToyRate]
[cBrandId]
[imPhoto]
[siToyQoh]
[siLowerAge]
[siUpperAge]
[siToyWeight]
[vToyImgPath]
FROM[GlobalToyz].[dbo].[Toys]
WHEREvToyNameLIKE'%Racer%';
5.根据2000年的玩具销售总数,显示“PickoftheMonth”玩具的前五名玩具的ID。
SELECTtop5[cToyId]
FROM[GlobalToyz].[dbo].[PickOfMonth]
WHEREiYear=2000
ORDERBYiTotalSoldDESC;
6.根据OrderDetail表,显示玩具总价值大于¥50的定单的号码和玩具总价值。
SELECT[cOrderNo]
[mToyCost]
FROM[GlobalToyz].[dbo].[OrderDetail]
WHEREmToyCost>50;
7.显示一份包含所有装运信息的报表,包括:
OrderNumber,ShipmentDate,ActualDeliveryDate,DaysinTransit.(提示:
DaysinTransit=ActualDeliveryDate–ShipmentDate)
CREATEVIEWv_shipment
AS
SELECTcOrderNo,dShipmentDate,dActualDeliveryDate,dActualDeliveryDate-dShipmentDateDaysInTransit
FROMShipment;
SELECT*
FROMv_shipment;
8.显示所有玩具的名称、商标和种类(ToyName,Brand,Category)。
SELECTToys.vToyName,
ToyBrand.cBrandName,
Category.cCategory
FROMToys,ToyBrand,Category
WHEREToys.cBrandId=ToyBrand.cBrandIdANDToys.cCategoryId=Category.cCategoryId;
9.显示玩具的名称和所有玩具的购物车ID。
如果玩具不在购物车中,则显示NULL值。
SELECT//左外连接
GlobalToyz.dbo.Toys.[vToyName]
GlobalToyz.dbo.ShoppingCart.cCartId
FROM[GlobalToyz].[dbo].[Toys]LEFTJOINGlobalToyz.dbo.ShoppingCartON(GlobalToyz.dbo.Toys.cToyId=GlobalToyz.dbo.ShoppingCart.cToyId);
10.以下列格式显示所有购物者的名字和他们的简称:
(Initials,vFirstName,vLastName),例如AngelaSmith的Initials为A.S。
Left(expression,integer)为截取表达式expression左起integer个字符串的函数。
“+”为连接符。
SELECTleft(vFirstName,1)+'.'+left(vLastName,1)Initials
[vFirstName]
[vLastName]
FROM[GlobalToyz].[dbo].[Shopper];
11.显示所有玩具的平均价格,并舍入到整数。
SELECTROUND(AVG(mToyRate),0)AVG
FROM[GlobalToyz].[dbo].[Toys];
12.显示所有购买者和收货人的名、姓、地址和所在城市。
SELECT
Recipient.vFirstName
Recipient.vLastName
Recipient.vAddress
Recipient.cCity
FROM[GlobalToyz].[dbo].[Recipient]
UNION
SELECT
Shopper.vFirstName
Shopper.vLastName
Shopper.vAddress
Shopper.cCity
FROMGlobalToyz.dbo.Shopper;
13.显示没有包装的所有玩具的名称。
(要求用子查询实现)
SELECTvToyName
FROM[GlobalToyz].[dbo].[Toys]
WHEREToys.cToyId=ANY
(SELECTGlobalToyz.dbo.OrderDetail.cToyId
FROMGlobalToyz.dbo.OrderDetail
WHEREGlobalToyz.dbo.OrderDetail.cWrapperIdISNULL);
14.显示已发货定单的定单号码以及下定单的时间。
(要求用子查询实现)
SELECTGlobalToyz.dbo.Orders.[cOrderNo]
[dOrderDate]
FROM[GlobalToyz].[dbo].[Orders]
WHEREOrders.cOrderNo=ANY
(SELECTShipment.cOrderNo
FROMShipment
WHEREcDeliveryStatus='d');
实验三视图与触发器
1.定义一个视图,包括购买者的姓名、所在州和他们所订购玩具的名称、价格和数量。
CREATEVIEWCUSTOMER
AS
SELECTShopper.vFirstName
Shopper.vLastName
Shopper.cState
Toys.vToyName
Toys.mToyRate
OrderDetail.siQty
FROMGlobalToyz.dbo.shopper,GlobalToyz.dbo.Toys,GlobalToyz.dbo.Orders,GlobalToyz.dbo.OrderDetail
WHEREShopper.cShopperId=Orders.cShopperIdANDOrders.cOrderNo=OrderDetail.cOrderNoANDOrderDetail.cToyId=Toys.cToyId;
2.基于
(1)中定义的视图,查询显示所有California州的购买者的姓名和他们所订购玩具的名称及数量。
SELECT[vFirstName]
[vLastName]
[vToyName]
[siQty]
FROM[GlobalToyz].[dbo].[CUSTOMER]
WHEREcState='California';
3.视图定义如下:
CREATEVIEWvwOrderWrapper
AS
SELECTcOrderNo,cToyId,siQty,vDescription,mWrapperRate
FROMOrderDetailJOINWrapper
ONOrderDetail.cWrapperId=Wrapper.cWrapperId
以下更新命令,在更新siQty和mWrapperRate属性使用了以下更新命令时出现错误:
UPDATEvwOrderWrapper
SETsiQty=2,mWrapperRate=mWrapperRate+1
FROMvwOrderWrapper
WHEREcOrderNo=‘000001’
修改更新命令,以更新基表中的值。
UPDATEvwOrderWrapper
SETsiQty=2
FROMvwOrderWrapper
WHEREcOrderNo='000001';
UPDATEvwOrderWrapper
SETmWrapperRate=mWrapperRate+1
FROMvwOrderWrapper
WHEREcOrderNo='000001';
4.在OrderDetail上定义一个触发器,如果购物者改变了定单的数量,玩具的成本也自动地改变。
(提示:
Toycost=Quantity*ToyRate)
CREATETRIGGERCheckToyCost
ONOrderDetail
FORUPDATE
AS
IFUPDATE(siQty)
BEGIN
UPDATEOrderDetail
SETmToyCost=siQty*mToyRate
FROMOrderDetail,Toys
END
GO
原OrderDetail表
修改玩具编号000007的数量为4后
修改订单编号000005的数量(每个都加3)后
实验四存储过程
1.编写一段程序,将每种玩具的价格提高¥0.5,直到玩具的平均价格接近$24.5为止。
此外,任何玩具的最大价格不应超过$53。
USEGlobalToyz
GO
WHILE(SELECTAVG(mToyRate)FROMToys)<24.5
BEGIN
IF(SELECTMAX(mToyRate)<53
BEGIN
UPDATEToys
SETmToyRate=mToyRate+0.5
END
BREAK
END;
程序执行前
程序执行后
2.创建一个称为prcCharges的存储过程,它返回某个定单号的装运费用和包装费用。
CREATEPROCEDUREprcCharges(@orderNoCHAR(6)OUTPUT)
AS
BEGIN
SELECTcOrderNo,mShippingCharges,mGiftWrapCharges
FROMOrders
WHEREcOrderNo=@orderNo
END
GO
测试用例
EXECprcCharges@orderNo='000001';
EXECprcCharges@orderNo='000005';
3.创建一个称为prcHandlingCharges的过程,它接收定单号并显示经营费用。
prchandlingCharges过程应使用prcCharges过程来得到装运费和礼品包装费。
提示:
经营费用=装运费+礼品包装费。
CREATEPROCEDUREprcHandlingCharges(@orderNoCHAR(6))
AS
BEGIN
CREATETABLE#t(orderNoCHAR(6),pmShippingChargesmoney,pmGiftWrapChargesmoney)
INSERTINTO#t
EXECprcCharges@orderNo
SELECTorderNo,pmShippingCharges+pmGiftWrapChargesASpHandlingCharges
FROM#t
END
GO
测试用例
EXECprcHandlingCharges@orderNo='000001';
EXECprcHandlingCharges@orderNo='000005';
实验五事务与游标
1.名为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为参数,实现这个事务。
CREATEPROCEDUREprcAddOrder@CartIDCHAR(6),@ShopperIDCHAR(6)
AS
DECLARE@OrderNoCHAR(6),@OrderNumCHAR(6),@ToyIDCHAR(6),@quantityCHAR(6)
EXECprcGenOrder@OrderNoOUTPUT
SELECT@OrderNo=ISNULL(CONVERT(NVARCHAR,@OrderNo),'
CREATETABLE#t(OrderNoCHAR(6),CartIdCHAR(6),ShopperIdCHAR(6),OrderDateDATETIME)
INSERTINTO#t(OrderNo,CartId,ShopperId,OrderDate)
VALUES(@OrderNum,@CartID,@ShopperID,getdate())
INSERTINTOOrders(cOrderNo,cCartId,cShopperId,dOrderDate)
SELECT*FROM#t
SELECT@ToyID=cToyId,@quantity=siQtyFROMShoppingCart
WHEREcCartId=@CartID
INSERTINTOOrderDetail(cOrderNo,cToyId,siQty)
VALUES(@OrderNum,@ToyID,@quantity)
UPDATEOrderDetail
SETmToyCost=siQty*mToyRate
FROMOrderDetail,Toys
GO
程序执行前Orders表
程序执行前OrderDetail表
测试用例
EXECprcOrder@CartID='000001',@ShopperID='000001';
程序执行后Orders表
程序执行后OrderDetail表
EXECprcAddOrder@CartID='000005',@ShopperID='000020';
程序执行后Orders表
程序执行后OrderDetail表
2.编写一个程序显示每天的定单状态。
如果当天的定单值总合大于170,则显示“Highsales”,否则显示”Lowsales”.报告中要求列出日期、定单状态和定单总价值。
DECLARE@turnovermoney
SELECTCONVERT(VARCHAR(12),Orders.dOrderDate,111)ASdate,Orders.cOrderProcessed,SUM(mToyCost)ASturnover,orderState=
CASE
WHEN@turnover>170THEN'HighSales'
ELSE'Lo
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验 报告