数据库实验报告.pdf

《数据库实验报告.pdf》由会员分享，可在线阅读，更多相关《数据库实验报告.pdf（31页珍藏版）》请在三一文库上搜索。

1、数据库实验报告 班级：计算机科学与技术 1005 学号：0909102518 姓名：赵书剑 指导教师：盛津芳 目录 一实验目的1 二实验要求1 三实验内容1 四运行结果6 五实验总结20 一 实验目的 1了解 DBMS的工作环境和系统框架； 2通过 SQL语言对数据库进行操作； 3熟悉 SQL语句。 二 实验要求 1所有的 SQL语句和源代码； 2要求有适当的注释； 3 性约束实施、 实验三、 实验四和实验五要求给出相应的测试用 例。 三实验内容 实实验验一一：创建表、更新表和实施数据完整性 1运行给定的 SQL Script，建立数据库 GlobalToyz。 2创建所有表的关系图。 3列出

2、所有表中出现的约束（包括 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

3、） 玩具的价格应大于 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表，显示玩具总价值大

4、于￥50的定单的 号码和玩具总价值。 7. 显示一份包含所有装运信息的报表，包括：Order Number, Shipment Date, Actual Delivery Date, Days in Transit.(提 示 ： DaysinTransit=Actual Delivery Date Shipment Date) 8. 显示所有玩具的名称、商标和种类（Toy Name, Brand, Category）。 9. 以 下 列 格 式 显 示 所 有 购 物 者 的 名 字 和 他 们 的 简 称 ： （Initials, vFirstName, vLastName） ,例如 Ang

5、ela Smith 的 Initials 为 A.S。 10.显示所有玩具的平均价格，并舍入到整数。 11. 显示所有购买者和收货人的名、姓、地址和所在城市，要求 显示结果中的重复记录。 12. 显示没有包装的所有玩具的名称。（要求用子查询实现） 13. 显示已收货定单的定单号码以及下定单的时间。（要求用子 查询实现） 14.显 示 一 份 基 于Orderdetail 的 报 表 ， 包 括 cOrderNo,cToyId和mToyCost， 记录以cOrderNo升序排列， 并计算每一笔定单的玩具总价值。（提示：使用运算符 COMPUTE BY）。 15. 把 价 格 在 $20 以 上

6、的 所 有 玩 具 的 信 息 拷 贝 到 称 为 PremiumToys的新表中。 实实验验三三：视图与触发器 1. 定义一个视图， 包括购买者的姓名、 所在州和他们所订购玩具 的名称、价格和数量。 2. 基于（1）中定义的视图，查询显示所有 California州的购 买者的姓名和他们所订购玩具的名称及数量。 3. 视图定义如下： CREATE VIEW vwOrderWrapper AS SELECTcOrderNo,cToyId,siQty, vDescription, mWrapperRate FROM OrderDetail JOIN Wrapper ONOrderDetail.c

7、WrapperId= Wrapper.cWrapperId 以下更新命令，在更新 siQty 和 mWrapperRate 属性使用 了以下更新命令时出现错误： UPDATE vwOrderWrapper SETsiQty=2,mWrapperRate= mWrapperRate + 1 FROM vwOrderWrapper WHERE cOrderNo = 000001 修改更新命令，以更新基表中的值。 4. 在OrderDetail上定义一个触发器， 如果购物者改变了定单 的数量，玩具的成本也自动地改变。（提示：Toy cost = Quantity * Toy Rate） 实实验验四四

8、：存储过程 1. 编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平 均价格接近$24.5 为止。此外，任何玩具的最大价格不应超 过$53。 2. 创建一个称为 prcCharges的存储过程，它返回某个定单号 的装运费用和包装费用。 3. 创建一个称为 prcHandlingCharges的过程，它接收定单 号并显示经营费用。PrchandlingCharges 过程应使用 prcCharges过程来得到装运费和礼品包装费。 提示：经营费用=装运费+礼品包装费 实实验验五五：事务与游标 1. 名为 prcGenOrder的存储过程产生存在于数据库中的定单 号： CREATEPROCED

9、UREprcGenOrder 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=999andOrderNo=9999 and OrderNo=99999Then Convert(char,OrderNo+1) END RETURN 当购物者确认定单时，应该出现下面的步骤： （1）用上面的过程产生定单号。 （2） 定单号， 当前日期， 购物车ID， 和购物者ID应

10、该加到 Orders 表中。 （3）定单号，玩具 ID，和数量应加到 OrderDetail表中。 （4）在 OrderDetail表中更新玩具成本。（提示：Toy cost = Quantity * Toy Rate）. 将上述步骤定义为一个事务。 编写一个过程以购物车 ID和购物者ID 为参数，实现这个事务。 编写一个程序显示每天的定单状态。如果当天的定单值总合大于 170，则显示“High sales”,否则显示”Low sales”.报告中要 求列出日期、定单状态和定单总价值。 四.运行结果 实验一： 表的关系图如下： 实验二: 1 显示属于California和Illinoi州的顾客

11、的名、 姓和emailID select vFirstName,vLastName,vEmailId from Shopper where cState = California or cState = Illinois; 2显示定单号码、商店 ID，定单的总价值，并以定单的总价值的升 序排列。 selectcOrderNo,cShopperId,mTotalCostfromOrdersorder by mTotalCost; 3显示在 orderDetail表中 vMessage为空值的行。 select * from OrderDetail where vMessage is NULL;

12、4显示玩具名字中有“Racer”字样的所有玩具的材料。 select * from Toys where vToyName like %Racer%; 5根据 2000 年的玩具销售总数，显示“Pick of the Month” 玩具的前五名玩具的 ID。 selectcToyId fromPickOfMonthgroupbycToyIdorderby sum(iTotalSold) desc limit 5; 6 根据 OrderDetail表， 显示玩具总价值大于￥50的定单的号码 和玩具总价值。 select cOrderNo,sum(mToyCost) from OrderDetai

13、l group by cOrderNo having sum(mToyCost) 50; 7显示一份包含所有装运信息的报表，包括：Order Number, ShipmentDate,ActualDeliveryDate,Daysin Transit. (提示：Days in Transit = Actual Delivery Date Shipment Date) select cOrderNo as Order Number,dShipmentDate as Shipment Date,dActualDeliveryDate as ActualDeliveryDate,datediff(d

14、ActualDeliveryDate,dShipme ntDate) as Days in Transit from Shipment; 8显示所有玩具的名称、商标和种类（Toy Name, Brand, Category）。 select vToyName,cBrandName,cCategory from (Toys natural join Toybrand) join Category using (cCategoryid); 9以下列格式显示所有购物者的名字和他们的简称：（Initials, vFirstName, vLastName） ,例如Angela Smith 的Initia

15、ls 为 A.S select concat(left(vFirstName,1), .,left(vLastName,1) as initials,vFirstName,vLastName from Shopper; 10显示所有玩具的平均价格，并舍入到整数。 select round(sum(mToyRate)/count(*) as average_rate from Toys; 11显示所有购买者和收货人的名、姓、地址和所在城市，要求显 示结果中的重复记录。 (selectvFirstName,vLastName,vAddress,cCityfrom Recipient)unional

16、l(select vFirstName,vLastName,vAddress,cCity from Shopper); 12显示没有包装的所有玩具的名称。（要求用子查询实现） select vToyName from Toys where cToyId in (select cToyId from OrderDetail where cGiftWrap = N); 13. 显示已收货定单的定单号码以及下定单的时间。 （要求 用子查询实现） select cOrderNo,dOrderDate from OrderswherecOrderNoin (selectcOrderNofromShipm

17、entwherecDeliveryStatus= d); 14.显 示 一 份 基 于Orderdetail的 报 表 ， 包 括 cOrderNo,cToyId 和 mToyCost，记录以 cOrderNo升序排列， 并计算每一笔定单的玩具总价值。（提示： 使用运算符COMPUTE BY） 。 SET gt=0;#grand total counter SET st=0;#subtotal counter SET pg=;#previous group SELECT cOrderNo,cToyId, mToyCost, (gt := gt + mToyCost) AS GrandTotal

18、, (st:=IF(pg!=cOrderNo,1,st+1)AS SubTotal,pg:=cOrderNo FROM OrderDetail ORDER BY cOrderNo ; 15.把 价 格 在 $20以 上 的 所 有 玩 具 的 信 息 拷 贝 到 称 为 PremiumToys的新表中。 createtablePremiumToysselect*fromToyswhere mToyRate 20; 16. 给 id 为000001玩具的价格增加$1。 updateToyssetmToyRate=mToyRate+1wherecToyId= 000001; 17. 删除“Larg

19、o”牌的所有玩具。 delete from Toys where cBrandId in (select cBrandId from ToyBrand where cBrandName = Largo); 有外键约束存在，无法删除 实验三： 1. 定义一个视图，包括购买者的姓名、所在州和他们所订购玩具的 名称、价格和数量。 CREATEVIEW demo(firstname,lastname,location,toyname,toyprice,toynum) AS SELECT vFirstName, vLastName, cState, vToyName, mToyRate, siQty F

20、ROM (Shopper JOIN Orders USING (cShopperId) JOIN OrderDetail USING(cOrderNo) JOIN Toys USING(cToyId); 2. 基于（1）中定义的视图，查询显示所有 California州的购买 者的姓名和他们所订购玩具的名称及数量。 SELECT firstname, lastname, toyname, toynum FROM demo WHERE LOCATION = California; 3.编写一段程序， 将每种玩具的价格提高￥0.5， 直到玩具的平均价 格接近$24.5为止。此外，任何玩具的最大价格

21、不应超过$53。 CREATEFUNCTIONPlusMoney(plusnumfloat,sumnum float,maxnum float) RETURNS int(11) BEGIN #Routine body goes here. Loop1:WHILE(SELECTSUM(mToyRate)/COUNT(*)FROM Toys) sumnum) DO UPDATE Toys SET mToyRate =mToyRate +plusnum WHERE mToyRate =0 and OrderNo=9 and OrderNo=99 and OrderNo=999 and OrderNo

22、=9999 and OrderNo=99999Then Convert(char,OrderNo+1) END RETURN 当购物者确认定单时，应该出现下面的步骤： （1）用上面的过程产生定单号。 （2） 定单号， 当前日期， 购物车ID， 和购物者ID应该加到 Orders 表中。 （3）定单号，玩具 ID，和数量应加到 OrderDetail表中。 （4）在 OrderDetail表中更新玩具成本。（提示：Toy cost = Quantity * Toy Rate）. 将上述步骤定义为一个事务。 编写一个过程以购物车 ID和购物者ID 为参数，实现这个事务。 - = -Template

23、generatedfromTemplateExplorer using: - Create Procedure (New Menu).SQL - - Use the Specify Values for Template Parameters -command(Ctrl-Shift-M)tofillinthe parameter - values below. - - This block of comments will not be included in - the definition of the procedure. - = SET ANSI_NULLS ON GO SET QUO

24、TED_IDENTIFIER ON GO - = - Author: - Create date: - Description: - = CREATEPROCEDUREprcGenOrder(ShoppingcartID char(6)OUTPUT,ShopperIDchar(6)OUTPUT,ToysID char(6) OUTPUT,QTY smallint OUTPUT) - Add the parameters for the stored procedure here AS DECLARE OrderNo char(6) SELECT OrderNo=Max(cOrderNo)FRO

25、M Orders SELECT 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 BEGIN -SETNOCOUNTONaddedtopreventextra result sets from - interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO Orders (cOrderNo,dOrde

26、rDate,cCartId,cShopperId) VALUES (OrderNo,CONVERT(datetime,GETDATE(),Shoppingca rtID,ShopperID) INSERT INTO OrderDetail (cOrderNo,cToyId,siQty) VALUES (OrderNo,ToysID,QTY) - Insert statements for procedure here END GO 2.编写一个程序显示每天的定单状态。如果当天的定单值总合大于 170，则显示“High sales”,否则显示”Low sales”.报告中要 求列出日期、定单状态

27、和定单总价值。 - = -TemplategeneratedfromTemplateExplorer using: - Create Procedure (New Menu).SQL - - Use the Specify Values for Template Parameters -command(Ctrl-Shift-M)tofillinthe parameter - values below. - - This block of comments will not be included in - the definition of the procedure. - = SET ANS

28、I_NULLS ON GO SET QUOTED_IDENTIFIER ON GO - = - Author: - Create date: - Description: - = CREATE PROCEDURE SATUTE AS BEGIN -SETNOCOUNTONaddedtopreventextra result sets from - interfering with SELECT statements. SET NOCOUNT ON; - Insert statements for procedure here SELECTGETDATE()ASdatetime,(CASEWHE

29、N COUNT(Orders.cOrderNo)=170THENHIGHWHEN COUNT(Orders.cOrderNo)170THENLOWEND)AS SATUTE,COUNT(Orders.mTotalCost) AS TOTALCOST FROM Orders END GO 五实验总结 通过数据库的实验，我对数据库的基本知识进行了一次系统的复 习，是的基础知识更加扎实了，对 SQL语言的掌握更加好，能够熟 练的利用 SQL语言操作数据库，真是受益匪浅，同时实践动手能力 也得到了提高，做实验确实是一件让人快速提高的事情。 一开始的时候，我并不知道怎么去操作，心里十分着急，后来在 老师和同学们的帮助下， 自己也查阅了很多资料， 开始慢慢的指导怎 么做了，我才开始发现，做实验确实是一件有趣的事情，我开始一点 点的钻研， 慢慢的用于发现了一些规律， 后来实验做起来就越来越轻 松了，看来坚持不懈，关于提问，自主学习在实验中是十分重要的。 正式通过这些， 我才能如愿的顺利完成这次实验， 同时也感谢那些帮 助过我的老师和同学们，没有你们的帮助，我是无法成长的，感谢你 们。