中南大学数据库实验报告.doc

中 南 大 学数据库实验 实验题目 班 级 姓 名学 号 一、实验内容实验一：创建表、更新表和实施数据完整性1 运行给定的SQL Script，建立数据库GlobalToyz。2 创建所有表的关系图。3 列出所有表中出现的约束（包括Primary key, Foreign key, check constraint, default, unique）4 对Recipient表和Country表中的cCountryId属性定义一个用户自定义数据类型，并将该属性的类型定义为这个自定义数据类型。5 把价格在$20以上的所有玩具的材料拷贝到称为PremiumToys的新表中。6 对表Toys实施下面数据完整性规则：（1）玩具的现有数量应在0到200之间；（2）玩具适宜的最低年龄缺省为1。7 不修改已创建的Toys表，利用规则实现以下数据完整性：（1）玩具的价格应大于0；（2）玩具的重量应缺省为1。8 给id为000001玩具的价格增加$1。实验二：查询数据库1. 显示属于California和Illinoi州的顾客的名、姓和emailID。2. 显示定单号码、商店ID，定单的总价值，并以定单的总价值的升序排列。3. 显示在orderDetail表中vMessage为空值的行。4. 显示玩具名字中有“Racer”字样的所有玩具的材料。5. 根据2000年的玩具销售总数，显示“Pick of the Month”玩具的前五名玩具的ID。6. 根据OrderDetail表，显示玩具总价值大于￥50的定单的号码和玩具总价值。7. 显示一份包含所有装运信息的报表，包括：Order Number, Shipment Date, Actual Delivery Date, Days in Transit. (提示：Days in Transit = Actual Delivery Date Shipment Date)8. 显示所有玩具的名称、商标和种类（Toy Name, Brand, Category）。9. 显示玩具的名称和所有玩具的购物车ID。如果玩具不在购物车中，则显示NULL值。10. 以下列格式显示所有购物者的名字和他们的简称：（Initials, vFirstName, vLastName）,例如Angela Smith的Initials为A.S。11. 显示所有玩具的平均价格，并舍入到整数。 12. 显示所有购买者和收货人的名、姓、地址和所在城市。13. 显示没有包装的所有玩具的名称。（要求用子查询实现）14. 显示已发货定单的定单号码以及下定单的时间。（要求用子查询实现）实验三：视图与触发器1. 定义一个视图，包括购买者的姓名、所在州和他们所订购玩具的名称、价格和数量。2. 基于（1）中定义的视图，查询显示所有California州的购买者的姓名和他们所订购玩具的名称及数量。3. 视图定义如下： CREATE VIEW vwOrderWrapper AS SELECT cOrderNo, cToyId, siQty, vDescription, mWrapperRate FROM OrderDetail JOIN Wrapper ON OrderDetail.cWrapperId = Wrapper.cWrapperId以下更新命令，在更新siQty和mWrapperRate属性使用了以下更新命令时出现错误： UPDATE vwOrderWrapper SET siQty = 2, mWrapperRate = mWrapperRate + 1 FROM vwOrderWrapper WHERE cOrderNo = 000001修改更新命令，以更新基表中的值。4. 在OrderDetail上定义一个触发器，如果购物者改变了定单的数量，玩具的成本也自动地改变。（提示：Toy cost = Quantity * Toy Rate）实验四：存储过程1. 编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平均价格接近$24.5为止。此外，任何玩具的最大价格不应超过$53。2. 创建一个称为prcCharges的存储过程，它返回某个定单号的装运费用和包装费用。3. 创建一个称为prcHandlingCharges的过程，它接收定单号并显示经营费用。PrchandlingCharges过程应使用prcCharges过程来得到装运费和礼品包装费。提示：经营费用=装运费+礼品包装费 实验五：事务与游标1. 名为prcGenOrder的存储过程产生存在于数据库中的定单号： CREATE PROCEDURE prcGenOrder OrderNo char(6) OUTPUT as SELECT OrderNo=Max(cOrderNo) FROM Orders SELECT OrderNo= CASE WHEN OrderNo=0 and OrderNo=9 and OrderNo=99 and OrderNo=999 and OrderNo=9999 and OrderNo=99999 Then Convert(char,OrderNo+1) END RETURN当购物者确认定单时，应该出现下面的步骤：（1）用上面的过程产生定单号。（2）定单号，当前日期，购物车ID，和购物者ID应该加到Orders表中。（3）定单号，玩具ID，和数量应加到OrderDetail表中。（4）在OrderDetail表中更新玩具成本。（提示：Toy cost = Quantity * Toy Rate）.将上述步骤定义为一个事务。编写一个过程以购物车ID和购物者ID为参数，实现这个事务。2. 编写一个程序显示每天的定单状态。如果当天的定单值总合大于170，则显示“High sales”,否则显示”Low sales”.报告中要求列出日期、定单状态和定单总价值。二、实验目的 熟悉SQL语言，加深对数据库知识的认识。学会用SQL语言解决实际数据库问题。三、实验源代码实验一、-3 /*sp_helpconstraint objname=Categorygo sp_helpconstraint objname=Countrygo sp_helpconstraint objname=OrderDetailgosp_helpconstraint objname=PickOfMonthgo sp_helpconstraint objname=Categorygo sp_helpconstraint objname=Recipientgo sp_helpconstraint objname=Shipmentgo sp_helpconstraint objname=ShippingModego sp_helpconstraint objname=ShippingRatego sp_helpconstraint objname=Shoppergo sp_helpconstraint objname=ShoppingCartgo sp_helpconstraint objname=ToyBrandgosp_helpconstraint objname=Toysgo sp_helpconstraint objname=Wrapper； */ -4 create type xianke from char(3)alter table Recipient alter column cCountryId kkalter table Countryalter column cCountryId kk5.USE GlobalToyzGOCREATE TABLE PremiumToys ( cToyIdchar(6) primary key,vToyNamevarchar(20) not null,vToyDescriptionvarchar(250),cCategoryIdchar(3) ,mToyRatemoney not null,cBrandIdchar(3),imPhoto image,siToyQohsmallint not null,siLowerAgesmallint not null,siUpperAgesmallint not null,siToyWeightsmallint,vToyImgPathvarchar(50) null)INSERT INTO PremiumToysSELECT * FROM Toys WHERE mToyRate206.7.ALTER Table Toys ADD siToyQoh smallint CHECK（siToyQoh=0 AND siToyQoh0)ALTER TABLE Toys ADD siToyWeight CHECK(siToyWeigh=1)(cToyId char(6) primary key,vToyNamevarchar(20) not null,vToyDescriptionvarchar(250),cCategoryIdchar(3),mToyRatemoney not null(mToyRate0),cBrandIdchar(3),imPhoto image,siLowerAgesmallint not null,siUpperAgesmallint not null,siToyWeightsmallint,vToyImgPathvarchar(50) null)8.update ToysSet mToyRate=mToyRate+1Where cToyId=000001实验二 1. SELECT vFirstName ,vLastName ,vEmailId FROM GlobalToyz.dbo.Shopper WHERE cState = California OR cState = IllinoisGO2. SELECT cOrderNo ,cShopperId ,mTotalCost FROM GlobalToyz.dbo.Orders ORDER BY mTotalCost ASCGO3. SELECT * FROM GlobalToyz.dbo.OrderDetail WHERE vMessage IS NULLGO4. SELECT top 5 cToyId ,siMonth ,iYear ,iTotalSold FROM GlobalToyz.dbo.PickOfMonth WHERE iYear = 2000 ORDER BY iTotalSold DESCGO5. SELECT top 5 cToyId ,siMonth ,iYear ,iTotalSold FROM GlobalToyz.dbo.PickOfMonth WHERE iYear = 2000 ORDER BY iTotalSold DESCGO6. SELECT cOrderNo ,mToyCost FROM GlobalToyz.dbo.OrderDetail WHERE mToyCost 50GO7. SELECT vToyName ,ToyBrand.cBrandId ,ToyBrand.cBrandName ,Category.cCategoryId ,Category.cCategory ,Category.vDescription FROM GlobalToyz.dbo.Toys, GlobalToyz.dbo.ToyBrand, GlobalToyz.dbo.Category WHERE toys.cBrandId = ToyBrand.cBrandId AND toys.cCategoryId = Category.cCategoryIdGO8. SELECT vToyName ,ToyBrand.cBrandId ,ToyBrand.cBrandName ,Category.cCategoryId ,Category.cCategory ,Category.vDescription FROM GlobalToyz.dbo.Toys, GlobalToyz.dbo.ToyBrand, GlobalToyz.dbo.Category WHERE toys.cBrandId = ToyBrand.cBrandId AND toys.cCategoryId = Category.cCategoryIdGO9. SELECT vToyName ,ShoppingCart.cCartId FROM GlobalToyz.dbo.Toys, GlobalToyz.dbo.ShoppingCart WHERE Toys.cToyId = ShoppingCart.cToyIdGO10. SELECT FLOOR(AVG(mToyRate) FROM GlobalToyz.dbo.ToysGO11. SELECT FLOOR(AVG(mToyRate) FROM GlobalToyz.dbo.ToysGO12. SELECT Shopper.vFirstName ,Shopper.vLastName ,Shopper.vAddress ,Shopper.cCity ,Recipient.vFirstName ,Recipient.vLastName ,Recipient.vAddress ,Recipient.cCity FROM GlobalToyz.dbo.Shopper, GlobalToyz.dbo.RecipientGO13. SELECT vToyName FROM GlobalToyz.dbo.Toys WHERE cToyId IN ( SELECT cToyId FROM GlobalToyz.dbo.OrderDetail WHERE cWrapperId IS NULL )GO14. SELECT cOrderNo ,dOrderDate FROM GlobalToyz.dbo.Orders WHERE cOrderNo IN ( SELECT cOrderNo FROM GlobalToyz.dbo.Shipment WHERE cDeliveryStatus = d )GO实验三1. CREATE VIEW IS_CHAXUNASSELECT Shopper.vFirstName,Shopper.vLastName,Shopper.cCity,Toys.vToyName,Toys.mToyRate,OrderDetail.siQtyFROM Shopper,Orders,OrderDetail,ToysWHERE Orders.cOrderNo=OrderDetail.cOrderNo AND Shopper.cShopperId=Orders.cShopperId AND OrderDetail.cToyId=Toys.cToyId;2. SELECT vFirstName,vLastName,vToyName,siQtyFROM IS_CHAXUNWHERE cCity=California;3. UPDATE vwOrderWrapperSET siQty = 2, mWrapperRate = mWrapperRate + 1 FROM vwOrderWrapperWHERE cOrderNo = 0000014. Create Trigger truOD ON OrderDetailFOR UPDATEASIF UPDATE(siQty) BEGIN DECLARE siQty intSELECT siQty=deleted.siQtyFROM deletedUPDATE OrderDetail SET mToyCost=mToyCost/siQty*siQty WHERE cOrderNo IN (SELECT cOrderNoFROM deleted) END实验四1. SELECT cOrderNo ,dOrderDate FROM GlobalToyz.dbo.Orders WHERE cOrderNo IN ( SELECT cOrderNo FROM GlobalToyz.dbo.Shipment WHERE cDeliveryStatus = d )GO2. CREATE PROCEDURE prcCharges cOrderNo int,mShippingCharges money OUTPUT, mGiftWrapCharges money OUTPUTASBEGINSELECT cOrderNo=cOrderNo, mShippingCharges =mShippingCharges, mGiftWrapCharges =mGiftWrapChargesFROM OrdersWHERE cOrderNo=cOrderNoEND3. CREATE PROCEDURE prcHandlingCharges cOrderNo intASBEGINDECLARE mScharges money DECLARE mGWcharges moneyEXEC prcCharges cOrderNo,mScharges output, mGWcharges outputRETURN mScharges+mGWchargesEND实验五1. CREATE PROCEDURE tianjia cCartId char(6),cShopperId char(6),cToyId char(6),siQty smallintASBEGINBEGIN TRANSACTIONDECLARE date datetime;DECLARE mToyRate moneyDECLARE cOrderNo char(6)SELECT date=GETDATE();EXEC cOrderNo= prcGenOrderINSERT INTO OrdersVALUES(cOrderNo,date,cCartId,cShopperId,null,null,null,null,null,null);INSERTINTO OrderDetailVALUES(cOrderNo,cToyId,siQty,null,null,null,null)SELECT mToyRate=mToyRate FROM Toys WHERE cToyId=cToyIdUPDATE OrderDetailSET mToyCost=siQty*mToyRateCOMMIT TRANSACTIONEND2. CREATE PROCEDURE reporASBEGINDECLARE date datetime;DECLARE totalcost money;SELECT date=GETDATE();SELECT totalcost=SUM(mTotalCost)FROM OrdersWHERE dOrderDate =dateIF(totalcost170)PRINTHigh SalesELSEPRINTLow SalesEND四、调用示例实验三实验四实验四实验五五、实验心得通过这几次实验，我加深对数据库SQL语言的理解和认识，并学会了用它解决一些简单的实际问题。我加深了SQL语言的理解。丰富了我的关于数据库领域的视野。在实验中，由于自己的基础知识不牢固，遇到了很多的问题，我也认识到了课本中理论的重要性。在实验之前一定要吃透课本。只有把课本的基础知识掌握牢固了才能进一步地做好实际操作。并在实验中发现问题，自己看书，自己解决问题。实验的收获也是可观的。不但培养了我的动手操作能力，知识运用能力，独立思考能力，而且让我学会了如何在做计算机方面的实验时，应该注意的一些东西。