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

1、中南大学数据库实验报告中南大学数据库实验报告学 院： 信息科学与工程学院 专业班级： 学 号： 姓 名： 指导老师： 实验一：创建表和实施数据完整性 - 1 实验二：存储过程与触发器 - 11 实验三：视图、事务与游标 - 15 数据库关系图 - 21 参考书籍 - 22 实验1：创建表和实施数据完整性1.运行给定的SQL Script，建立数据库GlobalToyz。了解表的结构。2.利用系统预定义的存储过程sp_helpdb查看数据库的相关信息，例如所有者、大小、创建日期等。3.利用系统预定义的存储过程sp_helpconstraint查看所有表中出现的约束（包括Primary key,

2、Foreign key, check constraint, default, unique）4.对表Toys实施下面数据完整性规则：（1）玩具的现有数量应在0到200之间；（2）玩具适宜的最低年龄缺省为1。 查询、更新数据库1.显示属于California和Illinoi州的顾客的名、姓和emailId。 SELECT vFirstName,vLastName,vEmailId From Shopper WHERE cState=California or cState=Illinoi; 2.显示定单号码、顾客ID，定单的总价值，并以定单的总价值的升序排列。 SELECT cOrderNo,

3、cShopperId,mTotalCost From Orders ORDER BY mTotalCost ASC; 3.显示在orderDetail表中vMessage为空值的行。 SELECT * From orderDetail WHERE vMessage IS NULL; 4.显示玩具名字中有“Racer”字样的所有玩具的基本资料。 SELECT * From Toys WHERE vToyName LIKE %Racer%;5.列出表PickofMonth中的所有记录，并显示中文列标题。SELECT cToyId 玩具编号,siMonth 月,iYear 年,iTotalSold

4、销售总量 From PickofMonth; 6.根据2000年的玩具销售总数，显示“Pick of the Month”玩具的前五名玩具的ID。 select top 5 cToyId,SUM(iTotalSold) as total from PickOfMonth where iYear=2000 group by cToyId Order By total DESC; 7.根据OrderDetail表，显示玩具总价值大于￥50的定单的号码和玩具总价值。 select cOrderNo,mToyCost from OrderDetail where mToyCost50; 8.显示一份包

5、含所有装运信息的报表，包括：Order Number, Shipment Date, Actual Delivery Date, Days in Transit. select cOrderNo Order_Number,dShipmentDate Shipment_Date,dActualDeliveryDate Actual_Delivery_Date,dActualDeliveryDate-dShipmentDate Days_in_Transit from Shipment; 9.显示所有玩具的名称、商标和种类（Toy Name, Brand, Category）。 select vT

6、oyName,cBrandId,cCategoryId from Toys; 10.以下列格式显示所有购物者的名字和他们的简称：（Initials, vFirstName, vLastName）,例如Angela Smith的Initials为A.S。select left(vFirstName,1)+.+left(vLastName,1) Initials, vFirstName,vLastName from shopper; 11.显示所有玩具的平均价格，并舍入到整数。 select Round(AVG(mToyRate),0)averagerate from Toys; 12.显示所有购

7、买者和收货人的名、姓、地址和所在城市，要求显示结果中的重复记录。 select vFirstName,vLastName,vAddress,cCity from REcipient union select vFirstName,vLastName,vAddress,cCity from shopper; 13.显示没有包装的所有玩具的名称。（要求用In子查询实现） select vToyName from Toys where cToyId NOT IN ( select cToyId from OrderDetail,Wrapper where OrderDetail.cWrapperId

8、=Wrapper.cWrapperId ); 14.显示已收货定单的定单号码以及下定单的时间。（要求用Exists子查询实现） select cOrderNo,dOrderDate from Orders where exists( select * from shipment where Orders.cOrderNo=cOrderNo and dactualDeliveryDate is not NULL ); 15.显示一份基于Orderdetail的报表，包括cOrderNo,cToyId和mToyCost，记录以cOrderNo升序排列，并计算每一笔定单的玩具总价值。（提示：使用运算

9、符COMPUTE BY）。 select cOrderNo,cToyId,mToyCost from Orderdetail order by cOrderNo compute sum(mToyCost) by cOrderNo; 16.把价格在$20以上的所有玩具的信息拷贝到称为PremiumToys的新表中。 create table PremiumToys ( cToyId char(6) check(cToyId like(0-90-90-90-90-90-9) ) primary key, vToyName varchar(20) not null, vToyDescription

10、varchar(250), cCategoryId char(3) references Category(cCategoryId) , mToyRate money not null, cBrandId char(3)references ToyBrand(cBrandId), imPhoto image, siToyQoh smallint not null, siLowerAge smallint not null, siUpperAge smallint not null, siToyWeight smallint, vToyImgPath varchar(50) null )inse

11、rt into PremiumToys select * from Toys where mToyRate 20 select * from PremiumToys17.给id为000001玩具的价格增加$1。 update Toys set mToyRate=mToyRate+1 where cToyId=000001 select cToyId,vToyName,mToyRate from Toys where cToyId=000001 修改前： 修改后： 18.删除“Largo”牌的所有玩具。 delete from Toyswhere cBrandId=(select cBrandI

12、d from ToyBrand where cBrandName=Largo) select * from Toyswhere cBrandId=( select cBrandId from ToyBrand where cBrandName=Largo ) 有外键约束存在，无法删除。实验2：存储过程与触发器1. 编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平均价格接近$24.5为止。此外，任何玩具的最大价格不应超过$53。 create procedure inprove_price as declare average money /*money 是数据类型*/ select a

13、verage=AVG(mToyRate ) from Toys begin while average24.5 begin update Toys set mToyRate=mToyRate+0.5 where mToyRate=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应该加到O

14、rders表中。（3）定单号，玩具ID和数量应加到OrderDetail表中。（4）在OrderDetail表中更新玩具成本。（提示：Toy cost = Quantity * Toy Rate）.将上述步骤定义为一个事务。编写一个过程以购物车ID和购物者ID为参数，实现这个事务。 begin transaction Order_Comfirmation declare cCartId char(6) declare ShopperId char(6) declare OrderNo char(6) declare cToyId char(6) declare siQty smallint d

15、eclare mToyRate money set cCartId=000009 set ShopperId=000007 exec prcGenOrder OrderNo output set cToyId=000008 set siQty=2 select mToyRate=mToyRate from Toys where cToyId=cToyId insert into Orders (cOrderNo,dOrderDate,cCartId,cShopperId) values(OrderNo,getdate(),cCartId,ShopperId) insert into Order

16、Detail (cOrderNo,cToyId,siQty) values(OrderNo,cToyId,siQty) update OrderDetail set mToyCost=siQty*( select mToyRate from Toys where cToyId=cToyId ) where cOrderNo=OrderNo commit 测试： select * from Orders select * from OrderDetail4.编写一个程序显示每天的定单状态。如果当天的定单值总合大于170，则显示“High sales”,否则显示”Low sales”。报告中要求列

17、出日期、定单状态和定单总价值。（要求用游标实现） declare sales_status scroll cursor for select distinct dOrderDate,sum(mTotalCost) Date_Total from Orders group by dOrderDate Open sales_status declare dOrderdate datetime declare Date_Total money print dOrderdate Date_Total fetch first from sales_status into dOrderdate,Date_

18、Totalprint convert(char(10),dOrderdate)+ +convert(char(10),Date_Total) if Date_Total170 print High sales else print Low sales while fetch_status=0 begin fetch next from sales_status into dOrderdate,Date_Total print convert(char(10),dOrderdate)+ +convert(char(10),Date_Total) if Date_Total170 and Date_Total is not null print High sales else print Low sales end deallocate sales_status 数据库表关系图参考书籍1.数据库原理、编程与性能（第二版） Patrick QNeil，Elzabeth ONeil机械工业出版社2.数据库系统概论（第四版）王珊，萨师煊 高等教育出版社