中南大学数据库实验报告

中 南 大 学

《数据库原理》实验报告

学生姓名 学 号 专业班级

指导教师 盛津芳 学 院 信息科学与工程学院 完成时间 2011年1月

实验一、实验二

一、实验目的：

1、 2、

了解DBMS 的工作环境和系统构架； 熟悉通过SQL 对数据库进行操作

二、实验内容及结果（附代码）：

1. 创建表、更新表和实施数据完整性

（1）运行给定的SQL Script，建立数据库GlobalToyz 。 （2）了解表的结构，建立所有表的关系图。

（3）利用系统定义的存储过程sp_helpdb查看数据库的相关信息，例如所有者、大小、创建日期等。

（4）查看所有表中出现的约束（包括Primary key, Foreign key, check constraint, default, unique）

（5）把价格在$20以上的所有玩具的信息拷贝到称为PremiumToys 的新表中。

select *

into PremiumToys from Toys

where mToyRate>20

（6）对表Toys 实施下面数据完整性规则：（1）玩具的现有数量应在0到200之间；（2）玩具适宜的最低年龄缺省为1。

alter table toys

add check(siToyQoh>0 and siToyQoh

（7）给id 为‘000001’玩具的价格增加$1。

update toys

set mToyRate=mToyRate+1 where cToyId=000001;

（8）列出表PickofMonth 中的所有记录，并显示中文列标题。

2. 查询数据库

（1）显示属于California 和Illinoi 州的顾客的名、姓和emailID

select vFirstName,vLastName,vEmailId from dbo.Shopper

where cState IN ('California','Illinoi');

（2）显示定单号码、顾客ID ，定单的总价值，并以定单的总价值的升序排列。

select cOrderNo,cShopperId,mTotalCost from dbo.Orders

ORDER BY mTotalCost ASC;

（3）显示在orderDetail 表中vMessage 为空值的行。

select *

From dbo.OrderDetail

where vMessage IS NULL;

（4）显示玩具名字中有“Racer ”字样的所有玩具的基本资料。

select *

From dbo.Toys

Where vToyName LIKE '%Racer%';

（5）根据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;

（6）根据OrderDetail 表，显示玩具总价值大于￥50的定单的号码和玩具总价值。

select cOrderNo,mToyCost From dbo.OrderDetail

Where mToyCost>50;

（7）显示一份包含所有装运信息的报表，包括：Order Number, Shipment Date, Actual Delivery Date, Days in Transit. (提示：Days in Transit = Actual Delivery Date – Shipment Date)

select

cOrderNo,dShipmentDate,dActualDeliveryDate,datediff(dd,dShipmentDate,dActualDeliveryDate) DaysInTransit

from shipment

（8）显示所有玩具的名称、商标和种类（Toy Name, Brand, Category）。

select vToyName,cBrandName,cCategory from Category,Toys,ToyBrand

where toys.cCategoryId=Category.cCategoryId and Toys.cBrandId=ToyBrand.cBrandId order by cBrandName

（9）以下列格式显示所有购物者的名字和他们的简称：（Initials, vFirstName, vLastName）, 例如Angela Smith的Initials 为A.S 。

select left(vFirstName,1)+'.'+left(vLastName,1) Initials,vFirstName,vLastName from shopper

（10）显示所有玩具的平均价格，并舍入到整数。

select ROUND(avg(mtoyrate),0) averagerate from toys

（11）显示所有购买者和收货人的名、姓、地址和所在城市，要求显示结果中的重复记录。

select vFirstName,vLastName,vAddress,cCity from Shopper union

select vFirstName,vLastName,vAddress,cCity from Recipient

（12）显示没有包装的所有玩具的名称。（要求用子查询实现）

select vToyName from toys

where cToyId IN (select cToyId from OrderDetail

where cWrapperId is null)

（13）显示已收货定单的定单号码以及下定单的时间。（要求用子查询实现）

select cOrderNo,dOrderDate from Orders

where cOrderNo IN (select cOrderNo from Shipment

where dActualDeliveryDate is not null)

（14）显示一份基于Orderdetail 的报表，包括cOrderNo,cToyId 和mToyCost ，记录以cOrderNo 升序排列，并计算每一笔定单的玩具总价值。（提示：使用运算符COMPUTE BY）。 3. 视图

（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 WHERE cOrderNo = ‘000001’

修改更新命令，以更新基表中的值，观察基表中的修改结果。

三、参考书籍：

《数据库系统概论》（第四版） 王珊，萨师煊 高等教育出版社

实验三、实验四

一、实验目的：

1、 了解DBMS 的工作环境和系统构架； 2、 熟悉通过SQL 对数据库进行操作

二、实验内容：

1. 实验三：存储过程与触发器

（1）编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平均价格接近$24.5为止。此外，任何玩具的最大价格不应超过$53。

（2） 创建一个称为prcCharges 的存储过程，它返回某个定单号的装运费用和包装费用。

（3）创建一个称为prcHandlingCharges 的过程，它接收定单号并显示经营费用。PrchandlingCharges 过程应使用prcCharges 过程来得到装运费和礼品包装费。

提示：经营费用=装运费+礼品包装费

（4）在OrderDetail 上定义一个触发器，如果购物者改变了定单的数量，玩具的成本也自动地改变。（提示：Toy cost = Quantity * Toy Rate） 2. 实验四：事务与游标

（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

‘000’+Convert(char,@OrderNo+1) WHEN @OrderNo>=999 and @OrderNo=9999 and @OrderNo

WHEN @OrderNo>=99999 Then Convert(char,@OrderNo+1) END RETURN

当购物者确认定单时，应该出现下面的步骤： a. 用上面的过程产生定单号。

b. 定单号，当前日期，购物车ID ，和购物者ID 应该加到Orders 表中。 c. 定单号，玩具ID ，和数量应加到OrderDetail 表中。

d. 在OrderDetail 表中更新玩具成本。（提示：Toy cost = Quantity * Toy Rate ）. 将上述步骤定义为一个事务。编写一个过程以购物车ID 和购物者ID 为参数，实现这个事务。

（2）编写一个程序显示每天的定单状态。如果当天的定单值总合大于170，则显示“High sales”, 否则显示”Low sales”. 报告中要求列出日期、定单状态和定单总价值。

三、实验结果：

1、 实验三：

（1）select *from toys

create proc avgpriceup_proc as

declare @mToyRate money

while{round((select avg(mToyRate) from toys)-24.5,1)0} begin

update Toys set mToyRate=mToyRate+0.5 select @mToyRate=mToyRate from Toys if(@mToyRate-53>0) break end go

exec avgpriceup_proc

select *from toys

（2）alter proc prCharges

@corderno char(6)

@mshippingcharges money output

@mgiftwrapcharges money output

as

select

@mshippingcharges=mshippingcharges

@mgiftwrapcharges=mgiftwrapcharges

from orders

where CorderNo=@corderno

go

declare @mshippingcharges money

mshippingcharges money

exec prcharges’000001’,@ mshippingcharges output

@mgiftwrapcharges output

print’装运费用’:’+couvert(varchar(10),@mshippingcharges)

print’包装费用’:’+couvert(varchar(10),@ mgiftwrapcharges)

（3）create pro pycHandlingcharge

@orderno char(6)

@runcost money output

As

Declare @ mshippingcharges money

@mshippingcharges money

exec prccharges’000001’,@mshippingcharges output

@mgiftwrapcharges output

Set @runcost=@mshippingcharges+@mgiftwrapcharges

Go

Declare @runcost money

Exec prcHandlingcharges’000001’,@runcost output

Print’经营费用’:+convert(varchar(10),@runcost)

（4）create trigger OrderDetail_tyg

on OrderDetail

for update

as

declare @siQty int

declare @mToyCost money

declare @ToyRate money

select @ToyRate=mToyRate from Toys

select @siQty=siQty from inserted

set @mToyCost=@siQty*@ToyRate

update OrerDetail

set mToyCost=@mToyCost

go

2、 实验四：

（1）begin transaction

declare @count int /*用来记录用户错误号*/

set @count=0 /*初始化*/

declare @OrderNo char(6)

exec prcGenorder @orderno output

set @count=@count+@@ERROR 记录上条语句是否发生错误 declare @ccartid char(6)

declare @cshopperid char(6)

declare @toyid char(6)

declare @siqty int

declare @mToyRate money

set @ccartid=’000009’ 自定义购物车ID

set @cshopper=’000007’ 自定义购物者ID

set @toyid=’000008’ 自定义玩具ID

set @siqty=2 自定义玩具数量

select @mToyRate=mToyRate from Toys where cToyId=@toyid

insert into orders values

(@OrderNo,GETDATE(),@ccartid,@cshopperid,null,null,null,null,null,null) Set @count=@count+@@ERROR 记录上条语句是否发生错误 Insert into OrderDetail values

(@OrderNo,@toyid,@siqty,null,null,null,@mToyRate*@siQty)

Set @count=@count+@@ERROR 记录上条语句是否发生错误

If(@count>0)

Begin

Print’发生错误’

rollback

end

else commit

select *from Orders

select *from OrderDetail

（2）create pro typeorder

@datetime datetime

As

Declare @totalcost money

Set @totalcost=(select sum(mTotalcost) from Orders

Where dOrderDate=@datetime)

If(@totalcost>170)

Print’High sales’

Else

Print’Low sales’

Exec typeorder ‘2010-12-20’ 输入查询日期

四、参考书籍：

《数据库系统概论》（第四版） 王珊，萨师煊 高等教育出版社